第5章 SQL(结构化查询语言)

1、河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 SQL SQL5.1SELECTSELECT查询语句查询语句5.2基于单表的查询基于单表的查询5.3基于多表的连接查询基于多表的连接查询5.4子子 查查 询询 5.5河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 SQL的全称为的全称为Structured Query Language（结构化查询语言），它利用一（结构化查询语言），它利用一些简单的句子构成基本的语法，来存取数些简单的句子构成基本的语法，来存取数据库的内容。据库的内容。 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.1.1 SQL

2、概述概述 SQL是应用于数据库的语言，本身是是应用于数据库的语言，本身是不能独立存在的。不能独立存在的。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.1.2 SQL的分类的分类 SQL按照用途可以分为如下按照用途可以分为如下3类。类。1数据定义语言（数据定义语言（Data Definition Language，DDL） 类似于这一类定义数据库对象的类似于这一类定义数据库对象的SQL叙述即为叙述即为DDL语言。例如，数据库创建语语言。例如，数据库创建语句（句（CREATE DATABASE）和表创建语）和表创建语句（句（CREATE TABLE）等。）等。河南工程学院河南工

3、程学院 计算机科学与工程系计算机科学与工程系2数据处理语言（数据处理语言（Data Manipulation Language，DML） SQL语法中处理数据语言称为语法中处理数据语言称为DML。例如，使用例如，使用SELECT（数据查询语句）查（数据查询语句）查询表中的内容，或者使用询表中的内容，或者使用INSERT（插入（插入语句）、语句）、DELETE（删除语句）和（删除语句）和UPDATE（更新语句）插入、修改和更新（更新语句）插入、修改和更新一笔记录等，这些语句属于一笔记录等，这些语句属于DML。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系3数据控制语言（数据控制语

4、言（Data Control Language，DCL） 数据控制语言（数据控制语言（DCL）是用来设置或）是用来设置或者更改数据库用户或角色权限的语句，这者更改数据库用户或角色权限的语句，这些语句包括些语句包括grant、deny、revoke等语句，等语句，在默认状态下，只有在默认状态下，只有sysadmin、dbcreator、db_owner或或db_securityadmin等角色的成等角色的成员才有权利执行数据控制语言。员才有权利执行数据控制语言。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 SQL Server是一个关系数据库管理系是一个关系数据库管理系统，建立

5、在关系模型基础之上，具有严格统，建立在关系模型基础之上，具有严格的数学理论基础。关系运算的特点是运算的数学理论基础。关系运算的特点是运算的对象和结果都是表。的对象和结果都是表。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系学 号姓 名专业名性别出生时间总学分备注001101王林计算机男1980-02-1050 001102程明计算机男1981-02-0150 001103王燕计算机女1979-10-0650 选择是单目运算，其运算对象是一个表。该运算按给定的条件，选择是单目运算，其运算对象是一个表。该运算按给定的条件，从表中选出满足条件的行形成一个新表作为运算结果。从表中选出满

6、足条件的行形成一个新表作为运算结果。选择运算的记号为选择运算的记号为 F(R)。其中其中是选择运算符，下标是选择运算符，下标F是一个条件表达式，是一个条件表达式，R是被操作的表。是被操作的表。【例例4.1】学生情况表如表学生情况表如表4.1所示。所示。运算式：运算式：性别性别=女女(学生学生)，该选择运算的结果如表，该选择运算的结果如表4.2所示。所示。学 号姓 名专业名性别出生时间总学分备注001103王燕计算机女1979-10-0650 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系投影也是单目运算，该运算从表中选出指定的属性值组成一个新表，投影也是单目运算，该运算从表中选

7、出指定的属性值组成一个新表，记为：记为：A(R)。其中其中A是属性名（即列名）表，是属性名（即列名）表，R是表名。是表名。【例例4.2】若在表若在表4.1中对学号、姓名和总学分投影，运算式为：中对学号、姓名和总学分投影，运算式为：学号，姓名，总学分学号，姓名，总学分（学生）（学生）该运算得到如表该运算得到如表4.3所示的新表。所示的新表。学 号姓 名总学分001101王林50001102程明50001103王燕50 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系T1T2T3T4T51A13M2B20N 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 等值连接：两

8、个表的某些列值相等。等值连接：两个表的某些列值相等。自然连接：要求两个表有共同属性（列），自然连接：要求两个表有共同属性（列），结果表是在参与操作的两个表的共同属性上结果表是在参与操作的两个表的共同属性上进行等值连接再去除重复的属性后所得的新进行等值连接再去除重复的属性后所得的新表。自然连接运算记为：表。自然连接运算记为：R S，其中，其中R和和S是参与运算的两个表。是参与运算的两个表。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 SQL查询分析器是一个图形用户界面，查询分析器是一个图形用户界面，用以交互地设计和测

9、试用以交互地设计和测试Transact-SQL语句、语句、批处理和脚本。批处理和脚本。 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.2.1 查询分析器查询分析器 启动启动SQL查询分析器可采用两种方法：查询分析器可采用两种方法： 在在“开始开始”菜单中，通过菜单中，通过Microsoft SQL Server下拉菜单的下拉菜单的“查询分析器查询分析器”命命令来打开查询分析器。令来打开查询分析器。 在在SQL Server企业管理器中通过工具菜企业管理器中通过工具菜单启动。单启动。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 启动启动SQL查询分析器并执行

10、相应语句查询分析器并执行相应语句的操作步骤如下。的操作步骤如下。（1）在）在“开始开始”菜单中，单击菜单中，单击“程序程序”级级联菜单，选择联菜单，选择“Microsoft SQL Server”程程序组中的序组中的“查询分析器查询分析器”选项，即可启动选项，即可启动SQL查询分析器。或在企业管理器中，单查询分析器。或在企业管理器中，单击击“工具工具”，选择，选择“SQL查询分析器查询分析器”选选项，也可启动项，也可启动SQL查询分析器。查询分析器。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系（2）在启动）在启动SQL查询分析器时，系统首先查询分析器时，系统首先打开打开“连接

11、到连接到SQL Server”对话框。使用对话框。使用该对话框可以指定连接到哪个该对话框可以指定连接到哪个SQL Server服务器。如图服务器。如图5-1所示。所示。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系图图5-1 “5-1 “连接到连接到SQL Server”SQL Server”对话框对话框 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系（3）单击）单击SQL Server文本框后面的按钮，文本框后面的按钮，可打开可打开“选择服务器选择服务器”对话框。可以在此对话框。可以在此对话框中选择要连接到的对话框中选择要连接到的SQL Server服务服务器

12、。如图器。如图5-2所示。所示。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系图图5-2 “5-2 “选择服务器选择服务器”对话框对话框河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系（4）在图）在图5-1所示的所示的“连接到连接到SQL Server”对话框中设置正确的验证模式，即可连接对话框中设置正确的验证模式，即可连接到到SQL Server服务器，并打开服务器，并打开SQL查询分查询分析器。析器。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系（5）进入）进入“SQL查询分析器查询分析器”界面，在工界面，在工具栏中，单击具栏中，单击 mast

13、er所在的下拉框，即可所在的下拉框，即可选择待查询的数据库，例如选择选择待查询的数据库，例如选择“学生信学生信息数据库息数据库”。（6）在）在SQL语句录入框中录入语句录入框中录入SQL语句，语句，然后单击工具栏中的按钮然后单击工具栏中的按钮“ ”，执行该，执行该“SELECT”查询语句，即可得到操作结查询语句，即可得到操作结果。果。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.2.2 查询语句格式查询语句格式1基本格式基本格式SELECT ALL | DISTICT ， ， FROM ， ， WHERE GROUP BY HAVING ORDER BY ASC | DES

