SQL SELECT statement
reference resources: https://www.w3school.com.cn/sql/sql_select.asp
The SELECT statement is used to SELECT data from a table. The results are stored in a result table (called a result set).
1. SQL SELECT statement
SELECT Column name FROM Table name
And:
SELECT * FROM Table name
Note: SQL statements are case insensitive. select is equivalent to select.
SQL SELECT instance
To get the contents of the columns named "LastName" and "FirstName" (from the database table named "Persons"), use a SELECT statement like this:
SELECT LastName,FirstName FROM Persons
SQL SELECT * instance
Now we want to select all the columns from the "Persons" table.
Replace the column name with the symbol * as follows:
SELECT * FROM Persons
Tip: the asterisk (*) is a shortcut to select all columns.
2. SQL SELECT DISTINCT statement
In the table, duplicate values may be included. This is not a problem, but sometimes you may want to list only different values.
The keyword DISTINCT is used to return a unique different value.
Syntax:
SELECT DISTINCT Column name FROM Table name
Use DISTINCT keyword
To SELECT all values from the "Company" column, we need to use the SELECT statement:
SELECT Company FROM Orders
To select only one different value from the "Company" column, we need to use the SELECT DISTINCT statement:
SELECT DISTINCT Company FROM Orders
3. WHERE clause
If necessary, add the data from the SELECT clause to the SELECT table.
grammar
SELECT column name FROM table name WHERE column operator value
The following operators can be used in the WHERE clause:
Use WHERE clause
If we only want to SELECT people living in the city "Beijing", we need to add a WHERE clause to the SELECT statement:
SELECT * FROM Persons WHERE City='Beijing'
4. SQL and & or operators
AND and OR can combine two OR more conditions in a WHERE sub statement.
- If both the first AND second conditions are true, the AND operator displays a record.
- If only one of the first and second conditions holds, the OR operator displays a record.
AND operator instance
Use AND to display all people whose last name is "Carter" AND whose first name is "Thomas":
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
OR operator instance
Use OR to display all people whose last name is "Carter" OR whose first name is "Thomas":
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
5. SQL ORDER BY clause
ORDER BY statement
- The ORDER BY statement sorts the result set based on the specified column.
- The ORDER BY statement sorts records in ascending ORDER BY default.
If you want to sort records in descending order, you can use the DESC keyword.
Example 1
Show company names in alphabetical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company
Example 2
Display the Company name in alphabetical order and the order number in numerical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
Example 3
Display company names in reverse alphabetical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
Example 4
Display the company name in reverse alphabetical order and the sequence number in numerical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
6.INSERT INTO statement
The INSERT INTO statement is used to insert a new row into a table.
grammar
INSERT INTO Table name VALUES (Value 1, Value 2,....)
We can also specify the column to insert data:
INSERT INTO table_name (Column 1, Column 2,...) VALUES (Value 1, Value 2,....)
Insert new row, SQL statement:
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
Insert data into the specified column, SQL statement:
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
7.Update statement
The Update statement is used to modify data in a table.
Syntax:
UPDATE Table name SET Column name = New value WHERE Column name = Certain value
Update a column in a row
We add firstname to the person whose lastname is "Wilson":
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
Update several columns in a row
We will modify the address and add the city name:
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
8.DELETE statement
The DELETE statement is used to DELETE rows in a table.
grammar
DELETE FROM Table name WHERE Column name = value
Delete a row
"Fred Wilson" will be deleted:
DELETE FROM Person WHERE LastName = 'Wilson'
Delete all rows
You can delete all rows without deleting the table. This means that the structure, attributes and indexes of the table are complete:
DELETE FROM table_name
Or:
DELETE * FROM table_name