Mysql

SQL命名规定和规范

1. 注释应该清晰、简洁地解释 SQL 语句的意图、功能和影响。
2. 库、表、列名应该使用小写字母，并使用下划线（_）或驼峰命名法。
3. 库、表、字段名应该简洁明了，具有描述性，反映其所存储数据的含义。
4. 库名应于对应的程序名一致 例如：程序名为 EcommercePlatform 数据库名命名为ecommerce_platform
5. 表命名最好是遵循 “业务名称_表” 的作用 例如：alipay_task、force_project、trade_config
6. 列名应遵循 “表实体_属性” 的作用 例如：product_name或productName

DDL 数据定义语言

包括：库、表、索引、视图等数据库对象的创建、修改和删除。

库管理——创建库

创建库（必须指定库名，可能指定字符集或排序方式）
Mysql8 默认值：字符集（utf8mb4 支持各种语言字符），排序规则（utf8mb4_0900_ai_ci不区分大小写的排序规则）

• 创建数据库，使用默认的字符集和排序方式

create database 数据库名;

• 判断并创建默认字符集数据库（推荐）

create database if not exists 数据库名;

• 创建数据库指定字符集

create database 数据库名 character set 字符集;

• 创建数据库指定排序方式（字符串在数据库里，谁和谁相等？谁排在谁前面）

create database 数据库名 collate 排序方式;

• 创建数据库指定字符集和排序方式

create database 数据库名 character set 字符集 collate 排序方式;

• 查看默认字符集和排序方式

show variables like 'character_set_database';
show variables like 'collation_database';

• 创建ddl_d1库,指定字符集为utf8,且排序方式用大小写敏感的utf8mb4_0900_as_cs模式

CREATE DATABASE IF NOT EXISTS ddl_d1 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

库管理——查看和使用库

• 查看所有库

show databases;

• 查看当前使用库

select database();

• 查看库下所有表

show tables from 数据库名;

• 查看创建库的信息和语句

show create database 数据库名;

• 选中和切换库

use 数据库名;

库管理——修改库

• 修改字符集

alter database 数据库名 character set 字符集;

• 修改排序方式

alter database 数据库名 collate 排序方式;

• 修改字符集和排序方式

alter database 数据库名 character set 字符集 collate 排序方式;

注意: 数据库不能修改名称,如果你想改名字,备份数据,删除旧库,创建新库,恢复数据即可!

库管理——删除库

• 直接删除

drop database 数据库名;

• 判断并删除

drop database if exists 数据库名;

表管理——创建表

建表事项:

1. 表名 列名 列类型必须填写的
2. 推荐使用if not exists
3. 注释不是必须得,但是是很有必要的!
4. 列之间使用,隔开,最后一列没有,

   create table [if not exist] 表名(
     # 列的信息
     列名 类型 [列的约束] [列的注释],
     列名 类型 [列的约束] [列的注释],
     ...
     列名 类型 [列的约束] [列的注释]
   )[描述][注释]

• demo：创建一个图书表books，判断不存在再创建，手动设置books表字符集为utf8mb4，添加表注释内容 图书表books中有以下列：图书名称book_name列,类型为varchar(20)，添加注释。图书价格book_price列,类型为double(4,1)，添加注释。图书数量book_num列,类型为int，添加注释。

CREATE TABLE IF NOT EXISTS books(
  # 列的信息
  book_name VARCHAR(20) COMMENT '图书名',
  book_price DOUBLE(4,1) COMMENT '图书价格',
  book_num INT COMMENT '图书数量'
)CHARSET = utf8mb4 COMMENT '图书表';

建表字段类型:

1. 整数类型(类型,占有空间,范围)

标准sql:

 int / integer   4字节  无符号 0 - 2^32-1  有符号 -2^31 - 2^31 -1    
 smallint        2字节  无符号 0 - 2^16-1  有符号 -2^17 - 2^17 -1

mysql方言:

 tinyint         1字节  无符号 0 - 2^8 -1  有符号 -2^7 - 2^7-1
 mediumint       3字节  无符号 0 - 2^24 -1  有符号 -2^23 - 2^23-1
 bigint          8字节  无符号 0 - 2^64 -1  有符号 -2^63 - 2^63-1

