MySQL-2（14000字详解）

一： 数据库约束

在 MySQL 中，约束是用于限制数据库表中数据的规则或条件。它们是为了保证数据的完整性和一致性。MySQL 提供了多种约束类型，包括 NOT NULL、UNIQUE、DEFAULT、PRIMARY KEY、FOREIGN KEY 和 CHECK等等

1. NOT NULL 约束：NOT NULL 约束用于确保列中的值不为空。

示例语句：

   CREATE TABLE Users (
       id INT NOT NULL,
       name VARCHAR(50) NOT NULL
   );
1
2
3
4

这是用于创建名为 “Users” 的表的 SQL 命令。该表包含两个列：id 和 name，并且id 和 name不能存储NULL值

2. UNIQUE 约束： UNIQUE 约束用于确保列中的值唯一。

示例语句：

   CREATE TABLE Users (
       id INT UNIQUE,
       email VARCHAR(50) UNIQUE
   );
1
2
3
4

这是用于创建名为 “Users” 的表的 SQL 命令。该表包含两个列：id 和 name，并且id 和 name的值是唯一的，不重复的

3. DEFAULT 约束：DEFAULT 约束用于为列设置默认值。

示例语句：

   CREATE TABLE Users (
       id INT,
       name VARCHAR(50) DEFAULT 'John'
   );
 
1
2
3
4
5

这是用于创建名为 “Users” 的表的 SQL 命令。该表包含两个列：id 和 name，并且在插入数据的时候，name列如果为空，默认值为 ‘John’

4. PRIMARY KEY 约束：PRIMARY KEY 约束用于定义表中的主键。主键是唯一标识表中每一行的列。

示例语句：

 CREATE TABLE Users (
       id INT PRIMARY KEY,
       name VARCHAR(50)
   );
  
1
2
3
4
5

这是用于创建名为 “Users” 的表的 SQL 命令。该表包含两个列：id 和 name，并且id作为这个表的主键。

主键就相当于现实生活中的身份证，一个表里只有一个主键，在这个表中，我们可以通过id来唯一标识学生，如果要查询学生的信息，只需要通过id查询即可，因为主键都是唯一且不为空的。

简单理解一下，主键就是NOT NULL 和UNIQUE 的结合，对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1，下面详细讲解一下auto_increment

当在 MySQL 数据库中创建表时，可以使用 AUTO_INCREMENT 属性为某个列指定自增长。

自增长列（也称为自增列或自动增长列）是一种特殊的列，其值会在每次插入新行时自动增加。通常用于为表生成唯一的标识符。自增长列只能与数字数据类型（如 INT、BIGINT 等）一起使用。

当我每新增一个数据的时候，那么主键的值就会自动+1

例如，如果你将一个表的主键列定义为 AUTO_INCREMENT，而且当前的最大主键值是 100

当你插入一条新的数据时，主键列的值会自动设为 101。当你插入下一条记录时，主键列的值将会是 102，依此类推，每次插入都会自动递增。

当我们给自增主键插入数据的时候，可以手动指定一个值，也可以让mysql自己分配，如果要让mysql自己分配，那么只需要在insert语句中，对于主键的值设置为null，即可，eg：

insert into student values（null，'张三'）;
1

注意，此时的null是让mysql自己分配主键的初始值，而不是将id设置为空。

5. FOREIGN KEY 约束：FOREIGN KEY 约束用于定义表之间的引用关系。它建立了从一个表到另一个表的链接，用于关联其他表的主键或唯一键。

示例语句：

-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识

DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
 id INT PRIMARY KEY auto_increment,
 sn INT UNIQUE,
 name VARCHAR(20) DEFAULT 'unkown',
 qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

• FOREIGN KEY (classes_id) REFERENCES classes(id)

这个语句将学生表的 classes_id 列作为外键，参考了 classes 表的 id 列。这意味着学生表中的 classes_id 列的值必须在 classes 表的 id 列中存在。

这样定义外键后，学生表中的 classes_id 列的值只能是 classes 表中存在的 id 值。如果尝试插入或更新学生表的数据时，classes_id 列的值不存在于 classes 表中，将会触发外键约束错误。

当删除或更新 classes 表中的行时，将会自动处理学生表中依赖于该行的数据，以避免数据不一致的情况发生。

6. CHECK 约束： CHECK 约束用于限制列中的值必须满足指定的条件。

示例语句：

   CREATE TABLE Products (
       product_id INT,
       quantity INT,
       CHECK (quantity > 0)
   );
1
2
3
4
5

