04 -- 第二章 约束和高级查询

一、约束

为表创建约束是为了保证进入数据库的数据都是有效的、可靠的。

在数据库中有五种约束来保证数据的有效性和可行型：

• 主键约束（PK）；
• 唯一约束（UQ）；
• 默认约束（PQ）；
• 外键约束（FK）；
• 检查约束（CK）；

1、主键约束（PK）

• 表中常有一列或多列的组合，其值能唯一的标识表中的每一行，
• 这样的一列或多列称为主键，也称为主键约束；
• 主键约束最显著性的特征是主键列中的值是不能重复的，通过主键约束可以强制表的实体唯一性；

语法结构：

ALTER TABLE tab_name ADD CONSTRAINT pk_name PRIMARY KEY	(col_name);

注意

1. 主键约束的列不允许有重复的值；
2. 主键约束的列不允许有NULL值；

实例之为dept表和emp表增加主键

ALTER TABLE dept ADD CONSTRAINT pk_dept_deptno PRIMARY KEY (deptno);

ALTER TABLE emp ADD CONSTRAINT pk_emp_empno PRIMARY KEY (emp);

2、自增长列

• 并不是所有的表在设计完成后都能找到适合作为主键的列，为此数据库提供了自增长列；
• 自增长列是int类型的，其值是由数据库自动维护的并且是永远都不会重复的，因此自增长列是最适合作为主键的；
• 在创建表时，通过AUTO_INCREMENT关键字来标识自增长列；
• 在MySQL数据库中自增长列必须是主键。

实例之在创建表时确定主键和自增长列

CREATE TABLE tab_name (
	col1 INT AUTO_INCERMENT PRIMARY KEY,
	col2 VARCHAR(14) ,
	col3 INT 
	...
);

3、唯一约束（UQ）

• 主键约束实现了主键列中的值具有唯一性，如果对非主键列中的值也要求具有唯一性时，就需要使用唯一约束（UNIQUE）。

语法结构：

ALTER TABLE tab_name ADD CONSTRAINT UNIQUE(col_name);

注意：

• 唯一约束的列不允许有重复的值；
• 唯一约束的值允许有一个NULL值。

创建表时可同时添加主键约束和唯一约束

CREATE TABLE tab_name(
	col1 INT AUTO_INCERMENT PRIMARY KEY,
	col2 VARCHAR(14) UNIQUE,
	col3 VARCHAR(12)
	...
);

4、默认约束(DF)

• 默认约束(DEFAULT)是为列中的值设置默认值。当使用INSERT语句添加记录时，如果列没有指定值，那么该列就使用默认值，如果列已经指定了，则默认值就无效了。

语法结构：

ALTER TABLE tab_name CHANGE COLUMN old_column_name new_col_name new_datatype DEFAULT value;

5、外键约束(PK)

在关系型数据库中，表与表之间存在三种关系：

1. 一对一（1:1）：指一张表中一条记录仅与 另一张表中的一条记录相对应；
2. 一对多（1:M）：指一张表中的一条记录与另一张表中的多条记录相对应；
3. 多对多（N:M）：指一张表中的多条记录与另一种张表中的多条记录相对应。

• 如果表中的列的值来自另外一张表的主键或唯一键的列称为外键（FOREIGN KEY 简称FK）；
• 将被引用值的表称为主表或父表，将引用值的表成为从表或子表；

语法规则：

ALTER TABLE tab_name ADD CONSTRAINT FOREIGN KEY fk_name (col_name) REFERENCES re_tab_name (re_col_name);

实例之为emp表的deptno列添加外键约束：

ALTER TABLE tab_name ADD CONSTRAINT FOREIGN KEY fk_emp_deptno (deptno) REFERENCES re_dept_deptno (deptno);

注意：

• 添加记录时，先添加主表记录，再添加子表记录；
• 删除记录时，先删除子表记录，再删除主表记录；
• 主表被子表引用的键必须是主键或唯一键；

6、检查约束(CK) 在MySQL中无效

• 检查（CHECK）约束是用于检查列中的值是否符合要求。

语法规则：

ALTER TABLE tab_name ADD CONSTRAINT ck_name CHECK (col_name_condition);

二、高级查询

1、 查询所有列

语法结构：

SELECT
	 * 
FROM 
	 tab_name;

2、查询指定列：

语法结构：

SELECT
	col1_name,
	col2_name,
	col3_name
FROM
	tab_name;

3 、去掉重复列

语法结构：

SELECT DISTINCT 
	col_name 
FROM 
	tab_name;

4、查询语句中使用算数表达式

语法结构：

SELECT 
	col+num
FROM
	tab_name;

5、查询语句中使用列的别名

语法结构：

SELECT 
	col1 AS new_name1,
	col2 AS new_name2,
	...
FROM 
	tab_name;

6、条件查询where子句

语法结构：

where子句中可以使用的比较运算符包括>、 >= 、<= 、<>

SELECT 
	条件
FROM
	tab_name
WHERE
	查询条件

7、逻辑运算符查询

逻辑运算符包括and、or、not三种运算
语法结构：

SELECT 
	条件
FROM 
	tab_name
WHERE
	条件1 AND 条件2；

SELECT 
	条件
FROM 
	tab_name
WHERE
	条件1 OR 条件2；

SELECT 
	条件
FROM 
	tab_name
WHERE
	条件1 NOT 条件2；

8、BETWEEN…AND

BETWEEN 60 AND 100 相当于 >=60 AND <= 100

语法结构：

SELECT 
	条件
FROM
	tab_name
WHERE 
	BETWEEN 条件1 AND 条件2;

9、in查询

in查询没有规律的查询范围。相当于多个or的组合。

实例之： id in(1,4,7)相当于id = 1 OR id = 4 OR id=7;

SELECT 
	条件
FROM
	tab_name
WHERE
	col_name in (条件1,条件2,条件3...);

10、模糊查询like

• 模糊查询使用通配符%和_来实现。
• % 表示任意字符，
• _ 表示一个字符；

语法结构：

SELECT 
	条件
FROM
	tab_name
WHERE 
	col_name LIKE '_虎_';

SELECT 
	条件
FROM
	tab_name
WHERE 
	col_name LIKE '_虎%';

SELECT 
	条件
FROM
	tab_name
WHERE 
	col_name LIKE '&虎';

11、NULL值查询

• NULL值在数据库中表示没有数据，应使用is来判断列中是存在NULL值

语法结构：

SELECT 
	条件
FROM
	tab_name
WHERE 
	col_name IS NULL;

or

SELECT 
	条件
FROM
	tab_name
WHERE 
	col_name IS NOT NULL;

12、聚合函数查询

• 聚合就是将多个数据聚合成一个数据，聚合是通过聚合函数实现的，聚合函数有MIN()、MIN()、SUM()、AVG()、COUNT()

语法结构

SELECT 
	MAX(col_name) 最大值,
	MIN(col_name) 最小值,
	AVG(col_name) 平均值,
	SUM(col_name) 总和,
	COUNT(*) 总数 
FROM 
	tab_name;

13、分组查询GROUP BY 子句

• 分组是按照指定的列，将列中相同的值分为一组，一组用一条记录来表示。分组后可以每组中的数据进行聚合查询。

实例之

SELECT
	deptno 部门编号,
	COUNT(*) 人数
FROM
	emp
GROUP BY
	deptno;

14、分组后筛选having子句

• Having子句是对分组后，每组中的数据进行筛选。相对于where子句是在分组前进行的筛选，having子句是在分组后进行的筛选。
  实例之

SELECT
	deptno 部门编号,
	COUNT(*) 人数
FROM
	emp
GROUP BY
	deptno
HAVING
	人数 > 3;

