[理学]SQL第4章数据操纵语句.ppt

1、1 4.1 在在SQL Server Management Studio中查看记录中查看记录 4.2 INSERT语句语句 4.3 UPDATE语句语句 4.4 DELETE语句语句 4.5 简单查询简单查询 4.6 数据统计查询数据统计查询 2 4.1.1 打开表打开表 4.1.2 快速定位记录快速定位记录 4.1.3 返回前几条记录返回前几条记录 3 例：在例：在SQL server Management Studio中打开中打开 “Northwind” 数据数据 库中的库中的“products”表。表。 1.启动启动【SQL server Management Studio】，在【对象资

2、源管理器】，在【对象资源管理器】 中展开树形目录，定位到中展开树形目录，定位到【products】表。】表。 2. 右击右击【products】表，在弹出的快捷菜单中选择【打开表】选项。】表，在弹出的快捷菜单中选择【打开表】选项。 3. 此时已经将此时已经将【products】表打开了，右边对话框显示的是】表打开了，右边对话框显示的是 【products】表里的记录内容，该表格对话框名为【结果窗格】。】表里的记录内容，该表格对话框名为【结果窗格】。 4 在【结果窗格】的最下方，可以快速定位到上一条、在【结果窗格】的最下方，可以快速定位到上一条、 下一条记录，也可以直接跳到第一条、最后一条记录。

3、如下一条记录，也可以直接跳到第一条、最后一条记录。如 果要想跳到某一条记录的话，在文本框内输入数字，再按果要想跳到某一条记录的话，在文本框内输入数字，再按 回车键。回车键。 5 1.在打开表之后，调出【属性】对话框。调出方法为：在打开表之后，调出【属性】对话框。调出方法为： 单击菜单栏中的【视图】单击菜单栏中的【视图】|【属性窗口】选项。在【属性】对【属性窗口】选项。在【属性】对 话框里，展开话框里，展开【TOP规范】选项，在【（最前面）】下拉列表规范】选项，在【（最前面）】下拉列表 框里选择【是】选项，在框里选择【是】选项，在【percent】下拉列表框里选择【否】下拉列表框里选择【否】 选

4、项，在【表达式】文本框里输入数字选项，在【表达式】文本框里输入数字“10”。 2.选择【结果窗格】，然后再单击【运行选择【结果窗格】，然后再单击【运行SQL】按钮。在】按钮。在 【结果窗格】里只显示前十条记录。【结果窗格】里只显示前十条记录。 6 4.1 在在SQL Server Management Studio中查看记录中查看记录 4.2 INSERT语句语句 4.3 UPDATE语句语句 4.4 DELETE语句语句 4.5 简单查询简单查询 4.6 数据统计查询数据统计查询 7 4.2.1 在在SSMS中插入记录中插入记录 4.2.2 用用INSERT语句记录语句记录 8 注意事项：注

5、意事项： 1.标识列、计算列的字段不能输入字段内容。标识列、计算列的字段不能输入字段内容。 2.不能为不能为NULL值的字段必须输入字段内容。值的字段必须输入字段内容。 3.输入字段内容的数据类型要和字段定义的数据类型一输入字段内容的数据类型要和字段定义的数据类型一 致。致。 4.如果字段上有约束，一定要符合约束要求。如果字段上有约束，一定要符合约束要求。 5.如果要让字段输入默认值，则不用在字段内输入任何如果要让字段输入默认值，则不用在字段内输入任何 数据，在保存记录时，数据，在保存记录时，SQL server会自动填入默认值，但会自动填入默认值，但 前提是该字段设置有默认值。前提是该字段设

6、置有默认值。 9 语法格式如下：语法格式如下： INSERT INTO table_name ( column_list ) VALUES ( expression ,.n ) 10 语法注释：语法注释： INTO：一个可选的关键字，可以将它用在：一个可选的关键字，可以将它用在 INSERT 和目标表和目标表 之间。之间。 table_name：将要接收数据的表或：将要接收数据的表或 table 变量的名称。变量的名称。 (column_list)：要在其中插入数据的一列或多列的列表。：要在其中插入数据的一列或多列的列表。必须用必须用 圆括号将圆括号将column_list括起来，并且用逗号进

7、行分隔括起来，并且用逗号进行分隔。 VALUES：引入要插入的数据值的列表。对于：引入要插入的数据值的列表。对于column_list（如果（如果 已指定）中或者表中的每个列，都必须有一个数据值。已指定）中或者表中的每个列，都必须有一个数据值。必须用圆括必须用圆括 号将值列表括起来号将值列表括起来。如果。如果 VALUES 列表中的值与表中列的顺序不列表中的值与表中列的顺序不 相同，或者未包含表中所有列的值，那么必须使用相同，或者未包含表中所有列的值，那么必须使用column_list明确明确 地指定存储每个传入值的列。地指定存储每个传入值的列。 11 1.最简单的最简单的INSERT 语句语

