MySQL学习笔记 03 - 约束和高级查询

一、约束

约束是在表中定义的用于维护数据库完整性的一些规则。通过为表中的列定义约束可以防止将错误的数据插入表中，也可以保持表之间数据的一致性。若某个约束条件只作用于单独的列，可以将其定义为列约束也可定义为表约束；若某个约束条件作用域多个列，则必须定义为表约束。在数据库中有五种约束来保证数据的有效性和可靠性

1. 主键约束(PK)
2. 唯一约束(UQ)
3. 默认约束(DF)
4. 外键约束(FK)
5. 检查约束(CK)（mysql中无效）

1、主键约束（PK）

表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行，这样的一列或多列称为表的主键，也称作主键约束。主键约束最显著的特征是主键列中的值是不允许重复的，通过主键约束可强制表的实体唯一性。
主键约束语法结构：

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

• 注意：

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

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

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

CREATE TABLE emp (
	empNo INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR (10),
	job VARCHAR (10),
	mgr INT,
	hirdate DATETIME,
	sal DOUBLE,
	comm DOUBLE,
	deptno INT
);

2、唯一约束（UQ）

主键约束实现了主键列中的值具有唯一性。如果对非主键列中的值也要求具有唯一性时，就需要使用唯一约束（UNIQUE）。例如：学生表中学号设置为主键，那么学生的身份证号就需要设置为唯一约束。

• 唯一约束语法结构：

 ALTER TABLE tab_name ADD CONSTRAINT UNIQUE(col_name);

• 注意：
  1.唯一约束的列不允许有重复的值
  2.唯一约束的列允许有一个NULL值

ATABLE dept ADD CONSTRAINT UNIQUE (dname);  

• 建表时也可以指定唯一约束

CREATE TABLE dept (
	deptno INT,
	dname VARCHAR (14) UNIQUE,
	loc VARCHAR (13)
);

3、默认约束（DF）

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

• 默认值语法结构：

ALTER TABLE tab_name CHANGE COLUMN old_col_name new_col_name new_datatype DEFAULT value;

实例：

ALTER TABLE emp CHANGE COLUMN comm comm DOUBLE DEFAULT 500;

4、外键约束（FK）

在关系型数据库中，表与表之间存在三种关系，分别是1对1（1:1），1对多（1:M），多对多（M:N）。

1. 1对1是指第一张表的一条记录仅与第二张表的一条记录应，例如丈夫和妻子是1对1的关系。
2. 1对多是指第一张表的一条记录与第二张表的多条记录相对应，例如一个班级有多个学生。
3. 多对多是指第一张表的多条记录与第二张表的多条记录相对应，例如超市有多种商品，有多个顾客，商品与顾客之间是多对多的关系。

• 公司数据库中录入的部门信息和员工信息存在如下关系：公司的一个部门中可能存在多个员工，公司的一个员工只能属于一个部门。也就是说公司的部门和员工是一对多的关系（一个部门有多个员工），这样的关系在数据的表中怎么体现？
  我们通过给员工表增加一个部门编号字段，来说明某个员工属于某个部门。
  emp表中的deptno列的值来自于dept表的deptno列，可以看出宋江是人事部的员工。
  表中的列也称作键，我们将表中列的值来自于另外一张表的主键或唯一键的列称为外键（foreign key 简称FK），将被引用值的表称为主表或父表，将引用值的表称为从表或子表。但是这样的关系只是开发人员自己定义的业务规则，数据库并不知道emp表的deptno列与dept表的deptno列是主外键关系。为了让数据库知道emp表与dept表的主外键关系，就必须在子表emp上创建外键约束，通过外键约束告诉数据库emp表与dept表具有主外键关系。
• 外键约束的语法规则：

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

实例：

ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY fk_dept_emp_deptno (deptno) REFERENCES dept (deptno);

设置了外键约束后，对emp表添加数据时，外键约束会验证 emp表新增记录的deptno列的值在dept表的deptno列中是否存在，若存在可以添加emp表记录，否则不允许添加emp表记录。设置了外键约束后，在删除dept表中的记录时，外键约束会验证 emp表中是否有deptno列的值与dept表中被删除行的deptno列的值相同，如果有，说明主表被删除的数据正在被子表引用，因此不允许删除。

• 在关系表中需要注意：

1. 添加记录时，先添主表记录，再添加子表记录。
2. 删除记录时，先删除子表记录，再删除主表记录。
3. 主表被子表引用的键必须是主键或唯一键。

5、检查约束（CK）（mysql中无效）

检查约束（CHECK）用于检查列中的值是否符号要求。例如考试成绩的值介于0-100之间，性别的值只能是男或女等等。

• 检查约束的语法规则：

ALTER TABLE tab_name 	ADD CONSTRAINT ck_name CHECK (col_name-condition);

• 实例：

ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal >= 0);

二、高级查询

1、查询所有列

• 语法结构：

SELECT * FROM tab_name;

• 实例：

SELECT
	*
FROM
	dept;

2、查询指定列

• 语法结构：

SELECT col_name1 , col_name2, ... FROM tab_name;

• 实例：

SELECT
	ename,
	sal,
	comm
FROM
	emp;

3、去掉重复值

• 语法结构：

SELECT DISTINCT col_name FROM tab_name;

• 实例：

SELECT DISTINCT
	sal
FROM
	emp;

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

SELECT
	ename,
	comm + 500
FROM
	emp;

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

• 语法结构：

SELECT col_name AS alias_name, col_name2 alias_name2... FROM tab_name;

• 实例：
  SELECT

ename AS '姓名',
	comm + 500 '预期奖金'
FROM
	emp;

6、条件查询where子句

6.1、比较运算符

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

SELECT
	*
FROM
	emp
WHERE
	sal < 3000;

6.2、逻辑运算符

• 逻辑运算包含 and 、or 、not三种运算。

SELECT
	*
FROM
	emp
WHERE
	sal > 4000
AND comm > 4000;

7、BETWEEN…AND

• Between and表示在两者之间，例如between 60 and 100相当于 >=60 and <=100。
• 实例：

SELECT
	*
FROM
	emp
WHERE
	comm BETWEEN 1000
AND 5000;

8、in查询

• in 用于没有规律的范围查询。相当于多个or的组合。例如 id in(1,4,7)相当于id=1 or id=4 or id=7。

SELECT
	*
FROM
	emp
WHERE
	job IN (
		'项目经理',
		'项目组长'
	);

9、模糊查询like

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

SELECT
	*
FROM
	emp
WHERE
	ename LIKE '卢_';

SELECT
	*
FROM
	emp
WHERE
	ename LIKE '卢%';

10、NULL值查询

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

SELECT
	*
FROM
	emp
WHERE
	mgr IS NULL;		//判断是否为空

SELECT
	*
FROM
	emp
WHERE
	mgr IS NOT NULL;	//判断不为空

11、排序order by子句

• Order by 子句用于排序，升序需指定ASC，降序需指定DESC，默认是ASC。

SELECT
	*
FROM
	emp
ORDER BY
	sal DESC,
	comm ASC;

12、聚合函数查询

• 聚合就是将多个数据聚合成一个数据，聚合是通过聚合函数实现的，聚合函数有sum、max、min、avg、count共五个。

SELECT
	MAX(sal) 最高工资,
	MIN(sal) 最低工资,
	AVG(sal) 平均工资,
	SUM(sal) 总工资,
	COUNT(*) 公司总人数
FROM
	emp;

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;