汽车装饰网站源码,网页设计图片与文字对齐左对齐,弹幕网站开发代码,音乐网站建设论文的目的和意义一、视图
1.1 含义 虚拟表#xff0c;和普通表一样使用 1.2 操作
创建视图 create view 视图名 as 修改视图 方式一#xff1a; create or replace view 视图名 as 【查看视图相关字段】 方式二#xff1a; alter view 视图名 as 【查看的SQL语句】 查看视图 方式一和普通表一样使用 1.2 操作
创建视图 create view 视图名 as 修改视图 方式一 create or replace view 视图名 as 【查看视图相关字段】 方式二 alter view 视图名 as 【查看的SQL语句】 查看视图 方式一 desc 视图名【看视图字段】 方式二 show create view 视图名 删除视图 drop view 视图名 1.3 SQL数据 /* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80018 Source Host : localhost:3306 Source Schema : mybatis_ssm Target Server Type : MySQL Target Server Version : 80018 File Encoding : 65001 Date: 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; 二、连接查询案例 1查询 01 课程比 02 课程成绩高的学生的信息及课程分数 方式一 SELECT * FROM t_mysql_student s, ( SELECT * FROM t_mysql_score WHERE cid 01 ) t1, ( SELECT * FROM t_mysql_score WHERE cid 02 ) t2 WHERE s.sid t1.sid AND t1.sid t2.sid AND t1.score t2.score 方式二 SELECT s.*, (case when t1.cid 01 then t1.score end ) 语文, (case when t2.cid 02 then t2.score end ) 数学 FROM t_mysql_student s, ( SELECT * FROM t_mysql_score WHERE cid 01 ) t1, ( SELECT * FROM t_mysql_score WHERE cid 02 ) t2 WHERE s.sid t1.sid AND t1.sid t2.sid AND t1.score t2.score 2查询同时存在 01 课程和 02 课程的情况 SELECT s.*, (case when t1.cid 01 then t1.score end ) 语文, (case when t2.cid 02 then t2.score end ) 数学 FROM t_mysql_student s, ( SELECT * FROM t_mysql_score WHERE cid 01 ) t1, ( SELECT * FROM t_mysql_score WHERE cid 02 ) t2 WHERE s.sid t1.sid AND t1.sid t2.sid 3查询存在 01 课程但可能不存在 02 课程的情况(不存在时显示为 null ) SELECT s.*, (case when t1.cid 01 then t1.score end ) 语文, (case when t2.cid 02 then t2.score end ) 数学 FROM t_mysql_student s INNER JOIN ( SELECT * FROM t_mysql_score WHERE cid 01 ) t1 on s.sid t1.sid left JOIN ( SELECT * FROM t_mysql_score WHERE cid 02 ) t2 on t1.sid t2.sid 4查询不存在 01 课程但存在 02 课程的情况 SELECT s.*, (case when sc.cid 01 then sc.score end ) 语文, (case when sc.cid 02 then sc.score end ) 数学 FROM t_mysql_student s, t_mysql_score sc WHERE s.sid sc.sid and s.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid 01 ) AND sc.cid02 5查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 SELECT s.sid, s.sname, ROUND(AVG(sc.score),2) n FROM t_mysql_student s LEFT JOIN t_mysql_score sc on s.sid sc.sid GROUP BY s.sid,s.sname HAVING n 60 6查询在t_mysql_score表存在成绩的学生信息 SELECT s.sid, s.sname FROM t_mysql_student s INNER JOIN t_mysql_score sc on s.sid sc.sid GROUP BY s.sid,s.sname 7查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) SELECT s.sid, s.sname, COUNT(sc.score) 选课总数, SUM(sc.score) 总成绩 FROM t_mysql_student s LEFT JOIN t_mysql_score sc on s.sid sc.sid GROUP BY s.sid,s.sname 三、思维导图
