mysql基础知识

MySQL

Mysql下载地址

https://downloads.mysql.com/archives/community/

命令行连接Mysql

连接到Mysql服务（Mysql数据库）的指令

mysql -h 主机IP（默认是本地） -P 端口（默认3306） -u 用户名 -p密码

注意：密码-p后面没有空格 不输入密码回车会要求输入密码

数据库三层结构

1. 所谓安装Mysql数据库，就是在主机安装了一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。Database Manage System
2. 一个数据库中可以创建多个表，以保存数据（信息）。表的本质是一个文件
3. 数据库管理系统、数据库和表的关系如下图所示

SQL语句分类

• DDL：数据定义语句【create table，db】
• DML：数据操作语句【增删改】
• DQL：数据查询语句【查】
• DCL：数据控制语句【管理数据库 比如用户权限 grant revoke】

操作数据库

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name
					[create_specification[,create_specification]...]
					
create_specification:
		[DEFAULT]CHARACTER SET character_name
		[DEFAULT]COLLATE collation_name
--通过反引号(table键上面的那个按键)规避关键字
create database `create`;

1. CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf8
2. COLLATE:指定数据库字符集的校对规则（常用的utf8_bin[区分大小写]、utf8_general_ci[不区分大小写]）默认是utf8_general_ci

查看、删除数据库

--显示数据库语句
SHOW DATABASES;

--显示数据库创建语句
SHOW CREATE DATABASE db_name;
--数据库删除语句
DROP DATABASE [IF EXISTS] db_name;

备份恢复数据库

• 备份数据库（注意：在DOS执行）
  mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql（可以带路径）
• 恢复数据库（注意：进入Mysql命令行再执行）
  Source 文件名.sql
• 备份数据库的表
  mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > 文件名.sql（可以带路径）

操作表

创建表

CREATE TABLE table_name
(
	field1 datatype.
    field2 datatype.
    field3 datatype
)character set 字符集 collate 校验规则 engine 存储引擎
field：指定列名 datatype：指定列类型（字段类型）
character set / charset：如不指定则为所在数据库的字符集
collate：如不指定则为所在数据库的校对规则
engine：引擎（todo）

注意：创建表时，要根据需保存的数据创建对应的列，并根据数据的类型定义相应的列类型。

Mysql常用数据类型（列类型）

列类型之整型

使用规范：在能满足需求的情况下，尽量选择占用空间小的类型

注意：如果没有指定unsigned（无符号） 则默认是有符号的

定义无符号的整数

create table test(id int unsigned);

数值型(bit)的使用

create table test(num BIT(8));

• bit字段显示时，按照位 的方式显示 例如255 显示b’11111111’
• 查询的时候仍然可以用使用 添加的数值
• 如果一个值只有0，1可以考虑使用bit（1），可以节约空间
• 位类型。M指定位数，默认值1，范围1~64
• 使用不多

列类型之小数型

小数的基本使用

1. FLOAT/DOUBLE [UNSIGNED]
   Float 单精度 ，Double 双精度
2. DECIMAL[M,D] [UNSIGNED]
   • 可以支持更加精确的小数位，M是小数位数（精度）的总数，D是小数点（标度）后面的位数
   • 如果D是0，则值没有小数点或分数部分。M最大65，D最大30。如果D被省略，默认为0.如果M被省略，默认是10.
   • 建议：如果希望小数的精度高，推荐使用decimal（例如与钱相关的字段）

列类型之字符串

字符串的基本使用

• CHAR(size)
  固定长度字符串，最大255字符
• VARCHAR(size)
  可变长度字符串 最大65535字节【utf8编码最大21844字符 1-3个字节用于记录大小】

字符串使用细节

1. char(4) //这个4代表的是字符数（最大255），不是字节数，不管是中文还是字母都是放4个，按照字符计算

   varchar(4)//这个4表示的也是字符数，不管是字母还是中文都已定义好的表的编码来存放数据，比如utf8存放一个字符就消耗了3个字节

