mysql--基础知识_windows mysql命令-优快云博客

本文链接：https://blog.youkuaiyun.com/lcx1252267029/article/details/143166542

一、操作系统

（一）windows使用cmd快速进入管理员

1、启动或关闭mysql服务

语法：net stop 服务名称 (启动）eg:net stop mysql
net start 服务名称 (关闭）

2、登录

假如密码为123456

（1）明密登录：mysql -uroot -p123456

（2）隐藏密码登录：mysql -uroot -p 回车后再输入123456

（二）linux操作系统

1、启动mysql服务：sudo mysql

（三）常用命令

mysql一般默认是不区分大小写，但在linux中，表名一般是区分在小写的。

退出：exit

查看mysql下的所有数据库：show databases;

使用数据库：use 数据库名

创建数据库：create database 数据库名

查看表：show tables;

导入数据：source 文件路径（路径不可出现中文）

二、数据库的基本知识

数据库最基本的单元为表table

行（row）：被称为数据/记录

列（colum）：被称为字段

字段：字段名、数据类型、约束行属性；则数据类型有字符串、数据、日期。

三、sql语句的分类

DQL：数据查询语言（均带select）

DML：数据操作语言，对表中的数据进行增删改
insert 增
delete 删
update 改

DDL：数据定义语言，主要操作表结构
create 新建，同等于增加
drop 删除
alter 修改

TCL：事务控制语言
事务提交：comint
事务回滚：rollback

DCL：数据控制语言
eg：授权grant，撤销权限revoke

四、sql基本语言练习

资料包：bjpowernode.sql

链接: https://caiyun.139.com/m/i?075CgGjJxR2Fg 提取码:pici 复制内容打开中国移动云盘手机APP，操作更方便哦

1.创建数据库

create database test;

2.查看数据库

show databases;

3.使用数据库

use test;

4.查看存在的表

show tables;

5.导入数据

source /home/***/bjpowernode.sql

6.查看存在的表

show tables;

+----------------+
| Tables_in_test |
+----------------+
| DEPT           |            部门表
| EMP            |             员工表
| SALGRADE       |      工资表
+----------------+

7.查看表数据

select * from 表名;

mysql> select * from emp; // 从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 salgrade;
   +-------+-------+-------+
   | GRADE | LOSAL | HISAL |
   +-------+-------+-------+
   |     1 |   700 | 1200 |
   |     2 | 1201 | 1400 |
   |     3 | 1401 | 2000 |
   |     4 | 2001 | 3000 |
   |     5 | 3001 | 9999 |
   +-------+-------+-------+

8.查看表结构

desc 表名;

9.查看数据库版本号

select version();

10.查看当前使用的数据库

select database();

11.简单查询

11.1. 查询一个字段

select DNAME from DEPT;

注：select 与 from为关键字，字段名与表名都是标识符，sql语句以英文分号";"为结尾，并不区分在小写。

11.2.查询多个字段以逗号","隔开

select DNAME, LOC from DEPT;

11.3.查询所有字段

①把所有字段名写上

②使用select * from 表名

方法二：效率与可读性差

11.4.为查询取别名

①select DNAME as '部门名称' from DEPT;

②select DNAME '部门名称' from DEPT;

注：可使用关键字as取别名或使用空格，若别名中为空格或为中文可用单引号括起来，双引号也可，但所有数据库中字符串统一使用单引号，单引号是标准，双引号在oracle数据库中无法使用。

11.5.字段可参与数学运算

select ename,sal*12 yearsal from EMP;

12.条件查询

12.1.查询符合条件

select
        字段1,字段2,字段3……
from
        表名
where
        条件;

12.2.条件符号

= 等于，可等于字符中，但不等于null，因null非一个值

<>或!= 不等于

> 大于

>= 大于等于

< 小于

<= 小于等于

between...and...两个值之间，为闭区间包括两边的值，需遵循左小右大，等同于>= and <=

is null 为空|is not null 不为空

and 并且|or 或者

select ename,job,deptno from EMP where job = "CLERK" and (deptno =10 or deptno =20);

注：and与or 的优化级：and>or，若需要先执行or需要加小括号，不确定优先级时也加小括号。

in 包含，相当于多个or |not in不在此范围内

select empno,ename,job from EMP where job = 'SALESMAN' OR job = 'MANAGER' ;

select empno,ename,job from EMP where job in ( 'SALESMAN' , 'MANAGER') ;

注：in不是一个区间，需跟具体的值。

select ename,sal from EMP where sal =800 or sal = 5000;

select ename,sal from EMP where sal in (800,5000);

not 可以取，主要使用在is或in中，is null | is not nul，in |not in

like称为模糊查询，支持%或下划线_匹配
       %匹配任意多个字符
       下划线：任意一个字符。
       （%是一个特殊的符号，_ 也是一个特殊符号）

找出名字中含有O的：select ename from emp where ename like '%O%';

找出名字以T结尾的：select ename from emp where ename like '%T';

找出名字以K开始的：select ename from emp where ename like 'K%';

找出第二个字每是A的：select ename from emp where ename like '_A%';

找出第三个字母是R的：select ename from emp where ename like '__R%';

注：若需要找了名字中含有下划线_的名字，可使用转义\，如'%\_%'

13.排序

select ename,sal from EMP order by sal; //默认为升序

select ename,sal from EMP order by sal desc;//指定降序

select ename,sal from EMP order by sal asc;//指定升序

13.1.按多个条件排序

先按工资升序排序，后按名字升序排序

select ename,sal from EMP order by sal asc,ename asc;//sal 在前，起主导，只有当sal相同时才考虑ename排序。

13.2.根据列数进行排序

select ename,sal from EMP order by 2; //根据第二列sal进行排序，不建设这样写，因为列可能发第一天变化。

13.3.综合案例

查找工资在1250与3000之间的员工信息，并按薪资降序排序

select ename,sal from EMP where sal between 1250 and 3000 order by sal desc;

14.数据处理函数

14.1.数据处理函数又称单行处理函数

单行处理函数的特点：一个输入对应一个输出；
相对的是：多行处理函数；
多行处理函数的特点：多个输入，对应一个输出，如sum。

多行：select sum(sal) from EMP;

14.2.常见单行处理函数

lower 转换小写
select lower(ename) from EMP;

upper 转换大写

substr 取子串//substr(被截取的字符串，起始下标，截取的长度）//起始下标从1开始
eg:取首字母为“A”的员工信息
①select ename from EMP where ename like 'A%';
②select ename from EMP where substr(ename,1,1) = 'A';

length 取长度
select length(ename) enamelength from EMP;

concat 将字符串拼接
eg:把员工的名字变成首字母大写
select concat(substr(ename,1,1),lower(substr(ename,2,length(ename)-1))) as bename from EMP;

trim 去除空格
select ename from EMP where ename=trim(' KING');

round 四舍五入//round(需要四舍五入的值,停留的小数点)

补充：select 后面可跟某个表的字段史，也可以跟字面量/字面值；
select 'nice' as 'good' from DEPT;

select round(123.578,2) from EMP;//保留2个小数
select round(123.578,-1) from EMP;//保留到十位数

rand() 生成随机数
select rand() from EMP;
select round(rand()*100,0) num from EMP;//生成100以内的随机数

ifnull 可以交null转换成一个具体值//ifnull(数据, 被当作哪个值）
ifnull是空处理函数，专心处理空的，在所有数据库当中，只要有null参与的数学运算，最终结果都为null。
select ename, sal + comm as salcomm from EMP;
eg：计算员工的年薪
select ename, (sal + ifnull(comm, 0))*12 as yearsalcomm from EMP;

str_to_date 将字符串转换成日期

date_format 格式化日期

fromat 设置千分位

case...when...then...when...then...else...end
eg:当员工的工作岗位为MANAGER时，工资上调10%，当为SALESMAN时工资上调30%，其它正常。
select ename, job, sal as oldsal, (case job when 'MANAGER' then sal * 1.1 when 'SALESMAN' then sal * 1.3 else sal end) as newsal from EMP;

15.分组函数（多行处理函数）

多行处理函数的特点：输入多行，最终输出一行。

5个：
        count 计数//select count(ename) from EMP;
        sum 求和//select sum(sal) from EMP;
        avg 平均值//select avg(sal) from EMP;
        max 最大值//select max(sal) from EMP;
        min 最小值//select min(sal) from EMP;

注：①分组函数在使用的时候必须先进行分组，然后才可使用。若没有对数据进行分组，整个表默认为一组。
②分组函数自动忽略NULL，不需要提前对NULL进行处理。//select count(comm) from EMP;
③分组函数中count(*)与count(具体字段)的区别：
count(具体字段)：表示统计该字段下所有不为NULL的元素的总数，同
count(*)：统计表的总行数
④分组函数不可直接使用在where子句中//报错：select ename, sal from EMP where sal > mix(sal);
⑤所有分组函数可以组合起来使用//select sum(sal), min(sal), max(sal), avg(sal) from EMP;

16.分组查询（非常重要）

语句：

        select
                ...
        from
                ...
        group by
                ...

可用用来：计算每个工作岗位的平均薪资/或是每个工作岗位的最高薪资等

16.1.关键字顺序（不可变）

使用having可对分完组后的数据进行过滤

        select
                ...
        from
                ...
        where
                ...
        group by
                ...
        order by
                ...

执行过程：from > where > group by > select > order by
所以，where在group by 之前，不可在where直接使用分组函数，但select在group by 之后，所以可在select直接使用分组函数。

16.2.工资之和

找出每个工作岗位的工资之和

思路：对工作岗位进行分组后，计算工资之和

select job, sum(sal) sumsal from EMP group by job;

select ename, job, sum(sal) sumsal from EMP group by job; //有些mysql上可执行，但无意义，在oracle的语法中会报错，oracle语法比mysql要严格。

重要结论：一条select语句当中，如果有group by 语句，select后面只能跟：参加分组的字段，以及分组函数，其它一律不能跟。

select job, sum(sal) sumsal from EMP group by job;

16.3.最高薪资

找出每个部门的最高薪资

select deptno, max(sal) from EMP group by deptno;

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

select deptno, job, max(sal) from EMP group by deptno, job;

16.4.使用having

使用having可对分完组后的数据进行过滤
having不可单独使用，不组替代where，必须与group by 进行使用。

找出每个部门最高薪资，并显示最高薪资大于3000的
①先找出每个部门最高薪资
select deptno, max(sal) from EMP group by deptno;
②显示最高薪资大于3000
select deptno, max(sal) from EMP group by deptno having max(sal) > 3000;
执行语句，效率不高，可先把大于3000先筛选出来后，再进行分组
select deptno,max(sal) from EMP where sal > 3000 group by deptno;

优化策略：where和having，优先选择having, where完成不了再选择having。

若是求每个部门的平均薪资，平均薪资不低于2500，不得不使用having
select deptno, avg(sal) from EMP group by deptno having avg(sal) >=2500;

16.5总结

select
              ...
from
              ...
where
              ...
group by
              ...
having
              ...
order by
              ...

以上关键字只能按上面顺序，不可颠倒
执行顺序：from > where > group by > having >select > order by

从某个表中查询数据，先经过where筛选出有价值的数据，对有价值数据进行分组后，使用having继续筛选，select查询出来最后排序输出。

找出每个岗位的平均薪资，并显示平均薪资大于1500的，除了MANAGER之后，要求按照平均薪资降序排序:
select job, avg(sal) from EMP where job <> 'MANAGER' group by job having avg(sal)> 1500 order by avg(sal) desc;

17.查询结果去除重复记录

使用去重关键字distinct,原表不会更改，只更改查询的结果。

①select distinct job from EMP;
②select ename, distinct job from EMP;//此编写错误，distinct只能出现在所有字段的最前方
eg:select distinct job,deptno from EMP;//distinct出现在两个字段之前，表示两个字段联合去重

18.连接查询(内外连接）

从一张表中单独查询，称为单表查询。
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。这种跨表查询，多张表联合起来查询数据，被称为连接查询。

18.1.连接查询的分类

根据表连接的方式分类：
       ① 内连接：
           等值连接
           非等值连接
           自连接

       ② 外连接：
           左外连接（左连接）
           右外连接（右连接）

③全连接

18.2.笛卡尔积现象

当两张表进行连接查询时，没有任何条件的限制会笛卡尔积现象。

查询每个员工所在部门名称：select ename,dname from emp, dept;
最终查询结果条数：是两张表条数的乘积14*5=56，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是一个数学现象。）

使用条件避免笛卡尔积现象
①select
       ename,dname
   from
       EMP, DEPT
   where
       emp.deptno = dept.deptno;

    ②select
       emp.ename,dept.dname
   from
       EMP, DEPT
   where
       emp.deptno = dept.deptno;

③select
       e.ename,d.dname
   from
       EMP e, DEPT d
   where
       e.deptno = d.deptno;//SQL92语法。

写法的优化中是③>②>①，表起别名，很重要，可提高效率。

最终查询的结果条数是14条，但是匹配的过程中，匹配的次数并不有减少还是56次。
因此通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数。

18.3内连接之等值连接。

案例：查询每个员工所在部门名称，显示员工名和部门名？
emp e和dept d表进行连接。条件是：e.deptno = d.deptno

SQL92语法：
   select
       e.ename,d.dname
   from
       EMP e, DEPT d
   where
       e.deptno = d.deptno;

   sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

SQL99语法：
   select
       e.ename,d.dname
   from
       EMP e
   [inner] join
       DEPT d
   on
       e.deptno = d.deptno;// 条件是等量关系，所以被称为等值连接。
//inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）

   sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

   SQL99语法：
       select
           ...
       from
           a
      [inner] join
           b
       on
           a和b的连接条件
       where
           筛选条件

18.4.内连接之非等值连接

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

select a.ename,a.sal, b.grade
from EMP a
join SALGRADE b
on sal between losal and hisal; 条件不是一个等量关系，称为非等值连接。

18.5内连接之自连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名？

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

注：自连接，就是把一个表看成两个表，输出提13条数据，没有领导的KING的数据。因内连接的特点：完成能够匹配上这个条件的数据查询出来。

内连接：A和B连接，AB两张表没有主次关系。平等的。

18.6.外连接

外连接（右外连接）：
select e.ename,d.dname
from EMP e
right join DEPT d
on e.deptno = d.deptno;

// outer是可以省略的，带着可读性强。
select e.ename,d.dname
from EMP e
right outer join DEPT d
on e.deptno = d.deptno;

right：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。

外连接（左外连接）：①
select e.ename,d.dname
from DEPT d
left join EMP e
on e.deptno = d.deptno;

带有right的是右外连接，又叫做右连接。
带有left的是左外连接，又叫做左连接。两者可互相转换

①中的语法等同于
select e.ename,d.dname
from EMP e
right join DEPT d
on e.deptno = d.deptno;

注：外连接的查询结果一定是 >= 内连接的查询结果的条数。

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名
select a.ename '员工名' , b.ename '领导名' from EMP a left outer join EMP b on a.mgr = b.empno;

18.7.多表联接

语法：
       select
           ...
       from
           a
       join
           b
       on
           a和b的连接条件
       join
           c
       on
           a和c的连接条件
       [right] join
           d
       on
           a和d的连接条件

注：一个SQL中内连接和外连接都可以混合。

案例：找出每个员工的部门名称以及工资等级，显示员工名，部门名，薪资，薪资等级
select e.ename, d.dname, e.sal, s.grade from EMP e join DEPT d on e.deptno = d.deptno join SALGRADE s on e.sal between s.losal and s.hisal;

案例：找出每个员工的部门名称以及工资等级，显示员工名，领导名，部门名，薪资，薪资等级
select e1.ename '员工名', e2.ename '领导名', d.dname, e1.sal, s.grade from EMP e1 left join EMP e2 on e1.mgr = e2.empno join DEPT d on e1.deptno = d.deptno join SALGRADE s on e1.sal between s.losal and s.hisal;

19.子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

19.1.子查询可出现的位置

select
        ...（select）
from
        ...（select）
where
        ...（select）

19.2.where子句中的子查询

案例：找出比最低工资高的员工名和工资。
select ename, sal from EMP where sal >(select min(sal) from EMP);

19.3.from子句中的子查询

注：from后面的子查询，可以将子查询的查询结果当做一个临时表。（技巧）

案例：找出每个岗位的平均的薪资等级
select e.job, avgsal, s.grade from (select job, avg(sal) avgsal from EMP group by job) e join SALGRADE s on e.avgsal between s.losal and s.hisal;

注：子查询中分组函数eg:avg(sal)尽量起别名，否侧条件判断时无法使用。

19.4.select后面出现的子查询（了解）

案例：找出每个员工的部门名称，要求显示员工名，部门名
select e.ename, (select d.dname from DEPT d where d.deptno = e.deptno) as dname from EMP e;

注：嵌套的select只能一条纪录，多于一条不可能行。
eg：select e.ename, (select dname from DEPT ) as dname from EMP e;

19.5.union合并查询结果集

案例：查询工作岗位是MANAGER和SALESMAN员工
select ename, job from EMP where job in ('MANAGER', 'SALESMAN');

select ename, job from EMP where job = 'MANAGER' union select ename, job from EMP where job = 'SALESMAN';

注：UNION 的效率更高。对于表连接，每连接一次新表则匹配次数满足笛卡尔积，成倍的翻…但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。

   a 连接 b 连接 c
   a 10条记录
   b 10条记录
   c 10条记录
   匹配次数是：1000

   a 连接 b一个结果：10 * 10 --> 100次
   a 连接 c一个结果：10 * 10 --> 100次
   使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）

union 的使用注意事项：
在进行结果合并时，要求两个结果集的列数相同
但eg:select ename, job from EMP where job = 'MANAGER' union select ename, sal from EMP where job = 'MANAGER' //在MYSQL可行，在oracle会报错
所以要求结果集合并时列和列的数据类型也要一致。

20.limit（重要）

limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。

20.1.limit语法

完整用法：limit startIndex, length
startIndex是起始下标，length是长度。
起始下标从0开始。

缺省用法：limit 5; 这是取前5.

eg:按薪资降序排序，取前5名员工。
select ename, sal from EMP order by sal desc limit 5;//或limit 0,5;

注：limit需要在order by 后执行。

eg:取出工资3-5名的员工
select ename, sal from EMP order by sal desc limit 2,3;//因下标从0开始，取3，需写2。

20.2.分页

每页显示3条记录
   第1页：limit 0,3       [0 1 2]
   第2页：limit 3,3       [3 4 5]
   第3页：limit 6,3       [6 7 8]
   第4页：limit 9,3       [9 10 11]

每页显示pageSize条记录
第pageNo页：limit (pageNo - 1) * pageSize , pageSize

21.关于DQL语句的大总结

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

   执行顺序：from > where > group by > having > select > order by > limit..

22.作业练习

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

select e.ename, t.deptno, t.sal from EMP e join (select deptno,max(sal) maxsal from EMP group by deptno) t on e.deptno = t.deptno and e.sal = t.maxsal;

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

select e.ename, t.deptno, e.sal from EMP e join (select deptno,avg(sal) avgsal from EMP group by deptno) t on e.deptno = t.deptno and e.sal > t.avgsal;

3.取得部门中(所有人的)平均的薪水等级,如下:

①select t.deptno,avg(t.grade) from (select deptno, grade from SALGRADE s right outer join EMP e 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 s.losal and s.hisal group by e.deptno;

4.不准用组函数(Max),取得最高薪水(给出两种解决方案)

①select sal from EMP order by sal desc limit 1;
②自连接：select sal from EMP where sal not in (select a.sal from EMP a join EMP b on a.sal < b.sal) ;

5.取得平均薪水最高的部门的部门编号(至少给出两种解决方案)

①select deptno from (select deptno,avg(sal) avgsal from EMP group by deptno) t where t.avgsal not in (select a.avgsal from (select deptno,avg(sal) avgsal from EMP group by deptno)a join (select deptno,avg(sal) avgsal from EMP group by deptno) b on a.avgsal < b.avgsal);//自连接

②select deptno from (select deptno,avg(sal) avgsal from EMP group by deptno) t order by t.avgsal desc limit 1;//排序取1

③select deptno from(select deptno,avg(sal) eavgsal from EMP group by deptno having eavgsal = (select max(t.avgsal) from (select avg(sal) avgsal from EMP group by deptno) t) ) t1; //使用having avg(sal)=max(sal)

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

select d.dname from (select deptno,avg(sal) avgsal from EMP group by deptno) t join DEPT d on t.deptno = d.deptno order by t.avgsal desc limit 1;

7.求平均薪水的等级最低的部门的部门名称

select d.dname from (select deptno, avg(sal) avgsal from EMP group by deptno) e join DEPT d on e.deptno = d.deptno join SALGRADE s on e.avgsal between s.losal and s.hisal order by s.grade limit 1;

8.取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名

select ename from EMP where sal > (select max(sal) maxsal from EMP where empno not in (select distinct mgr from EMP where mgr is not null) );

注：当not in 里有NULL值，但得出的结果必为空。

select empno,sal from EMP where empno not in (7902,7698,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, hiredate from EMP order by hiredate desc limit 5;

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

select s.grade, count(s.grade) numgrade from EMP e join SALGRADE s on e.sal between s.losal and s.hisal group by s.grade;

13.面试题

有 3 个表 S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1,找出没选过“黎明”老师的所有学生姓名。
2,列出 2 门以上(含 2 门)不及格学生姓名及平均成绩。
3,即学过 1 号课程又学过 2 号课所有学生的姓名。
请用标准 SQL 语言写出答案,方言也行(请说明是使用什么方言)。

1,①select SNAME from S where S.SNO not in (select distinct SC.SNO from SC join C on SC.CNO = C.CNO and C.CTEACHER = '黎明') ;

②select SNAME from S where S.SNO not in (select distinct SC.SNO from SC join C on SC.CNO = C.CNO where C.CTEACHER = '黎明') ;

2,SELECT S.SNAME,AVG(SC.SCGRADE) FROM S JOIN SC ON S.SNO = SC.SNO WHERE SC.SNO IN (SELECT SNO FROM SC WHERE SCGRADE <60 GROUP BY SNO HAVING COUNT(SNO) >= 2) GROUP BY S.SNAME;

3,SELECT S.SNAME FROM S JOIN (SELECT SNO FROM SC A WHERE CNO = '1') A ON A.SNO = S.SNO JOIN (SELECT SNO FROM SC A WHERE CNO = '2') B ON B.SNO = S.SNO;

14.列出所有员工及领导的姓名

SELECT E1.ENAME '员工名', E2.ENAME '领导名' FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO;

15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

SELECT E1.EMPNO, E1.ENAME, D.DNAME FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO JOIN DEPT D ON E1.DEPTNO = D.DEPTNO WHERE E1.HIREDATE < E2.HIREDATE;

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

SELECT D.DNAME,E.* FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO ;

17.列出至少有 5 个员工的所有部门

SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO) > 4);

18.列出薪金比"SMITH" 多的所有员工信息

SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');

19.列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

SELECT E.ENAME,D.DNAME,E.JOB, T.NPEOPLE FROM EMP E JOIN (SELECT DEPTNO,COUNT(DEPTNO) NPEOPLE FROM EMP GROUP BY DEPTNO) T ON E.DEPTNO = T.DEPTNO JOIN DEPT D ON D.DEPTNO = E.DEPTNO WHERE E.JOB = 'CLERK';

20.列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数,按照工作岗位分组求最小值。

SELECT JOB,COUNT(JOB) FROM EMP WHERE JOB IN (SELECT JOB FROM EMP GROUP BY JOB HAVING MIN(SAL) > 1500) GROUP BY JOB ORDER BY COUNT(JOB) LIMIT 1;

21.列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号

SELECT D.DNAME,E.ENAME FROM DEPT D JOIN EMP E ON D.DEPTNO = E.DEPTNO WHERE D.DNAME = 'SALES';

22.列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

SELECT E.ENAME '员工',D.DNAME,E2.ENAME '领导',S.GRADE FROM EMP E LEFT JOIN EMP E2 ON E.MGR = E2.EMPNO JOIN DEPT D ON E.DEPTNO = D.DEPTNO JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL WHERE E.SAL >(SELECT AVG(SAL) FROM EMP);

23. 列出与"SCOTT" 从事相同工作的所有员工及部门名称

SELECT E.ENAME,D.DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SCOTT') and ENAME <> 'SCOTT';

24.列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金

SELECT ENAME,DEPTNO, SAL FROM EMP WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = '30') AND DEPTNO <>'30';

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

SELECT E.ENAME,D.DNAME, E.SAL FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = '30') ;

26、列出在每个部门工作的员工数量, 平均工资和平均服务期限

SELECT D.DNAME, COUNT(E.ENAME) COUNTNAME, AVG(E.SAL) AVGSAL,AVG(TIMESTAMPDIFF(YEAR,E.HIREDATE,NOW())) AVGTIME FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO GROUP BY D.DNAME;

27、列出所有员工的姓名、部门名称和工资。

SELECT E.ENAME, D.DNAME, E.SAL FROM EMP E JOIN DEPT D ON E.DEPTNO =D.DEPTNO;

28、列出所有部门的详细信息和人数

SELECT D.*,T.PEL FROM DEPT D LEFT JOIN (SELECT DEPTNO,COUNT(ENAME) PEL FROM EMP GROUP BY DEPTNO) T ON D.DEPTNO = T.DEPTNO;

29、列出各种工作的最低工资及从事此工作的雇员姓名

SELECT E.JOB,E.ENAME,E.SAL FROM EMP E JOIN (SELECT JOB,MIN(SAL) MINSAL FROM EMP GROUP BY JOB) T ON E.JOB=T.JOB AND E.SAL = T. MINSAL;

30、列出各个部门的 MANAGER( 领导) 的最低薪金

SELECT DEPTNO,MIN(SAL) FROM EMP WHERE JOB = 'MANAGER' GROUP BY DEPTNO;

31、列出所有员工的年工资, 按年薪从低到高排序

SELECT ENAME,SAL * 12 YEARSAL FROM EMP ORDER BY YEARSAL ASE;

32、求出员工领导的薪水超过3000的员工名称与领导

SELECT E1.ENAME '员工',E2.ENAME '领导',E2.SAL '领导工资' FROM EMP E1 JOIN EMP E2 ON E1.MGR = E2.EMPNO WHERE E2.EMPNO IN (SELECT EMPNO FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP) AND SAL > 3000);

