数据库MySQL
1 MySQL数据库简介
1.1 sql、DB、DBMS分别是什么，关系？
DB：DataBase 数据库
DBMS：DateBase Management System 数据库管理系统
SQL：结构化查询语言、sql语句的编译有dbms完成
DBMS负责执行sql语句，通过之心sql语句来操作DB当中的数据
1.2 什么是表？
table 是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强
行：被称为数据/记录（data）
列：被称为字段（column）
| 学号（int） | 姓名（varchar） | 年龄（int） |
|---|---|---|
| 110 | 张三 | 20 |
| 123 | 李四 | 21 |
1.2.1 每个字段应该包括那些属性？
字段名、数据类型、相关约束
1.3 sql包括增删改插、怎么分类？
DQL（数据查询语言）：查询语句，凡是select语句都是DQL
DML（数据操作语言）：insert delete update，对表中数据进行增删改
DDL（数据定义语言）：create drop alter，对表中结构的增删改
TCL（事务控制语言）：commit提交事务，rollback回滚事务
DCL（数据控制语言）：grant授权、revoke撤销权限等
1.4 导入数据
-
登陆mysql数据库管理系统
dos命令窗口：
mysql -uroot -pluohaoxu -
查看有哪些数据库
show databases；（不是sql语句，属于mysql的命令）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lVbrUFsj-1632582379249)(D:\csdnNote\数据库MySQL.assets\image-20210922154445514.png)]
-
构建属于我们自己
create database bjpowernode；（不是sql语句，属于mysql的命令）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6rDHlNbt-1632582379251)(D:\csdnNote\数据库MySQL.assets\image-20210922154838274.png)]
-
使用bjpowernode数据
use bjpowernode；（这个不是sql语句，属于mysql命令）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9XX6i9a4-1632582379254)(D:\csdnNote\数据库MySQL.assets\image-20210922155008311.png)]
-
查看当前数据库有哪些表：
show tables；（这个不是sql语句，属于mysql命令） -
初始化数据
mysql> source D:\projects\mysql\bjpowernode.sql[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e8rYd9il-1632582379258)(D:\csdnNote\数据库MySQL.assets\image-20210922160550200.png)]
注意初始化完成后有三张表：
±----------------------+
| Tables_in_bjpowernode |
±----------------------+
| dept |
| emp |
| salgrade |
±----------------------+
1.5 bjpowernode.sql 这个文件以sql结尾，这样的文件是sql脚本
当一个文件的扩展名是.sql，并且该文件编写了大量的sql语句，我们成这样的文件为sql脚本
注意：使用source命令可以执行sql脚本
sql脚本数据量太大的时候，无法打开，请使用source命令完成初始化。
1.6 删除数据库
drop databases bjpowernode；
1.7 查看表结构
mysql> show bjpowernode;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept | （部门表）
| emp | （员工表）
| salgrade | （工资等级表）
+-----------------------+
查看表结构：desc
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int | NO | PRI | NULL | | 部门编号
| DNAME | varchar(14) | YES | | NULL | | 部门名称
| LOC | varchar(13) | YES | | NULL | | 部门位置
+--------+-------------+------+-----+---------+-------+
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | | 员工编号
| ENAME | varchar(10) | YES | | NULL | | 员工名称
| JOB | varchar(9) | YES | | NULL | | 工作岗位
| MGR | int | YES | | NULL | | 上级领导编号
| HIREDATE | date | YES | | NULL | | 入职日期
| SAL | double(7,2) | YES | | NULL | | 工资
| COMM | double(7,2) | YES | | NULL | | 补助
| DEPTNO | int | YES | | NULL | | 部门编号
+----------+-------------+------+-----+---------+-------+
mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int | YES | | NULL | | 等级
| LOSAL | int | YES | | NULL | | 最低工资
| HISAL | int | YES | | NULL | | 最高工资
+-------+------+------+-----+---------+-------+
1.8 表中的数据
mysql> select * from 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
2 常用命令
2.1 当前使用哪个数据库
select databases（）；
2.2 查看当前版本
select version（）；
2.3 结束一条语句
\c
2.4 退出mysql
ctrl+c
2.5 查看创建表的语句
show create table emp；
3 简单的查询语句（DQL）
3.1 语法格式
select 字段名1，字段名2，字段名3…from 表名；
提示：
- 任何一条sql语句以“；”结尾
- sql语句不分大小写
3.2 查询员工的年薪？
mysql> select ename,sal * 12 from emp;
+--------+----------+
| ename | sal * 12 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
3.3 给查询结果的列重命名？
mysql> select ename,sal * 12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
3.4 别名中有中文？
mysql> select ename,sal * 12 as '年薪' from emp;
+--------+----------+
| ename | 年薪 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
注意：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用
as关键字可以省略
3.5 查询所有字段？
select * from emp；//实际开发中不建议使用*，效率较低
4 条件查询
4.1 语法格式
selcet
字段，字段…
from
表名
where
条件；
执行顺序：先from，然后where，最后select
4.2 查询工资等于5000的员工姓名？
mysql> select ename from emp where sal = 5000;
+-------+
| ename |
+-------+
| KING |
+-------+
4.3 查询smith的工资？
mysql> select sal from emp where ename = 'smith';
+--------+
| sal |
+--------+
| 800.00 |
+--------+
4.4 找出工资高于3000的员工？
mysql> select ename from emp where sal >= 3000;
+-------+
| ename |
+-------+
| SCOTT |
| KING |
| FORD |
+-------+
4.5 找出工资不等于3000的？
mysql> select ename from emp where sal <> 3000;//"！="也可以
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| MILLER |
+--------+
4.6 找出工资在1100和3000之间的员工？
mysql> select ename from emp where sal >= 1100 and sal <= 3000;
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| TURNER |
| ADAMS |
| FORD |
| MILLER |
+--------+
mysql> select ename from emp where sal between 1100 and 3000;//闭区间，左小右大
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| TURNER |
| ADAMS |
| FORD |
| MILLER |
+--------+
between … and … 可以用在字符串方面。
mysql> select ename from emp where ename between 'a' and 'b';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
4.7 找出哪些人没有津贴？
在数据库当中null不是一个值，代表什么也没有，为空。
空不是一个值，不能用等号衡量。
必须使用is null或者is not null
mysql> select ename,sal from emp where comm is null;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
哪些人津贴不为null？
==mysql> select ename,sal from emp where comm is not null;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
+--------+---------+
找出工作岗位是manager和salesman员工？
mysql> select ename,job from emp where job = 'manager' or job = 'salesman';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
4.8 and和or联合起来用：找出薪资大于1000的并且部门编号是20或30部门的员工？
mysql> select ename,sal,deptno from emp where sal >= 1000 and (deptno = 20 or deptno = 30);
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| FORD | 3000.00 | 20 |
+--------+---------+--------+
注意：当优先级不确定的时候加小括号
4.9 in等同于or：找出岗位是manager和salesman的员工？
mysql> select ename,job from emp where job = 'manager' or job = 'salesman';
mysql> 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 |
+--------+----------+
4.10 not in：不在这几个值当中
mysql> select ename,sal from emp where sal not in('1000','3000');
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| MILLER | 1300.00 |
+--------+---------+
4.11 模糊查询like
找出名字当中含有o的？（在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_）
%代表任意多个字符，_代表一个字符。
mysql> select ename from emp where ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
找出名字中第二个字母是A的？
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
找出名字中有下划线的？
mysql> select ename from emp where ename like '%\_%';
5 排序（升序、降序）
按照工资升序，找出员工名和薪资？
select
ename,sal
from
emp
order by
sal;
mysql> select ename,sal from emp order by sal;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
注意：默认是升序，asc是升序，desc是降序；
mysql> select ename,sal from emp order by sal;
mysql> select ename,sal from emp order by sal asc;
mysql> select ename,sal from emp order by sal desc;
5.1 按照工资的降序排列，当工资相同的时候再按照名字的升序排？
mysql> select ename,sal from emp order by sal desc,ename asc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会起启用后面的字段
5.2 找出工作岗位是salesman的员工，并且要求按照薪资的降序排列
select
*
from
tablename
where
条件
order by
...；
mysql> select ename,job,sal from emp where job = 'salesman' order by sal desc;
+--------+----------+---------+
| ename | job | sal |
+--------+----------+---------+
| ALLEN | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+
6 分组函数
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
记住：所有的分组函数都是对“某一组”数据进行操作的。
6.1 找出工资总和？
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
6.2 找出工资最高？
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
6.3 找出工资最低？
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
6.4 找出平均工资？
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
6.5 找出总人数？
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
分组函数一共五个
分组函数还有另一个名字：多行处理函数（输入多行，输出一行）
分组函数自动忽略null
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
6.6 找出工资高于平均工资的员工？
mysql> select avg(sal) from emp;//平均工资
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
mysql> 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
avg（sal）
having 4
..
order by 6
..
所以员工高于平均工资？（嵌套子查询）
mysql> select ename,sal from emp where sal> (select avg(sal) from emp);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
count（*）和count（具体某个字段），他们有什么区别？
count（*）：不是统计某个字段数据的个数，而是统计总记录条数。
count（comm）：表示某个comm字段中不为null的数据总量。
分组函数也可以组合起来用
mysql> select count(*),avg(sal),max(sal),min(sal) from emp;
+----------+-------------+----------+----------+
| count(*) | avg(sal) | max(sal) | min(sal) |
+----------+-------------+----------+----------+
| 14 | 2073.214286 | 5000.00 | 800.00 |
+----------+-------------+----------+----------+
6.7 单行处理函数
什么是单行处理函数？
输入一行，输出一行。
计算每个员工的年薪？
mysql> select ename,(sal+comm)*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+----------+
重点：所有数据库都是这样规定的，只要null参与的运算结果一定是null
6.7.1 ifnull（）空处理函数
ifnull（可能为null的数据，被当做什么处理）
mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
ifnull（）输出单行处理函数
7 分组查询group by 和 having
group by：按照某个字段或者某些字段进行分组
having：是对分组之后的数据进行再次过滤
7.1 找出每个工作岗位的最高薪资？
mysql> select max(sal),job from emp group by job;
+----------+-----------+
| max(sal) | job |
+----------+-----------+
| 1300.00 | CLERK |
| 1600.00 | SALESMAN |
| 2975.00 | MANAGER |
| 3000.00 | ANALYST |
| 5000.00 | PRESIDENT |
+----------+-----------+
注意分组函数一般都会和group by联合使用，这也是为什么他被称为分组函数的原因。
任何一个分组函数（count等等）都是在group by语句之行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。
select ename,max(sal),job from emp group by job;
结果没有意义（mysql查询结果有，但没有意义；oracle数据库会报错。oracle的语法规则比mysql语法规则严谨）
记住一个规则：当一条语句中group by的话，select后面只能跟分组函数和参与分组的字段
7.2 找出每个部门不同工作岗位的最高薪资？
mysql> select deptno,job,sal from emp order by deptno;
+--------+-----------+---------+
| deptno | job | sal |
+--------+-----------+---------+
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 10 | CLERK | 1300.00 |
| 20 | CLERK | 800.00 |
| 20 | MANAGER | 2975.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | ANALYST | 3000.00 |
| 30 | SALESMAN | 1600.00 |
| 30 | SALESMAN | 1250.00 |
| 30 | SALESMAN | 1250.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1500.00 |
| 30 | CLERK | 950.00 |
+--------+-----------+---------+
联合分组
mysql> select deptno,job,max(sal) from emp group by deptno,job order by deptno;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
7.3 找出每个部门最高薪资，要求显示薪资大于2900的数据
第一步：找出每个部门最高薪资
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
第二步：找出薪资大于2900的
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>2900;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 10 | 5000.00 |
+--------+----------+
//效率低
mysql> select deptno,max(sal) from emp where sal >2900 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 10 | 5000.00 |
+--------+----------+
//效率高，建议能用where尽量使用where
7.4 找出每个部门平均薪资，要求显示薪资大于2000的数据
第一步：找出每个部门平均薪资
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：显示薪资大于2000的数据
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 10 | 2916.666667 |
+--------+-------------+
不能用where，则用having处理数据
having和group by搭档，没有group by不能用having
7.5 总结一个完整的dql语句怎么写？
select 5
..
from 1
..
where 2
..
group by 3
avg（sal）
having 4
..
order by 6
..
8 查询结果去重
8.1 distinct
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
8.2 联合去重
mysql> select distinct deptno,job from emp order by deptno;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 10 | CLERK |
| 10 | MANAGER |
| 10 | PRESIDENT |
| 20 | ANALYST |
| 20 | CLERK |
| 20 | MANAGER |
| 30 | CLERK |
| 30 | MANAGER |
| 30 | SALESMAN |
+--------+-----------+
8.3 统计岗位的数量
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
9 连接查询
9.1 什么是连接查询
在实际开发中，大多不是单表查询，一般都是多张表联合查询取出最红的结果。
一般一个业务都会对应多张表，比如：学生和班级，起码两张表。
9.2 连接查询的分类
按照语法出现的年份来划分的话，包括：
sql92（一些老的dba可能还在使用这些语法。dba：database administrator，数据库管理员）
sql99（比较新的语法）
根据表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（这个不讲，很少用）
9.3 在表的连接查询方面有一种现象被称为：笛卡尔积现象
9.3.1 找出每一个员工的部门名称，要求显示员工名和部门名
mysql> select ename,dname from emp,dept;
mysql> select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | OPERATIONS |
| SMITH | SALES |
| SMITH | RESEARCH |
| SMITH | ACCOUNTING |
| ALLEN | OPERATIONS |
| ALLEN | SALES |
| ALLEN | RESEARCH |
| ALLEN | ACCOUNTING |
| WARD | OPERATIONS |
| WARD | SALES |
| WARD | RESEARCH |
| WARD | ACCOUNTING |
| JONES | OPERATIONS |
| JONES | SALES |
...
56 rows in set (0.45 sec)
笛卡尔积现象：当两张表进行连接查询的时候，没有任何限制，最终的查询结果条数是两张表记录条数的乘积。
关于表的别名：
select e.ename，d.dname from emp e，dept d；
表的别名有什么好处：
- 执行效率高
- 可读性好
9.4 怎么避免笛卡尔积现象？（加条件过滤）
思考：避免了笛卡尔积现象，会减少记录的匹配字数吗？
不会，次数还是56次。只不过显示的有效记录。
9.4.1 找出每一个员工的部门名称，要求显示员工名和部门名
mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//sql92语法，以后不用
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
9.5 内连接——等值连接：最大特点是：条件是等量关系
9.5.1 查询每个员工的部门名称，要求显示员工名和部门名？
9.4.1中有sql92的语法、
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno=d.deptno;
sql99语法如下（常用）：
select
e.ename,d.dname
from
emp e
inner join //inner可以省略，带inner可读性更好一些
dept d
on
e.deptno=d.deptno;
语法：
...
A
join
B
on
连接条件
where
...
sql99语法结构更清晰：表的连接条件和后来的where条件分离了。
9.6 内连接——非等值连接：最大的特点：连接条件中的关系是非等量关系
9.6.1 找出每个员工的工资等级，要求显示员工名、工资等级
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
mysql> 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 |
+--------+---------+-------+
9.7 内连接——自连接：最大特点：一张表看做两张表。自己连接自己
9.7.1 找出每个员工的上级领导，要求显示员工名和对应的领导名
mysql> select e1.ename,e2.ename mgr from emp e1 join emp e2 on e1.mgr = e2.empno;
+--------+-------+
| ename | mgr |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
9.8 外连接
什么是外连接，和内链接有什么区别？
内连接：假设a和b表进行连接，使用内连接的话，凡是a表和b表能够匹配上的记录查询出来
ab两张表没有主副之分，两张表是平等的。
外连接：假设a和b表进行连接，使用外连接的话，ab两张表有一张表是主表，一张表是附表，主要查询主表中的数据，捎带这查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配。
外连接的分类：
左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。
左连接有右连接的写法，右连接有左连接的写法
9.8.1 找出每个员工的上级领导（所有员工必须查询出来）
9.7.1中有内连接查询方法，king数据丢失（中间包含null）
内连接语法：
select
e1.ename,e2.ename mgr
from
emp e1
join
emp e2
on
e1.mgr = e2.empno;
外连接语法：
select
e1.ename,e2.ename mgr
from
emp e1
left join //left outer join outer可以省略
emp e2
on
e1.mgr = e2.empno;
mysql> select
-> e1.ename,e2.ename mgr
-> from
-> emp e1
-> left join
-> emp e2
-> on
-> e1.mgr = e2.empno;
+--------+-------+
| ename | mgr |
+--------+-------+
| 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 |
+--------+-------+
外连接最重要的特点是：主表的数据无条件显示出来
外连接使用居多。
9.8.2 找出哪个部门没有员工？
mysql> select d.* from emp e right join dept d on e.deptno=d.deptno where e.ename is null;
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
9.9 三张表怎么连接
9.9.1 找出每个员工的部门名称和工资等级
注意：
...
A
join
B
join
C
on
...
表示a表先和b进行表连接，连接之后a表继续和c表连接
mysql> select e.ename,d.dname,s.grade,e.sal from emp e join dept d join salgrade s on (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);
+--------+------------+-------+---------+
| ename | dname | grade | sal |
+--------+------------+-------+---------+
| SMITH | RESEARCH | 1 | 800.00 |
| ALLEN | SALES | 3 | 1600.00 |
| WARD | SALES | 2 | 1250.00 |
| JONES | RESEARCH | 4 | 2975.00 |
| MARTIN | SALES | 2 | 1250.00 |
| BLAKE | SALES | 4 | 2850.00 |
| CLARK | ACCOUNTING | 4 | 2450.00 |
| SCOTT | RESEARCH | 4 | 3000.00 |
| KING | ACCOUNTING | 5 | 5000.00 |
| TURNER | SALES | 3 | 1500.00 |
| ADAMS | RESEARCH | 1 | 1100.00 |
| JAMES | SALES | 1 | 950.00 |
| FORD | RESEARCH | 4 | 3000.00 |
| MILLER | ACCOUNTING | 2 | 1300.00 |
+--------+------------+-------+---------+
9.9.2 找出每个员工的部门名称、工资等级、以及上级领导
mysql> select e.ename,d.dname,s.grade,e.sal,e2.ename mgr
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;
+--------+------------+-------+---------+-------+
| ename | dname | grade | sal | mgr |
+--------+------------+-------+---------+-------+
| SMITH | RESEARCH | 1 | 800.00 | FORD |
| ALLEN | SALES | 3 | 1600.00 | BLAKE |
| WARD | SALES | 2 | 1250.00 | BLAKE |
| JONES | RESEARCH | 4 | 2975.00 | KING |
| MARTIN | SALES | 2 | 1250.00 | BLAKE |
| BLAKE | SALES | 4 | 2850.00 | KING |
| CLARK | ACCOUNTING | 4 | 2450.00 | KING |
| SCOTT | RESEARCH | 4 | 3000.00 | JONES |
| KING | ACCOUNTING | 5 | 5000.00 | NULL |
| TURNER | SALES | 3 | 1500.00 | BLAKE |
| ADAMS | RESEARCH | 1 | 1100.00 | SCOTT |
| JAMES | SALES | 1 | 950.00 | BLAKE |
| FORD | RESEARCH | 4 | 3000.00 | JONES |
| MILLER | ACCOUNTING | 2 | 1300.00 | CLARK |
+--------+------------+-------+---------+-------+
10 子查询
10.1 什么是子查询？子查询可以出现在哪里？
select语句中嵌套select语句，被嵌套的select语句是子查询
子查询可以出现在哪里？
select
...（select）
from
...（select）
where
...（select）
10.2 where子句中使用子查询
10.2.1 找出高于平均薪资的员工信息？
mysql> select ename,sal from emp where sal>(select avg(sal) from emp);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
10.3 from后面嵌套子查询
10.3.1 找出每个部门平均薪水的薪资等级
第一步：找出每个部门的平均薪水
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：与salgrade进行连接
mysql> select a.deptno,a.avgsal,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) a join salgrade s on a.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
10.3.2 找出每个部门的平均的薪水等级
第一步：先找出每个部门每个员工的薪水等级
mysql> select e.ename,e.deptno,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+--------+---------+-------+
| ename | deptno | sal | grade |
+--------+--------+---------+-------+
| SMITH | 20 | 800.00 | 1 |
| ALLEN | 30 | 1600.00 | 3 |
| WARD | 30 | 1250.00 | 2 |
| JONES | 20 | 2975.00 | 4 |
| MARTIN | 30 | 1250.00 | 2 |
| BLAKE | 30 | 2850.00 | 4 |
| CLARK | 10 | 2450.00 | 4 |
| SCOTT | 20 | 3000.00 | 4 |
| KING | 10 | 5000.00 | 5 |
| TURNER | 30 | 1500.00 | 3 |
| ADAMS | 20 | 1100.00 | 1 |
| JAMES | 30 | 950.00 | 1 |
| FORD | 20 | 3000.00 | 4 |
| MILLER | 10 | 1300.00 | 2 |
+--------+--------+---------+-------+
第二步：继续group by
mysql> 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) |
+--------+--------------+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+--------------+
10.4 在select后面嵌套子查询
10.4.1 找出每个员工所正在部门名称，要求显示员工名和部门名
mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
用select子查询方式
mysql> select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
11 union 可以将查询结果集相加
11.1 找出工作岗位是salesman和manager员工？
mysql> select ename,job from emp where job in('salesman','manager');
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
使用union
mysql> select ename,job from emp where job = 'salesman'
-> union
-> select ename,job from emp where job = 'manager';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+--------+----------+
把两张不相关的表拼接到一起显示？
mysql> select ename from emp
-> union
-> select dname from dept;
+------------+
| ename |
+------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
注意：列数需要相同
12 limit 重点 分页查询
12.1 介绍
limit是mysql特有的，其他数据库中没有，不通用。（oracle中有一个相同的机制，叫做rownum）
limit去结果集中的部分数据，这是它的作用
语法机制：
limit startIndex，length
startindex表示起始位置
length表示取几个
12.2 取出工资前5名
mysql> select ename,sal from emp order by sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
mysql> select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
mysql> 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 |
+-------+---------+
limit是sql最后执行的一个环节
select 5
..
from 1
..
where 2
..
group by 3
avg（sal）
having 4
..
order by 6
..
limit x，x； 7
12.3 找出工资排名在第4到第9名的员工
mysql> 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 |
+--------+---------+
12.4 通用的标准分页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;
limit (pageNo-1)*pageSize,pageSize;
}
13 创建表
建表语句的语法格式：
create table 表名{
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
...
};
13.1 关于mysql当中字段的数据类型？一下只说常见的
int 整数型
bigint 长整型（java中long）
float 浮点型（float和double）
char 定长字符串（String）
varchar 可变长字符串（StringBuffer/StringBuilder）
date 日期类型（对应java中的java.sql.Date类型）
blob 二进制大对象（存储图片、视频等流媒体信息）binary large object（object）
clob 字符大对象（存储大文本，比如，可以存储4G的字符串） character large object（object）
13.2 char和varchar选择
char（6）：存jack也会给6个空间（效率高，比如性别，日期）定长
varchar（6）：存jack给4个空间（需要运算，效率低，比如简介）不定长
13.3 blob和clob选择
电影表：t_movie
id(int) name(varchar) playtime(date/char) haibao(blob) history(clob)
表名在数据库当中一般建议以：“t下划线”或者“tbl下划线”开始
13.4 创建学生表
学生信息包括：学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char
create table t_student(
sno bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
14 insert语句插入数据
insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3,...)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
insert into t_student(sno,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban','1950-10-12');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| sno | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+
insert into t_student(name,sex,classno,birth,sno) values('zhangsan','1','gaosan1ban','1950-10-12',2);
mysql> select * from t_student;
+------+----------+------+------------+------------+
| sno | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+
insert into t_student(name) values('wangwu'); //除name字段之外，剩下所有字段自动插入null
mysql> select * from t_student;
+------+----------+------+------------+------------+
| sno | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| NULL | wangwu | NULL | NULL | NULL |
+------+----------+------+------------+------------+
删除表，创建默认值（default）
drop table if exists t_student;
create table t_student(
sno bigint,
name varchar(255),
sex char(1) default 1,
classno varchar(255),
birth char(10)
);
mysql> insert into t_student(name) values('zhangsan');
mysql> select * from t_student;
+------+----------+------+---------+-------+
| sno | name | sex | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1 | NULL | NULL |
+------+----------+------+---------+-------+
注意的地方：
当一条insert语句执行成功后，表格当中必然会多一行记录
即使多的这一行记录当中某些字段是null，后期也没有办法再执行
insert语句插入数据了，只能使用update进行更新
insert别的写法（字段可以省略不写，但是后面的values对数量和顺序都有要求）
mysql> insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| sno | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
+------+----------+------+------------+------------+
一次插入多行数据
mysql> insert into t_student(sno,name,sex,classno,birth) values(3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| sno | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
| 3 | rose | 1 | gaosi2ban | 1952-12-14 |
| 4 | laotie | 1 | gaosi2ban | 1955-12-14 |
+------+----------+------+------------+------------+
15 表的复制
语法：
create table 表名 as select 语句
mysql> create table emp1 as select * from emp;
mysql> desc emp1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
16 将查询结果插入到一张表中
语法：
mysql> insert into emp1 select * from emp;
mysql> select * from emp1;
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
17 修改数据：update
语法：
update 表名 set 字段名1=值1，字段名2=值2....where 条件;
注意：没有条件整张表更新
17.1 将部门10的loc修改为shanghai，将部门名称修改为renshibu
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
mysql> update dept1 set loc ='SHANGHAI',dname='RENSHIBU' where deptno=10;
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | RENSHIBU | SHANGHAI |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | RENSHIBU | SHANGHAI |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
17.2 更新所有记录
mysql> update dept1 set loc='x',dname='y';
mysql> select * from dept1;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
| 10 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
+--------+-------+------+
18 删除数据
语法
delete from 表名 where 条件;
注意：没有条件全部删除
18.1 删除10部门数据
mysql> delete from dept1 where deptno = 10;
mysql> select * from dept1;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
+--------+-------+------+
18.2 删除所有数据
mysql> delete from dept1;
mysql> select * from dept1;
Empty set (0.00 sec)
18.3 删除大表（重点）
mysql> truncate table emp1;
表被截断，不可回滚，永久丢失
19 对表结构的修改，使用工具完成即可
修改表结构的语句不会出现在java代码中
出现在java代码当中的sql包括：insert delete update select（都是表中的数据操作）
增删改查有个术语：crud操作
create retrieve（检索） update delete
20 约束（constraint）
20.1 什么是约束，常见的约束有哪些
在创建表的时候，可以给表的字段添加相应的约束，添加约束的亩的是为了保证表中数据的合法性、有效性、完整性
常见约束：
非空约束（not null）：约束的字段不能为空
唯一约束（unique）：约束的字段不能重复
主键约束（primary key）：约束的字段既不能为null，也不能重复（简称pk）
外键约束（foregin key）：…（简称fk）
检查约束（check）oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束
20.2 非空约束 not null
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
username默认值不能为空
insert into t_user(id,username,password) values(1,'lisi','123');
mysql> select * from t_user;
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | lisi | 123 |
+------+----------+----------+
20.3 唯一性约束 unique
唯一约束修饰的字段具有唯一性，不能重复，但可以null
给某一列添加约束：
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 't_user.username'
唯一性报错
insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);
mysql> select * from t_user;
+------+----------+
| id | username |
+------+----------+
| 1 | zhangsan |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
+------+----------+
给两个列或者多个列添加unique
表级约束：
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode,username)
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
mysql> select * from t_user;
+------+----------+----------+
| id | usercode | username |
+------+----------+----------+
| 1 | 111 | zs |
| 2 | 111 | ls |
| 3 | 222 | zs |
+------+----------+----------+
insert into t_user values(3,'111','zs');
ERROR 1062 (23000): Duplicate entry '111-zs' for key 't_user.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');
insert into t_user values(3,'222','zs');
mysql> insert into t_user values(1,'111','zs');
Query OK, 1 row affected (0.07 sec)
mysql> insert into t_user values(2,'111','ls');
ERROR 1062 (23000): Duplicate entry '111' for key 't_user.usercode'
mysql> insert into t_user values(3,'222','zs');
ERROR 1062 (23000): Duplicate entry 'zs' for key 't_user.username'
20.4 主键约束 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 values(1,'zs','zs@123.com');
insert into t_user values(2,'ls','ls@123.com');
insert into t_user values(3,'ww','ww@123.com');
mysql> 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 values(1,'jk','jk@123.com');
ERROR 1062 (23000): Duplicate entry '1' for key 't_user.PRIMARY'
mysql> insert into t_user(username,email) values('jk','jk@123.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
测试得知，id是主键，因为添加主键约束，主键字段中的数据不能为null，也不能重复
主键的特点：不能为null，也不能重复
主键相关的术语：
主键约束：primary key
主键字段：id
主键值：id字段的每个值
主键作用：
表的设计三范式中有要求，第一范式就是任何一张表都应有主键
主键是每行记录的唯一标识（就像一个人的身份证号一样）
主键的分类：
根据主键字段的字段数量来划分：
单一主键：推荐的，常用的
复合主键：多个字段联合起来添加一个主键约束
根据主键性质来划分：
自然主键：和业务没有人和关系的自然数，推荐
业务主键：主键值和系统的业务挂钩。例如拿着银行卡的卡号做主键，拿着身份证号码作为主键，不推荐用
最好不要拿着业务挂钩的字段作为主键，因为以后业务一旦发生改变，主键值也需要发生变化，但有时候没有办法发生变化
一张表的主键约束只能有一个
使用表级约束方式定义主键
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
);
insert into t_user(id,name) values(1,'zs');
复合主键，不需要掌握：
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
usercode varchar(255),
primary key(id，username)
);
insert into t_user(id,name) values(1,'zs');
mysql提供主键值自增：
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
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');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+----------+
oracle当中也提供了一个自增机制，叫做：序列（sequeue）对象
20.5 外键约束
相关术语：
外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值
20.5.1 请设计数据库表，用来维护学生和班级信息
第一种方案：用一张表存储所有信息
缺点：冗余，不推荐
第二种方案：用两张表
t_class 班级表
cno(pk) cname
t_student 学生表
sno(pk) sname cno(fk)
将以上表的建表语句写出来：
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 primary key,
cname varchar(255)
);
create table t_student(
sno int primary key,
sname varchar(255),
cno int,
foreign key(cno) references t_class(cno)
);
insert into t_class values(101,'xxxxxxxx');
insert into t_class values(102,'yyyyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',101);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);
mysql> select * from t_class;
+-----+----------+
| cno | cname |
+-----+----------+
| 101 | xxxxxxxx |
| 102 | yyyyyyyy |
+-----+----------+
mysql> select * from t_student;
+-----+-------+------+
| sno | sname | cno |
+-----+-------+------+
| 1 | zs1 | 101 |
| 2 | zs2 | 101 |
| 3 | zs3 | 101 |
| 4 | zs4 | 102 |
| 5 | zs5 | 102 |
| 6 | zs6 | 102 |
+-----+-------+------+
子表中的外键约束只能写父表中存在的数据
外键值可以为null
外键字段引用别的字段，不一定是主键，但必须具有唯一性
21 存储引擎
mysql特有，oracle也有，但不叫这个名字
存储引擎是表的存储方式
21.1 完整的建表语句
CREATE TABLE `t_class` (
`cno` int NOT NULL,
`cname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
在mysql当中，凡是可以用飘号括起来的，最好别用，不通用。
建表的时候可以指定存储引擎，也可以指定字符集。
mysql
默认使用的存储引擎是innodb方式
默认采用的字符集是utf-8
21.2 什么是存储引擎
存储引擎这个名字只有在mysql中存在。（oracle中也有对应的机制，但是不叫存储引擎，叫“表的存储方式”）
mysql支持很多存储引擎，每个引擎代表了不同的存储方式
21.3 查看当前mysql支持的存储引擎
show engines \G
mysql> show engines \G
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.36 sec)
有九个
21.4 常见的存储引擎
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
myisam存储引擎不支持事务
是mysql最常用的引擎，但不是默认的
特征：
用三个文件表示三个表：
格式文件——存储表结构的定义xxx.frm
数据文件——存储表行的内容xxx.MYD
索引文件——存储表上索引xxx.MYI
优点：
可被压缩，节省空间，并且可转换为只读表，提高检索效率
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
优点：
支持事务，行级锁、外键。安全得到保障
表的结构存储在xxx.frm文件中
表的数据存储在tablesspace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读
这种innodb存储引擎在mysql崩溃后提供自动恢复
innodb支持级联删除和级联更新
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
缺点：不支持事务，数据容易丢失，因为所有数据和索引都是存储在内存当中的
有点：查询速度最快
以前叫hepa存储引擎
数据和索引都存储在内存当中
22 事务（transaction）
22.1 什么是事务
一个事务所是一个完整的业务逻辑单元，不可再分
比如：银行账户转账，从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语句同时成功或者同时失败，必须使用数据库的“事务机制”
22.2 和事务相关的语句只有dml语句：insert delete update
因为这三个语句都是和数据库表当中的数据相关的
事物的存在是为了保证数据的完整性，安全性
假设所有事物都能使用一条dml语句搞定，还需要事务机制吗
不需要，但事实通常一个业务需要多个dml语句
22.3 事务过程
开启事务机制（开始）——dml语句——提交事务或者回滚事务（结束）
dml语句时，执行成功后写到操作历史记录中，并不会真正修改数据
事务结束后删除历史操作记录，才修改硬盘数据或者回滚
提交：commit
回滚：rollback
22.4 事务特性
事务包括四大特性：ACID
A：原子性：事务是最小的工作单元，不可再分
C：一致性：事务必须保证多条dml语句同时成功或者同时失败
I：隔离性：事务A与事务B之间具有隔离
D：持久性：最终数据必须持久化到硬盘文件中，事务才算成功的结束
22.5 事务之间的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括四个
第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据
存在脏读（dirty read）现象：读到了脏数据
第二级别：读已提交（read committed）
对方事务提交之后的数据我方可以读取到
解决了脏读现象
读已提交存在的问题是：不可重复读
第三级别：可重复读（repeatable read）
解决了不可重复读问题
存在的问题是：读取到的数据是幻象
第四级别：序列化读/串行化读（serializable）
解决了所有问题
效率低，需要事务排队
oracle数据库默认隔离级别是：读已提交
mysql数据库默认隔离级别是：可重复读
22.6 演示事务
mysql事务默认情况下是自动提交的
自动提交：只要之心任意一条dml语句则提交一次
怎么关闭自动提交：start transaction
22.6.1 自动提交演示
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);
insert into t_user(username) values('zs');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
1 row in set (0.00 sec)
mysql> rollback;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
22.6.2 关闭自动回滚 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> rollback;
Query OK, 0 rows affected (0.12 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
mysql> insert into t_user(username) values('lisi');
Query OK, 1 row affected (0.19 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 3 | lisi |
+----+----------+
2 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_user(username) values('zs');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user(username) values('ww');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user(username) values('jk');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.06 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 3 | lisi |
| 4 | zs |
| 5 | ww |
| 6 | jk |
+----+----------+
5 rows in set (0.00 sec)
22.6.3 使用两个事务演示以上的隔离级别
设置事务的全局隔离级别
set global transaction isolation level read uncommitted;
设置 read uncommitted
查看事务的全局隔离级别
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
23 索引
23.1 什么是索引，有什么用
相当于一本书的目录，通过目录可以快速的找到对应的资源
在数据库方面，查询一张表的时候有两种索引方式：
第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）
索引可以缩小扫描范围
索引虽然可以提高效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改，这样就不适合添加索引。因为数据一旦修改，索引就需要重新排序，进行维护。
添加索引是给某一个字段，或者某一些字段添加索引
select ename,sal from emp where ename='smith';
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描
当ename字段上添加索引的时候，sql会根据索引进行扫描，快速定位
23.2 怎么创建索引，怎么删除索引
添加索引：
create index 索引名称 on 表名(字段名);
删除索引：
drop index 索引名称 on 表名;
23.3 什么时候考虑给字段添加索引（满足什么条件）
-
数据量庞大
-
该字段很少dml操作
-
该字段经常出现在where子句中
注意：主键和具有unique约束的字段自动会添加索引
根据主键查询效率更高，尽量根据主键检索
23.4 查看sql语句的执行计划
mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
type：全表扫描
rows：扫了14行
23.4.1 给薪资sal字段添加索引
create index emp_sal_index on emp(sal);
mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
type：ref
rows：扫了一行
23.5 索引底层采用的数据结构是
B+tree
23.6 索引的实现原理
添加了索引有可能在内存或者硬盘上
索引会自动排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tg5iNLC4-1632582379265)(D:\csdnNote\数据库MySQL.assets\image-20210924230043041.png)]
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
23.7 索引的分类
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加一个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段会自动添加索引
23.8 索引什么时候失效
select ename from emp where ename like '%A%';
模糊查询的时候，第一个通配符使用的是%，这个时候会失效
24 视图（view）
24.1 什么是视图
站在不同的角度去看到数据（同一张表的数据，通过不同的角度去看待）
24.2 怎么创建视图 怎么删除视图
创建
create view myview as select empno,ename from emp;
删除
drop view myview;
只有dql语句才能以视图对象的方式创建出来
24.3 对视图进行增删改查
会影响原表数据（通过视图影响原表数据的，不是直接操作的原表）
可以对视图进行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 |
+-------+--------+
通过视图修改原表数据
mysql> create view myview1 as select empno,ename,sal from emp_bak;
Query OK, 0 rows affected (0.32 sec)
mysql> update myview1 set ename='hehe',sal=1 where empno=7369;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp_bak;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | hehe | CLERK | 7902 | 1980-12-17 | 1.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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
24.4 视图的作用
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行crud操作
25 dba命令
数据备份
25.1 将数据库当中的数据导出
在windows的dos命令窗口中执行：
导出整个库：
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -pluohaoxu
导出指定表：
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -pluohaoxu
25.2 将数据库当中的数据导入
create database bjpowernode;
use bjpowernode;
source D:\bjpowernode.sql;
26 数据库设计三范式
26.1 什么是设计范式
设计表的依据 按照这个三范式设计的表不会出现数据的冗余。
26.2 三范式都有哪些
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖（联合主键，张三只依赖sno）
多对多，三张表，关系表两个外键
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖
一对多，两张表，多的表加外键
注意：在实际开发当中，以满足客户的需求为主，有时候会拿冗余换执行速度
26.3 一对一怎么设计
一对一设计有两种方案一：主键共享
t_user_login 用户登录表
id(pk) username password
t_user_detail 用户详细信息表
id(pk+fk) realname tel
一对一设计有两种方案二：外键唯一
t_user_login 用户登录表
id(pk) username password
t_user_detail 用户详细信息表
id(pk) realname tel userid(fk+unique)
mysql 34道作业题
1 取得每个部门的最高薪水的人员名称
第一步：取得每个部门的最高薪水
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
第二步：匹配出每个部门最高薪水的人员名称
mysql> select e.ename,e.sal,e.deptno from emp e join (select deptno,max(sal) maxsal from emp group by deptno) a on e.deptno = a.deptno and e.sal=a.maxsal;
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| FORD | 3000.00 | 20 |
+-------+---------+--------+
2 哪些人的薪水在部门的平均薪水之上
第一步：每个部门的平均薪水
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：哪些人比部门薪水要高，打印出来
mysql> select e.ename,e.sal,e.deptno from emp e join (select deptno,avg(sal) avgsal from emp group by deptno) t on e.deptno = t.deptno and e.sal > avgsal;
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| ALLEN | 1600.00 | 30 |
| JONES | 2975.00 | 20 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| FORD | 3000.00 | 20 |
+-------+---------+--------+
3 取得部门中（所有人的）平均的薪水等级
第一步：找出所有人的薪水等级
mysql> select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal>s.losal and e.sal<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 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| MILLER | 1300.00 | 10 | 2 |
+--------+---------+--------+-------+
第二步：将薪水等级按部门取平均值
mysql> select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal>s.losal and e.sal<hisal group by e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 20 | 2.0000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+--------------+
4 不准用组函数（max），取得最高薪水（给出两种解决方案）
第一种方案：将sal由高到低排序，取第一个的值
mysql> select sal from emp order by sal desc limit 0,1;
+---------+
| sal |
+---------+
| 5000.00 |
+---------+
第二种方案：表的自连接
第一步：找出表1比表2小的所有值
mysql> select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal;
+---------+
| sal |
+---------+
| 1300.00 |
| 950.00 |
| 1100.00 |
| 1500.00 |
| 1250.00 |
| 800.00 |
| 2450.00 |
| 2850.00 |
| 1600.00 |
| 2975.00 |
| 3000.00 |
+---------+
第二步：从表1中去除这些值
mysql> select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
+---------+
| sal |
+---------+
| 5000.00 |
+---------+
5 取得平均薪水最高的部门部门编号（至少给出两种方案）
第一种方案：
第一步：取出部门平均薪水
mysql> select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：排序取第一个
mysql> select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 0,1;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
第二种方案：
第一步和第一种方案一样
第二步：找出该表中最大值
第三步：找出与第一个表中和第二表中相同的值所代表的部门
mysql> select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
mysql> select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
| 2916.666667 |
+---------------+
1 row in set (0.00 sec)
mysql> select deptno,avg(sal) avgsal from emp group by deptno having avgsal = (select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
6 取得平均薪水最高的部门的部门名称
第一步：用第五问的结果和dept表连接
mysql> select e.deptno,d.dname from (select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 0,1) e join dept d on e.deptno=d.deptno;
+--------+------------+
| deptno | dname |
+--------+------------+
| 10 | ACCOUNTING |
+--------+------------+
7 求平均薪水的等级最低的部门的部门名称
第一步：求各部门的平均薪水
第二步：求各部门的平均薪水的等级
第三步：升序取第一个平均薪水等级
第四部：找出最低薪水等级所对应的部门
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
mysql> select t.deptno,t.avgsal,s.grade from salgrade s join (select deptno,avg(sal) avgsal from emp group by deptno) t on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
mysql> select s.grade from salgrade s join (select deptno,avg(sal) avgsal from emp group by deptno) t on t.avgsal between s.losal and s.hisal order by s.grade asc limit 1;
+-------+
| grade |
+-------+
| 3 |
+-------+
mysql> select t.deptno,t.avgsal,s.grade,d.dname from salgrade s join (select deptno,avg(sal) avgsal from emp group by deptno) t join (select s.grade from salgrade s join (select deptno,avg(sal) avgsal from emp group by deptno) t on t.avgsal between s.losal and s.hisal order by s.grade asc limit 1) t2 join dept d on t.avgsal between s.losal and s.hisal where s.grade=t2.grade and d.deptno=t.deptno;
+--------+-------------+-------+-------+
| deptno | avgsal | grade | dname |
+--------+-------------+-------+-------+
| 30 | 1566.666667 | 3 | SALES |
+--------+-------------+-------+-------+
8 取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名
第一步：找出普通员工
第二步：找出普通员工的薪水
第三步：找出比普通员工薪水还要高的领导名称，在领导中找
mysql> select empno from emp where empno not in (select distinct mgr from emp where mgr is not null);
+-------+
| empno |
+-------+
| 7369 |
| 7499 |
| 7521 |
| 7654 |
| 7844 |
| 7876 |
| 7900 |
| 7934 |
+-------+
mysql> select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
| 1600.00 |
+----------+
mysql> select ename from emp where sal>(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)) and empno in (select distinct mgr from emp where mgr is not null);
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| FORD |
+-------+
9 取得薪水最高的前五名员工
mysql> 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 取得薪水最高的第六名到第十名员工
mysql> 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 |
| WARD | 1250.00 |
+--------+---------+
11 取得最后入职的五名员工
mysql> 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 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+--------+------------+
12 取得每个薪水等级有多少员工
第一步：找出所有人的薪水等级
第二步：统计每个薪水等级的count
mysql> select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal>s.losal and e.sal<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 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| MILLER | 1300.00 | 10 | 2 |
+--------+---------+--------+-------+
mysql> select count(grade),s.grade from emp e join salgrade s on e.sal>s.losal and e.sal<hisal group by s.grade;
+--------------+-------+
| count(grade) | grade |
+--------------+-------+
| 3 | 1 |
| 2 | 3 |
| 3 | 2 |
| 3 | 4 |
| 1 | 5 |
+--------------+-------+
13 面试题
有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1 找出没选过“黎明”老师的所有学生姓名
2 列出2门以上（含2门）不及格学生姓名及平均成绩
3 即学过1号课程又学过2号课所有学生的姓名
14 列出所有员工及领导姓名
mysql> select e1.ename,e2.ename mgr from emp e1 join emp e2 on e1.mgr = e2.empno;
+--------+-------+
| ename | mgr |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
15 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称
mysql> select e1.deptno,e1.ename,e1.hiredate,e2.ename as mgr,e2.hiredate,d.dname from emp e1 join emp e2 join dept d on e1.mgr = e2.empno and e1.deptno=d.deptno where e1.hiredate < e2.hiredate;
+--------+-------+------------+-------+------------+------------+
| deptno | ename | hiredate | mgr | hiredate | dname |
+--------+-------+------------+-------+------------+------------+
| 20 | SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH |
| 30 | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES |
| 30 | WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES |
| 20 | JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH |
| 30 | BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES |
| 10 | CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING |
+--------+-------+------------+-------+------------+------------+
16 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
mysql> select e.*,d.* from emp e right join dept d on e.deptno=d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
17 列出至少有5个员工的所有部门
mysql> select count(*) countdeptno,deptno from emp group by deptno having count(*)>=5;
+-------------+--------+
| countdeptno | deptno |
+-------------+--------+
| 5 | 20 |
| 6 | 30 |
+-------------+--------+
18 列出薪金比“SMITH”多的所有员工信息
mysql> select e1.* from emp e1 join emp e2 on e1.sal>e2.sal and e2.ename='SMITH';
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
19 列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数
mysql> select e.ename,d.dname,t.countdeptno from emp e join dept d join (select count(*) countdeptno,deptno from emp group by deptno) t on d.deptno=e.deptno and t.deptno=e.deptno where e.job ='CLERK';
+--------+------------+-------------+
| ename | dname | countdeptno |
+--------+------------+-------------+
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+--------+------------+-------------+
20 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
mysql> select job,count(*) from emp group by job having min(sal)>1500;
+-----------+----------+
| job | count(*) |
+-----------+----------+
| MANAGER | 3 |
| ANALYST | 2 |
| PRESIDENT | 1 |
+-----------+----------+
21 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
第一步：找出SALES部门编号
第二步：找出员工
mysql> select deptno from dept where dname = 'SALES';
+--------+
| deptno |
+--------+
| 30 |
+--------+
mysql> select e.ename from emp e join (select deptno from dept where dname = 'SALES') t on e.deptno=t.deptno;
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
22 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
第一步：找出平均薪资
第二步：列出信息
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
mysql> select e.ename,e.deptno,e2.ename mgr,s.grade from emp e 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);
+-------+--------+-------+-------+
| ename | deptno | mgr | grade |
+-------+--------+-------+-------+
| FORD | 20 | JONES | 4 |
| SCOTT | 20 | JONES | 4 |
| CLARK | 10 | KING | 4 |
| BLAKE | 30 | KING | 4 |
| JONES | 20 | KING | 4 |
| KING | 10 | NULL | 5 |
+-------+--------+-------+-------+
23 列出与“SCOTT”从事相同工作的所有员工及部门名称
第一步：找出SCOTT的工作
第二步：列出信息
mysql> select job from emp where ename = 'SCOTT';
+---------+
| job |
+---------+
| ANALYST |
+---------+
mysql> select e.ename,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';
+-------+----------+
| ename | dname |
+-------+----------+
| FORD | RESEARCH |
+-------+----------+
24 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
第一步：找出30中员工的薪金
第二步：列出信息
mysql> select distinct sal from emp where deptno = 30;
+---------+
| sal |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+---------+
mysql> select distinct 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工作的所有员工的薪金的员工姓名和薪金，部门名称
第一步：找出30中员工的最高薪金
第二步：列出信息
mysql> select max(sal) from emp where deptno = 30;
+----------+
| max(sal) |
+----------+
| 2850.00 |
+----------+
mysql> 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 where deptno = 30);
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
26 列出在每个部门工作的员工数量，平均工资和平均服务期限
mysql> select d.dname,count(*),ifnull(avg(e.sal),0),ifnull(avg(TimeStampDiff(YEAR,hiredate,now())),0) from emp e right join dept d on e.deptno=d.deptno group by d.dname;
+------------+----------+----------------------+---------------------------------------------------+
| dname | count(*) | ifnull(avg(e.sal),0) | ifnull(avg(TimeStampDiff(YEAR,hiredate,now())),0) |
+------------+----------+----------------------+---------------------------------------------------+
| ACCOUNTING | 3 | 2916.666667 | 39.3333 |
| RESEARCH | 5 | 2175.000000 | 37.4000 |
| SALES | 6 | 1566.666667 | 39.6667 |
| OPERATIONS | 1 | 0.000000 | 0.0000 |
+------------+----------+----------------------+---------------------------------------------------+
27 列出所有员工的姓名、部门名称和工资
mysql> select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;
+--------+------------+---------+
| ename | dname | sal |
+--------+------------+---------+
| SMITH | RESEARCH | 800.00 |
| ALLEN | SALES | 1600.00 |
| WARD | SALES | 1250.00 |
| JONES | RESEARCH | 2975.00 |
| MARTIN | SALES | 1250.00 |
| BLAKE | SALES | 2850.00 |
| CLARK | ACCOUNTING | 2450.00 |
| SCOTT | RESEARCH | 3000.00 |
| KING | ACCOUNTING | 5000.00 |
| TURNER | SALES | 1500.00 |
| ADAMS | RESEARCH | 1100.00 |
| JAMES | SALES | 950.00 |
| FORD | RESEARCH | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+
28 列出所有部门的详细信息和人数
mysql> select d.*,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno;
+--------+------------+----------+----------------+
| DEPTNO | DNAME | LOC | count(e.ename) |
+--------+------------+----------+----------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+----------------+
29 列出各种工作的最低工资及从事此工作的雇员姓名
mysql> select e.ename,t.* from emp e join (select min(sal) as minsal,job from emp group by job) t on e.job=t.job and e.sal=t.minsal;
+--------+---------+-----------+
| ename | minsal | job |
+--------+---------+-----------+
| SMITH | 800.00 | CLERK |
| WARD | 1250.00 | SALESMAN |
| MARTIN | 1250.00 | SALESMAN |
| CLARK | 2450.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
+--------+---------+-----------+
30 列出各个部门MANAGER（领导）的最低薪金
mysql> select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
| 20 | 2975.00 |
| 30 | 2850.00 |
| 10 | 2450.00 |
+--------+----------+
31 列出所有员工的年工资，按年薪从低到高排序
mysql> select (sal+ifnull(comm,0)) * 12 yearSal,ename from emp order by sal asc;
+----------+--------+
| yearSal | ename |
+----------+--------+
| 9600.00 | SMITH |
| 11400.00 | JAMES |
| 13200.00 | ADAMS |
| 21000.00 | WARD |
| 31800.00 | MARTIN |
| 15600.00 | MILLER |
| 18000.00 | TURNER |
| 22800.00 | ALLEN |
| 29400.00 | CLARK |
| 34200.00 | BLAKE |
| 35700.00 | JONES |
| 36000.00 | SCOTT |
| 36000.00 | FORD |
| 60000.00 | KING |
+----------+--------+
32 求出员工领导的薪水超过3000的员工名称与领导名称
mysql> select e.ename,e2.ename mgr,e2.sal from emp e join emp e2 on e.mgr=e2.empno where e2.sal > 3000;
+-------+------+---------+
| ename | mgr | sal |
+-------+------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+------+---------+
33 求出部门名称中，带’S’字符的部门员工的工资合计、部门人数
mysql> select d.dname,ifnull(sum(e.sal),0),count(e.ename) from emp e right join dept d on e.deptno=d.deptno where d.dname like '%S%' group by d.deptno;
+------------+----------------------+----------------+
| dname | ifnull(sum(e.sal),0) | count(e.ename) |
+------------+----------------------+----------------+
| RESEARCH | 10875.00 | 5 |
| SALES | 9400.00 | 6 |
| OPERATIONS | 0.00 | 0 |
+------------+----------------------+----------------+
34 给任职日期超过30年的员工加薪10%
mysql> update emp set sal = sal * 1.1 where TimeStampDiff(YEAR,hiredate,now())>30;
Query OK, 14 rows affected (0.12 sec)
mysql> select * from emp ;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 880.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1760.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1375.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3272.50 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1375.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 3135.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2695.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3300.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5500.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1650.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1210.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1045.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3300.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1430.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
tno=d.deptno group by d.deptno;
±-------±-----------±---------±---------------+
| DEPTNO | DNAME | LOC | count(e.ename) |
±-------±-----------±---------±---------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
±-------±-----------±---------±---------------+
## 29 列出各种工作的最低工资及从事此工作的雇员姓名
```sql
mysql> select e.ename,t.* from emp e join (select min(sal) as minsal,job from emp group by job) t on e.job=t.job and e.sal=t.minsal;
+--------+---------+-----------+
| ename | minsal | job |
+--------+---------+-----------+
| SMITH | 800.00 | CLERK |
| WARD | 1250.00 | SALESMAN |
| MARTIN | 1250.00 | SALESMAN |
| CLARK | 2450.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
+--------+---------+-----------+
30 列出各个部门MANAGER（领导）的最低薪金
mysql> select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
| 20 | 2975.00 |
| 30 | 2850.00 |
| 10 | 2450.00 |
+--------+----------+
31 列出所有员工的年工资，按年薪从低到高排序
mysql> select (sal+ifnull(comm,0)) * 12 yearSal,ename from emp order by sal asc;
+----------+--------+
| yearSal | ename |
+----------+--------+
| 9600.00 | SMITH |
| 11400.00 | JAMES |
| 13200.00 | ADAMS |
| 21000.00 | WARD |
| 31800.00 | MARTIN |
| 15600.00 | MILLER |
| 18000.00 | TURNER |
| 22800.00 | ALLEN |
| 29400.00 | CLARK |
| 34200.00 | BLAKE |
| 35700.00 | JONES |
| 36000.00 | SCOTT |
| 36000.00 | FORD |
| 60000.00 | KING |
+----------+--------+
32 求出员工领导的薪水超过3000的员工名称与领导名称
mysql> select e.ename,e2.ename mgr,e2.sal from emp e join emp e2 on e.mgr=e2.empno where e2.sal > 3000;
+-------+------+---------+
| ename | mgr | sal |
+-------+------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+------+---------+
33 求出部门名称中，带’S’字符的部门员工的工资合计、部门人数
mysql> select d.dname,ifnull(sum(e.sal),0),count(e.ename) from emp e right join dept d on e.deptno=d.deptno where d.dname like '%S%' group by d.deptno;
+------------+----------------------+----------------+
| dname | ifnull(sum(e.sal),0) | count(e.ename) |
+------------+----------------------+----------------+
| RESEARCH | 10875.00 | 5 |
| SALES | 9400.00 | 6 |
| OPERATIONS | 0.00 | 0 |
+------------+----------------------+----------------+
34 给任职日期超过30年的员工加薪10%
mysql> update emp set sal = sal * 1.1 where TimeStampDiff(YEAR,hiredate,now())>30;
Query OK, 14 rows affected (0.12 sec)
mysql> select * from emp ;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 880.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1760.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1375.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3272.50 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1375.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 3135.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2695.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3300.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5500.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1650.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1210.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1045.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3300.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1430.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
扫一扫
1576
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
