MySql数据库

端口：文件下载的端口默认都是21，上网端口统一是80，mySql数据库端口默认3306，Oracle数据库默认1521

Mysql的使用

在dos命令行中登录mysql数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y4ZQ8tSz-1625638290001)(C:\Users\Think\Pictures\Saved Pictures\电脑截图\屏幕截图 2021-05-05 195603.png)]

• -u (user) -p (password) root是用户名和密码

表

表table，是数据库的基本组成单元

表包括行和列：

​ 行：被称为数据（data）

​ 列：被称为字段（ column ：列）

数据库中字符串：varchar （可变字符串）

Sql语句分类：

• DQL ：（数据查询语言）查询语句，凡是select语句
• DML：（数据操作语言）insert delete update 对表中的数据增删改
• DDL：（数据定义语言）creat drop alter ，对表结构的增删改
• TCL：（事务控制语言）commit 提交事务 rollback 回滚事务
• DCL：（数据控制语言）grant 授权 revoke 撤销权限

导入初始化数据

• 登录 dos命令窗口： mysql -uroot -proot
• 查看有哪几个数据库 ： show databases； （这个是mysql的命令，下同）
• 创建数据库 ：creat database students;
• 使用数据库：use students；
• 查看有哪几个表：show tables；
• 执行 sql 脚本：source sql文件的绝对路径
• 查看表的内部结构：desc tablename； 或者describe tablename；

mysql常用命令：

• select database ：查看当前使用的数据库
• select version；：查看版本号

sql

增删改查：CRUD: create（增） Retrieve（检索）Update（修改）Delete（删除）

sql查询语句

• 基本查询格式：select 字段名1，字段名2，字段名3，…from 表名；

  • 字段可以参与数学运算
  • 给查询结果的列名重命名 select srollno * 12 as newsrollno from student;【使用as，as关键字可以省略】
  • 字符串使用单引号括起来

• 查询全部字段用 ：select * from student； 【不推荐使用效率低】

• where条件查询：select 字段… from 表名 where 条件；

• and 和 or

  and是且,or是或

  in操作符允许在where子句中规定多个值

  select ename，job from emp where job in ("manager","salesman");
  select ename, job from emp where job = 'manager' or job = 'salesman';
  

• in 和 not in

  in：在…范围内

  not in：不在…范围内

• between…and : 查找指定范围的数据，闭区间

• like 模糊查询

  在模糊查询中有两个重要的通配符，% 和 _

  %代表任意多个字符， _ 代表任意一个字符

  可以结合使用not选出不包含什么的…

  例如找第二个字母是 a 的姓名就可以使用:

  select name from emp where name like '_a%';
  

• order by 排序 (升序,降序) : order by 字段名 desc/asc;

  默认是升序排列

  select id,name from emp order by salary;

  desc : 降序 select id,name from emp order by salary desc;

  asc : 升序 select id,name from emp order by salary asc;

  • select id,name,salary from emp order by salary desc,name asc;

    第一个字段排序起主导作用,只有当前面的字段无法完成排序时才会启用后面的字段进行排序,比如当地一个排序工资有相同的出现时,这时就会再按照第二个字段进行排序

• distinct （不同的）去重查询

  select distinct 字段名1、字段名2… from 表名;

  如果列中包含相同的名称,则只显示一个,不会重复显示

  select distinct name,id... from emp;
  

  此查询语句是name字段和id字段联合去重查询

函数

• 分组函数 (多行处理函数、聚合函数) :【sql Aggregate函数】

  【分组函数会自动忽略 null 】

  【分组函数不可以直接出现在where条件句中】

  为什么他们不能跟在where后面？

  这几个函数在使用时一般都和group by联合使用，原理是先分组在进行函数计算。

  当然我们在平常使用时，有这种情况。

  select max(age) from t_student

  这个sql语句中并没有group by，但还是可以使用分组函数max。这是因为当sql语句种没有手动使用group by时，那整个调用的表默认是一个分组。max函数是在这个大分组中取的最大值。所以我们就可以知道，应该是先分组，再调用分组函数，这样的逻辑。

  接下来我们了解下sql语句执行的顺序

  例：从学生表t_student中筛选出3年级年龄最大的女孩儿们，结果按照学号id升序

select max(age) from t_student where grade = 3 group by sex having sex = girl order by studentID asc

第一步：from 选择表

第二步：where 筛选条件，筛选对象--行

第三步：group by 将筛选出来的数据进行分组

第四步：having 筛选条件，筛选对象--组

第五步：select 选取最后的结果

第六步：order by 将结果按照特定顺序排列

 

从上面的执行顺序我们又知道了**先执行where，后group by分组**，**分组完之后才能使用分组函数**。

所以where后面的筛选条件出现了分组函数那就会报错。还没有进行分组呢，不能使用分组函数。

当我们遇到这种情况时，就要改变思路，**用子查询即可**，where后面跟一个子查询就迎刃而解了。**例如查找高于平均工资的员工：**						

```mysql
select id,name from emp where salary > (select avg(salary) from emp);

• count 计数 (返回指定列 值的数目,null值不计入)

  select count(name) from emp;
  

  select count(*) from emp; 【count( * ) 统计的时总记录条数，和null没有关系，不会忽略什么null】

  sum 求和

  select sum(salary) from emp;

  avg 平均数

  select avg(salary) from emp;

  max 最大值

  selecet max(salary) from emp;

  min 最小值

  select min(salary) from emp;

• 单行处理函数

• ifnull() 、isnull()

  ifnull（可能为null的数据，用户替换的数据）

  比如 ifnull（salary，0）；isnull（）同理

• group by 和 having

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

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

  举例：

  找出每个工作岗位的最高薪资

  select max(salary) from emp group by job;

  【分组函数一般和 group by 联合使用，并且分组函数是在group by 执行结束之后才会执行】

  找出男性中programmer工资最多的员工,

select job, max(salary) from emp where gender=‘male’ group by job having job = ‘programmer’ ;

```sql
+----+------+--------+------------+-------------+
| id | name | gender | job        | max(salary) |
+----+------+--------+------------+-------------+
|  2 | sum  | male   | programmer |     7866.00 |
|  3 | pony | male   | designer   |     6888.00 |
|  5 | niki | male   | boss       |     9008.00 |
|  7 | john | male   | devloper   |     9001.00 |
+----+------+--------+------------+-------------+

重要 重要 重要 重要

【注意：】在这里查询name是没有任何意义的，因为job分组了，可是name没有分组，这里的name是随机找的四个项，和工资并没有对应关系

所以，当一条select语句中有 group by 时，select 后面只能查询 参与分组的字段和分组函数，查询其他选项会出现没有对应关系，并且在Oracle中不支持这种语法规则

• 多个字段分组

  找出每个部门不同工作岗位的最高薪资

  select department,job,max(salary)  from emp group by department,job;
  

• 总结一个完整的DQL语句：

  顺序不可以变，但是可以缺省（后面的数字是执行顺序）

  select 5

  ​ …

  from 1

  ​ …

  where 2

  ​ …

  group by 3

  ​ ·w…

  having 4

  ​ …

  order by 6

  ​ …

  limit 7

  ​ …

  select … from … where … group by … having … order by … limit …

连接查询**：（**join on）

【连接查询很重要】

多张表联合查询取出最终的结果

不同的 SQL JOIN 类型：

• inner join：如果表中有至少一个匹配，则返回行
• left join：即使右表中没有匹配，也从左表返回所有的行，左边的是主表
• right join：即使左表中没有匹配，也从右表返回所有的行，右边的表是主表
• full join：只要其中一个表中存在匹配，则返回行

根据语法出现的年代来划分的话可以分为：

SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Adminstrator，数据库管理员）；
SQL99（比较新的语法）；

根据表的连接方式划分：

• 内连接：（inner join）

• 等值连接

• 非等值连接

• 外连接

  • 左连接（左边的表是主表）
  • 右连接（右边的表是主表）

• 全连接（左右两边都是主表）

在表的连接查询中存在笛卡尔积现象 当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

• 表的别名

  在写sql语句时，我们可以对表进行起别名，如：

  select  e.ename,d.dname from enp e, dep d;
  

  使用别名的优势：

  第一： 执行效率高； 以上sql语句为了，如果不使用别名，查询enme字段，它先会去emp找，再去dept表中找。
  有时候，两张表中可能会存在字段名一样的情况。

  第二：可读性好；

1. 怎么避免笛卡尔积现象？

当然是加条件进行过滤。

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？

答案是：不会减少匹配次数， 以以上查询案例为例，查询次数还是56次。只不过显示的是有效记录。

内连接：

内连接的等值连接最大的特点是：条件是等量关系。

案例：查询每个员工的部门名称，要求显示员工名和部门名。

为了避免笛卡尔乘积，我们需要判断两个部门的编号是否一致，如果一致则匹配。正确的写法为：

select e.ename, d.dname from emp e,dept d where e.deptno = d.deptno; // SQL92的老语法，已经不使用了

语法格式：

select … from 表1 join 表2 on 连接条件 where …

select e.name, d.dname from emp e inner join dept d on d.deptno = e.deptno;//sql99，常用，其中inner可以省略

• 自连接

  最大特点：一张表看做两张表，自己连自己。

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

  select empno, ename, mgr from emp;
  

  +-------+--------+------+
  | empno | ename  | mgr  |  # mgr 为上级领导编号
  +-------+--------+------+
  |  7369 | SMITH  | 7902 |
  |  7499 | ALLEN  | 7698 |
  |  7521 | WARD   | 7698 |
  |  7566 | JONES  | 7839 |
  |  7654 | MARTIN | 7698 |
  |  7698 | BLAKE  | 7839 |
  |  7782 | CLARK  | 7839 |
  |  7788 | SCOTT  | 7566 |
  |  7839 | KING   | NULL |
  |  7844 | TURNER | 7698 |
  |  7876 | ADAMS  | 7788 |
  |  7900 | JAMES  | 7698 |
  |  7902 | FORD   | 7566 |
  |  7934 | MILLER | 7782 |
  +-------+--------+------+
  14 rows in set (0.00 sec)
  

  思路：

  s in set (0.00 sec)
  

  思路：

  思路：

  （1）我们发现，员工名和对应上级领导都在同一个表中。这里，我们可以将一张表看做两张表。

  （2）假设原始的emp为员工表，我们可以将这个表命名a；

  （3）然后，我们手动整理出领导表，命名b；

  员工的领导编号 = 领导的员工编号（领导的自身编号）

  因此：

  select a.ename as '员工名', b.ename as '领导名' 
  from emp a 
  inner join emp b // inner可以省略
  on a.mgr = b.empno;
  
  select a.ename,b.ename from emp a inner join emp b on a.mgr = b.empno;
  

  +--------+--------+
  | 员工名  | 领导名  |
  +--------+--------+
  | 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  |
  +--------+--------+
  

​

• 外连接

  内连接： 假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来。A和B两张表没有主副之分，两张表是平等的。

  外连接： 假设A表和B表进行连接，使用外连接的话，A，B两张表中有一张是主表，一张是副表，主要查询主表中的数据，捎带着查询副表。当副表中数据没有和主表中的数据匹配上，副表自动填充NULL与之匹配。

  外连接的主要特点： 主表中的数据无条件的全部查询出来。

  左连接 ：表示左边的这张表是主表；

  右连接：表示右边的这张表是主表；

  select a.ename,b.ename from emp a left outer join emp b on a.mgr = b.empno;//outer可以省略
  

• 内连接和外连接的使用情景；

  需要查找两张表同时存在的数据，使用内连接，

  需要查找两张表中数据一张表存在，另一张表不存在的时候使用外连接

  内连接查询出来的记录会缺失所以要使用外连接

• 多表连接查询

  案例：找出每一个员工的部门名称、工资等级
  （1）emp表：员工名（ename），员工工资，部门编号（deptno）；

  +--------+---------+--------+
  | ename  | sal     | deptno |
  +--------+---------+--------+
  | SMITH  |  800.00 |     20 |
  | ALLEN  | 1600.00 |     30 |
  | WARD   | 1250.00 |     30 |
  | JONES  | 2975.00 |     20 |
  | MARTIN | 1250.00 |     30 |
  | BLAKE  | 2850.00 |     30 |
  | CLARK  | 2450.00 |     10 |
  | SCOTT  | 3000.00 |     20 |
  | KING   | 5000.00 |     10 |
  | TURNER | 1500.00 |     30 |
  | ADAMS  | 1100.00 |     20 |
  | JAMES  |  950.00 |     30 |
  | FORD   | 3000.00 |     20 |
  | MILLER | 1300.00 |     10 |
  +--------+---------+--------+
  14 rows in set (0.00 sec)
  

  （2）dept表：部门编号（deptno），部门名（dname）；

  +--------+------------+----------+
  | DEPTNO | DNAME      | LOC      |
  +--------+------------+----------+
  |     10 | ACCOUNTING | NEW YORK |
  |     20 | RESEARCH   | DALLAS   |
  |     30 | SALES      | CHICAGO  |
  |     40 | OPERATIONS | BOSTON   |
  +--------+------------+----------+
  4 rows in set (0.00 sec)
  

  （3）salgrade表：工资等级（grade）

  +-------+-------+-------+
  | GRADE | LOSAL | HISAL |
  +-------+-------+-------+
  |     1 |   700 |  1200 |
  |     2 |  1201 |  1400 |
  |     3 |  1401 |  2000 |
  |     4 |  2001 |  3000 |
  |     5 |  3001 |  9999 |
  +-------+-------+-------+
  5 rows in set (0.00 sec)
  

  我们需要用到多表连接：

  select e.ename as '员工名',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.mgr;
  

  查询结果：

  +--------+------------+-------+--------+
  | 员工名  | dname      | grade | 领导名  |
  +--------+------------+-------+--------+
  | SMITH  | RESEARCH   |     1 | FORD   |
  | ADAMS  | RESEARCH   |     1 | SCOTT  |
  | JAMES  | SALES      |     1 | BLAKE  |
  | WARD   | SALES      |     2 | BLAKE  |
  | MARTIN | SALES      |     2 | BLAKE  |
  | MILLER | ACCOUNTING |     2 | CLARK  |
  | ALLEN  | SALES      |     3 | BLAKE  |
  | TURNER | SALES      |     3 | BLAKE  |
  | JONES  | RESEARCH   |     4 | KING   |
  | BLAKE  | SALES      |     4 | KING   |
  | CLARK  | ACCOUNTING |     4 | KING   |
  | SCOTT  | RESEARCH   |     4 | JONES  |
  | FORD   | RESEARCH   |     4 | JONES  |
  | KING   | ACCOUNTING |     5 | NULL   |
  +--------+------------+-------+--------+
  14 rows in set (0.00 sec)
  

子查询

select 语句当中嵌套select语句，被嵌套的select语句就是子查询

子查询一般出现的位置：

select
	...(select)...
from
	...(select)...
where
	...(select)...

