mysql回顾

MYSQL

• MySQL概述
• 常用命令
• Tips1
• 执行顺序
• Tips2
• 多表的连接查询（以三张表为例）
• 子查询
• union
• limit分页
• DML
• 约束
• 存储引擎
• 事务
• 索引
• 视图
• 数据库设计三范式
• 数据库练习题34道

常用端口号：

文件下载：21

上网端口：80

MySQL端口：3306

Oracle端口：1521

MySQL概述

1. sql、DB、DBMS分别是什么？
2. 每个字段名应该包括哪些属性？ 字段名、数据类型、相关约束。
3. SQL语句的分类

​ DQL:查询语句，select相关地市DQL。

​ DML：insert delete update，对表当中的数据进行增删改。

​ DDL:create drop alter 对表结构的增删改。

​ TCL：commit提交事务，rollback回滚事务 。

​ DCL：grant授权na/revoke撤销权限等。

常用命令

//用source命令导入sql脚本
source F:\mysql脚本\bjpowernode.sql

//创建数据库
create database bjpowernode;

//查看所有数据库
show databases;

//使用指定数据库
use bjpowernode;

//查看当前数据库所有的表
show tables;

//查看表结构
desc dept;

//查看mysql版本号
select version();

//结束一条语句
\c

//退出mysql
exit;

//查看表的创建语句
show create table emp;

Tips1

1. 当表数据值是null时，不能使用等号，要使用 is null 和 is not null 来判断是否为空。

2. and 和 or 的优先级？ and优先级高，优先级不确定就加()。

3. in、not in，in等同于or。

4. like模糊查询，%和_用法。(当查询的值中有下划线的时候使用转义字符/下划线来解决)

5. 排序 order by asc、order by desc。

6. 分组函数 count、sum、avg、max、min，所有的分组函数都是对某一组数据进行操作的。

​ 分组函数自动忽略null的那条数据 如:select sum(comm) from emp;

7. 多行处理函数和单行处理函数？ifnull是单行处理函数。
8. 有null参与的运算结果都是null，如(null+1000)*12。

​ ifnull()空处理函数？ ifnull(可能为空的数据,值) 如 ifnull(comm,0) 如果comm是null就当做0来处理。

​ select ename, (ifnull(comm,0)+sal)*12 yearSal from emp;

9. count(*)和count(具体的某个字段)有什么区别？

   count(*)一定是总的记录的条数，count(具体的某个字段))如count(comm)记录的是comm不为null的记录的条数。

10. group by 和 having ？

    group by 是按照某个字段或者某些字段进行分组。

    having是对分组之后的数据进行再次过滤。

    注意:分组函数一般和group by联用，并且任何一个分主函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。(没有group by整张表的数据自成一组)

11. where后面不能直接用分组函数！

12. 当有group by 参与时，select的后面只能跟参加分组的字段和分组函数！！

​ 如：select deptno,job,max(sal) from emp group by deptno,job;

13. 多字段分组查询(如12的sql语句)。

14. having 和 where 的选择？

    优先使用where，where搞不定再用having。

执行顺序

重要！！

select		5
...
from		1
...
where		2
...
group by	3
...
having		4
...
order by	6
...
limit		7
...			

Tips2

1. 关于查询结果的去重？ distinct关键字去除重复记录。

​ 如：select distinct job from emp；

​ select distinct deptno,job from emp；表示deptno和job联合起来去重。

​ 注意：distinct关键字只能出现在所有字段的最前面。

2. 连接查询

   根据表的连接方式来划分：

   ​ 内连接：

   ​ 等值连接

   ​ 非等值连接

   ​ 自连接

   ​ 外连接：

   ​ 左外连接

   ​ 右外连接

   ​ 全连接(很少用)

3. 在表的连接查询方面有一种现象：笛卡尔现象(笛卡尔乘积现象) 加过滤条件可以防止笛卡尔积现象。

​ 案例：找出每一个员工的部门名称，要求显示员工名和部门名。

​ 内连接之等值连接：（连接条件的关系是等量）

​ select e.ename ename,d.dname dname from emp e,dept d where e.deptno=d.deptno;（老版本）

​ select e.ename ename,d.dname dname from emp e join dept d on e.deptno=d.deptno;（新版本join on）