注意: 选合适范围,范围合适先占有空间最小的!

• 创建一个t1表，包含: 年龄和学号(范围不确定,但是没有负值)

CREATE TABLE t1(
   t1_age TINYINT UNSIGNED COMMENT '年龄,无符号,范围就是 0 - 255',
   t1_number BIGINT UNSIGNED COMMENT '学号,最大的,且没有负号'
)

2. 浮点类型 使用对比: 精度要求不高,例如:身高,体重 float / double，精度要求特别高,钱、工资,价格decimal

stu_height float(4,1) unsigned COMMENT '身高，保留一位小数，多位会四舍五入',

3. 字符串类型
   差异：
4. 文本类型 在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。 开发经验：
   短文本、固定长度使用：char，例如：性别，手机号短文本
   非固定长度使用 varchar，例如：姓名，地址
   大文本，建议存储到文本文件，使用 varchar 记录文件地址，不使用 TEXT，直接存储大文本，他性能非常较差！
5. 时间类型 创建t2表，注册日期 字段插入自动添加时间,更新数据不变。
   更新日期 字段插入自动添加时间,更新数据时间改变。

CREATE TABLE t2(
  reg_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册日期,插入数据自动维护时间',
  up_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT  '更新日期,插入数据填写时间,更新数据自动改变时间'
)

表管理——修改和删除表

• 添加列

alter table 表名 add 列名 类型 [first|alter 列名] ;

• 修改列名

alter table 表名 change 原列名 新列名 新类型 [first|alter 列名] ;

• 修改列类型

alter table 表名 modify 列名 新类型 [first|alter 列名] ;

• 删除列

alter table 表名 drop 列名;

• 修改表名

alter table rename [to] 新表名;

• 删除表

drop table [if exists ] 表名;

• 清空表数据

truncate  table 表名;

DML 数据操作语言

数据操作语言——插入

数据操作最基本单位“行”

• 全列插入[不推荐]

insert into 表名 values | value (值,值,值...) 

• 指定列插入[推荐]

insert into 表名 (列名,列名...) values | value (值,值,值...) 

• 多行插入

insert into 表名 (列名,列名...) values | value (值,值,值...) ,(值,值,值...),(值,值,值...)
insert into 表名 values | value (值,值,值...) ,(值,值,值...),(值,值,值...)

• 插入一名学生的所有信息，包括学号、名字、年龄、生日和身高

INSERT INTO students VALUES (1,'二狗子',18,'1990-06-06',185.5);
INSERT INTO students(stu_name,stu_age,stu_birthday,stu_height,stu_id) VALUES ('二狗子',18,'1990-06-06',185.5,2);

• 插入一名学生的学号、名字、年龄，其他列使用默认值

INSERT INTO students (stu_id,stu_name,stu_age) VALUES (3,'驴蛋蛋',29);

• 插入两名学生的信息，包括学号、名字、年龄、生日和身高

INSERT INTO students VALUES (4,'狗剩子',20,'2020-02-20',223.5),(5,'石头子',18,'2020-02-20',223.5);

• 插入一名学生的信息，只提供学号、名字和年龄，其他列为空值

INSERT INTO students(stu_id,stu_name,stu_age,stu_birthday,stu_height) VALUES (6,'小笨蛋',20,NULL,NULL);

数据操作语言——修改

• 全表修改(全行修改)

update 表名 set 列名 = 新值 , 列名 = 值 , 列名 = 值 ...

• 条件修改(条件筛选行)

update 表名 set 列名 = 新值 , 列名 = 值 , 列名 = 值 ... where 条件

• 将学号为8的学生的姓名改为'黄六'

UPDATE students SET stu_name = '黄六' WHERE stu_id = 8;

• 将年龄小于20岁的学生的身高增加2.0

UPDATE students SET stu_height = stu_height + 2 WHERE stu_age < 20; # - * / 可以使用原列进行运算

• 将学号为11的学生的生日修改为'2003-07-10',且年龄改成21

