MySQL Study Notes Design in 2023

欧尼焦

已于 2023-06-12 18:20:32 修改

阅读量388

点赞数 2

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2023-02-16 18:16:54 首次发布

本文链接：https://blog.csdn.net/stange1/article/details/129055926

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL数据库的基础知识，包括数据模型的概念、SQL语言的分类（DDL、DML、DQL、DCL）以及数据库设计、操作和查询。重点讲解了如何创建、修改和删除表结构，以及如何进行数据的增删改查操作。此外，还涵盖了多表设计和查询，如一对一、一对多、多对多关系，以及事务处理和索引的应用。

摘要由CSDN通过智能技术生成

1 概述

1.1 MySQL相关概述

数据库：英文为 DataBase，简称DB，它是存储和管理数据的仓库。

数据库管理系统（DataBase Management System，简称DBMS）

SQL（Structured Query Language，简称SQL）：结构化查询语言，它是操作关系型数据库的编程语言

MySQL提供两个版本：商业版本（MySQL Enterprise Edition）;社区版本（MySQL Community Server）

1.2 数据模型

关系型数据库（RDBMS）
概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

二维表，指的是由行和列组成的表

二维表的优点：

使用表存储数据，格式统一，便于维护
使用SQL语言操作，标准统一，使用方便，可用于复杂查询

1.3 SQL分类

SQL语句根据其功能被分为四大类：DDL、DML、DQL、DCL

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	Language数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

2 数据库设计-DDL

2.1 约束

约束名	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key
主键自增	1、每次插入新的行记录时，数据库自动生成id字段(主键)下的值；2、具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)	auto_increment

2.2 字段

字段名	解释	备注
tinyint	微整型	tinyint unsigned：就是无符号,只能插入正整数,范围就是0~255
datetime	年月日时分秒	长日期：包括年月日时分秒信息
date	年月日	短日期：只包括年月日信息
char	String 类型的数据定义	1、char长度不可变；例如：定义char[10]和varchar[10],存进去‘csdn’,char占的长度为10，除了字符csdn’外，后面跟六个空格；2、char存取数度比varchar快，因为长度固定，方便程序存储与查找；3、但是char为此付出空间的代价，因为其长度固定，所以会有多余的空格占位符占据空间，以空间换取时间效率，而**varchar是以空间效率为首位；4、char的存储方式是，英文字符（ASCII）占1个字节，一个汉字占两个字节
varchar	String 类型的数据定义	1、varchar长度可变；定义char[10]和varchar[10],存进去‘csdn’,char占的长度为10，而varchar就立马把长度变为4；2、取数据的时候，char类型要用trim()去掉多余空格，varchar不需要；3、varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节。
decimal	依赖于M(精度)和D(标度)的值	1、小数值(精确定点数)例如：例如，语句DECIMAL(5,2)规定存储的值不超过5位数字，且小数点后有2位数字。2、decimal dd=345.545454879…–可以支持28位，对最后一位四舍五入。

2.3 修改表结构操作

2.3.1 添加字段

alter table 表名 add 字段名类型(长度) [comment 注释] [约束];
案例：为tb_emp表添加字段qq，字段类型为 varchar(11)

alter table tb_emp add  qq  varchar(11) comment 'QQ号码';

2.3.2 修改数据类型

alter table 表名 modify 字段名新数据类型(长度);
案例：修改qq字段的字段类型，将其长度由11修改为13

alter table tb_emp modify qq varchar(13) comment 'QQ号码';

alter table 表名 change 旧字段名新字段名类型(长度) [comment 注释] [约束];
案例：修改qq字段名为 qq_num，字段类型varchar(13)

alter table tb_emp change qq qq_num varchar(13) comment 'QQ号码';

2.3.3 删除字段

alter table 表名 drop 字段名;
案例：删除tb_emp表中的qq_num字段

alter table tb_emp drop qq_num;

2.3.4 修改表名

rename table 表名 to 新表名;
案例：将当前的tb_emp表的表名修改为emp

