MYSQL多表重点知识点

第一部分：今日技术操作及SQL多表基础语法

foreign key 外键约束 referemces 要引用哪张表
一个数据库里面的约束名称不能有重复的，建议是以fk_表名称_001,以数字结尾，没约束一次，数字都不同，这样就不会重复了

1.多表中表与表之间的关系
*一对一的关系 示例：一个公司可以有一个注册地址，一个注册地址只能对一个公司。
*一对多的关系 示例：一个部门下可以有多个员工，一个员工只能属于某一个部门。
*多对多的关系 示例：一个学生可以选择多门课程，一门课程可以被多个学生选择。

2.各表对应关系分析
*一对多关系 一对多建表原则：在多的一方创建外键指向一的一方的主键
*多对多关系 多对多建表原则：需要创建中间表，中间表中至少两个字段，分别作为外键指向多对多双方的主键
*一对一关系（少） 将两个表的主键建立对应关系即可 唯一外键对应： ?假设是一对多，在多的一方创建外键指向一的一方的主键，将外键设置为unique。

3.多表查询的分类
*连接查询
*交叉查询
*交叉连接cross join 查询到的是两个表的笛卡尔积。
*语法：
select * from 表1 cross join 表2; 示例：SELECT * FROM classes CROSS JOIN student;
(简)select * from 表1,表2; 示例：SELECT * FROM classes,student;

*内连接（显示和隐式内连接结果一样）=======[表和表之间的交集部分]
    *inner join(inner是可以省略的)
    *显示内连接：在SQL中显示的调用inner join关键字
        语法：select * from 表1 inner join 表2 on 关联条件;         示例：select * from classes c inner join student s on c.cid = s.cno;   c、s前面有个as可以省略的，是重命名的意思
    *隐式内连接：在SQL中没有调用inner join关键字
        语法：select * from 表1,表2 where 关联条件;                  示例：SELECT * FROM classes c,student s WHERE c.cid = s.cno;     显示是on   隐式是where

*外连接
    *outer join(outer可以省略的)
    *左外连接： =======【左边表的数据加上二个表之间的交集部分】
        语法：select * from 表1 left outer join 表2 on 关联条件; 示例：SELECT * FROM classes c LEFT OUTER JOIN student s ON c.cid = s.cno;
    *右外连接    =======【右边表的数据加上二个表之间的交集部分】
        语法：select * from 表1 right outer join 表2 on 关联条件;    示例：select * from classes c right outer join student s on c.cid = s.cno;

*子查询
含义：子查询：一个查询语句条件需要依赖另一个查询语句的结果。
*带in的子查询 =======（固定的范围）
示例：查询学生生日在91年之后的班级的信息。
select * from classes where cid in (SELECT cno FROM student WHERE birthday > ‘1991-01-01’); cno班级编号的外键
思路：查询所有来自于班级信息，条件是班级id，范围是查询班级编号对应的来自于学生表，条件是生日在91年以后出生的

    *带exists的子查询 ======是否存在
    示例：查询学生生日大于91年1月1日，如果记录存在，前面的SQL语句就会执行
            select * from classes where exists (SELECT cno FROM student WHERE birthday > '1991-01-01');

    *带any的子查询 =========大于任意一个数（大于最小的）
    示例：  SELECT * FROM classes WHERE cid > ANY (SELECT cno FROM student )   

    *带all的子查询 =========大于所有（大于最大的）
    示例：  SELECT * FROM classes WHERE cid > ALL (SELECT cno FROM student)

4.多表查询的练习
*查询班级名称，和班级总人数
SELECT c.cname,COUNT(*) FROM classes c,student s WHERE c.cid = s.cno GROUP BY c.cname;

*查询学生的姓名和学生所选的总课程平均成绩。
    select s.sname,avg(sc.score) from student s,stu_cour sc where s.sid = sc.sno group by s.sname;

*查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。
    select s.sname,count(*) from student s,stu_cour sc where s.sid = sc.sno group by s.sname having count(*) > 2;

*查询平均成绩大于80分的学生的总数。
    select count(*) from student s where s.sid in (SELECT sc.sno FROM stu_cour sc GROUP BY sc.sno HAVING AVG(sc.score) >  80);

*查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩。
    select s.sname,avg(sc.score) from student s,stu_cour sc where s.sid = sc.sno group by s.sname having avg(sc.score) > any(SELECT AVG(sc.score) 
    FROM student s,stu_cour sc,classes c WHERE s.sid = sc.sno AND s.cno = c.cid AND c.cname= '01班' GROUP BY s.sname);

