MySQL数据库练习——视图

最新推荐文章于 2024-10-30 22:57:10 发布

南村群童欺我老无力.

最新推荐文章于 2024-10-30 22:57:10 发布

阅读量1.9k

点赞数 64

分类专栏： Mysql复习专栏文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2301_80388658/article/details/138489955

版权

Mysql复习专栏专栏收录该内容

21 篇文章 0 订阅

订阅专栏

schooldb库——utf8字符集——utf8_general_ci排序规则

先创建库，再去使用下列的DDL语句。

DDL

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `createDate` datetime DEFAULT NULL COMMENT '创建时间',
  `modifyDate` datetime DEFAULT NULL COMMENT '修改时间',
  `userName` varchar(30) NOT NULL COMMENT '学生名称',
  `pwd` varchar(36) DEFAULT NULL COMMENT '密码',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
  `sex` char(2) DEFAULT '男' COMMENT '性别',
  `className` varchar(20) DEFAULT NULL,
  `addRess` varchar(255) DEFAULT NULL COMMENT '地址',
  `introduce` varchar(255) DEFAULT NULL COMMENT '简介',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseName` varchar(20) NOT NULL COMMENT '课程名称',
  `department` varchar(30) NOT NULL,
  `lv` int(11) DEFAULT NULL COMMENT '年级',
  `number` int(11) DEFAULT NULL COMMENT '课程人数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` int(11) NOT NULL COMMENT '课程编号',
  `studentId` int(11) NOT NULL,
  `result` float(5,2) NOT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DML

