MySQL-约束，子查询，常用函数

1.SQL

1.1 SQL 介绍

SQl（Structured Query Language，结构化查询语言）是一种用于管理关系型数据库系统的标准化语言。它是一种专门用于执行各种操作的语言，包括查询数据、插入、更新、删除数据以及定义和管理数据库模式（例如表和索引等）。之所以出现这个东西，是为了统一/屏蔽不同数据库厂商生产的数据库产品之间的差异。

SQL定义了一系列标准和规范，数据库厂商也需要按照规范来，当然也会有一些小的差异，相比没有规范来说要好的多。

SQL语言主要包括以下几种类型的语句：

数据查询语言（Data Query Language，DQL）：用于从数据库中检索数据。常见的DQL语句是SELECT。
数据操作语言（Data Manipulation Language，DML）：用于操作数据库中的数据，包括插入、更新和删除数据。常见的DML语句是INSERT、UPDATE和DELETE。
数据定义语言（Data Definition Language，DDL）：用于定义和管理数据库对象，例如表、索引、视图等。常见的DDL语句是CREATE、ALTER和DROP。
数据控制语言（Data Control Language，DCL）：用于控制数据库访问权限和安全性。常见的DCL语句是GRANT和REVOKE。

1.2 简单DDL

DDL：Data Definition Language 数据库定义语言

涉及关键字：creat, drop, alter

-- 创建表
-- IF NOT EXISTS 判断表是否已经存在，不存在时再创建，避免报错
CREATE TABLE IF NOT EXISTS users_info(`id` INT,`name` VARCHAR(20),`age` INT,`sex` CHAR(2)
);-- 添加数据
INSERT INTO users_info(`id`,`name`,`age`,`sex`)
VALUES(1,'小明',17,'男'),(2,'小花',18,'女'),(3,'小李',20,'男'),(4,'小七',16,'女');-- 修改表名 ALTER TABLE 表名 RENAME 新表名
ALTER TABLE users_info RENAME users;-- 修改列名(数据类型可以修改，也可以不修改) ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型
ALTER TABLE users CHANGE sex gender CHAR(2);-- 添加列(尾部) ALTER TABLE 表名 ADD 列名 类型;
ALTER TABLE users ADD address VARCHAR(50);
-- 添加列(指定列后面) ALTER TABLE 表名 ADD 列名 类型 AFTER 列名;
ALTER TABLE users ADD birthday DATE AFTER age;
-- 添加列(首列) ALTER TABLE 表名 ADD 列名 类型 FIRST;
ALTER TABLE users ADD `no` INT FIRST;-- 删除列 ALTER TABLE 表名 DROP 列名;
ALTER TABLE users DROP `no`;-- 修改数据类型 ALTER TABLE 表名 MODIFY 列名 新数据类型;
ALTER TABLE users MODIFY gender VARCHAR(2);

1.3 DDL增强

上面讲的DDL只是一系列基础操作，它让我们有库有表可以插入数据。但是对于插入的数据是否是有效数据，并不能保证。比如我们可以插入一条所有字段都是NULL的记录：

命令insert into student(id,name,score) values (null,null,null);

这种记录白白地占用了我们的存储空间，但是实际上并没有用处，为了防止表中插入这种数据，MYSQL提供了一系列的完整性验证机制。

约束分类：

1.3.1 实体完整性（主键）

主键通常用于唯一确定表中一条数据，设置为主键的字段不能为null并且不能重复。

主键可以设置在一个字段上，也可以设置在多个字段上。（但大多数场景都是设置在一个字段上，这个字段通常是业务主键或者流水号）。

主键的添加：

-- 添加主键约束，关键字：PRIMARY KEY
-- 第一种：创建表时添加主键约束两种方式
-- 方式一：数据类型后加PRIMARY KEY,一次只添加一个主键
CREATE TABLE students(`id` INT PRIMARY KEY,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50)
);
-- 方式二：可以设置一个主键也可以设置多个主键，PRIMARY KEY(列,列,列...)
-- 存在多个主键时，只有所有主键值都一样时，才认为是相同数据
CREATE TABLE students(`id` INT,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50)PREPARE KEY(`id`,`name`)
);-- 第二种：创建表之后添加主键约束
-- 语法：ALTER TABLE 表名 ADD PRIMARY KEY(列,列,列);可添加一个或多个主键
CREATE TABLE students(`id` INT,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50)
);
ALTER TABLE students ADD PRIMARY KEY(`id`);