34.求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数

SELECT D.DNAME,SUM(E.SAL) SUMSAL, COUNT(E.DEPTNO) COUNTPET FROM EMP E RIGHT JOIN (SELECT DEPTNO,DNAME FROM DEPT WHERE DNAME LIKE '%S%' ) D ON E.DEPTNO = D.DEPTNO GROUP BY D.DNAME;

23.表的创建

23.1.建表的语法格式

建表属于DDL语句，DDL包括：create、drop、alter。

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

   表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。
   字段名：见名知意。
   表名和字段名都属于标识符。

23.2.数据类型

常见的数据类型：

varchar(最长255)
           可变长度的字符串
           比较智能，节省空间。
           会根据实际的数据长度动态分配空间。

优点：节省空间
缺点：需要动态分配空间，速度慢。

char(最长255)
           定长字符串
           不管实际的数据长度是多少。
           分配固定长度的空间去存储数据。
           使用不恰当的时候，可能会导致空间的浪费。

优点：不需要动态分配空间，速度快。
缺点：使用不当可能会导致空间的浪费。

           varchar和char的选择：
                像性别是固定长度的字符串，所以选择char。
                而每一个人的名字长度不同，所以选择varchar。

int(最长11)：数字中的整数型。等同于java的int。

bigint：数字中的长整型。等同于java中的long。

