MySQL数据库(三)

  • A+
所属分类:SQL MySql

内容介绍

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

范式(理解)

范式:设计数据库表的规则

好的数据库表设计对数据的存储和后期的程序开发,都会产生重要的影响。 建立科学的,规范的数据库表就需要满足一些规则,这些规则就称为范式

范式的分类: 第一范式(1NF) 第二范式(2NF) 第三范式(3NF) 巴斯-科德范式(BCNF) 第四范式(4NF) 第五范式(5NF,又称完美范式) 满足最低要求的范式是第一范式(1NF)在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF), 其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。

三大范式:

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

小结:

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

多表查询

多表查询:从多张表中获取到有关系的数据

比如:查询分类信息以及分类下的所有商品

多表查询的语法分类:

1.交叉查询(避免的)

2.内连接查询

隐式内连接

显示内连接

3.外连接查询

左外连接连接

右外连接查询

4.子查询

交叉连接(了解)

select * from 表1,表2; 会产生2张表的乘积数据,简称笛卡尔积数据

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

内连接查询

隐式内连接:

语法格式:select * from 表1,表2 where 关联条件 and 筛选条件 and..;

ps:可以使用表别名简化书写

显示内连接

语法格式:select * from 表1 [inner] join 表2 on 关联条件 where 筛选条件 and ….;

on: 后跟的是关联条件

where: 后跟的是对结果的筛选条件

需求 :查询唐僧的信息,显示员工id,姓名,性别,工资和所在的部门名称

可以使用内连接去查询2张表之间有关系的数据

外连接查询

左外连接: 以join 左边的表为主,查询出来的是2张表之间有关系的数据以及左边表所有的数据

如果右表中没有满足条件的对应数据,则填充 null

语法格式:select * from 表1 left [outer] join 表2 on 关联条件 where 筛选条件;

右外连接: 以join 右边的表为主,查询出来的是2张表之间有关系的数据以及右边表所有的数据

如果左表中没有满足条件的对应数据,则填充null

语法格式:select * from 表1 rigth [outer] join 表2 on 关联条件 where 筛选条件;

子查询

用处1:一条sql执行的结果是另一条sql的条件,适用于单列单值或者单列多值

单列单值: select * from 表名 where (子查询结果)

单列多值: select * from 表名 where (子查询结果)

需求:

1 查询工资最高的员工是谁 (结果是单列单值)

2 查询工资小于平均工资的员工有哪些 (结果是单列单值)

3 查询工资大于5000的员工部门ID,来自于哪些部门(结果是单列多值)

4 查询开发部与财务部所有的员工信息(结果是单列多值)

用处2:一条sql执行的结果作为一张表存在,适用于多列多值

多列多值: select * from 表名,(子查询结果) where 条件

需求:

查询出2011-01-01以后入职的员工信息,包括部门名称(结果是多列多值)

事务技术

在实际的业务开发中,有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次 访问数据库的操作视为一个整体来执行,要么所有的SQL语句全部执行成功。 如果其中有一条SQL语句失败,就进行事务的回滚,所有的SQL语句全部执行失败。

例如转账:
    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 ;
如何控制2sql语句要不全部算成功,要不全部算失败呢?
我们得使用数据库提供的事务技术帮我们达成目的

事务技术的作用:

可以控制一件完整事情的多个步骤

只要有一个步骤出现了错误就算整件事情是失败了,只要所有步骤全部成功才控制当前这件事情是成功的

需求:创建账户表,通过事务技术完成转账的控制

事务操作

mysql中默认每一条sql语句都会自动提交 但是我们得让每一条sql语句都得有自己手动提交,所以要关闭自动提交

进行事务操作:

start transaction;开启一个事务(关闭自动提交)

commit; 提交事务并关闭当前的手动提交

rollback;事务回滚并关闭当前的手动提交

结论:我们可以将一个需要操作多次数据库的业务放入到一个事务中处理,方便我们管理该业务对数据库数据的每一次影响

了解:更改数据库的提交方式

show variables like ‘%autocom%’ set autocommit=off

但是该方式只能是临时的

了解:事务可以定义回滚点 (A库的数据—B库的数据) savepoint 回滚点名字; rollback to 回滚点名字;

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

原子性(Atomicity)

事务是一个不可分割的单位,事务中的操作要么都成功,要么都失败。

一致性(Consistency)

事务前后数据的完整性必须保持一致

持久性 (Durability)

指一个事务一旦被提交,它对数据库中数据的改变就是永久性的

隔离性(Isolation)

是指多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离,不能相互影响。

事务的隔离性

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

脏读 : 读未提交的数据 (必须避免)

一个事务读取到了另一个事务中尚未提交的数据

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

一个事务中两次读取的数据内容不一致

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

一个事务中两次读取的数据的数量不一致

解决方法

数据库的隔离级别

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

查询mysql的隔离级别

show variables like "%isolation%";  

设置事务的隔离级别

set global transaction isolation level 级别字符串;

1.读未提交 (最低级别,以上情况均无法保证)

read uncommitted:

2.读已提交 (可避免脏读情况发生。)

read committed

3.可重复读 (可避免脏读、不可重复读情况的发生。 mysql的默认隔离级别)

repeatable read

4.串行化 (可避免脏读、不可重复读、幻读(虚读)情况的发生)

使用serializable隔离级别,一个事务没有执行完,其他事务的SQL执行不了

serializable

  • 资源分享QQ群
  • weinxin
  • 官方微信公众号
  • weinxin
沙海
专业承接各类企业网站的开发和维护
C语言速查手册
TripodCloud:性价比最高的CN2 GIA服务器
网站https安全证书安装,伪静态配置

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: