记录生活
所有文章 分类 标签 60s Wallpaper Chat RSS Casual
记录生活

Contents

MySQL动手练习

 included in Other
   4058 words   9 minutes 
Beautiful landscape

Nature's Beauty

Explore the wonders of the world

参考资料

  • 鱼皮的 MySQL 学习网站：http://sqlmother.yupi.icu/
  • 在线运行 SQL：https://dbfiddle.uk
  • Python 连接 MySQL 与使用教程：https://github.com/cess-100/python-study2

练习 1：学生与班级管理

环境准备

创建并使用 python_test_1 数据库：

1
2

CREATE DATABASE python_test_1 CHARSET = utf8;
USE python_test_1;

创建学生表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

DROP TABLE IF EXISTS students;

CREATE TABLE students (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  `name` VARCHAR ( 20 ) DEFAULT '',
  age TINYINT UNSIGNED DEFAULT 0,
  height DECIMAL ( 5, 2 ),
  gender ENUM ( '男', '女', '中性', '保密' ) DEFAULT '保密',
  cls_id INT UNSIGNED DEFAULT 0,
  is_delete BIT DEFAULT 0
);

创建班级表：

1
2
3
4
5
6

DROP TABLE IF EXISTS classes;

CREATE TABLE classes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `name` VARCHAR ( 30 ) NOT NULL
);

插入数据：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

INSERT INTO students
VALUES
  ( 0, '小明', 18, 180.00, 2, 1, 0 ),
  ( 0, '小月', 18, 180.00, 2, 2, 1 ),
  ( 0, '小彭', 29, 185.00, 1, 1, 0 ),
  ( 0, '小刘', 59, 175.00, 1, 2, 1 ),
  ( 0, '小黄', 38, 160.00, 2, 1, 0 ),
  ( 0, '小凤', 28, 150.00, 4, 2, 1 ),
  ( 0, '小王', 18, 172.00, 2, 1, 1 ),
  ( 0, '小周', 36, NULL, 1, 1, 0 ),
  ( 0, '小程', 27, 181.00, 1, 2, 0 ),
  ( 0, '小菲', 25, 166.00, 2, 2, 0 ),
  ( 0, '小金', 33, 162.00, 3, 3, 1 ),
  ( 0, '小静', 12, 180.00, 2, 4, 0 ),
  ( 0, '小郭', 12, 170.00, 1, 4, 0 ),
  ( 0, '小杰', 34, 176.00, 2, 5, 0 );

INSERT INTO classes
VALUES
  ( 0, 'python_01期' ),
  ( 0, 'python_02期' );

查询练习

基本查询

  • 查询所有学生的编号和姓名
1
2
3
4
5

SELECT
  s.id AS '编号',
  s.`name` AS '姓名'
FROM
  students AS s;
  • 使用 DISTINCT 去重查询性别
1
2
3
4

SELECT DISTINCT
  gender
FROM
  students;

条件查询

比较运算符和逻辑运算符：查询指定年龄和身高范围的学生信息

1
2
3
4
5
6

SELECT
  *
FROM
  students
WHERE
  age > 18 OR height >= 180;

模糊查询：查询姓名至少有 2 个字的学生信息

1
2
3
4
5
6

SELECT
  *
FROM
  students
WHERE
  `name` LIKE '__%';

注意：% 匹配任意个字符，_ 匹配 1 个字符

范围查询：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查询年龄为 18 或 34 的学生信息
SELECT
  *
FROM
  students
WHERE
  age IN ( 18, 34 );

-- 查询年龄在 18~34 岁之间的信息（包含边界）
SELECT * FROM students WHERE age BETWEEN 18 AND 34;

判断字段是否为空：

1
2
3
4
5
6

SELECT
  *
FROM
  students
WHERE
  height IS NULL;

排序

ORDER BY 默认升序排列（ASC），降序使用 DESC。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查询 18 到 34 岁的男性
-- 身高从高到矮排序，身高相同则按年龄从小到大排序
SELECT
  *
FROM
  students
WHERE age BETWEEN 18 AND 34
  AND gender = '男'
ORDER BY
  height DESC,
  age ASC;

