Understanding SQL Advanced Advanced

sql tutorial column introduces 10 advanced advancements

Recommended (free): sql tutorial

sql advanced
1. TOP clause
TOP sub-clause statement is used to specify the number of records to be returned.

SELECT TOP 2 * FROM Persons 
 SELECT TOP 50 PERCENT * FROM Persons

2. Link search pattern
Now, we want to select people living in cities starting with "N" from the "Persons" table above :
We can use the following SELECT statement:

SELECT * FROM Persons 
 WHERE City LIKE ‘N%’

Tip: "%" can be used to define wildcards (missing letters in the pattern)
By using the NOT keyword, we can Select people living in cities that do not contain "lon" from the "Persons" table:
We can use the following SELECT statement:

SELECT * FROM Persons 
 WHERE City NOT LIKE ‘%lon%’

3, wildcard character

(1) By using the NOT keyword, we can select people from the "Persons" table who live in cities that do not contain "lon":
We can use the following SELECT statement:

SELECT * FROM Persons 
 WHERE City NOT LIKE ‘%lon%’

(2) We want to select people whose first character is followed by "eorge" from the "Persons" table above:
We can use the following SELECT statement:

SELECT * FROM Persons 
 WHERE FirstName LIKE ‘_eorge’

(3) We want to select people whose cities start with "A" or "L" or "N" from the "Persons" table above:
We can use the following SELECT statement:

SELECT * FROM Persons 
 WHERE City LIKE ‘[ALN]%’ 
 不 SELECT * FROM Persons 
 WHERE City LIKE ‘[!ALN]%’

4. IN operator
The IN operator allows us to specify multiple values ​​in the WHERE clause. IN Operator Example
Now, we want to select people with the last names Adams and Carter from the above table:
We can use the following SELECT statement:

SELECT * FROM Persons 
 WHERE LastName IN (‘Adams’,’Carter’)

5, BETWEEN Operator
The BETWEEN … AND operator selects a range of data between two values. The values ​​can be numeric, text, or dates
To display people between "Adams" (inclusive) and "Carter" (exclusive) in alphabetical order, use the following SQL:

SELECT * FROM Persons 
 WHERE LastName 
 BETWEEN ‘Adams’ AND ‘Carter’

The complement is No BETWEEN

6. By using SQL, you can specify aliases (Alias) for column names and table names.
Assume we have two tables respectively. Are: "Persons" and "Product_Orders". We give them the aliases "p" and "po" respectively.
Now, we want to list all orders for "John Adams".
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’

You can also use aliases for columns

SELECT LastName AS Family, FirstName AS Name 
 FROM Persons

7, join and Key
Sometimes in order to get complete results, we need to get results from two or more tables. We need to execute join.
Tables in a database can be linked to each other by keys. The primary key (Primary Key) is a column in which the value of each row is unique.


Reference two tables
We can get data from two tables by referencing two tables:
Who ordered the product, and they What product was ordered?

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo 
 FROM Persons, Orders 
 WHERE Persons.Id_P = Orders.Id_P

The following statement has the same effect as the above statement
SQL JOIN - Using Join
In addition to the above method, we can also use the keyword JOIN to extract data from two tables retrieve data.
If we want to list everyone's orders, we 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.LastName

INNER JOIN Return rows if there is at least one match

NOTE:

• JOIN: Return rows if there is at least one match in the table

• LEFT JOIN: Even if Even if there is no match in the right table, return all rows from the left table

• RIGHT JOIN: Even if there is no match in the left table, return all rows from the right table

• FULL JOIN: Return rows as long as there is a match in one of the tables
  

8、SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China 
 UNION 
 SELECT E_Name FROM Employees_USA

union和union all的区别就是union all会把所有的结果都输出来，union会把相同的给去掉

9、CREATE DATABASE database_name
用于创建数据库

CREATE TABLE Persons 
 ( 
 Id_P int, 
 LastName varchar(255), 
 FirstName varchar(255), 
 Address varchar(255), 
 City varchar(255) 
 )

10、sql约束
（1） 下面的 SQL 语句强制 “Id_P” 列和 “LastName” 列不接受 NULL 值：

CREATE TABLE Persons 
 ( 
 Id_P int NOT NULL, 
 LastName varchar(255) NOT NULL, 
 FirstName varchar(255), 
 Address varchar(255), 
 City varchar(255) 
 )

（2）SQL UNIQUE 约束 

• UNIQUE 约束唯一标识数据库表中的每条记录。 

• UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证 

• PRIMARY KEY 拥有自动定义的 UNIQUE 约束。 

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
（3）SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。 

• 主键必须包含唯一的值。 

• 主键列不能包含 NULL 值。
  每个表都应该有一个主键，并且每个表只能有一个主键。

想了解更多编程学习，敬请关注php培训栏目！

The above is the detailed content of Understanding SQL Advanced Advanced. For more information, please follow other related articles on the PHP Chinese website!