Oracle表的创建，序列和触发器，增删改查，删表

--Oracle表的创建，序列和触发器，增删改查，删表

-- 创建学生表student information table
CREATE TABLE SCOTT.STUDENTINFO
(
    "ID" NUMBER(4),
    "NAME" VARCHAR2(40 BYTE),
    "STUNO" VARCHAR2(40 BYTE),
    "AGE" NUMBER(4),
    "SCORE" NUMBER(4)
)
-- 创建序列和触发器实现oracle中表的主键id自增
-- 创建序列 sequence
CREATE SEQUENCE SCOTT.STUDENTINFO_SEQUENCE
START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE NOCACHE;

--创建触发器 trigger
CREATE OR REPLACE TRIGGER SCOTT.STUDENTINFO_TRIGGER
BEFORE INSERT ON SCOTT.STUDENTINFO FOR EACH ROW
BEGIN
    SELECT SCOTT.STUDENTINFO_SEQUENCE.NEXTVAL INTO:NEW.ID FROM DUAL;
END;

-- 创建NAME，STUNO索引
CREATE INDEX "SCOTT"."STUDENTINFO_INDEX1" ON "SCOTT"."STUDENTINFO" ("NAME", "STUNO") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 786432 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "STUDENT_INDEX02" ;
-- 创建STUNO索引
CREATE INDEX "SCOTT"."STUDENTINFO_INDEX1" ON "SCOTT"."STUDENTINFO" ("STUNO") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 786432 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "STUDENT_INDEX02" ;

--增加数据
INSERT INTO SCOTT.STUDENTINFO("NAME", "STUNO", "AGE", "SCORE") VALUES('ZHANG NING', '110123', 16, 89);
--删除数据
DELETE FROM SCOTT.STUDENTINFO WHERE ID = 1;
--修改数据
UPDATE SCOTT.STUDENTINFO SET NAME = 'LI SI' WHERE ID = 2;
--查找数据
SELECT * FROM SCOTT.STUDENTINFO WHERE ID = 2;
--删除表
DROP TABLE SCOTT.STUDENTINFO;

----对表字段的修改----
--删除表字段
ALTER TABLE 表名 DROP COLUMN 表列名;
--添加表字段
ALTER TABLE 表名 ADD(表列名 VARCHAR2(100 BYTE));
--更改表字段
ALTER TABLE 表名 RENAME COLUMN 原列名 TO 目标列名;
--对表名重命名
ALTER TABLE 表名 RENAME TO 新表名;