本博客java云同桌学习系列，旨在记录本人学习java的过程，并与大家分享，对于想学习java的同学，我希望这个系列能够鼓励大家一同与我学习java，成为“云同桌”。

每月预计保持更新数量三章起，每章都会从整体框架入手，介绍章节所涉及的重要知识点及相关练习题，并会设置推荐学习时间，每篇博客涉及到的点都会在开篇目录进行总览。（博客中所有高亮部分表示是面试题进阶考点）

MySQL数据库

1.数据库介绍
2.卸载MySQL
3.安装MySQL
4. MySQL常用命令
- 4.1 MySQL数据库管理语句
- 4.2 DDL（定义）语句
- - <1> 创建数据库
  - <2> 删除数据库
  - <3> 创建数据库表
  - <4> 删除数据库表
  - <5> 查看表结构desc
  - <6> 修改表结构alter
- 4.3 DML（操作）语句
- - <1> 增加表中数据insert
  - <2> 删除表中数据delete
  - <3> 修改表中数据update
  - <4> 查询表中数据select（基础）
5.MySQL数据类型
- 5.1 字符串数据类型
- 5.2 数值数据类型
- 5.3 日期和时间类型
- 5.4 二进制数据类型
6.MySQL的字段约束
7.MySQL运算符
8.MySQL联结表
- 8.1 一对一关系
- 8.2 一对多或者多对一关系
- 8.3 多对多关系
- 8.4 E-R图
9.MySQL查询进阶
- 9.1聚合函数
- 9.2 分组查询（Group By）
- 9.3 排序查询（Order By）
- 9.4 Limit数据分页查询
- 9.5 子查询语句
- 9.6 ==多表联查==
- - （1）内连接——where语句关系查询
  - （2）内连接——inner join
  - （3）自联结
  - （4）左联结
  - （5）右联结
  - （5）union组合查询
10.==MySQL事务管理==
- 10.1事务的ACID特性
- 10.2 事务的常见问题
- 10.3 事务的隔离级别
11.MySQL存储过程
12.MySQL触发器
- 练习：用触发器实现数据的统计
13.MySQL视图
14.MySQL索引与优化
- 14.1 索引概述
- 14.2 索引的结构——B+树
- 14.3 聚簇索引和非聚簇索引
- 14.4 慢查询日志与Explain执行计划
- 14.5 SQL优化总结
- - 14.5.1 ==索引优化==
  - 14.5.2 SQL语句优化
15.MySQL引擎
16.MySQL权限管理与数据导入导出
17.进阶面试题
- - 17.1 分组过滤
  - 17.2 同表自联结比较
  - 17.3 多层次分组查询
  - 17.4 表格数据行转列case when
18.基础实战练习
- - 数据库结构任务要求：
  - 建立数据库结构：
  - 填入测试数据
  - 任务要求

学习周期：两周半
学习建议：数据库的使用几乎是每个IT人员必须要会的一项技能，因此，可见数据库的重要性，在博主面试的过程中，几乎每家公司都问到了数据库的知识点，并且比重是比较大的，希望读者在学习时候不要急功近利，打好基础，多做数据库相关的练习，将知识点牢牢掌握。

1.数据库介绍

数据库：按照数据结构来组织，存储和管理数据的仓库

作用：
1.对数据进行持久化保存
2.方便数据的增删改查
3.可以处理并发访问
4.安全的权限管理机制

常见的专业数据库软件：

关系型数据库：MySQL、Oracle、PostgreSQL、SQLserver
非关系型数据库：Redis内存数据库，MongoDB文档数据库

关系型数据库：通过关系模型来建立表与表之间的关系

非关系型数据库：指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

MySQL数据库的结构：

一个数据库中，通常由多个表构成，每个表的结构如下图：

2.卸载MySQL

对于尚未安装MySQL的读者，可以跳过本节。

卸载MySQL数据库的步骤：

找到数据库数据文件data，备份数据
关闭MySQL数据库服务，打开任务管理器，找到mysqld的进程，将该进程停止
控制面板中，卸载MySQL数据库
注册表中，删除残留的MYSQL数据

通常所在目录为：
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL

第一个路径也有可能将001替换成其他序号

3.安装MySQL

找到官网 http://dev.mysql/downloads/mysql下载ZIP安装包，建议下载版本为5.7版本，也是企业里最常用的版本，其他版本数据库建议卸载后升级至5.7版本。
下载完成后解压至非中文路径中，例如：E:\MySQL\MySQL 5.7.32\mysql-5.7.32-winx64
在该路径下，如果之前没有数据文件，则新建一个data文件夹，如果有，则将data文件粘贴进来

在该路径下，新建一个my.ini配置文件，内容为：

[mysql] default-character-set=utf8
[mysqld] port = 3306 
basedir=E:\MySQL\MySQL 5.7.32\mysql-5.7.32-winx64
datadir=E:\MySQL\MySQL 5.7.32\mysql-5.7.32-winx64\data 
max_connections=200 
character-set-server=utf8 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true

注意将路径替换成你自己放置的路径

以管理员身份运行cmd，进入bin目录，执行 mysqld --initialize-insecure --user=mysql 命令
在管理员cmd窗口的bin目录下，执行 mysqld install命令安装。完成后会提示安装成功。如果提示已经安装过，则需要输入命令sc query mysql，sc delete mysql，删除该mysql 后重新执行安装命令
在管理员cmd窗口的bin目录下，执行 net start mysql命令启动MySQL服务
为方便命令，添加环境变量路径为：E:\MySQL\MySQL 5.7.32\mysql-5.7.32-winx64\bin
在普通cmd窗口中，进入bin目录，执行 mysql -u root -p 命令，默认没有密码，回车进入。即打开了数据库，可以使用show databases;来查看当前数据库中已有的表

4. MySQL常用命令

SQL语句以分号;作为一条命令的结尾，所以对于多条命令，可以分行输入

SQL语句中的快捷键：

快捷键	作用
\G	格式化输出（文本式，竖立显示）
\s	查看服务器端信息
\c	结束命令输入操作
\q	退出当前sql命令行模式
\h	查看帮助