14、C 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系2语句说明语句说明 SELECT语句的基本格式是由语句的基本格式是由SELECT子句、子句、FROM子句和子句和WHERE子句组成的子句组成的查询块。查询块。 整个整个SELECT语句的含义是：根据语句的含义是：根据WHERE子句的筛选条件表达式，从子句的筛选条件表达式，从FROM子句指定的表中找出满足条件记录，子句指定的表中找出满足条件记录，再按再按SELECT语句中指定的字段次序，筛语句中指定的字段次序，筛选出记录中的字段值构造一个显示结果表。选出记录中的字段值构造一个显示结果表。河南工程学院河南工程学院 计算机科学与工程系

15、计算机科学与工程系 如果有如果有GROUP子句，则将结果按子句，则将结果按的值进行分组，该值相等的记录为的值进行分组，该值相等的记录为一个组。一个组。 如果如果GROUP子句带子句带HAVING短语，则短语，则只有满足指定条件的组才会显示输出。只有满足指定条件的组才会显示输出。 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.1 查询表中指定的字段查询表中指定的字段 SELECT子句后的各个字段的先后顺子句后的各个字段的先后顺序可以与原表中的顺序不一致，但在结果序可以与原表中的顺序不一致，但在结果表中，字段是按照表中，字段是按照SELECT子句后的各个子句后的各个字段的顺

16、序显示。字段的顺序显示。SELECT student_name, student_sex, address FROM student_info河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系图图5-4 5-4 【例例5-15-1】的显示结果的显示结果河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.2 通配符通配符“*”的使用的使用 在在SELECT语句中，可以使用通配符语句中，可以使用通配符“*”显示所有的字段。显示所有的字段。SELECT * FROM student_info河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系图图5-5 5-5

17、 【例例5-25-2】的显示结果的显示结果河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.3 使用单引号加入字符串使用单引号加入字符串 在在SELECT语句中，可以在一个字段语句中，可以在一个字段的前面加上一个单引号字符串，对后面的的前面加上一个单引号字符串，对后面的字段起说明作用。字段起说明作用。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_name,家庭住址家庭住址, address FROM student_info河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系图图5-6 5-6 【例例5-35-3】的显

18、示结果的显示结果河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.4 使用别名使用别名 在显示结果时，可以指定以别名代替在显示结果时，可以指定以别名代替原来的字段名称，共有原来的字段名称，共有3种方法：种方法： 采用采用“字段名称字段名称 AS 别名别名”的格式；的格式； 采用采用“字段名称字段名称 别名别名”的格式；的格式； 采用采用“别名别名=字段名称字段名称”的格式。的格式。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_name AS 姓名姓名, address AS 家庭住址家庭住址 FROM student_info或

19、者或者SELECT student_name 姓名姓名, address 家庭家庭住址住址 FROM student_info或者或者SELECT 姓名姓名=student_name, 家庭住址家庭住址=address FROM student_info执行结果如图执行结果如图5-7所示。所示。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系图图5-7 5-7 【例例5-45-4】的显示结果的显示结果河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系要替换查询结果中的数据，则要使用查询中的要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：表达式，格式为：

20、CASE WHEN 条件条件1 THEN 表达式表达式1 WHEN 条件条件2 THEN 表达式表达式2 ELSE 表达式表达式END【例例4.8】查询查询XS表中计算机系各同学的学号、姓名和总学分，对其总学表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：分按以下规则进行替换：SELECT 学号学号, 姓名姓名, 等级等级= CASE WHEN 总学分总学分 IS NULL THEN 尚未选课尚未选课 WHEN 总学分总学分 =50 and 总学分总学分=52 THEN 合格合格 ELSE 优秀优秀ENDFROM XSWHERE 专业名专业名=计算机计算机河南工程学院河南

