多表查询
-- 添加外键约束
CREATE TABLE stu(
sid INT PRIMARY KEY,
NAME VARCHAR(50) NOT NULL
);
-- 添加外键约束方式一
CREATE TABLE score1(
score DOUBLE,
sid INT,
CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid)
);
-- 添加外键约束方式二
CREATE TABLE score1(
score DOUBLE,
sid INT
);
ALTER TABLE score1 ADD CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES stu(sid)
DROP TABLE score1;
-- 多对多关系
CREATE TABLE teacher(
tid INT PRIMARY KEY,
tname VARCHAR(20)
);
CREATE TABLE stu2(
sid INT PRIMARY KEY,
sname VARCHAR(50)
);
CREATE TABLE tea_stu_relation(
tid INT,
sid INT
);
ALTER TABLE tea_stu_relation ADD CONSTRAINT fk_tid FOREIGN KEY(tid) REFERENCES teacher(tid)
ALTER TABLE tea_stu_relation ADD CONSTRAINT fk_ssid FOREIGN KEY(sid) REFERENCES stu2(sid)
-- 一对一(主键连主键)
CREATE TABLE QQ(
qqid INT PRIMARY KEY,
PASSWORD VARCHAR(50)
);
CREATE TABLE QQDetail(
qqid INT PRIMARY KEY,
NAME VARCHAR(50),
address VARCHAR(200)
);
ALTER TABLE QQDetail ADD CONSTRAINT fk_QQ_QQDetail_qqid FOREIGN KEY(qqid) REFERENCES QQ(qqid)
图解:
-- 连接查询-使用主外键关系做为条件来去除无用信息
SELECT * FROM student,score
-- 也叫内连接查询,99查询法
SELECT * FROM student,score WHERE student.stuid=score.stuid;
SELECT s.stuid,s.stuname,c.score FROM student s,score c WHERE s.stuid=c.stuid;
SELECT e.empno,e.ename,d.dname,d.deptno FROM emp e,dept d WHERE e.deptno=d.deptno
-- 内连接查询(找两个表*同的数据)
SELECT s.stuid,s.stuname,c.score FROM student s JOIN score c ON s.stuid=c.stuid WHERE score>70;
SELECT s.stuid,s.stuname,c.score FROM student s,score c WHERE s.stuid=c.stuid AND score>70;
-- -- 外连接查询
SELECT s.stuid,s.stuname,c.score FROM student s LEFT JOIN score c ON s.stuid=c.stuid;
上述为左连接查询:score参照的是LEFT左边的student表,这两个表相同的数据都能出来,不相同的数据如果student表有的,则score表也能出来。左连接是先查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示NULL。查询的只是数据,跟主外键没有关系。
左连接查询结果如下:
-- 右外连接查询
SELECT * FROM student s RIGHT JOIN score c ON s.stuid=c.stuid;
右连接就是先把右表中所有记录都查询出来,然后左表满足条件的显示,不满足显示NULL。
左连接与右连接区别:看站在哪张表的角度去看。
-- 多表查询
-- 多张表查询
CREATE TABLE course(
courseid INT PRIMARY KEY,
cname VARCHAR(50)
);
SELECT s.stuid,s.stuname,c.score,cc.cname FROM student s,score c,course cc WHERE s.stuid=c.stuid AND c.courseid=cc.courseid;
-- INNER可以省略,MySQL默认的连接方式就是内连接
SELECT s.stuid,s.stuname,c.score,cc.cname FROM student s JOIN score c ON s.stuid=c.stuid JOIN course cc ON c.courseid=cc.courseid;
连接查询心得:
连接不限与两张表,连接查询也可以是三张、四张,甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积,而只是需要其中一部分,那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系,三张表的连接查询就一定有两个主外键关系,所以在大家不是很熟悉连接查询时,首先要学会去除无用笛卡尔积,那么就是用主外键关系作为条件来处理。如果两张表的查询,那么至少有一个主外键条件,三张表连接至少有两个主外键条件。
-- 自然连接
SELECT * FROM student NATURAL JOIN score;
大家也都知道,连接查询会产生无用笛卡尔积,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式:
- 两张连接的表中名称和类型完全一致的列作为条件,例如emp和dept表都存在deptno列,并且类型一致,所以会被自然连接找到!
当然自然连接还有其他的查找条件的方式,但其他方式都可能存在问题!