2. char(4)是定长（固定长度），就是说即使你插入的是‘aa’，也会占用分配的4个字符空间
   varchar(4)是变长，就是说如果你插入的是‘aa’，实际占用的空间大小并不是4个字符空间，而是按照实际占用空间来分配（varchar本身还需要1-3个字节来记录存放内容长度）

3. 什么时候使用char，什么时候使用varchar

   • 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等
   • 如果一个字段的长度是不确定，我们使用varchar，比如留言，文章等
   • 查询速度char>varchar
   • 在存放文本时，也可以使用TEXT类型，可以将TEXT列视为VARCHAR列，注意TEXT不能有默认值，大小0_{2^16字节，如果希望存放更多字符，可以选择MEDIUMTEXT（0}2^{24）或者LONGTEXT(0~2}32)

列类型之日期类型

日期类型的基本使用

CREATE TABLE t14(
					birthday DATE,
					job_time DATETIME,
					login_time TIMESTAMP NOT NULL DEFAULT
					CURRENT_TIMESTAMP ON UPDATE
					CURRENT_TIMESTAMP);
-- 	默认不为空且自动更新				
-- NOT NULL DEFAULT
-- CURRENT_TIMESTAMP ON UPDATE
-- CURRENT_TIMESTAMP

INSERT INTO t14(birthday,job_time) VALUES('1995-10-08',"2019-07-03 10:10:10");

修改表

添加列

ALTER TABLE table_name
ADD 	column1 datatype[DEFAULT EXPR]
        [,ADD column2 datatype]...;
--单个参数不需要加括号
--控制参数位置 after

修改列

--修改属性
ALTER TABLE table_name
MODIFY 	column1 datatype[DEFAULT EXPR]
        [, MODIFY column2 datatype]...;
 --修改列名
 ALTER TABLE employee CHANGE COLUMN `name` `username` VARCHAR(30);
        

删除列

ALTER TBALE table_name
DROP  column1 [DROP COLUMN2...];
--查看表的结构：desc 表名;--可以查看表所有的列

修改表名：Rename table 表名 to 新表名;

修改表字符集：alter table 表名 charset 字符集;

CRUD

1. Insert语句（添加数据 create）
2. Update语句（更新数据 update）
3. Delete语句（删除数据delete）
4. Select语句（查询语句 read）

Insert

insert into table_name (column1,column2[,column3...]) 
values(value1,value2[,value3...])

细节说明：

1. 插入的数据应与字段的数据类型相同。
   比如把‘abc’添加到int类型会错误
2. 数据的长度应在列的规定范围内
   例如：不能将一个长度为80的字符串加入到长度为40的列中
3. 在values中列出的数据位置必须与被加入的列的排列位置相对应
4. 字符和日期型数据应包含在单引号中
5. 列可以插入空值（前提是该字段允许为空）
   insert into table values(null)
6. insert into tab_name(列名) value(),(),() 形式添加多条数据
7. 如果是给表中的所有列添加数据，可以不写前面的字段名称
8. 默认值的使用，当不给某个字段赋值时，如果有默认值就会添加，否则报错

Update

update tab_name
set column1 = value1,column2 = value2[,column3 = value3...]
[where where_definition]

细节说明：

1. update语法可以用新值更新原有表行中的各列
2. set子语句指示要修改哪些列和给予哪些值
3. where子语句指定应更新哪些行。如果没有where子语句，则更新所有的行(慎用！！！)
4. 如果需要修改多个字段，可以通过set 字段1=值1，字段2=值2…

Delete

delete from tab_name
[where where_definition]

细节说明：

1. 如果不使用where语句，将删除表中所有的数据
2. Delete语句不能删除某一列的数据（可使用update设为null或者‘ ’）
3. 使用delete语句仅删除记录，不删除表本身。如果要删除表，使用drop table

Select

select [DISTINCT] *|{column1,column2,colunm3...}
from tab_name;

