Oracle-DDL语句详解

--------DDL-------   
--- 创建表
create table mytable 
( 
  myid number(4) not null primary key,---- 主键  并且不为空  
  myname varchar2(10) not null,
  mybirthday date default sysdate  ---  default 表示默认值  如果没有指定值  就是默认值  sysdate

)

insert  into mytable (myid ) values(3 )
insert  into mytable (myid,myname) values(1,'白白');
insert  into mytable  values(2,'白白',null);
select *from mytable

---删除表
drop table mytable

--修改表名
rename mytable  to userInfo
select *from userInfo

---修改表的结构 
alter  table userInfo add sex varchar2(2) default '女' --添加一个新列   默认值  女

alter  table userInfo modify myname  varchar2(5)  

alter table  userInfo drop column  sex

insert  into userInfo (myid,myname) values(1,'白白');
----------------- 约束
------ 主键约束

drop table myTable

create table myTable(
myid number(2)   primary key , 
--SYS_C009677   不指定名字  系统默认分配  主键  不能为null  唯一  等价于  二个约束   not null   unique
myname varchar2(10) ,
birthday date  default sysdate
)

create table myTable(
myid number(2) not null  , 
myname varchar2(10) ,
birthday date  default sysdate,
constraint   my_key  primary key (myid) --- my_key 是主键名称

)
--- 唯一约束   唯一的
create table stu_table(
sid number(2)   primary key  ,
sname varchar2(10) ,
stuaccount varchar2(2)  constraint stuaccount_unique     unique
)

insert into stu_table values(2,'小白','02')

---检查约束
create table student(
sid number(2)   primary key  ,
sname varchar2(10) ,
sex  varchar2(2)   constraint sex_checked   check (sex='男' or sex='女'),
sage number(3)

)
insert into student values(2,'小白','女',12)
select *from student

---not null 约束    不能为空  

---  外键约束
create table class_table (
cid  number(2)   primary key,
cname varchar2(10) unique

)

------ 给学生表 添加一个  外键列   外键约束
alter table student  add cid  number(2)

select *from student;

select *from class_table

---  表定义之后   添加 外键约束
alter table  student add  constraint FK_CID foreign  key (cid) references  class_table(cid);---class_table(cid) cid 必须是class_table 主键 

---- 三种设置约束的方法 

insert into class_table values(1,'java1014')
insert into student values(3,'小白~','男',-12,1)

update student set sage=12 where sid=3

alter table student add constraint age_check check(sage>0 and sage<200)

insert into student values(4,'小白2~','男',199,1)

---- 外键约束 补充
insert into class_table values(2,'java1114')
insert into student values(5,'小黑~','男', 12,2)

delete from student where sid=1

---删除外键  所在表的主键  数据是   有关联   默认选择 no-action 

delete from class_table where cid=2

alter table student add constraint fk_cid foreign key (cid) references class_table (cid) on delete cascade;---级联删除 
---直接将外键所在的行数据  删除 

delete from class_table where cid=2

--- 直接将外键所在的行数据   设置为 null
alter table student add constraint fk_cid foreign key (cid) references class_table (cid) on delete set  null;
delete from class_table where cid=2

----假删除    
delete ×  

alter table student  add flag number(1) 
select  *from student    where flag=1 

update  student set flag=0 where sid=5;  

---- 删除外键 约束
insert into student values(6,'小黑~','as', 999,2,1)

alter  table student  drop constraint   sex_checked 

--- 约束失效
alter table student disable constraint age_check

---激活约束
alter table student enable constraint age_check

--------------------------视图-----------
--- dba 开放权限   
grant 权限 to 用户
grant create view to scott;
grant dba to scott; 

dba----

create  or replace  view   myView     as  select *from emp; --- or replace  如果视图存在  就替换 

---视图使用起来和表的操作思路 完全一致
select *from myView
select *from emp
----- 使用 DML 语言 操纵  视图数据     ----原数据 也会改变 
update myView set ename='强弟' where empno=8888

insert  into myView(empno,ename) values(9999,'攻城狮')

insert  into emp(empno,ename) values(9999,'攻城狮')
---复杂视图
create or replace  view emp_dept as 
select  empno,ename,job,emp.deptno,dname,loc  from  emp,dept where emp.deptno=dept.deptno and mgr is not null

select *from emp_dept

---- 无法同时修改 多个基表的 信息
update emp_dept set    deptno=10, loc='China'      where empno=8888

--- 只读视图   控制 DML 语句进行 数据操作  
create or replace  view emp_dept as 
select  empno ,ename,job,emp.deptno,dname,loc  from  emp,dept where emp.deptno=dept.deptno and mgr is not null   
with  read only
-- 无法对只读视图进行DML 操作
update emp_dept set    deptno=10       where empno=8888

-----视图 别名  

create or replace  view emp_dept as 
select  empno    eo,ename  ee,job ,emp.deptno  do,dname de,loc ,sal  s  from  emp,dept where emp.deptno=dept.deptno and mgr is not null   
with  read only

-----  查询 sal  大于 2000  员工详细信息  （包含 部门信息）

select *from emp_dept  where  s>2000

---删除 视图 
drop  view emp_dept

---创建视图     部门编号 部门所在地  部门下的人数  以及  部门员工的工资和

--查询 部门所有员工工资和 大于 5000 的部门信息

--------------------序列  ---   生成主键   不能回退  不可逆性
create  sequence  my_num --- 序列名
increment  by 1  --自增 1 
minvalue 1
maxvalue 10
nocycle  ---不循环
nocache --不缓存

select my_num.nextval  from dual;
select my_num.currval  from dual;

create  sequence  my_num --- 序列名
increment  by 2  --自增 1 
minvalue 1
maxvalue 10
cycle  ---循环
cache 5-- 缓存5 个数  

---作为主键
create  sequence  my_num_class --- 序列名
increment  by 1  --自增 1 
minvalue 3
maxvalue 10
nocycle  ---循环
cache 5-- 缓存5 个数  

select my_num_class.nextval  from dual;
select my_num_class.currval  from dual;
select *from class_table
insert into class_table  values (my_num_class.nextval,'123'||my_num_class.currval)

----索引  

create table myTable(
myid number(2)   primary key , 
--SYS_C009677   不指定名字  系统默认分配  主键  不能为null  唯一  等价于  二个约束   not null   unique
myname varchar2(10) ,
birthday date  default sysdate
)
--如果指定 某一列有唯一约束   这一列 自动添加 索引

--- 手动创建
create  index  emp_name on emp(ename)

select *from emp ename = '123'  ------lkie 不会走索引    instr()

--- 用户   

--角色  

grant dba to 用户       --dba  是个角色

-----集合运算   ----
select *from emp;
select *from employee;
---并集    去重

select  empno ,ename from emp union   select  empno ,ename from employee; --去重

select  empno ,ename from emp union all   select  empno ,ename from employee; ---- 不去重

----交集
select  empno ,ename from emp intersect   select  empno ,ename from employee; 
----相对补集  差集
select  empno ,ename from emp minus   select  empno ,ename from employee; --- 左边 - 右边

select  empno ,ename from  employee minus   select  empno ,ename from emp;