项目6 CJGL数据库的数据查询

1、项目项目6 CJGL6 CJGL数据库的数据查询数据库的数据查询技能目标技能目标 在在CJGLCJGL数据库中能根据按照指定的要求灵活、快速地数据库中能根据按照指定的要求灵活、快速地查询相关信息。查询相关信息。知识目标知识目标u掌握掌握SELECTSELECT语句语法格式；语句语法格式；u掌握最基本的查询技术；掌握最基本的查询技术；u掌握条件查询技术；掌握多重条件查询技术；掌握条件查询技术；掌握多重条件查询技术；u掌握连接查询技术；掌握嵌套查询。掌握连接查询技术；掌握嵌套查询。 SELECTSELECT语句的语法格式语句的语法格式 SELECT ALL|SELECT ALL|* *|DISTI

2、NCTTOP nTOP n PERCENT|DISTINCTTOP nTOP n PERCENT INTO INTO FROM FROM 数据源列表数据源列表,n,n WHERE WHERE GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY ASC | DESC ASC | DESC SELECTSELECT语句各子句的功能语句各子句的功能（1 1）SELECTSELECT子句：指定查询返回的列。子句：指定查询返回的列。（2 2）INTOINTO子句：将检索结果存储到新表或视图中。子句：将检索结果存储到新表或视图中。（3 3）FROMFROM子

3、句：用于指定查询列所在的表和视图。子句：用于指定查询列所在的表和视图。（4 4）WHEREWHERE子句：指定用于限制返回的行的搜索条件。子句：指定用于限制返回的行的搜索条件。（4 4）GROUP BYGROUP BY子句：指定用来放置输出行的组。并且如果子句：指定用来放置输出行的组。并且如果 SELECT SELECT 子子句句 中包含聚合函数，则计算每组的汇总值。中包含聚合函数，则计算每组的汇总值。 （5 5）HAVINGHAVING子句：指定组或聚合的搜索条件。子句：指定组或聚合的搜索条件。HAVING HAVING 通常与通常与 GROUP GROUP BY BY 子句一起使用。子句一

4、起使用。 （6 6）ORDER BYORDER BY子句：指定结果集的排序。子句：指定结果集的排序。简单查询简单查询-SELECT -SELECT 子句子句 1 1、查询所有的列（使用、查询所有的列（使用* *）【例】从学生表中查询所有学生的信息【例】从学生表中查询所有学生的信息 。 Select * from 学生表学生表2 2、查询指定的列、查询指定的列 【例】从学生表中检索所有学生的学号、姓名、性别和政治面貌。【例】从学生表中检索所有学生的学号、姓名、性别和政治面貌。Select 学号学号,姓名姓名,性别性别,政治面貌政治面貌 from 学生表学生表3 3、使用、使用TOP TOP 关键

5、字关键字 【例】检索学生表中前【例】检索学生表中前3位学生的学号、姓名和性别。位学生的学号、姓名和性别。Select top 3 学号学号,姓名姓名,性别性别 from 学生表学生表4 4、使用、使用DISTINCT DISTINCT 关键字关键字【例】从学生表中查询所有学生的班级信息，并消去重复记录。【例】从学生表中查询所有学生的班级信息，并消去重复记录。Select distinct 班级编号班级编号 from 学生表学生表简单查询简单查询-SELECT -SELECT 子句子句5 5、更改列标题、更改列标题方法：方法：u采用采用“列标题列标题=列名列名”的格式的格式u采用采用“列名列名

6、列标题列标题”的格式的格式u采用采用“列名列名 as 列标题列标题”的格式的格式【例】查询每个学生的学号和姓名，并在标题上显示【例】查询每个学生的学号和姓名，并在标题上显示“学生学生学号学号”和和“学生姓名学生姓名”。 Select Select 学号学号 as as 学生学号学生学号, ,姓名姓名 学生姓名学生姓名 from from 学生表学生表简单查询简单查询-SELECT -SELECT 子句子句6 6、使用计算列、使用计算列【例】查询成绩表中所有学生成绩在提高【例】查询成绩表中所有学生成绩在提高10%后的信息，将提高后后的信息，将提高后的成绩列标题为的成绩列标题为“提高后成绩提高后成

7、绩”。 Select 学号学号,成绩成绩 原成绩原成绩,成绩成绩*1.1 as 提高后成绩提高后成绩 from 成绩表成绩表7 7、使用聚合函数、使用聚合函数 sum()average()max()min()sum()average()max()min()【例】统计学生表有多少名学生。【例】统计学生表有多少名学生。Select count(*) from 学生表学生表简单查询简单查询- INTO - INTO 子句子句 INTO INTO 子句用于将查询的结果插入新表中，其语法格式子句用于将查询的结果插入新表中，其语法格式如下。如下。 INTO INTO 新表名新表名【例】使用【例】使用INT

