MySQL命令大全

目录

一、基本SQL语句

1.1 数据库

1.2 建表语句

1.3 增删改查SQL 

1.4 字段操作

1.5 索引

二、基本函数 

三、权限

一、基本SQL语句

1.1 数据库

-- 创建数据库
CREATE DATABASE nacos;

-- 查看数据库
SHOW DATABASES;

-- 使用数据库
use mysql;

-- 删除数据库
delete database db;

1.2 建表语句

create table schema.user(
	user_id int primary key auto_increment comment '用户id',
	user_name varchar(30) not null comment '用户名',
	gender char(1) not null comment '性别：1-男，0-女，3-其他',
	create_user varchar(50) NOT NULL COMMENT '创建人',
  	update_user varchar(50) NOT NULL COMMENT '修改人',
  	create_time datetime NOT null default current_timestamp COMMENT '创建时间',
  	update_time datetime NOT null default current_timestamp on update current_timestamp COMMENT '修改时间',
	index idx_user_name (user_name )
) comment = '用户表';

1.3 增删改查SQL 

SELECT * from USER;

UPDATE USER SET NAME = '张三' WHERE ID = 1;
UPDATE USER SET NAME = '张三', AGE = 20 WHERE ID = 1;

-- 不指定字段新增
insert into a.business values ('pd1', 'pd111', '1000');
-- 指定字段新增
insert into a.business (department, products_id) values ('pd1', 'pd111');

DELETE FROM USER WHERE ID = 1;

1.4 字段操作

-- 新增字段
alter table user add column user_group varchar(20) default null comment '用户组';

-- 删除字段
alter table user drop column status;

-- 修改字段
alter table user modify column status char(1) not null 
comment '状态：0-正常，1-注销，2-封存';

-- 字段重命名
alter table user change mupdate_time update_time datetime 
NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP COMMENT '修改时间';

-- 字段顺序调整：将字段 platform 放到 department后面
alter table user modify platform char(1) not null comment 
'平台：1-支付宝，0-微信' after department;

-- 字段顺序调整：将 name 字段放到最前面
alter table user modify name varchar(20) first;

-- 表重命名
alter table user rename user_bak;
-- 清空表数据
truncate table test;

-- 删除视图
DROP VIEW IF EXISTS v_dept;
-- 重命名视图
alter view dake.v_pf rename dake.v_pf_bak;

1.5 索引

-- 普通索引
alter table user add index idx_user_id(user_id);

-- 添加唯一索引
alter table user add unique key unq_user_id(user_id);

-- 添加主键索引
alter table dprt add primary key (dpt_cd);

-- 删除索引
alter table user drop index idx_user_id;

-- 删除主键
alter table user drop primary key;

-- 修改主键，需要先删除再添加，如果是自增长主键，需要先取消自增长再删除；

-- 取消自增长的方法就是修改字段类型而已
-- 取消自增长
alter table user modify id int not null comment '主键';
-- 删除主键
alter table user drop primary key;
-- 添加主键
alter table user add primary key (user_id, group);

二、基本函数 

-- 查询版本号
SELECT VERSION();

-- 查询日期时间
select SYSDATE();
select now();
select SYSDATE() from dual;
select now() from dual;

-- 拼接字符串
select CONCAT("1", "a") from  dual;
select CONCAT("1", 2, "a") from  dual;

-- 截取字符串：截取左边两位
select left("123456", 2) from  dual;
-- 截取字符串：截取右边两位
select right("123456", 2) from  dual;


-- 是否开启慢sql日志，默认off
show variables like 'slow_query_log';

三、复杂函数

-- 查看超时时间，默认是28800秒，8个小时
show global variables like 'wait_timeout';
-- 设置全局超时时间
set global wait_timeout=604800;

-- 查看最大连接数，默认151
SHOW VARIABLES LIKE '%max_connections%';
-- 设置最大连接数
SET GLOBAL max_connections = 2000;

-- 查看mysqlx_max_connections最大连接数，默认100
SHOW VARIABLES LIKE '%mysqlx_max_connections%';

-- 查看当前登陆用户的连接，如果是root用户，可以查看所有；
-- 如果是当前用户，只查看当前用户。只列出前100条
show processlist;
-- 查看所有用户的连接
show full processlist; 

-- 查看所有的配置
SHOW GLOBAL VARIABLES;