UPDATE students SET stu_birthday = '2003-07-10' , stu_age = 21 WHERE stu_id = 11;

• 将所有学生的年龄增加1岁

UPDATE students SET stu_age = stu_age + 1;

数据操作语言——删除

• 全表删除

delete from  表名;

• 条件行删除

delete from 表名 where 条件;

• 将年龄大于23的学员移除

DELETE FROM students WHERE stu_age > 23;

• 将身高高于200且学号大于10的数据移除

DELETE FROM students WHERE  stu_height > 200 AND stu_id > 10;

• 将身高高于200或学号大于10的数据移除

DELETE FROM students WHERE  stu_height > 200 OR stu_id > 10;

• 将所有学生数据移除

DELETE FROM students;

DQL 数据查询语言

DQL 会基于原表数据查询出一个虚拟表

非表查询

利用select关键字，快速输出一个运算结果或者函数，类似 java 控制台输出。

• 查询当前时间

SELECT NOW();

指定表查询结果

• 查询的时候,结果来自于某一张表或者多表!

select 表名.列名, 列名, 列名 from 参照的表名     # 表名.列名 多表的时候,需要这么写,多表的列名可能重复!

• 查询全部员工信息

SELECT * FROM t_employee;
等价于
SELECT t_employee.* FROM t_employee;

• 查询全部员工姓名和工资

SELECT ename,salary FROM t_employee;

• 查询列并且起别名

select 列名 as 别名 , 列名 别名 from 表名

• 查询全部员工姓名和工资,名字显示为name名称

SELECT ename AS NAME,salary FROM t_employee;
等价于
SELECT ename NAME,salary FROM t_employee;

• 去掉重复行数据

select distinct 列名, 列名 from 表名;

• 查询员工的性别种类

SELECT DISTINCT gender FROM t_employee;

• 查询常数列：人为制造的一个值和一个列 (多个值,多个列)

select 列名, '值' as 列名 from 表名;

• 查询员工姓名和工资,并且添加国家为中国

SELECT ename NAME,salary,'中国' country FROM t_employee;

• 查询所有员工姓名，月薪和年薪（年薪等于月薪*12,结果列字段为:姓名,月薪,年薪 ）

SELECT ename 姓名,salary 月薪 , salary * 12 AS 年薪 FROM t_employee;

• 查询所有员工姓名，月薪，每月奖金，每月总收入（结果列字段为:姓名,月薪,奖金.月总）
  因为有些员工没有奖金, 奖金占比就是null, null 运算 任何值 = null
  ifnull(列,为null你给与的默认值) 0

SELECT ename  姓名 , salary  月薪 , salary * commission_pct 奖金 , salary +  salary * commission_pct 月总收入 FROM t_employee;
正确写法如下：
SELECT ename  姓名 , salary  月薪 , salary * IFNULL(commission_pct,0) 奖金 , salary +  salary * IFNULL(commission_pct,0) 月总收入 FROM t_employee;

• 显示表结构

DESC t_employee;

过滤数据（条件查询）

• 查询工资高于9000的员工信息

SELECT * FROM t_employee WHERE salary > 9000;

• 查询工资高于8000且性别为女的员工信息

SELECT * FROM t_employee WHERE salary > 8000 AND gender = '女';

算数运算符

注意:
     1. / 浮点除法 div  整数除法
     2. /0 不会抛出异常,结果是null
     3. 和浮点类型进行运算,结果也是浮点类型
     4. 优先级和之前一样,提升优先级 使用()

• 查询薪资奖金和大于20000的员工信息

SELECT * FROM t_employee WHERE salary + IFNULL(commission_pct,0) * salary > 20000;

• 查询员工编号是偶数的员工信息

SELECT * FROM t_employee WHERE eid % 2 = 0;

比较运算符

注意：
    等于对比 = 不能做空判断  null = null -> false    null <=> null -> true (方言) -> is null
    不等于对比 <> 标准语法  != mysql方言
    大于,小于,大于等于,小于等于 > >= < <=
    空值处理 is null  is not null 
    区间比较 between min and max   not between min and max    1 5   -> 1 2 3 4 5 
    范围比较  key in(x,y,z) -> key =x or key = y or key = z    not in 
    模糊匹配  like  | not like -> key  like '赵%'   key like '_伟%'

