MySQL基础

SQL语句分类：

• DDL：数据定义语句 【create 表，数据库…】
• DML：数据操作语句【增加insert，修改update，删除delete】
• DQL：数据查询语句【select】
• DCL：数据控制语句【管理数据库：用户权限grant revoke】

MySQL三层结构

MySQL数据库-普通表的本质仍然是文件

数据在数据库的存储方式：由行（row）和列（column）组成；表的一行称为一条记录 ，在java程序中，一行记录往往通过对象来映射

数据库部分

创建数据库

语法格式：

CREATE DATABASE [IF NOT EXISTS] <数据库名>

[[DEFAULT] CHARACTER SET <字符集名>] 

[[DEFAULT] COLLATE <校对规则名>];

[ ]中的内容是可选的。语法说明如下：

• <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
• IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
• [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集utf8。
• [DEFAULT] COLLATE：指定字符集的校对规则，常用的utf8_bin（区分大小写），默认是utf8_general_ci（不区分大小写）。

MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。

例如：创建一个使用utf8字符集，校验规则为utf8_bin的sql_01数据库

create database sql_01 
char set utf8 
collate utf8_bin;

查看和删除数据库

查看数据库：

SHOW DATABASES [LIKE '数据库名'];

语法说明如下：

• LIKE 从句是可选项，用于匹配指定的数据库名称。LIKE 从句可以部分匹配，也可以完全匹配。
• 数据库名由单引号' '包围。

查看数据库定义信息语句：

SHOW CREATE DATABASE 'db_name'

例如：show create database sql_01;

在创建数据库中，若想规避关键字，可以用反引号` `来解决

删除数据库

DROP DATABASE [IF EXISTS] db_name

备份恢复数据库

备份数据库（注意：在DOS执行）命令行

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql

这个备份文件就是对应的sql语句

备份数据库的表（注意：在DOS执行）

mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > 文件名.sql

恢复数据库（注意：在MySQL命令行再执行）

Source 文件名.sql

实际上恢复数据库也可以打开备份sql文件，复制语句并执行。

数据表部分

创建表

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)character set 字符集,collate 校验规则 engine 存储引擎

数据表也可以指定字符集和校验规则，若不指定，则默认使用数据库的

CREATE TABLE user (
	id INT,
    name VARCHAR(255),
    password VARCHAR(255),
    birthday DATE
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

常用数据类型（列类型）

补充：枚举类型enum(‘男’,‘女’)

字符与字节有什么区别

（一）“字节”的定义

字节（Byte）是一种计量单位，表示数据量多少，它是计算机信息技术用于计量存储容量的一种计量单位。

（二）“字符”的定义

字符是指计算机中使用的文字和符号，比如1、2、3、A、B、C、~！·#￥%……—*（）——+、等等。

（三）“字节”与“字符”

它们完全不是一个位面的概念，所以两者之间没有“区别”这个说法。不同编码里，字符和字节的对应关系不同：

①ASCII码中，一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间。一个二进制数字序列，在计算机中作为一个数字单元，一般为8位二进制数，换算为十进制。最小值0，最大值255。

②UTF-8编码中，一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。

③Unicode编码中，一个英文等于两个字节，一个中文（含繁体）等于两个字节。

符号：英文标点占一个字节，中文标点占两个字节。举例：英文句号“.”占1个字节的大小，中文句号“。”占2个字节的大小。

④UTF-16编码中，一个英文字母字符或一个汉字字符存储都需要2个字节（Unicode扩展区的一些汉字存储需要4个字节）。

⑤UTF-32编码中，世界上任何字符的存储都需要4个字节

字符串使用细节

1. char(4) varchar(4) 这个4说的是字符而不是字节，不区分这个字符是汉字还是字母。

2. char(4)是定长，即使插入的是’aa’，也会占用分配的4个字符空间。

   varchar(4)是变长，插入’aa’会根据实际占用空间分配，varchar本身还需要占用1-3个字节来记录存放内容的长度 真正长度=L(实际长度大小) +1到3个字节。

3. 查询速度：char>varchar ：如果数据是定长，推荐使用char；如果长度不确定，推荐使用varchar

4. 存放文本时，可以使用text数据类型替换varchar，注意text不能有默认值，大小0 ~ 2^16 字节，如果想要存放更多的字符，使用mediumtext 0~ 2^24 和longtext 0 ~ 2^32；数字占一个字节，汉字占3个字节。

日期类型使用细节

create TABLE t14(
	birthday date, -- 生日
    job_time time, -- 记录年月日 时分秒
    login_time timestamp 
		NOT NULL DEFAULT current_timestamp 
		on update current_timestamp -- 登录时，如果希望login_time自动更新，需要配置
);
insert into t14(birthday,job_time)values('2021-07-04','2021-07-04 20:13:14');
select * from t14;

timestamp在insert和update时，自动更新

修改表

• 添加列

  ALTER TABLE table_name
  	ADD (column_name datatype [DEFAULT expr]);
  

• 修改列

  ALTER TABLE table_name
  	MODIFY (column_name datatype [DEFAULT expr]);
  

• 删除列

  ALTER TABLE table_name 
  	DROP COLUMN column_name
  

• 查看表的结构

  desc table_name; -- 可以查看表的列
  

• 修改表名

  rename table table_name to new_table_name 
  

• 修改表字符集

  alter table table_name character set 字符集;
  

数据库的CRUD

insert语句

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

insert使用细节

1. 插入的数据类型要跟列类型一致

2. 数据长度应在列的规定范围内

3. 在values中列出的数据位置应与被加入列的排列位置相对应

4. 字符和日期类型应包含在单引号中

5. 列可以插入空值（前提是列可以为空）

6. 可以在values后面用逗号分隔多个括号来插入多个值：INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…),(值1, 值2,…),(值1, 值2,…)

