Mysql 常用命令整理

注意

本文章所有 sql 均测试成功，基于 mysql 8.0.18 版本。
// 2022-05-03
~~因为触发器与储存过程总是听说影响mysql性能，所以没有收录。~~
因为不同数据库的储存过程不同，当应用切换数据库的时候就会导致很多问题。

DDL 数据定义语言

-- 查看所有数据库
show databases;
-- 查看匹配的数据库
show databases like '%mysql%';
-- 查看创建数据库语句
show create database test_mysql ;
-- 进入数据库
use test_mysql ;
-- 查看当前使用的数据库
select database();
-- 创建数据库
create database test_mysql;
-- 创建数据库指定字符集和排序规则,如果这个数据库不存在
create database if not exists test_mysql default character set utf8mb4 default collate utf8mb4_general_ci;
-- 删除数据库
drop database test_mysql;

-- mysql 8.0 的存储引擎对比：https://blog.csdn.net/seagal890/article/details/82055219
-- 查看所有支持的引擎(mysql8默认innodb)
show engines;

-- 查看当前数据库下面的所有数据表
show tables;

-- 查看建表语句
show create table test_mysql ;
-- 查看表结构
desc test_mysql;
-- 创建空表
create table test_mysql;
-- 索引参考 https://blog.csdn.net/qq_41573234/article/details/80250279
-- 创建表并添加字段,设置索引以及设置表引擎与字符集,如果这个数据表不存在就执行
create table if not exists test_mysql(
    -- 主键自增不为空
	id int(10) primary key not null auto_increment,
	name varchar(10) null default "",
	title varchar(10) null default "",
	age tinyint(1) not null
	-- 指定主键(可选，此处设置上面就不需要设置主键，但需要指定自增！！)
	-- primary key (id) 
	-- 设置普通索引(可选)
	-- title(title),
	-- 指定唯一联合索引(可以指定单列/多列)
	-- unique name_age (name, age),
	-- 全文索引只能作用在  char、varchar、text、类型的字段上。创建全文索引需要使用  fulltext  参数进行约束。
	-- fulltext key title(title)
	-- 单列索引(可选)
	-- index title(title),
	-- 多列索引(可选)
	-- index name_title(name,title)
	-- 索引还可以指定字段大小，比如字段长20 可以设置 字段(10) 来索引字段前10长度
	-- 还可以在索引设置 字段 asc 进行索引排序设置
) engine=innodb default charset=utf8mb4;
-- 修改表引擎
alter table test_mysql engine=innodb;
-- 添加字段(最后)
alter table remove_filed add name varchar(12);
-- 指定位置添加字段 (表头)
alter table remove_filed add id int(10) first;
-- 指定位置添加字段（指定字段之后）
alter table remove_filed add age int(1) after name;
-- mysql给已经存在的主键添加自增：https://blog.csdn.net/weixin_42134712/article/details/110394441
-- mysql auto_increment：主键自增长：http://c.biancheng.net/view/7624.html
-- mysql 创建表并设置主键自增：https://blog.csdn.net/myxx520/article/details/5130249
-- 主键自增
alter table test_mysql  modify id int(10) not null auto_increment;
-- 修改数据类型
alter table remove_filed modify name int(10);
-- 修改字段
alter table remove_filed change id id varchar(12);
-- 删除字段
alter table remove_filed drop id;
-- 删除表
drop table test_mysql;
-- 清空表(不建议，无法恢复)
truncate test_mysql;