rename table tb_emp to emp;

3 数据库操作-DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据（INSERT）
修改数据（UPDATE）
删除数据（DELETE）

3.1 增加(insert)

批量添加数据（全部字段）:
insert into 表名 values (值1, 值2, …), (值1, 值2, …);

3.2 修改(update)

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
案例1：将tb_emp表中id为1的员工，姓名name字段更新为’张三’

update tb_emp set name='张三',update_time=now() where id=1;

3.3 删除(delete)

delete from 表名 [where 条件] ;
案例1：删除tb_emp表中id为1的员工

delete from tb_emp where id = 1;

4 数据库表中数据查询-DQL

4.1 语法、测试数据

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP  BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基本查询（不带任何条件）
条件查询（where）
分组查询（group by）
排序查询（order by）
分页查询（limit）

测试数据：

create database db02; -- 创建数据库
use db02; -- 切换数据库
-- 员工管理(带约束)
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');

4.1.1 基本查询

distinct	去除重复记录

-- 查询多个字段：select 字段1, 字段2, 字段3 from  表名;
SELECT username'用户名',job'工作' 
FROM tb_emp;

SELECT * 
FROM tb_emp;

-- 设置别名:select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ]  from  表名;
SELECT create_time'创建日期' 
FROM tb_emp;

-- 去除重复(distinct)记录:select distinct 字段列表 from  表名;
-- 查询已有的员工关联了哪几种职位(不要重复)
SELECT DISTINCT job'工作' 
FROM tb_emp;

4.1.2 条件查询

select 字段列表 from 表名 where 条件列表 ; -- 条件列表：意味着可以有多个条件

常用的比较运算符如下:

比较运算符	功能	java中特殊表示
>	大于	`>`（Greater than）
>=	大于等于	`≥`（Greater equal）
<	小于	`&lt`(Less than)
<=	小于等于	`≤`（Less equal）
=	等于
<> 或 !=	不等于
between … and …	在某个范围之内(含最小、最大值)
in(…)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
is null	是null

常用的逻辑运算符如下:

逻辑运算符	功能
and 或 &&	并且 (多个条件同时成立)
or 或 \|\|	或者 (多个条件任意一个成立)
not 或 !	非 , 不是

常用的通配符如下:

通配符	解释
_	通配符 “_” 代表任意1个字符
%	通配符 “%” 代表任意个字符（0个 ~ 多个）

-- 1. 查询 姓名 为 杨逍 的员工
SELECT * 
FROM tb_emp 
WHERE name='杨逍';

-- 2. 查询 id小于等于5 的员工信息
SELECT * 
FROM tb_emp 
WHERE id <= 5;

-- 3. 查询 没有分配职位 的员工信息
SELECT * 
FROM tb_emp 
WHERE job IS NULL;

-- 4. 查询 有职位 的员工信息
SELECT * 
FROM tb_emp 
WHERE job IS NOT NULL;

-- 5. 查询 密码不等于 '123456' 的员工信息
SELECT * 
FROM tb_emp 
WHERE `password` != '123456';

-- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2000-01-01'(包含) 之间的员工信息
SELECT * 
FROM tb_emp 
WHERE `entrydate` >= '2000-01-01' <='2000-01-01';

SELECT * 
FROM tb_emp 
WHERE `entrydate` BETWEEN '2000-01-01' AND '2000-01-01';

-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
SELECT * 
FROM tb_emp 
WHERE entrydate BETWEEN '2000-01-01' AND '2010-01-01' AND gender = 2;

-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
SELECT * 
FROM tb_emp 
WHERE `job`=  2 OR job = 3 OR job = 4;

SELECT * 
FROM tb_emp 
WHERE `job` IN (2,3,4);

-- 9. 查询 姓名 为两个字的员工信息
SELECT * 
FROM tb_emp 
WHERE `name` LIKE '__'; # 通配符 "_" 代表任意1个字符

