2.1 SQL语言介绍

SQL（Structured Query Language）语言是一种在关系数据库中定义和操纵数据的标准语言，是用户与数据库之间进行交流的接口。

2.1.1 SQL数据库的体系结构

SQL语言支持关系数据库的三级模式、二级映像的结构，二级映像保证了数据库的数据独立性；

SQL的关系数据库具有的特点：

SQL用户可以是应用程序，也可以是终端用户。（嵌入应用程序中，也可以直接访问）
SQL用户可以用SQL语言对基本表和视图进行查询。
一个视图是从若干基本表或其他视图上导出的表。在数据库中只存放该视图的定义，不存放该视图所对应的数据。数据都是来自于基本表。视图是一个虚表；
一个或一些基本表对应一个数据文件
- 一个基本表也可以放在若干数据文件中。
- 一个数据文件对应存储设备上的一个存储文件；
一个基本表可以带若干索引。索引也放在数据文件中。
一个表空间可以由若干数据文件组成；
一个数据库可以由多个存储文件组成；

2.1.2 SQL的特点

SQL语言是一个综合的、通用的、功能极强的同时简洁易学的语言；集数据定义、数据查询、数据操纵和数据控制于一体；

综合统一：风格统一，可以完成数据生命周期中的全部活动。
高度非过程化：指示做什么，而与去做的过程无关；
面向集合的操作方式：一次操作得到若干记录；
同一种语法结构提供两种使用方式：SQL既可以是独立式语言，又是嵌入式语言；
语言简洁，易学易用

Sql功能	动词
数据定义	CREATE、DROP、ALTER
数据操纵	INSERT、DELETE、UPDATE、SELECT
数据控制	COMMIT、ROLLBACK、GRANT、REVOKE

2.1.3 SQL语言的组成

1. 数据定义语言（DDL）

创建、修改、删除和重命名数据库（CREATE、DROP、ALTER、RENAME）；还有删除表中所有行，但不删除表（TRUNCATE）

2. 数据操作语言（DML）

增删改查现有数据行

3. 数据控制语言（DCL）

用于事务控制，并发控制，完整性和安全性控制等；

2.2 数据定义

SQL的数据定义包括数据库定义、表的定义、视图和索引的定义；

2.2.1 数据库的定义和删除

1. 创建数据库

1	CREATE DATABASE 数据库名

2. 选则数据库

USE 数据库名

3. 删除数据库

1	DROP DATABASE 数据库名

2.2.2 数据类型

Mysql支持的数据库类型主要分为4类，即字符串类型、数值类型、日期和时间类型，布尔类型。

1. 字符串类型

常用字符类型式CHAR, VARCHAR类型

CHAR：描述定长的字符串，说明格式的语法为：CHAR(L)；L表示字符串最大长度，取值范围为 1 ~ 255；比L大截断，小了补空格填补；
VARCHAR：描述变长的字符串，说明格式为：VARCHAR(L)；L表示字符串长度，取值范围为 1 ~ 255；比L大截断，小了不会用空格填补，按实际长度存储；

字符串要用单引号或双引号括起来；

2. 数值类型

常用数值类型是INT,DECIMAL类型

INT：表示整数，存储长度默认为4个字节。说明格式为INT;
DECIMAL：用来表示所有的数值数据，说明格式为DECIMAL(p,s)，p表示数值数据的最大长度， s表示数值数据中小数点后的数组位数，p，s在定义时可以省略；

3. 日期和时间类型

常用的日期和时间类型时DATA，TIME，DATETIME类型

DATE：用来保存固定长度的日期数据，说明格式为DATE; 日期值格式为YYYY-MM-DD;
TIME：保存固定长度的时间数据，说明格式为TIME；时间值格式为HH:MM:SS;
DATETIME：保存固定长度的日期时间数据，说明格式为DATETIME；日期时间值格式为YYYY-MM-DD HH:MM:SS；

4. 布尔类型

布尔类型BOOLEAN类型只有两个值——TRUE和FALSE，即真值和假值；

2.2.3 基本表的定义、删除和修改

