oracle服务器先事写好的壹段具,数据库之子查询

概念

把3个询问结果作为另3个查询的功底。

回顾:
壹.单行函数
2.表连接
oracle中的表连接
内连接
等值连接
select e.id,e.first_name,d.name
from s_emp e,s_dept d
where e.dept_id = d.id;
select d.id,d.name dname,r.name rname
from s_dept d,s_region r
where d.region_id = r.id;
select e.id,e.first_name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id = r.id;
非等值连接
select e.id,e.first_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary between g.losal and g.hisal;
自连接
select distinct m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id = m.id;

函数:oracle服务器先事写好的壹段具有一定效果的主次片段,内置于oracle服务器,供用户调用

概念

根据一定标准把数据分为若干局部

997755.com澳门葡京 1

子查询出现的地点

  • where  之后
  • having  之后 把结果作为组数据过滤的规则
  • from  之后 把询问结果作为3个表

外连接
等值连接
select e.id,e.first_name,d.name
from s_emp e,s_dept d
where e.dept_id = d.id(+);
select d.id,d.name dname,r.name rname
from s_dept d,s_region r
where d.region_id = r.id(+);
非等值连接
select e.id,e.first_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary between g.losal(+) and g.hisal(+);
自连接
select m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id(+) = m.id
and e.id is null;

单行函数:输入2个参数,输出三个结果,例如:upper(‘baidu.com’)->BAIDU.COM

语法

from  表名
    where  条件
        group  by  分组标准
        (group标准的要写在where之后,没有where写在from之后)        

 

语句:select  dept_id,count(id)  from  s_emp  group  by  dept_id;
  • 其实执行时先进行from  s_emp
  • 再执行group by  dept_id
  • 最后是计算输出select  dept_id,count(id)

997755.com澳门葡京 2

 

 where 之后  作为基准

select distinct manager_id from s_emp;

997755.com澳门葡京 3

 

查询结果应该是几个,不过因为有二个null,所以有八个,不过不影响下边包车型地铁运算。比如:

select id, first_name from s_emp where id in(1,2,3,NULL);

997755.com澳门葡京 4

 

null对其查询未有影响。即使有人的员工id出今后地点的查询结果个中,则该人是理事。

select id, first_name from s_emp where id in(select distinct manager_id from s_emp);

997755.com澳门葡京 5

 

in找到3个,就能判断该字段在里面。那么要找出具有普通职员和工人就无法用“not  in”。(因为有null,它不受控制会潜移默化查询)

997755.com澳门葡京 6

select id, first_name from s_emp where id not in(select distinct manager_id from s_emp where manager_id is not null);

997755.com澳门葡京 7

  select d.id,d.name
      from s_emp e,s_dept d
          where e.dept_id(+) = d.id
             and e.id is null;

多行函数:输入几个参数,或然是当中扫描多次,输出一个结实,例如:count(*)->14

怎么样对组数据进行过滤

  • where是对表数据开始展览过滤的。
  • 组数据过滤有优秀的语法——having
  • having要加在group后面

having之后  把结果作为组数据过滤的规格


总括emp表中职员和工人业总会人数

示范:依据机构号分组,总括每一个单位的人数,展现人数大于二的部门同时按机关号降序排列

select dept_id, count(id) from s_emp group by dept_id having count(id)>2 order by dept_id desc;

997755.com澳门葡京 8

以身作则:根据单位号分组,求每种机构的平均薪酬,须求出示平均薪金大于4二单位的平均薪金

select avg(salary) from s_emp where dept_id=42;

997755.com澳门葡京 9

 

select dept_id, avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp where dept_id=42);

997755.com澳门葡京 10

1.sql9玖规范中的表连接
内连接:
select 字段列表
from 表1 [inner] join 表2
on 关联条件;

select count(*) from emp;

以身作则:遵照部门号分组,总结每一个机构的平均报酬,呈现平均薪金大于1300的单位

select dept_id, avg(salary) from s_emp group by dept_id having avg(salary)>1300;

997755.com澳门葡京 11

 

在分组语句中,select后的字段要么是分组标准,要么是由此合适组函数处理过的内容

子查询出现在from之后

别的3个核发的select语句,都能够用作一张内存表。

 select e.id,e.first_name,d.name
    from s_emp e inner join s_dept d
       on e.dept_id = d.id;

  三表连接:
  select 字段列表
    from 表1 join 表2 on 关联条件1
                   join 表3 on 关联条件2;
  select e.first_name,d.name,r.name
     from s_emp e join s_dept d on e.dept_id = d.id
          join s_region r on d.region_id = r.id; 

oracle服务器先事写好的壹段具,数据库之子查询。注意:*号适用于表字段较少的意况下,假使字段较多,扫描多间多,功效低,项目中倡导使用某3个非null唯壹的字段,常常是主键

举例:依据机构号分组,总计各个部门的平均工资,展现平均工资大于1300的机关,还要来得部门名

