您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:SQL复杂查询和视图

新葡亰496net:SQL复杂查询和视图

发布时间:2019-09-08 03:40编辑:网络数据库浏览(140)

    分组查询

     笔记:

    子查询

    子查询


    一、聚合函数:计数 最大值  最小值  平平均数量  求和



    SQL能够将寻觅到的元组按某一规格进行分组,分组是属性值一样的为一组

    1.计数 COUNT()  忽略NULL值

    具体中,非常多情状下需求张开下述条件判别

    现实中,非常多场馆下要求打开下述条件推断

    • 求每个学员的平分战表

    方式1:COUNT(*)  *代表全部字段 一般用来查询表中国共产党有多少条记下(实体)

    • 某一成分是否是某一集合成员
    • 某百分之十团是不是含有另10%团
    • 测量试验集结是不是为空
    • 测验集结是或不是存在另一元组
    • 某一因素是还是不是是某一集结成员
    • 某10%团是还是不是包涵另十分之一团
    • 测量检验集结是不是为空
    • 测量检验集结是不是留存另一元组

    SELECT sn,AVG(score)
    FROM sc
    GROUP BY sn

    SELECT COUNT(*) zongshu FROM emp;

    子查询是出以后WHERE子句中的SELECT语句被称为子查询,子查询重临了二个聚众。

    子查询是出新在WHERE子句中的SELECT语句被称为子查询,子查询重返了两个相会。

    新葡亰496net 1

    办法2:计算有些字段中国和欧洲NULL值的个数

    IN子查询

    IN子查询

    先按sn举办分组,即sn一样的为一组,然后求该组中score的平均值。最后输出是按组输出,叁个组为一条记下

    SELECT COUNT(ALL comm) FROM emp;-- all 能够简轻便单



    分组过滤

    办法3:去重计数  总括某列中不重复非NULL值的个数

    宗旨语法:查询语句 [NOT] IN 子查询

    着力语法:查询语句 [NOT] IN 子查询


    SELECT COUNT( DISTINCT job) FROM emp;

    语义:查询语句发生的结果是不是在子查询当中

    语义:查询语句发生的结果是还是不是在子查询个中

    当大家对结果集举办分组后,要是急需对分组进行筛选,只留下大家供给的分组,那即将用到分组过滤。

    2.最大值  MAX() 求某一列中的最大值,不分组的景况下不得与任何字段一齐使用

    • 列出选修了001号课程的学员学号和人名
    • 列出选修了001号课程的学生学号和姓名

    分组过滤的主要词是HAVING

    SELECT MAX(sal) FROM emp ;

    SELECT sn, sname
    FROM student
    新葡亰496net:SQL复杂查询和视图。WHERE sn IN (SELECT sn FROM sc WHERE cn="001");

    SELECT sn, sname
    FROM student
    WHERE sn IN (SELECT sn FROM sc WHERE cn="001");

    • 求比不上格成绩领先两门课的同室学号

    3.细小值  MIN()  不分组的情况下不得与任何字段一同行使

    括号中产生的结果是三个集合(这里称为子集结),集合中的元素是学过001课程的学习者的学号。前半有的的查询语句是从student中每取一条记下来查看记录中的sn是还是不是在子集结中。如若是则将该记录举办标志,不然收取下一条继续相比。最后将被标识的笔录中sn和snames属性值输出。

    括号中生出的结果是二个汇集(这里称为子集结),群集中的成分是学过001课程的学员的学号。前半某个的查询语句是从student中每取一条记下来查看记录中的sn是还是不是在子集合中。假若是则将该记录进行标志,不然抽取下一条继续相比。最终将被标识的记录中sn和snames属性值输出。

    SELECT sn
    FROM sc
    WHERE score < 60
    GROUP BY sn HAVING COUNT(*)>2

    SELECT  MIN(sal) FROM emp ;

    新葡亰496net 2

    新葡亰496net 3

    将成绩小于60分的笔录按学号实行分组,然后总结每种分组中著录的条数。若是条数大于2则将学号输出。

    4.平均值  AVG()  不分组的气象下不得与别的字段一齐利用

    • 查询既学过001课程又学过002课程的同窗学号
    • 询问既学过001课程又学过002课程的同桌学号
    • 求有两门以上课程比不上格的学习者学号和他的平均成绩

    SELECT AVG(sal) FROM emp;

    SELECT sn
    FROM sc
    WHERE cn="001" AND sn IN (SELECT sn FROM sc WHERE cn="002");

    SELECT sn
    FROM sc
    WHERE cn="001" AND sn IN (SELECT sn FROM sc WHERE cn="002");

    SELECT sn,AVG(score)
    FROM sc
    WHERE sn IN(
        SELECT sn
        FROM sc
        WHERE score<60
        GROUP BY sn HAVING COUNT(*)>2
        )
    新葡亰496net:SQL复杂查询和视图。GROUP BY sn;

    5.求和  SUM() 不分组的动静下不得与别的字段一同行使

    新葡亰496net 4

    新葡亰496net 5

    SQL语言中的并交代


    在事关代数中有汇集的并,交,差运算,SQL语言也能兑现相应的运算。

    SQL语言;并运算(UNION),交运算(INTERSECTS),差运算(EXCEPT)

    着力语法: 子查询 {UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL]} 子查询

    注意:MySQL中只有UNION操作

    当未有ALL时结果聚焦会去除重复元组,而有ALL则会保留

    新葡亰496net 6

    • 上学过002号课的同校或许学过003号同学的学号

    SELECT sn FROM sc WHERE cn="003"
    UNION
    SELECT sn FROM sc WHERE cn="002"

    新葡亰496net 7

    • 求即学过002又学过003课程的同窗学号

    SELECT sn FROM sc WHERE cn="002"
    INTERSECTS
    SELECT sn FROM sc WHERE cn="003"

    询问小结


    SELECT语句完整定义(::==代表定义)

    新葡亰496net 8

    在面向对象/对象关周全据库中SQL将中间转播为OQL,这一个能够当作精晓。

    SELECT SUM(sal) FROM emp;

    非相关子查询

    非相关子查询

    练习:



    1.询问员工的总人数;

    询问分为外层查询和内层查询

    查询分为外层查询和内层查询

    SELECT COUNT(*) FROM emp;

    新葡亰496net 9

    新葡亰496net 10

    2.查询员工的平均薪资;

    外层查询的参数可以被带走到内层查询中,而内层查询的参数不能够在外层查询中采用,那和高级编程中的循环贰个道理。

    外层查询的参数能够被带入到内层查询中,而内层查询的参数不能够在外层查询中利用,那和高端编制程序中的循环三个道理。

    SELECT AVG(sal) FROM emp;

    当内层查询未有使用到外查询的参数时,我们得以内层查询是非相关子查询。上海体育场合中正是非相关子查询。推断是还是不是相关最简单易行的格局正是内层查询是或不是能独立实践。

    当内层查询未有应用到外查询的参数时,大家得以内层查询是非相关子查询。上海体育场所中就是非相关子查询。判定是还是不是相关最简易的艺术正是内层查询是或不是能独立试行。

    3.查询文员的总人数;

    相关子查询

    相关子查询

    SELECT COUNT(*) FROM emp WHERE job='文员';



    1. 询问30号部门粤语员的总薪给;

    新葡亰496net 11

    新葡亰496net 12

    SELECT SUM(sal IFNULL(comm,0)) FROM emp WHERE deptno=30 AND job ='文员';

    上海教室的例证中内层子查询利用到了外围的变量(Stud),这样内层查询就无法独立实践

    上海教室的例证中内层子查询利用到了外围的变量(Stud),那样内层查询就不能够独立推行

    5.查询10号部门中职员和工人的最低工资;

    SOME与ALL子查询

    SOME与ALL子查询

    SELECT MIN(sal) FROM emp WHERE deptno=10;



    6.询问入职时间在二零零三-二〇〇七的职工的参天薪给

    大旨语法:查询语句 Θ SOME 子查询

    基本语法:查询语句 Θ SOME 子查询

    SELECT MAX(sal) FROM emp WHERE hiredate BETWEEN '2001' AND '2005';

                  查询语句 Θ ALL 子查询

                  查询语句 Θ ALL 子查询

    7.查询二〇〇五年在此之前入职的职工的平均报酬

    Θ 是运算符:<,>,<=,>=,<>

    Θ 是运算符:<,>,<=,>=,<>

    SELECT AVG(sal) FROM emp WHERE hiredate <'2005';

    Θ SOME表示前边的记录须求与子查询结果中某些记录做运算,假设为TRUE则该记录被接受

    Θ SOME表示前边的记录必要与子查询结果中某些记录做运算,假若为TRUE则该记录被接受

    8.查询贰零零叁年过后入职的员工的参天薪酬和最低薪金和平均薪金。

    Θ ALL表示后面包车型客车笔录须要与子查询结果中具备记录做运算,要是全为TRUE则该记录才被接受

    Θ ALL表示前边的记录须要与子查询结果中有着记录做运算,倘使全为TRUE则该记录才被接受

    SELECT MAX(sal) ,MIN(sal),AVG(sal) FROM emp  WHERE hiredate >'2002';

    • 找寻薪水最低的教育工小编姓名
    • 搜索薪俸低于的老师姓名

    9.询问30号部门的万丈薪金、最低报酬、平均工资;

    SELECT tname
    FROM teacher
    WHERE salary <= ALL(
        SELECT salary
        FROM teacher)

    SELECT tname
    FROM teacher
    WHERE salary <= ALL(
        SELECT salary
        FROM teacher)

    SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE deptno =30;

    新葡亰496net 13

    新葡亰496net 14

    10.查询10号或20号部门的文员的参天工资、最低薪给、平均薪水;

    • 找寻001号课战表不是参天的兼具学员的学号
    • 找寻001号课成绩不是参天的全体学生的学号

    SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE (deptno =10 OR deptno =20 ) AND job ='文员';

    SELECT sn
    FROM sc
    WHERE cn="001" AND score < SOME(
        SELECT score
        FROM sc
        WHERE cn = "001")

    SELECT sn
    FROM sc
    WHERE cn="001" AND score < SOME(
        SELECT score
        FROM sc
        WHERE cn = "001")

    二、分组  GROUP BY

    新葡亰496net 15

    新葡亰496net 16

    分组总结时SELECT 语句中得以有分组字段和聚合函数,但不用放别的字段;

    • 找寻具备成绩都不如格的学员姓名(相关询问)
    • 搜索全部战表都不比格的学员姓名(相关询问)

    SELECT deptno, AVG(sal),MAX(sal) ,MIN(sal) ,SUM(sal)FROM  emp WHERE sal >20000 GROUP BY deptno;

    SELECT sname
    FROM student
    WHERE 60 > ALL(
        SELECT score
        FROM sc
        WHERE sc.sn = student.sn)

    SELECT sname
    FROM student
    WHERE 60 > ALL(
        SELECT score
        FROM sc
        WHERE sc.sn = student.sn)

    HAVING 客户过滤聚合函数的值 只可以用在分组前面

    新葡亰496net 17

    新葡亰496net 18

    SELECT deptno ,MIN(sal),MAX(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>8000 AND MAX()<50000;

    • 找寻张三同学成绩最低的教程号
    • 寻觅张三同学成绩最低的科目号

    SELECT deptno ,MIN(sal) minSal ,MAX(sal) b FROM emp GROUP BY deptno HAVING minSal>8000 AND b<50000;

    SELECT cn
    FROM sc,student
    WHERE sname="张三" AND sc.sn=student.sn AND score <=ALL(
        SELECT score
        FROM sc
        WHERE sn=student.sn)

    SELECT cn
    FROM sc,student
    WHERE sname="张三" AND sc.sn=student.sn AND score <=ALL(
        SELECT score
        FROM sc
        WHERE sn=student.sn)

    采纳多少个字段分组:

    新葡亰496net 19

    新葡亰496net 20

    计量各样单位中各样地方的平均薪酬:

    EXISTS查询

    EXISTS查询

    SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno ,job ;



    分组演练:

    主干语法: [NOT] EXISTS (子查询)

    大旨语法: [NOT] EXISTS (子查询)

    -- 1.查询各样部门的参天工资、最低薪给、平均薪资。

    意义:剖断子查询结果集是不是为空,当子查询为空时,EXISTS判定为false,而NOT EXISTS剖断为true。NOT EXISTS使用情况比很多

    意思:剖断子查询结果集是或不是为空,当子查询为空时,EXISTS剖断为false,而NOT EXISTS决断为true。NOT EXISTS使用情状比很多

    SELECT deptno,MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY deptno;

    • 寻找学过001号教授授课的保有科指标同窗姓名
    • 搜寻学过001号教授讲课的富有课程的同桌姓名

    -- 2.询问各样地方的平均报酬 降序排列

    等价调换为不设有这么一门课程,该学科由001讲师讲课并且该学科该同学没学过

    等价调换为官样文章那样一门科目,该课程由001教师授课並且该学科该同学没学过

    SELECT job,AVG(sal) avgSal FROM emp GROUP BY job ORDER BY avgSal DESC ;

    SELECT sname
    FROM student
    WHERE NOT EXISTS(
        SELECT *
        FROM course
        WHERE tn="001" AND NOT EXISTS(
            SELECT *
            FROM sc
            WHERE sn=student.sn AND cn=course.cn)
        )

    SELECT sname
    FROM student
    WHERE NOT EXISTS(
        SELECT *
        FROM course
        WHERE tn="001" AND NOT EXISTS(
            SELECT *
            FROM sc
            WHERE sn=student.sn AND cn=course.cn)
        )

    -- 3.查询平均薪金大于一千0的地方

    • 列出从未学过李明老师课的学习者姓名
    • 列出从未学过李明老师课的上学的小孩子姓名

    SELECT job ,AVG(sal) avgSal FROM emp GROUP BY job HAVING avgSal >10000 ;

    等价于不设有这么的学员,他学过李明先生的课

    等价于不真实那样的学员,他学过李明先生的课

    -- 4.询问各个机关的人头

    SELECT sname
    FROM student
    WHERE NOT EXISTS(
    新葡亰496net,    SELECT *
        FROM course,sc,teacher
        WHERE course.cn=sc.cn AND teacher.tn=course.tn AND tname="李明" AND student.sn=sc.sn)

    SELECT sname
    FROM student
    WHERE NOT EXISTS(
        SELECT *
        FROM course,sc,teacher
        WHERE course.cn=sc.cn AND teacher.tn=course.tn AND tname="李明" AND student.sn=sc.sn)

    SELECT deptno ,COUNT(ename) FROM emp GROUP BY deptno;

    • 列出足足学过98030101号同学学过全体课程的校友学号
    • 列出足足学过98030101号同学学过全部科指标同室学号

    -- 5.查询人数大于5的机构

    等价于官样文章98030101同学学过的学科,该同学未有学过

    等价于空头支票98030101同校学过的学科,该同学未有学过

    SELECT deptno ,COUNT(ename) qty FROM emp GROUP BY deptno HAVING qty>5;

    SELECT sn
    FROM sc AS sc1
    WHERE sn !="98030101" AND NOT EXISTS(
         SELECT *
         FROM sc AS sc2
         WHERE sn="98030101" NOT EXISTS(
            SELECT *
            FROM sc AS sc3
            WHERE sc2.cn=cn AND sn=sc1.sn)
        )

    SELECT sn
    FROM sc AS sc1
    WHERE sn !="98030101" AND NOT EXISTS(
         SELECT *
         FROM sc AS sc2
         WHERE sn="98030101" NOT EXISTS(
            SELECT *
            FROM sc AS sc3
            WHERE sc2.cn=cn AND sn=sc1.sn)
        )

    -- 6.询问部门人数小于3的机关的平均薪酬、最高级技术员资、最低工资

    新葡亰496net 21

    新葡亰496net 22

    SELECT deptno,AVG(sal),MAX(sal),MIN(sal) FROM emp  GROUP BY deptno HAVING COUNT(*)<=3;

    结果总计与聚类总括

    结果总结与聚类计算

    -- 7.询问各种部门西藏中华南理哲大学程公司资抢先10000的总人口 降序展现



    SELECT deptno ,COUNT(empno) qty FROM emp WHERE sal>10000 GROUP BY deptno ORDER BY qty DESC ;

    结果总括和聚类总括是对查询结果聚集的部分数码进行总结

    结果总计和聚类总结是对查询结果聚焦的片段数量举办测算

    -- 8.询问各种岗位青海中华南理教院程公司资超越10000的人头 升序呈现

    • 求放肆两名教师职员和工人的薪资差额(差额>0)
    • 求大肆两名导师的薪给差额(差额>0)

    -- 9.询问平均工资大于一千0的前八个机构。

    SELECT t1.tname AS teacher1, t2.tname AS teacher2,t1.salary-t2.salary
    FROM teacher AS t1, teacher AS t2
    WHERE t1.salary >t2.salary

    SELECT t1.tname AS teacher1, t2.tname AS teacher2,t1.salary-t2.salary
    FROM teacher AS t1, teacher AS t2
    WHERE t1.salary >t2.salary

    SELECT AVG(sal) ,deptno FROM emp  GROUP BY deptno HAVING AVG(sal)>10000 LIMIT 2;

    新葡亰496net 23

    新葡亰496net 24

    -- 10.查询每种机关中并未有津贴的总人口。

    聚类计算

    聚类总结

    SELECT deptno,COUNT(*) FROM emp WHERE comm IS NULL GROUP BY deptno

    新葡亰496net 25

    新葡亰496net 26

    三、组合查询 - 子查询

    • 求教师薪水总和
    • 求助教报酬总和

    1.单行单列  跟在WHERE子句后,用于决断的尺度

    SELECT SUM(salary)
    FROM teacher

    SELECT SUM(salary)
    FROM teacher

    -- 比天可汗薪酬高的持有职员和工人的新闻

    新葡亰496net 27

    新葡亰496net 28

    SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='李世民')

    • 求数据库课程的平分成绩
    • 求数据库课程的平分战绩

    2.单行多列  跟在WHERE子句后

    SELECT AVG(score)
    FROM sc,course
    WHERE sc.cn=course.cn AND course.cname="数据库"

    SELECT AVG(score)
    FROM sc,course
    WHERE sc.cn=course.cn AND course.cname="数据库"

    -- 查询和唐太宗薪酬及任务都同样的人的具有音信

    新葡亰496net 29

    新葡亰496net 30

    SELECT * FROM emp WHERE (job,sal) IN (SELECT job ,sal FROM emp WHERE ename='李世民')

    AND ename !='李世民';

    3.多行单列

    -- 查询大于30号部门中恣心所欲一个职员和工人报酬的人的兼具消息

    -- any:任性的情趣

    SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);

    SELECT * FROM emp WHERE sal >  (SELECT MIN(sal) FROM emp WHERE deptno=30);

    -- 查询大于30号部门中享有人报酬的职工音讯

    SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

    4.多行多列 用在FROM子句前面 作为虚表使用

    SELECT e.ename FROM (SELECT * FROM emp WHERE deptno =30) e WHERE e.comm IS NULL;

    组成查询-- 合併结果集  上下两条查询语句的字段个数要长期以来;

    UNION --去除重复数据的集合;

    SELECT ename,sal FROM emp WHERE deptno=10 UNION SELECT  ename,sal FROM emp WHERE deptno=20;

    UNION ALL  -- 不去除重复记录

    SELECT ename,sal  FROM emp WHERE deptno=10 UNION ALL SELECT  ename,sal FROM emp WHERE deptno=20;

    四、链接

    1. 内连接 去除笛Carl积:去除相称错误的多少;

    1.1 mysql方言格局:

    SELECT * FROM emp ,dept  WHERE emp.deptno= dept.deptno AND dept.dname='学工部' ORDER BY emp.sal DESC ;

    1.2 标准SQL方式:SELECT * FROM emp INNER JOIN dept  ON emp.deptno= dept.deptno;

    1.3本来连接:SELECT * FROM emp NATURAL JOIN dept;

    2.外连接

    2.1 左外连接 LEFT OUTE奥迪Q3 JOIN  -- out可以省略,以左侧表的行数为准,若侧边表未有与之合作的数据,那么用null值填充

    SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno =d.deptno;

    SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.deptno =d.deptno;

    2.2 右外接连  哈弗IGHT OUTECRUISER JOIN  与左外连接意思相反

    SELECT * FROM emp e RIGHT  JOIN dept d ON e.deptno =d.deptno;

    二。1:查询雇员表吉林中华南理文大学程公司资最高的雇员的职员和工人号、员工姓名、报酬和部门号。

    2:薪酬超过13000的雇员,根据单位编号进行分组,分组后的平均薪给必需高出15000,查询各分组的平均薪资,依据薪俸的倒序举行排列

    3:查询每种雇员和其所在的单位名

    4.查询每一种雇员姓名及其报酬所在的级差

    5:查询雇员名第四个字不是‘中‘的雇员的全名、所在的机构名、薪金所在的等第。

    6:查询每种雇员和其经纪的全名

    -- 7:查询各类雇员和其经营的姓名(包含公司老总娘本身(他方面未有经营))

    SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

    -- 8:查询种种雇员的全名及其所在单位的单位名(包蕴未有雇员的部门)

    SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;

    -- 9:查询每种机构安徽中华南理哲高校程集团资最高的人的真名、薪给和部门编号

    SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);

    SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;

    -- 10:查询各类单位平均报酬所在的阶段

    SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

    SELECT deptno,AVG(sal) avgSal  FROM emp GROUP BY deptno

    ) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

    -- 11:查询每一个机关内平均的薪饷等第

    SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

    SELECT deptno,AVG(sal IFNULL(comm,0)) avgSal  FROM emp GROUP BY deptno

    ) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

    -- 12:查询雇员中有如何人是老板人

    -- 13:不准用max函数,求雇员表中工资的最高值。

    SELECT * FROM emp ORDER BY sal IFNULL(comm,0)  DESC LIMIT 0,1;

    -- 14:平均工资最高的单位的单位编号

    SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

    -- 15:求平均薪水最高的机构的机构名称

    SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

    -- 16:查询手下有职工的管理者的新闻

    SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

    -- mysql联表查询练习:

    -- 1.查询发售部工资高于三千0的全体人;

    SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='销售部' AND emp.sal >20000;

    -- 2.询问每种机关的人数,须求出示部门名称;

    SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (

    SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1  ON dept.deptno=d1.deptno;

    -- 3.询问每种部门的参天薪金,平均报酬,最低报酬 ,供给出示部门名称;

    SELECT * FROM dept LEFT JOIN (

    SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2  ON dept.deptno = d2.deptno

    -- 4.询问教学商讨部中入职时间最先的职工消息,要突显部门名称,姓名,入职时间;

    SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'  ORDER BY hiredate  LIMIT 1;

    -- 5.查询当前不曾职员和工人的机构新闻;

    SELECT * FROM (

    SELECT dept.deptno,dept.dname ,dept.loc  ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;

    三。课后功课:

    -1.学生表

    Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

    --创造测量检验数据

    create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

    insert into Student values('02' , '钱电' , '1990-12-21' , '男');

    insert into Student values('03' , '孙风' , '1990-05-20' , '男');

    insert into Student values('04' , '李云' , '1990-08-06' , '男');

    insert into Student values('05' , '周梅' , '1991-12-01' , '女');

    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

    insert into Student values('08' , '王菊' , '1990-01-20' , '女');

    --2.课程表

    Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号

    create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

    insert into Course values('01' , '语文' , '02');

    insert into Course values('02' , '数学' , '01');

    insert into Course values('03' , '英语' , '03');

    --3.教师表

    Teacher(Tid,Tname) --Tid 教授编号,Tname 教授姓名

    create table Teacher(Tid varchar(10),Tname varchar(10));

    insert into Teacher values('01' , '张三');

    insert into Teacher values('02' , '李四');

    insert into Teacher values('03' , '王五');

    --4.成绩表

    SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数

    create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));

    insert into SC values('01' , '01' , 80);

    insert into SC values('01' , '02' , 90);

    insert into SC values('01' , '03' , 99);

    insert into SC values('02' , '01' , 70);

    insert into SC values('02' , '02' , 60);

    insert into SC values('02' , '03' , 80);

    insert into SC values('03' , '01' , 80);

    insert into SC values('03' , '02' , 80);

    insert into SC values('03' , '03' , 80);

    insert into SC values('04' , '01' , 50);

    insert into SC values('04' , '02' , 30);

    insert into SC values('04' , '03' , 20);

    insert into SC values('05' , '01' , 76);

    insert into SC values('05' , '02' , 87);

    insert into SC values('06' , '01' , 31);

    insert into SC values('06' , '03' , 34);

    insert into SC values('07' , '02' , 89);

    insert into SC values('07' , '03' , 98);

    课后功课:

    -1.学生表

    Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

    --成立测验数据

    create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

    insert into Student values('02' , '钱电' , '1990-12-21' , '男');

    insert into Student values('03' , '孙风' , '1990-05-20' , '男');

    insert into Student values('04' , '李云' , '1990-08-06' , '男');

    insert into Student values('05' , '周梅' , '1991-12-01' , '女');

    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

    insert into Student values('08' , '王菊' , '1990-01-20' , '女');

    --2.课程表

    Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 助教编号

    create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

    insert into Course values('01' , '语文' , '02');

    insert into Course values('02' , '数学' , '01');

    insert into Course values('03' , '英语' , '03');

    --3.教师表

    Teacher(Tid,Tname) --Tid 教授编号,Tname 教师姓名

    create table Teacher(Tid varchar(10),Tname varchar(10));

    insert into Teacher values('01' , '张三');

    insert into Teacher values('02' , '李四');

    insert into Teacher values('03' , '王五');

    --4.成绩表

    SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数

    create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));

    insert into SC values('01' , '01' , 80);

    insert into SC values('01' , '02' , 90);

    insert into SC values('01' , '03' , 99);

    insert into SC values('02' , '01' , 70);

    insert into SC values('02' , '02' , 60);

    insert into SC values('02' , '03' , 80);

    insert into SC values('03' , '01' , 80);

    insert into SC values('03' , '02' , 80);

    insert into SC values('03' , '03' , 80);

    insert into SC values('04' , '01' , 50);

    insert into SC values('04' , '02' , 30);

    insert into SC values('04' , '03' , 20);

    insert into SC values('05' , '01' , 76);

    insert into SC values('05' , '02' , 87);

    insert into SC values('06' , '01' , 31);

    insert into SC values('06' , '03' , 34);

    insert into SC values('07' , '02' , 89);

    insert into SC values('07' , '03' , 98);

    --3、查询平均战绩超过等于60分的同校的学生编号和学习者姓名和平均战绩

    SELECT st.sid,st.sname,AVG(sc.score) avgScore FROM student st ,sc WHERE st.sid=sc.Sid GROUP BY sid  HAVING avgScore>60;

    --4、查询平均成绩小于60分的校友的学员编号和学员姓名和平均成绩

    --5、查询全体同学的学员编号、学生姓名、选课总量、全数科目标总战绩

    SELECT st.sid,st.sname ,COUNT(sc.cid),SUM(sc.score) FROM student st ,sc WHERE st.sid=sc.sid GROUP BY st.sid;

    --6、查询选修"张三"老师所授课程的上学的小孩子中,战绩最高的学习者音讯及其成绩

    SELECT * FROM student st,sc,course co,teacher  te WHERE

    st.sid=sc.sid AND sc.cid=co.cid AND co.tid=te.tid AND te.tname='张三' ORDER BY sc.score DESC LIMIT 1;

    --7、查询本周过生日的上学的儿童

    SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW()) ;

    --8、查询上周过出生之日的学员

    SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW()) 1 ;

    --9、查询前一个月过寿辰的学生

    SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW());

    --10、查询上个月过生日的学习者

    SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW()) 1;

    --11、查询每门功战绩最棒的前两名

    SELECT  sid,cid,score

    FROM sc r1

    WHERE  (SELECT COUNT(*) FROM sc r2 WHERE r2.cid=r1.cid AND r1.score <= r2.score) <=2 ORDER BY cid ;

    --12、总计每门课程的上学的小孩子选修人数(超越5人的课程才总括)。需求输出课程号和选修人数,查询结果按人头降序排列,若人数同样,按学科号升序排列

    SELECT  cid,COUNT(*) qty FROM sc GROUP BY cid HAVING qty>5 ORDER BY qty DESC ;

    --13、检索至少选修两门学科的学生学号

    SELECT sid ,COUNT(*) FROM sc GROUP BY sid HAVING COUNT(*) >=2;

    --14、查询选修了全体学科的学习者消息

    SELECT sid ,COUNT(*) aa  FROM sc GROUP BY sid HAVING aa=(SELECT COUNT(*) FROM course);

    --15、查询各学生的岁数

    SELECT * ,YEAR(NOW())-YEAR(sage) FROM student

    --1、查询"01"课程比"02"课程成绩高的上学的小孩子的音信及学科分数

    SELECT  a.sid,a.score,b.score FROM (

    SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.Cid=01 ) a,

    (

    SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.cid=02) b  WHERE a.sid=b.sid  AND a.score >b.score;

    --2、查询"01"课程比"02"课程战表低的上学的小孩子的音讯及学科分数

    --3、查询平均成绩超乎等于60分的同学的上学的小孩子编号和学员姓名和平均成绩

    SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN

    (SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs>=60)s2 ON s1.Sid=s2.Sid ;

    --4、查询平均成绩小于60分的同校的学习者编号和学习者姓名和平均成绩

    SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN

    (SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs<60)s2 ON s1.Sid=s2.Sid ;

    --5、查询全数同学的学习者编号、学生姓名、选课总的数量、全部科目的总成绩

    SELECT DISTINCT s1.Sid,s1.Sname,s2.cc,s2.ss FROM student s1 INNER JOIN

    (SELECT sid,COUNT(cid)cc,SUM(score)ss FROM sc GROUP BY sid) s2 ON s1.Sid=s2.Sid;

    --6、查询选修"张三"老师所授课程的学生中,战绩最高的学员音信及其战表

    SELECT DISTINCT * FROM student s1 INNER JOIN (SELECT * FROM sc WHERE cid=01 ORDER BY score DESC LIMIT 2) s2 ON s1.sid=s2.sid;

    --7、查询本周过生日的学生

    --8、查询下一周过出生之日的学习者

    --9、查询前段日子过生日的上学的小孩子

    --10、查询本月过出生之日的学生

    --11、查询每门功战绩最棒的前两名

    (SELECT *FROM sc WHERE cid =01 ORDER BY score DESC LIMIT 2)UNION

    (SELECT *FROM sc WHERE cid =02 ORDER BY score DESC LIMIT 2)UNION

    (SELECT *FROM sc WHERE cid =03 ORDER BY score DESC LIMIT 2);

    --12、总计每门课程的学习者选修人数(抢先5人的教程才总计)。供给输出课程号和选修人数,查询结果按人口降序排列,若人数同样,按学科号升序排列

    SELECT cid,COUNT(sid) cs FROM sc GROUP BY cid HAVING cs>5 ORDER BY cs DESC;

    --13、检索至少选修两门学科的学员学号

    SELECT sid FROM sc GROUP BY sid HAVING COUNT(cid)>=2;

    --14、查询选修了全方位学科的学生消息

    SELECT * FROM Student s1 INNER JOIN (SELECT sid,COUNT(cid) cc FROM sc GROUP BY sid HAVING cc=3) s2 ON s1.sid=s2.sid

    --15、查询各学生的年华

    --1、查询"01"课程比"02"课程成绩高的学习者的音信及教程分数

    --2、查询"01"课程比"02"课程战表低的学习者的新闻及教程分数

    四。1:查询雇员表海南中华南理理高校程集团资最高的雇员的职员和工人号、职员和工人姓名、薪给和机关号。

    2:薪给当先1两千的雇员,遵照部门编号实行分组,分组后的平均薪酬必得高出1五千,查询各分组的平均薪水,遵照薪俸的倒序举行排列

    3:查询每一种雇员和其所在的单位名

    4.查询每一种雇员姓名及其报酬所在的阶段

    5:查询雇员名第4个字不是‘中‘的雇员的全名、所在的机构名、薪资所在的级差。

    6:查询每一种雇员和其经纪的全名

    -- 7:查询每一个雇员和其经营的姓名(满含集团CEO娘本人(他方面未有经营))

        SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

    -- 8:查询各种雇员的全名及其所在机构的单位名(包罗未有雇员的单位)

      SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;

    -- 9:查询每种机构江西中华南理法大学程集团资最高的人的真名、薪给和单位编号

        SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);

        SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;

    -- 10:查询每个机构平均薪水所在的品级

    SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

    SELECT deptno,AVG(sal) avgSal  FROM emp GROUP BY deptno

    ) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

    -- 11:查询各样机关内平均的薪资品级

      SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

    SELECT deptno,AVG(sal IFNULL(comm,0)) avgSal  FROM emp GROUP BY deptno

    ) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

    -- 12:查询雇员中有如何人是主管人

    -- 13:不准用max函数,求雇员表中薪俸的最高值。

      SELECT * FROM emp ORDER BY sal IFNULL(comm,0)  DESC LIMIT 0,1;

    -- 14:平均报酬最高的单位的单位编号

        SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

    -- 15:求平均薪给最高的机构的机构名称

        SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

    -- 16:查询手下有职工的首长的消息

            SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

    -- mysql联表查询练习:

    -- 1.查询贩卖部工资高于30000的全体人;

        SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='销售部' AND emp.sal >20000;

    -- 2.询问每种部门的人口,供给出示部门名称;

    SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (

      SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1  ON dept.deptno=d1.deptno;

    -- 3.询问每种部门的最高级技术员资,平均薪酬,最低报酬 ,须要出示部门名称;

    SELECT * FROM dept LEFT JOIN (

      SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2  ON dept.deptno = d2.deptno

    -- 4.询问教学切磋部中入职时间最初的职工消息,要显得部门名称,姓名,入职时间;

        SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'  ORDER BY hiredate  LIMIT 1;

    -- 5.查询当前从未职员和工人的机关音信;

    SELECT * FROM (

      SELECT dept.deptno,dept.dname ,dept.loc  ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:SQL复杂查询和视图

    关键词: