关系数据库Mysql学习（三）

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

• 一、数据库三范式
• 二、表连接   
• 三、视图
• 四、事务

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、数据库三范式

1. 数据表中每一列的值不可拆分，即具有原子性
2. 表中的列与主键有依赖关系
3. 表中其他非主键列之间不能有依赖关系

二、表连接

1.内连接

（1）在进行多表查询时，需要进行表的连接。

select * from stu,sc,course;-- 在表连接时没有指定条件，会进行笛卡尔运算后输出

（2）连接特点：连接的表中的数据必须同时满足连接条件才能进行数据的显示（如果一个表在连接的另一个表中没有匹配数据，数据不展示）

（3）内连接的两种写法

-- 第一种连接
select * from student,sc2 where student.sno=sc2.sno;
-- 第二种连接
select * from student inner join sc2;-- 会出现笛卡尔积现象

（4）使用内连接时指定条件是通过on进行指定

select * from student inner join sc2 on student.sno=sc2.sno;

2.外连接

（1）左外连接

左表（主表） left outer(可以省略) join 右表(从表)；此时主表中的数据全部显示，如果在从表中有对应的数据，进行从表数据显示；如果从表中没有对应的数据，用null值填充。

select * from student left join sc2 on student.sno=sc2.sno;
select * from student left join sc2 on student.sno=sc2.sno where cno is null;

(2)右外连接

左表(从表) right outer(可以省略) join 右表(主表)

select * from student right join sc2 on student.sno=sc2.sno;

3.自连接

当前的表自己进行连接，此时要给表起别名；

  select * from student a,student b where a.sno=b.sno;

小结：在进行表连接时，不仅仅可以两个表进行连接，还可以进行多表连接。

三、视图

在进行表连接时，语句书写比较复杂，语句比较长；此时就会创建视图。

（1）视图：把表连接的关系进行存储，不进行数据的存放，如果频繁使用表连接，可以把表连接的关系存储到视图中。不占用真实物理存储空间

（2）创建视图注意事项：创建视图和查询语句之间用as连接，as不能省略；创建视图时不能出现重复的列名；视图的名字不能和已有视图名相同，也不能和表名相同。

（3）视图创建

create view stuview as
select student.sno,sname,sage,ssex,dept,grade,course.cname from student,sc2,course where student.sno=sc2.sno and course.cno=sc2.cno;
> OK

(4)视图查询

 select * from stuview;

1·视图只是存储关系，不进行真实数据存放；

2·表中添加数据时，如果满足进行视图创建表连接的关系，数据将会在视图中显示；

3·表中列进行删除时，视图不能打开，引用的列找不到；

所以视图在进行数据查看时方便，一般不在视图中进行添加修改和删除操作。

4·单表也可以有视图，但一般不给单表创建视图

四、索引

（1）引入目的：加快数据查询的效率

（2）什么时候要创建索引：如果频繁根据某个列进行数据查询；

（3）索引个数最好在5个以内

（4）表连接用索引的列进行连接会加快连接效率

（5）explain 查询的语句 分析语句是如何执行的

mysql>  explain  select * from user where username="root";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
各字段的意思：
（1）select_type
SIMPLE：简单查询
（2）type(查询类型)：
const 如果是根据主键或唯一索引，只取出确定的一行数据。是最快的一种
ref 非唯一性索引键的查询
all 不做索引，全表查询
（3）rows(查询到需要的数据，进行多少趟数据搜索)

1.索引创建

 mysql> create index t1(索引名) on user(username);

2.索引类型

（1）普通索引

（2）唯一索引：当前列上的值是惟一的

（3）联合索引：联合多个列进行索引的创建

mysql> create index t1 on user(username,password);
如果进行条件指定时，条件中包含username，使用索引，没有包含username不使用索引

查询原则：最左优先原则，和创建索引列的顺序有关系；

3.索引失效的情况

（1）逻辑查询中用逻辑运算符 or ，索引失效 ；and 可以进行索引的使用

（2）如果在模糊查询中，数据在表中占到一半以上，索引失效

（3）模糊查询中，如果第一个字母是匹配符，索引失效

五、事务

事务：一个单元的语句。

注意：事务是一组代码的完成，是进行一个流程的表示，流程中每一个语句必须同时完成事务才能成功，否则失败。

1.事务的特性

（1）原子性：一个事务执行的过程不可拆分；

（2）一致性：保持数据出入的一致性；

（3）隔离性：多个事务之间操作互不干扰；

（4）持久性：事务一旦提交，数据就会在磁盘中进行持久化存储。

2.有关事务的操作

（1）开启事务 begin;

（2）事务回滚：rollback，只能在事务没有提交之前回滚

（3）事务提交：commit;

（4）事务自动提交关闭：set autocommit=0;

3.事务的隔离级别

（1）读未提交

两个客户端，一个事务没有提交，另一个读取

命令行和Navicat
-- 自动提交关闭：
（1）两个客户端选中数据库
（2）两个客户端set autocommit=0;
（3）两个客户端设置隔离级别：set transaction_isolation="read-uncommitted";
（4）两个客户端开启事务
（5）命令行查看表中默认值
（6）Navicat中进行数据的修改，但不提交
（7）命令行再次读取表中数据，读取到了没有提交的修改后的数据
（8）Navicat回滚rollback
（9）命令行又回到了没修改前
===》命令行读到了未提交的修改数据---》脏读
（10）事务提交，结束事务

（2）读已提交

两个客户端，一个事务提交了，另一个事务再来读

（1）show variables
    两个客户端： set autocommit=0
                set transaction_isolation="read-committed"
                begin;
（2）命令行查询初值
（3）Navicat中进行命令修改，但未提交
（4）命令行查看，不会出现脏读，隔离级别较高
（5）Navicat中事务提交commit
（6）命令行再读取一遍表中数据，读取到了修改后的数据
====》在一个事务中读取的数据不一致
---》出现现象：不可重复读
（7）命令行结束事务commit

（3）可重读

（1）show variables
两个客户端：set autocommit=0
           set transaction_isolation="repeatable-read"
           begin;
（2）命令行查看初始数据
（3）Navicat进行数据修改，但未提交
（4）命令行读取到未修改数据
（5）Navicat提交事务，命令行读取到未修改数据
（6）命令行结束一个事务commit,开启下一个事务，可查询到修改后的数据
===================================================================
（1）两个客户端同时开启事务
（2）Navicat进行主键数据修改，并提交
（3）命令行查询到的还是原数据
（4）命令行进行相同数据的修改，会报错
===》幻读

（4）串行化

（1）两个客户端：
    set autocommit=0
    set transaction_isolation="SERIALIZABLE"
（2）不要开启事务，先在Navicat开启事务，修改数据但不提交
（3）命令行开启事务，进行数据查询，会报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
（4）命令行查询除了修改那一行的任何一条数据，可以查到
===》隔离级别：锁住当前未提交的修改数据

总结