大学数据库知识点

1、第六章数据库检索简单查询SELECT语句的基本语法格式：SELECT查询项列表INTO新表名FROM数据源表列表WHERE条件表达式ORDER BY排序依据排序方式GROUP BY分组依据HAVING条件表达式基本查询格式：SELECT查询项列表FROM数据源表说明查询项列表中各选项用逗号(，)隔开；查询项中可以包含：*、DISTINCT. TOP n、AS别名的使用； 例：在学生管理数据库系统(student)中，有学生信息表(student)、学生成绩表(stgrade)、课程信息表(course)，具体结构如下显示，完成该数据库系统中的查询。STUDENT (Sno,Sname,Sex,

2、Birth,Addr,Dept)，分别表示学号、姓名、性别、出生 日期、地址、专业；STGRADE(Sno,Cno,Grade,Score)，分别表示学号、课程号、成绩、所得学分；COURSE(Cno,Cname,Term,Point,Tname)，分别表示课程号、课程名称、基本学分、 任课教师姓名。从student表中查询学生的学号，姓名，性别，出生日期信息。SELECT sno,sname,sex,birth FROM student从student表中查询所有学生的所有信息。SELECT * FROM student(*代表表中的所有字段)从student表中查询专业信息SELECT d

3、ept FROM student(结果中会出现重复值，即学号有重复)对比：SELECT DISTINCT dept FROM student (DISTINCT 用于去掉查询结果中的 重复值)从stgrade表中查询选修了课程的学生学号。(同上小题)SELECT sno FROM stgrade对比：SELECT DISTINCT sno FROM stgrade从student表中查询前10个学生的学号，姓名，性别信息。(或前10%个学生的信息)SELECT TOP 10 sno,sname,sex FROM studentSELECT TOP 10 PERCENT sno,sname,se