7. 如果是给表中所有字段插入数据，可以省略字段名称（即列1,列2）

8. 默认值的使用，当不给某个字段赋值时，如果有默认值则会添加，否则报错；

   如果某列没有指定not null，那么添加数据时，默认值就为null；

   使用default指定默认值

update语句

UPDATE table_name
	SET column1=value1,column2=value2,...
		[WHERE some_column=some_value];

update使用细节

1. update可以使用新值更新原有表行各列。
2. set语句指定要修改哪些列和要给予哪些值。
3. where语句指定修改哪些行，没有where则针对整个表修改数据

delete语句

DELETE FROM table_name
	WHERE some_column=some_value;

delete使用细节

1. 如果不使用delete子句，则删除表中所有数据
2. delete不能只删除某一列的值
3. 使用delete仅删除记录，而不能删除表，删除表使用drop table 表名。

select语句

SELECT [DISTINCT] column_name,column_name
	FROM table_name;

DISTINCT可选，指显示结果时，是否去掉重复数据（要查询的记录，只有每个字段都相同，才会去重）

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

  SELECT [DISTINCT] column_name|expression,column_name|expression
  	FROM table_name;
  

• 在select语句中使用别名

  SELECT column_name as 别名
  	FROM table_name;
  

例子：统计每个学生的总分并使用别名

select 'name',(chinese+english+math) as total_score from student

Where子句

常使用的运算符

举例说明

搜索 empno 等于 7900 的数据：

Select * from emp where empno = 7900;

Where + 条件（筛选行）

条件：列，比较运算符，值

比较运算符包涵：= > < >= ,<=, !=,<> 表示（不等于）

Select * from emp where ename = 'SMITH';

例子中的 SMITH 用单引号引起来，表示是字符串，字符串要区分大小写。

逻辑运算

And : 与 同时满足两个条件的值。

Select * from emp where sal > 2000 and sal < 3000;

查询 EMP 表中 SAL 列中大于 2000 小于 3000 的值。

Or : 或 满足其中一个条件的值

Select * from emp where sal > 2000 or comm > 500;

查询 emp 表中 SAL 大于 2000 或 COMM 大于500的值。

Not : 非 满足不包含该条件的值。

select * from emp where not sal > 1500;

查询EMP表中 sal 小于等于 1500 的值。

逻辑运算的优先级：

()    not        and         or

特殊条件

1.空值判断： is null

Select * from emp where comm is null;

查询 emp 表中 comm 列中的空值。

2.between and (在 之间的值)

Select * from emp where sal between 1500 and 3000;

查询 emp 表中 SAL 列中大于等于 1500 的小于等于 3000 的值。

3.In

Select * from emp where sal in (5000,3000,1500);

查询 EMP 表 SAL 列中等于 5000，3000，1500 的值。

4.like

Like模糊查询

Select * from emp where ename like 'M%';

查询 EMP 表中 Ename 列中以 M 开头的值，M 为要查询内容中的模糊信息。

• % 表示多个字值，_下划线表示一个字符；
• M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
• %M% : 表示查询包含M的所有内容。
• %M_ : 表示查询以M在倒数第二位的所有内容。

5.不带运算符

WHERE 子句并不一定带比较运算符，当不带运算符时，会执行一个隐式转换。当 0 时转化为 false，1 转化为 true。例如：

SELECT studentNO FROM student WHERE 0

则会返回一个空集，因为每一行记录 WHERE 都返回 false。

Order By子句

SELECT column_name,column_name
	FROM table_name
	ORDER BY column_name,column_name ASC|DESC;

• Order by指定排序的列，既可以是表中的列名，也可以是select语句中指定的别名。

• ASC(默认)升序，DESC降序

• Order by子句应位于select语句的结尾。

函数

统计函数

COUNT() 函数

COUNT() 函数返回匹配指定条件的行数。

• COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name;

• COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

• COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name;

注释：COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access。

SUM() 函数

返回满足where条件的行的和，一般使用在数值列

SELECT SUM(column_name) FROM table_name 
			[WHERE where_expression];

例子：

统计班级数学成绩总和

select sum(math) from student

AVG() 函数

AVG() 函数返回数值列的平均值。

SELECT AVG(column_name) FROM table_name

MAX() 和 MIN()函数

MAX() 函数返回指定列的最大值，MIN() 函数返回指定列的最小值。

SELECT MAX|MIN(column_name) FROM table_name;

例子：

求出班级数学最高分和最低分

select MAX(math),MIN(math) from student

Group By 分组和having过滤子句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SELECT column1,column2...
	FROM table_name
	GROUP BY column;

例子：显示每个部门的平均工资和最高工资

select avg(sal),max(sal),deptno from emp group by deptno;

例子：显示每个部门的每种岗位的平均工资和最低工资

select avg(sal),min(sal),deptno,job from emp group by deptno,job;

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

SELECT column1,column2...
	FROM table_name
	GROUP BY column having...;

例子：显示平均工资低于2000的部门号和他的平均工资

select avg(sal) as avg_sal deptno 
	from emp group by deptno 
		having avg_sal < 2000;

字符串函数

常用字符串函数：

例如：使用concat连接字符串，把多列拼成一列

select concat(ename,'job is',job) from emp;

使用instr查找字符串

dual 亚元表，系统表 可以作为测试表使用

select instr('lai','i') from dual;

例子：以首字母小写的方式显示所有的员工emp表的名字

select concat( lcase( substring(ename,1,1) ), substring(ename,2) ) 
		from emp;
select concat( lcase( left(ename,1) ), substring(ename,2) ) 
		from emp;

数学函数

常用

时间函数

unix_timestamp() :返回从1970-1-1到现在的秒数

from_unixtime():可以把一个unix_timestamp秒数转成指定格式的日期

interval 后面的单位是year minute hour second day等

函数日期的类型可以是date也可以是datetime和timestamp

例子：查询在十分钟内发送的新闻;

select * 
	from mes 
		where DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();

查询2011-11-11 和 1990-1-1相差多少天

select DATEDIFF('2011-11-11','1990-01-01') from dual;

把unix_timestamp秒数转成日期

select from_unixtime(1618483484,'%Y-%m-%d %H:%i:%s') from dual;

加密和系统的函数

USER():查看登录到数据库的有哪些客户，以及登录的ip

password(str)：加密函数，mysql数据库的用户密码就是password函数加密

流程控制函数

判断是否是null，要使用is null，判断不为空，要使用is not

例子：查询emp表，如果comm是null，则显示0.0

select ename,if(comm is null,0.0,comm) from emp;
select ename,ifnull(comm,0.0) from emp;

如果emp表的job 是clerk则显示职员，如果是manager则显示经理，如果是salesman则显示销售，其它正常显示。

select ename, (select case 
               when job = 'clerk' then '职员' 
               when job = 'manager' then '经理' 
               when job = 'salesman' then '销售' 
               else job end) as 'job'
	from emp;

查询增强

sql中日期能直接比较

例子：如何查找在1992.1.1后入职的员工

select * from emp where hiredate > '1992-01-01';

查询表的结构

desc emp;

分页查询

基本语法：select … limit start,rows 表示从start+1行开始取，取出rows行，start从0开始计算

*第n页：select * from emp order by empno limit (n-1)rows,rows;

例子：将雇员的id按升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页

