2018年1月11日18:17:01SQL MySql评论991字数 3113阅读10分22秒阅读模式

内容介绍

1  范式（理解）
2  多表查询（重点）
3  SQL分类之TCL（事务）

范式(理解)

范式:设计数据库表的规则文章源自JAVA秀-https://www.javaxiu.com/397.html

好的数据库表设计对数据的存储和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库表就需要满足一些规则，这些规则就称为范式文章源自JAVA秀-https://www.javaxiu.com/397.html

范式的分类：第一范式（1NF）第二范式（2NF）第三范式（3NF）巴斯-科德范式（BCNF）第四范式(4NF）第五范式（5NF，又称完美范式）满足最低要求的范式是第一范式（1NF）在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。文章源自JAVA秀-https://www.javaxiu.com/397.html

三大范式：文章源自JAVA秀-https://www.javaxiu.com/397.html

第一范式（1NF）
    规则：数据库表中的某一列如果有多个值的话，必须拆分成为不同的列
第二范式（2NF）
    规则：在满足第一范式的基础上，需要满足2点
          1 一张表只能用来描述一件事情
          2 表中的每个字段都依赖主键
第三范式（3NF）
    规则：在满足第一范式和第二范式基础上，从表的外键必须使用主表的主键（表关系建立）

小结：文章源自JAVA秀-https://www.javaxiu.com/397.html

范式: 设计数据库表的规范
1NF：数据库表的列具有原子性 列不能再次被拆分
2NF：一张表只能描述一件事情，表中得有主键
3NF：若存在关系，从表的外键必须指向主表的主键

多表查询

多表查询：从多张表中获取到有关系的数据文章源自JAVA秀-https://www.javaxiu.com/397.html

比如：查询分类信息以及分类下的所有商品文章源自JAVA秀-https://www.javaxiu.com/397.html

多表查询的语法分类：文章源自JAVA秀-https://www.javaxiu.com/397.html

1.交叉查询（避免的）文章源自JAVA秀-https://www.javaxiu.com/397.html

2.内连接查询文章源自JAVA秀-https://www.javaxiu.com/397.html

隐式内连接文章源自JAVA秀-https://www.javaxiu.com/397.html

显示内连接文章源自JAVA秀-https://www.javaxiu.com/397.html

3.外连接查询文章源自JAVA秀-https://www.javaxiu.com/397.html

左外连接连接文章源自JAVA秀-https://www.javaxiu.com/397.html

右外连接查询文章源自JAVA秀-https://www.javaxiu.com/397.html

4.子查询文章源自JAVA秀-https://www.javaxiu.com/397.html

交叉连接(了解)

select * from 表1,表2; 会产生2张表的乘积数据，简称笛卡尔积数据文章源自JAVA秀-https://www.javaxiu.com/397.html

我们发现这种方式查询出来的数据对我们来说没有任何意思
对于多表查询来说：我们最终要得到的数据其实是2张表有关系的数据
那么对于有关系的数据我们只能通过条件筛选，过滤掉没有关系的数据
所以：条件过滤对多表查询致关重要
条件过滤：可以寻找2张表之间有关联的字段进行过滤

内连接查询

隐式内连接:文章源自JAVA秀-https://www.javaxiu.com/397.html

语法格式：select * from 表1,表2 where 关联条件 and 筛选条件 and..;文章源自JAVA秀-https://www.javaxiu.com/397.html

ps:可以使用表别名简化书写文章源自JAVA秀-https://www.javaxiu.com/397.html

显示内连接文章源自JAVA秀-https://www.javaxiu.com/397.html

语法格式：select * from 表1 [inner] join 表2 on 关联条件 where 筛选条件 and ….;文章源自JAVA秀-https://www.javaxiu.com/397.html

on: 后跟的是关联条件文章源自JAVA秀-https://www.javaxiu.com/397.html

where: 后跟的是对结果的筛选条件文章源自JAVA秀-https://www.javaxiu.com/397.html

需求：查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称文章源自JAVA秀-https://www.javaxiu.com/397.html

可以使用内连接去查询2张表之间有关系的数据文章源自JAVA秀-https://www.javaxiu.com/397.html

外连接查询

左外连接: 以join 左边的表为主,查询出来的是2张表之间有关系的数据以及左边表所有的数据文章源自JAVA秀-https://www.javaxiu.com/397.html

如果右表中没有满足条件的对应数据,则填充 null文章源自JAVA秀-https://www.javaxiu.com/397.html

语法格式：select * from 表1 left [outer] join 表2 on 关联条件 where 筛选条件;文章源自JAVA秀-https://www.javaxiu.com/397.html

右外连接: 以join 右边的表为主,查询出来的是2张表之间有关系的数据以及右边表所有的数据文章源自JAVA秀-https://www.javaxiu.com/397.html

如果左表中没有满足条件的对应数据,则填充null文章源自JAVA秀-https://www.javaxiu.com/397.html

语法格式：select * from 表1 rigth [outer] join 表2 on 关联条件 where 筛选条件;文章源自JAVA秀-https://www.javaxiu.com/397.html

子查询

用处1：一条sql执行的结果是另一条sql的条件，适用于单列单值或者单列多值文章源自JAVA秀-https://www.javaxiu.com/397.html

单列单值: select * from 表名 where (子查询结果)文章源自JAVA秀-https://www.javaxiu.com/397.html

单列多值: select * from 表名 where (子查询结果)文章源自JAVA秀-https://www.javaxiu.com/397.html

需求：文章源自JAVA秀-https://www.javaxiu.com/397.html

1 查询工资最高的员工是谁 (结果是单列单值)文章源自JAVA秀-https://www.javaxiu.com/397.html

2 查询工资小于平均工资的员工有哪些 (结果是单列单值)文章源自JAVA秀-https://www.javaxiu.com/397.html

3 查询工资大于5000的员工部门ID，来自于哪些部门(结果是单列多值)文章源自JAVA秀-https://www.javaxiu.com/397.html

4 查询开发部与财务部所有的员工信息(结果是单列多值)文章源自JAVA秀-https://www.javaxiu.com/397.html

用处2：一条sql执行的结果作为一张表存在，适用于多列多值文章源自JAVA秀-https://www.javaxiu.com/397.html

多列多值: select * from 表名，(子查询结果) where 条件文章源自JAVA秀-https://www.javaxiu.com/397.html

需求：文章源自JAVA秀-https://www.javaxiu.com/397.html

查询出2011-01-01以后入职的员工信息，包括部门名称(结果是多列多值)文章源自JAVA秀-https://www.javaxiu.com/397.html

事务技术

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。文章源自JAVA秀-https://www.javaxiu.com/397.html

例如转账:
    1   tom  1000   
    2   rose 1000   
完成转账这个业务需要操作2次数据库     
1.从tom的帐号上减去500
     update account set money = money - 500 where id = 1 ;
2.将500加到rose帐号上
     update account set money = money + 500 where id = 2 ;
如何控制2条sql语句要不全部算成功，要不全部算失败呢？
我们得使用数据库提供的事务技术帮我们达成目的

事务技术的作用：文章源自JAVA秀-https://www.javaxiu.com/397.html

可以控制一件完整事情的多个步骤文章源自JAVA秀-https://www.javaxiu.com/397.html

只要有一个步骤出现了错误就算整件事情是失败了，只要所有步骤全部成功才控制当前这件事情是成功的文章源自JAVA秀-https://www.javaxiu.com/397.html

需求：创建账户表，通过事务技术完成转账的控制文章源自JAVA秀-https://www.javaxiu.com/397.html

事务操作

mysql中默认每一条sql语句都会自动提交但是我们得让每一条sql语句都得有自己手动提交，所以要关闭自动提交文章源自JAVA秀-https://www.javaxiu.com/397.html

进行事务操作：文章源自JAVA秀-https://www.javaxiu.com/397.html

start transaction;开启一个事务(关闭自动提交)文章源自JAVA秀-https://www.javaxiu.com/397.html

commit; 提交事务并关闭当前的手动提交文章源自JAVA秀-https://www.javaxiu.com/397.html

rollback;事务回滚并关闭当前的手动提交文章源自JAVA秀-https://www.javaxiu.com/397.html

结论：我们可以将一个需要操作多次数据库的业务放入到一个事务中处理，方便我们管理该业务对数据库数据的每一次影响文章源自JAVA秀-https://www.javaxiu.com/397.html

了解：更改数据库的提交方式文章源自JAVA秀-https://www.javaxiu.com/397.html

show variables like ‘%autocom%’ set autocommit=off文章源自JAVA秀-https://www.javaxiu.com/397.html

但是该方式只能是临时的文章源自JAVA秀-https://www.javaxiu.com/397.html

了解：事务可以定义回滚点（A库的数据—B库的数据） savepoint 回滚点名字; rollback to 回滚点名字;文章源自JAVA秀-https://www.javaxiu.com/397.html

面试题: 事务的四大特性(ACID)

原子性（Atomicity）

事务是一个不可分割的单位，事务中的操作要么都成功，要么都失败。文章源自JAVA秀-https://www.javaxiu.com/397.html

一致性（Consistency）

事务前后数据的完整性必须保持一致文章源自JAVA秀-https://www.javaxiu.com/397.html

持久性（Durability）

指一个事务一旦被提交，它对数据库中数据的改变就是永久性的文章源自JAVA秀-https://www.javaxiu.com/397.html

隔离性（Isolation）

是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。文章源自JAVA秀-https://www.javaxiu.com/397.html

事务的隔离性

问题:假如事务不具备隔离性，用户会在读取数据库数据的时候发生读取问题

脏读 : 读未提交的数据（必须避免）

一个事务读取到了另一个事务中尚未提交的数据文章源自JAVA秀-https://www.javaxiu.com/397.html

不可重复读 (针对update修改的)

一个事务中两次读取的数据内容不一致文章源自JAVA秀-https://www.javaxiu.com/397.html

虚读/幻读(针对insert插入的)

一个事务中两次读取的数据的数量不一致文章源自JAVA秀-https://www.javaxiu.com/397.html

解决方法

数据库的隔离级别

read uncommitted             读未提交    以上三个问题都会发生
read committed(oracle默认)    读已提交    避免脏读的发生
repeatable read(mysql默认)    重复读      避免脏读和不可重复的读的发生
serializable                 串行化      避免所有问题的发生