4.1 MySQL数据库管理语句

数据库连接命令：mysql -h localhost -u root -p -P 3306

  其中：-h 后指定连接数据库的地址，本机连接则为localhost
  		-u 指定用户名
  		-p 指定用户名密码，接下来后弹出密码输入命令，root用户密码默认为空
  		-P 指定连接端口号，MySQL默认端口号为3306

查看所有的数据库show databases;
选择要操作的数据库名 use 数据库名;
查看当前操作数据库的所有表show tables;(前提：必须要使用use命令进入到某个数据库中)

查看建表语句：show create table 表名;
通常用于数据库迁移时复制表结构

  快捷操作命令：
  \G 格式化输出（文本式，竖立显示）
  \s 查看服务器端信息
  \c 结束命令输入操作
  \q 退出当前sql命令行模式
  \h 查看帮助
  \d 修改语句默认结尾符

4.2 DDL（定义）语句

<1> 创建数据库

create database 库名 default charset=utf8mb4;指定要创建的数据库的名字及默认编码

<2> 删除数据库

谨慎操作，数据库删除会连同其中的表结构及数据一同删除，除非已有备份，否则无法恢复
drop database 数据库名;

<3> 创建数据库表

create table [if not exists] 表名(
	字段1 类型,
	字段2 类型
)engine=innodb default charset=utf8;



[if not exists] 可选命令：如果表存在则无需创建，如果不存在就会执行创建命令

<4> 删除数据库表

drop table 数据库表名; 切记谨慎操作

<5> 查看表结构desc

desc 数据库表名;

<6> 修改表结构alter

添加新的字段：alter table 表名 add 字段信息位置参数;
删除字段：alter table 表名 drop 待删除的字段名;
修改字段（更改字段名）：alter table 表名 change 旧字段名新字段名新字段信息;
修改字段（不更改字段名）alter table 表名 modify 新字段名新字段信息;
注意：一般没有特殊情况，不要修改已有的表结构
修改表名：alter table 原表名 rename as 新表名;
修改表引擎：alter table 表名 engine='新的引擎';

4.3 DML（操作）语句

<1> 增加表中数据insert

insert into 表名(字段1,字段2,字段3) values
	(a值1,a值2,a值3),
	(b值1,b值2,b值3);

<2> 删除表中数据delete

delete from 表名 where 字段=某个值;

通过数据库表中的某个字段来确定要删除的整个用户数据

<3> 修改表中数据update

update 表名 set 字段名1=新的数据1,字段名2=新的数据2 where条件;

update 表名 set 字段=字段+值 where 条件;这种会在原来值的基础上增加新值

<4> 查询表中数据select（基础）

查看表中所有数据：select * from 表名;

查看某些字段数据select 字段1，字段2 from 表名;

5.MySQL数据类型

MySQL中，在建立数据库表的结构时，就需要指定将来要存储的数据的数据类型，在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重地影响应用程序的功能和性能。

5.1 字符串数据类型

字符串类型是数据库中最为常用的数据类型，其中常用的有char，varchar和text

char(n) 定长字符串

创建表时，会分配固定的存储空间，只接收不超出其固定长度的字符串数据
varchar(n) 变长字符串

存储长度不等的字符串，如果存储长度小于n，则只分配该字符串长度的空间，存入的字符串长度最大不可以超过n
```
  varchar类型相比char类型更加灵活，但效率更低
```
text 变长文本类型存储

可接收最大长度为64K的字符串文本

5.2 数值数据类型

decimal(m,d) 可变浮点型，可以在括号指定数据m位，其中小数占d位，一般用于存储货币的情况

有符号数值可以存储正或负的数值，无符号数值则只能存储正的数值，但正数存储容量大了一倍（二进制数据第一位是符号位），默认是有符号数，可以通过unsigned设置为无符号数

5.3 日期和时间类型

必须按照时间的格式进入存储数据，如果数据不合法，则为0

类型	大小( bytes)	格式	用途
DATE	3	YYYY-MM-DD	日期值
TIME	3	HH:MM:SS	时间值或持续时间
YEAR	1	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

5.4 二进制数据类型

底层以二进制的形式进行存储数据，可存储图像、多媒体等

6.MySQL的字段约束

在建立表结构时，可以给每一个字段添加约束，使其具有某些特点

约束	含义
unsigned	无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)
例char(5)，varchar(7)	字段类型后面加括号限制宽度
not null	不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错
default	设置默认值
primary key	主键不能为空,且唯一.一般和自动递增一起配合使用。
auto_increment	定义列为自增属性，一般用于主键，数值会自动加1
unique	唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

这里简单说一下主键：
由于表中不允许记录重复，所以引入主键来作为每一行记录的唯一标识

要求：
1.主键不允许为空
2.尽量避免修改主键
3.可以使用自增类型作为主键，自动为每条新记录分配一个自增整数

7.MySQL运算符

学习运算符时我建议看一下菜鸟编程MySQL运算符中对于运算符的详细介绍，每个运算符都有使用实例

以下会列出一些常见的运算符，部分有使用举例：

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间 >=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空
AND	逻辑与
OR	逻辑或

between运算符

  mysql> select * from test;
  +------+-------+---------+
  | name | years | details |
  +------+-------+---------+
  | fb   |    22 | NULL    |
  | wl   |    23 | NULL    |
  | ww   |    20 | NULL    | 
  | zmx  |    22 | NULL    |
  +------+-------+---------+
  
  mysql> select * from test where years between 22 and 23;
  +------+-------+---------+
  | name | years | details |
  +------+-------+---------+
  | fb   |    22 | NULL    |
  | wl   |    23 | NULL    |
  | zmx  |    22 | NULL    |
  +------+-------+---------+
  3 rows in set (0.00 sec)

like 模糊匹配

适用于不知道具体数据内容时进行的查询，不推荐使用，效率较低

通配符	描述
%	代表任意个任意字符
__	代表一个任意字符

  -- 查询name字段中包含五的
  select * from users where name like '%五%';
  	  
  -- 查询表中 name 字段为两个字符的数据
  select * from users where name like '__';
   
  -- 查询 name 字段最后为五，的两个字符的数据
  select * from users where name like '_五';