INSERT INTO `student` VALUES ('1', '2023-09-16 09:22:22', '2024-04-15 10:12:12', '张三', '123456', '15612345678', '19', '男', '信息1班', '石家庄', '一首张三的歌送给大家');
INSERT INTO `student` VALUES ('2', '2023-09-17 09:22:22', '2024-04-15 08:11:12', '李四', '123156', '15612345178', '19', '女', '信息1班', '石家庄', '一曲美丽的舞蹈送给大家');
INSERT INTO `student` VALUES ('3', '2023-09-18 09:23:22', '2024-04-15 11:12:12', '王五', '123256', '15612345278', '20', '男', '信息2班', '沈阳', '大刀王五也是英雄');
INSERT INTO `student` VALUES ('4', '2023-09-19 09:24:22', '2024-04-15 12:13:12', '赵六', '123356', '15612345378', '18', '男', '信息1班', '甘肃', '六六大顺');
INSERT INTO `student` VALUES ('5', '2023-09-10 09:25:22', '2024-04-15 13:14:12', '阮小七', '143456', '15612345678', '19', '男', '信息2班', '石家庄', '阮氏三雄，小七最霸气');
INSERT INTO `student` VALUES ('6', '2023-09-21 09:26:22', '2024-04-15 14:15:12', '朱重八', '153456', '15612355678', '18', '男', '信息2班', '浙江', '开局一个碗。');
INSERT INTO `student` VALUES ('7', '2023-09-22 09:27:22', '2024-04-15 15:16:12', '苏老九', '163456', '15612365678', '20', '男', '信息2班', '石家庄', '武状元');
INSERT INTO `student` VALUES ('8', '2023-09-23 09:28:22', '2024-04-15 16:17:12', '王石', '123476', '15612345778', '22', '男', '信息1班', '徐蚌', '钻石王老五');
INSERT INTO `student` VALUES ('9', '2023-09-24 19:29:22', '2024-04-15 17:18:12', '萧十一', '128456', '15612385678', '21', '男', '信息1班', '石家庄', '帅气逼人，英雄也。');
INSERT INTO `student` VALUES ('10', '2023-09-25 09:20:22', '2024-04-15 18:19:12', '宫十二', '129456', '15612395678', '22', '女', '信息1班', '杭州', '十二条舔狗和一位绿茶的故事');
INSERT INTO `course` VALUES ('1', '计算机基础', '信息工程系', '1', '800');
INSERT INTO `course` VALUES ('2', 'MySQL应用基础', '信息工程系', '2', '567');
INSERT INTO `course` VALUES ('3', 'Java基础', '信息工程系', '1', '567');
INSERT INTO `course` VALUES ('4', '专业导论', '信息工程系', '1', '645');
INSERT INTO `course` VALUES ('5', 'Excel实战训练', '信息工程系', '1', '863');
INSERT INTO `course` VALUES ('6', '大学英语', '教务处', '1', '432');
INSERT INTO `course` VALUES ('7', '大学语文', '教务处', '1', '533');
INSERT INTO `course` VALUES ('8', '高等数学(一)', '教务处', '2', '456');
INSERT INTO `score` VALUES ('1', '1', '1', '89.00');
INSERT INTO `score` VALUES ('2', '1', '2', '89.50');
INSERT INTO `score` VALUES ('3', '1', '3', '98.00');
INSERT INTO `score` VALUES ('4', '1', '4', '82.00');
INSERT INTO `score` VALUES ('5', '1', '5', '59.00');
INSERT INTO `score` VALUES ('6', '1', '6', '22.00');
INSERT INTO `score` VALUES ('7', '1', '7', '68.00');
INSERT INTO `score` VALUES ('8', '1', '8', '7.00');
INSERT INTO `score` VALUES ('9', '1', '9', '91.00');
INSERT INTO `score` VALUES ('10', '1', '10', '69.00');
INSERT INTO `score` VALUES ('11', '2', '1', '99.00');
INSERT INTO `score` VALUES ('12', '2', '2', '69.50');
INSERT INTO `score` VALUES ('13', '2', '3', '58.00');
INSERT INTO `score` VALUES ('14', '2', '4', '72.00');
INSERT INTO `score` VALUES ('15', '2', '5', '89.00');
INSERT INTO `score` VALUES ('16', '2', '6', '82.00');
INSERT INTO `score` VALUES ('17', '2', '7', '58.00');
INSERT INTO `score` VALUES ('18', '2', '8', '77.00');
INSERT INTO `score` VALUES ('19', '2', '9', '17.00');
INSERT INTO `score` VALUES ('20', '2', '10', '79.00');
INSERT INTO `score` VALUES ('21', '3', '1', '55.00');
INSERT INTO `score` VALUES ('22', '3', '2', '77.50');
INSERT INTO `score` VALUES ('23', '3', '3', '88.00');
INSERT INTO `score` VALUES ('24', '3', '4', '12.00');
INSERT INTO `score` VALUES ('25', '3', '5', '88.00');
INSERT INTO `score` VALUES ('26', '3', '6', '71.00');
INSERT INTO `score` VALUES ('27', '3', '7', '36.00');
INSERT INTO `score` VALUES ('28', '3', '8', '94.00');
INSERT INTO `score` VALUES ('29', '3', '9', '66.00');
INSERT INTO `score` VALUES ('30', '3', '10', '34.00');
INSERT INTO `score` VALUES ('31', '4', '1', '55.00');
INSERT INTO `score` VALUES ('32', '4', '2', '87.50');
INSERT INTO `score` VALUES ('33', '4', '3', '88.00');
INSERT INTO `score` VALUES ('34', '4', '4', '82.00');
INSERT INTO `score` VALUES ('35', '4', '5', '88.00');
INSERT INTO `score` VALUES ('36', '4', '6', '31.00');
INSERT INTO `score` VALUES ('37', '4', '7', '86.00');
INSERT INTO `score` VALUES ('38', '4', '8', '94.00');
INSERT INTO `score` VALUES ('39', '4', '9', '86.00');
INSERT INTO `score` VALUES ('40', '4', '10', '34.00');
INSERT INTO `score` VALUES ('41', '5', '1', '95.00');
INSERT INTO `score` VALUES ('42', '5', '2', '27.50');
INSERT INTO `score` VALUES ('43', '5', '3', '88.00');
INSERT INTO `score` VALUES ('44', '5', '4', '82.60');
INSERT INTO `score` VALUES ('45', '5', '5', '88.00');
INSERT INTO `score` VALUES ('46', '5', '6', '31.50');
INSERT INTO `score` VALUES ('47', '5', '7', '86.50');
INSERT INTO `score` VALUES ('48', '5', '8', '94.70');
INSERT INTO `score` VALUES ('49', '5', '9', '86.00');
INSERT INTO `score` VALUES ('50', '5', '10', '34.00');
INSERT INTO `score` VALUES ('51', '6', '1', '75.00');
INSERT INTO `score` VALUES ('52', '6', '2', '77.50');
INSERT INTO `score` VALUES ('53', '6', '3', '88.00');
INSERT INTO `score` VALUES ('54', '6', '4', '72.60');
INSERT INTO `score` VALUES ('55', '6', '5', '88.00');
INSERT INTO `score` VALUES ('56', '6', '6', '71.50');
INSERT INTO `score` VALUES ('57', '6', '7', '76.50');
INSERT INTO `score` VALUES ('58', '6', '8', '94.70');
INSERT INTO `score` VALUES ('59', '6', '9', '76.00');
INSERT INTO `score` VALUES ('60', '6', '10', '74.00');
INSERT INTO `score` VALUES ('61', '7', '1', '75.00');
INSERT INTO `score` VALUES ('62', '7', '2', '67.50');
INSERT INTO `score` VALUES ('63', '7', '3', '68.00');
INSERT INTO `score` VALUES ('64', '7', '4', '72.60');
INSERT INTO `score` VALUES ('65', '7', '5', '88.00');
INSERT INTO `score` VALUES ('66', '7', '6', '61.50');
INSERT INTO `score` VALUES ('67', '7', '7', '76.50');
INSERT INTO `score` VALUES ('68', '7', '8', '64.70');
INSERT INTO `score` VALUES ('69', '7', '9', '76.00');
INSERT INTO `score` VALUES ('70', '7', '10', '64.00');
INSERT INTO `score` VALUES ('71', '8', '1', '95.00');
INSERT INTO `score` VALUES ('72', '8', '2', '97.00');
INSERT INTO `score` VALUES ('73', '8', '3', '98.00');
INSERT INTO `score` VALUES ('74', '8', '4', '92.00');
INSERT INTO `score` VALUES ('75', '8', '5', '98.00');
INSERT INTO `score` VALUES ('76', '8', '6', '91.00');
INSERT INTO `score` VALUES ('77', '8', '7', '96.00');
INSERT INTO `score` VALUES ('78', '8', '8', '94.00');
INSERT INTO `score` VALUES ('79', '8', '9', '96.00');
INSERT INTO `score` VALUES ('80', '8', '10', '94.00');

