Oracle 初学

--创建表
CREATE TABLE STUDENTS(
       ST_ID NUMBER(10),
       ST_NAME VARCHAR2(100),
       ST_SEX VARCHAR2(10),
       ST_JOIN_DATE DATE,
       ST_HOBBIES VARCHAR2(100)
);
--修改表 增加一个字段
ALTER TABLE STUDENTS ADD (ST_AGE NUMBER(3));

--修改表 删除一个字段
ALTER TABLE STUDENTS DROP COLUMN ST_AGE;

--修改表 修改字段名
ALTER TABLE STUDENTS RENAME COLUMN ST_HOBBIES TO ST_HOBBY; 

--修改表 修改字段类型
ALTER TABLE STUDENTS MODIFY ST_HOBBY VARCHAR2(200);

--修改表 修改表名称
RENAME STUDENTS TO STUDENT;
RENAME STUDENT TO STUDENTS;

--删除表
DROP TABLE STUDENTS;

--新增记录
INSERT INTO STUDENTS(ST_ID,ST_NAME,ST_SEX) VALUES(1,'KEVEN','男');
INSERT INTO STUDENTS(ST_ID,ST_NAME,ST_SEX) VALUES(2,'张三','男');
INSERT INTO STUDENTS(ST_ID,ST_NAME,ST_SEX) VALUES(3,'李四','男');
INSERT INTO STUDENTS(ST_ID,ST_NAME,ST_SEX) VALUES(4,'王五','男');
INSERT INTO STUDENTS(ST_ID,ST_NAME,ST_SEX) VALUES(5,'赵六','男');

INSERT INTO STUDENTS(ST_ID,ST_NAME,ST_SEX,ST_JOIN_DATE) VALUES(6，'赵六','男',SYSDATE);

--修改插入时间格式
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
INSERT INTO STUDENTS(ST_ID,ST_NAME,ST_SEX,ST_JOIN_DATE) VALUES(7，'赵六','男','2016-5-2');

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI:SS';
INSERT INTO STUDENTS(ST_ID,ST_NAME,ST_SEX,ST_JOIN_DATE) VALUES(7，'赵六','男','2016-5-2 09:00:45');

--修改语句
UPDATE STUDENTS SET ST_SEX ='女' WHERE ST_ID = 4;

UPDATE STUDENTS SET ST_HOBBIES = 'LOL,WOW' WHERE ST_ID=5;

UPDATE STUDENTS SET ST_HOBBIES = ST_HOBBIES|| ' LOL'WHERE ST_ID = 3;

--删除记录
DELETE FROM STUDENTS WHERE ST_ID = 5;

--查询表
SELECT 
ST_ID,ST_NAME,ST_SEX,ST_JOIN_DATE,ST_HOBBIES
FROM STUDENTS;

-- 按条件查询
SELECT ST_ID,ST_NAME,ST_SEX,ST_JOIN_DATE,ST_HOBBIES FROM STUDENTS WHERE ST_ID = 4;

SELECT ST_ID,ST_NAME,ST_SEX,ST_JOIN_DATE,ST_HOBBIES FROM STUDENTS WHERE ST_ID NOT IN(4);

SELECT ST_ID,ST_NAME,ST_SEX,ST_JOIN_DATE,ST_HOBBIES FROM STUDENTS WHERE ST_SEX NOT IN('男');

--提交
COMMIT;

--主键约束  PRIMARY KEY 
--增加一个名为 PK_STUDENTS_ID 的主键约束
ALTER TABLE STUDENTS ADD CONSTRAINT PK_STUDENTS_ID PRIMARY KEY (ST_ID);
--删除主键
ALTER TABLE STUDENTS DROP CONSTRAINT PK_STUDENTS_ID;


--唯一约束 UNIQUE
--增加唯一约束
ALTER TABLE STUDENTS ADD CONSTRAINT UN_STUDENTS_NAME UNIQUE(ST_NAME);
--删除唯一约束
ALTER TABLE STUDENTS DROP CONSTRAINT UN_STUDENTS_NAME;


--检查约束 CHECK
--添加检查约束
ALTER TABLE STUDENTS ADD CONSTRAINT CK_STUDENTS_SEX CHECK( ST_SEX = '男' OR ST_SEX = '女' OR ST_SEX IS NOT NULL);
--删除检查约束
ALTER TABLE STUDENTS DROP CONSTRAINT CK_STUDENTS_SEX;


--外键约束  FOREIGN KEY
-- 确认主从关系 建立在从表上
CREATE TABLE SCORE(
       ST_ID NUMBER(10),
       SCORE  NUMBER(3)
);

--添加外键
ALTER TABLE SCORE ADD CONSTRAINT FK_SCST_ID FOREIGN KEY(ST_ID) REFERENCES STUDENTS(ST_ID); 


--添加外键  删除主表（students）的时候同时删除从表相关的数据
ALTER TABLE SCORE ADD CONSTRAINT FK_SCST_ID FOREIGN KEY(ST_ID) REFERENCES STUDENTS(ST_ID) ON DELETE CASCADE; 


--添加外键  删除主表（students）的时候同时将从表相关的数据外键设置为null
ALTER TABLE SCORE ADD CONSTRAINT FK_SCST_ID FOREIGN KEY(ST_ID) REFERENCES STUDENTS(ST_ID) ON DELETE SET NULL; 


--删除外键
ALTER TABLE SCORE DROP CONSTRAINT FK_SCST_ID;