8、O子句创建一个包含学号和姓名，并命名为子句创建一个包含学号和姓名，并命名为new_student的新表。的新表。USE CJGLGOSELECT 学号,姓名INTO new_studentFROM 学生表GO 简单查询简单查询- WHERE- WHERE子句子句 使用使用WHEREWHERE子句的目的是为了从表格的数据集中过滤子句的目的是为了从表格的数据集中过滤出符合条件的行。出符合条件的行。 其语法格式如下其语法格式如下: :SELECT INTO FROM WHERE 简单查询简单查询- WHERE- WHERE子句子句1.1.比较运算符：比较运算符： 、= =、 、=、=1989-1-1

9、1989-1-12.2.查询政治面貌为党员，族别为少数民族的学生信息，列出所有字段。查询政治面貌为党员，族别为少数民族的学生信息，列出所有字段。 Select Select * * from from 学生表学生表 where where 政治面貌政治面貌=党员党员 and and 族别族别汉族汉族3.3.查询所有姓查询所有姓“李李”和姓和姓“张张”的学生信息，列出所有字段。的学生信息，列出所有字段。 Select Select * * from from 学生表学生表 where where 姓名姓名 likelike李，张李，张%简单查询简单查询- ORDER BY- ORDER BY子句

10、子句 语法格式：语法格式：ORDER BY 排序项排序项 ASC | DESC ,排序项排序项 ASC | DESC,n 【例】按班级编号降序显示学生的学号、姓名、班级和出生日期，【例】按班级编号降序显示学生的学号、姓名、班级和出生日期，班级相同时按年龄由大到小排序。班级相同时按年龄由大到小排序。SELECT 学号,姓名,班级,出生日期FROM 学生表ORDER BY 班级编号 DESC, 出生日期 ASC简单查询简单查询- GROUP BY- GROUP BY子句子句 如果需要按某一列数据的值进行分类，在分类的基如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就要使用础上再进行查

