Oracle 常用sql场景应用（未完待续......）

wfkjxy

　　--1、创建一张学生信息表stuinfo，字段包括学号、姓名、性别、年龄、入学日期
　　SQL>CREATE TABLE stuinfo (snochar(4),sname varchar2(10),sex int,age int,sdate date);
　　Table created
　　--2、创建stuinfo表主键约束
　　SQL>ALTER TABLE stuinfo ADD CONSTRAINT pk_stu PRIMARY KEY (SNO);

　　Table>　　Executed in 0.952 seconds
　　--3、将入学日期增加sysdate默认值，需要注意的是sysdate后必须加not null
　　SQL>ALTER TABLE stuinfo MODIFY sdate DEFAULT SYSDATE NOT NULL;

　　Table>　　Executed in 0.546 seconds
　　--4、添加5条模拟数据
　　SQL> insert into stuinfo values('001','lilei','0','23',default);
　　SQL> insert into stuinfo values ('002','xiaofang','1','22',to_date('2014-09-01','yyyy-mm-dd'));
　　SQL> insert into stuinfo values('003','weiwei','1','24',to_date('2008-08-08','yyyy-mm-dd'));
　　SQL> insert into stuinfo values('004','xiaochao','0','27',to_date('2009-10-08','yyyy-mm-dd'));
　　SQL> insert into stuinfo values('005','tom','0','28',to_date('2007-03-08','yyyy-mm-dd'));
　　--5、检索stuinfo表
　　SQL> select * from stuinfo;

　　--6、创建一张成绩表，列内容包括学号、课程代码、成绩，其中学号是本表的外键
　　SQL>CREATE TABLE score (gnochar(4),class int,score NUMBER(4,2),CONSTRAINT FK_NO FOREIGN KEY (gno)REFERENCES stuinfo(sno));
　　Table created
　　--7、测试向score表中插入sno在stuinfo表中不存在的记录
　　SQL>insert into score values('006',1212,96.33);
　　ORA-02291: 违反完整约束条件 (EPM.FK_NO) - 未找到父项关键字
　　--8、对于存在外键的表，插入或者更新时都需要与父表关联，所以必须在外键处创建索引
　　SQL>CREATE INDEX inx_score ON score(gno);
　　Index created
　　Executed in 0.062 seconds
　　--9、将stuinfo表重命名为student
　　SQL>ALTER TABLE stuinfo RENAME TO student;
　　--10、为student表增加检查——age between 1 and 120

　　SQL>>　　--11、测试向student表插入age是126的数据
　　SQL> INSERT INTO student VALUES('006','dushuai',0,'126',to_date('1989-01-05','yyyy-mm-dd'));
　　ORA-02290: 违反检查约束条件 (EPM.AGE_CHECK)
　　--12、将student表中学号为006的学生年龄更新为26
　　SQL> UPDATE student SET age=26 WHEREsno='006';
　　1 row updated
　　Executed in 0.078 seconds
　　--13、测试对student进行删除，检查能够正常删除
　　SQL>DROP TABLE student;
　　ORA-02449: 表中的唯一/主键被外键引用
　　说明：如果表中的主键或唯一值被其他表外键引用，要么先删除外键或者在drop后加cascade constraint 代表进行级联删除；
　　正确SQL>DROP TABLE student CASCADE CONSTRAINT;
　　--14、oracle闪回技术，即从回收站恢复被drop的表
　　SQL> FLASHBACK TABLE student TO BEFORE DROP;
　　Done
　　Executed in 0.296 seconds
　　--15、查询回收站中的内容
　　SQL>SELECT r.original_name,r.type,r.droptime FROM recyclebin r;

　　--16、为表添加整体注释
　　SQL>COMMENT ON TABLE student IS '学生信息表';
　　--17、为student表中的age列添加注释
　　SQL>COMMENT ON COLUMN student.age IS '年龄需要大于1岁并小于等于120岁';
　　--18、创建一张表，只使用student表中的表结构，而不使用数据
　　SQL>CREATE TABLE student_temp AS SELECT* FROM student WHERE 1=2;
　　说明：因为1=2条件不成立，所以只将student表的结构复制给了新表；
　　相同道理，如果条件成立为真，则会将表结构和数据都会复制给新表！
　　新表只能继承旧表的结构和数据，无法继承主键、外键、索引等约束；
　　--19、设置表为只读模式
　　SQL>ALTER TABLE student_temp1 READ ONLY;
　　说明：表设置为只读后，无法进行插入、更新等DML操作，但允许删除表；
　　SQL>INSERT INTO student_temp1VALUES('007','wangzi','0','98',TO_DATE(SYSDATE,'YYYY-MM-DD'));
　　ORA-12081: 不允许对表"EPM"."STUDENT_TEMP1" 进行更新操作
　　SQL> DROP TABLE student_temp1;
　　Table dropped
　　Executed in 1.7 seconds
　　--20、将只读表恢复成读写状态

　　SQL>>
　　Table>　　Executed in 0.265 seconds
　　--21、student_temp1表新增备注列，类型为varchar2（200）

　　SQL>>
　　Table>　　Executed in 1.264 seconds
　　--22、将student_temp1表中的note表设置为不使用（unused）

　　SQL>>
　　Table>　　Executed in 1.794 seconds
　　说明：使用set unused可以对表中的列进行标注，同时减少drop column的大量redo
　　注意set unused后的列数据无法恢复（除非通过数据备份），然后在系统开销较小的时候，对标记set unused的表列进行删除操作，格式如下：

　　SQL>>
　　Table>　　Executed in 2.247 seconds
　　--23、Oracle新建表时应避免使用系统保留关键字，如果确实需要使用应当需要使用双引号进行包围并且在使用时也需要带双引号进行引用
　　SQL>ALTER TABLE t1 RENAME TO "select";
　　SQL>SELECT * FROM "select";
　　说明：创建表时如果表名使用的是小写，在oracle数据字典中将自动转化为大写，用户使用select访问的时候表名使用小写或大写都是同等效果，但是如果使用双引号，则大写的双引号可以正常访问，而小写的就会失败，因为双引号具有固定、锁定的含义；即表名t == T == "T" != "t"
　　即表名t == T == "T" != "t"