--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 altered

Executed in 0.952 seconds

--3、将入学日期增加sysdate默认值，需要注意的是sysdate后必须加not null

SQL>ALTER TABLE stuinfo MODIFY sdate DEFAULT SYSDATE NOT NULL;

Table altered

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> ALTER TABLE student ADD CONSTRAINTage_check CHECK(age>1 and age<=120);

--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> ALTER TABLE student_temp1 READ WRITE;

Table altered

Executed in 0.265 seconds

--21、student_temp1表新增备注列，类型为varchar2（200）

SQL> ALTER TABLE student_temp1 ADD note varchar2(200);

Table altered

Executed in 1.264 seconds

--22、将student_temp1表中的note表设置为不使用（unused）

SQL> ALTER TABLE student_temp1 SET UNUSED(note);

Table altered

Executed in 1.794 seconds

说明：使用set unused可以对表中的列进行标注，同时减少drop column的大量redo

注意set unused后的列数据无法恢复（除非通过数据备份），然后在系统开销较小的时候，对标记set unused的表列进行删除操作，格式如下：

SQL> ALTER TABLE student_temp1 DROPUNUSED COLUMNS; 

Table altered 

Executed in 2.247 seconds

本文出自 “oralce学习之路” 博客，请务必保留此出处http://dushuai.blog.51cto.com/9461011/1569765

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