sql_server_2000_从入门到精通

第一章 一、SQL Server 2000 版本: 1.企业版（具备所有功能） ---作为生产数据库服务器使用。支持 SQL Server 2000 中的所有可用功能。 2.个人版（安装个人数据库） ---一般供移动的用户使用。 3.开发版（适用于我们安装，支持企业版的所有功能） ---供程序员用来开发将 SQL Server 2000 用作数据存储的应用程序。 4.版（适用于简洁开发）。 ---作为小工作组或部门的数据库服务器使用。 注意: 企业版和标准版只能安装在服务器版本的操作系统中，如（2000 Server、NT 4.0 Server等）。 二、SQL Server 2000 安装组件 1.安装数据库服务器(我们选择该组件) ---启动SQL SERVER安装程序。 2.安装Analysis Services ---在处理OLAP(联机分析处理)多维数据集的计算机上安装Analysis Services。 3.安装English Query ---可通过英语查询数据库。 三、目录路径： 1.\Program Files\Microsoft SQL Server\MSSQL\Binn ---置放程序文件。包含程序文件及通常不会更改的文件，需要的空间不大。 2.\Program Files\Microsoft SQL Server\MSSQL\Data ---置放数据文件。包含数据库和日志文件、系统日志、备份数据、复制数据所在的目录文件夹，需要的空间大。 3.\Program Files\Microsoft SQL Server\80 ---置放一些共享工具和com组件。比如联机丛书、开发工具等组件。 四、自带的系统和示例数据库 1.系统数据库 a.master 数据库 --- SQL Server 系统的所有系统级别信息。它记录所有的登录帐户和系统配置设置。 b.model 数据库 ---用作在系统上创建的所有数据库的模板。充当所有数据库的原始模板 c.tempdb 数据库 ---保存所有的临时表和临时存储过程。在SQL Server每次启动时都会重新创建该数据库，也就说该数据库在每次启动服务器时是没有任何数据的，是干净的。 d.msdb 数据库 ---供 SQL Server 代理程序调度警报和作业以及记录操作员时使用。 2.示例数据库 a.pubs 数据库 ---以一个图书出版公司为模型。可以演示SQL Server数据库中的许多操作。 b.Northwind Traders 数据库 ---包含一个名为 Northwind Traders 的虚构公司的销售数据，该公司从事世界各地的特产食品进出口贸易。 五、各种主要SQL Server工具 1.查询分析器 ---创建查询和其它 SQL 脚本，并针对 SQL Server 数据库执行它们等功能。 2.企业管理器 ---管理和配置 SQL Server，复制、导入、导出和转换数据等所有功能。 3.服务管理器 ---启动、关闭服务器。 4.事件探查器 ---查找并诊断有问题、运行慢的查询，监视 SQL Server 的性能以精细地调整工作负荷。 5.导入和导出数据 6.联机丛书 六、SQL Server 2000的特性 1. Internet 集成 ---SQL Server 2000 程序模型与 Windows DNA 构架集成，用以开发 Web 应用程序。 2．可伸缩性和可用性 ---同一个数据库引擎可以在不同的平台上使用 3. 企业级数据库功能 4. 易于安装、部署和使用 5. 数据仓库 ---析取和分析汇总数据以进行联机分析处理 (OLAP) ********************************************************* 第二章 结构化查询语言(SQL---Structured Query Language)： 通过SQL我们可以与数据库交互(访问、修改、删除数据等操作)，MS SQL Server2000在SQL-92基础上加入了一些特有的性质，称之为Transact-SQL(T-SQL)。 (SQL是由国际标准化组织 (ISO) 和美国国家标准学会 (ANSI) 发布的标准的主题) 一、T-SQL数据类型: 1.字符串 a.char ---固定长度的非 Unicode 字符数据，最大长度为 8,000 个字符 b.varchar ---可变长度的非 Unicode 数据，最长为 8,000 个字符。 c.text ---可变长度的非 Unicode 数据，最大长度为 2^31 - 1 (2,147,483,647) 个字符。 2. Unicode 字符串 a.nchar ---固定长度的Unicode 字符数据，最大长度为 4,000 个字符 b.nvarchar c.ntext 3. 整数 a.bigint ---从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。 b.int ---从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）。 c.smallint ---从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整数数据。 d.tinyint ---从 0 到 255 的整数数据。 e. bit ---1 或 0 的整数数据 f. decimal 和numeric ---从 -10^38 +1 到 10^38 –1 的固定精度和小数位的数字数据。 g. money ---货币数据值介于 -2^63 (-922,337,203,685,477.5808) 与 2^63 - 1 (+922,337,203,685,477.5807) 之间，精确到货币单位的千分之十。 h. smallmoney ---货币数据值介于 -214,748.3648 与 +214,748.3647 之间，精确到货币单位的千分之十。 4. 浮点精度数字 a.float ---从 -1.79E + 308 到 1.79E + 308 的浮点精度数字。 b.real ---从 -3.40E + 38 到 3.40E + 38 的浮点精度数字。 5. 日期 a.datetime ---从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒）。 b.smalldatetime ---从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟。 6. 二进制字符串 a.binary ---固定长度的二进制数据，其最大长度为 8,000 个字节。 b.varbinary ---可变长度的二进制数据，其最大长度为 8,000 个字节。 c.image ---可变长度的二进制数据，其最大长度为 2^31 - 1 (2,147,483,647) 个字节。 7. 其它 a.timestamp ---数据库范围的唯一数字，每次更新行时也进行更新。 b.sql_variant ---种存储 SQL Server 支持的各种数据类型（text、ntext、timestamp 和 sql_variant 除外）值的数据类型。 c.cursor ---游标的引用。 二、SQL语句: 1.DML --- SQL 语句的子集，用于检索和操作数据。 2.DCL ---用于控制对数据库对象的权限的 SQL 语句的子集。使用 GRANT 和 REVOKE 语句来控制权限 3.DDL ---一种语言，通常是数据库管理系统的一部分，用于定义数据库的所有特性和属性，尤其是行布局、列定义、键列（有时是选键方法）、文件位置和存储策略。 DQL 三、DML: 1.Select子句 用于将数据返回给应用程序或另一个 Transact-SQL 语句、或者用以填充游标的 Transact-SQL 语句。SELECT 语句返回一个表格格式的结果集，它通常由从一个或多个表中析取的数据组成。 语法： SELECT [ALL | DISTINCT] select_column_list [INTO [new_table_name]] [FROM {table_name | view_name} [(optimizer_hints)][[, {table_name2 | view_name2}[(optimizer_hints)] [WHERE clause] [GROUP BY clause][HAVING clause] [ORDER BY clause][COMPUTE clause] [FOR BROWSE] 简单语法： SELECT select_column_list [FROM {table_name } ] (所有例子及练习均使用pubs数据库) 例1： 检索 Shippers 表中的所有书籍信息： SELECT * FROM Shippers “*”代表所有的表字段，即所有列。 例2： 检索所有作者的姓：(authors table) SELECT au_lname FROM authors 可以在SELECT之后接我们所要查找的所有作者的姓字段。 例3： 检索所有作者的姓和名：(authors table) SELECT au_lname,au_fname FROM authors 可以得知，在SELECT之后我们可以接一个到多个表字段(列)。 练习： 1． 打印所有员工的职位ID。(employee table) 2． 打印所有出版图书的种类、标题、发行日期。(titles table) 例4： 检索所有作者的姓、名，并且以以下格式显示： 作者姓 作者名 ... ... ... ... 分析： 通过上面的学习，我们得知，显示的结果表格的列名都是默认的取查询表的对应列名。但我们发现，为了简洁方便，我们一般将部分表列名缩写，比如作者姓---au_lname,可是对客户来说就不怎么友好了，客户可能希望以上格式的结果，因此，SQL提供了一种“用户自定义标题”的功能，从而实现客户友好化。 解答1： SELECT au_lname '作者姓',au_fname '作者名' FROM authors 解答2: SELECT '作者姓' = au_lname ,'作者名' = au_fname FROM authors 解答3: SELECT au_lname as '作者姓',au_fname as '作者名' FROM authors 例5: 需求1：检索所有员工详细信息。 需求2：检索所有名为’ Helen’的员工详细信息(如员工ID、职位ID、受雇日期等)。 解答1： 通过以上所学，我们可以很快得出解决方案： SELECT * FROM employee 解答2： 这是一个带条件的需求了，客户只想看到名为’Helen’的员工信息，也就是说其余的员工信息不是他所关心的，光靠以上所学我们是满足不了客户的需求的，SQL提供了带条件查询的解决方案： SELECT lname from employee WHERE fname = ‘Helen’ WHERE子句语法： SELECT select_column_list [FROM {table_name}] [WHERE < search_condition >] 该子句用于返回满足搜索条件的特定行。 例6： 检索员工ID为’ F-C16315M’的员工的职位ID以及雇用日期。 解答： SELECT job_id,hire_date FROM employee WHERE emp_id = ‘F-C16315M’ 练习： 1． 按以下格式打印发货人的相关信息： 发货人ID 公司名称 联系电话 ... ... ... 2． 打印订单ID为’ 10253’的订单中订购的所有产品ID及每种产品数量。 ********************************************************* 第三章 一、算术运算符: 可以在数据类型为数字的列或者数字常量上进行加、减、乘、除、求模。 1．+(加) 两个数相加。这个加法算术运算符也可以将一个以天为单位的数字加到日期中。 例1： 需求：计算客户可订购的总单元数 解答： use Northwind SELECT ProductName, UnitsInStock + UnitsOnOrder FROM Products 例2： 需求：打印订单号为’10248’的订单签订日期后10天的具体日期 解答： use Northwind SELECT orderdate as ‘10天之前日期’, orderdate + 10 as ’10天之后日期’ FROM orders WHERE OrderID = ‘10248’ 2．-(减) 两个数相减。该减法算术运算符也可以从日期中减去一个以天数为单位数值。 3．*(乘) 例1： 需求：检索现代食谱图书的标识号和价格，并将价格乘以 1.15。 解答： USE pubs SELECT title_id, price * 1.15 AS NewPrice FROM titles WHERE type = 'mod_cook' 4．/(除) 例： 需求：打印编写商务图书的作者应得的版税 解答： USE pubs SELECT ((ytd_sales * price) * royalty)/100 AS 'Royalty Amount' FROM titles WHERE type = 'business' 5．%(求模) 模是两个整数相除后剩余的整数。 例：SELECT 20%6 结果为2 SELECT 20%2 结果为0 运算符优先级： 优先级分别为先乘(*)，除(/)，求模(%)，后为减(-)和加(+) 二、赋值运算符: ‘=’ 例： USE Northwind GO SELECT FirstColumnHeading = 'xyz', SecondColumnHeading = ProductID FROM Products GO 请大家预测一下结果？ 三、比较运算符: 测试两个表达式是否相同。 一般用于WHERE子句中。 =,>,<,<>,>=,<= 例： 需求：检索折扣大于10的所有折扣类型。 解答： SELECT discounttype FROM discounts WHERE discount > 10 四、逻辑运算符: 多个查找条件可以通过用以下的逻辑运算符的结合来做： OR(当任何一个指定查找条件是真时返回结果 true or ture 返回 true true or false 返回 true false or false 返回 false 两个条件之一成立。 例1： 需求：检索类型为商业书籍或心理学书籍的书名 解答： SELECT title,type FROM titles WHERE type = 'business' OR type = 'psychology' 例2： 需求：检索标题ID为’ BU2075’或价格大于20的书名。 解答： SELECT title FROM titles WHERE title_id = ‘BU2075’ OR price > 20 AND(当所有指定的查找条件是真时返回结果 true or ture 返回 true true or false 返回 false false or false 返回 false 两个条件必须同时成立。 例： 需求：检索标题ID为’ BU2075’且价格大于20的书名。 解答： SELECT title FROM titles WHERE title_id = ‘BU2075’ AND price > 20 请大家预测一下结果。 NOT(否定其后的表达式 反转搜索条件的结果 例： 需求：检索除德国出版商以外的其余出版商的名称。 解答： SELECT pub_name,country FROM publishers WHERE NOT (country = ‘Germany’) 五、范围运算符: 1．between，not between 语法：test_expression [ NOT ] BETWEEN begin_expression AND end_expression 例1： 需求：检索年度至今单位销售额为4095到12000之间的书标题ID。 解答： SELECT title_id, ytd_sales FROM titles WHERE ytd_sales BETWEEN 4095 AND 12000 请大家思考以下语句执行结果： SELECT title_id, ytd_sales FROM titles WHERE ytd_sales > 4095 AND ytd_sales < 12000 分析以下语句执行结果： SELECT title_id, ytd_sales FROM titles WHERE ytd_sales NOT BETWEEN 4095 AND 12000 六、列表运算符: 确定给定的值是否与子查询或列表中的值相匹配。如果匹配则显示匹配的结果集。 例： 需求：列出所有居住在加利福尼亚、印地安纳或马里兰州的作者。 解答1： SELECT au_lname, state FROM authors WHERE state IN ('CA', 'IN', 'MD') 解答2： 大家思考！ NOT IN正好相反。 七、串联运算符: 可以通过’+’运算符将字符串串联起来。 例： 需求：打印完整的作者姓名，并按特定格式显示(姓 名)，比如姓为joe，名为zhou，则应显示命名格式为’joe zhou’。 解答： SELECT (au_lname + ‘ ’ + au_fname) ‘name’ FROM authors 上机练习： 1． 打印姓名为’Smith Ann’的员工信息。(pubs--employee) 2． 查找姓为 Carson、Carsen、Karson 或 Karsen 的作者所在的行。(使用通配符) 3． 查找所有员工的ID (pubs--employee) 4． 打印食品种类的ID、名称、描述。 (northwind-- Categories) 5． 打印所有居住在西雅图的客户的电话号码。(northwind--customers) 6． 打印所有装运所在城市为France，且运费大于30的订单详细资料。(northwind--orders) 7． 打印产品ID为1到10之间的所有产品名称。 (northwind--products) 8． 打印产品供应商ID为2或者产品种类ID为2的产品名称。(northwind--products) 9． 检索订单日期为1996年7月19日的所有订单。(northwind--orders) 10． 打印产品单价为12.75、16.75、20的所有产品名称。(northwind--products) 11． 按以下格式检索： 出版者 详细地址 其中，地址格式应为：国家|州|城市(pubs--publishers) ********************************************************* 第四章 一、通配符: 通过关键字LIKE来查找带通配符机制的字符串。 LIKE关键字用来选择那些与字符串的指定部分匹配的行 SQL SERVER 2000有以下通配符： 通配符 描述 % 包含零个或更多字符的任意字符串 _ 任何单个字符 [] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符 [^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。 例1：查找出版商名称以字符串’books’结束的所有出版商信息。 解答： select pub_name from publishers where pub_name like '%books' 思考：如果改成’%books%’,会返回什么结果？ 思考：查找出版商名称以字符串’books’开始的所有出版商信息。 例2：查找出版商名称第二个字符为’i’的所有出版商信息。 解答： select pub_name from publishers where pub_name like '_i%' 思考：如果改成’_i_’会返回什么结果？ 例3：查找出版商名称包含字符’a’或’b’或’c’的所有出版商信息。 解答： select pub_name from publishers where pub_name like '%[a-c]%' 思考：’%[abc]%’,结果 思考：’%[^abc]%’,结果 二、IS NULL 和 IS NOT NULL关键字: 在SQL中，NULL是一个未知值，或者是一个尚未提供数据的值。注意：NULL和零意义不相同 例：查找stor_id为NULL的所有折扣类型 解答： SELECT * FROM discounts WHERE stor_id IS NULL IS NOT NULL意义和IS NULL相反。 ISNULL系统函数---使用指定的替换值替换 NULL 语法：ISNULL ( check_expression , replacement_value ) 例：检索书名、类型及价格，当价格为null时应显示0.00。 解答： SELECT title AS Title, type AS Type, ISNULL(price, 0.00) AS Price FROM titles 三、ORDER BY关键字: 指定结果集的排序。 语法： SELECT select_column_list FROM table_name [ORDER BY column_name | select_list_number | expression [ASC|DESC][, column_name | select_list_number | expression [ASC|DESC]...] 其中ASC代表升序(缺省值)，从最低值到最高值对指定列中的值进行排序。 DESC代表降序，从最高值到最低值对指定列中的值进行排序。 例1：打印所有员工的资料，并按员工的雇用日期的升序排列。 解答： SELECT * FROM employee ORDER BY hire_date ASC 例2：按pub_id的降序排列来打印所有图书的标题信息。 解答： SELECT * FROM titles ORDER BY pub_id DESC 注意：ORDER BY不仅可以对单列进行排序，同时也可对多列排序(其排序是嵌套的)。 例3：打印出版图书标题相关的出版商id、图书类型、图书价格，并且按照出版商id升序排列，同一出版商对应的出版图书类型按降序排列，同一出版商的同一出版图书类型的价格也按降序排列。 分析：需求比较复杂，得实现嵌套查询，首先id升序排列，而同一id对应得出版图书类型则按降序，价格一样降序。因此，我们可以通过ORDER BY来实现该嵌套排序。 解答： SELECT pub_id, type, title_id, price FROM titles ORDER BY pub_id, type DESC, price DESC 四、TOP关键字: 用于限制返回到结果集中的行数 语法：TOP n [PERCENT] n代表返回的行数，如果使用了PERCENT，则代表返回结果集的n%。 TOP一般和ORDER BY结合使用。 例1：打印最后加入公司的三名员工的ID。 解答： SELECT TOP 3 * FROM employee ORDER BY hire_date DESC 注意：TOP关键字紧跟在SELECT关键字之后使用！ SELECT TOP 10 PERCENT * FROM employee ORDER BY hire_date 假设employee表共有43行记录，那么以上语句返回几行数据？ 例2：打印发行日期在1991/6/9和1991/6/30之间的图书预付款最高的三种图书的ID。 解答： SELECT TOP 3 title_id,pubdate,advance FROM titles WHERE pubdate >= '6/9/1991' and pubdate <= '1991/6/30' ORDER BY advance DESC 五、ALL 和 DISTINCT关键字: 用于去除冗余行。 例：检索所有已出版书籍的作者ID。 解答： SELECT ALL au_id FROM titleauthor 通过以上语句我们可以解决问题，但是，大家可以发现，有的作者ID重复出现，而客户只想知道哪些作者，并不愿看到重复数据，因此，我们得去掉冗余行，通过DISTINCT关键字就能做到。 SELECT DISTINCT au_id FROM titleauthor 现在，冗余行没有了！ 注意：DISTINCT关键字一般和聚合函数一起使用，下面将讲解聚合函数。 对于 DISTINCT 关键字来说，各空值将被认为是相互重复的内容。当 SELECT 语句中包括 DISTINCT 时，不论遇到多少个空值，在结果中只返回一个 NULL。 SELECT ALL au_id FROM titleauthor 等同于 SELECT au_id FROM titleauthor 也就是说，SQL缺省的是查找所有记录(包括冗余行)。 六、聚合函数: 用于在查询结果集中生成汇总值。 常用聚合函数： 聚合函数 功能描述 AVG([ALL|DISTINCT] expression) 数字表达式中所有值的平均值 SUM([ALL | DISTINCT] expression) 数字表达式中所有值的和 MAX(expression) 表达式中的最大值 MIN(expression) 表达式中的最小值 COUNT([ALL | DISTINCT] expression) 表达式中值的个数 COUNT(*) 选定的行数 例1：计算所有书籍的平均价格。 分析：通过AVG聚合函数可以求平均值。 解答： SELECT AVG(price) FROM titles 例2：计算商务书籍的平均价格(不包括重复价格值)。 解答： SELECT AVG(DISTINCT price) FROM titles WHERE type = 'business' 对比以下语句执行结果： SELECT AVG(price) FROM titles WHERE type = 'business' 例3：计算 titles 表中所有书籍的本年度截止到目前的销售总额 解答： USE pubs SELECT SUM(ytd_sales) FROM titles 分析：通过使用SUM聚合函数来汇总列ytd_sales所有值的和，从而得到销售总额。 例4：计算商务书籍的预付款平均值和 year-to-date 的销售额总和。 解答： USE pubs SELECT AVG(advance), SUM(ytd_sales) FROM titles WHERE type = 'business' 目的：聚合函数可以一起使用。每一个聚合函数生成一个汇总值。 例5：查找产品的最高单价。 解答： SELECT MAX(unitprice) FROM products 请大家分析以下语句： SELECT productname,MAX(unitprice) FROM products 例6：查找 titles 中的书籍总数 解答： USE pubs SELECT COUNT(*) FROM titles COUNT(*)返回符合查询中指定的搜索条件的行的数目，而不消除重复值和NULL值。 例7：查找作者所居住的不同城市的数量。 解答： USE pubs GO SELECT COUNT(DISTINCT city) FROM authors GO 例8：计算现有员工的总人数。 解答： USE pubs GO SELECT count(emp_id) FROM employee GO 请大家思考在?处应填写什么内容? 可以得知，COUNT(ALL|DISTINCT expression)和COUNT(*)功能相似，但不包括NULL值。其中ALL选项会计算重复值，而DISTINCT选项不会计算重复值。 例9：对比分析下列两条语句执行结果。 语句1：SELECT COUNT(DISTINCT city) FROM authors 语句2：SELECT COUNT(ALL city) FROM authors 聚合函数小结： 1． SUM、AVG、COUNT、MAX 和 MIN 忽略空值，而 COUNT(*) 不忽略。 2． 可选关键字 DISTINCT 可以与 SUM、AVG 和 COUNT 一同使用，以便在应用聚合函数之前消除重复值（默认为 ALL）。 3． SUM 和 AVG 只能对数字列使用。MIN 和 MAX 不能对 bit 数据类型使用。除 COUNT(*) 外，其它聚合函数均不能对 text 和 image 数据类型使用 4． 查询列不能和聚合函数一起使用，除非使用分组函数(后面内容)。 5． 对NULL值的计算其返回结果为NULL值。 注意：SUM和COUNT的区别。 七、GROUP BY分组函数以及CUBE运算符: 使用聚合函数把结合集归纳为在询问中定义的分组 语法： SELECT column_list FROM table_name WHERE condition_expression GROUP BY [ALL] expression [, expression] [HAVING search_condition] 例1：计算每种类型书籍的平均价格并显示。 分析：我们先看看以下语句： SELECT AVG(price) FROM titles 该语句可以得出所有书籍(包括所有类型的书籍)的平均价格，显而易见，该语句并不能满足客户需求，客户要看的是每种类型书籍的平均价格。即分类别的查看数据，通过我们即将学习的GROUP BY分组函数就能解决客户需求。 解答： SELECT type,AVG(price) FROM titles GROUP BY type 注意：在该语句中，查询列可以和聚合函数一起使用，是因为对出现的查询列使用了分组函数！ 由此可见，GROUP BY将对其后紧接的列进行分组，比如现代烹调书籍是一组，商业书籍是一组。通过和聚合函数的一起使用，我们就能对每组进行聚合运算，从而得到正确结果。 例2：计算版税为10%的每种类型书籍的平均价格并显示。 解答： SELECT type,AVG(price) FROM titles WHERE royalty = 10 GROUP BY type 例3：执行以下语句： SELECT type,AVG(price) FROM titles WHERE royalty = 10 GROUP BY ALL type 查看结果并对比例2，分析ALL关键字在这里的作用。 例4：按类型和出版商 ID 分组，得到平均价格和 year-to-date 的销售额总和。 解答： USE pubs SELECT type, pub_id, 'avg' = AVG(price), 'sum' = SUM(ytd_sales) FROM titles GROUP BY type, pub_id 提示：GROUP BY也可以对多列的组合进行分组。 例5：打印本年度截止到目前的销售总额超过 $40,000 的出版商。 分析：按照需求，大家可能很快就给出了答案，答案如下： SELECT pub_id, total = SUM(ytd_sales) FROM titles WHERE SUM(ytd_sales) > 40000 GROUP BY pub_id 但是，很遗憾，以上语句是错误的，在WHERE子句中是不允许使用聚合函数的，因此，HAVING子句就能派上用场了！用法如下： SELECT pub_id, total = SUM(ytd_sales) FROM titles GROUP BY pub_id HAVING SUM(ytd_sales) > 40000 注意：HAVING子句可以结合GROUP BY子句一起使用，也可以单独使用。当结合GROUP BY子句时，HAVING子句将对分组后的结果进行条件筛选，显示筛选后的结果集；如单独使用则和WHERE子句功能基本类似，区别就是可以在HAVING子句中使用聚合函数！ 练习：显示本年度截止到目前的销售总额超过 $10,000 的前两位出版商。(提示：GROUP BY可以与ORDER BY子句结合使用) 例6：按类型和出版商 ID 分组，得到平均价格和 year-to-date 的销售额总和，并分别打印每一类型和每一个出版商的平均价格和销售额总和。 解答： USE pubs SELECT type, pub_id, 'avg' = AVG(price), 'sum' = SUM(ytd_sales) FROM titles GROUP BY type, pub_id WITH CUBE 输出的结果集如下： --------------------------------------------- type pub_id avg sum business 0736 2.9900 18722 business 1389 17.3100 12066 business NULL 13.7300 30788 mod_cook 0877 11.4900 24278 mod_cook NULL 11.4900 24278 popular_comp 1389 21.4750 12875 popular_comp NULL 21.4750 12875 psychology 0736 11.4825 9564 psychology 0877 21.5900 375 psychology NULL 13.5040 9939 trad_cook 0877 15.9633 19566 trad_cook NULL 15.9633 19566 UNDECIDED 0877 NULL NULL UNDECIDED NULL NULL NULL NULL NULL 14.7662 97446 NULL 0736 9.7840 28286 NULL 0877 15.4100 44219 NULL 1389 18.9760 24941 --------------------------------------------- 由此可见，CUBE运算符在 SELECT 语句的 GROUP BY 子句中指定，对每组数据进行数据汇总。 八、日期、数字、字符函数: A．日期函数 函数 功能介绍 GETDATE 返回数据类型为datetime的当前系统的日期和时间 DATEDIFF 返回指定的两个日期之间的指定日期部分相差值 DATEADD 在指定日期的指定日期部分加上指定时间并返回 DATENAME 返回指定日期的指定日期部分的字符串 DATEPART 返回指定日期的指定日期部分的整数 DAY 返回代表指定日期的天的日期部分的整数 MONTH 返回代表指定日期月份的整数 YEAR 返回表示指定日期中的年份的整数 DATEPADD语法： DATEADD ( datepart , number, date ) --- datepart：指定应向日期的哪一部分添加值。 number：增加值 date：被操作的日期 DATEDIFF语法： DATEDIFF ( datepart , startdate , enddate ) --- datepart：指定应向日期的哪一部分进行减运算。 startdate：开始日期 enddate：结束日期 DATEPART语法： DATEPART( datepart , date ) --- datepart：指定日期的哪一部分。 date：被操作日期 DATENAME语法和DATEPART语法一致。功能也相似，唯一区别就是DATENAME函数返回字符串，而DATEPART返回整数。 SQL SERVER2000中的日期部分以及缩写： 日期部分 缩写 Year yy or yyyy quarter qq or q Month mm or m Day dd or d DayOfYear dy or y Week ww or wk Hour hh Minute mi or n second ss or s millisecond ms 例1： SELECT GETDATE() ‘当前日期’ 返回结果集： 当前日期 ------------------------------------------------------ 2006-05-17 19:14:51.050 （所影响的行数为 1 行） 例2：假设当前日期为2006-05-17 SELECT DATEADD(day,10,GETDATE()) 返回结果： 10天之后的日期 ------------------------------------------------------ 2006-05-27 20:56:32.517 （所影响的行数为 1 行） 例3：打印出版书籍的已出版天数 解答： SELECT DATEDIFF(day,pubdate,getdate()) '出版天数' FROM titles 返回结果： 出版天数 ----------- 5453 5456 5435 5443 5456 5447 2110 5435 4357 2110 5322 5450 5338 5453 5453 5322 5453 5453 （所影响的行数为 18 行） 例4：以字符串的格式打印当天所在月份？。 解答： SELECT DATENAME(yy,GETDATE()) + '年' + DATENAME(mm,GETDATE()) + '月' 返回结果： --------------------------------------------------------- 2006年05月 （所影响的行数为 1 行） 例5：返回当前的月份。 解答： SELECT '第' + CONVERT(CHAR(1),MONTH(GETDATE())) + '月' 返回结果： ----- 第5月 （所影响的行数为 1 行） 补充： SQL提供了两种数据类型转换的方法，分别如下： 1． CAST 语法：CAST ( expression AS data_type ) 例： SELECT 2． CONVERT 语法：CONVERT (data_type[(length)], expression [, style]) 教材214页。 B．字符串函数 函数 功能介绍 SUBSTRING 返回字符、binary、text 或 image 表达式的一部分 ASCII 返回字符表达式最左端字符的 ASCII 代码值 CHAR 将ASCII 代码转换为字符的字符串函数 LEFT 返回从字符串左边开始指定个数的字符 RIGHT 返回字符串中从右边开始指定个数的字符 LEN 返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格 LOWER 将大写字符数据转换为小写字符数据后返回字符表达式 UPPER 返回将小写字符数据转换为大写的字符表达式 LTRIM 删除起始空格后返回字符表达式 RTRIM 截断所有尾随空格后返回一个字符串。 REPLACE 用新字符串替换原有字符 例1：SELECT SUBSTRING('abcdef',1,3) 分析：从第一个字符’a’开始提取3个字符’abc’。 例2：SELECT ASCII('a') 分析：返回字符’a’的ASCII码。 例3：SELECT LEFT(' abcde',2) 分析：返回表达式' abcde'左边开始指定的2个字符。 例4：SELECT LEN('ab cde') 分析：返回字符串'ab cde'的长度 例5：SELECT UPPER('abcde') 分析：返回字符串'abcde'的大写格式 例6：SELECT RTRIM(LTRIM(' abc e ')) 分析：去除字符串' abc e '的左右空格并返回结果。 例7：SELECT REPLACE('abcde','cd','ef') 分析：将字符串'abcde'中的部分字符串'cd'替换为'ef' C．数值函数 函数 功能介绍 ROUND 返回数字表达式并四舍五入为指定的长度或精度 FLOOR 返回小于或等于所给数字表达式的最大整数 RAND 返回 0 到1 之间的随机float 值。 CEILING 返回大于或等于所给数字表达式的最小整数。 例1：SELECT ROUND(123.9994, 0), ROUND(123.9995, 3), ROUND(123.45, -2) 分析：当第二个参数值为正数时从数字表达式(第一个参数)的小数位往后移动3位，从该处进行四舍五入。负数则移动方向相反。 例2：SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45) 预测结果。 D．其余函数 1．DATALENGTH( ) 返回指定的表达式所用的字节数 2．USER_NAME( ) 返回当前用户名 ********************************************************* 第五章 一、联接查询: 在前面，我们都是对单张表查询，而在现实中，客户有时查看的数据将会涉及到多张表的内容(比如：？)，因此，数据库提供了一种对一张以上的表进行查询的功能，我们称之为联接查询！联接可被定义一个操作，包括一次从多个表中检索数据 注意：对于实现联接查询的多张表之间需满足一些特定条件，在下面我们将会详细掌握。 联接条件可在 FROM 或 WHERE 子句中指定，建议在 FROM 子句中指定联接条件 各种联接类型： 1． 内联接(inner join) 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行 结合比较运算符使用。 语法： SELECT select_column_list FROM table_name1 [INNER] JOIN table_name2 ON table_name1.ref_column_name join_operator table_name2.ref_column_name 通过join关键字将两张表联接起来，on关键字指出两张表之间的联接公共列！其中join_operator可以是=、<>这样的比较运算符。 例1：检索同一个城市中的作者和出版商的详细资料 分析：我们已知，作者的相关资料存储在authors表，出版商信息存储在publishers表中。因此，客户查看的资料来自于两张不同的表。客户要求作者和出版商必需在同一城市，也就是理解为，authors表中的city列和publishers表中的city列中都出现的城市所在的行记录都得检索出来，从而涉及到检索多张表中数据。而authors表与publishers表都有列--city，且两张表的city列数据类型兼容，因此我们可以在这两张表上进行联接查询操作！ 解答： USE pubs SELECT * FROM authors INNER JOIN publishers ON authors.city = publishers.city 以上语句将从authors和publishers中查找居住城市相同的作家和出版商的信息，其中通过INNER JOIN将两张表联接起来，接着再通过ON指出两张表的公共列，authors.city = publishers.city表示只搜索两张表中都存在的city所在的行记录。 注意：在返回结果集中我们发现city列出现了两次，其中一列属于authors表，一列属于publishers表，数据是冗余的，没有任何意义，这种通过*查找所有列的联接查询我们称之为相等联接。 因此，为了去掉多余的列，我们可以更改选择列表来消除两个相同列中的一个，将*改为特定的表列名。通过指定表列名的联接查询我们称之为自然联接！ 例2：执行以下语句，对比例1结果： USE pubs SELECT publishers.pub_id, publishers.pub_name, publishers.state,authors.* FROM authors INNER JOIN publishers ON authors.city = publishers.city 例3：查找折扣为5折的所有折扣类型和商铺名称。 分析：首先，折扣类型相关资料存储在discounts表中，其次商铺名称信息存储在sto