MySQL下载地址

一、MySQL储存值类型

1、数值类型

• 整型

  字段	字节数	有符号范围	无符号范围	用途
  TINYINT	1	-128 ~ 127	0 ~ 255	年龄、状态、布尔值
  SMALLINT	2	-32768 ~ 32767	0 ~ 65535	小范围计数
  MEDIUMINT	3	-838万 ~ 838万	0 ~ 1677万	中等范围
  INT	4	-21亿 ~ 21亿	0 ~ 42亿	最常用：ID、数量
  BIGINT	8	-9.22×10¹⁸ ~ 9.22×10¹⁸	0 ~ 1.84×10¹⁹	超大数、时间戳
  - 浮点型

  字段	字节数	特点	用途
  FLOAT(M,D)	4	单精度，约7位精度	科学计算
  DOUBLE(M,D)	8	双精度，约15位精度	高精度科学计算
  DECIMAL(M,D)	变长	精确小数	金额专用

  金额必须用DECIMAL以保证精度，常规浮点会有精度丢失问题

  M表示总位数，N表示小数位数

CREATE TABLE products (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,      -- 非负ID
    age TINYINT UNSIGNED,                            -- 年龄 0-255
    stock MEDIUMINT,                                 -- 库存
    price DECIMAL(10,2) NOT NULL,                    -- 价格：10位总长，2位小数
    score FLOAT                                      -- 评分（允许精度误差）
);
-- DECIMAL(10,2) 表示：总位数10，小数2位
-- 范围：-99999999.99 ~ 99999999.99

2、字符类型

-- 长度固定 → CHAR
phone CHAR(11)           -- 手机号固定11位
id_card CHAR(18)         -- 身份证固定18位
sex CHAR(1)              -- 'M' / 'F'
-- 长度可变 → VARCHAR
name VARCHAR(50)         -- 名字长度不定
address VARCHAR(200)     -- 地址长度不定
-- 长度超过255 → TEXT
content TEXT             -- 文章正文

-- ENUM：单选
status ENUM('pending', 'paid', 'shipped', 'cancelled')
-- SET：多选
tags SET('技术', '娱乐', '体育', '财经')  -- 可存 '技术,体育'

3、日期类型

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_time DATETIME DEFAULT CURRENT_TIMESTAMP,           -- 下单时间
    birthday DATE,                                            -- 生日
    duration TIME,                                            -- 处理时长
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 自动更新
    create_year YEAR                                          -- 年份
);

-- 插入数据
INSERT INTO orders VALUES (
    1,
    '2024-01-15 10:30:00',
    '1990-05-20',
    '02:30:00',
    NOW(),   -- TIMESTAMP 自动赋值
    2024
);

4、二进制类型

不建议在数据库中直接存图片/文件，建议存文件路径：

-- ✅ 推荐
avatar_url VARCHAR(255)  -- 存文件路径或CDN链接

-- ❌ 不推荐（除非极小图片）
avatar BLOB

5、Json类型

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    extra JSON   -- JSON类型
);
-- 插入JSON数据
INSERT INTO users VALUES (1, '张三', '{"age": 25, "city": "北京", "hobbies": ["读书", "游泳"]}');

-- 或使用函数
INSERT INTO users VALUES (2, '李四', JSON_OBJECT('age', 30, 'city', '上海'));

查询Json数据

-- 读取JSON字段
SELECT name, extra->'$.age' AS age FROM users;
SELECT name, extra->>'$.city' AS city FROM users;  -- 去引号

-- 条件查询
SELECT * FROM users WHERE extra->>'$.city' = '北京';

-- 修改JSON
UPDATE users SET extra = JSON_SET(extra, '$.age', 26) WHERE id = 1;

6、常用业务及建表示例

CREATE TABLE IF NOT EXISTS goods(
    goods_id INT AUTO_INCREMENT PRIMARY KEY,
    seller_uid INT NOT NULL COMMENT '卖家uid',
    title VARCHAR(255) NOT NULL COMMENT '商品标题',
    description TEXT COMMENT '商品描述',
    price DECIMAL(10,2) NOT NULL COMMENT '售价',
    purchase_price DECIMAL(10,2) DEFAULT NULL COMMENT '买入价格（可选）',
    status VARCHAR(50) NOT NULL DEFAULT 'on_sale' COMMENT '状态: on_sale(在售), sold(已售), off_shelf(下架)',
    likes_count INT NOT NULL DEFAULT 0 COMMENT '想要人数',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
    FOREIGN KEY (seller_uid) REFERENCES user(uid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

• 建表时加上ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  • utf8mb4 额外支持emoji表情
  • InnoDB 是默认引擎
• COMMENT字段是注释

二、增效

• 分表

  对于一个大表，可以考虑拆分成两个表，一个表放经常被查询的，另一个放不常被查询的

• 索引

  对于常被查询,且内容差异较大的字段，添加索引

  不可忽视索引加速了查询，却拖慢了增删改，而且会占用额外的磁盘和内存空间

  java CREATE TABLE students ( id BIGINT NOT NULL AUTO_INCREMENT, class_id BIGINT NOT NULL, name VARCHAR(100), gender CHAR(1), score INT, PRIMARY KEY (id), -- 主键自动创建主键索引 INDEX idx_score (score), -- 普通索引 INDEX idx_name_score (name, score), -- 联合索引（多列） UNIQUE INDEX uni_name (name) -- 唯一索引 );

  对比维度INDEX（普通索引）联合索引（多列普通索引）UNIQUE INDEX（唯一索引）是否允许重复值✅ 允许✅ 允许（但组合值可重复）❌ 不允许（NULL 除外）加速查询对单列查快对多列组合查快，遵循最左前缀同普通索引，同时多了唯一约束是否约束数据❌ 纯加速❌ 纯加速✅ 强制数据唯一NULL 特殊处理可包含多个 NULL可包含多个 NULL可包含多个 NULL（MySQL 认为 NULL 不是值）

  • 单列索引

  ```sql
  -- 建索引
  ALTER TABLE students ADD INDEX idx_score (score);
  
  -- 能加速的查询
  SELECT * FROM students WHERE score > 80;          -- ✅ 走索引
  SELECT * FROM students ORDER BY score;            -- ✅ 避免文件排序
  SELECT * FROM students WHERE score IN (70,80,90); -- ✅ 走索引
  
  -- 不能加速
  SELECT * FROM students WHERE name = '小明';     -- ❌ name 没索引，全表扫
  ```
  

  • 联合索引

    ```sql -- 建索引 ALTER TABLE students ADD INDEX idx_name_score (name, score);

    -- ✅ 能走索引的查询（遵循最左前缀） SELECT * FROM students WHERE name = '小明'; -- 命中 name SELECT * FROM students WHERE name = '小明' AND score > 80; -- 命中两列 SELECT * FROM students WHERE name LIKE '小%' AND score > 80; -- 范围也能走

    -- ❌ 不能走索引的查询（跳过了最左列 name） SELECT * FROM students WHERE score > 80; -- 联合索引用不上 ```

  • 唯一索引

    ```sql -- 建唯一索引 ALTER TABLE students ADD UNIQUE INDEX uni_name (name);

    -- 正常插入 INSERT INTO students (name) VALUES ('小明');

    -- 再次插入同名（直接报错，阻止重复） INSERT INTO students (name) VALUES ('小明'); -- ERROR 1062: Duplicate entry '小明' for key 'uni_name' ```

三、增删查改

1、增INSERT

基本语法

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

自增字段和默认字段允许不出现

可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值，每组值用逗号,分隔：

-- 一次性添加多条新记录:
INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81),
  (3, '三宝', 'M', 83);

• 忽略插入

  使用IGNORE 实现记录已存在时直接忽略

  sql INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

• 更新插入

  ON DUPLICATE KEY UPDATE 语句实现记录已存在时则更新，依据唯一键或主键判断冲突

  sql INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99; -- name, gender, score 更新为指定的新值 -- class_id 不变

2、删DELETE

在生产环境，永远不要想着去删主键约束。如果想修改主键字段或排序规则，应该通过新建表 → 迁移数据 → 重命名的方式平滑切换，而不是直接 DROP PRIMARY KEY

• DELETE

删除表中**部分或全部数据**，表结构还在，可回滚

基本语法

```sql
-- 删除单条数据
DELETE FROM <表名> WHERE id=5;
-- 删除id=5,6,7的记录:
DELETE FROM students WHERE id>=5 AND id<=7;

• 如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，无事发生
• 不带WHERE条件的DELETE语句会删除整个表的数据，表还在。为此，建议先SELECT测试WHERE条件是否筛选出期望的记录集，然后再DELETE
• 自增计数器不会重置（如果删了所有数据，下次 INSERT 的 ID 会继续之前的序号）

- **`DROP`**

    ***删除整个表，表彻底消失，不能回滚***

    ```sql
    -- 表完全消失，数据、结构、索引、约束全没
    DROP TABLE students;
    ```

    执行后**立即自动提交，无法回滚**

- **`TRUNCATE`**
清空所有数据，但保留表结构

    ```sql
    TRUNCATE TABLE students;
    ```

    - 比 `DELETE FROM` 逐行删高效
    - 重置自增计数器
    - 无法回滚，不能加 WHERE 条件

### 3、查`SELECT`

基础语法

```sql
-- *表示所有列
SELECT * FROM <表名>
-- 也可指定列(投影)
SELECT 列1, 列2, 列3 FROM <表名>

查询结果是一个二维表，包含列名和每行的数据

条件查询

在查询语句之后加WHERE字段，后接布尔语句

SELECT * FROM <表名> WHERE <条件语句>

支持AND,OR ,NOT ,() 组合条件

优先级按照NOT、AND、OR

-- 按AND条件查询students:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
-- 按OR条件查询students:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
-- 按NOT条件查询students:
SELECT * FROM students WHERE NOT class_id = 2;  -- 班级不是2的学生
-- NOT也等价于<>
SELECT * FROM students WHERE class_id <> 2;  -- 班级不是2的学生

更灵活的条件

查询分数在60分(含)～90分(含)之间的学生可用

• [ ] WHERE score >= 60 OR score <= 90 错误❌
• [x] WHERE score >= 60 AND score <= 90
• [x] WHERE score IN (60, 90)
• [x] WHERE score BETWEEN 60 AND 90
• [ ] WHERE 60 <= score <= 90 错误❌

更多操作

• 排序ORDER

  SELECT查询时，结果集通常按照id排序

  ORDER BY 语句允许按照其他排序，DESC 表示倒序，与ASC升序相对

  sql -- 按score从低到高: SELECT id, name, gender, score FROM students ORDER BY score; -- 按score从高到低: SELECT id, name, gender, score FROM students ORDER BY score DESC;

  ORDER允许多列排序

  sql -- 先按score, 再按gender排序: SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

  如果存在WHERE子句，那么ORDER BY子句要放在WHERE之后

• 跳过LIMIT

  LIMIT <N-M> OFFSET <M> 实现查询库里的 M+1到 N **条，即表示从M+1开始往后数N-M条

  ```sql SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 3; -- 查询4,5,6

  SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 6; -- 查询7,8,9 -- 也可简写为 LIMIT 6,3 ```

  • 如果起始点OFFSET设置的大于总字段数，不报错，返回一个空的结果集
  • N越大，查询效率越低，因此适用于小范围
  • 聚合

  对于统计总数、平均数等，常用聚合函数进行查询，可以高效获得结果

  基础语法

  sql -- 聚合查询并设置结果集的列名为num: SELECT COUNT(*) num FROM students; -- 也可加条件 SELECT COUNT(*) boys FROM students WHERE gender = 'M';

  聚合函数	说明	是否限定为数值	若WHERE条件没有匹配到任何行
  COUNT	计算某列的总行数，考虑哪一列失去了意义，所以直接*	否	返回 0
  SUM	计算某列的合计值	是	返回NULL
  MIN	计算某列的最小值	否	返回NULL
  MAX	计算某列的最大值	否	返回NULL
  AVG	计算某列的平均值	是	返回NULL

  sql -- 使用聚合查询计算男生平均成绩: SELECT AVG(score) average FROM students WHERE gender = 'M';

• 分组GROUP

  考虑SELECT COUNT(*) num FROM students WHERE class_id = 1; 语句

  无法自动统计1班以外的班，为此，使用分组聚合

  sql -- 按class_id分组: SELECT COUNT(*) num FROM students GROUP BY class_id;

  该语句COUNT()的结果为不同class_id的个数，GROUP BY子句指定了按class_id分组

  常用加入分组依据字段如SELECT一并查询，方便辨析聚合结果来自哪一条

  sql -- 按class_id分组: SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

  该语句执行结果类似：

  class_id	num
  1	4
  2	3
  3	3

  也可以多列分组

  sql -- 按class_id, gender分组: SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

  上述语句先按照class_id再按照gender

  得到结果类似

  class_id	gender	num
  1	M	2
  1	F	2
  2	F	1
  2	M	2
  3	F	2
  3	M	1
  - 多表查询

  基础语法

  sql SELECT * FROM <表1> <表2>

  对于语句

  sql SELECT * FROM students, classes

  结果类似

  id	class_id	name	gender	score	id	name
  1	1	小明	M	90	1	一班
  1	1	小明	M	90	2	二班
  1	1	小明	M	90	3	三班
  1	1	小明	M	90	4	四班
  2	1	小红	F	95	1	一班
  2	1	小红	F	95	2	二班
  2	1	小红	F	95	3	三班
  2	1	小红	F	95	4	四班
  3	1	小军	M	88	1	一班
  3	1	小军	M	88	2	二班
  3	1	小军	M	88	3	三班
  3	1	小军	M	88	4	四班
  4	1	小米	F	73	1	一班
  4	1	小米	F	73	2	二班
  4	1	小米	F	73	3	三班
  4	1	小米	F	73	4	四班
  5	2	小白	F	81	1	一班
  5	2	小白	F	81	2	二班
  5	2	小白	F	81	3	三班
  5	2	小白	F	81	4	四班
  6	2	小兵	M	55	1	一班
  6	2	小兵	M	55	2	二班
  6	2	小兵	M	55	3	三班
  6	2	小兵	M	55	4	四班
  7	2	小林	M	85	1	一班
  7	2	小林	M	85	2	二班
  7	2	小林	M	85	3	三班
  7	2	小林	M	85	4	四班
  8	3	小新	F	91	1	一班
  8	3	小新	F	91	2	二班
  8	3	小新	F	91	3	三班
  8	3	小新	F	91	4	四班
  9	3	小王	M	89	1	一班
  9	3	小王	M	89	2	二班
  9	3	小王	M	89	3	三班
  9	3	小王	M	89	4	四班
  10	3	小丽	F	88	1	一班
  10	3	小丽	F	88	2	二班
  10	3	小丽	F	88	3	三班
  10	3	小丽	F	88	4	四班

  可见结果非常夸张！先罗列表一的列，在后面追加表二的列。实则得到的结果是两个表的笛卡尔积

  多表可使用别名投影查询，也可条件

  优化SQL语句：

  sql SELECT s.id sid, -- 引用表的别名,并给字段附上别名sid s.name, s.gender, s.score, c.id cid, c.name cname FROM students s, classes c -- 给表起简洁的别名s和c WHERE s.gender = 'M' AND c.id = 1; -- 加条件

  得到结果类似

  sid	name	gender	score	cid	cname
  1	小明	M	90	1	一班
  3	小军	M	88	1	一班
  6	小兵	M	55	1	一班
  7	小林	M	85	1	一班
  9	小王	M	89	1	一班
  - 联合查询JOIN
  - 内连接INNER JOIN

  ```sql
  SELECT
  s.id,
  s.name,
  s.class_id,
  c.name class_name,   -- 引用班级表name字段并起别名class_name
  s.gender,
  s.score
  FROM students s
   -- 引入班级表，设置条件s.class_id = c.id
  INNER JOIN classes c ON s.class_id = c.id;
  ```
  
  该语句原则上等价于
  
  ```sql
  SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
  FROM students s, classes c
  WHERE s.class_id = c.id;
  ```
  
  但可读性，约定性，规范性上都建议用专门的`INNER JOIN`
  
  结果类似
  
  | 1 | 小明 | 1 | 一班 | M | 90 |
  | --- | --- | --- | --- | --- | --- |
  | 2 | 小红 | 1 | 一班 | F | 95 |
  | 3 | 小军 | 1 | 一班 | M | 88 |
  | 4 | 小米 | 1 | 一班 | F | 73 |
  | 5 | 小白 | 2 | 二班 | F | 81 |
  | 6 | 小兵 | 2 | 二班 | M | 55 |
  | 7 | 小林 | 2 | 二班 | M | 85 |
  | 8 | 小新 | 3 | 三班 | F | 91 |
  | 9 | 小王 | 3 | 三班 | M | 89 |
  | 10 | 小丽 | 3 | 三班 | F | 88 |
  

  • 外链接

    考虑到两个表的ON条件不一定全满足，例如学生表的classid只有1，2，3。而班级表的id存在1，2，3，4。这种情况使用INNER JOIN，只会返回1，2，3。即严格共有字段。

    外链接提供另一种查询方式

    RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

    外链接分为左，右和全。LEFT OUTER JOIN 完全同理。

    使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

• INNER JOIN

  !inner-join.jpg

  inner-join.jpg

• LEFT OUTER JOIN

  !left-outer-join.jpg

  left-outer-join.jpg

• RIGHT OUTER JOIN

  !right-outer-join.jpg

  right-outer-join.jpg

• FULL OUTER JOIN

  !full-outer-join.jpg

  full-outer-join.jpg

4、改UPDATE

基础语法

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

-- 更新id=1的记录:
UPDATE students SET name='大牛', score=66 WHERE id=1;
-- 更新id=5,6,7的记录:
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
-- 更新score<80的记录:
UPDATE students SET score=score+10 WHERE score<80;  -- 自操作

如果WHERE条件没有匹配到任何记录，UPDATE 就当无事发生

如果没有WHERE，会使得该列所有值修改！

为此，最好先用SELECT语句测试WHERE条件是否筛选出期望的记录集，然后再UPDATE

四、管理MySQL

常用命令

-- =====数据库层=====
-- 建库
CREATE DATABASE test;
-- 删库（危险，不可回滚）
DROP DATABASE test;
-- 切换当前操作的库
USE test;
-- 查看所有数据库
SHOW DATABASES;

-- =====表层=====
-- 查看当前库所有表
SHOW TABLES;
-- 查看表结构（字段、类型、是否可空等）
DESC students;
-- 查看建表完整 SQL（含引擎、字符集等）
SHOW CREATE TABLE students;
-- 添加列
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
-- 删除列
ALTER TABLE students DROP COLUMN birth;
-- 修改列类型
ALTER TABLE students MODIFY COLUMN birth DATE;
-- 添加索引
ALTER TABLE students ADD INDEX idx_score (score);
-- 删除索引
ALTER TABLE students DROP INDEX idx_score;
-- 删除表（数据和结构全没）
DROP TABLE students;
-- 清空表数据，保留结构
TRUNCATE TABLE students;

-- =====数据层=====
-- 查
SELECT * FROM students WHERE score > 80;
-- 增
INSERT INTO students (name, score) VALUES ('小明', 90);
-- 改
UPDATE students SET score = 95 WHERE id = 1;
-- 删
DELETE FROM students WHERE id = 5;

-- =====常用工具命令=====
-- 查看当前在哪个库
SELECT DATABASE();
-- 查看当前 MySQL 版本
SELECT VERSION();
-- 退出客户端
EXIT;

五、事务ACID

当要求一组SQL操作的逻辑单元，要么全部成功，要么全部失败时，引入事务

SQL语句实例

-- 开始一段事务
START TRANSACTION;
-- ====执行逻辑====
UPDATE account SET balance = balance - 200 WHERE name = '张三';
UPDATE account SET balance = balance + 200 WHERE name = '李四';

-- 判断是否成功,用具体编程语言实现

-- 执行回滚
ROLLBACK;

-- 执行提交
COMMIT;

python实现

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='123456', database='test')

try:
    # 开始事务（自动关闭自动提交）
    conn.autocommit = False
    cursor = conn.cursor()

    # 执行一组SQL
    cursor.execute("UPDATE account SET balance = balance - 200 WHERE name = '张三'")
    cursor.execute("UPDATE account SET balance = balance + 200 WHERE name = '李四'")

    # 手动提交
    conn.commit()
    print("转账成功")

except Exception as e:
    # 发生错误，回滚
    conn.rollback()
    print(f"转账失败，已回滚:{e}")

finally:
    cursor.close()
    conn.close()

Java实现

Connection conn = null;
try {
    conn = DriverManager.getConnection(url, user, password);
    conn.setAutoCommit(false);  // 关闭自动提交，开启事务

    Statement stmt = conn.createStatement();
    stmt.executeUpdate("UPDATE account SET balance = balance - 200 WHERE name = '张三'");
    stmt.executeUpdate("UPDATE account SET balance = balance + 200 WHERE name = '李四'");

    conn.commit();  // 提交
    System.out.println("转账成功");
} catch (SQLException e) {
    if (conn != null) {
        conn.rollback();  // 回滚
    }
    e.printStackTrace();
}

隔离机制

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	✓	✓	✓	最高
READ COMMITTED	✗	✓	✓	较高
REPEATABLE READ（MySQL默认）	✗	✗	可能	中等
SERIALIZABLE	✗	✗	✗	最低

MySQL默认是 REPEATABLE READ，常用 READ COMMITTED 需显式设置

1. 代码使用 try-catch
2. 事务开始手动 START
3. 无报错后 COMMIT
4. 报错 ROLLBACK
5. 事务尽量短小，不要在里面做复杂计算或等待用户输入

六、编程语言实现SQL的常用方法

1、上下文管理器

每次都要try-catch-finally 。。。

建议引入上下文管理器，自动管理提交，回滚，关闭

• python实现

  python @contextmanager def txn(self): """事务上下文管理器 —— 自动 commit/rollback + 归还连接。""" conn = self.connect() try: yield conn conn.commit() except Exception: conn.rollback() raise finally: conn.close()

  使用时

  ```python with self.txn() as conn: with conn.cursor() as cur: ...

  无需在catch和finally!!!

  ```

本文由 tazume-sans 原创，转载请注明出处。