--第1页
select * from emp order by empno limit 0,3;
--第2页
select * from emp order by empno limit 3,3;
--第3页
select * from emp order by empno limit 6,3;

分组加强

例子：

• 显示每种岗位的雇员总数，平均工资。

select count(*),avg(sal),job
	from emp
	group by job;

• 显示雇员总数，以及获得补助的雇员数

思路：获得补助的雇员数，就是comm列为非null，就是count(列)，如果该值为空，不会被统计

select count(*),count(comm)
	from emp;

多子句查询

顺序：group by,having,order by,limit

select column1,column2... from table
	group by column
	having condition
	order by column
	limit start,rows;

例子：请统计各个部门（group by）的平均工资（avg），

​ 并且是大于1000的（having），

​ 并且按照平均工资从高到低排序（order by），

​ 取出前面两行记录（limit）

select deptno avg(sal) as avg_sal 
	from dept
	group by deptno
	having avg_sal > 1000
	order by avg_sal desc
	linit 0,2

多表查询

多表查询：指基于两个或两个以上的表查询。

默认情况下：当两个表查询时，规则如下：

1. 从第一张表中，取出一行和第二张表的每一行进行组合，返回结果【含有两张表的所有列】
2. 一共返回的记录数 = 第一张表行数*第二张表的行数
3. 这样多表查询默认处理返回的结果，称为笛卡尔集
4. 解决这个多表的关键就是要写出正确的过滤条件where

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

例子：

• 显示雇员名，雇员工资及所在部门名称 【笛卡尔积】

  分析：雇员名，雇员工资来自 emp表‘部门名称来自dept表。

  select ename,sal,dname 
  	from emp,dept 
  	 where emp.deptno = dept.deptno;
  

自连接

自连接：指在同一张表的连接查询（将同一张表当做两张表）

特点：

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

例子:显示公司员工名字和他的上级名字

select worker.ename as '职员名',boss.ename as '上级名'
	from emp worker,emp boss
	where worker.mgr = boss.ename;

子查询

子查询：指嵌入在其他sql语句中的select语句，也叫嵌套查询

• 单行子查询：指只返回一行数据的子查询语句
• 多行子查询：指返回多行数据的子查询，使用关键字in

例子：

• 查询和smith同一个部门的所有员工

select * 
	from emp 
	where deptno = (
        select deptno 
        	from emp 
        	where ename = 'smith');

• 如何查询和部门10的工作相同的雇员的名字，岗位，工资，部门号，但是不包含10号部门自己的雇员

1. 查询到10号部门有哪些工作

   select distinct job from emp where deptno = 10;
   

2. 把上述查询结果当做子查询使用

   select ename,job,sal,deptno 
   	from emp 
   	where job in (
           select distinct job 
           	from emp 
           	where deptno = 10
       ) and deptno <> 10
   

子查询临时表

子查询当做临时表使用

例子：查询各个类别中价格最高的商品

1. 先得到各个类别中，价格最高的商品（max+group by）当做临时表
2. 把临时表和已知表连接起来

select goods_id,temp.cat_id,goods_name,shop_price
	from (
        select cat_id,max(shop_price) as max_price
        	from ecs_goods
        	group by cat_id
    ) temp, ecs_goods
    where temp.cat_id = ecs_goods.cat_id
    and temp.max_price = ecs_goods.shop_price

all和any

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

例子：显示工资比 部门30的所有员工的工资 高的员工的姓名，工资和部门号

select ename,sal,deptno
	from emp
	where sal > all(
    	select sal 
        	from emp 
        	where deptno = 30	
        )

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

例子：显示工资比30号部门中其中一个员工高的员工的姓名，工资和部门号

select ename,sal,deptno
	from emp
	where sal > any(
    	select sal 
        	from emp 
        	where deptno = 30	
        )

多列子查询

多列子查询：指查询返回多列数据的子查询语句

例子：查询与smith部门，岗位相同的雇员（不包含smith）

select * 
	from emp
	where (deptno,job) = (
    	select deptno,job
        	from emp
        	where ename = 'smith'
    )and ename != 'smith'

表复制

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

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

例子：把emp表的数据插入到my_table01表中,然后将my_table01表自我复制(呈指数增加)

insert into my_table01(id,'name',sql,job,deptno)
	select empno,ename,sal,job,deptno from emp;
insert into my_table01
	select * from my_table01;

例子：如何删除一张表重复的记录