float：单精度浮点型数据

double：双精度浮点型数据

date：短日期类型

datetime：长日期类型

clob
           字符大对象
           最多可以存储4G的字符串。
           比如：存储一篇文章，存储一个说明。
           超过255个字符的都要采用CLOB字符大对象来存储。
           Character Large OBject:CLOB

blob

23.3.创建学生表

学号、姓名、年龄、性别、邮箱地址
   create table t_student(
       no int,
       name varchar(32),
       sex char(1) [default 默认值],
       age int(3),
       email varchar(255)
   );

删除表：
drop table t_student; // 当这张表不存在的时候会报错！

// 如果这张表存在的话，删除
drop table if exists t_student;

23.4.快速创建表(了解）

create table EMP2 as select * from EMP;//as可进行删除，创建表结构并插入数据，但没有索引
create table EMP3 like EMP;//只创建表结构，不插入数据，有索引

原理：
       将一个查询结果当做一张表新建！！！！！
       这个可以完成表的快速复制！！！！
       表创建出来，同时表中的数据也存在了！！

24.插入数据insert (DML)

语法格式：
insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);

注：字名和值需一一对应：数量要对应、数据类型要对应。如①②
insert语句中的“字段名”可省略，但等于都写上了！所以值也要都写上！如③

①insert into t_student(no,name,sex,email) values(1, 'Toy','m','toy@39.com');
②insert into t_student(no,name,email) values(2,'baby','baby@89.com');
③insert into t_student values(3,'tager','m','tager@89.com');

