SQL Overview (en)
Sure! Here's the translation into English while retaining the wiki format:
SQL (Structured Query Language) is the standard language used for managing and manipulating data in relational databases. A relational database itself is a system for organizing data in the form of interconnected tables.
Basic Concepts of SQL
- Table: A structure consisting of rows (records) and columns (fields). Each row represents a single piece of data, while each column represents a data attribute.
- Row (Record): A complete data entry in a table.
- Column (Field): A single data attribute in a table.
- Query: An SQL command used to retrieve, insert, update, or delete data from a database.
Example of Using SQL on Ubuntu Server (using MySQL)
Connecting to the Database
Before we can execute SQL queries, we need to connect to the database first. For example, we have a database named `mydatabase` with the username `root` and password `password`.
mysql -u root -p mydatabase
The system will prompt you to enter the password.
Creating a Table
To create a new table named `users` with columns `id`, `username`, and `password`, we can use the following query:
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50), password VARCHAR(50) );
Inserting Data
To insert data into the `users` table, we use the `INSERT INTO` command:
INSERT INTO users (username, password) VALUES ('john_doe', 'password123');
Retrieving Data
To retrieve all data from the `users` table, we use the `SELECT` command:
SELECT * FROM users;
If we want to retrieve specific data, for example, just the username and password, we can write:
SELECT username, password FROM users;
Updating Data
To update data in a specific row, we use the `UPDATE` command:
UPDATE users SET password = 'newpassword' WHERE id = 1;
The above command will change the password of the user with ID 1 to 'newpassword'.
Deleting Data
To delete data from a table, we use the `DELETE` command:
DELETE FROM users WHERE id = 1;
The above command will delete the user with ID 1.
Other Important SQL Commands:
- WHERE: Used to filter data based on certain conditions.
- ORDER BY: Used to sort the results of a query.
- GROUP BY: Used to group data based on specific columns.
- HAVING: Used to filter results after grouping.
- JOIN: Used to combine data from two or more tables.
More Complex Example
For example, if we want to find all users whose usernames start with the letter 'j':
SELECT * FROM users WHERE username LIKE 'j%';
Conclusion
SQL is a powerful and flexible language for managing databases. By understanding the basics of SQL, you can easily perform various operations on databases, from creating tables to executing complex queries.
Note:
- MySQL is one of many database management systems that support SQL. There are also PostgreSQL, Oracle, SQL Server, and many others.
- SQL syntax may vary slightly between database systems, but the fundamental concepts remain the same.