1. Classification of SQL language

SQL consists of three parts: Data definition language DDL: create tables, create views, and create indexes Data manipulation language DML: Add, Delete, modify, and query Data control language DCL: Assign permissionsCopy the code

Data Definition Language (DDL) keywords

Operation Object Operation Description CREATE Delete Modify a DATABASE CREATE DATABASE DROP DATABASE TABLE CREATE TABLE DROP TABLE ALTER TABLE INDEX CREATE INDEX DROP INDEX view  CREATE VIEW DROP VIEWCopy the code

Data Manipulation Language (DML) keywords

Operation Object Operation Method Add Delete Modify query records INSERT DELETE update SELECTCopy the code

SQL SELECT syntax SELECT column name FROM table name and: SELECT * FROM table nameCopy the code

INSERT INTO VALUES (1, 2,....) INSERT INTO table_name (column 1, column 2...) VALUES (Value 1, value 2,....) INSERT INTO Persons VALUES ('Gates'.'Bill'.'Xuanwumen 10'.'Beijing'INSERT INTO Persons (LastName, Address) VALUES ('Wilson'.'Champs-Elysees')
Copy the code

UPDATE table name SET column name = new value WHERE column name = some value UPDATE a column in a row we call lastname is"Wilson"UPDATE Person SET firstname ='Fred' WHERE LastName = 'Wilson'To UPDATE several columns in a row, we change address and add city: UPDATE Person SET address ='Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'
Copy the code

DELETE Statement The DELETE statement is used to DELETE rows from a table. Syntax DELETE FROM table name WHERE column name = value Deletes a row"Fred Wilson"DELETE FROM Person WHERE LastName ='Wilson'Delete All Rows You can delete all rows without deleting the table. This means that the table structure, attributes, and indexes are complete: DELETE FROM table_name or: DELETE * FROM table_nameCopy the code

Data Control Language (DCL) keywords

Add permission Delete permission database Grant REVOKECopy the code

Use order by to sort

Order by +desc Order by +desc Order by +desc order by +descCopy the code

Use the TOP keyword

Top 3 Returns the top 50% of the top three rowsCopy the code

3. Function query

The sum () function

The sum() function returns the sum of the column dataCopy the code

Business () function

Average () returns the average of the column dataCopy the code

The left () function

abcd  Left('String', 2) ab returns two characters to the left of the stringCopy the code

4. Connection query

The type of connection query

The types of join queries include inner join outer join cross joinCopy the code

In the connection

An inner Join returns only the records that match the two tablesCopy the code

Left join

Left JOIN returns the records that match the two tables, as well as the excess records from the left tableCopy the code

Right join

The right Join returns the records that match the two tables, as well as the excess records from the right tableCopy the code

Full Join

Full Join returns the records that match the two tables, as well as the excess records from the left and right tablesCopy the code

Cross join

Cross join left and right tables in pairsCopy the code

5. Specialized relational operations

Case 1

Select * from student where σ Sdept= 'male'Copy the code

Case 2

Select * from Student where Student = Sdept(Student);Copy the code

Example 3

Select * from Student where name = Sname,Sdept(Student);Copy the code

6. Data Control Language (DCL) keywords

Insert a single row of data using insert

Example: Insert into Students values (name, gender, date of birth)'Wang Weihua'.'male'.'1983/6/15')Copy the code

Use insert, the SELECT statement to add data from an existing table to an existing new table

Insert into < new table >< column name > select < original table name > from < original table name >'name'.'address'.'Email')select name,address,email from Students
Copy the code

Use delete to delete a row in a table

Delete from < table name >[where< delete condition >] Example: Delete from awhere name="Wang Weihua"Select * from table A where column value is Wang Weihua;Copy the code

Use TRUNCate TABLE to delete data in the entire table

Syntax: TRUNCate table < table name > Example: TRUNCate table addressListCopy the code

Use update to modify data

