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