DQL

创建视图，查询所有学生的各科目平均成绩。

# 创建视图
create VIEW showinfo
AS
(SELECT userName,avg(result) from student INNER JOIN score on student.id=score.studentId GROUP BY studentId);
 
# 查询视图
select * from showinfo;

执行结果：

查询结果:

并根据平均成绩进行倒序排列。

# 创建视图
create VIEW showinfo_order
AS
(SELECT userName,avg(result) from student INNER JOIN score on student.id=score.studentId GROUP BY studentId ORDER BY avg(result) desc);
 
# 查询视图
select * from showinfo_order;

自主练习：

创建视图，查询所有学生的各科目总成绩，并根据总成绩进行倒序排列。

总结

视图是为了方便后期查询使用，可以在视图中直接查看，很是方便。

视图详解：

视图（View）是一种虚拟的表，它基于数据库中的一个或多个表创建，用于提供一个特定的数据视图或查询结果。

以下是视图的一些详细解释：

简化查询：可以将复杂的查询语句封装为一个视图，使后续的查询更加简单和直观。

数据抽象：隐藏了底层表的结构和关联，提供了一个简化的数据接口。

安全性：可以限制用户只能访问视图中定义的数据，而无法访问底层表的其他数据。

数据独立性：当底层表的结构发生变化时，只需修改视图，而无需修改依赖于该视图的查询和应用程序。
提高性能：可以在视图中进行预编译和优化，提高查询效率。

方便数据管理：可以将常用的查询结果作为视图进行管理，方便数据的维护和使用。

可重用性：多个查询可以共享同一个视图，减少重复编写查询语句的工作。

逻辑数据独立性：使得应用程序不依赖于底层表的具体结构。创建视图的语法通常包括指定视图的名称、选择用于构建视图的列等。使用视图时，就像使用普通表一样进行查询操作。

需要注意的是，视图并不是实际存在的物理表，它只是根据定义的查询语句生成的结果集。在使用视图时，要注意以下几点：

视图可能会降低性能，尤其是在复杂的视图或涉及大量数据的情况下。

对视图的更新操作可能会受到限制，具体取决于视图的定义和底层表的结构。

视图的创建和维护需要谨慎，以确保其正确性和有效性。

视图在以下场景中能发挥较好的作用：

复杂查询简化：例如，有多个关联表，需要经常执行复杂的联合查询。可以创建一个视图来封装这个复杂查询，以后只需使用视图即可获取所需数据。

提供特定数据子集：如只允许用户访问特定的客户数据，可以创建一个只包含这些数据的视图。
数据安全控制：通过视图限制用户对敏感数据的访问。

数据汇总和统计：创建一个视图来汇总和计算特定的业务指标。例如，假设有一个销售系统，其中包含客户表、订单表和产品表。可以创建一个视图来获取每个客户的总销售额，这样在查询客户的总销售额时，无需每次都执行复杂的连接和计算。

跨多个表的查询：如果经常需要从多个表中获取相关数据，可以创建一个视图来简化这个查询。

数据隐藏和抽象：可以隐藏表之间的复杂关系，只向用户展示他们需要的信息。例如，将复杂的关联表结构封装在视图中，用户只需使用视图，无需了解底层表的结构。

特定场景的数据访问：根据不同的业务需求，创建特定的视图，满足特定场景的数据访问需求。比如，为某个特定报告创建一个视图，包含所需的特定列和数据。