Mysql学习笔记

mysql逻辑架构

连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信
服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行
引擎层

存储引擎真正的负责了MYSQL中数据的存储和提取，服务器通过API与存储引擎进行通信

存储层

主要是将数据存储在运行于裸设备的文件系统之上，并完成于存储引擎的交互

1
2
3

select x from tableA A left join tableB B on A.key=B.key
select x from tableA A right join tableB B on A.key=B.key where A.key is NULL
select x from tableA A Full outer join tableB on A.key=B.key where A.key is Null or B.key is Bull

建表

CREATE TABLE `tbl_dept`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `deptName` VARCHAR(30) DEFAULT NULL,
  `locAdd` VARCHAR(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_emp`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `deptId` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
  KEY `fk_dept_id` (`deptId`)
  #CONSTRAINT `fk_dept_id` FOREIGN KEY (deptId) REFERENCES `tbl_dept` (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

索引：索引是帮助MYSQL高效获取数据的数据结构（排好序的快速查找数据结构）

索引分类

单值索引
唯一索引
复合索引

基本语法

创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

删除
DROP INDEX [indexName] ON mytable;

查看
SHOW INDEX FROM table_name\G

哪些情况需要创建索引

主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引

查询中与其他表关联的字段，外键关系建立索引

频繁更新的字段不适合创建索引

Where条件里用不到的字段不创建索引
哪些情况不需要创建索引

表记录太少

经常增删改的表

数据重复且分布平均的表字段
MYSQL常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候

I/O：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈：top，free，uistat和vmstat来查看系统的性能状态
explain+SQL

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

select_type
- SIMPLE：简单的select查询，不包含子查询或者UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为
- SUBQUERY：在SELECT或WHERE列表中国呢包含了子查询
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION
- UNION RESULT：从UNION表获取结果的SELECT
type

1	system>const>eq_ref>ref>range>index>ALL

possible_keys：理论上应该用到哪些索引
key：真正用到的索引
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
Extra（包含不适合在其他列中显示但十分重要的额外信息）
- Using filesort：说明mysql会对数据使用过一个外部的索引排序，而不是按照表内的索引顺序进行读取（文件内排序）
- Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。
- Using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行

索引失效（避免索引失效）

全局匹配我最爱
最佳左前缀法则
不在索引列上做任何操作，会导致索引失效转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引，减少select *
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
is null , is not null也无法使用索引
like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效
少用or，用它来连接时会索引失效
查询截取分析
- 慢查询的开启与捕获
- explain+慢SQL分析
- show profile查询SQL在mysql服务器里面的执行细节和生命周期情况
- SQL数据库服务器的参数调优
优化原则：小表驱动大表
- 当B表的数据集必须小于A表的数据集时，用in优于exists
1
select * from A where id in (select id from B)
- 当A表的数据集必须小于B表的数据集时，用exists优于in

1	select * from A where exists (select 1 from B where B.id=A.id)

批量数据脚本
- 建表
- 设置参数log_bin_trust_function_creators
- 创建函数，保证每条数据都不同
- 创建存储过程
- 调用存储过程
MYSQL的表级锁有两种模式
- 表共享读锁
- 表独占写锁
读锁会阻塞写，但是不会堵塞读，而写锁会把读和写都堵塞。
并发事物处理带来的问题
- 更新丢失：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题。
- 脏读：事务A读取到了事务B已修改但尚未提交的数据。
- 不可重复读：事务A读取到了事务B已经提交的修改数据，不符合隔离性
- 幻读：事务A读取到了事务B已提交的新增数据，不符合隔离性。
MYSQL事务隔离级别
- 读未提交（read uncommitted）
- 读已提交（read committed）
- 可重复读（repeatable read）
- 可序列化（serializable）
MYSQL复制过程分成三步
- master将change记录到二进制日志（binary log），这些记录过程叫做二进制日志事件，binary log events
- slave将master的binary log events拷贝到它的中继日志（relay log）
- slave重做中继日志中的事件，将改变应用到自己的数据库中，MYSQL复制是异步的且串行化的

MySQL题目

sql文件源码如下

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

source加载数据库表

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

首先取得每个部门的最高薪水

mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     20 | 3000.00 |
|     30 | 2850.00 |
|     10 | 5000.00 |
+--------+---------+
3 rows in set (0.00 sec)

将以上的查询结果最为一张临时表t和emp表进行连接，查询条件是t.deptno=e.deptno and t.maxsal=e.sal

mysql> 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 |
+-------+--------+---------+
4 rows in set (0.00 sec)

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

首先取得每个部门的平均薪水

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

将以上的查询结果最为一张临时表t和emp表进行连接，查询条件是t.deptno=e.deptno and e.sal>t.avgsal

mysql> select e.ename,e.sal,t.*
    -> 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;
+-------+---------+--------+-------------+
| ename | sal     | deptno | avgsal      |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 |     30 | 1566.666667 |
| JONES | 2975.00 |     20 | 2175.000000 |
| BLAKE | 2850.00 |     30 | 1566.666667 |
| SCOTT | 3000.00 |     20 | 2175.000000 |
| KING  | 5000.00 |     10 | 2916.666667 |
| FORD  | 3000.00 |     20 | 2175.000000 |
+-------+---------+--------+-------------+
6 rows in set (0.00 sec)

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

第一步先找出每个员工的薪水等级

mysql> select e.ename,e.sal,e.deptno,s.grade
    -> from emp e
    -> join
    -> salgrade s
    -> on 
    -> e.sal between s.losal and s.hisal;
+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| SMITH  |  800.00 |     20 |     1 |
| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| JONES  | 2975.00 |     20 |     4 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| CLARK  | 2450.00 |     10 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| KING   | 5000.00 |     10 |     5 |
| TURNER | 1500.00 |     30 |     3 |
| ADAMS  | 1100.00 |     20 |     1 |
| JAMES  |  950.00 |     30 |     1 |
| FORD   | 3000.00 |     20 |     4 |
| MILLER | 1300.00 |     10 |     2 |
+--------+---------+--------+-------+
14 rows in set (0.00 sec)

基于上面的表按照部门编号分组之后求grade的平均值

mysql> select e.deptno,avg(s.grade)
    -> from emp e
    -> join
    -> salgrade s
    -> on e.sal between s.losal and s.hisal
    -> group by
    -> e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

不使用max函数，取得最高薪水

降序之后limit

mysql> select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)

表的自连接

mysql> select sal from emp where sal not in (select distinct a.sal from emp a jo
in emp b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

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

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

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

第二步找出薪水最高的部门编号

mysql> select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

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

mysql> 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 |
+------------+-------------+
1 row in set (0.00 sec)

找出平均薪水的等级最低的部门的部门名称

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

mysql> select  d.dname,avg(e.sal) from dept d join emp e on e.deptno=d.deptno group by d.dname;
+------------+-------------+
| dname      | avg(e.sal)  |
+------------+-------------+
| RESEARCH   | 2175.000000 |
| SALES      | 1566.666667 |
| ACCOUNTING | 2916.666667 |
+------------+-------------+
3 rows in set (0.00 sec)

第二步，将上述表和salgrade表连接找出每个部门的平均薪水的等级

mysql> select t.dname,s.grade,t.avgsal
    -> from salgrade s
    -> join
    -> (select d.dname,avg(e.sal) as avgsal from dept d join emp e on e.deptno=d.deptno group by d.dname) t
    -> on t.avgsal between s.losal and s.hisal;
+------------+-------+-------------+
| dname      | grade | avgsal      |
+------------+-------+-------------+
| RESEARCH   |     4 | 2175.000000 |
| SALES      |     3 | 1566.666667 |
| ACCOUNTING |     4 | 2916.666667 |
+------------+-------+-------------+
3 rows in set (0.00 sec)

第三步，找出最低等级

mysql> 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 |
+-------+
1 row in set (0.00 sec)

第四步，给第二步得到的表加一个grade=3的限制条件

mysql> select t.dname,s.grade,t.avgsal
    -> from salgrade s
    -> join
    -> (select d.dname,avg(e.sal) as avgsal from dept d join emp e on e.deptno=d.deptno group by d.dname) t
    -> 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 | grade | avgsal      |
+-------+-------+-------------+
| SALES |     3 | 1566.666667 |
+-------+-------+-------------+
1 row in set (0.00 sec)

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

第一步，找出员工代码在mgr字段上出现的，排除NULL

mysql> select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)

第二步，找出普通员工的最高薪水

mysql> select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+
1 row in set (0.00 sec)

第三步，找出薪资高于1600的

mysql> 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 |
+-------+---------+
6 rows in set (0.00 sec)

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

第一步，获取每个员工的薪水等级

mysql> select e.ename,e.sal,s.grade
    -> from emp e
    -> join 
    -> salgrade s
    -> on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

根据上述表，对等级进行分组，并统计每个分组的人数

mysql> select t.grade,count(t.grade)
    -> from 
    -> (select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
    -> group by t.grade;
+-------+----------------+
| grade | count(t.grade) |
+-------+----------------+
|     1 |              3 |
|     3 |              2 |
|     2 |              3 |
|     4 |              5 |
|     5 |              1 |
+-------+----------------+
5 rows in set (0.00 sec)

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

使用左外连接

mysql> 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  |
+--------+--------+
14 rows in set (0.00 sec)

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

第一步，找出受雇日期早于其直接上级的所有员工和领导

mysql> select a.ename,a.hiredate,b.ename,b.hiredate
    -> from emp a
    -> left join emp b
    -> on a.mgr = b.empno
    -> where a.hiredate < b.hiredate;
+-------+------------+-------+------------+
| ename | hiredate   | ename | hiredate   |
+-------+------------+-------+------------+
| SMITH | 1980-12-17 | FORD  | 1981-12-03 |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 |
| WARD  | 1981-02-22 | BLAKE | 1981-05-01 |
| JONES | 1981-04-02 | KING  | 1981-11-17 |
| BLAKE | 1981-05-01 | KING  | 1981-11-17 |
| CLARK | 1981-06-09 | KING  | 1981-11-17 |
+-------+------------+-------+------------+
6 rows in set (0.00 sec)

第二步，找出员工的编号和部门编号

mysql> select a.ename,a.empno,a.deptno,a.hiredate,b.ename,b.hiredate
    -> from emp a
    -> left join emp b
    -> on a.mgr=b.empno
    -> where a.hiredate<b.hiredate;
+-------+-------+--------+------------+-------+------------+
| ename | empno | deptno | hiredate   | ename | hiredate   |
+-------+-------+--------+------------+-------+------------+
| SMITH |  7369 |     20 | 1980-12-17 | FORD  | 1981-12-03 |
| ALLEN |  7499 |     30 | 1981-02-20 | BLAKE | 1981-05-01 |
| WARD  |  7521 |     30 | 1981-02-22 | BLAKE | 1981-05-01 |
| JONES |  7566 |     20 | 1981-04-02 | KING  | 1981-11-17 |
| BLAKE |  7698 |     30 | 1981-05-01 | KING  | 1981-11-17 |
| CLARK |  7782 |     10 | 1981-06-09 | KING  | 1981-11-17 |
+-------+-------+--------+------------+-------+------------+
6 rows in set (0.00 sec)

连接部门表根据部门编号找出员工所在部门

mysql> select a.ename,a.empno,d.dname
    -> from emp a 
    -> left join emp b
    -> on a.mgr=b.empno
    -> join dept d
    -> on a.deptno = d.deptno
    -> where a.hiredate<b.hiredate;
+-------+-------+------------+
| ename | empno | dname      |
+-------+-------+------------+
| SMITH |  7369 | RESEARCH   |
| ALLEN |  7499 | SALES      |
| WARD  |  7521 | SALES      |
| JONES |  7566 | RESEARCH   |
| BLAKE |  7698 | SALES      |
| CLARK |  7782 | ACCOUNTING |
+-------+-------+------------+
6 rows in set (0.00 sec)

列出部门名称和这些部门的员工信息

使用外连接

mysql> 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      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
15 rows in set (0.00 sec)

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

第一步，按照部门编号进行分组计数

mysql> select count(deptno) from emp group by deptno;
+---------------+
| count(deptno) |
+---------------+
|             5 |
|             6 |
|             3 |
+---------------+
3 rows in set (0.00 sec)

连接dept表，列出部门名称

mysql> select d.dname,count(e.deptno) from emp e join dept d on e.deptno=d.deptno  group by e.deptno;
+------------+-----------------+
| dname      | count(e.deptno) |
+------------+-----------------+
| RESEARCH   |               5 |
| SALES      |               6 |
| ACCOUNTING |               3 |
+------------+-----------------+
3 rows in set (0.00 sec)

使用having找出至少有5个员工的部门

mysql> select d.dname,count(e.deptno)
    -> from emp e
    -> join dept d on e.deptno=d.deptno
    -> group by e.deptno
    -> having count(e.deptno)>=5;
+----------+-----------------+
| dname    | count(e.deptno) |
+----------+-----------------+
| RESEARCH |               5 |
| SALES    |               6 |
+----------+-----------------+
2 rows in set (0.00 sec)

列出薪资比’SMITH’多的所有员工信息

mysql> 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 |
+--------+---------+
13 rows in set (0.01 sec)

列出所有’CLERK’的姓名及其部门名称，部门的人数

先找出所有的’CLERK’

mysql> select ename,job,deptno from emp where job = 'clerk';
+--------+-------+--------+
| ename  | job   | deptno |
+--------+-------+--------+
| SMITH  | CLERK |     20 |
| ADAMS  | CLERK |     20 |
| JAMES  | CLERK |     30 |
| MILLER | CLERK |     10 |
+--------+-------+--------+

连接dept表找出所有’CLERK’的部门名称

mysql> select e.ename,e.job,d.dname,e.deptno
    -> from emp e
    -> join dept d
    -> on d.deptno=e.deptno
    -> where e.job='clerk';
+--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
| MILLER | CLERK | ACCOUNTING |     10 |
+--------+-------+------------+--------+
4 rows in set (0.00 sec)

找出每个部门的人数

mysql> select deptno,count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
|     10 |        3 |
+--------+----------+
3 rows in set (0.00 sec)

使用这张表和上一张表做连接

mysql> select t1.*,t2.deptcount
    -> from (select e.ename,e.job,d.dname,e.deptno from emp e join dept d on d.deptno=e.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 |
+--------+-------+------------+--------+-----------+
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
+--------+-------+------------+--------+-----------+
4 rows in set (0.00 sec)

列出最低薪资大于1500的各种工作及从事此工作的全部雇员人数

按照工作岗位分组并计数，使用having统计最低薪资大于1500的人

mysql> select job,count(*) from emp group by job having min(sal) > 1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| MANAGER   |        3 |
| ANALYST   |        2 |
| PRESIDENT |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

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

mysql> 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 |
+--------+------------+--------+-------+
| FORD   | RESEARCH   | JONES  |     4 |
| SCOTT  | RESEARCH   | JONES  |     4 |
| CLARK  | ACCOUNTING | KING   |     4 |
| BLAKE  | SALES      | KING   |     4 |
| JONES  | RESEARCH   | KING   |     4 |
| KING   | ACCOUNTING | NULL   |     5 |
+--------+------------+--------+-------+
6 rows in set (0.00 sec)

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

第一步，找出’SCOTT’从事的工作

mysql> select job from emp where ename = 'scott';
+---------+
| job     |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)

第二步，连接dept表，找出和’SCOTT’从事相同工作的人和部门名称

mysql> select e.ename,d.dname
    -> from emp e
    -> join dept d
    -> on e.deptno = d.deptno
    -> where
    -> e.job = (select job from emp where ename = 'scott')
    -> and e.ename != 'scott';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

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

第一步，找出部门30的所有薪资

mysql> select distinct sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
5 rows in set (0.00 sec)

第二步，找出其他员工的姓名和薪资

mysql> select distinct sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
5 rows in set (0.00 sec)

mysql> select ename,sal 
    -> from emp
    -> where sal in(select distinct sal from emp where deptno = 30)
    -> and deptno <> 30;
Empty set (0.00 sec)

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

mysql> select d.deptno,count(e.ename),ifnull(avg(e.sal),0) '平均薪资',avg(TimeStampDiff(year,hiredate,now())) '服务期限'
    -> from emp e
    -> right join dept d
    -> on e.deptno = d.deptno
    -> group by d.deptno;
+--------+----------------+--------------+--------------+
| deptno | count(e.ename) | 平均薪资     | 服务期限       |
+--------+----------------+--------------+--------------+
|     10 |              3 |  2916.666667 |      39.6667 |
|     20 |              5 |  2175.000000 |      37.4000 |
|     30 |              6 |  1566.666667 |      39.8333 |
|     40 |              0 |     0.000000 |         NULL |
+--------+----------------+--------------+--------------+
4 rows in set (0.00 sec)

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

mysql> select e.ename,l.ename,l.sal
    -> from emp e
    -> left join emp l
    -> on e.mgr = l.empno
    -> where l.sal>3000;
+-------+-------+---------+
| ename | ename | sal     |
+-------+-------+---------+
| JONES | KING  | 5000.00 |
| BLAKE | KING  | 5000.00 |
| CLARK | KING  | 5000.00 |
+-------+-------+---------+
3 rows in set (0.00 sec)

求部门名称中，带’S’字符的部门员工的工资合计、部门人数

mysql> select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0)
    -> 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) | ifnull(sum(e.sal),0) |
+--------+------------+---------+----------------+----------------------+
|     20 | RESEARCH   | DALLAS  |              5 |             10875.00 |
|     30 | SALES      | CHICAGO |              6 |              9400.00 |
|     40 | OPERATIONS | BOSTON  |              0 |                 0.00 |
+--------+------------+---------+----------------+----------------------+
3 rows in set (0.00 sec)