997755.com澳门葡京 12

select dept_id, avg(salary), name from s_emp e, s_dept d where dept_id=d.id group by dept_id, name having avb(salary)>1300; 

997755.com澳门葡京 13

(存在部分题材)相配展现次数难点

就此用组函数,因为组函数只会有3个结实, 如若用count:

select dept_id, avg(salary), count(name) from s_emp e, s_dept d where dept_id=d.id group by dept_id having avg(salary)>1300;

997755.com澳门葡京 14

 

如果用max:

select dept_id, avg(salary), max(name) from s_emp e, s_dept d where dept_id=d.id group by dept_id having avg(salary)>1300;

997755.com澳门葡京 15

 

因为大概会有个别机关尚未对应机构名,数据会丢掉,则要全体出示部门表的内容,所以还要加上国外贸大学接连。

select dept_id, avg(salary), count(name) from s_emp e, s_dept d where dept_id=d.id(+) group by dept_id having avg(aslary)>1300;

997755.com澳门葡京 16

演示:  

select id, first_name name, salary from s_emp;

997755.com澳门葡京 17

  。。。。。。

select id,first_name name,salary from
s_emp;  能够看作一张有八个字段的内部存款和储蓄器表

 

select name, salary from(select id, first_name name, salary from s_emp) where salary>1000;

997755.com澳门葡京 18

外连接:
左外连接:内接连的结果集 + 左表相称不上的多少
select 字段列表
from 左表 left [outer] join 右表
on 关联条件;
select e.id,e.first_name,d.name
from s_emp e left join s_dept d
on e.dept_id = d.id;
右外连接:内连接的结果集 + 右表相称不上的数量
select 字段列表
from 左表 right [outer] join 右表
on 关联条件;
select e.id,e.first_name,d.name
from s_emp e right join s_dept d
on e.dept_id = d.id;
全外连接:内再三再四的结果集 + 两表相配不上的数量
select 字段列表
from 左表 full [outer] join 右表
on 关联条件;
select e.id,e.first_name,d.name
from s_emp e full join s_dept d
on e.dept_id = d.id;

总括公司有微微个不另行的机关

 sql语句的施行各类

(1)from   先找到要操作的表

(2)where  会有过滤条件

(3)group  by  分组肯定在having以前

(4)having   对组数据进行过滤  通过上边举例的外号可以规定having和select的实践各种

(5)select    查询输出(语法规则供给select写前边)

(6)order  by   永远在最终

以身作则:遵照单位号分组,求每一个单位的平均薪给,要求出示平均薪资大于4二部门的平均薪给(结合子查询在from之后和where之后)

select dept_id, avg(salary) asal from s_emp group by dept_id;

997755.com澳门葡京 19

select dept_id,avg(salary) asal from s_emp  group by
dept_id  放在from前面作为一张有多少个字段:dept_id  asal  的内部存储器表使用。

   

select * from (select dept_id, avg(salary) asal from s_emp group by dept_id) where asal>(select avg(salary) from s_emp where dept_id=42);

  997755.com澳门葡京 20

 

     select d.id,d.name
       from s_emp e right join s_dept d
          on e.dept_id = d.id
             where e.id is null;

select count(distinct deptno) from emp;

以身作则:根据单位号分组,总计每一个单位的人数,供给出示部门人数大于二的部门,还必要依据人口实行排序

select dept_id, count(id) ac from s_emp group by dept_id having count(id)>2 order by ac;

997755.com澳门葡京 21

 

贰.集合运算(合并结果集)
union: 八个结果集取并集 并排重、排序
union all:多个结实集间接取并集
select id from s_emp union
select id from s_dept;
select id from s_emp union all
select id from s_dept;
intersect: 取多少个结实集的混杂
select id from s_emp intersect
select id from s_dept;
minus: 第贰个结果集 – 第2个结实集
select id from s_emp minus
select id from s_dept;
— 四个结实集的字段列表的数额和数据类型必须合营
select id,first_name from s_emp minus
select id,name from s_dept;

计算有佣金的职工人数

  select id,first_name from s_emp union
  select null,name from s_dept;

select count(comm) from emp;

  1. 组函数和分组
    3.1 组函数
    一) 常用的组函数
    count(par|) : 总计一组数据的行数
    参数能够是此外项目 还足以是

    — 计算薪金超过1500的职员和工人数
    select count(*) from s_emp where salary>1500;
    max(par) : 总括一组数据中的最大值
    min(par):总计一组数据中的最小值
    参数能够是数字、字符串、日期类型
    — 列出最早和最晚入职的职员和工人
    select max(to_char(start_date,’yyyy-mm-dd’)),
    min(to_char(start_date,’yyyy-mm-dd’))
    from s_emp;
    sum(par) : 总括壹组数据的和
    avg(par) : 总括1组数据的平均值
    参数是数字类型
    — 列出销售部的总工资和平均薪俸
    select sum(salary),avg(salary)
    from s_emp e,s_dept d
    where e.dept_id = d.id and d.name=’Sales’;

    2) 组函数对null的拍卖: 忽略
    select count(commission_pct) from s_emp;
    三) 组函数 能够排重
    select sum(salary),sum(distinct salary) from s_emp;
    select count(salary),count(distinct salary) from s_emp;