1.3.2 主键自增

主键不能为空且不能重复，如果让用户输入主键的值的话很容易会出现重复的情况，于是有了自增的概念，主键值自动增加，不需要我们输入值，自增的列必须为主键列。

设置自增的方式：

-- 主键自增,关键字：AUTO_INCREMENT
-- 第一种：创建表时，添加自增
CREATE TABLE students(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50)
);-- 第二种：创建表后，添加自增
-- 语法：ALTER TABLE 表名 MODIFY 主键列名 类型 AUTO_INCREMENT;
CREATE TABLE students(`id` INT PRIMARY KEY,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50)
);
ALTER TABLE students MODIFY `id` INT AUTO_INCREMENT;-- 设置自增起始值
-- 语法：ALTER TABLE 表名 AUTO_INCREMENT=值;
ALTER TABLE students AUTO_INCREMENT=100;

1.3.3 关联完整性(外键)

这种一个类的变量可以找到另外一个类对象的这种关联关系,在数据库中怎么体现呢? 外键。一个表中的外键列,需要参照另一个表的主键值生成,并且一个表的外键列的值和另一个表的主键值的数据类型必须一致,然后就可以通过这个表中的外键去找另一个表的主键,能找到主键就能根据主键找到对应的一行数据，常用于有关联关系的两个表中。

外键的值必须是关联表中的主键值，也可以为空。

设置外键的方式：

-- 添加外键约束，关键字：FOREIGN KEY
-- 第一种：创建表时添加外键约束
-- 创建教师信息表
CREATE TABLE teachers(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50)
);
-- 创建学生信息表
CREATE TABLE students(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50),teacher_id INT,FOREIGN KEY(teacher_id) REFERENCES teachers(id)
);
-- 注意：引用students中添加的外键列，指向teachers表时，必须先创建teachers表
-- 测试语句：
-- 添加一个老师
INSERT INTO teachers(`id`,`name`,age,address)
VALUES(1,'张三',27,'北京');
-- 添加一个学生，通过teacher_id指向张老师
INSERT INTO students(`id`,`name`,age,address,teacher_id)
VALUES(1,'小明',15,'上海',1);
-- 添加一个学生，teacher_id没有设置值
INSERT INTO students(`id`,`name`,age,address)
VALUES(2,'小红',14,'上海');
-- 添加一个学生，teacher_id指向一个不存在的讲师，报错
INSERT INTO students(`id`,`name`,age,address,teacher_id)
VALUES(1,'小亮',15,'深圳',2);DROP TABLE students;
DROP TABLE teachers;-- 第二种：创建表后添加外键约束
-- 语法：ALTER TABLE 表名 ADD FOREIGN KEY(外键列名) REFERENCES 指向的表的表名(主键列列名)
CREATE TABLE teachers(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50)
);
-- 创建学生信息表
CREATE TABLE students(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20),`age` INT,`address` VARCHAR(50),teacher_id INT
);
-- 添加外键约束
ALTER TABLE students ADD FOREIGN KEY(teacher_id) REFERENCES teachers(id);
-- 测试语句：
-- 添加一个老师
INSERT INTO teachers(`id`,`name`,age,address)
VALUES(1,'张三',27,'北京');
-- 添加一个学生，通过teacher_id指向张老师
INSERT INTO students(`id`,`name`,age,address,teacher_id)
VALUES(1,'小明',15,'上海',1);
-- 添加一个学生，teacher_id没有设置值
INSERT INTO students(`id`,`name`,age,address)
VALUES(2,'小红',14,'上海');
-- 添加一个学生，teacher_id指向一个不存在的讲师，报错
INSERT INTO students(`id`,`name`,age,address,teacher_id)
VALUES(1,'小亮',15,'深圳',2);

1.3.4 唯一约束(unique)

唯一约束是指定table中的列或列组合不能重复，保证数据的一致性。

唯一约束不允许出现重复的值，但是可以有多个null

设置唯一约束的方式：

-- 唯一约束，关键字：UNIQUE
-- 第一种：创建表时，添加 UNIQUE约束
CREATE TABLE temp(id INT UNIQUE,`name` VARCHAR(20)
);
-- 或
CREATE TABLE temp(id INT,`name` VARCHAR(20),UNIQUE(id)
);
-- 第二种：创建表之后，添加 UNIQUE约束
CREATE TABLE temp1(id INT,`name` VARCHAR(20)
);
ALTER TABLE temp1 ADD UNIQUE(id);

