多表设计之外键约束
约束的作用 :约束是用来保证数据的完整性。
单表约束
- 主键约束
- 唯一约束
- 非空约束
多表约束 :外键约束:用来保证数据完整性(多表之间)
表与表之间的关系
一对多的关系
多对多的关系
一对一的关系
多表设计之多表分析及创建
多表查询之多表查询的概述
-
-
-
- 连接查询
-
-
- 交叉连接:cross join
- 交叉连接:查询到的是两个表的笛卡尔积。
- 语法:
- select * from 表1 cross join 表2;
- select * from 表1,表2;
- 内连接:inner join(inner是可以省略的)
- 显示内连接:在SQL中显示的调用inner join关键字
- 语法:select * from 表1 inner join 表2 on 关联条件;
- 隐式内连接:在SQL中没有调用inner join关键字
- 语法:select * from 表1,表2 where 关联条件;
- 显示内连接:在SQL中显示的调用inner join关键字
- 外连接:outer join(outer可以省略的)
- 左外连接:
- 语法:select * from 表1 left outer join 表2 on 关联条件;
- 右外连接
- 语法:select * from 表1 right outer join 表2 on 关联条件;
- 子查询
- 语法:select * from 表1 right outer join 表2 on 关联条件;
- 左外连接:
- 子查询:一个查询语句条件需要依赖另一个查询语句的结果。
多表查询之交叉连接
使用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中的事务管理
事务的特性:
原子性
原子性:事务的不可分割,组成事务的各个逻辑单元不可分割。
一致性
一致性:事务执行的前后,数据完整性保持一致。
隔离性
隔离性:事务执行不应该受到其他事务的干扰。
持久性
持久性:事务一旦结束,数据就持久化到数据库中。
事务的隔离级别
隔离性:一个事务的执行,不应该受到其他事务的干扰。
如果不考虑隔离性(一个事务执行受到其他的事务的干扰),引发一些安全问题,主要体现在读取数据上:
- 脏读:一个事务读到了另一个事务未提交的数据,导致查询结果不一致
- 不可重复读:一个事务读到了另一个事务已经提交的update的数据,导致多次查询结果不一致。
- 虚读/幻读:一个事务读到了另一个事务已经提交的insert的数据,导致多次查询结果不一致。
设置事务的隔离级别:
SET SESSION TRANSACTION ISOLATION LEVEL ****
- read uncommitted :脏读,不可重复读,虚读都有可能发生
- read committed :避免脏读。但是不可重复读和虚读是有可能发生
- repeatable read :避免脏读和不可重复读,但是虚读有可能发生。
- serializable :避免脏读,不可重复读,虚读。