只顾:那个多个行函数,不总结NULL值

3.2 分组
3.2.1 语法
group by 分组标准

职工业总会薪资,平均薪资,四舍5入,保留小数点后0位

    .....
        where ....
           group by ...
             ...
               select
                 order by

  3.2.2 分组统计每个部门的人数
      select dept_id,count(*) cnt
         from s_emp
            group by dept_id;
      /* 分组语句中列出的字段必须是分组标准
          或者是组函数的参数*/
      select id,dept_id,count(*) cnt
         from s_emp
            group by dept_id;  -- 错误

  3.2.2 多列分组
     分组统计每个部门的人数,显式部门的名称和人数
     select e.dept_id,d.name,count(e.id) cnt
        from s_emp e join s_dept d on e.dept_id = d.id
            group by e.dept_id,d.name;


     分组统计每个部门的人数,列出人数超过2个的部门

     select dept_id,count(*) cnt
         from s_emp
            -- 错误:where子句中不能使用组函数
            -- where count(*)>2 
              group by dept_id;

 3.2.3 having子句
    分组后,根据条件筛选出符合条件的组
    select dept_id,count(*) cnt
        from s_emp
           where 1=1
             group by dept_id
                having count(*)>2
                   order by cnt;

    -- 语法顺序
    select 字段列表
        from 表名
           where 条件   -- 从表中根据条件筛选符合条件的行
             group by 分组标准  -- 根据分组标准分成多个组
                having 条件  -- 从分组结果中根据条件筛选符合条件的组
                   order by 排序标准 排序方式; 

     -- 执行顺序
     from
       where 
          group by
            having
              select
                 order by

select sum(sal) “总薪金”,round(avg(sal),0) “平均报酬”

— 演习:列出平均薪资大于1000 的机构的音信
select dept_id,avg(salary) avgsal
from s_emp
group by dept_id
having avg(salary)>1000;

from emp;

select d.id,d.name,avg(e.salary) avgsal
    from s_emp e,s_dept d
        where e.dept_id = d.id
            group by d.id,d.name
                having avg(e.salary)>1000
                    order by avgsal;

查询员工表中最高级工程师资,最低薪水

  1. 子查询
    子查询指的是把一条select语句嵌入到另一条sql语句中
    推行时,先实施放到的子查询,然后再进行外层的sql语句
    4.1 where子句
    一) 单行单列的子查询
    — 列出薪水比’Ben’高的职工的新闻
    a. 列出’Ben’的工资
    select salary from s_emp where first_name=’Ben’;
    — 1100
    b. 列出薪资超越’Ben’的职员和工人的新闻
    select id,first_name,salary from s_emp
    where salary>1100;
    c. 合并
    select id,first_name,salary from s_emp
    where salary>(
    select salary from s_emp where first_name=’Ben’
    );
    二) 多行单列的结果集
    子查询的结果集为多值时,不能够动用相比运算符
    急需运用处理多值的运算值,比如in、not in、any、all等
    (any、all要和相比运算符协作使用:>any、<all)
    — 使用子查询列出富有的公司主的音信
    a) 列出老总的号子
    select distinct manager_id from s_emp;
    — null,1,2,3,6,7,8,9,10
    b) 依据编号,列出官员的音讯
    select id,first_name,title from s_emp
    where id in(null,1,2,3,6,7,8,9,10);
    c) 合并
    select id,first_name,title from s_emp
    where id in(
    select distinct manager_id from s_emp);
    — 使用子查询列出普通职员和工人的音信
    select id,first_name,title from s_emp
    where id not in(
    select distinct manager_id from s_emp
    where manager_id is not null);
    3) 使用exists
    — 列出有职员和工人的单位的音讯
    select * from s_dept d where exists(
    select * from s_emp e where e.dept_id=d.id
    );

select max(sal) “最高级工程师资”,min(sal) “最低工资”

4.2 having子句
— 列出平均薪金高于公司平均薪俸的部门的新闻
select dept_id,avg(salary) from s_emp
group by dept_id
having avg(salary)>(
select avg(salary) from s_emp
);
4.3 from子句
二个select语句产生的结果集,能够作为是1个内视图或然匿名视图,只可以在时下说话使用
select id,name,yearsal from
(select id,first_name name,12*salary+1000 yearsal from s_emp) e
where yearsal>15000;

from emp;

