MySQL数据库

目录​​​​​​​

1:数据库概述及数据准备​​​​​​​

1.1:什么是数据库?什么是数据库管理系统?什么是SQL?他们之间的关系是什么?

1.2:在Windows操作系统中,怎么使用命令来启动和关闭MySQL服务呢?

1.3:mysql常用命令

1.4:数据库中最基本的元素是表:table

1.5:SQL的分类

1.6:导入演示数据

1.7:表结构描述

2:常用命令

2.1:查看mysql数据库的版本号

2.2:查看当前使用的是哪个数据库?

3:简单的查询

3.1:如何查询一个字段?

3.2:如何查询俩个或多个字段?

3.3:如何给查询的列起别名?

3.4:计算员工的年薪

4:条件查询

4.1:条件查询:不是将表中所有数据都查出来,是查询出来符合条件

4.2:= 等于

4.3:<> 或!= 不等于

4.4:< 小于

4.5:<= 小于等于

4.6:> 大于

4.7:>= 大于等于

4.8:between … and …两个值之间, 等同于 >= and <=

4.9:is null 为空(is not null 不为空)

4.10:and 并且,or 或者

4.11:in 包含:相当于多个 or (not in 表示不在这几个值当中的数据)

4.12:like 称为模糊查询,支持%(匹配任意多个字符)或下划线(匹配任意一个字符)匹配【%是一个特殊的符号,_ 也是一个特殊符号】

5:排序数据

5.1:查询所有员工薪资,排序?

5.2:指定降序

5.3:指定升序

5.4:可以两个字段排序吗?或者可以多个字段排序吗?

5.5:根据字段的位置也可以排序

5.6:找出工资在1250到3000之间的员工信息,要求按照薪资降序排列

6:数据处理函数

6.1:数据处理函数又被称为单行处理函数

6.2:单行处理函数常见的有哪些?

7:分组函数

7.1:分组函数(多行处理函数)

8:分组查询【重点】

8.1:什么是分组查询?

8.2:如何进行分组查询?

8.3:将之前的关键字全部组合在一起,来看一下他们的执行顺序?

8.4:使用having可以对分完组之后的数据进一步过滤;having不能单独使用,having不能代替where,having必须和group by联合使用

8.5:总结

8.6:把查询结果去除重复记录

9:连接查询【重点】

9.1:什么是连接查询?

9.2:连接查询的分类?

9.3:当两张表进行连接查询时,没有任何条件的限制会发生什么现象?

9.4:怎么避免笛卡尔积现象?

9.5:内连接之等值连接

9.6:内连接之非等值连接

9.7:内连接之自连接

9.8:外连接(右外连接又叫右连接)

9.9:外连接(左外连接又叫左连接)

9.10:三张表、四张表怎么连接?

10:子查询

10.1:什么是子查询?

10.2:子查询都可以出现在哪里呢?

10.3:where子句中出现的子查询?

10.4:from子句的子查询

10.5:select后面的出现的子查询(了解)

11:union(合并查询结果集)

11.1:union效率高

12:limit的使用【重点】

12.1:limit作用

12.2:limit怎么用呢?

12.3:注意:MySQL当中limit在order by之后执行

12.4:按照薪资降序,取出排名在三到五名的员工?

12.5:分页

12.6:DQL大总结

13:表的创建(建表)

13.1:建表的语法格式(建表属于DDL语句,DDL包括create,drop,alter)

13.2:关于MySQL中的数据类型

13.3:创建一个学生表?

13.4:删除表

13.5:插入数据(insert)DML

13.6:指定默认值

13.7:insert插入日期

13.8:date和datetime两个类型的区别?

13.9:在MySQL中怎么获取系统当前时间?

13.10:修改update(DML)

13.11:删除数据delete(DML)

13.12:insert语句可以一次插入多条记录

13.13:快速创建表(表的复制)

13.14:将查询结果插入到一张表当中?(insert)

13.15:快速删除表中的数据

13.16:约束(constraint)【重点】

14:存储引擎

14.1:什么是存储引擎?有什么用呢?

14.2:怎么给表添加/指定“存储引擎”呢?

14.3:怎么查看mysql支持哪些存储引擎呢?

14.4:查看mysql版本?

14.5:关于mysql常用的存储引擎?

15:事务【重点】

15.1:什么是事务?

15.2:事务是如何做到多条DML语句同时成功同时失败的呢?

15.3:怎么提交事务?怎么回滚事务?

15.4:事物的四个特性

15.5:事务的隔离性

16:索引

16.1:什么是索引?

16.2:实现原理

16.3:添加索引的条件

16.4:索引的删除和创建

16.5:在mysql当中,怎么查看一个SQL语句是否使用了索引进行检索?

16.6:索引的失效

16.7:索引是各种数据库进行优化的重要手段,优化的时候考虑的因素就是索引

17:视图(view)

17.1:什么是视图?

17.2:创建和删除

17.3:用途

17.4:视图对象在实际开发中到底有什么作用?

18:DAB命令

18.1:DAB常用命令

19:数据库设计的三范式

19.1:什么是数据库设计范式?

19.2:总结表的设计


1:数据库概述及数据准备​​​​​​​
1.1:什么是数据库?什么是数据库管理系统?什么是SQL?他们之间的关系是什么?

        ①数据库(DataBase 简称DB):按照指定格式存储数据的一些集合

                顾名思义:存储数据的仓库,实际上就是一堆文件。这些文件中存储了具有特定格式的数据

        ②数据库管理系统(DataBaseManagementSystem 简称DBMS):专门用来管理数据库中的数据,数据库管理系统可以对数据库中的数据进行增删改查

                常见的数据库管理系统

                          MySQL、Oracle、MS Sqlserver、DB2、Sybase等

        ③SQL:结构化查询语言

                SQL一般发音为sequel

                全称:Structured Query Language

                程序员需要学习SQL语句,通过编写SQL语句,然后DBMS负责执行SQL语句,最终来完成数据库中数据的增删改查操作

                SQL是一套标准,程序员主要学习的就是SQL语句,这个SQL在MySQL中可以使用,在DB2中也可以使用

        ④三者之间的关系

                 DBMS--执行-->SQL--操作-->DB

                先安装数据库管理系统MySQL,然后学习SQL怎么写,编写SQL语句后,DBMS对SQL语句进行执行,最终来完成数据库的数据管理

1.2:在Windows操作系统中,怎么使用命令来启动和关闭MySQL服务呢?

        语法:

              net stop 服务名称:关闭

              net start 服务名称:启动

        其它服务的启停都可以采用以上的命令

1.3:mysql常用命令

            ①退出MySQL:exit

            ②查看MySQL中有哪些数据库?

                    show databases;(注意以分号结尾,分号是英文的分号)

mysql> show databases;

+--------------------+

| Database ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

+--------------------+

| information_schema |

| mysql ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

| performance_schema |

| sakila ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

| sys ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

| world ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

+--------------------+

        ③怎么选择使用某个数据库呢?

                 mysql> use sys;

                表示正在使用一个名叫sys的数据库

        ④怎么创建数据库?

                mysql> create database bjpowernode;

                mysql> show databases;

+--------------------+

| Database ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

+--------------------+

| bjpowernode ᅠᅠᅠᅠᅠᅠᅠ|

| information_schema |

| mysql ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

| performance_schema |

| sakila ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

| sys ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

| world ᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠᅠ|

+--------------------+

      ⑤怎么查看数据库下有哪些表?

                 mysql> show tables;

1.4:数据库中最基本的元素是表:table

         什么是表table?为什么用表来存储数据呢?

姓名 性别 年龄 列(字段)
张三 20 行(记录)
李四 21 行(记录)
王五 22 行(记录)

        数据库当中是以表格的形式表示数据的(因为表格比较直观)

        任何一张表都有行和列:

            行(row):被称为数据/记录

            列(column):被称为字段(每一个字段都有:字段名、数据类型、约束等属性)

1.5:SQL的分类

        DQL:

                数据查询语言(凡是带有select关键字的都是查询语句)

                select...

        DML:

                数据操作语言(凡是对表中的数据进行增删改的都是DML)

                insert  增

                delete 删

                update 改

                这个主要是操作表中的数据data

        DDL:

                数据定义语言

                凡是带有create、drop、alter的都是DDL。

                DDL主要操作的是表的结构,不是表中的数据。

                create:新建,等同于增

                drop:删除

                alter:修改

                这个增删改和DML不同,这个主要是对表结构进行操作

        TCL:

                事务控制语言

                包括:

                        事务提交:commit;

                        事务回滚:rollback;

        DCL:

                数据控制语言

                例如:授权grant、撤销授权revoke...

1.6:导入演示数据

        怎么查看表中的数据?

            select * from 表名;  //“*”代表所有

1.7:表结构描述

        不看表中的数据,只看表的结构,命令为什么?

                desc tabl_name;

2:常用命令
2.1:查看mysql数据库的版本号

          mysql> select version();

+-----------+

| version() |

+-----------+

| 8.0.19   |

+-----------+
2.2:查看当前使用的是哪个数据库?

     mysql> select database();

+------------+
| database() |
+------------+
| NULL     |
+------------+

mysql> show
    -> databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| bjpowernode        |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+

注:不见分号不执行;分号表示结束!
     mysql> show
    ->
    ->
    ->
    ->
    ->
    -> c

mysql>

c用来终止一条命令的输入
3:简单的查询
3.1:如何查询一个字段?

     select 字段名 from 表名;

             注意:select和from都是关键字;

                        字段名和表名都是标识符;

             强调:

                        对于sql语句来说,是通用的

                        所有的sql语句以“;”结尾

                        sql语句不区分大小写

3.2:如何查询俩个或多个字段?

        俩个字段:

                  使用逗号“,”隔开

                  select 字段名, 字段名 from 表名;

        多个字段:

                  第一种方式:可以把每个字段都写上

                  第二种方式:可以使用“*”号

                          这种方式的缺点:效率低

                                                      可读性差,在实际开发中不建议

3.3:如何给查询的列起别名?

        mysql> select  dname as deptname from dept;

                使用as关键字起别名

                        注:只是将显示的查询结果列名显示为deptname,原列表还是叫dname

                        记住:select语句是永远都不会进行修改操作的(只负责查询)

                as关键字可以省略

       mysql> select dname deptname  from dept;

                起别名时,别名里面有空格,将其用单引号或双引号括起来就OK了

               在所有的数据库中,字符串统一使用单引号括起来是标准,双引号在oracle数据库中用不了,但在mysql中可以使用

