day15(mysql 的多表查询，事务)

2023-08-20 21:06:16

mysql之多表查询

1.合并结果集

　　作用:合并结果集就是把两个select语句查询的结果连接到一起！

/*创建表t1*/

CREATE  TABLE t1(

	a INT PRIMARY KEY ,

	b VARCHAR(10)

)

INSERT INTO t1 VALUES(1,'a');

INSERT INTO t1 VALUES(2,'b');

INSERT INTO t1 VALUES(3,'c');

day15(mysql 的多表查询，事务)

/*创建t2*/

CREATE  TABLE t2(

	c INT PRIMARY KEY ,

	d VARCHAR(10)

)

INSERT INTO t2 VALUES(4,'d');

INSERT INTO t2 VALUES(5,'e');

INSERT INTO t2 VALUES(6,'f');

day15(mysql 的多表查询，事务)

　　合并结果集有两种方式：

　　　　union: 去除重复记录。　　

/*使用union进行连接两个select语句*/

select * from t1 union select * from t2;
查询结果为:

day15(mysql 的多表查询，事务)





　　为了体现去重思想： 我们给t1添加一条数据




insert into t1 values(4,'d');


day15(mysql 的多表查询，事务)




　　重新进行查询则：




select * from t1 union select * from t2;


day15(mysql 的多表查询，事务)


证明了 t1表和t2表的相同的数据会去掉






　　


　　　union all：不用出去重复记录。




select * from t1 union all select * from t2;


day15(mysql 的多表查询，事务)


输出结果中   4d出现了两次






　　总结 ：  union ：连接两个查询语句  去除完全相同的数据


　　　　　　union all: 连接两个查询语句，不去除完全相同的记录


　　　　　    要求：被合并的两个结果：列数、列类型必须相同。


2.连接查询


　　连接查询就是求出多个表的乘积  eg：t1连接t2,那么查询出来的结果为t1*t2


　　




select * from t1,t2;


day15(mysql 的多表查询，事务)




　　连接查询会产生  笛卡尔积


　　假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。


　　为了去除笛卡尔积给我们带来的多余的数据   我们使用连接查询 给了一个约束条件（外键约束）


　　为了更好的体验连接查询的约束条件 我们重新建了两个表




create table classes(

           cid int primary key auto_increment,

           cname varchar(20),

            cnum int

);

insert into classes values(null,'01班'，20);

insert into classes values(null,'02班'，30);

insert into classes values(null,'03班'，40);

insert into classes values(null,'04班'，41);   
create table student(
　　　　　　sid int primary kay auto_increament,
　　　　　　cname varchar(20),
　　　　　　sex varchar(1),
　　　　　　brithday date,
　　　　　　cno int,
　　　　　　constraint fk_st_c1 foreign key (cno) references classes (cid)
);


/*给student表添加数据*/
INSERT INTO student VALUES(NULL,'张三','男','1990-09-01',1);
INSERT INTO student VALUES(NULL,'李四','女','1991-02-13',1);
INSERT INTO student VALUES(NULL,'王五','男','1990-03-12',1);
INSERT INTO student VALUES(NULL,'赵六','男','1992-02-12',2);
INSERT INTO student VALUES(NULL,'田七','男','1994-05-21',2);
INSERT INTO student VALUES(NULL,'张五','女','1990-05-17',2);
INSERT INTO student VALUES(NULL,'张老七','女','1990-06-17',3);
INSERT INTO student VALUES(NULL,'王老四','女','1990-04-12',3);
INSERT INTO student VALUES(NULL,'李六','男','1990-09-12',1);　　




　　班级表    　　　　　　　　　　　　学生表


day15(mysql 的多表查询，事务)                          day15(mysql 的多表查询，事务)


学生表中的cno字段的外键是班级表中的cid


这样我们使用连接查询会产生（班级表的条数*学生表的条数=4*9=36）




SELECT COUNT(*) FROM classes ,student 


day15(mysql 的多表查询，事务)




　　加上约束条件使没用的数据过滤掉  使用where关键字




SELECT COUNT(*) FROM classes c,student s  WHERE c.cid=s.cno;