表是数据库存储数据的基本单元；

表中存储数据的逻辑结构是一张二维表，即表由行、列两部分组成。

称表中的一行为一条记录；

称表中的一列为一个属性；

1. 创建表

graph TD
A[创建表] -->B(定义表的结构)
B -->C[表名]
B -->D[各个列]
D -->E[列名]
D -->F[列的数据类型]
D -->H[列上的约束]
B -->G[表上的约束]

CREATE TABLE 表名
(
	<列明> <数据类型> [DEFAULT<默认值>]
	[,···]
)；

TIP：

使用 DESC 显示表的结构
设置了自增类型的字段，需将其设置为主键，否则创建失败。
表明日期时间可用当下系统时间补充，用CURRENT_TIMESTAMP;

2. 利用子查询来创建表

1 2	CREATE TABLE <表名> SELECT 语法；

例如：

1 2	CREATE TABLE dept_c SELECT * FROM dept；

3. 修改表的结构

对表的修改包括：增加新的列，删除原有的列或修改列的数据类型，宽度等；

1）增加一个新列

1 2	ALTER TABLE <表名> ADD [COLUMN] <列名> <数据类型> [DEFAULT <默认值>];

一次只能增加一个新列

2）修改一个表中已有的列

1 2	ALTER TABLE <表名> MOIFY [COLUMN] <列名> <数据类型> [DEFAULT <默认值>];

3）从一个表中删除一列

1 2	ALTER TABLE <表名> DROP [COLUMN] <列名>；

4. 截断表和删除表

1）截断表：删除数据保留表结构

1	TRUNCATE TABLE <表名>；

2）删除表：删除数据和表结构

1	DROP TABLE <表名>；

2.3 数据查询

SELECT语句的基本语法如下：

SELECT * | <列名 | 列表达式>[,<列名 | 列表达式>]···
FROM <表名或视图名>[,<表名或视图名>]···
[ WHERE <条件表达式> ]
[ GROUP BY <分组列名1>[,<分组列名2>]]···
	[ HAVING <组条件表达式> ]
[ ORDER BY <排序列名1 [ ASC| DESC ]>[,<排序列名2 [ ASC| DESC ]>]···];

[]表示该部分可选；<>表示该部分必有。

执行过程如下

读取FROM子句中的表、视图的数据，如果是多个表或视图，执行笛卡尔积操作；
选择满足WHERE子句中给出的条件表达式的记录；
按GROUP BY子句中指定列的值对记录进行分组，同时提取满足HAVING子句中组条件表达式的那些组；
按SELECT子句中给出的列名或列表达式求值输出；
ORDER BY子句对输出的记录进行排序；

2.3.1 基本查询

1. SELECT子句的规定

SELECT子句用于描述输出值的列明或表达式；

1	SELECT [ ALL \| DISTINCT ] * \| <列名或列名表达式>

DISTINCT：表示输出无重复结果的记录； ALL：选项是默认的表示输出所有记录，包括重复记录

1）查询所有列

1	SELECT * FROM dept;

2）查询指定的列

1	SELECT deptno, dname FROM dept;

3）去掉重复的行

1	SELECT deptno, job FROM emp;

上面的这个语句会把所有的输出出来，有重复的行值出现，需要去掉重复的记录，则可以用下面的语句：

1	SELECT DISTINCT deptno, job FROM emp;

2. 为列起别名的操作

1	原字段名 [AS] 列别名

3. 使用WHERE子句指定查询条件

WHERE子句后的行表达式可以由运算符组合而成，常用的比较运算符：

运算符名称	符号及格式	说明
算术比较判断	`<、<=、>、>=、<>、!=、=`	比较两个表达式的值
逻辑比较判断	`NOT、AND、OR`	非与或操作（优先级高低排序）
之间判断	`<表达式>[ NOT ] BETWEEN <值1> AND <值2>`	搜索（不）在指定范围里的值
字符串模糊判$断^1$	`<字符串> [ NOT ] LIKE <匹配模式>`	查找（不）在给定模式的值
空值判断	`<表达式> IS [ NOT ] NULL`	判断是否为空值
之内判断	`<表达式> [ NOT ] IN (<集合>)`	判断表示式的值是否在集合内

