一.视图
1.介绍什么是视图
虚拟表，和普通表一样使用
2.视图的语法
语法讲解
创建视图
create view 视图名 as 查询语句 ;
视图修改
#方式一 create or replace view 视图名 as 查询语句; #方式二 alert view 视图名 as 查询语句;
删除视图
drop view 视图名
查看视图
#查看视图相关字段 desc 视图名; #查看视图相关语句 show create view 视图名;
实例操作
创建视图
CREATE VIEW v_su as SELECT s.*,sc.cid,sc.score FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid;
修改视图
ALTER VIEW v_su as SELECT s.*,sc.cid,sc.score FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid
CREATE or REPLACE VIEW v_zx as SELECT s.*,sc.cid,sc.score FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid;
查看视图
desc v_sU
SHOW CREATE view v_su
二.MySQL面试题
1.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;
2.面试题实战
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
#考察内容：联表查询 内连接 所需表:t_mysql_student t_mysql_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
03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
#外连接
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
04）查询不存在" 01 "课程但存在" 02 "课程的情况
#子查询
SELECT * 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.cid = '02'
05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#聚合函数=》 分组 外连接
SELECT s.sid, s.sname, ROUND( AVG( sc.score ) ) 分数 FROM t_mysql_student s LEFT JOIN t_mysql_score sc ON s.sid = sc.sid GROUP BY s.sid, s.sname HAVING 分数 >= 60
06）查询在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
07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
#考核点 聚合函数 外连接
SELECT s.sid, s.sname, COUNT( sc.cid ), SUM( sc.score ) FROM t_mysql_score sc, t_mysql_student s WHERE sc.sid = s.sid GROUP BY s.sid, s.sname
08）查询「李」姓老师的数量
select count(*) from t_mysql_teacher where tname like '李%'
三.思维导图