​ inner join…on的inner可以省略。

​ 新版本的优点：将表连接的条件和将来真正数据过滤的where条件分离了。

​

​ 内连接之非等值连接：(连接条件的关系是非等量关系)

​ 先笛卡尔积，然后再过滤即可。

​ 案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

​ select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal order by s.grade;

​ 内连接之自连接：

​ 先自己连自己，然后过滤。

​ 案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

​ select e1.ename ename,e2.ename mgrname from emp e1 join emp e2 on e1.mgr=e2.empno;

​ 该查询结果只有13条数据，因为King没有领导，King的mgr为NULL，不匹配，所以不会查。(外连接会显示)

4. 什么是外连接？外连接和内连接有什么区别？

​ 内连接：A表和B表进行内连接，A和B表没有主副之分，两张表是平等的。

​ 外连接：AB两张表中，一张主表，另一张副表。主要查询主表中的数据，捎带查询副表，当

副表中的数据没有和主表中的数据匹配上时，副表自动模拟出null匹配。

​

​ 外连接之左连接和右连接：

​ 案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

​ select e1.ename ‘员工姓名’,e2.ename ‘领导姓名’ from emp e1 left join emp e2 on e1.mgr=e2.empno;

​ left outer join…on 和 right outer join…on 的outer可以省略。

​ 该查询有14条数据，有King，即使不匹配，但是主表有King，King的领导默认为NULL

​

​ 案例：找出哪个部门没有员工？

​ select * from dept d left join emp e on d.deptno=e.deptno where e.empno is NULL;

注意：外连接使用居多！！！

多张表的连接查询

...
  A
join
  B
join
  C
on
   ...

​ 案例：找出每一个员工的部门名称以及工资等级。

​ select e.ename ‘员工姓名’,d.dname ‘部门名称’,s.grade ‘工资等级’ from emp e join dept d join salgrade s on e.deptno= d.deptno and e.sal between s.losal and s.hisal order by s.grade;

**分析：**emp和dept是内连接中的等值连接e.deptno= d.deptno、和salgrade是内连接中的非等值连接(先笛卡尔积，然后过滤)e.sal between s.losal

​ 案例：找出每一个员工的部门名称以及工资等级、以及上级领导。

​ select e.ename ‘员工姓名’,d.dname ‘部门名称’,s.grade ‘工资等级’,e2.ename ‘上级名称’ from emp e join dept d join salgrade s on e.deptno= d.deptno and e.sal between s.losal and s.hisal left join emp e2 on e.mgr=e2.empno; 注意 left join 的位置

**分析：**和emp e2 的连接是内连接中的自连接。

​

子查询

1. where后的子查询：找出高于平均工资的员工。

​ select ename,sal from emp where sal>(select avg(sal) from emp);

2. from后面嵌套子查询：找出每个部门平均工资的工资等级。

​ select t.deptno,t.avgsal,grade from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;

​ 找出每个部门平均的工资等级。

​ select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;

3. select后嵌套子查询：找出每个员工所在的部门名称，要求显示员工名和部门名。(用得少)

   select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

   常用写法：select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

union

union可以将查询结果集相加

案例：找出工作岗位是SALESMAN和MANAGER的员工？

​ 方式一：select * from emp where job='SALESMAN’or job=‘MANAGER’;

​ 方式二：select * from emp where job in (‘SALESMAN’,job=‘MANAGER’);

​ 方式三：select * from emp where job='SALESMAN’union select * from emp where job=‘MANAGER’;

limit分页(重要)

语法机制：

​ limit startIndex,length

案例：取出前5名员工：

​ select * from emp order by sal desc limit 0,5;

​ select * from emp order by sal desc limit 5;

每页显示3条记录：

第1页：0，3

第2页：3，3

第3页：6，3

第4页：9，3

第5页：12，3

第pageNo页：(pageNo-1)*pageSize,pageSize

pageSize指每页显示多少条数据。

pageNo表示当前页数。

mysql字段数据类型：

int、bigint(java中的long)、float、char、varchar、date、BLOG(二进制大对象:图片、视频等，java中的Object)、CLOB（存储较大的文本：可以存储4G的字符串。）

DML语句

插入：

修改：

删除：

约束

非空、唯一、主键、外键、检查（mysql有，oracle没有）