day15(mysql 的多表查询，事务)




　　使无效数据过滤掉，所以union 一般我们不会使用


　　因此产生了内连接和外连接查询


　　内连接分为：   (特点  查询条件必须满足)


　　　　显式内连接（inner join   .....on）   其中  inner关键字可以省略


　　　　　　　　




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


SELECT * FROM classes c  JOIN student s ON c.cid=s.cno; /*省略inner关键字*/


day15(mysql 的多表查询，事务)




　　　　隐式内连接


　　　　　　就是我们刚才提到的连接查询


　　　　




SELECT * FROM classes c,student s  WHERE c.cid=s.cno;


day15(mysql 的多表查询，事务)




　　　　外连接


　　　　　　左外连接:  左表作为基础


　　　　　　　　left outer join   on




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




　　把班级表作为基础  查询出来的结果为：


day15(mysql 的多表查询，事务)


　　　　　　右外连接:  右表作为基础


　　　　　　　　right outer join on




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




　　 把学生表作为基础  查询出来的结果为:


day15(mysql 的多表查询，事务)


使用一张图来总结左连接和右连接


　　　　day15(mysql 的多表查询，事务)


3.子查询


　　子查询：一个select语句中包含了另一个select语句


　　　　子查询的位置


　　　　　　where后   ：作为条为被查询的一条件的一部分


　　　　　　from 后     ：   作表；


　　 当子查询出现在where后作为条件时，还可以使用如下关键字：


    1. 

    1. any
      2. 

    2. all
      3. 



　　　　　　子查询结果集的形式：


      • 

      1. 单行单列（用于条件）
        2. 

      2. 单行多列（用于条件）
        3. 

      3. 多行单列（用于条件）
        4. 

      4. 多行多列（用于表）
        5. 



　　　　




查询学生生日在91年之后的班级的信息。

select * from classes where cid in (SELECT cno FROM student WHERE birthday > '1991-01-01');


day15(mysql 的多表查询，事务)


带exists的子查询

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

select * from classes where exists (SELECT cno FROM student WHERE birthday > '1991-01-01');


day15(mysql 的多表查询，事务)


带any的子查询

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


day15(mysql 的多表查询，事务)


带all的子查询

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


day15(mysql 的多表查询，事务)




　　事务


事务的特性（面试题）


原子性：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。


一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。转账前和转账后的总金额不变。


隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。


持久性：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。


4、事务的隔离级别


赃读：指一个事务读取了另一个事务未提交的数据。


不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。一个事务读取到了另一个事务提交后的数据。（update）


虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。 (insert)


数据库通过设置事务的隔离级别防止以上情况的发生：


* 1、READ UNCOMMITTED: 赃读、不可重复读、虚读都有可能发生。


* 2、READ COMMITTED: 避免赃读。不可重复读、虚读都有可能发生。（oracle默认的）


* 4、REPEATABLE READ:避免赃读、不可重复读。虚读有可能发生。（mysql默认）


* 8、SERIALIZABLE: 避免赃读、不可重复读、虚读。


级别越高，性能越低，数据越安全


mysql中：


查看当前的事务隔离级别：SELECT @@TX_ISOLATION;


更改当前的事务隔离级别：SET TRANSACTION ISOLATION LEVEL 四个级别之一。


设置隔离级别必须在事务之前


练习：设置事务的隔离级别为 read uncommitted







时间




线程1




线程2




说明








t1




begin;












t2




select * from account where name='zs';


结果1000块












t3






begin;










t4






update account set money=money+100 where name='zs';










t5




select * from account where name='zs';


结果1100块






读到了另一个线程未提交事务的数据。赃读发生了








t6






commit;










t7




select * from account where name='zs';


结果1100块






读到了另一个线程提交事务的update数据。不可重复读发生了








t8






insert into account values(4,'zl',1000);


执行insert语句插入数据，事务自动提交了










t9




select * from account;


查到4条数据






读到了另一个线程自动提交事务的insert语句数据。虚读发生了








t10




commit;




 



上一篇：cookie  -- 添加删除

下一篇：Springboot集成SpringData JPA