MySQL基础篇

一、MySQL基础

黑窗口命令操作:
连接MySQL服务器：
    mysql -u用户名 -p密码 [-h数据库服务器的IP地址 -P端口号]
​
-h  参数不加，默认连接的是本地 127.0.0.1 的MySQL服务器
-P  参数不加，默认连接的端口号是 3306
、
**上述指令，可以有两种形式：**
密码直接在-p参数之后直接指定。
密码在-p回车之后，在命令行中输入密码，然后回车

1、对数据库的操作

-- 展示所有的数据库
show databases;
​
-- 创建数据库（文件夹）:
creat databases [数据库名称];
create database day06;//如果有相同名字的数据库已经存在了那么程序会报错。
create database [ if not exists ] 数据库名;//数据库不存在,则创建该数据库；如果存在则不创建
​
-- 删除数据库语法
drop databases 数据库名称;//数据库不存在将会报错
drop database day06;
drop database [ if exists ] 数据库名 ;//数据库存在时删除
​
-- 使用/切换数据库（■ 切换文件夹意思）
use [数据库名称];
use day06;
​
查询当前数据库：
select database();

2、创建表

★ 创建表
/*
   语法：
   create table student(
      列名1  列的数据类型1,
      列名2  列的数据类型2,
        ..........
      列名n  列的数据类型n
   );
*/
注意事项：最后一行不能有逗号。
数据类型：/*
        int：整数
        double：小数
        varchar(文字个数): 文字（字符串）
        data/datetime:日期  年月日/年月日时分秒
        */
create table tb_user (
    id int comment 'ID,唯一标识',   # id是一行数据的唯一标识（不能重复）
    username varchar(20) comment '用户名',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别'
) comment '用户表';
/*刚创建的表它是不存在数据的。
*/
★ /*  约束  */
● not null：非空约束
     限制该字段值不能为null 
● unique：唯一约束
     保证字段的所有数据都是唯一、不重复的
● primary key：主键约束
     主键是一行数据的唯一标识，要求非空且唯一
● default：默认约束
     保存数据时，如果未指定该字段值，则采用默认值
● foreign key：外键约束
     让两张表的数据建立连接，保证数据的一致性和完整性
★ 注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
★ 主键自增：auto_increment
- 每次插入新的行记录时，数据库自动生成id字段(主键)下的值
- 具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)
★ 注意：主键增长只会增长，不会因为成员的删除而减少对应的值。
​
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 '用户表';
/******************************************************************************/
★ 数据类型：

类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
TINYINT	1byte	(-128，127)	(0，255)	小整数值
SMALLINT	2bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3bytes	(-8388608，8388607)	(0，16777215)	大整数值
INT/INTEGER	4bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8bytes	(-2^63，2^63-1)	(0，2^64-1)	极大整数值
FLOAT	4bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
DOUBLE	8bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

类型	大小	描述
CHAR	0-255 bytes	定长字符串(需要指定长度)
VARCHAR	0-65535 bytes	变长字符串(需要指定长度)
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

//使用mySql创建一个表
create table 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 '员工表';
​
​

3、删除表中数据

★ 查询当前数据库所有表：
show tables;
★ 查看指定表结构
   desc 表名 ;#可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息
★ 查询指定表的建表语句
show create table 表名 ;
​
删除表 drop table [ if exists ] 表名
drop table if exists tb_emp;  
-- 在删除表时，表中的全部数据也会被删除。
​
★ 查看表
-- 查看表  desc 表名称
desc  student;
-- DML的插入数据
/*
    insert into 表明（字段名1,字段名2）values（值1,值2）
 */
insert into student (id, name, chinese, math, birthday) values (
                      101,'杜文杰',150,150,'2023-01-02');
insert into student(id, name, chinese, math, birthday) value (
                     102,'周星驰',0,0,'2023-01-3');
insert into student(id,chinese, math)value (
 104,150,140
    );
insert into student(id, name, chinese, math, birthday) VALUE (
105,'胡歌',130,120,'2023-01-2');
​
​
★ 条件删除
-- 删除
/*
delete语法：
 delete  from 表名 where 条件
 注意事项：删除一定带条件否则就全删了
            条件：
                =：判断条件相同
                !=:不等于
                >:判断大于
                <:判断小于
                >=:
                <=:
*/
delete from day06.student where id = 101;
案例1：删除tb_emp表中id为1的员工
delete from tb_emp where id = 1;
 删除tb_emp表中所有员工
