MySQL 备忘 - Wiki | Tsong khapa

记录 MySQL 数据库、数据表、约束等基础内容，以及数据的增删查改方法。

操作数据库

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

例如，创建默认 utf8 编码的数据库 db_test

CREATE DATABASE db_test CHARACTER SET gbk;

修改数据库

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;

例如，修改数据库 db_test 的编码格式

ALTER DATABASE db_test CHARACTER SET utf8;

删除数据库

DROP {DATABASE| SCHEMA} [IF EXISTS] db_name;

MySQL 数据类型

三种主要的类型：文本、数字和日期/时间类型。

文本类型有 CHAR, VARCHAR, TEXT 等
数字类型有 SMALLINT, INT, FLOAT, DOUBLE 等，通常有 UNSIGNED 选项，即 UNSIGNED INT 等
时间类型有 DATE, DATETIME, TIMESTAMP 等

具体可参考 SQL 数据类型的 MySQL 部分。

操作数据表

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type,
    ...
)

例子

CREATE TABLE table_test (
    username VARCHAR(20),
    age TINYINT UNSIGNED,
    salary FLOAT(8,2) UNSIGNED,
);

查看数据表

查看有哪些数据表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

查看指定数据表信息

SHOW CREATE TABLE table_name;
SHOW COLUMNS FROM table_name;

修改数据表

添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name];

例子：

ALTER TABLE user1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;

ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST;

添加多列语

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)

多了()小括号，要添加的各个列用逗号分开，不能指定要添加的列的位置。

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name;

例子

ALTER TABLE user1 DROP truename, DROP password;

可以在删除一列的同时，添加一列

ALTER TABLE user1 DROP age, ADD sex ENUM('1', '2', '3') NOT NULL;

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name];

把 id 字段改成第一列，列的类型没有变
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];
使用 CHANGE 关键字也可以修改列定义

修改表名称

方法1

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;

方法2

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2]
方法2 可以一次给多个表重命名

尽量少修改表名、列名。

约束

主键约束

每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为 NOT NULL

例子：

CREATE TABLE table_test3 (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY，
    username VARCHAR(20) NOT NULL
);

给 id 字段添加了 PRIMARY KEY，指定该字段为主键。

其中 AUTO_INCREMENT 表示自动编号，必须与主键组合使用。

唯一约束

保证记录的唯一性
字段可以为空
可以存在多个唯一约束

例子

CREATE TABLE table_test5 (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY,
    age TINYINT UNSIGNED
);

id 字段为自动编号的主键，username 字段有唯一约束，并且有 NOT NULL 指定字段值不能为空。

默认约束（默认值）

插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

CREATE TABLE table_test6 (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY,
    sex ENUM('1', '2', '3') DEFAULT '3'
);

插入记录时如果没有指定 sex 字段，默认值为 3。

外键约束

使用外键的要求

父表和字表必须使用相同的存储引擎，必须是 InnoDB，而且禁止使用临时表
外键列和参照列必须具有相似的数据类型。
- 数字长度，是否有符号位，必须相同
- 字符的长度可以不同
外键列和参考列必须有索引。
- 如果外键列不存在索引的话，MySQL 将自动创建索引。
- 如果要把一个没有索引的列为参考列，命令会失败。

例子

CREATE TABLE province (
    id SMALLINT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pname VARCHAR(20) NOT NULL
);

CREATE TABLE user (
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) REFERENCES province (id)
);

参照列是 province 表的 id 字段，查看索引的方法：

SHOW INDEXES FROM province;
SHOW INDEXES FROM province\G;

删除或更新父表时，子表的操作：

CASCADE
SET NULL
RESTRICT
NO ACTION

例子

CREATE TABLE user (
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) REFERENCES province (id) ON DELETE CASCADE
);

CASCADE 在父表删除或更新记录时，同时也删除或更新子表相应的行。
SET NULL 在附表删除或更新记录时，会设置子表的外键列为 NULL。
RESTRICT 拒绝对父表的删除或更新操作
NO ACTION 标准 SQL 关键字，在 MySQL 中与 RESTRICT 意义相同。

修改表的约束

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

[CONSTRAINT [symbol]] 可省略，可以通过 symbol 来给约束命名。

CREATE TABLE user2 (
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED
);
ALTER TABLE user2 ADD id SMALLINT UNSIGNED;
ALTER TABLE user2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);

