关键词：MySQL；数据库操作；DDL；DML
一、引言
MySQL 作为广泛应用的关系型数据库管理系统，对于开发人员和数据库管理员而言，熟练掌握其操作至关重要。本文章通过一系列 SQL 示例，详细阐述 MySQL 数据库的常用操作。
参考资料：黑马程序员
二、数据库的查询与创建
2.1 查询所有数据库
使用 SHOW DATABASES; 语句可查询当前 MySQL 实例中的所有数据库。此语句返回一个列表，包含已存在的数据库名称。例如：
SHOW DATABASES;
2.2 创建数据库
通过 CREATE DATABASE 语句创建新的数据库。例如，创建名为 db02 的数据库：
CREATE DATABASE if not extists db02;
if not exists ：数据库不存在,则创建该数据库；如果存在则不创建
2.3 使用数据库
use db01;
三、表结构定义（DDL - Data Definition Language）
3.1 创建表 - 基本语法
使用 CREATE TABLE 语句创建表，并定义表中的列及其属性。以下示例创建一个名为 tb_user 的表，包含 id、username、name、age 和 gender 列：
CREATE TABLE tb_user (id INT COMMENT 'ID, 唯一标识',username VARCHAR(20) COMMENT '用户名',name VARCHAR(10) COMMENT '姓名',age INT COMMENT '年龄',gender CHAR(1) COMMENT '性别'
) COMMENT '用户表';
数值类型
| 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
|---|---|---|---|---|
| TINYINT | 1byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2bytes | (-32768，32767) | (0，65535) | 大整数值 |
| MEDIUMINT | 3bytes | (-8388608，8388607) | (0，16777215) | 大整数值 |
| INT/INTEGER | 4bytes | (-2147483648，2147483647) | (0，4294967295) | 大整数值 |
| BIGINT | 8bytes | (-2^63，2^63-1) | (0，2^64-1) | 极大整数值 |
| FLOAT | 4bytes | (-3.402823466 E+38，3.402823466351 E+38) | 0 和 (1.175494351 E-38，3.402823466 E+38) | 单精度浮点数值 |
| DOUBLE | 8bytes | (-1.7976931348623157 E+308，1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308) | 双精度浮点数值 |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
字符串类型
| 类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串(需要指定长度) |
| VARCHAR | 0-65535 bytes | 变长字符串(需要指定长度) |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
3.2 创建表 - 基本语法（含约束）
在创建表时，可以为列添加约束条件，以确保数据的完整性。例如，为 tb_user 表添加主键、非空、唯一和默认值约束：
在MySQL数据库当中，提供了以下5种约束：
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段值不能为null | not null |
| 唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 | default |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | foreign key |
CREATE TABLE tb_user (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID, 唯一标识',username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',name VARCHAR(10) NOT NULL COMMENT '姓名',age INT COMMENT '年龄',gender CHAR(1) DEFAULT '男' COMMENT '性别'
) COMMENT '用户表';
3.3 创建表 tb_emp
除了纯 SQL 语句创建表，也可借助图形化工具创建表。以下为通过 SQL 语句创建 tb_emp 表，包含主键、唯一约束、默认值等多种约束：
CREATE TABLE tb_emp (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',username VARCHAR(20) NOT NULL COMMENT '用户名',password VARCHAR(32) DEFAULT '123456' NULL COMMENT '密码',name VARCHAR(10) NOT NULL COMMENT '姓名',gender TINYINT UNSIGNED NOT NULL COMMENT '性别, 1 男, 2 女',image VARCHAR(300) NULL COMMENT '图像url',job TINYINT UNSIGNED NULL COMMENT '职位, 1 班主任, 2 讲师, 3 学工主管, 4 教研主管',entrydate DATE NULL COMMENT '入职日期',create_time DATETIME NOT NULL COMMENT '创建时间',update_time DATETIME NOT NULL COMMENT '修改时间',CONSTRAINT tb_emp_username_uindex UNIQUE (username)
) COMMENT '员工表';
3.4 查看表结构
- 查看当前数据库下的表：使用
SHOW TABLES;语句查看当前数据库中所有表的名称：
SHOW TABLES;
- 查看指定表结构：通过
DESC语句查看指定表的详细结构，包括列名、数据类型、是否允许为空等信息。例如，查看tb_emp表结构：
DESC tb_emp;
- 查看数据库的建表语句：使用
SHOW CREATE TABLE语句查看创建指定表的完整 SQL 语句，包括表的所有定义和约束。例如，查看tb_emp表的建表语句：
SHOW CREATE TABLE tb_emp;
3.5 修改表结构
- 添加字段：使用
ALTER TABLE...ADD语句为表添加新字段。例如，为tb_emp表添加qq字段，类型为VARCHAR(11)：
ALTER TABLE tb_emp ADD qq VARCHAR(11) COMMENT 'QQ';
- 修改字段类型：使用
ALTER TABLE...MODIFY语句修改表中已有字段的类型。例如，将tb_emp表中qq字段的类型修改为VARCHAR(13)并命名为qq_num：
ALTER TABLE tb_emp MODIFY qq_num VARCHAR(13) COMMENT 'QQ';
- 修改字段名及类型：使用
ALTER TABLE...CHANGE语句修改字段名及类型。例如，将tb_emp表中qq字段修改为qq_num，类型为VARCHAR(13)：
ALTER TABLE tb_emp CHANGE qq qq_num VARCHAR(13) COMMENT 'QQ';
- 删除字段：使用
ALTER TABLE...DROP COLUMN语句删除表中的字段。例如，删除tb_emp表中的qq_num字段：
ALTER TABLE tb_emp DROP COLUMN qq_num;
- 修改表名：使用
RENAME TABLE语句修改表的名称。例如，将tb_emp表名修改为emp：
RENAME TABLE tb_emp TO emp;
3.6 删除表结构
使用 DROP TABLE 语句删除表。为避免删除不存在的表导致错误，可使用 IF EXISTS 子句。例如，删除 tb_emp 表：
DROP TABLE IF EXISTS tb_emp;
四、数据操作语言（DML - Data Manipulation Language）
4.1 插入数据 - INSERT
- 为指定字段插入值：使用
INSERT INTO...VALUES语句为表中的指定字段插入数据。例如，为tb_emp表的username、name、gender、create_time和update_time字段插入值：
INSERT INTO tb_emp (username, name, gender, create_time, update_time) VALUES ('wuji', '张无忌', 1, NOW(), NOW());
- 为所有字段插入值：为表中的所有字段插入数据时，需按表定义的字段顺序提供值。例如：
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time)
VALUES (NULL, 'zhiruo', '123', '周芷若', 2, '1.jpg', 1, '2010 - 01 - 01', NOW(), NOW());
-- 或者：
INSERT INTO tb_emp VALUES (NULL, 'zhiruo2', '123', '周芷若', 2, '1.jpg', 1, '2010 - 01 - 01', NOW(), NOW());
- 批量插入数据：可一次性为表插入多条记录。例如，批量为
tb_emp表的username、name、gender、create_time和update_time字段插入数据：
INSERT INTO tb_emp (username, name, gender, create_time, update_time) VALUES
('weifuwang', '韦一笑', 1, NOW(), NOW()), ('xieshiwang', '谢逊', 1, NOW(), NOW());
4.2 更新数据 - UPDATE
- 更新单条记录：使用
UPDATE...SET语句更新表中的数据，并通过WHERE子句指定更新条件。例如，将tb_emp表中ID为1的员工姓名更新为张三并更新update_time：
UPDATE tb_emp SET name = '张三', update_time = NOW() WHERE id = 1;
- 更新多条记录：若不指定
WHERE子句，则会更新表中的所有记录。例如，将tb_emp表中所有员工的入职日期更新为2010 - 01 - 01并更新update_time：
UPDATE tb_emp SET entrydate = '2010 - 01 - 01', update_time = NOW();
4.3 删除数据 - DELETE
- 删除单条记录：使用
DELETE FROM...WHERE语句删除表中的数据，并通过WHERE子句指定删除条件。例如，删除tb_emp表中ID为1的员工：
DELETE FROM tb_emp WHERE id = 1;
- 删除所有记录：若不指定
WHERE子句，则会删除表中的所有记录。例如，删除tb_emp表中的所有员工：
DELETE FROM tb_emp;
