MySQL中 DQL语句实训

最新推荐文章于 2024-06-20 21:54:09 发布

把握节奏

最新推荐文章于 2024-06-20 21:54:09 发布

阅读量802

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_67741143/article/details/130362291

版权

学习目标

创建xscjgl数据库，对xscjgl数据库中的xs,kc,xs_kc表进行查询。

背景铺设：创建数据库并填入数据

create database xscjgl;
use xscjgl;
/*
 Navicat Premium Data Transfer

 Source Server         : phonesy
 Source Server Type    : MySQL
 Source Server Version : 50731
 Source Host           : localhost:3306
 Source Schema         : xscjgl

 Target Server Type    : MySQL
 Target Server Version : 50731
 File Encoding         : 65001

 Date: 18/04/2023 09:12:32
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for kc
-- ----------------------------
DROP TABLE IF EXISTS `kc`;
CREATE TABLE `kc`  (
  `课程号` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `课程名` char(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `开课学期` tinyint(1) NOT NULL DEFAULT 1 COMMENT '只能为1-8',
  `学时` tinyint(1) NOT NULL,
  `学分` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`课程号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of kc
-- ----------------------------
INSERT INTO `kc` VALUES ('101', '计算机基础', 1, 80, 5);
INSERT INTO `kc` VALUES ('102', '程序设计与语言', 2, 68, 4);
INSERT INTO `kc` VALUES ('206', '离散数学', 4, 68, 4);
INSERT INTO `kc` VALUES ('208', '数据结构', 5, 68, 4);
INSERT INTO `kc` VALUES ('209', '操作系统', 6, 68, 4);
INSERT INTO `kc` VALUES ('210', '计算机原理', 5, 85, 5);
INSERT INTO `kc` VALUES ('212', '数据库原理', 7, 68, 4);
INSERT INTO `kc` VALUES ('301', '计算机网络', 7, 51, 3);
INSERT INTO `kc` VALUES ('302', '软件工程', 7, 51, 3);

-- ----------------------------
-- Table structure for xs
-- ----------------------------
DROP TABLE IF EXISTS `xs`;
CREATE TABLE `xs`  (
  `学号` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `姓名` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `专业名` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `性别` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1为男 0为女',
  `出生时间` date NOT NULL,
  `总学分` tinyint(1) NULL DEFAULT NULL,
  `照片` blob NULL,
  `备注` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`学号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of xs
-- ----------------------------
INSERT INTO `xs` VALUES ('081101', '王林', '计算机', 1, '1990-02-22', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081102', '程明', '计算机', 1, '1991-02-01', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081103', '王燕', '计算机', 0, '1989-10-06', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081104', '韦严平', '计算机', 1, '1990-08-26', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081106', '李方方', '计算机', 1, '1990-11-20', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081107', '李明', '计算机', 1, '1990-05-01', 54, NULL, '提前修完《数据结构》，并获学分');
INSERT INTO `xs` VALUES ('081108', '林一帆', '计算机', 1, '1989-08-05', 52, NULL, '已提前修完一门课');
INSERT INTO `xs` VALUES ('081109', '张强民', '计算机', 1, '1989-08-11', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081110', '张蔚', '计算机', 0, '1991-07-22', 50, NULL, '三好生');
INSERT INTO `xs` VALUES ('081111', '赵琳', '计算机', 0, '1990-03-18', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081113', '严红', '计算机', 0, '1989-08-11', 48, NULL, '有一门功课不及格，待补考');
INSERT INTO `xs` VALUES ('081201', '王敏', '通信工程', 1, '1989-06-10', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081202', '王林', '通信工程', 1, '1989-01-29', 40, NULL, '有一门课不及格，待补考');
INSERT INTO `xs` VALUES ('081203', '王玉民', '通信工程', 1, '1990-03-26', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081204', '马琳琳', '通信工程', 0, '1989-02-10', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081206', '李计', '通信工程', 1, '1989-09-20', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081210', '李红庆', '通信工程', 1, '1989-05-01', 44, NULL, '已提前修完一门课，并获得学分');
INSERT INTO `xs` VALUES ('081216', '孙祥欣', '通信工程', 1, '1989-03-09', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081218', '孙研', '通信工程', 1, '1990-10-09', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081220', '吴薇华', '通信工程', 0, '1990-03-18', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081221', '刘燕敏', '通信工程', 0, '1989-11-12', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081241', '罗林琳', '通信工程', 0, '1990-01-30', 50, NULL, '转专业学习');

-- ----------------------------
-- Table structure for xs_kc
-- ----------------------------
DROP TABLE IF EXISTS `xs_kc`;
CREATE TABLE `xs_kc`  (
  `学号` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `课程号` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `成绩` tinyint(1) NULL DEFAULT NULL,
  `学分` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`学号`, `课程号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of xs_kc
-- ----------------------------
INSERT INTO `xs_kc` VALUES ('081101', '101', 80, 5);
INSERT INTO `xs_kc` VALUES ('081101', '102', 78, 4);
INSERT INTO `xs_kc` VALUES ('081101', '206', 76, 4);
INSERT INTO `xs_kc` VALUES ('081102', '102', 78, 4);
INSERT INTO `xs_kc` VALUES ('081102', '206', 78, 4);
INSERT INTO `xs_kc` VALUES ('081103', '101', 62, 5);
INSERT INTO `xs_kc` VALUES ('081103', '102', 70, 4);
INSERT INTO `xs_kc` VALUES ('081103', '206', 81, 4);
INSERT INTO `xs_kc` VALUES ('081104', '101', 90, 5);
INSERT INTO `xs_kc` VALUES ('081104', '102', 84, 4);
INSERT INTO `xs_kc` VALUES ('081104', '206', 65, 4);
INSERT INTO `xs_kc` VALUES ('081106', '101', 65, 5);
INSERT INTO `xs_kc` VALUES ('081106', '102', 71, 4);
INSERT INTO `xs_kc` VALUES ('081106', '206', 81, 4);
INSERT INTO `xs_kc` VALUES ('081107', '101', 78, 5);
INSERT INTO `xs_kc` VALUES ('081107', '102', 80, 4);
INSERT INTO `xs_kc` VALUES ('081107', '206', 68, 4);
INSERT INTO `xs_kc` VALUES ('081108', '101', 85, 5);
INSERT INTO `xs_kc` VALUES ('081108', '102', 64, 4);
INSERT INTO `xs_kc` VALUES ('081108', '206', 87, 4);
INSERT INTO `xs_kc` VALUES ('081109', '101', 66, 5);
INSERT INTO `xs_kc` VALUES ('081109', '102', 83, 4);
INSERT INTO `xs_kc` VALUES ('081109', '206', 70, 4);
INSERT INTO `xs_kc` VALUES ('081110', '101', 95, 5);
INSERT INTO `xs_kc` VALUES ('081110', '102', 90, 4);
INSERT INTO `xs_kc` VALUES ('081110', '206', 89, 4);
INSERT INTO `xs_kc` VALUES ('081111', '101', 91, 5);
INSERT INTO `xs_kc` VALUES ('081111', '102', 70, 4);
INSERT INTO `xs_kc` VALUES ('081111', '206', 76, 4);
INSERT INTO `xs_kc` VALUES ('081113', '101', 63, 5);
INSERT INTO `xs_kc` VALUES ('081113', '102', 79, 4);
INSERT INTO `xs_kc` VALUES ('081113', '206', 60, 4);
INSERT INTO `xs_kc` VALUES ('081201', '101', 80, 5);
INSERT INTO `xs_kc` VALUES ('081202', '101', 65, 5);
INSERT INTO `xs_kc` VALUES ('081203', '101', 87, 5);
INSERT INTO `xs_kc` VALUES ('081204', '101', 91, 5);
INSERT INTO `xs_kc` VALUES ('081210', '101', 76, 5);
INSERT INTO `xs_kc` VALUES ('081216', '101', 81, 5);
INSERT INTO `xs_kc` VALUES ('081218', '101', 70, 5);
INSERT INTO `xs_kc` VALUES ('081220', '101', 82, 5);
INSERT INTO `xs_kc` VALUES ('081221', '101', 76, 5);
INSERT INTO `xs_kc` VALUES ('081241', '101', 90, 5);

SET FOREIGN_KEY_CHECKS = 1;

习题：

(1)查询学生的学号，姓名，出生日期

use xscjgl;
select 学号,姓名,出生时间 from xs;

(2)查询1990年以后出生的学生的学号，姓名，出生日期

select 学号,姓名,出生时间 from xs where 出生时间>="1990-1-1";

(3)按姓名升序显示学生的学号，姓名。

 select 学号,姓名 from xs order by 姓名;

(4)按专业名统计各专业的学生人数。

select 专业名,count(*) 各专业人数 from xs group by 专业名;

(5)查询姓“王”的学生的学号，姓名信息

select 学号,姓名 from xs where 姓名 like "王%";

(6)查询学生的学号，姓名，课程名，成绩信息。

SELECT b.`学号`,b.`姓名`,c.`课程名`,a.`成绩` FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号`;

(7)统计学生的成绩总分并按总分由高到低排名。

SELECT b.*,sum(成绩) 总分 FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` group by a.学号 order by 总分 desc;

(8)查询学生成绩总分高于200分的学生信息。

SELECT b.*,sum(成绩) 总分 FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` group by a.学号  having 总分>200;

(9)统计没有参加考试的学生名单。

select * from xs where 学号 not in(SELECT b.学号 FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` group by a.学号);

(10)显示总分前三名的学生信息。

SELECT b.*,sum(成绩) 总分 FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` group by a.学号 order by 总分 desc limit 3;

(11)查询参加计算机基础课程考试的学生成绩高于课程平均分的学生信息。

SELECT b.* ,a.`成绩`FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` AND c.课程名='计算机基础' AND a.成绩>=(
SELECT AVG(a.成绩) FROM xs_kc a,kc b WHERE a.课程号=b.课程号  AND b.课程名='计算机基础');

(12)查询程序设计与语言课程最高分的学生的学号和姓名

SELECT b.学号,姓名,MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='程序设计与语言' AND a.`成绩`=(
SELECT MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='程序设计与语言');

(13)查询计算机基础，程序设计与语言和离散数学三门课程中最高分学生的学号，

SELECT b.学号,姓名,MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='计算机基础' AND a.`成绩`=(
SELECT MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='计算机基础')
UNION ALL
SELECT b.学号,姓名,MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='程序设计与语言' AND a.`成绩`=(
SELECT MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='程序设计与语言')
UNION ALL
SELECT b.学号,姓名,MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='离散数学' AND a.`成绩`=(
SELECT MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='离散数学');

把握节奏

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
MySQL中 DQL语句实训

(13)查询计算机基础，程序设计与语言和离散数学三门课程中最高分学生的学号，(11)查询参加计算机基础课程考试的学生成绩高于课程平均分的学生信息。(2)查询1990年以后出生的学生的学号，姓名，出生日期。(12)查询程序设计与语言课程最高分的学生的学号和姓名。(6)查询学生的学号，姓名，课程名，成绩信息。(8)查询学生成绩总分高于200分的学生信息。(5)查询姓“王”的学生的学号，姓名信息。(3)按姓名升序显示学生的学号，姓名。(1)查询学生的学号，姓名，出生日期。(10)显示总分前三名的学生信息。
复制链接

扫一扫