模糊判断匹配字符串模式使用通配符 % 和 _ 。%用于表示0个或任意多个字符，_表示任意一个字符；

4. 使用ORDER BY子句对查询结果排序

使用ORDER BY子句对查询结果进行排序时要注意一下两点：

（1）当SELECT语句中包含多个子句时，ORDER BY 必须是最后一个；

（2）可以使用列的别名、列的位置（SELECT子句中的顺序）进行排序；

2.3.2 分组查询

数据分组市容过在SELECT语句中加入GROUP BY子句完成的。通常用聚合函数对每个组的数据进行汇总、统计；用 HAVING 子句来限定查询结果集中只显示分组后的、其聚合函数的值满足指定条件的那些组；

1. 聚合函数

函数	说明
`COUNT(*)` `COUNT(<列名>)`	记录的个数；对一列中的值计算个数
`SUM(<列名>)`	求某一列的总和
`AVG(<列名>)`	求某一列的平均值
`MAX(<列名>)`	求某一列的最大值
`MIN()`	求某一列的最小值

使用聚合函数要注意的点：

（1）聚合函数只能出现在所查询的列、ORDER BY子句、HAVING子句中，不能出现在WHERE子句、GROUP BY子句中。

（2）除了COUNT(*)之外，其他聚合函数都忽略对列值为NULL的统计；

2. 使用GROUP BY子句

1）按单列分组

【例】查询emp表中每个部门的平均工资和最高工资，按部门编号升序排列；

1
2
3

SELECT deptno, AVG(sal) 平均工资，MAX(sal) 最高工资 FROM emp
	GROUP BY deptno
	ORDER BY deptno；

2）按多列分组

【例】查询emp表中每个部门、每种岗位的平均工资和最高工资；

1
2
3

SELECT deptno, AVG(sal) 平均工资，MAX(sal) 最高工资 FROM emp
	GROUP BY deptno, job
	ORDER BY deptno；

3. 使用HAVING子句

【例】查询部门编号在30以下的各个部门的部门编号、平均工资，要求只显示平均工资大于等于2000的信息；

SELECT deptno, AVG(sal) 平均工资 FROM emp
	WHERE deptno<30
	GROUP BY deptno
	HAVING AVG(sal)>=2000;

2.3.3 连接查询

连接查询是指对两个或两个以上的表或视图的查询。连接查询是关系数据库中最主要、最有意义的查询，是关系数据库的一项核心功能；

MySql提供了4种类型的连接，即相等连接、自身连接、不等连接和外连接；

1. 相等连接

也称为简单连接或内连接，是把两个表中指定列的值相等的行连接起来；

【例】查询工资大于等于3000的员工的信息

SELECT empno, ename, sal, e.deptno, loc
	FROM emp e, dept d
	WHERE e.deptno = d.deptno
	AND sal >= 3000
	ORDER BY e.deptno;

可用ON子句来实现内连接

SELECT empno, ename, sal, e.deptno, loc
	FROM emp e INNER JOIN dept d
	ON e.deptno = d.deptno
	WHERE sal >= 3000
	ORDER BY e.deptno;

2. 自身连接

是通过把一个表定义为两个不同别名的方法来完成自身连接的；

【例】一个雇员表，含有雇员编号，管理员编号。管理员也是雇员；所以这两列有参照关系；

SELECT e.ename 雇员, m.ename 管理员
	FROM emp e, emp m
	WHERE m.empno = e.mgr
	AND e.deptno = 20;

3. 不等连接

用其他运算符产生的连接叫做不等连接；

4. 左外连接

左外连接的格式如下：

1	FROM 表 1 LEFT OUTER JOIN 表 2 ON 表1.列 = 表2.列;

左外连接的结果是显示表1中的所有记录和表2中与表1.列相同的记录；

5. 右外连接

右外连接的格式如下：

1	FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.列 = 表2.列;

