SQL语句详析

SQL语句详析

1.   CREATE DATABASE database_name [WITH LOG IN “pathname”]

创建数据库。

database_name：数据库名称。

“pathname”：事务处理日志文件。

创建一database_name.dbs目录，存取权限由GRANT设定，无日志文件就不能使用

BEGINWORK等事务语句（可用START DATABASE语句来改变）。

可选定当前数据库的日志文件。

如：select dirpath form systables where tabtype = “L”;

例：create databse customerdb with log in “/usr/john/log/customer.log”;

1            DATABASEdatabse-name [EXCLUSIVE]

选择数据库。

database_name：数据库名称。

EXCLUSIVE：独占状态。

存取当前目录和DBPATH中指定的目录下的数据库，事务中处理过程中不要使用此语句。

例：dtabase customerdb;

3.   CLOSE DATABASE

关闭当前数据库。

database_name：数据库名称。

此语句之后，只有下列语句合法：

CREATEDATABASE； DATABASE； DROPDATABSE； ROLLFORWARD DATABASE；

删除数据库前必须使用此语句。

例：close database;

4.   DROP DATABASE database_name

删除指定数据库。

database_name：数据库名称。

用户是DBA或所有表的拥有者；删除所有文件，但不包括数据库目录；不允许删除当前数据库（须先关闭当前数据库）；事务中处理过程中不能使用此语句，通过ROLLBACK WORK也不可将数据库恢复。

例：drop databse customerdb;

5.   CREATE [TEMP] TABLE table-name(column_name datatype [NOTNULL], …)

[IN“pathname”]

创建表或临时表。

table-name：表名称。

column_name：字段名称。

data-type：字段数据类型。

path-name：指定表的存放位置

TEMP用于指定建立临时表；表名要唯一，字段要唯一；有CONNECT权限的用户可建立临时表；创建的表缺省允许CONNECT用户存取，但不可以ALTER。

例：create table user

(c0 serial not null, c1 char (10),

c2char(2),

c3smallint,

c4decimal(6,3),

c5date

)in “usr/john/customer.dbs/user;

6.   ALTER TABLE

ALTERTABLE table-name

{ADD(newcol_name newcol_type [BEFORE oldcol_name],…) | DROP (oldcol_name,…)

|MODIFY (oldcol_name newcol_type [NOT NULL], … )}, …

修改表结构。

table-name：表名称。

newcol_name：新字段名称

newcol_type：新字段类型

oldcol_name：老字段名称

可以使用单个或多个ADD子句、DROP子句、MODIFY子句，但某个字句失败，操作即中止；原字段是NULL，不允许MODIFY为NOTNULL，除非所有NULL字段中均非空，反之可以；ALTER使用者是表的拥有者或拥有DBA权限，或被授权；事务中处理过程中不要使用此语句。

例：alter table user

add( c6 char(20) before c5);

7.   RENAME TABLE oldname TO newname

修改表名。

oldname：原名称。

newname：新名称。

RENAME使用者是表的拥有者或拥有DBA权限，或被授权；事务中处理过程中不要使用此语句。

例：rename user to bbb;

8.   DROP TABLE table-name

删除表。

table-name：表名称。

删除表意味着删除其中所有数据、各字段上的索引及对表的赋权、视图等；用户不能删除任何系统目录表；语句使用者是表拥有者或拥有DBA权限，事务中处理过程中不要使用此语句。

9.   RENAME COLUMN table.oldcolumn， TO newcolumn

修改字段名。

table.oldcolumn：表名及原字段名称

newcolumn：新字段名称。

语句使用者是表的拥有者或拥有DBA权限或有ALTER权限的用户，事务中处理过程中不要使用此语句。

例：rename column user.c6 toc7;

10.CREATEVIEW view-name column-list

CREATEVIEW view-name column-listAS select_statement [WITH CHECK OPTION]

创建视图。

view-name：视图名称。

column-list：字段列表。

select_statement：SELECT语句。

以下语句不使用视图：ALTER TABLE，DROP INDEX，ALTER INDEX，LOCK TABLE，CREATE INDEX， RENAME TABLE；视图将延用基表的字段名，对表达式等虚字段和多表间字段重名必须指明标识其字段名；若对视图中某些字段命名，则所有字段都必须命名；视图中数据类型延用基表中的数据类型，虚字段起诀于表达式；不能使用ORDER BY和UNION子句；对视图中所有的字段要有SELECT权限；事务中处理过程中使用此语句，即使事务回滚，视图也将建立，不能恢复。

