mysql基础从头到尾快速梳理

本文主要是介绍mysql基础从头到尾快速梳理，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MYSQL数据库学习

mysql的启动

net start mysql
net stop mysql

MYSQL客户端的连接

mysql -h 127.0.0.1 -p 3306 -u root -p

SQL

SQL通用语法

SQL语句可以单行或者多行书写，以分号结尾
SQL语句可以使用空格/缩进来增强语句的可读性
Mysql数据库的SQL语句不区分大小写，关键字建议使用大写
注释：
○ 单行注释：—注释内容或 #注释内容
○ 多行注释：/注释内容/

SQL分类

SQL语句，其功能主要分为四类：DDL，DML，DQL，DCL

DDL ：数据定义语言，用来定义数据对象
DML：数据操作语言，用来对数据库表中的数据进行增删改
DQL：数据查询语言，用来查询数据库中表的记录
DCL：数据控制语句，用来创建数据库用户，控制数据库的访问权限

DDL

数据库操作

查询所有数据库

show database;

查询当前数据库

select database() ;

创建数据库

create database 数据库名;

删除数据库

drop database [if exists] 数据库名;

切换数据库

use 数据库名 ;

表操作

表操作-查询创建

查询当前数据库所有表

show tables;

查看指定表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

创建表结构

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
…
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;

create table tb_user(
id int comment ‘编号’,
name varchar(50) comment ‘姓名’,
age int comment ‘年龄’,
gender varchar(1) comment ‘性别’
) comment ‘用户表’;

表操作-修改

添加字段
[ COMMENT 注释] [约束];ALTER TABLE 表名 ADD 字段名类型（长度） [ COMMENT 注释] [约束];
修改数据类型
ALTER TABLE 表名 MODIFY 字段名新数据类型;
修改字段名和字段类型
alter table 表名 change 旧字段名新字段名类型（长度） [comment 注释] [约束];
删除字段
alter table 表名 drop 字段名
修改表名
alter table 表名 rename to 新表名

表操作-删除

删除表
drop table [if exists ] 表名;
删除指定表，并重新创建表
truncate table 表名;

DML

● 添加数据
● 修改数据
● 删除数据

添加数据
a. 给指定字段添加数据
insert into 表名（字段名1，字段名2，…） values (值1，值2，…);

b. 给全部字段添加数据
insert into 表名 values(值1，值2，…);

c. 批量添加数据
insrt into 表名(字段名1，字段名2，…) values(值1，值2，…)，（值1，值2，…），(值1，值2，…);

insert into 表名 values (值1，值2，…),(值1，值2，…)，(值1，值2，…);

d. 修改数据·
update 表名 set 字段名1 = 值1，字段名2 = 值2 ，…[where 条件];

e. 删除数据
delete from 表名 [where 条件];

注意事项
■ delete 语句的条件可以没有，也可以有，如果没有条件，将删除整张表
■ delete语句不能删除某一个字段的值可以使用update ，将该字段值设置为NULL即可
■ 当进行删除全部数据操作时，datagrip会提示我们，询问是否确认删除

DQL

数据库查询语言

查询关键字 select
在一个正常的业务系统中，查询操作的频次是远高于增删改的，我们去访问企业官网，电商网站，在这些网站中我们所能看到的数据，实际都是需要从数据库中查询并展示的，而且在查询的过程中，可能还会涉及条件，排序，分页等操作
基本语法
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数

● 基本查询（不带条件）
● 条件查询（where）
● 聚合函数(count,max,min,avg,xum)
● 分组查询（group by）
● 排序查询（order by）
● 分页查询(limit)
基础查询