注意事项：

1. select指定查询哪些列的数据
2. column指定列名
3. *代表查询所有列
4. from指定查询哪张表
5. distinct（取消重复行） 可选，指显示结果时，是否去掉重复数据

使用表达式对查询的列进行运算

select *|{column1 | expression,column2 | expression...}
from tab_name

在select语句中可使用as语句(as可以省略)

select column as 别名 from tab_name;

在where子语句中经常使用的运算符

比较运算符

> < <= >= = <> !=	大于 小于 大于(小于)等于、不等于
between…and…	显示在某一区间的值(都是闭区间)
in(set)	显示在in列表中的值，例：in(100,200)
like ‘%张%’ not like	模糊查询
is null	判断是否为空

逻辑运算符

and	多个条件同时成立
or	多个条件任一条件成立
not	不成立，例如：where not(age>10)

使用order by 字句排序查询结果

select *| column1,column2,column3...
from tab_name 
order by column1 asc|desc,...

细节说明

1. order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名
2. asc 升序（默认），desc 降序
3. order by 字句应位于select语句的结尾

函数

合计/统计函数

合计函数 -count

count返回行的总数

select count(*) | count(列名) from tab_name
[where where_definition]

count(*) 和 count（column）的区别

count(*) 返回所有满足条件的行数

count(column) 会排除column值为null的情况

CREATE TABLE test_count (NAME VARCHAR (20)) ;

INSERT INTO test_count VALUES('tom'),('jack'),('mary'),(NULL);

SELECT COUNT(*) FROM test_count;--4

SELECT COUNT(NAME) FROM test_count;--3

合计函数-sum

sum函数返回满足where条件的行的和

select sum(列名1) [,sum(列名2)...] from tab_name
[where where_difinition]

注意：sum只对值类型有效，其他类型的会报错

合计函数-avg

avg函数返回满足where条件的一列的平均值

select avg(列名1)[,avg(列名2)...] from tab_name
[where where_definition]

合计函数-max/min

max/min 函数返回满足where条件的一列的最大/最小值

select max(列名) from tab_name
[where where_difinition]

分组统计

使用group by子句对列进行分组

select column1,column2,column3...from tab_name
group by column

使用having子句对分组后的结果进行过滤

select column1,column2,column3...from tab_name
group by column having ...

group by用于对查询后的结果进行分组统计，

having子句用于限制分组显示结果

字符串函数

charset（str)	返回字符串字符集
concat(string2[,…])	连接字符串
instr(string,substring)	返回substring在string中出现的位置，没有返回0
ucase(string2)	转换成大写
lcase(string2)	转换成小写
left(string2,length) /right	从string2中的左/右边起取length个字符
length(string2)	string2的长度[按照字节]
replace(str,search_str,replace_str)	在str中用replace_str代替search_str
strcmp(string1,string2)	逐字符比较两个字符串的大小
substring(str,positon[,length])	从str的position开始【从1开始计算】，取length个字符
ltrim(string2) rtrim(string2) trim	去除前端空格或者后端空格

数学函数

abs(num)	绝对值
bin(decimal_number)	十进制转二进制
ceiling(num)	向上取整，比num大的最小整数
conv(num,from_base,to_base)	进制转换
floor(num)	向下取整，比num小的最大整数
format(num,decimal_places)	保留小数位数 四舍五入
hex(decimalnumber)	转十六进制
least(num1,num2[,…])	求最小值
mod(numerator,denominator)	求余
rand([seed])	rand([seed])其范围为0≤v≤1

rand()每次返回的数值不同，一旦给了seed种子 那么这个种子对应的随机数就是固定的了

日期函数

current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回该时间的日期部分
date_add(date,INTERVAL d_value d_type)	在date中加上日期或时间
date_sub(date,INTERVAL d_value d_type)	在date上减去一个时间
datediff(date1,date2)	日期差（单位是天）date1-date2 可能是负数
timediff(time1,time2)	时间差（多少小时多少分钟多少秒）
now()	当前时间
YEAR|MONTH|DATE (datetime) FROM_UNIXTIME()	年月日