in 运算符
in（）填入集合，作为where的查询条件

  mysql> select * from test;
  +------+-------+---------+
  | name | years | details |
  +------+-------+---------+
  | fb   |    22 | NULL    |
  | wl   |    23 | NULL    |
  | ww   |    20 | NULL    |
  | zmx  |    22 | NULL    |
  +------+-------+---------+
  4 rows in set (0.18 sec)
  mysql> select * from test where years in (22,23);
  +------+-------+---------+
  | name | years | details |
  +------+-------+---------+
  | fb   |    22 | NULL    |
  | wl   |    23 | NULL    |
  | zmx  |    22 | NULL    |
  +------+-------+---------+
  3 rows in set (0.02 sec)

8.MySQL联结表

MySQL作为一个关系型数据，最大的特点便是通过表与表之间的关系搭建起来的数据存储

关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（ relational））互相关联。

表与表之间的关系，通过外键进行关联。外键存在于两个拥有相同字段的表中，其中一个表的主键在另一个表中作为外键查找数据时，可以通过外键确定数据对应关系。

8.1 一对一关系

一对一关系：一张表中的一条记录与另外一张表中最多有一条明确的关系，两个表的主键是同样的字段

通常用于一个用户的多个信息，为了优化表结构，将经常查询的用户信息放到一个表，不经常查询的信息也以用户id作为主键放到另一个表中

8.2 一对多或者多对一关系

一对多或多对一关系：一张表的主键，作为多个表的外键

这是最为常用的数据库关系，包括接下来的多对多关系也是转化为了一对多关系解决问题

8.3 多对多关系

多对多关系：多个数据都各自对应多个其他表的外键

譬如：每个学生有多个老师，每个老师有多个学生

这样，每个学生的数据表中，都需要多个老师的外键，这显然是不可以的，所以对于多对多关系，通常是在两个表中建立一个中间表，将其转化为两个一对多关系进行存储。

8.4 E-R图

e-r图是最为常用的表示数据库之间关系的图形

名称	图形表示
实体，现在存在的事物	矩形
属性，实体的属性	椭圆
关系，两个实体之间的关系	菱形
映射基数，两个实体之间的关系的详细描述	如下：

1 ： 1 一张表
1 ： m 一对多，多对一//出现关系字段，放到多的m方作为外键
M ： N 多对多//出现关系表

9.MySQL查询进阶

查询语句总览表：

子句	说明	是否必须
select	要返回的列或表达式，字段列表	是
from	查询的数据表	需要在表中查询时
Where	数据行的过滤	否
group by	分组	仅在分组聚合计算时
having	分组后的数据过滤	否
order by	输出排序	否
limit	要提取的结果行数	否

9.1聚合函数

函数名	作用
max()	取得最大值
min()	取得最小值
count()	取得数据个数
sum()	取得数据的和
avg()	取得数据的平均值

count（）的使用方法：select count(统计个数的子段名) from 表名;

取得test表中用于年龄数据的人的个数：
mysql> select * from test;
+------+-------+---------+
| name | years | details |
+------+-------+---------+
| fb   |    22 | NULL    |
| wl   |    23 | NULL    |
| ww   |    20 | NULL    |
| zmx  |    22 | NULL    |
+------+-------+---------+
4 rows in set (0.01 sec)

mysql> select count(years) from test;
+--------------+
| count(years) |
+--------------+
|            4 |
+--------------+
1 row in set (0.40 sec)

9.2 分组查询（Group By）

实际场景中，比较常见的是分组查询，譬如，我想要得到上述例子中年龄分别为20，22，23各有多少人?

这时候就需要对不同年龄的人进行分组，然后统计个数

Group By 分组

一般情况下，都是配合聚合函数进行使用，分组的字段必须是显示的字段
select count(统计个数的子段名) from 表名 group by 要分组的字段名

  那么上述的问题，就可以通过下面的命令进行显示：
  ```
  取得test表中年龄分别为20，22，23的人的个数：
  mysql> select years,count(years) from test group by years;
  +-------+--------------+
  | years | count(years) |
  +-------+--------------+
  |    20 |            1 |
  |    22 |            2 |
  |    23 |            1 |
  +-------+--------------+
  3 rows in set (0.12 sec)
  ```

Having 字句

用于在分组查询后再进行一次过滤having 条件

取得test表中年龄大于等于22的人的个数，并分组列出：
mysql> select years,count(years) from test group by years having years >= 22;
+-------+--------------+
| years | count(years) |
+-------+--------------+
|    22 |            2 |
|    23 |            1 |
+-------+--------------+
2 rows in set (0.11 sec)

9.3 排序查询（Order By）

在查询时，可以对返回的数据结构进行排序

order by 待排序的字段名排序方式

排序方式默认是升序
ASC 升序
Desc 降序

允许多字段排序，会按照字段前后顺序进行逐级比较，先比较第一个排序的字段名，然后如果相同，再比较第二个排序的字段名

练习实例：将test表中的所有信息按照年龄升序的方式显示
mysql> select * from test;
+------+-------+---------+
| name | years | details |
+------+-------+---------+
| fb   |    22 | NULL    |
| wl   |    23 | NULL    |
| ww   |    20 | NULL    |
| zmx  |    22 | NULL    |
+------+-------+---------+
4 rows in set (0.00 sec)

mysql> select * from test order by years asc;
+------+-------+---------+
| name | years | details |
+------+-------+---------+
| ww   |    20 | NULL    |
| fb   |    22 | NULL    |
| zmx  |    22 | NULL    |
| wl   |    23 | NULL    |
+------+-------+---------+
4 rows in set (0.01 sec)

9.4 Limit数据分页查询

有时候只需要一部分数据，就可以使用此命令获得特定顺序数据

提取第n条数据：limit n
跳过m条数据，提取第n条数据：limit m n

练习实例：查询test表中年龄为前3名的信息
mysql> select * from test order by years desc limit 3;
+------+-------+---------+
| name | years | details |
+------+-------+---------+
| wl   |    23 | NULL    |
| fb   |    22 | NULL    |
| zmx  |    22 | NULL    |
+------+-------+---------+
3 rows in set (0.00 sec)