-- 10. 查询 姓 '张' 的员工信息
SELECT * 
FROM tb_emp 
WHERE `name` LIKE '张%'; # 通配符 "%" 代表任意个字符（0个 ~ 多个）

4.1.3 聚合函数、分组查询

聚合函数语法：select 聚合函数(字段列表) from 表名 ;
分组查询语法：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

常用聚合、分组函数：

函数	功能	注意
count	统计数量	1、按照列去统计有多少行数据。2、在根据指定列统计时，如果这列中有null的行，该行不会被统计在其中。
max	最大值	计算指定列数值和，如果不是数值类型，计算结果为0
min	最小值	计算指定列的最大值
avg	平均值	计算指定列的最小值
sum	求和	计算指定列的平均值

常用分组函数：

函数	功能	注意
having	分组后过滤条件	常与count聚合函数连用

执行顺序:where > 聚合函数 > having

where与having区别

执行时机不同	判断条件不同
where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。	where不能对聚合函数进行判断，而*having可以如：count（）**。

--  =================== DQL: 分组查询 ======================
-- 聚合函数
-- select  聚合函数(字段列表)  from  表名 ;

-- 1. 统计该企业员工数量
SELECT COUNT(username)'员工数量' 
FROM tb_emp;

SELECT COUNT(*) #功能跟 COUNT(1) 一样，优先使用COUNT(*)底层做了优化
FROM tb_emp;

SELECT COUNT(1) 
FROM tb_emp; #结果是29；count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL。

-- 2. 统计该企业员工 ID 的平均值
SELECT AVG(id) 
FROM tb_emp;

-- 3. 统计该企业最早入职的员工
SELECT MIN(entrydate) 
FROM tb_emp;

-- 4. 统计该企业最迟入职的员工
SELECT MAX(entrydate) 
FROM tb_emp;

-- 5. 统计该企业员工的 ID 之和
SELECT SUM(id) 
FROM tb_emp;


-- 分组
-- select  字段列表  from  表名  [where 条件]  group by 分组字段名  [having 分组后过滤条件];
-- 1. 根据性别分组 , 统计男性和女性员工的数量
SELECT gender, COUNT(*)'人数'
FROM  tb_emp 
GROUP BY gender

-- 3. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
SELECT entrydate'入职在2015-01-01(包含)之前',COUNT(*)'员工数量'
FROM tb_emp
WHERE entrydate <= '2015-01-01'
GROUP BY job
HAVING COUNT(*)>=2

4.1.4 排序查询

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

ASC	升序（默认值）
DESC（descend）	降序

--  =================== 排序查询 ======================
-- 1. 根据入职时间, 对员工进行升序排序
SELECT * FROM tb_emp ORDER BY entrydate ASC;

-- 2. 根据入职时间, 对员工进行降序排序
SELECT * FROM tb_emp ORDER BY entrydate DESC;

-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 更新时间 进行降序排序
SELECT * FROM tb_emp ORDER BY entrydate ASC ,update_time DESC;

4.1.5 分页查询

select 字段列表 from 表名 limit 起始索引, 查询记录数 ;

LIMIT 0, 5	从索引0开始，每次取5条数据，即从表中数据的第一行开始取5行数据
LIMIT 6, 10	从索引6开始，每次取10条数据，即从表中数据的第7行开始取10行数据

--  =================== 分页查询 ======================
-- 分页查询: select 字段列表 from表名 limit 起始索引，查询记录数；
-- 1. 从起始索引0开始查询员工数据, 每页展示5条记录
SELECT * 
FROM tb_emp
LIMIT 0, 5; 

-- 2. 查询 第1页 员工数据, 每页展示5条记录
SELECT * 
FROM tb_emp
LIMIT 0 , 5; 

-- 3. 查询 第2页 员工数据, 每页展示5条记录
SELECT * 
FROM tb_emp
LIMIT 5 , 5; 



-- 4. 查询 第3页 员工数据, 每页展示5条记录
SELECT * 
FROM tb_emp
LIMIT 10, 5;

4.1.6 查询时进行赋名