-- 查看所有的mysql配置的状态
SHOW STATUS;

-- 查看某个配置的状态
SHOW STATUS like '%下面变量%';

Aborted_clients                	由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 
Aborted_connects            	尝试已经失败的MySQL服务器的连接的次数。 
Connections                     试图连接MySQL服务器的次数。 
Created_tmp_tables          	当执行语句时，已经被创造了的隐含临时表的数量。 
Delayed_insert_threads     		正在使用的延迟插入处理器线程的数量。 
Delayed_writes                	用INSERT DELAYED写入的行数。 
Delayed_errors                	用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 
Flush_commands            		执行FLUSH命令的次数。 
Handler_delete                 	请求从一张表中删除行的次数。 
Handler_read_first          	请求读入表中第一行的次数。 
Handler_read_key           		请求数字基于键读行。 
Handler_read_next          		请求读入基于一个键的一行的次数。 
Handler_read_rnd           		请求读入基于一个固定位置的一行的次数。 
Handler_update             	 	请求更新表中一行的次数。 
Handler_write                 	请求向表中插入一行的次数。 
Key_blocks_used            		用于关键字缓存的块的数量。 
Key_read_requests         		请求从缓存读入一个键值的次数。 
Key_reads                      	从磁盘物理读入一个键值的次数。 
Key_write_requests         		请求将一个关键字块写入缓存次数。 
Key_writes                      将一个键值块物理写入磁盘的次数。 
Max_used_connections   			同时使用的连接的最大数目。 
Not_flushed_key_blocks   		在键缓存中已经改变但是还没被清空到磁盘上的键块。 
Not_flushed_delayed_rows      	在INSERT DELAY队列中等待写入的行的数量。 
Open_tables                  	打开表的数量。 
Open_files                     	打开文件的数量。 
Open_streams               		打开流的数量(主要用于日志记载） 
Opened_tables              		已经打开的表的数量。 
Questions                     	发往服务器的查询的数量。 
Slow_queries                	要花超过long_query_time时间的查询数量。 
Threads_connected       		当前打开的连接的数量。 
Threads_running          		不在睡眠的线程数量。 
Uptime                        	服务器工作了多少秒。

常用命令

-- Linux 系统默认情况下MySQL数据库是区分大小写的，
-- Windows系统默认情况下MySQL数据库是不区分大小写的。
-- 这里的系统指的是 MySQL 安装的系统

-- 查看大小写情况，默认值为0，即区分大小写
SHOW VARIABLES like '%lower_case_table_names%';

-- 查看所有的全局参数
SHOW GLOBAL VARIABLES;

四、权限操作

-- 查看数据库所有用户
select user from mysql.user;

-- 查看root用户的localhost的所有权限
show grants for 'root'@'localhost' ;

-- 查看dake用户的localhost的所有权限
show grants for 'dake'@'localhost' ;

-- 查看dake用户的远程连接权限
show grants for 'dake'@'%' ;

-- 使用root账户登陆mysql服务器，密码是123456
mysql -uroot -p123456

-- 查看mysql服务器的所有用户的连接服务器情况
select host,user,plugin,authentication_string from mysql.user;


-- 修改root用户的远程登陆使用mysql_native_password的方式登陆，密码是123456
-- 在mysql8之后，默认的密码方式是caching_sha2_password，这里修改后可以使用
-- 密码进行登陆，不然会登陆失败
alter user 'root'@'%' identified with mysql_native_password by '123456';
-- 修改玩后要刷新权限
flush privileges;
-- 此时在查看用户连接服务器情况，则plugin的取值就不一样了
select host,user,plugin,authentication_string from mysql.user;

-- 创建用户
CREATE USER 'root'@'%' IDENTIFIED BY '123456';

-- 授予root用户所有的远程权限
grant all on *.* to 'root'@'%';
-- 授予repl用户主从同步权限
grant replication slave, REPLICATION CLIENT on *.* to repl@'%';
 
-- 查看root用户的远程权限
SHOW GRANTS FOR 'root'@'%';
 
-- 刷新权限
FLUSH PRIVILEGES;
-- 刷新主机
flush hosts;

-- 创建用户
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
-- 远程密码访问
alter user 'test'@'%'  IDENTIFIED with mysql_native_password by '123456';
-- 删除用户
drop USER 'repl'@'%';