MySql多表与事务

多表设计之外键约束

约束的作用  ：约束是用来保证数据的完整性。

单表约束

1. 主键约束
2. 唯一约束
3. 非空约束

多表约束 ：外键约束：用来保证数据完整性（多表之间）

表与表之间的关系

一对多的关系

多对多的关系

一对一的关系

多表设计之多表分析及创建

多表查询之多表查询的概述

1. 1. 1. 1. 连接查询
2. 交叉连接:cross join
   1. 交叉连接：查询到的是两个表的笛卡尔积。
   2. 语法：
      1. select * from 表1 cross join 表2;
      2. select * from 表1,表2;
3. 内连接:inner join(inner是可以省略的)
   1. 显示内连接：在SQL中显示的调用inner join关键字
      1. 语法：select * from 表1 inner join 表2 on 关联条件;
   2. 隐式内连接：在SQL中没有调用inner join关键字
      1. 语法：select * from 表1,表2 where 关联条件;
4. 外连接:outer join(outer可以省略的)
   1. 左外连接：
      1. 语法：select * from 表1 left outer join 表2 on 关联条件;
   2. 右外连接
      1. 语法：select * from 表1 right outer join 表2 on 关联条件;
         1. 子查询

1. 子查询：一个查询语句条件需要依赖另一个查询语句的结果。

多表查询之交叉连接

使用cross join关键字 ：select * from classes cross join student;

不使用cross join关键字 ：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;

多表查询之外连接

左外连接 ：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的子查询

SELECT * FROM classes WHERE cid > ANY (SELECT cno FROM student )

带all的子查询

SELECT * FROM classes WHERE cid > ALL (SELECT cno FROM student)

多表查询之练习

     1.查询班级名称，和班级总人数

Select c.cname,count(*) from class c,student s where c.cid=s.cno group by c.cname;

     2.查询学生的姓名和学生所选的总课程平均成绩。

select s.sname,avg(sc.score) from student s,stu_cour sc where s.sid = sc.sno group by s.sname;

select s.sname,avg(sc.score) from student s,stu_cou sc where s.sid=sdc.sno group by s.sname;

     3.查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。

select s.sname,count(*) from student s,stu_cour sc where s.sid = sc.sno group by s.sname having count(*) > 2;

select s.sname,count(*) from student s,stu_cour sc where s.sid=sc.snogroup by s.sname having count(*)>2;

    4.查询平均成绩大于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);

select count(*) from student s where s.sid in(select sc.sno from stu_cour sc group by sc.sno havaing avg(sc.score)>80);

    5.查询学生和平均成绩，但是平均成绩大于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.cname AND c.cname ='01班' GROUP BY s.sname);

    6.查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的评价成绩。

Select  s.sid ,avg(sc.score) from student s,stu_cour sc  class cwhere s.sid= sc.sno and c.name =’01班’ group by s.sname;

Select s.sname ,avg(sc.score) from student s, stu_cour sc where s.sid=sc.sno having avg(sc.score) >any

事务的概述

事务：指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么全都成功，要么全都失败。

MySQL中的事务管理

事务的特性：

原子性

原子性：事务的不可分割，组成事务的各个逻辑单元不可分割。

一致性

一致性：事务执行的前后，数据完整性保持一致。

隔离性

隔离性：事务执行不应该受到其他事务的干扰。

持久性

持久性：事务一旦结束，数据就持久化到数据库中。

事务的隔离级别

隔离性：一个事务的执行，不应该受到其他事务的干扰。

如果不考虑隔离性（一个事务执行受到其他的事务的干扰），引发一些安全问题，主要体现在读取数据上：

1. 脏读：一个事务读到了另一个事务未提交的数据，导致查询结果不一致
2. 不可重复读：一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致。
3. 虚读/幻读：一个事务读到了另一个事务已经提交的insert的数据，导致多次查询结果不一致。

设置事务的隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL  ****

1. read uncommitted ：脏读，不可重复读，虚读都有可能发生
2. read committed    ：避免脏读。但是不可重复读和虚读是有可能发生
3. repeatable read    ：避免脏读和不可重复读，但是虚读有可能发生。
4. serializable           ：避免脏读，不可重复读，虚读。