第5章 Transact-SQL程序设计 5.1 数据查询SELECT的用法 5.2 插入表记录INSERT的用法 5.3 删除数据行DELETE的用法 5.4 修改表字段数据UPDATE 　 的用法

数据库查询（SELECT）的各种语法格 式，特别是分组查询、计算和统计以及表联 接JOIN等查询操作； 能力目标 　　数据库查询（SELECT）的各种语法格 式，特别是分组查询、计算和统计以及表联 接JOIN等查询操作； 　　修改数据库记录UPDATE语句的用法； 　　插入数据记录INSERT语句的用法； 　　删除数据库记录DELETE语句的用法。

态度目标 　　团队精神； 　　态度积极； 　　任务按时完成； 　　出勤。

重点难点 　　重点： 　　熟练掌握数据操作的编程方法。 　　难点：

5.1 数据查询SELECT的用法 1. SELECT的Transact-SQL语法结构 要的子句可归纳如下： SELECT select_list [ INTO new_table ] [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression] [ HAVING search_condition] [ ORDER BY order_expression [ ASC | DESC ] ]

5.1 数据查询SELECT的用法 2. SELECT子句 SELECT子句指定查询返回的列，这些列可以是 一个或多个表或视图（View）中的列。见【案例5.1】 【案例5.2】【案例5.3】。 SELECT [ ALL | DISTINCT ] [ TOP expression [ PERCENT ] [ WITH TIES ] ] <select_list> <select_list> ::=     { * | { table_name | view_name | table_alias }.*       | { column_name | [ ] expression }       [ [ AS ] column_alias ]       | column_alias = expression     } [ ,...n ]

5.1 数据查询SELECT的用法 【案例5.1】从eTradeInfo数据库的员工信息表employees中查 询在1950年1月1日前出生的职员的姓名和职务。 　SELECT emp_name AS 姓名,emp_title AS 职务 　FROM employees WHERE emp_birthday < '1950-01-01' 【案例5.2】从eTradeInfo数据库的产品信息表products中查询 单价高于11005.20元的前100条产品信息。 　SELECT TOP(100) * FROM products WHERE UnitPrice > 11005.20 【案例5.3】从eTradeInfo数据库的产品信息表products中查询 不同型号的产品名称、型号和生产日期。 SELECT DISTINCT ProType AS 型号, Product AS 产品名称, ProDate AS 生产日期 FROM products

5.1 数据查询SELECT的用法 3. INTO子句 通过查询语句创建新表，并将查询的结果插入 新表中。 【案例5.4】 　　通过查询语句创建新表，并将查询的结果插入 新表中。 【案例5.4】 　　从数据库eTradeInfo 中的employees表中查询在 1950年1月1日前出生的职员的姓名和职务，并将查 询结果存储到新表someEmployees。 SELECT emp_name,emp_title INTO someEmployees FROM employees WHERE emp_birthday < '1950-01-01'

5.1 数据查询SELECT的用法 4. FROM子句 指定从其中检索记录行的数据表、视图、派 生表和联接表等。 　　指定从其中检索记录行的数据表、视图、派 生表和联接表等。 [ FROM { < table_source > } [ ,...n ] ] < table_source > ::=     table_name [ [ AS ] table_alias ]     | view_name [ [ AS ] table_alias ]     | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]     | < joined_table > < joined_table > ::=      < table_source > < join_type > < table_source > ON < search_condition >     | < table_source > CROSS JOIN < table_source > < join_type > ::=     [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]     JOIN

5.1 数据查询SELECT的用法 【案例5.5】使用CROSS JOIN，查询eTradeInfo数据库中两个 数据表employees和departments的所有记录。 SELECT * FROM departments CROSS JOIN employees 查询返回的结果集是由departments和employees中所有行的完 全联接组成（笛卡尔积）。 试与以下语句比较：SELECT * FROM departments, employees 【案例5.6】从eTradeInfo数据库两个数据表departments和 employees中，列出每个部门的员工姓名和部门名称信息，并 将departments表中所有没有分配员工的部门也查询出来（使 用LEFT JOIN）。 SELECT emp_name,dept_name FROM departments LEFT OUTER JOIN employees ON (departments.dept_id = employees.dept_id)