9.5 子查询语句

之前的运算符里，我们将过一个in运算符，in（）填入集合作为条件

同样，select查询语句也可以填入in（）中作为外层select语句的条件，这样就形成了select语句的嵌套，可以利用嵌套关系完成一些复杂情况的查询

mysql> select * from place;
+------+----------+
| id   | place    |
+------+----------+
| 1001 | 北京     |
| 1002 | 呼和浩特 |
+------+----------+

mysql> select * from users;
+------+------+------+------+
| id   | name | age  | sex  |
+------+------+------+------+
| 1002 | 李四 |   22 | 女   |
| 1001 | 王五 |   21 | 男   |
+------+------+------+------+

实例练习：表users中存放用户基础信息和id，表place存放id对应的家庭住址，请找出年龄为21的用户的家庭住址

语句为：
select place from place 
where id in (
	select id from users 
	where age = 21 
);

mysql> select place from place where id in (select id from users where age = 21 );
+-------+
| place |
+-------+
| 北京  |
+-------+
1 row in set (0.00 sec)

子查询嵌套语句是已知的数据库中效率最低的一种语句，尽可能不要使用，可以用后面介绍的自联结来替代。

9.6 多表联查

这里引用了菜鸟编程——Mysql连接的使用的实例

以下为测试数据：

mysql> use RUNOOB;
Database changed
mysql> SELECT * FROM b;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程       | 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)
 
mysql> SELECT * from a;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程      | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程      | 2017-04-12      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 5         | 学习 C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

（1）内连接——where语句关系查询

最简单的一种连接查询方式：

select 表名1.字段名1,表名2.字段名2
from 表名1，表名2
where 表名1.字段名3 = 表名2.字段名3；

即：select填入最终显示的字段，
from填入涉及的表名，
where条件是两个表之间的关系字段（外键），更多的表则需要更多的关系，使用and来连接多个关系

实例练习：连接以上两张表，读取b表中所有runoob_author字段在a表对应的runoob_count字段值,以及对应的id和title

select b.runoob_id,b.runoob_title,a.runoob_count 
from a,b
where a.runoob_auther = b.runoob_auther

结果为：
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程         | 10             |
| 2           | 菜鸟教程         | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+

（2）内连接——inner join

这一次我们使用inner join关键字来进行连接，语法如下：

select 表名1.字段名1,表名2.字段名2
from 表名1
inner join 表名2
on 表名1.字段名3 = 表名2.字段名3；

注意，如果多个表进行join连接的话，则需要多个inner join on语句

则依旧是上面的问题，这一次我们使用inner join on 语句：
连接以上两张表，读取b表中所有runoob_author字段在a表对应的runoob_count字段值,以及对应的id和title

select b.runoob_id,b.runoob_title,a.runoob_count 
from a
inner join b
on a.runoob_auther = b.runoob_auther

结果为：
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程         | 10             |
| 2           | 菜鸟教程         | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+

内连接的图形表示：

（3）自联结

自联结在查询一些特定数据时可以作为子嵌套查询的替代

mysql> SELECT * from b;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程      | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程      | 2017-04-12      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 5         | 学习 C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+

比如，想要查询能够学习PHP的菜鸟编程网站还可以学习什么？

这时候，就需要自己与自己进行联结，为了区分，需要用as取一个别名

自联结语法如下：
select 表名.字段名
from 表名 as 别名1
inner join 表名 as 别名2
on 别名1.字段 = 别名2.字段
where 条件

on里面填写的便是你想要得到的两个字段的共性，where条件指定那个字段

则，上述那个问题：

查询能够学习PHP的菜鸟编程网站还可以学习什么？
select b.runoob_title,b.runoob_author
from b as b1
inner join b as b2
on b1.runoob_author = b2.runoob_author
where b1.runoob_author = "菜鸟编程";

+---------------+---------------+
| runoob_title  | runoob_author |
+---------------+---------------+
| 学习 PHP    | 菜鸟教程         |
| 学习 MySQL  | 菜鸟教程         |
+---------------+---------------+

（4）左联结

以左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null

左联结语法：
select 表名1.字段名1,表名2.字段名2
from 表名1
left join 表名2
on 表名1.字段名3 = 表名2.字段名3

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM a LEFT JOIN b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程         | 10             |
| 2           | 菜鸟教程         | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| 5           | FK              | NULL           |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

左联结，此例中a表作为基准，所以a表中的id和title属性都是完整的

（5）右联结

以右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null

右联结语法：
select 表名1.字段名1,表名2.字段名2
from 表名1
right join 表名2
on 表名1.字段名3 = 表名2.字段名3

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM a RIGHT JOIN b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

右联结，此例中b表作为基准，所以b表中的count属性都是完整的

（5）union组合查询

简单来说，UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中；
多个 SELECT 语句会删除重复的数据；
可以使用排序语句对整个结果集进行排序；

语法：
select 语句
union [all]
select 语句
[order by 字段]

* all可以指定不删除重复数据
* union组合的两个select语句显示的字段必须相同，否则显示不在一起
* 可以指定整个结果的排序

10.MySQL事务管理

事务是面试官经常喜欢问的一个知识点，其实事务是很简单的

事务:指一系列SQL操作所组成的一个程序执行逻辑单元，所有操作要么全部失败，要么全部成功

事务的语法：

 1.start transaction;或者begin;
 2.输入此事务的SQL命令
 3mit；确认提交
 或者
 3.rollback；事务回滚，废弃此事务操作

10.1事务的ACID特性

原子性：整个事务要么全部成功，要不全部失败
一致性：事务在执行前后数据库都是一致的，不能发生数据错乱
隔离性：事务与事务互相隔离，都有各自的数据空间
持久性：事务一旦提交，所操作的数据将永久保存下来

10.2 事务的常见问题

脏读

读取到了未提交存入数据库的数据

例：事务A和B操作同一数据，事务A更新数据，事务B读取了数据，但A随后回滚操作，那么B读到的就是脏数据

