/ 编程

MySQL 备忘

记录 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 BYHAVING 字据。

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的零距离接触