在 MySQL 中，CHECK 是用来添加约束条件（constraint）的关键字。

它可以用来限制插入（INSERT）或更新（UPDATE）操作对表中某一列的值的合法性。

比如说：CHECK (quantity > 0) 用来限制 “quantity” 列的值必须大于零。如果满足条件，则操作被允许进行；如果不满足条件，则操作将会被拒绝，并返回错误消息。

二：表的增删查改（进阶）

2.1 新增

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
 id INT primary key auto_increment,
 name VARCHAR(20) comment '姓名',
 age INT comment '年龄',
 email VARCHAR(20) comment '邮箱',
sex varchar(1) comment '性别',
mobile varchar(20) comment '手机号'
);

-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;
1
2
3
4
5
6
7
8
9
10
11
12
13

最后一条条 SQL 语句的作用是将另一张名为 student 的表中的 name 和 qq_mail 列的值插入到名为 test_user 的表中的 name 和 email 列中

2.2查询

2.2.1聚合查询

2.2.1.1 聚合函数

1. COUNT案例：

-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;

-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
SELECT COUNT(qq_mail) FROM student;
1
2
3
4
5
6

2. SUM案例：

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;

-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;
1
2
3
4
5

3. AVG案例：

-- 统计平均总分
SELECT AVG(chinese + math + english) as avg_total FROM exam_result;
1
2

4. MAX案例：

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;
1
2

5. MIN案例：

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;
1
2

2.2.1.2GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。

但是需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

案例：

create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);

insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);

select role,max(salary),min(salary),avg(salary) from emp group by role;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• “select role,max(salary),min(salary),avg(salary) from emp group by role;”

这条SQL语句的含义是从"emp"表中查询每个不同"role"（角色）的最高薪水（“max(salary)”）、最低薪水（“min(salary)”）和平均薪水（“avg(salary)”）。

在给定的数据例子中，结果是这样的：

2.2.1.3HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

• 分组前筛选用where
• 分组后筛选用having

案例：

显示平均工资低于1500的角色和它的平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;
1

2.2.2联合查询（多表查询）

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

2.2.2.1内连接

内连接（Inner Join）是MySQL中常用的一种连接方式，它将两个表中符合连接条件的行进行匹配，并返回匹配成功的结果。

内连接的语法如下：

select 字段(列名) from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
1
2
3

其中，表1 和 表2 是要连接的两个表，列名 是连接条件。连接条件可以是两个表中具有相同值的列或表达式。

下面是一个示例，假设有两个表 students 和 scores，students 表存储学生的基本信息，scores 表存储学生的考试成绩。

students 表：

+------+--------+
| id   | name   |
+------+--------+
| 1    | Alice  |
| 2    | Bob    |
| 3    | Charlie|
| 4    | David  |
+------+--------+
1
2
3
4
5
6
7
8

scores 表：

+------+-------+-------+
| id   | score | grade |
+------+-------+-------+
| 1    | 90    | A     |
| 2    | 80    | B     |
| 3    | 95    | A     |
| 4    | 75    | C     |
+------+-------+-------+
1
2
3
4
5
6
7
8

我们可以使用内连接将这两个表连接起来，获取学生的成绩及姓名：

SELECT students.name, scores.score FROM students INNER JOIN scores ON students.id = scores.id;
1

以上语句将返回以下结果：

+--------+-------+
| name   | score |
+--------+-------+
| Alice  | 90    |
| Bob    | 80    |
| Charlie| 95    |
| David  | 75    |
+--------+-------+
1
2
3
4
5
6
7
8

这里的连接条件是 students.id = scores.id，即两个表中的 id 列相等。通过内连接，我们将学生姓名和成绩进行了关联，并只返回匹配成功的结果。

students 表和scores 表取笛卡尔积后形成的新表如图所示：

+----+----------+------+------+-------+-------+
| id | name     | id   | score | grade | grade |
+----+----------+------+------+-------+-------+
| 1  | Alice    | 1    | 90    | A     | A     |
| 1  | Alice    | 2    | 80    | B     | B     |
| 1  | Alice    | 3    | 95    | A     | A     |
| 1  | Alice    | 4    | 75    | C     | C     |
| 2  | Bob      | 1    | 90    | A     | A     |
| 2  | Bob      | 2    | 80    | B     | B     |
| 2  | Bob      | 3    | 95    | A     | A     |
| 2  | Bob      | 4    | 75    | C     | C     |
| 3  | Charlie  | 1    | 90    | A     | A     |
| 3  | Charlie  | 2    | 80    | B     | B     |
| 3  | Charlie  | 3    | 95    | A     | A     |
| 3  | Charlie  | 4    | 75    | C     | C     |
| 4  | David    | 1    | 90    | A     | A     |
| 4  | David    | 2    | 80    | B     | B     |
| 4  | David    | 3    | 95    | A     | A     |
| 4  | David    | 4    | 75    | C     | C     |
+----+----------+------+------+-------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

