MySQL 基础操作全解析：从建表到增删改查 工作中的 速查手册

一、创建数据表

1. 基础建表

CREATE TABLE users (  
  id INT COMMENT '字段唯一值',
  name VARCHAR(50) COMMENT '用户名, varchar(50) -> 代表字段最大长度为50个字符',
  age INT COMMENT '年龄',
  gender CHAR(1) COMMENT '性别, char(1) -> 代表字段最大长度为1个字符, 1个字符代表一个字节'
) COMMENT '测试用户表';

👉 要点解析：

• VARCHAR(n)：可变长度字符串，最大长度为 n。

• CHAR(n)：定长字符串，占用固定长度。

• COMMENT：添加字段或表的注释，便于维护。

---

2. 插入数据

INSERT INTO users VALUES (1, '张三', 18, '男');
INSERT INTO users VALUES (2, '张三', 18, '男');
INSERT INTO users VALUES (3, '李四', 19, '女');

---

3. 带约束的建表

CREATE TABLE users (
  id INT PRIMARY KEY COMMENT '字段 primary key -> 主键',
  name VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名, not null -> 不可为空, unique -> 唯一值',
  age INT COMMENT '年龄',
  gender CHAR(1) DEFAULT '男' COMMENT '性别, default ‘男’ -> 默认设置为 男'
) COMMENT '测试用户表';

👉 关键约束：

• PRIMARY KEY：唯一标识一行数据。

• NOT NULL：字段值不能为空。

• UNIQUE：字段值唯一。

• DEFAULT：设定默认值。

---

二、员工表实例

我们在 db01 数据库中新建一个 员工表 emp。

USE db01;

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'id 主键唯一值，自增',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名，20个字符，必填',
    name VARCHAR(10) NOT NULL COMMENT '姓名，10个字符，必填',
    sex CHAR(1) DEFAULT '1' COMMENT '性别 1-男；2-女',
    phone VARCHAR(11) UNIQUE NOT NULL COMMENT '手机号，11位，唯一',
    image VARCHAR(255) COMMENT '员工头像',
    time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间，默认当前时间',
    job VARCHAR(20) COMMENT '职位',
    salary DOUBLE COMMENT '薪资 double 型',
    age INT UNSIGNED NOT NULL COMMENT '年龄 无负数，必填'
) COMMENT '员工表';

插入数据

INSERT INTO emp VALUES (NULL, 'zhangsan', '张三', '1', '12345678901', '张三.jpg', '2020-01-01 00:00:00', '程序员', 5000, 18);
INSERT INTO emp VALUES (NULL, 'lisi', '李四', '1', '22343333901', '李四.jpg', '2020-01-01 00:00:00', NULL, 299, 20);

---

三、表结构操作

1. 查询表结构

SHOW TABLES;        -- 查询当前数据库的所有表
SHOW CREATE TABLE emp; -- 查看建表语句
DESC emp;           -- 查看表字段信息

2. 修改表结构

ALTER TABLE emp ADD COLUMN salary DOUBLE COMMENT '新增字段';
ALTER TABLE emp MODIFY COLUMN salary INT NOT NULL COMMENT '修改字段类型';
ALTER TABLE emp CHANGE COLUMN new_salary salary DOUBLE COMMENT '修改字段名';
ALTER TABLE emp DROP COLUMN salary;  -- 删除字段
ALTER TABLE employee RENAME TO emp; -- 修改表名
DROP TABLE users;                   -- 删除表

---

四、数据操作

1. 更新数据

UPDATE emp SET salary = 555 WHERE id = 1;
UPDATE emp SET salary = 555;  -- 批量更新

2. 删除数据

DELETE FROM emp WHERE id = 1;

---

五、查询数据

1. 基础查询

SELECT * FROM emp;  
SELECT id, username, phone FROM emp;
SELECT id AS id_num, username AS name FROM emp;

2. 去重查询

SELECT DISTINCT sex FROM emp;

3. 条件查询

SELECT * FROM emp WHERE name = 'wangwu';
SELECT * FROM emp WHERE age < 18;
SELECT * FROM emp WHERE job IS NULL;
SELECT * FROM emp WHERE salary != 555;
SELECT * FROM emp WHERE salary BETWEEN 500 AND 1000;
SELECT * FROM emp WHERE salary IN (500, 1000);

4. 模糊查询

SELECT * FROM emp WHERE name LIKE '%张三%';  -- 包含
SELECT * FROM emp WHERE name LIKE '张三%';   -- 以张三开头
SELECT * FROM emp WHERE name LIKE '张三_';   -- 张三 + 1个字

5. 排序与分页

SELECT * FROM emp ORDER BY salary DESC;          -- 降序
SELECT * FROM emp ORDER BY salary ASC;           -- 升序
SELECT * FROM emp ORDER BY salary DESC LIMIT 3;  -- 取前3条
SELECT * FROM emp ORDER BY salary DESC LIMIT 3 OFFSET 0;

---

六、聚合与分组

聚合函数

SELECT COUNT(*) FROM emp;   -- 总数
SELECT SUM(salary) FROM emp; -- 求和
SELECT AVG(salary) FROM emp; -- 平均值
SELECT MAX(salary) FROM emp; -- 最大值
SELECT MIN(salary) FROM emp; -- 最小值

分组查询

SELECT sex, COUNT(*) FROM emp GROUP BY sex;

having 过滤分组

SELECT salary, COUNT(*) 
FROM emp 
WHERE time < '2021-01-01' 
GROUP BY salary 
HAVING salary > 20;

👉 区别：

• WHERE：分组前过滤。

• HAVING：分组后过滤。

---

七、总结

本文涵盖了 MySQL 中的常见操作：

1. 建表：字段、注释、约束。

2. 表结构操作：增、删、改、查。

3. 数据操作：增删改查、模糊查询、排序、分页。

4. 聚合与分组：COUNT、SUM、AVG、MAX、MIN。

5. WHERE 与 HAVING 区别。