MySQL学习笔记（总结）

1. 数据库服务器操作命令

启动数据库：net start mysql80 （注释：windows命令）

停止数据库：net stop mysql80 （注释：windows命令）

重启数据库：systemctl restart mysql;（重启后全局变量失效）

连接数据库：mysql -h localhost -P 3306 -u root -p （注释：windows命令）

备份数据库：mysqldump [-h{ip}] -uroot -p1234 数据库名 > d:/数据库名.sql（windows命令）

退出数据库：exit （注释：mysql命令，下记略）

2. DDL数据库操作命令

查询所有数据库：show databases; //显示所有数据名

切换当前数据库：use {数据库名}; // 将数据库切换到指定数据库

查询当前数据库：select database(); // 显示当前数据库名

创建数据库：careate database [if not exists] {数据库名} [default charset 字符集]

删除数据库：drop database {数据库名}

3. DDL表操作

查询所有表：show tables; // 显示当前数据库所有表名

查询表结构：desc {表名}; // 显示表字段 属性 约束

查询建表语句：show create table {表名};

创建表：create table {表名} ( {字段名} {类型} [comment 注释], ... ) [comment=表注释];

修改表_添加字段：alter table {表名} add {字段名} {类型} [comment 注释 约束];

修改表_修改类型：alter table {表名} modify {字段名} {类型} [comment 注释 约束];

修改表_修改字段：alter table {表名} change {原字段} {新字段} {新类型} [comment 约束];

修改表_删除字段：alter table {表名} drop {字段名};

修改表_修改表名：alter table {表名} rename to {新表名};

重新建表：truncate table {表名}; // 删除后重新建表(删数据比delete快，需要有改表权限)

删除表：drop table [if exists] {表名};

4. DML数据操作（增删改）

单条插入_指定字段：insert into {表名} (字段, 字段...) values (值, 值...);

单条插入_全部字段：insert into {表名} values (值, 值...);

批量插入_指定字段：insert into {表名} (字段, 字段...) values (字段, 字段),(字段， 字段)...;

批量插入_全部字段：insert into {表名} values (字段, 字段),(字段, 字段)...;

修改数据：update {表名} set {字段}={值}, {字段}={值}... [where 条件];

删除数据：delete from {表名} [where 条件];

5. DQL数据操作（查询）

查询：select 字段... from {表名} [where 条件] [group by 字段...] [having 字段] order by {字段} asc, {字段} desc limit 下标值 件数值;

去重查询：select distinct 字段... from {表名} [where 条件]...; // 据说去重比分组快

条件运算符： =; !=;<> ; >; <; >=; <=; beturn and ; in ; like ; is null; no in; not like; is not null;

逻辑运算符：and ; or ; !

关联查询：inner join; left [outer] join; right [outer] join; full outer join;

联合查询：union; union all;

嵌套查询（子查询）：

标量子查询：select * from {表1}  where {字段} =  (select {字段} from {表2});

行子查询：select * from {表1}  where {字段, 字段} =  (select {字段, 字段} from {表2});

列子查询：select * from {表1}  where {字段} [in,any,some,all]  (select {字段} from {表2});

表子查询：select * from {表1}  where {字段, 字段} in  (select {字段, 字段} from {表2});

判空函数： if (表达式, 字段1, 字段2)  // 如果表达式成立则字段1，否则字段2

判空函数： ifnull (字段1, 字段2)  // 如果字段1是null则字段2，否则字段1

case when {表达式} then {字段1} else {字段2} end;

case {字段1} when {值1} then {} when {值2} then {} else {} end; 

6. DCL数据访问权限（用户权限）

查询用户：select * from user;

创建用户：create user '{用户名}'@'{主机名}' identified {密码}; // 主机 'localhost'本地访问权限，'%'全部机器访问权限

修改密码：alter user '{用户名}'@'{主机名}' identified with mysql_native_password by {新密码};

删除用户：drop user '{用户名}'@'{主机名}';

查询用户权限：show grants for '{用户名}'@'{主机名}';

授权用户权限_数据库下所有表：grant {权限}, {权限}... on {数据库.表名} to '{用户名}'@'{主机名}';

授权用户权限_所有数据的表：grant {权限}, {权限}... on *.* to '{用户名}'@'{主机名}';

撤销权限：revoke  {权限}, {权限}... on {数据库.表名} from '{用户名}'@'{主机名}';

7. 约束

建表约束_字段指定：create table {表名} (字段 类型 primary key, 字段 类型 not null, 字段 类型 unique, 字段 类型 check(条件), 字段 类型 defult);

建表约束_外键：create table {表名} (... constraint 外键名 foreign key (字段) 主表名(字段) );

