目录
1 多表查询分类
将多个表的数据横向的联合起来。1）内连接；2）外连接：左外连接和右外连接；3）交叉连接；4）自然连接。
1.1 内连接（inner join）
只返回两个表中连接字段相等的行。
语法一：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
语法二：select 列名 from 表1,表2 where 表1.公共字段=表2.公共字段
注意：显示公共字段需要指定表名。
不指定表名会报错。
可以给表取别名
思考：select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 和
select * from 表2 inner join 表1 on 表1.公共字段=表2.公共字段 是否一样？
答：结果一样。
注意：三个表的内连接怎么实现？
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
inner join 表3 on 表2.公共字段=表3.公共字段
1.2 左外连接（left join）
以左边的表为标准，如果右边的表没有对应的记录，用NULL填充。
返回包括左表中的所有记录和右表中连接字段相等的记录。
思考：
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 left join 表1 on 表1.公共字段=表2.公共字段 是否一样？
答：不一样，左连接以左边的表为准。
1.3 右外连接（right join）
以右边的表为标准，如果左边的表没有对应的记录，用NULL填充。
返回包括右表中的所有记录和左表中连接字段相等的记录。
语法：select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段
思考：
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 right join 表1 on 表1.公共字段=表2.公共字段 是否一样？
答：一样的
1.4 交叉连接
插入测试数据
1、如果没有连接表达式返回的是笛卡尔积
2、如果有连接表达式等价于内连接
1.5 自然连接（natural）
自动的判断连接条件，通过名字字段来判断。
自然连接又分为：
1. 自然内连接 natural join
2. 自然左外连接 natural left join
3. 自然右外连接 natural right join
eg:
1）自然内连接
2）自然左外连接
3）自然右外连接
自然连接结论：
1）表连接通过同名的字段来连接；
2）如果没有同名的字段返回笛卡尔积；
3）会对结果进行整理，整理的规则如下：
- 连接字段保留一个
- 连接字段放在最前面
- 左外连接左边在前，右外连接右表在前
1.6 using()
1.用来指定连接字段；
2.using()也会对连接字段字段进行整理，整理方式和自然连接是一样的。
1.7 子查询
语法： select 语句 where 条件 （select ... from 表）
1. 外面的查询称为父查询，括号里的查询称为子查询；
2.子查询为父查询提供查询条件。
1.7.1 例题
eg:查找笔试77分的学生信息
查找笔试最高分的学生
1）
2）
注意：上面的例题，子查询只能返回一个值。如果子查询返回多个值就不能用“=”了，需要用in
1.7.2 in | not in子查询
用于子查询的返回结果多个值
1、查找笔试成绩及格的学生
2、查询不及格的同学
3、查询没有通过的同学（不及格、缺考）
1.7.3 exists和not exists
1、如果有人笔试超过70分就显示所有的学生
2、如果没人超过70分就显示所有的学生
1.7.4 子查询分类
1、标量子查询：子查询返回的结果就一个；
2、列子查询：子查询返回的结果是一个列表；
3、行子查询：子查询返回的结果是一行。
例题：查询成绩最高的男生和女生
4、表子查询：子查询返回的结果是一个表
例题：查询成绩最高的男生和女生
2 视图
1、视图是一张虚拟表，他表示一张表的部分或多张表的综合的结构。
2、视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。
2.1 创建视图
语法： create [or replace] view 视图的名称 as select语句
注意：因为视图是一个表结构，所以创建视图后，会在数据库文件夹中多一个与视图名同名的.frm文件。
2.2 使用视图
视图是一张虚表，视图的用法和表的用法一样。
2.3 查看视图的结构
语法：desc 视图名
例题：
2.4 查看创建视图
语法：show create view 视图名
2.5 显示所有视图
方法一：
方法二：
方法三：
2.6 更改视图
语法：alter view 视图名 as select 语句
2.7 删除视图
语法：drop view [if exists] 视图1, 视图2,...
2.8 视图的作用
1. 筛选数据，防止未经许可访问敏感数据
2. 隐藏表结构
3. 降低SQL语句的复杂度
2.9 视图的算法
场景：找出语文成绩最高的男生和女生
我们可以将子查询封装到视图中
可以将上面的子查询更改成视图，但是，结果和上面不一样
原因：这是因为视图的算法造成的
- merge：合并算法，将视图的语句和外层的语句合并后在执行。
- temptable：临时表算法，将视图生成一个临时表，再执行外层语句
- undefined：未定义，MySQL到底用merge还是用temptable由MySQL决定，这是一个默认的算法，一般视图都会选择merge算法，因为merge效率高。
解决：再创建视图的时候指定视图的算法
语法：create algorithm=temptable view 视图名 as select 语句
指定算法创建视图
3 事务
1、事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作；
2、事务是一个不可分割的执行单元；
3、事务作为一个整体要么一起执行，要么一起回滚。
插入测试数据：
3.1 事务操作
事务：
- 一个最小的不可再分的工作单元；
- 通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)；
- 一个事务包含一条或多条DML语句(insert,update,delete)；
- 在一个事务中要么所有的语句都成功执行，要么都失败，即所有的DML语句都成功执行才会修改硬盘数据。
开启事务：start transaction 或 begin [work]
提交事务：commit
回滚事务：rollback
例题：
delimiter // #更改定界符
思考：事务什么时候产生？什么时候结束？
答：开启的时候产生，提交事务或回滚事务都结束。
注意：只有innodb和BDB才支持事务，mysiam不支持事务。
3.2 设置事务的回滚点
语法： 设置回滚点：save point 回滚点名
回滚到回滚点：rollback to 回滚点
3.3 事务的特性（ACID）
1、原子性（atomicity）：事务是一个整体，不可以再分，要么一起执行，要么不一起执行；
2、一致性（consistency）：事务完成时，数据必须处于一致的状态；
3、隔离性（isolation）：每个事务都是相互隔离的；
4、永久性（durability）：事务完成时，对数据的修改是永久性的。
3.4 事务的隔离级别
- read uncommitted 读未提交
- read committed 读以提交
- repeatable read 可重复读
- serializable 串行化
这四个隔离级别逐渐增高。
① read uncommitted：事务A未提交的数据，事务B也可以读取到，这种隔离级别最低。这种事务会导致"dirty read(脏读)"。因为事务A的数据还没有提交，事务B就可以读取到，那如果事务A在事务B读取后回滚了呢，就导致了事务B读取到的数据是"脏数据"。
② read committed：事务A未提交的数据，事务B读取不到，事务A提交后的数据事务B才能读取到。这个事务级别不会导致"dirty read"，但会导致"不可重复读"。假设事务A需要半天，在这期间有很多的其它事务都在修改数据，那么就导致了一个问题，事务A在开启时读到的数据与半天后读到的数据差别很大，那么事务A需要在这半天内读到的数据都是一样的该怎么办，比如每个月底网络运营商系统出账的时候，那肯定得在出账期间读到的数据都必须一样才行。
③ repeatable read：事务A提交后的数据，事务B读取不到，事务B读取的数据依旧是事务B刚开始时的数据。MySQL的事务默认是这个级别。
④ serializable：事务A在执行的时候，事务B只能等待，就是说当多个事务需要执行时，只能排队一个个的来，就是串行化的字面意思了。这种隔离级别最高，但会导致数据库的吞吐量很低一般不用。
4 索引
索引的优点：查询速度快
索引的缺点：
- 1. 增、删、改（数据操作语句）效率低了
- 2. 索引占用空间
4.1 索引的类型
- 1、普通索引
- 2、唯一索引
- 3、主键索引：只要主键就自动创建主键索引，不需要手动创建。
- 4、全文索引：搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。
4.2 创建普通索引
语法： create index [索引名] on 表名 （字段名）
alter table 表名 add index [索引名称] （列名）
例题：
#创建索引方法一
#创建索引方法二
#创建表的时候就添加索引
4.3 创建唯一索引
语法一：create unique index 索引名 on 表名 （字段名）
语法二：alter table 表名 add unique [index] [索引名] （列名）
语法三：创建表的时候添加唯一索引，和创建唯一键是一样的
例题：
1）
2）
3）
4.4 删除索引
语法：drop index 索引名 on 表名
4.5 创建索引的指导原则
1、该列用于频繁搜索；
2、该列用于排序；
3、公共字段要创建索引；
4、如果表中的数据很少，不需要创建索引。MySQL搜索索引的时间比逐条搜索数据的时间要长；
5、如果一个字段上的数据只有几个不同的值，该字段不适合做索引，比如性别。
5 函数
5.1 数字类
5.2 字符类
coalesce(字段1，字段2) 如果字段1不为空就显示字段1，否则，显示字段2
5.3 时间类
5.4 加密函数
5.5 判断函数
语法：if(表达式1,值1,值2)
例题：
6 预处理
预编译一次，可以多次执行。用来解决一条SQL语句频繁执行的问题。
预处理语句：prepare 预处理名字 from 'sql语句'
执行预处理：execute 预处理名字 [using 变量]
例题一：
例题二：传递参数
注意：
1）?是位置占位符；
2）变量以@开头；
3）通过set给变量赋值。
例题三：
7 存储过程
7.1 存储过程的优点
1）存储过程可以减少网络流量；
2）允许模块化设计；
3）支持事务。
7.2 创建存储过程
语法：create procedure 存储过程名（参数） begin // sql语句 end;
注意：由于过程中有很多SQL语句，每个语句的结束都要用（；）结束。默认情况下，分号既表示语句结束，又表示向服务器发送SQL语句。我们希望分号仅表示语句的结束，不要将SQL语句发送到服务器执行，通过delimiter来更改结束符。
例题：
7.3 调用存储过程
语法：call 存储过程名()
7.4 删除存储过程
语法：drop procedure [if exists] 存储过程名
7.5 查看存储过程的信息
语法：show create procedure 存储过程名\G
7.6 显示所有的存储过程
语法：show procedure status \G
7.7 存储过程的参数
存储过程的参数分为：输入参数（int）[默认], 输出参数(out),输入输出参数(inout)
存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。
例题一：传递学号，获取对应的信息
例题二：查找同桌
注意：
1）通过declare关键字声明局部变量；全局变量@开头就可以了
2）给变量赋值有两种方法
法一：set 变量名=值
法二：select 字段 into 变量 from 表 where 条件
3）声明的变量不能与列名同名
例题三：输出参数
例题四：输入输出参数
练习：
1、显示地区及每个地区参加考试的人数，并按照人数降序排列。
2、显示有学生参加考试的地区
方法一：过滤
方法二：链接查询
3、显示男生和女生的人数
方法一：
方法二：
4、显示每个地区的男生和女生女生和总人数
279
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
