mysql最后一天

最新推荐文章于 2024-07-19 03:51:13 发布

Fazlife

最新推荐文章于 2024-07-19 03:51:13 发布

阅读量239

点赞数

本文链接：https://blog.csdn.net/qq_41260055/article/details/103318425

版权

1、事务（Trasaction）

1.1 什么是事务

一个事务是一个完整的业务逻辑单元，不可再分
比如：银行转账，从A账户向B账户转账10000，需要执行两条update语句
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';

以上两条DML语句同时成功或者同事失败，那么就需要使用数据库的“事务机制”

1.2 和事务相关的语句只有：DML语句（insert delete update）

为什么：因为它们这三个语句都是和数据库当中的数据相关
事务的存在就是为了保证数据的完整性，安全性

1.3 假设所有业务都能使用一条DML语句搞定，还需要事务机制吗不需要事务

但实际情况不是这样的，通常一个事务业务需要多条DML语句共同联合完成

1.4 事务的特性

事务包括四大特性：ACID
A: 原子性：事务是最小的工作单元，不可再分
C：一致性：事务必须保证多条DML语句同时成功或者同事失败
I：隔离性：事务A与事务B之间具有隔离
D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功

1.5 关于事务之间的隔离性

事务隔离级别：理论上4个级别
第一级别：未读提交（read uncommitted）
	对方事务还没有提交，我们当前事务可以读取到对方未提交的数据
	未读提交存在脏读（Dirty Read）现象：表示读到了脏的数据
第二级别：读已提交（Read committed）
	对方事务提交之后数据我方可以读取到
	读已提交存在的问题是：不可以重复读取
第三级别：可重复读（Repeatavle Read）
	这种隔离级别解决了：不可重复读取的问题
	存在的问题是：读取的数据是幻象
第四级别：序列化读/串行化读
	解决了所有问题
	问题：效率低，需要事务排队

oracle数据库默认的隔离级别：读已提交
mysql数据库默认的隔离级别：可重复读

1.6 演示事务

mysql事务默认情况下是自动提交的（只要执行任意一条DML语句则提交一次）
关闭自动提交语句：start transaction

准备表：
drop table if exists_user;
create table t_user(
	id int primary key auto_increment,
	username varchar(255)
);

演示：
insert into t_user values('zs');
select * from t_user;

rollback;//回滚
select * from t_user;

演示：使用start transaction关闭自动回滚
start transaction;
insert into t_user values('ls');
select * from t_user;

insert into t_user values('we');
select * from t_user;

rollback;//这里事务结束了 如果需要，则必须重新start transaction
select * from t_user;

演示：演示提交
start transaction;

insert into t_user values('ls');
insert into t_user values('we');

select * from t_user;

commit;

rollback;

注意：在id的自增用过的数字 在回滚之后用过的数字就不能用了

添加保存点：
savepoint 点名;
rollback 点名;

1.7 使用两个事务演示以上的隔离级别

第一：演示read uncommitted  读未提交
设置事务的隔离级别：set global transaction isolation level read uncommitted;
查看事务的全局隔离级别：
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+

第二：演示Read committed    读已提交
设置事务的隔离级别：set global transaction isolation level read committed;
查看事务的全局隔离级别：
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+

。。。。。

2、索引

2.1 什么是索引有什么用

索引相当于一本书的目录，通富哦索引快速找到对应的数据
在数据库方面，查询一张表的有两种索引方式：
第一：全面扫面
第二：根据索引检索

缩小扫描范围

索引虽然可以提高效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，
也需要数据库不断的维护，是有维护成本的，
比如：表中的数据经常被修改，这样不适合添加索引，数据一旦修改，索引需要重新排序

添加索引是给某一个字段，或者说某些字段加索引

select ename,sal from emp where ename = 'SMITH';
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描
当ename字段添加了索引，以上sql语句根据索引扫描，快速定位

2.2 怎么创建索引对象怎么删除索引对象

创建索引
create index 索引名称 on 表名（字段名）;
删除索引
drop index 索引名称 on 表名;  //注意：这里没有字段名

2.3 什么时候考虑给字段添加索引（满足什么条件）

