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;

其中 -ppasswordpassword 为你的密码,和 -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; 

更新列

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

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 Reverse Engineer 菜单

生成结果示例:

MySQL EER 图

MySQL EER 图

相关资源