不可重复读

多次读取数据期间数据发生了变化

例：事务A和B操作同一数据，事务A在读取数据，事务B更新了数据，此时事务A继续读取数据，发现与之前数据不一致

幻读

多次读取数据期间数据的数量发生了变化

例：事务A和B操作同一数据，事务A在读取数据的count（），事务B插入了一条新数据，此时事务A继续读取数据的count()，发现与之前不一致

10.3 事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（READ_UNCOMMITTED）	允许	允许	允许
读已提交（READ_COMMITTED）	禁止	允许	允许
可重复读（REPEATABLE_READ）	禁止	禁止	可能会
顺序读（SERIALIZABLE）	禁止	禁止	禁止

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。一般数据默认级别是可重复读。

可以使用命令查看和设置当前会话的隔离级别
1.查看会话隔离级别：@@tx_isolation

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.93 sec)

2.设置隔离级别：set session transaction isolation level 隔离级别;

mysql> set session transaction isolation level read uncommitted; 
Query OK, 0 rows aﬀected (0.00sec)

read_uncommite（读未提交）

前一个事务还未提交数据时，下一个事务便进入操作
read_commited（读已提交）

前一个事务提交数据后，数据才可以被下一个事务访问
repeatable_read（可重复读）

前一个事务对同一数据的值的多次操作都完成后，才允许下一个事务进入
serializiable（顺序读）

不允许事务并发，必须依次排序执行

11.MySQL存储过程

存储过程可以理解为函数，是多条MySQL语句的集合，为了特定功能时编写，经过编译后存储进数据库，需要时调用

创建存储过程create procedure 存储过程名()

  \d //
  create procedure p1()
  begin
  set @i=10;
  while @i<90 do
  insert into users values(null,concat('user:',@i),@i,0);
  set @i=@i+1;
  end while;
  end;
  //
  
  * 因为语句中也用到了;所以先将语句结尾更改为//。
  * 使用begin和end限定存储过程代码体。
  * concat（）是连接函数

调用存储过程call 存储过程名()
查看存储过程代码show create procedure 存储过程名\G
删除存储过程drop procudure 存储过程名

像类似与存储过程的处理逻辑，不是数据库所擅长的内容，建议由应用程序java来处理

12.MySQL触发器

触发器:指响应写操作（增、删、改）而提前定义好的、自动执行的一组SQL语句

可以类比理解为我们在前端部分中学的事件，特定事件发生时执行

创建触发器：
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

  说明：
  # trigger_name：触发器名称
  # trigger_time:触发时间，可取值：BEFORE或AFTER
  # trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
  # tb1_name：指定在哪个表上
  # trigger_stmt：触发处理 SQL语句，在begin和end中间写代码块

查看所有触发器show triggers \G
删除触发器drop triggers 触发器名称

在使用触发器时，可以建立虚拟表来方便某些操作

虚拟表	insert触发器	delete触发器	update触发器
new	访问被插入的行	×	访问更新后的新值
old	×	在触发器之前可以读取待删除的行	访问更新前的旧值

练习：用触发器实现数据的统计

需求：

-- 1.创建一个表，users_count 里面有⼀个num的字段 初始值为0或者是你当前users表中的count
-- 2.给users表创建一个触发器

-- 当给users表中执行insert添加数据之后,就让users_count里面 num+1,
-- 当users表中的数据删除时,就让users_count里面 num-1,
-- 想要统计users表中的数据总数时,直接查看 users_count

mysql> select * from users;
+------+------+
| name | age  |
+------+------+
| 小王 |   18 |
| 小刘 |   20 |
+------+------+
2 rows in set (0.01 sec)

mysql> select * from users_count;
+------+
| num  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

创建触发器

  \d //
  
  create trigger trigger_count1 after insert
  on users for each row
  begin
  update users_count set num=num+1;
  end;
  //
  
  create trigger trigger_count2 after delete
  on users for each row
  begin
  update users_count set num=num-1;
  end;
  //
  
  \d ;

插入操作时users_count里面 num+1

  mysql> insert into users (name,age) values("刘三",21);
  Query OK, 1 row affected (0.13 sec)
  
  mysql> select * from users;
  +------+------+
  | name | age  |
  +------+------+
  | 小王 |   18 |
  | 小刘 |   20 |
  | 刘三 |   21 |
  +------+------+
  3 rows in set (0.00 sec)
  
  mysql> select * from users_count;
  +------+
  | num  |
  +------+
  |    1 |
  +------+
  1 row in set (0.00 sec)

删除操作时users_count里面 num-1

  mysql> delete from users where age=20;
  Query OK, 1 row affected (0.14 sec)
  
  mysql> select * from users;
  +------+------+
  | name | age  |
  +------+------+
  | 小王 |   18 |
  | 刘三 |   21 |
  +------+------+
  2 rows in set (0.00 sec)
  
  mysql> select * from users_count;
  +------+
  | num  |
  +------+
  |    0 |
  +------+
  1 row in set (0.00 sec)

13.MySQL视图

视图:封装select动态查询语句的虚拟表

视图本身不包含数据，视图每次只会执行封装好的select语句，可以简化SQL操作，重用SQL语句，保护部分数据

普通的select语句

  mysql> select * from users;
  +------+------+
  | name | age  |
  +------+------+
  | 小王 |   18 |
  | 刘三 |   21 |
  +------+------+
  2 rows in set (0.00 sec)

创建视图：create view 视图名 as 要封装的select语句
```
  create view v_users as select * from users;
```

调用视图：像正常表一样去查询

  mysql> select * from v_users;
  +------+------+
  | name | age  |
  +------+------+
  | 小王 |   18 |
  | 刘三 |   21 |
  +------+------+
  2 rows in set (0.17 sec)

查看当前库中视图：show table status where comment='view' \G;
删除视图：drop view 视图名

14.MySQL索引与优化

14.1 索引概述

索引：帮助MySQL高效获取数据的一种数据结构

索引类似于图书的目录，是一种B+树的有序结构，可以提高数据的检索，降低数据库的IO成本

尤其现在是大数据时代，对于上千万级别的数据，有无索引的查询语句运行时间之差能达到5秒之多