8、句 示范案例示范案例1 在在“Northwind”数据库的数据库的“Order Details”表里插入表里插入 一 条 记 录 ， 其 中一 条 记 录 ， 其 中 “ OrderID”字 段 的 值 为字 段 的 值 为 10248； “ProductID”字段的值为字段的值为1；“UnitPrice”字段的值为字段的值为10； “Quantity”字段的值为字段的值为2；“Discount”字段的值为字段的值为0.8 。 程序清单如下：程序清单如下： INSERT order details (orderid,productid,unitprice,quantity,discount)

9、VALUES(10248,1,10,2,0.8) 12 2.省略清单的省略清单的INSERT语句语句 示范案例示范案例2 在上例中省略字段清单，程序清单如下：在上例中省略字段清单，程序清单如下： INSERT order details VALUES(10249,2,10,2,0.9) 13 3.标识列、允许为空字段的处理标识列、允许为空字段的处理 示范案例示范案例3 在在“Northwind”数据库的数据库的“Categories”表里插入一条记录，表里插入一条记录， 其中其中“CategoryName”字段的值为字段的值为“图书图书”；“Description”字字 段的值为段的值为“所有

10、类型的图书所有类型的图书” 。 程序清单如下：程序清单如下： INSERT INTO categories(categoryname,description) VALUES(图书图书,所有类型的图书所有类型的图书) 14 4.有默认值字段的处理有默认值字段的处理 示范案例示范案例4 在在“学生学生”数据库中创建数据库中创建“教师教师”表（性别为有默认表（性别为有默认 值列）值列） 。在该表中录入数据。在该表中录入数据。 程序清单如下：程序清单如下： INSERT INTO teachers(teach_id,teach_name,tsex,tdegree) VALUES(rj001, 李波李波

11、,DEFAULT, 本科本科) 15 5.省略省略VALUES清单的清单的INSERT语句语句 在在T-SQL中，有一种简单的插入多行的方法。这种中，有一种简单的插入多行的方法。这种 方法是使用方法是使用SELECT语句查询出的结果代替语句查询出的结果代替VALUES子句。子句。 这种方法的语法结构如下：这种方法的语法结构如下： INSERT INTO table_name (column_name1,column_name2,column_namen) SELECT column_name ,n FROM table_name WHERE search_conditions 16 示范案例示

12、范案例5 在在“Northwind”数据库中，从数据库中，从“Employees”表里表里 查出查出“EmployeeID”、“LastName”、“FirstName”、 “ P o s t a l C o d e ” 、 “ C i t y ” 、 “ A d d r e s s ” 、 “HomePhone”7个字段，组成一个个字段，组成一个“雇员通信录雇员通信录”表。表。 17 程序清单如下：程序清单如下： CREATE TABLE 雇员通信录雇员通信录 (雇员雇员ID int primary key, 姓氏姓氏 nvarchar(20) not null, 名字名字 nvarchar

13、(10) not null, 邮政编码邮政编码 nvarchar(10) null, 城市城市 nvarchar(15) null, 地址地址 nvarchar(60) null, 家庭电话家庭电话 nvarchar(24) null) GO INSERT INTO 雇员通信录雇员通信录 SELECT employeeid,lastname,firstname,postalcode,city, address,homephone FROM employees GO 18 4.1 在在SQL Server Management Studio中查看记录中查看记录 4.2 INSERT语句语句 4.

14、3 UPDATE语句语句 4.4 DELETE语句语句 4.5 简单查询简单查询 4.6 数据统计查询数据统计查询 19 4.3.1 在在SSMS中更新记录中更新记录 4.3.2 用用UPDATE语句更新记录语句更新记录 20 注意事项：注意事项： 1.在修改在修改char和和nchar这类长度固定的字段时，要注意这类长度固定的字段时，要注意 其后的空格其后的空格 。 2.在可以为空值在可以为空值NULL的字段中，如果要设为的字段中，如果要设为NULL， 可以按可以按Ctrl+0键键 。 3.如果将记录字段内容修改过后，又想回到修改前的值，如果将记录字段内容修改过后，又想回到修改前的值， 可用

