oracle sql语句

-- 建表 --

CREATE TABLE userinfo(

user_pk NUMBER(4) CONSTRAINT pk_userinfo PRIMARY KEY,

user_name VARCHAR2(20),

user_password VARCHAR2(20)

);

-- 建序列 --

CREATE SEQUENCE userinfo_user_pk_sequence  

INCREMENT BY 1 

START WITH 1 

NOMAXVALUE NOCYCLE CACHE 10;

DROP SEQUENCE userinfo_user_pk_sequence;

-- 插入数据 userinfo_user_pk_sequence.CURRVAL: 当前序列号  --

SELECT * FROM userinfo;

INSERT INTO userinfo VALUES(userinfo_user_pk_sequence.NEXTVAL, 'daihuajiao', '123456', 1);

-- 添加表字段 --

ALTER TABLE userinfo ADD detail_id NUMBER(4) DEFAULT NULL;

-- 删除表字段 --

ALTER TABLE userinfo DROP COLUMN detail_id;

-- 修改字段类型 直接删除再添加 --

-- 无数据时

ALTER TABLE userinfo MODIFY (detail_id NUMBER(5));

-- 有数据时

ALTER TABLE userinfo RENAME COLUMN detail_id TO detail_id2;

ALTER TABLE userinfo ADD detail_id NUMBER(4);

UPDATE userinfo SET detail_id = CAST(detail_id2 AS NUMBER(4));

ALTER TABLE userinfo DROP COLUMN detail_id2;

-- 清空表数据 --

TRUNCATE TABLE userinfo;

 

CREATE TABLE detail_info(

detail_id_pk NUMBER(4) CONSTRAINT pk_detail_id PRIMARY KEY,

user_pk NUMBER(4) CONSTRAINT fk_user_pk REFERENCES userinfo (user_pk),

info VARCHAR2(20) CONSTRAINT info_un UNIQUE

);

CREATE SEQUENCE detail_info_sequence

INCREMENT BY 1

START WITH 1

NOMAXVALUE NOCYCLE CACHE 10;

-- 删除数据表

DROP TABLE detail_info;

CREATE FOREIGN KEY detail_userinfo_key (detail_info.USER_PK);

-- 查询约束、新增约束、删除约束 --

SELECT * FROM USER_CONSTRAINTS WHERE table_name='DETAIL_INFO'; -- DBA_CONSTRAINTS 全局查找 owner='TENGYONG' AND 

ALTER TABLE detail_info ADD CONSTRAINT SYS_YUESHU FOREIGN KEY (user_pk) REFERENCES userinfo (user_pk);

ALTER TABLE detail_info DROP CONSTRAINT SYS_YUESHU;

-- 查询约束的列

SELECT * FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME='PK_DETAIL_ID'; -- 全局同上

 

ALTER TABLE detail_info ADD scote NUMBER(4);

ALTER TABLE detail_info ADD CONSTRAINT check_scote CHECK (scote BETWEEN 0 AND 100);

-- 重命名约束名

ALTER TABLE DETAIL_INFO RENAME CONSTRAINT check_scote TO new_check_scote;

-- 删除约束

ALTER TABLE DETAIL_INFO DROP CONSTRAINT check_scote;

-- 建立视图

INSERT INTO detail_info VALUES(detail_info_sequence.NEXTVAL, 1, '123457',1);

CREATE VIEW user_detail AS SELECT a.*,b.detail_id_pk,b.info FROM userinfo a,detail_info b WHERE a.user_pk=b.user_pk WITH READ ONLY;

SELECT * FROM user_detail;

DROP VIEW user_detail;

SELECT * FROM DETAIL_INFO;

 

select * from v$version; 

SELECT * FROM V$SESSION;  -- 查看连接用户

SELECT * FROM DBA_VIEWS WHERE owner = 'TENGYONG'; -- 查看视图

SELECT * FROM DBA_SEQUENCES WHERE sequence_owner='TENGYONG'; -- 查看序列

 

-- 约束

PRIMARY KEY -- 主键

NOT NULL -- 非空

UNIQUE -- 唯一约束

FOREIGN KEY -- 外键

CHECK -- 条件约束

 

-- 分页

SELECT COUNT(*) FROM userinfo;

SELECT * FROM (SELECT ROWNUM AS rowno, t.* FROM userinfo t) WHERE rowno BETWEEN 90 AND 100;