期末试题 :

一，创建数据库Game
二，数据表操作
1、创建表格players，记录游戏玩家信息：
player_id：玩家ID，主键
player_name：玩家姓名，不能为空
age：年龄，必须大于等于18岁
country：国家， 默认值为'中国'
unique_player_id：玩家ID唯一约束
2、创建表格games，记录游戏信息：
game_id：游戏ID，主键
game_name：游戏名称，不能为空
release_date：发布日期
genre：游戏类型
unique_game_name：游戏名称唯一约束
3、创建表格scores，记录玩家游戏得分：
score_id：得分ID，主键
player_id：玩家ID，外键参考players表的player_id
game_id：游戏ID，外键参考games表的game_id
score：得分
fk_player：玩家ID外键约束，关联players表
fk_game：游戏ID外键约束，关联games表
三，数据操作
 1、向表格players插入数据：

 2、向表格games插入数据：

 3、向表格scores插入数据：
(1,1, 1, 85),
(2,2, 2, 92),
(3,3, 3, 78),
(4,4, 4, 89),
(5,5, 5, 95),
(6,6, 6, 80),
(7,7, 7, 87),
(8,8, 8, 90),
(9,1, 2, 85),
(10,2, 1, 92),
(11,3, 6, 78),
(12,3, 7, 89),
(13,3, 8, 95),
(14,1, 2, 80),
(15,4, 7, 87),
(16,6, 7, 90),
(17,3, 1, 95),
(18,1, 4, 80),
(19,4, 3, 87),
(20,6, 8, 90);
四，完成如下查询题目
1.查询得分ID、玩家姓名、游戏名称和得分
2.查询有得分大于88分的人的游戏名称,游戏发布日期,游戏类型(不重复信息)
3.总得分大于250的玩家的玩家ID,玩家姓名,年龄,国家
4.查询马起和诸葛亮玩的相同游戏有哪些
5.将每个玩家的总得分计算出来
6.将马超的国家,魏国改成蜀国
7.查询年龄大于25的玩家姓名
8.查询蜀国玩王者荣耀有哪些玩家
9.查询玩各个游戏的总人数
10.查询游戏类型为射击的游戏名称

参考答案: 

