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 TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;