三、多表查询

• 查询两个或者两个以上的数据表或者视图的查询称之为连接查询，这种连接查询通常建立在存在相互关系的父子表之间。
• 表的连接查询包括等值连接、自链接、内连接和外连接。

1、笛卡尔积现象

• 表查询中的而笛卡尔积现象：多行表在查询时，如果定义了无效连接或者漏写了连接条件，就会产生笛卡尔积现象；
• 笛卡尔积现象就是每个表的每一行都和其他表的每一行组合；假设两张表的总行数分别是X行和Y行，笛卡尔积就会返回X*Y行结果。

2、等值连接查询

• 通常在存在主外键关联关系的表之间进行，并将连接条件设定为有关系的列【主键-外键】；
• 使用等号 “=” 连接相关的表；
• 为避免笛卡尔积现象，n个表进行等值连接查询，最少需要n-1个等值条件来约束；

实例之查询每个部门的所有员工

SELECT
	d.dname,
	e.ename
FROM
	dept d,
	emp e
WHERE
	d.deptno = e.deptno;

3、自连接查询

• 多表查询不仅仅在多个表之间进行也可以在一个表中进行多表查询，称为自连接查询。

实例之查询当前公司员工和所属上级员工的信息

SELECT
	e1.empno 员工编号,
	e1.ename 员工姓名,
	e2.empno 领导编号,
	e2.ename 领导姓名
FROM
	emp e1,
	emp e2
WHERE
	e1.mgr = e2.empno;

4、内连接查询

• 内连接查询时多表连接查询的一种方式，这种方式使用INNER JOIN 关键字来实现，INNER可省略；

注：

1. jion子句实现内连接查询；
2. on关键字表示连接条件；
3. 内连接返回满足on条件的记录。

实例之查询每个部门的所有员工

SELECT
	d.dname,
	e.ename
FROM 
	emp e
INNER JOIN
    dept d 
ON
    e.deptno = d.deptno;

相当于

SELECT 
	d.dname,
	e.ename
FROM
	dept d,
	emp e
WHERE
	d.deptno = e.deptno;

5、外连接

• 外连接分为左外连接（left outer join）和右外连接（rigth outer join）其值outer可以省略；
• 在左外连接中join左侧的表称为左表，右侧的表称为右表；
• 左表的每一行数据去匹配右表的每一行数据，如果左表的数据与其右表的数据满足ON条件，则将相关数据返回到查询结果集中；
• 如果左表的数据与右表的数据不满足ON的条件，那么左表的数据也会将返回的查询结果集中，而右表使用NULL值填充结果集；
• 左外连接与右外连接原理相同。

实例之查询每个部门的所有员工

• 向dept表中插入一条新数据

INSERT INTO
	dept
VALUES(
	40,
	'项目部',
	'兰州'
);

• 体验右外连接

SELECT 
	d.dname,
	e.ename
FROM
	emp e
RIGHT OUTER JOIN 
	dept d
ON
	e.deptno = d.deptno;

• 体验左外连接

SELECT 
	e.ename,
	d.dname
FROM
	dept d
LEFT OUTER JOIN emp e
ON
	e.deptno = d.deptno;

6、子查询

• 在日常使用的时候，WHERE查询体条件中的限制条件不是一个确定的值，而是来自了另外一个查询结果；
• 在SQL中这种为了给主查询提供数据而首先执行的查询语句称为子查询；
• 根据返回结果的不同，可以分为单行子查询、多行子查询等等。

单行子查询

实例之查询软件部门下的所有员工

SELECT
	*
FROM
	emp e
WHERE
	e.deptno = (
		SELECT
			d.deptno
		FROM
			dept d
		WHERE
			d.dname = '软件部'
	);

多行子查询

• 如果子查询返回了多行记录，则称这样的嵌套查询为多行子查询，多行子查询就需要用到多行记录的操作符如：IN、ANY、ALL等等

