1.MySql语言分类(重点)
- 数据库查询语言(
DQL-data query language)
用于使用查询语句(select)
- 数据库操纵语言(
DML-data manipulation language)
用于使用增删改语句(insert,delete,update等)
- 数据库定义语言(
DDL-data definition language)
用于使用创建与删除数据库/表语句(create,drop,alter)
- 数据库控制语言(
DCL-data control language)
用于设置用户等操作(grant,revoke等)
- 事务控制语言(
TCL-transactional control language)
用于开启事务后提交与回滚语句(commit,rollback)
2.数据库常用命令
1.库命令
查看数据库版本:mysql -version;
创建数据库: create databases 库名;
删除库:drop database [if exists] 数据库名称;
选择数据库:use 库名;
查看当前选择的数据库:select database();
退出:exit/quit/(\q),一般用在Linux中
查看数据库:show databases;
查看数据库中的表:show tables;
查看表结构:desc 表名;
查看建表语句命令:show create table 表名;
2.表命令
1.建表
注意:这里用的是反引号 键盘esc键下面按键的符号
create table 表名称(
`字段名称` 数据类型(数据长度) 字段约束,
`字段名称` 数据类型(数据长度) 字段约束
)
删表格式:drop table [if exists] 数据表名称1,数据表名称2,数据表名称n...;`
2.添加字段
alter table 表名 add 字段名 数据类型(长度);
3.删除字段
alter table 表名 drop 字段名;
4.修改字段
alter table 表名 change 旧字段名称 新字段名称 数据类型(长度)[字段约束];
3.查询语句
1.简单查询
select 字段名 from 表名 where 条件;
2.条件查询
=:判断是否等于某值
<>或!=:判断是否不等于某值,建议使用前者
or:或者,用于判断两者满足条件,会将所有满足条件的全部查询
and:和/与/并且,用于满足两个条件
between...and...:两者之间,用于判断两者之间内容,
is null:用于判断值是否为`null`空值
is not null
in:在什么什么之中(包含什么什么东西),
not in
like:模糊查询, 如 like '%S%', like 'S%'(表示匹配后面)
not like
order by:排序,用在最后.排序可以使用多个字段,多个字段使用逗号分隔,排序时会以第一个字段排序,如果数据相同则向后推. desc 表示倒序
3.聚合函数
一般联合分组函数一起使用. group by 字段名;
count(字段名):取得数据总条数,统计时不包含null值
sum(字段名):求和
avg(字段名):求平均值
min(字段名):最小值
max(字段名):最大值
ifnull(字段名,默认值):如果字段值为null则替换默认值
having与where相同,他们都是条件查询,但是区别在于where是在group by之前使用,而having是在group by之后使用,以结果当做虚表继续使用条件查询
分组查询后的条件,以结果为虚表再查询
4.查询语句执行顺序
select 字段名... from 表名 where 条件... group by 分组字段... having 条件... order by 排序字段... [asc|desc];
首先执行where语句
执行group
执行having对分组
执行select
执行orde
5.多表查询
三表查询:
#与表的前后没有关系
SELECT
e.ename,
d.dname
FROM
emp_dept ed
JOIN emp e ON e.empno = ed.eid
JOIN dept d ON d.deptno = ed.did
WHERE
e.empno = 1;
SELECT
e.ename,
d.dname
FROM
emp e
JOIN emp_dept ed ON e.empno = ed.eid
JOIN dept d ON d.deptno = ed.did
WHERE
d.deptno = 60
AND e.empno =1
6.子查询(重点)
可以在where中当作条件使用;
可以将其当作临时表t,与其他表进行多表联查
可以当作字段使用
7.limit分页
格式: limit 第几条数据,每页显示数量
第几条数据: (页数-1)*数量;
使用在查询语句之后,仅在order by之前
`注意:分页查询是最终查询,一定是所有结果出来之后进行分页`
8.练习题
https://github.com/zxyJava1119/mysql.git
4.增删改
1.增加
insert into 表名(字段名...) values(数据...);
values后面的数据插入顺序和表字段一致,可以省略前面的字段名
2.修改
update 表名 set 字段名1='修改值',字段名1='修改值'... where 条件;
3.删除
delete from 表名 where 条件;
5.表约束
非空约束:`not null`:代表此字段不允许出现`null`值
唯一约束:`unique`:代表此字段不允许出现相同数据
主键约束:`primary key`:代表表中主要字段,主键约束,并且主键约束附带`唯一约束`与`非空约束`
外键约束:`foreign key`:代表表A与表B的关联项(关联字段),并且表A为主表(需要有主键)表B为辅表(需要有外键)并且表B外键连接表A主键
自定义检查约束:`check`:MySQL目前为止还不支持
主键自增:`AUTO_INCREMENT`:用于设置主键的自增数据只能设置主键并且只能是整数字段
6.事务
1.四大特征
- A(原子性:Atomicity)
整个事务中的所有操作,必须作为一个单元全部完成,要么全部成功,要么全部失败
- C(一致性:Consistency)
在事务的开始之前与结束之后数据中的数据必须保持一致的状态
- I(隔离性:Isolation)
一个事务的操作不会受到其他事务操作的影响
- D(持久性:Durability)
在事务执行完毕后该事务对数据库的所有的操作都必须持久化存储到数据库中(提交事务),并且事务数据不能回滚
Mysql 的事务实现原理
Mysql 的事务实现原理，就是 InnoDB 是如何保证 ACID 特性的。 首先，A 表示 Atomic 原子性，也就是需要保证多个 DML 操作是原子的，要么都成功，要么都失败。 那么，失败就意味着要对原本执行成功的数据进行回滚，所以InnoDB设计了一个 UNDO_LOG 表，在事务执行的过程中，把修改之前的数据快照保存到UNDO_LOG 里面，一旦出现错误，就直接从 UNDO_LOG 里面读取数据执行反向操作就行了。 其次，C 表示一致性，表示数据的完整性约束没有被破坏，这个更多是依赖于业务层面的保证，数据库本身也提供了一些，比如主键的唯一余数，字段长度和类型的保证等等。 接着，I 表示事物的隔离性，也就是多个并行事务对同一个数据进行操作的时候，如何避免多个事务的干扰导致数据混乱的问题。 而 InnoDB 实现了 SQL92 的标准，提供了四种隔离级别的实现。分别是：RU（未提交读） RC（已提交读） RR（可重复读） 跟着Mic学架构学架构 c学架构 跟着Mic学架构 跟着Mic学架构 跟着Mi跟着Mi Serializable（串行化） InnoDB 默认的隔离级别是 RR（可重复读），然后使用了MVCC机制解决了脏读和不可重复读的问题，然后使用了行锁/表锁的方式解决了幻读的问题。最后一个是 D，表示持久性，也就是只要事务提交成功，那对于这个数据的结果的影响一定是永久性的。 不能因为宕机或者其他原因导致数据变更失效。 理论上来说，事务提交之后直接把数据持久化到磁盘就行了，但是因为随机磁盘IO 的效率确实很低，所以 InnoDB 设计了 Buffer Pool 缓冲区来优化，也就是数据发生变更的时候先更新内存缓冲区，然后在合适的时机再持久化到磁盘。那在持久化这个过程中，如果数据库宕机，就会导致数据丢失，也就无法满足持久性了。 所以 InnoDB 引入了 Redo_LOG 文件，这个文件存储了数据被修改之后的值，当我们通过事务对数据进行变更操作的时候，除了修改内存缓冲区里面的数据以外，还会把本次修改的值追加到 REDO_LOG 里面。 当提交事务的时候，直接把 REDO_LOG 日志刷到磁盘上持久化，一旦数据库出现宕机，在 Mysql 重启在以后可以直接用 REDO_LOG 里面保存的重写日志读取出来，再执行一遍从而保证持久性。 因此，在我看来，事务的实现原理的核心本质就是如何满足ACID的，在InnDB里面用到了 MVCC、行锁表锁、UNDO_LOG、REDO_LOG 等机制来保证。
2.事务隔离级别
脏读(Dirty Read)
当一个事务读取了某行数据时,另一个事务已经对此数据做了更新但未提交,这样就产生了脏读
一个事务读取了其他事务未提交的数据
- 不可重复读(Non-Repeatable Read)
在同一个事务,同一个读操作对同一张表的前后两次读取数据产生了不同的结果,这就是不可重复读
- 幻读(phantom Read)
幻读是指在同一个事务中之前没有的记录,由于其他事务的提交而出现新纪录
1.隔离级别
1. 读未提交 read-uncommitted
两个事务,事务A修改了数据,还未提交;事务B可以读取到;
存在脏读,不可重复读,幻读
2. 读已提交 read-committed -->Oracle默认
两个事务,事务A修改了数据,未提交时,事务B读取不到;一旦事务A提交了数据,事务B可以读取到;
存在不可重复读,幻读
3. 可重复读 repeatable-read -->MySQL默认
两个事务,事务A修改了数据并提交,事务B也读取不到;
4. 串行化 Serializable
将一个事务与其他事务完全隔离(最安全的,但是效率也是最低的)
2.相关指令
查看隔离级别
- MySQL8.0
select @@transaction_isolation;select @@global.transaction_isolation;select @@session.transaction_isolation;
- MySQL8.0内
select @@tx_isolation;select @@global.tx_isolation;select @@session.tx_isolation;
# 查看隔离级别
select @@transaction_isolation;
# 设置隔离级别
set transaction isolation level read uncommitted;
set global transaction isolation level read uncommitted;
# 配置后重新查看隔离级别
开启事务:start transaction;
提交事务:COMMIT
7.索引
1.概述
索引在数据库中起到非常重要的点,如果没有索引在数据量非常庞大的情况下搜索(查询)结果会非常的缓慢,索引类似于字典(目录),使用索引后查找则会指定准确的查找不会全表扫描(全表扫描是将数据从1到最终全部查看甚至所有字段挨个查)
索引的优点:查询速度明显提升
索引的缺点:如果添加索引的列需要频繁更新(增删改)则不建议设置索引,每次修改都会重新配置全表索引
常用的就是主键约束(也是一种索引)
2.创建索引
创建索引后需要给表中的字段配置
默认带有索引的格式:primary key主键默认带有索引,unique唯一约束默认带有索引
创建索引格式:create index 索引名称 on 表名(字段名);
修改索引格式:alter table 表名 add index 索引名称(字段名);
查看索引格式:show index from 表名;
创建索引实例:
# 未使用索引查询
explain select * from tb_student
# 使用索引查询
explain select id from tb_student
# 创建索引
create index user_index on tb_user(username);
create index user_index1 on tb_user(password);
# 测试查询
explain select * from tb_user;
explain select username from tb_user;
explain select username,password from tb_user;
# 查看索引
show index from tb_user;
3.删除索引
# 删除索引
drop index user_index1 on tb_user;
8.数据库三范式
1.第一范式
数据库表中不能出现重复的字段,每个字段的原子性不能再分
1. 不符合第一范式的实例
以上表中的id数据重复了,id可以理解为学生编号,编号一旦重复代表学生信息不安全,这种情况就改设置学生id为
唯一(最好是主键)
2.第二范式(多对多)
数据库第二范式是在第一范式的基础上添加约束,要求所有非主键字段应该
完全依赖于主键,不能产生部分依赖
3.第三范式(一对多)
9. 数据库5.7版本如何运行8.0的sql文件
① 将 utf8mb4_0900_ai_ci 替换为 utf8_general_ci
② 将 utf8mb4 替换为 utf8
370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