3.4:计算员工的年薪

        mysql> select ename,sal*12 from emp;   //结论:字段可以使用数学表达式  

        mysql> select ename,sal*12 as yearsal from emp;   //起别名(别名若为中文,用单引号括起来)

4:条件查询
4.1:条件查询:不是将表中所有数据都查出来,是查询出来符合条件
 语法格式:

       select
            字段1,字段2...
       from
            表名
       where
            条件;
4.2:= 等于

查询薪资等于800的员工姓名和编号?

          select empno,ename from emp where sal = 800;

查询SMITH的编号和薪资?

          select empno,sal from emp where ename = 'SMITH';

4.3:<> 或!= 不等于

查询薪资不等于800的员工姓名和编号?

          select empno,ename from emp where sal != 800;

          select empno,ename from emp where sal <> 800;   

4.4:< 小于

查询薪资小于2000的员工姓名和编号?

          mysql> select empno,ename,sal from emp where sal < 2000; 

  +-------+--------+---------+

  | empno | ename  | sal     |

  +-------+--------+---------+

  |  7369 | SMITH  |  800.00 |

  |  7499 | ALLEN  | 1600.00 |

  |  7521 | WARD   | 1250.00 |

  +-------+--------+---------+
4.5:<= 小于等于

查询薪资小于等于3000的员工姓名和编号?

          select empno,ename,sal from emp where sal <= 3000;

4.6:> 大于

查询薪资大于3000的员工姓名和编号?

          select empno,ename,sal from emp where sal > 3000;

4.7:>= 大于等于

查询薪资大于等于3000的员工姓名和编号?

  select empno,ename,sal from emp where sal >= 3000;

4.8:between … and …两个值之间, 等同于 >= and <=

查询薪资在2450和3000之间的员工信息?包括2450和3000

  第一种方式:>= and <=   

 select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;

    +-------+-------+---------+

    | empno | ename | sal     |

    +-------+-------+---------+

    |  7566 | JONES | 2975.00 |

    |  7698 | BLAKE | 2850.00 |

    |  7782 | CLARK | 2450.00 |

    |  7788 | SCOTT | 3000.00 |

    |  7902 | FORD  | 3000.00 |

    +-------+-------+---------+

  第二种方式:between … and …

    select

      empno,ename,sal

    from

      emp

    where

      sal between 2450 and 3000;

    注意:

      使用between and的时候,必须遵循左小右大。

      between and是闭区间,包括两端的值。

4.9:is null 为空(is not null 不为空)

注意:在数据库当中null不能使用等号进行衡量。(条件=null ,error)需要使用is null,因为数据库中的null代表什么也没有,它不是一个值,所以不能使用等号衡量。

4.10:and 并且,or 或者

and和or同时出现的话,有优先级问题吗?

        and和or同时出现,and优先级较高。如果想让or先执行,需要加“小括号”,以后在开发中,如果不确定优先级,就加小括号就行了。

4.11:in 包含:相当于多个 or (not in 表示不在这几个值当中的数据)

   注意:in不是一个区间,in后面跟的是具体的值

              not 可以取非,主要用在 is 或 in 中

4.12:like 称为模糊查询,支持%(匹配任意多个字符)或下划线(匹配任意一个字符)匹配【%是一个特殊的符号,_ 也是一个特殊符号】

找出名字以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%';

找出名字中有下划线的

           ‘%_%’  // ””表示转义字符

5:排序数据
5.1:查询所有员工薪资,排序?
select

      ename,sal

   from

      emp

   order by

      sal;   //默认升序
5.2:指定降序
 select

      ename,sal

   from

      emp

   order by

      sal desc;  
5.3:指定升序
select

      ename,sal

   from

      emp

   order by

      sal asc;
5.4:可以两个字段排序吗?或者可以多个字段排序吗?

查询员工名字和薪资,要求按照薪资升序,若薪资一样,再按名字升序排列     

 select

        ename,sal

      from

        emp

      order by

        sal asc,ename asc;  //sal在前,起主导;只有sal相等时,才会考虑启用ename排序
5.5:根据字段的位置也可以排序
 select

      ename,sal

   from

      emp

   order by

       2;     //2表示第二列,第二列是sal,按照查询结果的第二列排序
5.6:找出工资在1250到3000之间的员工信息,要求按照薪资降序排列

        select

          ename,sal

        from

          Emp

        where

          sal between 1250 and 3000

        order by

          sal desc;  (排序总是在最后执行)

6:数据处理函数
6.1:数据处理函数又被称为单行处理函数

单行处理函数(特点:一个输入对应一个输出)

多行处理函数(特点:多个输入对应一个输出)

6.2:单行处理函数常见的有哪些?
lower 转换小写

    mysql> select lower(ename) as ename from emp;

upper 转换大写

    mysql> select upper(name) as name from t_student;

substr 取子串(substr(被截取的字符串, 起始下标,截取的长度))

    mysql> select substr(ename, 1, 1) as ename from emp;

    注意:起始下标从1开始,没有0

concat函数进行字符串的拼接

    mysql> select concat(empno,ename) from emp;

length 取长度

    mysql> select length(ename) enamelength from emp;

trim 去空格

    mysql> select * from emp where ename = trim('   KING');

str_to_date 将字符串转换成日期

date_format 格式化日期