15、将光标聚焦到该字段，然后按可用将光标聚焦到该字段，然后按Esc键。键。 4.如果想放弃整条记录的修改，可以连按两次如果想放弃整条记录的修改，可以连按两次Esc键。键。 21 语法格式如下：语法格式如下： UPDATE table_name SET column_name = expression | DEFAULT | NULL ,.n FROM ,.n WHERE := table_name AS table_alias WITH ( ,.n ) 22 语法注释：语法注释： table_name：需要更新的表的名称。：需要更新的表的名称。 SET：指定要更新的列或变量名称的列表。：指定要更新

16、的列或变量名称的列表。 column_name：含有要更改数据的列的名称。：含有要更改数据的列的名称。 expression | DEFAULT | NULL ：列值表达式。：列值表达式。 ：修改数据来源表。：修改数据来源表。 23 示范案例示范案例6 使用使用T-SQL语句，将语句，将“Northwind”数据库数据库 “Employees”表中表中“EmployeeID”字段值为字段值为1的记录的的记录的 “LastName”字段值修改为字段值修改为“bb” 。 程序清单如下：程序清单如下： UPDATE employees SET lastname=bb WHERE employeeid

17、=1 24 示范案例示范案例7 用计算值来更新记录，修改用计算值来更新记录，修改“Northwind”数据库数据库 “Products”表中表中“ProductID”字段值为字段值为1的记录的的记录的 “UnitPrice”字段的值，使其值在当前的基础上减少字段的值，使其值在当前的基础上减少5 。 程序清单如下：程序清单如下： UPDATE products SET unitprice=unitprice-5 WHERE productid=1 25 4.1 在在SQL Server Management Studio中查看记录中查看记录 4.2 INSERT语句语句 4.3 UPDATE语句

18、语句 4.4 DELETE语句语句 4.5 简单查询简单查询 4.6 数据统计查询数据统计查询 26 4.4.1 在在SSMS中删除记录中删除记录 4.4.2 用用DELETE语句删除记录语句删除记录 4.4.3 用用Truncate table语句删除记录语句删除记录 27 注意事项：注意事项： 1.记录删除之后不能再撤消删除记录删除之后不能再撤消删除 。 2.一次可以删除多条记录一次可以删除多条记录 。 3.在选择记录后，按在选择记录后，按Delete键也可以进行删除操作键也可以进行删除操作 。 4.如果要删除的记录是其他表的外键字段，删除操作可如果要删除的记录是其他表的外键字段，删除操作

19、可 能会影响外键表能会影响外键表 。 28 语法格式如下：语法格式如下： DELETE table_name FROM ,.n WHERE := table_name AS table_alias ,.n ) 29 语法注释：语法注释： table_name：是要从其中删除行的表的名称。：是要从其中删除行的表的名称。 FROM ：指定附加的：指定附加的 FROM 子句。子句。 table_name AS table_alias ：是为删除操作提供标准的表名。：是为删除操作提供标准的表名。 WHERE：指定用于限制删除行数的条件。：指定用于限制删除行数的条件。如果没有提供如果没有提供 WHERE

20、 子句，则子句，则 DELETE 删除表中的所有行。删除表中的所有行。 ：指定删除行的限定条件。对搜索条件中：指定删除行的限定条件。对搜索条件中 可以包含的谓词数量没有限制。可以包含的谓词数量没有限制。 30 示范案例示范案例8 将将“Northwind”数据库数据库“Order Details”表中表中 “OrderID”字段值为字段值为10251的记录删除。的记录删除。 程序清单如下：程序清单如下： DELETE order details WHERE orderid=10251 31 示范案例示范案例9 在在“Northwind”数据库中删除雇员数据库中删除雇员“Nancy”的所有订的所

21、有订 单。在该例中删除的数据引用的是雇员表中的数据。单。在该例中删除的数据引用的是雇员表中的数据。 程序清单如下：程序清单如下： DELETE orders FROM employees WHERE employees.employeeid=orders.employeeid AND employees.firstname=Nancy 32 基本语法格式基本语法格式: TRUNCATE TABLE table_name 功能功能:删除表中的所有记录删除表中的所有记录 语法注释：语法注释： TRUNCATE TABLE：为关键字：为关键字 table_name：为要删除所用记录的表名。：为要删除

22、所用记录的表名。 33 TRUNCATE与与DELETE比较：比较： 比比DELETE快快,不记录日志的操作不记录日志的操作,它将释放表的数它将释放表的数 据和索引所占据的所有空间及所有为全部索引分配的页据和索引所占据的所有空间及所有为全部索引分配的页,删删 除的数据是不可恢复的。除的数据是不可恢复的。 34 示范案例示范案例10 删除学生信息管理数据库删除学生信息管理数据库student中教师表中教师表teachers中中 的所有记录的所有记录 。 程序清单如下：程序清单如下： DELETE teachers 或或 TRUNCATE TABLE teachers 35 4.1 在在SQL S

