MySQL -basic
Basic MySQL
Published: 2020-05-01

The article contains the basic knowledge of MySQL…

安装MySQL

参考：https://blog.csdn.net/xhmico/article/details/125197747（Linux下安装MySQL（详细教程））

数据类型

  1. Char（定长）

  2. varchar 在列内容前有1-2个字节来标志该列的内容长度。（不定长）

  3. timestamp 4个字节 ； time 3个字节； date 3个字节； datetime 8个字节； year 1个字节

  4. blob用于存放二进制数据，如图像、音频等二进制信息。意义：二进制，0-255都可能出现

  5. blob在于防止因为字符集的问题，导致信息丢失。比如：一张图片有0xFF字节，这个在ascii字符集认为为非法，在入库的时候，就被过滤了。

  6. longtext存放超大量文本，longblob存放超大量二进制数据，mediumtext存放中等大小文本，mediumblob存放中等大小二进制数据。

  7. text 用于存放大量文本（比如10000字）。text不用加默认值，加了没用。

  8. float是浮点数有损精度，decimal是定点数，整数部分和小数部分分开存储，因此比float要精确。

整形

使用无符号的话需要加unsigned

上图中的写法：INT(7)表示如果长度超出7则不做改动，如果长度不足7则前面用0填充，但是要实现用0填充的话就必须在后面加上ZEROFILL，并且一旦加上ZEROFILL，我们的int会自动变成unsigned类型。注意数值的范围由int来决定不由7决定，7只是表示了数值的最大宽度，当然，如果不设置的话它也会有默认值。

小数

dec和decimal是一样的。并且定点数类型不仅范围跟double一样，精度还要比double高，因此当需要存储高精度数据的时候推荐用定点数类型。

注意，这里的decimal(M,D)中的D表示小数后几位，M表示整数位加小数位总共的位数，如果D和M都确定，那么整数位位数就可以知道了，比方说float(5,2)，那么小数位两位，整数位3位，这个时候如果插入1111.1会报错，因为这个数的整数位位数有4位。

另外decimal的M、D要比float和double的严格得多，如果我们插入的数据小数位数超过D，double和float不会报错而会四舍五入，但是decimal会报错，插入失败。

最后，在不加M、D的情况下，只有decimal有默认值：M=10、D=0，也就是说我们只能插入宽度为10，小数位位数为0的整数，而double、float则随便插入，且精度会随着插入的数变化（1.22则精确到小数点后两位，1.323则精确到小数点后三位）。

字符型

注意：char是给多少位就用多少位，不管实际上用到的位数多少，而varchar是动态使用位数的，也就是用到多少位就给多少位，但是char效率高，varchar效率低。所以像性别之类的固定长度的就用char，像姓名之类变长的就用varchar

Enum

存季节、性别的时候可以用enum。不区分大小写。

Set

Set跟enum的区别就是set可以插入多个。不区分大小写。

日期型

timestamp更能反映真实时间

Set time_zone=’+9:00’意思是把时区设置为东9区，这个时候TIMESTAMP会发生变化但是DATETIME不会变。

其中t1是datetime，t2是timestamp

库操作

建库

库的修改（主要是字符集的修改）

以前能用rename database 库名 to 新库名;现在不行了。要改的话直接去文件夹里面改文件名。

修改库的字符集：

删库（安全方式）

表操作

建表（安全方式）

create table if not exists class_name(

id int primary key not null auto_increment,

sname varchar(10) not null default ‘’,

salary decimal(6,2) not null default 0.00,

fanbu smallint not null default 0

)engine myisam charset utf8;

注意：在建表最后一行engine myisam charset utf8后面还可以加auto_increment=1表示自增列从1开始计数，如果是auto_increment=100表示自增列从100开始计数。

复制表

仅复制表的结构

create table 表 like 表;

结构跟数据都复制过来

create table 表 select * from 表；

仅复制部分数据

Create table 表 select … from 表 where …;

仅复制部分字段不复制数据

跨数据库建表

修改表

修改列属性：

  1. 改列的属性值：alter table 表 modify column（column可省略） 列名 char(4) not null default ‘'（列名 新类型 新约束）;

  2. 改列名：alter table 表 change column（column可以省略） 旧列名 新列名 新类型 新参数;

增加新列：

  1. alter table 表 add column **** after **;

  2. alter table 表 add column **** first;

  3. alter table 表 add column 名字 类型 not null default;

删除列：

alter table 表 drop column（column可省略）列名;

