MySQL 常用操作

如果是本地开发，使用 GUI 工具比如官方的 MySQL Workbench 会省事很多，但命令行方式在服务器环境特别有用。

另，如果官方的下载地址很慢的话，可尝试这个镜像，速度超快的。

数据库

创建数据库

CREATE DATABASE <db_name>;

删除数据库

DROP DATABASE <db_name>; 

注意 DROP 操作没有确认步骤，回车就执行了。所以进行删除操作时需要谨慎。

重命名数据库

RENAME 命令是用来对表进行重命名的，数据库没有对应的命令，只能间接实现。原理是将老数据库中的所有表重命名到新数据库下。当然，首先得创建新数据库。

RENAME TABLE old_db.table TO new_db.table;

这是一个重复性的操作，所以可以通过脚本来实现，

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

或，

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

其中 -ppassword 中 password 为你的密码，和 -p 之间没有空格。

注：上面为 shell 脚本而非 sql 脚本，在 shell 中运行或保存成 shell 文件执行。

清空数据库

清空数据库，即清空数据库中所有表中的记录。

SET FOREIGN_KEY_CHECKS = 0;

SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';')
FROM   information_schema.tables
WHERE  table_type   = 'BASE TABLE'
  AND  table_schema IN ('db1_name','db2_name');

PREPARE stmt FROM @str;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

导出数据库

导出操作是通过 mysqldump 命令行工具来完成的。

$ mysqldump -u <username> -p --databases [db1] [db2]... > backup.sql

默认导出时是带数据记录的，可通过 --no-data 只导出表结构，

$ mysqldump -u <username> -p --no-data --databases [db1] [db2]... > backup.sql

更多 mysqldump 相关的参数可参见这里。

导入数据库

$ mysql -u username -ppassword database_name < backup.sql

表

创建表

CREATE TABLE [IF NOT EXISTS] table_name(
    column_list
);

重命名表

RENAME TABLE tb1 TO tb2;

如果想一次重命名多张表，

RENAME TABLE tb1 TO tb2, tb3 TO tb4;

删除表

DROP TABLE <table_name>;

同时删除多张表，

DROP TABLE table1,table2...;

像这种操作表记录的，如果表存在外键关联，一般会报错，如果你明显知道自己在干什么，且知道操作是安全的，可暂时关闭 MySQL 的外键检查，操作完成后再开启。后续的操作中遇到该报错可同样应用此设置。

SET FOREIGN_KEY_CHECKS = 0; 
DROP TABLE <table_name>;
SET FOREIGN_KEY_CHECKS = 1; 

清空表

TRUNCATE [TABLE] tbl_name;

添加/删除主键

ALTER TABLE user 
  DROP PRIMARY KEY; 

ALTER TABLE user 
  ADD PRIMARY KEY(id) 

变更主键

已经设置主键的情况下想要变更主键，可以先去掉原来的主键，再新增主键。

ALTER TABLE user 
  DROP PRIMARY KEY; 

ALTER TABLE user 
  ADD COLUMN id INT NOT NULL auto_increment PRIMARY KEY first; 

列

更新列

更新列的定义可通过 CHANGE 或 MODIFY。区别仅在于后者不需要重新指定一个新的一列名。

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;

ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;

添加列

ALTER TABLE <table_name> ADD COLUMN tempID int NULL DEFAULT 3;

添加列到指定位置

默认会添加到表中最后一列，可通过 AFTER 来控制其位置。比如，将新增的列添加到 ID 列后面。

ALTER TABLE <table_name> ADD COLUMN tempID int NULL DEFAULT 3 AFTER ID;

添加列到首位

如果想添加列到首位，通过 AFTER 就无法实现了，对应的也不是 BEFORE，而是使用 FIRST。

ALTER TABLE user 
  ADD COLUMN id INT NOT NULL auto_increment PRIMARY KEY first; 

删除列

ALTER TABLE <table_name> DROP COLUMN <column_name>;

一次删除多列，

ALTER TABLE table
DROP COLUMN column_1,
DROP COLUMN column_2,
…;

记录

插入记录

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

一次插入多条记录：

INSERT INTO table(c1,c2,...)
VALUES 
   (v11,v12,...),
   (v21,v22,...),
    ...
   (vnn,vn2,...);

删除记录

删除满足条件的列：

DELETE FROM `table_name` [WHERE condition];

如果是删除某列其值介于一个区间，比如 id 在 50~100 的记录，可以这样：

DELETE FROM tablename WHERE id BETWEEN 50 AND 100;

需要删除的记录，其某列的值，在一个可选的列表中，可以将 WHERE 搭配 IN 关键词：

DELETE FROM tablename WHERE id IN (1,2,3,...,254);

像上面范围命中时，还可配合 <> 不等于来进一步控制：

DELETE FROM tablename WHERE id BETWEEN 1 AND 254 AND id<>10;

范围的情况常用的场景是命中一个日期区间的记录：

DELETE FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

处理日期区间时除了上面的 BETWEEN AND，还可通过大于小于运算符来进行：

DELETE FROM `objects`
WHERE (date_field <= '2010-01-30 14:15:55' AND date_field >= '2010-09-29 10:15:55')

更新记录

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
[WHERE
    condition];

示例：

UPDATE employees 
SET 
    lastname = 'Hill',
    email = 'mary.hill@classicmodelcars.com'
WHERE
    employeeNumber = 1056;

外键

创建外键

外键可在创建表时指定，通过 FOREIGN KEY 关键字。

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

示例：

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

对于已经存在的表，使用 ALTER TABLE 配合 ADD FOREIGN KEY 来完成。

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;

示例：

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

创建的同时可指定一个外键名，否则 MySQL 会自己生成一个。这个名字在删除时有用。

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

外键删除

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

创建并查看表的关系图

有一样是命令行无法进行的，那就是生成数据库整体的表关系图(EER 图)。这个需要借助文章开头提到的 GUI 工具 MySQL Workbench。

方法是从菜单中 database -> Reverse Engineer... 进入，然后跟着向导一步步直到结束，便创建好了相应数据库的表间关系图。

MySQL Reverse Engineer 菜单

生成结果示例：

MySQL EER 图