笔试面试那件小事（数据库SQL查询）

SQL中常用的关键词及其解释如下：

SELECT：将资料从数据库中的表格内选出，两个关键字：从（FROM）数据库中的表格内（SELECT）

SELECT <属性名>  FROM <表名>

 

DISTINCT：在上述SELCT关键词后面加上一个DISTINCT就可以去除选择除了的档位中的重复，从而完成求得这个表格内有哪些不同的值的功能。

SELECT DISTINCT <属性名> FROM <表格名>

 

WHERE：这个关键词可以帮助我们选择性的抓取资料，而不是全部取出来。

SELECT <属性名> FROM <表格名> WHERE <条件>

 

AND OR：复合条件的连接词

SELECT <属性名> FROM <表格名> WHERE (简单条件) {[AND|OR]}(简单条件)

 

IN：故名思议，从什么当中选取

SELECT <属性名> FROM <表格名> WHERE (属性)IN（{查询}）

 

BETWEEN：从某些值的范围之内的资料库取出值

SELECT <属性名> FROM <表格名> WHERE (属性) BETWEEN '值一' AND '值二'；其中值一和值二可以是一个SQL查询语句

 

ORDER BY:我们经常需要能够获取的资料做一个比较系统的显示。例如按从大到小排列（descending）排序，这个时候就需要用到ORDER BY

SELECT <属性名> FROM <表格> [WHERE “条件”] ORDER BY (属性)[ASC,DESC]

 

内置函数：可以对数字形态存在的行或者列做运算，包括AVG(平均)，COUNT(计数)、MAX（最大值）、MIN(最小值)、SUM(总和)

SELECT 函数名（<属性名>） FROM <表格>

 

COUNT：能够统计出有多少个元组被选出来

SELECT COUNT(<属性名>) FROM <表格>

 

GROUP BY:根据列对结果集进行分组

SELECT <属性1>，<属性2> FROM <表格名> GROUP BY <属性名>

 

HAVING：一般跟在GROUP BY后面，来设定GROUP BY的选择条件

SELECT <属性1>，SUM<属性2> FROM <表格名> BROUP BY<属性1> HAVING（函数条件）

 

AS：可以给选定的表或者列设置别名

 

&&&&&&&&&&&&&&&&&&&&&&&&&例子&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Student（S#，Sname，Sage，Ssex） 学生表        S#：学号；Sname：学生名字；Sage：年龄；Ssex：性别

Course（C#，Cname，T#）课程表              C#：课程编号；Cname：课程名字；T# 教师编号

SC（S#，C#，score）成绩表               S#：学号；C#：课程编号；score：成绩

Teacher（T#，Tname）教师表            T#：教师编号 ；Tname：教师名字

****************************************************************************************************

1>查询“001”课程比“002”课程成绩高的所有学生的学号

SELECT a.S#    FROM (SELECT S#,score FROM SC WHERE C#='001') a,(SELECT S#,score FROM SC WHERE C#='002') b

WHERE a.score>b.score AND a.S#=b.S#;

 

2>查询平均成绩大于60分的同学的学号和平均成绩

SELECT S#，AVG(score)  FROM SC 

GROUP BY S# HAVING AVG(score)>60;

 

3>查询所有同学的学号、姓名、选课数、总成绩

SELECT Student.S#,Student.Sname,Count(SC.C#),SUM(score)

FROM  Student,SC

WHERE Student.S#=SC.C#

GROUP BY Student.S#,Sname;

 

4>查询姓“李”的老师的个数

SELECT COUNT(DISTINCT(Tname))

FROM Teacher

WHERE Tname like '李%';

 

5>查询没学过“叶平”老师课的同学的学号和姓名

SELECT Student.S#,Student.Sname

From Student

WHERE S# NOT IN (SELECT distinct(SC.S#) FROM SC,Course,Teacher WHERE SC.C#=Course.C# AND Course.T#=Teacher.T# AND Teacher.Tname='叶平')

 

6>查询学过“001”课程并且也学过“002”课程的同学的学号、姓名

SELECT Student.S#,Student.Sname

FROM Student ,SC

WHERE Student.S#=SC.S# AND SC.C#='001' AND EXISTS (SELECT * FROM SC AS SC_2 WHERE SC_2.S#=SC.S# AND SC_2.C#='002')

 

7> 查询学过“叶平”老师所教的所有课的同学的学号、姓名

SELECT S#，Sname

FROM Student