聚合函数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- 计数，查询总人数
SELECT
  COUNT(*) AS '总人数'
FROM
  students;

-- 查询女性最大年龄
SELECT
  MAX( age )
FROM
  students
WHERE
  gender = '女';

-- 查询男性最矮身高
SELECT
  MIN( height )
FROM
  students
WHERE
  gender = '男';

-- 年龄总和
SELECT
  SUM( age )
FROM
  students;

-- 平均年龄
SELECT AVG( age ) FROM students;
SELECT SUM( age ) / COUNT(*) FROM students;

-- 四舍五入函数，保留 2 位小数
SELECT
  ROUND( AVG( height ), 2 )
FROM
  students;

分组

统计各性别的人数：

1
2
3
4
5
6
7

SELECT
  gender,
  COUNT(*)
FROM
  students
GROUP BY
  gender;

统计各性别中最高的身高：

1
2
3
4
5
6
7

SELECT
  gender,
  MAX( height )
FROM
  students
GROUP BY
  gender;

注意：GROUP BY 需要配合聚合函数才能使用。以下语句会报错： SELECT * FROM students GROUP BY gender;
SELECT gender, name FROM students GROUP BY gender;

GROUP_CONCAT 可以对同一分组内的指定字段进行拼接：

1
2
3
4
5
6
7

SELECT
  gender,
  GROUP_CONCAT(name)
FROM
  students
GROUP BY
  gender;

HAVING 子句

HAVING 在分组后进行筛选，注意 GROUP BY 在 HAVING 前使用。

1
2
3
4
5
6
7
8
9

SELECT
  class
FROM
  Courses
GROUP BY
  class
HAVING
  COUNT( class ) >= 5
-- 分组后聚合，找到 class 数量大于 5 的

等同于使用子查询：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
  class
FROM (
  SELECT
    class,
    COUNT(student) AS s_count
  FROM Courses
  GROUP BY class
) AS t
WHERE
  t.s_count >= 5

LIMIT 限制

MySQL 中用于限制查询结果数量：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 查询前 5 条数据
SELECT * FROM students LIMIT 0, 5;
SELECT * FROM students LIMIT 5;

-- 分页查询，每页显示 b 条，显示第 a 页
-- SELECT * FROM students LIMIT b*(a-1), b;

-- 每页显示 2 条，第 1 页
SELECT * FROM students LIMIT 0, 2;

-- 每页显示 2 条，第 6 页
SELECT * FROM students LIMIT 10, 2;

表连接

将学生表和班级表根据班级 id 连接起来：

1
2
3
4
5
6

SELECT
  *
FROM
  students
INNER JOIN classes
  ON students.cls_id = classes.id;

提示：进行表连接时，推荐给表指定别名；表可以和自己连接。

子查询

  • 标量子查询
1
2
3
4

SELECT
  product_name,
  (SELECT AVG(price) FROM products) AS avg_price
FROM products;
  • 列子查询
1
2
3
4

SELECT * FROM orders
WHERE customer_id IN (
  SELECT customer_id FROM premium_customers
);
  • 行子查询
1
2
3
4
5
6
7

SELECT * FROM employees
WHERE (department, salary) = (
  SELECT department, MAX(salary)
  FROM employees
  GROUP BY department
  LIMIT 1
);

练习 2：商品管理

环境准备

创建并使用 JD 数据库：

1
2

CREATE DATABASE JD CHARSET = utf8;
USE JD;

创建商品表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

DROP TABLE IF EXISTS goods;

CREATE TABLE goods (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  `name` VARCHAR ( 150 ) NOT NULL,
  cate_name VARCHAR ( 40 ) NOT NULL,
  brand_name VARCHAR ( 40 ) NOT NULL,
  price DECIMAL ( 10, 3 ) NOT NULL DEFAULT 0,
  is_show BIT NOT NULL DEFAULT 1,
  is_saleoff BIT NOT NULL DEFAULT 0
);

插入数据：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

