MySQL基础复习

MySQL基础复习

目录

目录

基础概念

数据库：存储数据的仓库；

关系型数据库：存储数据的仓库，数据以关系型表的形式存储；

数据库管理系统：操纵管理数据库的大型软件，如MySQL、Oracle等；

数据库客户端：用于客户输入指令，控制数据库管理系统管理数据的终端；

SQL：操作数据库管理系统管理数据的指令；

概念图

image-20240323162121446

MySQL数据模型

一个MySQL服务可以创建多个数据库，一个数据库可以创建多张关系型表；

（MySQL是关系型数据库）

基础命令

启动-停止MySQL服务

启动：net start mysql80
停止：net stop mysql80

注：mysql80指的是安装mysql时注册到win系统上的服务名称；

MySQL自带的客户端连接

mysql [-h 127.0.0.1] [-P 3306] -u root -p

SQL

SQL通用语法

image-20240323163156785

注意：MySQL数据库语法的两个独特

① 语句不区分大小写

② "#"注释

SQL分类

image-20240323170931687

语法学习通用解释

  1. "[...]"内的属性值代表可选项，不选的话数据库均有默认处理；
  2. "[if not exists]"如果不存在某某执行该条语句，"[if exists]"如果存在某某执行该条语句；

DDL-数据定义语言（数据库-表的定义操作）

① DDL-数据库操作

显示所有数据库

show databases;

查询当前所属数据库

select database();

创建数据库

create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

注意：数据库字符集不建议设置为utf8，原因是utf8字符集只能存储占三个字节的字符，当有些字符占四个字节时，存储该字符会出现问题；

todo 字符集和排序规则后续有详细说明；

删除数据库

drop database [if exists] 数据库名;

使用数据库，或者说切换数据库

use 数据库名;

② DDL-表操作

显示当前数据库所有表

show tables;

查看指定表的表结构

desc 表名;

显示指定表的建表语句

show create table 表名;

创建表

create table [if not exists] 表名(

​ 字段1 字段1类型 [comment 字段1注释],

​ 字段2 字段2类型 [comment 字段2注释],

​ 字段3 字段3类型 [comment 字段3注释],

​ ...

​ 字段n 字段n类型 [comment 字段n注释]

) [comment 表注释];

todo MySQL支持的数据类型后续有详细说明；

新增表字段

alter table 表名 add 字段名 字段类型 [comment 注释] [约束];

todo 什么是约束，后面也会详细说明；

修改表字段的字段类型-注释-约束等

alter table 表名 modify 字段名 修改后的字段类型 [comment 注释] [约束];

注意：这里其实也可以修改后面的注释和约束，如果之前有注释，之后修改的时候不想修改注释，也要写一下原来的注释内容，否则先前的注释会被清空；

支持修改表字段原字段名的写法

alter table 表名 change 原字段名 新字段名 字段类型 [comment 注释] [约束];

删除指定字段

alter table 表名 drop 字段名;

修改表名

alter table 原表名 rename to 新表名;

删除指定表

drop table [if exists] 表名;

删除指定表，并重新创建该表（清空表数据）

truncate table 表名;

番外：MySQL支持的数据类型

MySQL中支持的数据类型有很多，主要分为三类：数值型、字符串型、日期时间型；

学习数据类型通用注意点：

关注类型名-该类型存储的值属于什么大类（数值大类或字符串大类等）-该类型存储的值所占内存大小或值范围

数值型

image-20240323175443072

  1. 无符号代表不存储数学中的负值，将负值存储空间留给正值使用，当确定某个字段类型不可能存储负值时使用（例如年龄、身高、体重等）。

    数值型无符号类型创建表语法举例：

    create table [if not exists] 表名(

    ​ 字段1 数值型类型 [unsigned] [comment 字段1注释],

    ​ ...

    ​ 字段n 字段n类型 [comment 字段n注释]

    ) [comment 表注释];

  2. 在有符号范围的情况下，Java中的数据类型和MySQL中的数据类型安全映射的关系如下：

    [byte:tinyint]、[short:smallint]、[int:int或integer]、[long:bigint]、[float:float]、[double:double]

    但是如果当数据库的某个数值型设置了无符号属性，那么和Java中的数据类型映射关系就要重新考虑了。否则可能会导致数值溢出或不匹配的情况。

    举例：当MySQL的tinyint类型设置了无符号属性时，它的取值范围是0到255。而Java中的byte数据类型是有符号的，取值范围是-128到127。因此，如果将MySQL中的无符号tinyint和Java中的byte进行映射，可能会导致数值溢出或不匹配的情况。

    因此为了安全地映射这两种数据类型，你可以考虑使用Java中的short数据类型来代替byte。short数据类型的取值范围是-32768到32767，可以很好地容纳MySQL中无符号tinyint的取值范围，确保数据映射的安全性。

  3. 精度-标度的理解：

    举例：数值123.45，精度是5，标度是2。

