数据库学习记录20150605

1. 给属性列取名：

    SELECT institute AS 所属学院， classNo AS 班级编号， className AS 班级名称，

    FROM Class

2. 将大写字母改为小写字母：

    lower()

3. 获取当前系统的日期、年份、月份：

    getdate() 、year() 、month()

        eg: 查询年龄大于19的学生

              SELCT studentNo, studentName, birthday

               FROM Student

              WHERE year(getdate()) - year(birthday) > = 19

4. 查询属性值在某一个范围：

    BETWEEN AND

    WHERE score BETWEEN 80 AND 90

或  WHERE score > = 80 AND score < = 90

5.查询属性值不在某一个范围：

    WHERE score NOT BETWEEN 80 AND 90

或  WHERE score > = 80 OR score > = 90

6. 集合查询:

    IN 和 NOT IN

        eg: 查询选修了指定科目的全部学生

              SELECT studentNo, courseNo, score

              FROM Score

              WHERE courseNo IN ('001', '003', '005')

7. 空值查询：

    IS null 和 IS NOT null

8. 字符匹配查询：

    LIKE 和 NOT LIKE

    notes：① 不含有通配符时 LIKE 与  “ = ” 查询结果一样。

                  ② 通配符 % 表示任意长度字符串； _ 表示任意一个字符。

        eg: 查询出含有“会计”的班级信息

              SELECT *

              FROM Class

              WHERE className LIKE ' %会计% '

9. 字符匹配查询（需转码）：

    ESCAPE

        eg: 查询出班级表中班级名称中含有“08_”符号的班级名称

              WHERE className LIKE ' %08\_%' ESCAPE ' \ '

10. 排序运算（升序和降序）：

    ASC 和 DESC

        eg: 按籍贯的降序排序输出

              SELECT studentName, native, classNo

              FROM Student

              WHERE native != ' 南昌 ' AND native != '上海 '

              ORDER BY native DESC

11. 查询表：

        eg: 查询1991年出生的女同学基本信息

              SELECT studentNo, studentName, birthday

              FROM ( SELECT * FROM Student WHERE sec = ' 女 ' ) AS a

              WHERE year (birthday) = 1991

12. 等值链接查询：

        eg: SELECT studentNo, studentName, native, b.classNo, className

               FROM Student AS a, Class AS b

               WHERE a.classNo=b.classNo AND institute = ' 会计学院 '

        eg: SELECT a.studentNo, studentName

               FROM Student a, Course b, Score c

               WHERE b.courseNo = c.courseNo AND c.studentNo = a.studentNo

               AND b.courseName = ' 计算机原理 '

13. 外连接：

    LEFT OUTER JOIN、RIGHT OUTER JOIN 、FULL OUTER JOIN

        eg: SELECT className, institute, studentNo, studentName

              FROM Class a LEFT OUTER JOIN Student b ON a.classNo = b.classNo

              ORDER BY className

14. 聚合函数

    count()、sum()、avg()、max()、min()

        eg: 查询学生总人数

              SELECT count (*) 学生人数

               FROM Student

        eg: 查询所有选课学生的人数

              SELECT count ( DISTINCT studentNo )  学生人数

               FROM Score

    notes: WHERE 不能直接使用聚合函数

15. 分组聚合

    GROUP BY 和 HAVING

        eg: SELECT studentNo, count(*) 门数, avg(score)平均分, max(score)最高分

               FROM Score

               GROUP BY studentNo

               HAVING avg(score) >= 80

        eg: 查询成绩最高分的学生

              SELECT studentNo, courseNo, score

               FROM Score

               WHERE score = ( SELECT max (score) FROM Score )

16. 嵌套子查询（支持多重嵌套）

    IN

        eg: 查询选修过课程的学生姓名

               SELECT studentName

               FROM Student

               WHERE Student.studentNo IN ( SELECT Score.studentNo FROM Score)