insert into t_student(no) values(4);//insert语句但凡是执行成功了，必然会多一条记录。没有给其它字段指定值的话，默认值是NULL。

23.1 insert插入日期

数字格式化：format

格式化数字：format(数字, '格式')
select ename,format(sal, '$999,999') as sal from emp;

str_to_date：将字符串varchar类型转换成date类型
date_format：将date类型转换成具有一定格式的varchar字符串类型。

drop tabale if exists t_user;
create table t_user(
        id int(3),
        name varchar(25),
        birth date //生日可用date日期类型，也可用char类型
);

23.1.1.str_to_date函数可以将字符串转换成日期类型date
语法格式：
str_to_date('字符串日期', '日期格式')

mysql的日期格式：
%Y 年、 %m 月、 %d 日、%h 时、%i 分、%s 秒

插入数据：
insert into t_user(id,name,birth) values(1,'baby','1995-12-01');
insert into t_user(id,name,birth) values(2, 'toy', str_to_date( '01-05-2022','%d-%m-%Y' ));
insert into t_user(id,name,birth) values(3, 'TRE', str_to_date( '01/05/2022','%d/%m/%Y' ));

注：str_to_date通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date。但所提供的日期为%Y-%m-%d格式，可不用使用该函数，因系统会自动进行转换。