delete from tb_emp;
​
注意事项:
• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
• 当进行删除全部数据操作时，会提示询问是否确认删除所有数据，直接点击Execute即可。 

4、增加

insert语法：
- 向指定字段添加数据：
    insert into 表名 (字段名1, 字段名2) values (值1, 值2);
全部字段添加数据：
insert into 表名 values (值1, 值2, ...);、
批量添加数据（指定字段）：
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);
批量添加数据（全部字段）：
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);
向tb_emp表的username、name、gender字段插入数据：
-- 因为设计表时create_time, update_time两个字段不能为NULL，所以也做为要插入的字段
insert into tb_emp(username, name, gender, create_time, update_time)
values ('wuji', '张无忌', 1, now(), now());
向tb_emp表的所有字段插入数据：
insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)
values (null, 'zhirou', '123', '周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());
批量向tb_emp表的username、name、gender字段插入数据
insert into tb_emp(username, name, gender, create_time, update_time)
values ('weifuwang', '韦一笑', 1, now(), now()),
       ('fengzi', '张三疯', 1, now(), now());
​
Insert操作的注意事项：
1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
2. 字符串和日期型数据应该包含在引号中。
3. 插入的数据大小，应该在字段的规定范围内。

5、修改

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;
将tb_emp表的所有员工入职日期更新为'2010-01-01'
update tb_emp set entrydate='2010-01-01',update_time=now();
​
注意事项:
1. 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
2. 在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。

二、DQL查询语句

/*语法格式：
SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP  BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数
*/
-- 基础查询：
查询多个字段
select 字段1, 字段2, 字段3 from  表名;
select id,name,chinese,math,birthday from day06.student;
​
-- 基础查询(*的方式不推荐 ，不见名起意)
查询所有字段（通配符）
select *  from  表名;
select * from day06.student;
​
-- 设置别名
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ]  from  表名;
​
-- 去除重复记录
select distinct 字段列表 from  表名;
​
-- 查询指定列
select name,chinese from day06.student;
-- 查询指定列并且去除重复元素，去除重复必须查询的列的值都相同才算重复！！！！
select  distinct name,chinese from day06.student;
select distinct name from day06.student;
​
-- 给列起别名，使用as关键字来起别名，而且as还可以省略！！！！
select name as 姓名, chinese 语文成绩 from day06.student;

1、条件查询

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

构造条件的运算符分为两类：
- 比较运算符
- 逻辑运算符
**比较运算符**       **功能**                 
>                     大于                       
>=                    大于等于                   
<                     小于                       
<=                    小于等于                   
=                     等于                       
<> 或 !=              不等于                     
between ...  and ...  在某个范围之内(含最小、最大值)           
in(...)               在in之后的列表中的值，多选一 
like 占位符            模糊匹配(_匹配单个字符, %匹配任意个字符) 
is null               是null               
-- ****************************************************************************** --
常用的逻辑运算符如下:
**逻辑运算符**    **功能**                    
and 或 &&         并且 (多个条件同时成立)     
or 或 ||          或者 (多个条件任意一个成立) 
not 或 !          非 , 不是                   

2、聚合函数

语法：
select  聚合函数(字段列表)  from  表名 ;
/*************************************************************************/
函数===============功能
count ：按照列去统计有多少行数据。
sum ：  计算指定列的数值和，如果不是数值类型，那么计算结果为0
max ：  计算指定列的最大值
min ：  计算指定列的最小值
avg ：  计算指定列的平均值
注意 :  聚合函数会忽略空值，对NULL值不作为统计。

-- count 不会对null值进计算的。
select count(gender) from tb_emp;
-- 获取总数据量的方式
select count('1') from tb_emp;
select count('*') from tb_emp;
-- 推荐使用

-- 获取某条的最小值
select min(entrydate) from tb_emp;
-- 获取某条的最大值
select max(entrydate) from tb_emp;
-- 获取某条的平均值
select avg(id) from tb_emp;
-- 求取某条总和
select sum(id) from tb_emp;

3、分组查询

● 分组： 按照某一列或者某几列，把相同的数据进行合并输出。
● 分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。
分组查询通常会使用聚合函数进行计算。
● 语法：
select  字段列表  from  表名  [where 条件]  group by 分组字段名  [having 分组后过滤条件];

> 注意事项:
> ​ 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
> ​	执行顺序：where > 聚合函数 > having 

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

-- 根据性别分组
select gender, count(*) from tb_emp group by gender;

