mysql速查速记

一、mysql概述

1.mysql简述

mysql是常用的关系型数据库，是一种数据库管理系统（DBMS），当然还有noSQL类型的非关系型数据库，具体区别自行百度。在同类型的数据库当中mysql是开源且免费的，所以用途较广。

那我们如何理解数据库呢？
其实就是存放数据的仓库，涉及到了对用户的操作（DCL）库的操作（DDL）库中数据的操作（DML）数据查询和使用（DQL）特殊数据的处理（TPL）这一完整的逻辑，和相应的增删改查（CRUD）操作。
mysql是基于客户机-服务器的数据库，可以理解为，需要使用数据的一方软件称为客户机，就像软件的使用者，使用mysql语句对数据进行操作的一方被称为服务器，类似开发者。因此，安装了mysql的计算机被称为数据库服务器。

数据库是一个系统软件，它提供的是一种操作数据的服务，有DOS命令行操作方式和图形化软件（navicat、SQLyog）操作两种方式，使用起来很方便。

2.mysql的启动和关闭

上面提到了mysql提供的是一种服务，因此下载完成后是什么都看不见的，只有开启之后才能进行操作。

1. 手动开启
   1、打开cmd输入services.msc打开服务管理器，找到MySQL2、此时会发现启动类型处是自动类型，表示打开电脑的时候就会自动开启mysql服务，我们当然是不需要的，右击–>点击属性–>设置为手动，我们也可以看到，右击后显示启动和关闭这就是第一种开启方式。

2. 程序员开启
   1、打开cmd右击点击以管理员身份打开，输入net start mysql开启，输入net stop mysql关闭。
   

3.mysql使用

1. DOS命令行
   1、一定要在mysql打开的状态下使用，密码就是安装完mysql之后设置的密码，之后就可以快乐的敲代码了。
   2、输入exit退出
   
2. navicat

1、将连接名和密码填写完成就好（mysql一定要是开着的）

3. SQLyog
   1、mysql连接状态下进入就好
   

4.mysql的安装

mysql8版本的安装教程

二、mysql语言的组成

1.DCL（数据控制语言，操作人的）

管理用户：
1.添加 
	create user ‘用户名’@‘主机名’ identitied by ‘密码’;
2.删除 
	drop user ‘用户名’;
3.查询 
切换到 mysql数据库 
	use mysql 
查询user表 
	select * from user
4.修改密码 
update user set password =password（‘新密码’） where user=“用户名”；
set password for ‘用户名’@‘主机名’=password（‘新密码’）；

2.DDL（数据定义语言，操作库的）

库表管理

1. 操作数据库（CRUD）
   1、创建create：

Create database + 库名;
Create database if not exits + 库名;
Create database + 库名 character set gbk;//设置编码规则
Create database if not exits + 库名 character set gbk;

2. 查询resrieve:

Show databases;
Show create database + 库名;--显示数据库详细内容

3. 修改update：

Alter database + 库名 character set utf8;//修改格式

4. 删除delete:

Drop darabase + 库名

5. 使用数据库：

Select database()// 查询当前数据库
Use 库名

1. 操作表
   1、创建create：

语法：create table 表名（列名 数据类型1，列名 数据类型2）;
数据类型：
Int、double、date、timestamp、varchar
复制操作：create table 表2 like 表1

2. 查询resrieve:

Show tables
Desc 表名//表的结构

3. 修改update：

1.添加一列
	Alter table 表名 add column 列名 类型 【first|after 字段名】;
2.删除列
	Alter table 表名 drop column 列名;
3.修改表的字符集
	Show create table 表名
	Alter table 表名 character set utf8;
4.修改表名
	Alter table 表名 rename 【to】 新表名;
5.修改列名称:
	Alter table 表名 change column 旧列名 新列名 类型;
6.修改列类型：
	Alter table 表名 modify column 列名 改的数据类型
7.修改列的约束:
	Alter table 表名 modify column 列名 新约束

4. 删除delete：

	drop table if exits book;
	show tables;

5. 复制表

1.复制表的结构
	create table 表2 like 表2 
2.复制表中的数据
	create table 表2 select * from 表1;//全部数据
	create table 表1 select id from 表2 where 条件;//部分数据 

约束语言

1. 常见约束

1.not null;
非空，该字段不能为空值。
2.unique
该字段的值不能重复。
3.primary key
主键，该字段不可重复且非空==unique+not null
4.foreign key
外键，1、用于限制两个表的关系，从表的字段值引用了主表的某些字段值。
	 2、外键的列和主表的列引用类型要求一致，意义相同，名称无要求。
	 3、主表中被引用的得是一个主键（要不然为什么叫外键）。
	 4、插入数据先插主表，删除数据先删从表。
5.unique和primary的辨析：
	区别：主键不能为空，但unique可以。
	相同：都具有唯一性，支持组合键，但是不支持。