23、erver Management Studio中查看记录中查看记录 4.2 INSERT语句语句 4.3 UPDATE语句语句 4.4 DELETE语句语句 4.5 简单查询简单查询 4.6 数据统计查询数据统计查询 36 4.5.1 选择表中的若干列选择表中的若干列 4.5.2 选择表中的若干记录选择表中的若干记录 4.5.3 对查询的结果排序对查询的结果排序 37 基本语法格式：基本语法格式： SELECT DISTINCT|TOP n PERCENT WITH TIES select_list FROM table_list WHERE search_conditions ORDER B

24、Y order_list ASC | DESC 38 注释说明：注释说明： SELECT子句子句用于指定所选择的要查询的特定表中的列，它可以用于指定所选择的要查询的特定表中的列，它可以 是星号（是星号（*）、表达式、列表、变量等。）、表达式、列表、变量等。 FROM子句子句用于指定要查询的表或者视图，最多可以指定用于指定要查询的表或者视图，最多可以指定16个表个表 或者视图，用逗号相互隔开。或者视图，用逗号相互隔开。 WHERE子句子句 用来限定查询的范围和条件。用来限定查询的范围和条件。 ORDER BY 用于指定排序方式。用于指定排序方式。 DISTINCT 用于消除结果集中的重复值。用于

25、消除结果集中的重复值。 TOP n PERCENT 用于显示前用于显示前n条或条或n%条的记录信息条的记录信息. WITH TIES 用于在结果集中包含附加记录用于在结果集中包含附加记录,在使用在使用ORDER BY子子 句时句时,当出现两个或多个记录和最后一条记录的值相等时当出现两个或多个记录和最后一条记录的值相等时,这些附加记这些附加记 录也将出现在结果集中。录也将出现在结果集中。 39 1.查询表查询表中的所有列中的所有列 示范案例示范案例11 查询查询“Northwind”数据库中数据库中Categories表中的所有记录表中的所有记录 。 程序清单如下：程序清单如下： USE nor

26、thwind GO SELECT * FROM categories GO 40 2.查询表中部分列查询表中部分列 示范案例示范案例12 查询查询“Northwind”数据库中数据库中“Categories”表中的表中的 “categoryname”和和“description”字段字段 。 程序清单如下：程序清单如下： USE northwind GO SELECT categoryname，Description FROM categories GO 41 3.为结果集内的列指定别名为结果集内的列指定别名 格式：表达式格式：表达式AS别名别名 由于计算列不是数据表中的列，所以用到了由于计算

27、列不是数据表中的列，所以用到了“AS”关关 键字来指定列的别名。即使要查询的列是数据表中真实存键字来指定列的别名。即使要查询的列是数据表中真实存 在的列，也可以用在的列，也可以用“AS”来指定别名。关键字来指定别名。关键字AS可以省略。可以省略。 42 示范案例示范案例13 查询查询“Northwind”数据库中数据库中“Order Details”表中每个订单的表中每个订单的 总价总价 。 程序清单如下：程序清单如下： USE northwind GO SELECT orderid,productid,unitprice,quantity,discount, unitprice*(1-dis

28、count)*quantity AS zongjia FROM order details GO 43 1.1.查看不重复记录查看不重复记录DISTINCT 例如，在例如，在“Northwind”数据库中要查看货物在哪些城数据库中要查看货物在哪些城 市里有过销售记录。在订购表市里有过销售记录。在订购表“Orders”中有一个字段是中有一个字段是 “ShipCity”，通过查询这个字段，就可以了解货物曾经销，通过查询这个字段，就可以了解货物曾经销 往过哪些城市。往过哪些城市。 如果程序代码如下：如果程序代码如下： SELECT shipcity FROM orders 或或 SELECT ALL

29、 shipcity FROM orders 44 如果要显示不重复的记录的话，可以使用如下代码如果要显示不重复的记录的话，可以使用如下代码 ： SELECT DISTINCT shipcity FROM orders Distinct关键字可以同时指定多个字段，例如同时指定关键字可以同时指定多个字段，例如同时指定 两个字段的话，那么查询的结果是两个字段同时不重复的两个字段的话，那么查询的结果是两个字段同时不重复的 记录，如以下代码：记录，如以下代码： SELECT DISTINCT shipname,shipcity FROM orders 45 示范案例示范案例14 在在“northwind

