从0基础学习MySQL数据库(三)

MySQL数据库技术

十、多表查询

1.分页查询

语法：limit 开始的索引，每页查询的条数
-- 公式：开始的索引=当前的页码-1 * 每页的条数

-- 每页显示3条数纪录
select * from 表名 limit 0,3; -- 第一页
select * from 表名 limit 3,3; -- 第二页

-- 注意：起始位置从0索引开始

2.多表查询

概念

合并多个数据表的信息查询，又称为连接查询

-- 案例表
-- 创建班级表
CREATE TABLE class(
	cno INT PRIMARY KEY,-- 班级编号
	cname VARCHAR(50),-- 班级名称
	address INT -- 班级所在教室号
)
-- 学生表
CREATE TABLE student(
	sno INT PRIMARY KEY,-- 学号
	sname VARCHAR(50),-- 姓名
	job VARCHAR(50),-- 职位
	mgr INT,-- 上级的学号
	score FLOAT(5,2), -- 成绩
	cno INT, -- 班级
	CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno)
)
-- 成绩等级表
CREATE TABLE scorerank(
	scorelow INT,
	scorehigh INT,
	rank VARCHAR(50)
)CHARACTER SET utf8
INSERT INTO scorerank VALUES(260,269,'普通');
INSERT INTO scorerank VALUES(270,279,'良好');
INSERT INTO scorerank VALUES(280,289,'优秀');
INSERT INTO scorerank VALUES(290,300,'杰出');

INSERT INTO class VALUES(1,'classone',101);
INSERT INTO class VALUES(2,'classtwo',102);
INSERT INTO class VALUES(3,'classthree',103);
INSERT INTO class VALUES(4,'classfour',104);

SELECT * FROM class;
SELECT * FROM student;

INSERT INTO student VALUES(1801,'tom','banzhang',NULL,272,1);
INSERT INTO student VALUES(1802,'rose','fubanzhang',1801,265,2);
INSERT INTO student VALUES(1803,'jack','fubanzhang',1801,274,1);
INSERT INTO student VALUES(1804,'bob','math-kdb',1808,279,1);
INSERT INTO student VALUES(1805,'james','chinese-kdb',1808,267,3);
INSERT INTO student VALUES(1806,'george','english-kdb',1808,280,1);
INSERT INTO student VALUES(1807,'curry','mzuzhang',1804,270,2);
INSERT INTO student VALUES(1808,'fox','xuexiweiyuan',1802,282,2);
INSERT INTO student VALUES(1809,'simons','czuzhang',1805,270,1);
INSERT INTO student VALUES(1810,'love','luren',1807,262,3);
INSERT INTO student VALUES(1811,'tt','luren',1807,270,3);

分类

内连接

外连接

自连接

交叉连接

-- 案例：查询所有学生名和班级名，涉及到两张表
select sname,cname from student,class;

问题分析：

笛卡儿积，出现多余没用的数据

解决代码：

select sname,cname from student,class where student.cno = class.cno;

--注意：如果解决笛卡尔积现象，多表查询的规定，多表查询的条件是至少不能小于表的个数-1，加了一个条件笛卡尔积就会消失
所以两张表至少有一个条件，三张表至少有两个条件

自连接查询

是指同一张表的连接查询

-- 查询学生的上级的姓名
SELECT s1.`sname`,boss.`sname` bossname FROM student s1,student boss WHERE s1.`mgr`=boss.`sno`;

内连接

等值连接、不等值连接、自然连接

-- 等值连接 inner join  on 显示内连接，隐式内连接
SELECT sname,cname  FROM student INNER JOIN class ON student.`cno`=class.`cno`;-- 显示内连接
SELECT sname,cname FROM student,class WHERE student.cno = class.cno; -- 隐式内连接

-- 不等值连接，范围型，添加between and

-- 自然连接: natural join 所有符合条件的结果返回，自动连接符合的条件进行查询
SELECT * FROM student NATURAL JOIN class;

外连接

左外连接、右外连接

-- 左外连接
语法：select 字段列表 from 表1 left join 表2 条件  左表为主
SELECT s.`sname`,c.`cname`,s.`id` FROM student s LEFT JOIN class c ON s.`cno` = c.`cno`;

-- 右外连接，和左外类似
SELECT s.`sname`,c.`cname`,s.`id` FROM student s RIGHT JOIN class c ON s.`cno` = c.`cno`;

交叉连接

就是笛卡尔积 cross join

SELECT COUNT(*) FROM student CROSS JOIN class;

3.子查询

嵌套查询，select语句中包含select

如果一条语句中包含两个或者以上的select，那么这样的语句叫子查询

子查询出现的位置：

​ 1.where后，作为条件的一部分

​ 2.from后，作为被查下的一张表

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