创建约束：
create table 表名(
	列名 列类型 not null,#非空
	列名 列类型 primary key,#主键
	列名 列类型 unique,#唯一
	【constraint 约束名】foreign key(从表名) references 主表（被引用列）
)

删除约束：
1、非空
删除非空
alter table 表名 modify column 列名 列类型 ;
2、主键
删除主键
alter table 表名 drop primary key;
3、唯一
删除唯一
alter table 表名 drop index 索引名;
4、外键
删除外键
alter table 表名 drop foreign key 外键名;

修改约束：
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
2、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(列名);
3、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(列名);
4、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(列名) references 主表（被引用列）;

2. 自增长列

解释：
1.自增长列不用手动插入值，自动提供序列，默认从1开始。
2.一个表至多有一个自增序列。
3.自增长列只能是数值型。
4.自增长列必须为一个主键。
会出现的问题：
如果删除某些数据之后，序列值仍然下延不会自动补齐，会出现1、3、8、9的序列情况。

创建自增：
create table 表名（列名 列类型 约束语句 auto_increment）;
删除自增：
alter table 表名 modify column 列名 列类型 约束语句 auto_increment;
修改自增：
alter table 表名 modify column 列名 列类型 约束语句

3.DML（数据操作语言，操作表的）

增删改

1.添加数据
	方法一：Insert into 表名（列名1，列名2）values(值1，值2)
		1、列名和值一一对应
		2、表名后不列表示对所有赋值
		3、values中除了数字，其他需要用引号
		4、支持子查询
	方法二：Insert into 表名 set 列名=值,列名=值
2.删除数据
	Delete from 表名 [where 条件]
		如果不加条件，会删除所有数据,可以回滚（rollback）
	Truncate table 表名 
		截断表，只留着表头，不能回滚，删除大量数据的时候效率很高。
3.修改数据
	修改单表：
		Update 表名 set 列名1=值1,列名2=列2 [where 条件] 【limit 开始条目 条目数】
		注意上面写的逗号不能省略，limit 开始条目 条目数 表示选择多少条数据进行修改
	修改多表：
		update 表1 别名 
		left|right|inner join 表2 别名 
		on 连接条件  
		set 列名=值,列名=值 
		【where 筛选条件】;
	提前介绍一下表连接：
		left,right表示左侧写的表，右侧写的表是主表。
		inner完全可以省略，on后面表示两个表连接的条件。

4.DQL（数据查询语言，操作数据的）

执行顺序

语句执行顺序
select distinct 查询列表 ⑦distinct用来去重
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤ 分组一定要写在where后面
having 筛选 ⑥ 筛选写在分组后面
order by排序列表 ⑧
limit 起始条目索引，条目数; ⑨

基础查询

注意：查询到的，看到的是虚拟的，原本数据库可能不长这样。
1.查询列
select 列名 from 表名;
2、查询所有
select * from 表名;
3.查询常量
select 常量值;
常量值如果是字符型或者日期型得用双引号，数字不用。
4.查询函数
select 函数名(实参名);
5.查询表达式
select 表达式;
6.起别名
select 列名 as 别名 from 表名;
select 列名 别名 from 表名;
7.去重
select distinct 列名 from 表名;
8.计算
select 数值+数值; 
直接运算
select 字符+数值;
先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;
结果都为null
9.拼接函数
select concat("字符1"，"字符2");
10.isfull函数
select ifnull(列名，指定的值) from 表名;
如果是null返回指定值，否则返回原值。
11.isnull函数
select isnull(列名) from 表名;
如果为null返回1，否则返回0

条件查询

1. 条件运算

select 列名 from 表名 where 筛选条件
> <  大于 小于
>= <=  大于等于 小于等于
<> !=  不等于 
between and 在什么之间
in 是否在其中

2. 逻辑运算

&& and
|| or
!  not

3. 模糊查询

一、使用like操作符和通配符
1.百分号（%）:表示多个字符
select 列名 from 表名 where 待查询的列名 like '%jol%';
表示匹配到字符串中含有jol的字符串。
2.下划线（_）:表示匹配一个字符
select 列名 from 表名 where 待查询的列名 like '_jol';
表示只能匹配到包含4个字符的字符串。