--  列出工资高于本部门平均工资的员工的信息    
 a) 列出每个部门的编号及其平均工资
   select dept_id,avg(salary) avgsal from s_emp
        group by dept_id;  
   -- 相当于包含dept_id和avgsal两个字段的一张表  s
 b) 使用表连接(s_emp、s) 实现功能
    s_emp: id,first_name,salary
    s: avgsal
    关联字段:  s_emp.dept_id和s.dept_id
    select e.id,e.first_name,e.salary,s.avgsal
      from s_emp e,(
          select dept_id,avg(salary) avgsal
             from s_emp
               group by dept_id) s
           where e.dept_id = s.dept_id
              and e.salary > s.avgsal;

入职最早,入职最晚员工

4.4 select之后
外接连的另壹种实现格局,并且更加灵活
— 列出员工及其所在机关的音讯
select id,first_name,salary,(
select name from s_dept d where e.dept_id=d.id
) dname
from s_emp e;

select max(hiredate) “最晚入职时间”,min(hiredate) “最早入职时间”

-- 练习:列出所有和'Mark'在同一部门的员工的信息
select id,first_name,dept_id from s_emp
    where dept_id=
(select dept_id from s_emp where first_name='Mark');

from emp;

对象名_姓名缩写_座位号

多行函数:count/sum/avg/max/min

xxxx_zsm_00

伍.表的操作
5.一 表的创办和删除
5.一.一 标识符的命名
1) 由a-z、A-Z、0-9、_、$、#构成
二)必须用假名初阶
三) 无法和首要字重名
四) 无法和任何的数据库对象重名
5) 1-30位
5.1.2 创建表
— 语法
create table 表名(
字段名 数据类型,
….
字段名 数据类型
);
create table testid_zsm_00(
id number,
name varchar2(20)
);
5.1.3 删除表
drop table 表名;
drop table testid_997755.com澳门葡京,zsm_00;

5.二 数据操作语句(DML)
— 创制1个测试表
八个字段:
编号 数字
名字 字符串
入职日期 日期
create table emp_zsm_00(
id number(7),
name varchar2(20),
start_date date
);

5.2.1 插入语句(insert)  -- 一次一整行
  1) 语法
   insert into 表名[(字段列表)] values(值列表);
   字段列表的数量和顺序和值列表的数量和顺序必须一致
  2) 不省略字段列表
   -- 写入全部字段
   insert into emp_zsm_00(id,name,start_date)
         values(1,'test1','13-OCT-17');
   commit;
   -- 写入部分字段 ( 没有给值的字段必须允许为null )
   insert into emp_zsm_00(id,name)
         values(2,'test2');
   commit;
 3) 省略字段列表(相当于给出了全部字段,并且顺序和表结构一致)
     -- 给全部字段提供值
     insert into emp_zsm_00 values(3,'test3',sysdate);  
     commit;       
      insert into emp_zsm_00 values(4,'test4',
           to_date('2017-10-13','yyyy-mm-dd')); 
     commit;
     -- 给部分字段提供值
      insert into emp_zsm_00 values(5,'test5',null); 
     commit;

伍.二.贰 更新语句(update)
1) 语法
update 表名 set 字段 = 新值[,字段 = 新值,…][where 条件];
二) — 更新 把入职日期改为’2017-0一-20′
update emp_zsm_00 set start_date=’12-JAN-17′;

  3) -- 把id=1的名字改为'Ben',入职日期改为当前日期
      update emp_zsm_00 set name='Ben',
          start_date=sysdate where id=1;
      commit;

5.2.3 删除语句(delete) -- 整行删除
  1) 语法
    delete [from] 表名 [where子句];
  2) 删除表中全部数据
     delete from emp_zsm_00;
     rollback; -- 撤销没有提交的操作
  3) 带where子句的删除
     delete from emp_zsm_00 where name like 'test%';
     commit;

5.三 事务控制语句 (TCL)
select: 和工作非亲非故
ddl: 隐式提交的业务
dml: 暗许必要显式提交

5.3.1 事务控制语句的含义
  commit;     确认事务(提交所有未提交的操作)
  savepoint 保存点;  定义保存点
  rollback;   回滚事务(撤销所有未提交的操作)
  rollback to 保存点; 回顾到保存点的位置

5.3.2 事务的四大特性(ACID)
  1) 原子性:事务中的语句是一个不可分割的整体
    转账:
    账户表:account
    字段:id   balance
    A账户---> B账户  2000
    update account set balance = balance - 2000
              where  id='A';
    a
    update account set balance = balance + 2000
              where  id='B'; 
    b
    if a&&b
      commit;
    else
       rollback;

    要么全部成功,要么全部失败

 2)一致性
   事务执行的结果必须是使数据库从一种一致性状态变为另一种一致性状态

 3) 隔离性
    一个事务对数据的改变,在提交之前,对于其他的事务是不可见的。
 4)  持久性
   事务一旦提交,对数据的改变就是永久的

