网站国际化建设方案,建站公司推荐首推万维科技,上海企业网站营销电话,产品设计专业最好的大学目录
一. 视图 1.1 含义 1.2 视图的基本语法 1.3 视图的实操
二. 外连接、内连接和子查询的使用 2.1 SQL脚本 2.2 使用外连接、内连接和子查询进行解答
三. 思维导图 一. 视图 1.1 含义 视图#xff08;view#xff09;是一种虚拟存在的表#xff0c;是一个逻辑表#x…目录
一. 视图 1.1 含义 1.2 视图的基本语法 1.3 视图的实操
二. 外连接、内连接和子查询的使用 2.1 SQL脚本 2.2 使用外连接、内连接和子查询进行解答
三. 思维导图 一. 视图 1.1 含义 视图view是一种虚拟存在的表是一个逻辑表视图中的数据并不在数据库中实际存在。 行和列数据来自我们定义视图的查询中使用的表并且是在使用视图时动态生成的。作为一个select语句保存在数据字典中的通俗的讲视图只保存了查询的SQL逻辑不保存查询结果。所以我们在创建视图的时候主要的工作就落在创建这条SQL查询语句上。 1.2 视图的基本语法
创建视图
create view 视图名
as
查询语句;
修改视图
#方式一
create or replace view 视图名
as
查询语句;#方式二
alert view 视图名
as
查询语句;
查看视图
#查看视图相关字段
DESC 视图名;#查看视图相关语句
SHOW CREATE VIEW 视图名;
删除视图
drop view 视图名,视图名,...; 1.3 视图的实操
创建视图
CREATE VIEW v_student_score AS SELECT
s.*,
sc.cid,
sc.score
FROMt_mysql_student s,t_mysql_score sc
WHEREs.sid sc.sid; 查看视图
查看视图相关字段
DESC v_student_score; 查看视图创建的相关语句
SHOW CREATE VIEW v_student_score; 删除视图
DROP VIEW v_student_score;
可以看见我们之前创建的视图已经没有了 二. 外连接、内连接和子查询的使用 2.1 SQL脚本
/*Navicat Premium Data TransferSource Server : localhostSource Server Type : MySQLSource Server Version : 80018Source Host : localhost:3306Source Schema : mybatis_ssmTarget Server Type : MySQLTarget Server Version : 80018File Encoding : 65001Date: 04/07/2023 23:53:33
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS 0;-- ----------------------------
-- Table structure for t_mysql_course
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_course;
CREATE TABLE t_mysql_course (cid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程编号,cname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程名称,tid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师编号,PRIMARY KEY (cid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 课程信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_course
-- ----------------------------
INSERT INTO t_mysql_course VALUES (01, 语文, 02);
INSERT INTO t_mysql_course VALUES (02, 数学, 01);
INSERT INTO t_mysql_course VALUES (03, 英语, 03);-- ----------------------------
-- Table structure for t_mysql_score
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_score;
CREATE TABLE t_mysql_score (sid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生编号外键,cid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程编号外键,score float NULL DEFAULT 0 COMMENT 成绩,INDEX sid(sid) USING BTREE,INDEX cid(cid) USING BTREE,CONSTRAINT t_mysql_score_ibfk_1 FOREIGN KEY (sid) REFERENCES t_mysql_student (sid) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT t_mysql_score_ibfk_2 FOREIGN KEY (cid) REFERENCES t_mysql_course (cid) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 成绩信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_score
-- ----------------------------
INSERT INTO t_mysql_score VALUES (01, 01, 80);
INSERT INTO t_mysql_score VALUES (01, 02, 90);
INSERT INTO t_mysql_score VALUES (01, 03, 99);
INSERT INTO t_mysql_score VALUES (02, 01, 70);
INSERT INTO t_mysql_score VALUES (02, 02, 60);
INSERT INTO t_mysql_score VALUES (02, 03, 80);
INSERT INTO t_mysql_score VALUES (03, 01, 80);
INSERT INTO t_mysql_score VALUES (03, 02, 80);
INSERT INTO t_mysql_score VALUES (03, 03, 80);
INSERT INTO t_mysql_score VALUES (04, 01, 50);
INSERT INTO t_mysql_score VALUES (04, 02, 30);
INSERT INTO t_mysql_score VALUES (04, 03, 20);
INSERT INTO t_mysql_score VALUES (05, 01, 76);
INSERT INTO t_mysql_score VALUES (05, 02, 87);
INSERT INTO t_mysql_score VALUES (06, 01, 31);
INSERT INTO t_mysql_score VALUES (06, 03, 34);
INSERT INTO t_mysql_score VALUES (07, 02, 89);
INSERT INTO t_mysql_score VALUES (07, 03, 98);-- ----------------------------
-- Table structure for t_mysql_student
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_student;
CREATE TABLE t_mysql_student (sid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生编号,sname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生名称,sage varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生年龄,ssex varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生性别,PRIMARY KEY (sid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 学生信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_student
-- ----------------------------
INSERT INTO t_mysql_student VALUES (01, 赵雷, 1990-01-01, 男);
INSERT INTO t_mysql_student VALUES (02, 钱电, 1990-12-21, 男);
INSERT INTO t_mysql_student VALUES (03, 孙风, 1990-12-20, 男);
INSERT INTO t_mysql_student VALUES (04, 李云, 1990-12-06, 男);
INSERT INTO t_mysql_student VALUES (05, 周梅, 1991-12-01, 女);
INSERT INTO t_mysql_student VALUES (06, 吴兰, 1992-01-01, 女);
INSERT INTO t_mysql_student VALUES (07, 郑竹, 1989-01-01, 女);
INSERT INTO t_mysql_student VALUES (09, 张三, 2017-12-20, 女);
INSERT INTO t_mysql_student VALUES (10, 李四, 2017-12-25, 女);
INSERT INTO t_mysql_student VALUES (11, 李四, 2012-06-06, 女);
INSERT INTO t_mysql_student VALUES (12, 赵六, 2013-06-13, 女);
INSERT INTO t_mysql_student VALUES (13, 孙七, 2014-06-01, 女);-- ----------------------------
-- Table structure for t_mysql_teacher
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_teacher;
CREATE TABLE t_mysql_teacher (tid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师编号,tname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师名称,PRIMARY KEY (tid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 教师信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_teacher
-- ----------------------------
INSERT INTO t_mysql_teacher VALUES (01, 张三);
INSERT INTO t_mysql_teacher VALUES (02, 李四);
INSERT INTO t_mysql_teacher VALUES (03, 王五);SET FOREIGN_KEY_CHECKS 1;2.2 使用外连接、内连接和子查询进行解答 01查询 01 课程比 02 课程成绩高的学生的信息及课程分数 涉及表t_mysql_student t_mysql_score连接方式内连接 行转列流程控制函数 SELECTs.*,( CASE WHEN t1.cid 01 THEN t1.score END ) 语文,( CASE WHEN t2.cid 02 THEN t2.score END ) 数学
FROMt_mysql_student s,( SELECT * FROM t_mysql_score WHERE cid 01 ) t1,( SELECT * FROM t_mysql_score WHERE cid 02 ) t2
WHEREs.sid t1.sid AND t1.sid t2.sid AND t1.score t2.score 02查询同时存在 01 课程和 02 课程的情况 涉及表t_mysql_student t_mysql_score连接方式内连接 行转列流程控制函数 SELECTs.*,( CASE WHEN t1.cid 01 THEN t1.score END ) 语文,( CASE WHEN t2.cid 02 THEN t2.score END ) 数学
FROMt_mysql_student s,( SELECT * FROM t_mysql_score WHERE cid 01 ) t1,( SELECT * FROM t_mysql_score WHERE cid 02 ) t2
WHEREs.sid t1.sid AND t1.sid t2.sid 03查询存在 01 课程但可能不存在 02 课程的情况(不存在时显示为 null ) 涉及表t_mysql_score连接方式外连接 以01课表为主 SELECT * FROM( SELECT * FROM t_mysql_score WHERE cid 01 ) t1
LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid 02 ) t2
ON t1.sid t2.sid 推荐上面的这种查询方式看着不太友好所以还有另外的一种加上学生的信息 涉及表t_mysql_student t_mysql_score连接方式内连接外连接行转列流程控制函数 可以更加直接的看到那个学生哪门课程没有学习一目了然 SELECTs.*,( CASE WHEN t1.cid 01 THEN t1.score END ) 语文,( CASE WHEN t2.cid 02 THEN t2.score END ) 数学
FROMt_mysql_student sINNER JOIN ( SELECT * FROM t_mysql_score WHERE cid 01 ) t1 ON s.sid t1.sidLEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid 02 ) t2 ON t1.sid t2.sid 04查询不存在 01 课程但存在 02 课程的情况 涉及表t_mysql_student t_mysql_score查询方式子查询行转列流程控制函数 SELECTs.*,( CASE WHEN sc.cid 01 THEN sc.score END ) 语文,( CASE WHEN sc.cid 02 THEN sc.score END ) 数学
FROMt_mysql_student s,t_mysql_score sc
WHEREs.sid sc.sid AND s.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid 01 ) AND sc.cid 0205查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 涉及表t_mysql_student t_mysql_score查询方式外连接聚合函数四舍五入 round() 平均值 avg() 有聚合函数意味着分组 GROUP BY SELECTs.sid,s.sname,round(avg( sc.score ),2) 平均分
FROMt_mysql_student s LEFT JOIN t_mysql_score sc ON s.sid sc.sid
GROUP BYs.sid,s.sname
HAVING平均分 60 06查询在t_mysql_score表存在成绩的学生信息 涉及表t_mysql_student t_mysql_score查询方式内连接最后建议进行一次分组不然数据过多 SELECTs.sid,s.sname
FROMt_mysql_student s INNER JOIN t_mysql_score sc ON s.sid sc.sid
GROUP BYs.sid,s.sname 07查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) 涉及表t_mysql_student t_mysql_score查询方式外连接聚合函数计数 count() 求和 sum() 有聚合函数意味着分组 GROUP BY SELECTs.sid,s.sname,count(sc.score) 选课总数,sum(sc.score) 总成绩
FROMt_mysql_student s LEFT JOIN t_mysql_score sc ON s.sid sc.sid
GROUP BYs.sid,sc.sid 08查询「李」姓老师的数量 SELECTcount(*)
FROMt_mysql_teacher
WHEREtname LIKE 李% 三. 思维导图
