9.16 [MySQL] SQL约束与策略.多表操作.多表查询

SQL约束与策略

主键约束

添加主键约束

删除主键约束

非空约束

唯一约束

添加唯一约束

删除唯一约束

外键(多表查询中使用)

外键约束

数据准备：
create database day02_2;
use day02_2;
#创建分类表：
CREATE TABLE category(
	cid varchar(32) PRIMARY KEY,#主键ID
	cname VARCHAR(20)#分类名称
);
INSERT INTO category VALUES('c001','电脑办公');
INSERT INTO category VALUES('c002','服装');
#创建商品表：
CREATE TABLE product(
	pid INT PRIMARY KEY,#主键ID
	pname VARCHAR(20),#商品名称
	price DOUBLE,#商品价格
	category_cid VARCHAR(32)#外键
);

#导入数据
INSERT INTO product(pid,pname,price,category_cid) VALUES(1,'联想电脑',5000,'c001');
INSERT INTO product(pid,pname,price,category_cid) VALUES(2,'海尔电脑',3000,'c001');
INSERT INTO product(pid,pname,price,category_cid) VALUES(3,'雷神电脑',5000,'c001');

INSERT INTO product(pid,pname,price,category_cid) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(7,'劲霸',2000,'c002'); 

为了保证数据完整，需要添加外键约束

删除外键约束

自动增长策略

多表操作

实际开发中，一个项目通常需要很多张表才能完成。
例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

1. 已知：
   我们是有一张商品表（product）
   
   
2. 需求：
   现在我们需要修改分类名称，将所有的“电脑办公” 修改为 “笔记本电脑”
   Update product set category_name = ‘笔记本电脑’ where category_name = ‘电脑办公’
   修改了3行
3. 需求变型：
   假如我这个商品表里有十几万条类别为电脑办公的记录
   修改了十几万条
   会导致数据库执行效率变慢，甚至造成数据库宕机
4. 解决：
   为了数据的使用和维护更为方便，我们将单表数据划分为多表。
   商品数据保存在商品表中，
   分类数据保存在分类表中。
   
   
   现在我们需要修改分类名称，将所有的“电脑办公” 修改为 “笔记本电脑”
   Update category set cname = “笔记本电脑” where cname = ‘电脑办公’;
   此时仅修改一条
   外键：category_cid

多表关系

通过外键，我们可以确定两张表的数据关系。

一对多

常见实例：商品类别和商品、班级和学生、国家和人民

多对多

常见实例：老师和学生、演员和角色

一对一

5. 在实际的开发中应用不多.因为一对一可以合成一张表。例如：员工表和身份证表、丈夫和妻子表
6. 建表方式：
7. 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。

多表查询

同时查询多张表获取到需要的数据 比如：我们想查询到开发部有多少人，需要将部门表和员工表同时进行查询

多表查询的分类

多表查询的规律：
1.确定查询的数据涉及到几张表
2.查询的条件是什么
3.查询哪些字段

笛卡尔积现象

左表中的数据和右表中的数据一一相连(相乘)

如何清除笛卡尔积

Where 条件过滤

内连接

外连接

子查询

概述：将上一条SELECT语句结果作为另一条SELECT语法一部分（查询条件，查询结果，表） SELECT 查询字段 FROM 表WHERE 查询条件;
SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);

子查询的三种情况

子查询的结果是一个值的时候

子查询结果是单列多行的时候

子查询的结果是多行多列

子查询总结

子查询结果只要是 单列 ，肯定在 WHERE 后面作为 条件
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
子查询结果只要是 多列 ，肯定在 FROM 后面作为 表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件

简单的多表查询练习

表结构：
CREATE DATABASE test;
USE test;

##部门表
#DROP IF EXISTS TABLE DEPT;
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,##部门编号
DNAME VARCHAR(14) , ##部门名称
LOC VARCHAR(13) ##部门地址
) ;

INSERT INTO DEPT VALUES (10,‘ACCOUNTING’,‘NEW YORK’);
INSERT INTO DEPT VALUES (20,‘RESEARCH’,‘DALLAS’);
INSERT INTO DEPT VALUES (30,‘SALES’,‘CHICAGO’);
INSERT INTO DEPT VALUES (40,‘OPERATIONS’,‘BOSTON’);