伍.三.叁 部分成功 部分退步
— 删除表中全体数据 属于ddl语句,不能够收回
truncate table emp_zsm_00;

   insert into emp_zsm_00(id,name) values(1,'test1');
   savepoint a;
   insert into emp_zsm_00(id,name) values(2,'test2');
   savepoint b;
   insert into emp_zsm_00(id,name) values(3,'test3');
   savepoint c;
   insert into emp_zsm_00(id,name) values(4,'test4');
   select * from emp_zsm_00;
   rollback to b;
   commit;
   select * from emp_zsm_00;

练习:
一.施用select语句建表:
drop table emp_zsm_00;
create table emp_zsm_00 as select * from s_emp;
2.给拥有’Carmen’的手下人涨薪给
1) 查询’Carmen’的编号
2) 依照’Carmen’的数码 查询其属下的编号
3) 依照职员和工人编号 改薪金
三.删减和’Ben’同单位的职工

按单位求出该单位平均薪水,且平均薪给取整数,选取截断

select deptno “部门编号”,trunc(avg(sal),0) “部门平均薪水”

from emp

group by deptno;

(继续)查询部门平均报酬大于三千元的机构

select deptno “部门编号”,trunc(avg(sal),0) “部门平均薪酬”

from emp

group by deptno

having trunc(avg(sal),0) > 2000;

(继续)按部门平均工资降序排列

select deptno “部门编号”,trunc(avg(sal),0) “部门平均工资”

from emp

group by deptno

having trunc(avg(sal),0) > 2000

order by 2 desc;

除10号部门外,查询部门平均薪水大于两千元的单位,格局一【having
deptno<>10】

select deptno,avg(sal)

from emp

group by deptno

having deptno<>10;

除10号部门外,查询部门平均薪金大于两千元的单位,格局2【where
deptno<>10】

select deptno,avg(sal)

from emp

where deptno<>10

group by deptno;

提倡

体现部门平均薪俸的最大值

select max(avg(sal)) “部门平均薪酬的最大值”

from emp

group by deptno;

考虑:展现部门平均薪资的最大值和该部门编号?

select max(avg(sal)) “部门平均工资的最大值”,deptno “部门编号”

from emp

group by deptno;

错误

group by 子句的底细:

一)在select子句中出现的非多行函数的有所列,【必须】出现在group by子句中

二)在group by子句中现身的拥有列,【可出现可不现】在select子句中

where和having的区别:

where:

①)行过滤器

二)针对原始的笔录

3)跟在from后面

4)where可省

5)先执行

having:

一)组过滤器

2)针对分组后的笔录

3)跟在group by后面

4)having可省

5)后执行

oracle中综合语法:

1)select子句—–必须

二)from子句——-必须,不知写什么表了,就写dual

3)where子句——可选

4)group by子句—可选

5)having子句—–可选

六)order by 子句–可选,要是出现列名,外号,表明式,字段

————————————————————————————-多表查询

职工表emp和机构表dept的笛卡尔集(笛Carl集表=列数之和,行数之积,笛Carl集表内中稍加数据是不符合供给的)

select emp.ename,dept.dname

from emp,dept;

选拔等值连接/内接连(只好选拔=号),显示职员和工人的号码,姓名,部门名,使用表外号简化

select emp.empno,emp.ename,dept.dname,dept.deptno

from emp,dept

where emp.deptno = dept.deptno;

行使非等值连接(无法使用=号,其它符号能够,例如:>=,<=,<>,betwen
and等),展现职员和工人的号码,姓名,月薪,薪给级别

select e.empno,e.ename,e.sal,s.grade

from emp e,salgrade s

where e.sal between s.losal and s.hisal;

内接连查询:只可以查询出符合条件的记录

外接连查询:既能查询出符合条件的笔录,也能依据1方强行将另3个方查询出来

运用外接连,按机关十,20,30,40号,总计各机构职工人数,必要出示部门号,部门名,人数

部门号 部门名 人数

10 ACCOUNTING 3

20 RESEARCH 5

30 SALES 6

40 OPERATIONS 0

等值连接/非等值连接/内连接:只会询问出多张表中,依照有个别字段相称,符合条件的笔录,不符合条件的记录是不会存在的

左外连接[是oracle专用的,不是SQL99规则]:

select dept.deptno “部门号”,dept.dname “部门名”,count(emp.empno) “人数”

from dept,emp

where dept.deptno = emp.deptno(+)

group by dept.deptno,dept.dname;

右外连接:

select dept.deptno “部门号”,dept.dname “部门名”,count(emp.empno) “人数”

from dept,emp

where emp.deptno(+) = dept.deptno

group by dept.deptno,dept.dname;

使用左外连接,按单位10,20,30,40号,总结各机构员工人数,须求出示部门号,部门名,人数,且按人头降序排列

select dept.deptno “部门号”,dept.dname “部门名”,count(emp.empno) “人数”

from dept,emp