-- 对入职时间在2015-01-01之前的职位进行分组
select job, count(*) from tb_emp where entrydate <= '2015-01-01' group by job;

-- 上一条的数量大于等于的的组数
select job, count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;

4、排序查询

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

★ 排序方式：
- ASC ：升序（默认值）
- DESC：降序
-- 按照入职时间进行升序排列
select *from tb_emp order by entrydate asc;
-- 按照入职时间进行降序排列
select *from tb_emp order by entrydate desc;
-- 按照入职时间进行升序排列，当入职时间相同时，按照更新时间进行降序排列
select *from tb_emp order by entrydate asc, update_time desc;
注意事项：如果是升序, 可以不指定排序方式ASC 
注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 

5、分页查询

select  字段列表  from   表名  limit  起始索引, 查询记录数 ;
从起始索引0开始查询员工数据, 每页展示5条记录
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 0 , 5; -- 从索引0开始，向后取5条记录
-- limit 5; 如果查询的是第1页数据，起始索引可以省略，直接简写为：limit 条数

注意事项:

1. 起始索引从0开始。
计算公式 ：起始索引=（查询页码 - 1）* 每页显示记录数

2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

3. 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit  条数

-- 分页查询
-- 查询第一页五条数据
select *from tb_emp
limit 0,5;
-- 查询第二页5条数据
select *from tb_emp
limit 5,5;

select *from tb_emp where name like '张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31'
limit 0,10;
select *from tb_emp where name like '%张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31'
limit 0,10;
-- ***************************************************************************
-- if(条件表达式, true取值 , false取值)
select if(gender=1,'男性员工','女性员工') AS 性别, count(*) AS 人数
from tb_emp
group by gender;

> if(表达式, tvalue, fvalue) ：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue

-- case 表达式 when 值1 then 结果1  when 值2  then  结果2 ...  else  result  end
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   表达式    when   值1   then  结果1   [when 值2  then  结果2 ...]     [else result]     end

在实际开发中，尽量避免用到if或者case when then end，因为这种相当于是逻辑判断的工作最好交给代码业务层来处理，而不是数据库

三、多表设计

1、一对多，一对一，多对多

    一对多（One-to-Many）是指数据库关系模型中的一种关联关系，表示一个实体（表）的记录对应多个关联实体（表）的记录。这种关系常见于现实生活中的许多场景，比如一个部门可以有多个员工，一个订单可以有多个订单项等。

   在MySQL中，可以通过在一方表（通常是“一”那一方）中添加一个外键列来实现一对多关系。该外键列指向多方（通常是“多”那一方）的主键列。
   
   
   外键（Foreign Key）是数据库中用于建立表之间关联关系的一种约束。它定义在一个表中的字段（或一组字段），它引用另一个表中的主键（或唯一键），用于创建表之间的关系。
   
   ★ 外键的作用如下：

1、建立关系：外键可以用来建立表之间的关系，定义了两个表之间的连接点。通过外键，可以将一个表的数据与另一个表的数据关联起来。

2、数据完整性：外键约束可以保证数据的完整性和一致性。当设置外键约束后，只能插入与外键关联表中存在的值。这样可以防止插入无效的值以及避免产生孤立的数据。

4、数据一致性维护：外键约束可以确保数据的一致性。如果外键表中的数据发生变动，例如删除了某个主键值，那么在引用该主键的其他表中的对应外键值将会受到限制或自动更新，以保持数据的一致性。

★ 使用外键时需要注意以下几点：

1、主键和外键的类型要匹配：外键字段的数据类型必须与所引用表的主键（或唯一键）的数据类型相匹配。

2、外键的索引：为了提高查询性能，建议为外键字段创建索引。这样可以加快关联查询的速度。

3、外键约束的创建和删除：外键约束可以在创建表时定义，也可以在表创建后通过ALTER TABLE语句来添加。当不再需要外键约束时，可以使用ALTER TABLE语句来删除。

4、级联操作：在定义外键时，可以指定级联操作。比如当删除主表（被引用表）的某行时，可以选择级联删除或级联更新相关的外键表中的数据。

★ 外键约束的语法：
-- 创建表时指定
create table 表名(
	字段名    数据类型,
	...
	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (主表列名)	
);

-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key(外键字段名) references 主表(主表列名);

-- 修改表： 添加外键约束
alter table tb_emp  
add  constraint  fk_dept_id  foreign key (dept_id)  references  tb_dept(id);

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

- 逻辑外键
  - 概念：在业务层逻辑中，解决外键关联。
  - 通过逻辑外键，就可以很方便的解决上述问题。