注：INTERVAL 的d_type 可以是YEAR MONTH DAY HOUR MINUTE SECOND

select UNIX_TIMESTAMP() from dual; --返回1970到现在的秒数 
-- 类似java中的System.currentTimeMillis()获取毫秒数
SELECT FROM_UNIXTIME(1655397866,'%Y-%M-%D %H:%I:%s');
--将UNIX_TIMESTAMP()转为指定格式的日期

--意义：在开发中，可以存放一个整数，然后表示时间通过该函数进行转换

加密和系统函数

user()	查询用户
datebase()	数据库名称
md5(str)	为字符串算出一个MD5 32 的字符串，（用户密码）加密
password(str)	从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密 mysql8.0已经移除 用sha1(str)代替

流程控制函数

if(expr1,expr2,expr3)	如果expr1为true，则返回expr2，否则返回expr3（三元运算）
ifnull(expr1,expr2)	如果expr1不为null空，则返回expr1，否则返回expr2
select case when expr1 then expr2 when expr3 then expr4 else expr5 end	如果expr1为true，则返回expr2，如果expr3为true，则返回expr4其他情况返回expr5

注意：判读是否为空使用 is (not) null

case when 的两种写法（ job的位置不同）

select ename,job,(SELECT
CASE
		WHEN job = 'clerk' THEN
		'职员' 
		WHEN job = 'manager' THEN
		'经理' 
		WHEN job = 'salesman' THEN
		'销售' 
		ELSE job 
	END ) from emp;
	
select ename,job,(SELECT
CASE job
		WHEN 'clerk' THEN
		'职员' 
		WHEN 'manager' THEN
		'经理' 
		WHEN 'salesman' THEN
		'销售' 
		ELSE job 
	END ) from emp;
	

单表查询的加强

-- 如何用where查询1991-1-1之后入职的员工
select * from emp;

select * from emp where DATEDIFF(hiredate,'1991-1-1')>0

select * from emp where hiredate > '1991-1-1'

-- 模糊查询 查询首字母为s的员工信息

select * from emp where ename like 'S%';


-- 显示第三个字母为o的员工  _表示单个字符 %表示多个字符
select * from emp where ename like '__o%'

-- 显示没有上级的员工的信息
select * from emp where mgr is NULL

desc emp;


-- 按照工资升序排列
select * from emp ORDER BY sal;

-- 按照部门号升序工资降序排列
select * from emp ORDER BY deptno,sal DESC

分页查询

基本语法

select ... limit start ,rows
-- 表示从start+1行开始取，取出rows行，start从0开始计算

分组查询加强

-- 显示每种岗位的雇员人数和平均工资 emp表
select job,count(*),FORMAT(AVG(sal),2) from emp GROUP BY job;

desc emp;

-- 显示雇员总数以及获得补助的雇员数
SELECT COUNT(*),(SELECT COUNT(*) from emp WHERE comm is not NULL) from emp ;

-- count(column) 只统计非空数据
select COUNT(*),count(comm) from emp;

-- 统计没有获得补助的人
select count(*),count(IF(comm is NULL,1,NULL)) from emp;

-- ifnull(expr1,expr2) 如果expr1 为空 则返回expr2 不为空返回expr1
-- select count(*),count(IFNULL(comm,1)) from emp



-- 显示管理者的总人数 只要在mgr中出现过的就是管理者
select COUNT(mgr) from (SELECT DISTINCT mgr from emp) temp;

select COUNT(DISTINCT mgr) from emp;

-- 显示雇员工资的最大差额

SELECT MAX(sal),MIN(sal),MAX(sal)-MIN(sal) from emp

数据分组的总结：如果select语句中同时包含了group by ，having，limit，order by 那么他们的顺序是group by，having，order by