语法	说明
if(表达式, tvalue, fvalue)	当表达式为true时，取值tvalue；当表达式为false时，取值fvalue
case 表达式 when 值 then 结果	case 表达式 when 值1 then 结果1 [when 值2 then 结果2 …] [else result] end

# 根据性别分组 查询各组人数
# if(条件表达式，true，false)
SELECT IF(a.gender = 1,'男','女') 性别, COUNT(*) 人数
FROM tb_emp a
GROUP BY a.gender;

# 根据职位统计员工数量
# 职位，说明: 1 班主任,2 讲师，3 学工主，4 教研主管
# case 字段 when 值1 then '展示值1' when 值2 then '展示值2' else '其他展示值' end
SELECT CASE a.job
	WHEN 1 THEN'班主任'
	WHEN 2 THEN'讲师'
	WHEN 3 THEN'学工主'
	WHEN 4 THEN'教研主管'
	ELSE '未分配'END 职位,
	COUNT(*) 员工数量
FROM tb_emp a
GROUP BY a.job;

5 多表设计

一对多(多对一)
多对多
一对一

5.1 一对多

一对多关系实现：在数据库表中多的一方，添加字段，来关联属于一这方的主键。

	概念	缺点
物理外键	使用foreign key定义外键关联另外一张表	1、影响增、删、改的效率（需要检查外键关系）2、仅用于单节点数据库，不适用与分布式、集群场景 3、容易引发数据库的死锁问题，消耗性能
逻辑外键	在业务层逻辑中，解决外键关联

注意：企业开发中，很少使用物理外键，大部分使用逻辑外键。甚至在一些数据库开发规范中，会明确指出禁止使用物理外键foreign key

5.2 一对一

一对一：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

5.3 多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

6 多表查询

6.1 数据准备

#建议：创建新的数据库
create database db04;
use db04;

-- 部门表
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());

6.2 多表查询时笛卡尔积

查询用户表和部门表中的数据：
select * from  tb_emp , tb_dept;
总共85条记录，这就是员工表所有记录(17行)与部门表所有记录(5行)所有组合情况，称之为笛卡尔积。
如何去除无效的笛卡尔积呢？只需要给多表查询加上连接查询的条件即可。
select * from tb_emp , tb_dept where >tb_emp.dept_id = tb_dept.id ;

6.3 多表查询分类

1、连接查询

内连接：相当于查询A、B交集部分数据

2、外连接

左外连接：查询左表所有数据(包括两张表交集部分数据)
右外连接：查询右表所有数据(包括两张表交集部分数据)
3、子查询

6.3.1 内连接

隐式内连接语法:
select 字段列表 from 表1 , 表2 where 条件 ... ;
-- 隐式内连接
select tb_emp.name,tb_dept.name
from tb_emp
join tb_dept on tb_emp.id = tb_dept.id;
显式内连接语法：
select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;
-- 显式内连接
select a.name '员工姓名',b.name as 部门名称
from tb_emp a,tb_dept b
where a.dept_id = b.id;

6.3.2 外连接

外连接分为两种：左外连接 和 右外连接。

左外连接语法结构：
select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;
左外连接相当于查询左表所有数据，也包含表1和表2交集的数据。
-- 左外连接
# 查询员工表所有的员工姓名和 对应的部门名称
select a.name 员工,b.name 部门
from tb_emp a
  left join tb_dept b
on a.dept_id = b.id;
-- 左外连接
# 查询员工表所有的员工姓名和 对应的部门名称
select a.name 员工,b.name 部门
from tb_emp a
  left join tb_dept b
on a.dept_id = b.id;

右外连接语法结构：
select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;
右外连接相当于查询右表所有数据，当然也包含表1和表2交集的数据。

6.3.3 子查询

SQL语句中嵌套select语句，称为子查询，又称嵌套查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ... );
根据子查询结果的不同分为：
1. 标量子查询（子查询结果为单个值[一行一列]）
2.列子查询（子查询结果为一列，但可以是多行）
3. 行子查询（子查询结果为一行，但可以是多列）
4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内