11、询，就要使用GROUP BYGROUP BY子句，它的语法子句，它的语法格式如下。格式如下。 GROUP BY GROUP BY 如：如：1.在课程表中查询不同考核类型的平均学时。在课程表中查询不同考核类型的平均学时。 select 考核类型,avg(学时) 平均学时 from 课程表 group by 考核类型简单查询简单查询- GROUP BY- GROUP BY子句子句 2.在学生表中查询各民族学生的平均年龄，结果要求给出民族和平在学生表中查询各民族学生的平均年龄，结果要求给出民族和平均年龄。均年龄。 select 族别,avg(datediff(yy,出生日期,getdate() 平均

12、年龄 from 学生表 group by 族别其中，datediff()是系统函数，返回两个指定日期间的时间间隔数目。共有三个参数： datediff(datepart,startdate,enddate) 1）datepart参数可以是下列的值：年 yy/yyyy；季度 qq/ q； 月 mm/ m；日 dd/ d；周 wk/ww；星期dw/w；小时hh； 分钟mi/n；秒ss/s；毫秒ms；微妙mcs；纳秒ns 2）startdate 和 enddate 参数是合法的日期表达式。 getdate()返回当前的日期和时间。简单查询简单查询- HAVING- HAVING子句子句 HAVING

13、 HAVING子句指定组或聚合的搜索条件。子句指定组或聚合的搜索条件。HAVINGHAVING通常在通常在GROUP BY GROUP BY 子句中使用。如果不使用子句中使用。如果不使用GROUP BY GROUP BY 子句，则子句，则 HAVINGHAVING的行为与的行为与WHERE WHERE 子句一样，它的语法格式如下。子句一样，它的语法格式如下。 HAVING 搜索条件搜索条件 如：查询如：查询“信息工程系信息工程系”（班级号第（班级号第4,5位为位为“04”）所有班级中，）所有班级中，人数少于人数少于30人的班级编号和各班班级人数人的班级编号和各班班级人数Select 班级编号,

14、count(学号) 班级人数From 学生表Where substring(班级编号,4,2)=04Group by 班级编号 having count(学号)30简单查询简单查询- COMPUTE - COMPUTE 子句子句 COMPUTE COMPUTE子句可以用于子句可以用于SELECT SELECT 语句中既可查看明细语句中既可查看明细行，又可查看汇总行。可以计算分组的汇总值，也可以计行，又可查看汇总行。可以计算分组的汇总值，也可以计算整个结果集的汇总值。语法格式如下。算整个结果集的汇总值。语法格式如下。 COMPUTE 聚合函数名聚合函数名 (列名列名) , N BY 表达式表达式

15、, N 如：查询课程表中考核类型为考试的各课程信息，并计算总学时。如：查询课程表中考核类型为考试的各课程信息，并计算总学时。Select *From 课程表Where 考核类型=考试Compute sum(学时)多表查询多表查询 某些查询涉及的数据可能来自不同的表，此类查询称为多表查询，包括联合查某些查询涉及的数据可能来自不同的表，此类查询称为多表查询，包括联合查询和连接查询。询和连接查询。联合查询格式：联合查询格式：SELECT FROM UNIONSELECT FROM 如：分别查询部门表和学生表，给出系部编号、系部名称，学生的学号、姓名和性别，如：分别查询部门表和学生表，给出系部编号、系

16、部名称，学生的学号、姓名和性别，并将结果联合输出。并将结果联合输出。 select 学号学号,姓名姓名,性别性别 from 学生表学生表 union select 系部编号系部编号,系部名称系部名称 from 部门表部门表连接查询连接查询 连接的类型分为内连接、外连接和交叉连接。连接的连接的类型分为内连接、外连接和交叉连接。连接的格式有两种如下。格式有两种如下。格式一：格式一：SELECT FROM ON ( )格式二：格式二： SELECT FROM , WHERE . . 交叉连接交叉连接 交叉连接又称笛卡儿积，返回两个表的乘积。交叉连接又称笛卡儿积，返回两个表的乘积。 例：例： 表表A有

17、有10行数据，表行数据，表B有有20行数据，那么表行数据，那么表A和表和表B交叉连接的结交叉连接的结果记录集有果记录集有200行（行（1020）数据。）数据。 交叉连接使用交叉连接使用CROSS JOINCROSS JOIN关键字来创建。关键字来创建。 说明：说明： 交叉连接只是用于测试一个数据库的执行效率，在实际应用中是交叉连接只是用于测试一个数据库的执行效率，在实际应用中是无意义的无意义的 交叉连接的使用是比较少的，交叉连接不需要连接条件。交叉连接的使用是比较少的，交叉连接不需要连接条件。交叉连接（续）交叉连接（续）如：查询成绩表与学生表的所有组合。如：查询成绩表与学生表的所有组合。SEL

18、ECT成绩表成绩表 .* ,学生表学生表.* FROM 成绩表成绩表 CROSS JOIN 学生表学生表内连接内连接 内连接把两个表中的数据，通过相同的列，连接生成第内连接把两个表中的数据，通过相同的列，连接生成第3 3个表，仅个表，仅包含那些满足连接条件的数据行。内连接可使用包含那些满足连接条件的数据行。内连接可使用INNER JOININNER JOIN关键字关键字来创建。来创建。如：如：1. 查询学生记录，列出学号、姓名、所属班级名称查询学生记录，列出学号、姓名、所属班级名称 select 学号,姓名,班级名称 from 学生表 as S join 班级表 as C on S.班级编号=

19、C.班级编号 2. 查询课程表中课程名称包含查询课程表中课程名称包含“数据库数据库”的平均分数的平均分数 select avg(成绩) 平均分 from 成绩表 S,课程表 C where S.课程编号=C.课程编号 and 课程名称 like%数据库%说明：说明： 在多表查询中，在多表查询中，Select子句或子句或 Where子句中的列名前都加上了子句中的列名前都加上了表名作为前缀，这样可避免来自不同表中相同属性名发生混淆。表名作为前缀，这样可避免来自不同表中相同属性名发生混淆。 由由于多次用到表名，为了便于阅读和书写，可以使用表的别名形式。于多次用到表名，为了便于阅读和书写，可以使用表的

20、别名形式。内连接内连接 3. 查询查询“信息工程系信息工程系”所有班级信息，列出班级名称与人数所有班级信息，列出班级名称与人数 select 班级名称,count(学号) 学生人数 from 学生表 S,班级表 C,部门表 D where S.班级编号=C.班级编号 and C.系部编号=D.系部编号 and 系部名称=信息工程系 group by 班级名称外连接外连接 分类：左外连接、右外连接和全外连接。分类：左外连接、右外连接和全外连接。u左外连接：只包括左表的所有行，不包括右表的不匹配行左外连接：只包括左表的所有行，不包括右表的不匹配行的外连接；的外连接；u右外连接：只包括右表的所有行，

21、不包括左表的不匹配行右外连接：只包括右表的所有行，不包括左表的不匹配行的外连接。的外连接。u完整外部连接：既包括左表不匹配的行，也包括右表的不完整外部连接：既包括左表不匹配的行，也包括右表的不匹配的行的连接匹配的行的连接 左外连接左外连接 语法如下：语法如下：SELECT FROM 左表名左表名 LEFT OUTER JOIN 右表名右表名ON 连接条件连接条件 【例】查询是否所有的学生均有成绩，包括学生的姓名和成绩相关【例】查询是否所有的学生均有成绩，包括学生的姓名和成绩相关信息。信息。SELECT 姓名, 成绩表.*FROM 学生表 LEFT JOIN 成绩表 on 学生表.学号 = 成绩

22、表.学号右外连接右外连接右外连接的语法如下。右外连接的语法如下。SELECT FROM 左表名左表名 RIGHT OUTER JOIN 右表名右表名ON 连接条件连接条件【例】查询是否所有的学生均有成绩，包括学生的姓名和成绩相关【例】查询是否所有的学生均有成绩，包括学生的姓名和成绩相关信息。信息。SELECT成绩表.*,姓名FROM 成绩表 RIGHT JOIN 学生表 on 成绩表.学号 = 学生表.学号全外连接全外连接 全外连接的语法如下。全外连接的语法如下。SELECT FROM 左表名左表名 FULL OUTER JOIN 右表名右表名ON 连接条件连接条件 包括所有连接表中的所有记录

23、，不论它们是否匹配。包括所有连接表中的所有记录，不论它们是否匹配。混合连接混合连接 可将内连接和外连接灵活混合使用。可将内连接和外连接灵活混合使用。如：查询如：查询“0900401001”0900401001”班级中所有学生的成绩（包括班级编号、班级名称、班级中所有学生的成绩（包括班级编号、班级名称、学号、姓名、成绩），如果没有成绩，也要给出学生姓名（外连接）。学号、姓名、成绩），如果没有成绩，也要给出学生姓名（外连接）。Select C. 班级编号班级编号, 班级名称班级名称,S1.学号学号,姓名姓名,成绩成绩From 班级表班级表 C join 学生表学生表 S1 on C.班级编号班级编

24、号=S.班级编号班级编号 left join 成绩表成绩表 S2 on S1.学号学号=S2.学号学号Where C.班级编号班级编号=0900401001子查询子查询 在在SQLSQL中，将一条中，将一条SELECTSELECT语句作为另一条语句作为另一条SELECTSELECT语句语句的一部分称为子查询。外层的的一部分称为子查询。外层的SELECTSELECT语句被称为外部查询语句被称为外部查询或父查询，内层的或父查询，内层的SELECTSELECT语句成为内部查询或子查询。语句成为内部查询或子查询。 子查询如下。子查询如下。SELECT /*外层查询或父查询外层查询或父查询*/FROM

25、WHERE IN ( SELECT /*内层查询或子查询内层查询或子查询*/ FROM WHERE )单值嵌套单值嵌套 单值嵌套就是通过子查询返回一个单一的数据。当子单值嵌套就是通过子查询返回一个单一的数据。当子查询返回的是单值，可以使用查询返回的是单值，可以使用 ， ，= =，=或或等比等比较运算符参加相关表达式的运算。较运算符参加相关表达式的运算。单值嵌套（续）单值嵌套（续）【例【例 】查找学生】查找学生“张三张三”所在的班级名称。所在的班级名称。 步骤步骤1：查询学生：查询学生“张三张三”所在的班级编号，查询语句如下。所在的班级编号，查询语句如下。SELECT 班级编号FROM 学生表W

26、HERE 姓名=张三得到的结果为得到的结果为 0900401001步骤步骤2：查询班级编号为：查询班级编号为0900401001 的班级名称。的班级名称。 SELECT 班级名称FROM 班级表WHERE 班级编号=0900401001得到结果为得到结果为“11网络技术网络技术”。 组合：组合：SELECT 班级名称FROM 班级表WHERE 班级编号 =(SELECT 班级编号班级编号 FROM 学生表学生表 WHERE 姓名姓名=张三张三)单值嵌套（续）单值嵌套（续）如：如：1. 查找年龄最大的学生信息。查找年龄最大的学生信息。 select *from 学生表where 出生日期=(select min(出生日期) from 学生表)2. 查询学生表中比学号查询学生表中比学号“002”的学生年龄大的学生记录的学生年龄大的学生记录select *from 学生表where 出生日期any(select 出生日期 from 学生表)多值嵌套多值嵌套 子查询的返回结果是一列值的嵌套查询称为多值嵌套子查询的返回结果是一列值的嵌套查询称为多值嵌套查询。多值嵌套查询经常使用查询。多值嵌套查询经常使用ININ操作符。操作符。ININ操作符使用的操作符使用的格式如下。格式如下。 表达式表