字符串型

image-20240323175654617

  1. char(指定的定长长度)和varchar(指定存储的最大长度)的区别：

    char为定长字符串，指定长度后，不管存入的字符串长度为多少，数据库都会存指定长度字符串，未利用部分也会占用空间；（空间换时间）

    varchar为边长字符串，指定可存入最大长度，存入数据时数据库会判断其字符串所占用实际空间，不会存在多余未被利用的存储空间；（时间换空间）

  2. 包含blob的类型，存储的是二进制文本。（例如音频、视频等）但实际开发中我们一般不会将音频、视频等二进制文本存在关系型数据库中，读写效率低。

    包含text的类型，存储的是普通文本（大字符串），使用的时候需要考虑待存入的文本是否会超出设置类型的存储大小。

日期时间型

image-20240323175946496

DML-数据操作语言（表中数据增删改操作）

① DML添加数据

给指定字段添加数据

insert into 表名 (字段1,字段2,字段3,...) values (值1,值2,值3,...);

给全部字段添加数据

insert into 表名 (值1,值2,值3,...);

批量添加数据

insert into 表名 (字段1,字段2,字段3,...) values (值1,值2,值3,...),(值1,值2,值3,...),...;

insert into 表名 (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...),...;

image-20240324131756686

② DML修改数据

根据指定过滤条件，修改符合条件的数据

update 表名 set 字段1=值1, 字段2=值2, ... [where 条件];

image-20240324134926270

③ DML删除数据

根据指定过滤条件，删除符合条件的数据

delete from 表名 [where 条件];

image-20240324135343190

DCL-数据控制语言（管理数据库用户、控制数据库的访问权限）

我们知道，一个数据库服务可以创建多个库，多个访问用户。各个数据库能被哪些用户访问，这些用户对这些数据库访问权限的设置都由具有权限的用户（例如：root）操作DCL语句进行设置的。

image-20240324140049697

① DCL用户管理

数据库服务器的所有用户信息都存储在系统默认的mysql数据库下的user表当中。因此：

查询所有用户

use mysql;

select * from user;

  1. 我们可以看到，服务器默认会给我们创建四个用户，其中root用户具有所有权限。image-20240324142102811

  2. 上图中我们可以看到Select_priv、Insert_priv、Update_priv、Delete_priv等等，都是mysql提供的可设置的用户操作权限。具体各权限字段有什么具体权限含义，基础这里不重点讲；

  3. Host和User字段指定允许什么用户名在哪台指定主机上可以访问该数据库；localhost表示只能在本机上访问该数据库，%表示可以在任意主机上访问该数据库。

创建用户

create user '用户名'@'主机名' identified by '密码';

修改用户密码

alter user '用户名'@'主机名' identified with 加密方式 '新密码';

加密方式举例：mysql_native_password

删除用户

drop user '用户名'@'主机名';

image-20240324145250817

② DCL权限控制

MySQL中定义了很多种权限，但是常用的就是以下几种：

image-20240324145701704

显示指定用户权限

show grants for '用户名'@'主机名';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

例：授予用户所有权限 grant all on test.* to 'username'@'localhost';、仅授予用户部分权限grant select,insert on test.* to 'username'@'localhost';

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意：如果要授予某个用户一整个数据库所有表的权限，使用数据库名.*。不指定数据库名表示给当前数据库的表设置权限。使用*.*代表给所有数据库，所有表授予权限。

image-20240324153536490

DQL-数据查询语言（数据查询操作）

① DQL语法结构

image-20240325105701342

② DQL基本查询

查询指定字段数据

select [distinct] 字段1, 字段2, ... from 表名;

distinct为可选关键字，用于去除查询的重复数据。

查询全表数据

select * from 表名;

给指定字段设置别名查询(as关键字可以省略)

select [distinct] 字段1 [as 别名1], 字段2 [as 别名2], ... from 表名;

③ DQL条件查询

按条件查询

select 字段列表 from 表名 where 条件列表;

image-20240324155506707

④ DQL聚合函数