修改表名：

Alter table 表 rename to 新表名;

修改表的字符集：

Alter table 表 convert to character set ‘utf8’;

删除表（安全方式）

临时表

CREATE TEMPORARY TABLE tbl_name...

当断开数据库链接的时候，临时表就会自动销毁，当然也可以使用drop table命令删除临时表

也可以直接将查询结果导入临时表：

CREATE TEMPORARY TABLE tbl_name SELECT * FROM source_tbl_name

另外，MySQL也允许在内存中直接创建临时表，因为在内存中速度会很快，语法如下：

CREATE TEMPORARY TABLE tmp_table (
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL
) TYPE = HEAP;

myisam、innodb、memory表文件比较：

image-20210410202053333

由此可见innodb开销相对较大，不适合作为临时表

增删改查

增

方法一

  1. insert into 表 () values ();

  2. insert into 表1 select *** from 表2 ; 表1插入表2相应数据

  3. insert into 表 values (NULL); 表示插入空值

  4. insert into class02 select good_name,good_num from class03;

方法二

法一VS法二

default关键字

举例：

create table emp(
  empid int(10) not null auto_increment,
  ename varchar(100) not null,
  primary key (empid)
);
insert into emp values(default, "qyf");
insert into emp values(default, "nig");

删

Delete

要删哪张表就在delete之后加上哪张表的别名

Delete和limit联用：

Delete from 表 where 条件（where 条件可以按需填写） limit 删除条数；

删整行：

delete from 表 where 条件;

Truncate

truncate相当于删表再重建一张同样结构的表，操作后得到一张全新表。相当于把旧表扔了重画一张。

而delete是从删除所有的层面来操作的。相当于用橡皮把表的数据库擦掉。

Delete VS truncate

注意：truncate之后再插入数据的话id从1开始

注意：truncate之后没有显示几行被删

改

修改单表记录：

  1. update 表 set 属性=CASE 属性 when "" then "" else "" end;

  2. update 表 set 属性="" where “";

  3. update 表 set 属性 = concat(‘HTC’,substring(goods_name,选取第几位开始截取,选取的length)) where goods_name like ‘诺基亚%';

    注释：

第三句中concat是连接字符串用的

修改多表记录：

查

select语句后面跟for update是读取锁的方式之一，解决丢失修改不能靠事务，要加必要的锁。

示例：

  1. select * from 表名 where 列名 like “%x%"; —->从表中查找列名中带一个“x”的列

  2. select concat(列名,""),concat(列名,"") from "”

  3. select A.,B. from A,B;

  4. select goods_name,goods_num,goods_id from 表 where goods_id in (4,11); #goods_id 在{4,11}这个集合里都满足。

  5. select goods_name,goods_num,goods_id from 表 where goods_id between 100 and 500 #100<=goods_id<=500

  6. select goods_name from 表 where goods_id not in (2,5); #选取goods_id不是2或5的所有goods_name.

  7. select goods_name from 表 where goods_id !=2 and goods_id!=5.

  8. select goods_name from 表 where goods_id>=100 and goods_id<=400 or goods_id>=1000 and goods_id<=2000.

  9. select goods_name from 表 where goods_id>=100 and (goods_num<50 or goods_num>240) and goods_id>900.

  10. select goods_name from 表 where goods_name like ‘诺基亚%'; %可以匹配任意字符

  11. select goods_name from 表 where goods_name like ‘诺基亚N__'; _ 可以配一个字符

  12. select goods_name,goods_id,market_price-shop_price as price from goods where 1;

  13. select goods_id,sum(goods_number) from goods group by goods_id;

  14. select goods_id,(market_num-goods_num) as nums from goods where 1 having nums>200; having关键字的使用

  15. select sname,if(gender = 0,‘优先’,‘等待’) as vip from 表;

可以select 列 as 别名，也可以from 表 as 别名;

Utf8等字符集设置

1、

set charactor_set_client = gbk;

告诉连接器，使用utf8

set charactor_set_connection = utf8;

再告诉，如果返回值，返回gbk结果

set charactor_set_results = gbk;

2、

set charactor_set_client = gbk;

set charactor_set_connection = gbk;

set charactor_set_results = gbk; 可以简写成 set names gbk;

单行函数

字符函数

CONCAT拼接

LENGTH

Upper、lower

Left、right

从左边或右边开始截取若干个字符

Substr、substring

Instr

Trim

Lpad

Rpad

Replace

数学函数

Round

