Oracle 笔记03

用子查询  查询谁是普通员工


select first_name,id  from s_emp  
    where   id in (领导的id);
select first_name,id  from s_emp  
    where   id in (select 
    distinct manager_id from s_emp);
select first_name,id  from s_emp  
    where   id in (1,2,3,黑盒子);
select first_name,id  from s_emp  
    where   id  not in (select 
    distinct manager_id from s_emp);
注意：not  in  使用时要注意NULL值
select first_name,id  from s_emp  
    where   id  not in (select 
    distinct manager_id from s_emp 
    where manager_id is not null);
select first_name,id  from s_emp  
    where   id  not in (select 
    distinct nvl(manager_id,-1) from s_emp );
select first_name,id  from s_emp  
    where   id  not in (select 
    distinct nvl(manager_id,1) from s_emp );
select first_name,id  from s_emp  
    where   id  not in (select 
    distinct nvl(manager_id,25) from s_emp );
    //logic  error


找出和id 是1的职位相同的员工
select title  from  s_emp where id=1;
select  first_name,title from s_emp where 
title=(select title  from  s_emp where id=1)
and  id!=1;


------------------------------------------
SQL数据类型：
number       数字类型
varchar2(n)  变长字符串
char(n)      定长字符串  4k
date         日期类型


CLOB         大字符类型  4g
BLOB         大二进制类型 4g 


日期类型：
表达系统当前时间   sysdate
和语言相关的-------NLS_LANG
英文默认格式
DD-MON-YY
07-NOV-12


07-11月-12


处理日期的函数
语言环境
时间的表达问题
to_date(要转换的日期字符串,格式字符串)


建表   字段  都要遵循标识符的定义


create table 表名(
    字段名    类型,
    字段名    类型,
    字段名    类型
);


create table  testtype(
    id     number,
    fname  varchar2(10),
    sname  char(10),
    birday date
);
插入语句
insert into testtype values(1,'xsy','xsy',
sysdate);


insert into testtype values(9527,'zxc','zxc',
'01-NOV-08');
insert into testtype values(9527,'zxc','zxc',
'01-11月-08');


我们想把 2008-08-08 08:08:08


to_date


insert into testtype values(1,'abc','abc',
to_date('2008-08-08 08:08:08',
'yyyy-mm-dd hh:mi:ss'));


yyyy   四位年
mm     二位月
dd     日
hh     12小时的   hh24
mi     分钟
ss     秒


day    表达星期几
mon    月的英文缩写
month   




to_char(日期,'日期格式')


select to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;


to_date  就是按照格式放入日期
to_char  按照格式来显示日期


日期的加减法：