二、使用正则表达式
1.基本字符匹配
select 列名 from 表名 where 待查询的列名 regexp '1000';
表示检索出包含1000的所有行
select 列名 from 表名 where 待查询的列名 regexp '.000';
.可以表示任意字符
2.进行or匹配
select 列名 from 表名 where 待查询的列名 regexp '1000|2000';
匹配含有1000或者2000的行
3.匹配几个字符之一
select 列名 from 表名 where 待查询的列名 regexp '[123]000';
匹配含有1000、2000或者3000的行，但是可能造成错误查找，
因为mysql会认为是1|2|3000，因此可以写他的完全形式[1|2|3]000。
4.匹配范围
select 列名 from 表名 where 待查询的列名 regexp '[1-9]000';
匹配1000-9000
5.匹配特殊字符
select 列名 from 表名 where 待查询的列名 regexp '\\.';
匹配带.字符的字符串。
\\f 换页 \\n 换行 \\r 回车 \\t 制表 \\v 纵向制表
6.匹配字符类
见下图
7.匹配多个实例
select 列名 from 表名 where 待查询的列名 regexp '[[:digit:]]{4}';
表示匹配匹配任意数字连续4个
	* 0或多个
	+ 1或多个
	? 0或1个
	{n} 指定n个
	{n,} 不少于n个
	{n,m} n-m个
8.定位符
select 列名 from 表名 where 待查询的列名 regexp '^[0-9\\.]';
表示匹配以数字和.开头的字符串。
    ^ 开始
    $ 结尾
    [[:<:]] 词的开始
    [[:>:]] 词的结尾

匹配字符类表

like和regexp的区别 : regexp=like+通配符

排序查询

select 查询列表 from 表where 筛选条件order by 排序列表 【asc|desc】
asc表示升序,一般是默认的可以不写,desc降序.

函数

一.字符函数
1.concat连接函数
select concat(last_name,'_',first_name) 姓名 from 表名;
2.substr截取函数
select substr("123456789",2,5);
输出2,3,4,5.默认索引为1.
3.upper,lower变大写,变小写
select upper("tom");
4.replace替换函数
SELECT REPLACE('一个董憨憨','一个','一只');
参数1：原字符串  
参数2：要被替换的字符串
参数3：替换字符串
5.length获取字节长
select length("123456")
6.trim去除前后空格
select trim('   一个董憨憨   ')
select trim('aa' from "aaaaaaaa一个董憨憨aaaaaaaa");
7.lpad左填充rpda右填充
select lpad("一个董憨憨",4,'a');
8.instr返回第一次出现的索引数
select instr("aaaa一个董憨憨aaaa","一个");

二.数学函数
1.ceil向上取整 floor向下取整
select ceil(1.09);
select floor(1.99);
2.round四舍五入
select round(1,458,2);--保留两位
3.truncate截断
select truncate(1.458,2);
4.mod取整
select mod(10,3);
select 10%3;
5.rand函数,0-1之间

三.日期函数
1.返回当前日期+时间
select now();
2.返回年
select year(now());
select year('2021-4-12');
select year(日期类型的列名) from 表名;
3.返回月
select month(now());
select MONTHNAME(now());
4.返回日
select day(now());
5.data_format将日期转换成字符
select date_format(now(),'%y年%m月%d日');
6.curdate返回当前日期
select curdate();
7.str_to_date将字符转换成日期
select str_to_date('2021-4-12','%y-%c-%d');
8.curtime返回当前时间
select curtime();
9.datediff返回两个日期相差天数
select datediff('2021-4-12','2021-4-13');

四.流程控制函数
1.select if(条件表达式,表达式1,表达式2);
如果表达式成立返回表达式1,否则返回表达式2.
2.case类型
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

分组函数
select SUM(列名) from 表名;
select AVG(列名) from 表名;
select MIN(列名) from 表名;
select MAX(列名) from 表名;
select COUNT(列名) from 表名;

1.语法
	select max(字段) from 表名;
2.支持的类型
	sum和avg一般用于处理数值型
	max、min、count可以处理任何数据类型
3.以上分组函数都忽略null
4.都可以搭配distinct使用，实现去重的统计
	select sum(distinct 字段) from 表;
5.count函数
	count(字段)：统计该字段非空值的个数
	count(*):统计结果集的行数
	count(1):统计结果集的行数

分组查询

select 分组函数，分组后的字段
from 表
【where 筛选条件(分组前筛选)】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
注意:在where后面不能加上分组函数
	例如:select avg(scores) from student where avg(scores)>80;
是错误的

连接查询

1、内连接：等值连接、非等值连接、
2、自连接
3、外连接：左外连接、右外连接、全外连接
4、交叉连接
内连接与外连接的区别：内连接连接的两个表都要满足条件，外连接还要包括不满足条件的部分

1.笛卡尔积(自然连接):就是每一个元组都匹配.
select 列1,列2 from 表1,表2;

2.内连接
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

3.外连接
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

4.交叉连接（类似笛卡尔积）
select 查询列表
from 表1 别名
cross join 表2 别名;

子查询

子查询：嵌套在其他语句内部的select语句称为子查询或内查询， 外面的语句可以是insert、update、delete、select等，
一般select作为外面语句较多， 外面如果为select语句，则此语句称为外查询或主查询