23.1.2.date_format函数可以将日期类型转换成特定格式的字符串

select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;

注：date_format通常使用在select查询语句中，设置展示的日期格式。

select * from t_user;//所查询的日期进行了默认的日期格式化，自动将数据库中的date类型转换成varchar类型，并且采用的格式是mysql默认的日期格式：'%Y-%m-%d'

24.2.date与datetime的区别

date是短日期：只包括年月日信息。
datetime是长日期：包括年月日时分秒信息。

drop table if exists t_user;
   create table t_user(
       id int,
       name varchar(32),
       birth date,
       create_time datetime
   );

mysql短日期默认格式：%Y-%m-%d
mysql长日期默认格式：%Y-%m-%d %h:%i:%s

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');
insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

在mysql中获取当前时间，可使用now() 函数，带有：时分秒信息，是datetime类型的。

24.3.insert语句接入多条记录

语法：insert into t_user(字段名1,字段名2) values(),(),(),();

insert into t_user(id, name, birth, create_time) values
(1,'te','1995-12-02',now()),
(2,'ke','1995-06-02',now()),
(3,'be','1995-11-02',now());

24.4.把查询结果插入表中（了解）

insert into t_user select * from t_user where id = 2;//表名后面不可使用as

25.修改update（DML）

语法格式：
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;

