大家好，欢迎来到IT知识分享网。
MySQL数据库
- B站资源
- 网盘资源
链接：https://pan.baidu.com/share/init?surl=bi7UBfgOb8X9EVHafWHhng
提取码：57fp
文章目录
- MySQL的安装与配置
- DB、DBMS、SQL的关系
- 表（table）
- SQL语句的分类
- 常用命令
- 导入演示数据
- DQL语句
- DML语句
- DDL语句
- 约束
- 存储引擎 (了解内容)
- 事务 (transaction)
- 索引
- 视图(view)
- DBA命令
- 数据库设计的三范式(面试重点)
- 34道作业题
-
- 1. 取得每个部门最高薪水的人员名称
- 2. 哪些人的薪水在部门的平均薪水之上
- 3. 取得部门中（所有人的）平均的薪水等级
- 4. 不用组函数（max），取得最高薪水
- 5. 取得平均薪水最高的部门的部门编号
- 6. 取得平均薪水最高的部门的部门名称
- 7. 求平均薪水的等级最低的部门的部门名称
- 8. 取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
- 9. 取得薪水最高的前五名员工
- 10. 取得薪水最高的第六到第十名员工
- 11. 取得最后入职的 5 名员工
- 12. 取得每个薪水等级有多少员工
- 13. 面试题：
- 14. 列出所有员工及其领导的姓名
- 15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
- 16. 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
- 17. 列出至少有 5 个员工的所有部门, 按照部门编号分组，计数，筛选出 >= 5
- 18. 列出薪金比”SMITH” 多的所有员工信息
- 19. 列出所有”CLERK”( 办事员) 的姓名及其部门名称, 部门的人数
- 20. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
- 21. 列出在部门”SALES”< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
- 22. 列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
- 23. 列出与”SCOTT” 从事相同工作的所有员工及部门名称
- 24. 列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
- 25. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
- 26. 列出在每个部门工作的员工数量, 平均工资和平均服务期限
- 27. 列出所有员工的姓名、部门名称和工资。
- 28. 列出所有部门的详细信息和人数
- 29. 列出各种工作的最低工资及从事此工作的雇员姓名
- 30. 列出各个部门的 MANAGER( 领导) 的最低薪金
- 31. 列出所有员工的 年工资, 按 年薪从低到高排序
- 32. 求出员工领导的薪水超过3000的员工名称与领导
- 33. 求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数
- 34. 给任职日期超过 30 年的员工加薪 10%.
MySQL的安装与配置
- 安装
双击安装文件mysql-5.5.36-win32.msi(见网盘资源)，点击next，勾选协议点击next，选择Typical点击，点击Install，带点击next，点击Finished安装完成。
安装目录：C:\Program Files (x86)\MySQL\MySQL Server 5.5
- 配置
点击Finish后计入配置页面，勾选Detailed Configuration点击next，勾选Developer Machine点击next，勾选Multifunctional Database点击next，选择默认位置点击next，默认勾选点击next，默认端口号3306点击next，手动勾选Manual Selected Default Character Set / Collation选择字符集为utf8，勾选配置环境变量点击next，设置超管密码并勾选远程控制选项，点击execute执行配置，点击Finish配置完成。
DB、DBMS、SQL的关系
- DB：Database，数据库，数据库在硬盘上以文件的形式存在。
- DBMS：Database Management System，数据库管理系统，如：MySQL，Oracle，DB2，Sybase，SqlServer等。
- SQL：Structure Query Language，结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。
DBMS通过执行SQL语句来操作DB中的数据。
表（table）
表（table）是数据库的基本组成单元，数据库中所有的数据都以表的形式组织。
一个表包括行和列：
- 行：数据/记录，（data）
- 列：字段（column），包括字段名，数据类型，相关的约束等。
SQL语句的分类
- DQL（数据查询语言）：查询语句，凡是select语句都是DQL；
- DML（数据操作语言）：insert，delete，update，对表的数据进行增删改；
- DDL（数据定义语言）：create，drop，alter，对表结构的增删改；
- TCL（事务控制语言）：commit提交事务，rollback回滚事务；
- DCL（数据控制语言）：grant授权，revoke撤销权限等。
常用命令
- 查看mysql版本
mysql --version
mysql -V - 创建和使用数据库
create database 数据库名称;
use 数据库名称; - 查询当前使用的数据库
select database(); - 终止一条语句
如果想要终止一条正在编写的语句，可键入\c。 - 退出mysql
exit - 查看现有数据库
show databases; - 指定数据库
use 数据库名称; - 查看当前使用的数据库
select database(); - 查看当前库中的表
show tables; - 查看其他库中的表
show tables from 数据库名称; - 查看表结构
desc 表名; - 查看表的创建语句
show create table 表名; - 删除数据库
drop database 数据库名称;
导入演示数据
- 登录mysql数据库管理系统
dos命令窗口：mysql -uroot -p密码 - 查看有哪些数据库
show databases; - 创建属于我们自己的数据库
create database bjpowernode;(这个不是SQL语句，属于MySQL的命令。) - 使用bjpowernode数据
use bjpowernode;(这个不是SQL语句，属于MySQL的命令。) - 查看当前使用的数据库中有哪些表
show tables; (这个不是SQL语句，属于MySQL的命令。) - 初始化数据
source D:\course\05-MySQL\resources\bjpowernode.sql
bjpowernode.sql，这个文件以sql结尾，这样的文件被称为“sql脚本”。什么是sql脚本呢？
当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
注意：直接使用source命令可以执行sql脚本。
sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。
该数据的每张表的内容如下，之后的内容都是基于这些数据进行操作演示。
表
show tables;
| Tables_in_bjpowernode |
|---|
| dept |
| emp |
| salgrade |
员工表emp：
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
部门表dept
| DEPTNO | DNAME | LOC |
|---|---|---|
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
薪资等级表
| GRADE | LOSAL | HISAL |
|---|---|---|
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
DQL语句
- 任何一条sql语句以
;结尾 - sql语句不区分大小写
- 建议字符串使用单引号
''括起来，虽然mysql支持双引号，但不通用。
简单的查询语句
select 字段名1,字段名2,字段名3,.... from 表名;
example:
- 查询员工的年薪？（字段可以参与数学运算。）
select ename,sal * 12 from emp; - 给查询结果的列重命名
select ename,sal * 12 as yearsal from emp; - 别名中有中文
select ename,sal * 12 as 年薪 from emp;// 错误
select ename,sal * 12 as '年薪' from emp; - as关键字可以省略？
select empno,ename,sal * 12 yearsal from emp; - 查询所有字段
select * from emp;// 实际开发中不建议使用*，效率较低。
条件查询
语法：select 字段,字段... from 表名 where 条件;
执行顺序：先from，然后where，最后select。
| 运算符 | 说明 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between … and … | 两个值之间，等同于>= and <= |
| is null | 为空，（not null）不为空 |
| and | 并且 |
| or | 或者 |
| in | 包含，相当于多个or |
| not | 取非，主要用在is或in中 |
| like | 模糊查询，支持%或者下划线匹配，%匹配任意个字符，_匹配一个字符 |
- 查询工资等于5000的员工姓名
select ename from emp where sal = 5000; - 查询SMITH的工资
select sal from emp where ename = 'SMITH';// 字符串使用单引号括起来。 - 找出工资高于3000的员工
select ename,sal from emp where sal > 3000; - 找出工资不等于3000的？
select ename,sal from emp where sal <> 3000;
select ename,sal from emp where sal != 3000; - 找出工资在1100和3000之间的员工，包括1100和3000
select ename,sal from emp where sal >= 1100 and sal <= 3000;
select ename,sal from emp where sal between 1100 and 3000;// between…and…是闭区间 [1100 ~ 3000]
between and在使用的时候必须左小右大。
between and除了可以使用在数字方面之外，还可以使用在字符串方面。
select ename from emp where ename between 'A' and 'C';
select ename from emp where ename between ‘A’ and ‘D’; // 左闭右开。 - 找出哪些人津贴为NULL
在数据库当中NULL不是一个值，代表什么也没有，为空。空不是一个值，不能用等号衡量。必须使用 is null或者is not null。
select ename,sal,comm from emp where comm is null; - 找出哪些人津贴不为NULL？
select ename,sal,comm from emp where comm is not null; - 找出哪些人没有津贴？
select ename,sal,comm from emp where comm is null or comm = 0; - 找出工作岗位是MANAGER和SALESMAN的员工？
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; - and和or联合起来用：找出薪资大于1000的并且部门编号是20或30部门的员工。
select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno = 30;// 错误的
select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);// 正确的。
注意：当运算符的优先级不确定的时候加小括号。 - in等同于or：找出工作岗位是MANAGER和SALESMAN的员工？
select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
select ename,job from emp where job in('SALESMAN', 'MANAGER');
select ename,job from emp where sal in(800, 5000);// in后面的值不是区间，是具体的值。 - 模糊查询like
-
- 找出名字当中含有O的
在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_
%代表任意多个字符，_代表任意1个字符。
select ename from emp where ename like '%O%';
- 找出名字当中含有O的
-
- 找出名字中第二个字母是A的
select ename from emp where ename like '_A%';
- 找出名字中第二个字母是A的
-
- 找出名字中最后一个字母是T的
select ename from emp where ename like '%T';
- 找出名字中最后一个字母是T的
排序
| 语法 | 执行顺序 |
|---|---|
| select 字段 | 3 |
| from 表名 | 1 |
| where 条件 | 2 |
| order by … | 4 |
order by是最后执行的。
- 按照工资升序，找出员工名和薪资？
select ename,sal from emp order by sal;
注意：默认是升序。怎么指定升序或者降序呢？asc表示升序，desc表示降序。
select ename , sal from emp order by sal;// 升序
select ename , sal from emp order by sal asc;// 升序
select ename , sal from emp order by sal desc;// 降序。 - 按照工资的降序排列，当工资相同的时候再按照名字的升序排列。
select ename,sal from emp order by sal desc , ename asc;
注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。 - 找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列。
select ename,job,sal from emp where job = 'SALESMAN' order by sal desc;
单行处理函数
什么是单行处理函数？输入一行，输出一行。
- 计算每个员工的年薪
select ename,(sal+comm)*12 as yearsal from emp;
重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。
使用ifnull函数：
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp; ifnull()空处理函数
ifnull(可能为NULL的数据,被当做什么处理) ： 属于单行处理函数。
select ename,ifnull(comm,0) as comm from emp;
分组函数（多行处理函数）
| 分组函数 | 描述 |
|---|---|
| count | 计数 |
| sum | 求和 |
| avg | 平均值 |
| max | 最大值 |
| min | 最小值 |
分组函数一共5个。
分组函数还有另一个名字：多行处理函数。
多行处理函数的特点：输入多行，最终输出的结果是1行。
Note：所有的分组函数都是对“某一组”数据进行操作的。
- 找出工资总和？
select sum(sal) from emp; - 找出最高工资？
select max(sal) from emp; - 找出最低工资？
select min(sal) from emp; - 找出平均工资？
select avg(sal) from emp; - 找出总人数？
select count(*) from emp;
select count(ename) from emp; - 分组函数自动忽略null
select count(comm) from emp;
select sum(comm) from emp where comm is not null;// 不需要额外添加这个过滤条件。sum函数自动忽略NULL。 - 找出工资高于平均工资的员工？
select ename,sal from emp where sal > avg(sal);// ERROR 1111 (HY000): Invalid use of group function
思考以上的错误信息：无效的使用了分组函数？
原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。
怎么解释？
因为group by是在where执行之后才会执行的。
| 语法 | 执行顺序 |
|---|---|
| select … | 5 |
| from … | 1 |
| where … | 2 |
| group by … | 3 |
| having … | 4 |
| order by … | 6 |
- count(*)和count(具体的某个字段)，他们有什么区别
count(*): 不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(comm): 表示统计comm字段中不为NULL的数据总数量。 - 分组函数也能组合起来用：
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp; - 找出工资高于平均工资的员工？
第一步：找出平均工资
select avg(sal) from emp;
| avg(sal) |
|---|
| 2073. |
第二步：找出高于平均工资的员工
select ename,sal from emp where sal > 2073.;
| ename | sal |
|---|---|
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
结合第一步和第二步：
select ename,sal from emp where sal > (select avg(sal) from emp);
分组查询（group by 和 having）
- group by ：按照某个字段或者某些字段进行分组。
- having : 对分组之后的数据进行再次过滤。
Note: 分组函数通常和group by联合使用
- 找出每个工作岗位的最高薪资。
select max(sal),job from emp group by job;
注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。
select ename,max(sal),job from emp group by job;
以上在mysql当中，查询结果是有的，但是结果没有意义，在Oracle数据库当中会报语法错误。
Oracle的语法规则比MySQL语法规则严谨。
记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
- 每个工作岗位的平均薪资？
select job,avg(sal) from emp group by job; - 找出每个部门不同工作岗位的最高薪资。
select
deptno,job,max(sal)
from
emp
group by
deptno,job; - 找出每个部门的最高薪资，要求显示薪资大于2900的数据。
第一步：找出每个部门的最高薪资
select max(sal),deptno from emp group by deptno;
第二步：找出薪资大于2900
select max(sal),deptno from emp group by deptno having max(sal) > 2900;// 这种方式效率低。
select max(sal),deptno from emp where sal > 2900 group by deptno;// 效率较高，建议能够使用where过滤的尽量使用where。 - 找出每个部门的平均薪资，要求显示薪资大于2000的数据。
第一步：找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;
第二步：要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000; - where后面不能使用分组函数：
select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno;// 错误了。
这种情况只能使用having过滤。
总结DQL语句
| 语法 | 执行顺序 |
|---|---|
| select … | 5 |
| from … | 1 |
| where … | 2 |
| group by … | 3 |
| having … | 4 |
| order by … | 6 |
补充知识（distinct）
`distinct`只能出现在所有字段的最前面。 select distinct job from emp; // distinct关键字去除重复记录。
select distinct deptno,job from emp;
- 统计岗位的数量？
select count(distinct job) from emp;
连接查询
在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。
连接查询的分类:
根据语法出现的年代来划分的话，包括：
- SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）
- SQL99（比较新的语法）
根据表的连接方式来划分，包括：
- 内连接：
1. 等值连接
2. 非等值连接
3. 自连接 - 外连接：
1. 左外连接（左连接）
2. 右外连接（右连接）
3. 全连接（这个不讲，很少用！）
笛卡尔积现象
笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。
问题：找出每一个员工的部门名称，要求显示员工名和部门名。
select ename,dname from emp,dept;
| ename | dname |
|---|---|
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
| … | … |
| 56 rows in set (0.00 sec) |
这条语句就会产生笛卡尔积现象，即每个员工都会和每个部门名匹配出现，共产生14*4=56条记录。
怎么避免笛卡尔积现象？当然是加条件进行过滤。
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; 内连接
语法：
... A join B on 连接条件 where ... - 等值连接
- 查询每个员工的部门名称，要求显示员工名和部门名
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; // inner可以省略的，带着inner目的是可读性好一些。 select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;
- 非等值连接
- 找出每个员工的工资等级，要求显示员工名、工资、工资等级
select e.ename,e.sal,s.grade from emp e inner join // inner 可省略 salgrade s on e.sal between s.losal and s.hisal;
- 找出每个员工的上级领导，要求显示员工名和对应的领导名。
select a.ename as '员工名',b.ename as '领导名' from emp a inner join emp b on a.mgr = b.empno; 外连接
什么是外连接，和内连接有什么区别？
内连接： 假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。 AB两张表没有主副之分，两张表是平等的。 外连接： 假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中 的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。 外连接的分类？ 左外连接（左连接）：表示左边的这张表是主表。 右外连接（右连接）：表示右边的这张表是主表。 左连接有右连接的写法，右连接也会有对应的左连接的写法。 - 找出每个员工的上级领导？（所有员工必须全部查询出来。）
内连接： select a.ename '员工', b.ename '领导' from emp a join emp b on a.mgr = b.empno; 外连接：（左外连接/左连接） select a.ename '员工', b.ename '领导' from emp a left join emp b on a.mgr = b.empno; // outer是可以省略的。 select a.ename '员工', b.ename '领导' from emp a left outer join emp b on a.mgr = b.empno; 外连接：（右外连接/右连接） select a.ename '员工', b.ename '领导' from emp b right join emp a on a.mgr = b.empno; // outer可以省略。 select a.ename '员工', b.ename '领导' from emp b right outer join emp a on a.mgr = b.empno; 外连接最重要的特点是：主表的数据无条件的全部查询出来。
- 找出哪个部门没有员工
select d.* from emp e right join dept d on e.deptno = d.deptno where e.empno is null; 三张表的连接查询
语法：
... A join B join C on ... 表示A表和B表先进行表连接，连接之后A表继续和C表进行连接。或者理解为A和B连接之后的结果再和C连接。
- 找出每一个员工的部门名称以及工资等级
select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on - 找出每一个员工的部门名称、工资等级、以及上级领导。
select e.ename '员工',d.dname,s.grade,e1.ename '领导' from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp e1 on e.mgr = e1.empno;
子查询
select ..(select). from ..(select). where ..(select). where子句中使用子查询
- 找出高于平均薪资的员工信息
select * from emp where sal > (select avg(sal) from emp);
from后面嵌套子查询
- 找出每个部门平均薪水的等级
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值） select deptno,avg(sal) as avgsal from emp group by deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916. | | 20 | 2175.000000 | | 30 | 1566. | +--------+-------------+ 第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal; +--------+-------------+-------+ | deptno | avgsal | grade | +--------+-------------+-------+ | 30 | 1566. | 3 | | 10 | 2916. | 4 | | 20 | 2175.000000 | 4 | +--------+-------------+-------+ - 找出每个部门平均的薪水等级
第一步：找出每个员工的薪水等级。 select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; +--------+---------+--------+-------+ | ename | sal | deptno | grade | +--------+---------+--------+-------+ | SMITH | 800.00 | 20 | 1 | | ALLEN | 1600.00 | 30 | 3 | | WARD | 1250.00 | 30 | 2 | | JONES | 2975.00 | 20 | 4 | | MARTIN | 1250.00 | 30 | 2 | | BLAKE | 2850.00 | 30 | 4 | | CLARK | 2450.00 | 10 | 4 | | SCOTT | 3000.00 | 20 | 4 | | KING | 5000.00 | 10 | 5 | | TURNER | 1500.00 | 30 | 3 | | ADAMS | 1100.00 | 20 | 1 | | JAMES | 950.00 | 30 | 1 | | FORD | 3000.00 | 20 | 4 | | MILLER | 1300.00 | 10 | 2 | +--------+---------+--------+-------+ 第二步：基于以上结果，继续按照deptno分组，求grade平均值。 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; +--------+--------------+ | deptno | avg(s.grade) | +--------+--------------+ | 10 | 3.6667 | | 20 | 2.8000 | | 30 | 2.5000 | +--------+--------------+
在select后面嵌套子查询
- 找出每个员工所在的部门名称，要求显示员工名和部门名
方法一： select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; 方法二： select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
union（将查询结果相加）
- 找出工作岗位是SALESMAN和MANAGER的员工
第一种：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; 第二种：select ename,job from emp where job in('MANAGER','SALESMAN'); +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | +--------+----------+ 第三种：union select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN'; +--------+----------+ | ename | job | +--------+----------+ | JONES | MANAGER | | BLAKE | MANAGER | | CLARK | MANAGER | | ALLEN | SALESMAN | | WARD | SALESMAN | | MARTIN | SALESMAN | | TURNER | SALESMAN | +--------+----------+
limit（重点，用于分页查询）
- limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
- limit取结果集中的部分数据，这时它的作用。
语法机制：
limit startIndex, length - 取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc; select ename,sal from emp order by sal desc limit 0, 5; select ename,sal from emp order by sal desc limit 5; - limit是sql语句最后执行的一个环节：
select 5 ... from 1 ... where 2 ... group by 3 ... having 4 ... order by 6 ... limit 7 ...; - 找出工资排名在第4到第9名的员工？
select ename,sal from emp order by sal desc limit 3,6; +--------+---------+ | ename | sal | +--------+---------+ | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | +--------+---------+ - 通用的标准分页sql？
每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3
每页显示pageSize条记录：
第pageNo页：(pageNo – 1) * pageSize, pageSize
pageSize是什么？是每页显示多少条记录
pageNo是什么？显示第几页java代码{ int pageNo = 2; // 页码是2 int pageSize = 10; // 每页显示10条 limit (pageNo - 1) * pageSize, pageSize }
DML语句
创建表
建表语句的语法格式：
create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型, .... ); 关于MySQL中字段的常见的数据类型
int 整数型(java中的int) bigint 长整型(java中的long) float 浮点型(java中的float double) char 定长字符串(String) varchar 可变长字符串(StringBuffer/StringBuilder) date 日期类型 （对应Java中的java.sql.Date类型） BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object） CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object） ...... char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。
表名在数据库当中一般建议以：t_或者tbl_开始。
创建学生表，学生信息包括： 学号、姓名、性别、班级编号、生日 学号：bigint 姓名：varchar 性别：char 班级编号：int 生日：char create table t_student( no bigint, name varchar(255), sex char(1), classno varchar(255), birth char(10) ); 删除数据
语法格式：
delete from 表名 where 条件; 注意：没有条件全部删除。
- 删除10部门数据？
delete from dept1 where deptno = 10; - 删除所有记录？
delete from dept1; - 怎么删除大表中的数据？（重点）
truncate table 表名; // 表被截断，不可回滚。永久丢失。 - 删除表？
drop table 表名; // 这个通用。 drop table if exists 表名; // oracle不支持这种写法。
表结构修改alter
对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）
DDL语句
insert插入数据
语法格式： insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....) 要求：字段的数量和值的数量相同，并且数据类型要对应相同 insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12'); // 除name字段之外，剩下的所有字段自动插入NULL。 insert into t_student(name) values('wangwu'); drop table if exists t_student; // 当这个表存在的话删除。 create table t_student( no bigint, name varchar(255), sex char(1) default 1, // 设定默认值 classno varchar(255), birth char(10) ); insert into t_student(name) values('zhangsan'); // 字段可以省略不写，但是后面的value对数量和顺序都有要求。 insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23'); // 一次插入多行数据 insert into t_student (no,name,sex,classno,birth) values (3,'rose','1','gaosi2ban','1952-12-14'), (4,'laotie','1','gaosi2ban','1955-12-14'); - 表的复制
语法：
// 将查询结果当做表创建出来。 create table 表名 as select语句; - 将查询结果插入到一张表中
insert into dept1 select * from dept; update修改数据
语法格式：
update 表名 set 字段名1=值1,字段名2=值2... where 条件; 注意：没有条件整张表数据全部更新。
- 将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU
update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10; - 更新所有记录
update dept1 set loc = 'x', dname = 'y'; 约束
常见的约束有哪些呢？
| 约束名 | 描述 |
|---|---|
| 非空约束(not null) | 约束的字段不能为NULL |
| 唯一约束(unique) | 约束的字段不能重复 |
| 主键约束(primary key) | 约束的字段既不能为NULL，也不能重复（简称PK） |
| 外键约束(foreign key) | …（简称FK） |
| 检查约束(check) | 注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。 |
not null (非空约束)
drop table if exists t_user; create table t_user( id int, username varchar(255) not null, password varchar(255) ); insert into t_user(id,password) values(1,'123'); //ERROR 1364 (HY000): Field 'username' doesn't have a default value insert into t_user(id,username,password) values(1,'lisi','123'); unique (唯一性约束)
唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。
案例：
- 给某一列添加unique
drop table if exists t_user; create table t_user( id int, username varchar(255) unique // 列级约束 ); insert into t_user values(1,'zhangsan'); insert into t_user values(2,'zhangsan'); ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username' insert into t_user(id) values(2); insert into t_user(id) values(3); insert into t_user(id) values(4); - 给两个列或者多个列添加unique
drop table if exists t_user; create table t_user( id int, usercode varchar(255), username varchar(255), unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】 ); insert into t_user values(1,'111','zs'); insert into t_user values(2,'111','ls'); insert into t_user values(3,'222','zs'); select * from t_user; insert into t_user values(4,'111','zs'); ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode' drop table if exists t_user; create table t_user( id int, usercode varchar(255) unique, username varchar(255) unique ); insert into t_user values(1,'111','zs'); insert into t_user values(2,'111','ls'); ERROR 1062 (23000): Duplicate entry '111' for key 'usercode' 注意：not null约束只有列级约束。没有表级约束。
primary key (主键约束)
给一张表添加主键约束:
drop table if exists t_user; create table t_user( id int primary key, // 列级约束 username varchar(255), email varchar(255) ); insert into t_user(id,username,email) values(1,'zs',''); insert into t_user(id,username,email) values(2,'ls',''); insert into t_user(id,username,email) values(3,'ww',''); select * from t_user; +----+----------+------------+ | id | username | email | +----+----------+------------+ | 1 | zs | zs@123.com | | 2 | ls | ls@123.com | | 3 | ww | ww@123.com | +----+----------+------------+ insert into t_user(id,username,email) values(1,'jack',''); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' insert into t_user(username,email) values('jack',''); ERROR 1364 (HY000): Field 'id' doesn't have a default value - 主键的相关术语
主键约束 : primary key
主键字段 : id字段添加primary key之后，id叫做主键字段
主键值 : id字段中的每一个值都是主键值。 - 主键的作用
- 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。（三范式后面会说）
- 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）
- 主键的分类
- 根据主键字段的字段数量来划分：
单一主键（推荐的，常用的。）
复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。） - 根据主键性质来划分：
自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）
Note：最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。
- 根据主键字段的字段数量来划分：
- 一张表的逐渐约束只能有1个.
- 使用表级约束方式定义主键
drop table if exists t_user; create table t_user( id int, username varchar(255), primary key(id) ); insert into t_user(id,username) values(1,'zs'); insert into t_user(id,username) values(2,'ls'); insert into t_user(id,username) values(3,'ws'); insert into t_user(id,username) values(4,'cs'); select * from t_user; insert into t_user(id,username) values(4,'cx'); ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' - mysql提供主键值自增（非常重要）
drop table if exists t_user; create table t_user( id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。 username varchar(255) ); insert into t_user(username) values('a'); insert into t_user(username) values('b'); insert into t_user(username) values('c'); insert into t_user(username) values('d'); insert into t_user(username) values('e'); insert into t_user(username) values('f'); select * from t_user;NOTE: Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。
foreign key (外键约束)
- 外键约束的相关术语
外键约束: foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值。
Example: 请设计数据库表，用来维护学生和班级的信息.
方案一: 一张表存储所有数据
no(pk) name classno classname ------------------------------------------------------------------------------------------- 1 zs1 101 北京大兴区经济技术开发区亦庄二中高三1班 2 zs2 101 北京大兴区经济技术开发区亦庄二中高三1班 3 zs3 102 北京大兴区经济技术开发区亦庄二中高三2班 4 zs4 102 北京大兴区经济技术开发区亦庄二中高三2班 5 zs5 102 北京大兴区经济技术开发区亦庄二中高三2班 缺点：冗余。【不推荐】 方案二: 两张表（班级表和学生表）
t_class 班级表 cno(pk) cname -------------------------------------------------------- 101 北京大兴区经济技术开发区亦庄二中高三1班 102 北京大兴区经济技术开发区亦庄二中高三2班 t_student 学生表 sno(pk) sname classno(该字段添加外键约束fk) ------------------------------------------------------------ 1 zs1 101 2 zs2 101 3 zs3 102 4 zs4 102 5 zs5 102 t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。
顺序要求：
删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，在删除父表。
drop table if exists t_student; drop table if exists t_class; create table t_class( cno int, cname varchar(255), primary key(cno) ); create table t_student( sno int, sname varchar(255), classno int, primary key(sno), foreign key(classno) references t_class(cno) ); insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy'); insert into t_student values(1,'zs1',101); insert into t_student values(2,'zs2',101); insert into t_student values(3,'zs3',102); insert into t_student values(4,'zs4',102); insert into t_student values(5,'zs5',102); insert into t_student values(6,'zs6',102); select * from t_class; select * from t_student; insert into t_student values(7,'lisi',103); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`)) NOTE:
- 外键可以为NULL。
- 外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
被引用的字段不一定是主键，但至少具有unique约束。
存储引擎 (了解内容)
- MySQL中完整的建表语句
CREATE TABLE `t_x` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。 建表的时候可以指定存储引擎，也可以指定字符集。 mysql默认使用的存储引擎是InnoDB方式。 默认采用的字符集是UTF8 - 什么是存储引擎
存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”）
mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。 - 查看当前mysql支持的存储引擎
show engines \Gmysql 5.5.36版本支持的存储引擎有9个： * 1. row * Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL * 2. row * Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO * 3. row * Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO * 4. row * Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO * 5. row * Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO * 6. row * Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO * 7. row * Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO * 8. row * Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES * 9. row * Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO - 常见的存储引擎
- Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NOMyISAM这种存储引擎不支持事务。 MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。 MyISAM采用三个文件组织一张表： xxx.frm（存储格式的文件） xxx.MYD（存储表中数据的文件） xxx.MYI（存储表中索引的文件） 优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。 缺点：不支持事务。 - Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。 表的结构存储在xxx.frm文件中 数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。 InnoDB支持级联删除和级联更新。 - Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。 优点：查询速度最快。 以前叫做HEPA引擎。
事务 (transaction)
事务的概念
- 一个事务是一个完整的业务逻辑单元，不可再分。
比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句： update t_act set balance = balance - 10000 where actno = 'act-001'; update t_act set balance = balance + 10000 where actno = 'act-002'; 以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。 要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。 - 和事务相关的语句只有：DML语句。（insert delete update）
为什么？因为它们这三个语句都是和数据库表当中的“数据”相关的。 事务的存在是为了保证数据的完整性，安全性。 - 假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？
不需要事务。 但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。 - 事务包括四大特性：ACID
A: 原子性：事务是最小的工作单元，不可再分。 C: 一致性：事务必须保证多条DML语句同时成功或者同时失败。 I：隔离性：事务A与事务B之间具有隔离。 D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。 - 事务隔离性存在隔离级别，理论上隔离级别包括4个：
第一级别：读未提交（read uncommitted） 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。 读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。 第二级别：读已提交（read committed） 对方事务提交之后的数据我方可以读取到。 这种隔离级别解决了: 脏读现象没有了。 读已提交存在的问题是：不可重复读。 第三级别：可重复读（repeatable read） 这种隔离级别解决了：不可重复读问题。 这种隔离级别存在的问题是：读取到的数据是幻象。 第四级别：序列化读/串行化读（serializable） 解决了所有问题。 效率低。需要事务排队。 oracle数据库默认的隔离级别是：读已提交。 mysql数据库默认的隔离级别是：可重复读。
例子演示
Note：mysql事务默认情况下是自动提交的。
即只要执行任意一条DML语句则提交一次。执行start transaction;即可关闭自动提交，进入手动状态。
step1. 准备表：
drop table if exists t_user; create table t_user( id int primary key auto_increment, username varchar(255) ); step2. 演示MySQL中事务的自动提交机制：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。
mysql> insert into t_user(username) values('zs'); Query OK, 1 row affected (0.03 sec) mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | +----+----------+ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | +----+----------+ 1 row in set (0.00 sec) step3. 演示：使用start transaction;关闭自动提交机制。
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into t_user(username) values('lisi'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | | 2 | lisi | +----+----------+ 2 rows in set (0.00 sec) mysql> insert into t_user(username) values('wangwu'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | | 2 | lisi | | 3 | wangwu | +----+----------+ 3 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.02 sec) mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | +----+----------+ 1 row in set (0.00 sec) -------------------------------------------------------------------- mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into t_user(username) values('wangwu'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_user(username) values('rose'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_user(username) values('jack'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | | 4 | wangwu | | 5 | rose | | 6 | jack | +----+----------+ 4 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.04 sec) mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | | 4 | wangwu | | 5 | rose | | 6 | jack | +----+----------+ 4 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | | 4 | wangwu | | 5 | rose | | 6 | jack | +----+----------+ 4 rows in set (0.00 sec) step4. 演示两个事务，假如隔离级别
演示第1级别：读未提交 set global transaction isolation level read uncommitted; 演示第2级别：读已提交 set global transaction isolation level read committed; 演示第3级别：可重复读 set global transaction isolation level repeatable read; mysql远程登录：mysql -h192.168.151.18 -uroot -p444 索引
索引的概念和基本用法
- 什么是索引？有什么用？
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。 在数据库方面，查询一张表的时候有两种检索方式： 第一种方式：全表扫描 第二种方式：根据索引检索（效率很高） 索引为什么可以提高检索效率呢？ 其实最根本的原理是缩小了扫描的范围。 索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中 的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改 这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。 添加索引是给某一个字段，或者说某些字段添加索引。 `select ename,sal from emp where ename = 'SMITH';` 当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。 当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。 索引底层采用的数据结构是：B + Tree - 怎么创建索引对象？怎么删除索引对象？
创建索引对象： create index 索引名称 on 表名(字段名); 删除索引对象： drop index 索引名称 on 表名; - 什么时候考虑给字段添加索引？(满足什么条件)
* 数据量庞大。（根据客户的需求，根据线上的环境） * 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护） * 该字段经常出现在where子句中。（经常根据哪个字段查询）
Note: 主键和具有unique约束的字段自动会添加索引。根据主键查询效率较高。尽量根据主键检索。
- 例子演示
查看sql语句的执行计划： mysql> explain select ename,sal from emp where sal = 5000; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 给薪资sal字段添加索引： create index emp_sal_index on emp(sal); mysql> explain select ename,sal from emp where sal = 5000; +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ - 索引的实现原理？
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率
是最高的。
select ename from emp where ename = ‘SMITH’;
通过索引转换为：
select ename from emp where 物理地址 = 0x3; - 索引的分类
单一索引：给单个字段添加索引 复合索引: 给多个字段联合起来添加1个索引 主键索引：主键上会自动添加索引 唯一索引：有unique约束的字段上会自动添加索引 .... - 索引什么时候失效？
select ename from emp where ename like '%A%'; 模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。
视图(view)
- 什么是视图？
站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。 - 怎么创建视图？怎么删除视图？
create view myview as select empno,ename from emp; drop view myview; 注意：只有DQL语句才能以视图对象的方式创建出来。 - 对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）
可以对视图进行CRUD操作。 - 面向视图操作？
mysql> select * from myview; +-------+--------+ | empno | ename | +-------+--------+ | 7369 | SMITH | | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7839 | KING | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | +-------+--------+ create table emp_bak as select * from emp; create view myview1 as select empno,ename,sal from emp_bak; update myview1 set ename='hehe',sal=1 where empno = 7369; // 通过视图修改原表数据。 delete from myview1 where empno = 7369; // 通过视图删除原表数据。 - 视图的作用？
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员
只对视图对象进行CRUD。
DBA命令
- 将数据库当中的数据导出
在windows的dos命令窗口中执行：（导出整个库） mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333 在windows的dos命令窗口中执行：（导出指定数据库当中的指定表） mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123 - 导入数据
create database bjpowernode; use bjpowernode; source D:\bjpowernode.sql
数据库设计的三范式(面试重点)
- 什么是设计范式？
设计表的依据。按照这个三范式设计的表不会出现数据冗余。 - 三范式都是哪些？
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。 第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。 多对多？三张表，关系表两个外键。 t_student学生表 sno(pk) sname ------------------- 1 张三 2 李四 3 王五 t_teacher 讲师表 tno(pk) tname --------------------- 1 王老师 2 张老师 3 李老师 t_student_teacher_relation 学生讲师关系表 id(pk) sno(fk) tno(fk) ---------------------------------- 1 1 3 2 1 1 3 2 2 4 2 3 5 3 1 6 3 3 第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。 一对多？两张表，多的表加外键。 班级t_class cno(pk) cname -------------------------- 1 班级1 2 班级2 学生t_student sno(pk) sname classno(fk) --------------------------------------------- 101 张1 1 102 张2 1 103 张3 2 104 张4 2 105 张5 2 提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。 - 一对一怎么设计？
一对一设计有两种方案：主键共享 t_user_login 用户登录表 id(pk) username password -------------------------------------- 1 zs 123 2 ls 456 t_user_detail 用户详细信息表 id(pk+fk) realname tel .... ------------------------------------------------ 1 张三 2 李四 一对一设计有两种方案：外键唯一。 t_user_login 用户登录表 id(pk) username password -------------------------------------- 1 zs 123 2 ls 456 t_user_detail 用户详细信息表 id(pk) realname tel userid(fk+unique).... ----------------------------------------------------------- 1 张三 2 2 李四 1
34道作业题
1. 取得每个部门最高薪水的人员名称
第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值) mysql> select deptno,max(sal) as maxsal from emp group by deptno; +--------+---------+ | deptno | maxsal | +--------+---------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+---------+ 第二步：将以上的查询结果当做一张临时表t， t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal select e.ename, t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno = e.deptno and t.maxsal = e.sal; 2. 哪些人的薪水在部门的平均薪水之上
第一步：找出每个部门的平均薪水 select deptno,avg(sal) as avgsal from emp group by deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916. | | 20 | 2175.000000 | | 30 | 1566. | +--------+-------------+ 第二步：将以上查询结果当做t表，t和emp表连接 条件：部门编号相同，并且emp的sal大于t表的avgsal select t.*, e.ename, e.sal from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on e.deptno = t.deptno and e.sal > t.avgsal; +--------+-------------+-------+---------+ | deptno | avgsal | ename | sal | +--------+-------------+-------+---------+ | 30 | 1566. | ALLEN | 1600.00 | | 20 | 2175.000000 | JONES | 2975.00 | | 30 | 1566. | BLAKE | 2850.00 | | 20 | 2175.000000 | SCOTT | 3000.00 | | 10 | 2916. | KING | 5000.00 | | 20 | 2175.000000 | FORD | 3000.00 | +--------+-------------+-------+---------+ 3. 取得部门中（所有人的）平均的薪水等级
平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。 平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。 第一步：找出每个人的薪水等级 emp e和salgrade s表连接。 连接条件：e.sal between s.losal and s.hisal select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; +--------+---------+--------+-------+ | ename | sal | deptno | grade | +--------+---------+--------+-------+ | CLARK | 2450.00 | 10 | 4 | | KING | 5000.00 | 10 | 5 | | MILLER | 1300.00 | 10 | 2 | | SMITH | 800.00 | 20 | 1 | | ADAMS | 1100.00 | 20 | 1 | | SCOTT | 3000.00 | 20 | 4 | | FORD | 3000.00 | 20 | 4 | | JONES | 2975.00 | 20 | 4 | | MARTIN | 1250.00 | 30 | 2 | | TURNER | 1500.00 | 30 | 3 | | BLAKE | 2850.00 | 30 | 4 | | ALLEN | 1600.00 | 30 | 3 | | JAMES | 950.00 | 30 | 1 | | WARD | 1250.00 | 30 | 2 | +--------+---------+--------+-------+ 第二步：基于以上的结果继续按照deptno分组，求grade的平均值。 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; +--------+--------------+ | deptno | avg(s.grade) | +--------+--------------+ | 10 | 3.6667 | | 20 | 2.8000 | | 30 | 2.5000 | +--------+--------------+ 4. 不用组函数（max），取得最高薪水
第一种：sal降序，limit 1 select ename,sal from emp order by sal desc limit 1; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | +-------+---------+ 第二种方案：select max(sal) from emp; 第三种方案：表的自连接 select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal); +---------+ | sal | +---------+ | 5000.00 | +---------+ select distinct a.sal from emp a join emp b on a.sal < b.sal a表 +---------+ | sal | +---------+ | 800.00 | | 1600.00 | | 1250.00 | | 2975.00 | | 1250.00 | | 2850.00 | | 2450.00 | | 3000.00 | | 5000.00 | | 1500.00 | | 1100.00 | | 950.00 | | 3000.00 | | 1300.00 | +---------+ b表 +---------+ | sal | +---------+ | 800.00 | | 1600.00 | | 1250.00 | | 2975.00 | | 1250.00 | | 2850.00 | | 2450.00 | | 3000.00 | | 5000.00 | | 1500.00 | | 1100.00 | | 950.00 | | 3000.00 | | 1300.00 | +---------+ 5. 取得平均薪水最高的部门的部门编号
第一种方案：降序取第一个。 第一步：找出每个部门的平均薪水 select deptno,avg(sal) as avgsal from emp group by deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916. | | 20 | 2175.000000 | | 30 | 1566. | +--------+-------------+ 第二步：降序选第一个。 select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916. | +--------+-------------+ 第二种方案：max 第一步：找出每个部门的平均薪水 select deptno,avg(sal) as avgsal from emp group by deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916. | | 20 | 2175.000000 | | 30 | 1566. | +--------+-------------+ 第二步：找出以上结果中avgsal最大的值。 select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t; +---------------+ | max(t.avgsal) | +---------------+ | 2916. | +---------------+ 第三步： select deptno,avg(sal) as avgsal from emp group by deptno having avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t); +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916. | +--------+-------------+ 6. 取得平均薪水最高的部门的部门名称
select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname order by avgsal desc limit 1; +------------+-------------+ | dname | avgsal | +------------+-------------+ | ACCOUNTING | 2916. | +------------+-------------+ 7. 求平均薪水的等级最低的部门的部门名称
平均薪水是800 平均薪水是900 那么他俩都是1级别。 第一步：找出每个部门的平均薪水 select deptno,avg(sal) as avgsal from emp group by deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916. | | 20 | 2175.000000 | | 30 | 1566. | +--------+-------------+ 第二步：找出每个部门的平均薪水的等级 以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.hisal select t.*,s.grade from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t join salgrade s on t.avgsal between s.losal and s.hisal; +------------+-------------+-------+ | dname | avgsal | grade | +------------+-------------+-------+ | SALES | 1566. | 3 | | ACCOUNTING | 2916. | 4 | | RESEARCH | 2175.000000 | 4 | +------------+-------------+-------+ select t.*,s.grade from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal); +-------+-------------+-------+ | dname | avgsal | grade | +-------+-------------+-------+ | SALES | 1566. | 3 | +-------+-------------+-------+ 抛开之前的，最低等级你怎么着？ 平均薪水最低的对应的等级一定是最低的. select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1; +-------------+ | avgsal | +-------------+ | 1566. | +-------------+ select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal; +-------+ | grade | +-------+ | 3 | +-------+ 8. 取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
比“普通员工的最高薪水”还要高的一定是领导！ 没毛病！！！！ mysql> select distinct mgr from emp where mgr is not null; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | 7788 | | 7782 | +------+ 员工编号没有在以上范围内的都是普通员工。 第一步：找出普通员工的最高薪水！ not in在使用的时候，后面小括号中记得排除NULL。 select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null); +----------+ | max(sal) | +----------+ | 1600.00 | +----------+ 第二步：找出高于1600的 select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)); +-------+---------+ | ename | sal | +-------+---------+ | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ 9. 取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ 10. 取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5, 5; +--------+---------+ | ename | sal | +--------+---------+ | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | +--------+---------+ `
11. 取得最后入职的 5 名员工
日期也可以降序，升序。 select ename,hiredate from emp order by hiredate desc limit 5; +--------+------------+ | ename | hiredate | +--------+------------+ | ADAMS | 1987-05-23 | | SCOTT | 1987-04-19 | | MILLER | 1982-01-23 | | FORD | 1981-12-03 | | JAMES | 1981-12-03 | +--------+------------+ 12. 取得每个薪水等级有多少员工
分组count 第一步：找出每个员工的薪水等级 select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; +--------+---------+-------+ | ename | sal | grade | +--------+---------+-------+ | SMITH | 800.00 | 1 | | ALLEN | 1600.00 | 3 | | WARD | 1250.00 | 2 | | JONES | 2975.00 | 4 | | MARTIN | 1250.00 | 2 | | BLAKE | 2850.00 | 4 | | CLARK | 2450.00 | 4 | | SCOTT | 3000.00 | 4 | | KING | 5000.00 | 5 | | TURNER | 1500.00 | 3 | | ADAMS | 1100.00 | 1 | | JAMES | 950.00 | 1 | | FORD | 3000.00 | 4 | | MILLER | 1300.00 | 2 | +--------+---------+-------+ 第二步：继续按照grade分组统计数量 select s.grade ,count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade; +-------+----------+ | grade | count(*) | +-------+----------+ | 1 | 3 | | 2 | 3 | | 3 | 2 | | 4 | 5 | | 5 | 1 | +-------+----------+ 13. 面试题：
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。
14. 列出所有员工及其领导的姓名
select a.ename '员工', b.ename '领导' from emp a left join emp b on a.mgr = b.empno; +--------+-------+ | 员工 | 领导 | +--------+-------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | KING | NULL | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+-------+ 15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
emp a 员工表 emp b 领导表 a.mgr = b.empno and a.hiredate < b.hiredate select a.ename '员工', a.hiredate, b.ename '领导', b.hiredate, d.dname from emp a join emp b on a.mgr = b.empno join dept d on a.deptno = d.deptno where a.hiredate < b.hiredate; +-------+------------+-------+------------+------------+ | 员工 | hiredate | 领导 | hiredate | dname | +-------+------------+-------+------------+------------+ | CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING | | SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH | | JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH | | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES | | WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES | | BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES | +-------+------------+-------+------------+------------+ 16. 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
select e.*,d.dname from emp e right join dept d on e.deptno = d.deptno; +-------+--------+-----------+------+------------+---------+---------+--------+------------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | dname | +-------+--------+-----------+------+------------+---------+---------+--------+------------+ | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | ACCOUNTING | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | ACCOUNTING | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | ACCOUNTING | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | RESEARCH | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | RESEARCH | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | RESEARCH | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | RESEARCH | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | RESEARCH | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | SALES | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | SALES | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | SALES | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | SALES | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | SALES | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | SALES | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | OPERATIONS | +-------+--------+-----------+------+------------+---------+---------+--------+------------+ 17. 列出至少有 5 个员工的所有部门, 按照部门编号分组，计数，筛选出 >= 5
select deptno from emp group by deptno having count(*) >= 5; +--------+ | deptno | +--------+ | 20 | | 30 | +--------+ 18. 列出薪金比”SMITH” 多的所有员工信息
select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH'); +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ 19. 列出所有”CLERK”( 办事员) 的姓名及其部门名称, 部门的人数
select ename,job from emp where job = 'CLERK'; +--------+-------+ | ename | job | +--------+-------+ | SMITH | CLERK | | ADAMS | CLERK | | JAMES | CLERK | | MILLER | CLERK | +--------+-------+ select e.ename,e.job,d.dname from emp e join dept d on e.deptno = d.deptno where e.job = 'CLERK'; +--------+-------+------------+ | ename | job | dname | +--------+-------+------------+ | MILLER | CLERK | ACCOUNTING | | SMITH | CLERK | RESEARCH | | ADAMS | CLERK | RESEARCH | | JAMES | CLERK | SALES | +--------+-------+------------+ select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno where e.job = 'CLERK'; +--------+-------+------------+--------+ | ename | job | dname | deptno | +--------+-------+------------+--------+ | MILLER | CLERK | ACCOUNTING | 10 | | SMITH | CLERK | RESEARCH | 20 | | ADAMS | CLERK | RESEARCH | 20 | | JAMES | CLERK | SALES | 30 | +--------+-------+------------+--------+ //每个部门的人数？ select deptno, count(*) as deptcount from emp group by deptno; +--------+-----------+ | deptno | deptcount | +--------+-----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+-----------+ select t1.*,t2.deptcount from (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno where e.job = 'CLERK') t1 join (select deptno, count(*) as deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno; +--------+-------+------------+--------+-----------+ | ename | job | dname | deptno | deptcount | +--------+-------+------------+--------+-----------+ | MILLER | CLERK | ACCOUNTING | 10 | 3 | | SMITH | CLERK | RESEARCH | 20 | 5 | | ADAMS | CLERK | RESEARCH | 20 | 5 | | JAMES | CLERK | SALES | 30 | 6 | +--------+-------+------------+--------+-----------+ 20. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
按照工作岗位分组求最小值。 select job,count(*) from emp group by job having min(sal) > 1500; +-----------+----------+ | job | count(*) | +-----------+----------+ | ANALYST | 2 | | MANAGER | 3 | | PRESIDENT | 1 | +-----------+----------+ 21. 列出在部门”SALES”< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
select ename from emp where deptno = (select deptno from dept where dname = 'SALES'); +--------+ | ename | +--------+ | ALLEN | | WARD | | MARTIN | | BLAKE | | TURNER | | JAMES | +--------+ 22. 列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
select e.EMPNO, e.ENAME, d.DNAME, s.GRADE, e2.ENAME as LEADER 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) from emp); 23. 列出与”SCOTT” 从事相同工作的所有员工及部门名称
select e.ENAME, e.JOB, d.DNAME from emp e join dept d on e.DEPTNO = d.DEPTNO where e.JOB = (select JOB from emp where ENAME = 'SCOTT') and e.ENAME <> 'SCOTT'; 24. 列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
select distinct sal from emp where deptno = 30; +---------+ | sal | +---------+ | 1600.00 | | 1250.00 | | 2850.00 | | 1500.00 | | 950.00 | +---------+ select ename,sal from emp where sal in(select distinct sal from emp where deptno = 30) and deptno <> 30; Empty set (0.00 sec) 25. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
select e.ENAME, e.SAL, d.DNAME from emp e join dept d on e.DEPTNO = d.DEPTNO where e.SAL > (select max(sal) from emp t where t.DEPTNO = 30); 26. 列出在每个部门工作的员工数量, 平均工资和平均服务期限
select d.DEPTNO, d.DNAME, COUNT(e.ENAME) as num_emp, ifnull(avg(SAL), 0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime from emp e right join dept d on e.DEPTNO = d.DEPTNO group by e.DEPTNO; +--------+--------+-------------+----------------+ | deptno | ecount | avgsal | avgservicetime | +--------+--------+-------------+----------------+ | 10 | 3 | 2916. | 38.0000 | | 20 | 5 | 2175.000000 | 35.8000 | | 30 | 6 | 1566. | 38.3333 | | 40 | 0 | 0.000000 | 0.0000 | +--------+--------+-------------+----------------+ 在mysql当中怎么计算两个日期的“年差”，差了多少年？ TimeStampDiff(间隔类型, 前一个日期, 后一个日期) timestampdiff(YEAR, hiredate, now()) 间隔类型： SECOND 秒， MINUTE 分钟， HOUR 小时， DAY 天， WEEK 星期 MONTH 月， QUARTER 季度， YEAR 年 27. 列出所有员工的姓名、部门名称和工资。
select e.ENAME, d.DNAME, e.SAL from emp e join dept d on e.DEPTNO = d.DEPTNO; +--------+------------+---------+ | ename | dname | sal | +--------+------------+---------+ | CLARK | ACCOUNTING | 2450.00 | | KING | ACCOUNTING | 5000.00 | | MILLER | ACCOUNTING | 1300.00 | | SMITH | RESEARCH | 800.00 | | JONES | RESEARCH | 2975.00 | | SCOTT | RESEARCH | 3000.00 | | ADAMS | RESEARCH | 1100.00 | | FORD | RESEARCH | 3000.00 | | ALLEN | SALES | 1600.00 | | WARD | SALES | 1250.00 | | MARTIN | SALES | 1250.00 | | BLAKE | SALES | 2850.00 | | TURNER | SALES | 1500.00 | | JAMES | SALES | 950.00 | +--------+------------+---------+ 28. 列出所有部门的详细信息和人数
select d.*, count(e.ENAME) as num_emp from emp e right join dept d on e.DEPTNO = d.DEPTNO group by e.DEPTNO; +--------+------------+----------+---------+ | DEPTNO | DNAME | LOC | num_emp | +--------+------------+----------+---------+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | | 40 | OPERATIONS | BOSTON | 0 | +--------+------------+----------+---------+ 29. 列出各种工作的最低工资及从事此工作的雇员姓名
select job,min(sal) as minsal from emp group by job; +-----------+----------+ | job | minsal | +-----------+----------+ | ANALYST | 3000.00 | | CLERK | 800.00 | | MANAGER | 2450.00 | | PRESIDENT | 5000.00 | | SALESMAN | 1250.00 | +-----------+----------+ emp e和以上t连接 select e.ename,t.* from emp e join (select job,min(sal) as minsal from emp group by job) t on e.job = t.job and e.sal = t.minsal; +--------+-----------+---------+ | ename | job | minsal | +--------+-----------+---------+ | SMITH | CLERK | 800.00 | | WARD | SALESMAN | 1250.00 | | MARTIN | SALESMAN | 1250.00 | | CLARK | MANAGER | 2450.00 | | SCOTT | ANALYST | 3000.00 | | KING | PRESIDENT | 5000.00 | | FORD | ANALYST | 3000.00 | +--------+-----------+---------+ 30. 列出各个部门的 MANAGER( 领导) 的最低薪金
select deptno, min(sal) from emp where job = 'MANAGER' group by deptno; +--------+----------+ | deptno | min(sal) | +--------+----------+ | 10 | 2450.00 | | 20 | 2975.00 | | 30 | 2850.00 | +--------+----------+ 31. 列出所有员工的 年工资, 按 年薪从低到高排序
select ename,(sal + ifnull(comm,0)) * 12 as yearsal from emp order by yearsal asc; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | JAMES | 11400.00 | | ADAMS | 13200.00 | | MILLER | 15600.00 | | TURNER | 18000.00 | | WARD | 21000.00 | | ALLEN | 22800.00 | | CLARK | 29400.00 | | MARTIN | 31800.00 | | BLAKE | 34200.00 | | JONES | 35700.00 | | FORD | 36000.00 | | SCOTT | 36000.00 | | KING | 60000.00 | +--------+----------+ 32. 求出员工领导的薪水超过3000的员工名称与领导
select a.ename '员工',b.ename '领导' from emp a join emp b on a.mgr = b.empno where b.sal > 3000; +-------+------+ | 员工 | 领导 | +-------+------+ | JONES | KING | | BLAKE | KING | | CLARK | KING | +-------+------+ 33. 求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数
select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal from emp e right join dept d on e.deptno = d.deptno where d.dname like '%S%' group by d.deptno,d.dname,d.loc; +--------+------------+---------+----------------+----------+ | deptno | dname | loc | count(e.ename) | sumsal | +--------+------------+---------+----------------+----------+ | 20 | RESEARCH | DALLAS | 5 | 10875.00 | | 30 | SALES | CHICAGO | 6 | 9400.00 | | 40 | OPERATIONS | BOSTON | 0 | 0.00 | +--------+------------+---------+----------------+----------+ 34. 给任职日期超过 30 年的员工加薪 10%.
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30; 免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://haidsoft.com/140909.html
