现在位置: 首页 / 数据库 / 正文

MySQL数据库-基础篇

  • 小四
  • 数据库
  • 2023-01-21
  • 61热度
  • 0评论

卷心菜接下来将用N篇学习笔记记录MySQL相关知识，由浅入深、成体系；欢迎点赞关注！

本文是第一篇，内容主要来源于拉勾教育自动化测试训练营的2-2学习内容；

1 .数据库相关基础概念

1.1.什么是数据库？

1.数据库(DataBase) 就是存储和管理数据的仓库

2.本质是一个文件系统, 还是以文件的方式,将数据保存在电脑上；

1.2.数据库的类型

关系型数据库（如MySQL、Oracle）非关系型数据库{也称NoSQL}（如Redis、MongoDB、HBase等）

1.3.数据库管理系统

1.3.1.概念：

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理维护数据库的大型软件；

MySql就是一个 数据库管理系统软件, 安装了Mysql的电脑,我们叫它数据库服务器。

1.3.2.作用：

用于建立、使用和维护数据库，对数据库进行统一的管理;

1.3.3.数据库表的概念

数据库中以表为组织单位存储数据，表类似我们Java中的类,每个字段都有对应的数据类型； 使用熟悉的Java程序来与关系型数据对比,发现以下关系:

类 -----> 表 类中属性 ----> 表中字段 对象 ---> 数据记录

1.3.4.数据库管理系统、数据库、表之间的关系

MySQL中管理着很多数据库，在实际开发环境中 一个数据库一般对应一个应用，数据库中保存着多张表，每一张表对应着不同的业务，表中保存着对应业务的数据；

1.4.结构化查询语言的概念

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

1.4.1.SQL的作用：

是所有关系型数据库的统一查询规范，不同的关系型数据库都支持SQL所有的关系型数据库都可以使用SQL不同数据库之间的SQL 有一些区别（类似方言）

1.4.2.SQL的通用语法

1）可以单行 或者 多行书写，以分号 结尾 ; 2）可以使用空格和缩进来增加语句的可读性； 3） MySql中使用SQL不区分大小写，一般关键字大写，数据库名 表名 列名 →小写； 4） 注释方式 ①单行注释：-- 空格（MySQL可以用 #）②多行注释：/**/

1.4.3.SQL的分类

数据定义语言：DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等；

数据控制语言：DCL(Daat Control Language)，用来定义数据库的访问权限、安全级别、创建用户。(了解)

数据操作语言：DML(Data Manipulation Language)，用来对数据库中表的记录进行更新；

数据查询语言：DQL(Data Query Language)，用来查询数据库中表的记录；

需要重点掌握DML、DQL；

1.5.MySQL基础操作指令

1.5. 1.对数据库的操作(DDL)

创建数据库：

create database 数据库名；#创建指定名称的数据库 create database 数据库名 character set 字符集；#创建指定名称的数据库，并且指定字符集;

修改数据库：

alter database 数据库名 character set UTF8;#将数据库的字符集 修改为 utf8

查询数据库：

use 数据库; # 切换数据库 show databases; select database(); #查看当前正在使用的数据库 show create database 数据库名； #查看一个数据库的定义信息

删除数据库：

drop database 数据库名;

1.5.2 对数据库表的操作(DDL)

创建数据库表的语法

use 数据库;#切换到数据库 create table 表名( 字段名1 类型[(宽度)] [约束条件]， # 这里的类型既:限制 字段1 必须以 XX类型 来存储记录 字段名2 类型[(宽度)] [约束条件]， 字段名3 类型[(宽度)] [约束条件] ) 例如： create table file( id int not null primary key autoincrement, sex char(6) not null default 0, age int(3) null, hobby varchar not null # )

查看表

show tables; #查看当前数据库中的所有表名 show craete tables 表名;#查看创建表的SQL语句 desc tables;#查看数据表的结构

修改表

重命名表名： rename table 旧表名 to 新表名 添加列 alter table 表名 add 字段名称 字段类型 修改列名 alter table 表名 change 旧列名 新列名 列类型 修改列类型 ALTER TABLE 表名 MODIFY 列名 列类型; 删除列 alter table 表名 drop 列名;

约束

约束指插入数据时，应该遵循的规则，就叫做约束。

主键约束：不可重复的，唯一的，非空的 非空约束：不能为空的约束 唯一约束：不能重复的约束 外键约束：关联外部表的约束，添加外键约束之后，主表和从表的数据就可以关联起来。 默认值：不设置数据时，默认的数据

1.5.3. 操作数据表的数据(DML)

插入数据

insert into 表名 （字段名1，字段名2...） values(字段值1，字段值2...); # 插入全部字段时，可以不写字段名 #值与字段必须要对应，个数相同&数据类型相同 #值的数据大小，必须在字段指定的长度范围内 #varchar char date类型的值必须使用单引号或双引号 包裹 #如果要插入空值，可以忽略不写，或者插入null

修改数据

update 表名 set 列名 = 值 [where 条件表达式：字段名 = 值 ]

删除数据