在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。 甚至在一些数据库开发规范中，会明确指出禁止使用物理外键 foreign key 

★ 总结：

    外键是一种数据库约束，用于建立表之间的关系和维护数据的完整性。通过外键，可以实现多表之间的关联查询和确保数据的一致性。在设计数据库时，合理使用外键可以提高数据的质量和可靠性。
    在数据库中，通过外键（Foreign Key）关联表之间的数据是一种常见的数据库设计模式。下面是创建外键的方式示例，包括一对多（One-to-Many）、一对一（One-to-One）和多对多（Many-to-Many）的关系：

★ 一对多关系：
    在一对多关系中，一个实体（表）的记录对应多个关联实体（表）的记录。在创建外键时，需将多方表中的外键指向一方表的主键（或唯一键）。    

★ 一对一关系：
    在一对一关系中，一个实体（表）的记录对应一个关联实体（表）的记录。在创建外键时，需将两个表中的主键和外键进行关联。

★ 多对多关系：
    在多对多关系中，一个实体（表）的记录可以与多个关联实体（表）的记录发生多次关联。通常需要通过中间表来实现多对多关系，中间表中包含两个表的主键作为外键。

★ 注意事项：
    在创建外键时，需要确保被引用的列（主键或唯一键）在引用前已经创建好。此外，根据数据库引擎的不同，外键的创建方式可能会有所差异。某些数据库引擎还可能需要启用外键约束的支持。
    
★ 在插入数据时，如果存在一对多关系，需要注意以下几点：

1. 确保主键存在：在插入“一”方表的数据之前，必须确保对应的主键值已经存在于“一”方表中。否则，在插入“多”方表时将无法引用有效的外键值。

2. 设置外键值：在插入“多”方表的数据时，需要设置外键字段的值与对应的“一”方表的主键值相匹配。这样才能建立起正确的一对多关系。

3. 维护数据一致性：插入数据时要确保数据的一致性。也就是说，插入的数据必须符合外键约束，即外键字段的值必须存在于被引用表的主键中。否则，将会引发外键约束错误。

4. 使用事务：对于一对多关系的插入操作，建议使用事务来确保操作的原子性和一致性。在事务中，可以确保要么所有的插入操作都成功，要么全部失败回滚，以保持数据的完整性。

示例：
假设有一个部门表（departments）和一个员工表（employees），一个部门可以有多个员工。在插入员工数据时，需要注意以下事项：

1. 确保部门存在：在插入员工数据之前，先确保对应的部门已经存在于部门表中，这样才能设置正确的外键值。

2. 设置外键值：在插入员工表时，设置外键字段（比如department_id）的值与对应的部门表中的主键值相匹配，以便与部门建立正确的关联关系。

3. 维护数据一致性：插入员工数据时，要确保设置的department_id值在部门表中存在，以满足外键约束。否则，插入操作将失败。

4. 使用事务（可选）：如果涉及到同时插入部门和员工数据，可以使用事务来确保操作的一致性。这样可以保证要么同时成功插入，要么回滚到初始状态。

总之，插入一对多关系的数据时，需要确保参与关联的表和字段的一致性，合理设置外键值，以及使用事务（如果需要）来保证数据的完整性和一致性。


★ 在删除一对多关系的数据时，需要注意以下几点：

1. 处理相关联的数据：在删除“一”方表的数据之前，需要考虑和处理对应的“多”方表的数据。如果存在外键关联，“多”方表的数据可能会依赖于“一”方表的数据。因此，在删除“一”方表的数据之前，需要先删除或更新相关联的“多”方表的数据。

2. 外键约束：删除数据时，要遵守外键约束。确保要删除的数据没有被其他表的外键引用，否则数据库可能会拒绝删除操作。此时，可以选择级联删除或将外键设置为 NULL 值，或者先更新外键引用，再进行删除操作。

3. 事务处理：如果涉及到多个表的删除操作，建议使用事务来确保所有操作的原子性和一致性。在事务中，要么所有的删除操作都成功，要么全部失败回滚，以保持数据的完整性。

示例：
假设有一个部门表（department）和一个员工表（employee），一个部门可以有多个员工。在删除部门数据时，需要注意以下事项：

1. 处理相关联的员工数据：在删除部门数据之前，需要先删除或更新与该部门关联的员工表中的数据。可以根据需求，选择级联删除相关的员工数据，或者将员工的部门外键设为 NULL 或默认值。