• 查询有奖金的员工信息

SELECT * FROM t_employee WHERE commission_pct IS NOT NULL;

• 查询出生日期在 1990-01-01 和 1995-01-01 之间的员工信息

SELECT * FROM t_employee WHERE birthday BETWEEN '1990-01-01' AND '1995-01-01';

• 查询手机号码以 '138' 开头的员工信息

SELECT * FROM t_employee WHERE tel LIKE '138________'
SELECT * FROM t_employee WHERE tel LIKE '138%'

• 查询邮箱以 '@qq.com' 结尾的员工信息

SELECT * FROM t_employee WHERE email LIKE '%@@qq.com'

• 查询地址为NULL的员工信息

SELECT * FROM t_employee WHERE address IS NULL;

• 查询工作地点在 '北京'、'上海' 或 '深圳' 的员工信息。

SELECT * FROM t_employee WHERE work_place IN ('北京','上海', '深圳' )

• 查询出生日期不在 '1980-01-01' 和 '2000-01-01' 之间的员工信息。

SELECT * FROM t_employee WHERE birthday NOT BETWEEN '1990-01-01' AND '1995-01-01';

逻辑运算符

• 查询薪资大于5000并且工作地点为'北京'的员工信息。

SELECT * FROM t_employee WHERE salary > 5000  AND  work_place LIKE '%北京%';

# FIND_IN_SET('值',列名) -> 值是否出现: 出现 1 不出现0 ;
SELECT ename , FIND_IN_SET('北京',work_place) FROM t_employee;
SELECT * FROM t_employee WHERE salary > 5000 AND FIND_IN_SET('北京',work_place) = 1;

• 查询奖金比例为NULL或者地址为NULL的员工信息

SELECT * FROM t_employee WHERE commission_pct IS NULL OR address IS NULL;

• 查询性别为'男'并且工作地点不为'上海'的员工信息。

SELECT * FROM t_employee WHERE gender = '男' AND FIND_IN_SET('上海',work_place) = 0;

• 查询薪资大于5000且工作地点为'北京'或者'上海'的员工信息。

SELECT * FROM t_employee WHERE  salary > 5000 AND ( FIND_IN_SET('上海',work_place) = 1 OR FIND_IN_SET('北京',work_place) = 1 );

运算符优先级和结合性

在MySQL中，圆括号（）被认为是最高优先级的运算符。使用圆括号可以改变其他运算符的优先级！

SELECT * FROM TABLE WHERE (a + b) * c > d;

单行函数/数值函数

SELECT ABS(-5),CEIL(2.3),CEIL(-2.3), 
FLOOR(2.3), FLOOR(-2.3), TRUNCATE(RAND(),2),
RAND(8),RAND(8), ROUND(2.3),ROUND(2.36,1),TRUNCATE(2.36,1);

单行函数/字符串函数

SELECT CHAR_LENGTH('abc'),CONCAT('%','娃娃','%'), FIND_IN_SET('aa','cc,dd,aa,bb,gg,aa');

单行函数/时间函数

单行函数/流程函数

if(表达式,true_value,false_value) if 函数处理有两个结果的流程

• 如果生日1990年之前，则薪资涨幅为当前薪资的 10%，否则为 5%

SELECT ename ,salary , birthday , ROUND(IF(YEAR(birthday)>1990, salary * 1.05 ,salary*1.1),1) AS newSalary FROM t_employee;

ifnull(列名,null_value) 处理列等于null的时候,我们赋予其他的默认值 null_value

• 查询员工的姓名和工资以及奖金数额

SELECT ename,salary, salary * IFNULL(commission_pct,0.05) AS 奖金额度 FROM t_employee;

case 关键字: 处理多流程结果
第一种语法: case when 表达式 then result1 when 表达式2 then result2 ... else default_value end [as 别名]

SELECT ename,gender,commission_pct ,
       CASE 
          WHEN gender = '男' THEN 2000*IFNULL(commission_pct,0.1)
          WHEN gender = '女' THEN 3000*IFNULL(commission_pct,0.1)
          ELSE 0
       END  AS 补助金额1
       FROM t_employee;   

