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,最多增长到500M
create 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_data
alter 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的表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)
)

--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_info
create 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 student
union
select student_id,student_name from student_info

--3.4 union all 求并集，不去除重复列
select student_id,student_name from student
union all
select student_id,student_name from student_info

--3.5 intersect 求交集
select student_id,student_name from student
intersect
select student_id,student_name from student_info

--3.6 minus 求差集(在student表中存在，在student_info表中不存在)
select student_id,student_name from student
minus
select 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;