Ceil、floor

Truncate

Mod

Rand

获取随机数，返回0-1之间的小数

日期函数

Now

Curdate、curtime

Year、month、monthname

Datediff

Str_to_date、date_format

日期格式符号

其他函数

Version()、DataBase()、user()

Password()、md5()

返回该字符的密码形式

流程控制函数

If（类似三目运算符）、if else

Case(switch)

Case(if else)

分组函数

sum、count、max、min、avg

这里的max、min按照字符排序来选择，count按照非空来计数

经过上面的几个操作我们可以断定这些函数都忽略了null值

分组函数与distinct联用

Count函数详解

myisam中有一个计数器，当调用count时直接返回计数器的值，所以效率非常高

和分组函数一同查询的字段有限制

这里的employee_id显然是无效数据

分组查询

注意点

Group by

·

Having

这里的筛选是分组后的筛选，刚才那些where字段的筛选是分组前的筛选

按表达式或函数分组

按多个字段分组

添加排序

Sql92

等值连接

注意：为表起别名的时候相当于生成了一个视图，因此用这个表的时候应该用别名去引用它，而不应该用原表名。

增加筛选

添加分组

排序

三表连接

总结

非等值连接

自连接

Sql99（内连接、外连接、交叉连接）

内连接

等值

注意：后面的表连接前面的表的时候必须要有连接条件，不然无法连接。Inner可以省略。

非等值连接

自连接

外连接

左外、右外连接

全外连接

在左外或者右外的基础上把从表中有但是主表没有的那个字段的内容也查询出来

全外连接-内连接

A表独有+B表独有即可

交叉连接

子查询

注意：经过实验发现，mysql 8.0版本子查询使用条件过滤的时候不能使用AS别名字段，只能使用原字段，否则会识别不出来

Where或having后面

标量子查询（一行一列）

列子查询（多行子查询）

或者

扩展

相当于

行子查询（结果集一行多列或多行多列）

原先的做法：

行子查询的做法：

Select后面（仅支持标量子查询）

From后面（子查询结果的表必须起别名）

Exists(1表示子查询存在，0表示子查询不存在)

这里的select * 也可以替换为select 1

注意：用 exists的一定能用in来代替，比方说上面这个案例用in来写就是：

limit分页查询

如果offset从0开始则可省略

联合查询

使用union all之后就算两个查询有重复的东西，最终的结果集也会将所有信息显示出来。

QUERY1 union QUERY2 order by **; order by 是针对最终结果集排的序，但是如果语句中有limit等让内部排序有意义(影响最终结果集)的话，内部order by就会发挥作用，如：(select goods_name from goods where goods_id<5 order by goods_num desc limit 3) union (select cat_name from cats where cat_id<5 order by cat_num asc limit 8);

注意：union会去重，union all不会去重。当我们确定两表连接不会有重复的数据之后优先选择union all，它的效率更高。

约束（列级约束和表级约束）

这里的两个null的字段设置了唯一约束，唯一约束可以为空但是也不能有两个空，所以这里这样写会报错。

Check：比方说我们需要id必须大于0，那么可以在建表时加约束：check(id>0)或者在之后修改表约束的时候add check(id>0)或者drop check check_name

其中constraint 约束名可以省略

可以通过desc 表名、show index from 表名这两个函数来查看表结构和表索引约束。

通用的写法就是把unique、primary key写成列约束，外键写成表约束

组合约束

主键和唯一键都可以组合约束，但是不推荐。语法：

这个时候只有id和stuinfo两个值都重复的时候才会报错，如果只是id重了或者只是stuinfo重了或者两个都没重那是可以正常插入的。

唯一键和主键类似

外键

级联删除

在添加外键约束的语句最后写上ON DELETE CASCADE

级联置空

在添加外键约束的语句最后写上ON DELETE SET NULL

修改表时添加约束

同理constraint 外键名可省略

修改表时删除约束

这里也可以不写NULL

标识列（自增列）

设置自增起始位置和步长（MySQL中不支持设置起始位置）

也可以set @@auto_increment_increment=3；

MySQL中如果要修改自增的起始位置，只能通过插入的第一条数据时将该数据id设置为我们想要的起始id来实现。

修改表时设置标识列

修改表时删除标识列

事务

脏读：不符合一致性

不可重复读：不符合隔离性

幻读：不符合隔离性

事物不能撤销，如果出错，则做一次“补偿性事物”

查看数据库引擎：show engines