注意：没有条件限制会导致所有数据全部更新。

update t_user set name = 'tena', birth = '1995-05-21' where id = 2;

更新所有：update t_user set name = '12'；

26.删除数据 delete （DML）

语法格式：
delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！

delete from t_user where id = 2;

insert into t_user(id) values(2);

delete from t_user; // 删除所有！

27.快速删除表中数据truncate（重要）

使用delete进行删除，是删除数据的原理（DML），较慢，空间不会被释放，优化：可回滚，恢复数据

truncate是对表一次截断，为物理删除，不支持回滚，优化：删除较慢。

truncate table EMP2;//语法：truncate table 表名；只删除数据，不删除表

28.表结构的增删改(DDL)

对表结构的修改：添加字段，删除字段，修改字段

对表结构的修改需使用：alter

在实际的开发中，需求一旦确定之后，表一旦设计好之后，很少的进行表结构的修改。因为开发进行中的时候，修改表结构，成本比较高。

增加列：ALTER TABLE table_name ADD column_name column_definition;
                alter table t_user add sex char(1);
删除列：ALTER TABLE table_name DROP column_name;
                alter table t_user drop sex;
修改列定义：ALTER TABLE table_name MODIFY column_name new_column_definition;
                alter table t_user modify name char(10);
重命名列：ALTER TABLE table_name CHANGE old_column_name new_column_name column_definition;
                alter table t_user change name newname varchar(255);
增加索引：ALTER TABLE table_name ADD INDEX index_name (column_name);
删除索引：ALTER TABLE table_name DROP INDEX index_name;

29.约束

非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）

29.1.非空约束not null

非空约束not null约束的字段不能为NULL。
   drop table if exists t_vip;
   create table t_vip(
       id int,
       name varchar(255) not null // not null只有列级约束，没有表级约束！
   );
   insert into t_vip(id,name) values(1,'zhangsan');
   insert into t_vip(id,name) values(2,'lisi');

insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

29.2.唯一性约束:unique

唯一性约束unique约束的字段不能重复，但是可以为NULL。
   drop table if exists t_vip;
   create table t_vip(
       id int,
       name varchar(255) unique,//列级约束
       email varchar(255)
   );
   insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
   insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
   insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
   select * from t_vip;

insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

insert into t_vip(id) values(4);
insert into t_vip(id) values(5);//name字段虽然被unique约束了，但是可以为NULL。

表级约束：需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) // 约束没有添加在列的后面，这种约束被称为wd 。
);

29.3.unique 和not null的联合

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）

drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null unique
);

desc t_vip;

29.4.主键约束（primary key，简称PK）（非常重要）

主键约束的相关术语
       主键约束：就是一种约束。
       主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
       主键值：主键字段中的每一个值都叫做：主键值。

主键的用处：
       主键值是每一行记录的唯一标识。
       主键值是每一行记录的身份证号！！！

记住：任何一张表都应该有主键，没有主键，表无效！！

主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

表添加主键约束：

drop table if exists t_vip;
// 1个字段做主键，叫做：单一主键
create table t_vip(
id int primary key, //列级约束
name varchar(255)
);

drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
primary key(id) // 表级约束
);

表级约束主要是给多个字段联合起来添加约束
drop table if exists t_vip;
// id和name联合起来做主键：复合主键！！！！
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);

在实质开放中，不建议使用：复合主键。建议使用单一主键！
因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。复合主键比较复杂，不建议使用！！！

在一个表中主键约束只有写一个：|
drop table if exists t_vip;
create table t_vip(
id int primary key,
name varchar(255) primary key
);
报错：ERROR 1068 (42000): Multiple primary key defined

主键建议使用：int、bigint、char等类型。
不建议使用：varchar为做主机，一般为数字，定长的。

主键除了：单一主键和复合主键之外，还可以分为：
自然主键：主键值是一个自然数，和业务没关系。
业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

在实际开发中使用自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。
业务主键缺点：主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以业务主键不建议使用，尽量使用自然主键。

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值（auto_increment）：drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！
name varchar(255)
);

insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;

29.5.外键约束（foreign key，简称FK）（非常重要）

    外键约束涉及到的相关术语：
       外键约束：一种约束（foreign key）
       外键字段：该字段上添加了外键约束
       外键值：外键字段当中的每一个值。

业务背景：
请设计数据库表，来描述“班级和学生”的信息？

       第一种方案：班级和学生存储在一张表中
       t_student
       no(pk)           name       classno           classname
       ----------------------------------------------------------------------------------
       1                   jack                   100           北京市大兴区亦庄镇第二中学高三1班
       2                   lucy                   100           北京市大兴区亦庄镇第二中学高三1班
       3                   lilei                   100           北京市大兴区亦庄镇第二中学高三1班
       4                   hanmeimei   100           北京市大兴区亦庄镇第二中学高三1班
       5                   zhangsan       101           北京市大兴区亦庄镇第二中学高三2班
       6                   lisi                  101           北京市大兴区亦庄镇第二中学高三2班
       7                   wangwu      101           北京市大兴区亦庄镇第二中学高三2班
       8                   zhaoliu      101           北京市大兴区亦庄镇第二中学高三2班
       分析以上方案的缺点：数据冗余，空间浪费。

       第二种方案：班级一张表、学生一张表

       t_class 班级表
       classno(pk)           classname
       ------------------------------------------------------
       100                   北京市大兴区亦庄镇第二中学高三1班
       101                   北京市大兴区亦庄镇第二中学高三1班

       t_student 学生表
       no(pk)           name               cno(FK引用t_class这张表的classno)
       ----------------------------------------------------------------
       1                   jack                        100
       2                   lucy                       100
       3                   lilei                       100
       4                   hanmeimei       100
       5                   zhangsan           101
       6                   lisi                          101
       7                   wangwu              101
       8                   zhaoliu                 101

语法：

ALTER TABLE 子表
ADD CONSTRAINT 外键名
FOREIGN KEY (子表列名) REFERENCES 父表 (父表列名);

当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。
所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。
那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

注意：
t_class是父表
t_student是子表

删除表的顺序：先删子，再删父。

创建表的顺序：先创建父，再创建子。

删除数据的顺序：先删子，再删父。

插入数据的顺序：先插入父，再插入子。

注：子表中的外键引用的父表中的某个字段，被引用的这个字段不必须是主键，但至少具有unique约束。且外键值可以为NULL。

30.存储引擎（了解）

存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）
存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。

30.1.添加/指定“存储引擎”

show create table t_student;//查看表