INSERT INTO goods
VALUES
  ( 0, 'r510vc 15.6英寸笔记本', '笔记本', '华硕', '3399', DEFAULT, DEFAULT ),
  ( 0, 'y400n 14.0英寸笔记本电脑', '笔记本', '联想', '4999', DEFAULT, DEFAULT ),
  ( 0, 'g150th 15.6英寸游戏本', '游戏本', '雷神', '8499', DEFAULT, DEFAULT ),
  ( 0, 'x550cc 15.6英寸笔记本', '笔记本', '华硕', '2799', DEFAULT, DEFAULT ),
  ( 0, 'x240 超极本', '超级本', '联想', '4880', DEFAULT, DEFAULT ),
  ( 0, 'u330p 13.3英寸超极本', '超级本', '联想', '4299', DEFAULT, DEFAULT ),
  ( 0, 'svp13226scb 触控超极本', '超级本', '索尼', '7999', DEFAULT, DEFAULT ),
  ( 0, 'ipad mini 7.9英寸平板电脑', '平板电脑', '苹果', '1998', DEFAULT, DEFAULT ),
  ( 0, 'ipad air 9.7英寸平板电脑', '平板电脑', '苹果', '3388', DEFAULT, DEFAULT ),
  ( 0, 'ipad mini 配备 retina 显示屏', '平板电脑', '苹果', '2788', DEFAULT, DEFAULT ),
  ( 0, 'ideacentre c340 20英寸一体电脑', '台式机', '联想', '3499', DEFAULT, DEFAULT ),
  ( 0, 'vostro 3800-r1206 台式电脑', '台式机', '戴尔', '2899', DEFAULT, DEFAULT ),
  ( 0, 'imac me086ch/a 21.5英寸一体电脑', '台式机', '苹果', '9188', DEFAULT, DEFAULT ),
  ( 0, 'at7-7414lp 台式电脑 linux', '台式机', '宏碁', '3699', DEFAULT, DEFAULT ),
  ( 0, 'z220sff f4f06pa工作站', '服务器/工作站', '惠普', '4288', DEFAULT, DEFAULT ),
  ( 0, 'poweredge ii服务器', '服务器/工作站', '戴尔', '5388', DEFAULT, DEFAULT ),
  ( 0, 'mac pro专业级台式电脑', '服务器/工作站', '苹果', '28888', DEFAULT, DEFAULT ),
  ( 0, 'hmz-t3w 头戴显示设备', '笔记本配件', '索尼', '6999', DEFAULT, DEFAULT ),
  ( 0, '商务双肩背包', '笔记本配件', '索尼', '99', DEFAULT, DEFAULT ),
  ( 0, 'x3250 m4机架式服务器', '服务器/工作站', 'ibm', '6888', DEFAULT, DEFAULT );

查询练习

  • 查询每种类型商品的最高价、最低价、平均价、数量
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  cate_name,
  MAX( price ),
  MIN( price ),
  AVG( price ),
  COUNT(*)
FROM
  goods
GROUP BY
  cate_name;
  • 查询每种类型中最贵的商品的所有信息
1
2
3
4
5
6
7

SELECT
  *
FROM
  goods
WHERE price IN (
  SELECT MAX( price ) FROM goods GROUP BY cate_name
);
  • 查询每种类型中最贵商品的信息
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
  *
FROM
  goods
INNER JOIN (
  SELECT cate_name, MAX( price ) AS max_price
  FROM goods
  GROUP BY cate_name
) AS max_price_goods
  ON goods.cate_name = max_price_goods.cate_name
  AND goods.price = max_price_goods.max_price;

如果只需要部分特定信息，可以不需要表连接：

1
2
3
4
5
6
7
8

SELECT
  cate_name,
  MAX( price ),
  GROUP_CONCAT( `name` )
FROM
  goods
GROUP BY
  cate_name;

新增与修改练习

将查询出的数据插入到新的商品分类表中：

1
2
3
4
5
6
7

INSERT INTO goods_cates ( `name` )
SELECT
  cate_name
FROM
  goods
GROUP BY
  cate_name;

把商品表中的 cate_name 替换成商品分类表中的商品 id：

1
2
3
4

UPDATE goods
INNER JOIN goods_cates
  ON goods.cate_name = goods_cates.NAME
SET goods.cate_name = goods_cates.id;

修改表结构，把 cate_name 改成 cate_id：

1
2

ALTER TABLE goods
CHANGE cate_name cate_id INT UNSIGNED NOT NULL;

外键约束

外键可以确保数据的完整性和一致性。

添加外键：

1
2
3
4
5

-- ALTER TABLE 表名 ADD FOREIGN KEY (外键字段) REFERENCES 表名(主键id)

ALTER TABLE goods
ADD FOREIGN KEY ( cate_id )
REFERENCES goods_cates ( id );

上述语句在 goods 表中添加外键 cate_id，与 goods_cates 表的 id 字段关联。向 goods 表插入数据时，cate_id 必须存在于 goods_cates 表的 id 中，否则会引发外键约束错误。

有时执行上述语句会报错，原因是当前数据库存在不符合规则的数据，需先删除那些数据再重新添加外键。

创建表时同时设置外键：

1
2
3
4
5
6
7
8

CREATE TABLE goods_test (
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR ( 150 ) NOT NULL,
  cate_id INT UNSIGNED NOT NULL,
  brand_id INT UNSIGNED NOT NULL,
  FOREIGN KEY ( cate_id ) REFERENCES goods_cates ( id ),
  FOREIGN KEY ( brand_id ) REFERENCES goods_brands ( id )
);

查看外键名：

1
2
3
4
5

SHOW CREATE TABLE goods_test;

-- 根据名称删除外键约束
ALTER TABLE goods_test
DROP FOREIGN KEY goods_test_ibfk_1;

视图

什么是视图

通俗地讲，视图就是一条 SELECT 语句执行后返回的结果集。

视图的特点

  • 视图是对若干张基本表的引用，是一张虚表，不存储具体数据，基本表数据改变时视图也会跟着改变
  • 视图可以隐藏敏感数据，如只显示姓名、地址而不显示社会保险号和工资等
  • 视图使复杂的查询易于理解和使用

视图的意义

程序对数据库操作时，一旦数据库发生变化，程序就需要修改。使用视图而非普通表格，可以通过只修改视图来解决这个问题。

视图操作

创建视图 v_goods_info，查询产品名称、分类名称及对应品牌：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE VIEW v_goods_info AS
  SELECT
    goods.NAME gname,
    goods_cates.NAME gcname,
    goods_brands.NAME gbname
  FROM
    goods
  INNER JOIN goods_cates
    ON goods.cate_id = goods_cates.id
  INNER JOIN goods_brands
    ON goods.brand_id = goods_brands.id;

-- SHOW TABLES 会显示表和视图

原表产品名称改变后会影响视图：

1
2
3
4

UPDATE goods
SET NAME = 'xxx'
WHERE
  id = 24;

删除视图：

1

DROP VIEW v_goods_info;

其他技巧

批量删除大量数据

假设需要根据时间范围删除大量数据：

1
2
3
4
5

-- 删除前三个月的数据
DELETE FROM
  orders
WHERE
  TIMESTAMP < SUBDATE( CURDATE(), INTERVAL 3 MONTH );

若数据量很大，该语句可能执行失败。可以按主键分批删除：

先找出前三个月最后一个订单的 id：

1
2
3
4
5
6

SELECT
  MAX( id )
FROM
  orders
WHERE
  TIMESTAMP < SUBDATE( CURDATE(), INTERVAL 3 MONTH );

然后按主键 id 分批删除：

1
2
3
4
5
6

DELETE FROM
  orders
WHERE
  id <= 【上面查询到的 id】
ORDER BY id
LIMIT 1000;

拓展与后续学习建议

  • 查询优化：深入学习 EXPLAIN 分析查询计划，了解如何通过索引优化提升查询性能
  • 事务进阶：学习 SAVEPOINT 保存点、回滚段等高级事务控制
  • 触发器：了解如何使用触发器自动执行业务逻辑
  • 数据库设计实践：通过实际项目练习数据库设计，包括 ER 图绘制、表结构规范化
  • ORM 框架：学习 SQLAlchemy 等 ORM 工具，体会对象关系映射的优势与局限
Updated on 2024-04-28
 MySQL
Back | Home