SQL review

From：http://www.w3school.com.cn/sql/index.asp
这里稍作删减，以便记忆。

SELECT

SELECT LastName, FirstName FROM Persons

SELECT * FROM Persons

DISTINCT

SELECT DISTINCT Company FROM Orders // 列出不同的值

WHERE

SELECT * FROM Persons WHERE City=’Beijing’

// 操作符有=、<>、>、<、>=、<=、BETWEEN、LIKE

AND & OR

SELECT * FROM Persons WHERE FirstName=’Thomas’ AND LastName=’Carter’

SELECT * FROM Persons WHERE firstname=’Thomas’ OR lastname=’Carter’

SELECT * FROM Persons WHERE (FirstName=’Thomas’ OR FirstName=’William’)
AND LastName=’Carter’

ORDER BY (用于对结果集排序)

// 默认升序，可以使用DESC关键字

SELECT Company, OrderNumber FROM Orders ORDER BY Company

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

INSERT INTO

INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,….)

INSERT INTO Persons (LastName, Address) VALUES (‘Wilson’, ‘Champs-Elysees’)

UPDATE

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE Person SET Address = ‘Zhongshan 23’, City = ‘Nanjing’
WHERE LastName = ‘Wilson’

DELETE

DELETE FROM 表名称 WHERE 列名称 = 值

DELETE * FROM table_name // 不删除表的情况下删除所有的行

高级

TOP (规定返回的记录数)

SELECT TOP number|percent column_name(s) FROM table_name

// 等价MySQL语法：SELECT column_name(s) FROM table_name LIMIT number

SELECT TOP 2 * FROM Persons

SELECT TOP 50 PERCENT * FROM Persons

LIKE (搜索列中的指定模式)

SELECT * FROM Persons WHERE City LIKE ‘N%’ // 以 “N” 开始的

SELECT * FROM Persons WHERE City LIKE ‘%g’ // 以 “g” 结尾的

SELECT * FROM Persons WHERE City LIKE ‘%lon%’ // 包含 “lon” 的

SELECT * FROM Persons WHERE City NOT LIKE ‘%lon%’ // 不包含 “lon” 的

SQL通配符

% 替代一个或多个字符

_ 替代一个字符

[charlist] 字符列中的任何单一字符

[^charlist]或者[!charlist] 不在字符列中的任何单一字符

SELECT * FROM Persons WHERE FirstName LIKE ‘_eorge’ // George

LIKE ‘C_r_er’ // Carter

LIKE ‘[ALN]%’ // 以 “A” 或 “L” 或 “N” 开头的，例如：London、New York

LIKE ‘[!ALN]%’ // 不以 “A” 或 “L” 或 “N” 开头的，例如：Beijing

IN (在 WHERE 子句中规定多个值)

SELECT * FROM Persons WHERE LastName IN (‘Adams’,’Carter’) // 姓氏为 Adams 和 Carter 的人

BETWEEN

SELECT * FROM Persons WHERE LastName BETWEEN ‘Adams’ AND ‘Carter’ // 以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人 （备注：经过实验发现，是>=”Adams”和<=”Carter”，between是包含边界值的）

SELECT * FROM Persons WHERE LastName NOT BETWEEN ‘Adams’ AND ‘Carter’ // 范围之外的人

Alias (别名)

SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName=’Adams’ AND p.FirstName=’John’

// 以上为表名称别名，别名使查询程序更易阅读和书写

SELECT LastName AS Family, FirstName AS Name FROM Persons

JOIN

// 从两个或更多的表中获取结果

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 (内连接): 如果表中有至少一个匹配，则返回行
• LEFT JOIN (左连接): 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN (右连接): 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN (全连接): 只要其中一个表中存在匹配，就返回行

UNION

// 用于合并两个或多个 SELECT 语句的结果集

// UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA

SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA // 允许重复的值

SELECT INTO

// 用于创建表的备份复件

SELECT * INTO Persons_backup FROM Persons

SELECT * INTO Persons IN ‘Backup.mdb’ FROM Persons // 向另一个数据库中拷贝表

CREATE DATABASE

CREATE DATABASE my_db

CREATE TABLE

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

Constraints 约束

约束用于限制加入表的数据的类型

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）

NOT NULL

强制列不接受 NULL 值

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

UNIQUE

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

// CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

ALTER TABLE Persons ADD UNIQUE (Id_P)

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

ALTER TABLE Persons DROP INDEX uc_PersonID // 撤销UNIQUE约束

PRIMARY KEY

主键：唯一、不能包含 NULL 值、

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

// CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

ALTER TABLE Persons　ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

ALTER TABLE Persons　DROP PRIMARY KEY

FOREIGN KEY

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

// CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders

CHECK

用于限制列中的值的范围

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

// CONSTRAINT chk_Person CHECK (Id_P>0 AND City=’Sandnes’)

ALTER TABLE Persons ADD CHECK (Id_P>0)

ALTER TABLE Persons DROP CHECK chk_Person

DEFAULT

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

