本博客java云同桌学习系列,旨在记录本人学习java的过程,并与大家分享,对于想学习java的同学,我希望这个系列能够鼓励大家一同与我学习java,成为“云同桌”。
每月预计保持更新数量三章起,每章都会从整体框架入手,介绍章节所涉及的重要知识点及相关练习题,并会设置推荐学习时间,每篇博客涉及到的点都会在开篇目录进行总览。(博客中所有高亮部分表示是面试题进阶考点)
MySQL数据库
学习周期:两周半
学习建议:数据库的使用几乎是每个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.com/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命令
3.commit;确认提交
或者
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 affected (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 | 预估的需要读取的记录条数 |
filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
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 账户@登录地址;
- 数据的导入和导出
-
数据导出
在cmd命令界面,执行命令:mysqldump -u root -p 要导出的数据库名 > .sql文件路径
或者只导出某个表的话,在导出的数据库名后空格加上表名 -
数据导入
在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)
都学习到这里了,不妨关注点赞一下吧~