MySQL 常用命令行

连接 MySQL

1	mysql -h 主机地址 -u 用户名 -p 用户密码

连接到本机上的 MySQL

首先打开 DOS 窗口，然后进入目录 mysql\bin ，再键入命令 mysql -u root -p 回车后提示你输密码。注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。

如果刚安装好 MySQL，超级用户 root 是没有密码的。直接回车即可进入到 MySQL 中了，MySQL 的提示符是： mysql>

连接到远程主机上的 MySQL

假设远程主机的 IP 为：110.110.110.110，用户名为 root，密码为 abcd123。则键入以下命令：

1	mysql -h 110.110.110.110 -u root -p abcd123;

注：u 与 root 之间可以不用加空格，其它也一样。

退出 MySQL 命令。

exit

修改密码

1	mysqladmin -u 用户名 -p 旧密码 password 新密码

给 root 加个密码 ab12。
首先在 DOS 下进入目录 mysql\bin。然后键入以下命令 mysqladmin -u root -password ab12
注：因为开始时 root 没有密码，所以 -p 旧密码 一项就可以省略了。

再将 root 的密码改为 def345。

1	mysqladmin -u root -p ab12 password def345

增加新用户
注意：和上面不同，下面的因为是 MySQL 环境中的命令，所以后面都带一个分号作为命令结束符。
1
GRANT SELECT ON 数据库.* TO 用户名@登录主机 identified BY "密码"
1. 增加一个用户 test1 密码为 abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用 root 用户连入 MySQL，然后键入以下命令：
1
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO test1@"%" Identified BY "abc";
但增加的用户是十分危险的，你想如某个人知道 test1 的密码，那么他就可以在 internet 上的任何一台电脑上登录你的 MySQL 数据库并对你的数据可以为所欲为了，解决办法见 2。
1. 增加一个用户 test2 密码为 abc，让他只可以在 localhost 上登录，并可以对数据库 mydb 进行查询、插入、修改、删除的操作（localhost 指本地主机，即 MySQL 数据库所在的那台主机）。
这样用户即使用知道 test2 的密码，他也无法从 internet 上直接访问数据库，只能通过 MySQL 主机上的 web 页来访问了。
1
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO test2@localhost test2@localhost identified BY "abc";
如果你不想 test2 有密码，可以再打一个命令将密码消掉。
1
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO test2@localhost test2@localhost identified BY "";

数据库

创建数据库

建立一个数据库
1
CREATE DATABASE <数据库名>;

创建数据库并分配用户

CREATE DATABASE <数据库名>;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON 数据库名.* TO 数据库名@登录主机 IDENTIFIED BY '密码';

SET PASSWORD FOR '数据库名'@'登录主机' = '密码';

依次执行 3 个命令完成数据库创建。

注意：“密码”和“数据库”是用户自己需要设置的。

显示数据库

1	SHOW DATABASES;

注意：为了不再显示的时候乱码，要修改数据库默认编码。以下以 GBK 编码页面为例进行说明：

修改 MySQL 的配置文件。
my.ini 文件里面修改 default-character-set=gbk。
代码运行时修改。

Java 代码：

1	jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk

PHP 代码：

1	header("Content-Type:text/html;charset=gb2312");

C 语言代码：
1
int mysql_set_character_set( MySQL * mysql, char * csname)；
该函数用于为当前连接设置默认的字符集。字符串 csname 指定了 1 个有效的字符集名称。连接校对成为字符集的默认校对。该函数的工作方式与 SET NAMES 语句类似，但它还能设置 charset 的值，从而影响了由 mysql_real_escape_string() 设置的字符集。

删除数据库

1	DROP DATABASE <数据库名>;

删除一个已经确定存在的数据库

1 2	mysql> DROP DATABASE drop_database; Query OK, 0 rows affected (0.00 sec)

删除一个不确定存在的数据库

mysql> DROP DATABASE drop_database;
ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
//发生错误，不能删除 'drop_database' 数据库，该数据库不存在。

mysql> DROP DATABASE IF EXISTS drop_database;
Query OK, 0 rows affected, 1 warning (0.00 sec)
//产生一个警告说明此数据库不存在

mysql> CREATE DATABASE drop_database;
Query OK, 1 row affected (0.00 sec)

mysql> DROP DATABASE IF EXISTS drop_database;
Query OK, 0 rows affected (0.00 sec)
//IF EXISTS 判断数据库是否存在，不存在也不产生错误

连接数据库

1	mysql> USE <数据库名>;

屏幕提示：Database changed

USE 语句可以通告 MySQL 把 db_name 数据库作为默认（当前）数据库使用，用于后续语句。

该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的 USE 语句：

mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable;   # SELECT COUNT(*) FROM db1.mytable
mysql> USE db2;
mysql> SELECT COUNT(*) FROM mytable;   # SELECT COUNT(*) FROM db2.mytable

使用 USE 语句为一个特定的当前的数据库做标记，不会阻碍您访问其它数据库中的表。