ALTER TABLE Persons ALTER City SET DEFAULT ‘SANDNES’

ALTER TABLE Persons ALTER City DROP DEFAULT

DROP

ALTER TABLE table_name DROP INDEX index_name // 删除索引

DROP TABLE 表名称

DROP DATABASE 数据库名称

TRUNCATE TABLE 表名称 // 仅仅除去表内的数据，但并不删除表本身

ALTER

在已有的表中添加、修改或删除列

ALTER TABLE Persons ADD Birthday date

ALTER TABLE Persons ALTER COLUMN Birthday year // 改变数据类型实例

ALTER TABLE Person DROP COLUMN Birthday

AUTO INCREMENT

新记录插入表中时生成一个唯一的数字

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

VIEW (视图)

视图是基于 SQL 语句的结果集的可视化的表

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

DROP VIEW view_name // 撤销视图

Date (日期)

NULL

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products // 规定处理NULL值为0

数据类型

MySQL数据类型：文本、数字和日期/时间类型

SQL函数 (用于计数和计算的内建函数)

内建SQL函数的语法是：SELECT function(列) FROM 表

Aggregate函数：面向一系列的值，并返回一个单一的值

Scalar函数：面向某个单一的值，并返回基于输入值的一个单一的值

AVG (平均值)

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders) // 找到 OrderPrice 值高于 OrderPrice 平均值的客户

COUNT (返回匹配指定条件的行数)

SELECT COUNT(*) FROM table_name // 返回表中的记录数

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer=’Carter’

SELECT COUNT(*) AS NumberOfOrders FROM Orders

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders // 计算 “Orders” 表中不同客户的数目

FIRST (返回指定的字段中第一个记录的值)

// 可使用 ORDER BY 语句对记录进行排序

SELECT FIRST(column_name) FROM table_name

LAST (返回最后一个记录的值)

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

MAX (返回最大值)

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

MIN (最小值)

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

SUM (总数/总和)

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer // 查找每个客户的总金额

GROUP BY

用于结合合计函数，根据一个或多个列对结果集进行分组

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

Customer	SUM(OrderPrice)
Bush	2000
Carter	1700
Adams	2000

如果不用GROUP BY，是以下结果：

Customer	SUM(OrderPrice)
Bush	5700
Carter	5700
Bush	5700
Bush	5700
Adams	5700
Carter	5700

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate

HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000 // 查找订单总金额少于 2000 的客户

UCASE (把字段的值转换为大写)

SELECT UCASE(LastName) as LastName,FirstName FROM Persons // 把 “LastName” 列转换为大写

LCASE (把字段的值转换为小写)

SELECT LCASE(LastName) as LastName,FirstName FROM Persons

MID (提取字符)

SELECT MID(City,1,3) as SmallCity FROM Persons // 提取前3个字符

// MID(column_name,开始位置,返回的字符数)

LEN (返回文本字段中值的长度)

SELECT LEN(City) as LengthOfCity FROM Persons

ROUND

SELECT ROUND(column_name,decimals) FROM table_name // (要舍入的字段，要返回的小数位数)

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

NOW (返回当前的日期和时间)

SELECT ProductName, UnitPrice, Now() as PerDate FROM Products // 显示当天的日期所对应的名称和价格

FORMAT (对字段的显示进行格式化)

SELECT ProductName, UnitPrice, FORMAT(Now(),’YYYY-MM-DD’) as PerDate FROM Products

总结

语句	语法
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND|OR condition
ALTER TABLE (add column)	ALTER TABLE table_name ADD column_name datatype
ALTER TABLE (drop column)	ALTER TABLE table_name DROP COLUMN column_name
AS (alias for column)	SELECT column_name AS column_alias FROM table_name
AS (alias for table)	SELECT column_name FROM table_name AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name)
CREATE TABLE	CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,…….)
CREATE UNIQUE INDEX	CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s)FROM table_name WHERE condition
DELETE FROM	DELETE FROM table_name (*Note: **Deletes the entire table!!)*or DELETE FROM table_name WHERE condition)
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name1,SUM(column_name2)FROM table_name GROUP BY column_name1
HAVING	SELECT column_name1,SUM(column_name2)FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value
IN	SELECT column_name(s)FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2,….) or INSERT INTO table_name(column_name1, column_name2,…)VALUES (value1, value2,….)
LIKE	SELECT column_name(s)FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s)FROM table_name ORDER BY column_name [ASC|DESC]
SELECT	SELECT column_name(s)FROM table_name
SELECT *	SELECT *FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s)FROM table_name
SELECT INTO(used to create backup copies of tables)	SELECT *INTO new_table_name FROM original_table_name or SELECT column_name(s)INTO new_table_name FROM original_table_name
TRUNCATE TABLE(deletes only the data inside the table)	TRUNCATE TABLE table_name
UPDATE	UPDATE table_name SET column_name=new_value[, column_name=new_value]WHERE column_name=some_value
WHERE	SELECT column_name(s)FROM table_name WHERE condition