一、SQL发展历史
SQL是结构化查询语言(Structrue Query Language)的英文缩写,它上使用关系模型的数据库应用语言,由IBM在70年代开发出来,作为IBM关系数据库原型System R的原型关系语言,实现了关系数据库中的信息检索。
80年代初,美国国家标准局(ANSI)开始着手制定SQL标准,最早的ANSI标准于1986年完成,它也被叫做SQL-86。标准的出台使SQL作为标准的关系数据库语言的地位得到加强。SQL标准几经修改和完善,目前新的SQL标准是1992年制定的SQL-92,它的全名是“International Standard ISO/IEC 9075:1992,Database Language SQL”。
二、数据类型
数据类型是指列、存储过程参数和局部变量数据特征,它决定了数据的存储格式,代表着不同的信息类型。
数据类型可分为系统数据类型和用户定义数据类型两种。
(一)系统数据类型
数据类型名称 定义标识
二进制型 binary[(n)],varbinary[(n)]
字符型 char[(n)],varchar[(n)]
日期时间型 datetime,smalldatetime
整数型 int,smallint,tinyint
精确数值型 decimal,numeric
近似数值型 float,real
货币型 money,smallmoney
位型 bit
时间戳型 timestamp
文本型 text
图像型 image
1、二进制型
二进制数据类型代表二进制数,其最大长度为255字节,二进制数可以是0——9和A——F或a——f的字符组成,二进制数以0x标识开头,其中每两个字符为一组,构成一个字节,如0xf5。
分类:定长二进制数据类型(用binary[(n)]声明)、变长二进制数据类型(用varbinary[(n)]声明)。
[例]:DECLARE @var1 binary(10)
SELECT @var1 '0x10 ff aa'
DECLARE @var2 varbinary(64)
[注]:a、在数据类型定义语句或变量声明时,如果不指定n值,则其长度为1,然而在
CONVERT转换函数中如果不指定n值时,其缺省长度则为30。
b、定长和变长的区别:对于定长的数据类型,不管其输入值的实际长度是多少,
它都占用n字节。如果所赋数据长度超过n时,超出部分将被截断。而变长二进制列的存
储长度是可变的,它为字符串的实际长度,但最大不得超过n所规定的值。当输入数据
长度超过n时,余下部分将被截断。
2、字符类型
在输入字符数据时应将数据引在单引号内。
字符类型也有定长(char[(n)])和变长(varchar[(n)])二种。
3、日期时间类型
分类:datetime和smalldatetime
datetime类型的数据长度为8字节,smalldatetime类型的数据长度为4字节。
4、整数
分类:int、smallint、tinyint
int其长度为4个字节,smallint其长度为2个字节,tinyint其长度为1 字节。
5、精确数值型
分类:decimal[(p[,s])]和numeric[(p[,s])] {p指精度,s指小数位}
6、近似数值型
分类:float[(n)]、real
float数据类型可以存储正、负浮点数,n确定二进制数精度,它可以为1到15。当其精度为1到7之间时,也就等同于real数据类型。
7、货币型
分类:money(占8个字节)、smallmoney(占4个字节)
8、位数据类型
位数据类型用bit关键字声明,其数据有两种取值:0和1。在输入0以外的其它值勤时系统均将它们当作1看待。
9、时间戮数据类型
用timestamp声明
10、文本和图像类型
文本(text )和图像(image)类型是两种可变长度的数据类型。
向text列中插入数据时,应将数据引在单引号内。向image列中插入数据时,应在数据前加0x引导符。
(二)标识符
标识符的命名规则:
1、标识符长度以为1到30字符;
2、标识符的第一个字符必须为字母或_、@、#符号。其中@和#符号具有特殊意义:当标识符开头为@时,表示它是一局部变量;标识符首字符为#时,表示一临时数据库对象,对于表或存储过程,名称开头含一个#号时表示为局部临时对象,含两个##号时表示为全局临时对象。
3、标识符中第一个字符后面的字符可以为字母、数字或#、$、_符号。
4、缺省情况下,标识符内不允许有空格,也不允许使用关键字等作为标识符,但可以使用引号来定义特殊标识符。
(三)运算符
1、算术运算符:+、-、*、/、%(取模);
2、位运算运:&(与)、|(或)、^(异或)、~(求反);
3、比较运算符:<、>、=、>=、<=、<>;
4、字符串运算符:字符串运算符(+)实现字符串之间和连接操作。如'abc'+'123'结果为:'abc123'。
5、运算符的优先级
*括号( );
*取反运算:~;
*乘、除、求模运算:*、/、%;
*异或运算:^;
*与运算:|;
*NOT连接;
*AND连接;
*OR连接。
(四)变量
声明格式:DECLARE @<变量名> <变量类型>[,@<变量名> <变量类型>…];
赋 值:用SELECT语句。
[例]:DECLARE @var1 int, @var2 money
SELECT @var1=100, @var2=$29.95
SELECT @var1, @var2
(五)流控制语句
1、IF…ELSE…语句
格式:
IF 布尔表达式
{SQL语句或语句块}
[ELSE
{SQL语句或语句块}]
2、BENGIN……END语句
将多条SQL语句封装起来,构成一个语句块。
格式:
BEGIN
{SQL语句或语句块}
END
3、GOTO语句
4、WHILE、BREAK、CONTINUE语句
[例] 求1到10之间的奇数据和
DECLARE @i smallint, @sum smallint
SELECT @i=0,@sum=0
WHILE @i>=0
BEGIN
SELECT @i=@i+1
IF @i>10
BREAK
IF (@i % 2)=0
CONTINUE
ELSE
SELECT @sum = @sum + @i
END
SELECT @sum
5、WAITFOR 语句
格式: WAITFOR {DELAY 'time' | TIME 'time'}
time:datetime
[例]: WAITFOR TIME '10:00' 设置在10:00执行一次命令;
WAITFOR DELAY '1:00' 设置在一小时后执行一次命令。
6、RETURN语句
使程序从一个查询或存储过程中无条件地返回,其后面的语句不再执行。
RETURN([整数表达式])
7、CASE表达式
1)、简单CASE表达式
CASE 表达式
WHEN 表达式1 THEN 表达式2
[WHEN 表达式3 THEN 表达式4[……]]
[ELSE 表达式N]
END
[例]:
select name=convert(varchar(15),au_lname),
contract=case contract
when 0 then 'invalid contract.'
when 1 then 'valid contract.'
end
from authors
2)、搜索型CASE表达式
CASE 表达式
WHEN 布尔表达式1 THEN 表达式1
[WHEN 布尔表达式2 THEN 表达式2[……]]
[ELSE 表达式N]
END
[例]:
select name=convert(varchar(15),au_lname),
contract=case contract
when cintract=0 then 'invalid contract.'
when contract<>0 then 'valid contract.'
end
from authors
3)、CASE关系函数
CASE关系函数有以下三种形式:
COALESCE(表达式1,表达式2)
COALESCE(表达式1,表达式2,…,表达式N)
NULLIF(表达式1,表达式2)
二、函数
(一)日期函数
GETDATE ( ) 返回系统当前日期和时间
DATEADD (datepart,number,date) 返回datetime或smalldatetime类型数据,其值为date值加上datepart和number参数指定的时间间隔。
DATEDIFF (datepart,date1,date2) 返回date1和date2间的时间间隔,其单位由datepart参数指定
DATENAME (datepart,date) 返回日期中指定部分对应的字符串
DATEPART (datepart,date) 返回日期中指定部分对应的整数值
DATENAME和DATEPART函数分别返回日期中指定日期元素对应的字符串和整数值。
(二)字符串函数
+ (expression,expression) 连接两个或多个字符串、二进制串、列名
ASCII (char_expr) 返回表达式中最左边一个字符的ASCII值
CHAR (integer_expr) 返回整数所代表的ASCII值对应的字符
LOWER (char_expr) 将大写字符转换为小写字符
UPPER (char_expr) 将小写字符转换为大写字符
LTRIM (char_expr) 删除字符串开始部分的空格
RTRIM (char_expr) 删除字符串尾部的空格
RIGHT (char_expr,integer_expr) 返回char-expr字符串中integer-expr个字符以后的部分字符串,integer-expr为负时,返回NULL
SPACE (integer_expr) 返回一个由空格组成的字符串,空格等于integer-expr值,integer-expr为负时,返回NULL
STR (float_expr[,length[,decima]]) 将一个数值型数据转换为字符串,length为字符串的长度,decimal为小数点的位数
STUFF (char_expr1,start,length,char_expr2) 从char-exprl字符的star个字符位置处删除length个字符,然后把char-
expr2字符串插入到char-exprl的start处
SUBSTRING (expression,start,length) 从expression的第start个字符处返回length个字符
REVERSE (char_expr) 返回char-expr的逆序
CHARINDEX ('pattern',expression) 返回指定pattern字符串在表达式中的起始位置
DIFFERENCE (char_expr1,char_expr2) 比较两个字符串,返回它们的相似性,返回值为1--4
PATINDEX ('%prattern%',expression) 返回expression中首次出现pattern的起始位置
REPLICATE (char_expr,integer_expr) 返回一个由char_expr重复intrger_expr次组成的字符串
SOUNDEX (char_expr) 返回一个四代码,说明字符串读音的相似性
(三)数学函数
ASIN、ACOS、ATAN (float_expr) 求float_expr的反正弦、反余弦、反正切
ATN2 (float_expr1,float_expr2) 求float_expr1/float_expr2的反正切
SIN、COS、TAN、COT (float_expr) 求float_expr的正弦、余弦、正切
DEGREES (numeric_expr) 将弧度转换为度
RADIANS (numeric_expr) 将度转换为弧度
EXP (float_expr) 求float_expr的指数值
POWER (numeric_expr,y) 求numberic_expr的y次方
SQRT (float_expr) 求float_expr的平方根
LOG (float_expr) 求float_expr的自然对数
LOG10 (float_expr) 求float_expr以10为底的对数
ABS (numeric_expr) 求numeric_expr的绝对值
CEILING (numeric_expr) 返回大于等于numberic_expr的最小整数
FLOOR (numeric_expr) 返回小于等于numberic_expr的最大整数
RAND ([seed]) 返回0到1之间的随机浮点数,可以使用整数表达式指定其初值
PI ( ) 返回常数3.141492653589793
ROUND (numeric_expr,length) 将numberic_expr小数点后的值四舍五入,保留的小数位数为length
SIGN (numeric_expr) numberic_expr的值为正数、0、或负数时分别返回1、0、-1数值
(四)集合函数
COUNT ([ALL|DISTICT]expression) 计算表达式中非空值的数量,可用于数字型列,使用DISTICT时删除重复值
COUNT ( * ) 计算所有行数,包括空值行,对COUNT(*)不能使用DISTINCT关键字
MIN (expression) 计算表达式最小值,可用于数字开型、字符型和日期时间型列,但不能用于bit、text、image列,MIN函数忽略表达式中的空值
MAX (expression) 计算表达式最大值,可用于数字开型、字符型和日期时间型列,但不能用于bit、text、image列,MIN函数忽略表达式中的空值
SUM ([ALL|DISTICT]expression) 计算表达式所有值的和,它忽略表达式中的空值,使用DISTINCT关键字时删除表达式中的重复值,它适用于数字型列
AVG ([ALL|DISTICT]expression) 计算表达式的平均值,它忽略空值,使用DISTINCT关键字时删除表达式中的重复值,适用于数字型列
(五)文本和图像函数
TEXTPTR (column_name) 以varbinary数据类型返回指向text或image列首页数据的指针,如果还未使用insert或update语句初始化text或image列时,返回null
TEXTVALID ('table_name.column_name',text_ptr) 检查指向table_name.column_name的text_ptr指针的有效性,如果指针有效则返回1,否则返回0
(六)转换函数
CONVERT (datatype[(length)],expression[,style])
参数expression转换后的数据类型,它只能为系统数据类型,当datatype参数为char、varchar、binary或varbinary数据类型时,length说明转换后数据的长度。length的最大值为255,缺省长度为30。
将datetime或smalldatetime数据类型转换为字符数据时,用style参数说明转换后的字符串格式。
三、表、视图与索引
(一)表
数据表可分为永久表和临时表两种,临时表在用户退出或系统恢复时被自动删除。临时表又分为局部临时表和全局临时表两种,在创建表时,系统根据表名来确定是临时表还是永久表,临时表的表名开头包含两个#。表名的最大长度(包括#在内)为20个字符。
1、建立数据表
使用CREATE TABLE语句建立表,其格式为:
CREATE TABLE [database.[owner.]]table_name
(
{col_name datatype [NULL | NOT NULL | IDENTITY[(seed,increment)]]
[constraint [constraint […constraint]]] | [[,] constraint]}
[[,]{next_col_name | next_constraint}…]
)[ON segment_name]
database指定所建表的存放位置,缺省时为当前数据库。
owner指定表所有者,缺省时为当前用户。
table_name是新建表的名称。
col_name定义表的列名,在一个表中,列名必须唯一,但在同一个数据库的不同表中列名可以相同。
datatype指定列的数据类型。
IDENTITY指定该列为IDENTITY列,其列值由系统自动插入。每个表中能有一个IDENTITY列,该列值不能由用户更新,也不允许空值。IDENTITY列的数据类型只能为int,smallint,tinyint,numeric,decimal等系统数据类型,IDENTITY列数据类型为numeric和decimal时,不允许出现小数位。对于IDENTITY列,seed为IDENTITY列的基值
,increment为IDENTITY列的列值增量。缺省时,seed和increment的值均为1。
[例]:
CREATE TABLE person
(
person_id INT IDENTITY(1,10),
name CHAR(8) NOT NULL
)
2、修改表
使用ALTER TABLE语句可以修改表结构,为其添加列,或打开、关闭已有约束,增加、删除约束等操作。
ALTER TABLE语句格式为:
ALTER TABLE [database.[owner]]table_name
[WITH {CHECK | NOCHECK}]
{{ CHECK | NOCHECK}CONSTRAINT {constraint_name | ALL}
|
[ADD
{column_definition [column_constraints] | [[,] table_colstraint]}
[,{column_definition [column_constraints] | [[,] table_colstraint]}……]
|
[DROP CONSTRAINT]
constraint_name [,constraint_name2]……]}
ADD项参数说明向表中增加列或表约束,其中列定义与CREATE TABLE语句中的列定义方法相同。
DROP项说明删除表中现有约束。
[例]
ALTER TABLE person
ADD
country char(2) NULL
3、删除表
DROP TABLE语句的格式为:
DROP TABLE [[database.]owner.]table_name
[,[[database.]owner.]table_name……]
[例]:DROP TABLE person
(二)表数据操作
1、添加数据
格式:
INSERT [INTO]
{table_name | view_name} {(column_list)]
{
DEFAULT VALUES | values_list | select_statement |
EXECute {procedure_name | @procedure_name_var}
[[procedure_name=] {value | @variable [OUTPUT] | DEFAULT}
[, [[procedure_name=] {value | @variable [OUTPUT] | DEFAULT}]……]
column_list列出要添加数据的列名。在给表或视图中部分列添加数据时,必须使用该选项说明这部分列名。
DEFAULT VALUES 说明向表中所有列插入其缺省值。对于具有INDENTITY属性或timestamp数据类型的列,系统将自动插入下一个适当值。对于没有设置缺省值的列,根据它们是否允许空值,将插入null或返回一错误消息。
values_list的格式为:
VALUES (DEFAULT | constant_expression
[,DEFAULT | constant_expression]……)
[例1]:
INSERT publishers
VALUES('9900','DELPHI','Beijing',null,'china')
[例2]:
INSERT publishers(pub_id,pub_name,contry,city)
VALUES('9900','DELPHI','china','Beijing')
[例3]:假定有两个表tab1和tab2,它们列的排列顺序分别为:col1,col2,col3和col1,col3,col2。这时,可使用下面两种方法来实现数据拷贝:
INSERT tab1(col1,col3,col2)
SELECT * FROM tab2
或
INSERT tabl
SELECT col1,col2,col3 FROM tab2
2、修改数据
UPDATE {table_name | view_name}
SET
[column_name = {column_list1 | variable_list1 | variable_and_column_list1}
[[,column_name = {column_list2 | variable_list2 | variable_and_column_list2}
……
[,column_name = {column_listn | variable_listn | variable_and_column_listn
}]]
| expression]
[WHERE clause]
*SET子句指定被修改的列名及其新值,WHERE子句说明修改条件,指出表或视图中的哪
些行需要修改。
[例1]:使用SET子句将discounts表中所有行的discounts值增加0.1:
UPDATE discounts
SET discount=discount+0.1
[例2]:同时修改discounts表中折扣类型为volume discount的lowqty可doscount列值:
UPDATE discounts
SET discount=discount+0.5 , lowqty=lowqty+200
WHERE discounttype='volume discount'
3、删除数据
DELETE和TRUNCATE TABLE语句都可以用来删除表中的数据,DELETE语句的格式为:
DELETE [FROM] {table_name | view_name}
[WHERE clause]
TRUNCATE TABLE语句的格式为:
TRUNCATE TABLE [ [database.]owner.]table_name
*TRUNCATE TABLE语句删除指定表中的所有数据行,但表结构及其所有索引继续保留,
为该表所定义的约束、规则、缺省和触发器仍然有效。如果所删除表中包含有
IDENTITY列,则该列将复位到它的原始基值。使用不带WHERE子句的DELETE语句也可以
删除表中所有行,但它不复位IDENTITY列。
*TRUNCATE TABLE不能删除一个被其它表通过FOREIGN KEY约束所参照的表。
[例1]:
DELETE discounts
TRUNCATE TABLE discounts
[例2]:
DELETE titles
WHERE type='business'
[例3]:
DELETE titles
WHERE title_id IN
(SELECT titleauthor.title_id FROM authors,titles,titleauthor
WHERE authors.au_id=titleauthor.au_id
AND titleauthor.title_id=titles.title_id
AND city='gary')
(三)索引
用CREATE INDEX语句创建索引,其格式为:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [[database.]owner]table_name (column_name[,column_name]……)
[WITH
[PAD_INDEX,]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] SORTED_DATA | SORTED_DATA_REORG]
[[,] IGNORE_DUP_ROW | ALLOW_DUP_ROW]]
[ON segment_name]
1、唯一索引:在调用CREATE INDEX语句时,使用UNIQUE选项创建唯一索引。
2、复合索引:是对一个表中的两列或多列的组合值进行索引,复合索引的最在列数为16,这些列必须在同一个表中。
[例]:CREATE INDEX ta ON titleauthor(au_id,title_id)
3、簇索引(排序):在调用CREATE INDEX语句时,使用CLUSTERED选项创建簇索引。
4、非簇索引:在调用CREATE INDEX语句时,使用NONCLUSTERED选项创建簇索引。
(四)视图
1、建立视图
格式:
CREATE VIEW [owner.]view_name
[(column_name [, column_name]……)]
[WITH ENCRYPTION]
AS select_statment [WITH CHECK OPTION]
四、查询
用SELECT语句实现数据库的查询操作。同时,它还可以使用各种子句对查询结果进行分组统计、合计、排序等操作。SELECT语句还可将查询结果生成另一个表(临时表或永久表)。
SELECT语句的语法格式为:
SELECT [ALL | DISTINCT] select_list
[INTO [new_table_name]]
[FROM {table_name | view_name} [(optimizer_hints)]
[[,table_name2 | view_name2} [(optimizer_hints)]
……
[,table_name16 | view_name16} [(optimizer_hints)]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]
在SELECT语句中,子句可以省略,但在列出时必须按照以上顺序。
(一)简单查询
简单的SQL查询只包括SELECT列表、FROM子句和WHERE子句,它们分别说明所查询列、查询操作的表或视图、以及搜索条件等。
[例]:查询title表中1991年所出版的'business'类图书及其价格。
SELECT title,price
FROM titles
WHERE DATEPART(YEAR,pubdate) = 1991 AND type = 'business'
1、SELECT列表语句
SELECT列表语句(select_list)指定所选择的列,它可以为一组列名列表、星号、表达式、变量(包括局间变量和全局变量)等构成。
a、选择所有列
用星号表示选择指定表或视图中的所有列。
SELECT *
FROM discounts
b、选择指定列并指定它们的显示次序
在SELECT列表语句中指定列名来选择不同的列,各列之间用逗号分隔,显示结果中数据的排列顺序为列名的列表顺序。
SELECT discount,discounttype
FROM discounts
在SELECT列表中,还可以对数值列进行算术运算(包括加、减、乘、除、取模等)。其中,加、减、乘、除操作适用于任何数值型列(如int,smallint,tinyint,decimal,numeric,float,real,money,smallmoney等),而取模操作不能用于money和smallmoneyu数据类型列。
对数值列进行运算时,如果列值为空(null),则所执行的所有算术操作的结果仍为空。
SELECT discounttype,'lowqty+50'=lowqty+50
FROM discounts
在SELECT列表中,还可以指定字符串常量或变量改变输出结果的显示格式。
DECLARE @var char(12)
SELECT @var='折扣幅度'
SELECT '折扣名称:',discounttype,@var,discount
SELECT discounts
c、删除重复行
SELECT语句中可以使用ALL | DISTINCT 选项来显示所有行(ALL)或删除重复的行(DISTINCT),缺省时为ALL。使用DISTINCT选项时,对于所有数据重复的SELECT列表值只显示一次。
SELECT DISTINCT country FROM publishers
2、FROM子句
FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定16个表或视图,它们相互之间用逗号分隔,如果这些表或视图属于不同的数据库,可用“数据库.所有者名称.对象”格式限定表或视图对象。
SELECT au_id,titles.title_id
//由于在两个表中都存在“title_id”列,加入“titles.”避免二义性
FROM titles,titleauthor
WHERE titles.title_id = titleauthor.title_id
在FROM子句中可为每个表或视图指定一个别名,别名紧跟在对象名称之后,之间用空格分隔,然后可以使用别名引用表中各列。
SELECT au_id,t.title_id
FROM titles t,titleauthor ta
WHERE t.title_id = ta.title_id
3、全用WHERE子句限定搜索条件
SELECT语句中使用WHERE子句指定查询条件。
WHERE语句中可包含的运算符:
比较运算符 >、>=、=、<=、<、<>、!>、!< 大小比较
范围运算符 BETWEEN...AND... 判断表达式值是否在指定范围之内,NOT BETWEEN...AND...
列表运算符 IN 判断表达式值是否为列表中的指定项,NOT IN
模式匹配符 LIKE 判断列值是否与指定的字符通配格式相符,NOT LIKE
空值判断符 IS NULL 判断表达式值是否为空,NOT IS NULL
逻辑运算符 AND 用于多条件的逻辑连接,OR,NOT
[例1]范围运算符:查询书价为$10~$30之内的图书
SELECT title,price
FROM titles
WHERE price BETWEEN $10 AND $30
[例2]列表运算符:列出publishers表中社址在德国和法国的出版社
SELECT pub_name
FROM publishers
WHERE country IN ('germany','france')
模式匹配符:
模式匹配符[NOT] LIKE常用于模糊条件查询,它判断列值是否与指定的字符串格式相匹配,可用于char,varchar,datetime和smalldatetime数据类型。可使用的通配字符有以下几种:
百分号 %:可匹配任意类型长度的字符;
下划线 _:匹配单个任意字符,它常用来限制表达式的字符长度;
方括号 []:指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个字符。
[^]:其取值与[]相同,但它要求所匹配对象为指定字符以外的任一个字符。
[例3]:查找名称以“publishing”字符串结尾的出版社
SELECT pub_name
FROM publishers
WHERE pub_name LIKE '%publishing'
[例4]查找名称以A——F字符开头的出版社
SELECT pub_name
FROM publishers
WHERER pub_name LIKE '[A-F]%'
[例5]查找名称以A——F以外字符开头的出版社
SELECT pub_name
FROM publishers
WHERER pub_name LIKE '[^A-F]%'
[例6]列出名称长度为个5个字符,且以‘GG’开头的出版社
SELECT pub_name
FROM publishers
WHERER pub_name LIKE 'GG___'
[例7]空值判断符:查找目前仍未定价的图书
SELECT tltle,price
FROM tltles
WHERE price IS NULL
[例8]逻辑运算符:列出收价低于$5或高于$15、且当年销量小于5000的图书
SELECT price,ytd_sales,title
FROM titles
WHERE (price < $5 OR price>$15) AND ytd_sales<5000
4、查询结果排序
在SELECT语句中,使用ORDER BY子句对查询结果按一列或多列进行排序。ORDERBY子句的语法格式为:
ORDER BY {{table_name. | view_name.}colcumn_name
| select_list_number | expression} [ASC | DESC]
[……{{table_name16. | view_name16.}colcumn_name
| select_list_number | expression} [ASC | DESC]]
[例] 列出“business”类的图书标识和价格,结果排序方式为:价格由高到低、title_id列由低到高
SELECT title_id ,price
FROM titles
WHERE type='business'
ORDER BY price DESC,title_id, ASC
(二)统计
在SELECT 语句中,可以使用集合函数、行集合函数、GROUP BY子句和COMPUTE子句对查询结果进行统计。GROUP BY子句可与行集合函数或集合函数一起使用,而COMPUTE子句只能与行集合函数一起使用。
在SELECT语句中,也可以单纯使用集合函数进行统计,这时它将所有符合条件的数据统计到一起,形成一行统计数据,这种统计方法叫做标量统计。
[例] 统计“business”类图书的平均价格:
SELECT 'average price'=AVG(price)
FROM titles
WHERE type='business'
1、GROUP BY子句
GROUP BY子句的语法格式为:
GROUP BY [ALL] aggregate_free_expression
[,aggregate_free_expression]……
[例] 统计每类图书的平均价格
SELECT type,AVG(price) 'average price'
FROM titles
GROUP BY type
a、HAVING子句
在使用GROUP BY子句时,还可以用HAVING子句为分组统计进一步设置统计条件,HAVING子句与GROUP BY子句的关系和WHERE子句与SELECT子句的关系类似。
HAVING子句可以参照选择列表中的任一项,在HAVING子句中还可以使用逻辑运算符连接多个条件,最多为128个。
[例] 按图书类别分组统计出未指定类型以外的其它图书的平均价格
SELECT type,AVG(price) 'average price'
FROM titles
GROUP BY type
HAVING type<>'UNDECIDED'
在HAVING子句中可以使用集合函数。
[例] 按图书类别分组统计图书的平均价格,但排除那些只包括一种图书的图书类别
SELECT type,AVG(price) 'average price'
FROM titles
GROUP BY type
HAVING COUNT(*)<>1
b、统计结果排序
[例] 按图书类别统计其平均价格,并按平均价格进行排序
SELECT type,AVG(price) 'average price'
FROM titles
GROUP BY type
HAVING type<>'UNDECIDED'
ORDER BY AVG(price) DESC
c、GROUP BY子句中的ALL选项
在SELECT语句中同时使用GROUP BY和WHERE子句进行分组统计时,在统计列表中只列出符合WHERE子句所定条件的数据项。
如果在GROUP BY子句中使用ALL选项,则在统计列表中将列出被统计表中所有的分组,即使是不符合WHERE子句指定条件的分组也将列出,但并不对这些分组进行统计。
[例]:
SELECT type,AVG(price) 'average price'
FROM titles
WHERE type<>'business'
GROUP BY ALL type
结果列出了'business'类图书,但它并不说明'business'类图书的平均价格为空,而是该类图书不符合统计条件。
d、使用GROUP BY 子句应注意的事项
在GROUP BY子句中不能使用集合函数。
必须在GROUP BY子句中列出SELECT选择列表中所有的非集合项。
[例] 下面语句的GROUP BY子句中必须全部列出type,pub_id列,缺一不可:
SELECT type,pub_id,AVG(price) 'average price'
FROM titles
GROUP BY type,pub_id
2、COMPUTE子句
它不仅显示统计结果,而且还显示统计数据的细节。
COMPUTE子句的语法格式为:
COMPUTE row_aggreggate(column_name)
[,row_aggregate(column_name)……]
[BY column_name[,column_name]……]
在COMPUTE子句中使用BY选项可以对数据进行分组统计。
在使用COMPUTE子句时,必须遵守以下规则:
*在集合函数中不能使用DISTINCT关键字。
*COMPUTE BY子句必须与ORDER BY子句同时联合使用,并且COMPUTE BY子句中BY后的列名列表必须与ORDER BY子句中的相同,或为其子集,且二者从左到右的排列顺序必须一致。
*COMPUTE子句中不使用BY选项时,统计出来的为合计值。
(三)利用查询结果创建新表
SELECT语句中使用INTO选项可以将查询结果写进新表中,新表结构与SELECT语句选择列表中的字段结构相同。
[例]
SELECT * INTO #new_publishers
FROM publishers
WHERE country='USA'
(四)使用UNION运算符实现多查询联合
UNION运算符可以将两个或两个以上的查询结果合并成一个结果集合显示。UNION运算符的句法格式为:
查询1[UNION [ALL] 查询N]……
[ORDER BY子句]
[COMPUTE子句]
其中,查询1至查询N的格式为:
SELECT select_list
[INTO 子句]
[FROM子句]
[WHERE子句]
[GROUP BY子句]
[HAVING子句]
[例] 将titles表和discounts表的两个查询结果合并到一起。
SELECT name=discounttype,value=discount
FROM discounts
UNION
SELECT title,ytd_sales
FROM titles
WHERE title LIKE 'T%'
联合查询时,查询结果的列标题为第一个查询语句中的列标题,因此要定义列标题时必须在第一个查询语句中定义。要对联合查询结果排序时,也必须使用第一查询语句中的列名。
SELECT name=discounttype,value=discount
FROM discounts
UNION
SELECT title,ytd_sales
FROM titles
WHERE title LIKE 'T%'
ORDER BY value
(五)连接
1、等值连接和自然连接
对于等值连接和自然连接,在WHERE子句中使用等于比较运算符,二者的区别在于等值连接的查询结果中列出所连接表中的所有列,包括它们之间的重复列。而自然连接的选择列表中删除被连接表间的重复列。
[例]
等值连接:
SELECT *
FROM authors,titleauthor
WHERE authors.au_id=titleauthor.au_id
自然连接:
SELECT a.*,ta.title_id,ta.au_ord,ta.royaltyper
FROM authors a,titleauthor ta
WHERE a.au_id=ta.au_id
2、不等连接
不等连接使用除等于运算符以外的其它比较运算符。这些运算符包括>、>=、<=、<、!>、!<和 <>等。
3、自连接
自连接中,使用同一个表的相同列进行比较,这时,对于同一个表应给出不同的别名。
[例] 使用自连接列出合著的图书标识及其作者姓名
SELECT DISTINCT t1.title_id,author=CONVERT(char(8),au_fname)+''+CONVERT(char(15),au_lname)
FROM authors a,titleauthor t1,titleauthor t2
WHERE t1.title_id = t2.title_id
AND t1.au_id<>t2.au_id
AND t1.au_id=a.au_id
4、外连接
Ø连接中,查询结果中所显示的仅是符合查询条件的行,而采用外连接时,它不仅包含符合连接条件的行,而且还包括左表或右表连接中的所有行。
外连接操作符有*=和*=两种,采用*=连接时,查询结果中将包含第一个表中的所有行,而采用=*连接时,查询结果将包含=*操作符后面表中的所有数据行。
在进行一些统计时,需要使用外连接。例如,假定有两个表,一个表(PERSON)包含人员的姓名(NAME)及其标识(ID),另一个表(DESC)包含人员标识(ID)及其受奖惩情况(MEMO)。在统计单位所有人员及其奖惩情况时,使用外连接书写查询语句就特别简单。即:
SELECT name,memo
FROM person,desc
WHERE person.id*=desc.id
(六)子查询
子查询是嵌套在SELECT、INSERT、UPDATE和DELETE语句的WHERE子句和HAVING子句中的SELECT语句,它也可以嵌套在另一个子查询中。SELECT语句中子查询的语法格式为:
SELECT [ALL | DISTINCT] subquery_select_list
[FROM {table_name | view_name } [optimizer_hints]
[[,{table_name2 | view_name2 } [optimizer_hints]
[……,{table_name16 | view_name16 } [optimizer_hints]
[WHERER clause]
[GROUP BY clause]
[HAVING clause]
1、[NOT] IN 子查询
这种语句的执行分两个步骤:首先执行内部子查询,然后根据子查询的结果再执行外层查询。
SELECT title=CONVERT(char(25),title)
FROM titles
WHERE title_id IN
(SELECT title_id
FROM titleeauthor
WHERE au_id LIKE '99%')
2、[NOT] EXISTS子查询
它返回逻辑值TRUE或FALSE,并不产生其它任何实际值。所以这种子查询的选择列表常用‘SELECT *’格式。
SELECT title=CONVERT(char(25),title)
FROM titles
WHERE title_id EXISTS
(SELECT title_id
FROM titleeauthor
WHERE title_id=titles.title_id AND au_id LIKE '99%')
3、由比较运算符引出的子查询
在使用单一比较操作符引出子查询时,必须保证子查询返回一个单值,否则将引起查询错误。比较运算符与ALL或ANY修饰符连用时,允许子查询返回多个值。
SELECT title
FROM titles
WHERE price>(SELECT AVG(price) FROM titles)
SELECT title
FROM titles
WHERE price>ALL
(SELECT price FROM titles WHERE type='business')