format 设置千分位
case..when..then..when..then..else..end

    当员工的工作岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%,其它正常。 (注意:不修改数据库,只是将查询结果显示为工资上调)

    select ename,job, sal as oldsal,

    (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal

     from emp;

round 四舍五入

    mysql> select round(1234.567, 0) as result from emp; //保留整数位。

    mysql> select round(1234.567, 1) as result from emp; //保留1个小数

    mysql> select round(1234.567, 2) as result from emp; //保留2个小数

    mysql> select round(1234.567, -1) as result from emp; // 保留到十位。
        
rand() 生成随机数

    mysql> select round(rand()*100,0) from emp; // 100以内的随机数

ifnull 可以将 null 转换成一个具体值

ifnull是空处理函数。专门处理空的。在所有数据库当中,只要有NULL参与的数学运算,最终结果就是NULL。

注意:NULL只要参与运算,最终结果一定是NULL。为了避免这个现象,需要使用ifnull函数。

ifnull函数用法:ifnull(数据, 被当做哪个值)。如果“数据”为NULL的时候,把这个数据结构当做哪个值。
7:分组函数
7.1:分组函数(多行处理函数)

   特点:输入多行,最终输出一行

   分类:

      count 计数

      sum 求和

      avg 平均值

      max 最大值

      min 最小值

注意:

      1:分组函数在使用的时候必须先进行分组,然后才能用

      2:如果没有对数据进行分组,整张表默认为一组

      3:分组函数自动忽略null,不需要提前对null进行处理

      4:分组函数中count(*)和count(具体字段)

        count(具体字段):表示统计该字段下所有不为NULL的元素的总数。

        count(*):统计表当中的总行数。(只要有一行数据,count则++)

               因为每一行记录不可能都为NULL,一行数据中有一列不为NULL,则这行数据就是有效的

      5:分组函数不能够直接使用在where子句中(因为分组函数在使用的时候必须先分组之后才能使用;where在执行的时候还没有分组,所以where后面不能出现分组函数)

      6:所有的分组函数可以组合起来一起用

*找出最高(低)工资?

    mysql> select max(sal) from emp;

    mysql> select min(sal) from emp;

*计算工资和?

    mysql> select sum(sal) from emp;

*计算平均工资?

    mysql> select avg(sal) from emp;

*计算员工数量?

    mysql> select count(sal) from emp;
8:分组查询【重点】
8.1:什么是分组查询?

        在实际的应用中,可能有这样的需求,需要先进行分组,然后对每一组的数据进行操作,这时就需要分组查询

8.2:如何进行分组查询?
select

   ...

from

   ...

group by

   ...
8.3:将之前的关键字全部组合在一起,来看一下他们的执行顺序?
Select

  ...

From

  ...

Where

  ...

Group by

  ...

Order by

  ...

以上的关键字顺序不能颠倒

执行顺序:

1from

2where

3group by

4select

5order by

*找出每个工作岗位的工资和?

实现思路:按照工作岗位分组,然后对工资求和

Select

  Job,sum(sal)

From

  Emp

Group by

  Job;

以上这个语句的执行顺序?

        先从emp表中查询数据

        根据job字段进行分组

        然后对每一组数据进行sum(sal)

结论:在一条mysql语句当中,如果有group by语句的话,select只能跟:参加分组的字段,以及分组函数,其它的一律不能跟

8.4:使用having可以对分完组之后的数据进一步过滤;having不能单独使用,having不能代替where,having必须和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;

以上的sql语句执行效率较低

可以先将大于3000的都找出来,然后再分组

Select

     Deptno,max(sal)  

From

     Emp

Where

     Sal>3000

Group by

     Deptno;

//where和having,优先选择where,where实在完成不了的,再选择having
8.5:总结
Select

  ...

From

  ...

Where

  ...

Group by

  ...

Having

  ...

Order by

  ...

以上关键字只能按这个顺序来,不能颠倒

   执行顺序:

  1from

  2where

  3group by

  4having

  5select

  6order by

从某张表中查询数据

        先经过where条件筛选出有价值的数据

        对这些有价值的数据进行分组

        分组之后可以使用having继续筛选

        select查询出来

        最后排序输出

*找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除MANAGER岗位之外,要求按照平均薪资降序排列

Select

  Job,avg(sal) as avgsal

From

  Emp

Where

  Job<>’MANAGER’

Group by

  job

Having

  Avg(sal)>1500

Order by

  Avgsal desc;
8.6:把查询结果去除重复记录

注意:原表中的数据不会被修改,只是查询结果去重

     去重需要使用一个关键字:distinct

                mysql>select distinct job from emp;

     distinct只能出现在所有字段的前方

                mysql>select distinct job, deptnp from emp;

9:连接查询【重点】
9.1:什么是连接查询?

        从一张表中单独查询,称为单表查询

        两张或多张表联合起来查询数据,这种跨表查询,被称为连接查询

9.2:连接查询的分类?

        根据语法的年代分类:

                SQL92:1992年的时候出现的语法

                SQL99:1999年的时候出现的语法

        根据表连接的方式分类:

                内连接

                   等值连接

                   非等值连接

                   自连接

                外连接

                   左外连接(左连接)

                   右外连接(右连接)

                全连接

9.3:当两张表进行连接查询时,没有任何条件的限制会发生什么现象?

        例子:查询每个员工所在部门名称

                mysql>select * from emp;

                mysql>select ename,deptno from emp;

        当两张表进行连接查询时,没有任何条件限制的时候,最终查询结果条数是两张表条数的乘积,这种现象被称为:笛卡尔积现象

9.4:怎么避免笛卡尔积现象?

连接时加条件,满足这个条件的记录被筛选出来

Select

 e.ename,d.dname

From

 emp e,dept d

Where

 e.deptno = d.deptno;  //SQL92语法
9.5:内连接之等值连接

例子:查询每个员工所在部门名称,显示员工名和部门名?

SQL92语法:

Select

 e.ename,d.dname

From

 emp e,dept d

Where

 e.deptno = d.deptno;

  SQL92的缺点:结构不清晰,表的连接条件和后期进一步筛选的条件,都放到了where后面

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

SQL99语法:

Select

  e.ename,d.dname

From

  emp e

(inner可要可不要,带着可读性更好)Join

dept d

On

      e.deptno = d.deptno;  //条件是等量关系

9.6:内连接之非等值连接

例子:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级?

  MySQL>select * from emp;  e

  MySQL>select * from salgrade;  s

Select

  1. ename,e.sal,s.grade

From

  Emp e

Join

  Salgrade s

On

  E.sal Between s.losal and s.hisal;  

//条件不是一个等量关系,称为非等值连接

9.7:内连接之自连接

例子:查询员工的上级领导,要求显示员工名和对应的领导名?

  Mysql>select empno ,ename,mgr from emp;

  技巧:一张表看成两张表

Emp a 员工名

Emp b 领导名

Select

  1. ename as ‘员工名’,b.ename as ‘领导名’

From

  Emp a

Join

  Emp b

On

A.mgr = b.empno;  //员工的领导编号 = 领导的员工编号

以上就是内连接中的自连接,技巧:一张表看做两张表

9.8:外连接(右外连接又叫右连接)

Select

  e.ename,d.dname

From

  emp e right join dept d   //right表示将关键字右边的这张表看成主表,主要是为了将这张表中的数据全部查询出来,捎带着关联查询左边的表

在外连接当中,两张表连接,产生了主次关系

在内连接当中,两张表连接,没有主次关系

On

      e.deptno = d.deptno;

9.9:外连接(左外连接又叫左连接)

 Select

  e.ename,d.dname

From

  Dept d left Join emp e

On

      e.deptno = d.deptno;

任何一个右连接都有左连接的写法

任何一个左连接都有右连接的写法

 Select

  e.ename,d.dname

From

  emp e right out join dept d

On

      e.deptno = d.deptno;

//out是可以省略的,带着可读性强

*思考:外连接的查询结果条数一定是大于等于内连接的查询结果条数吗?

     答案是肯定的

9.10:三张表、四张表怎么连接?

语法:

Select

  ...

From

 a

Join

 b

On

  a和b的连接条件

Join

   c

On

  a和c的连接条件

Right join

d

On

  a和d的连接条件

一条SQL中内连接和外连接可以混合,都可以出现

例子:找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?

  Mysql > select * from emp;  e

  Mysql > select * from dept;  d

  Mysql > select * from salgrade;  s

Select

  1. ename,e.sal,d.dname,s.grade

From

  Emp e

Join

  Dept d

On

  1. deptno = d.deptno

Join

  Salgrade s

On

  1. sal betweeen s.losal and s.hisal;
10:子查询
10.1:什么是子查询?

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

10.2:子查询都可以出现在哪里呢?

Select

  ...(select)

From

  ...(select)

Where

  ...(select)

10.3:where子句中出现的子查询?

例子:找出比最低工资高的员工姓名和工资?

Select

  Ename,sal

From

  Emp

Where

  Sal > min(sal)  //error  where子句中不能直接使用分组函数

实现思路:

  第一步:查询最低工资是多少

Select min(sal) from emp;

  第二步:找出大于800的

Select ename,sal from emp where sal >800;

  第三步:合并

Select ename,sal from where sal > (select min(sal) from emp);

10.4:from子句的子查询

注意:from后面的子查询,可以将子查询的查询结果当做一张临时表

例子:找出每个岗位的平均工资的薪资等级?

   第一步:找出每个岗位的平均工资(按照岗位分组求平均值)

Select job,avg(sal) from emp group by job;

   第二步:把以上的查询结果就当做一张真实存在的表t

Mysql> select * from salgrade; s表

   t表和s表进行表连接,条件:t表avg(sal) between s.losal and s.hisal;

Select

  1. *,s.grade

From

  (Select job,avg(sal) from emp group by job) t

Join

  Salgrade s

On  

T.avgsal between s.losal and s.hisal;

10.5:select后面的出现的子查询(了解)

例子:找出每个员工的部门名称,要求显示员工名、部门名?

Select

  1. Ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname

From

  Emp e;

11:union(合并查询结果集)
11.1:union效率高

例子:查询工作岗位是MANAGER和SALESMAN的员工?

1 Select ename,job from emp where job = ‘MANAGER or SALESAMN’;

2 Select ename,job from emp where job in (‘MANAGER,SALESAMN’);

3 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在进行结果集合并的时候,要求两个结果集的列数相同且列的数据类型也相同

12:limit的使用【重点】
12.1:limit作用

        将查询结果集的一部分取出来,通常使用在分页查询中(分页的作用是为了提高用户的体验,因为一次将全部结果都查出来,用户体验感差,可以一页一页翻页看)

12.2:limit怎么用呢?

limit的完整用法:

  Limit startIndex,length

 startIndex是起始下标,length是长度

 起始下标从0开始

按照薪资降序,取出排名在前五名的员工?

Select

 Ename,sal

From

 Emp

Order by

 Sal desc

Limit 5;  //取前五

12.3:注意:MySQL当中limit在order by之后执行
12.4:按照薪资降序,取出排名在三到五名的员工?

Select

 Ename,sal

From

 Emp

Order by

 Sal desc

Limit 2,3;  //2表示起始位置从下标2开始,就是第3条记录;3表示长度

12.5:分页

每页显示3条记录

第一页:limit 0,3  [0  1  2]

第二页:limit 3,3  [3  4  5]

第三页:limit 6,3  [6  7  8]

第四页:limit 9,3  [9  10  11]

每页显示pagesize条记录

第pageno页:

limit (pageno - 1) * pagesize , pagesize

12.6:DQL大总结

Select

  ...

From

  ...

Where

  ...

Group by

  ...

Having

  ...

Order by

  ...

Limit

  ...

执行顺序

1.from

2.where

3.group by

4.having

5.select

6.order by

7.limit

13:表的创建(建表)
13.1:建表的语法格式(建表属于DDL语句,DDL包括create,drop,alter)

 Creat table 表名

(

  字段名1 数据类型,

  字段名2 数据类型,

字段名3 数据类型

);

表名:建议以t_或者tbl_开始,可读性强,见名知意

字段名:见名知意

两者都属于标识符

13.2:关于MySQL中的数据类型

Varchar(255)

 可变长度的字符串

 比较智能,节省空间

 会根据实际的数据长度动态分配空间

优点:节省空间

缺点:需要动态分配内存,速度慢

Char(255)

 定长字符串

 不管实际的数据长度是多少

 分配固定长度的空间去存储数据

 使用不恰当的时候,可能会导致空间的浪费

优点:不需要动态分配空间,速度快

缺点:使用不当是会造成空间的浪费

  varchar和char应该怎么去选择?

固定字符串长度:char

可变字符串长度:varchar

Int (11)

 数字中的整数型

Bigint

 数字中的长整数型

Float

 单精度浮点型数据

Double

 双精度浮点型数据

Date

 短日期类型

Datetime

 长日期类型

Clob(character large object)(>255)

 字符大对象

 最多可以存储4G的字符串

 比如:存储一个说明,存储一篇文章

Blob(binary large object)

 二进制大对象

 专门用来存储图片、声音、视频等流媒体数据

13.3:创建一个学生表?

学号、姓名、年龄、性别、邮箱地址

   create table t_student

    -> (

    -> no int,

    -> name varchar(32),

    -> sex char(1),

    -> age int(3),

    -> email varchar(255)

    -> );

13.4:删除表

  Drop table 表名;  //当这张表不存在的时候会报错

  Drop table if exists 表名;  //如果这张表存在的话,删除

13.5:插入数据(insert)DML

语法格式:

  Insert into 表名(字段名1,字段名2,字段名3...)  value(值1,值2,值3) ;

注意:字段名和值要一一对应(数量要对应,数据类型要对应)

  mysql> insert into  t_student(no,name,sex,age,email) values (1,'zhangsan','m','20','zhangsan@123.com');

mysql>  insert into  t_student(email,name,sex,age,no) values ('liai@123.com','lisi','m','19','2');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;

+------+----------+------+------+------------------+

| no   | name     | sex  | age  | email            |

+------+----------+------+------+------------------+

|    1 | zhangsan | m    |   20 | zhangsan@123.com |

|    2 | lisi     | m    |   19 | liai@123.com     |

+------+----------+------+------+------------------+

注意:insert语句但凡是执行成功了,那么必然会多一条记录

     没有给其它字段指定值的话,默认值是NULL

13.6:指定默认值

    Drop table if exists t_student;

       create table t_student

    -> (

    -> no int,

    -> name varchar(32),

    -> sex char(1) dedault ‘m’,  //default意思:缺省、默认

    -> age int(3),

    -> email varchar(255)

-> );

*insert into t_student value(2);  //错误的

//注意:前面的字段名省略的话,等于都写上了,所以值也要都写上!

insert into t_student value(2,’lisi’,’m’,’20’,’lisi@123.com’);

13.7:insert插入日期

数字格式化:format(数字,’格式’)

Date_format:将date类型转化成具有一定格式的varchar字符串类型

  语法格式:

Date_format(日期类型数据,’日期格式’)

  Date_format(birth, ’%m/%d/%y’) as birth from t_user;

Str_to_date:将字符串varchar类型转化成date类型

   语法格式:

Star_to_date(‘字符串日期’,’日期格式’)

Mysql的日期格式:

%y 年

%m 月

%d 日

%h 时

%i 分

%s 秒

Star_to_date(‘01-10-1990’,’%d-%m-%y’)

注意:数据库中有一条命名规范:所有的标识符全部都是小写,单词和单词之间使用下划线进行衔接

13.8:date和datetime两个类型的区别?

Date是短日期,包括年月日信息

Datetime是长日期,包括年月日时分秒信息

MySQL短日期默认格式:%y-%m-%d

MySQL长日期默认格式:%y-%m-%d %h:%i:%s

mysql>    insert into t_user(id,name,birth,create_time) value(1,'zhangsan','1990-10-01','2022-04-21 10:20:30');

13.9:在MySQL中怎么获取系统当前时间?

  Now()函数,并且获取的时间带有:时分秒信息

mysql>    insert into t_user(id,name,birth,create_time) value(2,'lisi','1991-10-01',now());

13.10:修改update(DML)

语法格式:

Update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3...where 条件;

例子:update t_user set name=’jack’,birth=’2000-10-11’ where id = 2;

13.11:删除数据delete(DML)

语法格式:

Delete from 表名 where 条件;

例子:

Delete from t_user where id=2;

注意:没有条件,整张表的数据会被全部删除

13.12:insert语句可以一次插入多条记录

语法:

Insert into t_user(字段名1,字段名2) value(),(),(),();

例子:

mysql>    insert into t_user(id,name,birth,create_time) values

(1,'zhangsan','1990-10-01',now()),

(2,'lisi','1991-10-01',now()),

(3,'wangwu','1998-10-01',now());

13.13:快速创建表(表的复制)

Create table emp2 as select * from emp;(将一个查询结果当做一张表新建)

13.14:将查询结果插入到一张表当中?(insert)

Create table dept_bak as select * from dept;

Select * from dept;

Insert into dept_bak select * from dept;

Select * from dept_bak;

13.15:快速删除表中的数据

Delete from dept_bak;  //这种方式速度慢

Delete语句删除数据的原理?(这种操作属于DML操作)

  表中的数据被删除了,但是这个数据在硬盘上的真实存储空间不会被释放

  优点:支持回滚,后悔了可以再恢复数据(rollback)

  缺点:删除效率比较低

truncate语句删除数据的原理?(这种操作属于DDL操作)

  这种删除效率比较高,表被一次截断,物理删除

  优点:快速

  缺点:不支持回滚

  用法:truncate table dept_bak;  

13.16:约束(constraint)【重点】
  1. 什么是约束?(作用:保证表中的数据有效)

在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性

  1. 常见的约束

非空约束:not null

唯一性约束:unique

主键约束:primary key(简称PK)

外键约束:foreign key(简称FK)

检查约束:check(MySQL不支持,oracle支持)

(3)非空约束:not null约束的字段不能NULL

Drop table if exits t_vip;

Create table t_vip(

   Id int,

   Name varchar(255) not null

);

Insert into t_vip(id,name) value(1,’zhangsan’);

Insert into t_vip(id,name) value(2,’lisi’);

小插曲:

xxxx.sql这种文件被称为sql脚本文件

sql脚本文件中编写了大量的sql语句

在执行sql语句的时候,该文件中所有的sql语句会全部执行

批量的执行sql语句,可以使用sql脚本文件

在mysql当中怎么执行sql脚本呢?

  Mysql>source D:cource3-MYSQLdocumentvip.sql

(4)唯一性约束:unique约束的字段不能重复,但是可以为NULL

Drop table if exits t_vip;

Create table t_vip(

   Id int,

   Name varchar(255) unique  //约束添加在列的后面,这种约束叫列级约束

 );

Insert into t_vip(id,name) value(1,’zhangsan’);

Insert into t_vip(id,name) value(2,’lisi’);

*name和email两个字段联合起来具有唯一性

Drop table if exits t_vip;

Create table t_vip(

   Id int,

   Name varchar(255) ,

   Email varcahr(255),

   Unique(name,email)  //约束没有添加在列的后面,这种约束叫表级约束

 );

Insert into t_vip(id,name,email) value(1,’zhangsan’,’zhangsan@123.com’);

Insert into t_vip(id,name,email) value(2,lisi,lisi@sina.com);

*unique和not null可以联合

Drop table if exists t_vip;

Create table t_vip;

 Id int,

 Name varchar(255) not null unique

);

  1. 主键约束(primary key简称PK)【重点】

