【练习】SQL专项练习（一）

最新推荐文章于 2023-09-25 13:55:28 发布

九圣残炎

最新推荐文章于 2023-09-25 13:55:28 发布

阅读量290

点赞数

分类专栏： MySQL 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/qq_40878316/article/details/131149313

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

单表查询练习

（一）数据准备

# 创建roles表（角色信息表）
CREATE TABLE roles (
    roleNo int(10) PRIMARY KEY AUTO_INCREMENT COMMENT '主键-角色信息表编号-自增',
    rolename varchar(10) COMMENT '角色姓名',
    sex varchar(4) COMMENT '角色性别',
    power varchar(20) COMMENT '角色所属阵营',
    profession varchar(10) COMMENT '角色职业',
    cost int(10) COMMENT '角色售价'
);

# 插入多条学生数据
INSERT INTO roles (rolename, sex, power, profession, cost) VALUES 
('王昭君', '女', '北夷', '法师', 8888),
('诸葛亮', '男', '蜀', '法师', 18888),
('张飞', '男', '蜀', '辅助', 13888),
('白起', '男', '秦', '坦克', 13888),
('大乔', '女', '吴', '辅助', 13888),
('孙尚香', '女', '吴', '射手', 5888),
('百里玄策', '男', '长城守卫军', '战士', 13888),
('小乔', '女', '吴', '法师', 5888),
('安琪拉', '女', '勇士之地', '法师', 0),
('百里守约', '男', '长城守卫军', '射手', 13888),
('花木兰', '女', '长城守卫军', '战士', 18888),
('钟无艳', '女', '稷下学院', '战士', 5888),
('李白', '男', '长安', '刺客', 18888),
('露娜', '男', '勇士之地', '刺客',13888),
('孙膑', '男', '稷下学院', '辅助',5888),
('东皇太一', '男', '楚汉之地', '辅助',13888),
('孙策', '男', '吴', '战士',13888),
('蔡文姬', '女', '魏', '辅助',13888),
('廉颇', '男', '稷下学院', '坦克',8888),
('成吉思汗', '男', '北夷', '射手',18888),
('吕布', '男', '魏', '战士',18888),
('鲁班七号', '男', '秦', '射手',8888),
('雅典娜', '女', '勇士之地', '战士',18888),
('公孙离', '女', '长安', '射手',13888),
('貂蝉', '女', '魏', '法师',13888),
('亚瑟', '男', '勇士之地', '战士',0);

（二）练习题目

1、查询角色"百里守约"的基本信息

2、查询角色"百里守约"或”百里玄策”的基本信息

3、查询姓"张"角色的姓名，所属阵营，职业

4、查询姓名中含有"约"字的角色的基本信息

5、查询姓名长度为三个字，姓“孙”的角色的id，姓名，性别，所属阵营和角色售价

6、查询姓"百"或者姓”孙”的角色的基本信息

7、查询姓"百"并且职业是"射手"的角色信息

8、查询阵营是"吴"、”蜀”、”魏”或者"秦"的角色的信息

9、查询姓"孙"，但是所属阵营不是"吴"的角色信息

10、查询所属阵营不是"吴"、”蜀”、”魏”、"秦"的角色信息

11、查询全部角色信息，并按照“性别”排序

12、查询现有角色都来自于哪些不同的阵营

13、查询所有男性，并按售价升序排序

14、统计共有多少个角色

15、统计售价大于13888的角色有多少个

16、统计男性角色的平均售价

17、查询辅助角色中的最低售价是多少

18、统计法师角色中的男女角色各有多少人

19、统计每个价位中的每种性别的角色人数，并按照售价升序排序

（三）练习答案

1、查询角色"百里守约"的基本信息

SELECT roleNo,rolename AS '名字',sex AS '性别',power AS '阵营',profession AS '职业',cost AS '角色售价' FROM roles WHERE rolename='百里守约';

2、查询角色"百里守约"或”百里玄策”的基本信息