1. 先创建一个my_table02表

   create table my_table02 like emp; --将emp表的结构复制给my_table02这张表
   

2. 让这张表有重复记录(反复执行下列语句)

   insert into my_table02
   	select * from emp;
   

3. 考虑去重

   思路

   1. 先创建一张临时表temp，结构与my_table02一致
   2. 把my_table02的记录通过distinct关键字处理后存到temp表
   3. 清除my_table02的所有记录
   4. 把temp表的记录插入到my_table02中
   5. drop table temp；

   create table temp like my_table02;
   
   insert into temp
   	select distinct * from my_table02;
   	
   delete from my_table02;
   
   insert into my_table02
   	select * from temp;
   	
   drop table temp;
   

合并查询

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

1. union all

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

2. union

   该操作符用于取得两个结果集的并集，当使用该操作符时，会取消重复行

外连接

外连接：

1. 左外连接（左侧表完全显示） select … from 表1 left join 表2 on 条件 ；此时表1为左表，表2为右表
2. 右外连接（右侧表完全显示）select … from 表1 right join 表2 on 条件 ；此时表1为左表，表2为右表

例子：使用左外连接，显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩为null

select 'name',stu.id,grade
	from stu left join exam
	on stu.id = grade.id;

使用右外连接，显示所有成绩，如果没有名字匹配，显示空

select 'name',stu.id,grade
	from stu right join exam
	on stu.id = grade.id;

约束

约束：用来确保数据库的数据满足特定的商业规则。

在mysql中，约束包括：not null,unique,primary key,foreign key和check五种

主键 primary key

用于唯一的标识表行的数据，当定义外键约束后，该列唯一且不能为null

在创建表时：字段名 字段类型 primary key

注意：

• primary key不能重复且不为null

• 一张表最多有一个主键，但可以是复合主键

  create table t18
  	(
          id int,
          'name' varchar(32),
          email varchar(32),
          primary key(id,'name') -- 这里就是复合主键
      )
  

  复合主键(id,‘name’)只有当两者都相同时才插入失败

主键指定方式有两种：

1. 直接在字段后面指定：字段名 primary key
2. 在表定义最后写 primary key(列名)

唯一 unique

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

字段名 字段类型 unique

注意：

• 如果没指名not null，则unique字段可以多个null
• 一张表可以有多个unique字段

外键 foreign key

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

froeign key(本表字段) references 主表名(主键名或unique字段名)

注意：

• 外键指向的表的字段，必须是primary key或者是unique
• 表的类型必须是innodb，这样表才支持外键
• 外键字段的类型必须要和主键字段的类型一致（长度可以不同）
• 外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为null】
• 一旦建立主外键关系，数据就不能轻易删除

check

用于强制行数据必须满足的条件

列名 类型 check （check条件）

注意：Oracle 和 sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

例子：

create table t23(
	id int primary key,
    'name' varchar(32),
    sex varchar(6) check(sex in('man','woman')),
    sal double check(sal > 1000 and sal <2000)
)

自增长auto_increment

字段名 整形 primary key auto_increment

注意：

1. 字段一般是整形，小数也可以，但是很少用

2. 一般和primary key一起使用

3. 自增长也可以单独使用，但是需要配合unique

4. 自增长默认从1开始，也可以通过如下命令修改：

   alter table 表名 auto_increment = xxx;
   

5. 添加数据时，自增长列如果指定了值，则以该值为准（不建议这样做）

索引index

使用索引，能在不加内存，不改程序，不调sql的基础上，极大提高查询速度

在哪个表哪一列创建索引：

create index 索引名 on 表名(列名)

创建索引后，表.ibd变大（因为创建索引也需要空间，ibd文件存放数据库数据）（空间换时间）

创建索引后，只对创建索引的列有效

索引原理（还需要深入了解）

没有索引时，select语句会进行全表扫描，查询速度慢

mysql底层实现是B+树

代价：

1. 磁盘占用
2. 对dml（delete update insert）语句有影响，因为需要对索引进行维护

索引类型

1. 主键索引，主键自动为主索引（类型为primary key）

2. 唯一索引（unique）

3. 普通索引（index）

4. 全文索引（fulltext）【使用于myisam引擎】

   一般开发不适用mysql自带的全文索引，而是考虑使用：全文搜索Solr 和 ElasticSearch(ES)框架

索引使用

查询索引(4种方式)