这里的一致性可以这么理解：转账之后两个用户的财产总额不变

事务的创建

修改事务级别

将事务修改为某一等级：

set session/global transaction isolation level read uncommitted

并发情况下更新丢失

此外，事务还有并发情况下更新丢失的问题

解决方法：一个人在完成并提交事务之前，另一个人不能访问同一个文件。

savepoint（设置保存点，和rollback to联用）

Delete和truncate在事务使用时的区别

truncate无法回滚

而且delete之所以可以回滚是因为他在删除的时候每删一行在日志中写一行，而truncate没有这样的操作。

所以有个面试题就是在delete数据的时候提示磁盘空间不足，就是这个原因。

视图

创建视图

一般视图名取为view_name

修改视图

删除视图

查看视图

或者show create view myv3\G；可以格式化显示

更新视图

注意：这里视图内的数据改了，原始表的数据也改了。视图很少拿来更新，有很多更新操作是不被允许的。

上面这个例子由于包含group by，因此无法更新

常量视图无法更新

select包含子查询，无法更新

涉及join，无法更新

From一个无法更新的视图，无法更新

Where的子查询中用到了from子句中的表，无法更新

变量

系统变量

全局变量（需要super权限才能设置）

全局变量赋值还可以使用语句：set global autocommit = 0;

注意重启之后全局变量就恢复默认值了，如果需要每次重启之后全局变量不会变回去，那么需要修改配置文件，不过一般情况我们不会去改它

会话变量

自定义变量

用户变量

局部变量

对比用户变量和局部变量

必须在begin end中

存储过程

创建语法

其中in可省略，但是为了可读性一般不省略。

第一句也可以写成CREATE OR REPLACE PROCEDURE …

调用语法

删除存储过程

注意：一次删除多个存储过程的操作是不支持的

查看存储过程

注意：DESC不能用于查看存储过程

案例

使用存储过程批量插入数据案例参考：https://blog.csdn.net/dd2016124/article/details/125134556（MySQL使用存储过程批量插入数据）

使用存储过程和定时任务批量复制表数据到另一个表案例参考：https://blog.csdn.net/holmes369/article/details/105096977（Mysql使用存储过程和定时任务批量复制表数据到另一个表）

函数

创建函数

调用函数

查看函数

删除函数

流程控制结构

分支结构

If函数

Case结构

Case作为表达式，嵌套在其他语句中使用，可以放在任何地方，写法如下：

Case作为独立语句去使用，只能放在begin end中，写法如下：

If结构

循环结构

While

Loop

如果不想死循环，那么需要搭配leave

Repeat

案例

触发器

Trigger：当一件事情发生了，会引发另外的一件事情发生，我们称之为触发器。

在MySQL里触发器的意思就是，当我们对一张表操作的时候，会触发对其他表相关的一些操作。

MySQL是关系型的数据库，表与表之间是有联系的，当我们修改一张表的时候，也希望另一张表也做相应的修改。

比如A是学员表，B是成绩表，如果A表中一个学院的名字改了，那么B表中的学员的名字也要做相应的修改。

语法：

DELIMITER //
CREATE TRIGGER 名字 时间(AFTER|BEFORE) 动作(INSERT|UPDATE|DELETE) ON 表1
FOR EACH ROW
BEGIN
sql语句 表2;
END //
DELIMITER ;

解释：某个动作(insert|update|delete)，这里我们称该动作叫d1，在表1执行的时候，某些sql语句会在表2执行，执行时间(after|before)为d1的前面或者后面

那FOR EACH ROW是什么意思呢？

意思是BEGIN END符中间的sql语句（按上图来讲就是“sql语句 表2;”这一块），一行一行地执行

New和Old

New：指的是表1修改后的那行

Old：指的是表1修改之前那行的内容

例子：

DROP TRIGGER IF EXISTS x1;
DELIMITER //
CREATE TRIGGER x1 AFTER UPDATE ON 表1
FOR EACH ROW
BEGIN
UPDATE 表2 SET money=400 WHERE id=old.id;
END //
DELIMITER ;

这个时候修改表1的id为1的那一行，表2的id为1的那一行也会更改

但是如果把上面的old换成new，假设表1存在某条记录：

id name
2 test

我们执行语句：

update 表1 set id=30 where name="test";

这个时候表1确实会被改动，因为确实存在这样一条记录。

此时old.id=2，而new.id=30

假设表2又没有id为30的，那这个时候表2就不会有数据被改动

例子2：

