-- 建表 --
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;