3 SELECT高级查询
3.1 数据汇总
3.1.1 聚合函数
例如,下面的SQL语句用来查询authors表中有几个作者的地址存在:
USE bookdb
GO
SELECT COUNT(address) FROM authors
GO
执行结果:
2
而下面的SQL语句则是查询book表中书籍的价格的最大值:
USE bookdb
GO
SELECT MAX(price) FROM book
GO
执行结果为:
45.0
3.1.2 GROUP BY子句
例如,下面的SQL语句首先在表clients、表book、表orderform中插入几笔记录,然后使用GROUP BY子句汇总各个客户所定购的书籍总数:
USE bookdb
GO
--在clients表中插入两个客户
INSERT clients VALUES(2,'科技书店','北京市朝阳区')
INSERT clients VALUES(3,'明天书屋','北京市西城区')
--GO
--在book表中插入4本书的记录
INSERT book VALUES(5,'AutoCAD 2000 中文版使用指南',2,25.0,'21世纪出版社')
INSERT book VALUES(6,'Office 2000 中文版使用指南',2,28.0,'明天出版社')
INSERT book VALUES(7,'Windows 2000 Professional 中文版使用指南',1,30.0,'东东出版社')
INSERT book VALUES(8,'Linux 使用指南',3,32.0,'唐唐出版社')
GO
--在orderform表中插入6笔记录
INSERT orderform VALUES(2,6,10,GETDATE(),2)
INSERT orderform VALUES(3,5,10,GETDATE(),3)
INSERT orderform VALUES(4,3,25,GETDATE(),1)
INSERT orderform VALUES(5,8,15,GETDATE(),1)
INSERT orderform VALUES(6,4,30,GETDATE(),3)
INSERT orderform VALUES(7,7,40,GETDATE(),2)
GO
SELECT clients.client_name,SUM(orderform.book_number) AS 书籍总数
FROM clients,orderform
WHERE clients.client_id=orderform.client_id
GROUP BY clients.client_name
GO
CUBE参数会对检索的字段中各类型的数据做汇总运算。例如,下面的SQL语句就是使用CUBE进行汇总的例子:
SELECT clients.client_name,book.book_name,
SUM(orderform.book_number) AS 书籍总数
FROM clients,orderform,book
WHERE clients.client_id=orderform.client_id AND book.book_id=orderform.book_id
GROUP BY clients.client_name,book.book_name WITH CUBE
ROLLUP参数会依据GROUP BY后面所列第一个字段做汇总运算。如果要检索不同客户定购的各种书的总量和所有书的总量,可执行下述SQL语句:
FROM clients,orderform,book
WHERE clients.client_id=orderform.client_id AND book.book_id=orderform.book_id
GROUP BY clients.client_name,book.book_name WITH ROLLUP
3.1.3 HAVING子句
例如,下面的SQL语句用于查询定购书量大于等于40本的客户名称和书名:
SELECT clients.client_name,book.book_name,
SUM(orderform.book_number) AS 书籍总数
FROM clients,orderform,book
WHERE clients.client_id=orderform.client_id AND book.book_id=orderform.book_id
GROUP BY clients.client_name,book.book_name
HAVING SUM(orderform.book_number)>=40
3.1.4 COMPUTE和COMPUTE BY子句
例如,下面就是使用COMPUTE子句的例子:
SELECT clients.client_name,book.book_name,orderform.book_number
FROM clients,orderform,book
WHERE clients.client_id=orderform.client_id AND book.book_id=orderform.book_id
COMPUTE SUM(orderform.book_number)
如果需要各个客户的定购数量,而不是总的定购数量,则可执行下面的SQL语句:
FROM clients,orderform,book
WHERE clients.client_id=orderform.client_id AND book.book_id=orderform.book_id
ORDER BY clients.client_name
COMPUTE SUM(orderform.book_number) BY clients.client_name
3.2 联接查询
例如,下面使用联接查询书名称及其作者:
USE bookdb
GO
SELECT book_name,author_name FROM book JOIN authors
    ON (book.author_id=authors.author_id)
GO
无法在ntext、text或image列上直接联接表。不过,可以用SUBSTRING在ntext、text或image列上间接联接表。例如:
SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20)
= SUBSTRING(t2.textcolumn, 1, 20)
在表t1和t2中的每个文本列前20个字符上进行两表内联接。此外,另一种比较两个表中的ntext或text列的方法是用WHERE子句比较列的长度。例如:
WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)
3.2.1 内联接
内联接使用INNER JOIN关键词,上面查询书名称及其作者的例子就是一个内联接的例子,也可以按下面方式查询:
USE bookdb
GO
SELECT book_name,author_name FROM book INNER JOIN authors
    ON (book.author_id=authors.author_id)
GO
3.2.2 外联接
1.左向外联接
例如,下面的SQL语句首先删除order_id为7的订单(定购的书籍为《Windows 2000 Professional 中文版使用指南》),然后查询那本书没有被定购:
USE bookdb
GO
DELETE orderform WHERE order_id=7
GO
SELECT orderform.order_date,book.book_name, orderform.book_number
 FROM book LEFT OUTER JOIN orderform ON (book.book_id=orderform.book_id)
GO
2.右向外联接
实际上,右向外联接和左向外联接的的功能是一样的,例如,将上面的示例中,将FROM子句中book表和orderform表交换一下位置,然后使用RIGHT OUTER JOIN:
USE bookdb
GO
SELECT orderform.order_date,book.book_name, orderform.book_number
 FROM orderform RIGHT OUTER JOIN book ON (book.book_id=orderform.book_id)
GO
3.2.3 交叉联接
例如,下面的SQL语句使用交叉联接产生客户和作者可能的组合:
USE bookdb
GO
SELECT clients.client_name,authors.author_name
 FROM clients CROSS JOIN authors
GO
3.3 子查询
例如,下面的SQL语句使用子查询来查询《Windows 2000 网络管理》一书的作者:
USE bookdb
GO
SELECT author_name FROM authors
WHERE author_id =
             (SELECT author_id
              FROM book
              WHERE book_name='Windows 2000 网络管理')
GO
使用下面的联接方式也能完成此功能:
USE bookdb
GO
SELECT author_name
FROM authors JOIN book ON(book.author_id=authors.author_id)
WHERE book_name='Windows 2000 网络管理'
GO
3.3.1 子查询类型
1.使用IN或NOT IN
例如,下面的SQL语句查询已经被定购的书的名称和出版社:
USE bookdb
GO
SELECT book_name,publisher
FROM book
WHERE book_id IN(
     SELECT book_id
     FROM orderform)
GO
如果要查询没有被定购的书籍,则可以使用NOT IN:
USE bookdb
GO
SELECT book_name,publisher
FROM book
WHERE book_id NOT IN(
     SELECT book_id
     FROM orderform)
GO
2.UPDATE、DELETE和INSERT语句中的子查询
子查询可以嵌套在UPDATE、DELETE和INSERT语句以及SELECT语句中。例如,删除没有被定购的书籍信息:
USE bookdb
GO
DELETE book
WHERE book_id NOT IN(
     SELECT book_id
     FROM orderform)
GO
SELECT * FROM book
GO
3.比较运算符的子查询
例如,下面查找大于平均价格的书籍:
USE bookdb
GO
SELECT DISTINCT book.book_name
FROM book
WHERE price >
   (SELECT AVG(price)
   FROM book)
GO
例如,下面返回价格最高的一本书的书名:
USE bookdb
GO
SELECT book.book_name
FROM book
WHERE price >=ALL
   (SELECT price
   FROM book)
GO
4.存在性检查
例如,要查询所有被定购的书籍名称及其相应的出版社,可使用下面的SQL语句:
USE bookdb
GO
SELECT book_name,publisher
FROM book
WHERE EXISTS(
     SELECT *
     FROM orderform)
GO
3.3.2 多层嵌套
例如,下面使用多层嵌套子查询来查询客户“科技书店”所定购的书籍名称:
USE bookdb
GO
SELECT book_name
FROM book
WHERE book_id IN(
     SELECT book_id
     FROM orderform
     WHERE client_id=(
          SELECT client_id
           FROM clients
           WHERE client_name='科技书店')
    )
GO
7.1.3.4 相关子查询
例如,下面使用相关子查询查询名为“刘耀儒”的作者所著的书目:
USE bookdb
GO
SELECT book_name
FROM book
WHERE '刘耀儒' IN
   (SELECT author_name
   FROM authors
   WHERE authors.author_id = book.author_id)
GO
7.1.4 使用UNION运算符组合多个结果
例如,要查询所有作者和客户的号码和名称,可使用下面的SQL语句:
USE bookdb
GO
SELECT author_id,author_name FROM authors
UNION
SELECT client_id,client_name FROM clients
GO
7.1.5 在查询的基础上创建新表
例如,下面的SQL语句将查询得到的书名和作者插入到新建的表author_book中:
USE bookdb
GO
SELECT book.book_name,authors.author_name
INTO author_book
FROM authors,book
WHERE authors.author_id=book.author_id
GO
执行结果可通过下面的SELECT语句来查看:
SELECT * FROM author_book
7.2 错误处理
7.2.1 使用@@ERROR全局变量处理错误
例如,下述语句就是一个使用@@ERROR的例子:
USE bookdb
GO
SELECT * FROM book
GO
IF @@ERROR=0
PRINT '执行成功!'
GO
7.2.2 使用RAISERROR
例如,下面的例子在返回给应用程序的消息中替换了DB_ID和DB_NAME函数的值:
DECLARE @DBID INT
SET @DBID = DB_ID()
 