select to_char(sysdate-1,
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;
向前走一个小时
select to_char(sysdate+1/24,
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;


往前加一个月   往后推一个月


select to_char(add_months(sysdate,1),
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;
select to_char(add_months(sysdate,2),
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;


next_day  下一个星期几是几号


select next_day(sysdate,'星期五') from dual;
select next_day(next_day(sysdate,'星期五'),
'星期五')from dual;




last_day  本月的最后一天
select to_char(last_day(sysdate),
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;


补充：
round(日期)
select to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss day mon'),
to_char(round(sysdate+1/24),
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;
默认对天进行四舍五入 
select to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss day mon'),
to_char(round(sysdate,'mm'),
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;
select to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss day mon'),
to_char(round(sysdate,'y'),
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;
trunc(日期)
select to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss day mon'),
to_char(trunc(sysdate,'mm'),
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;
select to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss day mon'),
to_char(trunc(sysdate,'y'),
'yyyy-mm-dd hh24:mi:ss day mon')
from  dual;


在一个月的任何时间执行一条语句
得到的都是下一个月的开始的时间
to_char()


trunc(add_months(sysdate,1),'mm')
trunc(last_day(sysdate)+1)
----------------------------------------------
SQL约束的种类
主键    －－－－－－primary key
唯一    －－－－－－unique
非空    －－－－－－not null
检查    －－－－－－check
外键    －－－－－－foreign key  references




表上建立约束：对数据的最后一道屏障  
主键    －－－－－－唯一  且 非空
                     一个表最多只能有一个主键
唯一    ------------不能有重复的
非空    ------------不能有NULL值
检查    －－－－－－字段要符合检查条件
外键    -------------涉及到两张表
                     s_dept  -----id  (主表)
    s_emp   -----dept_id(子表)
    定义了外键的表就是子表
                     s_dept  -----region_id
    s_region ----id(主键)
    外键 引用主表的字段 必须有唯一性限制
    外键的取值只能是主表字段的值 要么是NULL
    这样能保证子表数据的完整性
    一旦违反外键约束 就会报数据完整性约束


约束的语法：
列级约束：在定义表的列的时候 直接定义的约束
1.主键-------列级
create table   testconstraint_pk(
    id    number,
    name  varchar(30)
);
insert into testconstraint_pk values(
1,'aobama');


drop table  testconstraint_pk;
create table   testconstraint_pk1(
    id    number primary key,
    name  varchar(30)
);
insert into testconstraint_pk1 values(
1,'aobama');
如果违法约束 会出现系统的约束名
create table   testconstraint_pk2(
    id    number constraint 
    testconstraint_pk2_id_pk primary key,
    name  varchar(30)
);
insert into testconstraint_pk2 values(
1,'aobama');
要求大家建立一张表 有两个字段 
id   number
name varchar2(20)
要求id 设置列级的主键约束  
    name 设置列级别的唯一性约束
create  table   test_c_pkuk(
    id     number constraint 
    test_c_pkuk_id_pk primary key,
    name   varchar(30) constraint
    test_c_pkuk_name_uk unique
);


insert into test_c_pkuk values(211,'abc');
0001: 违反唯一约束条件 (OPENLAB.TEST_C_PKUK_NAME_UK)


not  null  的列级约束和主键语言相同
检查约束
要求大家建立一张表 有两个字段 
id   number
salary  number
要求id 设置列级的主键约束  
    salary 设置列级别的检查约束  要求工资
    大于3500
create  table   test_c_pkck(
    id     number constraint 
    test_c_pkck_id_cpk primary key,
    salary   number constraint
    test_c_pkck_salary_ck  check(salary>3500)
);


insert into test_c_pkck values(10087,3500);




表级约束：在所有的列定义完成后  对表的某些
列加的约束
为什么要有表级约束？
建立一张表  有三个字段
fid   number 
sid   number 
name  varchar2(30)


要求 fid  和 sid  联合做主键
要求 fid  和 sid  联合唯一
解决联合约束
not  null  这个约束 不可能出现联合非空
所有not null  没有表级约束


语法形式如下：
create table  test_table_pk(
    id   number,
    name  varchar2(30),
    salary number,
    constraint  test_table_pk_id_pk  
    primary key(id)
);


使用表级别约束建立表
fid       number
sid       number
name      varchar2(20)
salary    number


fid  sid  要联合做主键
name   唯一
salary  要求大于8000 


create table  test_table_con1001(
    fid       number,
    sid       number,
    name      varchar2(20),
    salary    number,
    constraint  test_table_con1001_fsid_pk 
    primary key(fid,sid),
    constraint  test_table_con1001_name_uk
    unique(name),
    constraint  test_table_con1001_salary_ck
    check(salary>8000)
);
--------------------------------
外键：
     --主表：部门表
     drop table myemp;
     drop table mydept;
     create  table  mydept(
         id     number primary key,
name   varchar2(30)
     );
      
    /*子表：员工表*/
     create  table  myemp(
         id    number  primary key,
         name  varchar2(30),
         fid   number constraint myemp_fid_fk
         references  mydept(id) 
     );






1.先建立主表  后子表
2.插入数据 一般先插入主表  
  除非子表的外键使用NULL值
  insert into  mydept values(1,'test');
  insert into  myemp values(2,'taoge',1);


3.删除 数据 先删除子表  后删父表
  除非你设置了级联（级联置空  级联删除） 


4.删除表
  先删子表 后删父表
  
  drop   table  s_dept cascade constraints; 
  (先解除主外键关系 再删除表)
把下面的外键关系改成 表级外键约束
     create  table  mmdept(
         id     number primary key,
name   varchar2(30)
     );
      
    /*子表：员工表*/
     create  table  mmemp(
         id    number  primary key,
         name  varchar2(30),
         fid   number ,
constraint mmemp_fid_fk
         foreign  key(fid) 
references  mmdept(id) 
     );
级联  -----级联置空   级联删除
drop table mmdept;
drop table mmemp;


    create  table  mmdept(
         id     number primary key,
name   varchar2(30)
     );
     insert into mmdept values(1,'test');
     commit;
      
    /*子表：员工表*/
     create  table  mmemp(
         id    number  primary key,
         name  varchar2(30),
         fid   number ,
constraint mmemp_fid_fk
         foreign  key(fid) 
references  mmdept(id) 
on delete cascade 
     );
     insert into mmemp values(1,'zs',1);
     insert into mmemp values(2,'zss',1);
     insert into mmemp values(3,'zsss',1);
     commit;
当主表删除数据时级联 删除子表相关的数据


 create  table  mmemp(
         id    number  primary key,
         name  varchar2(30),
         fid   number ,
constraint mmemp_fid_fk
         foreign  key(fid) 
references  mmdept(id) 
on delete set null 
     );


------------------------------------------
约束：
   5类约束----a  b c  d  e
   语法----表级 和列级别
   not   null
   check
   外键----级联置空  删除


------------------------------------------
数据库中dml
insert   
delete  
update 


1.插入语句     insert
create table  testdmla(
    id    number  primary key,
    fname   varchar2(10),
    sname   char(10)
);
insert  into  表名  values 
(字段值1,字段值2,....);
插入值的顺序 和 desc  出现的字段顺序相同


insert into  testdmla values(1,'abc','abc');


select  length(fname) ,length(sname)  from 
testdmla;


select * from testdmla where fname='abc';


select * from testdmla where sname='abc';


select * from testdmla where fname='abc  ';
select * from testdmla where sname='abc  ';
定长字符串 如果数据长度不够则补空格


insert into testdmla(id,sname) 
values(100,'baoge');


必须包含所有的非空字段


注册一个帐号 
insert into testdmla  values(10086,
'quange','chen');


insert into testdmla  values(10087,
'moyan','mo');




commit;   ------确认本次事务(交易) 
rollback; ------取消本次事务(交易)  


一般  事务谁发起  谁结束


drop table testdmla;
create table  testdmla(
    id    number  primary key,
    fname   varchar2(30),
    sname   char(30)
);
insert into testdmla   select id
,first_name,last_name from s_emp;


insert into testdmla(id,fname)   select id
,first_name from s_emp;


2.删除数据


  delete  from  表名 where 条件;
  
  delete from testdmla where id=1;


  commit;
  rollback;
3.更新数据 update
  update   表名  set  字段名=值
  ,字段名2=值 where  条件;


  update  testdmla  set fname='who'
  where  id=10086;


dml 语句都具备事务特性   事务是非自动
提交的
ddl  是自动提交事务
select  没有事务特性