查询多个字段
select 字段1，字段2，字段3… from 表名;
select * from 表名；//查询所有字段，能不适用就少用（不直观，影响效率）
字段设置别名
select 字段1 [AS 别名1],字段2 [AS 别名2] … from 表名；、
select 字段1 [别名1]，字段2 [别名2] …from 表名;
去除重复记录
select distinct 字段列表 from 表名;
条件查询
语法
seelect 字段列表 from 表名 where 条件列表;
条件between and
in()
like 占位符
is null
and &&
or ||
NOt !
聚合函数
将一列数据作为一个整体进行纵向计算
常见的聚合函数：
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
语法：
select 聚合函数（字段列表） from 表名;
null值是不参与所有聚合函数运算的
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
where 与 having的区别：
● 执行时机不同：where是分组之前进行过滤，不满足where 条件，不参与分组，而having是分组之后对结果进行过滤
● 判断条件不同：where 不能对聚合函数进行判断，而having可以
注意事项
● 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
● 执行顺序：where > 聚合函数 > having
● 支持多字段分组，具体语法为：group by columnA,columnB
排序查询（升序降序）
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；
排序方式
● ASC:升序（默认值）
● desc:降序
分页查询
在网站中各种各样的分页条，后台都需要借助数据库的分页操作
语法：
select 字段列表 from 表名 limit 起始索引，查询记录数;
执行顺序
DQL语句在执行时的执行顺序，也就是先执行哪部分后执行哪部分
from 先执行，然后 where 再执行select
from - where - group by – having – select – order by – limit–
DCL
data control language 数据控制语言 ,用来管理数据库用户，控制数据库访问权限
管理用户
查询用户
select * from mysql.user;
创建用户
create user ‘用户名’@‘主机名’ identified by ‘密码’
修改用户密码
alter user ‘用户名’ identified with mysql_native_password by ‘新密码’;
删除用户
drop user ‘用户名’@‘主机名’;
权限控制
all ，allprivileges 所有权限
select 查询数据
insert 插入数据
update 修改数据
delete 删除数据
alter 修改表
drop 删除数据库表视图
create 创建数据库/表
查询权限
show grants for ‘用户名’@‘主机名’;
授予权限
grant 权限列表 on 数据库名，表名 to ‘用户名’@‘主机名’;
撤销权限
revoke 权限列表 on 数据库名，表名 from ‘用户名’@‘主机名’;
注意事项：
● 多个权限之间，使用逗号分隔
● 授权时，数据库名和表名可以使用*进行通配，代表所有
函数
字符串函数，数值函数，日期函数，流程函数
字符串函数
concat() 字符串拼接
lower ()将字段穿str全部转为小写
upper（）将字符串str全部转为大写
lpad(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim() 去掉字符串头部和尾部的空格
substring(str,start,len)返回从字符串str从start位置起的len个长度的字符串
数值函数
ceil(x)向上取整
floor(x)向下取整
mod(x,y)返回x/y的模
rand()返回0-1内的随机数
round(x,y)求参数x的四舍五入的值，保留y位小数
日期函数
curdate()返回当前日期
curtime()返回当前时间
now()返回当前日期和时间
year(date)获取指定date的年份
month(date)获取指定date的月份
day(date)获取指定date的日期
date_add(date,interval,exprtype)返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)返回起始时间date1和结束时间date2之间的天数
select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(),interval 70 year);
select datediff(‘2021-10-01’,‘2021-12-01’);
流程函数
可以在SQL语句中实现条件筛选，从而提高语句的效率。
if（value,t,f）如果value为true,则返回t,否则返回f
ifnull(value1,value2)如果value1不为空，返回value1,否则返回value2
case when [vall] then [res1] … else [default] end 如果vall为true，返回res1,否则返回default默值
case [expr] when [vall] then [res1] … else [default] end 如果expr的值等于vall，返回res1,…否则返回default默认值

约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据
保证数据库中数据的正确，有效性和完整性
分类
● 非空约束限制该字段数据不能为null
● 唯一约束保证该字段的所有数据都是唯一，不重复的
● 主键约束主键是一行数据的唯一标识，要求非空且唯一
● 默认约束保存数据时，如果未指定该字段的值，则采用默认值
● 检查约束保证字段值满足某一个条件
● 外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性
约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
约束演示
需求如下：

外键约束
用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
我们来看一个例子：

左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID，姓名，年龄，职位，薪资，入职日期，上级主管ID，部门ID,在员工的信息存储的是部门ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键，关联的是另一张表的主键
注意：目前上述两张表，只是在逻辑上存在这样一层关系。在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的
语法：

添加外键
create table 表名(
字段名数据类型，
…
[constrantnt] [外键名称] foreing key (外键字段名) references 主表(主表列名)
);
alter table 表名 add constraint 外键名称 foreing key(外键字段名)
references 主表（主表列名）;
删除外键
alter table 表名 drop foreing key 外键名称;
删除更新行为
添加了外键后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为：
● no action
● 当在父表中删除/更新对应记录时，首先检查该记录是否有对应的外键，如果有则不允许删除/更新
● restrict
● 当在父表中删除/更新对应记录时，首先先检查该记录是否有对应外键，如果有则不允许更新./删除与 no action 一致
● cascade
● 当在父表中删除更新对应记录时，首先检查该记录是否有对应的外键，如果有，则也删除/更新外键在子表中的记录
● set null
● 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null
● set default
● 父表有变更时，子表将外键列设置成一个默认的值（innodb不支持）
alter table 表名 add constraint 外键名称 foreing key (外键字段) references 主表名（主表字段名） on update cascade on delete cascade;
多表查询
多表关系
● 一对多（多对一）
● 多对多
● 一对一
一对多
● 案例：部门与员工的关系
● 关系：一个部门对应多个员工，一个员工对应一个部门
● 实现：在多的一方建立外键，指向一的一方的主键
多对多
a. 案例：学生与课程的关系
b. 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
c. 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一
a. 案例：用户与用户详细的关系
b. 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在里一张表中，以提升操作效率
c. 实现:在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的unique

