DB2的常用sql语句
建表语句
CREATE TABLE table_name
( id BIGINT GENERATED BY DEFAULT AS IDENTITY, --id
empid BIGINT, --员工编号
deptid BIGINT, --部门编号
appdate DATE, --申请日期
delflag VARCHAR(2), --删除标记
PRIMARY key(id)
);
新增一列
ALTER TABLE TABLE_name ADD COLUMN col_name col_type
删除一列
ALTER TABLE TABLE_name DROP COLUMN col_name
两条删除表语句的区别
ALTER TABLE ....ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
DROP TABLE ....;
DROP TABEL:彻底删除一个表,包括表的定义。
ALTER TABLE …ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE:删除行的内容(不作日志)而且表的定义不会被删除。这个命令在删除大表又不用作日志时效率要高些
清空表
DELETE FROM TABLE_name
修改表名
alter table old_table_name rename to new_table_name;
修改列名
ALTER TABLE table_name RENAME COLUMN oldcolname TO newcolname;
修改表字段类型
ALTER TABLE TABLE_name ALTER COLUMN col_name SET old_type TYPE new_type;
改变字段长度
alter table TABLE_name alter col_name set data type varchar(1000);
BETWEEN AND语句
SELECT * FROM EMPLOYEE WHERE id BETWEEN 1 AND 5;
复制一张表中的数据到另一张表
insert into table_name_new (select * from table_name_old);
复制表结构:相当创建一张与旧表有相同字段的新表,但不复制数据
create table table_name_new as (select * from table_name_old) definition only;
表重组(锁表时用于解表)
CALL SYSPROC.ADMIN_CMD('REORG TABLE table_name');
按行号排序
SELECT e.num,e.* from
(SELECT ROW_NUMBER() OVER() AS num,id FROM HROUTTRAIN ORDER BY num desc) e
ORDER BY e.num ASC
查询结果集的第一条数据
SELECT e.num,e.* from
(SELECT ROW_NUMBER() OVER() AS num,id FROM HROUTTRAIN ORDER BY num desc) e
ORDER BY e.num desc FETCH FIRST 1 ROWS ONLY
分页查询
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY date desc) AS r,e.* FROM emp e
WHERE e.name='A' AND r<=5)
WHERE r>0
查询表的列名并统计有多少列
SELECT COLNAME FROM SYSCAT.COLUMNS WHERE TABNAME='table_name' ;
查看某表的权限
SELECT * FROM SYSCAT.TABAUTH WHERE TABNAME='score_001'
在查询时构建虚拟表
SELECT
*
FROM
(VALUES
('张三','男','市场部',4000),
('赵红','男','技术部',2000),
('李四','男','市场部',5000),
('李白','女','技术部',5000),
('王五','女','市场部',3000),
('王蓝','女','技术部',4000)
) AS EMPLOY(NAME,SEX,DEPT,SALARY)
虚拟表不能起别名