DB2数据库操作常用sql
1. 查看当前schema名称
VALUES CURRENT SCHEMA;
2. 查询当前用户下所有序列
SELECT * FROM SYSCAT.SEQUENCES;
3. 查看某一个序列的值
VALUES NEXTVAL FOR 模式名.序列名
4. 创建序列
CREATE SEQUENCE SEQ_ABC START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24;
--获取下一个值
select nextval for SEQ_ABC from codechange7;
--获取当前值
select prevval for SEQ_ABC from codechange7;
5. 删除序列
DROP SEQUENCE SEQ_ABC ;
6. 修改序列
--序列设置开始值
ALTER SEQUENCE 序列名 (SEQ_ABC ) RESTART WITH 1;
--修改
修改最大值: ALTER SEQUENCE <sequence_name> MAX VALUE <numeric-constant> | NO MAXVALUE
修改最小值: ALTER SEQUENCE <sequence_name> MIN VALUE <numeric-constant> | NO MINVALUE
修改步长: ALTER SEQUENCE <sequence_name> INCREMENT BY <numeric-constant>;
修改CACHE值: ALTER SEQUENCE <sequence_name> CACHE <numeric-constant> | NO CACHE
修改循环属性: ALTER SEQUENCE <sequence_name> <CYCLE | NO CYCLE>
修改排序属性:ALTER SEQUENCE <sequence_name> <ORDER | NO ORDER>
从新计数:ALTER SEQUENCE <sequence_name> RESTART | RESTART WITH <numeric-constant>
7. 创建表、删除表
CREATE TABLE BOOKS
(
BOOKID INTEGER,
BOOKNAME VARCHAR(100),
ISBN CHAR(10)
);
DROP TABLE BOOKS;
8. 添加、删除列
ALTER TABLE BOOKS ADD BOOKPRICE DOUBLE(8,2);
ALTER TABLE BOOKS DROP BOOKPRICE ;
9. 列的修改及限制
1.修改长度
ALTER TABLE TAB_NAME ALTER C1 SET DATA TYPE VARCHAR(20);
2.设置为非空
ALTER TABLE TAB_NAME ALTER C1 SET NOT NULL;
CREATE TABLE TAB_NAME (ID INTERGER NOT NULL);
3.设置默认值
CREATE TABLE TAB_NAME (ID INTEGER,NAME VARCHAR(10) WITH DEFAULT 'NONE');
4.为列创建序列
使用关键字 generated always as identity
CREATE TABLE TAB_NAME (ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1,INCREMENT BY 1));
5.唯一性约束
主键约束使用关键字primary key
CREATE TABLE TAB_NAME (ID INT NOT NULL PRIMARY KEY);
唯一约束使用关键字unique
ALTER TABLE TAB_NAME ADD CONSTRAINT UNIQUE (ID);
CREATE UNIQUE INDEX IDX_NAME ON TAB_NAME (ID);
6.检查性约束
ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1) CHECK (BOOKTYPE IN ('F','N') );