1.3.5 非空约束not null 和默认值 default

所有的类型都可以是null,包括int，float类型，设置为not null的字段必须填入值。

经常和default一起使用，当不填写数据时，把默认值设置为指定的值。

设置方式：

-- 非空约束 NOT NULL 和 默认值 DEFAULT
-- 第一种：创建表时添加约束
CREATE TABLE temp2(id INT NOT NULL,`name` VARCHAR(20) DEFAULT '无名氏',sex VARCHAR(10) NOT NULL DEFAULT '男'
);-- 第二种：创建表之后，添加约束
-- 语法：ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL DEFAULT 默认值; 
CREATE TABLE temp2(id INT,`name` VARCHAR(20),sex VARCHAR(10)
);
ALTER TABLE temp2 MODIFY id INT NOT NULL;
ALTER TABLE temp2 MODIFY `name` VARCHAR(20) DEFAULT '无名氏';
ALTER TABLE temp2 MODIFY sex VARCHAR(10) NOT NULL DEFAULT '男';

1.4 基础DQL

DQL：Data QUery Language,数据查询语言，主要用于查询表。

它通常用于一张表或多张表中（视图或子查询等）中按指定的条件筛选某些记录。涉及到的命令有select。

语法：select 列限定 from 表限定 where 行限定

1.4.1 条件判断

AND：且、和的意思，必须符合两个添加的判断，等同于java中的&&

-- 创建学生成绩表
CREATE TABLE stu_score(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20),score DECIMAL(5,2)
);-- 添加数据
INSERT INTO stu_score(`name`,score)
VALUES('张三',80.5),('李四',64),('王二狗',95),('李雷',76),('韩梅梅',87.5),('赵王',92.56);-- AND
-- 语法：SELECT 列限定 FROM 表限定 WHERE 表达式A AND 表达式B;
-- 例如：查询成绩表中，分数大于70且小于90分的学生的姓名和id
SELECT id,`name` FROM stu_score 
WHERE score>70 AND score<90;

OR：或的意思，符合一个判断条件即可，等同于Java中的||

-- OR
-- 语法：SELECT 列限定 FROM 表限定 WHERE 表达式A OR 表达式B;
-- 例如：查询成绩表中，姓名是张三或者成绩大于90分的
SELECT * FROM stu_score 
WHERE `name`='张三' OR score>90;

1.4.2 关系表达式

大于：> 小于：< 等于：= 大于等于：>= 小于等于：<= 不等于：<>

注意：= 和<> 额外留意，和Java中有所不同，java中判断等于是==，这里是=；

java中判断不等于是!=,这里使用<>

-- 关系表达式
-- > < >= <= = <>
-- 例如：查询表中成绩大于80分的
SELECT * from stu_score 
WHERE score > 80;
-- 例如：查询表中成绩为空的
SELECT * from stu_score 
WHERE score IS NULL;
-- 例如：查询表中成绩不为空的
SELECT * from stu_score 
WHERE score IS NOT NULL;

注意：判断是否为空是IS NULL，而不是=NULL；同理判断是否不为空是IS　NOT　NULL而不是<>NULL;AND和OR同时出现时，AND优先级高于OR。

BETWEEN...AND ...：在...之间

-- BETWEEN ... AND ...
-- 语法：SELECT 列限定 FROM 表限定 WHERE 列名 BETWEEN 值 AND 值;
-- 例如：查询成绩表中，分数大于80且小于90分的学生信息
SELECT * FROM stu_score
WHERE score BETWEEN 80 AND 90;

IN：在指定数据中

-- IN
-- 语法：SELECT 列限定 FROM 表限定 WHERE 列名 IN(值,值,值);
-- 例如：查询id为2，3的学生信息
SELECT * FROM stu_score
WHERE id IN(2,3);

1.4.3 模糊查询 LIKE

搜索功能的实现，一般会用到LIKE模糊查询，其中%匹配任意个数的任意字符，_ 匹配单个任意字符。如果想要查询%或_，需要转义 \%,\_