30、”数据库中数据库中“suppliers”表中检索所有行，表中检索所有行， 但每个国家但每个国家“Country”只显示一次。只显示一次。 程序清单如下：程序清单如下： USE northwind GO SELECT DISTINCT country FROM suppliers GO 46 2.2.查看满足条件记录查看满足条件记录WHERE 如果只希望得到表中满足特定条件的一些记录，用户如果只希望得到表中满足特定条件的一些记录，用户 可以在查询语句中使用可以在查询语句中使用WHERE子句。子句。 47 常用的查询条件常用的查询条件 ： 查询条件查询条件运算符运算符意义意义 比较比较 =,=-,

31、=,!=,!,!; NOT+上述运算符上述运算符 比较大小比较大小 确定范围确定范围 BETWEEN AND , NOT BETWEEN AND 判断值是否在范围内判断值是否在范围内 确定集合确定集合IN, NOT IN判断值是否为列表中的值判断值是否为列表中的值 字符匹配字符匹配LIKE, NOT LIKE判断值是否与指定的字符通配格式相符判断值是否与指定的字符通配格式相符 空值空值IS NULL, IS NOT NULL判断值是否为空判断值是否为空 多重条件多重条件AND , OR ,NOT用于多重条件判断用于多重条件判断 48 （1）比较大小）比较大小 查询查询“Northwind”数据

32、库中数据库中“Products”表中库存表中库存 量量unitinstock不为零的产品不为零的产品 。 程序清单如下：程序清单如下： USE northwind GO SELECT * FROM products WHERE unitsinstock0 GO 49 （2）确定范围）确定范围 示范案例示范案例16 在在“Northwind”数据库中数据库中“products”表中检索表中检索 “unitprice”在在$10.00到到$20.00之间的所有产品的产品名称之间的所有产品的产品名称 和单价和单价 。 程序清单如下：程序清单如下： SELECT * FROM products WHE

33、RE unitprice BETWEEN 10 AND 20 GO 50 （3）确定集合）确定集合IN 示范案例示范案例17 在在“northwind”数据库中数据库中“suppliers”表中检索坐落在表中检索坐落在 日本或意大利的公司列表日本或意大利的公司列表 。 程序清单如下：程序清单如下： SELECT * FROM suppliers WHERE country IN(Japan,Italy) 51 （4）模糊查询）模糊查询通配符：通配符： 语法格式：语法格式： not like ESCAPE %： 代表任意长度的字符串（长度可为代表任意长度的字符串（长度可为0）的字符串。）的字符串

34、。 _: 代表任意单个字符。代表任意单个字符。 ：表示和方括号中列出的任意一个字符相匹配。：表示和方括号中列出的任意一个字符相匹配。 : 表示不在方括号中列出的任意一个字符。表示不在方括号中列出的任意一个字符。 52 例： acf-htionaction,aftion,agtion,ahtion 0-5ch 0ch,1ch,2ch,3ch,4ch,5ch Abplc 53 字符匹配字符匹配 示范案例示范案例18 在在“northwind”数据库数据库“customers”表中检索公司名表中检索公司名 称中包含字符串称中包含字符串“restaurant”公司名称公司名称 。 程序清单如下：程序清

35、单如下： SELECT * FROM customers WHERE companyname LIKE %restaurant% 54 转义字符的使用：转义字符的使用： 示范案例示范案例19 在在“northwind”数据库的类别数据库的类别“categories”表查询表查询 类别名称类别名称categoryName中包含下划线中包含下划线_的所有的类别记的所有的类别记 录信息录信息 。 程序清单如下：程序清单如下： SELECT * FROM categories WHERE categoryname LIKE %_% 55 程序清单如下：程序清单如下： SELECT * FROM cat

36、egories WHERE categoryname LIKE %#_% ESCAPE # 这里的这里的#号是程序员自行定义的转义字符，并在语句号是程序员自行定义的转义字符，并在语句 中使用中使用ESCAPE关键字进行说明，可将其替换为其他的符关键字进行说明，可将其替换为其他的符 号，如号，如/、$等等等等 。 56 （5）涉及空值（非空值）查询）涉及空值（非空值）查询 列表达式列表达式 is not null 示范案例示范案例20 在在“northwind”数据库数据库“Customers”表中查看表中查看 区域为空值的顾客信息区域为空值的顾客信息 。 程序清单如下：程序清单如下： SELE