下面的例子可以从 db1 数据库访问作者表，并从 db2 数据库访问编辑表：

1 2	mysql> USE db1; mysql> SELECT author_name, editor_name FROM author, db2.editor WHERE author.editor_id = db2.editor.editor_id;

当前选择的数据库

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

MySQL 中 SELECT 命令类似于其他编程语言里的 print 或者 write，你可以用它来显示一个字符串、数字、数学表达式的结果等等。

显示 MySQL 的版本

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.6.45-log |
+------------+
1 row in set (0.01 sec)

显示当前时间

mysql> SELECT NOW(); 
+---------------------+
| NOW()               |
+---------------------+
+---------------------+
| 2021-01-13 20:05:07 |
+---------------------+
1 row in set (0.00 sec)

显示年月日

mysql> SELECT DAYOFMONTH(CURRENT_DATE);
+--------------------------+
| DAYOFMONTH(CURRENT_DATE) |
+--------------------------+
|                       13 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTH(CURRENT_DATE);
+---------------------+
| MONTH(CURRENT_DATE) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT YEAR(CURRENT_DATE);
+--------------------+
| YEAR(CURRENT_DATE) |
+--------------------+
|               2021 |
+--------------------+
1 row in set (0.01 sec)

显示字符串

mysql> SELECT "welcome to my blog!"; 
+---------------------+
| welcome to my blog! |
+---------------------+
| welcome to my blog! |
+---------------------+
1 row in set (0.00 sec)

当计算器用

SELECT ((4 * 4) / 10 ) + 25; 
+----------------------+
| ((4 * 4) / 10 ) + 25 |
+----------------------+
|              26.6000 |
+----------------------+
1 row in set (0.00 sec)

串接字符串

SELECT CONCAT(f_name, " ", l_name) AS Name 
FROM employee_data 
WHERE title = 'Marketing Executive'; 
+---------------+
| Name          |
+---------------+
| Monica Sehgal |
| Hal Simlai    |
| Joseph Irvine |
+---------------+
3 rows in set (0.00 sec)

注意：这里用到 CONCAT() 函数，用来把字符串连接起来。另外，我们还用到 AS 给结果列 CONCAT(f_name, " ", l_name) 起了个假名。

数据表

创建数据表

1	CREATE TABLE <表名> (<字段名1> <类型1> , ... , <字段名n> <类型n>);

以下为数据表的字段：

字段名	数字类型	数据宽度	是否为空	是否主键	自动增加	默认值
id	int	4	否	PRIMARY KEY	AUTO_INCREMENT
name	char	20	否
sex	int	4	否			0
degree	double	16	是

执行的 SQL 语句为：