WHERE S# IN (

    SELECT S#  FROM SC,Course,Teacher

       WHERE  SC.C#=Course.C#  AND Course.T#=Teacher.T# AND Teacher.Tname='叶平'

       GROUP BY S# HAVING (

             COUNT(SC.C#)=(SELECT COUNT(C#) FROM Course,Tracher  WHERE Teacher.T#=Course.C# AND Tname='叶平')

)

);

 

9>查询所有课程成绩小于60分的同学的学号、姓名

SELECT S#,Sname

FROM Sudent

WHERE S# NOT IN (SELECT Student.S# FROM Student,SC WHERE S.S#=SC.S#  AND score>=60)

 

10>查询没有学全所有课程的同学的学号、姓名

SELECT Student.S#,Student.Sname

FROM SC,Student

WHERE Student.S#=SC.S#  

GROUP BY Student.S#,Student.Sname  HAVING COUNT(C#)<(SELECT COUNT(C#) FROM Course);

 

11>查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名

SELECT S#，Sname FROM Student，SC

WHERE Student.S#=SC.S#  AND C# IN (SELECT  C# FROM SC WHERE S#='1001')

 

12>把SC表中的“夏啸南”老师教的课的成绩更改为此课程的平均成绩

UPDATE SC   SET score=（SELECT  AVG(SC_2.score) FROM SC sc_2 ,Course,Teacher  WHERE SC.C#=SC_2.C# AND Course.C#=SC_2.C# AND Course.T#=Treacher.T#  AND Tname='夏啸南'）;

 

13>查询和“1002”学号的同学学习的课程网球相同的其他同学的学号和姓名

SELECT Student.S#，Sname

FROM Student  SC

WHERE Student.S#=SC.S#  AND C# IN (SELECT C# FROM SC WHERE S#='1002')

GROUP BY S# HAVING COUNT(*)=(SELECT COUNT(*) FROM SC WHERE S#='1002');

 

14>删除学习“夏啸南”老师课的SC表记录

DELETE

FROM SC

WHERE  C# IN（SELECT C# FROM Course Teacher WHERE Course.T#=Teacher.T# AND Tname='夏啸南'）

 

15>向表SC中插入一些记录，这些记录要求符合以下的条件：没有上过“003”课程的同学的学号、2号课程、以及2号课程的平均成绩

INSERT

INTO SC

SELECT S#,'002',(SELECT AVG(score) FROM SC WHERE C#='002')

FROM SC WHERE S# NOT IN (SELECT S# FROM SC SC_2 WHERE C#='003');

 

16>按照平均成绩从高到底显示所有学生的“数据库”、“企业管理”、“英语”三门课程的成绩，按照如下的方式显示：

学生ID，数据库，企业管理，英语，有效课程数，有效平均分

SELECT S#  as 学生ID，

    （SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004'）AS 数据库，

    （SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001'）AS 企业管理，

    （SELECT score FROM SCWHERE SC.S#=t.S# AND C#='005'）AS 英语，

     COUNT（*） AS 有效课程数，AVG（t.score）AS 平均成绩

FROM SC AS t

GROUP BY S#

ORDER BY avg(t.score);

 

17>查询各科成绩最高分和最低分：以如下形式显示：课程ID，最高分，最低分

SELECT L.C# AS 课程ID，L.score AS 最高分，R.score AS 最低分

FROM SC L，SC R

WHERE  L.C#=R.C#  AND  L.score=（SELECT MAX(IL.score)

                                                                       FROM SC AS IL,Student AS IM

                                                                       WHERE L.C#=IL.C#  AND IM.S#=IL.S#

                                                                        GROUP BY IL.C#）

                                                  AND

                                                  R.score=(SELECT MIN(IR.score)

                                                                            FROM SC AS IR

                                                                             WHERE R.C#=IR.C#

                                                                             GROUP BY IR.C#);

 

18>按照各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT t.C# AS 课程号，Cname AS 课程名，isnull(AVG(score),0) AS 平均成绩，100*SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0)/COUNT(*) AS 及格百分数

FROM SC T，Course

WHERE t.C#=Course.C#

GROUP BY t.C#

ORDER BY 100*SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0);

 

19>查询不同老师所教不同课程平均分从高到低显示

SELECT MAX(Z.T#) AS 教师ID，MAX(Z.Tname) AS 教师姓名，C.C# AS 课程ID，MAX(C.Cname) AS 课程名称，AVG(Score) AS 平均成绩

FROM SC AS T，Course AS C，Teacher AS Z

WHERE T.C#=C.C# AND C.T#=Z.T#

BROUP BY C.C#

ORDER BY AVG(score) DESC;