37、CT * FROM customers WHERE region IS NULL 57 （6）多重条件查询）多重条件查询 示范案例示范案例21 在在“northwind”数据库数据库“products”表中检索产品名称表中检索产品名称 “productname”以字母以字母T开头或产品标识为开头或产品标识为46,并且单价大并且单价大 于于$16.00 。 程序清单如下：程序清单如下： SELECT * FROM products WHERE (productname LIKE t% OR productid=46) AND unitprice 16 58 3.3.限制返回行数限制返回行数TOP

38、 TOP n PERCENT WITH TIES 示范案例示范案例22 在在“Northwind”数据库数据库“order details”订单明细表中订单明细表中 查看订购数量最多的五条订单查看订购数量最多的五条订单 。 程序清单如下：程序清单如下： SELECT TOP 5 * FROM order details ORDER BY quantity DESC 59 用户可以使用用户可以使用ORDER BY子句对查询结果按照一个子句对查询结果按照一个 或多个属性列的升序（或多个属性列的升序（ASC）或降序（）或降序（DESC）排列，默）排列，默 认为升序。如果不使用认为升序。如果不使用OR

39、DER BY子句，则结果集按照记子句，则结果集按照记 录在表中的顺序排列。录在表中的顺序排列。 基本语法格式：基本语法格式： ORDER BY 列名列名，列名，列名 DESC|ASC 60 示范案例示范案例23 在在“Northwind”数据库数据库“products”产品表中查看产品表中查看 所有产品记录，并以产品名排序。所有产品记录，并以产品名排序。 程序清单如下：程序清单如下： SELECT * FROM products ORDER BY productname 61 当按多列排序时，先按前面的列排序，如果值相同再当按多列排序时，先按前面的列排序，如果值相同再 按后面的列排序。按后面的

40、列排序。 示范案例示范案例24 在在“Northwind”数据库数据库“products”产品表中查看所产品表中查看所 有产品记录，并以供应商编号和产品名排序有产品记录，并以供应商编号和产品名排序 。 程序清单如下：程序清单如下： SELECT * FROM products ORDER BY supplierid,productname 62 4.1 在在SQL Server Management Studio中查看记录中查看记录 4.2 INSERT语句语句 4.3 UPDATE语句语句 4.4 DELETE语句语句 4.5 简单查询简单查询 4.6 数据统计查询数据统计查询 63 4.6

41、.1 使用聚合函数使用聚合函数 4.6.2 对结果进行分组对结果进行分组 4.6.3 用用COMPUTE子句来归类子句来归类 64 SQL Server提供了许多聚合函数，主要有：提供了许多聚合函数，主要有： AVG（列名）（列名）计算给定列的平均值计算给定列的平均值 COUNT (列名列名)计算表达式中值的数目计算表达式中值的数目 COUNT (*)计算所选择的行的数目计算所选择的行的数目 MAX （列名）（列名）计算给定列的中的最大值计算给定列的中的最大值 MIN （列名）（列名）计算给定列的中最小值计算给定列的中最小值 SUM （列名）（列名）计算给定列的中所有值的和计算给定列的中所有值

42、的和 65 字段的数据类型决定了可以用在该字段上的聚合函数字段的数据类型决定了可以用在该字段上的聚合函数 类型。类型。 如：如：SUM 和和 AVG 只能用在数据类型代表数字的只能用在数据类型代表数字的 字段上。字段上。 66 示范案例示范案例25 在在“Northwind”数据库中查询数据库中查询“employees”表中雇表中雇 员的数目员的数目 。 程序清单如下：程序清单如下： SELECT count(*) AS 雇员数目雇员数目 FROM employees 67 实验并思考：实验并思考： 1） 如果本例中将如果本例中将count(*)更改为更改为count(employeeid),

43、其其 显示的个数为多少？显示的个数为多少？ 2） 如果改为如果改为count(city)呢？结果又如何呢？呢？结果又如何呢？ 68 对包含空值的字段使用聚合函数时，应注意对包含空值的字段使用聚合函数时，应注意 SQL Server 的聚合函数（的聚合函数（COUNT(*) 除外）将除外）将 忽略字段中的空值。忽略字段中的空值。 COUNT（*）将计算所有的行，即使每个字段）将计算所有的行，即使每个字段 都含有空值。都含有空值。 69 GROUP BY子句将查询结果集按某一列或多列值分子句将查询结果集按某一列或多列值分 组，分组列的值相等的为一组，并对每一组进行统计计算。组，分组列的值相等的为一