存储引擎

MyISAM 不支持事务

InnoDB 支持事务，行级锁，外键等，数据的安全得到保障

MEMORY 不支持事务。数据容易丢失(断电数据就没了)，因为所有数据和索引都是存储在内存当中的。优点：查询速度最快。

事务(重点)

• 和事务相关的语句只有DML语句。(insert delete update)
• 一个事务就是一个完整的业务逻辑。
• 事务的存在是为了保证数据的完整性，安全性。
• 通过一个业务需要多条DML语句共同联合完成，必须保证这多条DML语句必须同时成功或者同时失败。

​ 提交事务？

​ 清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。

​ 提交事务标志着，事物的结束。并且是一种全部成功的结束。

​ 回滚事务？

​ 将之前所有的DML操作全部撤销，并且清空事务活动的日志文件

​ 回滚事务标志着事务的结束。并且是一种全部失败的结束。

怎么提交事务（transaction），怎么回滚事务？

​ 提交事务：commit；语句

​ 回滚事务：rollback；语句

怎么将mysql的自动提交机制关闭掉？

​ transaction start；

事务包括4大特性？

A：原子性

说明事务是最小的工作单元。不可再分。

C：一致性

所有事务要求，在同一事务当中，所有的操作必须同时成功，或者同时失败，以保证数据的一致性。

I：隔离性

A事务和B事务之间具有一定的隔离。

D：持久性

事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！

事务与事务之间的隔离级别有哪些？4个级别

​ 读未提交：read uncommitted（最低隔离级别） 没有提交也可以读到

​ 什么是读未提交？

​ 事务A可以读取到事务B未提交的数据。

​ 这种隔离级别存在的问题就是：

​ 脏读现象

​ 我们称读到了脏数据。

​ 这种隔离级别一般地域上理论上的，大多数的数据库都是二档起步！

​ 读已提交：read committed 提交之后才可以读到

​ 事务A只能读取到事务B提交之后的数据。

​ 这种隔离级别解决了上面问题？

​ 这种隔离级别解决了脏读的现象。

​ 这种隔离级别操作上面问题？

​ 不可重复读取数据

​ 什么是不可重复读取数据呢？

​ 在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的 数据是4条，3不等于4

​ 这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。

​ oracle数据库默认的隔离级别是：read committed

​ 可重复读：repeatable read 提交之后也读不到，永远读取的都刚是开启事务时的数据

​ 什么是可重复读取？

​ 事务A开启之后，不管是多久，每次在事务A中读取的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。

​ 可重复度解决了什么问题？

​ 解决了不可重复读取数据。

​ 可重复读操作的问题是什么？

​ 可能会出现幻影读。

​ 每次读取到的数据都是幻象。不够真实！

​ mysql中默认的事务的隔离级别就是这个！！！！！

​ 序列化读：serializable（最高隔离级别） 事务排队，不能并发

​ 这是最高隔离级别，效率最低。解决了所有的问题。

​ 这种隔离级别表示事务排队，不能并发！

​ synchronized，线程同步(事务同步)

​ 每次读取到的数据都是最真实的，并且效率是最低的。

索引

在mysql当中，主键上，以及unique字段上都会自动添加索引的！！！

数据库索引底层使用了B+树。

下面这个看看即可，他还不是B+树。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lcHiDXs2-1668219830226)(D:\笔记图片\数据库索引平衡二叉树.png)]

什么条件下我们会考虑给字段添加索引呢？

​ 条件1：数据量庞大。

​ 条件2：该字段经常出现在where后面。

​ 条件3：该字段很少出现DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

索引怎么创建？怎么删除？语法是什么？

创建索引：

​ create index emp_ename_index on emp(ename);

​ 给emp表的ename字段添加索引，起名：emp_ename_index

删除索引：

​ drop index emp_ename_index on emp;

​ 将emp表上的emp_ename_index索引对象删除。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U5bZi70e-1668219830227)(D:\笔记图片\创建、删除索引.png)]

从上面得出，当不使用索引，直接使用where查询时，需要从头到尾遍历，最后需要14条数据才能找到King；

创建索引后，只需要1条就可以找到King。

索引失效1：

select * from emp where ename like ‘%T’;

ename上技即使添加索引，也不会走索引，为什么？

