细说SQL，由浅入深

建库

create database ceshi charset=utf8;
show create database ceshi;

这里需要注意：

utf8：实际上，MySQL 中的 utf8 字符集只支持最多 3 字节的 UTF-8 编码字符。这意味着它能够存储基本的多语言字符和符号，但不支持某些特殊的 Unicode 字符，例如许多 emoji（表情符号）和一些 CJK（中文、日文、韩文）字符。
utf8mb4：utf8mb4 是 MySQL 的真正的 UTF-8 编码支持，能够存储所有 Unicode 字符，包括所有 4 字节字符（如 emoji 和某些少数语言字符）。

如果需要修改字符集，命令如下：

ALTER DATABASE ceshi CHARACTER SET = utf8mb4;

使用并查看数据库

mysql> use ceshi
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| ceshi      |
+------------+
1 row in set (0.00 sec)

创建数据表

drop table if exists students;
create table students ( studentNo varchar(10) primary key, name varchar(10), sex enum('男', '女'), hometown varchar(20), age tinyint(4), class varchar(10), card varchar(20));

查看数据表创建过程

mysql> show create table students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `studentNo` varchar(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `hometown` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL,
  `card` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`studentNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

查看表结构

mysql> desc students;
+-----------+-------------------+------+-----+---------+-------+
| Field     | Type              | Null | Key | Default | Extra |
+-----------+-------------------+------+-----+---------+-------+
| studentNo | varchar(10)       | NO   | PRI | NULL    |       |
| name      | varchar(10)       | YES  |     | NULL    |       |
| sex       | enum('男','女')   | YES  |     | NULL    |       |
| hometown  | varchar(20)       | YES  |     | NULL    |       |
| age       | tinyint(4)        | YES  |     | NULL    |       |
| class     | varchar(10)       | YES  |     | NULL    |       |
| card      | varchar(20)       | YES  |     | NULL    |       |
+-----------+-------------------+------+-----+---------+-------+

插入数据

insert into students values ('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),('008', '小乔', '女', '河南', '15', '3班', null),('009', '百里守约', '男', '湖南', '21', '1班', ''),('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655');

查询所有字段

select * from 表名
select * from students;

查询指定字段

select 列1,列2,… from 表名
select students.name,students.age from students;

别名

在select后面的列名/表名部分，可以使用空格或者as为列起别名，这个别名出现在结果集中
select s.name 姓名, s.age 年龄 from students s;

消除重复行

在select后面列前使用distinct可以消除重复的行
select distinct sex from students;

类似于group by本身
select sex from students group by sex;

条件判断

基本概念
使用where子句对表中的数据筛选，符号条件的数据会出现在结果集中
select 字段1,字段2… from 表名 where 条件;
select * from students where studentNo=1;

where后面支持多种运算符，进行条件的处理

比较运算
	比较运算符
		等于: =
		大于: >
		大于等于: >=
		小于: <
		小于等于: <=
		不等于: !=
	例：
		查询小乔的年龄
		select age from students where name='小乔';
		查询20岁以下的学生
		select * from students where age<20;
		查询家乡不在北京的学生
		select * from students where hometown!='北京';

逻辑运算
	and
		查询年龄小于20的女同学
		select * from students where age<20 and sex='女';
	or
		select * from students where sex='女' or class='1班';
	not
		select * from students where not hometown='天津';

模糊查询
	like
		%表示任意多个任意字符
			查询姓孙的学生
			select * from students where name like '孙%';
			查询叫乔的学生
			select * from students where name like '%乔';
			查询姓名含白的学生
			select * from students where name like '%白%';
			查询姓名为两个字的学生
			select * from students where name like '__';
		_表示一个任意字符 
			查询姓孙且名字是一个字的学生
			select * from students where name like '孙_';

范围查询
	in 表示在一个非连续的范围内
		查询家乡是北京或上海或广东的学生
		select * from students where hometown in ('北京','上海','广东');
	between ... and ...表示在一个连续的范围内
		查询年龄为18至20的学生
		select * from students where age between 18 and 20;

空判断
	空判断
		注意：null与''是不同的 判空is null
		查询没有填写身份证的学生
		select * from students where card is null;
	判非空
		select * from students where card is not null;

排序

为了方便查看数据，可以对数据进行排序
语法：将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推默认按照列值从小到大排列
select * from 表名 order by 列1 asc|desc,列2 asc|desc,…
例子：
查询所有学生信息，按年龄从小到大排序
select * from students order by age;
查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc,studentNo;

聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数
count(*)表示计算总行数，括号中写星与列名，结果是相同的聚合函数不能在 where 中使用

聚合函数不能在 where 中使用

查询学生总数
	select count(*) from students;
max(列)表示求此列的最大值 
	查询女生的最大年龄
	select max(age) from students where sex='女';
min(列)表示求此列的最小值 
	查询1班的最小年龄
	select min(age) from students;
avg(列)表示求此列的平均值 
	查询女生的平均年龄
	select avg(age) from students where sex='女';
sum(列)表示求此列的和 
	查询北京学生的年龄总
	select sum(age) from students where hometown='北京';

分组

按照字段分组，表示此字段相同的数据会被放到一个组中分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中可以对分组后的数据进行统计，做聚合运算
语法:select 列1,列2,聚合… from 表名 group by 列1,列2…
例：
查询各种性别的人数
select sex,count(*) from students group by sex;
查询各种年龄的人数
select age,count(*) from students group by age;
查询各个班级学生的平均年龄、最大年龄、最小年龄
select max(age),min(age),avg(age) from students group by class;

分组后的数据筛选

语法：
select 列1,列2,聚合... from 表名
 group by 列1,列2,列3... having 列1,...聚合...
having后面的条件运算符与where的相同
having和where的区别
where:
1、“Where”是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用（分组前）
2、where用在聚合函数之前，后面不能使用“聚合函数”
having:
1、“Having”是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用（分组后）
2、having可以用在“聚合函数”之后，所以可以使用聚合函数
例：
查询男生总人数
方案一
select count(*) from students where sex='男';
方案二
select sex,count(*) from students group by sex having sex='男';

对比where与having
where是对from后面指定的表进行数据筛选，属于对原始数据的筛选 having是对group by的结果进行筛选，建议多用where更节省资源

mysql语句执行顺序

获取部分行（分页查询）

当数据量过大时，在一页中查看数据是一件非常麻烦的事情
语法：
select * from 表名 limit start,count;
从start开始，获取count条数据,start索引从0开始
例：
查询前3行学生信息
select * from students limit 0,3;
查询第4到第6行学生信息
select * from students limit 3,3;

连接查询

笛卡尔积

笛卡尔积（Cartesian Product）是指两个集合之间所有可能的配对。在数据库中，笛卡尔积通常是指两个表之间的连接，结果是第一个表中的每一行与第二个表中的每一行进行配对，从而生成一个新的结果集。

笛卡尔积的概念

对于两个集合 A 和 B，笛卡尔积 A×B的结果是一个包含所有可能组合的有序对的集合。具体来说，如果集合 A 有 m 个元素，而集合 B 有 n 个元素，则笛卡尔积 A×B 将包含 m×n 个元素（即所有可能的配对）。

在数据库中的应用
假设我们有两个表：

表 A：

id	name
1	Alice
2	Bob

表 B：

id	fruit
1	Apple
2	Banana
3	Cherry

SELECT * FROM A, B;

结果集将如下所示，包含了所有可能的配对组合：

A.id	A.name	B.id	B.fruit
1	Alice	1	Apple
1	Alice	2	Banana
1	Alice	3	Cherry
2	Bob	1	Apple
2	Bob	2	Banana
2	Bob	3	Cherry

连接查询简介
当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

内连接查询：只有在两个表中有匹配的行时，这些行才会出现在结果集中。

左连接查询：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充

右连接查询：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充

全连接：全连接是A表的所有行并上B表的所有行得出的结果集，一般用于垂直分表后再结合，对于任一表中没有匹配的记录，结果中对应的字段将为 NULL

通过union 连接两条单独的select语句

准备数据，练习多表查询

drop table if exists courses;

create table courses (
courseNo int(10) unsigned primary key auto_increment, name varchar(10)
);

insert into courses values ('1', '数据库'),
('2', 'qtp'),
('3', 'linux'),
('4', '系统测试'),
('5', '单元测试'),
('6', '测试过程');

drop table if exists scores; 
create table scores (
id int(10) unsigned primary key auto_increment, courseNo int(10),
studentno varchar(10), score tinyint(4)
);

insert into scores values ('1', '1', '001', '90'),
('2', '1', '002', '75'),
('3', '2', '002', '98'),
('4', '3', '001', '86'),
('5', '3', '003', '80'),
('6', '4', '004', '79'),
('7', '5', '005', '96'),
('8', '6', '006', '80');

等值连接/内连接

方式一
select * from 表1,表2 where 表1.列=表2.列;

方式二(又称内连接)
select * from 表1
inner join 表2 on 表1.列=表2.

例子：

查询学生信息及学生的成绩

select
* from
students stu, scores sc
where
stu.studentNo = sc.studentNo;

select
* from
students stu
inner join scores sc on stu.studentNo = sc.studentNo;

查询课程信息及课程的成绩

select
* from
courses cs, scores sc
where
cs.courseNo = sc.courseNo;

select
* from
courses cs
inner join scores sc on cs.courseNo = sc.courseNo;

查询学生信息及学生的课程对应的成绩

select
* from
students stu, courses cs, scores sc
where
stu.studentNo = sc.studentno and cs.courseNo=sc.courseNo;

select
* from
students stu
inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on cs.courseNo = sc.courseNo;

查询王昭君的成绩，要求显示姓名、课程号、成绩

select
stu.name, sc.courseNo, sc.score
from
students stu, scores sc
where
stu.studentNo = sc.studentNo and stu.name = '王昭君';

select
stu.name, sc.courseNo, sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo where
stu.name = '王昭君';

查询王昭君的数据库成绩，要求显示姓名、课程名、成绩

select
stu.name, cs.name, sc.score
from
students stu, scores sc, courses cs
where
stu.studentNo = sc.studentNo and sc.courseNo = cs.courseNo and stu.name = '王昭君'
and cs.name = '数据库';

select
stu.name, cs.name, sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on sc.courseNo = cs.courseNo where
stu.name = '王昭君' and cs.name = '数据库';

查询所有学生的数据库成绩，要求显示姓名、课程名、成绩

select
stu.name, cs.name, sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on sc.courseNo = cs.courseNo where
cs.name = '数据库';

select
stu.name, cs.name, sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on sc.courseNo = cs.courseNo where
cs.name = '数据库';

查询男生中最高成绩，要求显示姓名、课程名、成绩

select
stu.name, cs.name, sc.score
from
students stu, scores sc, courses cs
where
stu.studentNo = sc.studentNo and sc.courseNo = cs.courseNo and stu.sex = '男'
order by
sc.score desc limit 1;

select
stu.name, cs.name, sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on sc.courseNo = cs.courseNo where
stu.sex = '男'
order by
sc.score desc limit 1;

左连接

格式
select * from 表1
left join 表2 on 表1.列=表2.列

例：查询所有学生的成绩，包括没有成绩的学生

select
* from
students stu
left join scores sc on stu.studentNo = sc.studentNo;

查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select
* from
students stu
left join scores sc on stu.studentNo = sc.studentNo left join courses cs on cs.courseNo = sc.courseNo;

右连接

格式
select * from 表1
right join 表2 on 表1.列=表2.列;

添加两门课程

insert into courses values 
(0, '语文'),
(0, '数学');

例
查询所有课程的成绩，包括没有成绩的课程

select
* from
scores sc
right join courses cs on cs.courseNo = sc.courseNo;

查询所有课程的成绩，包括没有成绩的课程，包括学生信息

select
* from
scores sc
right join courses cs on cs.courseNo = sc.courseNo left join students stu on stu.studentNo=sc.studentNo;

自关联

表结构：
设计省信息的表结构
provinces id，ptitle
设计市信息的表结构
citys id，ctitle ，proid
citys表的proid表示城市所属的省，对应着provinces表的id值
问题：能不能将两个表合成一张表呢？思考：观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大

建表

定义表areas，结构如下
id atitle pid
因为省没有所属的省份，所以可以填写为null
城市所属的省份pid，填写省所对应的编号id 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

准备数据：
create table areas( aid int primary key, atitle varchar(20), pid int);

insert into areas
values ('130000', '河北省', NULL),
('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'),
('130600', '保定市', '130000'),
('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),
('410000', '河南省', NULL),
('410100', '郑州市', '410000'),
('410300', '洛阳市', '410000'),
('410500', '安阳市', '410000'),
('410700', '新乡市', '410000'),
('410800', '焦作市', '410000');

例题：
查询一共有多少个省
select count(*) from areas where pid is null;

查询河南省的所有城市
子查询
select atitle from areas where atitle !="河南市" and pid=(select aid from areas where atitle="河南省");

连表查询
先进行连表
select * from areas a inner join areas b on a.aid=b.pid;

select
* from
areas as p
inner join areas as c on c.pid=p.aid where
p.atitle='河北省';

或者
select * from areas a,areas b where a.aid=b.pid and a.atitle="河南省";

添加区县数据

insert into areas values 
('410101', '中原区', '410100'),
('410102', '二七区', '410100'),
('410103', '金水区', '410100');

查询郑州市的所有区县

法一
select * from areas a,areas b where a.aid=b.pid and a.atitle="郑州市";

法二

select
* from
areas as c
inner join areas as a on a.pid=c.aid where
c.atitle='郑州市';

查询河南省的所有区县

法一
select * from areas a,areas b,areas c where a.aid=b.pid and b.aid=c.pid and a.atitle="河南省";

法二

select
* from
areas as p
left join areas as c on c.pid=p.aid left join areas as a on a.pid=c.aid where
p.atitle='河南省';

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

主查询：主要查询的对象,第一条 select 语句

主查询和子查询的关系
子查询是嵌入到主查询中子查询是辅助主查询的,要么充当条件,要么充当数据源子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类

就查询大于平均年龄的学生对子查询进行理解

select * from students where age>(select avg(age) from students);

标量子查询 (Scalar Subquery):

标量子查询是返回单行单列的 SQL 子查询。这种类型的查询通常用于需要一个具体值进行比较的场合，比如在 SELECT、WHERE 或 HAVING 子句中。用于返回单个值，适用于比较特定条件。

示例

SELECT name
FROM students
WHERE age = (SELECT MAX(age) FROM students WHERE sex = '男');

解释：此查询查找年龄最大的男生的姓名。内层子查询 (SELECT MAX(age) FROM students WHERE sex = '男') 返回最大年龄的值，外层查询根据这个值进行比较。

列子查询 (Column Subquery)

列子查询是返回多行单列数据的子查询。这种类型的查询通常用于条件判断中，可以返回多个行的同一列数据。核心思想：常用于 IN 判断。

示例

SELECT name
FROM students
WHERE studentno IN (SELECT studentno FROM scores WHERE score > 80);

解释：此查询查找所有在 score 表中得分超过 80 的学生的姓名。内层子查询 (SELECT studentno FROM scores WHERE score > 80) 返回所有符合条件的学生编号，外层查询根据这些编号拉取匹配的学生姓名。

行子查询:
行子查询是返回一行数据的子查询，这种查询可以返回多列的结果。核心思想：多个字段同时约束

示例

SELECT name, sex
FROM students
WHERE (studentNo, class) IN (
    SELECT studentNo, class
    FROM students
    WHERE sex = '男' AND class = '2班'
      AND age = (SELECT MAX(age) FROM students WHERE sex = '男' AND class = '2班')
);

解释：假设我们想在 2班 中找到年龄最大的男生的姓名和性别。，其中的子查询返回性别为男生的记录，且根据年龄进行排序，选择第一条记录。

表级子查询:
返回的结果是多行多列：in 后的括号写多个字段，只要与里面的约束项有交集即可：查询多个字段+in实现多行多列

示例
获取每个学生的名字和他们的平均分

SELECT s.name, avg_scores.avg_score
FROM students s
JOIN (
    SELECT studentno, AVG(score) AS avg_score
    FROM scores
    GROUP BY studentno
) avg_scores ON s.studentno = avg_scores.studentno;

说明：

表级子查询：
- 内层子查询 (SELECT studentno, AVG(score) AS avg_score FROM scores GROUP BY studentno) 返回每个学生编号及其对应的平均分。这个结果集有两列：studentno 和 avg_score。
外层查询：
- 外层查询 SELECT s.name, avg_scores.avg_score 从 students 表中选择学生姓名，同时连接内层查询的结果集 avg_scores，根据学生编号来匹配。
结果：
- 最终查询返回的结果集包含每个学生的姓名和他们的平均分。这就是通过表级子查询准备的中间数据被用于最终查询的实例。

子查询中特定关键字使用

在 SQL 中，子查询使用了几个特定的关键字和操作符，以增强查询的灵活性和功能性。以下是一些常用的关键字及其在子查询中的特定用法：

格式: 主查询 where 条件 in (列子查询)
any | some 任意一个
格式: 主查询 where 列 = any (列子查询)
在条件查询的结果中匹配任意一个即可,等价于 in
all
格式: 主查询 where 列 = all(列子查询) : 等于里面所有
格式: 主查询 where 列 <>all(列子查询) : 不等于其中所有
!=all 等价于 not in

1. IN

用法：用于检查某个值是否存在于子查询返回的结果集中。

示例：

SELECT name
FROM students
WHERE studentno IN (SELECT studentno FROM scores WHERE score > 80);

说明：上面的查询查找所有在 scores 表中得分超过 80 分的学生姓名。

2. EXISTS

用法：用于检查子查询是否返回至少一行。如果子查询返回至少一行，则 EXISTS 返回 TRUE。

示例：

SELECT name
FROM students s
WHERE EXISTS (SELECT 1 FROM scores sc WHERE sc.studentno = s.studentno AND sc.score > 85);

说明：这个查询查找所有在 scores 表中至少有一门课程得分超过 85 的学生姓名。EXISTS 子查询检查是否存在满足条件的记录。

3. ANY 和 SOME

用法：用于比较一个值是否与子查询返回的任何值匹配。ANY 和 SOME 是等效的。

示例：

SELECT name
FROM students
WHERE studentNo IN (
    SELECT studentno
    FROM scores
    WHERE score > ANY (SELECT score FROM scores WHERE courseNo = 1)
);

说明：这个查询查找只要有得分高于 scores 表中 数据库 课程的任何一个分数（及比课程中最低分数高级可）的学生姓名。

4. ALL

用法：用于比较一个值是否与子查询返回的所有值匹配。只有在所有值都符合条件时，ALL 才返回 TRUE。

示例：

SELECT name
FROM students s
WHERE studentNo IN (
    SELECT studentno
    FROM scores
    WHERE score > ALL (SELECT score FROM scores WHERE courseNo = 1)
);

说明：这个查询查找所有得分高于 scores 表中 数据库 课程所有分数的学生姓名，只有当学生的分数高于所有列出的分数时，才会被选中。

5. LIMIT

用法：在某些数据库系统中，子查询可以用 LIMIT 字句来限制返回的结果数量。

示例（MySQL）：

SELECT s.name
FROM students s
JOIN (
    SELECT studentno
    FROM scores
    ORDER BY score DESC
    LIMIT 5
) top_scores ON s.studentNo = top_scores.studentno;

说明：这个查询找到在 scores 表中得分的前 5 名的学生的姓名。

6. JOIN 和子查询

在子查询中，您还可以结合使用 JOIN 来关联其他表的数据。

示例：

SELECT s.name
FROM students s
JOIN (SELECT studentno, AVG(score) AS avg_score 
      FROM scores 
      GROUP BY studentno) AS avg_scores 
ON s.studentno = avg_scores.studentno
WHERE avg_scores.avg_score > 85;

说明：这里，内层子查询返回每位学生的平均分，外层查询找到姓名符合条件的学生。

数据分表

数据分表通常用于将大数据表拆分为多个小表，以提高性能、可管理性和维护性。以下是一个简单的示例，以产品管理为例，展示如何将 goods 表分成两个表：goods 和 goods_details，实现数据的分表管理。

示例场景

1.原始 goods 表

有一个原始的 goods 表，其中包含产品的基本信息和详细信息。

CREATE TABLE IF NOT EXISTS goods (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(40),
    brand VARCHAR(40),
    price DECIMAL(10, 2),
    description TEXT 
);

2. 插入示例数据到 goods 表

INSERT INTO goods (name, category, brand, price, description) VALUES
('LaserJet Pro P1606dn 黑白激光打印机', '打印机', '惠普', '1849.00', '稳定的黑白激光打印机。'),
('华硕 R510VC 15.6英寸笔记本', '笔记本', '华硕', '3399.00', '高性能笔记本电脑。'),
('联想 Y400N 14.0英寸笔记本电脑', '笔记本', '联想', '4999.00', '轻薄便携的笔记本电脑。'),
('苹果 iPad Mini 7.9英寸平板电脑', '平板电脑', '苹果', '1998.00', '流畅的操作体验，轻便携带。');

3. 数据分表

为了分表，我们决定将 description 字段从 goods 表中分离出来，创建一个新的表 goods_details，用于存储产品的详细描述。

创建 goods_details 表

CREATE TABLE IF NOT EXISTS goods_details (
    detail_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_id INT UNSIGNED,
    description TEXT,
    FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE
);

4. 将描述数据插入到 goods_details 表

为了将数据从 goods 表迁移到 goods_details 表，你可以执行如下操作：

先插入到 goods_details

INSERT INTO goods_details (goods_id, description)
SELECT id, description FROM goods;

5.更新 goods 表以删除 description 字段

在确认所有描述已经迁移到新表后，您可以更新 goods 表：

ALTER TABLE goods DROP COLUMN description;

更新后表结构

现在，您将拥有两个表：

goods 表 (基础信息)：
- id | name | category | brand | price
goods_details 表 (详细信息)：
- detail_id | goods_id | description

6. 查询示例
通过 JOIN 来查询完整的产品信息，包括基础信息和详细描述：

SELECT g.*, d.description
FROM goods g
LEFT JOIN goods_details d ON g.id = d.goods_id;

数据分表实战演练

准备数据

create table goods(
id int unsigned primary key auto_increment, name varchar(150),
cate varchar(40), brand_name varchar(40),
price decimal(10,3) default 0, is_show bit default 1, is_saleoff bit default 0
);

insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','4999',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

查询练习：

求所有电脑产品的平均价格,并且保留两位小数

select round(avg(price),2) from goods;

查询所有价格大于平均价格的商品，并且按价格降序排序

select id,name,price from goods where price >(select avg(price) from goods) order by price desc;

查询价格大于或等于"超级本"价格的商品，并且按价格降序排列

select id,name,price from goods where price=any(select price from goods where cate='超级本') order by price desc;
-- = any 或者 =some 等价 in ，只要有一个满足就输出

数据分表

-- 创建“商品分类”表
create table if not exists goods_cates(
cate_id int unsigned primary key auto_increment, cate_name varchar(40)
);
-- 查询goods表的所有记录，并且按"类别"分组，然后将分组结果写入到goods_cates数据表中
insert into goods_cates (cate_name) select cate from goods group by cate;
-- 通过goods_cates数据表来更新goods表
-- 连表并复制过去（连表并设置）
update goods as g inner join goods_cates as c on g.cate = c.cate_name set cate = cate_id;

-- 通过create...select来创建数据表并且同时写入记录,一步到位，相当于两条语句中省略了分号
create table goods_brands (
brand_id int unsigned primary key auto_increment,
brand_name varchar(40)) select brand_name from goods group by brand_name;

-- 通过goods_brands数据表来更新goods数据表
update goods as g inner join goods_brands as b on g.brand_name = b.brand_name set g.brand_name = b.brand_id;

-- 查看 goods 的数据表结构,会发现 cate 和 brand_name对应的类型为 varchar 但是存储的都是字符串

-- 修改数据表结构，把cate字段改为cate_id且类型为int unsigned，把brand_name字段改为 brand_id且类型为int unsigned
-- 修改 goods 表，将 cate 字段改为 cate_id，并将类型更改为 INT UNSIGNED
ALTER TABLE goods 
CHANGE COLUMN cate cate_id INT UNSIGNED;

-- 修改 goods 表，将 brand_name 字段改为 brand_id，并将类型更改为 INT UNSIGNED
ALTER TABLE goods 
CHANGE COLUMN brand_name brand_id INT UNSIGNED;

-- 为 goods 表中的 cate_id 添加外键约束，参考 goods_cates 表的 cate_id
ALTER TABLE goods
ADD CONSTRAINT fk_cate
FOREIGN KEY (cate_id) REFERENCES goods_cates(cate_id)
ON DELETE CASCADE;  -- 当 goods_cates 中的记录被删除时，自动删除关联的 goods 记录

-- 为 goods 表中的 brand_id 添加外键约束，参考 goods_brands 表的 brand_id
ALTER TABLE goods
ADD CONSTRAINT fk_brand
FOREIGN KEY (brand_id) REFERENCES goods_brands(brand_id)
ON DELETE CASCADE;  -- 当 goods_brands 中的记录被删除时，自动删除关联的 goods 记录


-- 分别在 good_scates 和 goods_brands表中插入记录
insert into goods_cates(cate_name) values ('路由器'),('交换机'),('网卡'); insert into goods_brands(brand_name) values ('海尔'),('清华同方'),('神舟');

-- 在 goods 数据表中写入任意记录
insert into goods (name,cate_id,brand_id,price) values('LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

-- 查询所有商品的详细信息 (通过左右链接来做)
select * from goods left join goods_cates on goods.cate_id=goods_cates.id inner join goods_brands on goods.brand_id=goods_brands.id;

-- 查询没有商品的品牌(通过右链接或子查询来做)
-- 右链接
	select * from goods right join goods_brands on goods.brand_id =goods_brands.id;
	-- 子查询
	select * from goods_brands where id not in (select DISTINCT brand_id from goods);

内置函数

拼接字符串concat(str1,str2…)
演示
select concat(12,34,'ab');
案例
体现类似"王昭君的家乡是北京"的功能.

包含字符个数length(str)
演示
select length('abc');
案例:查找班级里边名字为两个字的所有学生信息

截取字符串
left(str,len)返回字符串str的左端len个字符 right(str,len)返回字符串str的右端len个字符 substring(str,pos,len)返回字符串str的位置pos起len个字符
演示
select substring('abc123',2,3);
案例:实现王昭君叫王某某,张飞叫张某某的功能

lpad（如果不够左边补齐），rpad（如果不够右边补齐）
lpad：函数语法：lpad(str1,length,str2)。其中str1是第一个字符串，length是结果字符串的长度，str2是一个填充字符串。如果str1的长度没有length那么长，则使用str2在左侧填充；如果str1的长度大于length，则截断。

例子：

去除空格
ltrim(str)返回删除了左空格的字符串str rtrim(str)返回删除了右空格的字符串str

演示

案例:实现左右空格都去掉的功能

大小写转换

lower(str)
upper(str)
演示

数学函数

求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0

求x的y次幂pow(x,y)

获取圆周率PI()

随机数rand()，值为0-1.0的浮点数

案例1:实现0-10之间的随机数
案例2:做出一个从学生中抽奖的功能

日期时间函数

当前日期
select current_date();

当前时间
select current_time();

当前日期时间
select now();

日期格式化
date_format(date,format)
参数format可选值如下
%Y 获取年，返回完整年份
%y 获取年，返回简写年份
%m 获取月，返回月份
%d 获取日，返回天值
%H 获取时，返回24进制的小时数
%h 获取时，返回12进制的小时数
%i 获取分，返回分钟数
%s 获取秒，返回秒数

例：将使用-拼接的日期转换为使用空格拼接，年份改成简单版
select date_format('2016-12-21','%y %m %d');

流程控制

case语法：等值判断说明：当值等于某个比较值的时候，对应的结果会被返回；如果所有的比较值都不相等则返回 else的结果；如果没有else并且所有比较值都不相等则返回null

格式：case 值 when 比较值1 then 结果1 when 比较值2 then 结果2 … else 结果 end as result;

演示

案例:做出一个女同学称为美女,男同学称为帅哥的小功能

自定义函数

创建

delimiter $$ （定义$$为分隔符）
create function 函数名称(参数列表) returns 返回类型
begin 
sql语句 
end
$$ 
delimiter ;（还原";"为分隔符）

delimiter $$ 定义了$$ 暂时取代“;”的作用，切记之后改回来
说明：delimiter用于设置分割符，默认为分号在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符

示例
要求：创建函数my_trim，用于删除字符串左右两侧的空格

step1：定义分割符
delimiter $$

step2：创建函数

create function my_trim(str varchar(100)) returns varchar(100) begin 
return ltrim(rtrim(str)); 
end
$$

step3：还原分割符
delimiter ;

使用自定义函数

select ' abc ',my_trim(' abc ');

存储过程

存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合

语法

delimiter //
create procedure 存储过程名称(参数列表) begin
sql语句
end
// 
delimiter ;

示例:
创建查询过程，查询学生信息
step1：设置分割符
delimiter //
step2：创建存储过程

create procedure proc_stu() 
begin
select * from students; 
end
//

step3：还原分割符
delimiter ;

调用
语法
call 存储过程(参数列表);
call proc_stu();

视图

视图可以看作定义在数据库上的虚拟表，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图

视图的作用：
视图隐藏了底层的表结构，简化了数据访问操作
加强了安全性，使用户只能看到视图所显示的数据

格式：定义视图，建议以v_开头
create view 视图名称 as select语句;
例：创建视图，查询学生对应的成绩信息

create view v_stu_score_course as select
stu.*,cs.courseNo,cs.name courseName,sc.score from
students stu
inner join scores sc on stu.studentNo = sc.studentNo inner join courses cs on cs.courseNo = sc.courseNo;

查看视图：查看表会将所有的视图也列出来
show tables;

使用：视图的用途就是查询
select * from v_stu_score_course;

删除视图
drop view 视图名称;
drop view v_stu_score_course;

事务

事务广泛的运用于订单系统、银行系统等多种场景，多个步骤要不全部执行，要不一条也不执行
事务主要用来处理操作两大，复杂度高的数据，Innodb存储引擎支持事务；MyISAM不支持事务

事务ACID特点
原子性（atomicity）
事务是一个整体，是不可分割的，事务中所有操作要不都执行，要不都不执行
一致性（consistency）
即当事务完成时，数据必须处于一致状态
在事务开始之前，数据处于一致状态，当事务开始后，数据可能处于不一致状态，事务执行完成后，数据处于一致状态。
隔离性（isolation）
对数据进行修改的所有并发事务都是隔离的，以防止多个事务执行时由于交叉执行导致数据不一致；
持久性（durability）
事务处理的结果都是永久的

MySQL事务具有以下四个属性（通常称为ACID属性）：

1、原子性（Atomicity）：事务是一个原子操作，它要么全部成功，要么全部失败回滚。如果事务中的任何操作失败，则所有操作都将回滚到之前的状态，以确保数据库中的数据不会被部分更改。一个 SQL 语句失败了，那么该事务的所有SQL 语句必须都进行回滚，退回到事务前的状态。（COMMIT、ROLLBACK）

实现技术：使用事务日志（也称为重做日志（Redo Log）或写前日志（Write-Ahead Log, WAL））来记录操作。如果事务执行失败，可以通过日志回滚到事务开始前的状态。

2、一致性（Consistency）：事务的执行必须使数据库从一个一致状态转换到另一个一致状态。这意味着事务必须满足所有约束条件，以保持数据的完整性和一致性。比如说你转钱给别人，你的银行卡-100，别人的必须+100。实现技术：MVCC（多版本并发控制），支持并发事务时确保数据的一致性，避免读取不一致的数据。

3、隔离性（Isolation）：并发事务的执行不能相互干扰。事务必须在独立的空间内执行，这意味着它们看起来像是在独占访问数据库。对于事务的操作，主要分成两种：读操作与写操作之间的影响、写操作与写操作之间的影响。写操作与写操作：通过锁（如行锁和表锁）来解决写操作与读操作：主要是通过 MVCC 机制

4、持久性（Durability）：一旦事务完成提交，其结果就是永久性的，并且即使在系统故障的情况下，也必须能够恢复这些结果。持久性是通过 Redo log 和检查点来实现的。

检查点（Checkpoint）是数据库管理系统（DBMS）中用于提高恢复效率的一个重要技术。它允许系统定期将内存中的所有变更（脏页）写入到磁盘，从而减少恢复时需要处理的日志量，降低恢复时间。

事务日志的工作原理

检查点工作原理：

周期性执行：在指定的时间间隔内，或在系统负载较低时，数据库会执行检查点操作。
刷新数据：所有在内存中的变更会被写入到磁盘。这些更新主要涉及修改的页，确保数据库的最新状态在磁盘上可用。
减少恢复时间：在系统崩溃后，恢复过程只需要从最近的检查点开始读取日志，从而减少恢复时的工作量。

检查点和重做日志之间的关系

写入顺序：
- 当进行数据修改时，首先将变更写入重做日志，然后再将数据写入内存。为什么不直接写入到数据库？因为将信息先写入重做日志缓冲区，
  - 1、允许数据库系统以顺序的方式将日志写入磁盘。这比随机写入数据页更高效，尤其是在磁盘 I/O 性能较低的情况下。
  - 2、减少锁争用：如果每次更新都直接修改磁盘上的数据，可能导致系统在高并发场景下出现锁竞争等性能问题。因为多次、频繁地访问磁盘会导致大规模的 I/O 操作及争用。
  - 3、异步提交：写入重做日志后，即使数据还在内存中待写，可以通过异步方式提交或批量写入，从而减少了需要立即对磁盘进行操作的次数。
- 更新的数据最终需要保存到磁盘，但这个步骤会在需要时才执行。
触发检查点：
- 数据库系统会在特定条件下触发检查点（例如系统负载较低时）。检查点会将所有在内存中但未写入磁盘的数据强制写入磁盘。
恢复过程：
- 如果系统崩溃，恢复过程会从最后一次检查点开始。
- 数据库会读取检查点后面的重做日志，只需重做检查点之后的操作，以恢复到一致的状态。

每次检查点执行时，重做日志中的持久化数据状态会被更新。以后即使发生崩溃，恢复时只需要重做自上一个检查点后所记录的更改。这大大减少了需要重做的日志内容，从而减少了恢复时间。

案例
现在A要给B转账500元
1. 检查A的账户余额>500元
2. A 账户中扣除500元;
3. B 账户中增加500元;
A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位

要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎查看表的创建语句，可以看到engine=innodb

方式
开启事务
开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
begin;
提交事务
将缓存中的数据变更维护到物理表中
commit;
回滚事务
放弃缓存中变更的数据
rollback;

实验：
开启两个终端
查询学生信息
select * from students;
终端1增加数据
begin;
insert into students(studentNo,name) values ('013','我是新来的');
select * from students;
此时终端2查询数据发现并没有插入新数据据
select * from students;
终端1提交数据
commit;
终端二查询，发现有新增的数据
select * from students;

设置自动提交
show variables like "autocommit";
set autocommit=0或1

索引

普通索引（看作一个字段）

是最基本的索引，它没有任何限制。

查看索引
show index from 表名\G

创建索引(类似于字段的添加)
方式一：建表时创建索引

create table 
test_table(
id int primary key,
name varchar(10) unique, age int,
index index_age(age)
);

方式二：对于已经存在的表，添加索引
如果指定字段是很长的字符串，建议指定长度形成前缀索引（以前多少个字符做索引）
格式：建议的创建方式：create index 索引名称 on 表名(字段名称(长度))
或者是

alter table students add index idx_studentNo(studentNo);

例：格式

create index age_index on from(age);
create index name_index on from(name(10));

索引前缀
在创建索引时使用length指定该字段数据的前几个字符做索引，通常用于数据较长时

删除索引：
drop index 索引名称 on 表名;

唯一性索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

create unique index uk_sNo on students(studentNo);
show index from students\G;

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

不需要指定名称：alter table students add primary key(studentNo);
show keys from students;
删除主键
alter table students drop primary key;

全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

索引失效：
多条件中有or（如果条件1和条件2有一个没创建索引，都要进行全表扫描，索引失效）
使用like加%模糊查询
索引是表达式的一部分，或者加入判断和计算
字段类型是字符串，数据没有加‘’

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

索引总结
索引扫描方式是全表扫描。对于经常更新的数据的表或者数据较少的表，不建议使用索引
字段中唯一性比较差，不适合创建索引
所以应尽量建立在字段中数据较短的字段，如果数据比较长，应该创建前缀索引
创建索引的字段经常会在where中使用

索引缺点：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

外键foreign key

在数据库中，外键（Foreign Key）是一个表中的字段（或一组字段），其值用于唯一识别另一表中的一条记录。外键用于维护数据的一致性和完整性，通常用来表示两张表之间的关系。

如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）
对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错
查看外键
show create table 表名;

设置外键约束
方式一：创建数据表的时候设置外键约束

方式二：对于已经存在的数据表设置外键约束
alter table 从表名 add [CONSTRAINT 约束名] foreign key (从表字段) references 主表名(主表字段);
删除外键：alter table 表名 drop foreign key 外键名称;
在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率

外键的主要特性

引用其他表的主键：
- 外键通常引用另一张表的主键。这种引用机制使得可以在不同的表之间创建关联。
维护数据完整性：
- 外键约束确保外键字段的值必须在被引用表的相应主键字段中存在，帮助防止非法数据的插入。例如，如果一个记录在父表中不存在，那么在子表中就不能插入有该外键的记录。
支持级联操作：
- 外键可以配置为支持级联操作，例如级联更新（当被引用的主键更新时，相关的外键值也会更新）和级联删除（当被引用的主键删除时，相关的外键记录也会被删除）。

外键的示例

假设我们有两个表：学生表（Students） 和 选课表（Enrollments）

-- 创建 Students 表
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100)
);

-- 创建 Enrollments 表
CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

-- 向 Students 表插入数据
INSERT INTO Students (StudentID, StudentName) VALUES (1, '张三');
INSERT INTO Students (StudentID, StudentName) VALUES (2, '李四');
INSERT INTO Students (StudentID, StudentName) VALUES (3, '王五');

-- 向 Enrollments 表插入数据
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (101, 1, 1001);  -- 张三选了课程 1001
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (102, 2, 1002);  -- 李四选了课程 1002
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (103, 1, 1003);  -- 张三选了课程 1003
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (104, 3, 1001);  -- 王五选了课程 1001

如果尝试在 Enrollments 表中插入不存在于 Students 表的 StudentID，数据库会拒绝这个操作。

进行不合法的插入操作：现在，我们尝试在 Enrollments 表中插入一条记录，其中 StudentID 为 4，这个 ID 在 Students 表中并不存在。

INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES (105, 4, 1001);

字符编码

发展历程
ASCII：基本字符集使用128个常用字符，扩展字符集128个，共256个，用1个字节8位表示。
GB2312：中国国家标准的简体中文字符集，采用2个字节表示
GBK：1万多个汉字，采用2个字节表示
Unicode：两字节表示的世界通用码，它为每种语言中的每个字符设定了统一并且唯一的二进制编码
UTF-8：一种以8个bit为一组的Unicode的表示格式，是一种变长的编码方式，一个中文字符占3个字节
UTF-16：2或4个字节，固定长度

mysql中的字符编码
mysql5.7之前默认使用latinl，8.0开始默认使用utf8mb4

查看表的字符编码：
create table student(id int,name char(10));
show create table student;

默认是拉丁

创建表时指定
create table class(id int,name char(10)) charset=utf8;
show create table class;

查看字符编码变量
show variables like "character%";

修改字符编码
1、编译时修改
2、之后配置文件修改
vim /etc/my.cnf
[mysqld]下加入
character-set-server=utf8

重启服务
systemctl restart mariadb
再次进入，成功修改

如果想要修改某个库的字符编码：
create database abc charset=utf8;
show create database abc;

字符集校验

系统相应修改字符集
ci：不区分大小写
cs：区分大小写
bin：区分大小写

存储引擎

查看存储引擎
查看所有存储引擎
show engines\G

查看默认存储引擎
show variables like "default_storage_engine";

查看表的存储引擎
show create table student\G

创建表时设定存储引擎：
create table 表名（字段1 字段类型，字段2 字段类型,…）engine="存储引擎类型";

常见的存储引擎
Innodb

简介
	支持事务
	行级锁，每一条数据都可以锁定
	存储级别最大支持64TB
Inodb存储文件
	ibdatal：数据文件
	.frm：表结构
适用范围
	以写为主，对数据一致性比较高，支持事务、行级锁和外键约束

MyISAM

简介
	不支持事务
	表级锁
	存储级别最大支持256TB
MySAM存储引擎表存储三个文件：
	.frm 存储表格式
	.MYD 存储数据
	.MYI 存储引擎
适用范围
	以读为主，不频繁更新，插入和查询速度块，对数据一致性要求不高

MEMORY
工作在内存中，速度快、不能永久保存

查看帮助

？+数据类型

数据库设计之三范式

数据库设计中的三范式（Third Normal Form，3NF）是确保数据库结构合理性和减少数据冗余的重要原则。三范式的定义主要包括以下内容：

第一范式（1NF）：
- 确保每列保持原子性，数据表中的每一列都是不可分割的基本数据项。
第二范式（2NF）：
- 消除部分依赖，在满足第一范式的基础上，要求数据库中非主属性完全依赖于主键
- 这意味着如果主键是由多个列组成的，所有非主属性都必须依赖于这个复合主键的所有部分，而不是仅依赖于其中的一部分。
第三范式（3NF）：
- 消除传递依赖。在满足第二范式的基础上，要求所有非主属性必须直接依赖于主键，而不能依赖于其他非主属性。也就是说，如果存在这样的依赖（例如，A -> B，B -> C，且 A 是主键），则需要将其拆分成多个表

实例讲解

第一范式（1NF）：

遵循原子性。即，表中字段的数据，不可以再拆分。

先看一个不符合第一范式的表结构，如下：

员工编码	姓名	年龄
001	销售部小张	28
002	运营部小黄	25
003	技术部小高	22

在这一个表中的，姓名字段下的数据是可以再进行拆分的，因此它不符合第一范式，那怎么样才符合第一范式呢？如下：

员工编码	部门	姓名	年龄
001	销售部	小张	28
002	运营部	小黄	25
003	技术部	小高	22

第二范式（2NF）：

在满足第一范式的情况下，遵循唯一性，消除部分依赖。即，表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。

我们用一个经典案例进行解析。

学号	姓名	年龄	课程名称	成绩	学分
001	小张	28	语文	90	3
001	小张	28	数学	90	2
002	小黄	25	语文	90	3
002	小黄	25	语文	90	3
003	小高	22	数学	90	2

我们先分析一下表结构。

1. 假设学号是表中的唯一主键，那由学号就可以确定姓名和年龄了，但是却不能确定课程名称和成绩。

2. 假设课程名称是表中的唯一主键，那由课程名称就可以确定学分了，但是却不能确定姓名、年龄和成绩。

3. 虽然通过学号和课程名称的联合主键，可以确定除联合主键外的所有的非主键值，但是基于上述两个假设，也不符合第二范式的要求。

我们可以基于上述的三种主键的可能，拆分成 3 张表，保证一张表只描述一件事情。

1. 学生表 - 学号做主键

学号	姓名	年龄
001	小张	28
002	小黄	25
003	小高	22

2. 课程表 - 课程名称做主键

课程名称	学分
语文	3
数学	2

3. 成绩表 - 学号和课程名称做联合主键

学号	课程名称	成绩
001	语文	90
001	数学	90
002	语文	90
002	语文	90
003	数学	90

为什么我们就要遵循第二范式呢？不遵循第二范式会造成什么样的后果呢？

1. 造成整表的数据冗余。

如，学生表，可能我就只有2个学生，每个学生都有许多的信息，比如，年龄、性别、身高、住址......如果与课程信息放到同一张表中，可能每个学生有3门课程，那数据总条数就会变成6条了。但是通过拆分，学生表我们只需要存储 2 条学生信息，课程表只需要存储 3 条课程信息，成绩表就只需保留学号、课程名称和成绩字段。

2. 更新数据不方便。

假设，课程的学分发生了变更，那我们就需要把整表关于该课程的学分都要更新一次，但如果我们拆分出课程表，那我们就只需要把课程表中的课程信息更新就行。

3. 插入数据不方便或产生异常。

假设主键是学号或课程名称，我们新增了某个课程，需要把数据插入到表中，这时，可能只有部分人有选修这门课程，那我们插入数据的时候还要规定给哪些人插入对应的课程信息，同时可能由于成绩还没有，我们需要对成绩置空，后续有成绩后还得重新更新一遍。
假设主键是学号和课程名称的联合主键。同样也是新增了某课程，但是暂时没有人选修这门课，缺少了学号主键字段数据，会导致课程信息无法插入。

第三范式（3NF）：

在满足第二范式的情况下，消除传递依赖。即在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取某非主键字段 B。

仍然用一个经典例子来解析

学号	姓名	班级	班主任
001	小黄	一年级（1）班	高老师

这个表中，学号是主键，它可以唯一确定姓名、班级、班主任，符合了第二范式，但是在非主键字段中，我们也可以通过班级推导出该班级的班主任，所以它是不符合第三范式的。

那怎么设计表结构，才是符合第三范式的呢？

1. 学生表

学号	姓名	班级
001	小黄	一年级（1）班

2. 班级表

班级	班主任
一年级（1）班	高老师

通过把班级与班主任的映射关系另外做成一张映射表，我们就成功地消除了表中的传递依赖了。

尽管范式是一种很好的指导规范，但在实际应用中，我们也不需要太局限在范式中，更多的是应该从项目中出发，设计出合理的表结构。

E-R 图

ER 图（实体-关系图，Entity-Relationship Diagram）是一种用于数据建模的图形化工具，它帮助我们描述数据库的结构和数据之间的关系。ER 图在数据库设计过程中非常重要，因为它提供了一个清晰的图形表示，能够帮助开发人员、设计人员和利益相关者理解系统的具体内容。
ER 图的基本组成部分

实体（Entity）：

实体是指在数据库中需要存储的信息的对象，通常表示为矩形框。
实体可以是物理对象（如学生、教师、产品等）或抽象对象（如课程、订单等）。
实体通常会有一个唯一的标识符（主键）。

属性（Attribute）：

属性是描述实体的特征或信息，通常表示为椭圆形。
每个实体可以有多个属性。

关系（Relationship）：

关系描述了两个或多个实体之间的关系，通常表示为菱形。
关系可以是“一对一”、“一对多”或“多对多”。

ER 图中的关系类型

一对一（1:1）：
每个实体 A 只能与一个实体 B 相关联，反之亦然。
一对多（1:N）：
一个实体 A 可以与多个实体 B 相关联，但每个实体 B 只能与一个实体 A 相关联。
多对多（M:N）：
一个实体 A 可以与多个实体 B 相关联，同时一个实体 B 也可以与多个实体 A 相关联。

发布者：LJH，转发请注明出处：https://www.ljh.cool/6259.html

3 SQL细说（超详细）