44、组，并对每一组进行统计计算。 对查询结果集分组的目的是为了细化聚合函数的作用对象。对查询结果集分组的目的是为了细化聚合函数的作用对象。 GROUP BY子句的语法格式为：子句的语法格式为： GROUP BY ALL 列名列名，列名，列名 WITH CUBE|ROLLUP HAVING 筛选条件表达式筛选条件表达式 70 语法注释：语法注释： ALL：用于指定包含所有组和结果集：用于指定包含所有组和结果集 。 BY 列名：是按列名指定的字段进行分组列名：是按列名指定的字段进行分组 。 使用使用WITH CUBE会对会对GROUP BY所列出的所有分组字段进所列出的所有分组字段进 行汇总运算行汇总

45、运算 。 使用使用WITH ROLLUP会对会对GROUP BY所列出的第一个分组所列出的第一个分组 字段进行汇总运算字段进行汇总运算 。 HAVING筛选条件表达式：表示对生成的组筛选后再对满足筛选条件表达式：表示对生成的组筛选后再对满足 条件的组进行统计条件的组进行统计 。 71 USE northwind SELECT productid, orderid ,quantity FROM order details GO 115 1110 2110 2225 3115 3230 235 只对满足只对满足 WHERE 子句的行分组子句的行分组 115 235 345 USE northwin

46、d SELECT productid ,SUM(quantity) AS total_quantity FROM order details GROUP BY productid GO 示例示例 1 USE northwind SELECT productid ,SUM(quantity) AS total_quantity FROM order details WHERE productid = 2 GROUP BY productid GO 示例示例 2 72 示范案例示范案例26 统计统计“northwind”数据库数据库“orders”表中每个城市的表中每个城市的 订单总数订单总数 。

47、 程序清单如下：程序清单如下： SELECT shipcity,count(orderid) AS 订单总数订单总数 FROM orders GROUP BY shipcity 73 使用使用 GROUP BY 子句时，应注意子句时，应注意 SQL Server 将为将为每一组每一组计算一个计算一个汇总值汇总值，并把汇总，并把汇总 值保存在一个字段中。值保存在一个字段中。 对于指定的一组，对于指定的一组，SQL Server 只生成一条记录只生成一条记录，不不 返回详细信息。返回详细信息。 SQL Server 只对满足只对满足 WHERE 子句的记录进行分子句的记录进行分 组和汇总。组和汇总

48、。 不要对可能不要对可能包含空值的字段包含空值的字段使用使用 GROUP BY 子句，子句， 因为空值也将被当作一组，而不管记录是否满足因为空值也将被当作一组，而不管记录是否满足 WHERE 子句的条件。子句的条件。 74 USE northwind SELECT productid, orderid ,quantity FROM order details GO 115 1110 2110 2225 3115 3230 235 345 USE northwind SELECT productid, SUM(quantity) AS total_quantity FROM order deta

49、ils GROUP BY productid HAVING SUM(quantity)=30 GO 示例示例 75 示范案例示范案例27 统计统计“northwind”数据库订购数据库订购“orders”表中订单总数超表中订单总数超 过过20的城市，以及这些城市的订单总数和总运费的城市，以及这些城市的订单总数和总运费 。 程序清单如下：程序清单如下： SELECT shipcity,count(orderid) 订单总数订单总数, sum(freight) 运货费总数运货费总数 FROM orders GROUP BY shipcity HAVING count(orderid)20 76 U

50、SE northwind SELECT productid, orderid ,quantity FROM order details GO 115 1110 2110 2225 3115 3230 USE northwind SELECT productid，orderid ,SUM(quantity) AS total_quantity FROM order details GROUP BY productid，orderid GO 示例示例 1115 2110 2225 3115 3230 77 示范案例示范案例28 在在“northwind”数据库订购数据库订购“orders”表中按年

51、份表中按年份 统计每个城市的订单总数统计每个城市的订单总数 。 程序清单如下：程序清单如下： SELECT shipcity,count(orderid) 订单总数订单总数, year(orderdate) AS 订购年份订购年份 FROM orders GROUP BY shipcity,year(orderdate) ORDER BY shipcity,year(orderdate) 78 CUBE 操作操作符比符比 ROLLUP 操作操作符符 多产生两个汇总多产生两个汇总 值值 总数总数 对对 orderid 1 中的所有行汇总中的所有行汇总 对对 orderid 2 中的所有行汇总中的

52、所有行汇总 只对只对 productid 1 中的行汇总中的行汇总 productid 1、 orderid 1 的具体值的具体值 productid 1、 orderid 2 的具体值的具体值 只对只对 productid 2 中的行汇总中的行汇总 productid 2、 orderid 1 的具体值的具体值 productid 2、 orderid 2 的具体值的具体值 只对只对 productid 3 中的行汇总中的行汇总 productid 3、 orderid 1 的具体值的具体值 productid 3、 orderid 2 的具体值的具体值 NULLNULL95 NULL130

