Oracle_DAY03

来源：互联网发布：人民公网移动wifi知乎编辑：程序博客网时间：2024/06/07 06:09

1.用子查询,查询谁是普通员工

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');

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') fromdual;

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;