ORACLE SQL整理笔记

--1.Oralce表空间--1.1.创建20M大小的名为lvxc_test的表空间存在目录'D:\lvxc\data\lvxc_test.dbf'create tablespace lvxc_test datafile 'D:\lvxc\data\lvxc_test.dbf' size 20M;--1.2.创建20M大小的名为lvxc_test的表空间存在目录'D:\lvxc\data\lvxc_test.dbf'，超过物理文件的存储空间每次自动增长5M,最多增长到500Mcreate tablespace lvxc_test datafile 'D:\lvxc\data\lvxc_test.dbf' size 20M autoextend on next 5M maxsize 500M;--1.3.查询表空间名及其物理文件路径select tablespace_name, file_name from dba_data_files order by file_name;--1.4.查看用户名以及默认表空间名select user_id,username,default_tablespace from dba_users where username='LVXC_STUDY'--1.5.修改数据库的默认表空间为lvxc_test(以后新建的数据库没指定default tablespace时其default tablespace就是lvxc_test)alter database default tablespace lvxc_test--1.6.利用rename选项将表空间lvxc_test重命名为lvxc_test_dataalter tablespace lvxc_test rename to lvxc_test_data;--1.7.删除名为lvxc_test_data表空间及其物理文件alter tablespace lvxc_test_data offline;drop tablespace lvxc_test_data including contents and datafiles、--2.Oracle数据表--2.1、创建一张主键为student_id的表studentcreate table student(  student_id number not null,  student_name varchar(20),  student_age number ,  status varchar(3),  version number default 0,  constraint pk_student primary key(student_id))--2.2、删除student表的主键alter table student drop primary key; --2.3、在SQL命令行下，可以通过desc显示数据表的表结构desc student;--2.4、给sutdent表增加class_id列alter table student add (class_id number);--2.5、修改student表字段class_id的数据类型为varchar(20)alter table student modify (class_id varchar(20));--2.6、删除student表class_id列alter table student drop column class_id;--2.7、删除student表并且删除其关联的约束drop table student cascade constraint; --3.Oracle查询--3.1、创建一张与student表具有相同结构的空数据表student_infocreate table student_info as select * from student where 1<>1;--或create table student_info as select * from student where 1=0;--3.2、将student表中的student_id和student_name 两列数据插入到student_info表中insert into student_info(student_id ,student_name) select student_id,student_name from student;--3.3 union求并集，去除重复列select student_id,student_name from studentunionselect student_id,student_name from student_info--3.4 union all 求并集，不去除重复列select student_id,student_name from studentunion allselect student_id,student_name from student_info--3.5 intersect 求交集select student_id,student_name from studentintersectselect student_id,student_name from student_info--3.6 minus 求差集(在student表中存在，在student_info表中不存在)select student_id,student_name from studentminusselect student_id,student_name from student_info--3.7 左连接（left join:以第一张表为基础表，第二张表可以有空记录）；右连接（right join:以第二张表为基础表，第一张表可以有空记录）--    全连接（full join:先左连接，后右连接，后union操作或先右连接，再左连接，后union操作。两张表都可以有空记录）--4.Oracle层次化查询--4.1 以market_name='亚洲'开始（如何没有satrt with ,则针对所有记录），递归查出 market_name='亚洲'这条记录的market_id等于下一条记录的parent_market_id;--    及把market_name='亚洲'为根查处根和根下所有记录。--    sys_connect_by_path() 对起始至当前记录之间的结果集进行聚合操作    select market_id , mark_name, sys_connect_by_path(market_name,'/') as market_path    from market    start with market_name='亚洲'    connect by  prior market_id = parent_market_id;--5.Oracle约束--5.1 创建一张主键约束名为pk_student,主键为student_id的表student，如果不指定主键约束名，系统会默认取个名字。create table student(  student_id number not null,  student_name varchar(20),  student_age number ,  status varchar(3),  version number default 0,  constraint pk_student primary key(student_id))--5.2、通过数据字典user_constraints查看表为student的主键约束详情.select table_name,constraint_name,constraint_type,r_constraint_name,status from user_constraints where lower(table_name)='student'--5.3、将student表的主键约束名由pk_student改为pk_con_student.alter table student rename constraint pk_student to pk_con_student;--5.4、为student添加一个约束名为fk_student外键为student_name的外键约束，外键引用student_info里的主键student_name;alter table student add constraint fk_student foreign key(student_name) references student_info(student_name);--5.5、级联更新：通过deferred进行延迟校验，即事务提交后进行更新（一个事务可包含多条sql语句）；还有一种是immediate及时校验alter table student add constraint fk_student foreign key(student_name) references student_info(student_name) deferrable initially deferred;--5.6、级联删除：通过on delete cascade指定外键采用级联删除机制alter table student add constraint fk_student foreign key(student_name) references student_info(student_name) on delete cascade;--5.7、删除student表外键约束fk_student;alter table student drop constraint fk_student;--5.7、为表student在列student_name,student_age上创建一个唯一性约束，名为uniq_student;alter table student add constraint uniq_student unique (student_name,student_age);--5.8、为表student创建检查约束:年龄大于0小于100，并且status为ACT或TRM。alter table student add constraint chk_student check(student_age>0 and student_age<100 and status in('ACT','TRM'));--5.9、更改表student的version字段的默认值为1;alter table student modify version default 1;