-- 一，创建数据库Game-- 1，删除数据库DROP DATABASE IF EXISTS Game;-- 2，创建数据库CREATE DATABASE Game;-- 3，修改数据库编码方式和字符集排列顺序ALTER DATABASE Game CHARACTER SET utf8 COLLATE utf8_bin;-- 4，使用数据库USE Game;-- 二，数据表操作-- 1、创建表格players，记录游戏玩家信息：DROP TABLE IF EXISTS players;CREATE TABLE players (player_id INT PRIMARY KEY, -- 玩家ID，主键player_name VARCHAR(50) NOT NULL, -- 玩家姓名，不能为空age INT CHECK (age >= 18), -- 年龄，必须大于等于18岁country VARCHAR(50) DEFAULT '中国', -- 国家， 默认值为'中国'CONSTRAINT unique_player_id UNIQUE (player_id) -- 玩家ID唯一约束);-- 2、创建表格games，记录游戏信息：DROP TABLE IF EXISTS games;CREATE TABLE games (game_id INT PRIMARY KEY, -- 游戏ID，主键game_name VARCHAR(50) NOT NULL, -- 游戏名称，不能为空release_date DATE, -- 发布日期genre VARCHAR(50), -- 游戏类型CONSTRAINT unique_game_name UNIQUE (game_name) -- 游戏名称唯一约束);-- 3、创建表格scores，记录玩家游戏得分：DROP TABLE IF EXISTS scores;CREATE TABLE scores (score_id INT PRIMARY KEY, -- 得分ID，主键player_id INT, -- 玩家ID，外键game_id INT, -- 游戏ID，外键score INT, -- 得分CONSTRAINT fk_player FOREIGN KEY (player_id) REFERENCES players(player_id), -- 玩家ID外键约束，关联players表CONSTRAINT fk_game FOREIGN KEY (game_id) REFERENCES games(game_id) -- 游戏ID外键约束，关联games表);-- 三，数据操作SELECT * FROM players;SELECT * FROM games;SELECT * FROM scores;-- 1、向表格players插入数据：INSERT INTO players (player_id, player_name, age, country) VALUES(1,'刘备',21,'蜀国'),(2,'关羽',23,'蜀国'),(3,'张飞',45,'蜀国'),(4,'诸葛亮',28,'蜀国'),(5,'赵云',30,'蜀国'),(6,'马超',18,'魏国'),(7,'黄忠',19,'蜀国'),(8,'曹贼',27,'魏国');-- 2、向表格games插入数据：INSERT INTO games (game_id, game_name, release_date, genre) VALUES(1,'绝地求生','2023-07-01','射击'),(2,'英雄联盟','2023-07-02','MOBA'),(3,'守望先锋','2023-07-03','射击'),(4,'王者荣耀','2023-07-04','MOBA'),(5,'蛋仔派对','2023-07-05','休闲'),(6,'开心消消乐','2023-07-06','休闲'),(7,'使命召唤','2023-07-07','射击'),(8,'刺激战场','2023-07-08','射击');-- 3、向表格scores插入数据：INSERT INTO scores (score_id, player_id, game_id, score) VALUES(1,1, 1, 85),(2,2, 2, 72),(3,3, 3, 78),(4,4, 4, 89),(5,5, 5, 95),(6,6, 6, 80),(7,7, 7, 87),(8,8, 8, 90),(9,1, 2, 85),(10,2, 1, 22),(11,3, 6, 78),(12,3, 7, 83),(13,3, 8, 95),(14,1, 2, 80),(15,4, 7, 81),(16,6, 7, 90),(17,3, 1, 95),(18,1, 4, 80),(19,4, 3, 87),(20,6, 8, 90);-- 四，完成如下查询题目-- 1.查询得分ID、玩家姓名、游戏名称和得分SELECT scores.score_id AS 得分ID, players.player_name AS 玩家姓名, games.game_name AS 游戏名称, scores.score AS 得分FROM scoresJOIN players ON scores.player_id = players.player_idJOIN games ON scores.game_id = games.game_id;-- 2.查询有得分大于88分的人的游戏名称,游戏发布日期,游戏类型(不重复信息)-- 第一种方法SELECT DISTINCT g.game_name AS 游戏名称, g.release_date AS 游戏发布日期, g.genre AS 游戏类型FROM games gINNER JOIN scores s ON g.game_id = s.game_idWHERE s.score > 88;-- 第二种方法SELECT DISTINCT games.game_name AS 游戏名称, games.release_date AS 游戏发布日期, games.genre AS 游戏类型FROM scoresJOIN games ON scores.game_id = games.game_idWHERE scores.score > 88;-- 3.总得分大于250的玩家的玩家ID,玩家姓名,年龄,国家-- 第一种方法SELECT p.player_id AS 玩家ID, p.player_name AS 玩家姓名, p.age AS 年龄, p.country AS 国家FROM players pINNER JOIN scores s ON p.player_id = s.player_idGROUP BY p.player_id, p.player_name, p.age, p.countryHAVING SUM(s.score) > 250;-- 第二种方法SELECT players.player_id AS 玩家ID, players.player_name AS 玩家姓名, players.age AS 年龄, players.country AS 国家FROM scoresJOIN players ON scores.player_id = players.player_idGROUP BY players.player_idHAVING SUM(scores.score) > 250;-- 4.查询马起和诸葛亮玩的相同游戏有哪些SELECT DISTINCT g.game_name AS 相同游戏FROM games gINNER JOIN scores s1 ON g.game_id = s1.game_idINNER JOIN scores s2 ON s1.game_id = s2.game_idWHERE s1.player_id = 6 -- 马超的player_idAND s2.player_id = 4 -- 诸葛亮的player_id;-- 5.将每个玩家的总得分计算出来SELECT p.player_name AS 玩家姓名, SUM(s.score) AS 总得分FROM players pJOIN scores s ON p.player_id = s.player_idGROUP BY p.player_name;-- 6.将马超的国家,魏国改成蜀国UPDATE players SET country = '蜀国' WHERE player_name = '马超';-- 7.查询年龄大于25的玩家姓名SELECT player_name AS 姓名 FROM players WHERE age> 25;-- 8.查询蜀国玩王者荣耀有哪些玩家SELECT player_name AS 玩家姓名FROM playersJOIN scores ON players.player_id = scores.player_idJOIN games ON scores.game_id = games.game_idWHERE country = '蜀国' AND game_name = '王者荣耀';-- 9.查询玩各个游戏的总人数SELECT games.game_name AS 游戏名称, COUNT(DISTINCT scores.player_id) AS 总人数FROM gamesLEFT JOIN scores ON games.game_id = scores.game_idGROUP BY games.game_name;-- 10.查询游戏类型为射击的游戏名称SELECT game_name AS 游戏名称FROM gamesWHERE genre= '射击';