4、x FROM studentTOP n用于限制在结果中只显示前面的n条记录；TOP n PERCENT用于限制在结果中 只显示前面的n%条记录。从student表中查询学生的学号，姓名，性别，年龄信息。提示：年龄可通过birth字段的计算得到，age=YEAR(GETDATE()-YEAR(birth)SELECT sno,sname,sex,YEAR(GETDATE()-YEAR(birth) FROM studentSELECT sno,sname,sex,YEAR(GETDATE()-YEAR(birth) AS age FROM studentSELECT sno,sname,sex,

5、age=YEAR(GETDATE()-YEAR(birth) FROM student 其中：为结果集内的列指定别名有3中写法，如下：、select列名(表达式)列别名from数据源、select列名(表达式)AS列别名from数据源、select列别名=列名(表达式)from数据源带条件的查询：WHERE子句的应用格式：SELECT查询项列表FROM数据源表 WHERE条件表达式说明：查询中希望得到表中满足特定条件的一些记录时，用户可以在查询语句中使用WHERE 子句(2)常用的查询条件及运算符如下:查询条件运算符意义例比较=,=(!),),(!=),或NOT+上述运算符比较大小成绩50确定

6、范围NOT BETWEEN AND 判断值是否在范围中成绩 BETWEEN 60 AND 100确定集合IN(列表)/ NOT IN例表)判断值是否为列表中的值专业IN物流，财会)字符匹配LIKE / NOT LIKE判断值是否与指定的字符通配格式相符姓名LIKE ，王专业NOT LIKE 数据空值IS NULL / NOT IS NULL判断值是否为空成绩IS NULL多重条件AND,OR,NOT用于多重条件判断姓名LIKE ，王 AND专业IN( 物流，财会)例：数据库表同上从student表中查询所有男生记录。SELECT * FROM student WHERE sex=男从stude

7、nt表中查询所有非“计算机”专业的学生信息。SELECT * FROM student WHERE dept计算机从student表中查询1985年之后(包括1985年)出生的学生信息。SELECT * FROM student WHERE birth=1985/1/1SELECT * FROM student WHERE YEAR(birth)=1985从student表中查询年龄在18至20岁之间的学生学号、姓名、性别、专业信息。SELECT sno,sname,sex,dept FROM studentWHERE YEAR(GETDATE()-YEAR(birth) BETWEEN 18

8、 AND 20从student表中查询所有姓“王”的学生学号、姓名、性别、专业信息。SELECT sno,sname,sex,dept FROM student WHERE sname LIKE 王SELECT sno,sname,sex,dept FROM student WHERE sname=K()从student表中查询所有姓名中的第二个字不是“小”的学生信息。SELECT * FROM student WHERE sname NOT LIKE _小从student表中查询专业为“数学”，“英语”和“计算机”专业的学生信息。SELECT * FROM student WHERE dep

9、t IN (数学，英语，计算机)或：SELECT * FROM student WHERE dept=数学OR dept=英语OR dept=计算机从student表中查询除“数学”，“英语”和“计算机”以外专业的学生信息。SELECT * FROM student WHERE dept NOT IN (数学，英语，计算机)或：SELECT * FROM studentWHERE dept数学AND dept英语AND dept计算机从stgrade表中查询课程号为“C01”，成绩合格的记录信息。SELECT * FROM stgrade WHERE cno= C01 AND grade=60

10、从stgrade表中查询所有成绩为空的记录。SELECT * FROM stgrade WHERE grade IS NULLSELECT * FROM stgrade WHERE grade NULL注意：如果要查询的字符串本身含有%或_，需要使用ESCAPE换码字符对通配符进行转义。例如：SELECT CNO,CNAMEFROM COURSEWHERE CNAME like VB/_6.0 ESCAPE /对查询结果排序一一ORDER BY子句的应用格式：SELECT查询项列表FROM数据源表WHERE条件表达式ORDER BY排序字段排序方式说明：排序方式默认为“升序”；其中ASC表示升

11、序排列，DESC表示降序排列若需按多个字段排序，则多个字段间用“，”隔开，具体可参见例题。举例：数据库同上从student表中查询所有学生的学号、姓名、性别、出生日期和专业，并将结果按出 生日期升序显示(即按年龄从大到小排序)。SELECT sno,sname,sex,birth,dept FROM student ORDER BY birth从stgrade表中查询选修了课程号为“C01”课程的学生的学号和成绩，并将结果按 成绩降序显示(即按成绩从高到低排序)。SELECT sno,grade FROM stgrade WHERE cno=c01 ORDER BY grade DESC从st

12、udent表中查询所有学生的信息，并将结果按专业的降序排列，专业相同的学生 的学号升序排列。SELECT * FROM student ORDER BY dept DESC,sno ASC /* 其中 ASC 可省略 */从stgrade表中查询所有信息，并将结果按课程号的降序排列，课程号相同的按成绩 由高到低排列(降序)。SELECT * FROM stgrade ORDER BY cno desc,grade DESC四、对数据进行统计一一聚合函数的应用聚合函数及功能：COUNT(*):统计记录的个数COUNT(列名)：统计一列中非空值的个数SUM(列名)：统计一列值的总和(该列必须是数值

13、型)AVG(列名)：统计一列值的平均值(该列必须是数值型)MAX(列名)：统计一列值的最大值MIN(列名)：统计一列值的最小值说明：允许使用(DISTINCT|ALL列名)，其中DISTINCT表示去掉指定列中的重复值再进 行统计；ALL表示不取消重复值，默认是ALL。例：从student表中查询学生的总人数。SELECT COUNT(*) FROM student从stgrade表中查询选修了课程号为“C01”课程的学生人数。SELECT COUNT(*) FROM stgrade WHERE cno=c01从stgrade表中查询成绩不为空的记录个数。SELECT COUNT(grade)

14、 FROM stgradeSELECT COUNT(*) FROM stgrade WHERE grade IS NOT null从student表中查询学生专业个数。SELECT COUNT(DISTINCT DEPT) FROM student从stgrade表中查询成绩的平均值、总分、最高分及最低分。SELECT AVG(grade),SUM(grade),MAX(grade),MIN(grade) FROM stgrade五、对结果进行分组一一GROUP BY子句的应用格式：SELECT查询项列表FROM数据源表列表WHERE条件表达式GROUP BY分组依据HAVING条件表达式OR

15、DER BY排序依据排序方式说明：(具体参见例题)SELECT子句中的列名必须是GROUP BY子句中已有的列名或是包含在聚合函数中的 列名；HAVING子句必须用于GROUP BY子句的后面；HAVING子句与WHERE子句的区别:WHERE子句用于对表中记录进行条件筛选；HAVING 子句用于对分组统计后的结果进行条件筛选；如果查询语句中既有ORDER BY子句又有GROUP BY子句，则ORDER BY子句用于GROUP BY子句之后。例：从student表中查询每个专业的学生人数(即按dept值分组，统计记录个数)。SELECT dept,COUNT(*) FROM student G

16、ROUP BY dept从stgrade表中查询每门课程的成绩平均值，并将结果按成绩平均值从高到低排列。SELECT cno,AVG(grade) FROM stgrade GROUP BY cno ORDER BY AVG(grade) DESCSELECT cno,AVG(grade) as 平均成绩 FROM stgradeGROUP BY cno ORDER BY 平均成绩 DESC从student表中查询专业为“Computer”的学生人数。(即从student表中统计各 个专业的学生人数，并筛选出专业为“Computer”的结果。)SELECT COUNT(*) FROM stud

17、ent WHERE dept=Computer GROUP BY deptSELECT COUNT(*) FROM student GROUP BY dept HAVING dept=ComputerSELECT COUNT(*) FROM student HAVING dept=Computer从student表中查询学生人数超过10人的专业及对应的学生人数。SELECT dept,COUNT(*)FROMstudentGROUPBY dept HAVING COUNT(*)=10SELECT dept,COUNT(*)FROMstudentGROUPBY dept WHERE COUNT(

18、*)=10SELECT dept,COUNT(*)FROMstudentWHERECOUNT(*)=10 GROUP BY dept从stgrade表中查询每个学生成绩合格的课程数目。SELECT sno,count(*) FROM stgrade WHERE grade=60 GROUP BY sno从stgrade表中查询每个学生成绩合格的课程数目，并从结果中筛选出合格数目超 过3(包括3)人的学号及课程数。SELECT sno,count(*) FROM stgrade WHERE grade=60 GROUP BY sno HAVING count(*)=3其他子句 INTO子句、UN

19、ION、COMPUTE的应用格式：SELECT查询项列表INTO新表名FROM数据源表列表 SELECT查询项列表FROM数据源表列表 UNION SELECT查询项列表FROM数据源表列表 SELECT查询项列表 FROM数据源表列表COMPUTE集合函数BY列名说明：INTO子句用于将查询结果生成新表(或临时表：表名前加#)UNION用于将查询结果合并显示；要求各结果集中的列数必须相同；且对应的数据类 型也必须相同。COMPUTE自子句用于对查询结果的所有记录进行汇总统计，并显示所有参加汇总的详 细信息；“BY列名”是指按列名字段进行分组计算，并显示被统计记录的详细信息； BY选项必须与O

20、RDER BY子句一起使用，即必须按某一字段进行排序后才能分组。例：SELECT sno,sname,sex INTO xs FROM student WHERE dept=ComputerSELECT sno,sname,sex FROM student WHERE dept=ComputerUNIONSELECT sno,sname,sex FROM student WHERE dept=English对比： SELECT sno,sname,sex FROM student WHERE dept= Computer AND dept= EnglishSELECT * FROM xsUNI

21、ONSELECT sno,sname,sex FROM student WHERE dept= EnglishSELECT * FROM stgrade COMPUTE sum(grade),avg(grade),count(grade)SELECT * FROM stgrade ORDER BY sno COMPUTE sum(grade),avg(grade),count(grade)BY sno高级查询一.连接查询概念及分类若一个查询同时涉及多个表，则称之为连接查询。包括非等值查询、自然连接、自身查询、外连接等应用范围：查询项来自于多个表、或查询项及查询条件来自于多个表。交叉连接=广义笛卡尔积(不带连接条件的多表连接，一般无实际应用意义)语法格式：SELECT查询项列表FROM表1,表2SELECT 查询项列表 FROM 表 1 CROSS JOIN 表 2（2）实例：（略）等值与非等值连接（带连接条件的多表连接（1）语法格式：SELECT查询项列表FROM表1 连接方式表2 ON连接条件SELECT查询项列表FROM表1,表2 WHERE连接条件 （仅用于内连接）（2）说明：连接条件的一般格式：表1.列名 比较运算符 表2.列名其中的比较运算符有：=,=,=,=,!=（仅用于单值子查询）IN、比较运算符结合A