Syntax: update< table name >set< column name = updated value > [where< update condition >] Example: update addressListsetAge = 18whereName ="Wang Weihua"(Change wang Weihua's age to 18 years old)Copy the code

** Use select to query data **

Select * from students; select * from students; select * from students; select * from students;Copy the code

SQL Advanced Tutorial

TOP SQL clause

SQL TOP instance now, we want to start from above"Persons"Select the first two records in the table. SELECT TOP 2 * FROM Persons SELECT TOP PERCENT FROM Persons SELECT TOP PERCENT FROM Persons"Persons"Select 50% of the records in the table. SELECT * FROM Persons SELECT TOP 50 PERCENT * FROM PersonsCopy the code

SQL LIKE operator

Example 1 Now, we want to start from the above"Persons"Select from the table where you live"N"SELECT * FROM Persons WHERE City LIKE SELECT * FROM Persons WHERE City LIKE'N%'Example 2 Next, we want to go from"Persons"Select from the table where you live"g"SELECT * FROM Persons WHERE City LIKE'%g'Example 3 Next, we want to go from"Persons"Select from the table live in contains"lon"SELECT * FROM Persons WHERE City LIKE SELECT * FROM Persons WHERE City LIKE'%lon%'Example 4: By using the NOT keyword, we can use the"Persons"Table to select live in does not contain"lon"SELECT * FROM Persons WHERE City NOT LIKE SELECT * FROM Persons WHERE City NOT LIKE'%lon%'
Copy the code

SQL wildcards

Using the % wildcard example 1 now, we want to go from the above"Persons"Select from the table where you live"Ne"SELECT * FROM Persons WHERE City LIKE SELECT * FROM Persons WHERE City LIKE'Ne%'
Copy the code

Example 2 Next, we want to go from"Persons"Select from the table live in contains"lond"SELECT * FROM Persons WHERE City LIKE SELECT * FROM Persons WHERE City LIKE'%lond%'Using the _ wildcard example 1 now, we want to go from the above"Persons"After the first character of the selected name in the table is"eorge"SELECT * FROM Persons WHERE FirstName LIKE SELECT * FROM Persons WHERE FirstName LIKE'_eorge'Example 2 Next, we want to go from"Persons"The last name of the selected record in the table"C"Beginning, then an arbitrary character, then"r", then any character, then"er"SELECT * FROM Persons WHERE LastName LIKE'C_r_er'Using the [charlist] wildcard example 1 now, we want to get from the above"Persons"Select the city you live in"A" 或 "L" 或 "N"SELECT * FROM Persons WHERE City LIKE'[ALN]%'Example 2. Now, we want to go from the above"Persons"The city you live in is not selected in the table"A" 或 "L" 或 "N"SELECT * FROM Persons WHERE City LIKE'[!ALN]%'
Copy the code

SQL IN operator

The IN operator allows us to specify multiple values IN the WHERE clause.

SELECT * FROM Persons WHERE LastName IN (SELECT * FROM Persons WHERE LastName IN (SELECT * FROM Persons WHERE LastName IN ('Adams'.'Carter')
Copy the code

SQL BETWEEN operator

The BETWEEN operator instance displays in alphabetical order BETWEEN"Adams"And"Carter"SELECT * FROM Persons WHERE LastName BETWEEN SELECT * FROM Persons WHERE LastName BETWEEN'Adams' AND 'Carter'SELECT * FROM Persons WHERE LastName NOT BETWEEN; SELECT * FROM Persons WHERE LastName NOT BETWEEN'Adams' AND 'Carter'
Copy the code

SQL Alias

Suppose we have two tables that are:"Persons" 和 "Product_Orders". We give each of them an alias"p" 和 "po". Now, we want to list"John Adams"All orders. We can use the following SELECT statement:  SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John'SELECT statements without aliases:  SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons, Product_Orders WHERE Persons.LastName='Adams' AND Persons.FirstName='John'As you can see from the two SELECT statements above, aliases make the query easier to read and write.Copy the code

SELECT LastName AS Family, FirstName AS Name FROM PersonsCopy the code

SQL JOIN

SQL JOIN is used to query data from two or more tables based on the relationship between the columns in those tables.

Referencing two tables We can retrieve data from both tables by referencing two tables: Who ordered the product, and what did they order? SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_PCopy the code

In addition to the above methods, we can also use the keyword JOIN to retrieve data from two tables. If we want to list all orders, we can use the following SELECT statement:  SELECT Persons.LastName, Persons.FirstName, ORDER.OrderNo FROM Persons INNER JOIN Orders ON persons.id_p = orders.id_p ORDER BY persons.lastname In addition to the INNER JOIN we used in the above example, there are several other joins we can use. The following lists the types of joins you can use and the differences between them. LEFT JOIN: returns all rows from the LEFT table even if there is no match in the RIGHT table RIGHT JOIN: returns all rows from the RIGHT table even if there is no match in the LEFT table FULL JOIN: returns rows as long as there is a match in one of the tablesCopy the code

SQL INNER JOIN keyword

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_nameCopy the code

Now we want to list everyone's orders. You can use the following SELECT statement:  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastNameCopy the code

The INNER JOIN keyword returns a row if there is at least one match in the table. If rows in Persons do not match rows in Orders, those rows will not be listed.

SQL LEFT JOIN keyword

Now, we want to list all the people and their orders, if any. You can use the following SELECT statement:  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastNameCopy the code

The LEFT JOIN keyword returns all rows from Persons, even if there are no matching rows in the right table.

SQL RIGHT JOIN keyword

Now, we want to list all orders and who ordered them, if any. You can use the following SELECT statement:  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastNameCopy the code

The RIGHT JOIN keyword returns all rows from the RIGHT table (Orders), even if there are no matching rows in Persons.

SQL FULL JOIN keyword

Now we want to list all the people, their orders, all the orders, and the people who ordered them. You can use the following SELECT statement:  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastNameCopy the code

The FULL JOIN keyword returns all rows from Persons and Orders. If rows from Persons do not match in table Orders, or if rows from Orders do not match in table Persons, these rows will also be listed.

SQL UNION and UNION ALL operators

Use the UNION command example to list all the different employee names in China and the United States: SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USACopy the code

Note: This command cannot list all employees in China and the United States. In the example above, we have two employees with the same name, and only one of them is listed. The UNION command just picks different values.

The UNION ALL UNION ALL command is almost equivalent to the UNION command, except that the UNION ALL command lists ALL the values. SQL Statement 1 UNION ALL SQL Statement 2Copy the code

SQL SELECT INTO statement

The SQL SELECT INTO statement can be used to create a backup copy of a table.

SQL SELECT INTO instance - create backup copy the following example will do"Persons"The SELECT * INTO Persons_backup FROM Persons IN clause can be used to copy a table to another database'Backup.mdb'SELECT LastName,FirstName INTO Persons_backup FROM Persons. SELECT Persons_backup FROM PersonsCopy the code

SQL SELECT INTO instance - with A WHERE clause we can also add a WHERE clause. The following example is obtained from"Persons"Table from which to extract live in"Beijing"To create a name with two columns"Persons_backup"SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing'
Copy the code

SQL SELECT INTO instance - joined table It is also possible to SELECT data from more than one table. The following example creates a file named"Persons_Order_Backup"Select Persons from Persons and Orders from Persons;  SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_PCopy the code

SQL CREATE DATABASE statement

The CREATE DATABASE statement CREATE DATABASE is used to CREATE a DATABASE. SQL CREATE DATABASE syntax CREATE DATABASE database_name SQL CREATE DATABASE instance Now we want to CREATE a DATABASE named"my_db"Database. CREATE DATABASE my_DB Adds DATABASE tables by creating TABLE.Copy the code

SQL CREATE TABLE statement

SQL CREATE TABLE example This example shows how to CREATE a TABLE named"Person"In the table. This table contains five columns named:"Id_P","LastName","FirstName","Address"As well as"City":  CREATE TABLE Persons ( Id_P int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )Copy the code

SQL Constraints (Constraints)

SQL constraints Constraints are used to restrict the types of data that can be added to a table. Constraints can be specified when the TABLE is created (through the CREATE TABLE statement) or after the TABLE is created (through the ALTER TABLE statement). We will discuss the following constraints: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULTCopy the code

SQL NOT NULL constraint

SQL NOT NULL Constraint The NOT NULL constraint forces columns NOT to accept NULL values. The NOT NULL constraint forces a field to always contain a value. This means that you cannot insert a new record or update a record without adding a value to the field. The following SQL statement forces"Id_P"Column and"LastName"Columns do not accept NULL values:  CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )Copy the code

SQL UNIQUE constraints

SQL PRIMARY KEY constraint

SQL FOREIGN KEY constraint

SQL CHECK constraints

SQL DEFAULT constraints

SQL CREATE INDEX statement