MySql | 中级知识 | 学习笔记

约束

约束是作用在表中字段的规则，目的保证数据库中数据的正确、有效性和完整性。
约束是为了保证进入数据库的数据都是有效的、可靠的，会对列的值进行一些约束，
确保存进去的数据都是有效的

利用SHOW CREATE TABLE 表名;可以查看当前表的建表语句，以及约束等等

分类

约束	描述	关键字
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
非空约束	限制该字段的数据不能为 null	NOT NULL
检查约束（8.0.16版本之后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

主键约束

主键约束最显著的特征是主键列的值是唯一的，在创建或更改表的时候可以通过primary key来创建主键，一个表中只能有一个主键primary key

1. 创建主键

在创建表的时候创建主键

-- 写法一：
CREATE TABLE 表名 (
	id int PRIMARY KEY, -- 在需要定义主键的列名后面加上 PRIMARY KEY 关键字
	name varchar(10)
);

-- 写法二：
CREATE TABLE 表名 (
	id int,
	name varchar(10),
	PRIMARY KEY (id) -- 设置主键
);

-- 写法三：
CREATE TABLE 表名 (
	id int,
	name varchar(10),
	constraint 约束名 PRIMARY KEY (id)
);

在创建表之后再添加主键

-- 写法一
ALTER TABLE 表名 ADD [CONSTRAINT] PRIMARY KEY (列名);

-- 写法二
ALTER TABLE 表名 MODIFY [COLOMN] 列名 列类型 PRIMARY KEY;

-- 写法三
ALTER TABLE 表名 CHANGE 列名 列名 列类型 PRIMARY KEY;

2. 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;  -- mysql 8.0.22 之后的版本

注意：如果该主键有其相关联的外键，或者设置了自增，则该主键无法直接删除

自增长列（标识列）

自增长列是指在插入新记录时，数据库系统会自动为该列生成一个唯一的、递增的值的列。在MySQL中，可以使用AUTO_INCREMENT属性来实现自增长列。通常情况下在 MySql 中自增长列设置为主键列。

1. 设置自增长列

创建表时设置

CREATE TABLE 表名 (
	id int PRIMARY KEY AUTO_INCREMENT,
	name varchar(10)
);

在建表之后设置

-- 写法一
ALTER TABLE 表名 MODIFY [COLOMN] 列名 列类型 AUTH_INCREMENT;

-- 写法二
ALTER TABLE 表名 CHANGE 列名 列名 列类型 PRIMARY KEY;

2. 删除自增长列

ALTER TABLE 表名 MODIFY [COLOMN] 列名 列类型;

3. 特点
   1. 数据类型选择：自增长列通常与数值类型的列一起使用，如INT或BIGINT。根据数据量的大小和需求选择适合的数据类型，并确保它们可以容纳您预期的记录数量。
   2. 主键唯一性：自增长列通常用作主键列，用于唯一标识每个记录。确保自增长列的值在表中是唯一的，这可以通过将其定义为主键或添加唯一约束来实现。
   3. 删除操作：删除记录后，自增长列的值通常不会回退。下一个插入的记录仍然会使用比已删除记录中最大的自增长值更大的值。这是为了保持自增长列的唯一和递增性质。
   4. 一个表中通常只有一个自增长列
   5. 自增长列可以利用SET AUTO_INCREMENT_INCREMENT = 值来设置递增的步长（该设置为全局设置，只要执行一次，后期的所有添加了自增长的表中在添加数据时都按照值来进行递增，该设置在重新登录一次数据库后会重置为1）

联合主键

联合主键是由两个或多个列组成的主键，用于唯一标识表中的每个记录。联合主键要求每个组合的值都是唯一的，即整个联合主键的值在表中是唯一的。
使用联合主键可以解决需要多个列来唯一标识记录的情况。例如，在一个存储订单信息的表中，可能需要同时使用 订单号 和 产品ID 来唯一标识每个订单记录。在这种情况下，可以将订单号和产品ID两列定义为联合主键。

1. 设置联合主键

在建表时设置联合主键

CREATE TABLE 表名 (
	列名1 列类型1,
	列名2 列类型2,
	列名3 列类型3,
	PRIMARY KEY(列名1, 列名2)
);

建表之后设置联合主键

ALTER TABLE 表名 ADD [CONSTRAINT] PRIMARY KEY (列名1, 列名2);

2. 删除联合主键

ALTER TABLE 表名 DROP PRIMARY KEY

外键约束

外键约束可以用来建立表与表之间的关联，通常用于实现关系型数据库中的数据关系。外键列一般称为FK，下图中带外键的表为从表，带主键的表为主表。

1. 设置外键约束

在创建表时设置唯一约束

CREATE TABLE 表名 (
	id int,
	name varchar(20),
	CONSTRAINT 约束名 FOREIGN KEY (name) REFERENCES 主键表名(主键列名)
);