索引类型	描述
主键索引	根据主键建立索引，不允许重复，不允许空值
唯一索引	用来建立索引的列的值是唯一的，允许空值
普通索引	用表中普通列构成的索引，不一定
全文索引	用大文本对象的列构成的索引
组合索引	多个非空列组合构建的索引，需遵循最左原则

添加索引：alter table 表名 add index 索引名(要建立索引的字段)

但建立索引也要适当，因为建立索引会极大的消耗空间，每建立一个索引就会建立一颗B+树，增删改的时候非常麻烦

14.2 索引的结构——B+树

首先，要了解B+树之前，我们先了解一下B树，B树是一种自平衡树状结构，一般多用于存储系统上

但B数还存在许多弊端，还可以进行优化，提高查询效率，便演变成了B+树，数据结构如下图：可以理解为叶子节点存储数据并用链表连接的一种有序二叉树

这样演变之后有几个显而易见的好处：

1.减少磁盘读写代价,因为非叶子节点不存储数据，所以每一块内存中可以包含更多的索引
2.减少随机IO次数，因为叶子节点存在顺序性，所以IO操作有很大一部分都是效率更高的顺序IO
3.查询速度稳定，由于B+树只有查找到叶子节点才可以得到数据，所以所有数据的查询速度都是一样的

14.3 聚簇索引和非聚簇索引

聚簇索引：索引即数据，找到索引便找到了数据

非聚簇索引：索引是一个文件，数据在另一个文件，根据索引文件找到数据的key值，然后到数据文件中根据key值找到数据

14.4 慢查询日志与Explain执行计划

慢查询日志：MySQL提供的一种日志记录，用来记录MySQL中响应时间超过阈值的语句

Explain执行计划:查询语句经过MySQL优化器后会生成一个记录，里面展示了该语句要使用的索引等等

语法：explain 查询语句 \G

mysql> explain select * from test where name='fb' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 30
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

参数	参数解释
id	在一个大的查询语句句中每个 SELECT关键字都对应一个唯一的 id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列列等值查询时，与索引列列进行行等值匹配的对象信息
rows	预估的需要读取的记录条数
ﬁltered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

14.5 SQL优化总结

14.5.1 索引优化

适当建立索引
- 使用自增类型主键
- 对经常进行where判断的字段建立索引
- 尽量对唯一性的字段建立索引
- 考虑联合索引进行索引覆盖
- 索引不是越多越好，索引会占用大量空间，且需要维护
查询时避免索引失效
- 不要在查询的索引列上使用函数
- 不要在查询的索引列上进行运算
- 避免查询条件左右类型不匹配发生隐式转换
- 使用like模糊查询时通配符%在第一位
- 使用联合查询时谨记最左前缀原则（从最左字段开始使用索引）

14.5.2 SQL语句优化

避免嵌套语句，如子查询
尽量查找具有索引的字段
避免多表查询（将复杂查询简单化）

15.MySQL引擎

首先，我们需要明确客户端与服务器的通信方式：客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端发送一段文本（处理结果）

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server 的功能，把真实存取数据的功能划分为存储引擎的功能。

存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

存储引擎	描述
ARCHIVE	用于数据存档（行被插入后不能再修改）
BLACKHOLE	丢弃写操作，读操作会返回空内容
CSV	在存储数据时，以逗号分隔各个数据项
FEDERATED	用来访问远程表
InnoDB	具备外键支持功能的事务存储引擎
MEMORY	置于内存的表
MERGE	用来管理多个MyISAM表构成的表集合
MyISAM	主要的非事务处理存储引擎
NDB	MySQL集群专用存储引擎

其中最为常用的便是Innodb和MyISAM引擎，需要明确知道两种引擎的区别

Innodb	MyISAM
支持事务	不支持事务
分成两种文件进行存储：.frm文件存储表结构.ibd文件存储数据和索引	分成三种文件进行存储：.frm文件存储表结构.myd文件存储数据.myi文件存储索引
聚簇索引	非聚簇索引
无主键会自动生成不可见的主键，索引与数据在一起	不存在主键和索引
支持外键	不支持外键
优点较多，并发情况下表现优异	相对简单，小型应用效率更优

16.MySQL权限管理与数据导入导出

mysql中权限最高的用户是root

创建权限用户的语法：grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;

  授权的操作可以是：insert、select等，或者所有权限 all
   
  # 在mysql中 创建一个 zhangsan 用户，授权可以对tlxy这个库中的所有表 进行 添加和查询 的权限
  grant select,insert on tlxy.* to zhangsan@'%' identified by '123456';
   
  # 用户 lisi。密码 123456 可以对tlxy库中的所有表有 所有操作权限
  grant all on tlxy.* to lisi@'%' identified by '123456';

删除用户drop user 账户@登录地址;

数据的导入和导出
1. 数据导出
  在cmd命令界面，执行命令：mysqldump -u root -p 要导出的数据库名 > .sql文件路径
  或者只导出某个表的话，在导出的数据库名后空格加上表名
2. 数据导入
  在cmd命令界面，执行命令：mysqldump -u root -p 要导入的空数据库名 < .sql文件路径

17.进阶面试题

17.1 分组过滤

用一条 SQL语句句查询出每门课都大于 80分的学生姓名

name	course	score
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

思路：每门课都大于80分，则代表每个人的最低分需要大于80，所以可以按照每个人进行分组，然后通过分组过滤对最低分附加条件

mysql> select * from mst_stu;
+-------+--------+-------+
| name  | course | score |
+-------+--------+-------+
| 张三  | 语文   |    81 |
| 张三  | 数学   |    75 |
| 李四  | 语文   |    76 |
| 李四  | 数学   |    90 |
| 王五  | 语文   |    81 |
| 王五  | 数学   |   100 |
| 王五  | 英语   |    90 |
+-------+--------+-------+
7 rows in set (0.00 sec)

mysql> select name from mst_stu group by name having min(score)>80;
+------+
| name |
+------+
| 王五 |
+------+
1 row in set (0.00 sec)

17.2 同表自联结比较