-- 查询表中是否有索引
show index from t25;
show indexes from t25;
show keys from t25;
desc t25;

添加索引

-- 添加唯一索引
create unique index id_index on t25(id);
-- 添加普通索引(两种方式)
create index id_index on t25(id);
alter table t25 add index id_index (id);
-- 添加主键索引 
-- 一种是创建表时使用primary key
-- 另一种如下
alter table t25 add primary key(id);

删除索引

-- 删除普通索引
drop index id_index on t25;
-- 删除主键索引
alter table t25 drop primary key;

修改索引：先删除索引，再添加新的索引

创建索引规则

1. 较频繁的作为查询条件字段应该创建索引

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

   select * from emp where sex = '男';
   

3. 更新非常频繁的字段不适合创建索引

4. 不会出现在where子句的字段不该创建索引

事务transaction

什么是事务？

事务用于保证数据的一致性，它由一组相关的dml（数据库操作语言）语句构成，该组dml语句要么全部成功，要么全部失败。

如：转账就是要用事务来处理，用以保证数据的一致性

将多个dml语句（update,delete,insert）当做一个整体，要么全部成功，要么全部失败 ——>使用事务来解决

执行事务操作时，mysql会在表上加锁，防止其他用户修改表的数据，这对用户来讲非常重要。

事务操作

演示：

1. 创建一张测试表

   create table t27(
       id int,
       'name' varchar(32)
   );
   

2. 开始事务

   start transaction;
   

3. 设置保存点

   savepoint a;
   

4. 执行dml操作

   insert into t27 values(1,'tom');
   select * from t27;
   

5. 设置保存点

   savepoint b;
   

6. 执行dml操作

   insert into t27 values(2,'jack');
   

7. 回滚到b

   rollback to b;
   

8. 回滚到事务开始

   rollback;
   

9. 提交事务(提交之后，不能再回滚)

   commit;
   

   注意：

   • 回退到a点，则不能再回退到b点，因为会把b保存点删除
   • 使用commit语句之后，会确认事务的变化，结束事务，自动删除该事务所定义的所有保存点，释放锁，数据生效。
   • 使用commit语句之后，其他会话将可以查看到该事务变化后的新数据（所有的数据正式生效）

事务细节

1. 如果不开启事务，默认情况下，dml语句是自动提交的，不能回滚
2. 如果开始一个事务，没有创建保存点，执行rollback，默认是回退到事务开始的状态
3. 可以在事务还未提交时，创建多个保存点
4. 可以在事务还未提交时，选择回退到哪个保存点
5. mysql事务机制需要innodb存储引擎才可以使用，myisam用不了。
6. 开始一个事务 start transaction；set autocommit = off；

事务隔离级别

定义：多个连接开启各自事务来 操作数据库中的数据库时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

如果不使用隔离级别，可能会导致如下问题：

• 脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，会产生脏读
• 不可重复读（nonrepeatable read）：同一查询在同一事务多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
• 幻读（phantom read）：同一查询在同一事务多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

举例说明：

1. 开两个mysql的控制台

   1. 查看当前隔离级别（默认是可重复读）isolation(隔离)

   select @@tx_isolation;
   -- mysql8.0上诉指令不可用，使用如下指令
   select @@transaction_isolation;
   

2. 把其中一个控制台隔离级别设置 read uncommitted

   set session transaction isolation level read uncommitted
   

3. 创建表

   create table 'account'(
       id int,
       'name' varchar(32),
       money int
   );
   

4. 开启事务后，一个控制台插入数据但是没有提交，而另一个控制台却可以看到该数据，这就是脏读

5. 开启事务后，一个控制台修改数据并提交，而另一个控制台还没提交事务却可以看到该数据，这就是不可重复读

6. 开启事务后，一个控制台添加数据并提交，而另一个控制台还没提交事务却可以看到该数据，这就是幻读

注意：

• 隔离级别跟事务有关，所以必须先开启事务。
• 使用serializable级别时，如果已经有事务在操作这张表，另外一个事务操作时就会卡住，因为加了锁；效率较低，不建议使用

操作

1. 查看当前对话隔离级别

   select @@tx_isolation;
   -- mysql8.0上诉指令不可用，使用如下指令
   select @@transaction_isolation;
   

2. 查看当前系统隔离级别

   select @@global.tx_isolation;
   

3. 设置当前会话隔离级别

   set session transaction isolation level read uncommitted
   

4. 设置当前系统隔离级别

   set global transaction isolation level read uncommitted 
   