聚合函数的作用：将一列数据作为一个整体，进行纵向计算。

常见聚合函数

image-20240325102826838

语法

select 聚合函数(待聚合字段) from 表名;

注意：所有的null值不参与聚合函数的运算。

⑤ DQL分组查询

语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

  1. where和having的区别：

    image-20240325124038202

  2. image-20240325124104664

⑥ DQL排序查询

语法

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

  1. 排序方式

    asc：升序（默认值）

    desc：降序

  2. 如果是多字段排序，当第一个字段相同时才会根据第二个字段进行排序。

⑦ DQL分页查询

语法

select 字段列表 from 表名 limit 起始索引, 查询条数;

image-20240325124938878

⑧ DQL执行顺序

image-20240325125351405

函数

函数：是指一段可以直接被另一段程序调用的程序或代码

字符串函数

image-20240325130614249

数值函数

image-20240326092157766

日期函数

image-20240326093206313

流程函数

image-20240326095737961

函数一般作用在一列上的业务理解：

我们学习其他语言，定义函数时，一般会选择函数有哪些入参，但是不管怎样，函数要处理的目标数据，数据类型或者其具有相同的父类至少，这样该函数才有通用性。在关系型数据库中，一列数据的数据类型是一样的，因此函数一般作用于一列数据。

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

作用：用于约束表结构中所存储的数据。

目的：保证数据库中数据的正确、有效性和完整性。

image-20240326100806473

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

有了约束之后创建表的语法结构如下：

create table [if not exists] 表名(

​ 字段1 字段1类型 [约束] [约束] ... [comment 字段1注释],

​ 字段2 字段2类型 [约束] [约束] ... [comment 字段2注释],

​ 字段3 字段3类型 [约束] [约束] ... [comment 字段3注释],

​ ...

​ 字段n 字段n类型 [约束] [约束] ... [comment 字段n注释]

) [comment 表注释];

示例：

image-20240326101756210

补充：auto_increment自增约束；

语法：

image-20240326101835063

外键约束专题

image-20240326104148299

添加外键语法

create table [if not exists] 表名(

​ 字段1 字段1类型 [约束] [约束] ... [comment 字段1注释],

​ 字段2 字段2类型 [约束] [约束] ... [comment 字段2注释],

​ 字段3 字段3类型 [约束] [约束] ... [comment 字段3注释],

​ ...

​ 字段n 字段n类型 [约束] [约束] ... [comment 字段n注释],

​ [constraint] [外键名称] foreign key (外键字段) references 主表名 (主表字段名)

) [comment 表注释];

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名);

image-20240326102919803

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update 行为 on delete 行为;

多表查询

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为如下三种：

① 一对多（多对一）

② 多对多

③ 一对一

多表间关系

① 一对多（多对一）

场景：部门与员工的关系（一个部门可以有多个员工，一个员工只能隶属一个部门）

表设计实现方案：在多的一方建立外键，指向一的一方（主表）的主键。

image-20240326105906517

② 多对多

场景：学生与课程的关系（一个学生可以选多个课程，一个课程可以被多个学生选择）

表设计实现方案：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

image-20240326110334064

③ 一对一

场景：用户与用户详情的关系（多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率）

表设计实现方案：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

image-20240326111710068

连接查询

image-20240326112303450

image-20240326112325151

① 内连接

隐式内连接

select 字段列表 from 表1, 表2 where 连接条件...;

显示内连接（inner关键字可以省略）

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

  1. 内连接查询的是两张表交集的部分，及拿着a表中的每一条数据去b表中找符合连接条件的数据，找到进行连接返回，找不到a表这条数据也丢弃。
  2. 隐式内连接和显示内连接的区别只是写法上的不同；

② 外连接

左外连接（outer关键字可以省略）

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;

  1. 左外连接是拿表1的所有数据根据连接条件去表2找，找到则连接返回，找不到表1的该条数据会连接一条空的表2数据返回。

右外连接（outer关键字可以省略）

select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

  1. 右外连接是拿表2的所有数据根据连接条件去表1找，找到则连接返回，找不到表2的该条数据会连接一条空的表1数据返回。

③ 自连接

自连接顾名思义就是自己连接自己。

自连接

select 字段列表 from 表a 别名a join 表A 别名b on 连接条件...;

  1. 自连接是一种自己连接自己的思想，所以不管是内连接还是外连接，语法都可以实现这种自连接的思想。
  2. 自连接的两张表都要取别名，因为不取别名无法区分自连接的两张表。