• from后嵌套子查询【最常用，很重要】

  案例1： 找出每个部门平均薪水的薪资等级

  第一步：计算每个部门的平均工资（按照部门编号分组）：

  select deptno, avg(sal)  as avgsal  from emp group by deptno;
  
  +--------+-------------+
  | deptno | avgsal	   |
  +--------+-------------+
  |     10 | 2916.666667 |
  |     20 | 2175.000000 |
  |     30 | 1566.666667 |
  +--------+-------------+
  3 rows in set (0.00 sec)
  

  第二步：对每个部门的平均工资进行分级：

  第二步：对每个部门的平均工资进行分级：

  我们可以将上面查询出的结果当作一张临时新表t，与表salgrade进行连接查询，条件为
  t.avgsal between s.losal and s.hisal

  select 
  	t.deptno, t.avgsal, 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 |
  +--------+-------------+-------+
  |     10 | 2916.666667 |     4 |
  |     20 | 2175.000000 |     4 |
  |     30 | 1566.666667 |     3 |
  +--------+-------------+-------+
  3 rows in set (0.00 sec)
  

  **案例2：**找出每个部门平均的薪资等级

  分析：找出每个员工薪水的等级 / 员工数 = 平均薪资等级

  select s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
  
  select avg(t.grade) from 
  (select s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
  group by t.deptno;
  
  #但是，这里实际上没必要把新表看做一张临时表，这里其实可以直接写：
  
  select 
  	e.deptno, avg(s.grade) 
  from 
  	emp e 
  join 
  	salgrade s 
  on 
  	e.sal between losal and hisal
  group by
  	deptno;
  

• 在select后面嵌入子查询
  案例： 找出每个员工所在的部门名称，要求显示员工名和部门名；

  方法一：使用内连接的方式：

  （1）第一步： 找出每个员工所在的部门编号：

  select ename, deptno from emp;
  

  （2）第二步： 关联dept表：

  select e.ename, e.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
  

  查询结果：

  查询结果：

  +--------+--------+------------+
  | ename  | deptno | dname      |
  +--------+--------+------------+
  | SMITH  |     20 | RESEARCH   |
  | ALLEN  |     30 | SALES      |
  | WARD   |     30 | SALES      |
  | JONES  |     20 | RESEARCH   |
  | MARTIN |     30 | SALES      |
  | BLAKE  |     30 | SALES      |
  | CLARK  |     10 | ACCOUNTING |
  | SCOTT  |     20 | RESEARCH   |
  | KING   |     10 | ACCOUNTING |
  | TURNER |     30 | SALES      |
  | ADAMS  |     20 | RESEARCH   |
  | JAMES  |     30 | SALES      |
  | FORD   |     20 | RESEARCH   |
  | MILLER |     10 | ACCOUNTING |
  +--------+--------+------------+
  

  方法二：在select后面嵌入子查询

  方法二：在select后面嵌入子查询

  第一步：找出部门名：
  select deptno, dname from dept;
  第二步：嵌套查询

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

• union

union可以将查询结果集相加

select ename ,job from emp where job = 'manager' 
union 
select ename,job from emp where job = 'salesman';

union 相加的连个查询结果必须列数相同，并且相加后的表的字段名是以前一个查询决定的

limit

【重点：分页查询主要依赖limit】

limit 是 mysql 特有的，其他数据库没有，不通用，oracle中有一个类似的机制叫做：rownum

• limit 的作用: 取结果集 中的部分数据

  limit 是sql语句中的最后一个执行环节

• 语法：

  limit startIndex,length

  startIndex:起始位置

  length：取的长度,也就是取几个

  数据位置是从 0 开始的

  案例：

  取工资前五名：
  select ename,salary from emp order by desc limit 0,5;
  select ename,salary from emp order by desc limit 5;  //默认从第一个开始
  

  案例：取工资排名的第四到第九名的员工

  select ename,salary from emp order by desc limit 3,6; //注意：后面的数是取几条数据不是取到几
  

  注意：后面的数是取几条数据不是取到几

• 通用的标准分页 sql 语法

  如果每一页显示3条记录，那么：

  第1页：0, 3
  第2页：3, 3
  第3页：6, 3
  第4页：9, 3
  第5页：12, 3
  …
  第n页：(n-1)*3, 3

  如果每页显示 pageSize 条记录：

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

DDL（database define ）

create drop alter

• 表的创建 （create）

  语法：create create create create create create

  create table 表名(字段名1 数据类型，字段名2 数据类型,...);
  

  mysql中常见的数据类型：

  int 	整数型
  bigint	长整型
  float   浮点型
  double	浮点型
  date	日期类型（对应Java中的java.sql.Date）
  char	定长字符串（对应Java中的String）
  varchar	可变长度字符串（对应Java中的StringBuffer、StringBuilder）
  BLOB	二进制大对象（存储图片、视频等流媒体信息）Binary Large Object
  CLOB	字符大对象（存储较大文本，比如可以存储4G字符串）Character Large Object
  

  在实际的开发中，视频文件一般是不会放在数据库中的，因为数据库资源很珍贵，视频文件比较大，一般会把视频放在硬盘中，将硬盘的文件路径存在到数据库中。但是会将一些小的图片（头像）和视频直接存在数据库中，这样的好处是便于维护。

  表名一般建议以：t_ 或者 tbl_ 开头，见名知义

  示例：

  创建一张学生表：

  create table t_student(
  	id bigint,
      name varchar(255),
      gender char(1),
      classid varchar(255),
      birth char(10)
  );
  

• 复制表：

  //完全复制一张表
  create table emp1 as select * from emp;
  //只复制表的指定的列
  create table emp2 as select ename,empno from emp;
  

• 删除表
  通用写法：

drop table 表名; // 这个通用。

drop table if exists 表名; // oracle不支持这种写法。

• 修改表结构
  在实际的开发中，我们很少去修改的表的结构。因为在初次设计时，就已经设计好表的结构。除非前期的设计有很大的问题，但是这种情况很常见。即使后期要修改的表的结构，我们可以使用Navicat或其他可视化工具进行修改。

  修改表结构的语句不会出现在Java代码当中。

  出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）

  （1）修改列的语法：

  alter table 表名 modify 字段名 数量类型 [default] [修改该列的位置]
  

  例如：

  alter table t_student modify name char(2) null first;
  

  （2）重命名数据表：

  alter table 表名 rename to 新表名
  

DML(database Manipulation language)

insert delete update

• insert 添加数据

  语法：

  • insert into 表名（字段1，字段2，字段3，…）values（值1，值2，值3，…）；

    字段的数量和值相同，并且数据类型对应相同，顺序没有要求

  • 省略列名插入数据：

    插入数据时，我们可以省略列名，但后面的值必须要按照顺序进行赋值

  • 一次插入多行数据：

    insert into 表名 (列1, 列2, 列3, …) values (值1, 值2, 值3, …), (值1, 值2, 值3, …)；

  • 将查询结果插入到另一张表中：

    insert into dept1 select * from dept;

    （要求列数相同）

    一：如果要插入目标表不存在：

    select * into 目标表 from 表 where …

    二：如果要插入目标表已经存在：

    insert into 目的表 select * from 表 where 条件

    三：如果是跨数据库操作的话： 怎么把A数据库的atable表所查询的东西，全部插入到B 数据库的btable表中

    select * into B.btable from A.atable where …

• update 修改数据

  语法：

  update 表名 set 列名1 = 值1，列名2 = 值2…where…

  注意：没有条件整张表的数据全部更新

  修改指定字段数据
  案例：将dept表中的的部门10的数据的LOC（地点）改为SHANGHAI，DNAME（部门名）改为RENSHIBU

  表dept1 原始记录：

  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   |
  +--------+------------+----------+
  
  update dept1 set LOC = 'SHANGHAI', DNAME = 'RENSHIBU' where deptno = 10;
  

• delete 删除数据

  语法：

  delete from 表名 where 条件；

  注意没有条件全部删除

  案例：删除10部门的数据：

  delete from dept1 where deptno = 10;
  
  mysql> select * from dept1;
  +--------+-------+------+
  | DEPTNO | DNAME | LOC  |
  +--------+-------+------+
  |     20 | x     | y    |
  |     30 | x     | y    |
  |     40 | x     | y    |
  |     20 | x     | y    |
  |     30 | x     | y    |
  |     40 | x     | y    |
  +--------+-------+------+
  6 rows in set (0.00 sec)
  

• 删数据量特别大的表中的数据【删除是不可逆的，找不回来，谨慎使用】

  truncate table emp; //表被截断，数据丢失，不可回滚
  

约束（constraint）

• 常见的约束：四个

  1. 非空约束(not null)：约束的字段不能为NULL；

     create table t _user(
     	id int,
     	username varchar(255) not null,
     	password varchar(255)
     );
     

  2. 唯一约束(unique)：约束的字段不能重复 ;

     //给单个字段添加唯一性约束【列级约束】
     drop table if exists t_student;
     create table t_student (
      id int;
      username varchar(255) unique;
      password varchar(255)
     );
     
     //多个字段唯一性约束【表级约束】
     drop table if exists t_student;
     create table t_student (
      id int;
      username varchar(255) unique;
      password varchar(255)
     );
     

  3. 主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）；

     //主键列级约束
     drop table if exists t_user;
     create table  t_user(
     id int primary key,  // 列级主键
     username varchar(255),
     email varchar(255)
     );
     
     //主键表级约束
     drop table if exists t_user;
     create table u_user(
     id int,
     username varchar(255),
     password varchar(255)
     primary key(id) // 表级约束
     );
     

     主键的作用：主键值（主键列的每一行的值）是这行记录在这张表中的唯一标识

     表的设计三范式中：第一范式就要求任何一个表中都应该主键

     一张的表的主键只能有一个，但多个字段可以联合起来添加一个主键称为复合主键（复合主键不推荐使用）

     主键的分类

     • 根据主键字段的字段数量来划分：

       • 单一主键：推荐使用，常用的。（这种方式是推荐的）
       • 复合主键：多个字段联合起来添加一个主键约束（复合主键不建议使用，因为复合主键违法三范式）

     • 根据主键的性质来划分：

       • 自然主键：主键值最好就是一个和业务没有任何关系的自然数。

       • 业务主键：主键值和系统的业务挂钩，例如，使用银行卡的卡号作为主键，使用身份证号码作为主键。（不推荐使用），最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变，主键值可能也需要发生变化，但是有时候没有办法变化，因为变化可能会导致主键值发生变化。

     mysql提供主键值自增：auto_increment 从1开始自增

     drop table if exists t_user;
     create table t_user(
     id int primary key auto_increment,
     username varchar(255)
     );
     

     oracle中也提供了一种机制提供自增：序列

  4. 外键约束(foreign key)：（简称FK）；

     drop table if exists t_student;
     drop table if exists t_class;
     
     creat table t_class(
     	id int primary key,
         cnanme varchar(255),
     );
      
     creat table t_student(
     	sid int primary key,
         sname varchar(355),
         cid int,
         foreign key(cid) references t_class(id)
     );
     

     PS：

     • 外键可以为null

     • 外键字段引用某个表的字段时需要保证被引用的字段具有唯一性约束（unique）

  5. 检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束

存储引擎（了解）

常见的存储引擎
4.1 MyISAM

	  Engine: MyISAM
	  Support: YES
	  Comment: MyISAM storage engine
Transactions: NO
	   	  XA: NO
  Savepoints: NO

MyISAM这种存储引擎不支持事务。

MyISAM是MySQL最常用的存储引擎，但是这种引擎不是默认的。

MyISAM采用三个文件组织一张表：

xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）
优点： 可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。

缺点： 不支持事务。

4.2 InnoDB
InnoDB存储引擎是MySQL的默认的引擎。

	  Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
		  XA: YES
  Savepoints: YES

它管理的表具有下列主要特征：

它管理的表具有下列主要特征：

每个InnoDB表在数据库目录中以.frm格式文件表示

InnoDB表空间tablespace被用于存储表的内容

提供一组用来记录事务性活动的日志文件

用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理

提供全ACID兼容

在MySQL服务器崩溃后提供自动恢复

多版本（MVCC）和行级锁定

支持外键及引用的完整性，包括级联删除和更新 。

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

表的结构存储在xxx.frm文件中

缺点：数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。

这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。

InnoDB支持级联删除和级联更新（一般在删表的时候，要先删子表，再删父表。而级联删除表示删父的时候也把

子表一起删除。级联更新也是如此，更新一个字段值，凡是用过该字段值的都会更新，在实际开发中很少用到）。

4.3 MEMORY

	  Engine: MEMORY
	 Support: YES
	 Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
	  	  XA: NO
  Savepoints: NO

MEMORY存储引擎管理的表具有下列特征：

在数据库目录内，每个表均以.frm格式的文件表示。

表数据及索引被存储在内存中。

表级锁机制。

不能包含TEXT或BLOB字段。

缺点： 不支持事务。 数据容易丢失。因为所有数据和索引都是存储在内存当中的。

优点： **查询速度最快。**因为数据存储在内存中

以前叫做HEPA引擎。

事务(Transaction)

• sql中和事务相关的语句只有 DML 语句 insert 、delete、update

• 事务的原理

  假如一个事，需要先执行insert，再执行一条update，最后执行一条delete，这个事才算完成。

  开启事务机制（开始）：

  执行insert语句–> insert … （这个执行成功之后，把这个执行记录到数据库的操作历史【记录到缓存中】，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据）
  执行update语句–> update… （这个执行也是记录一下操作历史，不会真正的修改硬盘上的数据）
  执行delete语句–> delete… （这个执行也是记录一下操作历史，不会真正的修改硬盘上的数据）

  提交事务或者回滚事务（结束）

  提交和回滚都会使事务结束，如果是提交，将会把操作历史执行在硬盘上，让硬盘上的数据彻底改变，然后所有的历史操作就会清空。如果是回滚事务，也会把历史记录清空，不会把操作写入到硬盘

  提交事务：commit；

  回滚事务：rollback；

  设置保存点：savepoint；比如设置一个保存点 savepoint s, 就可以使用 rollback s 回滚到指定的位置

• 事务的四大特性：ACID

  A：原子性：事务是最小的工作单元，不可再分。

  C：一致性：同步成功和失败，事务必须保证多条DML语句同时成功或者同时失败。

  I：隔离性：事务A与事务B之间具有隔离性。

  D：持久性：持久性说的是最终数据必须从缓存中持久化到硬盘文件中，事务才算成功的结束。

• 事务的隔离级别：

  read uncommitted

  read committed

  repeatable read

  serializable read

  第一级别：读未提交(的事务) 【对方事务还没提交，我们当前事务可以读取到对方未提交的数 据】

  ​ 存在脏读（Dirty Read）现象：表示读到了脏的数据

  第二级别：读已提交（的事务）【对方事务提交的事务我方可以读取到】

  ​ 解决了脏读

  ​ 存在 不可重复读 ：不可重复读：对方事务提交了新的，我方事务拿到的就是对方 事务新提交的，之前的就读取不到了，每次读取的都是不同的

  第三级别：可重复读；【当我方事务在一次执行过程中，对方事务提交了新的，我方事务读到 的还是上次的，所以叫可重复读】

  ​ 解决了不可重复读

  ​ 存在 读取到的数据是幻象（比如对方事务把十条记录都删除了，我方还是可以读取 到）

  第四级别：序列化读（serializable）

  ​ 事务之间是并发的（类似并发就是需要排队一个一个的来），一个事务没有结束， 另一个事务无法开启

  ​ 解决了所有问题，

  ​ 效率低，需要事务排队

  Mysql数据库默认的隔离级别是：可重复读

  Oracle数据库默认的隔离级别是：读已提交

  为什么mysql的默认隔离级别是 可重复读

  这个是有历史原因的，当然要从我们的主从复制开始讲起了！
  主从复制，是基于什么复制的？
  是基于binlog复制的！这里不想去搬binlog的概念了，就简单理解为binlog是一个记录数据库更改的文件吧～
  binlog有几种格式？
  三种，分别是

  • statement:记录的是修改SQL语句
  • row：记录的是每行实际数据的变更
  • mixed：statement和row模式的混合

  那Mysql在5.0这个版本以前，binlog只支持STATEMENT这种格式！而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别！

  讲讲我们为什么选读已提交(Read Commited)作为事务隔离级别！

  缘由一：在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！

  缘由二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行

  在RC级别下，主从复制用什么binlog格式？
  OK,在该隔离级别下，用的binlog为row格式，是基于行的复制！Innodb的创始人也是建议binlog使用该格式！

  互联网项目请用：读已提交(Read Commited)这个隔离级别！

• mysql事务默认情况下是自动提交的（自动提交：只要执行任意DML语句则提交一次）

  关闭自动提交：start transaction；

  insert into t_user (username) values('zs');
  
  select * from t_user;
  
  +----+----------+
  | id | username |
  +----+----------+
  |  1 | zs       |
  +----+----------+
  
  mysql> rollback;
  Query OK, 0 rows affected (0.00 sec)
  
  即使使用了rollback，这里也是没有办法回滚了，因为MySQL默认提交了事务。已经将上面的插入语句持久化到了硬盘中：
  
  mysql> select * from t_user;
  +----+----------+
  | id | username |
  +----+----------+
  |  1 | zs       |
  +----+----------+
  
  
  使用start transaction;关闭事务自动提交;（注意：rollback一次就是失效了）
  
  start transaction;
  
  insert into t_user (username) values ('lisi');
  insert into t_user(username) values('wangwu');
  
  mysql> select * from t_user;
  +----+----------+
  | id | username |
  +----+----------+
  |  1 | zs       |
  |  2 | lisi     |
  |  3 | wangwu   |
  +----+----------+
  
  回滚：
  
  rollback;
  
  再次查询：
  
  select * from t_user;
  
  +----+----------+
  | id | username |
  +----+----------+
  |  1 | zs       |
  +----+----------+
  

索引

• 索引的作用

  查询一张表有两种检索方式：

  1. 全表扫描
  2. 根据索引检索（效率很高）

  索引可以提高检索效率

• 什么时候给字段添加索引：

  • 数据量庞大

  • 该字段涉及的 DML（insert、delete、update）操作很少（因为字段进行修改操作，索引也需要维护）

  • 该字段经常出现在 where 子句中（根据哪个字段查询）

    select ename, sal from emp where ename = 'SMITH';
    
    当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
    

• 注意：主键和具有 unique 约束的字段会自动添加索引，所以根据主键检索效率比较高

• 如何创建索引

  creat index 索引名 on 表名（列名）;

  creat index emp_sal_index on emp(sal);
  

  删除索引：

  drop index 索引名称 on 表名；
  

• 索引的实现原理：通过 B Tree 缩小扫描范围

  索引的工作流程：

  （1）给ename字段添加索引时：

  create index emp_ename_index on emp(ename);
  

  只要这句一执行，会在硬盘或是在内存中生成索引，具体是硬盘还是内存，是要根据不同的存储引擎而定。如果是memory的引擎，会存储在内存中，如果是MyISAM，则会存在硬盘中。

  （2）查询

  select ename from emp where ename = 'SMITH';
  

  （3）在内存或硬盘中，生成了一个索引对象（emp_ename_index），如下图右面的图；

  （3）在内存或硬盘中，生成了一个索引对象（emp_ename_index），如下图右面的图；

  （4）索引进行分区，根据首字母分为了M区，S区…；

  （5）首先条件是ename字段，这时会查看ename字段有没有对应的索引，结果找到了ename字段对应的索引对象是：emp_ename_index，然后通过索引检索。首先是ename=‘SMITH’，先定位S区，继续定位m区…缩短了扫描的数量，很快定位到SMITH。

  （6）数据库表中的任意一行记录都会有一个物理地址。查询语句的原sql语句将变为：

  select ename from emp where ename = Ox3;
  

  这条sql语句不再通过表查了，而是通过内存或者硬盘进行查询。

  

• 索引的分类：

  • 单一索引：给单个字段添加索引；
  • 复合索引： 给多个字段联合起来添加1个索引；
  • 主键索引：主键上会自动添加索引；
  • 唯一索引：有unique约束的字段上会自动添加索引；
    …

• select ename from emp where ename like '%A%';
  

  模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

  即使ename加了索引，索引也是失效，仍然要全表扫描。

• 可以使用explain关键字查询mysql的执行计划，该关键字是mysql数据库独有的！

  explain select 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为all：表示全表扫描；
  
  rows为14：表示扫描了14行记录；
  

视图

• 什么是视图

  同一张表的数据站在不同的角度区看待

  从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据，用户只能看到标有自己用户名的行。从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。从数据库系统数据库系统)内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样。

• 怎么创建视图,删除视图

  create view myview as select empno,ename from emp;
  
  drop view myview;
  

• 对视图进行增删改查，会影响到原表的数据

• 视图的作用：

  • 简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常

    使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

  • 安全性。通过视图用户只能查询和修改他们所能见到的数据。但不能授权到数据库特定行和特定的列上。

    通过视图，用户可以被限制在数据的不同子集上：

    使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。

DBA命令

• 新建用户

  create user 用户名 identified by '密码';
  

• 授权

  grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
  

  dbname= * 表示所有数据库

  tbname= * 表示所有表

  login ip = %表示任何ip

  password为空，表示不需要密码即可登录

  with grant option; 表示该用户还可以授权给其他用户

  细粒度授权

  首先以root用户进入mysql，然后键入命令：grant select,insert,update,delete on . to p361 @localhost Identified by “123”;

  如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 “%” 。

  粗粒度授权

  我们测试用户一般使用该命令授权，

  GRANT ALL PRIVILEGES ON . TO ‘p361’@’%’ Identified by “123”;

  注意：用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:

  GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123"  WITH GRANT OPTION;
  

  privileges包括：

  alter：修改数据库的表

  create：创建新的数据库或表

  delete：删除表数据

  drop：删除数据库/表

  index：创建/删除索引

  insert：添加表数据

  select：查询表数据

  update：更新表数据

  all：允许任何操作

  usage：只允许登录

• 回收权限

  revoke privileges on dbname[.tbname] from username;
  
  revoke all privileges on *.* from p361;
  

• 导出整个数据库

在windows的dos命令窗口中执行：

mysqldump bjpowernode>D:\bjpowernode.sql -uroot -proot

• 导出指定库下的指定表

  在windows的dos命令窗口中执行：

  mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –proot
  

• 导入数据库

  登录MYSQL数据库管理系统之后执行：

  创建数据库:

  create database 数据库名;
  

  使用该数据库：

  use 数据库名;
  

  导入：

  source D:\ bjpowernode.sql
  

数据库设计三范式

• 三范式：

1. 任何一张表都应该有主键，并且每一个字段保证原子性（不可再分）

2. 所有非主键字段完全依赖主键，不能产生部分依赖（所以不建议使用复合主键）

3. 所有非主键字段直接依赖主键，不能产生传递依赖

• 三范式总结

• 第一范式：有主键，具有原子性，字段不可再分；

• 第二范式：非主键全部依赖主键，没有部分依赖；

• 第三范式：非主键直接依赖主键，没有传递依赖

• 第一范式

  任何一张表都应该有主键，并且每一个字段保证原子性（不可再分）

  第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

  不符合第一范式的示例：

  

  存在问题：

  最后一条记录和第一条重复（不唯一，没有主键）；

  联系方式字段可以再分，不是原子性的；

  改进后：

  

  关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采

  用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利

  行可能就采用一个字段了。