mysql> CREATE TABLE MyClass(
     > id int(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
     > name char(20) NOT NULL,
     > sex int(4) NOT NULL DEFAULT '0',
     > degree double(16, 2));

删除数据表

1	DROP TABLE <表名>;

例如：删除表名为 MyClass 的表

1	DROP TABLE MyClass;

注意：

DROP TABLE 用于取消一个或多个表。但必须有表的 DROP 权限。所有的表数据和表定义会被取消，所以使用本语句要小心。
对于一个带分区的表，DROP TABLE 会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据。DROP TABLE 还会取消与被取消的表有关联的分区定义（.par）文件。
对于不存在的表，使用 IF EXISTS 用于防止错误发生。当使用 IF EXISTS 时，对于每个不存在的表，会生成一个 NOTE。
RESTRICT 和 CASCADE 可以使分区更容易。但目前，RESTRICT 和 CASCADE 不起作用。

插入数据

1	INSERT INTO <表名> (<字段名1>, ..., <字段名n >) VALUES (值1, ..., 值n );

例如：往表 MyClass 中插入二条记录, 这二条记录表示：编号为 1 的名为 Tom 的成绩为 96.45, 编号为 2 的名为 Joan 的成绩为 82.99，编号为 3 的名为 Wang 的成绩为 96.5。

1	mysql> INSERT INTO MyClass(id, name, degree) VALUES (1, 'Tom', 96.45),(2, 'Joan', 82.99), (3, 'Wang', 96.59);

查询表中的数据

查询所有行命令：

1	SELECT <字段1, 字段2, ...> FROM <表名> WHERE <表达式>;

SELECT 一般配合 WHERE 使用，以查询更精确更复杂的数据。

例如：查看表 MyClass 中 degree 大于 90 的数据。

mysql> SELECT * FROM MyClass WHERE degree > 90;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  1 | Tom  |   0 |  96.45 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
3 rows in set (0.00 sec)

查询前几行数据

例如：查看表 MyClass 中前 2 行数据

mysql> SELECT * FROM MyClass ORDER BY id LIMIT 0, 2;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  1 | Tom  |   0 |  96.45 |
|  2 | Joan |   0 |  82.99 |
+----+------+-----+--------+
2 rows in set (0.00 sec)

删除表中数据

1	DELETE FROM <表名> WHERE <表达式>

例如：删除表 MyClass 中编号为 1 的记录

mysql> SELECT * FROM MyClass;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  1 | Tom  |   0 |  96.45 |
|  2 | Joan |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
3 rows in set (0.00 sec)

mysql> DELETE FROM MyClass WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM MyClass;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  2 | Joan |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
2 rows in set (0.00 sec)

修改表中数据

1	UPDATE <表名> SET <字段> = <新值>, ... WHERE <表达式>

例如：修改表 MyClass 中 id 为 2 的 name 为 Mary。

1	mysql> UPDATE MyClass SET name = 'Mary' WHERE id = 2;

单表的 UPDATE 语句：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name 
SET col_name1=expr1 [, col_name2=expr2 ...] 
[WHERE where_definition] 
[ORDER BY ...] 
[LIMIT row_count]

多表的 UPDATE 语句：

1
2
3

UPDATE [LOW_PRIORITY] [IGNORE] table_references 
SET col_name1=expr1 [, col_name2=expr2 ...] 
[WHERE where_definition]

注意：UPDATE 语法可以用新值更新原有表行中的各列。SET 子句指示要修改哪些列和要给予哪些值。WHERE 子句指定应更新哪些行。如果没有 WHERE 子句，则更新所有的行。如果指定了 ORDER BY 子句，则按照被指定的顺序对行进行更新。LIMIT 子句用于给定一个限值，限制可以被更新的行的数目。

修改数据表

增加字段

1	ALTER TABLE <表名> ADD <字段> <类型> <其他>;

1	mysql> ALTER TABLE MyClass ADD passtest int(4) default '0'

增加索引

1	ALTER TABLE <表名> ADD INDEX <索引名>(<字段名1>, <字段名2>, ...)

1	mysql> ALTER TABLE employee ADD INDEX emp_name(name);

增加主关键字的索引

1	ALTER TABLE <表名> ADD PRIMARY KEY (<字段名>);

1	mysql> ALTER TABLE employee ADD PRIMARY KEY (id);

增加唯一索引

1	ALTER TABLE <表名> ADD UNIQUE <索引名>(<字段名>);

1	mysql> ALTER TABLE employee ADD UNIQUE emp_name2(cardnumber);

增加全文索引
1
ALTER TABLE <表名> ADD FULLTEXT <索引名>(<字段名>);
1
mysql> ALTER TABLE employee ADD FULLTEXT emp_name3(name);
注意：全文索引只能用于数据库引擎为 MyISAM 的数据表，只适用于 VARCHAR 和 TEXT 类型的字段，此外，MySQL 自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。

删除某个索引

1	ALTER TABLE <表名> DROP INDEX <索引名>;

1	mysql>ALTER TABLE employee DROP INDEX emp_name;

修改原字段名称及类型

1	ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新字段类型> <其他>;

1	ALTER TABLE employee CHANGE name full_name char(10) NOT NULL;

删除字段

1	ALTER TABLE <表名> DROP <字段名>;

1	ALTER TABLE employee DROP address;

修改表名
1
RENAME TABLE <原表名> TO <新表名>;
1
mysql> RENAME TABLE MyClass TO YouClass;
注意：
- 当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。
- 如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事务退回到最初状态。
- RENAME TABLE 在 MySQL 3.23.23 中被加入。

备份数据库

命令在 DOS 的 \mysql\bin 目录下执行，导出文件默认是存在 mysql\bin 目录下。

导出整个数据库

mysqldump -u 用户名 -p'密码' 数据库名 > 导出的文件名.sql

mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql
Enter password: ******

mysqldump -u root -p database_name > outfile.sql
Enter password: ******

导出所有数据库

1 2	mysqldump -uroot -p --all-databases > outfile.sql mysqldump -uroot -p -A > outfile.sql

导出一个数据库结构

1	mysqldump -u root -p -d –add-drop-table database_name > outfile.sql

-d 没有数据, –add-drop-table在每个 CREATE 语句之前增加一个 DROP TABLE

带语言参数导出

1	mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile.sql

导出数据表

导出数据表和表结构

1	mysqldump -u root -h 登录主机 -p'密码' -P 端口号数据库名表名 > 文件名.sql

将特定数据库特定表中的数据和表格结构和数据全部返回。

1	mysqldump -u root -h localhost -p -P3306 database_name table > table.sql

导出表结构却不导出表数据
只返回特定数据库特定表格的表格结构，不返回数据，添加 -d 命令参数。
1
mysqldump -u root -h localhost -p -P3306 -d database_name table > table.sql
导出表结构和满足 WHERE 条件的表数据
只返回特定数据库中特定表的表格结构和满足特定条件的数据。
1
mysqldump -u root -h localhost -p -P3306 database_name table -where="time>'2017-01-01'" > table.sql
导出数据却不导出表结构
只返回特定数据库中特定表格的数据，不返回表格结构，添加 -t 命令参数。
1
mysqldump -u root -h localhost -p -t -P3306 database_name table > table.sql

导入 SQL 文件

1	source database.sql;