表1：

id score
1 99
2 59
3 63

表2：

id state
1 pass
2 fail
3 pass 
DROP TRIGGER IF EXISTS x1;
DELIMITER //
CREATE TRIGGER x1 AFTER UPDATE ON 表1
FOR EACH ROW
BEGIN
IF new.score >= 60 THEN
UPDATE 表2 SET state="pass" WHERE id=new.id;
ELSEIF new.score < 60 then
UPDATE 表2 SET state="fail" WHERE id=new.id;
END IF;
END //
DELIMITER ;

上面的new.id也可以都换成old.id

这个时候如果修改表1的score，表2的state也会进行相应的修改

例子3：

DROP TRIGGER IF EXISTS x1;
DELIMITER //
CREATE TRIGGER x1 BEFORE INSERT ON 表1
FOR EACH ROW
BEGIN
INSERT INTO 表2 VALUES("xx");
END //
DELIMITER ;

由于是before

因此先执行表2的插入操作，再执行表1的insert动作

在触发器中定义变量：

DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON users 
FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40) character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;#后面发现中文字符编码出现乱码，这里设置字符集
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2);     #函数CONCAT可以将字符串连接
INSERT INTO logs(log) values(s1);
END $
DELIMITER ;

查看触发器

SHOW TRIGGERS;

如果SHOW TRIGGERS 语句无法查看指定的触发器，我们还可以在triggers表中查看触发器信息：

SELECT * FROM information_schema.triggers;

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME="xxx";

查看触发器创建语句

SHOW CREATE TRIGGER "xxx";

删除触发器

DROP TRIGGER IF EXISTS "trigger_name"

限制和注意事项

触发器会有以下两种限制：

  • 触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。

  • 不能再触发器中使用以显示或隐式方式开始或结束事务的语句，如START TRANS-ACTION,COMMIT或ROLLBACK。

注意事项：

  • MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作，如果对事务表进行的操作，如果出现错误，那么将会被回滚，如果是对非事务表进行操作，那么就无法回滚了，数据可能会出错。
  • MySQL的触发器中不能对同一个表进行增删改操作，否则会出错。

总结

触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过得的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

数据库触发器有以下的作用：

  • 安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

# 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。

# 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

  • 审计。可以跟踪用户对数据库的操作。

# 审计用户操作数据库的语句。

# 把用户对数据库的更新写入审计表。

  • 实现复杂的数据完整性规则

# 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。

# 提供可变的缺省值。

  • 实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

# 在修改或删除时级联修改或删除其它表中的与之匹配的行。

# 在修改或删除时把其它表中的与之匹配的行设成NULL值。

# 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

# 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。

  • 同步实时地复制表中的数据。

  • 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

事件

事件就是定时任务，是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

优点：1、一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能；2、可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了； 缺点：1、定时触发，不可以调用；

参考：https://blog.csdn.net/weixin_57176230/article/details/124905271（MySQL 事件）

DEFINER与INVOKER（与存储过程、函数、事件、触发器、视图相关）

mysql创建view、trigger、function、procedure、event时都会定义一个Definer

SQL SECURITY 有两个选项，一个为DEFINER，一个为INVOKER；

SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行。DEFINER 表示按定义者拥有的权限来执行；INVOKER 表示用调用者的权限来执行。默认情况下，系统指定为DEFINER；

参考：https://blog.csdn.net/weixin_29266749/article/details/113274335（mysql definer/invoker是什么？MySQL定义数据库对象之指定definer/invoker）、https://blog.csdn.net/weixin_39757212/article/details/113137727（mysql 存储过程中definer和invoker详解）

mysql游标

用于循环集合

CURSOR 游标

FETCH xxx into xxx

游标每fetch一次指针向下走一格，直到游标指向数据为空为止：

mysql执行一个字符串

PREPARE预编译之后execute执行

mysql中in的注意事项以及组合in的使用方法

sql语句in中的内容的个数最好限制在200个以下，不然会出现效率问题

SELECT id
FROM t
WHERE id
IN (就是这里的内容的个数需要限制在200个以下);

mysql组合in的使用场景：

SELECT a,b 
FROM test 
WHERE (a = 1 AND b = 2) 
OR (a = 3 AND b = 4) 
OR (a = 5 AND b = 6) 
OR (a = 9 AND b = 10);

使用组合in优化后：

SELECT a,b 
FROM test 
WHERE (a,b) 
IN ((1,2),(3,4),(5,6),(9,10));