-- mysql查看索引（show index）：http://c.biancheng.net/view/7364.html
-- 查看指定表的所有索引
show index from test_mysql;
-- alter 创建普通索引单列/多列
alter table test_mysql add index name(name);
-- alter 创建唯一索引单列/多列
alter table test_mysql add unique index name(name);
-- alter 创建主键索引
alter table test_mysql add primary key(id);
-- create 创建普通索引(只能普通索引或unique索引)单列/多列
create index name on test_mysql (name);
-- create 创建唯一索引(只能普通索引或unique索引)单列/多列
create unique index name on test_mysql (name);
-- mysql——删除索引：https://blog.csdn.net/qq_41573234/article/details/80328281
-- 删除索引(删除指定列时，会自动的删除索引中的那列，当索引的所有列被删除，则索引自动删除)
alter table test_mysql drop index name;
-- 删除索引(删除指定列时，会自动的删除索引中的那列，当索引的所有列被删除，则索引自动删除)
drop index name on test_mysql;

-- mysql视图的作用（详细）：https://blog.csdn.net/hxnlyw/article/details/81669964
-- 创建视图(单表)
create view view_name as select * from test_mysql ;
-- 创建视图(指定字段名)
create view view_name (id,title) as select id,title from test_mysql ;
-- 创建视图(字段名可以设置别名，按照顺序映射，如title字段映射到真实表中的name字段)
create view view_name (id,title) as select id,name from test_mysql ;
-- 创建视图(多表)
create view view_name as select test_mysql.id,test_mysql1.name from test_mysql join test_mysql1 on test_mysql.id = test_mysql1.id;
-- 查看视图字段(与查看表语句一样)
desc view_name ;
-- 查看视图创建语句(与查看表创建一样)
show create view view_name ;
-- 修改视图
alter view view_name (id,name) as select id,name from test_mysql ;
-- 删除视图
drop view view_name ;

-- mysql 事务：https://www.runoob.com/mysql/mysql-transaction.html
-- 事务(innodb引擎才支持事务)
-- 事务处理可以用来维护数据库的完整性，保证成批的 sql 语句要么全部执行，要么全部不执行。
-- 事务用来管理 insert,update,delete 语句
-- mysql设置事务自动提交（开启和关闭）：http://c.biancheng.net/view/7291.html#
-- 查看事务自动提交模式
show variables like 'autocommit';
-- 开启事务
begin;
-- 回滚事务
rollback;
-- 提交事务
commit;

DML 数据操作语言

-- mysql insert：插入数据（添加数据）：http://c.biancheng.net/view/2574.html
-- mysql dml（数据操作语言） 详解： https://www.knowledgedict.com/tutorial/mysql-dml.html
-- 插入数据(单行)
insert into test_mysql.test_mysql (name, age, title) values("王五",11,"员工");
-- 插入数据(多行,逗号分割)
insert into test_mysql.test_mysql (name, age, title) values("王五",11,"员工"),("王五",11,"员工"),("王五",11,"员工"),("王五",11,"员工");
-- 插入数据(不指定字段名,但是数据顺序要对应数据库中的字段)
insert into test_mysql.test_mysql values (11,"王五","员工",22);
-- 快速复制表数据
insert into test_mysql.test1_mysql select * from test_mysql ; 
-- 快速复制表数据并指定条件和设置字段值
insert into test_mysql.test_mysql (name, age, title) select name, age, "老板" from test_mysql.test_mysql where age > 10 ; 

-- 更新字段（多个字段通过逗号分隔）
update test_mysql.test_mysql set title = "老板" where id = 3;
-- 更新字段（更新指定数量数据行于按照顺序进行更新）
update test_mysql.test_mysql set title = "老板" where id = 3 order by id desc limit 2;
-- 关联更新(只更新单表)
update test_mysql,test1_mysql set test_mysql.title = test1_mysql.title where  test_mysql.name = test1_mysql.name;
-- 关联更新(只更新单表,支持多种关联与子查询)
update test_mysql left join test1_mysql on test_mysql.name = test1_mysql.name set test_mysql.age = test1_mysql.age;  
-- 多表更新(更新多表,支持多种关联)
update test_mysql left join test1_mysql on test_mysql.name = test1_mysql.name set test_mysql.age = test1_mysql.age, test1_mysql.title  = test_mysql .title ;  

