数据库范式练习

5.5视图的作用
视图可以隐藏的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD
mysql> create view myview2 as select empno a,ename b,sal c from emp_bak;
Query OK, 0 rows affected (0.01 sec)
mysql> select view2 from myview2;
ERROR 1054 (42S22): Unknown column ‘view2’ in ‘field list’

mysql> select * from myview2;
±-----±-------±--------+
| a | b | c |
±-----±-------±--------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
±-----±-------±--------+
14 rows in set (0.00 sec)

mysql> insert into myview2(a,b,c) values(…);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘…)’ at line 1

任务82：074 数据库设计三范式.flv （必须掌握）
7.1什么是设计范式？
设计表的依据。按照这个三范式设计的表不会出现数据冗余。
7.2三范式都是哪些？
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖
多对多？三张表，关系表两个外键
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键字段，不能产生传递依赖
一对多？
班级t_class
Cno(PK) cname\

学生t_student
Sno(pk) sname classno(fk)
101 张1 1
102 张2 1
103 张3 2

104 张4 2
提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度

T_student 学生表
Sno(PK) Sname
1 张三
2 李四
3 王五
T_teacher 讲师表
Tno（pk） tname
1 王老师
2 张老师
3 李老师
T_student_teacher_relation 学生讲师关系表

Id (pk) sno(fk) tno(fk)
1 ` 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3

（不符合可以分为邮箱和电话）

（符合记录）

任务83：075 表的设计经典设计方案.flv
一对一怎么设计？
一对一设计有两种方案：主键共享，外键唯一
T_user_login 用户登陆表
Id（pk） username password
1 zs 123
2 ls 456
T_user_detail用户详细信息表
Id(PK) realname tel userid(fk+unique)…
1 张三 11111111 2
2 李四 11114156 1

任务84：076 074-34道作业题.flv
重新还原数据库
mysql> drop database bjpowernode;
Query OK, 16 rows affected (0.09 sec)

mysql> create database bjpowernode;
Query OK, 1 row affected (0.00 sec)
mysql> use bjpowernode;
Database changed
mysql> source C:\Users\Administrator\Downloads\mysql资料\数据脚本\bjpowernode\bjpowernode.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Step1
mysql> select deptno,max(sal) as maxsal from emp group by deptno;
±-------±--------+
| deptno | maxsal |
±-------±--------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
±-------±--------+
Step 2:
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 e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t
-> on e.deptno=t.deptno and e.sal=t.maxsal;
±------±-------±--------+
| ename | deptno | maxsal |
±------±-------±--------+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
任务85：077 075-34道作业题.flv
2.哪些人的薪水在部门的平均薪水之上
Step1：找出每个部门的平均薪水
Select deptno,avg(sal) as avgsal from emp group by deptno;
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
±-------±------------+
3 rows in set (0.00 sec)
Step2:将以上查询结果当做t表,t和emp表连接
条件：部门编号相同，并且emp的sal 大于t表的avgsal
mysql> select e.ename,t.* from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t
-> on e.sal>t.avgsal and t.deptno=e.deptno;
±------±-------±------------+
| ename | deptno | avgsal |
±------±-------±------------+
| ALLEN | 30 | 1566.666667 |
| JONES | 20 | 2175.000000 |
| BLAKE | 30 | 1566.666667 |
| SCOTT | 20 | 2175.000000 |
| KING | 10 | 2916.666667 |
| FORD | 20 | 2175.000000 |
±------±-------±------------+
6 rows in set (0.00 sec)
select e.ename,t.,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;
mysql> select e.ename,t.,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;
±------±-------±------------±--------+
| ename | deptno | avgsal | sal |
±------±-------±------------±--------+
| ALLEN | 30 | 1566.666667 | 1600.00 |
| JONES | 20 | 2175.000000 | 2975.00 |
| BLAKE | 30 | 1566.666667 | 2850.00 |
| SCOTT | 20 | 2175.000000 | 3000.00 |
| KING | 10 | 2916.666667 | 5000.00 |
| FORD | 20 | 2175.000000 | 3000.00 |
±------±-------±------------±--------+
6 rows in set (0.00 sec)

任务86：078 076-34道作业题.flv
取得部门种（所有人的）平均的薪水等级
平均的薪水等级，先计算每一个薪水的等级，然后找出每个平均薪水的等级值
平均薪水等级:先计算平均薪水，然后找出每个平均薪水的等级值
第一步：找出每个人的薪水等级
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 order by deptno;
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 order by deptno;
±-------±--------±-------±------+
| ename | sal | deptno | grade |
±-------±--------±-------±------+
| MILLER | 1300.00 | 10 | 2 |
| CLARK | 2450.00 | 10 | 4 |
| KING | 5000.00 | 10 | 5 |
| ADAMS | 1100.00 | 20 | 1 |
| JONES | 2975.00 | 20 | 4 |
| FORD | 3000.00 | 20 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| TURNER | 1500.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| JAMES | 950.00 | 30 | 1 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
±-------±--------±-------±------+
第二步：基于以上的结果继续按照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;
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) |
±-------±-------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
±-------±-------------+
3 rows in set (0.00 sec)
任务87：079 077-34道作业题.flv
3.不准用组函数（MAX),取得最高薪水
第一种：sal 降序，limit 1
Select ename, sal from emp order by sal desc limit 1;
mysql> Select ename, sal from emp order by sal desc limit 1;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
±------±--------+
1 row in set (0.00 sec)
第二种方案：select max(sal) from emp;
mysql> select ename,sal from emp;
±-------±--------+
| ename | sal |
±-------±--------+
| SMITH | 800.00 |
| 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 |
±-------±--------+

第三种方案：表的自连接
Select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal<b.sal);
Selct xxx from a join b on a.sal>b.sal;
Step1 a表
mysql> select sal from emp;
±--------+
| 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 |
±--------+
Step2 b表
mysql> select distinct a.sal from emp a join emp b on a.sal<b.sal
-> ;
±--------+
| sal |
±--------+
| 800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
±--------+
11 rows in set (0.00 sec)
最终表
mysql> 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 |
±--------+
1 row in set (0.00 sec)

任务88：080 078-34道作业题.flv
5、取得平均薪水最高的部门的部门编号
第一种方案:
找出每个部门的平均薪水
Select deptno,avg(sal) as avgsal from emp group by deptno;
mysql> Select deptno,avg(sal) as avgsal from emp group by deptno;
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
Step 2 降序选第一个取得平均薪水最高的部门编号
（1）
mysql> Select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 10 | 2916.666667 |
±-------±------------+
（3）select max(t.avgsal) from ()t; (Select deptno,avg(sal) as avgsal from emp group by deptno)
mysql> select max(t.avgsal) from (Select deptno,avg(sal) as avgsal from emp group by deptno) t;
±--------------+
| max(t.avgsal) |
±--------------+
| 2916.666667 |
±--------------+
mysql> 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 |
±-------±------------+
1 row in set (0.00 sec)
最二步：找出以上结果avg最大的值
mysql> select max(t.avgsal) from(select deptno,avg(sal) as avgsal from emp group by deptno) t;
±--------------+
| max(t.avgsal) |
±--------------+
| 2916.666667 |
±--------------+
1 row in set (0.00 sec)
第三步：
Select deptno,avg(sal) as avgsal from
Emp group by deptno;
6.取得平均薪水最高的部门名称

mysql> select
-> deptno,avg(sal) as avgsal
-> from emp
-> group by
-> deptno
-> having avgsal=(select max(t.avgsal) from(select deptno,avg(sal) as avgsal from emp group by deptno) t );
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 10 | 2916.666667 |
±-------±------------+

任务90：082 080-34道作业题.flv
7、求平均薪水的等级最低的部门的部门名称
第一种按照部门名称分组，找出每个部门的平均薪水
平均薪水是800
平均薪水是900
那么他俩都是1级别
emp e
Deptno dept
Step1:按照部门名字进行分组，找出每个部门的平均薪水
Select deptno, avg(sal) as avgsal from emp group by deptno;
第二种：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件:
T.avgsal between a.losal and s.hisal

Select t.*, s.grade
From t
Join salgrade s
on
T.avgsal between s.losal and s.hisal;

mysql> select t.*, s.grade
-> from (select deptno,avg(sal) as avgsal from emp group by deptno) t
-> join salgrade s
-> on
-> t.avgsal between s.losal and s.hisal;
±-------±------------±------+
| deptno | avgsal | grade |
±-------±------------±------+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
±-------±------------±------+
抛开之前的，最低等级你怎么着？
平均薪水最低的对应的等级一定是最低的。
mysql> select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
±------------+
| avgsal |
±------------+
| 2916.666667 |
±------------+
1 row in set (0.00 sec)
Select grade from salgrade where () between losal and hisal;
mysql> select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) between losal and hisal;
±------+
| grade |
±------+
| 4 |
±------+
1 row in set (0.00 sec)

mysql> select t.*,s.grade
-> from
-> (select deptno,avg(sal) as avgsal from emp group by deptno) t
-> join
-> salgrade s
-> on
-> t.avgsal between s.losal and s.hisal;
±-------±------------±------+
| deptno | avgsal | grade |
±-------±------------±------+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
±-------±------------±------+
3 rows in set (0.00 sec)
任务91：083 081-34道作业题.flv

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

比“普通员工的最高薪水”还要高的一定是领导！
	没毛病！！！！

mysql> select * from salgrade;
±------±------±------+
| GRADE | LOSAL | HISAL |
±------±------±------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+
5 rows in set (0.00 sec)

mysql> select distinct mgr from emp;
±-----+
| mgr |
±-----+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
±-----+
7 rows in set (0.00 sec)
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 |
±------±--------+
任务91：083 081-34道作业题.flv
9、取得薪水最高的前五名员工
mysql> select ename,sal from emp order by sal desc limit 5;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
±------±--------+
mysql> select ename,sal from emp order by sal desc limit 5;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
±------±--------+’
10.取得薪水最高的第六到第十名员工
mysql> select ename,sal from emp order by sal desc limit 5,5;
//sal最高的6-10
±-------±--------+
| ename | sal |
±-------±--------+
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
±-------±--------+
5 rows in set (0.00 sec)
(5，5+5）

任务93：085 083-34道作业题.flv

11.取得最后入职的5员工
日期也可以降序，升序
mysql> select ename,hiredate from emp order by hiredate desc limit 5;
任务94：086 084-34道作业题.flv

12、取得每个薪水等级有多少员工
分组count
第一步：找出每个员工的薪水等级
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 |
±-------±--------±------+
第二步：继续按照grade分组统计数量
mysql> select * from salgrade;
±------±------±------+
| GRADE | LOSAL | HISAL |
±------±------±------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+
5 rows in set (0.00 sec)

mysql> select e.,d.
-> from emp e
-> right join
-> dept d
-> on e.deptno=d.deptno;
±------±-------±----------±-----±-----------±--------±--------±-------±-------±-----------±---------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
±------±-------±----------±-----±-----------±--------±--------±-------±-------±-----------±---------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
±------±-------±----------±-----±-----------±--------±--------±-------±-------±-----------±---------+
17.列出至少有 5 个员工的所有部门
mysql> select deptno
-> from emp
-> group by
-> deptno
-> having count() >=5;
±-------+
| deptno |
±-------+
| 20 |
| 30 |
±-------+
2 rows in set (0.00 sec)
mysql> select deptno from emp
-> group by
-> deptno
-> having count() >=5;
±-------+
| deptno |
±-------+
| 20 |
| 30 |
±-------+
18.列出薪金比“SMITH”多的所有员工信息
mysql> select sal from emp where ename =‘SMITH’;
±-------+
| sal |
±-------+
| 800.00 |
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 |
±-------±--------+

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

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)
任务100：092 090-34道作业题.flv

没有员工的部门，部门人数是0
mysql> select d.*,count(e.ename), avg(e.sal)
-> 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) | avg(e.sal) |
±-------±-----------±---------±---------------±------------+
| 10 | ACCOUNTING | NEW YORK | 3 | 2916.666667 |
| 20 | RESEARCH | DALLAS | 5 | 2175.000000 |
| 30 | SALES | CHICAGO | 6 | 1566.666667 |
| 40 | OPERATIONS | BOSTON | 0 | NULL |
mysql> select
-> d.deptno, count(e.ename),avg(e.sal)
-> from
-> emp e
-> right join
-> dept d
-> on
-> e.deptno=d.deptno
-> group by
-> d.deptno;
±-------±---------------±------------+
| deptno | count(e.ename) | avg(e.sal) |
±-------±---------------±------------+
| 10 | 3 | 2916.666667 |
| 20 | 5 | 2175.000000 |
| 30 | 6 | 1566.666667 |
| 40 | 0 | NULL |
mysql> select d.deptno, count(e.ename),ifnull(avg(e.sal),0)
-> from
-> emp e
-> right join
-> dept d
-> on
-> e.deptno=d.deptno
-> group by
-> d.deptno;
±-------±---------------±---------------------+
| deptno | count(e.ename) | ifnull(avg(e.sal),0) |
±-------±---------------±---------------------+
| 10 | 3 | 2916.666667 |
| 20 | 5 | 2175.000000 |
| 30 | 6 | 1566.666667 |
| 40 | 0 | 0.000000 |
±-------±---------------±---------------------+
4 rows in set (0.01 sec)
当mysql当种怎么计算两个日期的”年差”，差了多少年？
Timestampdiff(间隔类型，前一个日期，后一个日期）
Timestampdiff(year,hiredate,now())
间隔类型
Second 秒 minute 分钟 hour小时
Day天 week星期 month月 quarter 季度
Year 年
mysql> select timestampdiff(YEAR,hiredate,now()) from emp;
±-----------------------------------+
| timestampdiff(YEAR,hiredate,now()) |
±-----------------------------------+
| 40 |
| 40 |
| 40 |
| 40 |
| 40 |
| 40 |
| 40 |
| 34 |
| 40 |
| 40 |
| 34 |
| 40 |
| 40 |
| 39 |
任务101：093 091-34道作业题.flv
27列出所有员工的姓名、部门名称和工资。

Select xx
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;
111

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

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

任务103：095 093-34道作业题.flv
30、列出各个部门的 MANAGER( 领导) 的最低薪金
mysql> 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.列出所有员工的年工资, 按年薪从低到高排序
mysql> 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 | 39.6667 |
| 20 | 5 | 2175.000000 | 37.6000 |
| 30 | 6 | 1566.666667 | 40.0000 |
| 40 | 0 | 0.000000 | 0.0000 |
±-------±-------±------------±---------------+
32.列出所有员工的年工资, 按年薪从低到高排序
mysql> 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 |
±-------±---------+
31、求出员工领导的薪水超过3000的员工名称与领导
mysql> 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’字符的部门员工的工资合计、部门人数
mysql> 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%.
mysql> update emp set sal =sal * 1.1 where timestampdiff(YEAR,hiredate,now()) >30;
Query OK, 14 rows affected (0.00 sec)
Rows matched: 14 Changed: 14 Warnings: 0

收起

题目

一．

二．

三．

解

一.

二.

三