sql 数据库设计三范式 练习题

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.his