6.4 多表查询解决思路与案例

思路
1. 要查询哪些字段：
2. 这些字段都在哪些表中
3. 如果有多张表，表之间如何关联
4. 是否有其他条件

# 思路
# 1. 要查询哪些字段：
# 2. 这些字段都在哪些表中
# 3. 如果有多张表，表之间如何关联
# 4. 是否有其他条件
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
# 思路
# 1. 要查询哪些字段：菜品表.name； 菜品表.价格； 分类名称
# 2. 这些字段都在哪些表中 菜品表dish  菜品表dish  分类表category
# 3. 如果有多张表，表之间如何关联 dish.id=category.id
# 4. 是否有其他条件 dish.price<10
select a.name 菜品名称,a.price 价格,b.name 分类
from dish a,category b
where a.id=b.id
and a.price<10;

-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
# 思路
# 1. 要查询哪些字段：dish.name ，菜品表.price，category
# 2. 这些字段都在哪些表中 dish  category
# 3. 如果有多张表，表之间如何关联 dish.category_id = category.id  需要分类名称，所以分类表中的类别id关联菜品表中的主键id
# 4. 是否有其他条件 dish.price >= 10 and dish.price <= 50    status=1
select a.name,a.price,b.name
from dish a
left join category b
on a.category_id = b.id
and a.price >= 10 and a.price <= 50
and a.status=1;

-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
# 思路
# 1. 要查询哪些字段：最贵的菜品 category.name  dash.price（max）
# 2. 这些字段都在哪些表中 category dish
# 3. 如果有多张表，表之间如何关联 dish.category_id = category.id
# 4. 是否有其他条件 每个分类
select max(b.price) 最贵的菜品价格,b.name 商品名,a.name 分类名称
from category a,dish b
where a.id = b.category_id
group by a.name; #每个类别

-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
# 思路
# 1. 要查询哪些字段：category.status count（*）  dish
# 2. 这些字段都在哪些表中 category dish
# 3. 如果有多张表，表之间如何关联 category.id=dish.category_id
# 4. 是否有其他条件 各个分类 状态为 '起售'  总数量大于等于3
select a.id 分类编号,a.name 分类名称,count(*) 菜品数量
from category a,dish b
where a.id=b.category_id and  b.status = 1
group by a.name
having count(*) >= 3;

select a.id 分类编号,a.name 分类名称,count(*) 菜品数量
from category a,dish b
where a.id=b.category_id and  b.status = 1
group by a.id
having count(*) >= 3;

select a.id,a.name as 分类名称, count(*) as 菜品总数量
from category a,dish b
where a.id = b.category_id and a.status = 1
group by a.id,a.name
having 菜品总数量 >= 3;


-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
# 思路
# 1. 要查询哪些字段：菜品名称、价格 套餐名称、价格  菜品份数
# 2. 这些字段都在哪些表中 dish setmeal   setmeal_dish
# 3. 如果有多张表，表之间如何关联 setmeal_dish.setmeal_id= setmea.id    setmeal_dish.dish_id=dish.id
# 4. 是否有其他条件   "商务套餐A"
select a.name 菜品名称,a.price 菜品价格,b.name 套餐名称,b.price 套餐价格,c.copies 菜品份数
from dish a,setmeal b ,setmeal_dish c
where c.setmeal_id = b.id and c.dish_id = a.id
and b.name='商务套餐A';

-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
# 思路
# 1. 要查询哪些字段：菜品的价格  菜品名称
# 2. 这些字段都在哪些表中 dish
# 3. 如果有多张表，表之间如何关联
# 4. 是否有其他条件 菜品平均价格
select avg(a.price)
from dish a;

select a.name 菜品名称,a.price 菜品价格
from dish a
where a.price <(select avg(a.price)
                from dish a);

7 事务

7.1 事务介绍

场景：学工部整个部门解散了，该部门及部门下的员工都需要删除了。

