表和表之间的关系
多对多的关系(利用第三张表来表示关系的)
并且第三个表作为从表 拥有其他两个主表的外键
创建老师表,学生表,中间表,并建立多对多的关系
一对一关系(不常用 完成可以写成一张表)
CREATE TABLE teacher(
tid int PRIMARY key,
tname VARCHAR(20)
);
CREATE TABLE student(
sid int PRIMARY key,
sname VARCHAR(20)
);
CREATE TABLE teacher_student(
tid int,
sid int
);
ALTER TABLE teacher_student ADD CONSTRAINT fk_teacher_tid FOREIGN KEY(tid)
REFERENCES teacher(tid);
ALTER TABLE teacher_student ADD CONSTRAINT fk_student_sid FOREIGN KEY(sid)
REFERENCES student(sid);
如果考虑查询效率问题 可以对表进行拆分
拆分有度
合并查询
UNION取两个表并集(字段名 ,类型相同)
UNION ALL把两个表的数据合并到一起
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 A,B;
学生表 和分数表 一起查询
SELECT * FROM student,score;
去除错误数据(利用两张表的编号相同去除)
查询法(利用表中的字段相同 去除重复数据)
SELECT * FROM student,score WHERE student.stuid=score.stuid;
查询学生编号和学生的分数
SELECT student.stuid,score.score FROM student,score WHERE student.stuid=score.stuid;
别名
SELECT s.stuid,c.score FROM student s,score c WHERE s.stuid=c.stuid;
创建科目表
CREATE TABLE course(
courseid int,
cname VARCHAR(20)
);
3个表查询
SELECT * FROM student,score,course;
去除错误数据(通过字段的联系去除)
SELECT s.stuname,c.score,o.cname FROM student s,score c,course o WHERE s.stuid=c.stuid
AND c.courseid=o.courseid;
连接查询(多表查询)
内连接(INNER可以省略)
on 后面是去除重复数据的条件
SELECT * FROM student s INNER JOIN score c ON s.stuid=c.stuid;
三表内连接查询
SELECT * FROM student s INNER JOIN score c INNER JOIN course o ON s.stuid=c.stuid
AND c.courseid=o.courseid;
查询表中80分以上学生的姓名 分数 科目信息
SELECT s.stuname,c.score,o.cname FROM student s INNER JOIN score c INNER JOIN
course o ON s.stuid=c.stuid AND c.courseid=o.courseid WHERE c.score>80;
外连接 左外连接 右外连接
学生和分数表
左外连接 就是以左边的表为主
会查询出左边的表的所有数据
右外连接 反之
SELECT * FROM student LEFT OUTER JOIN score ON student.stuid=score.stuid;
自然连接 关键词 NATURAL JOIN
可以自动匹配两个表中相同字段的值
要求字段名和类型相同
SELECT * FROM student NATURAL JOIN score;
创建员工表与部门表
子查询(嵌套查询)
员工表和部门表来测试
查询工资高于JONES的员工信息
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='JONES');
查询与SCOTT同一个部门的员工
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp where ename='SCOTT');
工资高于30号部门所有人的员工信息
SELECT * FROM emp WHERE sal>(SELECT max(sal) FROM emp WHERE deptno=30);
查询工作和工资与MARTIN完全相同的员工信息
SELECT * empWHERE sal = (sal FROM emp WHERE ename = 'MARTIN')
AND job = (SELECT job FROM emp WHERE ename = 'MARTIN');
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN');
有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno IN (
SELECT mgr FROM emp group by mgr HAVING COUNT(mgr)>=2);
查询员工编号为7788的员工名称、员工工资、部门名称、部门地址 SELECT e.ename,e.sal,d.dname,d.loc From emp e,dept d WHERE e.deptno=d.deptno AND
e.empno=7788;
求各个部门薪水最高的员工所有信息
把查询出来的结果 当做一张表 来连接查询
SELECT * FROM emp WHERE sal in(
SELECT MAX(sal) FROM emp group by deptno);
SELECT * FROM emp e1,(SELECT deptno,MAX(sal) msal FROM emp group by deptno) e2
WHERE e1.sal=e2.msal AND e1.deptno=e2.deptno;
求7369员工编号、姓名、经理编号和经理姓名
自连接查询
要查找的信息在一张中
一次查不出来
把自己这个表当做两个表来连接查询
SELECT e1.empno,e1.ename,e2.ename FROM emp e1,emp e2 WHERE e1.empno=e2.mgr
AND e2.empno=7369;