where dept.deptno = emp.deptno(+)

group by dept.deptno,dept.dname

order by 3 desc;

使用自连接,呈现”SMITH的上司是FOHummerH二D”这种格式

select users.ename || ‘的下面是’ ||boss.ename

from emp users,emp boss

where users.mgr = boss.empno;

只有13条记录,不含有KING

依照上述难点,将KING的上司是“”展现出来

select users.ename || ‘的上司是’ ||boss.ename

from emp users,emp boss

where users.mgr = boss.empno(+);

14条记录

小心:自连接也用到内一而再和外接连

————————————————————————————-子查询

子查询的机能:查询条件未知的东西

询问条件已知的标题:例如:查询薪给为800的职员和工人音信

查询条件未知的题材:例如:查询工资为20号部门平均薪金的职工音信

三个准绳未知的题目,能够表明为多个标准化已知的难题

询问薪俸比WAEvoqueD高的职工消息

第一:查询WARD的工资?

select sal from emp where ename = ‘WARD’;

第3:查询薪水比1250高的职员和工人音讯?

select * from emp where sal > 1250;

子查询:

select *

from emp

where sal > (

select sal

from emp

where ename = ‘WARD’

);

查询部门名叫’SALES’的员工消息(格局1:子查询)

先是:查询部门名叫’SALES’的号码?

select deptno from dept where dname = ‘SALES’;

其次:查询部门号为30的职工音信?

select * from emp where deptno = 30;

子查询:

select *

from emp

where deptno = (

select deptno

from dept

where dname = ‘SALES’

);

子查询细节:

一)子查询与父查询能够本着同一张表

贰)子查询与父查询能够针对区别张表

三) 子查询与父查询在价值观参数时,数量要平等

四) 子查询与父查询在古板参数时,类型要一致

五) 子查询与父查询在观念参数时,含义要一致

询问部门名字为’SALES’的职工新闻(情势2:多表查询)

select emp.*

from dept,emp

where (dept.deptno=emp.deptno) and (dept.dname=’SALES’);

询问各种职员和工人编号,姓名,部门名,薪给等级(3表查询,那三张表并无外健关联)

select e.empno,e.ename,d.dname,s.grade

from emp e,dept d,salgrade s

where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);

查询报酬低于的职工信息(单行子查询,使用=号)

先是:查询出薪俸低于是多少?

select min(sal) from emp;

第二:查询薪金为800的职员和工人消息?

select * from emp where sal = 800;

子查询:

select *

from emp

where sal = (

select min(sal)

from emp

);

查询部门名字为’ACCOUNTING’或’SALES’的职员和工人音讯(多行子查询,使用in关键字)

率先:查询部门名称叫’ACCOUNTING’或’SALES’的单位编号?

select deptno from dept where dname in (‘ACCOUNTING’,’SALES’);

第三:查询部门号为拾或30号的职员和工人消息?

select * from emp where deptno in (10,30);

子查询:

select *

from emp

where deptno in (

select deptno

from dept

where dname in (‘ACCOUNTING’,’SALES’)

);

查询薪俸比20号部门【任意any】2个职员和工人薪水【低<】的职工音讯(多行子查询,使用any关键字)

率先:查询20号部门的装有报酬?

select sal from emp where deptno = 20;

其次:查询薪俸比(800,297五,三千,1十0,3000)任意2个低的职工消息?

select * from emp where sal < any (800,2975,3000,1100,3000);

在oracle看来,

子查询:

select *

from emp

where sal

select sal

from emp

where deptno = 20

);

查询工资比30号部门【全数all】职员和工人【低<】的职员和工人新闻(多行子查询,使用all关键字)

先是:查询出30部门有着职工的薪水?

select sal from emp where deptno = 30;

其次:查询薪金比(1600,1250,1250,2850,1500,950)中具有的工钱都低的职工新闻?

select * from emp where sal

子查询:

select *

from emp

where sal

select sal

from emp

where deptno = 30

);

瞩目:学员们,不简单驾驭的多少个概念:

单行函数:输入一个参数,输出三个结出

多行函数:扫描多少个参数,输出三个结果

单行子查询:子查询只会回来2个结出,例如:800,父查询用=/<>/>=/<=那个标记来相比较

多行子查询:子查询会再次回到多于二个结果,例如:30,20,父查询用in/any/all那个标记来相比较

当多表查询,子查询同时能缓解难点时,按如下优先方案选拔:

多表查询–>子查询

瞩目:上述结果不是说多表查询能够替代子查询,有个别情状下,只可以用子查询消除,例如:oracle分页

————————————————————————————-集合查询

选拔并集运算,查询20号部门或30号部门的员工音信

select * from emp where deptno = 20

union

select * from emp where deptno = 30;

注意:

union:一个聚众中,倘诺都有雷同的,取其1

union all:三个聚众中,假如都有相同的,都取

