1. 外键
1.1 概念
[1] 外键
主表：被引用的表。
从表：包含外键的表，该外键引用主表中的主键。
主键：表中的唯一标识符，用于唯一标识表中的每一行。
外键：从表中的一列或多列，其值必须与主表中的主键值匹配。
[1] 外键约束
外键约束是一种数据库完整性规则，用于维护两个相关表之间的链接，并确保数据的引用完整性。
外键约束要求在一个表中的外键列的每个值必须在另一个表的主键列中存在，或者为NULL（如果外键列允许NULL值）。
[2] 外键字段
外键字段是存储外键值的列，它用于在从表中引用主表的主键。
外键字段可以是一个单独的列，也可以是多个列的组合，形成复合外键。
外键字段的数据类型必须与它引用的主键字段的数据类型完全一致。
外键字段上通常会创建索引，以提高查询效率。
[3] 外键值
外键值指的是外键字段中的实际数据值。
每个外键值都必须存在于被引用表的相应主键或唯一键中。
如果尝试插入或更新一条记录，而外键值不在被引用表中找到，那么操作将失败，除非指定了适当的外键行为（如CASCADE, SET NULL等）。
1.2 外键语法
先建主表，即被引用的表
create table 表1(
列1 数据类型1 约束条件1 comment 注释1,
列2 数据类型2 约束条件2 comment 注释2
);
再建从表，从表可以通过外键去引用主表
create table 表2(
列1 数据类型1 约束条件1 comment 注释1,
列2 数据类型2 约束条件2 comment 注释2,
foreign key (表2中的列名) references 表1(表1中被引用的列名)
);
SQL语句不先创建主表而先创建带有foreign key的从表会报错
1.3 外键关系之一对多
[1] 理论分析
以员工表与部门表为例
员工表角度：一个员工只能对应一个部门
部门表角度：一个部门可以有多个员工
部门与员工是一对多的关系，部门是"一"，员工是"多"。
外键放在多的一方还是少的一方？
（1）外键放在少的一方（放在部门）
员工表：
emp_id name age
1 a 18
2 b 19
3 c 20
部门表：
dep_id dep_name dep_desc emp_id1(外键)
1 开发 开发部门 1
1 开发 开发部门 2
1 开发 开发部门 3
（2）外键放在多的一方（放在员工）
员工表：
| emp_id | name | age | dep_id1(外键) |
| 1 | a | 18 | 1 |
| 2 | b | 19 | 1 |
| 3 | c | 20 | 1 |
部门表：
| dep_id | dep_name | dep_desc |
| 1 | 开发 | 开发部门 |
由数据量可见，一对多的情况下将外键放在多的一方更合适。
[2] 代码实现
先建主表部门表
create table dep(
dep_id int primary key auto_increment comment "部门编号",
dep_name varchar(20) comment "部门名称",
dep_desc varchar(20) comment "部门描述"
);
再建从表员工表
create table emp(
emp_id int primary key auto_increment comment "员工编号",
name varchar(20) comment "员工姓名",
age int comment "员工年龄",
dep_id1 int comment "员工编号",
foreign key (dep_id1) references dep(dep_id)
);
查看表结构
1.4 外键关系之一对一
[1]理论分析
用户基础信息表存使用频率高的数据：如账号、昵称、绑定手机号
用户详细信息表存使用频率低的数据：如邮箱、爱好
一个用户基础信息表只能对应一个用户详细信息表
一个用户详细信息表只能对应一个用户基础信息表
关系为"一对一"
外键放在任意一方都可以，但是推荐放在使用频率较高的一方
[2]代码实现
先建主表详细信息表
create table detail_info(
id int primary key auto_increment comment "详细信息编号",
mail varchar(30),
hobby set("read", "run", "rest")
);
再建从表基础信息表
create table main_info(
id int primary key auto_increment comment "基础信息编号",
name varchar(20),
phone_num varchar(11),
detail_id int,
foreign key (detail_id) references detail_info(id)
);
查看表结构
1.5 外键关系之多对多
[1] 理论分析
以图书表与作者表为例
一本书可以对应多位作者
一位作者也可以对应多本书
图书与作者的关系为"多对多"
两张表不能使用外键进行相互引用，需要创建第三张表来存储多对多的关系
图书表：
| id | title | price | author |
| 1 | 语文 | 10 | ? |
| 2 | 数学 | 11 | ? |
| 3 | 英语 | 12 | ? |
作者表：
| id | name | age | book |
| 1 | a | 18 | ? |
| 2 | b | 19 | ? |
| 3 | c | 20 | ? |
关联关系表：
| id | book_id | author_id |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
[2] 代码实现
先建图书表和作者表
create table book(
id int primary key auto_increment comment "图书编号",
title varchar(30) comment "图书名称",
price decimal(3,2) comment "图书价格"
);
create table author(
id int primary key auto_increment comment "作者编号",
name varchar(30) comment "作者姓名",
age int comment "作者年龄"
);
再建关联关系表
create table relation(
id int primary key auto_increment comment "关联关系编号",
book_id int comment "图书编号",
author_id int comment "作者编号",
foreign key (book_id) references author(id),
foreign key (author_id) references book(id)
);
查看表结构
1.6 外键约束之级联更新和级联删除
级联更新（CASCADE UPDATE）和级联删除（CASCADE DELETE）是外键约束的一部分，用于在主表和从表之间维护数据的完整性。更新或删除主表中的记录时，自动更新或删除从表中相关联的记录。
级联删除数据：在外键约束后面添加on delete cascade
级联更新数据：在外键约束后面添加on update cascade
create table t1(
id int primary key,
name varchar(30)
);
create table t2(
id int primary key,
main_id int,
foreign key (main_id) references t1(id) on delete cascade on update cascade
);
t2 表有一个外键 main_id 指向 t1 表的 id 。
ON DELETE CASCADE表示当 t1 中的id被删除时，t2 中的main_id也会被删除。
ON UPDATE CASCADE表示当 t1 中的id被更新时，t2 中的 main_id 也会被更新。
2. 过滤条件where
2.1 概念
过滤条件通常用于SELECT、UPDATE和DELETE语句中，以指定哪些行应该被查询、更新或删除。这些条件通过WHERE子句来定义。
语法：
SELECT 列1, 列2, ... FROM 表名 WHERE 条件;
UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件;
DELETE FROM 表名 WHERE 条件;
执行顺序：
from
where
select
2.2 代码实现
[1]创建员工信息表
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender enum("male","female") not null,
age int(3) unsigned not null,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
[2]添加数据
insert into emp(name, gender, age, hire_date, post, salary, office, depart_id) values
("cristiano", "male", 78, '20220306', "豪迈", 730.33, 401, 1), # 以下是开发部
("ronaldo", "female", 25, '20220102', "teacher", 12000.50, 401, 1),
("avril", "male", 35, '20190607', "teacher", 15000.99, 401, 1),
("lavigne", "female", 29, '20180906', "teacher", 11000.80, 401, 1),
("kylian", "female", 27, '20220806', "teacher", 13000.70, 401, 1),
("haaland", "male", 33, '20230306', "teacher", 14000.62, 401, 1), # 以下是运营部
("娜娜", "female", 69, '20100307', "sale", 300.13, 402, 2),
("芳芳", "male", 45, '20140518', "sale", 400.45, 402, 2),
("小明", "male", 34, '20160103', "sale", 350.80, 402, 2),
("亚洲", "female", 42, '20170227', "sale", 320.99, 402, 2),
("华华", "female", 55, '20180319', "sale", 380.75, 402, 2),
("田七", "male", 44, '20230808', "sale", 420.33, 402, 2), # 以下是营销部
("大古", "female", 66, '20180509', "operation", 630.33, 403, 3),
("张三", "male", 51, '20191001', "operation", 410.25, 403, 3),
("李四", "male", 47, '20200512', "operation", 330.62, 403, 3),
("王五", "female", 39, '20210203', "operation", 370.98, 403, 3),
("赵六", "female", 36, '20220724', "operation", 390.15, 403, 3);
[3]查询所有数据
[4]查询id大于等于3，小于等于6的数据
方式一（不生效）：
select * from emp where 3<=id<=6;
这个语句查询的结果仍然为所有数据
方式二：
select * from emp where id>=3 and id<=6;
方式三：between...and...
select * from emp where id between 3 and 6;
[5]查询工资是11000.80或13000.70或15000.99的数据
方式一：or
select * from emp where salary=11000.80 or salary=13000.70 or salary=15000.99;
方式二：in
select * from emp where salary in (11000.80,13000.70,15000.99);
[6]查询员工姓名中包含字母i的姓名和工资
使用%：
% 是一个通配符，用于LIKE查询中匹配任意数量的字符（包括零个字符）。当在LIKE子句中使用%时，它表示在该位置可以有任意长度的任意字符出现。
例如：SELECT * FROM table_name WHERE column_name LIKE '%value%';
这个查询会返回所有在column_name列中包含“value”的行，无论“value”前后有多少其他字符。
案例实现：
select name,salary from emp where name like "%i%";
[7]查询员工姓名由5个字符组成的姓名和工资
方式一：
使用下划线：_（下划线）也是一个通配符，_用于LIKE查询中匹配单个任意字符。在LIKE子句中使用_时，它表示在该位置只能有一个字符出现。
select name,salary from emp where name like "_____"; ---双引号内有5个_
方式二：
使用CHAR_LENGTH()：该函数用于返回字符串的长度
select name,salary from emp where char_length(name)=5;
[8]查询id小于3或id大于6的数据
方式一：
select * from emp where id<3 or id>6;
方式二：
select * from emp where not id between 3 and 6;
[9]查询岗位描述为空的员工姓名和岗位名称
方式一：使用 = 无法查询到值为NULL的数据
select * from emp where post_comment = NULL;
方式二：使用 is 才能查询到值为NULL的数据
select name,post from emp where post_comment is NULL;
3. 分组查询group by
3.1 概念
分组查询是指使用GROUP BY子句对查询结果进行分组，可以使用聚合函数对每个组的数据进行聚合操作，比如计算每个组的平均值(AVG)、总和(SUM)、最大值(MAX)、最小值(MIN)、统计个数(COUNT)等。
3.2 代码实现
[1]当MySQL的系统变量的sql_mode中含有ONLY_FULL_GROUP_BY时，无法分组查询数据
show variables like "%sql_mode%"; select * from emp group by post;
[2]sql_mode去掉ONLY_FULL_GROUP_BY，可以分组查询
set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
---session重启后失效 select * from emp group by post;
由以上结果可知，分组查询得到的数据是每组的第一条数据
[3]分组后使用聚合函数max
查询每个部门的最高工资
select max(salary) from emp group by post;
可以使用as给查询的对象起别名，但是不推荐，容易造成语义不明确，了解即可
select max(salary) as "最高工资" from emp group by post;
[4]分组后使用聚合函数min
查询每个部门的最低工资
select post,min(salary) as "最低工资" from emp group by post;
[5]分组后使用聚合函数avg
计算每个部门的平均工资
select post,avg(salary) as "平均工资" from emp group by post;
[6]分组后使用聚合函数sum
计算每个部门的工资总和
select post,sum(salary) as "工资总和" from emp group by post;
[7]分组后使用聚合函数count
统计每个部门的人数
select post,count(id) as "工资总和" from emp group by post; ---括号内的参数可以是不为空的任意列名
count不能对NULL进行统计：
select post,count(post_comment) from emp group by post;
[8]分组后使用聚合函数group_concat
概念：
GROUP_CONCAT() 的作用时将多个行中的数据连接成一个字符串。这个函数通常在 GROUP BY 子句中使用，可以将属于同一组的多个值连接成一个单一的字符串输出。
GROUP_CONCAT() 既可以自动连接查询的结果，也可以手动拼接字符串。
应用：
(1)group_concat将括号内的参数当作一个整体，自动与下一个值使用逗号进行拼接
按部门进行分组，查看每个部门下的所有员工姓名
select post,group_concat(name) from emp group by post;
(2)group_concat将括号内的参数不仅可以是列名，也可以是自定义的字符串，进行数据的拼接
select post,group_concat('pg_',name) from emp group by post;
(3)使用group_concat查询多条数据，并使用字符串进行拼接
select post,group_concat(name,":",salary) from emp group by post;
括号内的参数当作一个整体，与下一个值使用逗号，进行拼接
(4)如果不分组，直接使用group_concat没有实际意义
select group_concat(name) from emp;
(5)group_concat用于group by分组场景下的字符串拼接，而concat用于不分组的字符串拼接
concat()函数用于将两个或多个字符串值连接成一个字符串。它可以接受两个或多个参数，并将它们按顺序连接起来。
select concat(name,salary) from emp; select concat(name,":",salary) from emp;
标签：group,name,33,外键,emp,post,id,select From： https://www.cnblogs.com/hbutmeng/p/18457605