要求：查询比昨天气温高的日期信息

id	date	temperature
1	2022-04-01	20
2	2022-04-02	25
3	2022-04-03	21
4	2022-04-04	24

思路：很明显需要自身数据之间进行比较，所以可以利用自联结的笛卡尔积性，筛选出日期大一天且温度更高的日期。函数datediff()可以传入两个日期来计算差值

mysql> select s1.*
-> from mst_weather as s1
-> inner join mst_weather as s2
-> on datediff(s1.date,s2.date) = 1
-> and s1.temperature > s2.temperature ;
+----+------------+-------------+
| id | date       | temperature |
+----+------------+-------------+
|  2 | 2022-04-02 |          25 |
|  4 | 2022-04-04 |          24 |
+----+------------+-------------+
2 rows in set (0.01 sec)

17.3 多层次分组查询

要求：查询每个主笔最大的level下对应的最小gap的主播信息

zhuobo_id	level	gap
123	8	20
123	9	40
123	9	30
246	6	30
246	6	20

思路：首先，肯定是根据主播id进行分组并找到最大的level，然后再进行最小gap的查找

mysql> select zhubo_id,level,min(gap) from mst_zhubo 
->where (zhubo_id,level) in 
->(select zhubo_id,max(level) from mst_zhubo 
->group by zhubo_id)
->group by zhubo_id,level;
+----------+-------+----------+
| zhubo_id | level | min(gap) |
+----------+-------+----------+
|      123 |     9 |       30 |
|      246 |     6 |       20 |
+----------+-------+----------+
2 rows in set (0.00 sec)

17.4 表格数据行转列case when

要求：横向显示出每门课程的primary、middle、high的比率

class_id	grade	rate
abc123	primary	70%
abc123	middle	65%
abc123	high	72%
hjkk86	primary	69%
hjkk86	middle	63%
hjkk86	high	74%

这里特地讲一下case when这个语法，这个语法类似于if else：

case when 条件
then 满足条件返回值
else 不满足条件返回值
end

思路：对表所有信息进行查询，显示的字段有class_id和字符匹配primary、middle、high得到的比率

select class_id ,
(case when grade= 'primary' then rate else 0 end) as 'primary',
(case when grade= 'middle' then rate else 0 end) as 'middle',
(case when grade= 'high' then rate else 0 end) as 'high'
from mst_class
group by class_id;

+----------+---------+--------+------+
| class_id | primary | middle | high |
+----------+---------+--------+------+
| abc123   | 70%     | 65%    | 72%  |
| hjkk86   | 69%     | 63%    | 74%  |
+----------+---------+--------+------+
2 rows in set (0.00 sec)

18.基础实战练习

数据库结构任务要求：

数据表:
雇员表(employee):
雇员编号(empid,主键)，姓名(name)，性别(sex),职称(title)，出生日期(birthday)，所属部门(depid)

部门(department):部门编号(depid,主键)，部门名称(depname)

工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工资(titlesalary)，扣除(deduction)

建立数据库结构：

雇员表：
create table employee (
	empid int not null primary key auto_increment,
	name varchar(10) not null,
	sex enum('男','女'),
	title varchar(20),
	birthday date,
	depid int 
)engine=innodb default charset=utf8mb4;

部门表：
create table department(
	depid int not null auto_increment primary key,
    depname varchar(20) not null
)engine=innodb default charset=utf8mb4;


工资表：
create table salary(
	empid int not null,
    basesalary int not null,
    titlesalary int ,
    deduction int 
)engine=innodb default charset=utf8mb4;

填入测试数据

雇员表

insert into employee (name,sex,title,birthday,depid) values
('李四','女','实习生','1990-10-1',1),
('张三','男','工程师','1990-12-1',3),
('王五','男','经理','1998-1-1',2),
('赵四','男','工程师','2020-1-1',3);

mysql> select * from employee;
+-------+------+------+--------+------------+-------+
| empid | name | sex  | title  | birthday   | depid |
+-------+------+------+--------+------------+-------+
|     1 | 李四 | 女   | 实习生 | 1990-10-01 |     1 |
|     2 | 张三 | 男   | 工程师 | 1990-12-01 |     3 |
|     3 | 王五 | 男   | 经理   | 1998-01-01 |     2 |
|     4 | 赵四 | 男   | 工程师 | 2020-01-01 |     3 |
+-------+------+------+--------+------------+-------+
4 rows in set (0.00 sec)

部门表

insert into department (depname) values
('人事'),
('销售'),
('研发');

mysql> select * from department;
+-------+---------+
| depid | depname |
+-------+---------+
|     1 | 人事    |
|     2 | 销售    |
|     3 | 研发    |
+-------+---------+
3 rows in set (0.00 sec)

工资表

insert into salary (empid,basesalary,titlesalary,deduction) values
(1,1000,1000,0),
(2,7000,3000,500),
(3,6000,6000,100),
(4,5000,2000,500);

mysql> select * from salary;
+-------+------------+-------------+-----------+
| empid | basesalary | titlesalary | deduction |
+-------+------------+-------------+-----------+
|     1 |       1000 |        1000 |         0 |
|     2 |       7000 |        3000 |       500 |
|     3 |       6000 |        6000 |       100 |
|     4 |       5000 |        2000 |       500 |
+-------+------------+-------------+-----------+
4 rows in set (0.00 sec)

任务要求

修改表结构，在部门表中添加部门简介字段

