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��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提供了关连式资料库的查询语言 SQL(Structured Query Language), 是一种非常口语化、既易学又易懂的语法。此一语言几乎是每个资料库系统都必须提供的,用以表示关连式的操作,包含了资料的定义(DDL)以及资料的处理(DML)。SQL原来拼成 SEQUEL,这语言的原型以“系统 R“的名 字在 IBM圣荷西实验室完成,经过 IBM内部及其他的许多使用性及效率测试,其结果相当令人满意,并决定在系统R的技术基础发展出来 IBM的产品。而且美国国家标准学会(ANSI)及国际标准化组织(ISO 在 1987遵循一个几乎 是以IBM SQL为基础的标准关连式资料语言定义。
资料定义 DDL(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
SQL 的资料形态 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
包含了 年、月、日、时、分、秒、千分之一秒.
资料操作 DML (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
说明:删除符合条件的资料