因为连接条件是 students.id = scores.id，即两个表中的 id 列相等，所以我们在这张表中提取出了这张表：

+--------+-------+
| name   | score |
+--------+-------+
| Alice  | 90    |
| Bob    | 80    |
| Charlie| 95    |
| David  | 75    |
+--------+-------+
1
2
3
4
5
6
7
8

-- 写法1
select sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='许仙';

-- 写法2
select sco.score from student stu, score sco where stu.id=sco.student_id and stu.name='许仙';
1
2
3
4
5
6

对于这两种写法，效果是完全一样的

2.2.2.2 where on having 的区别

当在 MySQL 中使用 SQL 查询时，我们可以使用 WHERE、ON 和 HAVING 这三个子句来进行过滤和条件筛选。虽然它们的作用有些相似，但它们的使用场景和操作对象是不同的。

1. WHERE 子句： WHERE 子句用于在检索数据时对行进行条件过滤（分组前）。它通常与 SELECT 语句一起使用，用于筛选满足特定条件的行。

示例：

SELECT * FROM customers WHERE age > 25;
1

上面的示例查询将返回 age 大于 25 的所有 customers 行。

2. ON 子句：ON 子句通常用于 JOIN 操作，用于指定连接条件。它在连接两个或多个表时使用，指定连接表之间的关系。

示例：

SELECT * FROM customers JOIN orders ON customers.id = orders.customer_id;
1

上面的示例查询通过连接 customers 表和 orders 表，使用 customers 表的 id 列和 orders 表的 customer_id 列进行匹配。

3. HAVING 子句： HAVING 子句用于在检索数据后对结果进行条件过滤（分组后）。它通常与 GROUP BY 子句一起使用，用于筛选满足特定条件的分组结果。

示例：

SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id HAVING order_count > 5;
1

上面的示例查询将返回订单数量大于 5 的每个 customer_id。

2.2.2.3 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

在MySQL中，外连接是一种用来联接两个或多个表的方法，它允许我们检索那些无法在内连接中匹配的数据。外连接包括左外连接和右外连接，它们分别基于左表和右表的数据，以不同的方式返回结果。

1. 左外连接（Left Outer Join）：左外连接返回左表中的所有数据，以及与右表中匹配的数据。如果右表中没有匹配的数据，则以 NULL 值表示。左外连接使用关键字 LEFT JOIN 或 LEFT OUTER JOIN。

示例语句：

SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
1

这条查询语句的含义是：从Customers表中获取每个客户的姓名，以及他们对应的订单ID。如果客户没有任何订单，那么订单ID列将显示为NULL。

在上面的示例中，我们使用 LEFT JOIN 将 Customers 表和 Orders 表联接起来。它会将 Customers 表中的所有数据返回，如果在 Orders 表中有匹配的数据，则返回相应的 OrderID。如果没有匹配的数据，则 OrderID 列将显示为 NULL 值。

2. 右外连接（Right Outer Join）：右外连接返回右表中的所有数据，以及与左表中匹配的数据。如果左表中没有匹配的数据，则以 NULL 值表示。右外连接使用关键字 RIGHT JOIN 或 RIGHT OUTER JOIN。

示例语句：

SELECT Customers.CustomerName, Orders.OrderID FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
1

同理，与左外连接类似，这里不过多解释。

在上面的示例中，我们使用 RIGHT JOIN 将 Customers 表和 Orders 表联接起来。它会将 Orders 表中的所有数据返回，如果在 Customers 表中有匹配的数据，则返回相应的 CustomerName。如果没有匹配的数据，则 CustomerName 列将显示为 NULL 值。

2.2.2.4 自连接

自连接是指在同一张表连接自身进行查询，（本质上是把行转成列），用于比较行之间的关系。

MySQL中的自连接是指在同一张表中进行连接操作。自连接通常用于解决需要在同一表中比较不同行之间的数据的情况。

示例场景：假设我们有一个名为employees的表，其中包含员工的信息，包括emp_id（员工ID），emp_name（员工姓名），manager_id（经理ID）等字段。

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(255),
  manager_id INT
);
1
2
3
4
5

接着我们想要找到每个员工的经理姓名。

假设我们有以下数据示例：

表格 employees：

下面是一个使用自连接的示例查询语句：

SELECT 
  e.emp_name AS employee_name,
  m.emp_name AS manager_name
FROM
  employees e
  INNER JOIN employees m ON e.manager_id = m.emp_id;
1
2
3
4
5
6

根据上述示例数据和连接条件，查询的结果应该如下：

以上结果显示了每个雇员以及他们对应的经理名字，下面解释一下原因：

这张表在与自身求笛卡尔积之后形成的结果表的结构如下：

SELECT 
  e.emp_name AS employee_name,
  m.emp_name AS manager_name
FROM
  employees e
  INNER JOIN employees m ON e.manager_id = m.emp_id;
1
2
3
4
5
6

接着显示e.emp_name和 m.emp_name，接着我们通过e.manager_id = m.emp_id;筛选，筛选后结果就是：

2.2.3子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询：返回一行记录的子查询
eg：查询与“不想毕业” 同学的同班同学：

select * from student where classes_id=(select classes_id from student where name='不想毕业');
1

多行子查询：返回多行记录的子查询
eg：查询“语文”或“英文”课程的成绩信息

-- 使用IN
select * from score where course_id in (select id from course where name='语文' or name='英文');

-- 使用 NOT IN
select * from score where course_id not in (select id from course where ame!='语文' and name!='英文');
1
2
3
4
5

这个查询语句的目的是找到在score表中，课程名称为’语文’或者’英文’的记录，并返回对应的所有列数据。

2.2.3.4.from子句中使用子查询

在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

查询所有比“中文系2019级3班”平均分高的成绩信息：

-- 获取“中文系2019级3班”的平均分，将其看作临时表
SELECT
avg( sco.score ) score
FROM
score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
cls.NAME = '中文系2019级3班';
1
2
3
4
5
6
7
8
9

下面来详细解释一下这条语句：

这个查询语句用于获取"中文系2019级3班"的平均分，并将其视为临时表。

1. 首先，我们指定要从哪个表中获取数据。在这个查询中，我们使用了三个表，分别是score、student和classes。我们通过使用JOIN子句来连接这些表。

2. 我们使用JOIN子句将score表与student表连接起来。连接条件是score表中的student_id列与student表中的id列匹配。

3. 接下来，我们使用另一个JOIN子句将student表与classes表连接起来。连接条件是student表中的classes_id列与classes表中的id列匹配。

4. 然后，我们使用WHERE子句来筛选满足条件的数据。在这里，我们指定了classes表的NAME列等于’中文系2019级3班’，以限制结果只包括该班级的学生。

5. 最后，我们使用avg函数来计算score表中分数列的平均值，并将其别名为score。

整体而言，这个查询语句的目的是获取"中文系2019级3班"的平均分，并将结果作为临时表在查询中使用。

2.2.4合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

当在MySQL中需要将多个查询结果合并为单个结果集时，可以使用UNION运算符。UNION运算符用于合并两个或多个SELECT语句的结果集，并将其作为单个结果返回。下面是UNION的一些详细说明和示例语句：

基本语法：

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2
1
2
3

说明：

• UNION运算符用于合并两个或多个SELECT语句的结果集。
• UNION合并的结果集中不会包含重复的行，它自动去除重复的记录。
• UNION运算符必须由两个或多个SELECT语句组成，并且每个SELECT语句的列数和数据类型必须一致。

示例：
假设我们有两个表：students（学生表）和teachers（教师表），它们的结构如下：

students表：

+------+-------+
| id   | name  |
+------+-------+
| 1    | Alice |
| 2    | Bob   |
| 3    | Cindy |
+------+-------+
1
2
3
4
5
6
7

teachers表：

+------+-------+
| id   | name  |
+------+-------+
| 1    | Tom   |
| 2    | John  |
| 3    | Mary  |
+------+-------+
1
2
3
4
5
6
7

我们可以使用UNION运算符将这两个表的姓名字段合并为单个结果集，并去除重复的记录，如下所示：

SELECT name FROM students
UNION
SELECT name FROM teachers;
1
2
3

执行上述查询语句后，将得到以下结果：

+-------+
| name  |
+-------+
| Alice |
| Bob   |
| Cindy |
| Tom   |
| John  |
| Mary  |
+-------+
1
2
3
4
5
6
7
8
9
10

以上是一个简单的UNION示例，它将两个表的结果合并成一个不包含重复记录的结果集。

如果你想要一个包含重复记录的结果集，那么你可以使用:

union all
1

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。