2. 遵守外键约束：确保要删除的部门数据不被其他表的外键所引用。如果存在其他表中的外键引用了部门表的数据，需要先处理这些引用，才能顺利删除部门数据。

3. 事务处理（可选）：如果需要确保删除操作的一致性，可以使用事务来包裹删除部门和员工数据的操作，以确保要么全部成功删除，要么全部回滚。

总之，在删除一对多关系的数据时，需要首先处理相关联的数据，遵守外键约束，并考虑使用事务来保证操作的一致性和原子性。这样可以确保数据关联性的正确性和数据的完整性。

2、多表查询

多表查询：查询时从多张表中获取所需数据

> 单表查询的SQL语句：select  字段列表  from  表名;
> 那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select   字段列表  from  表1, 表2;
select * from  tb_emp , tb_dept;
查询结果中包含了大量的结果集，总数 = tb_emp条数 *  tb_dept的总条数，这种现象称之为笛卡尔积。
消除卡尔积的方式：给多表查询加上连接查询的条件即可。
select * from tb_emp , tb_dept where tb_emp.dept_id = tb_dept.id ;

连接查询
1. 内连接：
   内连接查询：查询两表或多表中交集部分数据。
   内连接从语法上可以分为：
  - 隐式内连接：
  select  字段列表   from   表1 , 表2   where  条件 ... ;
/***************************************************************************/
select tb_emp.name , tb_dept.name -- 分别查询两张表中的数据
from tb_emp , tb_dept -- 关联两张表
where tb_emp.dept_id = tb_dept.id; -- 消除笛卡尔积

  - 显式内连接：
  select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;
/***************************************************************************/
select tb_emp.name , tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = 
tb_dept.id;
注意事项:
    一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。
/************************************************************************************/

2. 外连接

   - 左外连接：查询左表所有数据(包括两张表交集部分数据)
select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
-- 左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表匹配的右边表中的数据
select emp.name , dept.name
from tb_emp AS emp left join tb_dept AS dept 
     on emp.dept_id = dept.id;

   
   - 右外连接：查询右表所有数据(包括两张表交集部分数据)
select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

- 右外连接
select dept.name , emp.name from tb_emp AS emp right join  tb_dept AS dept on emp.dept_id = dept.id;

3、子查询

子查询
SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
SELECT * FROM  t1  WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );
子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

根据子查询结果的不同分为:
1. 标量子查询（子查询结果为单个值[一行一列]）
   子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。
常用的操作符： =   <>   >    >=    <   <=   
可以将需求分解为两步：
1. 查询 "教研部" 部门ID
2. 根据 "教研部" 部门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. 列子查询（子查询结果为一列，但可以是多行）
子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。
常用的操作符：

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

案例：查询"教研部"和"咨询部"的所有员工信息

> 分解为以下两步：

> 1. 查询 "销售部" 和 "市场部" 的部门ID
> 2. 根据部门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 = '咨询部');
-- ******************************************************************************************
3. 行子查询（子查询结果为一行，但可以是多列）
子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

案例：查询与"韦一笑"的入职日期及职位都相同的员工信息 
> 可以拆解为两步进行： 
>
> 1. 查询 "韦一笑" 的入职日期 及 职位
> 2. 查询与"韦一笑"的入职日期及职位相同的员工信息 

-- 查询"韦一笑"的入职日期 及 职位
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 = '韦一笑');
-- *****************************************************************************************

4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

案例：查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

> 分解为两步执行：
> 1. 查询入职日期是 "2006-01-01" 之后的员工信息
> 2. 基于查询到的员工信息，在查询对应的部门信息
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 ;
-- ************************************************************************************************

子查询可以书写的位置：
1. where之后
2. from之后
3. select之后

四、事务

   ★ 事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
   ★ 事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。
   
   例如：
-- 删除学工部
delete from dept where id = 1;  -- 删除成功

-- 删除学工部的员工
delete from emp where dept_id = 1; -- 删除失败（操作过程中出现错误：造成删除没有成功）其中员工并没有被删除，但是因为删除了学工部，从而无法获得学工部的员工信息，于是就成为了数据垃圾。

MYSQL中有两种方式进行事务的操作：

1. 自动提交事务：即执行一条sql语句提交一次事务。（默认MySQL的事务是自动提交）
2. 手动提交事务：先开启，再提交 

事务操作有关的SQL语句：
    SQL语句                        描述           