例：create view v_user as select * from user where c1 = “B1”;

11.DROPVIEW view-name

删除视图。

view-name：视图名称。

用户可删除自己建立的视图；视图的后代视图也被删除；事务中处理中不要使用此语句。

例：drop view v_user;

12.CREATEINDEX

CREATE[UNIQUE/DISTINCT] [CLUSTER] INDEX index_nameON table_name

([column_name ASC/DESC],…)

创建索引。

index_name：索引名称。

table_name：表名称。

column_name：字段名称。

UNIQUE/DISTINCT：唯一索引。

CLUSTER：使表的物理存放顺序按索引排列。

ASC/DESC：升序或降序，缺省升序。

语句执行时，将表的状态置为EXCLUSIVE；复合索引最多包含8个字段，所有字段长度和不得大于120字节；事务中处理过程中使用此语句，即使事务回滚，索引将建立，不能恢复。

例：create cluster index ix_user on user(c5);

13.ALTERINDEX index-name TO [NOT] CLUSTER

修改索引性质。

index-name：索引名称。

TO[NOT] CLUSTER：去掉或加上CLUSTER属性。

语句执行时，将表的状态置为EXCLUSIVE；事务中处理过程中使用此语句，即使事务回滚，索引性质将改变，不能恢复。

例：alter index ix_user to not cluster;

14.DROPINDEX index-name

删除索引。

index-name：索引名称。

语句使用者是索引的拥有者或拥有DBA权限，事务中处理过程中不要使用此语句，否则事务无法恢复。

例：drop index ix_user;

15.CREATESYNONYM synonym FOR table-name

创建同义名。

synonym：同义名

table-name：表名称

数据库的创建者可以使用同义名；没有赋予同义名权限的用户不能使用同义名；同义名不能和表名相同；事务中处理过程中不要使用此语句。

例：create synonym user_alias for user;

16.DROPSYNONYM synonym

删除同义名。

synonym：同义名

可以删除自己建立的同义名；事务中处理过程中不要使用此语句，否则无法恢复。

例：drop synonym user_alias;

17.UPDATESTATISTICS [FOR TABLE table-name]

更新数据库的统计数字。

table-name：表名称

此语句仅作用于当前数据库；可提高查询效率；只有执行此语句，才改变统计数据。

例：update statistics for table user;

主要SQL语句详解(2)

1            GRANT{DBA|RESOURCE|CONNECT} TO {PUBLIC|user-list}

授权命令。

PUBLIC|user-list：全部或指定的用户。

三种权限居且仅居其一，事务处理过程中不要执行GRANT语句。

例：grant resource to pulbic;

GRANT tab-privilege ON table-name TO {PUBLIC|user-list}[WITH GRANT OPTION]

授表级权限。

tab-privilege：表级权限。

table-name：表名称。

PUBLIC|user-list：全部或指定的用户。

[WITH GRANTOPTION]：表示被授权用户有否权限进行二次授权。

用户可以在自己建立表达式或被[WITH GRANT OPTION]准许的表中进行赋权；限定越多的权限优先级越高。

例：grant update(c1,c6) on user to dick with grant option;

附（INFORMIX的权限）

(1)数据库的权限（控制对数据库的访问以及数据库中表的创建和删除）

DBA权限：全部权利，修改系统表，建立和删除表与索引、增加和恢复表数据，以及授予其他用户数据库权限等；

RESOURCE权限：允许对数据库表中的数据进行存取，建立永久性表以及索引。

CONNECT权限：只允许对数据库表中的数据进行存取，建立和删除视图与临时表。

(2)表级权限（对表的建立、修改、检索和更新等权限）

ALTER：更改权限

DELETE：删除权限

INDEX：索引权限

INSERT：插入权限

SELECT [(cols)]：指定字段或所有字段上的查询权限，不指明字段缺省为所有字段。

UPDATE [(cols)]：指定字段或所有字段上的更新权限，不指明字段缺省为所有字段。

ALL[PRIVILEGES]：以上所有表级权限

