MySQL基础操作——常用命令

搜集了一下经常使用的MySQL命令。
9de7bb31d0644e7393039222babfed10 欢迎收藏。

账号相关

-- 创建账号
CREATE DATABASE TEST DEFAULT CHARACTER SET utf8mb4 COLLATE=utf8mb4_bin;
CREATE USER 'TEST'@'%' IDENTIFIED WITH mysql_native_password BY 'Test_1qaz' password expire never;
-- 查询账号
select user,host from mysql.user;
-- 更改密码的三种方法
set password='********';
alter user user() identified by '********';
alter user 'root'@'localhost' identified by '********';
-- 分配权限
GRANT ALL PRIVILEGES ON TEST.* to 'TEST'@'%' ;
GRANT PROCESS on *.* to 'TEST'@'%'
FLUSH PRIVILEGES;
-- 查看用户权限
show grants for test;

字符集相关

-- 查看字符集和排序规则
show variables like 'character_set%';
show variables like 'collation%';
-- 查看 MySQL所支持的字符集
show charset;
-- 查看库的字符集
show create database sys\G
-- 查看表的字符集
show table status from sys like 'sys_config';
-- 查看表中所有列的字符集
show full columns from sys_config;

-- 创建库的时候指定字符集和排序规则
create database TEST2 default character set utf8mb4 collate utf8mb4_bin;
-- 创建表的时候指定字符集和排序规则
create table t1(id int(6),name char(10)) default character set utf8mb4 collate utf8mb4_bin;

-- 修改库的字符集
alter database shiyan default character set gbk; 
-- 修改表的字符集
alter table test1 convert to character set utf8;
-- 修改字段的字符集
alter table test1 modify name char(10) character set gbk;

-- 修改全局字符集
-- 建立连接使用的编码
set character_set_connection=utf8mb4;
-- 数据库的编码
set character_set_database=utf8mb4;
-- 结果集的编码
set character_set_results=utf8mb4;
-- 数据库服务器的编码
set character_set_server=utf8mb4;
set character_set_system=utf8mb4;
set collation_connection=utf8mb4;
set collation_database=utf8mb4;
set collation_server=utf8mb4;

引擎

-- 查看mysql现在已提供什么存储引擎
show engines;
-- 修改表引擎
ALTER TABLE t1 ENGINE=INNODB;
-- mysql当前默认的存储引擎
show variables like '%default_storage_engine%'; 
-- 某个表用了什么引擎
show create table t1;

注释

mysql注释符有三种:

1. 单行注释: – 注释内容 注意-- 后需要一个空格,-- 后内容将不会被识别,所以要下一行加分号来结束语句. oracle不需要空格
2. 单行注释: #注释内容
3. 多行注释: /注释内容/
4. 多行注释: /!注释内容/ 这种叫内联注释, 当 ! 后面所接的数据库版本号时, 当实际的版本 >= 那个版本号, 将注释内容解释为SQL, 否则就会当做注释来处理. 默认的, 当没有接版本号时, 当做SQL

执行脚本或命令

#执行sql脚本文件
未连接数据库: 
mysql -h 192.168.55.44 -P 3306 -u root -p******** < /tmp/test.sql
mysql -h 192.168.55.44 -P 3306 -u root -p******** -e "\. /tmp/test.sql"
mysql -h 192.168.55.44 -P 3306 -u root -p******** -e "source /tmp/test.sql"
已连接数据库: mysql> source /tmp/test.sql
#执行sql语句
mysql -h 192.168.55.44 -P 3306 -u root -p******** -e "select version();"

输出到文件

#输出到文件
mysql -h 192.168.55.44 -P 3306 -u root -p******** -e "select version();" -N > result.txt  #输出在客户端 -N: 忽略列名
mysql -h 192.168.55.44 -P 3306 -u root -p******** -e "select version() into outfile '/var/lib/mysql-files/result.txt';" #输出在服务器上
已连接数据库: tee 和 notee (相当于oracle spool 和 spool off)
#输出html
mysql -h 192.168.55.44 -P 3306 -u root -p******** --html #然后执行 tee -> 查询 -> notee
mysql -h 192.168.55.44 -P 3306 -u root -p******** -e "select version()" --html >> test.html #输出html(不带查询语句, 再加 -N 可以不带列名)

其他

#执行操作系统命令
mysql> system commands
#登录到指定的数据库
mysql -h127.0.0.1 -P 3306 -uhmonitor -phmonitor -DHMONITOR
#查看版本
mysqld --version #服务端版本
mysql --version  #客户端版本
mysql> select version();
mysql> select * from sys.version;
mysql> show variables like 'version';
mysql> status;
#获得帮助
help contents  -- 服务器方面帮助
help           -- 客户端帮助 常用有:
  clear(\c)清除当前输入语句
  connect(\r)重新连接到服务器. 可选参数为db和host
  delimiter(\d)设置语句分隔符
  ego(\G)向mysql服务器发送命令,垂直显示结果. 
  exit(\q)退出mysql. 与退出相同
  notee(\t)不要写入输出文件
  system(\！)执行系统shell命令
  tee(\T)设置输出文件[to_outfile]. 将所有内容附加到给定的输出文件中
  use(\u)使用其他数据库. 将数据库名称作为参数
  warnings(\W)在每个语句后显示警告
  nowarning(\w)不要在每个语句后显示警告
#php解除mysql导入2M限制
修改php.ini条目upload_max_filesize的值
#MySQL的数据迁移(轻量级数据)
mysqldump --login-path=bak test --set-gtid-purged=OFF --skip-lock-tables | mysql --login-path=bak3308 d3308