-- mysql关联删除：https://blog.csdn.net/weixin_30596023/article/details/95859484
-- 删除数据
delete from test_mysql.test_mysql where id = 3;
-- 关联删除(只删除单表)
delete test_mysql from test_mysql,test1_mysql where test_mysql.id = test1_mysql.id and test_mysql.id >10;
-- 关联删除(删除多表)
delete test_mysql,test1_mysql from test_mysql right join test1_mysql on test_mysql.id = test1_mysql.id where test1_mysql.id < 10;

DQL 数据查询语言

-- 单表查询，查询数据量
select count(1) from test_mysql;
-- 单表查询，查询指定字段最大值
select max(age) from test_mysql;
-- 单表查询，查询指定字段和
select sum(age) from test_mysql;
-- 单表查询，查询指定字段平均值
select avg(age) from test_mysql;

-- 单表查询
select * from test_mysql;
-- 单表查询，指定查询数量 x,y 只填写一个默认为 0,y
select id,name from test_mysql limit 1;
-- 单表查询，按照 id 排序，默认 asc
select id,name from test_mysql order by id desc;
-- 单表查询， group by
-- select id,name,age from test_mysql where age > 20 group by name;
select count(id),name from test_mysql group by name;
-- sql having 子句：https://www.w3school.com.cn/sql/sql_having.asp
-- 单表查询，通过 having 对聚合字段进行 where
-- select max(age) as max_age,title from test_mysql where max_age > 20 group by title;
select max(age) as max_age,title from test_mysql group by title having max_age > 20 ;

-- 单表查询，别名
select name as username from test_mysql;
-- 单表查询，去重，支持多个字段
select distinct name from test_mysql;
-- 单表查询，指定字段条件
select id,name from test_mysql where id > 20;
-- 单表查询，区间查询,也可以是时间区间
select id,name from test_mysql where id between 10 and 20;
-- 单表查询，in
select id,name from test_mysql where id in (10,20);
-- 单表查询，模糊查询 % 匹配任意字符，_ 匹配一个字符
select id,name from test_mysql where name like "%_五%";
-- 单表查询，is null,is not null
select id,name from test_mysql where name is null;

-- 子查询 作为单独表查询 test_mysql 并将子查询结果别名为 new_test_mysql
select * from (select * from test_mysql ) as new_test_mysql  where age > 10;
-- 子查询 作为条件集查询 test_mysql 并将子查询结果别名为 new_test_mysql
select * from test_mysql  where id in (select id from test_mysql );

-- 关联查询 inner left right 
select test_mysql.id,test1_mysql.name from test_mysql left join test1_mysql on test1_mysql.id = test_mysql.id;
-- 关联查询（表名设置别名） inner left right 
select t.id,t1.name from test_mysql as t left join test1_mysql as t1 on t1.id = t.id;
-- sql数据库多表查询之 where和INNER JOIN：https://blog.csdn.net/weixin_44839345/article/details/88847921
-- where 实现关联（不建议的操作）
-- select t.id,t1.name from test_mysql as t,test1_mysql as t1 where t.id = t1.id;

-- https://www.php.cn/mysql-tutorials-417851.html
-- 查询字段 if 语句
select *,if(sva=1,"男","女") as ssva from taname where sva != ""
-- 查询字段 CASE when 语句
select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''

DCL 数据控制语言

-- 查看所有用户 https://blog.csdn.net/team39/article/details/89413015
select * from mysql.user;