​ 原因是因为模糊匹配中以"%"开头了。

​ 所有尽量避免模糊查询的时候以"%"开始。

索引失效2：

使用or的时候会失效，除非两个字段都加索引。

索引失效3：

使用复合索引的时候，没有使用左侧的列查找，索引会失效。

什么是复合索引？

​ 两个字段或者多个字段联合起来添加的一个索引，叫做复合索引；

索引失效4：

索引字段参加运算时，索引会失效。

索引失效5：

在where当中索引列使用了函数。

等等索引失效情况。。。

索引的类别：

​ 单一索引

​ 复合索引

​ 主键索引

​ 唯一性索引(使用unique约束的字段)

​ …

​ 注意：唯一性比较弱的字段添加索引的用处不大。

视图

1. 什么是视图？

​ view：站在不同的角度去看待同一份数据。

​ 表复制：

​ create table dept2 as select * from dept;

​ select * from dept2;

2. 怎么创建视图对象？怎么删除视图对象？

   创建视图对象：

​ create view dept2_view as select * from dept2;

​ 删除视图对象：

​ drop view dept2_view;

​ 注意：只有DQL语句才能以view的形式创建。

​ create view view_name as 这里的语句必须是DQL语句；

3. 用视图做什么？

​ 我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作；

通过修改视图的数据，原表数据也会改变

原表数据被更新:

4. 视图在实际的开发中到底有什么用？

​ 视图是用来简化sql语句的，防止sql语句太长！

注意：视图对应的语句只能是DQL语句；

​ 但是视图对象创建完成之后，可以对视图进行增删改查CRUD等操作

数据库设计三范式(面试重点)

1. 什么是数据库设计范式？

   数据库表的设计依据。教你怎么进行数据库表的设计。

2. 数据库设计范式共有3个。

   第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

   第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

   第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

   设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

第一范式：

第二范式：

多对多三张表，关系表两个外键！！！

第三范式：

一对多两张表，多的表加外键！！！

总结表的设计：

一对多：两张表，多的表加外键。

多对多：三张表，关系表俩外键。

一对一：放到一张表中不就行了吗？为啥还要拆分表？

​ 在实际的开发中，可能存在一张表字段太多太庞大。这个时候要拆分表。

​ 一对一怎么设计？

一对一外键唯一！！！

数据库练习题34道

1. 取得每个部门最高薪水的人员名称。
2. 哪些人的薪水在部门的平均薪水之上
3. 取得部门中所有人平均的薪水等级
4. 不用组函数（max）,取得最高薪水
5. 取得平均薪水最高的部门的部门编号（给出两种解决方案）
6. 取得平均薪水最高的部门的部门名称
7. 求平均薪水的等级最低的部门的部门名称
8. 取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名
   找出领导的编号：
9. 取得薪水最高的前五名员工
10. 取得薪水最高的第6到第10名员工
11. 取得最后入职的五个员工
12. 取得每个薪水等级有多少个员工
13. 00
14. 列出所有员工及领导的姓名
15. 列出受雇日期早于其直接上级的所有员工编号，姓名，部门名称（写复杂了）
16. 列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门
17. 列出至少有五个员工的所有部门
18. 列出薪水比Smith多的所有员工信息
19. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数
20. 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
21. 列出部门在“销售部”工作的员工的姓名，假定不知道销售部的部门编号
22. 列出薪水高于公司平均水平的所有员工，所在部门、上级领导、雇员的工资等级
23. 列出与“scott”从事相同工作的所有员工及部门名称
24. 列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水
25. 列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水、部门名称
26. 列出在每个部门工作的员工数量，平均工资和平均服务期限
27. 列出所有员工的姓名、部门名称和工资
28. 列出所有部门的详细信息和人数
29. 列出各种工作的最低工资以及从事此工作的雇员姓名
30. 列出各个部门的manager(领导)的最低薪水
31. 列出所有员工的年工资，按年薪从低到高排序
32. 求出员工领导的薪水超过3000的员工名称与领导
33. 求出部门名称中，带“S”字符的部门员工的工资合计、部门人数
34. 给任职日期超过三十年的员工加薪10%