• 第二范式

  所有非主键字段完全依赖主键，不能产生部分依赖（所以不建议使用复合主键）

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

  不符合第二范式的示例：

  

  确定主键：

  

  以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗

  余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了

  主键的一个字段教师编号，这就是第二范式部分依赖。

  解决方案：

  学生信息表：

  

  教师信息表：

  

  教师和学生的关系表：

  

  如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系;

  以上是一种典型的“多对多”的设计；

  口诀：遇到多对多，表的设计关系为： 设计三张表，关系表中设计两个外键。

• 第三范式

  所有非主键字段直接依赖主键，不能产生传递依赖

  不符合第三范式的示例：

  

  从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级

  编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：

  学生信息表：

  

  班级信息表：

  

  以上设计是一种典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向

  一的一方的主键。

  口诀：一对多的情况，设计两张表，(一对多中的)多的表加外键。

• 注意：三范式的目的是减少数据冗余，实际开发中以客户需求为主，有的时候会拿冗余换速度

• 一对一

  在实际开发时用户表拆成两个表，一个只用来存储用户所需要的登录信息，如：账号，用户名，密码

  一个用来存储用户的详细信息

  第一种：主键共享；

  id既是主键，又是外键；

  t_user_login 用户登录表：

  id(pk)		username			password
  -----------------------------------------
  1				zs					123
  2				ls					456
  

  t_user_detail 用户详细信息表：

  id(pk+fk)	realname			tel			....
  ------------------------------------------------
  1				张三		  1111111111
  2				李四		  1111415621
  

  第二种：外键唯一；

  t_user_login 用户登录表：

  id(pk)		username		  password
  --------------------------------------
  1				zs				 123
  2				ls				 456
  

  由于外键是可以为空的，主键是唯一不为空，所以希望外键也有这个功能，就用外键保证不为空，唯一表示不重复。

  t_user_detail 用户详细信息表：

  id(pk)	   realname			tel			userid(fk+unique)....
  -----------------------------------------------------------
  1			张三	   1111111111			2
  2			李四	   1111415621			1
  

​

mysql笔试题

1. 取得每个部门最高薪水的人员名称

   emp员工表 dept部门表

   第一步：先找每个部门的最高薪水
   select deptno,max(sal) from emp group by deptno;
   +--------+----------+
   | deptno | max(sal) |
   +--------+----------+
   |     20 |  3000.00 |
   |     30 |  2850.00 |
   |     10 |  5000.00 |
   +--------+----------+
   第二步：把上面查询的结果作为子表和emp进行连接查询
   select e.ename,t.deptno,t.maxsal from emp e 
   join (select deptno,max(sal) as maxsal from emp group by deptno) t on e.deptno = t.deptno and t.maxsal = e.sal;
   +-------+--------+---------+
   | ename | deptno | maxsal  |
   +-------+--------+---------+
   | BLAKE |     30 | 2850.00 |
   | SCOTT |     20 | 3000.00 |
   | KING  |     10 | 5000.00 |
   | FORD  |     20 | 3000.00 |
   +-------+--------+---------+
   
   
   //错误的写法
   select e.ename,e.deptno, max(sal) from emp e join dept d on e.deptno = d.deptno group by e.deptno;
   
   //上面的是错误的，因为它是按照deptno进行分组的，没有按照name分组所以查询出来的name是随机值，没有对应关系，查询结果是错误的：
   +-------+--------+----------+
   | ename | deptno | max(sal) |
   +-------+--------+----------+
   | SMITH |     20 |  3000.00 |  #SMITH的工资是800，不是对应关系
   | ALLEN |     30 |  2850.00 |
   | CLARK |     10 |  5000.00 |
   +-------+--------+----------+
   3 rows in set (0.00 sec)
   

• 2、哪些人的薪水在部门的平均薪水之上

  //找每个部门的平均薪水
  select deptno,avg(sal) from emp group by deptno;
  //找大于平均薪水的员工
  select e.ename,e.sal,e.deptno 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;
  

• 3、找部门的（所有人）平均薪水等级

  //先找每个人的薪水等级
  select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; 
  //分组平均
  select e.deptno,avg(t.grade) from emp e 
  join (select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
  on e.ename = t.ename group by deptno;
  
  //其实在第一步完成后，没有必要再进行连接查询了，可以直接基于第一步的查询结果分组求平均值
  select deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by deptno; 
  
  //错误示例：
  select deptno, avg(select s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) from emp group by deptno;
  

• 4、不准用分组函数max() 取得最高薪水（给出两个解决方案）

  //第一种：降序，limit(0,1)
  select sal from emp order by sal desc limit 1;//默认从0开始，省略
  
  //第二种：表的自连接
  select distinct a.sal from emp a join emp b on a.sal > b.sal;
  select sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);
  //a最大的不在新查询出来的表中，再用not in 就可以筛选出来了
  