21、工程学院 计算机科学与工程系计算机科学与工程系该语句的执行结果如图该语句的执行结果如图4.4所示。所示。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.5 显示表达式的值显示表达式的值SELECT student_name, year(getdate()-year(born_date) as 年龄年龄 FROM student_info河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.6 使用使用DISTINCT短语消除重短语消除重复的记录复的记录 DISTINCT短语能够从结果表中去掉短语能够从结果表中去掉重复的记录。重复的记录。SELECT DIS

22、TINCT class_no FROM student_info河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.7 使用使用WHERE子句查询特定子句查询特定的记录的记录1关系表达式关系表达式 用关系运算符将两个表达式连接在一用关系运算符将两个表达式连接在一起的式子即为关系表达式，关系表达式的起的式子即为关系表达式，关系表达式的返回值为逻辑值（返回值为逻辑值（TRUE、 FALSE），关），关系表达式的格式为：系表达式的格式为： 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系在关系表达式字符型数据之间的比较是在关系表达式字符型数据之间的比较是对字符的对字符

23、的ASCII值进行比较。所有字符都值进行比较。所有字符都有一个有一个ASCII值与之对应。例如，字母值与之对应。例如，字母“A”、字母、字母“B”、字母、字母“C”，它们的，它们的ASCII值分别是值分别是65、66、67。字符串的比较是从左向右依次进行的。字符串的比较是从左向右依次进行的。在在SQL Server 2000中，日期字符串可以中，日期字符串可以按照按照“年年月月日日”的格式书写。的格式书写。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系运运 算算 符符 号号意意 义义= =等于等于 小于小于= 大于大于=大于或等于大于或等于!= != 或或 不等于不等于表表5-

24、1 5-1 关系运算符关系运算符河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系2逻辑表达式逻辑表达式 用逻辑运算符将两个表达式连接在一用逻辑运算符将两个表达式连接在一起的式子即为逻辑表达式，逻辑表达式的起的式子即为逻辑表达式，逻辑表达式的返回值为逻辑值（返回值为逻辑值（TRUE、FALSE），逻），逻辑表达式的格式为：辑表达式的格式为： 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系运运 算算 符符 号号意意 义义OROR或（或者）或（或者）ANDAND与（并且）与（并且）NOTNOT非（否）非（否）表表5-2 5-2 逻辑运算符逻辑运算符河南工程学院河南工程学

25、院 计算机科学与工程系计算机科学与工程系3特殊表达式特殊表达式运运 算算 符符 号号意意 义义% %通配符，包含零个或更多字符的任意字符串通配符，包含零个或更多字符的任意字符串_ _通配符，表示任意单个字符通配符，表示任意单个字符指定范围（指定范围（a-fa-f）或集合（）或集合（abcdefabcdef）中的任）中的任何单个字符何单个字符不属于指定范围（不属于指定范围（a-fa-f）或集合（）或集合（ abcdefabcdef ）中的任何单个字符，如：中的任何单个字符，如：a-fa-f或或abcdefabcdef BDTWEENANDBDTWEENAND定义一个区间范围定义一个区间范围IS

26、NULL IS NULL 测试字段值是否为空值测试字段值是否为空值LIKELIKE模式匹配，字符串匹配操作符模式匹配，字符串匹配操作符ININ检查一个字段值是否属于一组值之中检查一个字段值是否属于一组值之中EXISTSEXISTS检查某一个字段值是否有值，实际上，检查某一个字段值是否有值，实际上，EXISTSEXISTS是是IS NULLIS NULL的反义词的反义词表表5-3 5-3 特殊运算符特殊运算符河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系4有关关系运算符的使用有关关系运算符的使用SELECT student_name, address FROM student_i

27、nfo WHERE student_sex=女女SELECT * FROM student_info WHERE born_date 1980-12-31河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5有关逻辑运算符的使用有关逻辑运算符的使用SELECT student_name, tele_number, student_sex, address FROM student_info WHERE substring(address,1,3)=武汉市武汉市 AND student_sex=男男 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT stude

28、nt_name,student_sex,tele_number FROM student_info WHERE class_no=200001 OR SUBSTRING(tele_number,1,2)=87河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_name, student_sex, born_date FROM student_info WHERE not (year(getdate()-year(born_date)=23)或或SELECT student_name, student_sex, born_date FROM student

29、_info WHERE (year(getdate()-year(born_date) !=23河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系6有关特殊运算符的使用有关特殊运算符的使用使用使用“BETWEENAND”的作用是定的作用是定义表达式在两数之间。义表达式在两数之间。格式为：格式为：表达式表达式 NOT BETWEEN 表达式表达式1 AND 表达式表达式2河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT * FROM student_info WHERE (year(getdate()-year(born_date) BETWEEN 18 A

30、ND 20河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 当需要判断一个表达式的值是否为空当需要判断一个表达式的值是否为空值时，可使用关键字值时，可使用关键字“IS NULL”。 使用使用“IS NULL”短语的格式为：短语的格式为：表达式表达式 IS NOT NULL 当使用当使用NOT 关键字，表示不为空。关键字，表示不为空。 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_name, tele_number FROM student_info WHERE comment IS NULL河南工程学院河南工程学院 计算机科学与工程系

31、计算机科学与工程系 使用使用IN关键字可以指定一个值表，值关键字可以指定一个值表，值表中指定列出所有可能的值，当表达式与表中指定列出所有可能的值，当表达式与值表中的任意一个匹配时，即返回值表中的任意一个匹配时，即返回TRUE，否则返回否则返回FALSE。使用。使用IN关键字指定值表关键字指定值表的格式为：的格式为： 表达式表达式 IN （表达式（表达式1，n）河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_id, student_name, student_sex FROM student_info WHERE class_no IN (200001

32、,200002)或者或者SELECT student_id, student_name, student_sex FROM student_info WHERE class_no=200001 OR class_no=200002河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_id,student_name, student_sex FROM student_info WHERE class_no NOT IN (200001,200002)河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 LIKE关键字用于指出一个字符串是否关键字用于

33、指出一个字符串是否与指定的字符串相匹配，其运算对象可以与指定的字符串相匹配，其运算对象可以是是char、text、datetime和和smalldatetime等等类型的数据，返回逻辑值类型的数据，返回逻辑值TRUE或或FALSE。 LIKE关键字表达式的格式为：关键字表达式的格式为：string_expression1 NOT LIKE string_expression2 ESCAPE escape_character 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_id,student_nameFROM student_info WHERE s

34、tudent_name LIKE 刘刘_SELECT student_nameFROM student_info WHERE student_name LIKE 刘刘,王王%SELECT student_id,student_name FROM student_info WHERE tele_number LIKE _7%88河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系若要匹配的内容中含有通配符的字符（包括若要匹配的内容中含有通配符的字符（包括%，_，），可使），可使用关键字用关键字ESCAPE告诉系统其后的每个字符均作为实际匹配的字符，告诉系统其后的每个字符均作为实际匹配的

35、字符，而不再做为通配符。而不再做为通配符。例例19 查询查询DB_Design课程的课程号和学分。课程的课程号和学分。 SELECT Cno,Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE 例例20 查询以查询以DB_开头，且倒数第开头，且倒数第3个字符为个字符为 i的课程的详细情况。的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.8 使用使用ORDER BY子句对查询子句对查询结果排序

36、结果排序1“ORDER BY”子句的基本格式子句的基本格式基本格式：基本格式：SELECT FROM WHERE ORDER BY ASC|DESC，河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 “子句表达式子句表达式1”可以是一个列名、列可以是一个列名、列的别名、表达式或非零的整数值，而非零的别名、表达式或非零的整数值，而非零的整数值则表示字段、别名或表达式在选的整数值则表示字段、别名或表达式在选择列表中的位置。择列表中的位置。ASC表示升序，为默认表示升序，为默认值；值；DESC表示降序，排序时空值（表示降序，排序时空值（NULL）被认为是最小值。被认为是最小值。河南工程

37、学院河南工程学院 计算机科学与工程系计算机科学与工程系2“ORDER BY”子句应用实例子句应用实例SELECT student_id,student_name, born_date FROM student_info ORDER BY born_date河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_id,student_name, ru_date FROM student_info WHERE ru_date 19 AND student_sex=女女河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系2MAX和和MIN函数的应用函数的

38、应用 MAX和和MIN函数分别用于求表达式中函数分别用于求表达式中所有值项的最大值与最小值，语句格式为：所有值项的最大值与最小值，语句格式为： MAX / MIN （ALL|DISTINCT 表达式）表达式）河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 其中表达式是常量、列、函数或表达其中表达式是常量、列、函数或表达式，其数据类型可以是数字、字符和时间式，其数据类型可以是数字、字符和时间日期类型。日期类型。ALL表示对所有值进行运算，表示对所有值进行运算，DISTINCT表示去除重复值，默认为表示去除重复值，默认为ALL。MAX/MIN忽略忽略NULL值。值。河南工程学院河南

39、工程学院 计算机科学与工程系计算机科学与工程系SELECT MIN (born_date) 年龄最大年龄最大 , MAX (born_date) 年龄最小年龄最小 FROM student_infoSELECT MAX (result) 最高分最高分 FROM result_info WHERE student_id=20000101河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系3AVG函数的应用函数的应用 AVG函数的表达式格式与函数的表达式格式与MAX和和MIN的表达式格式一样。求表达式中所有的表达式格式一样。求表达式中所有项的平均值。其格式为：项的平均值。其格式为： AV

40、G（ALL|DISTINCT 表达式）表达式）河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 其中表达式是常量、列、函数或表达式，其中表达式是常量、列、函数或表达式，其数据类型只能是其数据类型只能是int，smallint，tinyint，bigint，decimal，numeric，float，real，money和和smallmoney。 ALL表示对所有值进行运算，表示对所有值进行运算，DISTINCT表示去除重复值，默认为表示去除重复值，默认为ALL。AVG忽略忽略NULL值。值。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT AVG (co

41、urse_time) 平均课时平均课时, AVG (course_score) 平均学分平均学分 FROM course_info WHERE course_start =1河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系4SUM函数的应用函数的应用 SUM函数用于求表达式中所有项的总函数用于求表达式中所有项的总和。其格式为：和。其格式为： SUM（ALL|DISTINCT 表达式）表达式）河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 其中表达式是常量、列、函数或表达其中表达式是常量、列、函数或表达式，其数据类型只能是式，其数据类型只能是int、smallint

42、、tinyint、bigint、decimal，numeric，float、real，money和和smallmoney。 ALL表示对所有值进行运算，表示对所有值进行运算，DISTINCT表示去除重复值，默认为表示去除重复值，默认为ALL。SUM忽略忽略NULL值。值。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT SUM (course_time) 总课时总课时, SUM (course_score) 总学分总学分 FROM course_info WHERE course_start =1河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.1

43、0 使用使用GROUP BY子句对查子句对查询结果进行分组询结果进行分组 利用利用SQL的的GROUP BY子句，能够快子句，能够快速而简便地将查询结果表按照指定的字段速而简便地将查询结果表按照指定的字段进行分组，值相等的记录分为一组。进行分组，值相等的记录分为一组。 GROUP BY子句一般和子句一般和SQL的聚合函的聚合函数一起使用。数一起使用。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT FROM GROUP BY SELECT class_no,COUNT( student_id) 学学生人数生人数 FROM student_info GROUP BY c

44、lass_no河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT COUNT(student_id) 总人数总人数, AVG(year(getdate()-year(born_date) 平平均年龄均年龄 FROM student_info GROUP BY student_sex河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.3.11 使用使用HAVING子句筛选结子句筛选结果表果表 在实际使用中，往往还要对分组后的在实际使用中，往往还要对分组后的结果按某种条件再进行筛选，而只输出满结果按某种条件再进行筛选，而只输出满足用户指定条件的记录。在足用户指

45、定条件的记录。在SQL中，中，HAVING子句能完成此功能。子句能完成此功能。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 “WHERE”子句与子句与“HAVING”子子句的区别在于作用对象不同：句的区别在于作用对象不同： WHERE子句的作用对象是表，是从表子句的作用对象是表，是从表中选择出满足筛选条件的记录。中选择出满足筛选条件的记录。 HAVING子句的作用对象是组，是从组子句的作用对象是组，是从组中选择出满足筛选条件的记录。中选择出满足筛选条件的记录。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT class_no 班级名班级名,COUNT

46、(student_id) 总人数总人数 FROM student_info GROUP BY class_no HAVING COUNT(student_id)2河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系COMPUTE子句子句COMPUTE子句用于分类汇总，格式为：子句用于分类汇总，格式为： COMPUTE 聚合函数名（聚合函数名（expression） ,n BY expression ,n 其中聚合函数名见表其中聚合函数名见表4.10，expression是是列名。列名。COMPUTE将产生额外的汇总行。将产生额外的汇总行。河南工程学院河南工程学院 计算机科学与工程系计

47、算机科学与工程系COMPUTE子句子句【例例4.51】查找通信工程专业学生的学号、姓名、出生查找通信工程专业学生的学号、姓名、出生时间，并产生一个学生总人数行。时间，并产生一个学生总人数行。SELECT 学号学号,姓名姓名, 出生时间出生时间 FROM XS WHERE 专业名专业名 = 通信工程通信工程 COMPUTE COUNT(学号学号) 【例例4.54】将学生按专业名排序，并汇总各专业人数和将学生按专业名排序，并汇总各专业人数和平均学分。平均学分。SELECT 学号学号,姓名姓名,出生时间出生时间,总学分总学分 FROM XS ORDER BY 专业名专业名 COMPUTE COUNT

48、(学号学号),AVG(总学分总学分) BY 专业名专业名河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系COMPUTE子句子句例例4.51执行结果如图。执行结果如图。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 在关系型数据库中，将一个查询同时在关系型数据库中，将一个查询同时涉及两个或两个以上的表，称为连接查询。涉及两个或两个以上的表，称为连接查询。 基于多表的查询是通过所谓的连接查基于多表的查询是通过所谓的连接查询来完成的。连接是根据各个表之间的逻询来完成的。连接是根据各个表之间的逻辑关系从两个或多个表中查询数据，结果辑关系从两个或多个表中查询数据，结果通常是

49、含有参加连接运算的两个表（或多通常是含有参加连接运算的两个表（或多个表）的指定列的表。个表）的指定列的表。 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 在在T_SQL中，连接查询有两大类表示中，连接查询有两大类表示形式，一是符合形式，一是符合SQL标准连接谓词表示形标准连接谓词表示形式，二是式，二是T_SQL扩展使用关键字扩展使用关键字JOIN的表的表示形式。示形式。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 指定每个表中要用于连接的列。典型的指定每个表中要用于连接的列。典型的连接条件在一个表中指定外键，在另一个连接条件在一个表中指定外键，在另一个表中指

50、定与其关联的键。表中指定与其关联的键。 指定比较各列的值时要使用的逻辑运算指定比较各列的值时要使用的逻辑运算符，如符，如“=、”等。等。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.4.1 连接谓词连接谓词 可以在可以在SELECT语句的语句的WHERE子句子句中使用比较运算符，给出连接条件对表进中使用比较运算符，给出连接条件对表进行连接，对这种表示形式称为连接谓词表行连接，对这种表示形式称为连接谓词表示形式。其基本格式为：示形式。其基本格式为： 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 连接谓词中的比较符可以是连接谓词中的比较符可以是、=、!=、等。

51、当比较符为等。当比较符为“=”时，时，就是等值连接；若在等值连接中去除结果就是等值连接；若在等值连接中去除结果表中相同的字段名，则为自然连接；若有表中相同的字段名，则为自然连接；若有多个连接条件，则为复合条件连接。若一多个连接条件，则为复合条件连接。若一个表与自身进行连接，称为自连接。个表与自身进行连接，称为自连接。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_info.*, class_info.* FROM student_info, class_infoWHERE student_info.class_no= class_info.class

52、_no河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_id, student_name,student_sex,born_date, student_info.class_no, tele_number,ru_date,address, comment,class_name,director,profession FROM student_info, class_info WHERE student_info.class_no= class_info.class_no河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 若要在一个表中查找具

53、有相同列值的若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均为表指定两个别名，且对所有列的引用均要用别名限定。要用别名限定。 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT a.student_id, a.course_no, b.course_no, a.result FROM result_info a, result_info b WHERE a.result=b.result AND a.student_id=b.student_id AND a.course_nob

54、.course_no河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 本例主要学习有关复合条件连接的表本例主要学习有关复合条件连接的表达式书写格式。在达式书写格式。在“WHERE”子句中，子句中，若有多个连接条件，则称为复合条件连接。若有多个连接条件，则称为复合条件连接。在多表操作中，复合条件连接的使用最为在多表操作中，复合条件连接的使用最为广泛。广泛。 书写书写“WHERE”子句中的条件表达子句中的条件表达式一般先写自然连接的条件表达式，然后式一般先写自然连接的条件表达式，然后通过逻辑运算符，再写出其他的附加限定通过逻辑运算符，再写出其他的附加限定条件。条件。河南工程学院河南工

55、程学院 计算机科学与工程系计算机科学与工程系SELECT student_info.student_id,student_name, course_info.course_name,result_info.resultFROM student_info,course_info,result_infoWHERE student_info.student_id= result_info.student_id AND result_info.course_no=course_info.course_no AND course_info.course_name=汇编语言汇编语言 AND result_

56、info.result70河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系5.4.2 以以JOIN关键字指定的连接关键字指定的连接 T-SQL扩展了以扩展了以JOIN关键字连接的表关键字连接的表示方式，使表的连接运算能力有了增强，示方式，使表的连接运算能力有了增强，可以将多个表连接起来，可以将多个表连接起来，FROM关键字后关键字后面的连接格式为：面的连接格式为： ON | CROSS JOIN 河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 其中其中first_table，second_table为需连为需连接的表，接的表，join_type表示连接类型，表示连

57、接类型，ON用于用于指定连接条件。指定连接条件。 join_type的格式为：的格式为： INNER| LEFT | RIGHT | FULL OUTER JOIN河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系 其中其中INNER表示内连接，表示内连接，OUTER表表示外连接，示外连接，join_hint是连接提示。是连接提示。JOIN表表示交叉连接。因此，以示交叉连接。因此，以JOIN关键字指定的关键字指定的连接有三种类型。连接有三种类型。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系1内连接内连接 内连接按照内连接按照ON所指定的连接条件合并所指定的连接条件

58、合并两个表，返回满足条件的行。两个表，返回满足条件的行。SELECT * FROM student_info INNER JOIN class_info ON student_info.class_no= class_info.class_no河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_info.*, class_name, director, professionFROM student_info INNER JOIN class_info ON student_info.class_no= class_info.class_no河南工程学院河

59、南工程学院 计算机科学与工程系计算机科学与工程系 内连接是系统默认的，可以省略内连接是系统默认的，可以省略INNER关键字，使用内连接后仍可使用关键字，使用内连接后仍可使用WHERE子句指定条件。子句指定条件。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_name,result FROM student_info JOIN result_info ON student_info.student_id= result_info.student_id WHERE result_info.course_no=2 AND result_info.resul

60、t60 内连接还可用于多个表的连接。内连接还可用于多个表的连接。河南工程学院河南工程学院 计算机科学与工程系计算机科学与工程系SELECT student_info.student_id,student_name, course_info.course_name,result_info.resultFROM student_info JOIN result_info ON student_info.student_id= result_info.student_idJOIN course_info ON result_info.course_no=course_info.course_no W