| t_student | CREATE TABLE `t_student` (
`no` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`sex` char(1) DEFAULT 'f',
`email` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

建表的时候可以在最后小括号的")"的右边使用：
ENGINE来指定存储引擎，InnoDB 为默认的。
CHARSET来指定这张表的字符编码方式。

30.2.mysql支持存储引擎

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

show engines \G //查看当前mysql支持的版本

mysql总体支持9大存储引擎FEDERATED、MRG_MYISAM、 MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、InnoDB、PERFORMANCE_SCHEMA，版本不同支持情况不同。

30.3.mysql常用的存储引擎

MyISAM存储引擎：其特征是使用三个文件表示每个表
        格式文件 — 存储表结构的定义（mytable.frm）
        数据文件 — 存储表行的内容（mytable.MYD）
        索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。
       可被转换为压缩、只读表来节省空间

注：对于一张表而言，或要是主键或者有unique约束的字段上会自动创建索引。

InnoDB存储引擎：是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
支持事务，支持数据库崩溃后自动恢复机制。
存储引擎最主要的特点是：非常安全。

特征：
        每个 InnoDB 表在数据库目录中以.frm 格式文件表示
        InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）– 提供一组用来记录事务性活动的日志文件
        用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
        提供全 ACID 兼容
        在 MySQL 服务器崩溃后提供自动恢复
        多版本（MVCC）和行级锁定
        支持外键及引用的完整性，包括级联删除和更新

其最大特征是支持事务：以保证数据的安全。效率不是很高，也不能压缩，不转换为只读，不能很好的节省存储空间。

MEMORY存储引擎：在MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定。此2特点使得MEMORY存储引擎非常快。

MEMORY 存储引擎管理的表的特征：
       – 在数据库目录内，每个表均以.frm 格式的文件表示。
       – 表数据及索引被存储在内存中。（目的就是快，查询快！）
       – 表级锁机制。
       – 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

31.事务（非常重点，必须理解掌握）

一个事务为一个完整的业务逻辑，是最小的工作单元，不可再分。

一个完整的业务逻辑：相当于从A账户向B账户中转账10000，将A账户的钱减去10000（update语句），将B账户的钱加上10000（update语句）。
此操作是最小工作单元，要么同时成功，要么同时失败，不可再分，两个update语句必做同时成功或失败，才能保证钱是正确的。

31.1.DML语句与事务

只有DML语句（insert、delete、update）才涉及到事务，其它语句与事务无关。

数据安全第一位！！！

如果只有一条DTS语句，是不涉及事务的，必须是多条的DML语句才需要事务的存在。

所以本质上：一个事务是多条DML语句同时成功，或同时失败。

31.2.事务的成功与失败

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件。

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务：
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
提交事务标志着，事务的结束。并且是一种全部成功的结束。

回滚事务：
将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
回滚事务标志着，事务的结束。并且是一种全部失败的结束。

31.3.提交事务与回滚事务

提交事务：commit;
回滚事务：rollback;//回滚永远都是只能回滚到上一次的提交点。

事务对应的单词是：transaction

mysql默认情况下是支持自动提交事务，则是执行一条数DML语句，提交一次。

自动提交，实质上不符合开发习惯，因一个业务通常是需要多条DML语句共同执行才能完成，为保证数据的安全，必做要求同时成功后再提交，不可执行一条提交一条。

关闭自动提交机制：
执行命令：start transaction; //开始事务

truncate table t_student;
select * from t_student;
start transaction;
insert into t_student values(1,'abc','f','abc@123.com');
insert into t_student values(2,'afc','f','afc@123.com');
select * from t_student;//可查看数据已插入
rollback;
select * from t_student;//rollback回滚到上一次提交点

______________________________________________
start transaction;
insert into t_student values(1,'abc','f','abc@123.com');
insert into t_student values(2,'afc','f','afc@123.com');
select * from t_student;//可查看数据已插入
commit;
select * from t_student;//commit成功写入数据

31.4.事务的4个特性

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

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

I：隔离性--A事务和B事务之间具有一定的隔离。教室A和教室B之间有一道墙，这道墙就是隔离性。

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

31.5.事务的隔离性

事务与事务之间有4个级别

读未提交：read uncommitted //最低隔离级别，没有提交就可以读取
        事务A可以读取事务B未提交的数据
        存在的问题：脏读现象，称读到了脏数据
        隔离级别一般理论上，是二档起步（读已提交）的。

读已提交：read committed //提交之后才能读到
        事务A只能读取到事务B提交之后的数据
        解决：脏读的现象。
        存在的问题：不可重复读取数据，即在事务开始后，第一次读的数据为3条，当前事务未结束，可能第二次再读取，得到的数据为4条，3不等于4称为不重复读取。
        此隔离级别，是比较真实的数据，每一次读的数据是绝对真实。
        Oracle数据库存默认的隔离级别是：read committed

可重复读：repeatable read //提交之后也读不到，永远读取的都是刚开启事务时的数据。
        事务A开启之后，不管是多久，每一次都在事务A中读取的数据都是一致的，即使事务B将数据修改后并提交，事务A都不会发生变化，为可重复读。
        解决：不可重复读取数据的瓿
        存在的问题：可能会出现幻影读，就每一次读取的数据都是幻象，不够真实。
        mysql数据库存默认的隔离级别是：repeatable read

31.6.验证隔离级别

查看隔离级别：SELECT @@tx_isolation

验证：read uncommited
set global transaction isolation

事务A                                               事务B
--------------------------------------------------------------------------------
use test;
                                                   use test;
start transaction;
select * from t_user;
                                                   start transaction;
                                                   insert into t_user(newname) values('zhangsan');
select * from t_user;

验证：read commited
set global transaction isolation level read committed;
事务A                                               事务B
--------------------------------------------------------------------------------
use test;
                                                   use test;
start transaction;
                                                   start transaction;
select * from t_user;
                                                   insert into t_user(newname) values('zhangsan');
select * from t_user;
                                                   commit;
select * from t_user;

验证：repeatable read
set global transaction isolation level repeatable read;
事务A                                               事务B
--------------------------------------------------------------------------------
use test;
                                                   use test;
start transaction;
                                                   start transaction;
select * from t_user;
                                                   insert into t_user(newname) values('zhangsan');
                                                   insert into t_user(newname) values('wangwu');
                                                   commit;
select * from t_user;

验证：serializable
set global transaction isolation level serializable;
事务A                                                                                                事务B
--------------------------------------------------------------------------------
use test;
                                                                                                            use test;
start transaction;
                                                                                                            start transaction;
select * from t_user;
insert into t_user(newname) values('zhangsan');
                                                                                                           select * from t_user;