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_privInsert_privUpdate_privDelete_priv等等,都是mysql提供的可设置的用户操作权限。具体各权限字段有什么具体权限含义,基础这里不重点讲;

  3. HostUser字段指定允许什么用户名在哪台指定主机上可以访问该数据库;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. wherehaving的区别:

    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  阅读(45)  评论(0编辑  收藏  举报