select 后面：仅仅支持标量子查询
from后面：支持表子查询
where和having后面：标量子查询、列子查询、行子查询
exists后面：标量子查询、列子查询、行子查询、表子查询

标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为多行多列
表子查询：结果集为多行多列
不清楚可以移步：各个查询的解析

分页查询

当与java语句进行交互的时候，我们可能会要求每次只显示一部分内容，就像网页一样，一页只显示一部分这时候就可以用到limit语句了。
limit 开始页数的索引 需要页数

select 查询列表
from 表
limit (page-1)*size,size;

5.TCL（事务处理语言，操作事务的）

1.事务

什么是事务？
比如我们去银行取钱给另一个人的银行账户汇钱，那么取钱的操作和汇钱的操作必须同时成功或者同时失败，要不然就会出乱子。

事务的特点：（acid）
原子性：一个事务是不可再分割的整体，要么都执行要么都不执行。
一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态。
隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的。
持久性：一个事务一旦提交了，则永久的持久化到本地。

1.开启事务 set autocommit=0;  start transaction;#可以省略
2.编写一组逻辑sql语句 注意：sql语句支持的是insert、update、delete
3.结束事务 提交：commit; 回滚：rollback;
4.回滚(rollback)
设置回滚点：savepoint 回滚点名;
回滚到指定的地方：rollback to 回滚点名;
解释：在事务中某一项sql语句不想执行了，就可以选择回滚，就是后退的意思。

2.并发事务

想一想，如果两个人同时访问同一个数据库中的某个数据，其中一个人A修改它，另一个人B会看到什么呢？这就是并发事务的问题。mysql用隔离等级来划分另一个人B看到的程度。

隔离等级：
第一级别：读未提交(read uncommitted),B能读到A还未提交的数据，
安全等级最低，相当于没有分隔。会造成脏读现象。（没有）
第二级别：读已提交(read committed),B能读到A已经提交的数据，但是会造成
B用户想要读取A没修改之前的数据就没办法实现，称为不可重复读现象。（玻璃）
第三级别：可重复读(repeatable read),B能重复读了，但是此时数据库内的
数据已经被修改了，B看到的是幻象，已经不是真实的数据了。（墙）
第四级别：串行化(serializable),A访问和修改的时候，不允许B再访问了，解
决了一切问题。（城墙）

设置隔离等级：
在默认状态下，事务是被自动提交的，就是执行一句提交一句sql
命令。因此需要手动设置等级。
	set global transaction isolation level read uncommitted;
设置第一等级。

1.事务使用步骤：
	set autocommit=0;
	start transaction;
	update 表名 set 列名=值 where 限制条件;
	commit;
2.savepoint的使用：
	set autocommit=0;
	start transaction;
	update 表名 set 列名=值 where 限制条件;
	savepoint 保存点名;
	update 表名 set 列名=值 where 限制条件;
	rollback to 保存点名;
	commit;

三、mysql优化

1.索引

索引可以大大降低查找的时间，注意，主键和unique约束字段自动添加索引，当使用含有索引的字段名进行查找的时候，可以优化。

1.查看扫描状况：可以看到扫描了多少条数据。
	explain select * from 表名;
2.创建索引：
	create index 索引名称 on 表名(字段名);
3.删除索引
	drop index 索引名称 on 表名;

2.视图

当部分数据不想让别人看到的时候就可以使用视图，就是对同一个数据库不同的看待方式，名称、数据量等可能会有变化，如果对视图进行修改的话，对数据库本身也会修改，提供了很好的接口。

1.创建视图：
	create view 视图名 as 查询语句;
2.修改视图：
	create or replace view 视图名 as 查询语句;
	alter view 视图名 as 查询语句;
3.删除视图：
	drop view 视图1，视图2;
4.查看视图：
	desc 视图名;
	show create view 视图名;
5.插入数据：
	insert into 视图名 values();
6.修改数据：
	update 视图名 set 字段名=值 where 限制条件;
7.删除数据:
	delete from 视图名 where 限制条件;
8.查看视图:
	select * from 视图名;

注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新.
1.包含分组函数、group by、distinct、having、union、
2.join
3.常量视图
4.where后的子查询用到了from中的表
5.用到了不可更新的视图

3.备份

1.导出数据（备份数据）：
	mysqldump 库名 > 路径名 -uroot -p 密码;
2.导入数据
	create database 库名;
	use 新创建的库;
	source 备份的路径名;

四、mysql设计范式

mysql设计范式：设计表的依据，使得表中不会产生数据的冗余。
1.第一范式：任何一张表都有主键，并且每个字段不可再分。

2.第二范式：非主键字段完全依赖主键，不能产生部份依赖
口诀：多对多，三张表，关系表两个外键（如下图）

3.第三范式：非主键字段直接依赖主键，不能产生传递依赖
口诀：一对多，两张表，多的表加外键。