• 示例一之查询公司中和员工张青相同薪水和奖金的员工

SELECT
	*
FROM
	emp e1
WHERE
	(e1.sal, e1.comm) = (
		SELECT
			e2.sal,
			e2.comm
		FROM
			emp e2
		WHERE
			e2.ename = '张青'
	);

• 示例二之统计所有员工都分布在那些部门信息：

SELECT
	*
FROM
	dept d
WHERE
	d.deptno IN (SELECT e.deptno FROM emp e);

• 示例三之查询公司中比任意一个员工的工资高的所有员工：

SELECT
	*
FROM
	emp e1
WHERE
	e1.sal > ANY (SELECT e2.sal FROM emp e2);

• 示例四之查询公司中比所有的助理的工资高i但不是助理的员工：

SELECT
	*
FROM
	emp e1
WHERE
	e1.sal > ALL (
		SELECT
			e2.sal
		FROM
			emp e2
		WHERE
			e2.job LIKE '%助理'
	);

多列子查询

• 实例之查询公司中和员工张青相同奖金和工资的员工

SELECT
	*
FROM
	emp e1
WHERE
	(e1.sal, e1.comm) = (
		SELECT
			e2.sal,
			e2.comm
		FROM
			emp e2
		WHERE
			e2.ename = '张青'
	);

7、分页查询limit子句

• 当表中的数据很多时，可以使用分页查询，以降低对数据库服务器的压力。使用limit子句可以以实现分页查询。

实例之查询员工信息

• 语法结构：

SELECT
	*
FROM
	emp
LIMIT x,y;

代码解析

• 第一个参数x表示从第几条开始查询（排序从0开始）；
• 第二个参数y表示输出几条数据。

8、综合查询示例

需求：统计2000年以后入职，部门人数超过2人的部门，按照部门人数从多到少排序输出，分页显示，每页5条

SELECT
	d.deptno AS '部门编号',
	d.dname AS '部门名称',
	COUNT(*) AS '人数'
FROM
	emp AS e
INNER JOIN dept AS d ON e.deptno = d.deptno
WHERE
	e.hirdate >= '2000-01-01 00:00:00'
GROUP BY
	e.deptno
HAVING
	人数 >= 2
ORDER BY
	人数 DESC
LIMIT 0,
 5;

代码解析：

当SQL语句中有多个子句时，子句的书写顺序和执行顺序如下：

子句	select	from	where	group	having	order	limit
书写顺序	1	2	3	4	5	6	7
执行顺序	5	1	2	3	4	6	7

执行顺序解释：

1. from：获取所有记录；
2. where：筛选掉不满足条件的记录，保留满足条件的记录；
3. group：对满足条件的记录进行筛选；
4. select：选择指定的列；
5. order by：对选出的列进行脾排序；
6. limit：取出指定行的记录。

四、单行函数

SQL函数即数据库的内置函数，可以应用在SQL语句中实现特定功能。

1、定义

• 函数用来执行数据处理和进行一些复杂运算；
• 函数的输入值叫做参数，在函数的内部对输的参数进行一些运算，最终结算得到一个结果返回给用户；
• MySQL的内置函数用于实现特定的功能，单行函数可以应用于SQL语句中。

2、字符串函数

1. 实例之大小写转换函数:UPPER、LOWER

SELECT UPPER('abcde');
SELECT LOWER('ABCDE');

代码解析：

• UPPER(str)：此函数将字符串转换成大写；
• LOWER(str)：此函数将字符串转换成小写。

2. 字符串连接函数：CONCAT

实例之连接字符串‘My’、‘SQL’

SELECT CONCAT('My','SQL');

4. 获取子字符串的函数SUBSTR

实例之获取子字符串函数

SELECT SUBSTR('SQL IS GOOD' FROM 2);

OR

SELECT SUBSTR('SQL IS GOOD' , 2);