运用set time/timing on,打开时间的开关

set time on;

set time off;

应用set tim/timing off,关闭时间的开关

set timing on;

set timint off;

行使交集运算[intersect],查询薪酬在1000-三千和1500-2500中间的职员和工人新闻(方式壹)

select * from emp where sal between 1000 and 2000

intersect

select * from emp where sal between 1500 and 2500;

用where行过滤,查询工资在1000-两千和1500-2500之内的职工音讯(格局2)

select *

from emp

where (sal between 1000 and 2000) and (sal between 1500 and 2500);

利用差集运算[minus],查询报酬在1000-两千,但不在1500-2500里面包车型大巴职工新闻(格局1)

select * from emp where sal between 1000 and 2000

minus

select * from emp where sal between 1500 and 2500;

应用where行过滤,查询工资在一千-3000,但不在1500-2500之间的职工音信(形式2)

select *

from emp

where (sal between 1000 and 2000) and (sal not between 1500 and 2500);

集结查询的底细:

1)集合操作时,必须有限辅助集合列数是相等

select empno,ename,sal,comm from emp where deptno = 20

union

select empno,ename,sal from emp where deptno = 30;错

二)集合操作时,必须确定保障集合列类型对应相同

select empno,ename,sal,comm from emp where deptno = 20

union

select empno,ename,sal,hiredate from emp where deptno = 30;错

3)A union B union C = C union B union A

select * from emp where deptno = 10

union

select * from emp where deptno = 20

union

select * from emp where deptno = 30;

四)当四个汇集操作时,结果的列名由第二个聚众列名决定

select empno “编号”,ename “姓名”,sal “薪水” from emp where deptno = 20

union

select empno,ename,sal from emp where deptno = 10;

当多表查询,子查询,集合查询都能到位同样职责时,按如下优化方案选取:

多表查询->子查询->集合查询

————————————————————————————-oracle分页

回顾mysql分页

用limit关键字

查询users表中前2条记下

select * from users limit 0,2

select * from users limit 2;

0意味第三条记下的索引号,索引号从0起始

二象征最多选取三个记录

询问出users前三条记下

select * from users limit 0,3

select * from users limit 3

查询出users第2条到第4条记录

select * from users limit 1,3;

回顾hibernate分页API

Query.setFirstResult(0);

Query.setMaxResult(3);

 

怎样是rownum,有啥特点

1)rownum是oracle专用的关健字

贰)rownum与表在壹齐,表亡它亡,表在它在

三)rownum在暗许意况下,从表中是查不出去的

4)唯有在select子句中,明显写出rownum才能显得出来

伍)rownum是number类型,且唯再三再四续

陆)rownum最小值是1,最大值与您的笔录条数相同

7)rownum也能参加关系运算

* rownum = 1 有值

* rownum < 5 有值

* rownum <=5 有值

* rownum > 2 无值

* rownum >=2 无值

* rownum <>2 有值 与 rownum < 2 相同

* rownum = 2 无值

八)基于rownum的特征,大家一般rownum只用于<或<=关系运算

展现emp表中三-8条记下(格局一:使用集合减运算)

select rownum “伪列”,emp.* from emp where rownum<=8

minus

select rownum,emp.* from emp where rownum<=2;

展现emp表中三-8条记下(格局贰:使用子查询,在from子句中央银行使,重点)

select xx.*

from (select rownum ids,emp.* from emp where rownum<=8) xx

where ids>=2;

注意:在子查询中的别称,不可加””引号

显示emp表中5-9条记录

select yy.*

from (select rownum ids,emp.* from emp where rownum<=9) yy

where ids>=5;

小心:在类型中,from后台恐怕有真实表名,也大概用子查询看作的表名,

并且真实表和子查询看作的表要做连接查询

————————————————————————————-创设表和平条约束

追思MySQL创制表语句users(id整型/name字符串/birthday日期型,暗中同意明日)

drop table if exists users;

create table if not exists users(

id int(5) auto_increment primary key,

name varchar(4) not null,

birthday date default ‘2015-4-27’

);

选用oracleSQL,创设用户表users(id整型/name字符串/birthday日期/sal整型,暗中同意今天)

create table users(

id number(5) primary key,

name varchar2(8) not null unique,

sal number(6,2) not null,

birthday date default sysdate

);

跻身回收站

drop table users;

询问回收站中的对象

show recyclebin;

闪回,即将回收站还原

flashback table 表名 to before drop;

flashback table 表名 to before drop rename to 新表名;

根本剔除users表

drop table users purge;

清空回收站

purge recyclebin;

 

测试如下类型

(1)number(5):

insert into users(id,name,sal) values(1,’A’,6666.66);

insert into users(id,name,sal) values(11,’AA’,6666.66);

insert into users(id,name,sal) values(111,’AAA’,6666.66);

insert into users(id,name,sal) values(1111,’AAAA’,6666.66);