##员工表
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
EMPNO int PRIMARY KEY, #员工编号
ENAME VARCHAR(10), #员工姓名
JOB VARCHAR(9), #员工工作
MGR int, #员工直属领导编号
HIREDATE DATE, #入职时间
SAL double, #工资
COMM double, #奖金
DEPTNO int #对应dept表的外键
);
添加 部门 和 员工 之间的主外键关系
ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO);

INSERT INTO EMP VALUES(7369,‘SMITH’,‘CLERK’,7902,“1980-12-17”,800,NULL,20);
INSERT INTO EMP VALUES(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30);
INSERT INTO EMP VALUES(7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30);
INSERT INTO EMP VALUES(7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,NULL,20);
INSERT INTO EMP VALUES(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30);
INSERT INTO EMP VALUES(7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,NULL,30);
INSERT INTO EMP VALUES(7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450,NULL,10);
INSERT INTO EMP VALUES(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-07-03’,3000,NULL,20);
INSERT INTO EMP VALUES(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000,NULL,10);
INSERT INTO EMP VALUES(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30);
INSERT INTO EMP VALUES(7876,‘ADAMS’,‘CLERK’,7788,‘1987-07-13’,1100,NULL,20);
INSERT INTO EMP VALUES(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,NULL,30);
INSERT INTO EMP VALUES(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,NULL,20);
INSERT INTO EMP VALUES(7934,‘MILLER’,‘CLERK’,7782,‘1981-01-23’,1300,NULL,10);

1. 返回员工的部门名、部门号。
   SELECT d.DNAME,e.DEPTNO FROM dept d,emp e WHERE d.DEPTNO=e.DEPTNO;
   
2. 工资水平多于smith的员工信息。
   SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename=‘smith’);
   
3. 返回所有员工和员工所属经理的姓名。
   SELECT e.ENAME,d.ENAME,d.JOB FROM emp e,emp d WHERE e.MGR=d.EMPNO;
   
4. 返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
   SELECT e.ENAME,d.ENAME FROM emp e,emp d WHERE e.MGR=d.EMPNO
   AND e.HIREDATE<d.HIREDATE;
   
5. 返回员工姓名及其所在的部门名称。
   SELECT e.ename,d.DNAME FROM emp e,dept d WHERE d.DEPTNO=e.DEPTNO
   
6. 返回从事clerk工作的员工姓名和所在部门名称。
   SELECT e.ename,d.DNAME FROM emp e,dept d WHERE d.DEPTNO=e.DEPTNO AND e.JOB=‘clerk’;
   
7. 返回部门号及其本部门的最低工资。
   SELECT d.DEPTNO,MIN(e.SAL) FROM dept d,emp e WHERE d.DEPTNO=e.DEPTNO GROUP BY d.DEPTNO
   
8. 返回销售部(sales)所有员工的姓名。
   SELECT e.ENAME FROM emp e WHERE e.DEPTNO IN (SELECT deptno FROM dept WHERE dname=‘sales’);
   
9. 返回工资水平多于平均工资的员工。
   SELECT * FROM emp e WHERE e.SAL>(SELECT AVG(sal) FROM emp);
   
10. 返回与SCOTT从事相同工作的员工。
    SELECT * FROM emp e WHERE e.JOB IN(SELECT job FROM emp WHERE ename=‘scott’);
    
11. 返回与30部门员工工资水平相同的员工姓名与工资。
    SELECT e.ENAME,e.SAL FROM emp e WHERE e.SAL IN(SELECT sal FROM emp WHERE deptno=30);
    
12. 返回工资高于30部门所有员工最高工资水平的员工信息。
    SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
    
13. 返回部门号、部门名、部门所在位置及其每个部门的员工总数。
    SELECT d.*,COUNT(e.ENAME) FROM emp e,dept d WHERE e.DEPTNO=d.DEPTNO GROUP BY e.DEPTNO;
    
14. 返回员工的姓名、所在部门名及其工资。
    SELECT e.ENAME,d.DNAME,e.SAL FROM emp e,dept d WHERE e.DEPTNO=d.DEPTNO;
    
15. 返回员工的详细信息。(包括部门名)
    SELECT e.*,d.dname FROM emp e,dept d;