-- mysql用户管理：添加用户、授权、删除用户 https://www.cnblogs.com/chanshuyi/p/mysql_user_mng.html
-- mysql 创建用户命令-grant：https://www.cnblogs.com/58top/p/7799500.html
-- 用户管理
-- 创建用户指定访问域名以及设置密码
-- create user 'xiaqiuchu'@'127.0.0.1' identified by '12345678';
-- create user 'xiaqiuchu'@'%' identified by '12345678';
create user 'xiaqiuchu'@'localhost' identified by '12345678';
-- 创建用户并授予指定权限，以及设置密码
grant all privileges on * . * to 'xiaqiuchu'@'localhost' identified by ‘123456’ ;
-- mysql 8.0修改密码：https://www.cnblogs.com/chloneda/p/12449819.html
-- 更改指定用户密码
alter user 'xiaqiuchu'@'localhost' identified with mysql_native_password by '00000000';
-- 更改指定用户 host 信息，如：远程访问，本地访问等
update mysql.user set host="127.0.0.1" where user="xiaqiuchu" and host="localhost";
-- mysql基础：dcl语句（包含权限信息设置）：https://www.cnblogs.com/rohn/p/11722515.html
-- 授权可以很细粒度，不止权限，还有链接信息很多很多。
-- 给用户授权 test_mysql 数据库中 test_mysql 表的查询与插入权限（可以多个用户与多个权限）
-- grant select, insert on test_mysql.test_mysql to 'xiaqiuchu'@'localhost';
-- 给用户授权所有数据库与所有表的所有权限
-- grant all on *.* to 'xiaqiuchu'@'localhost';
-- mysql：mysql数据库修改用户权限（远程访问权限、操作权限）：https://blog.csdn.net/m0_37482190/article/details/86624096
-- 给用户添加授权/更新权限 test_mysql 数据库中所有表的 查询与更新权限（如果是当前用户需要刷新 flush privileges;）
grant select,update on test_mysql.* to 'xiaqiuchu'@'localhost';
-- 查看指定用户权限
show grants for 'xiaqiuchu'@'localhost';
-- 删除用户
drop user 'xiaqiuchu'@'127.0.0.1';
-- 修改信息后刷新缓存/权限/刷新数据库
flush privileges;

其他

-- 分析sql执行
explain select id,name from test_mysql;


-- mysql查看数据库相关信息: https://www.cnblogs.com/jiangxiaobo/p/6110647.html
-- MySql中的变量定义：https://www.cnblogs.com/gavin110-lgy/p/5772577.html
-- 查看全局变量
show global variables;
-- Mysql show variables系统变量详解：https://www.cnblogs.com/cqliyongqiang/p/9515813.html
-- 查看会话变量
show session variables; -- show variables;
-- 查看指定变量（必须知道变量全名） global 全局、session 会话
select @@global.sql_mode;
select @@session.sql_mode;
-- 设置/使用用户变量 ：https://blog.csdn.net/jiang1986829/article/details/47313861
set @a=1;
-- 获取用户变量
select @a;
-- 查看指定变量：连接数设置
-- show session variables like '%max_connections%';
-- show variables like '%max_connections%';
show global variables like '%max_connections%';
-- 查看指定变量：最大连接数
show global status like 'Max_used_connections';
-- 查看字符集 服务器级别、数据库级别、表级别、列级别
show variables like 'character%';
-- 查看大小写规则设置
show variables like "%lower_case_table_names%";
-- 查看模式：宽松模式 vs 严格模式
show variables like "%sql_mode%";


-- 查看进程
show processlist;
-- 关闭指定进程
kill 进程ID号
 -- 查看是否锁表
SHOW ENGINE INNODB STATUS;
-- 退出命令行
quit;


-- mysql中profile的使用：https://www.jianshu.com/p/8f12163337e8
-- 查看是否开启保存最近n条 sql 查询耗时及相关配置（不建议线上环境使用，默认关闭，使用完毕后也建议关闭）
show variables like '%profiling%';
-- 查看是否开启保存最近n条sql执行时间 1开启 0关闭
select @@profiling;
-- 打开sql执行时间记录
set profiling=1;
-- 关闭sql执行时间记录
set profiling=0;
-- 查看最近n条 sql 执行时间
show profiles;

夏秋初的学习笔记

夏末秋初遇见最美好

Mysql 常用命令整理

注意

DDL 数据定义语言

DML 数据操作语言

DQL 数据查询语言

DCL 数据控制语言

其他

推荐学习

公告