mysql> alter table department add depintroduction text;
Query OK, 0 rows affected (1.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc department;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| depid           | int(11)     | NO   | PRI | NULL    | auto_increment |
| depname         | varchar(20) | NO   |     | NULL    |                |
| depintroduction | text        | YES  |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700

mysql> update employee
    -> inner join salary on employee.empid=salary.empid
    -> set employee.title='工程师',salary.basesalary=2000,salary.titlesalary=700
    -> where name='李四';
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select employee.name,employee.title,salary.basesalary,salary.titlesalary
    -> from employee
    -> inner join salary on employee.empid=salary.empid
    -> where employee.name='李四';
+------+--------+------------+-------------+
| name | title  | basesalary | titlesalary |
+------+--------+------------+-------------+
| 李四 | 工程师 |       2000 |         700 |
+------+--------+------------+-------------+
1 row in set (0.00 sec)

删除人事部门的部门记录

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from department
    -> where depname = '人事';
Query OK, 1 row affected (0.42 sec)

mysql> select * from department;
+-------+---------+-----------------+
| depid | depname | depintroduction |
+-------+---------+-----------------+
|     2 | 销售    | NULL            |
|     3 | 研发    | NULL            |
+-------+---------+-----------------+
2 rows in set (0.01 sec)

mysql> update employee set depid = null
    -> where depid not in (select depid from department);
Query OK, 1 row affected (0.39 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+-------+------+------+--------+------------+-------+
| empid | name | sex  | title  | birthday   | depid |
+-------+------+------+--------+------------+-------+
|     1 | 李四 | 女   | 工程师 | 1990-10-01 |  NULL |
|     2 | 张三 | 男   | 工程师 | 1990-12-01 |     3 |
|     3 | 王五 | 男   | 经理   | 1998-01-01 |     2 |
|     4 | 赵四 | 男   | 工程师 | 2020-01-01 |     3 |
+-------+------+------+--------+------------+-------+
4 rows in set (0.09 sec)

mysql> commit;
Query OK, 0 rows affected (0.22 sec)

查询出每个雇员的雇员编号，实发工资，应发工资

mysql> select employee.empid,
    -> (salary.basesalary + salary.titlesalary) as '应发工资',
    -> (salary.basesalary + salary.titlesalary - salary.deduction) as '实发工资'
    -> from employee
    -> left join salary
    -> on employee.empid = salary.empid;
+-------+----------+----------+
| empid | 应发工资 | 实发工资 |
+-------+----------+----------+
|     1 |     2700 |     2700 |
|     2 |    10000 |     9500 |
|     3 |    12000 |    11900 |
|     4 |     7000 |     6500 |
+-------+----------+----------+
4 rows in set (0.11 sec)

查询姓张且年龄小于 40 的员工记录

mysql> select * from employee
    -> where name like '张%'
    -> and (SELECT TIMESTAMPDIFF(YEAR, employee.birthday, CURDATE()) ) < 40;
+-------+------+------+--------+------------+-------+
| empid | name | sex  | title  | birthday   | depid |
+-------+------+------+--------+------------+-------+
|     2 | 张三 | 男   | 工程师 | 1990-12-01 |     3 |
+-------+------+------+--------+------------+-------+
1 row in set (0.00 sec)

查询雇员的雇员编号，姓名，职称，部门名称，实发工资

mysql> select e.empid,e.name,e.title,d.depname,
    -> (s.basesalary + s.titlesalary - s.deduction) as '实发工资'
    -> from employee as e
    -> left join department as d on e.depid = d.depid
    -> inner join salary as s on e.empid = s.empid;
+-------+------+--------+---------+----------+
| empid | name | title  | depname | 实发工资 |
+-------+------+--------+---------+----------+
|     1 | 李四 | 工程师 | NULL    |     2700 |
|     2 | 张三 | 工程师 | 研发    |     9500 |
|     3 | 王五 | 经理   | 销售    |    11900 |
|     4 | 赵四 | 工程师 | 研发    |     6500 |
+-------+------+--------+---------+----------+
4 rows in set (0.11 sec)

查询销售部门的雇员姓名

mysql> select d.depname,e.name
    -> from employee as e
    -> inner join department as d
    -> on e.depid = d.depid
    -> and d.depname = '销售';
+---------+------+
| depname | name |
+---------+------+
| 销售    | 王五 |
+---------+------+
1 row in set (0.00 sec)

统计各职称的人数

mysql> select title,count(*) as '人数'
    -> from employee
    -> group by title;
+--------+------+
| title  | 人数 |
+--------+------+
| 工程师 |    3 |
| 经理   |    1 |
+--------+------+
2 rows in set (0.40 sec)

统计各部门的部门名称，实发工资总和，平均工资

mysql> select d.depname,
    -> (sum(s.basesalary + s.titlesalary - s.deduction)) as '实发工资总和',
    -> (avg(s.basesalary + s.titlesalary - s.deduction)) as '平均工资'
    -> from employee as e
    -> inner join department as d on e.depid = d.depid
    -> inner join salary as s on e.empid = s.empid
    -> group by d.depname;
+---------+--------------+------------+
| depname | 实发工资总和 | 平均工资   |
+---------+--------------+------------+
| 研发    |        16000 |  8000.0000 |
| 销售    |        11900 | 11900.0000 |
+---------+--------------+------------+
2 rows in set (0.38 sec)

查询比销售部门所有员工基本工资都高的雇员姓名

mysql> select e.name
    -> from employee as e
    -> left join department as d on e.depid = d.depid
    -> inner join salary as s on e.empid = s.empid
    -> and (s.basesalary + s.titlesalary - s.deduction) >
    -> (select max(s.basesalary + s.titlesalary - s.deduction)
    -> from employee as e
    -> left join department as d on e.depid = d.depid
    -> inner join salary as s on e.empid = s.empid
    -> and d.depname = '销售'
    -> group by e.empid
    -> );
Empty set (0.01 sec)
-- 无满足条件的数据，接下来我们看一下具体数据：
mysql> select max(s.basesalary + s.titlesalary - s.deduction) as '销售部门与非销售部门最高薪资'
    -> from employee as e
    -> left join department as d on e.depid = d.depid
    -> inner join salary as s on e.empid = s.empid
    -> and d.depname = '销售'
    -> union
    -> select max(s.basesalary + s.titlesalary - s.deduction)
    -> from employee as e
    -> left join department as d on e.depid = d.depid
    -> inner join salary as s on e.empid = s.empid
    -> and d.depname != '销售';
+------------------------------+
| 销售部门与非销售部门最高薪资 |
+------------------------------+
|                        11900 |
|                         9500 |
+------------------------------+
2 rows in set (0.00 sec)

都学习到这里了，不妨关注点赞一下吧~

更多推荐

Java云同桌学习系列（十五）——MySQL数据库