第二种语法: case 列名 | 表达式 when 值 then result when 值 then 结果 ... else default_value end ...

SELECT ename,gender,commission_pct ,
       CASE gender
          WHEN '男' THEN 2000*IFNULL(commission_pct,0.1)
          WHEN '女' THEN 3000*IFNULL(commission_pct,0.1)
          ELSE 0
       END AS 补助金额2
       FROM t_employee; 

多行函数/聚合函数

单行函数是作用到单条查询结果中，多行函数是整体的处理。

• 求平均工资,最小和最大工资以及总工资

SELECT AVG(salary),MIN(salary),MAX(salary),SUM(salary) FROM t_employee;   

• 求最大年龄和最小年龄的员工生日

SELECT MIN(birthday) AS 年龄最大员工的生日 , MAX(birthday) AS 年龄最小的员工生日 FROM t_employee;

• 求总员工数和有奖金的员工数

SELECT COUNT(*) , COUNT(1) , COUNT(commission_pct) FROM t_employee;

分组查询

• 将数据行分成多个小组，最终统计的是组的特性和数据( 分组列,聚合函数)。group by 分组列,分组列...having 分组后的比较
• 分组查询的就是 分组字段 和 聚合函数
• having是分组后的条件，where是分组前的条件,having比较一般(99%)都是聚合函数 where可以是任何参数的比较
• 查询每种性别的员工数量以及性别平均工资

# 先按照性别gender列分组,分组以后统计分组特性列和聚合函数即可
# 先 GROUP BY 分组，再在每个分组上计算 COUNT(*)、AVG(salary) 等聚合函数。
SELECT gender , COUNT(*) ,AVG(salary) FROM t_employee GROUP BY gender;

• 查询生日年份、性别相同的人数和平均工资

SELECT YEAR(birthday),COUNT(*) , AVG(salary) FROM t_employee GROUP BY YEAR(birthday), gender;

• 查询工资高于5000，每种性别的员工数量以及性别平均工资

SELECT gender , COUNT(*) ,AVG(salary) FROM t_employee WHERE salary > 5000 GROUP BY gender;

• 查询平均工资高于11000的性别和性别人数

# 按照性别分组,分组以后筛选平均工资大于5000的组,平均工资大于5000是分组后的条件 having
SELECT gender , COUNT(*) ,AVG(salary) FROM t_employee GROUP BY gender HAVING AVG(salary) > 11000;

排序查询

• 语法：order by 列名 asc | desc , 列名 asc | desc
• 细节1: 多列排序,只有上一列相同,第二列才会生效!
• 细节2: asc正序 从小到大 desc 倒序 从大到小
• 细节3: asc 默认值 可以不写 order by price , xxx desc;
• 按照年龄正序排序，查询员工信息(从小到大)

SELECT * FROM t_employee ORDER BY birthday DESC;

• 按照工资倒序，查询员工信息

SELECT * FROM t_employee ORDER BY salary DESC;

• 按照工资倒序，如果工资相同，按照年龄正序排序查询员工信息(从小到大 生日从大到小)

SELECT * FROM t_employee ORDER BY salary DESC , birthday DESC;

• 查询有奖金的员工(where)，最终按照工资倒序显示员工信息

SELECT * FROM t_employee WHERE commission_pct IS NOT NULL ORDER BY salary DESC;

分页查询

• 语法 limit [offset偏移量 , ] number;
• offset 可以省略,偏移量 0 的时候,从头开始查询 limit 0 , 5 == limit 5;
• limit 10; 但是数据库一共有5条数据! 只会返回5条
• limit关键字真的影响数据, limit放在select语句的最后!
• 分页公式推算如下：

已知 page(当前页从1开始) size(页容量)
limit -> 分页查询 -> 前端会传递 page 和 size ->后台limit切割显示
limit (page-1)*page,size;

select 执行过程

关键字的书写顺序: select 列名 from 表 where 条件 and | or 条件 group by 分组字段,分组字段 having 分组后条件 order by 列名 asc | desc , 列名 asc | desc limit offset,number;

