MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习
多表设计之外键约束
约束
约束的作用
约束是用来保证数据的完整性。
单表约束
主键约束
唯一约束
非空约束
多表约束
外键约束:用来保证数据完整性(多表之间)。
演示外键约束作用
创建一个部门表
create table dept(
did int primary key auto_increment,
dname varchar(20)
);
insert into dept values (null,'市场部');
insert into dept values (null,'人事部');
insert into dept values (null,'教研部');
创建一个员工表
create table employee(
eid int primary key auto_increment,
ename varchar(20),
salary double,
birthday date,
sex varchar(10),
dno int
);
insert into employee values (null,'张三',8000,'1988-09-01','男',3);
insert into employee values (null,'李四',9000,'1988-09-01','男',1);
insert into employee values (null,'王五',6000,'1988-09-01','男',2);
insert into employee values (null,'赵六',10000,'1988-09-01','男',3);
insert into employee values (null,'孙七',10000,'1988-09-01','男',1);
删除其中的某个部门(是否可以)
查看数据
向员工表中插入一条记录(没有部门)
insert into employee values (null,'田八',10000,'1988-09-01','男',null);
删除一个人事部
delete from dept where did = 2;
向刚才做的这两个操作(插入一个没有部门的员工,删除一个带有员工的部门)。这种情况都是不应该发生。这个时候需要在多表之间添加外键约束。
添加外键约束
在员工表上添加外键
alter table employee add foreign key (dno) references dept(did);
设置外键为非空
alter table employee modify dno int not null;
表设计之表关系的介绍
表与表之间的关系
一对多的关系
一对多的例子:
一个部门下可以有多个员工,一个员工只能属于某一个部门。
多对多的关系
多对多的例子:
一个学生可以选择多门课程,一门课程可以被多个学生选择。
一对一的关系
一对一的例子:
一个公司可以有一个注册地址,一个注册地址只能对一个公司。
表设计之一对多关系
一对多关系介绍
一对多关系的建表原则
在多的一方创建外键指向一的一方的主键
多表设计之多对多
多对多的关系介绍
一个学生选择多门课程,一门课程被多个学生所选择
多对多的建表的原则
需要创建中间表,中间表中至少两个字段,分别作为外键指向多对多双方的主键
多表设计之一对一关系
一对一关系的介绍
一个公司可以对应一个注册地址,一个注册地址只能对应一个公司
一对一关系建表原则
唯一外键对应
假设是一对多,在多的一方创建外键指向一的一方的主键,将外键设置为unique。
主键对应
将两个表的主键建立对应关系即可。
多表查询之多表查询的概述
多表查询的分类
连接查询
交叉连接: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 关联条件;
外连接:outer join(outer可以省略的)
左外连接:
语法:select * from 表1 left outer join 表2 on 关联条件;
右外连接
语法:select * from 表1 right outer join 表2 on 关联条件;
子查询
子查询:一个查询语句条件需要依赖另一个查询语句的结果。
多表查询之数据准备
数据准备(文本最后)
班级表数据的准备
学生表数据的准备
课程表数据的准备
学生选课表的准备
多表查询之交叉连接
交叉连接
使用cross join关键字
select * from classes cross join student;
1.1.1.2不使用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的子查询
l查询学生生日大于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)
多表查询之练习
多表查询的练习
查询班级名称,和班级总人数
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);
事务的概述
事务的概念
事务:指的是逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全都成功,要么全都失败。
MySQL中的事务管理
环境准备
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
insert into account values (null,'小张',10000);
insert into account values (null,'小凤',10000);
转账案例
开启事务:
start transaction;
提交事务
commit;
回滚事务
rollback;
提交事务
回滚事务
事务的特性:
原子性
原子性:事务的不可分割,组成事务的各个逻辑单元不可分割。
一致性
一致性:事务执行的前后,数据完整性保持一致。
隔离性
隔离性:事务执行不应该受到其他事务的干扰。
持久性
持久性:事务一旦结束,数据就持久化到数据库中。
事务的隔离级别
如果不考虑隔离性,引发一些安全问题
隔离性:一个事务的执行,不应该受到其他事务的干扰。
如果不考虑隔离性(一个事务执行受到其他的事务的干扰),引发一些安全问题,主要体现在读取数据上:
脏读:一个事务读到了另一个事务未提交的数据,导致查询结果不一致
不可重复读:一个事务读到了另一个事务已经提交的update的数据,导致多次查询结果不一致。
虚读/幻读:一个事务读到了另一个事务已经提交的insert的数据,导致多次查询结果不一致。
解决这些安全性问题:
设置事务的隔离级别:
read uncommitted :脏读,不可重复读,虚读都有可能发生
read committed :避免脏读。但是不可重复读和虚读是有可能发生
repeatable read :避免脏读和不可重复读,但是虚读有可能发生。
serializable :避免脏读,不可重复读,虚读。
事务的隔离级别的演示
演示脏读
开启两个窗口A,B
设置A窗口的隔离级别为read uncommitted;
SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;
在A,B两个窗口中开启事务
start transaction;
在B窗口中完成转账的功能:
update account set money = money - 1000 where name= '小张';
update account set money = money + 1000 where name= '小凤';
***** 事务未提交!!!
在A窗口中进行查询
select * from account;
*****发现A窗口中已经查询到转账成功了!!!已经发生了脏读:一个事务中已经读到了另一个事务未提交的数据。
事务的隔离级别演示
避免脏读,演示不可重复读发生
开启两个窗口A,B
设置A窗口的隔离级别为read committed;
SET SESSION TRANSACTION ISOLATION LEVEL read committed;
分别在两个窗口中开启事务:
start transaction;
在B窗口中完成转账
update account set money = money - 1000 where name= '小张';
update account set money = money + 1000 where name= '小凤';
***** 没有提交事务!!!
l 在A窗口中进行查询:
select * from account;
***** 发现这个时候没有转账成功!!!(没有查询到另一个事务未提交的数据:说明已经避免了脏读)。
在B窗口中提交事务
commit;
在A窗口查询
select * from account;
***** 发现这次的结果已经发生了变化!!!(已经发生不可重复读:一个事务已经读到了另一个事务提交的update的数据,导致多次查询结果不一致。)
事务的隔离级别的演示
演示避免不可重复读
分别开启两个窗口A,B
设置A窗口的隔离级别:repeatable read;
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
在A,B两个窗口中开启事务:
start transaction;
在B窗口完成转账
update account set money = money - 1000 where name= '小张';
update account set money = money + 1000 where name= '小凤';
***** 未提交事务!!!
在A窗口中进行查询
select * from account;
***** 发现没有转账成功:说明避免脏读!!!
在B窗口中提交事务
commit;
在A窗口中再次查询:
***** 发现在一个事务中的多次查询结果是一致!!!(已经避免不可重复读)。
事务的隔离级别演示
演示串行化
开启两个窗口A,B
设置A窗口的隔离级别:serializable
SET SESSION TRANSACTION ISOLATION LEVEL serializable;
分别在两个窗口中开启事务:
start transaction;
在B窗口中插入一条记录
insert into account values (null,'小李',10000);
在A窗口中进行查询
select * from account;
*****发现A窗口已经卡住了(说明事务不允许出现并发,A窗口需要等待B窗口事务执行完成以后,才会执行A窗口的事务。)当B窗口的事务结束(提交或者回滚),那么A窗口马上就会出现结果。
DROP TABLE IF EXISTS `classes`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `classes` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(20) DEFAULT NULL, `cnum` int(11) DEFAULT NULL, PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `classes`--LOCK TABLES `classes` WRITE;/*!40000 ALTER TABLE `classes` DISABLE KEYS */;INSERT INTO `classes` VALUES (1,'01班',20),(2,'02班',30),(3,'03班',32),(4,'04班',41);/*!40000 ALTER TABLE `classes` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `course`--DROP TABLE IF EXISTS `course`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(20) DEFAULT NULL, PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `course`--LOCK TABLES `course` WRITE;/*!40000 ALTER TABLE `course` DISABLE KEYS */;INSERT INTO `course` VALUES (1,'Java'),(2,'PHP'),(3,'C++');/*!40000 ALTER TABLE `course` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `stu_cour`--DROP TABLE IF EXISTS `stu_cour`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `stu_cour` ( `scid` int(11) NOT NULL AUTO_INCREMENT, `sno` int(11) DEFAULT NULL, `cno` int(11) DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`scid`), KEY `FK_stu_cour_001` (`sno`), KEY `FK_stu_cour_002` (`cno`), CONSTRAINT `FK_stu_cour_001` FOREIGN KEY (`sno`) REFERENCES `student` (`sid`), CONSTRAINT `FK_stu_cour_002` FOREIGN KEY (`cno`) REFERENCES `course` (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `stu_cour`--LOCK TABLES `stu_cour` WRITE;/*!40000 ALTER TABLE `stu_cour` DISABLE KEYS */;INSERT INTO `stu_cour` VALUES (1,1,1,85),(2,1,3,72),(3,2,2,82),(4,2,3,65),(5,3,1,71),(6,3,2,75),(7,3,3,68),(8,4,1,72),(9,4,2,64),(10,5,2,91),(11,5,3,82),(12,6,1,74),(13,6,2,48),(14,7,2,73),(15,7,3,72),(16,8,1,65),(17,8,2,80),(18,9,1,81),(19,9,2,91),(20,9,3,78);/*!40000 ALTER TABLE `stu_cour` ENABLE KEYS */;UNLOCK TABLES;---- Table structure for table `student`--DROP TABLE IF EXISTS `student`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `sname` varchar(20) DEFAULT NULL, `sex` varchar(10) DEFAULT NULL, `birthday` date DEFAULT NULL, `cno` int(11) DEFAULT NULL, PRIMARY KEY (`sid`), KEY `fk_student_001` (`cno`), CONSTRAINT `fk_student_001` FOREIGN KEY (`cno`) REFERENCES `classes` (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `student`--LOCK TABLES `student` WRITE;/*!40000 ALTER TABLE `student` DISABLE KEYS */;INSERT INTO `student` VALUES (1,'张三','男','1990-09-01',1),(2,'李四','女','1991-02-13',1),(3,'王五','男','1990-03-12',1),(4,'赵六','男','1992-02-12',2),(5,'田七','男','1994-05-21',2),(6,'张五','女','1990-06-17',2),(7,'张老七','女','1990-04-12',3),(8,'王老四','女','1990-07-16',3),(9,'李六','男','1990-09-12',NULL);/*!40000 ALTER TABLE `student` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET aaa@qq.com_TIME_ZONE */;/*!40101 SET aaa@qq.com_SQL_MODE */;/*!40014 SET aaa@qq.com_FOREIGN_KEY_CHECKS */;/*!40014 SET aaa@qq.com_UNIQUE_CHECKS */;/*!40101 SET aaa@qq.com_CHARACTER_SET_CLIENT */;/*!40101 SET aaa@qq.com_CHARACTER_SET_RESULTS */;/*!40101 SET aaa@qq.com_COLLATION_CONNECTION */;/*!40111 SET aaa@qq.com_SQL_NOTES */;