SQL语句练习

忘是亡心i 2022-05-09 02:18 354阅读 0赞

本文章将给大家列举一下sql的语法。

sql的关键字有SELECT，FROM，GROUP BY，WHERE，ORDER BY，HAVING，DROP，ALTER，UNION，UNION ALL，LIKE

例子有四个表：学生表S(SID,SNAME,AGE,SEX)，课程表(CID,CNAME,TID)，成绩表(SID,CID,SCORE)，教师表(TID,TNAME)

建表的sql：[https://blog.csdn.net/glgom/article/details/83062108][https_blog.csdn.net_glgom_article_details_83062108]

--------------------

1.\-- 统计各科成绩、各分数段人数（课程ID,课程名称，\[100-85\]优秀，\[80-70\]良，\[70-60\]中，\[<60\]差）

SELECT SC.CID '课程ID',C.CNAME '课程名称',  
SUM(CASE WHEN SC.SCORE<=100 AND SC.SCORE>85 THEN 1 ELSE 0 END) AS '优秀',  
SUM(CASE WHEN SC.SCORE<=85 AND SC.SCORE>70 THEN 1 ELSE 0 END) AS '良',  
SUM(CASE WHEN SC.SCORE<=70 AND SC.SCORE>60 THEN 1 ELSE 0 END) AS '中',  
SUM(CASE WHEN SC.SCORE<=60 THEN 1 ELSE 0 END) AS '差'  
FROM SC  
LEFT JOIN C ON C.CID=SC.CID  
GROUP BY SC.CID  
或者  
SELECT SC.CID '课程ID',C.CNAME '课程名称',  
COUNT(CASE WHEN SC.SCORE<=100 AND SC.SCORE>85 THEN SCORE ELSE NULL END) AS '优秀',  
COUNT(CASE WHEN SC.SCORE<=85 AND SC.SCORE>70 THEN SCORE ELSE NULL END) AS '良',  
COUNT(CASE WHEN SC.SCORE<=70 AND SC.SCORE>60 THEN SCORE ELSE NULL END) AS '中',  
COUNT(CASE WHEN SC.SCORE<=60 THEN SCORE ELSE NULL END) AS '差'  
FROM SC  
LEFT JOIN C ON C.CID=SC.CID  
GROUP BY SC.CID

--------------------

2.\-- 查询"001"课程比"002"课程成绩高的所有学生的学号

要考虑到课程的成绩可能是没有的，因为是001比002课程高，所以左连接002课程就好，如果为空的成绩就为0  
SELECT T3.SID,T3.ONE,T3.TWO  
FROM(  
SELECT SC.SID,SC.SCORE ONE,CASE WHEN T1.SCORE THEN T1.SCORE ELSE 0 END TWO  
FROM SC  
LEFT JOIN SC T1 ON T1.SID = SC.SID AND T1.CID='002'  
WHERE SC.CID='001'  
)T3  
WHERE T3.ONE>T3.TWO

--------------------

3.\-- 查询平均成绩大于60份的同学的学号和平均成绩

SELECT SID,AVG(SCORE) AVG\_SCORE  
FROM SC  
GROUP BY SID  
HAVING AVG(SCORE)>60

若是只想保留小数点的后两位，则  
SELECT SID,CAST(AVG(SCORE) AS DECIMAL(18,2)) AVG  
FROM SC  
GROUP BY SID  
HAVING AVG(SCORE)>60

\-- decimal(a,b),存储数据范围是：-10^38~10^38-1 的固定精度和小数位的数字。a表示整个数值占据的位数，b为小数的位数，如decimal(10,2),数值一共有10位，整数占8位，小数占两位  
\-- cast(expression AS data\_type)是用来数据类型转换的，expression是任何有效的表达式

--------------------

4.-- 查询没学过"凯尔"老师课的同学学号，姓名  
SELECT S.SID,SNAME  
FROM S  
WHERE S.SID NOT IN(  
    SELECT DISTINCT SC.SID  
    FROM SC  
    WHERE SC.CID IN(  
        SELECT C.CID  
        FROM C,T  
        WHERE C.TID=T.TID AND T.TNAME='凯尔'  
    )  
)  
\-- distinct关键字，去重，必须放在开头，不能是select cid, distinct sid  
;

--------------------

5.\-- 查询学过“语文”并且学过“物理”的学生信息  
SELECT S.SID,S.SNAME,S.AGE,S.SEX  
FROM S  
INNER JOIN(  
    SELECT DISTINCT SC.SID  
    FROM SC  
    INNER JOIN C ON C.CID=SC.CID AND C.CNAME='语文'  
)T1 ON T1.SID=S.SID  
INNER JOIN(  
  SELECT DISTINCT SC.SID  
    FROM SC  
    INNER JOIN C ON C.CID=SC.CID AND C.CNAME='物理'  
)T2 ON T2.SID=S.SID  
;

--------------------

6.\-- 查询学过课程号'005'和课程号'033'的学生信息  
SELECT S.SID,S.SNAME,S.AGE,S.SEX  
FROM S,SC T1,SC T2  
WHERE S.SID=T1.SID AND S.SID=T2.SID AND T1.CID='005' AND T2.CID='033'  
;

\-- 或者  
SELECT S.SID,S.SNAME,S.AGE,S.SEX  
FROM S  
INNER JOIN(  
    SELECT DISTINCT SC.SID  
    FROM SC  
    WHERE SC.CID='005'  
) T1 ON T1.SID=S.SID  
INNER JOIN(  
    SELECT DISTINCT SC.SID  
    FROM SC  
    WHERE SC.CID='033'  
) T2 ON T2.SID=S.SID

--------------------

7.\-- 查询学过'010'但没学过'002'的学生的信息  
SELECT S.SID,S.SNAME,S.AGE,S.SEX  
FROM S  
WHERE S.SID IN(  
    SELECT DISTINCT SC.SID  
    FROM SC  
    WHERE SC.CID='010'  
)  
AND S.SID NOT IN(  
    SELECT DISTINCT SC.SID  
    FROM SC  
    WHERE SC.CID='002'  
)  
;

--------------------

8.-- 查询每位同学的每门课程的成绩(学生学号，姓名，课程1，课程2，····)  
SELECT S.SID,S.SNAME,  
MAX(CASE SC.CID WHEN '001' THEN SC.SCORE ELSE 0 END) '数据结构',  
MAX(CASE SC.CID WHEN '002' THEN SC.SCORE ELSE 0 END) '语文',  
MAX(CASE SC.CID WHEN '005' THEN SC.SCORE ELSE 0 END) '数学',  
MAX(CASE SC.CID WHEN '010' THEN SC.SCORE ELSE 0 END) '物理',  
MAX(CASE SC.CID WHEN '011' THEN SC.SCORE ELSE 0 END) '生物',  
MAX(CASE SC.CID WHEN '021' THEN SC.SCORE ELSE 0 END) '化学',  
MAX(CASE SC.CID WHEN '033' THEN SC.SCORE ELSE 0 END) '英语'  
FROM S  
LEFT JOIN SC ON SC.SID=S.SID  
GROUP BY S.SID  
\-- 用MAX是为了将无数据的点设为0，防止出现NULL  
;

9.-- 查询老师"凯尔"和老师"克列"的学生信息  
SELECT S.SID,S.SNAME,S.AGE,S.SEX  
FROM S  
INNER JOIN(  
    SELECT DISTINCT SC.SID  
    FROM SC  
    INNER JOIN C ON C.CID=SC.CID AND C.CID IN(  
        SELECT C.CID  
        FROM C  
        INNER JOIN T ON T.TID=C.TID AND T.TNAME="凯尔"  
    )  
)T1 ON T1.SID=S.SID  
UNION  
SELECT S.SID,S.SNAME,S.AGE,S.SEX  
FROM S  
INNER JOIN(  
    SELECT DISTINCT SC.SID  
    FROM SC  
    INNER JOIN C ON C.CID=SC.CID AND C.CID IN(  
        SELECT C.CID  
        FROM C  
        INNER JOIN T ON T.TID=C.TID AND T.TNAME="克列"  
    )  
)T1 ON T1.SID=S.SID  
;  
\-- UNION合并查询结果并去重，UNION ALL合并查询结果但不去重

[https_blog.csdn.net_glgom_article_details_83062108]: https://blog.csdn.net/glgom/article/details/83062108