1..
select t.deptno,d.dname,e.ename,t.maxsal from emp e join (select deptno,max(sal) maxsal from emp group by deptno) t on e.deptno=t.deptno and t.maxsal=e.sal join dept d on d.deptno=t.deptno;
2.
select e.ename,t.deptno,e.sal from emp e join (select deptno,avg(sal) avgsal from emp group by deptno) t on e.deptno=t.deptno and e.sal>avgsal;
3.
select deptno,avg(s.grade) avgsal from emp e join salgrade s on e.sal between s.losal and s.hisal group by deptno;
4.
select * from emp order by sal desc limit 1;
5.
select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
或者:
select t.deptno,max(avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t;
6.
select e.deptno,d.dname,avg(e.sal) avgsal from emp e join dept d on e.deptno=d.deptno group by e.deptno order by avgsal desc limit 1;
7.
select d.dname,t.avgsal,s.grade from dept d join (select deptno,avg(sal) avgsal from emp group by deptno) t on d.deptno=t.deptno join salgrade s on t.avgsal between s.losal and s.hisal order by s.grade limit 1;
8.
select ename,empno,sal from emp where sal>(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
9.
select ename,sal from emp order by sal desc limit 5;
10.
select * from emp order by sal desc limit 5,5;
11.
select * from emp order by hiredate desc limit 5;
12.
select t.grade,count(*) from (select * from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.grade;
13.

14.
select e1.ename '员工名',ifnull(e2.ename,'Big Boss') '领导名' from emp e1 left join emp e2 on e1.mgr=e2.empno;
15.
select e1.empno,e1.ename,d.dname from emp e1 join emp e2 on e1.mgr=e2.empno and e1.hiredate<e2.hiredate join dept d on e1.deptno=d.deptno;
16.
select d.dname,d.deptno,e.ename from dept d left join emp e on d.deptno=e.deptno order by d.deptno;
17.
select * from (select e.deptno deptno,d.dname dname,count(*) cnt from emp e join dept d on e.deptno=d.deptno group by deptno) t where t.cnt>=5;
18.
select * from emp where sal>(select sal from emp where ename='Smith');
19.
select o.ename,o.dname,t.cnt from (select e.ename ename,d.dname dname,e.sal,e.deptno deptno from emp e join dept d on e.deptno=d.deptno where job='clerk') o join (select deptno,count(*) cnt from emp group by deptno) t on o.deptno=t.deptno;
20.
select job,count(*),min(sal) minsal from emp group by job having minsal>1500;
21.
select ename from emp where deptno=(select deptno from dept where dname='sales');
22.
（注意king没有上级，所以要使用left join）
select e.ename '员工名称',d.dname '所在部门',e2.ename '上级领导',s.grade '雇员工资等级' from emp e join dept d on e.deptno=d.deptno left join emp e2 on e.mgr=e2.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal>(select avg(sal) avgsal from emp);
23.
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where job=(select job from emp where ename='scott') and ename <>'scott';
24.
select * from emp where deptno!=30 and sal in(select distinct sal from emp where deptno=30);
25.
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where sal>(select max(sal) from emp where deptno=30);
26.
select d.dname,count(e.ename) cnt,ifnull(avg(e.sal),0) avgsal,avg(timestampdiff(year,e.hiredate,now())) from emp e right join dept d on e.deptno=d.deptno group by e.deptno;
27.
select e.ename,d.dname,sal from emp e join dept d on e.deptno=d.deptno;
28.
select d.deptno,d.dname,d.dname,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by e.deptno;
29.
select t.job,t.minsal,e.ename from emp e join (select job,min(sal) minsal from emp group by job) t on e.job=t.job and e.sal=t.minsal;
30.
select d.deptno,d.dname,min(e.sal) from emp e right join dept d on e.deptno=d.deptno where job='manager' group by deptno;
31.
select ename,12*(sal+ifnull(comm,0)) yearsal from emp order by yearsal;
32.
select e1.ename '员工名',e2.ename '领导名',e2.sal '领导工资' from emp e1 join emp e2 on e1.mgr=e2.empno where e2.sal>3000;
33.
select d.deptno,d.dname,ifnull(sum(sal),0),count(e.ename) from dept d left join emp e on d.deptno=e.deptno where dname like '%S%' group by d.deptno;
34.
update emp set sal=sal*1.1 where timestampdiff(year,hiredate,now())>30;