追加外键约束：alter table {表名} add constraint {约束名} {约束} (字段) referenes 主表名(字段);

删除外键约束：alter table {表名} drop foreign key 外键约束名;

外键更新删除行为：no action 不允许删除更新，restrict 不允许删除更新，cascade 同时删除更新子表记录，set null 子表设置成 null

设置外键更新删除行为：alter table {表名} add constraint {约束名} {约束} (字段) referenes 主表名(字段) on update cascade on delete set null; // 主表更新子表同事更新。主表删除子表设置null

8. 事务（原子性，一致性，隔离性，持久性）

查询事务提交方式：select @@autocommint; // 1自动提交 0手动提交

设置事务提交方式：set @@autocommint = 0; // 改为手动提交

开启事务：start transaction; // 或者begin

提交事务：commit; 回滚事务：rollback;

隔离级别：read uncommited（读未提交）有脏读 不可重复读 幻读问题, read commited（读已提交）有不可重复读 幻读问题, reteatable read（可重复读）有幻读问题, serializable（串行化）三个问题都没有，执行效率低

查看事务隔离级别：select @@transaction_isolation;

设置事务隔离级别：set [session | global] transaction level {隔离级别}; // session 当前窗口生效 global所有窗口生效

9. 索引

创建索引：create [unique fulltext] index {索引名} on 表名(字段..); 

查看索引：show index from {表名};

删除索引：drop index {索引名} on {表名};

提示用指定索引查询：select * from 表 use index(索引名) where 条件;

提示不用指定索引查询:select * from 表 ignore index(索引名) where 条件;

强制使用索引查询：select * from 表 force index(索引名) where 条件;

10. 视图

创建视图：create view 视图名 as select * from 表; 

查询视图：select * from 视图名; // 与查询表一致

查询视图结构：show create view 视图名; // 与查询表结构一致

替换视图：create or replace view 视图名 as select * from 表; 

修改视图：alter view 视图名 as select * from 表; 

删除视图：drop view 视图名; 

创建视图检查选项：create view 视图名 as select * from 表 where with cascaded check option;

create view 视图名 as select * from 表 where 条件 with local check option;

11. 存储过程

查看系统变量：show [global session] variables;

查看系统变量值：select @@[global session] {系统变量名}；

设置系统变量：set @@[global session].{系统变量名} = 值; set [global session] {系统变量名} = 值;

设置自定义变量：set @变量名 = 值; set @变量名 := 值; select @变量名 := 值; // 无需声明

查询自定义变量：select @变量名, @变量名;

声明局部变量：declare 变量名 类型; // 可以default 指定默认值

设置局部变量：set 变量名 = 值; set 变量名 := 值; select 字段 into 变量 from 表;

创建存储过程：create procedure {存储过程名} (参数列表) begin ... end;

创建带参存储过程：create procedure {存储过程名} (in 参数 类型, out 返回值 类型) begin ... end;

条件判断： if 条件 then ... elseif 条件 then ... else ... end if;

条件判断：case 值 when 值 then ... else ... end case; case when 条件 then... else ... end case;

循环：while 条件 do ... end while; repeat ... until 条件;

循环：[循环名:] loop ... end loop [循环名]; // leave 循环名(退出) iterate 循环名(跳过)

调用存储过程：call {存储过程名}(参数列表); call {存储过程名}(参数, @自定义变量);

查询存储过程：select * from infomation_schema.routines where routine_schema = {数据库};

查询存储过程创建语句：show create procedure {存储过程名};

删除存储过程：drop procedure {存储过程名};

声明游标： declare 游标名 cursor for 查询语句;

打开游标：open 游标名;

获取当前游标记录：fetch 游标名 into 变量 [, 变量...]; // 多条记录需要循环调用

关闭游标：close 游标名;

12. 存储函数

创建存储函数：create function 存储函数名(参数名 参数类型) // 无需指定参数默认in

returns 返回值类型 begin ... return ...; end;

调用存储函数：select 存储函数名;

13. 触发器

创建触发器：create trigger 触发器名;

before insert / after insert / before update / after update / before delete / after delete //选一个

on 表名 for each row // 行级触发器

begin ... end;

查看触发器：show triggers;

删除触发器：drop trigger [schema名.]触发器名;

14. 锁

加全局锁：flush tables with read lock; 

释放全局锁：unlocak tables;

加表共享读锁（读锁）：lock tables 表名[,表名...] read; // 任何人只能读不能写

加表独占写锁（写锁）：lock tables 表名[,表名...] write; // 自己读写，其他人不能读写

释放表锁：unlock tables;

加查询共享锁：select ... lock in share mode;

加查询排他锁：select ... for update;