创建表之后设置唯一约束

ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY (从表列名) 
REFERENCES 主表表名(主键列名);

2. 删除外键约束

ALTER TABLE 表名 DROP COLUMN 外键列名 FOREIGN KEY 索引名; -- 设置外键的索引名称

唯一约束

对于非主键列中的值也要求唯一性时，就需要唯一约束

1. 设置唯一约束

在创建表时设置唯一约束

-- 写法一
CREATE TABLE 表名 (
	id int PRIMARY KEY,
	name varchar(10) UNIQUE -- 这种方式创建的唯一约束的约束名就是列名
);

-- 写法二
CREATE TABLE 表名 (
	id int PRIMARY KEY,
	name varchar(10),
	CONSTRAINT 约束名 UNIQUE (name)
);

创建表之后设置唯一约束

-- 写法一
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (name);

-- 写法二
ALTER TABLE 表名 MODIFY [COLOMN] 列名 列类型 AUTH_INCREMENT;

-- 写法三
ALTER TABLE 表名 CHANGE 列名 列名 列类型 PRIMARY KEY;

2. 删除唯一约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名 --（设置唯一时的约束名）

注意：唯一约束可以存在**NULL**

默认约束

给表中列名设置默认值DEFAULT 值，一般在创建表的时候，MySql会给表中字段自动添加默认为空的约束DEFAULT NULL

1. 设置默认值

建表时设置默认值

-- 写法一
CREATE TABLE 表名 (
	id int,
	name varchar(10) DEFAULT '值'	
);

建表之后设置默认值

-- 写法一
ALTER TABLE 表名 MODIFY [COLOMN] 列名 列类型 DEFAULT 值;

-- 写法二
ALTER TABLE 表名 CHANGE 列名 列名 列类型 DEFAULT 值;

2. 删除默认值

-- 写法一
ALTER TABLE 表名 MODIFY [COLOMN] 列名 列类型;

-- 写法二
ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT; -- 8.0.23以上的版本

-- 写法三
ALTER TABLE 表名 ALTER COLUMN 表名 SET DEFAULT NULL; -- 8.0.23以下的版本

非空约束

NOT NULL非空约束，如果给该表中列名设置了非空约束则说明，该列下的数据不能为空

1. 设置非空约束

创建表时定义非空约束

CREATE TABLE 表名 (
	id int NOT NULL,
	name varchar(10) NOT NULL
);

建表后定义非空约束

-- 写法一
ALTER TABLE 表名 MODIFY [COLOMN] 列名 列类型 NOT NULL;

-- 写法二
ALTER TABLE 表名 CHANGE 列名 列名 列类型 PRIMARY KEYNOT NULL;

2. 删除非空约束

ALTER TABLE 表名 MODIFY [COLOMN] 列名 列类型;

检查约束

从MySQL 8.0.19版本开始， MySQL 支持了检查约束。检查约束允许你在表定义中声明条件，并确保符合该条件的数据才能插入或更新到相应的列中。

1. 设置检查约束

创建表时定义非空约束

CREATE TABLE 表名 (
	id int,
	name varchar(10),
	age int,
	CHECK (age >= 18) -- 添加检查约束，该约束名为字段名
);

建表后定义非空约束

-- 写法一
ALTER TABLE 表名 ADD CONSTRAINT 列名 CHECK (条件);

-- 写法二
ALTER TABLE 表名 MODIFY 列名 列类型 CHECK (条件);

-- 写法三
ALTER TABLE 表名 CHANGE 列名 列名 列类型 CHECK (条件);

2. 删除检查约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

注意：当约束名为字段名的时候删除检查约束将会删除表中字段

聚合函数

聚合函数是将多个函数聚合成一个函数，聚合是通过聚合函数进行实现的，聚合函数一共有五个SUM、MAX、MIN、AVG、COUNT

SELECT 
SUM(字段名), -- 求该字段中所有数据的和
MAX(字段名), -- 求最大值
MIN(字段名), -- 求最小值
AVG(字段名), -- 求平均值
COUNT(字段名) -- 求总数
FROM 表名;

分组查询

GROUP BY 字句

分组是按照指定的列，将所有相同的数据分成一组，每一组用一条记录来表示，分组完后可以利用聚合函数进行查询

SELECT 聚合函数(列名), 列名1 FROM 表名 GROUP BY 列名1;

注意：在分组查询的时候，查询字段只能是分组列名或者是聚合函数

HAVING 字句

HAVING是对分组后的结果进行筛选，HAVING后面可以跟聚合函数

SELECT 聚合函数(列名), 列名1 FROM 表名 GROUP BY 列名1、聚合函数(列名);

数据库连接

