MySQL基础复习
MySQL基础复习
目录
基础概念
数据库:存储数据的仓库;
关系型数据库:存储数据的仓库,数据以关系型表的形式存储;
数据库管理系统:操纵管理数据库的大型软件,如MySQL、Oracle等;
数据库客户端:用于客户输入指令,控制数据库管理系统管理数据的终端;
SQL:操作数据库管理系统管理数据的指令;
概念图
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通用语法
注意:MySQL数据库语法的两个独特
① 语句不区分大小写
② "#"注释
SQL分类
语法学习通用解释
- "[...]"内的属性值代表可选项,不选的话数据库均有默认处理;
- "[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中支持的数据类型有很多,主要分为三类:数值型、字符串型、日期时间型;
学习数据类型通用注意点:
关注类型名-该类型存储的值属于什么大类(数值大类或字符串大类等)-该类型存储的值所占内存大小或值范围
数值型
-
无符号代表不存储数学中的负值,将负值存储空间留给正值使用,当确定某个字段类型不可能存储负值时使用(例如年龄、身高、体重等)。
数值型无符号类型创建表语法举例:
create table [if not exists] 表名(
字段1 数值型类型 [unsigned] [comment 字段1注释],
...
字段n 字段n类型 [comment 字段n注释]
) [comment 表注释];
-
在有符号范围的情况下,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的取值范围,确保数据映射的安全性。
-
精度-标度的理解:
举例:数值
123.45
,精度是5,标度是2。
字符串型
-
char(指定的定长长度)
和varchar(指定存储的最大长度)
的区别:char为定长字符串,指定长度后,不管存入的字符串长度为多少,数据库都会存指定长度字符串,未利用部分也会占用空间;(空间换时间)
varchar为边长字符串,指定可存入最大长度,存入数据时数据库会判断其字符串所占用实际空间,不会存在多余未被利用的存储空间;(时间换空间)
-
包含
blob
的类型,存储的是二进制文本。(例如音频、视频等)但实际开发中我们一般不会将音频、视频等二进制文本存在关系型数据库中,读写效率低。包含
text
的类型,存储的是普通文本(大字符串),使用的时候需要考虑待存入的文本是否会超出设置类型的存储大小。
日期时间型
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,...),...;
② DML修改数据
根据指定过滤条件,修改符合条件的数据
update 表名 set 字段1=值1, 字段2=值2, ... [where 条件];
③ DML删除数据
根据指定过滤条件,删除符合条件的数据
delete from 表名 [where 条件];
DCL-数据控制语言(管理数据库用户、控制数据库的访问权限)
我们知道,一个数据库服务可以创建多个库,多个访问用户。各个数据库能被哪些用户访问,这些用户对这些数据库访问权限的设置都由具有权限的用户(例如:root)操作DCL语句进行设置的。
① DCL用户管理
数据库服务器的所有用户信息都存储在系统默认的mysql
数据库下的user
表当中。因此:
查询所有用户
use mysql;
select * from user;
-
我们可以看到,服务器默认会给我们创建四个用户,其中
root
用户具有所有权限。 -
上图中我们可以看到
Select_priv
、Insert_priv
、Update_priv
、Delete_priv
等等,都是mysql
提供的可设置的用户操作权限。具体各权限字段有什么具体权限含义,基础这里不重点讲; -
Host
和User
字段指定允许什么用户名在哪台指定主机上可以访问该数据库;localhost
表示只能在本机上访问该数据库,%
表示可以在任意主机上访问该数据库。
创建用户
create user '用户名'@'主机名' identified by '密码';
修改用户密码
alter user '用户名'@'主机名' identified with 加密方式 '新密码';
加密方式举例:mysql_native_password
删除用户
drop user '用户名'@'主机名';
② DCL权限控制
MySQL
中定义了很多种权限,但是常用的就是以下几种:
显示指定用户权限
show grants for '用户名'@'主机名';
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
例:授予用户所有权限 grant all on test.* to 'username'@'localhost';
、仅授予用户部分权限grant select,insert on test.* to 'username'@'localhost';
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
注意:如果要授予某个用户一整个数据库所有表的权限,使用数据库名.*
。不指定数据库名表示给当前数据库的表设置权限。使用*.*
代表给所有数据库,所有表授予权限。
DQL-数据查询语言(数据查询操作)
① DQL语法结构
② DQL基本查询
查询指定字段数据
select [distinct] 字段1, 字段2, ... from 表名;
distinct
为可选关键字,用于去除查询的重复数据。
查询全表数据
select * from 表名;
给指定字段设置别名查询(as关键字可以省略)
select [distinct] 字段1 [as 别名1], 字段2 [as 别名2], ... from 表名;
③ DQL条件查询
按条件查询
select 字段列表 from 表名 where 条件列表;
④ DQL聚合函数
聚合函数的作用:将一列数据作为一个整体,进行纵向计算。
常见聚合函数
语法
select 聚合函数(待聚合字段) from 表名;
注意:所有的null
值不参与聚合函数的运算。
⑤ DQL分组查询
语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
-
where
和having
的区别:
⑥ DQL排序查询
语法
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
-
排序方式
asc
:升序(默认值)desc
:降序 -
如果是多字段排序,当第一个字段相同时才会根据第二个字段进行排序。
⑦ DQL分页查询
语法
select 字段列表 from 表名 limit 起始索引, 查询条数;
⑧ DQL执行顺序
函数
函数:是指一段可以直接被另一段程序调用的程序或代码
字符串函数
数值函数
日期函数
流程函数
函数一般作用在一列上的业务理解:
我们学习其他语言,定义函数时,一般会选择函数有哪些入参,但是不管怎样,函数要处理的目标数据,数据类型或者其具有相同的父类至少,这样该函数才有通用性。在关系型数据库中,一列数据的数据类型是一样的,因此函数一般作用于一列数据。
约束
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
作用:用于约束表结构中所存储的数据。
目的:保证数据库中数据的正确、有效性和完整性。
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
有了约束之后创建表的语法结构如下:
create table [if not exists] 表名(
字段1 字段1类型 [约束] [约束] ... [comment 字段1注释],
字段2 字段2类型 [约束] [约束] ... [comment 字段2注释],
字段3 字段3类型 [约束] [约束] ... [comment 字段3注释],
...
字段n 字段n类型 [约束] [约束] ... [comment 字段n注释]
) [comment 表注释];
示例:
补充:auto_increment
自增约束;
语法:
外键约束专题
添加外键语法
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 主表名 (主表字段名);
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update 行为 on delete 行为;
多表查询
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为如下三种:
① 一对多(多对一)
② 多对多
③ 一对一
多表间关系
① 一对多(多对一)
场景:部门与员工的关系(一个部门可以有多个员工,一个员工只能隶属一个部门)
表设计实现方案:在多的一方建立外键,指向一的一方(主表)的主键。
② 多对多
场景:学生与课程的关系(一个学生可以选多个课程,一个课程可以被多个学生选择)
表设计实现方案:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。
③ 一对一
场景:用户与用户详情的关系(多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率)
表设计实现方案:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(unique)
连接查询
① 内连接
隐式内连接
select 字段列表 from 表1, 表2 where 连接条件...;
显示内连接(inner关键字可以省略)
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
- 内连接查询的是两张表交集的部分,及拿着a表中的每一条数据去b表中找符合连接条件的数据,找到进行连接返回,找不到a表这条数据也丢弃。
- 隐式内连接和显示内连接的区别只是写法上的不同;
② 外连接
左外连接(outer关键字可以省略)
select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
- 左外连接是拿表1的所有数据根据连接条件去表2找,找到则连接返回,找不到表1的该条数据会连接一条空的表2数据返回。
右外连接(outer关键字可以省略)
select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;
- 右外连接是拿表2的所有数据根据连接条件去表1找,找到则连接返回,找不到表2的该条数据会连接一条空的表1数据返回。
③ 自连接
自连接顾名思义就是自己连接自己。
自连接
select 字段列表 from 表a 别名a join 表A 别名b on 连接条件...;
- 自连接是一种自己连接自己的思想,所以不管是内连接还是外连接,语法都可以实现这种自连接的思想。
- 自连接的两张表都要取别名,因为不取别名无法区分自连接的两张表。
联合查询
联合查询:就是把多次查询的结果合并起来,组成一个新的查询结果集。
语法
select 字段列表 from 表a ...
union [all]
select 字段列表 from 表b ...;
- 对于联合查询的多张表的列数、字段类型必须保持一致。否则合并会错误;(但是这一点我在
mysql8.0
测试的时候发现,只要字段数量一样就可以合并成功。。。不知道大家的是不是如此) union all
会将两次查询的数据直接合并,union
会对合并后的数据去重;
子查询
子查询:SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
语法示例
select * from t1 where column1 = (select column1 from t2);
子查询外部的语句可以是insert/update/delete/select
的任何一个。
根据子查询结果分类:
-
标量子查询(子查询结果为单个值)- 常用操作符(=、<>、>、>=、<、<=)
-
列子查询(子查询结果是一列)- 常用操作符(in、not in、any、some、all)
-
行子查询(子查询结果是一行)- 常用操作符(=、<>、in、not in)
-
表子查询(子查询结果是多行多列)- 常用操作符(in)
根据子查询位置分类:
- where之后
- from之后
- select之后
事务
事务:是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么一起成功,要么一起失败。
默认MySQL
的事务是自动提交的,也就是说,当执行一条DML
语句时,MySQL
会立即隐式的提交事务。
事务操作
查看事务提交方式
select @@autocommit;
默认为自动提交@@autocommit
值为1
设置事务的提交方式
-- 设置为自动提交(不设置默认也是自动提交)
set @@autocommit = 1;-- 设置手动提交
set @@autocommit = 0;
开启事务(相当于临时的开启手动提交事务,而不是将数据库@@autocommit
的值改为0)
start transaction 或 begin;
提交事务
commit;
回滚事务
rollback;
事务的四大特性
俗话
原子性:一组操作,作为一个事务,要么同时成功,要么同时失败,不允许事务中的操作部分成功,部分失败。遇到操作异常的情况,成功的操作也要回滚到原先的状态。
一致性:一组操作,作为一个事务,不管是成功还是失败,所操作的数据必须在你设定的操作逻辑下具有一致性。成功了,数据展现要符合你设定的操作逻辑,失败了,数据展现也要符合你对失败操作后的数据预期。
隔离性:多个事务在操作数据的时候彼此间不会互相影响。
持久性:事务一旦提交或者回滚后,它对数据库中的数据改变是永久的,因为事务提交或回滚后,数据都会跟电脑磁盘进行交互,将增删改的数据进行持久化。
并发事务问题
① 脏读
事务a进行数据更新操作,但是此时事务a还有后续操作待执行,事务a未提交,此时事务b来查事务a刚刚修改但是未提交的数据。这种现象就是并发事务下的脏读问题。
脏读是查询到另一个事务未提交的数据;
② 不可重复读
当解决了脏读还会发生不可重复读问题;
事务a先去查询id=1
的数据,然后再进行事务中其他操作的过程中,事务b对id=1
的数据进行了修改并提交到了数据库,然后事务a再次查询id=1
的数据的时候,导致了事务a对同一条数据的两次查询之间,数据不一致。这种现象就是并发事务下的不可重复读问题。
查询到别人已经提交的数据,这很正常,但是如果在别人未提交之前,你已经查询过一个该数据,两次相同的查询,但是数据不一致,这对该事务来说不正常;
③ 幻读
当解决了不可重复读还会发生幻读的问题;
事务a先去查询id=1
的数据,发现没有想要的数据,然后决定去插入,但是在事务a插入数据之前,事务b插入了该条数据,导致了事务a插入失败,然后你想查找什么原因,难道是自己看错了?于是你有查询了一遍。因为你解决了不可重复读的问题,导致这次查询还是查不到该条数据,这种现象就是幻读问题;
事务隔离级别
-
MySQL
数据库的默认隔离级别是Repeatable Read
,如果是Oracle
数据库的默认级别就是Read committed
所以不同是数据库默认的事务隔离级别可能不一样。
查看事务的隔离级别
select @@transaction_isolation;
设置事务的隔离级别
set [session | global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable];
session
表示该设置针对当前会话窗口有效;global
表示该设置针对当前会话窗口有效;- 事务的隔离级别越高,数据越安全,但是性能越低。