5. 默认隔离级别是可重复读，要修改默认的隔离级别可以去mysql文件里的my.ini修改

   #设置默认隔离级别
   transaction-isolation = read uncommitted 
   

事务的acid特性

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

表类型和存储引擎

1. mysql表类型由存储引擎（Storage Engines）决定，主要包括MYISAM，innoDB，Memory等
2. MySQL数据表主要支持六大类型，分别是：CSV，Memory，ARCHIVE，MRG_MYISAM，MYISAM，InnoDB
3. 这六种又分为两类：一类是“事务安全型”（transaction-safe），比如InnoDB；其余都属于第二类，称为“非事务安全型”

特点：

• InnoDB支持事务，行级锁定，外键

• MRG_MYISAM收集相同的MyISAM表

• Memory基于哈希的，数据存储在内存中（重启mysql服务，数据会丢失，但是表结构还在），对临时表有用，执行速度快（没有io读写），默认支持索引（hash表）

• MYISAM 批量添加速度快，不支持外键和事务，支持表级锁

操作

1. 查看所有的引擎

   show engines;
   

2. 修改存储引擎

   alter table t29 engine = InnoDB;
   

如何选择存储引擎

1. 如果应用不需要事务，处理的只是简单的CRUD操作，MyISAM是不二选择，速度快

2. 如果需要支持事务，使用InnoDB

3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快。

   但由于是内存存储引擎，所做的任何操作都会在服务器重启后消失（经典用法：用户的在线状态）

视图

视图：是一个虚拟表，其内容由查询来定义。同真实表一样，视图包含列，其数据来自真实表（基表）

对视图的总结：

1. 视图是根据基表（可以是多个基表）来创建的，视图是虚拟的表

2. 视图也有列，数据来自基表（映射）

3. 通过视图可以修改基表数据

4. 基表的改变也会影响到视图的数据

5. 视图文件格式是.frm即结构文件

6. 视图中可以再使用视图，比如从视图中挑选几列构造新的视图

   create view view2 
   		as
   		select no,'name' from view1;
   

基本使用

1. 创建视图

   create view 视图名 as select语句
   

2. 修改视图

   alter view 视图名 as select语句
   

3. 查看创建视图时使用的指令

   show create view 视图名
   

4. 删除视图

   drop view 视图名1,视图名2
   

5. 查看视图

   desc 视图名;
   

视图最佳实践

MySQL管理

用户管理

原因：当做项目开发时，根据不同的开发人员，赋予他相应的mysql操作权限，

​ 所以mysql管理人员（root）根据需要创建不同用户，赋予相应的权利，供开发人员使用

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

重要字段说明：

1. host：允许登录的”位置“，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
2. user：用户名
3. authentication_string：密码，通过mysql的password()函数加密之后的密码

注意：不同的数据库用户，登录到DBMS后，根据相应权限，操作的数据库和数据对象（表，视图，触发器）都不同

操作

• 创建用户

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

  说明：创建用户时，同时指明密码

• 删除用户

  drop user '用户名'@'允许登录的位置'
  

• 修改密码

  -- 修改自己的密码为abc
  set password = password('abc');
  -- 修改其他人密码，需要权限
  set password for 'root'@'localhost' = password('123');
  

权限管理

• 给用户授权

  grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码']
  

  说明：

  1. 权限列表，多个权限用逗号分开

     grant select on ...
     grant select,delete,create on ...
     grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限
     

  2. 特别说明

     *.* ：代表本系统中的所有数据库的所有对象（表，视图，存储过程）

     库.* ：表示某个数据库中的所有数据对象（表，视图，存储过程等）

     存储过程（Stored Procedure是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

     个人认为存储过程类似于java函数。

  3. identified by可以省略，也可以写出

     1. 如果该用户存在，就是修改该用户的密码
     2. 如果该用户不存在，就是创建该用户

• 回收用户权限

  revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';
  

• 权限生效指令

  -- 如果权限没有生效，使用如下指令
  flush privileges;
  

管理细节

1. 创建用户时，如果不指定Host，则为%，%表示所有IP都有连接权限

   create user '用户名';
   

2. 也可以这样指定

   create user '用户名'@'192.186.1.%'
   

   这表示该用户在192.186.1.*的ip都可以登录mysql

3. 删除用户时，如果host不是%，需明确指定’用户名’@‘host值’

2021年7月12日22:46:42