• 5、找平均薪水最高的部门编号(至少两种方案)

  //每个部门的平均薪水
  select deptno,avg(sal) from emp group by deptno;
  //第一种：排序
  select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 1;
  
  //第二种：max()
  select deptno,avg(sal) as avgsal from emp group by deptno 
  having avgsal = (select max(t.avgsal) from emp e 
  join (select deptno ,avg(sal) as avgsal from emp group by deptno) t
  on e.deptno = t.deptno);
  
  //错误示例：e表和t表的的deptno是没有关系的，e表的deptno是员工的部门编号，而t表的deptno是平均薪水最高的部门编号，所以直接查出来的deptno是错误的
  select t.deptno,max(t.avgsal) from emp e 
  join (select deptno ,avg(sal) as avgsal from emp group by deptno) t
  on e.deptno = t.deptno;
  
  

• 6、取得平均薪水最高的部门名称

  //第一种
  //每个部门的平均薪水
  select deptno,avg(sal) from emp group by deptno;
  //最高的部门名
  select d.dname, max(avgsal) 
  from (select deptno,avg(sal) as avgsal from emp group by deptno) t join dept d 
  on t.deptno = d.deptno;
  
  //第二种
  //通过分组加排序取得平均薪资最高的部门编号
  select deptno,sal from emp group by deptno order by sal desc limit 1;
  //和部门表连接查部门名
  select d.dname 
  from (select deptno,sal from emp group by deptno order by sal desc limit 1) t
  join dept d 
  on d.deptno = t.deptno;
  

• 7、找平均薪水的等级最低的部门名

  //找每个部门平均薪水
  select deptno,avg(sal) from emp group by deptno;
  //最低等级(多表连接)
  select d.dname,min(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
  join dept d 
  on d.deptno = t.deptno;
  

• 8、找比普通员工（员工代码没有在mgr字段上出现的都是普通员工）的最高薪水还要高的领导人名称

  //找普通员工最高薪资
  not in 在使用的时候记得排除后面括号里面的null值
  select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)
  //找高于普通员工薪水的领导
  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));
  

• 9、薪水最高的前五名：

  //没有考虑重复
  select ename,sal from emp order by sal desc limit 5;
  

• 10、取得薪水最高的第六到第十名员工

  select ename,sal from emp order by sal desc limit (5,5);
  

• 11、取得最后入职的5名员工

  select ename,sal from emp order by hiredate desc limit 5;
  

• 12、取得每个薪水等级有多少员工

  //每个员工的薪水等级
  select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
  
  //分组统计
  select t.grade,count(t.ename) 
  from (select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
  group by t.grade;
  

• 13、面试题：
  有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
  S（SNO，SNAME）代表（学号，姓名）
  C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
  SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
  问题：
  1，找出没选过“黎明”老师的所有学生姓名。
  2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
  3，即学过 1 号课程又学过 2 号课所有学生的姓名。

  1
  select s.name from sc join s on sc.sno = s.sno join c on sc.cno = c.cno where sc.cno not in (c.cname = "黎明");
  
  2
  select s.sname,c.avg(scgrade) from sc join s on s.sno =  sc.sno where sc.scgrade < 60;
  
  3
  select s.name from sc join s on s.sno = sc.sno join c.cno = sc.cno on c.cno = sc.cno where 
  

• 14、列出所有员工及领导的名字

  select a.ename '员工', b.ename '领导' from emp a 
  left join emp b on a.mgr = b.empno;
  

• 15、列出受雇日期早于其直接上级的员工的编号姓名，部门名

  select a.ename '员工'，b.ename '领导' from emp a
  join emp b
  on a.mgr = b.empno
  join dept d
  on a.deptno = d.deptno
  where a.hiredate < b.hiredate;
  

• 16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

  select e.*,d.*
  from emp e
  right join dept d
  on e.deptno = d.deptno;
  

• 17、列出至少有五个员工的所有部门

  select deptno from emp group by deptno having count(ename) >= 5; 
  

pt d
on d.deptno = t.deptno;

- 8、找比普通员工（员工代码没有在mgr字段上出现的都是普通员工）的最高薪水还要高的领导人名称

```sql
//找普通员工最高薪资
not in 在使用的时候记得排除后面括号里面的null值
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)
//找高于普通员工薪水的领导
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));

• 9、薪水最高的前五名：

  //没有考虑重复
  select ename,sal from emp order by sal desc limit 5;
  

• 10、取得薪水最高的第六到第十名员工

  select ename,sal from emp order by sal desc limit (5,5);
  

• 11、取得最后入职的5名员工

  select ename,sal from emp order by hiredate desc limit 5;
  

• 12、取得每个薪水等级有多少员工

  //每个员工的薪水等级
  select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
  
  //分组统计
  select t.grade,count(t.ename) 
  from (select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
  group by t.grade;
  

• 13、面试题：
  有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
  S（SNO，SNAME）代表（学号，姓名）
  C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
  SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
  问题：
  1，找出没选过“黎明”老师的所有学生姓名。
  2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
  3，即学过 1 号课程又学过 2 号课所有学生的姓名。

  1
  select s.name from sc join s on sc.sno = s.sno join c on sc.cno = c.cno where sc.cno not in (c.cname = "黎明");
  
  2
  select s.sname,c.avg(scgrade) from sc join s on s.sno =  sc.sno where sc.scgrade < 60;
  
  3
  select s.name from sc join s on s.sno = sc.sno join c.cno = sc.cno on c.cno = sc.cno where 
  

• 14、列出所有员工及领导的名字

  select a.ename '员工', b.ename '领导' from emp a 
  left join emp b on a.mgr = b.empno;
  

• 15、列出受雇日期早于其直接上级的员工的编号姓名，部门名

  select a.ename '员工'，b.ename '领导' from emp a
  join emp b
  on a.mgr = b.empno
  join dept d
  on a.deptno = d.deptno
  where a.hiredate < b.hiredate;
  

• 16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

  select e.*,d.*
  from emp e
  right join dept d
  on e.deptno = d.deptno;
  

• 17、列出至少有五个员工的所有部门

  select deptno from emp group by deptno having count(ename) >= 5;