-- 请统计各个部门的平均工资 并且是大于1000的 并且按照平均工资从高到底排序 取出前两行
-- 分析 各个部门---GROUP BY
-- 大于1000 HAVING 
-- 排序 ORDER BY
-- 取出前两行  limit
-- 平均工资 avg
SELECT
	AVG( sal ) avg_sal,
	deptno 
FROM
	emp 
GROUP BY
	deptno 
HAVING
	avg_sal > 1000 
ORDER BY
	avg_sal DESC 
LIMIT 0,2

多表查询

多表查询是指基于两个或两个以上的表查询，在实际应用中，查询单个表可能不能满足你的要求

tips：多表查询的条件不能少于表的个数-1 否则会出现笛卡尔积

自连接

自连接是指在同一张表的连接查询

特点：

1. 把同一张表当成两张表用
2. 需要给表取别名
3. 列名不明确，可以指定列的别名

子查询

• 什么是子查询
  子查询就是☞嵌套在其它sql语句中的select语句，也叫嵌套查询

• 单行子查询
  单行子查询是指只返回一行数据的子查询语句

• 多行子查询
  多行子查询是指返回多行数据的子查询 使用关键字 in

• 在多行子查询中使用all操作符

  select * from emp where sal > all(select sal from emp where deptno = 30)
  
  select * from emp where sal > (select MAX(sal) from emp where deptno = 30)
  

• 在多行子查询中使用any操作符

  -- any的用法 满足任意一个即可
  
  select * from emp where sal > ANY(select sal from emp where deptno = 30)
  
  SELECT * from emp WHERE sal > (SELECT MIN(sal) from emp WHERE deptno = 30)
  

• 多列子查询
  多列子查询是指查询返回多个列数据的子查询语句
  基本语法：（字段1，字段2…）= (select 字段1,字段2 from tab_name where where_definition)

• 将子查询作为临时表使用

   -- 查询每个部门中工资最高的人的信息
   
   select MAX(sal),deptno from emp GROUP BY deptno;
   
   
  select emp.* from emp,
  (select MAX(sal) max_sal,deptno from emp GROUP BY deptno) temp 
  WHERE
  emp.deptno = temp.deptno
  and 
  emp.sal = temp.max_sal
  

表复制

自我复制数据（蠕虫复制）

应用场景：为了对某个sql进行效率测试，我们需要海量数据，可以使用此方法为表创建海量数据

INSERT into my_tab01 SELECT * from my_tab01

 
 CREATE TABLE my_tab01
				( id INT,
					`name` VARCHAR(32),
					sal DOUBLE,
					job VARCHAR(32),
					deptno INT);
					
INSERT into my_tab01
				select empno,ename,sal,job,deptno from emp;

-- 自我复制

INSERT into my_tab01 
SELECT * from my_tab01

select COUNT(*) from my_tab01

DELETE from my_tab01


--  如何删除重复数据
-- java变量交换思想
insert into my_tab01 
SELECT empno,ename,sal,job,deptno from emp

-- 创建结构一样的临时表
CREATE TABLE my_tab_temp like my_tab01;
-- 导入去重数据 distinct
insert into my_tab_temp
select DISTINCT * from my_tab01;

-- 删除原来表并改名字或者删除原来表的数据 再将临时表数据导回去
-- 方法一
DROP table my_tab01;
RENAME TABLE my_tab_temp to my_tab01;
select * from my_tab01;

--方法二
delete from my_tab01;
insert into my_tab01 
select * from my_tab_temp;
drop table my_tab_temp;
select * from my_tab；

合并查询

介绍：有时候在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all

1. union all
   该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
2. union
   该操作符用于取得两个结果集的并集。当使用该操作符时，取消重复行

mysql表外连接

• 外连接
  1. 左外连接 （如果左侧的表完全显示，我们就说是左外连接）
  2. 右外连接 （如果右侧的表完全显示，我们就说是右外连接）

mysql约束