代码解析：

• 第一个参数是要被街区的字符串；
• 第二个参数是从第几位开始截取（从1开始）；

其他字符串函数见下表：

函数	示例	结果	作用
LENGTH	SELECT LENGTH('java web sniper')	15	截取字符串长度
INSTR	SELECT INSTR('java web sniper','web')	6	截取字符串在父字符串中的索引
LTRIM	SELECT LTRIM(' java web sniper')	java web sniper	去掉左侧空格
RTRIM	SELECT RTRIM('java web sniper ')	java web sniper	去掉右侧的空格
REPLACE	SELECT REPLACE('hello web sniper' , 'web sniper', 'MySQL')	hello MySQL	替换文本

3、数学函数

函数	示例	结果	作用
ROUND(x, [y])	SELECT ROUND(5.63); / SELECT ROUND(4.36,1);	6/4.4	对指定的值x进行四舍五入操作，可以保留指定的位数y
TRUNCATE(x,y)	SELECT TRUNCATE(5.6,0);	5	对指定的值x进行截取的操作，可以指定保留的数值位数y
CEIL(x)	SELECT CEIL(4.56);	5	返回不小于指定的值x的最小整数
FLOOR(x)	SELECT FLOOR(8.5);	8	返回不大于指定的值x的最大整数
ABS(x)	SELECT ABS(-12);	12	取绝对值函数
MOD(x)	SELECT MOD(5,3);	3	取x除以y的余数函数
SIGN(x))	SELECT SIGN(-128);	-1	取x的符号函数（负数、0、正数）
POWER(x,y)	SELECT POWER(2,4);	8	取x的y次幂函数
SQRT(x)	SELECT SQRT(16);	12	取x的二次方根函数

4、日期函数

4.1、当前日期函数

函数	示例	结果	作用
CURRENT_TIMESTAMP()	SELECT CURRENT_TIMESTAMP();	2020-11-23 13:06:39	获取当前时间戳函数
CURRENT_DATE()	SELECT CURRENT_DATE();	2020-11-23	获取当前日期函数
CURRENT_TIME()	SELECT CURRENT_TIME;	13:09:19	获取当前时间函数
NOW()	SELECT NOW();	2020-11-23 13:10:25	获取当前日期+时间

4.2、日期截取函数

函数	示例	结果	作用
DAYOFWEEK(date)	SELECT DAYOFWEEK('2020-11-23');	2	返回日期date是星期几
CURRENT_DATE()	SELECT DAYOFMONTH('2020-11-23');	23	返回date是一月中的第几日
DAYOFYEAR(date)	SELECT DAYOFYEAR('2020-11-23')	328	返回date是一年中的第几日
YEAR(date)	SELECT YEAR('2020-11-23');	2020	返回date的年份
MONTH(date)	SELECT MONTH('2020-11-23');	11	返回date中的月份数值
DAY(date)	SELECT DAY('2020-11-23');	23	返回date中的日期数值
WEEK(date)	SELECT WEEK('2020-11-23');	47	返回date是一年的第几周
HOUR(time)	SELECT HOUR('10:05:03');	10	返回time的小时数
MINUTE(time)	SELECT MINUTE('10:05:03');	5	返回time的分钟数
SECOND(time)	SELECT SECOND('10:05:03');	3	返回time的秒数

4.3、 日期增加函数 DATE_ADD(date,INTERVAL expr type)

1. date是一个DATETIME或DATE值
2. INTERVAL表示时间间隔，是固定写法
3. expr对date进行加减法的一个表达式
4. type是预期的格式，预期格式见下表。

type值	预期的expr格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND	‘MINUTES.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
HOUR_MICROSECOND	‘HOURS.MICROSECONDS’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
DAY_MICROSECOND	‘DAYS.MICROSECONDS’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_HOUR	‘DAYS HOURS’
YEAR_MONTH	‘YEARS-MONTHS’