MySQL数据库3

表和表之间的关系
多对多的关系(利用第三张表来表示关系的)
并且第三个表作为从表拥有其他两个主表的外键
创建老师表,学生表,中间表,并建立多对多的关系

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;

创建员工表与部门表

MySQL数据库3

子查询(嵌套查询)
    员工表和部门表来测试
    查询工资高于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;

相关推荐