​ all

​ any

子查询结果集的形式：

单行单列、单行多列、多行单列、多行多列

-- 单行单列：指返回一行一列数据的子查询语句
-- 案例：成绩大于bob的同学，第一步查询bob的成绩，第二步查询高于bob成绩的同学
SELECT score FROM student WHERE sname='bob'; 
SELECT * FROM student WHERE score>(SELECT score FROM student WHERE sname='bob');

-- 案例2：与tom在一个班级的同学
SELECT cno FROM student WHERE sname='tom';
SELECT cno,sname FROM student WHERE cno=(SELECT cno FROM student WHERE sname='tom');

-- 多行单列：返回多行数据的子查询
-- 案例：查询高于三班所有人成绩的同学，其中三班所有人成绩就是子查询
SELECT score FROM student WHERE cno=3;
SELECT * FROM student WHERE score > ALL(SELECT score FROM student WHERE cno=3);
-- 案例2：查询比三班任意一个人成绩高的同学名字，成绩，班级号
SELECT * FROM student WHERE cno=3;
SELECT sname,score,cno FROM student WHERE score > ANY(SELECT score FROM student WHERE cno=3);


-- 单行多列的查询
-- 案例：查询与tt班级和职务都相同的同学
SELECT job,cno FROM student WHERE sname='tt';
SELECT  * FROM student WHERE (job,cno) = (SELECT job,cno FROM student WHERE sname='tt');

-- 多行多列的查询
--案例：查询出高于自己班级平均成绩的同学信息
-- 第一步：查询出各个班级的平均成绩和班级号
SELECT AVG(score),cno FROM student GROUP BY cno;
-- 第二步：把上面的查询作为一张子表查询
SELECT s1.`sname`,s1.`score`,s1.`cno`,s2.myscore FROM student s1,(SELECT AVG(score) myscore,cno FROM student GROUP BY cno) s2
		WHERE s1.`cno`=s2.cno AND s1.`score`>s2.myscore;

十一、MySQL事务处理

1.TCL语言：

Transanction Control Language 事务控制语言

事务：一个或一组SQL语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

2.事务有哪些特点：

事物的ACID属性：

• **原子性(Atomicity)😗*是指事务是一个不可分割的工作单元，事务在执行时，要做的“要么不做，要么全做！” 这也是事务最重要的一点
• **一致性(Consistency)😗*必须使用数据库从一个一致性状态变换到另一个一致性状态。就是事务操作之后，数据库所处的状态和业务规则是一致的，比如a，b账户相互转账之后，总金额保持一致
• **隔离性(Isolation)😗*是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作以及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之前不能互相干扰，如果 多个事务并发执行，各个事务独立执行的
• **持久性(Durability)😗*是指一个事务一旦被提交，它对数据库中的数据的改变就是永久性的，接下来的其他操作和数据故障不应该对其有任何影响，数据一旦提交后就被持久化到数据库；

3.事务的创建

隐式事务：事务没用明显的开启和结束的标记

比如insert、update、delete语句

show variables like 'autocommit';
-- 结果autocommit 为 on 默认自动提交是开启的

显示事务：事务具有明显的开启和结束的标记

前提：必须先设置自动提交功能为禁用

set autocommit = 0; -- 禁止自动提交
set autocommit = 1; -- 开启自动提交

-- 来实现事务的处理

4.事务执行的步骤：

1.开启事务，当我们开启一个事务的时候，我们对sql的操作都发生在内存中，但是没用真正的反馈到数据库磁盘的文件中！

set autocommit = 0;
start transaction;-- 可选的

2.编写事务中的sql语句（select insert update delete）;

3.如果执行成功，就提交commit

4.如果有一条sql语句执行失败，就回滚rollback！

回滚：恢复到事务开启之前的状态

注意：回滚会自动关闭一个事务，如果想再次执行事务，需要重新开启事务！

5.事务的隔离级别

数据库的隔离级别：
有一种情况，对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题；
脏读：对于两个事务 T1、T2，T1 读取了已经被 T2 更新但还没有被提交的字段，之后，若 T2回滚，T1读取
的数据就是临时且无效的数据；
不可重复读：对于两个事务 T1、T2，T1 读取了一个字段，然后 T2 更新了该字段，之后，T1 再次读取同一个字段，值就不同了；
幻读：对于两个事务 T1、T2，T1从一个表中读取了一个字段，然后 T2在该表中插入了一些新的行，之后，如果 T1 再次读取同一个表时，就会多出几行；

4种隔离级别：

隔离级别	描述
Read Uncommitted：读取未提交数据	允许事务读取未被其他事务提交的变更、脏读、不可重复读和幻读的问题都会出现
Read Committed：读取提交内容	只运行事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读问题仍然可能出现
Repeatable Read：可重复读	确保事务可以多次从一个字段中读取相同的数据，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读的问题仍然存在
Serializable：可串行化	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除的操作，所有并发问题都可避免，但性能低下

mysql默认的事务隔离级别为：Repeatable Read、

oracle默认的隔离级别为：read committed

-- 查询方法
select @@tx_isolation;

-- 设置
set session tx_isolation='read-uncommitted';

	脏读	不可重复读	幻读
读未提交read-uncommitted	√	√	√
读已提交read-committed	×	√	√
可重复读repeatable-read	×	×	√
serializable	×	×	×

关于mysql四种事务隔离的应用我会后续在新的文章中展示

十二、视图

1.基本概念

视图又称为虚拟表，和普通表在使用上不一样，从mysql5.1版本之后出现的新特性，才开始提供视图的功能，行和列的数据来自定义的视图查询中使用的原始表，并且是在使用视图时动态生成的数据，只保存了sql逻辑，不保存查询的结果

2.使用场景

• 多个地方用到同样的查询结果；
• 该查询结果使用的sql语句比较复杂；

3.案例

-- 查询学生学号、学生名、和所在班级名
SELECT s.`sno`,s.`sname`,c.`cname` FROM student s,class c WHERE s.`cno`=c.`cno`;
-- 为了简化操作，用视图解决
CREATE VIEW myv1 AS SELECT s.`sno`,s.`sname`,c.`cname` FROM student s,class c WHERE s.`cno`=c.`cno`;
-- 查询
SELECT * FROM myv1;

4.好处

• 重用sql语句
• 简化复杂的sql操作，不必知道它的具体查询细节
• 保护数据，提高安全性，视图与原始表分离

5.创建视图

create view 视图名称 as 查询语句

-- 案例1：查询学生姓名带o字符的学生名、班级名称和职位名称
SELECT s.`sname` ,c.`cname`,s.`job` FROM student s,class c WHERE s.`sname` LIKE '%o%' AND s.`cno`=c.`cno`;
-- 使用视图
CREATE VIEW myv2 AS SELECT s.`sname` ,c.`cname`,s.`job` FROM student s,class c WHERE s.`sname` LIKE '%o%' AND s.`cno`=c.`cno`;
-- 简化后
SELECT * FROM myv2;

-- 案例2：查询各个班级的平均成绩级别
-- 第一步，查询各个班级的平均成绩
SELECT AVG(score),cno FROM student GROUP BY cno;
-- 第二步，查询平均成绩级别
SELECT d.ag,sr.`rank` FROM scorerank sr JOIN (SELECT AVG(score) ag,cno FROM student GROUP BY cno) d ON d.ag BETWEEN sr.`scorelow` AND sr.`scorehigh`;

-- 创建视图
CREATE VIEW myv3 AS SELECT d.cno,d.ag,sr.`rank` FROM scorerank sr JOIN (SELECT AVG(score) ag,cno FROM student GROUP BY cno) d 
			ON d.ag BETWEEN sr.`scorelow` AND sr.`scorehigh`;
-- 使用视图
SELECT * FROM myv3;

-- 案例3：根据案例2查询平均成绩最低的班级信息
SELECT * FROM myv3 ORDER BY ag LIMIT 1;

6.视图的修改

-- 方式一：替换
create or replace view 视图名 as 查询语句;

-- 案例：替换myv2
CREATE OR REPLACE VIEW myv2 AS SELECT AVG(score),cno FROM student GROUP BY cno;

-- 方式二：修改
alter view 视图名 as 查询语句;

-- 案例：修改视图
ALTER VIEW myv2 AS SELECT * FROM student s WHERE s.cno=1;
SELECT * FROM myv2;

7.视图删除

-- 语法
drop view 视图名

-- 案例
DROP VIEW myv2;

DROP VIEW myv1,myv3;

8.查看视图

DESC myv2;
SHOW CREATE VIEW myv2;
-- 和普通表的语法一样

9.修改视图数据

-- 添加数据
CREATE VIEW myv3 AS SELECT sno,sname,job FROM student;
SELECT * FROM myv3;
INSERT INTO myv3 VALUES(1813,'李四','luren');
SELECT * FROM student; -- 学生表也会改变

--修改数据
UPDATE myv3 SET sname='赵四' WHERE sno=1813;

--删除数据
DELETE FROM myv3 WHERE sno=1813;

-- 注意：
-- 视图一般不做跟新，只读数据

10.视图与表的区别

表要占用磁盘空间，视图不占用

视图不能添加索引

使用视图可以简化查询

提高安全性

区别	创建语法	是否实际占用物理空间	使用
视图	create view	只是保存了sql逻辑	增删改查，只是一般不能提供增删改
表	create table	保存了数据	增删改查