Mysql 命令手册

简介

数据类型

• 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值

DECIMAL| 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2| 依赖于M和D的值| 依赖于M和D的值 | 小数值

• 日期和时间类型

---

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。 TIMESTAMP类型有专有的自动更新特性，将在后面描述。

---

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

• 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

常用命令

查看binlog日志

  命令
    # mysqlbinlog  {binlog日志名称} |more

  样例
    # mysqlbinlog mysql-bin.000001|more

    登录mysql
    > show binlog events in 'my3306_binlog.000052';

  more：分屏展示，可以用f参数，整屏滚动
  可携带--no-defaults参数

查看所有mysql配置项

  命令
    # mysql>show variables;

  登陆mysql执行
  支持 like, 例如：show variables like  'log%'

查看master日志列表

  命令
    # mysql>show master logs

  需要登陆数据库

查看master状态

  命令
    # mysql> show master status;

  需要登陆数据库,可看到最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

产生新的binlog

  命令 
    # mysql> flush logs;

  刷新log日志，自此刻开始产生一个新编号的binlog日志文件

重置(清空)所有binlog日志

  命令 
    # mysql> reset master;

  清空所有的binlog日志，会新产生一个binlog日志

数据库管理

查看数据库创建语句及编码

 # SHOW CREATE DATABASE db_abner;

删除数据库

 # drop database <数据库名>;
 # mysql> drop database siguadantang;

创建数据库

 # CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;

修改数据库字符集

 # ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
 # ALTER DATABASE db_abner DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

表管理

查看表创建语句信息

SHOW CREATE TABLE 数据表名
# SHOW CREATE table t_user;

查看表结构信息

desc 表名

为数据表重命名

命令：ALTER TABLE 数据表名 RENAME [TO] 新表名；
注意：命令中出现的[ ]中的内容表示选择性条件，不是必需；
举例：ALTER TABLE grade RENAME newgrade；

创建临时表

 CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,    value INTEGER NOT NULL)

创建内存表

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

修改表字符集

 # ALTER TABLE t_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

删除数据表

命令：DROP TABLE 数据表名；
举例：DROP TABLE grade；

字段管理

修改字段字符集

 # ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
 # ALTER TABLE tbl_name CHANGE username username VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段长度

 # ALTER TABLE 表名 MODIFY COLUMN 字段名  数据类型(修改后的长度)
 # ALTER TABLE attence MODIFY COLUMN id INT(20)

修改字段名称

 # alter table <表名> change <字段名> <字段新名称> <字段的类型>
 # ALTER TABLE attence CHANGE attence_name NAME  VARCHAR(20)

修改字段数据类型

命令：ALTER TABLE 数据表名 MODIFY 字段名 数据类型；
举例：ALTER TABLE grade MODIFY id INT(20);（将字段名id的数据类型改为INT(20)）

修改字段排列位置

命令：ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST | AFTER 字段名2
举例1：ALTER TABLE grade MODIFY newname VARCHAR(20) FIRST;（将字段newname插入到表的第一个位置）
举例2：ALTER TABLE grade MODIFY id INT(11) AFTER grade;（将字段id插入到字段grade的后面）

新增字段

 # ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 DEFAULT NULL; 
 # ALTER TABLE 表名ADD COLUMN 字段名 字段类型  NOT NULL;
 # ALTER TABLE attence ADD COLUMN attence_name VARCHAR(20) DEFAULT NULL; 
 # ALTER TABLE attence ADD COLUMN age VARCHAR(20) NOT NULL;

删除字段

 # ALTER TABLE 表名 DROP COLUMN 字段名;

查看表结构及列编码

 # SHOW FULL COLUMNS FROM t_user;

权限管控

创建用户

 # CREATE USER 'username'@'host' IDENTIFIED BY 'password';
 样例：create user 'abner'@'%' identified by 'Mysql@123';

修改密码

# alter user 'root'@'localhost' identified by 'Mysql@123'
备注：修改用户root的密码为Mysql@123

权限查看

 # show grants;