多表查询概述
多表查询就是指从多张表中查询数据
分类
● 连接查询
○ 内连接，相当于查询A，B交集部分数据
○ 外连接
■ 左外连接：查询左表所有数据，以及两张表交集部分数据
■ 右外连接：查询右表所有数据，以及两张表交集部分数据
■ 自动连接：当前表与自身的连接查询，自连接必须使用表别名
● 子查询
● 内连接
○ 内连接查询的是两张表交集部分的数据
○ 内连接的语法分为两种：隐式内连接，显式内连接
○ 隐式内连接：
select 字段列表 from 表1，表2 where 条件…；
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 …；
外连接
● 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件…;
左外连接相当于查询表1（左表）的所有数据，当然也包含表1和表交集部分的数据。

● 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件…;
右外连接相当于查询表2 （右表）的所有数据，当然也包含表1和表2交集部分的数据。
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了，而我们在日常开发使用时，更偏向于左外连接
自连接
一张表连接查询多次
对于自连接可以是内连接也可以是外连接
select 字段列表 from 表A 别名A join 表A 别名B on 条件 …，
在自连接查询中，必须要为表起别名，要不然，我们不清楚所指的条件，返回的字段，到底是哪一张表的字段

联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集
select 字段列表 from 表A…
union all
select 字段列表 from 表B…
● 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
● union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重
如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all 联合查询时，将会报错
子查询
sql语句中嵌套select语句，称为嵌套查询，又称子查询
select * from t1 where volum1 = (select column1 from t2);
子查询外部的语句可以是insert / update /delete /select中的任何一个

分类
根据子查询结果不同，分为：
A.标量子查询(子查询结果为单个值)
B.列子查询(子查询结果为一列)
C.行子查询(子查询结果为一行)
D.表子查询(子查询结结构为多行多列)
根据子查询位置，分为：
where之后
from之后
select之后

标量子查询‘
子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种子查询称为标量子查询
= <> > >= < <=
列查询：
子查询返回的结果是一列（可以是多行）,这种子查询称为列子查询
常用的操作符：IN，NOT IN ， ANY ，SOME，ALL
in 在制定的集合范围之内，多选一
not in 不在指定的集合范围之内
any 子查询返回列表中，有任意一个满足即可
some 与any等同，使用some的地方都可以使用any
all 子查询返回列表的所有值都必须满足
行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
常用的操作符 = <> IN NOT IN
表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询
常用的操作符：IN

事务
事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
业务逻辑之前开启事务，执行完毕后提交事务，如果执行过程报错，则回滚事务，把数据恢复到事务开始之前的状态
默认mysql的事物是自动提交的，也就是说，当执行完一条DML语句时，Mysql会立即稳式的提交事务。
事务操作
控制事物一

查看/设置事物提交方式
select @@autocommit;
set @@autocommit = 0 ;
提交事务
commit;
回滚事物
rollback;
上述的这种方式，我们是修改了事物的自动提交行为，把默认的自动提交改为手动提交，此时我们执行的DML语句不会提交，需要手动的执行commit进行提交
控制事物二
开启事务
start transaction 或 begin;
提交事务
commit;
回滚事务
rollback;
转账案例：
start transaction

select * from account where name=‘张三’;
update account set money = money +1000 where name = ‘李四’;
commit;
rolloback;
事务四大特性
● 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
● 一致性：事务完成时，必须使所有的数据都保持一致状态
● 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行‘
● 持久性：事务一旦提交或者回滚，它对数据库中的数据的改变就是永久的
事务四大特性ACID
并发事务问题
脏读：一个事务读到另一个事务还没有提交的数据
不可重复度：一个事务先后读取同一记录，但两次读取的数据不用，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，出现幻读。
事务的隔离级别：
read uncommitted 脏读不可重复读幻读
read committed 不可重复读幻读
repeatable read 幻读
serializable
查看事务隔离级别
select @@transacttion_isolation;
设置事物的隔离级别
set [session | global] transaction isolation level {
read uncommitted | read committed | repeatable read | serializable}
事务的隔离级别越高，数据越安全，但是性能越低。