联合查询

联合查询：就是把多次查询的结果合并起来，组成一个新的查询结果集。

语法

select 字段列表 from 表a ...

union [all]

select 字段列表 from 表b ...;

  1. 对于联合查询的多张表的列数、字段类型必须保持一致。否则合并会错误；（但是这一点我在mysql8.0测试的时候发现，只要字段数量一样就可以合并成功。。。不知道大家的是不是如此）
  2. union all会将两次查询的数据直接合并，union会对合并后的数据去重；

子查询

子查询：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

语法示例

select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是insert/update/delete/select的任何一个。

根据子查询结果分类：

  1. 标量子查询（子查询结果为单个值）- 常用操作符（=、<>、>、>=、<、<=）

  2. 列子查询（子查询结果是一列）- 常用操作符（in、not in、any、some、all）

    image-20240326142838174

  3. 行子查询（子查询结果是一行）- 常用操作符（=、<>、in、not in）

  4. 表子查询（子查询结果是多行多列）- 常用操作符（in）

根据子查询位置分类：

  1. where之后
  2. from之后
  3. select之后

事务

事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么一起成功，要么一起失败。

image-20240326145137692

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句时，MySQL会立即隐式的提交事务。

事务操作

查看事务提交方式

select @@autocommit;

默认为自动提交@@autocommit值为1

设置事务的提交方式

-- 设置为自动提交（不设置默认也是自动提交）
set @@autocommit = 1;

-- 设置手动提交

set @@autocommit = 0;

开启事务（相当于临时的开启手动提交事务，而不是将数据库@@autocommit的值改为0）

start transaction 或 begin;

提交事务

commit;

回滚事务

rollback;

事务的四大特性

image-20240326151040951

俗话

原子性：一组操作，作为一个事务，要么同时成功，要么同时失败，不允许事务中的操作部分成功，部分失败。遇到操作异常的情况，成功的操作也要回滚到原先的状态。

一致性：一组操作，作为一个事务，不管是成功还是失败，所操作的数据必须在你设定的操作逻辑下具有一致性。成功了，数据展现要符合你设定的操作逻辑，失败了，数据展现也要符合你对失败操作后的数据预期。

隔离性：多个事务在操作数据的时候彼此间不会互相影响。

持久性：事务一旦提交或者回滚后，它对数据库中的数据改变是永久的，因为事务提交或回滚后，数据都会跟电脑磁盘进行交互，将增删改的数据进行持久化。

并发事务问题

image-20240326152308515

① 脏读

事务a进行数据更新操作，但是此时事务a还有后续操作待执行，事务a未提交，此时事务b来查事务a刚刚修改但是未提交的数据。这种现象就是并发事务下的脏读问题。

脏读是查询到另一个事务未提交的数据；

image-20240326152439260

② 不可重复读

当解决了脏读还会发生不可重复读问题；

事务a先去查询id=1的数据，然后再进行事务中其他操作的过程中，事务b对id=1的数据进行了修改并提交到了数据库，然后事务a再次查询id=1的数据的时候，导致了事务a对同一条数据的两次查询之间，数据不一致。这种现象就是并发事务下的不可重复读问题。

查询到别人已经提交的数据，这很正常，但是如果在别人未提交之前，你已经查询过一个该数据，两次相同的查询，但是数据不一致，这对该事务来说不正常；

image-20240326152552229

③ 幻读

当解决了不可重复读还会发生幻读的问题；

事务a先去查询id=1的数据，发现没有想要的数据，然后决定去插入，但是在事务a插入数据之前，事务b插入了该条数据，导致了事务a插入失败，然后你想查找什么原因，难道是自己看错了？于是你有查询了一遍。因为你解决了不可重复读的问题，导致这次查询还是查不到该条数据，这种现象就是幻读问题；

image-20240326152754755

事务隔离级别

image-20240326163805655

  1. MySQL数据库的默认隔离级别是Repeatable Read，如果是Oracle数据库的默认级别就是Read committed

    所以不同是数据库默认的事务隔离级别可能不一样。

查看事务的隔离级别

select @@transaction_isolation;

设置事务的隔离级别

set [session | global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable];

  1. session表示该设置针对当前会话窗口有效；global表示该设置针对当前会话窗口有效；
  2. 事务的隔离级别越高，数据越安全，但是性能越低。
posted @ 2024-03-26 17:04  zhao-XH  阅读(22)  评论(0)  编辑  收藏  举报