关键字执行的顺序: from 数据源 where 筛选前置条件 group by 分组 having 分组后的数据过滤 select 显示列的信息 order by 排序 limit 切割!

数据库约束

确保数据的准确性、可靠性、正确性。
添加时机：创建表时添加，创建表后ALTER TABLE语句添加。

数据库约束——列级约束

限定某个 字段/列 的值，不允许为空，关键字NOT NULL

• 默认：所有类型列默认都可以为 null，包括数字类型。
• 列上添加：非空约束只能添加到列上
• 多次使用：一个表中可以有很多列进行非空限定
• 空值判定：空字符串不是 null,0 也不是 null
• 建表时添加

CREATE TABLE 表名称 (
    字段名 数据类型，
    字段名 数据类型 NOT NULL,
    字段名 数据类型 NOT NULL
);

CREATE TABLE emp1( 
    id INT(10) NOT NULL, 
    NAME VARCHAR(20) NOT NULL, 
    sex CHAR(1) 
);

• 建表后修改

alter table 表名称 modify 字段名 数据类型 not null;

alter table emp1 modify sex char(1) not null;

数据库约束——列级约束（默认值约束）

限定某列/某个字段的添加默认值。

• 位置约定：默认值约束不能添加到唯一或者主键上，其他列都可以。
• 生效时机：当插入数据时，没有显示赋值，赋予默认值！
• 细节特点：添加约束时，default 默认值，默认值对应正确数据类型！

# 建表时添加
CREATE TABLE emp2( 
    id INT(10) default 0,
    NAME VARCHAR(20) NOT NULL default '二狗子');

# 建表后添加
alter table emp2 modify name varchar(20) default '' not null; #给gender字段增加默认值约束 
alter table emp2 modify tel char(11) default ''; #给tel字段增加默认值约束

数据库约束——行级约束（主键约束）

• 自定义主键：人为的创建一列，专门用来做主键，它的使命就是保证行数据不重复！例如：学号，身份证号等等
• 自然主键：不是人为创建的列，数据实体自带的属性列，当前环境下，此属性列唯一且不为空，可以做主键，但是它有两个使命：第一，承担实体属性值 第二，作为表的主键列！例如：DNA 序列等等
• 主键数量：每个表中只能有一个主键
• 单一和复合：主键可以由单个列或者多个列构成（复合主键）。
• 主键列类型：可以是任意类型，只要唯一且不重复即可。
• 主键命名：主键一般采用identify(标识)单词缩写xxid|xx_id等，但没有强制要求。
• 主键索引：创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据 主键查询的，就根据主键查询，效率更高)。如果删除主键约束了，主键约束对应的索引就自动 删除，主键索引固定命令：PRIMARY。

# 建表时添加
create table 表名称(
    字段名 数据类型 primary key, #列级模式
)
create table 表名称(
    字段名 数据类型，
    [constraint 约束名] primary key(字段名)#表级模式
)

# 建表后添加
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表)；
#字段列表可以是一个字段，也可以是多个字段

# 删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在，唯一消失。
#删除主键约束和索引
alter table 表名称 drop primary key;

数据库约束——行级约束（自增长约束）

限定某个整数类型字段，插入数据不显示维护，值自动增长！

• 添加位置：只能添加到键列（主键，唯一），普通列不可以。
• 约束数量∶每一张表只能有一个自增长约束。
• 数据类型：增加自增长约束的列必须是整数类型。
• 特殊情况：如果给自增长字段设置0或者null,列数据会自增长赋值，如果设置的是非零和非空数据，那么将真实设置值！

create table 表名称(
    字段名 数据类型 primary key auto_increment,
);
create table 表名称(
    字段名 数据类型 unique key auto_increment,
);

alter table 表名称 modify 字段名数据类 auto_increment;

# 删除
#alter table 表名称 modify 字段名 数据类型 auto_increment;
#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型；
#去掉auto_increment相当于删除

参照引用（外键约束）