DECLARE @DBNAME NVARCHAR(128)
SET @DBNAME = DB_NAME()
 
RAISERROR
   ('The current database ID is:%d, the database name is: %s.',
    16, 1, @DBID, @DBNAME)
而以下例子使用由用户定义的消息完成了同样的处理:
EXEC sp_addmessage 50005, 16,
      'The current database ID is:%d, the database name is: %s.','us_english'
GO
DECLARE @DBID INT
SET @DBID = DB_ID()
 
DECLARE @DBNAME NVARCHAR(128)
SET @DBNAME = DB_NAME()
 
RAISERROR (50005, 16, 1, @DBID, @DBNAME)
GO
7.3 管理ntext、text和image数据
例如,下面的SQL语句在test数据库中创建一个text1表,其中c2字段的数据类型为text,并插入一笔记录:
USE test
GO
CREATE TABLE text1 (c1 int, c2 text)
EXEC sp_tableoption 'text1', 'text in row', 'on'
INSERT text1 VALUES ('1', 'This is a text.')
GO
然后执行下面的SQL语句:
SELECT * FROM text1
7.3.1 检索ntext、text或image
1.在SELECT语句中引用该列
例如,可使用下面的语句将TEXTSIZE改为64512:
SET TEXTSIZE 64512
如果要改为默认值,则可以使用下面的SQL语句:
SET TEXTSIZE 0
2.使用TEXTPTR函数可获得传递给READTEXT语句的文本指针
例如,下面读取text1表的c2字段的第1到第7个字符:
--在对text数据类型的对象使用指针前,应将text in row选项关闭
EXEC sp_tableoption 'text1', 'text in row', 'off'
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(c2)
   FROM text1
READTEXT text1.c2 @ptrval 0 7
3.使用SUBSTRING函数可检索从列开头特定偏移位置开始的数据块
例如,可以使用下面的SQL语句来检索text1表的c2字段的第1到第7个字符:
SELECT SUBSTRING(c2, 1, 7) AS c2
FROM text1
3.使用PATINDEX函数可检索一些特定字节组合的偏移量
例如,下面的SQL语句检索字符a位于c2字段的第几个:
USE test
GO
SELECT PATINDEX('%a%',c2) AS 起始位置
FROM text1
GO
7.3.2 修改ntext、text或image
2.使用WRITETEXT语句重写该列的整个数据值
例如,下面的SQL语句修改test数据库中的text1表的字符串值:
USE test
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(c2) FROM text1
WRITETEXT text1.c2 @ptrval 'This is a modified text.'
GO
SELECT * FROM text1
GO
3.使用UPDATETEXT语句更新ntext、text或image列的特定数据块
例如,下面的SQL语句在text1表的c2字段的末尾加入字符串This is an inserted text.:
USE test
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(c2)
   FROM text1
UPDATETEXT text1.c2 @ptrval NULL 0 'This is an inserted text.'
GO
SELECT * FROM text1
GO
7.4 事务处理
7.3.2 显式事务
4.在事务内设置保存点
例如:
USE bookdb
GO
BEGIN TRAN MyTran --启动事务
    INSERT INTO book
        VALUES(9,'Windows 2000 Professional 看图速成',1,35,'21世纪出版社') --插入一笔记录
 
SAVE TRAN MySave --保存点
 
    DELETE book WHERE book_id=9 --删除记录
 
ROLLBACK TRAN MySave --回滚事务
COMMIT TRAN
GO
SELECT * FROM book --查询book表的记录
GO
7.3.3 自动提交事务
在下面的例子中,由于编译错误,第三个批处理中的任何INSERT语句都没有执行(没有返回显示结果)。但看上去好像是前两个INSERT语句没有执行便进行了回滚:
USE test
GO
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
INSERT INTO TestBatch VALUES (1, 'aaa')
INSERT INTO TestBatch VALUES (2, 'bbb')
INSERT INTO TestBatch VALUSE (3, 'ccc') /*符号错误*/
GO
SELECT * FROM TestBatch   /*不会返回任何结果*/
GO
7.3.4 隐式事务
下的SQL语句演示了在将IMPLICIT_TRANSACTIONS设置为ON时显式或隐式启动事务。它使用@@TRANCOUNT函数演示打开的事务和关闭的事务:
USE test
GO
 
SET NOCOUNT ON --不显示受影响的行数
CREATE table t1 (a int)
GO
INSERT INTO t1 VALUES (1)
GO
 