基本介绍：约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null,unique,primary key,foreign key,和check五种

主键

字段名 字段类型 primary key

-- 用于唯一的标示表行的数据，当定义主键结束后，该列不能重复不能为空

细节说明

1. 主键不能重复而且不能为空

2. 一张表最多只能有一个主键，但可以是复合主键（id+name ）

   create TABLE t18(
   id int,
   name VARCHAR(32),
   email VARCHAR(32),
   PRIMARY KEY (id,name));
   

3. 主键的定义方式有两种

   • 一种在字段后面指定 字段 primary key
   • 在表定义最后写 primary key（列名）

4. 使用desc 表名 可以看到主键的情况

5. 在实际开发中 每个表往往都会设计一个主键

非空

如果在类上定义了not null ,那么当插入数据时必须为列提供数据

字段名 字段类型 not null

唯一

当定义了唯一约束后，该列的值是不能重复的

字段名 字段类型 unique

细节说明 ：

1. 如果没有指定not null ，则unique字段可以有多个null
2. 一张表可以有多个unique字段

外键

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

foreign key (本表字段名) references 主表名（主键名或unique字段名）
-- 括号不能省略

细节说明：

1. 外键指向的表的字段，要求是primary key 捉着是unique
2. 表的类型是innodb，这样的表才支持外键
3. 外键字段的类型要和主键字段的类型一致（长度可以不一样）
4. 外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为null】
5. 一旦建立主外键的关系，数据就不能随意删除了
6. 有关联的数据必须先删除外键的所有关联数据再删除主键的数据

check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并且要求sal值在1000-2000之间，如果不在1000-2000之间就会提示错误。

👉oracle和sql server 均支持check，但是mysql5.7不支持check，只做语法校验，但不会生效 8.0.19支持check

列名 类型 check(check条件)

在mysql中实现check的功能，一般是在程序中控制，或者通过触发器完成

create table t20(
id int PRIMARY KEY,
name VARCHAR(32) not null default '',
sex varchar(10) CHECK (sex in ('man','woman')),
sal DOUBLE CHECK (sal BETWEEN 1000 and 2000));


insert into t20 values (3,'jack','woman',3000);
-- Check constraint 't20_chk_2' is violated.

SELECT * from t20;

自增长

字段名 整型 primary key auto_increment

-- 该列从最后一行数据开始自动的增加

细节说明：

1. 一般来说自增长是和primary key配合使用的
2. 自增长也可以单独使用（但是需要配合一个unique）
3. 自增长修饰的字段为整数型的（虽然小数也可以，但是非常非常少这样使用）
4. 自增长默认从1开始，你也可以通过如下命令修改
   alter table 表名 auto_increment = xxx;
5. 就算表中记录删除了 自增长还是会从删除前的记录开始自增 不会刷新
6. 如果指定了自增长，一把按照自增长的规则来添加数据

索引

说起提高数据库的性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍。

索引的原理：形成一个索引的数据结构，比如二叉树

索引的代价：

1. 磁盘占用
2. 对dml（增删改）语句的效率影响

在项目中 select操作多于增删改操作

索引的分类

• 主键索引
  主键自动的为索引（类型为primary key）
• 唯一索引（unique）
• 普通索引（index）
• 全文索引（fulltext）【适用于MyISAM】
  一般开发不适用mysql自导的全文索引，开发中考虑使用：全文搜索solr和elasticsearch

索引使用

1. 添加索引

   create [unique] index index_name on tab_name (col_name[(length)])
   [ASC|DESC],...
   

   alter table tab_name add index index_name (index_col_name,…)

2. 添加主键（索引）alter table 表名 add primary key(列名,…)

3. 删除索引
   drop index index_name on tab_name;
   alter table tab_name drop index index_name;

4. 删除主键索引 alter table tab_name drop primary key;

5. 查询表是否又索引

   • show index from tab_name;
   • show indexes from tab_name;
   • show keys from tab_name;
   • desc tab_name

小结