主键约束的相关术语?

   主键约束:就是一种约束

   主键字段:该字段上添加了主键约束,这样的字段叫主键字段

   主键值:主键字段中的每一个值都叫做主键值

什么是主键值?有什么用?

   主键值是每一行记录的唯一标识

   主键值是每一行记录的身份证号

注意:每一张都应该有主键,没有主键,表无效

主键的特征:

Not null + unique(主键值不能是NULL,同时也不能重复

怎么给一张表添加主键约束呢?

Drop table if exists t_vip;

Create table t_vip(

//一个字段做主键:单一主键

Id int primary key,

Name varchar(255)

);

Insert into t_vip(id,name) value (1,’zhangsan’);

Insert into t_vip(id,name) value (2,’lisi’);

//error

Insert into t_vip(id,name) value (2,’wangwu’);

*表级约束主要是给多个字段联合起来添加约束

Drop table if exists t_vip;

//id和name联合起来做主键:复合主键

Create table t_vip(

Id int,

Name varchar(255),

Email varchar(255),

Primary key(id,name)

);

Insert into t_vip(id,name,email) value (1,’zhangsan’,’zhangsan@123.com’);

Insert into t_vip(id,name,email) value (1,’lisi’,’lisi@123.com’);

*一张表中约束可以添加两个吗?

Drop table if exists t_vip;

Create table t_vip(

Id int primary key,

Name varchar(255) primary key

);

//error

结论:一张表,主键约束只能添加一个(主键只能有一个)

*主键值建议使用:

Int

Bigint

Char

不建议使用:varchar来做主键。主键一般都是数字,一般都是定长的

*主键除了单一主键和复合主键之外,还可以这样分类?

自然主键:主键是一个自然数,和业务无关

业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键值

在mysql中,有一种机制,可以帮我们自动维护一个主键值?

Drop table if exists t_vip;

Create table t_vip(

Id int primary key auto_increment(表示自增,从一开始,以一自增),

Name varchar(255)

);

insert into t_vip(name) value (‘zhangsan’);

insert into t_vip(name) value (‘zhangsan’);

insert into t_vip(name) value (‘zhangsan’);

insert into t_vip(name) value (‘zhangsan’);

insert into t_vip(name) value (‘zhangsan’);

insert into t_vip(name) value (‘zhangsan’);

Select * from t_vip;

  1. 外键约束(foreign key简称FK)   【重点】

外键约束的相关术语:

   外键约束:一种约束

   外键字段:该字段上添加了外键约束

   外键值:外键字段当中的每一个值(外键值可以为NULL)

Foreign key(cno) references t_class(classno)

14:存储引擎
14.1:什么是存储引擎?有什么用呢?

存储引擎是mysql中特有的一个术语,其他数据库没有(oracle中有,但不叫这个名字)

存储引擎是一个表存储/组织数据的方式

不同的存储引擎,表存储数据的方式不同

14.2:怎么给表添加/指定“存储引擎”呢?

Show create table t_student;

在建表的时候可以在最后小括号”)”的右边使用:

  ENGINE来指定存储引擎

  CHARSET来指定这张表的字符编码方式

结论:

  Mysql默认的存储引擎是:InnoDB

Mysql默认的字符编码方式是:utf8

建表时指定存储引擎,以及字符编码方式

Create table t_product(

Id int primary key,

Name varchar(255)

)engine=InnoDB default charset=gbk;

14.3:怎么查看mysql支持哪些存储引擎呢?

命令:Show engines  G

Mysql支持九大存储引擎

14.4:查看mysql版本?

命令:Select version();

+-----------+

| version() |

+-----------+

| 8.0.19  |

+-----------+

14.5:关于mysql常用的存储引擎?

MyISAM存储引擎?

它管理的表具有以下特征:

  使用三个文件表示每个表:

    格式文件 — 存储表结构的定义(mytable.frm)

    数据文件 — 存储表行的内容(mytable.MYD)

    索引文件 — 存储表上索引(mytable.MYI):索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制。

  可被转换为压缩、只读表来节省空间

提示一下:

  对于一张表来说,只要是主键或者加有unique约束的字段上会自动创建索引。

  MyISAM存储引擎特点:

    可被转换为压缩、只读表来节省空间

  MyISAM不支持事务机制,安全性低。

InnoDB存储引擎?

这是mysql默认的存储引擎,同时也是一个重量级的存储引擎。

InnoDB支持事务,支持数据库崩溃后自动恢复机制。

InnoDB存储引擎最主要的特点是:非常安全。

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

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

  – InnoDB 表空间 tablespace 被用于存储表的内容(表空间是一个逻辑名称。表空间存储数据+索引。)

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

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

  – 提供全 ACID 兼容

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

  – 多版本(MVCC)和行级锁定

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

InnoDB最大的特点就是支持事务:

  以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读,不能很好的节省存储空间。

MEMORY存储引擎?

使用 MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定。

这两个特点使得 MEMORY 存储引擎非常快。

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

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

  – 表数据及索引被存储在内存中。(目的就是快,查询快!)

  – 表级锁机制。

  – 不能包含 TEXT 或 BLOB 字段。

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

MEMORY引擎优点:查询效率是最高的。不需要和硬盘交互。

MEMORY引擎缺点:不安全,关机之后数据消失。因为数据和索引都是在内存当中。

15:事务【重点】
15.1:什么是事务?

*什么是事务?

一个事务其实就是一个完整的业务逻辑,是一个最小的工作单元,不可再分。

    本质:一个事务就是多条DML语句同时成功或同时失败

*什么是一个完整的业务逻辑?

  假设转账,从A账户向B账户中转账10000.

  将A账户的钱减去10000(update语句)

  将B账户的钱加上10000(update语句)

  这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元,要么同时成功,要么同时失败,不可再分。

这两个update语句要求必须同时成功或者同时失败,这样才能保证钱是正确的。

*只有DML语句才会有事务这一说,其它语句和事务无关

insert

delete

update

只有以上的三个语句和事务有关系,其它都没有关系。

因为只有以上的三个语句是数据库表中数据进行增、删、改的。

只要你的操作一旦涉及到数据的增、删、改,那么就一定要考虑安全问题。

15.2:事务是如何做到多条DML语句同时成功同时失败的呢?

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

事务开启了:

insert

insert

insert

delete

update

update

update

事务结束了!

在事务的执行过程中,每一条DML的操作都会记录到“事务性活动的日志文件”中。

在事务的执行过程中,我们可以提交事务,也可以回滚事务。

(1)提交事务?

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

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

(2)回滚事务?

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

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

15.3:怎么提交事务?怎么回滚事务?

*提交事务:commit; 语句

*回滚事务:rollback; 语句(回滚永远都是只能回滚到上一次的提交点!)

*事务对应的英语单词是:transaction

*测试一下,在mysql当中默认的事务行为是怎样的?

*mysql默认情况下是支持自动提交事务的。

