实际开发中,一个项目通常需要很多张表才能完成。例如:一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系,接下来我们将在单表的基础上,一起学习多表方面的知识。
表与表之间的关系
有3类表关系:一对多(多对一)、多对多、一对一(了解)
- 一对多关系:
- 常见实例:学生和考试成绩(画图),客户和订单,分类和商品,部门和员工.
- 一对多建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键.
- 多对多关系:
- 常见实例:学生和教师,商品和订单(画图),学生和课程、用户和角色
- 多对多关系建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键.
- 一对一关系:(了解)
- 在实际的开发中应用不多,比如QQ号码,和QQ用户信息
- 因为一对一可以创建成一张表.
- 两种建表原则:
- 外键唯一:主表的主键和从表的外键(唯一),形成主外键关系,外键唯一unique。
- 外键是主键:主表的主键和从表的主键,形成主外键关系。
- 在实际的开发中应用不多,比如QQ号码,和QQ用户信息
外键约束
现在我们有两张表“分类表”和“商品表”,为了表明商品属于哪个分类,通常情况下,我们将在商品表上添加一列,用于存放分类cid的信息,此列称为:外键
此时“分类表category”称为:主表,“cid”我们称为主键。“商品表products”称为:从表,category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系,呈现就是一对多关系。
外键特点:
-
-
- 从表外键的值是对主表主键的引用。
- 从表外键类型,必须与主表主键类型一致。
-
- 声明外键约束
语法:alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
[外键名称] 用于删除外键约束的,一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称
- 使用外键目的:
- 保证数据完整性
一对多操作
- category分类表,为一方,也就是主表,必须提供主键cid
- products商品表,为多方,也就是从表,必须提供外键category_id
实现:分类和商品
###创建分类表
create table category(
cid int(32) PRIMARY KEY ,
cname varchar(100) #分类名称
);
# 商品表
CREATE TABLE products (
pid int PRIMARY KEY ,
pname VARCHAR(40) ,
price DOUBLE ,
category_id int
);
#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);
操作
#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES(1,'服装');
#2 向商品表添加普通数据,没有外键数据,默认为null
INSERT INTO products (pid,pname) VALUES(1,'商品名称');
#3 向商品表添加普通数据,含有外键信息(数据存放在)
INSERT INTO products (pid ,pname ,category_id) VALUES(2,'商品名称2', 1);
#4 向商品表添加普通数据,含有外键信息(数据不存在) -- 不能异常
INSERT INTO products (pid ,pname ,category_id) VALUES(3,'商品名称2',9);
#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 1;
一对多建表: 省和市
/*
一对多建表: 省和市
建表原则: 主表和从表,从表的外键引用了主表的主键
*/
# 创建省表
CREATE TABLE province(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
pdesc VARCHAR(50)
);
#创建市表
CREATE TABLE city(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
province_id INT
);
#添加主外键关系
ALTER TABLE city ADD CONSTRAINT city_province_fk
FOREIGN KEY (province_id) REFERENCES province (pid);
#测试数据
INSERT INTO province (pname,pdesc) VALUES ('黑龙江省','很冷'),('广东省','服务业发达'),('新疆省','热');
INSERT INTO city (cname,province_id) VALUES('齐齐哈尔',1);
INSERT INTO city (cname,province_id) VALUES('东莞市',2);
#插入报错,province_id的值在主表中不存在
INSERT INTO city (cname,province_id) VALUES('东京',4);
#插入ok
INSERT INTO city (cname,province_id) VALUES('东莞市',2);
#删除报错,pid的值在从表中有值,不能直接删除。
DELETE FROM province WHERE pid = 2;
#删除方法:先删除city,再删除province
DELETE FROM city WHERE province_id = 2;
DELETE FROM province WHERE pid = 2;
多对多
- 商品和订单多对多关系,将拆分成两个一对多。
- products商品表,为其中一个一对多的主表,需要提供主键pid
- orders 订单表,为另一个一对多的主表,需要提供主键oid
- orderitem中间表,为另外添加的第三张表,需要提供两个外键oid和pid
实现:订单和商品
### 商品表[已存在]
### 订单表
create table orders(
oid int PRIMARY KEY ,
totalprice double #总计
);
### 订单项表
create table orderitem(
oid int,-- 订单id
pid int(50)-- 商品id
);
###---- 订单表和订单项表的主外键关系
alter table orderitem add constraint orderitem_orders_fk foreign key (oid) references orders(oid);
###---- 商品表和订单项表的主外键关系
alter table orderitem add constraint orderitem_product_fk foreign key (pid) references products(pid);
操作
#1 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES(3,'商品名称');
#2 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES(1,998);
INSERT INTO orders (oid ,totalprice) VALUES(2,100);
#3向中间表添加数据(数据存在)
INSERT INTO orderitem(pid,oid) VALUES(1, 1);
INSERT INTO orderitem(pid,oid) VALUES(1, 2);
INSERT INTO orderitem(pid,oid) VALUES(2,2);
#4删除中间表的数据
DELETE FROM orderitem WHERE pid=2 AND oid = 2;
#5向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderitem(pid,oid) VALUES(2, 3);
#6删除商品表的数据 -- 执行异常
DELETE FROM products WHERE pid = 1;
多对多建表:学生和课程
/*
多对多建表:
学生和课程
建表原则:
一张学生表,一张课程表
一张中间表,至少两个字段
*/
# 学生表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
sage INT
);
#课程表
CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20)
);
#中间表
CREATE TABLE student_course_item(
student_id INT,
course_id INT
);
#插入学生
INSERT INTO student(sname,sage) VALUES ('张三',18),('李四',20),('王五','30');
# 插入课程
INSERT INTO course (cname) VALUES ('数学'),('语法'),('java');
INSERT INTO student_course_item VALUES (1,2);
INSERT INTO student_course_item VALUES (2,3);
INSERT INTO student_course_item VALUES (2,4);
DELETE FROM student_course_item;
#添加外键约束
ALTER TABLE student_course_item ADD CONSTRAINT item_student_fk
FOREIGN KEY (student_id) REFERENCES student (sid);
ALTER TABLE student_course_item ADD CONSTRAINT item_course_fk
FOREIGN KEY (course_id) REFERENCES course (cid);
#删除外键约束
ALTER TABLE student_course_item DROP FOREIGN KEY item_student_fk;
ALTER TABLE student_course_item DROP FOREIGN KEY item_course_fk;
多表查询
CREATE TABLE category (
cid int PRIMARY KEY ,
cname VARCHAR(50)
);
CREATE TABLE products(
pid int PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为:1表示上架、0表示下架
category_id int,
CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
#分类
INSERT INTO category(cid,cname) VALUES('1','家电');
INSERT INTO category(cid,cname) VALUES('2','服饰');
INSERT INTO category(cid,cname) VALUES('3','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('1','联想',5000,'1',1);
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('2','海尔',3000,'1',1);
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('3','雷神',5000,'1',1);
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('4','JACK JONES',800,'1',2);
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('5','真维斯',200,'1',2);
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('6','花花公子',440,'1',2);
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('7','劲霸',2000,'1',2);
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('8','香奈儿',800,'1',3);
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('9','相宜本草',200,'1',3);
多表查询有三种
1.交叉查询(基本不会使用-得到的是两个表的乘积) [了解]
2.内连接查询(让查询有意义!!!): inner join
3.外连接查询(以一个表为基准,连接查询另外一个表): outer join
/*
多表查询有三种:
1.交叉查询
语法:
select * from 表1,表2;
总字段 = 表1字段+表2字段
总记录数= 表1记录数*表2记录数
但是 交叉查询是没意义的,实际上是一个矩阵(坐标)的笛卡尔
2.内连接查询(让查询有意义!!!): inner join
隐式内连接查询:不写关键字 inner join
select * from 主表,从表 where 主键.主键 = 从表.外键
显示内连接查询:写上关键字 inner join
select * from 主表 inner join 从表 on 主键.主键 = 从表.外键
3.外连接查询: outer join
(ps:两表对换位置,显示左右外连接)
左外连接(左表的数据全部查出现,不管是否null): left outer join
左外连接 以左表为准,左表中的数据必须每条都有
右外连接: right outer join
*/
#交叉查询
SELECT * FROM category,products;
#查出所有的记录数=category数 * products数
SELECT COUNT(*) FROM category,products;
#隐式内连接查询
SELECT * FROM category,products WHERE category.cid = products.category_id;
#显示内连接
SELECT * FROM category INNER JOIN products ON category.cid = products.category_id;
#左外连接: left outer join
SELECT * FROM category LEFT OUTER JOIN products ON category.cid = products.category_id;
#右外连接: right outer join
SELECT * FROM category RIGHT OUTER JOIN products ON category.cid = products.category_id;
#外连接
SELECT * FROM products RIGHT OUTER JOIN category ON category.cid = products.category_id;
# 练习
#1.查询哪些分类的商品已经上架
# 隐式内连接
SELECT DISTINCT c.cname FROM category c,products p WHERE c.cid = p.category_id AND flag = 1;
#显示内连接
SELECT DISTINCT c.cname FROM category c INNER JOIN products p ON c.cid = p.category_id AND flag = 1;
#2.查询所有分类的商品个数
#1隐式内连接
SELECT c.cname,c.cid FROM products p,category c WHERE p.category_id=c.cid;
#2查询所有商品个数
SELECT c.cname,c.cid,COUNT(*) FROM products p,category c WHERE p.category_id=c.cid;
#1查询所有分类的商品个数
SELECT c.cname,c.cid,COUNT(*) FROM products p,category c WHERE p.category_id=c.cid GROUP BY p.category_id;
SELECT c.cname,COUNT(*) FROM products p,category c WHERE p.category_id=c.cid GROUP BY c.cid;
子查询:
一条select语句结果作为另一条select语法一部分(查询条件,查询结果,表等)。
select ....查询字段 ... from ...表.. where ... 查询条件
#3 子查询, 查询“化妆品”分类商品详情
#隐式内连接
SELECT * FROM products p , category c
WHERE p.category_id=c.cid AND c.cname = '化妆品';
#子查询
##作为查询条件
SELECT * FROM products p
WHERE p.category_id =
(
SELECT c.cid FROM category c
WHERE c.cname='化妆品'
);
##作为另一张表
SELECT * FROM products p ,
(SELECT * FROM category WHERE cname='化妆品') c
WHERE p.category_id = c.cid;
#查询“化妆品”和“家电”两个分类商品详情
SELECT * FROM products p WHERE p.category_id IN
(SELECT c.cid FROM category c WHERE c.cname='化妆品' OR c.cname='家电');
子查询练习
/*
子查询:
一个查询的结果 作为另外一个查询的一部分
*/
# 查询“化妆品”分类商品详情
SELECT * FROM products WHERE category_id =
(SELECT cid FROM category WHERE cname = '化妆品');
SELECT p.* FROM products p,category c WHERE p.category_id = c.cid AND c.cname = '化妆品';
#查询“化妆品”和“家电”两个分类商品详情
SELECT * FROM products WHERE category_id IN
(SELECT cid FROM category WHERE cname IN ('化妆品','家电'));
SELECT p.* FROM products p,category c WHERE p.category_id = c.cid AND c.cname IN ('化妆品','家电');
#查询“化妆品”和“家电”两个分类商品详情
SELECT p.*,c.cname FROM products p,category c WHERE p.category_id = c.cid AND c.cname IN ('化妆品','家电');
查询练习
-- 员工表
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
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-04-19',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-05-23',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,'1982-01-23',1300,NULL,10);
-- 部门表
CREATE TABLE dept(
deptno INT,
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');
/*
课后练习:
*/
-- 1. 查询工资大于1200的员工姓名和工资
SELECT ename,sal FROM emp WHERE sal > 1200;
-- 2. 查询员工号为7654的员工的姓名和部门号
SELECT ename,deptno FROM emp WHERE empno = 7654;
-- 3. 选择工资不在1000到2000的员工的姓名和工资
SELECT ename,sal FROM emp WHERE sal < 1000 OR sal > 2000;
SELECT ename,sal FROM emp WHERE sal NOT BETWEEN 1000 AND 2000;
-- 4. 选择雇用时间在1981-05-01到1981-10-01之间的员工姓名,job和雇用时间
SELECT ename,job,hiredate FROM emp WHERE hiredate BETWEEN '1981-05-01' AND '1981-10-01';
-- 5. 选择在20或10号部门工作的员工姓名和部门号
SELECT ename,deptno FROM emp WHERE deptno IN (20,10);
-- 6. 选择在1981年雇用的员工的姓名和雇用时间
SELECT ename,hiredate FROM emp WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';
SELECT ename,hiredate FROM emp WHERE hiredate LIKE '1981%';
-- 7. 选择公司中没有管理者的员工姓名及job
SELECT ename,job FROM emp WHERE mgr IS NULL;
-- 8. 选择公司中有奖金的员工姓名,工资和奖金
SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL;
-- 9. 选择员工姓名的第三个字母是a的员工姓名
SELECT ename FROM emp WHERE ename LIKE '__a%';
-- 10. 选择姓名中有字母a和e的员工姓名
SELECT ename FROM emp WHERE ename LIKE '%a%e%' OR ename LIKE '%e%a%';
SELECT ename FROM emp WHERE ename LIKE '%a%' AND ename LIKE '%e%';
-- 12. 查询员工号,姓名,工资,以及工资提高百分之20%后的结果(请使用别名new salary)
SELECT empno,ename,sal,sal * 1.2 `new salary` FROM emp;
-- 13. 将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT ename,LENGTH(ename) FROM emp ORDER BY ename;
-- 14. 查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(sal), MIN(sal), AVG(sal), SUM(sal) FROM emp;
-- 15. 查询各job的员工工资的最大值,最小值,平均值,总和
SELECT job, MAX(sal), MIN(sal), AVG(sal), SUM(sal) FROM emp GROUP BY job;
-- 16. 查询具有各个job的员工人数
SELECT job,COUNT(*) FROM emp GROUP BY job;
-- 17. 查询员工最高工资和最低工资的差距(请用别名 DIFFERENCE)
SELECT MAX(sal)-MIN(sal) DIFFERENCE FROM emp;
-- 18. 查询各个管理者手下员工的最低工资,其中最低工资不能低于1000,没有管理者的员工不计算在内
SELECT mgr,MIN(sal) AS minsal FROM emp GROUP BY mgr HAVING minsal > 1000 AND mgr IS NOT NULL;
-- 19. 查询所有部门的名字,loc,员工数量和工资平均值
SELECT dept.dname,dept.loc,COUNT(emp.empno),AVG(emp.sal) FROM dept,emp WHERE emp.deptno = dept.deptno GROUP BY emp.deptno;
-- 1、按员工编号升序排列不在10号部门工作的员工信息
SELECT * FROM emp WHERE deptno !=10 ORDER BY empno;
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息,按年薪降序排列
SELECT * FROM emp WHERE ename NOT LIKE '_A%' AND sal > 800 ORDER BY sal DESC;
-- 3、求每个部门的平均薪水
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
-- 4、求各个部门的最高薪水
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;
-- 5、求每个部门每个岗位的最高薪水
SELECT deptno,job, MAX(sal) FROM emp GROUP BY deptno,job;
-- 6、求平均薪水大于2000的部门编号
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;
-- 8、求最高薪水的员工信息
SELECT * FROM emp WHERE sal =
(SELECT MAX(sal) FROM emp);
-- 9、求多于平均薪水的员工信息
SELECT * FROM emp WHERE sal >
(SELECT AVG(sal) FROM emp);
-- 11、求各个部门薪水最高的员工信息
#每个部门的最高薪水
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;
SELECT * FROM emp WHERE emp.sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);
SELECT * FROM emp,(SELECT deptno, MAX(sal) maxsal FROM emp GROUP BY deptno) d
WHERE emp.deptno = d.deptno AND emp.sal = d.maxsal;
===========================================================
-- 1. 查询和SCOTT相同部门的员工姓名和雇用日期
SELECT ename,hiredate,deptno FROM emp WHERE deptno =
(SELECT deptno FROM emp WHERE ename = 'SCOTT');
-- 2. 查询工资比公司平均工资高的员工的员工号,姓名和工资。
SELECT empno,ename,sal FROM emp WHERE sal >
(SELECT AVG(sal) FROM emp);
-- 3.(难度较高,可删除)查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
SELECT empno,ename,sal FROM emp,
(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) d
WHERE emp.deptno = d.deptno AND emp.sal > d.avgsal;
-- 4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT empno,ename FROM emp WHERE deptno IN
(SELECT deptno FROM emp WHERE ename LIKE '%u%')
-- 6. 查询管理者是KING的员工姓名和工资
SELECT ename,sal FROM emp WHERE mgr =
(SELECT empno FROM emp WHERE ename = 'KING');
练习:
-- 11、求各个部门薪水最高的员工信息
#每个部门的最高薪水
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;
SELECT * FROM emp WHERE emp.sal IN (5000,3000,2850);
SELECT * FROM emp WHERE emp.sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);
SELECT * FROM emp,(SELECT deptno, MAX(sal) maxsal FROM emp GROUP BY deptno) d
WHERE emp.deptno = d.deptno AND emp.sal = d.maxsal;
...