-- 模糊查询
-- LIKE
-- 语法：SELECT 列限定 FROM 表限定 WHERE 列名 LIKE ‘值’;
-- 例如：查询名字中有王子的学生信息
SELECT * FROM stu_score
WHERE `name` LIKE '%王%';

1.4.4 排序：ORDER BY

排序，望文知意就是让我们查到的数据按规则排序展示。

-- 排序: ORDER BY
-- 语法：SELECT 列限定 FROM 表限定 ORDER BY 列名 ASC/DESC
-- 升序：ASC 降序：DESC 如果不指定默认是升序
-- 例子：查询stu_score表中成绩不为null的学生数据，并按成绩降序排列
SELECT * FROM stu_score 
WHERE score IS NOT NULL 
ORDER BY score DESC;
-- 例子：查询stu_score表中成绩不为null的学生数据，并按成绩降序排列，如果成绩相同按id升序
SELECT * FROM stu_score 
WHERE score IS NOT NULL 
ORDER BY score DESC, id ASC;

1.4.5 限制条数：LIMIT

限制条数，通常和ORDER BY一起使用，获取指定条数的结果

-- 限制条数：LIMIT 一般会结合排序使用
-- 语法：SELECT 列限定 FROM 表限定 LIMIT 条数 
--       SELECT 列限定 FROM 表限定 LIMIT 开始值(不包含),条数
-- 例子：查询前三名学生的信息
SELECT * FROM stu_score 
ORDER BY score DESC
LIMIT 3;
-- 例子：查询第二，三名学生的信息(LIMIT的开始值不包含在内，会从开始值下一条输出指定条数)
SELECT * FROM stu_score 
ORDER BY score DESC
LIMIT 1,2;

1.5 单表查询（组函数）

组函数（Aggregate functions）是 SQL 中的一类函数，用于对一组数据进行聚合计算，返回单个值作为结果。组函数通常应用于 SELECT 语句中的列，并且通常与 GROUP BY 子句结合使用，以对数据按组进行聚合计算。

常见的组函数包括：

COUNT(): 计算指定列的行数，或者满足特定条件的行数。

SUM(): 计算指定列的数值之和。

AVG(): 计算指定列的数值平均值。

MIN(): 返回指定列的最小值。

MAX(): 返回指定列的最大值。

-- 常见组函数
-- 统计总数：count(*) 最大值：MAX(列) 最小值：MIN(列) 平均值：AVG(列) 总值：SUM(列)
-- AS 别名 对列或表设置别名，AS可加可不加
-- 一般列别名加AS，表别名不加AS
-- 例子：查询student表中，学生的总人数，成绩的最大值，最小值，平均分，和总分
SELECT COUNT(*) AS count,MAX(score) AS max,MIN(score) AS min,AVG(score) AS avg,SUM(score) AS sum
FROM student;

1.5.1 GROUP BY ：分组

-- 分组关键字：GROUP BY
-- 例子：查询student表中，每位老师所带学生的总人数，成绩的最大值，最小值，平均分，和总分
SELECT COUNT(*) AS count,MAX(score) AS max,MIN(score) AS min,AVG(score) AS avg,SUM(score) AS sum,teacher_id
FROM student
GROUP BY teacher_id;

1.5.2 HAVING：二次过滤

-- 过滤条件：HAVING
-- 例子：查询每个老师所带学生的平均分大于75的老师id和平均分
-- 注意：avg是列限定生成的，但是执行顺序是表限定->行限定->列限定
-- 			 所以 WHERE 行限定不能使用列限定中生成的avg，此时可以使用 HAVING进行过滤
SELECT teacher_id,AVG(score) AS avg FROM student
GROUP BY teacher_id
HAVING avg > 75;-- 例子：查询每个老师所带学生的平均分大于60的老师id和平均分,并以平均分降序
-- 先分组，再过滤，再排序  GROUP BY -> HAVING -> ORDER BY -> LIMIT
SELECT teacher_id,AVG(score) AS avg FROM student
GROUP BY teacher_id
HAVING avg > 60
ORDER BY avg DESC;

2.子查询

子查询又叫嵌套查询。它通常可以位于SELECT后面 FROM后面 WHERE后面,共三种使用场景。当我们查询一个表没有办法实现功能的时候,就需要使用子查询

上面我们讲到了分组查询，可以查询每个老师所带学生的最低分，但是我们刚才查出来之后,我们只能看到teacher_id,但是我们并不知道teacher_id对应的是那个老师,这个时候我们最好是显示老师的名字是比较好的,可以用子查询实现。

