DB - MySQL进阶操作

一、高级查询语句

1. 排序查询

-- 语法
ORDER BY
	排序字段1 排序方式1，
	排序字段2 排序方式2，
	...

排序方式：
- ASC：升序，默认的
- DESC：降序

当存在多个排序条件时，只有当前面的条件值一样时，才会判断第二个条件。

2. 聚合查询

将一个字段的数据作为一个整体进行计算，常用的聚合函数有

1. COUNT：计数
2. MAX：取最大
3. MIN：取最小
4. SUM：求和
5. AVG：求平均

-- 查询name字段中有多少实例
SELECT
	COUNT(name)
FROM 
	student
;
-- 查询表中有多少存在有效记录的实例
SELECT
	COUNT(*)	
	# `*`：任意字段中只要存在非NULL值就认为是一条有效记录，正是开发中不建议使用
FROM 
	student
;
-- 查询math字段的最大值
SELECT
	MAX(math)
FROM
	student
;
-- 最小、求和、求平均
SELECT MIN(math) FROM student;
SELECT SUM(math) FROM student;
SELECT AVG(math) FROM student;

计算聚合函数会自动排除NULL值，若要不忽略值为NULL的实例，解决方式有

1. 选择不包含NULL的字段计算
2. 使用 IFNULL() 函数填充NULL值

3. 分组查询

-- 语法
GROUP BY
	分组字段

-- 按照性别分组。分别查询男、女同学的平均分
SELECT 
	sex, 
	AVG(math)
FROM
	student
GROUP BY
	sex
;

-- 按照性别分组。分别查询男、女同学的平均分,人数
SELECT 
	sex,
	AVG(math),
	COUNT(id),
FROM
	student
GROUP BY
	sex
;

--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
SELECT 
	sex,
	AVG(math),
	COUNT(id),
FROM
	student
WHERE
	math > 70
GROUP BY
	sex
;

--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT 
	sex,
	AVG(math),
	COUNT(id),
FROM
	student
WHERE 
	math > 70
GROUP BY
	sex
HAVING
	COUNT(id) > 2
;
-- 可以为COUNT(id)起一个别名，提高查询结果的可读性
SELECT 
	sex,
	AVG(math),
	COUNT(id) 人数,
FROM 
	student
WHERE
	math > 70
GROUP BY
	sex
HAVING
	人数 > 2
;

1. 分组后查询的字段只能是`分组字段`和`聚合函数计算结果`，其它结果没有意义
2. WHERE 和 HAVING 的区别?
- WHERE 在分组之前进行限定，如果不满足条件，则不参与分组；HAVING 在分组之后进行限定，如果不满足结果，则不会被查询出来
- WHERE 后不可以跟聚合函数，HAVING 可以进行聚合函数的判断。

4. 分页查询

-- 语法
LIMIT
	开始的索引, 每页查询的条数
;

公式：开始的索引 = (当前页码-1) × 每页显示条数

-- 每页显示3条记录
SELECT
	*
FROM
	student
LIMIT 
	0, 3
;	# 第1页
SELECT * FROM student LIMIT 3, 3;	# 第2页
SELECT * FROM student LIMIT 6, 3;	# 第3页

LIMIT是MySQL方言

二、约束

约束指的是对表中的数据进行限定，保证数据的正确性、有效性和完整性。主要包含以下几类：

1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foreign key

1. 主键约束：PRIMARY KEY

什么是主键

1. 主键是表中记录的唯一标识，主键字段内容不能为NULL
2. 一张表中只能由一个字段作为主键，但是可以联合多个字段组成复合主键

• 添加主键

  -- 在创建表时，添加主键约束
  CREATE TABLE stu(
  	id INT PRIMARY KEY,		-- 给id添加主键约束
  	name VARCHAR(20)
  );
  

  -- 在创建表后，添加主键约束
  CREATE TABLE stu(
  	id INT,		
  	name VARCHAR(20)
  );
  ALTER TABLE
  	stu
  MODIFY
  	id INT PRIMARY KEY	# 给id添加主键约束
  ;
  

• 删除主键

  -- 删除主键
  ALTER TABLE
  	stu
  DROP
  	PRIMARY KEY
  ;
  

• 自动增长：如果某一列是数值类型的，使用 AUTO_INCREMENT 可以来完成值得自动增长

  -- 在创建表时，添加主键约束，并且完成主键自增长
  CREATE TABLE stu(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	name VARCHAR(20)
  );
  

  -- 在创建表后，添加主键约束，并且完成主键自增长
  CREATE TABLE stu(
  	id INT,
  	name VARCHAR(20)
  );
  ALTER TABLE 
  	stu 
  MODIFY 
  	id INT AUTO_INCREMENT
  ;
  

  -- 删除自动增长
  ALTER TABLE 
  	stu 
  MODIFY 
  	id INT
  ;
  

2. 外键约束：FOREIGN KEY

外键约束：建立表与表之间的关联，从而保证多表数据协同更新的正确性

对于两张表A、B，若对A中的某个字段定义了关联B中某字段的外键，则B相对于A中的外键而言是主表，而A是外部表。

• 添加外键

  -- 在建表时添加外键
  CREATE TABLE 表名(
  	...
  	字段i,
  	...
  	# 假设要将字段i作为关联另一张表中某个字段的外键 
  	CONSTRAINT 
  		外键名 FOREIGN KEY (字段i)
  	REFERENCES
  		主表名(主表字段名)	# 通常选择主键作为关联字段
  ;
  

  -- 建立部门表
  CREATE TABLE department(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	dep_name VARCHAR(20),
  	dep_location VARCHAR(20)
  );
  -- 建立员工表
  CREATE TABLE employee(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	name VARCHAR(20),
  	age INT,
  	dep_id INT
  	# 将dep_id字段作为关联department表中id字段的外键
  	CONSTRAINT 
  		emp_dept_fk FOREIGN KEY (dep_id)
  	REFERENCES 
  		department(id)
  ;
  

  -- 在建表后添加外键
  ALTER TABLE
  	表名
  ADD CONSTRAINT 
  	外键名 FOREIGN KEY (字段i名)
  REFERENCES
  	主表名(主表字段名)
  

• 级联操作

  -- 级联更新
  ON UPDATE CASCADE
  -- 级联删除
  ON DELETE CASCADE
  

  -- 在建表时添加外键和级联操作
  CREATE TABLE 表名(
  	...
  	字段i,
  	...
  	# 假设要将字段i作为关联另一张表中某个字段的外键 
  	CONSTRAINT 
  		外键名 FOREIGN KEY (字段i)
  	REFERENCES
  		主表名(主表字段名) 	# 通常选择主键作为关联字段
  	ON UPDATE CASCADE
  	ON DELETE CASCADE
  ;
  

  -- 在建表后添加外键和级联操作
  ALTER TABLE
  	表名
  ADD CONSTRAINT
  	外键名 FOREIGN KEY (字段i)
  REFERENCES
  	主表名(主表字段名)
  ON UPDATE CASCADE
  ON DELETE CASCADE
  ;