5.事务概述及其代码
*概述：指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么全都成功，要么全都失败。
*CREATE TABLE zhanghu(
zid INT PRIMARY KEY AUTO_INCREMENT,
zname VARCHAR(20) NOT NULL,
zmoney INT NOT NULL
);

    INSERT INTO zhanghu VALUES (1,'zs',10000);
    INSERT INTO zhanghu VALUES (2,'ls',10000);

    -- 开启事务
    START TRANSACTION;

    -- 执行转账
    UPDATE zhanghu SET zmoney=zmoney+1000 WHERE zid=1;
    UPDATE zhanghu SET zmoney=zmoney-1000 WHERE zid=2;

    -- 回滚事务
    ROLLBACK;

    -- 提交事务
    COMMIT;

设置事务的隔离级别：
    read uncommitted：   脏读，不可重复读，虚读都有可能发生  【效率最高安全最低】
    read committed  ：   避免脏读。但是不可重复读和虚读是有可能发生 -------------  orcal默认隔离级别
    repeatable read ：   避免脏读和不可重复读，但是虚读有可能发生。--------------  MySQL默认隔离级别
    serializable：       避免脏读，不可重复读，虚读。  【效率最低安全最高】

脏读：一个事务中已经读到了另一个事务未提交的数据。
串行化：发现A窗口已经卡住了（说明事务不允许出现并发，A窗口需要等待B窗口事务执行完成以后，才会执行A窗口的事务。）
当B窗口的事务结束（提交或者回滚），那么A窗口马上就会出现结果。

*事务的特性：原子性      一致性     隔离性     持久性

======================================================================================================

老师代码补充

– 交叉连接
SELECT * FROM classes CROSS JOIN student; 【表名无顺序限制，classes和student顺序无所谓】
SELECT * FROM classes,student;

– 内连接
SELECT * FROM classes c INNER JOIN student s ON c.cid = s.cno;
SELECT * FROM classes c,student s WHERE c.cid = s.cno; 【简写不能用on，要用where】

– 左外连接
SELECT * FROM classes c LEFT OUTER JOIN student s ON c.cid = s.cno;

– 右外连接
SELECT * FROM classes c RIGHT OUTER JOIN student s ON c.cid = s.cno;

– 子查询：带in的查询：查询学生生日在91年之后的班级的信息
SELECT * FROM classes WHERE cid IN (SELECT cno FROM student WHERE birthday > ‘1991-01-01’);

– 子查询：带exists的子查询：查询学生生日大于91年1月1日，如果记录存在，前面的SQL语句就会执行
SELECT * FROM classes WHERE EXISTS (SELECT cno FROM student WHERE birthday > ‘1991-01-01’);

– 子查询：带any的子查询：只要班级表中的cid大于任意一个学生表的cno
SELECT * FROM classes WHERE cid > ANY (SELECT cno FROM student);

– 子查询：带all的子查询：只要班级表中的cid大于所有学生表的cno
SELECT * FROM classes WHERE cid > ALL (SELECT cno FROM student);

/*
多表查询练习
*/
– 查询班级名称，和班级总人数
SELECT c.cname,COUNT(*) FROM classes c,student s WHERE c.cid = s.cno GROUP BY c.cname;

– 查询学生的姓名和学生所选的总课程平均成绩
SELECT s.sname,AVG(sc.score) FROM student s,stu_cour sc WHERE s.sid = sc.sno GROUP BY s.sname;

– 查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名
SELECT s.sname,COUNT() FROM student s,stu_cour sc WHERE s.sid = sc.sno GROUP BY s.sname HAVING COUNT() > 2;

– 查询平均成绩大于80分的学生的总数 根据编号得到成绩
SELECT COUNT(*) FROM student s WHERE s.sid IN (SELECT sc.sno FROM stu_cour sc GROUP BY sc.sno HAVING AVG(sc.score) > 80);

– 查询学生的姓名和平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩
SELECT s.sname,AVG(sc.score) FROM student s,stu_cour sc WHERE s.sid = sc.sno GROUP BY s.sname
HAVING AVG(sc.score) > ANY(SELECT AVG(sc.score) FROM student s,stu_cour sc,classes c
WHERE s.sid = sc.sno AND s.cno = c.cid AND c.cname = ‘01班’ GROUP BY s.sname);