PRINT '使用显示事务'
BEGIN TRAN
INSERT INTO t1 VALUES (2)
PRINT '事务内的事务数目: '+ CAST(@@TRANCOUNT AS char(5))
COMMIT TRAN
PRINT '事务外的事务数目: '+ CAST(@@TRANCOUNT AS char(5))
GO
 
PRINT '设置IMPLICIT_TRANSACTIONS为ON'
GO
SET IMPLICIT_TRANSACTIONS ON
GO
 
PRINT '使用隐式事务'
GO
--这里不需要BEGIN TRAN语句来定义事务的启动
INSERT INTO t1 VALUES (4)
PRINT '事务内的事务数目: '+ CAST(@@TRANCOUNT AS char(5))
COMMIT TRAN
PRINT '事务外的事务数目: '+ CAST(@@TRANCOUNT AS char(5))
GO
7.5 数据的锁定
7.5.4 自定义锁
2.自定义锁超时
若要查看当前LOCK_TIMEOUT的值,可以使用@@LOCK_TIMEOUT全局变量。例如,下面的SQL语句设置LOCK_TIMEOUT的值为1800毫秒,并使用@@LOCK_TIMEOUT来显示该值:
SET LOCK_TIMEOUT 1800
GO
DECLARE @Timeout int
SELECT @Timeout = @@lock_timeout
PRINT @Timeout
GO
3.自定义事务隔离级别
例如,若要设置事务隔离级别为可串行读,以确保并发事务不能在book表中插入幻像行,则可执行下述SQL语句:
USE bookdb
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT * FROM book
GO
若要确定当前设置的事务隔离级别,可以使用DBCC USEROPTIONS语句,例如:
USE bookdb
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DBCC USEROPTIONS
GO
4.锁定提示
例如,如果将事务隔离级别设置为SERIALIZABLE,并且在SELECT语句中使用表级锁定提示NOLOCK,则可执行下面的SQL语句:
USE bookdb
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT book_name FROM book WITH (NOLOCK)
GO
可以使用sp_lock存储过程来查看锁定:
EXEC sp_lock
GO
可以使用object_name函数来返回此锁定的数据库对象(表):
SELECT object_name(1029578706)
GO
7.6 使用游标
7.6.1 游标的概念
7.6.2 使用游标
3.从打开的游标中提取行
例如:
USE bookdb
GO
SET NOCOUNT ON
UPDATE book SET book_name = 'LINUX教程'
WHERE book_id = 20
IF @@ROWCOUNT = 0
   print '没有行被更新!'
GO
6.游标示例
下面是一个简单的游标使用的例子:
/*声明游标*/
DECLARE book_cursor CURSOR
 FOR SELECT * FROM book
/*打开游标*/
OPEN book_cursor
/*提取第一行数据*/
FETCH NEXT FROM book_cursor
/*关闭和释放游标*/
CLOSE book_cursor
DEALLOCATE book_cursor
下面使用游标打印一个简单的书籍信息的表格:
USE bookdb
GO
SET NOCOUNT ON
/*打印表标题*/
PRINT '               *********书籍信息表***********'
PRINT ' '
PRINT '-----------------------------------------------------------------'
PRINT '|                                              | 作 者 |价格|'
PRINT '-----------------------------------------------------------------'
/*声明变量*/
DECLARE @bookname varchar(100), @authorname varchar(20),
@bookprice varchar(40),@pubname varchar(80)
 
/*声明游标*/
DECLARE book_cursor CURSOR
 FOR SELECT book_name,author_name,price FROM book,authors
            WHERE book.author_id=authors.author_id
/*打开游标*/
OPEN book_cursor
/*提取第一行数据*/
FETCH NEXT FROM book_cursor
INTO @bookname, @authorname, @bookprice
 
WHILE @@FETCH_STATUS = 0
BEGIN
   /*打印数据*/
   PRINT '|'+@bookname+'|'+@authorname+'|'+CAST(@bookprice AS char(5))+'|'
   PRINT '-----------------------------------------------------------------'
   /*提取下一行数据*/
   FETCH NEXT FROM book_cursor
   INTO @bookname, @authorname, @bookprice
END
 
/*关闭和释放游标*/
CLOSE book_cursor
DEALLOCATE book_cursor
GO
7.7 小结
参考答案:
1.可以使用下面的SQL语句:
USE bookdb
GO
SELECT clients.client_name,SUM(orderform.book_number*book.price) AS 总金额
FROM clients,orderform,book
WHERE clients.client_id=orderform.client_id AND orderform.book_id = book.book_id
GROUP BY clients.client_name
2.可以使用下面的SQL语句:
USE bookdb
GO
SELECT * FROM book
WHERE book_id NOT IN(
     SELECT book_id
     FROM orderform)
3.使用UNION组合两个结果。