2.1 场景一： select 后面

-- 场景一：SELECT 后面
-- 语法：SELECT 字段名,(查询语句) FROM 表名
-- 例子：查询所有学生的信息并显示老师的姓名
SELECT *,(SELECT `name` FROM teacher WHERE id = teacher_id) AS teacher_name
FROM student;
-- 例子：查询张三的老师id和老师姓名
SELECT teacher_id,(SELECT `name` FROM teacher WHERE id = teacher_id) AS teacher_name
FROM student
WHERE `name` = '张三';-- 注意：当位于 SELECT 后面时，要注意
-- 1. 找好两个表之间的对应关系(外键等)
-- 2. 子查询中只能有一个字段(子查询的结果必须是一行一列)

2.2 场景二：from后面

还是学生表student，我们要将成绩进行分级，并且显示汉字的分级与字母的分级。这里可以使用子查询。相当于给student“新增”了2个字段

如 : 使用子查询对成绩划分等级, score<60 ,评级C 并且是差,score>=60 且 score<80 评级B并且是良,score>=80 评级是A并且是优

-- 场景二：FROM 后面
-- 语法：SELECT 字段名 FROM (查询语句) 表别名;
-- 例子：我们要将student表中的学生根据成绩进行分级， score<60 ,评级C 并且是差,score>=60 且 score<80 评级B并且是良,score>=80 评级是A并且是优，这里可以使用子查询
SELECT*,(CASE`rank` WHEN 'A' THEN'优' WHEN 'B' THEN'良' WHEN 'C' THEN'差' END ) AS rank_ch FROM( SELECT *,( CASE WHEN score >= 80 THEN 'A' WHEN score >= 60 THEN 'B' ELSE 'C' END ) AS `rank` FROM student ) temp;-- 注意：当位于 FROM后面时，要注意
-- 1. 我们可以把子查询当成一张表
-- 2. 必须要有别名，因为from后面的子查询优先被执行了，子查询的别名可以让其它查询将它当作一张表或列来操作

2.3 场景三： where后面

-- 场景三：位于WHERE后面
-- 例子： 在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息
SELECT * FROM student 
WHERE teacher_id IN (SELECT id FROM teacher WHERE `name`='张老师');-- 注意：1. 返回结果必须是一列，可以是多行
--       2. 如果返回结果是多行一列，判断条件就不能使用=了，要使用IN

3. UNION 和 UNION ALL

-- UNION 与 UNION ALL
-- 合并查询，合并查询的结果
-- UNION 去除重复项
-- UNION ALL 不去除重复项
-- 例子：查询teacher_id=2的所有学生的信息
SELECT * FROM student WHERE teacher_id = 2;
-- 查询分数大于60分的所有学生信息
SELECT * FROM student WHERE score > 60;
-- 查询teacher_id=2或分数大于60分的所有学生信息
-- 用 OR 实现(OR会去重，即同时满足两个条件只显示一次)
SELECT * FROM student WHERE teacher_id = 2 OR score > 60;
-- 用 UNION 实现，与or 效果一样
SELECT * FROM student WHERE teacher_id = 2
UNION
SELECT * FROM student WHERE score > 60;
-- 用 UNION ALL 实现，不会去重，同时满足多个条件会显示多条
SELECT * FROM student WHERE teacher_id = 2
UNION ALL
SELECT * FROM student WHERE score > 60;

注意：

union / union all 它俩的作用是把两张表或者更多表合并成一张表，前者会去重（去重的依据是UNION时SELECT出来的字段如果对应相等则认为是同一条记录，这的逻辑我们可以参考Java equals）但是or 尽管两行数据每个字段都相等,也不会去重，后者则不会去重，它会保留两张表中的所有记录，但是它性能高（因为去重操作要花时间），尽量使用union all，把去重这个工作交给代码去完成，这样可以减少MYSQL服务器的压力。

使用union / union all的时候要注意：

1.参与合并的表，它们SELECT出来的字段数量必须一致（强制规则）

2.参与合并的表，它们SELECT出来的字段的类型建议一一对应（非强制，但是最好遵循这条规则）

3.参与合并的表，它们SELECT出来的字段的顺序建议一致（非强制，但是最好遵循这条规则）