mysql的视图和连表查询案例

灰太狼 2024-02-05 22:30 70阅读 0赞

*  视图
    
     *  创建视图
        
         *  create view 视图名称
     *  视图修改
        
         *  create or replace view 视图名称 as 查询语句；
         *  alter view 视图名称 as 查询语句
     *  删除视图
        
         *  drop view 视图名，视图名。。。。
     *  查看视图
        
         *  DESC视图名；——查看视图相关字段
         *  SHOW CREATE VIEW 视图名； 查看视图
 *  联表查询案例
    
     *  查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
        
         *  select \* from t\_mysql\_students 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;
            
             *  考点：多表查询
     *  ）查询同时存在" 01 "课程和" 02 "课程的情况
        
         *  select \* from t\_mysql\_studets 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 t\_mysql\_studens s inner jion (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 t.sid=t2.sid;
     *  查询不存在" 01 "课程但存在" 02 "课程的情况
        
         *  select \* from t\_mysql\_students s,t\_mysql\_score sc where s.sid=sc.sid and sid not in (select \* from t\_mysql\_score where cid='01') and sc.cid='02'
     *  查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
        
         *  select s.sid ,s.sname,avg(sc.score) n from t\_mysql\_studetns s left join t\_mysql\_score sc on s.sid=sc.sid group by s.sid,s.sname having n>60;
     *  查询在t\_mysql\_score表存在成绩的学生信息
        
         *  select \* from t\_mysql\_students s inner join t\_mysql\_score sc on s.sid=sc.sid
     *  查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
        
         *  select s.sid ,s.sname,count(sc.score) ,sum(sc.score) from t\_mysql\_studets s left join t\_mysql\_score sc on s.sid=sc.sid gruop by s.sid ,s.sname;
     *  \-- 08）查询「李」姓老师的数量
        
         *  select count(tname) from t\_mysql\_teacher where tname like '李%'