*什么是自动提交?

  每执行一条DML语句,则提交一次!

这种自动提交实际上是不符合我们的开发习惯,因为一个业务通常是需要多条DML语句共同执行才能完成的,为了保证数据的安全,必须要求同时成功之后再提交,所以不能执行一条,就提交一条。

*怎么将mysql的自动提交机制关闭掉呢?

 先执行这个命令:start  transaction

*演示事务:

回滚事务

  mysql> use bjpowernode;

  Database changed

  mysql> select * from dept_bak;

  Empty set (0.00 sec)

  mysql> start transaction;

  Query OK, 0 rows affected (0.00 sec)

  mysql> insert into dept_bak values(10,'abc', 'tj');

  Query OK, 1 row affected (0.00 sec)

  mysql> insert into dept_bak values(10,'abc', 'tj');

  Query OK, 1 row affected (0.00 sec)

  mysql> select * from dept_bak;

  +--------+-------+------+

  | DEPTNO | DNAME | LOC  |

  +--------+-------+------+

  |     10 | abc   | tj   |

  |     10 | abc   | tj   |

  +--------+-------+------+

  2 rows in set (0.00 sec)

  mysql> rollback;

  Query OK, 0 rows affected (0.00 sec)

  mysql> select * from dept_bak;

  Empty set (0.00 sec)