5.1 数据查询SELECT的用法 5. WHERE子句 【案例5.7】 从销售合同明细中查询2006年前销售产品数量大 　　语法：[ WHERE <search_condition> ] 【案例5.7】 　　从销售合同明细中查询2006年前销售产品数量大 于1024的所有销售记录。 SELECT * FROM Sales S, Sale_Detail D WHERE S.Order_id = D.Order_id AND Quantity > 1024 AND Order_Date < '2006-01-01'

5.1 数据查询SELECT的用法 6. GROUP BY子句 对查询结果行进行分组显示，并且如果SELECT 子句 <select list> 中包含聚合函数，则计算每组的汇 总值。 [ GROUP BY [ ALL ] group_by_expression [ ,...n ] [ WITH { CUBE | ROLLUP } ] ]

5.1 数据查询SELECT的用法 CUBE：指定在结果集内，不仅包含由 GROUP BY提供的行，还包含汇总行。在结果集内返回每个 　　GROUP BY 汇总行在结果中显示为 NULL，但 可用来表示所有值。 　　ROLLUP：指定在结果集内不仅包含由GROUP BY提供的行，还包含汇总行。按层次结构顺序，从 组内的最低层到最高层汇总组。组的层次结构取决 于指定分组列时所使用的顺序。

5.1 数据查询SELECT的用法 【案例5.8】 按照Product和ProType分组查询Quantity小计和合计。 SELECT Product AS 产品名称, ProType 产品型号, SUM(Quantity) AS 库存数量 FROM Products GROUP BY Product, ProType WITH CUBE 下面是结果集： 产品名称　　　产品型号 库存数量 -------- -------- -------- 微机椅　　　红色椅40X40X50 210.00 微机椅　　　蓝色椅40X40X50 101.00 微机椅　　　 (null) 311.00 微机桌　　　红色桌100X60X70 223.00 微机桌　　　蓝色桌100X60X70 124.00 微机桌　　　(null) 347.00 (null) 　　　(null) 658.00 (null) 　　　红色椅40X40X50 210.00 (null) 　　　 蓝色椅40X40X50 101.00 (null) 红色桌100X60X70 223.00 (null) 蓝色桌100X60X70 124.00 表Products中包含以下数据行： Product ProType Quantity ------- ------- ------- 微机桌　　蓝色桌100X60X70　124 微机桌　　红色桌100X60X70　223 微机椅　　蓝色椅40X40X50　 101 微机椅　 红色椅40X40X50 210 　　按照Product和ProType分组查询Quantity小计和合计。

5.1 数据查询SELECT的用法 7. HAVING子句 指定组或聚合的搜索条件。HAVING通常与 GROUP BY子句一起使用。 [ HAVING < search_condition > ] 【案例5.9】 　　按照部门、性别分组统计各部门在2000年以来聘用员 工名单。 SELECT dept_id AS 部门编号, emp_sex AS 性别, emp_name AS 员工姓名, hiredate FROM employees GROUP BY dept_id, emp_sex, emp_name, hiredate HAVING hiredate >= '2000-01-01'

5.1 数据查询SELECT的用法 8. UNION子句 { <query specification> | ( <query expression> ) }   UNION [ ALL ]   <query specification | ( <query expression> ) [ UNION [ ALL ] <query specification> | ( <query expression> )     [ ...n ] ]

5.1 数据查询SELECT的用法 【案例5.10】 使用UNION运算符，查询并统一显示员工信息表 employees中2006年以来聘用的员工姓名和电话以及从 客户信息表customers中显示客户联系人姓名和电话。 　　SELECT emp_name AS 员工和客户编号, 　　emp_phone AS联系电话 　　FROM employees 　　WHERE hiredate >= '2006-01-01' 　　UNION ALL 　　SELECT customer, cust_phone 　　FROM customers 　　ORDER BY 1,2

5.1 数据查询SELECT的用法 9. ORDER BY子句 【案例5.11】 列出 Sale_Detail 的销售记录，要求按销售数 [ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ] 【案例5.11】 　　列出 Sale_Detail 的销售记录，要求按销售数 量降序、订单编号升序排序输出。 SELECT * FROM Sale_Detail ORDER BY quantity DESC, Order_ID ASC