2            REVOKE{DBA|RESOURCE|CONNECT} FROM {PUBLIC|user-list}

收权命令。

PUBLIC|user-list：全部或指定的用户。

三种权限居且仅居其一，事务处理过程中不要执行GRANT语句。

例：revoke resource from john;

REVOKEtab-privilege ON table-name FROM {PUBLIC|user-list}

收表级权限。

tab-privilege：表级权限。

table-name：表名称。

PUBLIC|user-list：全部或指定的用户。

[WITHGRANT OPTION]：表示被授权用户有否权限进行二次授权。

用户只能取消由其本人赋予其他用户的表级存取权限；不能取消自己的权限，对SELECT和UPDATE作取消时，将取消所有表中字段的SELECT和UPDATE权限。

例；revoke update on user from dick;

3            LOCKTABLE table-name IN {SHARE|EXCLUSIVE}MODE

记录级加锁和表级加锁或文件加锁。

table-name：表名称。

SHARE：允许读表中数据，但不允许作任何修改

EXCLUSIVE：禁止其他任何形式访问表

每次只能对表琐定一次；事务处理过程中，BEGIN WORK后立即执行LOCK TABLE以取代记录级加锁，COMMIT WORK和ROLLBACK WORK语句取消所有对表的加锁；若没有事务处理，锁将保持到用户退出或执行UNLOCK为止。

例：lock table user in exclusive mode;

4            UNLOCKTABLE table-name

取消记录级加锁和表级加锁或文件加锁。

table-name：表名称。

例：unlock user;

5            SETLOCK MODE TO [NOT] WAIT

改变锁定状态。

TO[NOT]：等待解锁，有可能被死锁或不等待并提示错误信息，表示此记录被锁，缺省值。

访问一个EXCLUSIVE状态下的记录，将返回一个错误。

6            STARTDATABSE db_name [WITH LOG IN “pathname”]

启动事务处理。

“pathname”：事务处理日志文件。

执行该语句前，需要先关闭当前数据库。

例；clost database;

startdatabse customer with log in “/usr/john/log/customer.log”;

7            BEGINWORK

开始事务。例：begin work;

8            COMMITWORK

提交（正常结束）事务。例：commit work;

9            ROLLBACKWORK

回滚（非正常结束）事务。例：rollback work;

10        SELECT

SELECT select_list FROM tab_name|view_name

WHERE condition

GROUP BY column_name

HAVING condition

ORDER BY column_list

INTO TEMP table_name

查询语句。

select_list：选择表或*

tab_name：表名称

view_name：视图名称。

condition：查询条件，可使用BETWEEN、IN、LIKE、IS NULL、LIKE、MATCHES、NOT、

AND、OR、=、!=或<>、>、　>=、<=、<、ALL、ANY、SOME

column_name：分组字段名称

condition：群聚条件

column_list：排序字段列表，缺省ASC，可指定DSC；排序时，NULL值小于非零值。

table_name：临时表名称

例：略

附（常用函数）

(1)集合函数：

count(*)、

sum(数据项/表达式)、avg(数据项/表达式)、max(数据项/表达式)、min(数据项/表达式)

count(distinct数据项/表达式)、sum(distinct数据项/表达式)、avg(distinct数据项/表达式)

(2)代数函数和三角函数

HEX(数据项/表达式)、ROUND(数据项/表达式)、TRUNC(数据项/表达式)、

TAN(数据项/表达式)、ABS(数据项/表达式)、MOD(被除数,除数)

(3)统计函数

标准差，stdev()、方差，variance()、范围，rang()

(4)时间函数

DAY(日期/时间表达式)：返回数字型

MONTH(日期/时间表达式)：返回整数

WEEKDAY(日期/时间表达式)：0&#0;&#0;6，0星期天，1星期一；返回整数

YEAR(日期/时间表达式)、返回整数

DATE(非日期表达式)：返回日期型

EXTEND(日期/时间表达式,[第一个至最后一个])：返回指定的整数

MDY(月,日,年)：返回日期型

CURRENT：返回日期型

(5)时间函数

ROUND()，四舍五入。如：ROUND(10.95,position)position进行四舍五入的前一位置

TRUNC()，截取。如：TRUNC(10.95,0)position截取的位置

INFORMIX临时表在下列情况下自动取消：

