原创文章，欢迎转载，转载请注明出处（作者和链接）。
第三章 结构化查询语言
SQL命令分类：
- 数据定义语言：DDL. 用于创建表、关系和其他结构。
- 数据操作语言：DML. 查询、插入、修改和删除。
- SQL/持久存储模块：SQL/PSM. 扩展了SQL,添加了过程式编程功能，例如变量和控制流语句。
- 事务控制语言：TCL. 用于标记事务的边界，控制事务的行为。
- 数据控制语言：DCL. 用于给用户和组赋予／取消数据库许可。
3.2、DDL创建表和关系
创建表结构基本格式：
CREATE TABLE NewTableName(
Columnname DataType OptionalConstraint,//列定义
Columnname DataType OptionalConstraint,
Columnname DataType OptionalConstraint,
optional table canstrains
...
);
列定义的三个部分分别为列名、列数据类型、列值的约束（可选）。
列约束：PRIMARY KEY（主键）、FOREIGN KEY（候选键）、NOT NULL（不能为空）、NULL（允许为空）、UNIQUE（该列不能有重复值）、CHECK（介绍见后面）。
列数据类型（此处只列举SQL Server2014最常见的几种，详细可点击查看SQL数据类型）：
| Bit | 允许0、1或NULL |
| Int | 4字节。允许从 -2,147,483,648 到 2,147,483,647 的所有数字。 |
| Date | 3字节。默认格式YYYY-MM-DD。 |
| Char(n) | n字节。固定长度的字符串。 |
| Varchar(n) | n字节。变长度的字符串，长度为0-8000字节。 |
| Numeric(p,s) | 带s位小数的p位数字，不存储小数点 |
| BigInt | 8字节的整型 |
举例：
CREATE TABLE EMPLOYEE(
EmployeeNumber Int PRIMARY KEY,
Name Char(25) NOT NULL,
Department Char(25) NOT NULL DEFAULT 'human resources',
Email VarChar(100) NOT NULL UNIQUE,
);
3.2.1 使用表约束定义主键
首先，必须为主键列提供列约束NOT NULL；然后使用CONSTRAINT标识的表约束来常见主键。例如：
CREATE TABLE EMPLOYEE(
EmployeeNumber Int NOT NULL,
Name Char(25) NOT NULL,
Department Char(25) NOT NULL DEFAULT 'human resources',
Email VarChar(100) NOT NULL UNIQUE,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeNumber)
);
利用表约束常见逐渐的3个优点：
• 定义复合键的要求，因为PRIMARY KEY列约束不能用于多个列。当逐渐包含多个属性时，必须采用表约束方式定义主键。
• 使用表约束可以命名定义主键的约束。
• 可以方便的定义代理键。
定义代理键举例：
ProjectID Int NOT NULL IDENTITY(1000,100),
CONSTRAINT PROJECT_PK PRIMARY KEY(ProJectID)
IDENTITY关键字指示，在创建第一行时，这个代理键以值M开始，每添加一行，就递增N。所以ProjectID从1000开始，每添加一行递增100。
3.2.2 使用表约束定义外键
举例：
CREATE TABLE EMPLOYEE(
EmployeeNumber Int NOT NULL,
Name Char(25) NOT NULL,
Department Char(25) NOT NULL DEFAULT 'human resources',
Email VarChar(100) NOT NULL UNIQUE,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeNumber)，
CONSTRAINT EMP_DEPART_FK FOREIGN KEY(Department) REFERENCES DEPARTMENT(DepartmentName)
ON UPDATE CASCADE
);
EMPLOYEE表的Department列是EMPLOYEE表的外键，它的取值必须在DEPARTMENT表的DepartmentName中存在对应项。
EMP_DEPART_FK定义了EMPLOYEE中的Department列和DEPARTMENT中的DepartmentName列之间的外键关系。ON UPDATE CASCADE：ONUPDATE显示了在DEPARTMENT中的DepartmentName主键值改变时应执行的操作。关键字CASCADE表示应对EMPLOYEE表中的相同的Department列做相同的改变。
若为ONDELETE CASCADE：在删除DEPARTMENT表中的一行时，应级联删除EMPLOYEE表中的所有相关行。
若为ON DELETE/UPDATECASCADE：在删除／更新DEPARTMENT表中的一行时，不对EMPLOYEE做任何改变。
3.3 插入
两种情况：
1、提供了所有列的数据：
INSERT INTO DEPARTMENT VALUES(
'Administration', 3.1415, 123, '188-1068-8738'/*⚠️数字不需要用引号*/
);
2、缺少某些列的数据：
INSERT INTO PROJECT
(ProName, Department, MaxHoues)
VALUES('2017-OFFER-COME', 'XUESAN', 12);
注意，列名的顺序必须与值的顺序一致。
3.4 SQL DML单表查询
3.4.1 SELECT/FROM/WHERE架构
语法：
SELECT 列名1, 列名2, ...
FROM 表名
WHERE 指定条件
SELECT：指定在查询结果中要列出的 列
FROM：指定查询中使用的表
WHERE： 指定在查询结果中要列出的行
如果希望去掉重复行，使用DISTINCT关键字：
SELECT DISTINCT 列名
FROM 表名
WHERE子句常使用的关键字：
1）结合多个条件：使用AND或OR关键字：
SELECT *
FROM PROJECT
WHERE Department = 'Finance'
AND Maxhours > 15;
2） IN／NOT IN：指定列值必须是/不是值集中的某一个
SELECT *
FROM PROJECT
WHERE Depetment IN('abs','ced','and');
3）BETWEEN：指定值的范围。包含两端的值。
SELECT *
FROM PROJECT
WHERE EmployName BETWEEN 'Amy' AND 'Mary';
4）（NOT）LIKE：
SELECT *
FROM PROJECT
WHERE EmployName LIKE 'Liu%';
% ：百分号代表一个或多个未指定的字符
_ ：下划线代表一个为指定的字符
5）IS (NOT) NULL：
SELECT *
FROM PROJECT
WHERE Phone IS NULL;
3.4.6 对查询结果进行排序
ORDER BY：默认情况下，SQL会按照升序排序。关键字ASC和DESC可指定升序或降序：
SELECT *
FROM PROJECT
ORDER BY Depertment DESC, LastName ASC;
把员工先按照Department降序排列，然后在Department中再按照LastName 升序排序。
3.4.7 SQL内置函数和计算
SQL的五个内置函数：
COUNT（列名）：统计结果中行的数目。可作用于任何数据类型。
SUM（列名）：统计一个数值列的总和。只能作用于数值型。
AVG（列名）：平均值
MAX（列名）：最大值
MIN（列名）：最小值
FIRST（列名）：第一条记录
LAST（列名）：最后一条记录
例如：
SELECT COUNT(DISTINCT Department) AS NumOfDepartments
FROM PROJECT
WHERE ProjectId <= 120;
⚠️：除某些使用GROUP BY子句的情况外，列名不能和内置函数一起使用。
⚠️：内置函数不能用于WHERE子句。可用HAVING子句。
3.4.8 内置函数和分组
“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。例如：
SELECT Department COUNT(*) AS NumOfEmployee
FROM EMPLOYEE
GROUP BY Department
HAVING COUNT(*) > 1
上述语句先将表根据Department划分成一个个小的区域，再分别对这些小区域求COUNT，然后筛选出COUNT(*)>1的部分。
⚠️：在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。
⚠️：Having与Where的区别
where子句的作用时在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚合函数。使用where条件过滤特定的行。having子句的作用时筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组。
3.5 SQL DML——多表查询
3.5.1 使用子查询处理多个表
例如，要查找在某个任务中工作时间超过50个小时的所有雇员的姓名。雇员姓名存储在EMPLOYEE表中，但其工作时间存储在ASSIGNMENT表中：
SELECT FirstName, LastName
FROM EMPLOYEE
WHERE EmployeeNumber IN(
SELECT EmployeeNumber
FROM ASSIGNMENT
WHERE WorkHours > 50
);
3.5.2 使用 连接查询多个表
子查询只有在结果来自一个表时才有用，但如果要显示两个或多个表中的数据，子查询就无效。必须使用JOIN操作。
JOIN的基本思想是连接两个或多个表的内容，建立一个新表。例如：
SELECT FirstName, LastName, WorkHours
FROM EMPLOYEE AS E, ASSIGNMENT AS A
WHERE E.EmployeeNumber = A.EmployeeNumber;
该语句的作用是创建一个含有三列的新表。
3.5.3 SQL JOIN ON语法
上一个示例显示了旧式连接语法。新式连接语法JOIN ON：
SELECT FirstName, LastName, Workhours
FROM EMPLOYEE AS E JOIN ASSIGNMENT AS A
ON E.EmployeeNumber = A.EmployeeNumber
3.5.4 内连接和外连接
INNER JOIN：与JOIN相同。在表中存在至少一个匹配时返回行。
LEFT (OUTER) JOIN：从左表那里返回所有的行，即使在右表中没有匹配的行。
RIGHT (OUTER) JOIN：从右表那里返回所有的行，即使在左表中没有匹配的行。
FULL (OUTER) JOIN：会从左表和右表那里返回所有的行。如果左表 中的行在右表中没有匹配，或者如果右表中的行在左表中没有匹配，这些行同样会列出。
3.6 修改和删除关系的SQL语句
3.6.1 修改数据
语法：
UPDATE 表名
SET 属性1 ＝ ‘新值’, 属性2 = '新值'
WHERE 选出要修改的行
3.6.2 删除数据
语法：
DELETE
FROM 表名
WHERE 选出要删除的行
删除表1的所有行：
DELETE
FROM 表1
3.7 修改与删除表和约束的SQL语句
3.7.1 DROP TABLE
语法：
DROP TABLE 表名
删除表的结构以及所有数据。但如果该表的主键是其他表的外键，则删除无效。
3.7.2 ALTER TABLE
用于添加、修改、删除列和约束。
例如：删除外键约束：
ALTER TABLE ASSIGNMENT
DROP CONSTRAINT ASSIGN_EM_FK;
删除外键约束后就可以删除EMPLOYEE表了。
3.7.3 TRUNCATE TABLE语句
TRUNCATE TABLE EMPLOYEE;
同delete一样，删除表的数据，但不删除表结构。不能用where子句限定删除数据的条件。
3.7.4 CHECK约束
CHECK约束类似于WHERE子句，也可以包含IN、NOT IN、LIKE等进行范围检查。
例如：PROJECT表中，StartDate必须早于EndDate，但是表定义中没有任何语句实施这种约束。那么：
ALTER TABLE PROJECT
ADD CONSTRAINT Project_Check_Dates
CHECK(StartDate < EndDate);
再比如，要在PROJECT中添加一列：
ALTER TABLE PROJECT
ADD TotalHours Numeric(8,2) NULL;
将刚刚添加的列修改为NOT NULL:
ALTER TABLE PROJECT
ALTER COLUMN TotalHours Numeric(8,2) NOT NULL DEFAULT 1;
删除该列：
ALTER TABLE PROJECT
DROP COLUMN TotalHours;