start transaction;/ begin ;    开启手动控制事务 
 commit;                         提交事务         
 rollback;                       回滚事务      
 
 手动提交事务使用步骤：
- 第1种情况：开启事务  =>  执行SQL语句   =>  成功  =>  提交事务
- 第2种情况：开启事务  =>  执行SQL语句   =>  失败  =>  回滚事务

-- 开启事务
start transaction ;
-- 删除学工部
delete from tb_dept where id = 1;
-- 删除学工部的员工
delete from tb_emp where dept_id = 1;
-- 提交事务 (成功时执行)
commit ;
-- 回滚事务 (出错时执行)
rollback ;

1、事务四大特性

★★★ 面试题：事务有哪些特性？

● 1、原子性（Atomicity） ：
    原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的操作要么全部成功，要么全部失败。
● 2、一致性（Consistency）：
    一个事务完成之后数据都必须处于一致性状态。
    ​  如果事务成功的完成，那么数据库的所有变化将生效。
    ​  如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。
● 3、隔离性（Isolation）：
    多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。
    ​  一个事务的成功或者失败对于其他的事务是没有影响。
● 4、持久性（Durability）：
    一个事务一旦被提交或回滚，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。

> 事务的四大特性简称为：ACID

五、数据库的优化方式

数据库优化是一种提高数据库性能和效率的过程，可以采取以下方式进行数据库优化：

1. 优化数据库设计：
   - 合理设计数据库模式，包括表的结构、关系和索引的设计，以适应实际的业务需求。
   - 规范化数据库，消除数据冗余，减少数据的存储空间和提高查询效率。
   - 使用适当的数据类型和字段大小，避免过度使用或浪费存储空间。

2. 优化查询：
   - 使用合适的索引，以加快查询速度。在频繁查询的列上建立索引，但避免过多的索引，以免降低写操作的性能。
   - 使用合适的查询语句，优化 SQL 语句的编写，避免不必要的计算和漫长的查询。
   - 避免使用 SELECT * 查询所有列，只选择需要的列，减少数据的传输量。
   - 调整查询顺序，使用连接（Join）等技术，减少查询的复杂性。

3. 硬件和系统优化：
   - 配置适当的硬件资源，包括 CPU、内存、磁盘和网络等，以满足数据库的需求。
   - 针对具体数据库系统，调整和优化相关的参数设置，以提高性能和并发能力。
   - 定期维护数据库，包括备份、数据清理、重建索引等操作，保持数据库的健康状态。

4. 缓存和缓冲优化：
   - 使用缓存技术，将常用的数据存储在内存中，减少对磁盘的访问，从而提高读取速度。
   - 配置适当的缓冲区大小，优化缓冲区的管理和利用，减少读写操作对磁盘的直接访问。

5. 优化事务管理：
   - 合理划分事务的粒度，尽量减少事务的持有时间和范围，以提高并发处理能力并减轻锁的争用。
   - 对于批量处理或大量数据操作，考虑使用批量提交或分批处理的方式，减少事务的开销和资源占用。

6. 查询性能监控和调优：
   - 使用数据库监控工具，对数据库的查询性能进行监控和分析，找出慢查询和瓶颈，并进行针对性的调优。
   - 使用性能测试工具模拟负载，进行压力测试和性能测试，评估数据库的性能瓶颈和扩展能力。

数据库优化是一个持续的过程，需要不断地监控和调整，根据实际情况进行优化。同时，也要根据具体的数据库系统和应用场景，采取相应的优化策略和技术手段。

1、索引

索引(index)：是帮助数据库高效获取数据的数据结构 。

- 简单来讲，就是使用索引可以提高查询的效率。
优点：

1. 提高数据查询的效率，降低数据库的IO成本。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。
缺点：

1. 索引会占用存储空间。
2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。

   采用二叉搜索树或者是红黑树来作为索引的结构有什么问题？
   说明：如果数据结构是红黑树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发访问，那么一个用户有可能需要23次磁盘IO，那么100万用户，那么会造成效率极其低下。所以为了减少红黑树的高度，那么就得增加树的宽度，就是不再像红黑树一样每个节点只能保存一个数据，可以引入另外一种数据结构，一个节点可以保存多个数据，这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。
-- ***************************************************************************

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

查看索引信息：
show  index  from  表名;
show  index  from  tb_emp;
删除索引
drop  index  索引名  on  表名;
drop index idx_emp_name on tb_emp;

注意事项：
- 主键字段，在建表时，会自动创建主键索引
- 添加唯一约束时，数据库实际上会添加唯一索引