A.退出数据库访问工具（如DBACCESS）

B.SQL通话结束（DISCONNECT）

C.发出取消表语句

D.退出程序时

11        INSERT

INSERT INTO view_name|table_name [(column_list)] VALUES (value_list)

或 INSERT INTO view_name|table_name[(column_list)] select_statement

插入数据

view_name|table_name：视图名或表名称

column_list：数据项列表。

value_list：值列表

select_statement：查询语句。

例：略

12        DELETEFROM view_name|table_nameWHERE search-conditions

删除语句。

view_name|table_name：视图名或表名称

search-conditions；删除条件

例：略

13        UPDATE

UPDATE view_name|table_nameSET column_1= value_1istWHEREsearch_conditions

或UPDATE view_name|table_nameSETcolumn_1|* = value_1istWHEREsearch_conditions

更新数据语句。

view_name|table_name：表名称或视图表名称

value_1ist：字段值

search_conditions：更新数据的条件

例：略

14        CHECKTABLE table-name

检查索引语句。

语句使用者是表的拥有者或拥有DBA权限；不能对systable使用此语句。

例：略

15        REPAIRTABLE table-name

修复索引。

语句使用者是表的拥有者或拥有DBA权限；不能对systable使用此语句。

例：略

16        LOADFROM “file-name” INSERT INTO table_name [(column_name[,…])]

将文本数据栽入表中。

例：load form “aa.txt”insert into user;

17        UNLOADTO “pathname”

将表中数据卸为文本。

例：unload to “aa.txt”select * from user;

18        INFO

系统信息查询。

INFO TABLES：得到当前数据库上表的名字。

INFO columnsFOR table_name：指定表上的字段信息。

INFO INDEXESFOR table_name：指定表上的索引信息。

INFO[ACCESS|PRIVILEGES] FOR table_name：指定表上的存取权限。

INFO STATUSFOR table_name：指定表的状态信息。

例： info tables;

SQL语法参考手册

DB2提供了关连式资料库的查询语言 ＳＱＬ(Structured Query Language)， 是一种非常口语化、既易学又易懂的语法。此一语言几乎是每个资料库系统都必须提供的，用以表示关连式的操作，包含了资料的定义（ＤＤＬ）以及资料的处理（ＤＭＬ）。SQL原来拼成 SEQUEL，这语言的原型以“系统 R“的名 字在 IBM圣荷西实验室完成，经过 IBM内部及其他的许多使用性及效率测试，其结果相当令人满意，并决定在系统R的技术基础发展出来 IBM的产品。而且美国国家标准学会（ANSI）及国际标准化组织（ISO　在 1987遵循一个几乎 是以IBM SQL为基础的标准关连式资料语言定义。

资料定义 ＤＤＬ（Data Definition Language)

资料定语言是指对资料的格式和形态下定义的语言，他是每个资料库要建立时候 时首先要面对的，举凡资料分哪些表格关系、表格内的有什麽栏位主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。

建表格：

语法：

CREATE TABLE table_name(

column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY],

column2 DATATYPE [NOT NULL],

...）

说明　

DATATYPE --是资料的格式，详见表。

NUT NULL --可不可以允许资料有空的（尚未有资料填入）。

PRIMARY KEY --是本表的主键。

更改表格　

ALTER TABLE table_name

ADD COLUMN column_name DATATYPE

说明　增加一个栏位（没有删除某个栏位的语法）

ALTER TABLE table_name

ADD PRIMARY KEY (column_name)

说明　更改表得的定义把某个栏位设为主键

ALTER TABLE table_name

DROP PRIMARY KEY (column_name)

说明　把主键的定义删除。

建立索引　

CREATE INDEX index_name ON table_name (column_name)

说明　对某个表格的栏位建立索引以增加查询时的速度

删除　

DROP table_name

DROP index_name

资料形态 DATATYPE

ＳＱＬ 的资料形态 DATATYPEs

smallint

16位元的整数

interger

32位元的整数

decimal(p,s)

p精确值和 s大小的十进位整数，精确值 p是指全部有几个数(digits)

大小值 s是指小数点後有几位数。

如果没有特别指定，则系 统会设为 p=5; s=0。

float

32位元的实数

double

64位元的实数

char(n)

n长度的字串，n不能超过 254