53、 NULL265 1NULL15 115 1210 2NULL35 2110 2225 3NULL45 3115 3230 描述描述 USE northwind SELECT productid, orderid, SUM(quantity) AS total_quantity FROM order details GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderid GO 示例示例 79 示范案例示范案例29 统计统计“Northwind”数据库数据库“orders”表中每个城市的表中每个城市的 订单总数，并进行汇

54、总。订单总数，并进行汇总。 程序清单如下：程序清单如下： SELECT shipcity,count(orderid) AS 订单总数订单总数 FROM orders GROUP BY shipcity WITH CUBE 80 联合使用联合使用 GROUP BY 子句和子句和 CUBE 操作符，能生成基操作符，能生成基 于于 GROUP BY 子句指定的子句指定的所有字段的可能组合所有字段的可能组合 使用使用 GROUP BY子句和子句和 CUBE 操作符时，应注意操作符时，应注意 如果在如果在 GROUP BY 子句中有子句中有 n 个字段个字段或表达式，或表达式， SQL Server

55、将在结果集中返回将在结果集中返回 2n 种可能的组种可能的组 合。合。 结果集中含有结果集中含有 NULL 的记录代表该记录由的记录代表该记录由 CUBE 操作符生成。操作符生成。 81 描述描述 USE northwind SELECT productid, orderid, SUM(quantity) AS total_quantity FROM order details GROUP BY productid, orderid WITH ROLLUP ORDER BY productid, orderid GO 示例示例 NULLNULL95 1NULL15 115 1210 2NULL

56、35 2110 2225 3NULL45 3115 3230 总数总数 只对只对 productid 1中的行汇总中的行汇总 productid 1、 orderid 1 的具体值的具体值 productid 1、 orderid 2 的具体值的具体值 只对只对 productid 2 中的行汇总中的行汇总 productid 2、 orderid 1 的具体值的具体值 productid 2、 orderid 2 的具体值的具体值 只对只对 productid 3 中的行汇总中的行汇总 productid 3、orderid 1 的具体值的具体值 productid 3、orderid 2

57、的具体值的具体值 82 示范案例示范案例30 在在“Northwind”数据库数据库“orders”表中按年份统计每表中按年份统计每 个城市的订单总数，并按城市进行汇总。个城市的订单总数，并按城市进行汇总。 程序清单如下：程序清单如下： SELECT shipcity,year(orderdate) AS 订购年份订购年份, count(orderid) AS 订单总数订单总数 FROM orders GROUP BY shipcity,year(orderdate) WITH ROLLUP 83 联合使用联合使用 GROUP BY 子句和子句和 ROLLUP 操作符，操作符，计算计算组组 中

58、的汇总值中的汇总值 使用使用 GROUP BY 子句和子句和 ROLLUP 操作符时，应注意操作符时，应注意 SQL Server 处理处理 GROUP BY 中字段列表的顺序中字段列表的顺序 是是从右到左从右到左，然后对每个组使用聚合函数。，然后对每个组使用聚合函数。 SQL Server 将在结果集中增加一行，这行将显将在结果集中增加一行，这行将显 示总和或平均值之类的汇总值，示总和或平均值之类的汇总值，新增的行以新增的行以 NULL 标识。标识。 84 COMPUTE子句对查询结果集中的所有记录进行汇子句对查询结果集中的所有记录进行汇 总统计，并显示所有参加汇总记录的详细信息。当与总统计

59、，并显示所有参加汇总记录的详细信息。当与by一一 起使用时，起使用时，COMPUTE子句在结果集内生成控制中断和小子句在结果集内生成控制中断和小 计。可在同一查询内指定计。可在同一查询内指定COMPUTE BY和和COMPUTE。 使用语法格式为：使用语法格式为： COMPUTE 聚合函数（列名）聚合函数（列名） BY 列名列名 85 注释说明：注释说明： 聚合函数（列名）聚合函数（列名）:例如例如SUM（），（），AVG（），（）， COUNT（）等。此处的列名用于指明要用来汇总函数处（）等。此处的列名用于指明要用来汇总函数处 理的字段或表达式，在此不能用字段别名，并且必须是理的字段或表达式，在此不能用字段别名，并且必须是 SELECT子句列表中的一项子句列表中的一项 。 BY 列名列名:按指定列名的字段进行分组计算，并显示被统按指定列名的字段进行分组计算，并显示被统 计记录的详细信息