左外连接的结果是显示表2中的所有记录和表1中与表2.列相同的记录；

2.3.4 子查询

子查询是指嵌入在其他SQL语句中的一个查询。最多可以嵌套255层；

使用子查询可以用一系列简单的查询构成复杂的查询，从而增强SQL语句的功能；

执行步骤

（1）首先取外层查询中表的第一个记录，根据它与内存查询相关列值进行内层查询的处理，若处理结果为真，则取此记录放入结果集。

（2）然后取外层表的下一个记录进行内层查询的处理

（3）重复这一过程，直到外层查询中表的全部记录处理完为止；

1. 返回单值的子查询

单值子查询向外层查询值返回一个值；

2. 返回多值的子查询

多值子查询可以向外层查询返回多个值；在WHERE子句中使用多值子查询时必须使用多值比较运算符，例如 [not] IN，[not] EXISTS，ANY，ALL，其中ANY，ALL必须与比较运算符结合使用；

1）使用 IN 操作符的多值子查询

比较运算符IN的含义为子查询返回列表中的任何一个。

IN操作符比较子查询返回列表中的每一个值，并且显示任何相等的数据行；

【例】查询所在岗位工资最高的员工信息，不包括CLERK和PRESIDENT；

1
2
3

SELECT empno, ename, job, sal FROM emp
	WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY job)
	AND job<>'CLERK' AND job<>'PRESIDENT';

注：这个例子的答案是错误的；根据薪资不能成立某岗薪资最高员工的参照关系；

大约应该写成这样才是正确的，不过这已经失去了展示 IN 操作符的意义了；

1
2
3

SELECT empno, ename, job, sal FROM emp, (SELECT job, MAX(sal) as MAXsal FROM emp GROUP BY job) as M
	WHERE job = M.job AND sal = M.Maxsal
	AND job<>'CLERK' AND job<>'PRESIDENT';

2）使用ALL操作符的多值子查询

ALL操作符比较子查询返回列表中的每一个值。 < ALL 为小于最小的， > ALL 为大于最大的；

【例】查询高于部门20的所有雇员工资的雇员信息

1 2	SELECT ename, sal, job FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 20);

3）使用ANY操作符的多值子查询

ANY操作符比较子查询返回列表中的每一个值。 < ALL 为小于最大的， > ALL 为大于最小的；

【例】查询高于部门20的任何雇员工资的信息

1 2	SELECT ename, sal, job FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 10);

4）使用EXISTS操作符的多行查询

EXISTS操作符比较子查询返回列表中的每一个行。

使用 EXISTS 时应注意外层查询的 WHERE 子句格式为 WHERE EXISTS；在内层子查询必须有WHERE 子句；

2.3.5 合并查询结果

当两个 SELECT 查询结果的结构完全一致时，可以对这两个查询执行合并运算，运算符为 UNION；

UNION 的语法格式如下：

SELECT 语句1
	UNION [ALL]
SELECT 语句2
	ORDER BY XXX;

UNION 在连接数据表的查询结果时，结果会删除重复的行，返回的行都是唯一的；
在使用 UNION ALL 的时候，结果不会删除重复行；
对合并查询结果进行排序，要排序的列名一定是来自第一个表中的列名（即使是别名）；

2.4 数据的维护

数据维护是指用INSERT, DELETE，UPDATE语句来插入、删除、更新数据库表中记录行的数据，由DML语言实现；

2.4.1 插入数据

1. INSERT语句

1
2
3

INSERT INTO 表名 [ ( 列名1[, 列名2···]) ]
	VALUES (值1[,值2···])
	[,(值1[,值2···]),···,(值1[,值2···])]

说明：

（1）插入数据时，列的个数、数据类型、顺序必须要和所提供数据的个数、数据类型，顺序保持一致或匹配；

（2）如果省略了表名后面列的列名表，即表示要为所有列插入数据，则必须根据表结构定义中的顺序为所有列提供数据，否则会出错；

【例】复制另一个表

1 2	CREATE TABLE dept_c SELECT * FROM dept;