#1、删除指定条件数据； delete from 表名 [where 字段名 = 值] ​ #2、删除表中的所有数据； ①delete from 表名； 清空这个表，但是它会保留自增长auto_increment的值，不推荐使用，有多少条记录 就执行多少次删除操作. 效率低； ​ ②truncated table 表名; #把表删掉，包括自增长auto_increment的值；推荐使用. 先删除整张表, 然后再重新创建一张一模一样的表. 效率高；

1.5.4 查询单表中数据(DQL)

简单查询

select * from 表名

去重关键字distinct

select distinct 列名 from 表名；

条件查询

select * from 表名 where 条件

排序

select * from 表名 where 条件 order by 列名 asc

分组

select * from 表名 where 条件 group by 列名

分组过滤

select * from 表名 where 条件 group by 列名 having 条件

limit

select * from 表名 limit 5

子查询

from 中使用子查询

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;

where中使用子查询

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

交叉连接（笛卡儿积）

select * from 表名1,表名2

内连接

select * from 表1 inner join 表2 on 表1.id = 表2.id

左连接

select * from 表1 left join 表2 on 表1.id = 表2.id

右连接

select * from 表1 right join 表2 on 表1.id = 表2.id

聚合函数

AVG() 求平均值 COUNT() 统计数量 MAX() 求最大值 MIN() 求最小值 SUM() 求和 STD() 求标准差

1.5.5. 查询语句运行顺序

select distincat * from A表 inner join B表 on A表.id=B表.id where A表.score>80 group by A表.classid having A表.
from join on where group by聚合函数（avg,sum等）having select distinct order bylimit

1.6.MySQL事务

事务是一组操作序列，要么全部成功，要么全部失败

1.6.1 事务的四个特性（ACID）

原子性（Atomicity）

事务包含的所有操作要么全部成功，要么全部失败回滚；

一致性（Consistency）

一个事务执行之前和执行之后都必须处于一致性状态；

隔离性（Isolation）

多用户并发访问数据库时，比如操作同一个数据表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离；

持久性（Durability）

一个事务一旦提交成功了，那么对数据库中的数据的改变是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作；

1.6.2 分布式操作带来的问题

分布式操作数据库时，会导致事务出现脏读、幻读、不可重复读的问题；

脏读：

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

幻读：

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

不可重复读：

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

三者区别： 脏读：在一个事务中，两次读取同一行记录，得到的数据不一样 幻读：在一个事务中，两次读取同一个表，得到的数据记录数不一样

不可重复读：在一个事务中，两次读同一个数据时，得到的数据不一样

1.6.3 数据库事务隔离级别

数据库事务隔离级别是用来解决上述分布式操作带来的3个问题；隔离级别从低到高有如下4种：

读未提交

查询没加锁，可能会产生“脏读”、“不可重复读”、“幻读”问题，所以基本不使用；

读已提交

常用的一种隔离级别（Oracle/SQL Server）；这种隔离级别可以有效的避免脏读，但查询种没加锁；

除非在查询中显示的加锁，如：

select * from t_user where user_id =8 lock in share mode;

select * from t_user where user_id=8 for update;

否则，普通的查询是不会加锁的。

那为什么“读提交”同“读未提交”一样，都没有查询加锁，但是却能够避免脏读呢？

这是因为另一个机制“快照”，这种既能保证一致性又不加锁的读也被称为“快照读”；

若没有“快照读”，那么当一个待更新的事务没有提交时，另一个对更新数据进行查询的事务会因为无法查询而被阻塞，这种情况下，并发能力就很差。而“快照读”就可以完成高并发的查询，不过，“读提交”只能避免“脏读”，并不能避免“不可重复读”和“幻读”；

可重复读

是MySQL的默认隔离级别；普通查询同样是使用的“快照读”;

与上一个“读已提交”不同的是，当事务启动时，就不允许执行“修改操作（Update）”了，而“不可重复读”恰恰是因为两次读取之间进行了数据的修改，因此，可以有效的避免“不可重复读”，但却避免不了“幻读”，因为幻读是由于“插入或者删除操作（Insert or Delete）”产生的；

串行化

事务串行化挨个顺序执行，“脏读”、“不可重复读”、“幻读”都可以被避免，但是执行效率很低，性能开销也大，所以基本不用；

#设置事务隔离级别指令 set global transaction isolation level 级别名称; read uncommitted 读未提交 read committed 读已提交 repeatable read 可重复读 serializable 串行化 ​ #如: 修改隔离级别为 读已提交 set global transaction isolation level read committed;

1.7数据库设计思想

1.7.1 三范式(最省空间)

1 NF ：设计出来的表中，列是不可以继续拆分的；

数据表中每个字段都是不可再分的最小数据单元，则满足第一范式；

2 NF： 每列都和主键有关系，一张表只描述一件事；

3 NF ：列之间不存在依赖关系；（消除传递依赖 ）

1.7.2 反三范式

冗余设计，提升性能；比如宽表；

当需要查询“订单表”所有数据并且只需要“用户表”的name字段时, 没有冗余字段 就需要去join 连接用户 表,假设表中数据量非常的大, 那么会这次连接查询就会非常大的消耗系统的性能. 这时候冗余的字段就可以派上用场了, 有冗余字段我们查一张表就可以了；

索引、多表查询等其他内容 将在下一篇笔记更新；欢迎点赞关注！