• 外键：引用或参照其他表主键列值的列，称为外键，外键的值范围应当对应引用主键的值范围
• 外键约束：外键应该引用主键的值，但是如果不添加约束，可能会出现错误数据，例如：MySQL数据，外键约束确保，外键必须且正确引用主键的值的限制！
• 外键数量：每个表中可以包含多个外键。
• 外键跨表：外键是跨表引用其他表的主键，被引用为主表（学生），外键表为子表（分数）。
• 外键类型：外键类型不能是任意类型，应该和主键类型对应，尽量命名相同！
• 主外键关系：关系型数据库，关系指的就是主外键关系，有主外键的两张表能水平联查。
• 其他影响：存在主外键关系（外键约束），删除主表数据时，可能会因为子表引用而删除失败。可 以先删除子表的所有引用数据再删除！

#建表时添加
create table 主表名称(
    字段1 数据类型 primary key
);

#子表中添加主外键约束
create table 子表名称(
    字段1 数据类型 primary key,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY(外键) references 主表名（主键） [on update xx][on delete xx]
):

# 建表后修改
alter table 从表名 add [CONSTRAINT 约束名] FOREIGN KEY(从表的字段) references 主表名（被引用字段） [on update xx][on delete xx];

最好是采用： ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
示例：当主表中的行被删除或更新时，从表中的相关行也会被相应地删除或更新。

ALTER TABLE
从表名
ADD
CONSTRAINT
外键约束名
FOREIGN KEY （从表外键列）
REFERENCES
主表名（主表主键列）
ON DELETE CASCADE
ON UPDATE
CASCADE:

数据库约束——多表级约束

• 一对一：两个表之间的每行数据都是唯一的对应关系。例如，一个员工与其唯一的员工档案。

# 员工表
CREATE TABLE emp(
    e_id  INT PRIMARY KEY AUTO_INCREMENT,
    e_name  VARCHAR(20) NOT NULL,  
    e_age  INT DEFAULT 18,
    e_gender  CHAR DEFAULT '男'
);

# 正常存在外键,外键添加unique唯一约束
CREATE TABLE profile1(
    p_id  INT PRIMARY KEY AUTO_INCREMENT,
    p_address VARCHAR(100) NOT NULL,  
    p_level  INT  DEFAULT 10,
    e_id INT UNIQUE ,  # 外键唯一
    # CONSTRAINT s_p_1 约束起名：s_p_1 
    CONSTRAINT s_p_1  FOREIGN KEY(e_id) REFERENCES emp(e_id) 
);

或者

外键直接当主键
CREATE TABLE profile2(
    e_id  INT PRIMARY KEY,
    p_address VARCHAR(100) NOT NULL,  
    p_level  INT  DEFAULT 10,
    CONSTRAINT s_p_2  FOREIGN KEY(e_id) REFERENCES emp(e_id) 
);

• 一对多：一个表关联另一个表多行数据，反方向只关联一行数据。例如，一个作者与多个文章的关系。

# 一对多: 作者和文章表
CREATE TABLE author(
    a_id  INT PRIMARY KEY AUTO_INCREMENT,
    a_name  VARCHAR(20) NOT NULL,  
    a_age  INT DEFAULT 18,
    a_gender  CHAR DEFAULT '男'
);

CREATE TABLE blog(
    b_id  INT PRIMARY KEY AUTO_INCREMENT,
    b_title VARCHAR(100) NOT NULL,  
    b_content VARCHAR(600) NOT NULL,  
    a_id INT ,  # 外键
    CONSTRAINT a_b_fk  FOREIGN KEY(a_id) REFERENCES author(a_id) 
);

• 多对多：两个表中的记录都可以与对方表中的多个记录相关联。例如，学生和课程之间的关系，一个学生可以选修多门课程，而一门课程也可以由多个学生选修。

# 多对多: 学生和课程表
CREATE TABLE student(
    s_id  INT PRIMARY KEY AUTO_INCREMENT,
    s_name  VARCHAR(20) NOT NULL,  
    s_age  INT DEFAULT 18
);


CREATE TABLE student_course(
    sc_id  INT PRIMARY KEY AUTO_INCREMENT,
    s_id INT,  
    c_id  INT,
    FOREIGN KEY(s_id) REFERENCES student(s_id) ,
    FOREIGN KEY(c_id) REFERENCES course(c_id) 
);