当然，除了和SELECT配合使用，还能和DELETE等配合使用，举一个和DELETE联用的例子：

DELETE FROM test
WHERE (a,b)
IN (
    SELECT a,b
    FROM ...
);

MySQL面试题

两种方法一种可以写constraint，一种不可以写

其他

mysql 8.0修改密码

$ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
$ flush privileges;

用户授权

一、用户授权

mysql8.0之前写法：

mysql> grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;

mysql8.0之后写法：

mysql> CREATE USER 'qyf'@'%' IDENTIFIED BY '你的密码';
mysql> grant all privileges on *.* to 'qyf'@'%';

添加远程访问权限：

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';

添加权限（和已有权限合并，不会覆盖已有权限）

GRANT Insert ON `your database`.`your table` TO `user`@`host`;

删除权限

REVOKE Delete ON `your database`.`your table` FROM `user`@`host`;

二、刷新权限
对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。

mysql> flush privileges;

表文件结构（innodb、myisam和memory的）

image-20210410201523552

Innodb的独立表空间和共享表空间

共享表空间优点

表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制（很多文档描述有点问题）。

数据和文件放在一起方便管理。

共享表空间缺点

所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，对于经常删除操作的这类应用最不适合用共享表空间。

共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了（可以理解为oracle的表空间10G，但是才使用10M，但是操作系统显示mysql的表空间为10G），进行数据库的冷备很慢；

独立表空间优点

每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。

空间可以回收（除drop table操作处，表空不能自已回收）

Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

独立表空间缺点

单表增加过大，当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法；

共享表空间和独立表空间之间的互换

SHOW VARIABLES LIKE "innodb_file_per_table";
-- ON表示独立表空间管理，OFF表示共享表空间管理 --

查看当前库

mysql> select database();

执行计划（EXPLAIN）

类似于DESC，在语句前添加关键字EXPLAIN后显示一些信息。https://www.cnblogs.com/yinjw/p/11864477.html

配置文件

Select用法

Set用法

设置系统的变量

设置隔离级别

Limit以及offset用法

limit offsite，N； （offsite（起始游标）可以不写，相当于 limit 0，N（长度））

语句1：select * from student limit 9,4

语句2：slect * from student limit 4 offset 9

//语句1和2均返回表student的第10、11、12、13行

//语句2中的4表示返回4行，9表示从表的第十行开始

Distinct用法

  1. select distinct(goods_name) ,goods_id from goods; 注意：distinct只能放在查询列名的最前面，但是与其他函数使用的时候，没有前后位置限制。

  2. select distinct name,age from user;

Between用法

select goods_name from goods where goods_price not between 50 and 4000;

“+” 的作用

\G的作用

按列显示，使得显示出来的信息更加人性化

Null的性质

任何东西跟null拼接都为null；字符串‘null’跟null没有关系。坑点：我们用concate函数的时候如果拼接内容中有一方为null，则结果为null，因此可能返回的不是我们想要的拼接的结果。

IFNULL判空（后面那个“0”表示如果是空的话就显示0）

条件运算符

模糊查询（包括安全等于<=>、is null、in、escape、转义的运用）（注意= NULL是不行的，一定要IS NULL）

下面这个‘$’可以是任意字符，只要后面excape里面表明清楚就可以了，他的意思是表示该字符后面的那个字符是转义的意思

注意：在可能为0的字段前面加IFNULL

多字段排序

Show variables like ‘%char%’

@@hostname

系统变量，显示主机名，应用场景：使用mycat时区分到底是哪个机器的库，因为linux跟windows的主机名必定不一样，因此可以使用这种方法来进行区分

Select @@transaction_isolation from dual（from dual可以省略）

Dual表示虚表，表示这个表根本不存在，我主要是想找前面那个东西。

设置时区

Set time_zone=’+9:00’意思是把时区设置为东9区，这个时候TIMESTAMP会发生变化但是DATETIME不会变。

其中t1是datetime，t2是timestamp

建表时设置timestamp为当前时间

timeStamp not null default CURRENT_TIMESTAMP;

刷新权限

flush privileges;

sql注释

–

查看表结构

DESC 表名;

查看表的各种参数状态

SHOW TABLE STATUS;

查看表索引约束

SHOW INDEX FROM 表名；

返回建表时的sql语句

show create table 表名;

SQL_NO_CACHE

加这个字段表示查询的时候不被缓存干扰

示例：

查看innodb引擎状态

show ENGINE innodb status;