index_type 指定索引的类型（创建主键默认生成索引），默认是 BTree 索引。

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY|] [index_name] [index_type] (index_col_name,...);

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition;

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES province (id);

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY;

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;

SHOW INDEXES FROM users2\G;
查看 users2 约束的名字，以 username 为例
ALTER TABLE users2 DROP INDEX username

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

数据的增删查改

INSERT 语句

插入数据

INSERT [INTO] tbl_name [(col_name,...)] {VALUES|VALUE} （{expr | DEFAULT},...),(...),...

CREATE TABLE users (
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);

INSERT users VALUES(NULL, 'Tom', '123', 25, 1);
INSERT users VALUES(DEFAULT, 'Tom', '123', DEFAULT, 1);
INSERT users VALUES(DEFAULT, 'Tom', '123', DEFAULT, 1), (NULL, 'Rose', md5('123'), DEFAULT, 0);

没有写列名的话，如果不给所有字段赋值会产生错误。

INSERT SET 语句

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...

与前面的方式区别在于，这个方法可以使用子查询。一次只可以插入一条记录。

INSERT users SET username='Ben', password='456';

INSERT SELECT 语句

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
此方法把查询结果插入到指定数据表，见下面 SELECT 语句。

UPDATE 语句

修改数据

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition];

UPDATE users SET age = age + 5;
UPDATE users SET age = age - id, sex = 0;
UPDATE users SET age = age + 10 WHERE id % 2 == 0;

DELETE 语句

删除数据

DELETE FROM tbl_name [WHERE where_condition];

DELETE FROM users WHERE id = 6;

SELECT 语句

查询数据

SELECT select_expr [, select_expr...]
[
    FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name|postion} [ASC|DESC],...]
    [HAVING where_condition]
    [ORDER BY {col_name|expr|position} [ASC|DESC],...]
    [LIMIT {[offset,] row_count|row_count OFFSET offset}]
]

每一个表达式表示想要的一列，必须有至少一个。
多个列之间以英文逗号分隔。
星号()表示所有列。tbl_name. 可以表示命名表的所有列。
查询表达式可以使用 AS(alias_name) 为其赋予别名。
别名可用于 GROUP BY, ORDER BY 或 HAVING 字据。

SELECT id, username FROM users;
SELECT users.id, users.username FROM users;
SELECT id AS userId, username AS uname FROM users;

AS 关键字可以省略，但是建议不省略。

WHERE

条件表达式，对记录进行过滤，如果没有指定 WHERE 子句，则显示所有记录。在 WHERE表达式中，可以使用 MySQL 支持的函数或运算符。

GROUP BY

查询结构分组

SELECT sex FROM users GROUP BY sex;
SELECT sex FROM users GROUP BY 1;
1 是代表位置，即 SELECT 语句里所查的第一个字段，这里就是 sex

HAVING

设置分组条件

SELECT sex, age FROM users GROUP BY sex HAVING age > 30;

HAVING 子句里，分组条件要么是一个聚合函数(max, min, avg, sum, count)，要么这个字段必须出现在当前的 SELECT 语句中。

ORDER BY

对查询结果进行排序

SELECT * FROM users ORDER BY id DESC;
SELECT * FROM users ORDER BY age, id DESC;
先按照年龄排序，如果年龄相同，按照 id 降序排列

LIMIT

限制查询数量

SELECT * FROM users LIMIT 2;
SELECT * FROM users 3, 2;
返回第四条记录和第五条记录(3 表示第四条记录，2表示返回两条记录。第一个位置为 0)

INSERT

将 SELECT 结果插入指定数据表

CREATE TABLE test (
    id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20)
);  
INSERT test (username) SELECT username FROM users WHERE age >= 30;

杂项

配置

[mysql]
default-character-set=utf8  
[mysqld]
character-set-server=utf8  
port=3306  
datadir="/path/to/datadir"  
default-storage-engine=INNODB

命令书写规范

关键字与函数名全部大写
数据库名、表名、字段名全部小写
SQL 语句必须以分号结尾

常用命令

显示当前服务器版本 SELECT VERSION();
显示当前日期时间 SELECT NOW();
显示当前用户 SELECT USER();
显示当前数据库 SELECT DATABASE();
查看警告 SHOW WARNINGS;
查看有哪些数据库 SHOW DATABASES;
查看指定数据库信息 SHOW CREATE DATABASE db_name;

慕课网课程：与MySQL的零距离接触