CREATE TABLE course(
    c_id  INT PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(10) NOT NULL,  
    c_teacher  VARCHAR(10) 
);

多表查询

多表查询（水平合并）

• 实现要求：只要求合并的结果集之间的列数和对应列的类型相同即可
• 主外键要求：union只是结果集垂直汇总，不涉及行数据水平连接，不要求有主外键
• 重复数据认定：一行中的所有列值都相同，认定为重复行

#数据准备
CREATE TABLE a(
    aid INT,
    aname VARCHAR(10)
)
CREATE TABLE b(
    bid INT,
    bname VARCHAR(10)
)

INSERT INTO a VALUES(1,'aaaa'),(2,'bbbb'),(3,'cccc');
INSERT INTO b VALUES(4,'aaaa'),(2,'bbbb'),(3,'cccc');

#去重复合并
SELECT aid, aname FROM a
UNION
SELECT bid ，bname FROM b;

#不去重复合并
SELECT aid, aname FROM a
UNION ALL
SELECT bid ，bname FROM b;

多表查询（垂直合并）

• 核心要求：水平连接是行和行数据连接，要求两个表必须有关系（主外键）
• 正确连接：为了正确的将行数据之间连接，水平连接需要额外判定主外键相等

多表查询（内连接）

内连接会根据指定的连接条件，将两个表中满足条件的行进行匹配，并返回匹配成功的行。

# 语法
select * from 表1 [ inner ] join 表2 on 表1.主键 =表2.外键（标准）
select* from 表1，表2 where 表1.主键=表2.外键（非标准）

多表查询（外连接）

• 左连接（LEFT JOIN） ：以左表为主，左表的数据一定全部保留
• 右连接（RIGHT JOIN） ：以右表为主，右表的数据一定全部保留
  没匹配上的那一边，用 NULL 补。

select* from 表1 left [ outer] join 表2 on 表1.主键=表2.外键（左外）
select * from 表1 right [ outer] join 表2 on 表1.主键= 表2.外键（右外）

• left 和 right 就是指定左或者右是逻辑主表。
• 建议将分析的逻辑主表放在第一个位置（最左），那么本次查询必然全部是左外连接。 from 逻辑主表 left join 表2 on 主 =外 left join 表3 on 主 =外 left join ..

e_id	name
1	张三
2	李四
3	王五

e_id	salary
1	10k
2	12k
4	8k

左连接：SELECT * FROM emp LEFT JOIN salary ON emp.e_id = salary.e_id;

emp.e_id	name	salary.e_id	salary
1	张三	1	10k
2	李四	2	12k
3	王五	NULL	NULL

右连接：SELECT * FROM emp RIGHT JOIN salary ON emp.e_id = salary.e_id;

emp.e_id	name	salary.e_id	salary
1	张三	1	10k
2	李四	2	12k
NULL	NULL	4	8k

• 内连接和外连接对比

类型	保留谁	常见用途
INNER JOIN	只要能匹配的	两边都必须有
LEFT JOIN	左表全部	主表 + 附加信息
RIGHT JOIN	右表全部	辅助表为主

-- 右连接
A RIGHT JOIN B
-- 等价于
B LEFT JOIN A

子查询

先执行里面的查询，把结果当成“条件 / 数据源”，再给外层查询用

• 找 “工资高于平均工资” 的员工

e_id	name	salary
1	张三	8k
2	李四	10k
3	王五	6k

SELECT * FROM emp
WHERE salary > (
    SELECT AVG(salary)
    FROM emp
);

• 子查询形态

# 返回 单个值（标量子查询）
WHERE salary > (SELECT AVG(salary) FROM emp)

# 返回 一列多行
SELECT *
FROM emp
WHERE dept_id IN (
    SELECT dept_id
    FROM dept
    WHERE dept_name = '技术部'
);

# 多行多列
SELECT *
FROM emp
WHERE (dept_id, salary) IN (
    SELECT dept_id, MAX(salary)
    FROM emp
    GROUP BY dept_id
);