SELECT roleNo,rolename AS '名字',sex AS '性别',power AS '阵营',profession AS '职业',cost AS '角色售价' FROM roles WHERE rolename='百里守约' OR rolename='百里玄策';

3、查询姓"张"角色的姓名，所属阵营，职业

SELECT rolename AS '姓名',power AS '阵营',profession AS '职业' FROM roles WHERE rolename LIKE '张%';

4、查询姓名中含有"约"字的角色的基本信息

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',profession AS '职业' ,cost AS '角色售价' FROM roles WHERE rolename LIKE '%约%';

5、查询姓名长度为三个字，姓“孙”的角色的id，姓名，性别，所属阵营和角色售价

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',cost AS '角色售价' FROM roles WHERE rolename LIKE '孙__';

6、查询姓"百"或者姓”孙”的角色的基本信息

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',profession AS '职业' ,cost AS '角色售价' FROM roles WHERE rolename LIKE '百%' OR rolename LIKE '孙%';

7、查询姓"百"并且职业是"射手"的角色信息

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',profession AS '职业' ,cost AS '角色售价' FROM roles WHERE rolename LIKE '百%' AND profession='射手';

8、查询阵营是"吴"、”蜀”、”魏”或者"秦"的角色的信息

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',profession AS '职业' ,cost AS '角色售价' FROM roles WHERE power IN ('吴','蜀','魏','秦');

9、查询姓"孙"，但是所属阵营不是"吴"的角色信息

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',profession AS '职业' ,cost AS '角色售价' FROM roles WHERE rolename LIKE '孙%' AND power != '吴';
# <>和!=均表示不等于

10、查询所属阵营不是"吴"、”蜀”、”魏”、"秦"的角色信息

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',profession AS '职业' ,cost AS '角色售价' FROM roles WHERE power NOT IN ('吴','蜀','魏','秦');

11、查询全部角色信息，并按照“性别”排序

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',profession AS '职业' ,cost AS '角色售价' FROM roles ORDER BY sex;

12、查询现有角色都来自于哪些不同的阵营

SELECT power AS '阵营' FROM roles GROUP BY power; #分组
SELECT DISTINCT power AS '阵营' FROM roles;       #去重

13、查询所有男性角色，并按售价升序排序

SELECT roleNo,rolename AS '姓名',sex AS '性别',power AS '阵营',profession AS '职业' ,cost AS '角色售价' FROM roles WHERE sex='男' ORDER BY cost ASC;

14、统计共有多少个角色

SELECT COUNT(roleNo) AS '总人数' FROM roles;

15、统计售价大于13888的角色有多少个

SELECT COUNT(roleNo) AS '人数' FROM roles WHERE cost>13888;

16、统计男性角色的平均售价

SELECT AVG(cost) AS '平均售价' FROM roles WHERE sex='男';

17、查询辅助角色中的最低售价是多少

SELECT MIN(cost) AS '最低售价' FROM roles WHERE profession='辅助';

18、统计法师角色中的男女角色各有多少人

SELECT sex AS '性别',COUNT(*) AS '人数' FROM roles WHERE profession='法师' group by sex;

19、统计每个价位中的每种性别的角色人数，并按照售价升序排序

SELECT cost AS '角色售价',sex AS '性别',COUNT(*) FROM roles GROUP BY cost,sex ORDER BY cost ASC;

多表查询练习

（一）准备数据

# 创建阵营信息表
CREATE TABLE powers
(
    powerid int(10) PRIMARY KEY COMMENT '主键-阵营信息表ID',
    powername varchar(20) NOT NULL COMMENT '阵营名称，不允许为空'
);

# 向阵营信息表中插入多条记录
INSERT INTO powers VALUES
(1001,'稷下学院'),
(1002,'秦'),
(1003,'楚汉之地'),
(1004,'魏'),
(1005,'蜀'),
(1006,'吴'),
(1007,'长安'),
(1008,'长城守卫军'),
(1009,'北夷'),
(1010,'勇士之地');

# 创建角色信息表
CREATE TABLE roleinfo
(
    roleid int(10) PRIMARY KEY COMMENT '主键-角色ID',
    rolename varchar(20) NOT NULL COMMENT '角色姓名，不允许为空',
    sex varchar(4) DEFAULT '男'  COMMENT '角色性别，默认-男',
    profession varchar(10) NOT NULL COMMENT '角色职业，不允许为空',
    powerid int(10) COMMENT '角色所属阵营',
    CONSTRAINT ui_rolename UNIQUE (rolename)  # 唯一约束，不可插入同名角色，避免重复插入
);

# 向角色信息表中插入多条记录
INSERT INTO roleinfo(roleid,rolename,sex,profession,powerid) VALUES
 (1,  '王昭君', '女',  '法师', 1009),
 (2,  '诸葛亮', '男',  '法师', 1005),
 (3,  '张飞',   '男',  '辅助', 1005),
 (4,  '白起',   '男',  '坦克', 1002),
 (5,  '小乔',   '女',  '法师', 1006),
 (6,  '大乔',   '女',  '辅助', 1006),
 (7,  '孙尚香',  '女',  '射手', 1006),
 (8,  '百里玄策','男',  '战士', 1008),
 (9,  '百里守约','男',  '射手', 1008),
 (10, '安琪拉',  '女',  '法师', 1010),
 (11, '李白',   '男',  '刺客', 1007),
 (12, '狂铁',   '男',  '战士', 1010),
 (13, '老夫子',  '男',  '战士', 1001),
 (14, '鬼谷子',  '男',  '辅助', 1003),
 (15, '干将莫邪', '男',  '法师', 1003),
 (16, '马超',    '男',  '战士', 1005),
 (17, '亚瑟',    '男',  '战士', 1010),
 (18, '花木兰',  '男',  '战士', 1008),
 (19, '成吉思汗', '男',  '射手', 1009),
 (20, '苏烈',    '男',  '坦克', 1008),
 (21, '庄周',    '男',  '辅助', 1001);

# 创建角色售价表
CREATE TABLE rolecost
(
    costid int(10) PRIMARY KEY COMMENT '主键-售价表ID',
    roleid int(10) NOT NULL COMMENT '角色ID，不允许为空',
    cost int(10) DEFAULT NULL COMMENT '角色售价',
    CONSTRAINT ui_roleid UNIQUE (roleid)  # 唯一约束，不可插入同一角色ID，避免重复插入
);

# 向角色售价表中插入多条记录
INSERT INTO rolecost(costid,roleid,cost) VALUES
(1,7,5888),
(2,6,13888),
(3,13,8888),
(4,9,13888),
(5,10,0),
(6,1,8888),
(7,2,18888),
(8,3,13888),
(9,5,5888),
(10,8,13888),
(11,11,18888),
(12,4,13888),
(13,12,13888),
(14,20,13888),
(15,18,18888),
(16,15,13888),
(17,14,13888),
(18,21,2888),
(19,17,0),
(20,19,18888),
(21,16,18888);

（二）练习题目

1.列出总人数大于3的阵营编号和总人数

2.列出阵营为'长安'和'长城守卫军'的角色ID，角色名

3.求出各阵营法师的人数，要求显示阵营名称

4.列出阵营为'吴'的所有女角色的姓名和职业

5.显示所有角色的姓名，所属阵营名称和角色售价

6.显示各阵营名称和该阵营的角色平均售价

7.显示售价最高的前3名角色的角色ID、姓名和职业

8.列出售价在2888-8888之间（包含2888和8888）的所有角色姓名、所属阵营和职业

9.列出所有售价比王昭君高的角色的姓名、职业、所属阵营和售价

（三）练习答案

1.列出总人数大于4的阵营编号和总人数

SELECT powerid AS '阵营编号',COUNT(*) AS '总人数' FROM roleinfo GROUP BY powerid HAVING COUNT(*) > 3;

2.列出阵营为'长安'和'长城守卫军'的角色ID，角色名