insert into users(id,name,sal) values(99999,’AAAAA’,6666.66);

insert into users(id,name,sal) values(100000,’AAAAAA’,6666.66); 错

五代表最多存9999玖

(2)number(6,2):

col sal for 9999.99

insert into users(id,name,sal) values(1,’A’,6.66);

insert into users(id,name,sal) values(11,’AA’,66.666);

insert into users(id,name,sal) values(111,’AAA’,666.6666);

insert into users(id,name,sal) values(1111,’AAAA’,6666.66666);

insert into users(id,name,sal) values(11111,’AAAAA’,66666.666666);错

number(6,2)

中间二表示最多彰显3人小数,接纳四舍5入,不足位数补0,同时要设置col …
for …

当中陆表示小数+整数不多于6个人

其间整数位数不得多于2位,能够等于3人

(3)varchar2(8):

insert into users(id,name,sal) values(1,’A’,7777.77);

insert into users(id,name,sal) values(2,’AA’,7777.77);

insert into users(id,name,sal) values(3,’AAA’,7777.77);

insert into users(id,name,sal) values(4,’AAAA’,7777.77);

insert into users(id,name,sal) values(5,’AAAAA’,7777.77);

insert into users(id,name,sal) values(6,’AAAAAA’,7777.77);

insert into users(id,name,sal) values(7,’AAAAAAA’,7777.77);

insert into users(id,name,sal) values(8,’AAAAAAAA’,7777.77);

insert into users(id,name,sal) values(9,’AAAAAAAAA’,7777.77);错

 

insert into users(id,name,sal) values(1,’哈’,7777.77);

insert into users(id,name,sal) values(2,’哈哈’,7777.77);

insert into users(id,name,sal) values(3,’哈哈哈’,7777.77);

insert into users(id,name,sal) values(四,’哈哈哈哈’,7777.7七);

insert into users(id,name,sal) values(5,’哈哈哈哈哈’,7777.7七);错

8意味着字节

GBK 赵 2字节

(4)date:默许格式为:’27-十月-15′

(伍)CLOB【Character Large
OBject】:大文本对象,即超过655陆伍字节的数据对象,最多存款和储蓄四G

(6)BLOB【Binary Large
OBject】:大贰进制对象,即图片,音频,录制,最多囤积肆G

为emp表增加image列,alter table 表名 add 列名 类型(宽度)

alter table emp

add image blob;

修改ename列的长短为十九个字节,alter table 表名 modify 列名 类型(宽度)

alter table emp

modify ename varchar2(20);

删除image列,alter table 表名 drop column 列名

alter table emp

drop column image;

重名列名ename为username,alter table 表名 rename column 原列名 to 新列名

alter table emp

rename column ename to username;

将emp表重命名emps,rename 原表名 to 新表名

rename emp to emps;

小心:修改表时,不会影响表中原有的数据

笔试题:有【一千亿】条会员记录,怎么样用最飞速的格局将薪金字段清零,其余字段内容不变?

首先:从emp表中删去sal字段

alter table emp

drop column sal;

其次:向emp表中添加sal字段,且内容暗许0

alter table emp

add sal number(6) default 0;

修改表不可回滚

创建表customers(单)和orders(多),使用primary key/not
null/unique/default/foreign key约束

要体现【on delete cascade/on delete set null】

要求:删除客户,级联删除他拥有的订单

delete from customers where id = 1;

要求:删除客户,不级联删除他拥有的订单,只是将外健设置为NULL

delete from customers where id = 1;

create table customers(

id number(3) primary key,

name varchar2(4) not null unique

);

insert into customers(id,name) values(1,’A’);

insert into customers(id,name) values(2,’B’);

create table orders(

id number(3) primary key,

isbn varchar2(6) not null unique,

price number(3) not null,

cid number(3),

–constraint cid_FK foreign key(cid) references customers(id) on delete
cascade

constraint cid_FK foreign key(cid) references customers(id) on delete
set null

);

insert into orders(id,isbn,price,cid) values(1,’isbn10′,10,1);

insert into orders(id,isbn,price,cid) values(2,’isbn20′,20,1);

insert into orders(id,isbn,price,cid) values(3,’isbn30′,30,2);

insert into orders(id,isbn,price,cid) values(4,’isbn40′,40,2);

创设表students,包含id,name,gender,salary字段,使用check约束【性别只好是男或女,薪酬介于5000到8000之间】

create table students(

id number(3) primary key,

name varchar2(4) not null unique,

gender varchar2(2) not null check ( gender in (‘男’,’女’) ),

salary number(6) not null check ( salary between 6000 and 8000 )

);

insert into students(id,name,gender,salary)
values(1,’哈哈’,’中’,6000);错

insert into students(id,name,gender,salary)
values(2,’呵呵’,’男’,5000);错

insert into students(id,name,gender,salary)
values(3,’嘻嘻’,’女’,7000);对

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website