1. 较为频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使作为频繁查询条件
   select * from emp where sex = ‘男’
3. 更新非常频繁的字段不适合创建索引
   select * from user where logincount = 1;
4. 不会出现在where子句中的字段不该创建索引

mysql事务

事务用于保证数据的一致性，它又一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性

事务和锁

当执行事务操作时（dml语句），mysql会在表上加锁，反正其他用户改表的数据，这对用户来讲非常重要

事务操作

1. start transaction – 开始一个事务
2. savepoint 保存点名 – 设置保存点
3. rollback to 保存点名 – 回退事务
4. rollback – 回退全部事务
5. commit --提交事务，所有的操作失效，不能回退
6. – 自动提交事务开关两种方式
   SHOW VARIABLES LIKE ‘autocommit’;
   select @@autocommit 0-不是自动提交 1-自动提交

细节：

1. 没有设置保存点
2. 多个保存点
3. 存储引擎
4. 开始事务方式

保存点

保存点是事务中的点，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点

回退事务

当执行回退任务时，通过指定保存点可以回退到指定的点

提交事务

使用commit语句可以提交事务，当执行了commit语句子句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务之后，其他会话将可以查询到事物变化后的新数据

事务细节

1. 如果不开始事务，默认情况下，dml操作是自动提交的不能回滚
2. 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认回到事务开始的状态（事务也会关闭）
3. 你也可以在这个事务中（还没有提交时），创建多个保存点，比如：savepoint aaa；执行dml，savepoint bbb；
4. 你可以在事务没有提交前，选择回退到哪个保存点 rollback to aaa;
5. mysql的事务机制需要innodb的存储引擎，myisam不好使
6. 开始一个事务的两种方式 start transaction，set autocommit = off；
7. 查看自动提交状态SHOW VARIABLES LIKE ‘autocommit’;select @@autocommit 0-不是自动提交 1-自动提交

事务隔离级别

定义：mysql隔离级别定义了事务与事务之间的隔离程度

1. 多个连接开启各自事务操作数据库中的数据时，数据库系统要负责隔离操作，以保证每个连接在获取数据时的准确性（通俗解释：每个事务根据隔离级别的不同看到的同一张表的数据不一样）
2. 如果不考虑隔离性，可能会引发如下问题：
   • 脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读
   • 不可重复读（nonrepeatable read）：同一个查询在同一个事务中多次进行，由于其他提交事务所做的修改或者删除，每次返回不同的结果集，此时发生不可重复读
   • 幻读（phantom read）：同意查询在同一事务中进行，由于其他事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

mysql隔离级别	脏读	不可重复读	幻读	加锁读
读未提交（read uncommitted）	✔	✔	✔	不加锁
读已提交（read committed）	×	✔	✔	不加锁
可重复读（repeatable read）	×	×	×	不加锁
可串行化（serializable）	×	×	×	加锁

查看当前隔离级别：select @@transaction_isolation;

查看系统当前隔离级别：select @@global.transaction_isolation;

设置当前会话隔离级别：set session transaction isolation level 隔离级别；

例如：set session transaction isolation level read uncommitted；

设置系统会话隔离级别：set global transaction isolation level 隔离级别；

例如：set global transaction isolation level read uncommitted；

mysql默认的隔离级别是repeatable read，一般情况下，没有特殊要求没有必要修改（该级别可以满足绝大部分项目的需求）

全局修改，修改my.ini文件，在[mysqlId]中添加

transaction-isolation = repeatable read

事务的acid特性

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么多发生，要么都不发生
2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换成另一个一致性状态
3. 隔离性（Isolation）
   事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

mysql表类型和存储引擎

基本介绍

1. mysql的表类型由存储引擎（storage engines）决定，主要包MyISAM、innoDB、Memory等
2. mysql数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MGR_MYISAM、MyISAM、InnoDb
3. 这六种又分为两类，一类是“事务安全型”（transaction-safe），比如：InnoDB；其余属于第二类，称为“非事务安全型”（non-transaction-safe）【MyISAM和Memory】

