欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

程序员文章站 2022-05-09 09:17:06
...

 多表设计之外键约束

     约束

     约束的作用

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


 单表约束

 主键约束

 唯一约束

 非空约束

 多表约束

 外键约束:用来保证数据完整性(多表之间)。

演示外键约束作用

创建一个部门表
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);

 删除其中的某个部门(是否可以)

    查看数据

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习
向员工表中插入一条记录(没有部门)
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);
MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习
设置外键为非空
alter table employee modify dno int not null;
MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 表设计之表关系的介绍

 表与表之间的关系

    一对多的关系

         一对多的例子:

         一个部门下可以有多个员工,一个员工只能属于某一个部门。

   多对多的关系

         多对多的例子:

         一个学生可以选择多门课程,一门课程可以被多个学生选择。

  一对一的关系

         一对一的例子:

         一个公司可以有一个注册地址,一个注册地址只能对一个公司。


 表设计之一对多关系

 一对多关系介绍

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习 一对多关系的建表原则

在多的一方创建外键指向一的一方的主键


 多表设计之多对多

    多对多的关系介绍

       一个学生选择多门课程,一门课程被多个学生所选择

 多对多的建表的原则

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

需要创建中间表,中间表中至少两个字段,分别作为外键指向多对多双方的主键


 多表设计之一对一关系

 一对一关系的介绍

一个公司可以对应一个注册地址,一个注册地址只能对应一个公司

 一对一关系建表原则

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 唯一外键对应

 假设是一对多,在多的一方创建外键指向一的一方的主键,将外键设置为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 关联条件;

 子查询

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


 多表查询之数据准备

 数据准备(文本最后)

 班级表数据的准备

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 学生表数据的准备

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 课程表数据的准备

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 学生选课表的准备

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 多表查询之交叉连接

 交叉连接

 使用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;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

隐式内连接
SELECT * FROM classes c,student s WHERE c.cid = s.cno;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 多表查询之外连接

 外连接

 左外连接

SELECT * FROM classes c LEFT OUTER JOIN student s ON c.cid = s.cno;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 右外连接

select * from classes c right outer join student s on c.cid = s.cno;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 多表查询之内连接与外连接的区别

 内连接和外连接的区别

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 多表查询之子查询

 子查询

 in的子查询

查询学生生日在91年之后的班级的信息。
select * from classes where cid in (SELECT cno FROM student WHERE birthday > '1991-01-01');

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 带exists的子查询

l查询学生生日大于91年1月1日,如果记录存在,前面的SQL语句就会执行

select * from classes where exists (SELECT cno FROM student WHERE birthday > '1991-01-01');
MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习
带any的子查询
SELECT * FROM classes WHERE cid > ANY (SELECT cno FROM student )

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

带all的子查询
SELECT * FROM classes WHERE cid > ALL (SELECT cno FROM student)

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 多表查询之练习

 多表查询的练习

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

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

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

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

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

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

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

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

查询学生和平均成绩,但是平均成绩大于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_2 多表 表关系 事务 一对多 多对多 sql练习



 事务的概述

     事务的概念

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

 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;
提交事务

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

 回滚事务

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习


 事务的特性:

 原子性

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

 一致性

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

 隔离性

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

持久性

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


 事务的隔离级别

 如果不考虑隔离性,引发一些安全问题

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

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

 脏读:一个事务读到了另一个事务未提交的数据,导致查询结果不一致

 不可重复读:一个事务读到了另一个事务已经提交的update的数据,导致多次查询结果不一致。

 虚读/幻读:一个事务读到了另一个事务已经提交的insert的数据,导致多次查询结果不一致。


 解决这些安全性问题:

设置事务的隔离级别:

 read uncommitted :脏读,不可重复读,虚读都有可能发生

read committed :避免脏读。但是不可重复读和虚读是有可能发生

repeatable read :避免脏读和不可重复读,但是虚读有可能发生。

 serializable :避免脏读,不可重复读,虚读。


 事务的隔离级别的演示

 演示脏读

开启两个窗口A,B
设置A窗口的隔离级别为read uncommitted;
SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

在A,B两个窗口中开启事务
start transaction;
在B窗口中完成转账的功能:
update account set money = money - 1000 where name= '小张';
update account set money = money + 1000 where name= '小凤';
***** 事务未提交!!!

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

在A窗口中进行查询
select * from account;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

*****发现A窗口中已经查询到转账成功了!!!已经发生了脏读:一个事务中已经读到了另一个事务未提交的数据。


 事务的隔离级别演示

避免脏读,演示不可重复读发生

开启两个窗口A,B
设置A窗口的隔离级别为read committed;
SET SESSION TRANSACTION ISOLATION LEVEL read committed;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

分别在两个窗口中开启事务:
start transaction;
在B窗口中完成转账
update account set money = money - 1000 where name= '小张';
update account set money = money + 1000 where name= '小凤';

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

***** 没有提交事务!!!

l 在A窗口中进行查询:

select * from account;
MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习
***** 发现这个时候没有转账成功!!!(没有查询到另一个事务未提交的数据:说明已经避免了脏读)。
在B窗口中提交事务
commit;
在A窗口查询
select * from account;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

***** 发现这次的结果已经发生了变化!!!(已经发生不可重复读:一个事务已经读到了另一个事务提交的update的数据,导致多次查询结果不一致。)

 事务的隔离级别的演示

 演示避免不可重复读

分别开启两个窗口A,B
设置A窗口的隔离级别:repeatable read;
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

在A,B两个窗口中开启事务:
start transaction;
在B窗口完成转账
update account set money = money - 1000 where name= '小张';
update account set money = money + 1000 where name= '小凤';

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

***** 未提交事务!!!
在A窗口中进行查询
select * from account;
***** 发现没有转账成功:说明避免脏读!!!
在B窗口中提交事务
commit;
在A窗口中再次查询:

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

***** 发现在一个事务中的多次查询结果是一致!!!(已经避免不可重复读)。


事务的隔离级别演示

 演示串行化

开启两个窗口A,B
设置A窗口的隔离级别:serializable
SET SESSION TRANSACTION ISOLATION LEVEL serializable;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

分别在两个窗口中开启事务:
start transaction;

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

在B窗口中插入一条记录
insert into account values (null,'小李',10000);

MYSQL_2 多表 表关系 事务 一对多 多对多 sql练习

在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 */;