概述
SQL:一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准
关系型数据库 (RDBMS)
关系型数据库管理系统（RDBMS）是基于关系模型的数据库管理系统。它由多个相互关联的二维表组成，这些表通过某些共有的字段相互连接。
二维表：
有行有列的就是二维表
SQL的使用
通用语法：
- SQL语句可以单行或多行书写，以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写。
注释:
- 单行注释:
-- 注释内容
或
# 注释内容
//(MySQL特有)
- 多行注释:
/* 注释内容 */
- show databases;
/* show databases; */
SQL分类
分为以下四大类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象（数据库，表，字段） |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
创建数据库
create database db01;
//这个db01就是数据库的名字
然后我们就会发现mysql的安装目录下的data里就有这个文件夹了
DDL
DDL英文全称是 Data Definition Language，数据定义语言，用来定义数据库对象(数据库、表)。
数据库操作
查询
- 查询所有数据库：
show databases;
- 查询当前数据库：
select database();
-- 这玩意就是返回正在use的db
-- 有时候可能对当前数据库操作的太多了
-- 已经不知道操作的是哪个db了
-- 所以就可以用这个方式返回
使用
- 使用数据库：
use 数据库名;
创建
- 创建数据库：
create database [if not exists] 数据库名;
-- [if not exists] 是个可选项
-- 输入了之后 如果存在同名数据库的话 就不会创建了 也不会报错
删除
- 删除数据库：
drop database [if exists] 数据库名;
-- [if not exists] 是个可选项
-- 输入了之后 如果不存在指定名称数据库的话 就不会删除了 也不会报错
上述语法中的database，也可以替换成schema。如:create schema db01;
数据类型
MySQL中的数据类型有很多，主要分为三类:数值类型、字符串类型、日期时间类型
数值类型
其中，注意范围分为有符号和无符号范围
以tinyint为例，当没有赋值得时，默认为有符号
如果想定义为无符号：
tinyint unsigned
字符串类型
重点看char和varchar，比较常用
定长字符串和变长字符串区别
- char(10): 最多只能存10个字符,不足10个字符,占用10个字符空间（性能高，浪费空间）
- 这玩意就比较适合存固定长度的数据，比如说手机号啥的
- varchar(10): 最多只能存10个字符,不足10个字符,按照实际长度存储（性能低，节省空间）
- 这玩意就适合存可以变的数据，比如username啥的
日期类型
date和datetime比较常用
表结构操作（创建、查询、修改、删除）
创建表
将以下表创建的代码
代码：
create table tb_user(
id int comment 'ID 唯一标识',
username varchar(20) comment '用户名',
name varchar(10) comment '名字',
age int comment '年龄',
gender char(1) comment '性别'
)comment '用户表';
注意在运行的时候别忘记选中指定的数据库
效果如下（使用的是idea来展示数据库）
表头就出来了！
但是我们想让这个id成为我们的主键（也就是唯一标识）
不过目前还不是，咱写的那个comment就是个注释，没屌用
咋办呢？引入约束！
约束
- 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
- 目的：保证数据库中数据的正确性、有效性和完整性。
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段值不能为null | not null |
| 唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key (auto_increment自增) |
| 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 | default |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | foreign key |
我们就用这几个约束来满足以下条件：
id: 唯一标识（自增
username：非空且唯一
name：非空
age： 空着那里就行
gender：默认约束 默认为男
代码如下：
（运行之前别忘记把刚才的table给drop了）
create table tb_user(
id int primary key auto_increment comment 'ID 唯一标识',
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '名字',
age int comment '年龄',
gender char(1) default '男' comment '性别'
)comment '用户表';
可以看到id那里有个小钥匙了~~~
简单的小小的练习案例
要求如下：
我们利用可视化工具创建：
注意！ 在这类后台管理系统的表结构中，额外添加两个通用字段：
创建时间 和 修改时间
- 创建时间：记录这条数据插入的时间
- 修改时间：每次修改都要更新一下这个时间，也就是最后更新时间
创建时间：
修改时间：
反正重点就是在需求文档里面以外需要三个额外字段：
- id：主键 自增
- create_time：创建时间
- update_time：最后更新时间
表操作
查询
- 查询当前数据库所有表:
show tables; - 查询表结构:
desc 表名; - 查询建表语句:
show create table 表名:
修改
- 添加字段:
alter table 表名 add 字段名 类型(长度)[comment 注释][约束]; - 修改字段类型:
alter table 表名 modify 字段名 新数据类型(长度); - 修改字段名和字段类型:
alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释“[约束]; - 删除字段:
alter table 表名 drop column 字段名; - 修改表名:
rename table 表名 to 新表名;
删除
删除表:drop table [if exists] 表名;
DML
DML英文全称是Data Manipulation Lanquage(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。
添加数据（INSERT)
写段代码试试：
insert into tb_emp (username,name,gender) values ('wuji','zhangwuji',1);
就是在username,name,gender添加新的值
👆注意！字段和列表得值必须一一对应
运行一下试试👇
报错了，为啥呢
因为create_time是非空字段哦，还记得吗
还有update_time也是非空字段
所以不能落下他们
问题来了，怎么获取现在的时间呢？
——now()
insert into tb_emp (username,name,gender,creat_date,update_time) values ('wuji','zhangwuji',1,now(),now());
这段代码就能跑了
更新数据（update)
修改数据:
update 表名 set 字段名1=值1,字段名2=值2,…[where 条件];
-- 当没有where的时候就是对所有行操作
来试试：
- 将 tb emp 表的ID为1员工 姓名name字段更新为’张三:
update tb_emp set name = '张三' , update_time = now() where id = 1;
还记得吗，每次更新数据还要对 update_time修改哦
删除操作（delete）
DQL（数据查询语言）✳✳✳
DQL的全称是Data Query Language（数据查询语言），主要用于查询数据库表中的记录。
关键字：SELECT
语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
测试数据
-- 员工管理(带约束)
create table tb_emp (
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
entrydate date comment '入职时间',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time) VALUES
(1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),
(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),
(17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33', '2022-10-27 16:36:07'),
(18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
(19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
(20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
(21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
(22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
(23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
(24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
(25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
(26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
(27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
(28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
(29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');
基础查询
- 查询指定字段
name,entrydate并返回
SELECT name, entrydate FROM tbemp;
- 查询返回所有字段
SELECT id, username, password, name, gender, image, job, entrydate, create_time, update_time FROM tbemp;
-- 推荐
注意：这个方法有个巧用：
就是输入SELECT之后直接输入from+表格后
然后再挑选，就会有联想功能了
SELECT * FROM tbemp;
-- 不推荐（不直观，性能低）
- 查询所有员工的
name,entrydate，并起别名（姓名、入职日期）
SELECT name AS 姓名, entrydate AS 入职日期 FROM tbemp;
或者
SELECT name 姓名, entrydate 入职日期 FROM tbemp;
- 查询已有的员工关联了哪几种职位（不要重复）
SELECT DISTINCT job FROM tbemp;
条件查询
例子：
-- 1. 查询 姓名 为 杨逍 的员工
select * from emp where name = '杨逍';
-- 2. 查询在 id小于等于5 的员工信息
select * from emp where id <= 5;
-- 3. 查询 没有分配职位 的员工信息 -- 判断 null , 用 is null
select * from emp where job is null;
-- 4. 查询 有职位 的员工信息 -- 判断 不是null , 用 is not null
select * from emp where job is not null ;
-- 5. 查询 密码不等于 '123456' 的员工信息
select * from emp where password <> '123456';
select * from emp where password != '123456';
-- 6. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select * from emp where entrydate between '2000-01-01' and '2010-01-01' ;
-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select * from emp where (entrydate between '2000-01-01' and '2010-01-01') and gender = 2;
-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from emp where job = 2 or job = 3 or job = 4;
select * from emp where job in (2,3,4);
-- 9. 查询姓名为两个字的员工信息
select * from emp where name like '__';
-- 10. 查询姓 '张' 的员工信息 ---------> 张%
select * from emp where name like '张%';
-- 11. 查询姓名中包含 '三' 的员工信息
select * from emp where name like '%三%';
-- 练习 : 员工管理列表查询
-- 条件 : name , gender , entrydate
select * from emp where name like '%张%' and gender = 1 and entrydate between '2000-01-01' and '2010-01-01';
分组查询 (group by)
聚合函数
代码：
-- 聚合函数
-- 1. 统计该企业员工数量 -- count
-- A. count(字段)
select count(id) from emp;
select count(job) from emp; -- null值不参与聚合函数运算
-- B. count(*)
select count(*) from emp;
-- C. count(值)
select count(1) from emp;
-- 2. 统计该企业员工 ID 的平均值
select avg(id) from emp;
-- 3. 统计该企业最早入职的员工的入职日期
select min(entrydate) from emp;
-- 4. 统计该企业最近入职的员工的入职日期
select max(entrydate) from emp;
-- 5. 统计该企业员工的 ID 之和
select sum(id) from emp;
注意：null值不参与所有聚合函数运算
分组查询
代码：
-- 分组
-- 1. 根据性别分组 , 统计男性和女性员工的数量 -- count
select gender , count(*) from emp group by gender;
-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位 -- count
select job ,count(*) from emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;
WHERE 和 HAVING 的区别：
| WHERE | HAVING | |
|---|---|---|
| 执行时机不同 | WHERE是在分组之前进行过滤，不满足WHERE条件，不参与分组 | HAVING是在分组之后对结果进行过滤 |
| 判断条件不同 | WHERE不能对聚合函数进行判断 | HAVING可以对聚合函数进行判断 |
注意事项
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
- 执行顺序: where >聚合函数>having 。
排序查询（order by)
代码：
-- 1. 根据入职时间, 对员工进行升序排序 -- 排序条件
select * from emp order by entrydate asc ; -- 默认升序, asc可以省略的
select * from emp order by entrydate ;
-- 2. 根据入职时间, 对员工进行降序排序
select * from emp order by entrydate desc;
-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 ID 进行降序排序
select * from emp order by entrydate asc , id desc ;
注意事项：
- 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
分页查询 (limit)
代码：
公式 : 起始索引 = (页码 - 1) * 每页记录数
注意事项：
- 起始索引从0开始，起始索引=（查询页码-1） *每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。（各数据库不通用）
- 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。
案例
案例一
在员工管理的列表上方有一些查询条件：员工姓名、员工性别，员工入职时间(开始时间~结束时间)
- 姓名：张
- 性别：男
- 入职时间：2000-01-01 ~ 2015-12-31
除了查询条件外，在列表的下面还有一个分页条，这就涉及到了分页查询
- 查询第1页数据（每页显示10条数据）
基于查询的结果，按照修改时间进行降序排序
结论：条件查询 + 分页查询 + 排序查询
SQL语句代码：
-- 根据输入条件查询第1页数据（每页展示10条记录）
-- 输入条件：
-- 姓名：张 （模糊查询）
-- 性别：男
-- 入职时间：2000-01-01 ~ 2015-12-31
-- 分页： 0 , 10
-- 排序： 修改时间 DESC
select *
from tb_emp
where name like '张%'
and gender = 1
and entrydate between '2000-01-01' and '2015-12-31'
order by entrydate desc
limit 0, 10;
案例2：
查询出男性员工和女性员工各自有多少人：
引入：if(条件表达式, true取值 , false取值)
终极版：
select if(gender=1,'男性员工','女性员工') AS 性别, count(*) AS 人数
from tb_emp
group by gender;
案例3：
员工职位统计，分别统计各个职位有多少人：
select (case job
when 1 then '班主任'
when 2 then '讲师'
when 3 then '学工主管'
when 4 then '教研主管'
else '未分配职位'
end) AS 职位 ,
count(*) AS 人数
from tb_emp
group by job;
知识点：case的使用
-- case 表达式 when 值1 then 结果1
when 值2 then 结果2 ...
else result
end
多表设计
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间
相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）
- 多对多
- 一对一
一对多
以部门和员工的关系为例：
-- 部门表
create table tb_dept
(
id int unsigned primary key auto_increment comment '主键ID',
name varchar(10) not null unique comment '部门名称',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '部门表';
-- 员工表
create table tb_emp
(
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
entrydate date comment '入职时间',
dept_id int unsigned comment '部门ID', -- 员工的归属部门
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
插入测试数据：
-- 部门表测试数据
insert into tb_dept (id, name, create_time, update_time) values
(1,'学工部',now(),now()),
(2,'教研部',now(),now()),
(3,'咨询部',now(),now()),
(4,'就业部',now(),now()),
(5,'人事部',now(),now());
-- 员工表测试数据
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',1,'2007-02-01',1,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',1,'2008-08-18',1,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',2,'2012-11-01',2,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());
外键
然后建立外键
图形化界面
上面那个就是物理外键（现在已经不常用了
一对一
- 案例: 用户 与 身份证信息 的关系
- 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
当业务对基本信息的查询很高
而对身份信息的查询很低
就可以把表拆成俩 提升效率👇
实现方法：
在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
这个UNIQUE就是把一对一和一对多区分开来的关键
建立两表：
-- 用户基本信息表
create table tb_user(
id int unsigned primary key auto_increment comment 'ID',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1 男 2 女',
phone char(11) comment '手机号',
degree varchar(10) comment '学历'
) comment '用户基本信息表';
-- 测试数据
insert into tb_user values (1,'白眉鹰王',1,'18812340001','初中'),
(2,'青翼蝠王',1,'18812340002','大专'),
(3,'金毛狮王',1,'18812340003','初中'),
(4,'紫衫龙王',2,'18812340004','硕士');
-- 用户身份信息表
create table tb_user_card(
id int unsigned primary key auto_increment comment 'ID',
nationality varchar(10) not null comment '民族',
birthday date not null comment '生日',
idcard char(18) not null comment '身份证号',
issued varchar(20) not null comment '签发机关',
expire_begin date not null comment '有效期限-开始',
expire_end date comment '有效期限-结束',
user_id int unsigned not null unique comment '用户ID',
constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户身份信息表';
-- 测试数据
insert into tb_user_card values (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1),
(2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),
(3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3),
(4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);
多对多
- 案例: 学生 与 课程的关系
- 关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择
- 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
例子：
-- 学生表
create table tb_student(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
-- 学生表测试数据
insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');
-- 课程表
create table tb_course(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '课程名称'
) comment '课程表';
-- 课程表测试数据
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');
-- 学生课程表（中间表）
create table tb_student_course(
id int auto_increment comment '主键' primary key,
student_id int not null comment '学生ID',
course_id int not null comment '课程ID',
constraint fk_courseid foreign key (course_id) references tb_course (id),
constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '学生课程中间表';
-- 学生课程表测试数据
insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);
可视化：
选中三个表，右键：
多表查询
先导入一下测试数据
-- 部门管理
create table tb_dept(
id int unsigned primary key auto_increment comment '主键ID',
name varchar(10) not null unique comment '部门名称',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '部门表';
insert into tb_dept (id, name, create_time, update_time) values(1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()), (4,'就业部',now(),now()),(5,'人事部',now(),now());
-- 员工管理
create table tb_emp (
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
entrydate date comment '入职时间',
dept_id int unsigned comment '部门ID',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());
以上代码建了两个表：部门（5个） & 员工（17位）
其中员工和部门属于一对多的关系 所以员工有个外键
（此处并非物理外键
我们先尝试一下多表查询
select * from tb_dept,tb_emp;
查询结果：
部门数据是贴在员工数据前面的（图标反了
但是为啥能出如此之多的结果呢？（85条
仔细一看是一个员工前面会紧贴着5个不同的部门
实际上，这是在执行一个笛卡尔积（Cartesian product）操作。笛卡尔积的结果是第一个表的每一行与第二个表的每一行组合，这会导致结果集中有大量的行。
例子中，tb_dept表有5行数据，而tb_emp表有17行数据。所以，执行上述查询的结果将是5（部门表的行数）乘以17（员工表的行数），即85行。
但是实际上一个员工只属于一个部门，剩下的都没用
那咋整呢？
这么整👇
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
现在就能对上了嘿嘿
内连接查询
隐式内连接语法：
select 字段列表 from 表1 , 表2 where 条件 ... ;
显式内连接语法：
select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;
至于这个join干嘛用的，看下图（好叼的图
我们用的就是这个inner，主打的就是一个内查询
就是查交集这一坨
做点小练习：查询员工的姓名及所属的部门名称
- 隐式内连接实现
select tb_emp.name , tb_dept.name -- 分别查询两张表中的数据
from tb_emp , tb_dept -- 关联两张表
where tb_emp.dept_id = tb_dept.id; -- 消除笛卡尔积
- 显式内连接实现
select tb_emp.name , tb_dept.name
from tb_emp inner join tb_dept
on tb_emp.dept_id = tb_dept.id;
多表查询时给表起别名：
-
tableA as 别名1 , tableB as 别名2 ;（as可以省略）
-
tableA 别名1 , tableB 别名2 ;
使用了别名的多表查询：
select emp.name , dept.name
from tb_emp emp inner join tb_dept dept
on emp.dept_id = dept.id;
注意事项:
一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。
外连接
外连接分为两种：左外连接 和 右外连接。
左外连接语法结构：
select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
右外连接语法结构：
select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
- 案例1：查询员工表中所有员工的姓名, 和对应的部门名称
-- 左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表匹配的右边表中的数据
select emp.name , dept.name
from tb_emp AS emp left join tb_dept AS dept
on emp.dept_id = dept.id;
所以这个左外链接能显示出dept_id为null的情况(即所有员工的情况都能显示出来）
而内链接显示不出来这个情况
- 案例2：查询部门表中所有部门的名称, 和对应的员工名称
-- 右外连接
select dept.name , emp.name
from tb_emp AS emp right join tb_dept AS dept
on emp.dept_id = dept.id;
子查询
SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ... );
子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。
根据子查询结果的不同分为：
-
标量子查询（子查询结果为单个值[一行一列]）
-
列子查询（子查询结果为一列，但可以是多行）
-
行子查询（子查询结果为一行，但可以是多列）
-
表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。
常用的操作符： = <> > >= < <=
案例1：查询"教研部"的所有员工信息
可以将需求分解为两步：
- 查询 “教研部” 部门ID
- 根据 “教研部” 部门ID，查询员工信息
-- 1.查询"教研部"部门ID
select id from tb_dept where name = '教研部'; #查询结果：2
-- 2.根据"教研部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 2;
-- 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
案例2：查询在 “方东白” 入职之后的员工信息
可以将需求分解为两步：
- 查询 方东白 的入职日期
- 查询 指定入职日期之后入职的员工信息
-- 1.查询"方东白"的入职日期
select entrydate from tb_emp where name = '方东白'; #查询结果：2012-11-01
-- 2.查询指定入职日期之后入职的员工信息
select * from tb_emp where entrydate > '2012-11-01';
-- 合并以上两条SQL语句
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');
列子查询
子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。
常用的操作符：
| 操作符 | 描述 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
案例：查询"教研部"和"咨询部"的所有员工信息
分解为以下两步：
- 查询 “销售部” 和 “市场部” 的部门ID
- 根据部门ID, 查询员工信息
-- 1.查询"销售部"和"市场部"的部门ID
select id from tb_dept where name = '教研部' or name = '咨询部'; #查询结果：3,2
-- 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);
-- 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
行子查询
子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
案例：查询与"韦一笑"的入职日期及职位都相同的员工信息
可以拆解为两步进行：
- 查询 “韦一笑” 的入职日期 及 职位
- 查询与"韦一笑"的入职日期及职位相同的员工信息
-- 查询"韦一笑"的入职日期 及 职位
select entrydate , job from tb_emp where name = '韦一笑'; #查询结果： 2007-01-01 , 2
-- 查询与"韦一笑"的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');
注意一下where后面的多个属性的对应方式
表子查询
子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。
案例：查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
注意：要求查询的是员工信息及其部门信息，要把所有员工信息搞出来，所以要用外查询
分解为两步执行：
- 查询入职日期是 “2006-01-01” 之后的员工信息
- 基于查询到的员工信息，在查询对应的部门信息
select * from emp where entrydate > '2006-01-01';
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
事务
场景：学工部整个部门解散了，该部门及部门下的员工都需要删除了。
-
操作：
-- 删除学工部 delete from dept where id = 1; -- 删除成功 -- 删除学工部的员工 delete from emp where dept_id = 1; -- 删除失败（操作过程中出现错误：造成删除没有成功） -
问题：如果删除部门成功了，而删除该部门的员工时失败了，此时就造成了数据的不一致。
要解决上述的问题，就需要通过数据库中的事务来解决。
介绍
在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。
简而言之：事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。
操作
MYSQL中有两种方式进行事务的操作：
- 自动提交事务：即执行一条sql语句提交一次事务。（默认MySQL的事务是自动提交，所以我们刚才执行的删除部门以及员工其实默认是两个事务）
- 手动提交事务：先开启，再提交
事务操作有关的SQL语句：
| SQL语句 | 描述 |
|---|---|
| start transaction; / begin ; | 开启手动控制事务 |
| commit; | 提交事务 |
| rollback; | 回滚事务 |
手动提交事务使用步骤：
- 第1种情况：开启事务 => 执行SQL语句 => 成功 => 提交事务
- 第2种情况：开启事务 => 执行SQL语句 => 失败 => 回滚事务
可以看到，错误行有个红色叹号，所以这个时候就是有错误，需要rollback
四大特性
面试题：事务有哪些特性？
- 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
事务的四大特性简称为：ACID
-
原子性（Atomicity） ：原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的操作要么全部成功，要么全部失败。
-
一致性（Consistency）：一个事务完成之后数据都必须处于一致性状态。
如果事务成功的完成，那么数据库的所有变化将生效。
如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。
比如说刚才那个案例，部门如果成功删除了，而部门下的员工还在，就是不满足一致性
- 隔离性（Isolation）：多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。
一个事务的成功或者失败对于其他的事务是没有影响。
- 持久性（Durability）：一个事务一旦被提交或回滚，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。
索引
介绍
索引(index)：是帮助数据库高效获取数据的数据结构 。（是数据结构哦！）
- 简单来讲，就是使用索引可以提高查询的效率。
测试没有使用索引的查询：
添加索引后查询：
-- 添加索引
create index idx_sku_sn on tb_sku (sn); #在添加索引时，也需要消耗时间
-- 查询数据（使用了索引）
select * from tb_sku where sn = '100000003145008';
优点：
- 提高数据查询的效率，降低数据库的IO成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。
缺点：
- 索引会占用存储空间。
- 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。（增删改的效率降低）
结构
MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。
我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。
在没有了解B+Tree结构前，我们先回顾下之前所学习的树结构：
二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大
当我们向二叉查找树保存数据时，是按照从大到小(或从小到大)的顺序保存的，此时就会形成一个单向链表，搜索性能会打折扣。
可以选择平衡二叉树或者是红黑树来解决上述问题。（红黑树也是一棵平衡的二叉树）
但是在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构。
思考：采用二叉搜索树或者是红黑树来作为索引的结构有什么问题？
答案 最大的问题就是在数据量大的情况下，树的层级比较深，会影响检索速度。因为不管是二叉搜索数还是红黑数，一个节点下面只能有两个子节点。此时在数据量大的情况下，就会造成数的高度比较高，树的高度一旦高了，检索速度就会降低。说明：如果数据结构是红黑树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发访问，那么一个用户有可能需要23次磁盘IO，那么100万用户，那么会造成效率极其低下。所以为了减少红黑树的高度，那么就得增加树的宽度，就是不再像红黑树一样每个节点只能保存一个数据，可以引入另外一种数据结构，一个节点可以保存多个数据，这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。
下面我们来看看B+Tree(多路平衡搜索树)结构中如何避免这个问题：
B+Tree结构：
- 每一个节点，可以存储多个key（有n个key，就有n个指针）
- 节点分为：叶子节点、非叶子节点
- 叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上
- 非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是：key+指针
- 为了提高范围查询效率，叶子节点形成了一个双向链表，便于数据的排序及区间范围查询
拓展：
非叶子节点都是由key+指针域组成的，一个key占8字节，一个指针占6字节，而一个节点总共容量是16KB，那么可以计算出一个节点可以存储的元素个数：16*1024字节 / (8+6)=1170个元素。
- 查看mysql索引节点大小：show global status like ‘innodb_page_size’; – 节点大小：16384
当根节点中可以存储1170个元素，那么根据每个元素的地址值又会找到下面的子节点，每个子节点也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：1170*1170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。
对于第二层每个元素有指针，那么会找到第三层，第三层由key+数据组成，假设key+数据总大小是1KB，而每个节点一共能存储16KB，所以一个第三层一个节点大概可以存储16个元素(即16条记录)。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W*16结果就是2000W+的元素个数。
结合上述分析B+Tree有如下优点：
- 千万条数据，B+Tree可以控制在小于等于3的高度
- 所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找
语法
创建索引
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
案例：为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);
看这个index的名字是有说法的（idx_emp_name）
就是 idx_(表名)_（字段名）
不过这玩意就是个命名规范，起名字别的也行
在创建表时，如果添加了主键和唯一约束，就会默认创建：主键索引、唯一约束
查看索引
show index from 表名;
案例：查询 tb_emp 表的索引信息
show index from tb_emp;
看这个图，有说法的
主键字段，在建表时，会自动创建主键索引，看上图id，系统就会默认给他创造个索引
添加唯一约束时，数据库实际上会添加唯一索引，username因为有唯一约束，所以系统也会给它创造个索因
删除索引
drop index 索引名 on 表名;
案例：删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;
看这个玩意
就剩下两个系统默认创造的主键的索引和唯一字段的索引
咱们自己创建的已经被删除了
注意事项：
主键字段，在建表时，会自动创建主键索引
添加唯一约束时，数据库实际上会添加唯一索引
2180
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
