立即学习:https://edu.csdn.net/course/play/9885/210112?utm_source=blogtoedu
-- 多表查询
-- 表之间的关系
-- 一对一
-- 一对多
CREATE TABLE person (id INT PRIMARY KEY auto_increment, name varchar(50));
CREATE TABLE car (name varchar(20), color varchar(20), pid INT,
CONSTRAINT car_person_fk FOREIGN KEY (pid) REFERENCES person(id)
)
-- 多对多
CREATE TABLE teacher (tid INT PRIMARY KEY auto_increment, name VARCHAR(50));
CREATE TABLE stu (sid INT PRIMARY KEY auto_increment, name VARCHAR(50));
CREATE TABLE tea_stu_rel (tid INT, sid INT);
ALTER TABLE tea_stu_rel ADD CONSTRAINT FOREIGN KEY (tid) REFERENCES teacher (tid);
ALTER TABLE tea_stu_rel ADD CONSTRAINT FOREIGN KEY (sid) REFERENCES stu (sid);
-- 为什么拆分表,避免大量冗余数据出现
-- 多表查询
-- 合并结果集:把两个select语句的查询结果合并到一起
-- UNION 去除重复记录
-- UNION ALL 不去除重复记录
-- 注意事项:被合并的两个结果:列数、列类型必须相同
-- SELECT * FROM tab1 UNION (ALL) SELECT * FROM tab2
CREATE TABLE A (name VARCHAR(10), score INT);
CREATE TABLE B (name VARCHAR(10), score INT);
INSERT INTO A VALUES ('a', 10), ('b', 20), ('c', 30);
INSERT INTO B VALUES ('a', 10), ('b', 20), ('d', 40);
SELECT * FROM A
UNION
SELECT * FROM B
SELECT * FROM A
UNION ALL
SELECT * FROM B
-- 连接查询
-- 什么是连接查询:跨表查询,需要关联多个表查询
-- 什么是笛卡尔集:假设A={a,b},B={0,1,2},则两个集合的笛卡尔集为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)},可以扩展到多个集合的情况
-- 同时查询两个表,出现的就是笛卡尔集结果
SELECT * FROM stu,teacher;
-- 查询时给表起别名
-- 多表联查,如何保证数据正确
-- 在查询时要吧主键和外键保持一致,主表中的数据参照子表中的数据
SELECT * FROM stu,teacher WHERE stu.sid = teacher.tid;
-- 根据连接方式分类
-- 内连接
-- 等值连接
SELECT * FROM stu st, score sc WHERE st.id = sc.sid; -- 99 查询
SELECT st.id, st.name, sc.score, st.gender FROM stu st INNER JOIN score sc ON st.id = sc.sid WHERE sc.score > 60;
-- 非等值连接
CREATE TABLE grade (name VARCHAR(10), socre_low INT, score_high INT);
SELECT st.id, st.name, sc.score, cs.name, g.name FROM stu st, score sc, course cs, grade g
WHERE st.id = sc.sid
AND sc.cid = cs.cid
AND sc.score >= g.score_low AND sc.score <= g.score_high;
SELECT st.id, st.name, sc.score, cs.name, g.name FROM stu st, score sc, course cs, grade g
WHERE st.id = sc.sid
AND sc.cid = cs.cid
AND sc.score BETWEEN g.score_low AND g.score_high;
SELECT st.id, st.name, sc.score, cs.name, g.name FROM stu st
JOIN score sc ON st.id = sc.sid
JOIN course cs ON sc.cid = cs.cid
JOIN grade g ON sc.score BETWEEN g.score_low AND g.score_high;
-- 自连接
-- 外连接
-- 左连接:
-- 两表满足条件相同的数据查出来,如果左边表中有不同的数据,也把左边表当中的数据查出来
-- 左边表中的数据全部查出来,右边表中只查出满足条件的内容
SELECT * FROM stu st LEFT JOIN score sc ON st.id = sc.sid;
-- 右连接
SELECT * FROM stu st RIGHT JOIN score sc ON st.id = sc.sid;
-- 多表连接
CREATE TABLE course (cid INT, name VARCHAR(50))
-- 99查询
SELECT st.`name`, sc.score, cs.`name`
FROM stu st, score sc, course cs
WHERE st.id = sc.sid
AND sc.cid = cs.cid;
-- 内连接
SELECT st.id, st.name, sc.score, cs.name FROM stu st
JOIN score sc ON st.id = sc.sid
JOIN course cs ON sc.cid = cs.cid;
-- 自然连接
-- 连接查询会产生无用的笛卡尔集,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式,也就是说不用去写条件。
-- 要求:两张连接表中列名称和类型完全一致的列作为条件会去除相同的列
SELECT * FROM score NATURAL JOIN course;