DB2中SQL基本语句的操作 (2011-10-23 19:09:36)转载▼
标签： it 分类： 数据库
--创建数据库
create database Etp;
--连接数据库
connect to Etp;
--断开连接
disconnect Etp;
--查看当前数据库下有哪些表
list tables;
--建表
create table studentInfo(
stuno char(5) not null,
stuname varchar(8),
stubirth date
);
--查看表结构
describe table studentinfo;
--新增表字段
alter table studentinfo add stutel int;
alter table studentinfo add abc int;
--修改字段类型
alter table studentinfo alter column stutel set data type char(11);
--删除字段
alter table studentinfo drop column abc;
--增加一个非空约束
alter table studentinfo alter column stuname set not null;
--重构表
reorg table studentinfo;
--增加一个唯一约束
alter table studentinfo alter column stutel set not null;
alter table studentinfo add constraint un_stutel unique(stutel);
--添加检查约束
alter table studentinfo add column stuAge int;
alter table studentinfo add constraint ch_stuAge check(stuAge > 0 and stuAge <150);
--添加主键约束
alter table studentinfo add constraint pk_stuno primary key(stuno);
--删除表
drop table studentinfo;
--创建表的同时添加约束方式1
create table studentinfo(
stuNo int not null,
stuName varchar(8) not null,
stuAge int,
stuTel char(8),
constraint pk_stuNo primary key(stuNo),
constraint un_stuName unique(stuName),
constraint ch_stuAge check(stuAge >=0 and stuAge <150)
);
--创建表的同时添加约束方式2
create table studentinfo(
stuNo int not null primary key,
stuName varchar(8) not null unique,
stuAge int check(stuAge >=0 and stuAge <150),
stuTel char(8)
);
--添加主外键
--新增班级表
create table classInfo(
classId int not null primary key,
className varchar(20)
);
--建表的同时添加外键
create table studentinfo(
stuNo int not null,
stuName varchar(8) not null,
stuBirth date not null,
stuAge int,
stuTel char(8),
fclassId int,
stuBirth date not null,
constraint pk_stuNo primary key(stuNo),
constraint un_stuName unique(stuName),
constraint ch_stuAge check(stuAge >=0 and stuAge <150),
constraint fk_fcalssId foreign key(fclassid) references classInfo(classId)
);
-- 自增
create table studentinfo(
stuNo int not null generated always as identity(start with 1 ,increment by 1),
stuName varchar(8) not null,
stuAge int,
stuTel char(8),
fclassId int,
stuBirth date not null,
constraint pk_stuNo primary key(stuNo),
constraint un_stuName unique(stuName),
constraint ch_stuAge check(stuAge >=0 and stuAge <150),
constraint fk_fcalssId foreign key(fclassid) references classInfo(classId)
);
--先建表再添加外键
alter table studentinfo add constraint fk_classId foreign key(fclassid) references classInfo(classId);
--从系统表中查询约束名
select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references;
--插入
insert into classinfo values(1,‘ETP-1‘);
insert into studentInfo values(1,‘xy‘,20,‘12345‘,1,‘1990-02-28‘);
--不是全部插入则需要写列名
insert into studentinfo(stuNo,stuName,stuTel) values(2,‘wj‘,‘111‘);
-- 有自增长的列要写清楚列名
insert into studentinfo(stuName,stuAge,stuTel,fclassid,stuBirth) values(‘xy‘,20,‘12345‘,1,‘1990-02-28‘);
insert into studentinfo(stuName,stuAge,stuTel,fclassid,stuBirth) values(‘tom‘,22,‘12345‘,2,‘1990-02-28‘);
--更新
update studentinfo set stuBirth = ‘1990-02-21‘ where stuName=‘xy‘;
update studentinfo set stuBirth = ‘1990-02-21‘,stuAge = 21 where stuName=‘xy‘;
--删除
deleted from studentinfo where stuName=‘xy‘;
--查询
select * from studentinfo where stuName=‘xy‘;
select stuName,stuAge from studentinfo;
--别名查询
select stuName as 姓名,stuAge as 年龄 from studentinfo;
select s.stuName as 姓名,s.stuAge as 年龄 from studentinfo s;
--运算查询
select s.stuName as 姓名,s.stuAge+5 as 年龄 from studentinfo s;
--串联运算查询
select stuName||stuAge from studentinfo;
--and 和 or
select s.stuName as 姓名,s.stuAge+5 as 年龄 from studentinfo s where s.stuName=‘xy‘ and s.stuAge=20;
select s.stuName as 姓名,s.stuAge+5 as 年龄 from studentinfo s where s.stuName=‘xy‘ or s.stuAge=20;
--null
select * from studentinfo where stuAge is null;
select * from studentinfo where stuAge is not null;
--between and 包括边界 相当于>=和<=s
select s.stuName as 姓名,s.stuAge+5 as 年龄 from studentinfo s where s.stuAge between 10 and 20
--in
select * from studentinfo where stuName in (‘xy‘,‘wj‘);
select * from studentinfo where stuName not in (‘xy‘,‘wj‘);
--模糊查询 like%，%表示多个字符
select * from studentinfo where stuName like ‘x%‘
--模糊查询 like_ ， _表示单个字段
select * from studentinfo where stuName like ‘x_‘；
--排序 order by
select * from studnetinfo order by fclassid desc;
select * from studnetinfo order by fclassid asc;
--distinct去掉重复
select distinct stuAge as 年龄 from studentinfo;
--group by,使用的时候,select 后面只能加2种字段: 1.group by 后面出现的，2.聚合函数
select fclassId as 班级号,count(stuName) as 学生个数 from studentinfo group by fclassid;
-having 在分组的基础上过滤,出现顺序where-group by-having
select fclassId as 班级号,count(stuName) as 学生个数 from studentinfo group by fclassid having count(StuName)>=2
DB2和Oracle的SQL语法对比
1、取前N条记录
Oracle：SELECT * FROM TableName WHERE rownum <= N;
DB2：SELECT * FROM TableName fetch first N rows only;
2、取得系统日期
Oracle：SELECT sysdate FROM dual;
DB2：SELECT current timestamp FROM sysibm.sysdummy1;
3、空值转换
Oracle：SELECT productid,loginname,nvl(cur_rate,‘0‘) FROM TableName ;
DB2：SELECT productid,loginname,value(cur_rate,‘0‘) FROM TableName;
Coalesce(cur_rate,‘0‘)
4、类型转换（8版有了to_char,to_date,9版新增了to_number）
Oracle：SELECT to_char(sysdate,‘YYYY-MM-DD HH24:MI:SS‘) FROM dual;
DB2：SELECT varchar(current timestamp) FROM sysibm.sysdummy1;
Oracle数据类型改变函数：to_char()、to_date()、to_number()等；如果仅仅取年，月，日等，可以用to_char(sysdate, ‘YYYY‘),to_char(‘MM‘) ,to_char(‘DD‘)取得。只取年月日TRUNC(SYSDATE)，取时分秒TO_CHAR(SYSDATE,‘HH24:MI:SS‘)。
DB2数据类型改变函数：char()、varchar()、int()、date()、time()等；取得年，月，日等的写法：YEAR(current timestamp)，MONTH(current timestamp)，DAY(current timestamp)，HOUR(current timestamp)，MINUTE(current timestamp)，SECOND(current timestamp)，MICROSECOND(current timestamp)，只取年月日可以用DATE(current timestamp)，取时分秒TIME(current timestamp)。Char()是定长字符串（1-255），varchar()为非定长字符串（1-32672）日期,时间形态变为字符形态: char(current date)，char(current time)将字符串转换成日期或时间形态:TIMESTAMP(‘2002-10-2012:00:00‘),DATE(‘2002-10-20‘),DATE(‘10/20/2002‘),TIME(‘12:00:00‘)
目前DB2 V8也支持to_char和to_date
5、快速清空大表
Oracle：TRUNCATE TABLE TableName ;
DB2：ALTER TABLE TableName active NOT logged initially WITH empty TABLE;
6、关于ROWID
Oracle它是由数据库唯一产生的，在程序里可以获得DB2 v8也有此功能。
7、To_Number
Oracle：SELECT to_number(‘123‘) FROM dual;
DB2：SELECT cast(‘123‘ AS integer) FROM sysibm.sysdummy1;
SELECT CAST ( current time AS char(8)) FROMsysibm.sysdummy1
8、创建类似表
Oracle：CREATE TABLE a AS SELECT * FROM b ;
DB2：CREATE TABLE a LIKE b ;
CREATE TABLE tab_newAS SELECT col1,col2…FROMtab_old DEFINITION ONLY (8版有效，9版无效)
9、decode方法
Oracle：decode方法（DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)）或者case语句DB2中只有CASE表达式SELECT id ,name ,CASE WHEN integer(flag)=0 THEN ‘假’ WHEN integer(flag)=1 THEN ‘真’ ELSE ‘异常’END FROM TEST或者SELECT id ,name , CASE integer(flag) WHEN 0 THEN ‘假’ WHEN 1 THEN ‘真’ELSE ‘异常’END FROM TEST
10、子查询（8版，9版也支持子查询）
Oracle:直接用子查询
Db2:WITH语句WITH a1 AS(SELECT max(id) AS aa1 FROM test ) SELECT id ,aa1 FROM test ,a1
11、数据类型
比较大的差别：
Oracle：char 2000
DB2: char 254
Oracle: date datetime
Db2: DATE：日期TIME：时间TIMESTAMP：日期时间
12、递归查询
Oracle语法简单，CONNECT BY PRIOR ... START WITH ...：
SELECT DISTINCT parent
FROM (SELECT T.parent FROM TABLE1 T
CONNECT BY PRIOR T.parent = T.child --递归的方向为从子向父
START WITH T.child = ‘xxx‘ )
DB2较难理解，要WITH一个虚拟表:
WITH PAR_CH(child, parent) AS
( SELECT child, parent FROM TABLE1
WHERE child = ‘xxx‘ --设置递归起点
UNION ALL
SELECT T.child, T.parent
FROM PAR_CH VT, TABLE1 T
WHERE VT.parent = T.child --递归的方向为从子向父
)
SELECT DISTINCT parent
FROM PAR_CH
原文：https://www.cnblogs.com/yooy/p/9168620.html