授权

 grant all privileges on *.* to abner@'%' with grant option;

为用户设置新密码

 SET PASSWORD FOR 'abner'@'%' = PASSWORD('Mysql@456');

状态监控

数据库备份恢复

数据库备份

  命令
    mysqldump -h主机名  -P端口 -u用户名 -p密码 --databases 数据库名 > 文件名.sql

  样例
    # mysqldump -h172.26.128.126 -P3306 -uroot -p1qaz2wsx --databases cinder >cinder.sql

  -h：数据库所在主机IP
  -p：端口号
  -u：数据库用户
  -databases：数据库

备份带删除表的数据库

  命令
    mysqldump  --add-drop-table -u{username} -p{password} --databases {databasename} > {backupfile.sql}

  样例
    # mysqldump --add-drop-table  -h172.26.128.126 -P3306 -uroot -p1qaz2wsx --databases cinder >cinder.sql

  --add-drop-table：表示将表的删除语句也一起生成，下次使用导入时，会先清楚该表
  -h：数据库所在主机IP
  -p：端口号
  -u：数据库用户
  -databases：数据库

压缩备份

  命令
    mysqldump -h主机名  -P端口 -u用户名 -p密码 –database 数据库名 | gzip > 文件名.sql.gz

  样例
    # mysqldump -h172.26.128.126 -P3306 -uroot -p1qaz2wsx --databases cinder |gzip>cinder.sql.gz

备份所有数据库

  命令
    mysqldump -h172.26.128.126 -P3306 -uroot -p1qaz2wsx --all-databases > allbackupfile.sql

  --all-databases：当前用户的所有数据库

备份多个数据库

  命令
    mysqldump -h{hostname} -u{username} -p{password} --databases {databasename1} {databasename2} {databasename3} > multibackupfile.sql

只备份数据库结构

  命令
    mysqldump -h{hostname} -u{username} -p{password} -no-data --databases {databasename1} {databasename2} {databasename3} > structurebackupfile.sql

备份表

  命令
    mysqldump -h{hostname} -u{username} -p{password} databasename {specific_table1} {specific_table2} > backupfile.sql

还原数据库

  命令
    mysql -h{hostname} -u{username} -p{password} {databasename} < backupfile.sql

还原压缩的数据库

  命令
    gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

以文本形式导入数据库

  命令
    mysql>LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE 表名;

导入数据库

  命令
    mysql>source d:\test.sql

  登陆数据库后操作

数据操作

同时添加多条数据

 语法：INSERT INTO 表名[(字段名1，字段名2，…)]

 　　　VALUES （值1，值2，…），（值1，值2，…），

 　　　…

 　　　（值1，值2，…）

 举例：INSERT INTO student VALUES
 　　　　(5，‘lilei’,99),
 　　　　(6,'hanmeimei',87),
 　　　　(8,'poly',76);

根据查询结果插入表

insert into student(id, name, score) select id, name, score from student_all where score between 60 and 100;

通过查询创建表并复制表数据

create table table_name_new as select * from table_name_old

通过查询创建表但不复制数据

create table table_name_new as select * from table_name_old where 1=2;

或者

create table table_name_new like table_name_old

mysql慢查询日志

• show variables like 'slow_query_log';

 查看是否开启慢查询日志:off-关闭，on-打开

• show variables like 'log_queries_not_using_indexes'

 是否开启将慢查询的sql 打印到慢查询日志：off-关闭，on-打开

• show variables like 'long_query_time';

 需要把超过多长时间的查询计入慢查询日志：0-任何查询sql都记入慢查询日志

• show variables like 'slow_query_log_file';

 慢查询日志文件位置

• 设置参数方法

set global slow_query_log=on;

• 分析慢查询日志

 mysqldumpslow -t 3 /opt/mysql/log/mysql_slow.log

 分析前3条日志

 还可以使用pt-query-digest，下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/,
 pt-query-digest帮助地址[pt-query-digest使用 https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html](https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html)

结语

内存表和临时表

回首页