数据库连接是指通过某种关联条件，将两个或多个数据库表中的数据连接在一起，以获取所需的数据结果。不同类型的连接（自连接、内连接和外连接）在如何匹配和获取数据上有所不同。

自连接

自连接是指连接同一张表的不同行，使用相同的表进行连接操作。它在表中的数据之间建立关系，以便在单个表内进行数据匹配和查询。一般使用JOIN实现

SELECT e.employee_name '员工姓名', m.employee_name '经理姓名' FROM employee e 
JOIN employee m ON e.manager_id = m.employee_id;

内连接

通过匹配两个或多个表中满足连接条件的行，从这些表中返回满足条件的交集数据，内连接查询使用inner join关键字实现，inner可以省略。内连接查询时，条件用on连接，多个条件使用 () 将其括起来

SELECT emp.emp_name '员工姓名', dept.dept_name '部门信息' FROM emp 
JOIN dept ON emp.emp_id = dept.dept_id;

外连接

分为左外连接（LEFT OUTER JOIN）和右外连接（RIGHT OUTER JOIN）

左外连接

左外连接返回左表中所有的行，以及与左表中的行满足连接条件的右表中的行。如果右表中的行没有与左表中的行匹配的行，那么将在结果中显示空值（NULL）

SELECT 字段名 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件

右外连接

右外连接与左外连接相反，它返回右表中所有的行，以及与右表中的行满足连接条件的左表中的行。如果左表中的行没有与右表中的行匹配的行，那么将在结果中显示空值（NULL）

SELECT 字段名 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件

注意：左外连接和右外连接可以通过替换 SQL 中的表结构顺序，即可互相替换

子查询

SQL 中嵌套 SELECT 语句，称为嵌套查询，又称为子查询

单行子查询

例如：查询软件部门的所有员工

SELECT * FROM employee 
WHERE dept_id = (SELECT dept_id FROM dept WHERE dept_name = '软件部门');

• 子查询结果作为父查询条件的值
• 父查询条件是 = 号时，子查询结果就是单行单列查询

多行子查询

子查询返回多行记录，多行子查询还需要用到多行记录的操作符
如： in , all , any , not in
IN : 在指定的集合范围之内，多选一
NOT IN : 不在指定的集合范围之内
ANY : 子查询返回列表中，有任意一个满足即可
ALL : 子查询返回列表的所有值都必须满足

-- 统计所有的员工分布在那些部门的信息
SELECT * FROM category WHERE category.no IN (SELECT category_no FROM goods);

多列子查询

多个列名对应多个查询结果

SELECT * FROM emp e1 
WHERE (e1.commn, e1.salary) = (SELECT e2.commn, e2.salary FROM emp e2);

SQL 语句的编写及执行顺序
编写顺序

1. select
2. from
3. where
4. group by
5. having
6. order by
7. limit

执行顺序

1. from
2. where
3. group by
4. 聚合函数
5. having
6. select
7. distinct
8. order by
9. limit

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

SELECT DEPTNO AS 部门,COUNT(1) AS 人数
FROM emp
WHERE HIREDATE>'1975-01-01'
GROUP BY DEPTNO
HAVING COUNT(1)>=2
ORDER BY COUNT(1) DESC
LIMIT 0,2

多表查询
在分组查询时，SELECT后面跟的查询字段必须好分组字段相一致，或者和HAVING后的字段相一致
函数
日期函数

1. 将当前日期减去时间间隔

DATE_SUB(date, INTERVAL value unit)

用于给指定日期减去指定的时间间隔，其中，`date`是一个日期或日期时间表达式，`value`是一个整数，表示要减去的时间间隔的数量，`unit`是一个表示时间单位的关键字。

常用的时间单位（unit）有：

• MICROSECOND：微秒
• SECOND：秒
• MINUTE：分钟
• HOUR：小时
• DAY：天
• WEEK：周
• MONTH：月
• QUARTER：季度
• YEAR：年

例如：

SELECT DATE_SUB('2023-7-24', INTERVAL 1 WEEK) FROM table_name;

执行结果：

2. 将日期格式化为指定的字符串格式

DATE_FORMAT(date, format);

例如：

SELECT DATE_FORMAT('2023-7-24 21:35', '%Y-%m-%d') 转化格式后;

执行结果：

3. 求当前日期的星期

DAYOFWEEK(date);

例如：

SELECT DAYOFWEEK('2023-7-24') 星期;

执行结果：

结果2表示一周的第二天（一周的第一天从星期天开始算起）

类似的函数还有：

• DAYOFYEAR(data)：返回一个日期在一年中的天数。它的返回值是一个整数，表示指定日期在当前年份中的天数，范围从 1 到 366。
• DAYOFMONTH(data)：用于返回一个日期的月份中的天数。它的返回值是一个整数，表示指定日期在当前月份中的天数，范围从 1 到 31。