varchar(n)

长度不固定且其最大长度为 n的字串，n不能超过 4000

graphic(n)

和 char(n)一样，不过其单位是两个字元double-bytes， n不能超过 127。这个形态是为了支援两个字元长度的字体，例如中文字

vargraphic(n)

可变长度且其最大长度为 n的双字元字串，n不能超过 2000

date

包含了 年份、月份、日期.

time

包含了 小时、分钟、秒.

timestamp

包含了 年、月、日、时、分、秒、千分之一秒.

资料操作 ＤＭＬ （Data Manipulation Language)

资料定义好之後接下来的就是资料的操作。资料的操作不外乎增加资料（insert)、 查询资料（query）、更改资料（update)、删除资料（delete）四种模式，以下分别介绍他们的语法：

增加资料：

INSERT INTO table_name (column1,column2,...) VALUES ( value1,value2, ...)

说明：

1.若没有指定column系统则会按表格内的栏位顺序填入资料

2.栏位的资料形态和所填入的资料必须吻合

3.table_name也可以是景观 view_name

INSERT INTO table_name (column1,column2,...) SELECT columnx,columny,...FROM another_table

说明：也可以经过一个子查询（subquery）把别的表格的资料填入

查询资料：

基本查询

SELECT column1,columns2,... FROM table_name

说明：把table_name 的特定栏位资料全部列出来

SELECT *

FROM table_name

WHERE column1 = xxx

[AND column2 > yyy] [OR column3 <> zzz]

说明：

1.'*'表示全部的栏位都列出来

2.WHERE之後是接条件式，把符合条件的资料列出来

SELECT column1,column2

FROM table_name

ORDER BY column2 [DESC]

说明：

ORDER BY是指定以某个栏位做排序，[DESC]是指从大到小排列，若

没有指明，则是从小到大排列

组合查询

组合查询是指所查询得资料来源并不只有单一的表格，而是联合一个以上的表格才能够得到结果的。

SELECT *

FROM table1,table2

WHERE table1.colum1=table2.column1

说明：

1.查询两个表格中其中 column1值相同的资料

2.当然两个表格相互比较的栏位，其资料形态必须相同

3.一个复杂的查询其动用到的表格可能会很多个

整合性的查询：

SELECT COUNT (*)

FROM table_name

WHERE column_name = xxx

说明：

查询符合条件的资料共有几笔

SELECT SUM(column1)

FROM table_name

说明：

1.计算出总和，所选的栏位必须是可数的数字形态

2.除此以外还有 AVG()是计算平均、MAX()、MIN()

计算最大最小值的整合性查询

SELECT column1,AVG(column2)

FROM table_name

GROUP BY column1

HAVING AVG(column2) > xxx

说明：

1.GROUP BY:以column1为一组计算 column2的平均值

必须和 AVG、SUM等整合性查询的关键字一起使用

2.HAVING :必须和 GROUP BY一起使用作为整合性的限制

复合性的查询

SELECT *

FROM table_name1

WHERE EXISTS (

SELECT *

FROM table_name2

WHERE conditions )

说明：

1.WHERE的 conditions可以是另外一个的 query

2.EXISTS在此是指存在与否

SELECT *

FROM table_name1

WHERE column1 IN (

SELECT column1

FROM table_name2

WHERE conditions )

说明　

1. IN後面接的是一个集合，表示column1存在集合里面

2. SELECT出来的资料形态必须符合 column1

其他查询

SELECT *

FROM table_name1

WHERE column1 LIKE 'x%'

说明：

1.LIKE必须和後面的'x%'相呼应表示以 x为开头的字串

SELECT *

FROM table_name1

WHERE column1 IN ('xxx','yyy',..)

说明　

1. IN後面接的是一个集合，表示column1存在集合里面

SELECT *

FROM table_name1

WHERE column1 BETWEEN xx AND yy

说明　

1. BETWEEN表示 column1的值介於 xx和 yy之间

更改资料：

UPDATE table_name

SET column1='xxx'

WHERE conditoins

说明：

1.更改某个栏位设定其值为'xxx'

2.conditions是所要符合的条件、若没有 WHERE则

整个 table的那个栏位都会全部被更改

删除资料：

DELETE FROM table_name

WHERE conditions

说明：删除符合条件的资料