主要的存储引擎/表类型特点

特点	MyISAM	InnoDB	Memory	Archive
批量插入的速度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
存储限制	没有	64TB	有	没有
B树索引	支持	支持	支持
哈希索引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

细节说明

1. MyISAM不支持事务也不支持外键，但其访问速度快，对事务完整性没有要求
2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
3. Memory存储引擎使用存在内存中的内容来创建表。每个Memory表只实际对应一个磁盘文件。Memory类型的访问速度非常的快，因为它的数据是放在内存中的，并且默认使用hash索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在

如何选择表的引擎

1. 如果你的应用不需要事务，处理的只是基本的crud操作，那么MyISAM是不二选择，速度快
2. 如果需要支持事务，选择InnoDB
3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘io的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务重启后将消失（经典用法 用户的在线状态）

修改存储引擎

alter table tab_name engine = engine_type;

视图（view）

基本概念

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
通过视图可以修改基表的数据，基表的改变也会影响视图的数据

视图的基本使用

1. create view view_name as select语句
2. alter view view_name as select语句
3. show create view view_name;
4. drop view view_name1,view_name2…;

细节说明

1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm）
2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图
3. 视图中可以再次使用视图

视图最佳实践

1. 安全。一些数据有着重要的信息。有些字段是保密的，不能让用户看到。这时可以创建一个视图，在这张试图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（join）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用join查询数据。
3. 灵活。如果系统表中有一张旧表存在设计问题，需要舍弃，但是很多应用都是基于这张表的，不宜修改，这时可以建立一个视图，视图中的数据直接映射到新建的表。这样就可以少很多改动，也可以达到升级数据表的目的。

mysql管理

mysql用户

mysql中的用户，都存储在系统数据库mysql中的user表中

user表字段说明：

1. host：允许登录的位置（IP），localhost表示只允许本地登录，也可以直接指定ip
2. user： 用户名
3. authentication_string： 密码，是通过mysql的password（）函数加密之后的密码

用户操作

创建用户

create user ‘用户名’@‘允许登录的位置’ identified by ‘密码’

说明：创建用户同时指定密码 @后面没有空格

删除用户

drop user ‘用户名’@‘允许登录的位置’；

修改密码

• 修改自己的密码
  set password = password(‘密码’) //5.7版本
  set password = ‘密码’ //8.0版本

• 修改他人的密码（需要有修改用户的权限）
  set pssword for ‘用户’@‘登录的地址’ = password('密码‘) //5.7版本

  set pssword for ‘用户’@‘登录的地址’ = '密码‘ //8.0版本

给用户授权/回收权限

授权

grant 权限列表 on 库.对象名 to ‘用户’@’登录位置‘ [identified by ‘密码’]//8.0版本不能接密码

说明：

1. 权限列表，多个权限用逗号隔开
   grant select on …
   grant select,delete,create,… on …
   grant all [privileges] on…

2. 特别说明

   *.*代表本系统中的所有数据库的所有对象（表、视图、存储过程）
   库,* 代表某个数据库中的所有数据对象（表、视图、存储过程等）
   

3. identified by 可以省略，也可以写出//8.0版本不可用

   • 如果用户存在，就是修改该用户的密码
   • 如果该用户不存在就是创建该用户并赋给相应的权限

回收权限

revoke 权限列表 on 库.对象名 from ‘用户名’@‘登陆位置’

revoke all on testdb.news from ‘test’@‘localhost’

权限生效 mysql8.0

flush privileges；

细节说明

1. 在创建用户的时候，如果不指定host，则为%，%表示所有IP都有连接权限
   create user xxx
2. 也可以指定一个网段
   create user ’xxx‘@‘192.168.1.%’ 表示xxx用户可以在192.168.1网段的ip登录
3. 在删除用户的时候，如果host不是%，需要明确指定’用户’@‘host’