提交事务

  mysql> use bjpowernode;

  Database changed

  mysql> select * from dept_bak;

  +--------+-------+------+

  | DEPTNO | DNAME | LOC  |

  +--------+-------+------+

  |     10 | abc   | bj   |

  +--------+-------+------+

  1 row in set (0.00 sec)

  mysql> start transaction;

  Query OK, 0 rows affected (0.00 sec)

  mysql> insert into dept_bak values(20,'abc

  Query OK, 1 row affected (0.00 sec)

  mysql> insert into dept_bak values(20,'abc

  Query OK, 1 row affected (0.00 sec)

  mysql> insert into dept_bak values(20,'abc

  Query OK, 1 row affected (0.00 sec)

  mysql> commit;

  Query OK, 0 rows affected (0.01 sec)

  mysql> select * from dept_bak;

  +--------+-------+------+

  | DEPTNO | DNAME | LOC  |

  +--------+-------+------+

  |     10 | abc   | bj   |

  |     20 | abc   | tj   |

  |     20 | abc   | tj   |

  |     20 | abc   | tj   |

  +--------+-------+------+

  4 rows in set (0.00 sec)

  mysql> rollback;

  Query OK, 0 rows affected (0.00 sec)

  mysql> select * from dept_bak;

  +--------+-------+------+

  | DEPTNO | DNAME | LOC  |

  +--------+-------+------+

  |     10 | abc   | bj   |

  |     20 | abc   | tj   |

  |     20 | abc   | tj   |

  |     20 | abc   | tj   |

  +--------+-------+------+

  4 rows in set (0.00 sec)

15.4:事物的四个特性

A:原子性

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

C:一致性

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

I:隔离性

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

  教室A和教室B之间有一道墙,这道墙就是隔离性。

 D:持久性

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

15.5:事务的隔离性

*A教室和B教室中间有一道墙,这道墙可以很厚,也可以很薄。这就是事务的隔离级别。

这道墙越厚,表示隔离级别就越高。

*事务和事务之间的隔离级别有哪些呢?

(1)读未提交:read uncommitted(最低的隔离级别)《没有提交就读到了》

*什么是读未提交?

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

*这种隔离级别存在的问题就是:

    脏读现象!(Dirty Read)

    我们称读到了脏数据。

*这种隔离级别一般都是理论上的,大多数的数据库隔离级别都是二档起步!

(2)读已提交:read committed《提交之后才能读到》

*什么是读已提交?

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

*这种隔离级别解决了什么问题?

    解决了脏读的现象。

*这种隔离级别存在什么问题?

不可重复读取数据。

 *什么是不可重复读取数据呢?

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

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

*oracle数据库默认的隔离级别是:read committed

(3)可重复读:repeatable read《提交之后也读不到,永远读取的都是刚开启事务时的数据》

*什么是可重复读取?

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

*可重复读解决了什么问题?

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

*可重复读存在的问题是什么?

    可能会出现幻影读,每一次读取到的数据都是幻象,不够真实!

    早晨9点开始开启了事务,只要事务不结束,到晚上9点,读到的数据还是那样!

    读到的是假象,不够绝对的真实。

*mysql中默认的事务隔离级别就是这个。

(4)序列化/串行化:serializable(最高的隔离级别)

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

  这种隔离级别表示事务排队,不能并发!

  synchronized,线程同步(事务同步)

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

*查看隔离级别:SELECT @@tx_isolation

+-----------------+

| @@tx_isolation  |

+-----------------+

| REPEATABLE-READ |

+-----------------+

这是mysql默认的隔离级别

*改变隔离级别?

Set global transaction isolation level read committed;

16:索引
16.1:什么是索引?

索引(index)是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制。

一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引。

*索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。

对于一本字典来说,查找某个汉字有两种方式:

  第一种方式:一页一页挨着找,直到找到为止,这种查找方式属于全字典扫描。效率比较低。

  第二种方式:先通过目录(索引)去定位一个大概的位置,然后直接定位到这个位置,做局域性扫描,缩小扫描的范围,快速的查找。这种查找方式属于通过索引检索,效率较高。

*select * from t_user where name = 'jack';

以上的这条SQL语句会去name字段上扫描,为什么?

  因为查询条件是:name='jack',如果name字段上没有添加索引(目录),或者说没有给name字段创建索引MySQL会进行全扫描,会将name字段上的每一个值都比对一遍。效率比较低。

*MySQL在查询方面主要就是两种方式:

  第一种方式:全表扫描

  第二种方式:根据索引检索。

注意:

  在实际中,汉语字典前面的目录是排序的,按照a b c d e f....排序,

  为什么排序呢?因为只有排序了才会有区间查找这一说!(缩小扫描范围

其实就是扫描某个区间罢了!)

*在mysql数据库当中索引也是需要排序的,并且这个排序和TreeSet数据结构相同。TreeSet(TreeMap)底层是一个自平衡的二叉树!在mysql当中索引是一个B-Tree数据结构,遵循左小右大原则存放,采用中序遍历方式遍历取数据。

16.2:实现原理

假设有一张用户表:t_user

id(PK) name 编号

--------------------------------------------------------100 zhangsan 0x1111

120 lisi       0x2222

99 wangwu 0x8888

88 zhaoliu 0x9999

101 jack 0x6666

55 lucy 0x5555

130 tom 0x7777

提醒1:在任何数据库当中主键上都会自动添加索引对象,id字段上自动有索引,

因为id是PK。另外在mysql当中,一个字段上如果有unique约束的话,也会自动创建索引对象。

提醒2:在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有

一个硬盘的物理存储编号。

提醒3:在mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式

存在。在MyISAM存储引擎中,索引存储在一个.MYI文件中;在InnoDB存储引擎中,索引存储在一个逻辑名称叫做tablespace当中;在MEMORY存储引擎当中,索引被存储在内存当中;不管索引存储在哪里,索引在mysql当中都是一个树的形式存在。(自平衡二叉树:B-Tree)

16.3:添加索引的条件

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

条件1:数据量庞大(到底有多么庞大算庞大,这个需要测试,因为每一个硬件环境不同)

条件2:该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描。

条件3:该字段很少用DML(insert delete update)操作。(因为DML之后,索引需要重新排序。)

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

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

16.4:索引的删除和创建

创建索引:

  mysql> create index emp_ename_index on emp(ename);

  给emp表的ename字段添加索引,起名:emp_ename_index

删除索引:

  mysql> drop index emp_ename_index on emp;

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

16.5:在mysql当中,怎么查看一个SQL语句是否使用了索引进行检索?

mysql> explain select * from emp where ename = 'KING';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_l  en | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

扫描14条记录:说明没有使用索引。type=ALL

mysql> create index emp_ename_index on emp(ename);

mysql> explain select * from emp where ename = 'KING';

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+

|  1 | SIMPLE      | emp   | ref  | emp_ename_index | emp_ename_index | 33      | const |    1 | Using where |

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+

16.6:索引的失效

失效的第1种情况:

  select * from emp where ename like '%T';

  ename上即使添加了索引,也不会走索引,为什么?

    原因是因为模糊匹配当中以“%”开头了!

    尽量避免模糊查询的时候以“%”开始。

    这是一种优化的手段/策略。

  mysql> explain select * from emp where ename like '%T';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第2种情况:

  使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。

mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';

+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+

  | id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |  +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+

  |  1 | SIMPLE      | emp   | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 | Using where |

+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+

失效的第3种情况:

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

  什么是复合索引?

两个字段,或者更多的字段联合起来添加一个索引,叫做复合索引。

  create index emp_job_sal_index on emp(job,sal);

  mysql> explain select * from emp where job = 'MANAGER';

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+

  | id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |  +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+

  |  1 | SIMPLE      | emp   | ref  | emp_job_sal_index | emp_job_sal_index | 30      | const |    3 | Using where |

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+  

  mysql> explain select * from emp where sal = 800;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第4种情况

  在where当中索引列参加了运算,索引失效。

  mysql> create index emp_sal_index on emp(sal);

  explain select * from emp where sal = 800;

+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

  | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |  +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

  |  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |

+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

  mysql> explain select * from emp where sal+1 = 800;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第5种情况

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

  explain select * from emp where lower(ename) = 'smith';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

16.7:索引是各种数据库进行优化的重要手段,优化的时候考虑的因素就是索引

*索引分类?

单一索引:一个字段上添加索引

复合索引:两个字段或更多的字段上添加索引

主键索引:主键上添加索引

唯一性索引:具有unique约束的字段上添加索引

17:视图(view)
17.1:什么是视图?

View:站在不同的角度去看待同一份数据

17.2:创建和删除

创建视图对象:

  create view dept2_view as select * from dept2;

删除视图对象:

  drop view dept2_view;

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

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

17.3:用途

我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作!(视图的特点:通过对视图的操作,会影响到原表数据。)

//面向视图查询

select * from dept2_view;

// 面向视图插入

insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');

// 查询原表数据

mysql> select * from dept2;

+--------+------------+----------+

| DEPTNO | DNAME      | LOC      |

+--------+------------+----------+

|     10 | ACCOUNTING | NEW YORK |

|     20 | RESEARCH   | DALLAS   |

|     30 | SALES      | CHICAGO  |

|     40 | OPERATIONS | BOSTON   |

|     60 | SALES      | BEIJING  |

+--------+------------+----------+

// 面向视图删除

mysql> delete from dept2_view;

// 查询原表数据

mysql> select * from dept2;

Empty set (0.00 sec)

17.4:视图对象在实际开发中到底有什么作用?

假设有一条非常复杂的SQL语句,而这条SQL语句需要在不同的位置上反复使用,每一次使用这个sql语句的时候都需要重新编写,很长,很麻烦,怎么办?

  可以把这条复杂的SQL语句以视图对象的形式新建,在需要编写这条SQL语句的位置直接使用视图对象,可以大大简化开发,并且利于后期的维护;因为修改的时候也只需要修改一个位置就行,只需要修改视图对象所映射的SQL语句。我们以后面向视图开发的时候,使用视图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中,视图对象也是存储在硬盘上的,不会消失。

再提醒一下:

  视图对应的语句只能是DQL语句,但是视图对象创建完成之后,可以对视图进行增删改查等操作。

小插曲:

  增删改查,又叫做:CRUD。

  CRUD是在公司中程序员之间沟通的术语,一般我们很少说增删改查,一般都说CRUD。

  C:Create(增)

  R:Retrive(查:检索)

  U:Update(改)

  D:Delete(删)

18:DAB命令
18.1:DAB常用命令

数据导出?

  注意:在windows的dos命令窗口中:

    mysqldump bjpowernode>D:bjpowernode.sql -uroot -p123456

  可以导出指定的表吗?

    mysqldump bjpowernode emp>D:bjpowernode.sql -uroot -p123456

数据导入?

  注意:需要先登录到mysql数据库服务器上。

  然后创建数据库:create database bjpowernode;

  使用数据库:use bjpowernode

  然后初始化数据库:source D:bjpowernode.sql

19:数据库设计的三范式
19.1:什么是数据库设计范式?

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

数据库设计范式共有3个

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

第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,

不要产生部分依赖。

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

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

第一范式

最核心,最重要的范式,所有表的设计都需要满足。

必须有主键,并且每一个字段都是原子性不可再分。

学生编号 学生姓名 联系方式

------------------------------------------

1001 张三 zs@gmail.com,1359999999

1002 李四 ls@gmail.com,13699999999

1001 王五 ww@163.net,13488888888

以上是学生表,满足第一范式吗?

  不满足,第一:没有主键。第二:联系方式可以分为邮箱地址和电话

学生编号(pk) 学生姓名 邮箱地址 联系电话

----------------------------------------------------

1001 张三 zs@gmail.com   1359999999

1002 李四 ls@gmail.com    13699999999

1003 王五 ww@163.net    13488888888

第二范式:

建立在第一范式的基础之上,

要求所有非主键字段必须完全依赖主键,不要产生部分依赖。

学生编号 学生姓名 教师编号 教师姓名

----------------------------------------------------

1001 张三  001     王老师

1002 李四  002     赵老师

1003 王五  001     王老师

1001 张三  002     赵老师

这张表描述了学生和老师的关系:(1个学生可能有多个老师,1个老师有多个学生)

这是非常典型的:多对多关系!

分析以上的表是否满足第一范式?

  不满足第一范式。

怎么满足第一范式呢?修改

学生编号+教师编号(pk) 学生姓名  教师姓名

----------------------------------------------------

1001 001 张三 王老师

1002 002 李四 赵老师

1003 001 王五 王老师

1001 002 张三 赵老师

学生编号和教师编号,两个字段联合做主键,复合主键(PK: 学生编号+教师编号)

经过修改之后,以上的表满足了第一范式。但是满足第二范式吗?

  不满足,“张三”依赖1001,“王老师”依赖001,显然产生了部分依赖。

  产生部分依赖有什么缺点?

    数据冗余了,空间浪费了,“张三”重复了,“王老师”重复了。

为了让以上的表满足第二范式,你需要这样设计:

  使用三张表来表示多对多的关系!!!!

  学生表

  学生编号(pk) 学生名字

  ------------------------------------

  1001 张三

  1002 李四

  1003 王五

  教师表

  教师编号(pk) 教师姓名

  --------------------------------------

  001 王老师

  002 赵老师

  学生教师关系表

  id(pk) 学生编号(fk) 教师编号(fk)

 ------------------------------------------------------

  1 1001 001

  2 1002 002

  3 1003 001

  4 1001 002

背口诀:

  多对多怎么设计?

    多对多,三张表,关系表两个外键第三范式

第三范式建立在第二范式的基础之上

要求所有非主键字典必须直接依赖主键,不要产生传递依赖。

学生编号(PK) 学生姓名 班级编号  班级名称

--------------------------------------------------------

  1001 张三 01 一年一班

  1002 李四 02 一年二班

  1003 王五 03 一年三班

  1004 赵六 03 一年三班

以上表的设计是描述:班级和学生的关系。很显然是1对多关系!

一个教室中有多个学生。

分析以上表是否满足第一范式?

  满足第一范式,有主键。

分析以上表是否满足第二范式?

  满足第二范式,因为主键不是复合主键,没有产生部分依赖,主键是单一主键。

  第三范式要求:不要产生传递依赖!

一年一班依赖01,01依赖1001,产生了传递依赖。

  不符合第三范式的要求。产生了数据的冗余。

那么应该怎么设计一对多呢?

  班级表:一

  班级编号(pk) 班级名称

  ----------------------------------------

  01 一年一班

  02 一年二班

  03 一年三班

  学生表:多

  学生编号(PK) 学生姓名 班级编号(fk)

  -------------------------------------------

  1001 张三 01

  1002 李四 02

  1003 王五 03

  1004 赵六 03

  背口诀:

一对多,两张表,多的表加外键

19.2:总结表的设计

  多对多:三张表,关系表两个外键

一对多:两张表,多的表加外键

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

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

一对一怎么设计?

  没有拆分表之前:一张表

  t_user

    id login_name login_pwd real_name email address........

    ---------------------------------------------------------------------------

    1 zhangsan 123 张三 zhangsan@xxx

    2 lisi 123 李四 lisi@xxx

    ...

这种庞大的表建议拆分为两张:

    t_login 登录信息表

    id(pk) login_name login_pwd

    ---------------------------------

    1 zhangsan 123

    2 lisi     123

    t_user 用户详细信息表

    id(pk) real_name email address........ login_id(fk+unique)    --------------------------------------------------------

    100 张三 zhangsan@xxx 1

    200 李四 lisi@xxx 2

    口诀:一对一,外键唯一

嘱咐:数据库设计三范式是理论上的。

实践和理论有的时候有偏差。

最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度。

因为在sql当中,表和表之间连接次数越多,效率越低。(笛卡尔积)

有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,

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

第一步:取得每个部门最高薪水(按照部门编号分组,找出每一组最大值)

mysql> select deptno,max(sal) as maxsal from emp group by deptno;

+--------+---------+

| deptno | maxsal  |

+--------+---------+

|     10 | 5000.00 |

|     20 | 3000.00 |

|     30 | 2850.00 |

+--------+---------+

第二步:将以上的查询结果当做一张临时表t,

t和emp表连接,条件:t.deptno = e.deptno and t.maxsal = e.sal

select

    e.ename, t.*

from

    emp e

join

    (select deptno,max(sal) as maxsal from emp group by deptno) t

on

    t.deptno = e.deptno and t.maxsal = e.sal;

+-------+--------+---------+

| ename | deptno | maxsal  |

+-------+--------+---------+

| BLAKE |     30 | 2850.00 |

| SCOTT |     20 | 3000.00 |

| KING  |     10 | 5000.00 |

| FORD  |     20 | 3000.00 |

+-------+--------+---------+

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

第一步:找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

+--------+-------------+

| deptno | avgsal      |

+--------+-------------+

|     10 | 2916.666667 |

|     20 | 2175.000000 |

|     30 | 1566.666667 |

+--------+-------------+

第二步:将以上查询结果当做t表,t和emp表连接

条件:部门编号相同,并且emp的sal大于t表的avgsal

select

    t.*, e.ename, e.sal

from

    emp e

join

    (select deptno,avg(sal) as avgsal from emp group by deptno) t

on

    e.deptno = t.deptno and e.sal > t.avgsal;

+--------+-------------+-------+---------+

| deptno | avgsal      | ename | sal     |

+--------+-------------+-------+---------+

|     30 | 1566.666667 | ALLEN | 1600.00 |

|     20 | 2175.000000 | JONES | 2975.00 |

|     30 | 1566.666667 | BLAKE | 2850.00 |

|     20 | 2175.000000 | SCOTT | 3000.00 |

|     10 | 2916.666667 | KING  | 5000.00 |

|     20 | 2175.000000 | FORD  | 3000.00 |

+--------+-------------+-------+---------+

3、取得部门中(所有人的)平均的薪水等级

    平均的薪水等级:先计算每一个薪水的等级,然后找出薪水等级的平均值。

    平均薪水的等级:先计算平均薪水,然后找出每个平均薪水的等级值。

    第一步:找出每个人的薪水等级

    emp e和salgrade s表连接。

    连接条件:e.sal between s.losal and s.hisal

     

    select

        e.ename,e.sal,e.deptno,s.grade

    from

        emp e

    join

        salgrade s

    on

        e.sal between s.losal and s.hisal;

    +--------+---------+--------+-------+

    | ename  | sal     | deptno | grade |

    +--------+---------+--------+-------+

    | CLARK  | 2450.00 |     10 |     4 |

    | KING   | 5000.00 |     10 |     5 |

    | MILLER | 1300.00 |     10 |     2 |

    | SMITH  |  800.00 |     20 |     1 |

    | ADAMS  | 1100.00 |     20 |     1 |

    | SCOTT  | 3000.00 |     20 |     4 |

    | FORD   | 3000.00 |     20 |     4 |

    | JONES  | 2975.00 |     20 |     4 |

    | MARTIN | 1250.00 |     30 |     2 |

    | TURNER | 1500.00 |     30 |     3 |

    | BLAKE  | 2850.00 |     30 |     4 |

    | ALLEN  | 1600.00 |     30 |     3 |

    | JAMES  |  950.00 |     30 |     1 |

    | WARD   | 1250.00 |     30 |     2 |

    +--------+---------+--------+-------+

    第二步:基于以上的结果继续按照deptno分组,求grade的平均值。

    select

        e.deptno,avg(s.grade)

    from

        emp e

    join

        salgrade s

    on

        e.sal between s.losal and s.hisal

    group by

        e.deptno;

    +--------+--------------+

    | deptno | avg(s.grade) |

    +--------+--------------+

    |     10 |       3.6667 |

    |     20 |       2.8000 |

    |     30 |       2.5000 |

    +--------+--------------+

4、不准用组函数(Max ),取得最高薪水

第一种:sal降序,limit 1

select ename,sal from emp order by sal desc limit 1;

+-------+---------+

| ename | sal     |

+-------+---------+

| KING  | 5000.00 |

+-------+---------+

第二种方案:select max(sal) from emp;

第三种方案:表的自连接

select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

+---------+

| sal     |

+---------+

| 5000.00 |

+---------+

select

    distinct a.sal

from

    emp a

join

    emp b

on

    a.sal < b.sal

a表

+---------+

| sal     |

+---------+

|  800.00 |

| 1600.00 |

| 1250.00 |

| 2975.00 |

| 1250.00 |

| 2850.00 |

| 2450.00 |

| 3000.00 |

| 5000.00 |

| 1500.00 |

| 1100.00 |

|  950.00 |

| 3000.00 |

| 1300.00 |

+---------+

b表

+---------+

| sal     |

+---------+

|  800.00 |

| 1600.00 |

| 1250.00 |

| 2975.00 |

| 1250.00 |

| 2850.00 |

| 2450.00 |

| 3000.00 |

| 5000.00 |

| 1500.00 |

| 1100.00 |

|  950.00 |

| 3000.00 |

| 1300.00 |

+---------+

5、取得平均薪水最高的部门的部门编号

第一种方案:降序取第一个。

    第一步:找出每个部门的平均薪水

        select deptno,avg(sal) as avgsal from emp group by deptno;

        +--------+-------------+

        | deptno | avgsal      |

        +--------+-------------+

        |     10 | 2916.666667 |

        |     20 | 2175.000000 |

        |     30 | 1566.666667 |

        +--------+-------------+

    第二步:降序选第一个。

        select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;

        +--------+-------------+

        | deptno | avgsal      |

        +--------+-------------+

        |     10 | 2916.666667 |

        +--------+-------------+

第二种方案:max

    第一步:找出每个部门的平均薪水

    select deptno,avg(sal) as avgsal from emp group by deptno;

    +--------+-------------+

    | deptno | avgsal      |

    +--------+-------------+

    |     10 | 2916.666667 |

    |     20 | 2175.000000 |

    |     30 | 1566.666667 |

    +--------+-------------+

    第二步:找出以上结果中avgsal最大的值。

    select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;

    +---------------+

    | max(t.avgsal) |

    +---------------+

    |   2916.666667 |

    +---------------+

    第三步:

    select

        deptno,avg(sal) as avgsal

    from

        emp

    group by

        deptno

    having

        avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

     

    +--------+-------------+

    | deptno | avgsal      |

    +--------+-------------+

    |     10 | 2916.666667 |

    +--------+-------------+

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

select

    d.dname,avg(e.sal) as avgsal

from

    emp e

join

    dept d

on

    e.deptno = d.deptno

group by

    d.dname

order by

    avgsal desc

limit

    1;

+------------+-------------+

| dname      | avgsal      |

+------------+-------------+

| ACCOUNTING | 2916.666667 |

+------------+-------------+

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

平均薪水是800

平均薪水是900

那么他俩都是1级别。

第一步:找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

+--------+-------------+

| deptno | avgsal      |

+--------+-------------+

|     10 | 2916.666667 |

|     20 | 2175.000000 |

|     30 | 1566.666667 |

+--------+-------------+

第二步:找出每个部门的平均薪水的等级

以上t表和salgrade表连接,条件:t.avgsal between s.losal and s.hisal

select

    t.*,s.grade

from

    (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t

join

    salgrade s

on

    t.avgsal between s.losal and s.hisal;

+------------+-------------+-------+

| dname      | avgsal      | grade |

+------------+-------------+-------+

| SALES      | 1566.666667 |     3 |

| ACCOUNTING | 2916.666667 |     4 |

| RESEARCH   | 2175.000000 |     4 |

+------------+-------------+-------+

select

    t.*,s.grade

from

    (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t

join

    salgrade s

on

    t.avgsal between s.losal and s.hisal

where

    s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);

+-------+-------------+-------+

| dname | avgsal      | grade |

+-------+-------------+-------+

| SALES | 1566.666667 |     3 |

+-------+-------------+-------+

抛开之前的,最低等级你怎么着?

    平均薪水最低的对应的等级一定是最低的.

    select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;

    +-------------+

    | avgsal      |

    +-------------+

    | 1566.666667 |

    +-------------+

    select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal;

    +-------+

    | grade |

    +-------+

    |     3 |

    +-------+

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

    比“普通员工的最高薪水”还要高的一定是领导!

        没毛病!!!!

mysql> select distinct mgr from emp where mgr is not null;

+------+

| mgr  |

+------+

| 7902 |

| 7698 |

| 7839 |

| 7566 |

| 7788 |

| 7782 |

+------+

员工编号没有在以上范围内的都是普通员工。

第一步:找出普通员工的最高薪水!

not in在使用的时候,后面小括号中记得排除NULL。

select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

+----------+

| max(sal) |

+----------+

|  1600.00 |

+----------+

第二步:找出高于1600的

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

+-------+---------+

| ename | sal     |

+-------+---------+

| JONES | 2975.00 |

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| SCOTT | 3000.00 |

| KING  | 5000.00 |

| FORD  | 3000.00 |

+-------+---------+

9、取得薪水最高的前五名员工

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

+-------+---------+

| ename | sal     |

+-------+---------+

| KING  | 5000.00 |

| SCOTT | 3000.00 |

| FORD  | 3000.00 |

| JONES | 2975.00 |

| BLAKE | 2850.00 |

+-------+---------+

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

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

+--------+---------+

| ename  | sal     |

+--------+---------+

| CLARK  | 2450.00 |

| ALLEN  | 1600.00 |

| TURNER | 1500.00 |

| MILLER | 1300.00 |

| MARTIN | 1250.00 |

+--------+---------+

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

日期也可以降序,升序。

     

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

    +--------+------------+

    | ename  | hiredate   |

    +--------+------------+

    | ADAMS  | 1987-05-23 |

    | SCOTT  | 1987-04-19 |

    | MILLER | 1982-01-23 |

    | FORD   | 1981-12-03 |

    | JAMES  | 1981-12-03 |

    +--------+------------+

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

分组count

第一步:找出每个员工的薪水等级

select

    e.ename,e.sal,s.grade

from

    emp e

join

    salgrade s

on

    e.sal between s.losal and s.hisal;

+--------+---------+-------+

| ename  | sal     | grade |

+--------+---------+-------+

| SMITH  |  800.00 |     1 |

| ALLEN  | 1600.00 |     3 |

| WARD   | 1250.00 |     2 |

| JONES  | 2975.00 |     4 |

| MARTIN | 1250.00 |     2 |

| BLAKE  | 2850.00 |     4 |

| CLARK  | 2450.00 |     4 |

| SCOTT  | 3000.00 |     4 |

| KING   | 5000.00 |     5 |

| TURNER | 1500.00 |     3 |

| ADAMS  | 1100.00 |     1 |

| JAMES  |  950.00 |     1 |

| FORD   | 3000.00 |     4 |

| MILLER | 1300.00 |     2 |

+--------+---------+-------+

第二步:继续按照grade分组统计数量

select

    s.grade ,count(*)

from

    emp e

join

    salgrade s

on

    e.sal between s.losal and s.hisal

group by

    s.grade;

+-------+----------+

| grade | count(*) |

+-------+----------+

|     1 |        3 |

|     2 |        3 |

|     3 |        2 |

|     4 |        5 |

|     5 |        1 |

+-------+----------+

13、面试题:

有 3 个表 S(学生表),C(课程表),SC(学生选课表)

S(SNO,SNAME)代表(学号,姓名)

C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)

SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)

问题:

1,找出没选过“黎明”老师的所有学生姓名。

2,列出 2 门以上(含2 门)不及格学生姓名及平均成绩。

3,即学过 1 号课程又学过 2 号课所有学生的姓名。

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

select

    a.ename '员工', b.ename '领导'

from

    emp a

left join

    emp b

on

    a.mgr = b.empno;

+--------+-------+

| 员工   | 领导    |

+--------+-------+

| SMITH  | FORD  |

| ALLEN  | BLAKE |

| WARD   | BLAKE |

| JONES  | KING  |

| MARTIN | BLAKE |

| BLAKE  | KING  |

| CLARK  | KING  |

| SCOTT  | JONES |

| KING   | NULL  |

| TURNER | BLAKE |

| ADAMS  | SCOTT |

| JAMES  | BLAKE |

| FORD   | JONES |

| MILLER | CLARK |

+--------+-------+

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

emp a 员工表

emp b 领导表

a.mgr = b.empno and a.hiredate < b.hiredate

select

    a.ename '员工', a.hiredate, b.ename '领导', b.hiredate, d.dname

from

    emp a

join

    emp b

on

    a.mgr = b.empno

join

    dept d

on

    a.deptno = d.deptno

where

     a.hiredate < b.hiredate;

+-------+------------+-------+------------+------------+

| 员工     | hiredate   | 领导    | hiredate   | dname      |

+-------+------------+-------+------------+------------+

| CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |

| SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |

| JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |

| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |

| WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |

| BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |

+-------+------------+-------+------------+------------+

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

select

    e.*,d.dname

from

    emp e

right join

    dept d

on

    e.deptno = d.deptno;

+-------+--------+-----------+------+------------+---------+---------+--------+------------+

| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |

+-------+--------+-----------+------+------------+---------+---------+--------+------------+

|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |

|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |

|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |

|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |

|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |

|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |

|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |

|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |

|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |

|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |

|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |

|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |

|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |

|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |

|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |

+-------+--------+-----------+------+------------+---------+---------+--------+------------+

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

按照部门编号分组,计数,筛选出 >= 5

select

    deptno

from

    emp

group by

    deptno

having

    count(*) >= 5;

+--------+

| deptno |

+--------+

|     20 |

|     30 |

+--------+

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

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');

+--------+---------+

| ename  | sal     |

+--------+---------+

| ALLEN  | 1600.00 |

| WARD   | 1250.00 |

| JONES  | 2975.00 |

| MARTIN | 1250.00 |

| BLAKE  | 2850.00 |

| CLARK  | 2450.00 |

| SCOTT  | 3000.00 |

| KING   | 5000.00 |

| TURNER | 1500.00 |

| ADAMS  | 1100.00 |

| JAMES  |  950.00 |

| FORD   | 3000.00 |

| MILLER | 1300.00 |

+--------+---------+

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

select ename,job from emp where job = 'CLERK';

+--------+-------+

| ename  | job   |

+--------+-------+

| SMITH  | CLERK |

| ADAMS  | CLERK |

| JAMES  | CLERK |

| MILLER | CLERK |

+--------+-------+

select

    e.ename,e.job,d.dname

from

    emp e

join

    dept d

on

    e.deptno = d.deptno

where

    e.job = 'CLERK';

+--------+-------+------------+

| ename  | job   | dname      |

+--------+-------+------------+

| MILLER | CLERK | ACCOUNTING |

| SMITH  | CLERK | RESEARCH   |

| ADAMS  | CLERK | RESEARCH   |

| JAMES  | CLERK | SALES      |

+--------+-------+------------+

select

    e.ename,e.job,d.dname,d.deptno

from

    emp e

join

    dept d

on

    e.deptno = d.deptno

where

    e.job = 'CLERK';

+--------+-------+------------+--------+

| ename  | job   | dname      | deptno |

+--------+-------+------------+--------+

| MILLER | CLERK | ACCOUNTING |     10 |

| SMITH  | CLERK | RESEARCH   |     20 |

| ADAMS  | CLERK | RESEARCH   |     20 |

| JAMES  | CLERK | SALES      |     30 |

+--------+-------+------------+--------+

//每个部门的人数?

select deptno, count(*) as deptcount from emp group by deptno;

+--------+-----------+

| deptno | deptcount |

+--------+-----------+

|     10 |         3 |

|     20 |         5 |

|     30 |         6 |

+--------+-----------+

select

    t1.*,t2.deptcount

from

    (select

        e.ename,e.job,d.dname,d.deptno

    from

        emp e

    join

        dept d

    on

        e.deptno = d.deptno

    where

        e.job = 'CLERK') t1

join

    (select deptno, count(*) as deptcount from emp group by deptno) t2

on

    t1.deptno = t2.deptno;

+--------+-------+------------+--------+-----------+

| ename  | job   | dname      | deptno | deptcount |

+--------+-------+------------+--------+-----------+

| MILLER | CLERK | ACCOUNTING |     10 |         3 |

| SMITH  | CLERK | RESEARCH   |     20 |         5 |

| ADAMS  | CLERK | RESEARCH   |     20 |         5 |

| JAMES  | CLERK | SALES      |     30 |         6 |

+--------+-------+------------+--------+-----------+

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

按照工作岗位分组求最小值。

select job,count(*) from emp group by job having min(sal) > 1500;

+-----------+----------+

| job       | count(*) |

+-----------+----------+

| ANALYST   |        2 |

| MANAGER   |        3 |

| PRESIDENT |        1 |

+-----------+----------+

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

select ename from emp where deptno = (select deptno from dept where dname = 'SALES');

+--------+

| ename  |

+--------+

| ALLEN  |

| WARD   |

| MARTIN |

| BLAKE  |

| TURNER |

| JAMES  |

+--------+

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

select

    e.ename '员工',d.dname,l.ename '领导',s.grade

from

    emp e

join

    dept d

on

    e.deptno = d.deptno

left join

    emp l

on

    e.mgr = l.empno

join

    salgrade s

on

    e.sal between s.losal and s.hisal

where

    e.sal > (select avg(sal) from emp);

+-------+------------+-------+-------+

| 员工     | dname      | 领导    | grade |

+-------+------------+-------+-------+

| JONES | RESEARCH   | KING  |     4 |

| BLAKE | SALES      | KING  |     4 |

| CLARK | ACCOUNTING | KING  |     4 |

| SCOTT | RESEARCH   | JONES |     4 |

| KING  | ACCOUNTING | NULL  |     5 |

| FORD  | RESEARCH   | JONES |     4 |

+-------+------------+-------+-------+

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

select job from emp where ename = 'SCOTT';

+---------+

| job     |

+---------+

| ANALYST |

+---------+

select

    e.ename,e.job,d.dname

from

    emp e

join

    dept d

on

    e.deptno = d.deptno

where

    e.job = (select job from emp where ename = 'SCOTT')

and

    e.ename <> 'SCOTT';

+-------+---------+----------+

| ename | job     | dname    |

+-------+---------+----------+

| FORD  | ANALYST | RESEARCH |

+-------+---------+----------+

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

select distinct sal from emp where deptno = 30;

+---------+

| sal     |

+---------+

| 1600.00 |

| 1250.00 |

| 2850.00 |

| 1500.00 |

|  950.00 |

+---------+

select

    ename,sal

from

    emp

where

    sal in(select distinct sal from emp where deptno = 30)

and

    deptno <> 30;

Empty set (0.00 sec)

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

select max(sal) from emp where deptno = 30;

+----------+

| max(sal) |

+----------+

|  2850.00 |

+----------+

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      |

+-------+---------+------------+

| KING  | 5000.00 | ACCOUNTING |

| JONES | 2975.00 | RESEARCH   |

| SCOTT | 3000.00 | RESEARCH   |

| FORD  | 3000.00 | RESEARCH   |

+-------+---------+------------+

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

没有员工的部门,部门人数是0

select

    d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime

from

    emp e

right join

    dept d

on

    e.deptno = d.deptno

group by

    d.deptno;

+--------+--------+-------------+----------------+

| deptno | ecount | avgsal      | avgservicetime |

+--------+--------+-------------+----------------+

|     10 |      3 | 2916.666667 |        38.0000 |

|     20 |      5 | 2175.000000 |        35.8000 |

|     30 |      6 | 1566.666667 |        38.3333 |

|     40 |      0 |    0.000000 |         0.0000 |

+--------+--------+-------------+----------------+

在mysql当中怎么计算两个日期的“年差”,差了多少年?

    TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

     

    timestampdiff(YEAR, hiredate, now())

    间隔类型:

        SECOND   秒,

        MINUTE   分钟,

        HOUR   小时,

        DAY   天,

        WEEK   星期

        MONTH   月,

        QUARTER   季度,

        YEAR   年

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

select

    e.ename,d.dname,e.sal

from

    emp e

join

    dept d

on

    e.deptno = d.deptno;

+--------+------------+---------+

| ename  | dname      | sal     |

+--------+------------+---------+

| CLARK  | ACCOUNTING | 2450.00 |

| KING   | ACCOUNTING | 5000.00 |

| MILLER | ACCOUNTING | 1300.00 |

| SMITH  | RESEARCH   |  800.00 |

| JONES  | RESEARCH   | 2975.00 |

| SCOTT  | RESEARCH   | 3000.00 |

| ADAMS  | RESEARCH   | 1100.00 |

| FORD   | RESEARCH   | 3000.00 |

| ALLEN  | SALES      | 1600.00 |

| WARD   | SALES      | 1250.00 |

| MARTIN | SALES      | 1250.00 |

| BLAKE  | SALES      | 2850.00 |

| TURNER | SALES      | 1500.00 |

| JAMES  | SALES      |  950.00 |

+--------+------------+---------+

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

select

    d.deptno,d.dname,d.loc,count(e.ename)

from

    emp e

right join

    dept d

on

    e.deptno = d.deptno

group by

    d.deptno,d.dname,d.loc;

+--------+------------+----------+----------------+

| deptno | dname      | loc      | count(e.ename) |

+--------+------------+----------+----------------+

|     10 | ACCOUNTING | NEW YORK |              3 |

|     20 | RESEARCH   | DALLAS   |              5 |

|     30 | SALES      | CHICAGO  |              6 |

|     40 | OPERATIONS | BOSTON   |              0 |

+--------+------------+----------+----------------+

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

select

    job,min(sal) as minsal

from

    emp

group by

    job;

+-----------+----------+

| job       | minsal        |

+-----------+----------+

| ANALYST   |  3000.00 |

| CLERK     |   800.00 |

| MANAGER   |  2450.00 |

| PRESIDENT |  5000.00 |

| SALESMAN  |  1250.00 |

+-----------+----------+

emp e和以上t连接

select

    e.ename,t.*

from

    emp e

join

    (select

        job,min(sal) as minsal

    from

        emp

    group by

        job) t

on

    e.job = t.job and e.sal = t.minsal;

+--------+-----------+---------+

| ename  | job       | minsal  |

+--------+-----------+---------+

| SMITH  | CLERK     |  800.00 |

| WARD   | SALESMAN  | 1250.00 |

| MARTIN | SALESMAN  | 1250.00 |

| CLARK  | MANAGER   | 2450.00 |

| SCOTT  | ANALYST   | 3000.00 |

| KING   | PRESIDENT | 5000.00 |

| FORD   | ANALYST   | 3000.00 |

+--------+-----------+---------+

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

select

    deptno, min(sal)

from

    emp

where

    job = 'MANAGER'

group by

    deptno;

+--------+----------+

| deptno | min(sal) |

+--------+----------+

|     10 |  2450.00 |

|     20 |  2975.00 |

|     30 |  2850.00 |

+--------+----------+

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

select

    ename,(sal + ifnull(comm,0)) * 12 as yearsal

from

    emp

order by

    yearsal asc;

+--------+----------+

| ename  | yearsal  |

+--------+----------+

| SMITH  |  9600.00 |

| JAMES  | 11400.00 |

| ADAMS  | 13200.00 |

| MILLER | 15600.00 |

| TURNER | 18000.00 |

| WARD   | 21000.00 |

| ALLEN  | 22800.00 |

| CLARK  | 29400.00 |

| MARTIN | 31800.00 |

| BLAKE  | 34200.00 |

| JONES  | 35700.00 |

| FORD   | 36000.00 |

| SCOTT  | 36000.00 |

| KING   | 60000.00 |

+--------+----------+

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

select

    a.ename '员工',b.ename '领导'

from

    emp a

join

    emp b

on

    a.mgr = b.empno

where

    b.sal > 3000;

+-------+------+

| 员工  | 领导   |

+-------+------+

| JONES | KING |

| BLAKE | KING |

| CLARK | KING |

+-------+------+

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

select

    d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal

from

    emp e

right join

    dept d

on

    e.deptno = d.deptno

where

    d.dname like '%S%'

group by

    d.deptno,d.dname,d.loc;

+--------+------------+---------+----------------+----------+

| deptno | dname      | loc     | count(e.ename) | sumsal   |

+--------+------------+---------+----------------+----------+

|     20 | RESEARCH   | DALLAS  |              5 | 10875.00 |

|     30 | SALES      | CHICAGO |              6 |  9400.00 |

|     40 | OPERATIONS | BOSTON  |              0 |     0.00 |

+--------+------------+---------+----------------+----------+

34、给任职日期超过 30 年的员工加薪 10%.

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;