数据量庞大            （客户需求）
该字段很少的DML操作   （因为字段修改操作，索引也需要维护）
该字段经常出现在where子句中  （经常根据哪个字段查询）

2.4 注意：主键和具有unique约束的字段自动添加索引

根据主键查询

2.5 查看sql语句的执行计划

explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

给薪资sal字段添加索引：
create index emp_sal_index on emp(sal);
explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
drop index emp_sal_index on emp(sal);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

2.6 索引底层采用的数据结构是：B + Tree

2.7 索引实现原理

	同过B Tree缩小扫秒范围，底层索引进行排序，分区。索引会根据表中的物理地址，查询。

EMP表
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+

索引会自动排序：select ename from emp order by ename asc;
还分区：
拆分之后放到B Tree上
生成在内存或者硬盘文件夹中的索引
+--------+
| ename  |
+--------+
| ADAMS  |
| ALLEN  |
| BLAKE  |
| CLARK  |
| FORD   |
| JAMES  |
| JONES  |
| KING   |
| MARTIN |
| MILLER |
| SCOTT  |
| SMITH  |
| TURNER |
| WARD   |
+--------+

在查询语句中，首先会查找是否有索引对象，查到了ename对应的索引：emp_ename_index，之后通过索引检索，ename = 'SMITH'，先定位到S区，继续定位...
很快定位到SMITH了。

2.8 索引的分类

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引

2.9 索引什么时候失效

select ename from emp where ename like '%A%';
模糊查询的时候，第一个通配符使用的是%,这个时候索引失效

3、视图（view）

3.1 什么是视图
站在不同的角度看待数据（同一张表的数据，通过不同的角度看待）

3.2 怎么创建视图删除视图
create view myview as select empno,ename from emp;
drop view myview;

3.3 对视图进行增删改查，会影响原表数据。（通过视图影响表数据的，不是直接操作原表）
可以对视图进行CRUD操作。

3.4 面向视图操作？
mysql> select * from myview;
±------±-------+
| empno | ename |
±------±-------+
| 7369 | SMITH |
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
±------±-------+

create table emp_bak as select * from emp;
create view myview1 as select empno,ename,sal from emp_bak;

update myview1 set ename='hehe',sal=1 where empno = 7369;
以上就是通过视图修改数据

delete from myview1 where empno = 7369;
通过视图删除字段数据

3.5 视图的作用？
视图可以隐藏表的识别细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员
只对试图对象进行CRUD。

4、DBA命令

4.1 将数据库当中的数据导出
在Windows的dos命令窗口执行
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p524736

4.2 导入数据
create database bjpowernode;
use bjpowernode;
source + sql文件路径

5、数据库设计三范式（重点内容，面试经常问）

5.1 什么是设计三范式
设计表的依据：按照三范式设计的表不会出现数据的冗余

5.2 三范式都有哪些
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分
解释原子不可再分：比如电话号码，13232455 这些数据是不能分成两列数据的

第二范式：建立在第一范式基础上，所有非主键字段完全依赖主键，不能产生部分依赖
解释：如果一张表主键设置两个，数据在询问时候就会出现两种选择，部分依赖，数据冗余。解决方法采用多表，外键

第三范式：建立在第二范式基础上，所有非主键字段直接依赖主键，不能产生传递依赖

一对多
班级t_class
学生t_student  放在一张表就产生传递依赖

解决：分两个表


注意：在实际开发中，为满足客户需要，有时候会拿冗余换速度，有时候会拿速度换冗余

5.3 一对一怎么设计
t_user_login
id(pk) username password

t_user_detail
id(pk)       realname     tel   ......

一对一设计两种方案
第一种方案：主键共享
t_user_login  //用户登陆表
id(pk)       username      password

1		zs	    1111
2		ls	    2222

t_user_detail //用户详细信息表
id(pk-fk)       realname           tel   ......

1		张三		1213121
2		李四		1444354

第二种方案：外键为一
t_user_login  //用户登陆表
id(pk)       username      password

1		zs	    1111
2		ls	    2222

t_user_detail //用户详细信息表
id(pk)       realname           tel         userid(fk-unique)    ......

1		张三		1213121		1
2		李四		1444354		2