SELECT roleid AS '角色ID',rolename AS '角色名' FROM roleinfo r INNER JOIN powers p ON r.powerid=p.powerid WHERE p.powername='长安' OR p.powername='长城守卫军';
SELECT roleid AS '角色ID',rolename AS '角色名' FROM roleinfo r INNER JOIN powers p ON r.powerid=p.powerid WHERE p.powername IN ('长安','长城守卫军');

3.求出各阵营法师的人数，要求显示阵营名称

SELECT p.powername AS '阵营',COUNT(*) FROM roleinfo r INNER JOIN powers p ON r.powerid=p.powerid WHERE profession = '法师' GROUP BY r.powerid;

4.列出阵营为'吴'的所有女角色的姓名和职业

SELECT r.rolename AS '姓名',r.profession AS '职业' FROM roleinfo r INNER JOIN powers p ON r.powerid=p.powerid WHERE p.powername='吴' AND r.sex='女';

5.显示所有角色的姓名，所属阵营名称和角色售价

SELECT r.roleid,r.rolename AS '姓名',p.powername AS '阵营',c.cost AS '角色售价' FROM roleinfo r 
INNER JOIN powers p ON r.powerid=p.powerid 
INNER JOIN rolecost c ON r.roleid=c.roleid;
# SELECT r.roleid,r.rolename AS '姓名',p.powername AS '阵营',c.cost AS '角色售价' FROM roleinfo r 
# INNER JOIN powers p 
# INNER JOIN rolecost c ON r.powerid=p.powerid AND r.roleid=c.roleid;

6.显示各阵营名称和该阵营的角色平均售价

SELECT p.powername AS '阵营',AVG(c.cost) FROM roleinfo r 
INNER JOIN powers p ON p.powerid=r.powerid 
INNER JOIN rolecost c ON c.roleid=r.roleid 
GROUP BY p.powername;

7.显示售价最高的前3名角色的角色ID、姓名和职业

SELECT r.roleid,r.rolename AS '姓名',r.profession AS '职业' FROM roleinfo r 
INNER JOIN rolecost c on r.roleid=c.roleid 
ORDER BY c.cost DESC LIMIT 3;

8.列出售价在2888-8888之间（包含2888和8888）的所有角色姓名、所属阵营和职业

SELECT r.rolename AS '姓名',p.powername AS '阵营',r.profession AS '职业' FROM roleinfo r 
INNER JOIN powers p ON r.powerid=p.powerid 
INNER JOIN rolecost c ON c.roleid=r.roleid 
where c.cost BETWEEN 2888 AND 8888;

9.列出所有售价比王昭君高的角色的姓名、职业、所属阵营和售价

SELECT r.rolename AS '姓名',p.powername AS '阵营',c.cost AS '售价' FROM roleinfo r 
INNER JOIN powers p ON r.powerid=p.powerid 
INNER JOIN rolecost c ON c.roleid=r.roleid 
WHERE c.cost > (
    SELECT c.cost FROM rolecost c 
    INNER JOIN roleinfo r ON r.roleid=c.roleid 
    WHERE r.rolename='王昭君' 
) ;

插入操作异常处理

报错：ERROR 1366 (HY000): Incorrect string value: '\xE7\x8E\x8B\xE6\x98\xAD...' for column 'rolename' at row 1

原因：数据库字符集未正常设置为utf8格式，导致无法插入中文

解决方法：修改表的字符集为utf8mb4

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 修改数据库默认字符集，后续创建表时不用重复设置字符集
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

九圣残炎

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【练习】SQL专项练习（一）

8.列出售价在2888-8888之间（包含2888和8888）的所有角色姓名、所属阵营和职业。9.列出所有售价比王昭君高的角色的姓名、职业、所属阵营和售价。9.列出所有售价比王昭君高的角色的姓名、职业、所属阵营和售价。2.列出阵营为'长安'和'长城守卫军'的角色ID，角色名。2.列出阵营为'长安'和'长城守卫军'的角色ID，角色名。7.显示售价最高的前3名角色的角色ID、姓名和职业。4.列出阵营为'吴'的所有女角色的姓名和职业。4.列出阵营为'吴'的所有女角色的姓名和职业。
复制链接

扫一扫