【数据库——MySQL】（11）查询和视图练习及讲解

目录

  • 1. 简述
  • 2. 题目
    • 2.1 查询
    • 2.2 视图的使用
      • 2.2.1 创建视图
      • 2.2.2 查询视图
      • 2.2.3 更新视图
      • 2.2.4 删除视图
  • 3. 解答
    • 3.1 查询
    • 3.2 视图的使用
      • 3.2.1 创建视图
      • 3.2.2 查询视图
      • 3.2.3 更新视图
      • 3.2.4 删除视图

1. 简述

在前面我们也给出了一些查询的练习题供大家练习，这里再给出进阶版的查询练习给大家练练手，同时还会给出上一篇文章视图的对应习题！

2. 题目

2.1 查询

先建立数据库 score，代码如下：

# 先删除数据库
DROP DATABASE IF EXISTS score;
# 创建数据库
CREATE DATABASE IF NOT EXISTS `score` 
DEFAULT CHARACTER SET 'gb18030' 
DEFAULT COLLATE 'gb18030_chinese_ci';USE score;SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (`id` char(4) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,`name` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('101', '数学学院');
INSERT INTO `department` VALUES ('102', '计算机学院');
INSERT INTO `department` VALUES ('103', '外语学院');
INSERT INTO `department` VALUES ('104', '物理学院');
INSERT INTO `department` VALUES ('105', '电气学院');
INSERT INTO `department` VALUES ('106', '马克思主义学院');
INSERT INTO `department` VALUES ('107', '哲学院');
INSERT INTO `department` VALUES ('108', '体育学院');
INSERT INTO `department` VALUES ('109', '土木学院');
INSERT INTO `department` VALUES ('110', '遥感学院');-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (`grade` char(2) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL DEFAULT '',`lowScore` int(0) NULL DEFAULT NULL,`highScore` int(0) NULL DEFAULT NULL,PRIMARY KEY (`grade`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('A', 90, 100);
INSERT INTO `grade` VALUES ('B', 80, 89);
INSERT INTO `grade` VALUES ('C', 70, 79);
INSERT INTO `grade` VALUES ('D', 60, 69);
INSERT INTO `grade` VALUES ('E', 0, 59);-- ----------------------------
-- Table structure for lesson
-- ----------------------------
DROP TABLE IF EXISTS `lesson`;
CREATE TABLE `lesson`  (`lessonid` int(0) NOT NULL,`lessonName` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,`xf` int(0) GENERATED ALWAYS AS ((`time` / 16)) STORED NULL,`deparmentId` char(5) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,`preLessonId` int(0) NULL DEFAULT NULL,`time` int(0) NULL DEFAULT NULL,INDEX `lessonid`(`lessonid`) USING BTREE,INDEX `ix_name`(`lessonName`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of lesson
-- ----------------------------
INSERT INTO `lesson` VALUES (101, '数学', DEFAULT, '101', NULL, 72);
INSERT INTO `lesson` VALUES (102, '英语', DEFAULT, '102', NULL, 96);
INSERT INTO `lesson` VALUES (103, '程序设计', DEFAULT, '103', 101, 64);
INSERT INTO `lesson` VALUES (104, '物理', DEFAULT, '104', 101, 32);
INSERT INTO `lesson` VALUES (105, '政治', DEFAULT, '106', NULL, 80);
INSERT INTO `lesson` VALUES (106, '法语', DEFAULT, '102', 102, NULL);
INSERT INTO `lesson` VALUES (107, '体育', DEFAULT, '108', NULL, 96);
INSERT INTO `lesson` VALUES (108, '地理信息系统', DEFAULT, '110', 103, 32);
INSERT INTO `lesson` VALUES (109, '数据库', DEFAULT, '103', 103, 48);-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (`Id` int(0) NOT NULL AUTO_INCREMENT,`stuId` char(13) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,`LessonId` int(0) NOT NULL,`score` int(0) NULL DEFAULT 0,PRIMARY KEY (`Id`) USING BTREE,INDEX `课程`(`LessonId`) USING BTREE,INDEX `fk-stu`(`stuId`) USING BTREE,CONSTRAINT `fk-stu` FOREIGN KEY (`stuId`) REFERENCES `stu` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT `课程` FOREIGN KEY (`LessonId`) REFERENCES `lesson` (`lessonid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 57 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, '20201001', 103, 99);
INSERT INTO `score` VALUES (2, '20201002', 101, 77);
INSERT INTO `score` VALUES (3, '20201001', 101, 65);
INSERT INTO `score` VALUES (4, '20201003', 101, 88);
INSERT INTO `score` VALUES (5, '20201002', 102, 98);
INSERT INTO `score` VALUES (6, '20211001', 103, 91);
INSERT INTO `score` VALUES (7, '20191002', 102, 82);
INSERT INTO `score` VALUES (8, '20191002', 101, 63);
INSERT INTO `score` VALUES (9, '20201003', 103, 71);
INSERT INTO `score` VALUES (10, '20201001', 104, 72);
INSERT INTO `score` VALUES (11, '20201003', 104, 94);
INSERT INTO `score` VALUES (12, '20201003', 102, 77);
INSERT INTO `score` VALUES (13, '20201004', 103, 82);
INSERT INTO `score` VALUES (14, '20211001', 104, 78);
INSERT INTO `score` VALUES (15, '20191003', 104, 74);
INSERT INTO `score` VALUES (16, '20211002', 102, 87);
INSERT INTO `score` VALUES (17, '20211001', 102, 51);
INSERT INTO `score` VALUES (18, '20201004', 101, 86);
INSERT INTO `score` VALUES (19, '20201001', 102, 84);
INSERT INTO `score` VALUES (20, '20201002', 103, 94);
INSERT INTO `score` VALUES (21, '20201002', 104, 48);
INSERT INTO `score` VALUES (29, '20191001', 101, 85);
INSERT INTO `score` VALUES (30, '20211002', 104, 82);
INSERT INTO `score` VALUES (31, '20191001', 103, 90);
INSERT INTO `score` VALUES (34, '20191004', 101, 68);
INSERT INTO `score` VALUES (35, '20211003', 102, 80);
INSERT INTO `score` VALUES (36, '20191004', 103, 83);
INSERT INTO `score` VALUES (37, '20191004', 105, 91);
INSERT INTO `score` VALUES (38, '20211002', 105, 84);
INSERT INTO `score` VALUES (39, '20211001', 105, 70);
INSERT INTO `score` VALUES (40, '20191004', 105, 88);
INSERT INTO `score` VALUES (46, '20211003', 104, 56);
INSERT INTO `score` VALUES (47, '20211003', 105, 76);
INSERT INTO `score` VALUES (48, '20211002', 101, 86);
INSERT INTO `score` VALUES (49, '20211001', 101, 76);
INSERT INTO `score` VALUES (50, '20211001', 104, 96);
INSERT INTO `score` VALUES (55, '20211003', 105, 90);
INSERT INTO `score` VALUES (56, '20211001', 106, 78);
INSERT INTO `score` VALUES (57, '20221203', 103, 88);
INSERT INTO `score` VALUES (58, '20221203', 102, 50);-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu`  (`id` char(13) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,`name` char(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,`sex` enum('男','女') CHARACTER SET gbk COLLATE gbk_bin NULL DEFAULT '男',`departmentId` char(5) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL DEFAULT '',`birthday` date NULL DEFAULT NULL,`hoppy` set('打球','跳舞','游戏','逛街','唱歌') CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,`address` json NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `fk-department`(`departmentId`) USING BTREE,CONSTRAINT `fk-department` FOREIGN KEY (`departmentId`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES ('20191001', '许和雅', '女', '102', '2001-01-12', '打球,游戏', '{\"tel\": \"18927763056\", \"name\": \"John\", \"address\": \"Beijing\"}');
INSERT INTO `stu` VALUES ('20191002', '冯红云', '女', '103', '2001-05-10', '打球', '{\"tel\": \"13908623506\", \"name\": \"Alice\", \"address\": \"ShangHai\"}');
INSERT INTO `stu` VALUES ('20191003', '冯海', '男', '103', NULL, '逛街', NULL);
INSERT INTO `stu` VALUES ('20191004', '刘一凡', '男', '105', NULL, '打球,跳舞,游戏', '{\"tel\": \"13702345067\", \"name\": \"Bob\", \"address\": \"Wuhan\"}');
INSERT INTO `stu` VALUES ('20191011', 'Alice', '女', '102', '1999-12-14', '打球,跳舞,逛街', NULL);
INSERT INTO `stu` VALUES ('20191012', 'john', '男', '103', '2000-02-05', NULL, NULL);
INSERT INTO `stu` VALUES ('20201001', '张三', '男', '101', NULL, '打球,逛街', NULL);
INSERT INTO `stu` VALUES ('20201002', '李四', '男', '102', '2001-01-12', '跳舞,逛街', NULL);
INSERT INTO `stu` VALUES ('20201003', '王五', '女', '103', NULL, '打球,跳舞,逛街', NULL);
INSERT INTO `stu` VALUES ('20201004', '赵六', '女', '101', NULL, '游戏,逛街', NULL);
INSERT INTO `stu` VALUES ('20201005', '张海', '男', '104', NULL, NULL, '{\"tel\": \"18985107123\", \"name\": \"abc\", \"address\": \"Wuhan\"}');
INSERT INTO `stu` VALUES ('20201006', '张三丰', '男', '105', '2000-09-20', '跳舞,唱歌', NULL);
INSERT INTO `stu` VALUES ('20201008', '陈启', '女', '105', NULL, NULL, '{\"tel\": \"13521234567\", \"address\": \"ShangHai\"}');
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', '男', '101', '2002-03-02', NULL, NULL);
INSERT INTO `stu` VALUES ('20211002', '雷淳雅', '女', '101', NULL, '跳舞,游戏', '{\"tel\": \"13707761223\", \"name\": \"sunny\", \"address\": \"Shenzhen\"}');
INSERT INTO `stu` VALUES ('20211003', '李磊', '男', '104', NULL, '跳舞,逛街', NULL);
INSERT INTO `stu` VALUES ('20221201', '郭靖', '男', '103', '2020-03-10', NULL, '{\"tel\": \"17725108427\", \"name\": \"guo\", \"address\": \"Chengdu\"}');
INSERT INTO `stu` VALUES ('20221202', '黄蓉', '男', '106', '2020-04-10', NULL, '{\"tel\": \"17715108427\", \"name\": \"huang\", \"address\": \"changsha\"}');
INSERT INTO `stu` VALUES ('20221203', 'Mary', '女', '103', NULL, NULL, NULL);-- ----------------------------
-- View structure for v_stu
-- ----------------------------
DROP VIEW IF EXISTS `v_stu`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_stu` AS select `stu`.`id` AS `id`,`stu`.`name` AS `name`,`department`.`name` AS `院系`,`lesson`.`lessonName` AS `课程`,`score`.`score` AS `成绩` from (((`stu` join `department` on((`stu`.`departmentId` = `department`.`id`))) left join `score` on((`stu`.`id` = `score`.`stuId`))) left join `lesson` on((`score`.`LessonId` = `lesson`.`lessonid`)));-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES ('20191001', '许和雅', '计算机学院', '数学', 85);
INSERT INTO `stu` VALUES ('20191001', '许和雅', '计算机学院', '程序设计', 90);
INSERT INTO `stu` VALUES ('20191002', '冯红云', '外语学院', '英语', 82);
INSERT INTO `stu` VALUES ('20191002', '冯红云', '外语学院', '数学', 63);
INSERT INTO `stu` VALUES ('20191003', '冯海', '外语学院', '物理', 74);
INSERT INTO `stu` VALUES ('20191004', '刘一凡', '电气学院', '数学', 68);
INSERT INTO `stu` VALUES ('20191004', '刘一凡', '电气学院', '程序设计', 83);
INSERT INTO `stu` VALUES ('20191004', '刘一凡', '电气学院', '政治', 91);
INSERT INTO `stu` VALUES ('20191004', '刘一凡', '电气学院', '政治', 88);
INSERT INTO `stu` VALUES ('20191011', 'Alice', '计算机学院', NULL, NULL);
INSERT INTO `stu` VALUES ('20191012', 'john', '外语学院', NULL, NULL);
INSERT INTO `stu` VALUES ('20201001', '张三', '数学学院', '程序设计', 99);
INSERT INTO `stu` VALUES ('20201001', '张三', '数学学院', '数学', 65);
INSERT INTO `stu` VALUES ('20201001', '张三', '数学学院', '物理', 72);
INSERT INTO `stu` VALUES ('20201001', '张三', '数学学院', '英语', 84);
INSERT INTO `stu` VALUES ('20201002', '李四', '计算机学院', '数学', 77);
INSERT INTO `stu` VALUES ('20201002', '李四', '计算机学院', '英语', 98);
INSERT INTO `stu` VALUES ('20201002', '李四', '计算机学院', '程序设计', 94);
INSERT INTO `stu` VALUES ('20201002', '李四', '计算机学院', '物理', 48);
INSERT INTO `stu` VALUES ('20201003', '王五', '外语学院', '数学', 88);
INSERT INTO `stu` VALUES ('20201003', '王五', '外语学院', '程序设计', 71);
INSERT INTO `stu` VALUES ('20201003', '王五', '外语学院', '物理', 94);
INSERT INTO `stu` VALUES ('20201003', '王五', '外语学院', '英语', 77);
INSERT INTO `stu` VALUES ('20201004', '赵六', '数学学院', '程序设计', 82);
INSERT INTO `stu` VALUES ('20201004', '赵六', '数学学院', '数学', 86);
INSERT INTO `stu` VALUES ('20201005', '张海', '物理学院', NULL, NULL);
INSERT INTO `stu` VALUES ('20201006', '张三丰', '电气学院', NULL, NULL);
INSERT INTO `stu` VALUES ('20201008', '陈启', '电气学院', NULL, NULL);
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', '数学学院', '程序设计', 91);
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', '数学学院', '物理', 78);
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', '数学学院', '英语', 51);
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', '数学学院', '政治', 70);
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', '数学学院', '数学', 76);
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', '数学学院', '物理', 96);
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', '数学学院', '法语', 78);
INSERT INTO `stu` VALUES ('20211002', '雷淳雅', '数学学院', '英语', 87);
INSERT INTO `stu` VALUES ('20211002', '雷淳雅', '数学学院', '物理', 82);
INSERT INTO `stu` VALUES ('20211002', '雷淳雅', '数学学院', '政治', 84);
INSERT INTO `stu` VALUES ('20211002', '雷淳雅', '数学学院', '数学', 86);
INSERT INTO `stu` VALUES ('20211003', '李磊', '物理学院', '英语', 80);
INSERT INTO `stu` VALUES ('20211003', '李磊', '物理学院', '物理', 56);
INSERT INTO `stu` VALUES ('20211003', '李磊', '物理学院', '政治', 76);
INSERT INTO `stu` VALUES ('20211003', '李磊', '物理学院', '政治', 90);
INSERT INTO `stu` VALUES ('20221201', '郭靖', '外语学院', NULL, NULL);
INSERT INTO `stu` VALUES ('20221202', '黄蓉', '马克思主义学院', NULL, NULL);
INSERT INTO `stu` VALUES ('20221203', 'Mary', '外语学院', '程序设计', 88);
INSERT INTO `stu` VALUES ('20221203', 'Mary', '外语学院', '英语', 50);-- ----------------------------
-- View structure for v_sum_score
-- ----------------------------
DROP VIEW IF EXISTS `v_sum_score`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_sum_score` AS select `stu`.`id` AS `id`,`stu`.`name` AS `Name`,sum(`score`.`score`) AS `SumScore` from (`stu` join `score` on((`stu`.`id` = `score`.`stuId`))) group by `stu`.`id`;-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES ('20201001', '张三', 320);
INSERT INTO `stu` VALUES ('20201002', '李四', 317);
INSERT INTO `stu` VALUES ('20201003', '王五', 330);
INSERT INTO `stu` VALUES ('20211001', '韦俊豪', 540);
INSERT INTO `stu` VALUES ('20191002', '冯红云', 145);
INSERT INTO `stu` VALUES ('20201004', '赵六', 168);
INSERT INTO `stu` VALUES ('20191003', '冯海', 74);
INSERT INTO `stu` VALUES ('20211002', '雷淳雅', 339);
INSERT INTO `stu` VALUES ('20191001', '许和雅', 175);
INSERT INTO `stu` VALUES ('20191004', '刘一凡', 330);
INSERT INTO `stu` VALUES ('20211003', '李磊', 302);
INSERT INTO `stu` VALUES ('20221203', 'Mary', 138);SET FOREIGN_KEY_CHECKS = 1;

  1. 查询显示成绩表中的最高分，平均分。

  2. 查询学生的学号，姓名 和 出生日期，按院系编号降序。

  3. 查询所有学生的学号，姓名和院系名。

  4. 查询参加了高等数学课程的考试的学生姓名和考试成绩。

  5. 查询每个学院的学生人数。

  6. 查询平均分最高的 5 名学生的学号，姓名和平均分。

  7. 查询总学分高于 10 分的所有学生。

  8. 查询数学学院的学生的学号，姓名，所选课程的成绩和等级,并按成绩从高到低排序。

  9. 查询数学学院的学生的学号，姓名，获得的学分并按学分从高到低排序。

  10. 查询英语成绩最高的学生的学号，姓名，所有功课的成绩和所在学院。

  11. 查询比计算机学院所有学生的总分都高的数学学院的学生的学号，姓名和总分。

2.2 视图的使用

此题需要用到数据库 YGGL，此数据库再前面给出的习题中已创建，未创建此数据库的同学请运行下述代码创建，已经创建的同学可以跳过此步骤！

drop database if exists yggl;create database if not exists yggl;USE yggl;drop table if exists departments;
CREATE TABLE Departments(DepartmentID char(3) NOT NULL PRIMARY KEY COMMENT '部门编号',DepartmentName char(20) NOT NULL COMMENT '部门名',Note text NULL COMMENT '备注'
);drop table if exists salary;
CREATE TABLE Salary(EmployeeID char(6) NOT NULL PRIMARY KEY COMMENT '员工编号',InCome float NOT NULL COMMENT '收入',OutCome float NOT NULL COMMENT '支出'
);drop table if exists employees;
CREATE TABLE Employees(EmployeeID char(6) NOT NULL PRIMARY KEY COMMENT '员工编号',Name char(10) NOT NULL COMMENT '姓名',Education char(4) NOT NULL COMMENT '学历',Birthday date NOT NULL COMMENT '出生日期',Sex char(2) NOT NULL COMMENT '性别',WorkYear tinyint(1) COMMENT '工作时间',Address char(20) NULL COMMENT '地址',PhoneNumber char(12) NULL COMMENT '电话号码',DepartmentID char(3) NOT NULL COMMENT '部门编号'
);insert into Departments values('1','财务部',null),('2','人力资源部',null),('3','经理办公室',null),('4','研发部',null),('5','市场部',null)
;insert into Employees values('000001','王林','大专','1966-1-23',1,8,'中山路32-1-508','83355668','2'),('010008','伍容华','本科','1976-3-28',1,3,'北京东路100-2','83321321','1'),('020010','王向蓉','硕士','1982-12-9',1,2,'四牌楼10-10-108','83792361','1'),('020018','李丽','大专','1960-7-30',0,6,'中山东路102-2','83413301','1'),('102201','刘明','本科','1972-10-18',1,3,'虎踞路100-2','83606608','5'),('102208','朱骏','硕士','1965-9-28',1,2,'牌楼巷5-3-106','84708817','5'),('108991','钟敏','硕士','1979-8-10',0,4,'中山路10-3-105','83346722','3'),('111006','张石兵','本科','1974-10-1',1,1,'解放路34-1-203','84563418','5'),('210678','林涛','大专','1977-4-2',1,2,'中山北路24-35','83467336','3'),('302566','李玉珉','本科','1968-9-20',1,3,'热河路209-3','58765991','4'),('308759','叶凡','本科','1978-11-18',1,2,'北京西路3-7-52','83308901','4'),('504209','陈林琳','大专','1969-9-3',0,5,'汉中路120-4-12','84468158','4')
;insert into Salary values('000001',2100.8,123.09),('010008',1582.62,88.03),('102201',2569.88,185.65),('111006',1987.01,79.58),('504209',2066.15,108),('302566',2980.7,210.2),('108991',3259.98,281.52),('020010',2860,198),('020018',2347.68,180),('308759',2531.98,199.08),('210678',2240,121),('102208',1980,100)
;

2.2.1 创建视图

  1. 在 YGGL 数据库创建视图 ds_view，视图包含 Departments 表的全部列。

  2. 在 YGGL 数据库创建视图 Employees_view，视图包含员工号码、姓名和实际收入。

  3. 创建视图，包含员工号码、姓名、所在部门名称和实际收入列。

2.2.2 查询视图

  1. 从视图 ds_view 中查询出部门号为 3 的部门名称。

  2. 从视图 Employees_view 中查询出姓名为“王林”的员工的实际收入。

2.2.3 更新视图

  1. 向视图 ds_view 中插入一行数据：6，财务部，财务管理。

  2. 尝试向视图 Employees_view 中插入一行数据，看看会发生什么。
    insert into Employees_view values('100000', '邓文辉', '9999');

  3. 修改视图 ds_view，将部门号为 5 的部门名称修改为“生产车间”。

  4. 修改视图 Employees_view 中号码为“000001”的员工的姓名为“王浩”。

  5. 删除视图 ds_view 中部门号为“1”的数据。

2.2.4 删除视图

  1. 删除视图 ds_view。

3. 解答

3.1 查询

  1. 查询显示成绩表中的最高分，平均分。

    select max(score) as 最高分, avg(score) as 平均分 from score;

  2. 查询学生的学号，姓名 和 出生日期，按院系编号降序。

    select id, `name`, birthday
from stu
ORDER BY departmentId desc;

  3. 查询所有学生的学号，姓名和院系名。

    select stu.id as 学号, stu.`name` as 姓名, department.`name` as 院系
from stu join department on stu.departmentId = department.id;

  4. 查询参加了高等数学课程的考试的学生姓名和考试成绩。

    select stu.`name`, score.score
from stu join score on stu.id = score.stuIdjoin lesson on score.LessonId = lesson.lessonid
where lesson.lessonName = '数学';

  5. 查询每个学院的学生人数。

    select department.`name` as 院系, count(*) as 学生人数
from department join stu on department.id = stu.departmentId
GROUP BY department.`name`;

  6. 查询平均分最高的 5 名学生的学号，姓名和平均分。

    select stu.id, stu.`name`, avg(score.score) as 平均分
from stu join score on stu.id = score.stuId
GROUP BY stu.id
ORDER BY avg(score.score) desc
limit 5;

  7. 查询总学分高于 10 分的所有学生。

    select stu.`name`, sum(if(score.score >= 60, lesson.xf, 0)) as 学分
from stu join score on stu.id = score.stuidjoin lesson on score.lessonid = lesson.lessonid
GROUP BY stu.`name`
having sum(if(score.score >= 60, lesson.xf, 0)) > 10;

  8. 查询数学学院的学生的学号，姓名，所选课程的成绩和等级,并按成绩从高到低排序。

    select stu.id, stu.`name`, lesson.lessonName, score.score, grade.grade
from score join stu on score.stuid = stu.idjoin lesson on score.LessonId = lesson.lessonidjoin department on stu.departmentId = department.idjoin grade on score.scoreBETWEEN grade.lowScore and grade.highScore
where department.`name` = '数学学院'
ORDER BY score.score desc;

  9. 查询数学学院的学生的学号，姓名，获得的学分并按学分从高到低排序。

    select stu.id, stu.`name`, sum(if(score.score >= 60, lesson.xf, 0)) as 学分
from stu join score on stu.id = score.stuidjoin lesson on score.LessonId = lesson.lessonidjoin department on stu.departmentId = department.id
where department.`name` = '数学学院'
GROUP BY stu.id
ORDER BY 学分 desc;

  10. 查询英语成绩最高的学生的学号，姓名，所有功课的成绩和所在学院。

    select stu.id, stu.`name`, score.score, department.`name` as 院系
from stu join score on stu.id = score.stuIdjoin department on stu.departmentId = department.id
where stu.id = 
(select stu.idfrom stu join score on stu.id = score.stuIdjoin lesson on score.LessonId = lesson.lessonidwhere lesson.lessonName = '英语'ORDER BY score.score desclimit 1
);

  11. 查询比计算机学院所有学生的总分都高的数学学院的学生的学号，姓名和总分。

    select stu.id, stu.`name`, sum(score.score) as 总分
from stu join score on stu.id = score.stuIdjoin department on stu.departmentId = department.id
where department.`name` = '数学学院'
GROUP BY stu.id
having 总分 > 
(select sum(score.score)from stu join score on stu.id = score.stuIdjoin department on stu.departmentId = department.idwhere department.`name` = '计算机学院'GROUP BY stu.idORDER BY sum(score.score) desclimit 1
);

3.2 视图的使用

3.2.1 创建视图

use yggl;

  1. 在 YGGL 数据库创建视图 ds_view，视图包含 Departments 表的全部列。

    CREATE OR REPLACE VIEW `yggl`.`ds_view` AS SELECT * FROM departments;

  2. 在 YGGL 数据库创建视图 Employees_view，视图包含员工号码、姓名和实际收入。

    CREATE OR REPLACE VIEW `yggl`.`Employees_view` AS SELECTemployees.EmployeeID, employees.`Name`, salary.InCome - salary.OutCome as 实际收入
FROMemployeesINNER JOINsalaryON employees.EmployeeID = salary.EmployeeID;

  3. 创建视图，包含员工号码、姓名、所在部门名称和实际收入列。

    CREATE VIEW `yggl`.`创建视图` AS SELECTemployees.EmployeeID, employees.`Name`, departments.DepartmentName, salary.InCome - salary.OutCome as 实际收入
FROMemployeesINNER JOINdepartmentsON employees.DepartmentID = departments.DepartmentIDINNER JOINsalaryON employees.EmployeeID = salary.EmployeeID;

3.2.2 查询视图

  1. 从视图 ds_view 中查询出部门号为 3 的部门名称。

    select DepartmentName
from ds_view
where DepartmentID = 3;

  2. 从视图 Employees_view 中查询出姓名为“王林”的员工的实际收入。

    select 实际收入
from employees_view
where `Name` = '王林';

3.2.3 更新视图

  1. 向视图 ds_view 中插入一行数据：6，财务部，财务管理。

    insert into ds_view values('6', '财务部', '财务管理');

  2. 尝试向视图 Employees_view 中插入一行数据，看看会发生什么。
    insert into Employees_view values('100000', '邓文辉', '9999');

    会报错，错误如下：
> 1394 - Can not insert into join view 'yggl.employees_view' without fields list原因是不能修改联合视图！

  3. 修改视图 ds_view，将部门号为 5 的部门名称修改为“生产车间”。

    update ds_view set DepartmentName = '生产车间'where DepartmentID = '5';

  4. 修改视图 Employees_view 中号码为“000001”的员工的姓名为“王浩”。

    update employees_view set `Name` = '王浩'where EmployeeID = '000001';

  5. 删除视图 ds_view 中部门号为“1”的数据。

    delete from ds_viewwhere DepartmentID = '1';

3.2.4 删除视图

  1. 删除视图 ds_view。
    # 删除视图 ds_view
drop view if exists ds_view;

上一篇文章：【数据库——MySQL】（10）视图和索引

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/143879.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

通俗易懂了解大语言模型LLM发展历程

通俗易懂了解大语言模型LLM发展历程

1.大语言模型研究路程 NLP的发展阶段大致可以分为以下几个阶段： 词向量词嵌入embedding句向量和全文向量理解上下文超大模型与模型统一 1.1词向量 将自然语言的词使用向量表示，一般构造词语字典，然后使用one-hot表示。   例如2个单词&…
阅读更多...
【STM32】IAP升级01 bootloader实现以及APP配置（主要）

【STM32】IAP升级01 bootloader实现以及APP配置（主要）

APP程序以及中断向量表的偏移设置 前言 通过之前的了解 之前的了解，我们知道实现IAP升级需要两个条件： 1.APP程序必须在 IAP 程序之后的某个偏移量为 x 的地址开始； 2.APP程序的中断向量表相应的移动，移动的偏移量为 x&#xff…
阅读更多...
深入理解 pytest.main()：Python 测试框架的核心功能解析

深入理解 pytest.main()：Python 测试框架的核心功能解析

前言 笔者平常运行pytest用例时，通常使用命令行方式，像这样 pytest -v pxl/test_dir/test_demo.py::TestDemo::test_my_var，执行某一条case，但每次命令行敲也挺麻烦的。那如何在python代码中调用pytest呢？带着疑问一…
阅读更多...
APP开发费用计算方法

APP开发费用计算方法

计算开发移动应用（APP）的费用涉及多个因素，包括项目的规模、复杂性、所需功能、技术选择、开发团队的经验、地理位置和市场需求等。以下是一些考虑开发APP费用的关键因素以及一般的费用计算方法，希望对大家有所帮助。北京木奇移动…
阅读更多...
第八天：gec6818arm开发板和Ubuntu中安装并且编译移植mysql驱动连接QT执行程序

第八天：gec6818arm开发板和Ubuntu中安装并且编译移植mysql驱动连接QT执行程序

一、Ubuntu18.04中安装并且编译移植mysql驱动程序连接qt执行程序 1 、安装Mysql sudo apt-get install mysql-serverapt-get isntall mysql-clientsudo apt-get install libmysqlclient-d2、查看是否安装成功，即查看MySQL版本 mysql --version 3、MySQL启动…
阅读更多...
PHP8中伪变量“$this-＞”和操作符“::”的使用-PHP8知识详解

PHP8中伪变量“$this-＞”和操作符“::”的使用-PHP8知识详解

对象不仅可以调用自己的变量和方法，也可以调用类中的变量和方法。PHP8通过伪变量“$this->”和操作符“::”来实现这些功能。 1.伪变量“$this->” 在通过对象名->方法调用对象的方法时，如果不知道对象的名称，而又想调用类中的方法…
阅读更多...
【新版】系统架构设计师 - 层次式架构设计理论与实践

【新版】系统架构设计师 - 层次式架构设计理论与实践

个人总结，仅供参考，欢迎加好友一起讨论 文章目录 架构 - 层次式架构设计理论与实践考点摘要层次式体系结构概述表现层框架设计MVC模式MVP模式MVVM模式使用XML设计表现层表现层中UIP设计思想 中间层架构设计业务逻辑层工作流设计业务逻辑层设计 数据访问层…
阅读更多...
三维模型3DTile格式轻量化压缩处理重难点分析

三维模型3DTile格式轻量化压缩处理重难点分析

三维模型3DTile格式轻量化压缩处理重难点分析 在对三维模型3DTile格式进行轻量化压缩处理的过程中，存在一些重要而又困难的问题需要解决。以下是几个主要的重难点： 1、压缩率和模型质量之间的平衡：压缩技术的目标是尽可能地减少数据大小&…
阅读更多...
【机器学习】期望最大算法（EM算法）解析：Expectation Maximization Algorithm

【机器学习】期望最大算法（EM算法）解析：Expectation Maximization Algorithm

【机器学习】期望最大算法（EM算法）：Expectation Maximization Algorithm 文章目录 【机器学习】期望最大算法（EM算法）：Expectation Maximization Algorithm1. 介绍2. EM算法数学描述3. EM算法流程4. 两个问…
阅读更多...
【AI视野·今日NLP 自然语言处理论文速览 第四十一期】Tue, 26 Sep 2023

【AI视野·今日NLP 自然语言处理论文速览 第四十一期】Tue, 26 Sep 2023

AI视野今日CS.NLP 自然语言处理论文速览 Tue, 26 Sep 2023 Totally 75 papers 👉上期速览✈更多精彩请移步主页 Daily Computation and Language Papers Physics of Language Models: Part 3.1, Knowledge Storage and Extraction Authors Zeyuan Allen Zhu, Yuanz…
阅读更多...
Databend 源码阅读：配置管理

Databend 源码阅读：配置管理

作者：尚卓燃（PsiACE）澳门科技大学在读硕士，Databend 研发工程师实习生 Apache OpenDAL(Incubating) Committer https://github.com/PsiACE 对于 Databend 这样复杂的数据库服务端程序，往往需要支持大量的可配置选项&am…
阅读更多...
k8s安装master节点遇到问题解决

k8s安装master节点遇到问题解决

1、安装k8s-1.19安装文档地址： https://kuboard.cn/install/history-k8s/install-k8s-1.19.x.html 2、按照文档中内容执行完master节点的操作报异常： 在执行： curl -sSL https://kuboard.cn/install-script/v1.19.x/init_master.sh | sh …
阅读更多...
npm安装心得（依赖库Python及node-sass依赖环境）

npm安装心得（依赖库Python及node-sass依赖环境）

在使用vue的开发环境过程中，总会遇到这样哪样的安装或者打包错误， vue运行或打包常见错误如下： 1. npm install时 node-sass npm ERR command failed （可能是node.js的版本和node-sass的版本不符，就是卸掉原来的node.…
阅读更多...
[滴水逆向]03-12 pe头字段说明课后作业，输出pe结构

[滴水逆向]03-12 pe头字段说明课后作业，输出pe结构

#include <iostream> #include <windows.h> using namespace std; #pragma warning(disable:4996) //DOC结构 typedef struct _DOC_HEADER {WORD e_magic;WORD e_cblp;WORD e_cp;WORD e_crlc;WORD e_cparhar;WORD e_minalloc;WORD e_maxalloc;WORD e_ss;WO…
阅读更多...
RHCE---Web 服务器

RHCE---Web 服务器

文章目录 目录 文章目录 前言 一.Web服务器概述 网址及HTTP协议概述&#xff1a; HTTP协议请求过程&#xff1a; 二.搭建动态HTTP网页 动态网页概述&#xff1a; 搭建动态的HTTP协议网页&#xff1a; 总结 前言 通过上一个章节的学习了解了时间服务器以及远程连接服务器&a…
阅读更多...
C++中实现雪花算法来在秒级以及毫秒及时间内生成唯一id

C++中实现雪花算法来在秒级以及毫秒及时间内生成唯一id

1、雪花算法原理 雪花算法&#xff08;Snowflake Algorithm&#xff09;是一种用于生成唯一ID的算法&#xff0c;通常用于分布式系统中&#xff0c;以确保生成的ID在整个分布式系统中具有唯一性。它的名称来源于雪花的形状&#xff0c;因为生成的ID通常是64位的整数&#xff0…
阅读更多...
Prometheus-Rules 实战

Prometheus-Rules 实战

文章目录 1 node rules2 nginx rule2.1 Nginx 4xx 错误率太多2.2 Nginx 5xx 错误率太多2.3 Nginx 延迟高 3 mysql rule3.1 MySQL 宕机3.2 实例连接数过多3.3 MySQL高线程运行3.4 MySQL 从服务器 IO 线程没有运行3.5 MySQL 从服务器 SQL 线程没有运行3.6 MySQL复制滞后3.7 慢查询…
阅读更多...
作为SiteGPT替代品，HelpLook的优势是什么？

作为SiteGPT替代品，HelpLook的优势是什么？

在当今快节奏的数字化世界中&#xff0c;企业不断寻求创新方式来简化运营并增强客户体验。由于聊天机器人能够自动化任务、提供快速响应并提供个性化互动&#xff0c;它们在业务运营中的使用变得非常重要。因此&#xff0c;企业越来越意识到像SiteGPT和HelpLook这样高效的聊天机…
阅读更多...
I/O

I/O

IO 流简介 IO 即 Input/Output&#xff0c;输入和输出。数据输入到计算机内存的过程即输入&#xff0c;反之输出到外部存储&#xff08;比如数据库&#xff0c;文件&#xff0c;远程主机&#xff09;的过程即输出。IO 流在 Java 中分为输入流和输出流&#xff0c;而根据数据的…
阅读更多...
Spring事务不生效的场景的解决方案

Spring事务不生效的场景的解决方案

一、前言 在Java Web开发中&#xff0c;使用Spring框架可以大大简化开发人员的工作。其中&#xff0c;事务管理是Spring框架中的一个重要功能&#xff0c;它可以确保多个数据库操作要么全部成功&#xff0c;要么全部失败。但是&#xff0c;在实际开发中&#xff0c;我们可能会…
阅读更多...
最新文章

Copyright @ 2022~2023