关闭

SQL语言基础

标签: sql语言tablefloatlistconstraints
2108人阅读 评论(0) 收藏 举报
分类:

一、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、外连接
     &Oslash;连接中,查询结果中所显示的仅是符合查询条件的行,而采用外连接时,它不仅包含符合连接条件的行,而且还包括左表或右表连接中的所有行。
     外连接操作符有*=和*=两种,采用*=连接时,查询结果中将包含第一个表中的所有行,而采用=*连接时,查询结果将包含=*操作符后面表中的所有数据行。
     在进行一些统计时,需要使用外连接。例如,假定有两个表,一个表(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')

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:59492次
    • 积分:953
    • 等级:
    • 排名:千里之外
    • 原创:33篇
    • 转载:7篇
    • 译文:0篇
    • 评论:9条
    文章分类
    最新评论