上海做网站建设的公司,软件开发培训中心,滨江做网站,网站开发的工具目录
一. 视图
1.1 含义
1.2 操作
创建视图
修改视图
删除视图
查看视图
二. 索引
2.1 什么是索引
2.2 为什么要使用索引
2.3 优点
2.4 缺点
2.5 何时不适用索引
2.6 索引何时失效
三. 执行计划
3.1 什么是执行计划
3.2 执行计划的作用
四. 面试题
表结构
…目录
一. 视图
1.1 含义
1.2 操作
创建视图
修改视图
删除视图
查看视图
二. 索引
2.1 什么是索引
2.2 为什么要使用索引
2.3 优点
2.4 缺点
2.5 何时不适用索引
2.6 索引何时失效
三. 执行计划
3.1 什么是执行计划
3.2 执行计划的作用
四. 面试题
表结构
表数据
题目 一. 视图
1.1 含义
虚拟表和普通表一样使用
1.2 操作 创建视图 create view 视图名 as 查询语句; 修改视图 1、create or replace view 视图名 as 查询语句; 2、alert view 视图名 as 查询语句; 删除视图 drop view 视图名,视图名,...; 查看视图 1、desc 视图名; ➡查看视图相关字段 2、show create view 视图名; ➡查看视图相关语句 二. 索引
2.1 什么是索引
索引是一种特殊的数据库结构由数据表中的一列或多列组成可以用来快速查询数据表中有某一特定值的记录。
2.2 为什么要使用索引
使用索引可以很大程度上提高数据库的查询速度还有效提升了数据库系统的性能。
2.3 优点 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。可以给所有的MySQL列类型设置索引。可以大大加快数据的查询速度这是使用索引最主要的原因。在实现数据的参考完整性方面可以加快表与表之间的连接。在使用分组和排序子句进行数据查询时可以减少查询中分组的排序的时间 2.4 缺点 创建和维护索引组需要耗费时间并且随着数据量的增加所耗费的时间也会增加。索引需要占磁盘空间除了数据表占数据空间之外每一个索引还要占一定的物理空间。如果有大量的索引索引文件可能比数据文件更快达到最大文件尺寸。当对表中的数据进行增删改时索引也要动态维护这就降低了数据的维护速度。 2.5 何时不适用索引 表记录太少。经常增删改的表数据重复且分布均匀的表字段只应该为经常查询和最经常排序的数据列建立索引如果某个数据类包含太多的重复数据建立索引没有太大意义频繁更新的字段不适合创建索引会增加IO负担where条件里用不到的字段不创建索引 2.6 索引何时失效 like以通配符%开头索引失效当全表查询比走索引查询快时会使用全表扫描而不走索引字符串不加单引号索引会失效where中索引列使用了函数例如substring字符串截取函数where中索引列有运算用了or右边的索引会失效用or索引不会失效is null可以走索引is not null无法使用索引取决于某一列的具体情况复合索引没有用到左列字段最左前缀法则如果没用到最左列索引或中间跳过了某列有索引的列索引会部分失效条件中有or前面的列有索引后面的列没有索引会失效。想让索引生效只能将or条件中的每个列都加上索引 三. 执行计划
3.1 什么是执行计划
使用explain关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。
3.2 执行计划的作用 表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查 四. 面试题
表结构 -- 1.学生表-t_mysql_student -- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别 -- 2.教师表-t_mysql_teacher -- tid 教师编号,tname 教师名称 -- 3.课程表-t_mysql_course -- cid 课程编号,cname 课程名称,tid 教师名称 -- 4.成绩表-t_mysql_score -- sid 学生编号,cid 课程编号,score 成绩 表数据 -- 学生表 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 , 女); -- 教师表 insert into t_mysql_teacher values(01 , 张三); insert into t_mysql_teacher values(02 , 李四); insert into t_mysql_teacher values(03 , 王五); -- 课程表 insert into t_mysql_course values(01 , 语文 , 02); insert into t_mysql_course values(02 , 数学 , 01); insert into t_mysql_course values(03 , 英语 , 03); -- 成绩表 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); 题目
考核点多表联查、内外连接、子查询
01查询 01 课程比 02 课程成绩高的学生的信息及课程分数
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 sc WHERE sc.cid 01 ) t1,( SELECT * FROM t_mysql_score sc WHERE sc.cid 02 ) t2
WHEREs.sid t1.sid AND s.sid t2.sid AND t1.score t2.score 02查询同时存在 01 课程和 02 课程的情况
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 sc WHERE sc.cid 01 ) t1,( SELECT * FROM t_mysql_score sc WHERE sc.cid 02 ) t2
WHEREs.sid t1.sid AND s.sid t2.sid 03查询存在 01 课程但可能不存在 02 课程的情况(不存在时显示为 null )
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 sc WHERE sc.cid 01 ) t1 ON s.sid t1.sidLEFT JOIN ( SELECT * FROM t_mysql_score sc WHERE sc.cid 02 ) t2 ON s.sid t2.sid 04查询不存在 01 课程但存在 02 课程的情况
SELECTs.* ,( CASE WHEN sc.cid 01 THEN sc.score END ) 语文,( CASE WHEN sc.cid 02 THEN sc.score END ) 数学
FROMt_mysql_score sc ,t_mysql_student s
WHERE
s.sidsc.sid andsc.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid 01 ) AND sc.cid 02 05查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECTs.sid,s.sname,round( avg( sc.score ) ) 平均成绩
FROMt_mysql_student s,t_mysql_score sc
WHEREs.sid sc.sid
GROUP BYs.sid,s.sname
HAVING平均成绩 60 06查询在t_mysql_score表存在成绩的学生信息
SELECTs.sid,s.sname
FROMt_mysql_student s,t_mysql_score sc
WHEREs.sid sc.sid
GROUP BYs.sid,s.sname 07查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECTs.sid,s.sname,count( sc.score ) 选课总数,sum( sc.score ) 总成绩
FROMt_mysql_student s,t_mysql_score sc
WHEREs.sid sc.sid
GROUP BYs.sid,s.sname 08查询「李」姓老师的数量 09查询学过「张三」老师授课的同学的信息 10查询没有学全所有课程的同学的信息 11查询没学过张三老师讲授的任一门课程的学生姓名 12查询两门及其以上不及格课程的同学的学号姓名及其平均成绩 13检索 01 课程分数小于 60按分数降序排列的学生信息 14按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 15查询各科成绩最高分、最低分和平均分 以如下形式显示课程 ID课程 name最高分最低分平均分及格率中等率优良率优秀率及格为60中等为70-80优良为80-90优秀为90 要求输出课程号和选修人数查询结果按人数降序排列若人数相同按课程号升序排列