【例】插入数据示例

1 2	INSERT INTO dept_c(deptno, dname, loc) VALUES(50, 'PERSONNEL', 'HONGKONG');

1 2	INSERT INTO dept_c VALUES(50, 'PERSONNEL', 'HONGKONG');

2. 利用子查询向表中插入数据

1 2	INSERT INTO 表名[ ( 列名1 [ ,列名2··· ] ) ] SELECT 语句;

2.4.2 更新数据

1. UPDATE语句

1
2
3

UPDATE 表名
	SET 列名 = 值 [,列名=值,··· ]
	[ where <条件> ];

说明：如果不用WHERE子句先定要更新的数据行，则会更新整个表的数据行；

注意：MySql运行在 SAFE_UPDATES 模式下，该模式会导致在非主键条件下无法执行 UPDATE 或 DELETE 命令，需要执行 SET SAFE_UPDATES = 0 修改数据库模式；

【例】更新 dept_c 表中部门10的地址为 CHINA

SET SAFE_UPDATES = 0
UPDATE dept_c
	SET loc = 'CHINA'
	WHERE deptno = 10;

【例】将 dept_c 表中所有部门的地址改为 CHICAGO

1 2	UPDATE dept_c SET loc = 'CHICAGO';

2. 利用子查询修改记录

【例】更新部门40的部门名称

1
2
3

UPDATE dept_c
	SET dname = (SELECT dname FROM dept WHERE deptno=40)
	WHERE deptno = 40;

2.4.3 删除数据

1. DELETE语句

1 2	DELETE FROM 表名 [ WHERE <条件> ];

说明：

（1）DELETE 是按行删除数据，不是删除行中某些列的数据；

（2）如果不用WHERE子句限定要删除的数据行，则会删除整个表的数据行。

（也可以用截断表的语句实现，格式为 TRUNCATE TABLE 表名 )

2. 利用子查询删除行

2.5 索引和视图

索引可以帮助用户提高查询数据的效率，类似于书中的目录。

视图是一张虚拟表，是基于一个或几个数据表生成的逻辑表；

2.5.1 索引的创建与删除

1. 创建索引

创建索引有两种办法：

（1）系统自动建立：当用户在一个表上建立主键（PRIMARY KEY）或唯一（UNIQUE）约束时，系统会自动创建唯一索引（UNIQUE INDEX）；

（2）手工建立：用户在一个表中的一列或多列上用CREATE INDEX语句来创建非唯一索引（NONUNIQUE INDEX）。

创建索引的语句格式

1	CREATE [ UNIQUE ] INDEX 索引名 ON 表名(列名[ ,列名 ]···);

【例】为 emp_c 表按员工的名字建立索引，索引名为emp_ename_idx;

1 2	CREATE INDEX emp_ename_idx ON emp_c(ename);

【例】为 emp_c 表按工作和工资建立索引，索引名为emp_job_sal_idx

1 2	CREATE INDEX emp_job_sal_idx ON emp_c(job,sal);

索引名的命名一般采用 表名_列名_idx 方式；

2. 查看索引

查看索引的语法：

1	SHOW INDEX FROM <表名>;

【例】查看emp_c的索引信息

1	SHOW INDEX FROM emp_c;

3. 删除索引

当一个索引不再需要时，应该删除它；

删除索引的语句格式

1	DROP INDEX 索引名 ON 表名

4. 使用索引时应注意的问题

一条DML只要涉及到索引关键字，DBMS就得调整索引；

索引是需要消耗磁盘空间的；如果表很大，其索引消耗磁盘空间的量也会很大。

满足以下条件之一，就可以为该列建立索引

（1）表上的 INSERT , DELETE， UPDATE操作较少；

（2）一列或多列经常出现在WHERE子句或连接条件中；

（3）一列或多列经常出现在 GROUP BY 或 ORDER BY 操作中；

（4）如果表很大，但大多数查询返回的数据量很少。因为如果返回数据量很大，就不如顺序地扫描这个表了；

（5）刺裂的取值范围很广，一般为随机分布；