删除学工部
delete from dept where id = 1; – 删除成功
删除学工部
delete from dept where id = 1; – 删除成功（操作过程中出现错误：造成删除没有成功）

问题：如果删除部门成功了，而删除该部门的员工失败，此时就造成了数据不一致。
解决上述问题，就需要数据库中事务来解决。

事务：事务是一组操作的集合，是一个不可分割的工作单位。事务把所有操作作为整体一起向系统提交或撤销操作请求，即操作要么同时成功，要么同时失败

7.2 事务操作

SQL语句	描述
start transaction; / begin ;	开启手动控制事务
commit;	提交事务
collback;	回滚事务

使用事务控制删除部门和该部门下员工：

-- 开启事务
start transaction ;

-- 删除学工部
delete from tb_dept where id = 1;

-- 删除学工部的员工
delete from tb_emp where dept_id = 1;

-- 上述的这组SQL语句，如果如果执行成功，则提交事务
-- 提交事务 (成功时执行)
commit ;

--上述的这组SQL语句，如果如果执行失败，则回滚事务
-- 回滚事务 (出错时执行)
rollback ;

7.3 事务四大特性（ACID）

特性	解释
原子性（Atomicity）	原子性指事务包装的一组sql是不可分割的工作单元，事务中操作要么全部成功，要么全部失败。
一致性（Consistency）	事务完成之后数据都必须处于一致性状态。
隔离性（Isolation）	多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离（一个事务的成功或者失败对于其他的事务是没有影响）。
持久性（Durability）	一个事务被提交或回滚，对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。

8 索引

8.1 索引介绍

索引(index)：帮助数据库高效获取数据的数据结构 ,提高查询的效率

优点	缺点
1. 提高数据查询效率，降低数据库O成本。2. 通过索引列对数据进行排序，降低数据排序成本，降低CPU消耗。	1. 索引占用存储空间。2. 索引提高查询效率，同时降低insert、update、delete效率。

8.2 索引结构（B+Tree）

MySQL支持的索引，如：Hash索引、B+Tree索引、Full-Text索引等。

我们平常所说的索引，如果没有特别指明，指 默认B+Tree 结构组织的索引。

B+Tree结构((多路平衡搜索树))：

每个节点上有多个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的高度
所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找

8.3 索引语法

create [ unique ] index 索引名 on 表名 (字段名,... ) ;
案例：为tb_emp表的name字段建立索引
create index idx_emp_name on tb_emp(name);
注意：在创建表时，添加的主键和唯一约束，会默认创建：主键索引、唯一约束

查看索引 show index from 表名;
案例：查询 tb_emp 表的索引信息
show  index  from  tb_emp;

删除索引
drop  index  索引名  on  表名;
案例：删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;

9 聚簇索引和非聚簇索引

9.1 聚簇索引

9.1.1 聚簇索引概念

聚簇索引：

概念：是指在数据库表中，将物理存储数据按照索引的顺序进行排序，形成一棵B+树，且树的节点与数据存储在同一磁盘区域，称为聚簇索引。
最大利用磁盘I/O缓存：聚簇索引实际存储顺序与索引顺序相同且在同一块磁盘区域，因而聚簇索引可以最大程度利用磁盘I/O缓存。
聚簇索引适用场景：多适用于主键、唯一键等常用于查询的列。

9.1.2 聚簇索引优缺点

最大利用磁盘I/O缓存比非聚簇索引查询速度快：由于存储顺序和索引顺序相同且在一块磁盘区域，因而聚簇索引可以最大利用磁盘I/O缓存，因而比非聚簇索引查询速度更快。

9.2 非聚簇索引

9.2.1 非聚簇索引概念

概念：是指将索引的值与数据的物理存储位置分离，以B+树的形式存储，但将数据和索引一起存储。
存储方式：在非聚簇索引中，索引的叶子节点中存储了指向实际数据存储位置的指针，又因为索引和数据分别存储，所以非聚簇索引可以较好地支持范围查询和排序操作。
非聚簇索引适用场景：多适用于非主键和非唯一键列。