5.1 数据查询SELECT的用法 10. COMPUTE子句 【案例5.12】 使用简单COMPUTE子句生成 Sale_Detail（销     { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }     ( expression ) } [ ,...n ]     [ BY expression [ ,...n ] ] ] 【案例5.12】 　　使用简单COMPUTE子句生成 Sale_Detail（销 售明细）中quantity（销售数量）汇总小计。 SELECT pro_id AS 产品编号, quantity AS 销售数量 FROM Sale_Detail ORDER BY pro_id COMPUTE SUM(quantity)

5.2 插入表记录INSERT的用法 　　1. 使用“SQL Server Management Studio”平台直接 插入表中数据

5.2 插入表记录INSERT的用法 2. 使用INSERT语句插入表中数据 INSERT [ INTO] table_name[ ( column_list ) ] VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) | execute_statement [; ] 【案例5.13】在部门信息表中添加新部门 “企业规划部” 信息。 　　INSERT INTO departments(dept_name,dept_addr) 　　VALUES ('企业规划部', '办公大楼2楼213') 【案例5.14】将新招聘的员工基本信息（存放在new_emp表中） 汇总到员工信息表中统一登记注册。 　　INSERT INTO employees 　　SELECT * FROM new_emp

5.3 删除数据行DELETE的用法 　　1. 使用“SQL Server Management Studio”平台删除 表中记录行

5.3 删除数据行DELETE的用法 2. 使用DELETE语句删除表中记录行 【案例5.15】从departments表中删除所有行。 DELETE [ FROM ] { table_name } 【案例5.15】从departments表中删除所有行。 DELETE FROM departments 【案例5.16】从employees表中删除前10个员工。 DELETE employees FROM (SELECT TOP(10) * FROM employees) AS emp WHERE employees.emp_id = emp.emp_id

5.4 修改表字段数据UPDATE的用法 　　1. 使用“SQL Server Management Studio”平台修改 表中数据项

5.4 修改表字段数据UPDATE的用法 2. 使用UPDATE语句修改表中数据项 UPDATE table_name SET { column_name = { expression | DEFAULT | NULL } [ WHERE < search_condition > ] 【案例5.17】将UPDATE语句与SELECT语句中的TOP子句 一起使用，对来自表departmentss的前10个部门的dept_addr 列更新为“华林大厦213”。 UPDATE departments SET dept_addr = '华林大厦213' FROM (SELECT TOP(10) * FROM departments ORDER BY dept_id) AS dept WHERE departments.dept_id = dept.dept_id

使用案例数据库 eTradeInfo，在 “ SQL Server Management Studio ” 管理窗口中，完成以下题目 案例分析 　　使用案例数据库 eTradeInfo，在 “ SQL Server Management Studio ” 管理窗口中，完成以下题目 Transact-SQL代码调试。 　　1. 从进货合同和明细表中，查询所有在2008年 9月到期的合同主要信息。 　　2. 按部门分组查询自2000年签订的合同信息。 　　3. 按照仓库编号，查询库存信息，并按入库时 间降序、库存数量升序显示。 　　4. 按部门分组统计出库数量。 　　5. 按部门统计查询今年签订的合同数量和金额。 　　6. 分组统计各类商品的库存数量。

1. 正确编写T-SQL语句，并在“ SQL编辑器 ” 窗口中调试； 2. 输入正确数据进行代码验证； 3. 灵活使用分组查询。 问题： 案例分析 要求： 　　1. 正确编写T-SQL语句，并在“ SQL编辑器 ” 窗口中调试； 　　2. 输入正确数据进行代码验证； 　　3. 灵活使用分组查询。 问题： 　　1. 分组列表项与查询列表项怎样对应？ 　　2. 在往有外键关系的表中输入数据时的循序 是怎样的？ 讨论： 　　从多个表中查询数据，用Join语句和不用Join 语句结果一样吗？

数据操作的四个命令：数据查询（SELECT）、 修改（UPDATE）、插入（INSERT）以及删除 （DELETE）等操作。 本章小结 　　数据操作的四个命令：数据查询（SELECT）、 修改（UPDATE）、插入（INSERT）以及删除 （DELETE）等操作。 　　在这些语句中，可以使用系统函数，可以进行 查询分组、派生新列表项、统计和计算等操作。 　　可以根据给定的条件修改或删除表中的数据。 这四种操作命令的语法中的参数有些是具有同样的 含义，因此，可以综合起来比较学习，对语法结构 不必死记硬背，可通过学习大量的示例来理解这些 语法中参数的含义和用法。