（6）表中包含了大量的NULL值；

2.5.2 视图

视图（View）是由 SELECT 子查询语句定义的一个逻辑表；只有定义没有数据，是一个“虚表”；

视图的使用和管理是可以被创建、更改和删除；

视图是查看和操作表中数据的一种方法。除了 SELECT 之外，视图在 INSERT、 UPDATE、DELETE方面受到某些限制；

1. 为什么建立视图

1）提供各种数据表现形式，隐藏数据的逻辑复杂性并简化查询语句；

可以使用各种不同的方式将基础表的数据展现在数据面前，以便符合用户的使用习惯；（比如提供多表查询视图。让用户在视图上操作，隐藏了多表数据的复杂逻辑，简化了用户查询语句）

2）提供某些安全性保证，简化用户权限管理

视图可以实现让不同的用户看见不同的列，从而保证某些敏感数据不被某些用户看到；可以面向视图的对象权限授予用户，简化用户的权限定义；

3）对重构数据库提供了一定的逻辑独立性

视图是数据库三级模式中外模式在具体 DBMS 中的体现；概念模式发生改变，外模式具有逻辑独立性；

2. 创建视图

通过 CREATE VIEW 语句创建视图，创建视图的语句格式如下：

CREATE [OR REPLACE] VIEW 视图名[ (别名 [ ,别名 ] ) ]
	AS
SELECT 语句
	[WITH CHECK OPTION];

说明：

（1）OR REPLACE : 创建的视图已存在，MySQL系统会重建这个视图。

（2）别名：为视图所产生的列定义的列名

（3）WITH CHECK OPTION ：所插入或修改的数据行必须满足视图所定义的约束条件；

（4）在子查询语句中不能包含 ORDER BY 子句

【例】创建带有 WITH CHECK OPTION 选项的视图

CREATE VIEW v_dept
  AS
  SELECT empno, ename, job, deptno FROM emp
  	WHERE deptno = 10
  WITH CHECK OPTION;

这个条件的限制了只能插入deptno为10的；

3. 修改视图

MySQL中可以通过 CREATE OR REPLACE VIEW 语句和 ALTER语句来修改视图；

CREATE OR REPLACE VIEW 是用创建视图的语句将原来的视图覆盖掉；

【例】修改上一例中的视图。取消约束条件检查

CREATE OR REPLACE VIEW v_dept
  AS
  SELECT empno, ename, job, deptno FROM emp
  	WHERE deptno = 10;

使用 ALTER 语句修改视图，语句格式如下：

ALTER VIEW 视图名[ (别名 [ ,别名 ]···) ] 
	AS
	SELECT 语句
	[WITH CHECK OPTION];

【例】修改上上一例中的视图。取消约束条件检查

ALTER VIEW v_dept_chk
	AS
	SELECT empno, ename, job, deptno FROM emp
  	WHERE deptno = 10;

4. 删除视图

使用 DROP VIEW 语句删除视图，删除视图对创建该视图的基础表或视图没有任何影响。其语句格式如下：

1	DROP VIEW 视图名 [, 视图,···]

5. 使用视图进行DML操作

用户通过视图对基本表中的数据进行 DML 的UPDATE、 INSERT、 DELETE 操作。

视图分为 简单视图 和 复杂视图 它们的区别如下：

1）简单视图

（1）数据是仅从一个表中提取的

（2）不包含函数和分组数据

（3）可以通过该视图进行DML操作

2）复杂视图

（1）数据是从多个表中提取的

（2）包含函数和分组数据

（3）不一定能够通过该视图进行DML操作

下面给出通过视图进行 DML 操作的规则

（1）可以在简单视图上执行 DML 操作

（2）如果在一个视图中包含了分组函数，GROUP BY子句，或 DISTINCT关键字，则不能通过该视图进行增删改操作；

（3）如果在一个视图中包含了由表达式组成的列，则不能通过该视图进行 UPDATE、INSERT操作

（4）如果在一个视图中没有包含引用表中那些不能为空的列，则不能通过该视图进行 INSERT 操作