Oracle

一，oracle的子查询

当一个select 语句被嵌套在另一个select 、update语句或delete语句中时被嵌套的sql语句就是子查询。在被执行的sql语句中，子查询也被称为内查询。包含了子查询语句的查询语句也被称为外查询语句。一般说外查询语句执行一地，子查询语句执行一遍

连接查询的效率要比子查询的效率高很多

 

Select * from 表名 where 列名 操作符（select *from 表名） 操作符可为<>= in notin…

 

子查询:子查询在主查询之前执行一次,子查询的结果被用于主查询.
   
    子查询使用原则:
                   1.子查询放在圆括号中.
                   2.将子查询放在比较条件的右边.
                   3.一般不在子查询中的order by子句不需要,除非你正在执行Top-N分析.
                   4.在单行子查询中用单行运算符,在多行子查询中用多行运算符.
                   5.子查询的语句可以放在where,having,from,select,create view,
                                         creat table,update,insert into和update的set子句.
       单行子查询:
                   仅返回一行.
                   使用单行比较符,= > >= < <= <>
      
       在子查询使用组函数：
                   select last_name.job_id,salary
                   from employees
                   where salary = (select min(salary) from employess);
      
       带子查询的having子句:
                    select department_id,min(salary)
                    from emplyees
                    group by department_id
                    having min(salary) > (select min(salary)
                                         from  employees
                                         where department_id = 50)
      
       注意:单行运算符用于多行子查询的错误
                     select emloyee_id,last_name
                     from emplyees
                     where salary = (select min(salary)
                                      from  emplyees 
                                      group by department_id);
       修改方案:=换成in
 
-----------------------------------------
       多行子查询:返回多于一行,使用多行比较符.
                 
                   in:等于列表中的任何成员
                   any:比较子查询返回的每个值
                   all:返回子查询返回的全部值

                   select last_name,salary,department_id
                   from employees
                   where salary in (select min(salary)
                                    from employeess
                                    group by deparment_id);
                   内查询先被执行,产生一个查询结果,然后主查询块处理和使用由内查询返回的
                   值完成它的搜索.
                   select  select last_name,salary,department_id
                   from employees
                   where salary in (2500,4200,4400,6000,7000,8300,8600,17000);
                   

        在多行子查询使用any运算符:
                  select employee_id,last_name,job_id,salary
                  from employees
                  where salary < any(select salary from employees
                                  where job_id ='it_prog')
                  and job_id <>'It_prog'
              
                  any:比较一个值与一个子查询返回的每一个值.上面的例子显示不是程序员的工资,而且
                  这些雇员的薪水少于IT程序员.
                  < any 意思是小于每一个的值,小于最大值   >any 大于所有的值,即大于最小值

        在多行子查询使用all运算符:
                  select employee_id,last_name,job_id,salary
                  from employees
                  where salary < all(select salary from employees
                                  where job_id ='it_prog')
                  and job_id <>'It_prog'
              
                  any:比较一个值与一个子查询返回的每个值.上面的例子显示不是程序员的工资,而且
                  这些雇员的薪水少于IT程序员的最低工资.
                  < all 意思是小于最小值   >all 意思是大于最大值
                  not 运算符可以与 in,any和all运算符一起使用.

      子查询的的空值:
                 select emp.last_name
                 from employees emp
                 where emp.employee_id not in (
                                            select mgr.manager_id from employees mgr)
 
                 由于内查询的结果为空.因为补角一个空值的结果还是空,所以,只要空值可能是子查询结果集
                 就不能用not in ,not in 相当于<> all,in 相当于 =any

二，join的使用方法

- 创建CityList（城市列表）
create table CityList(
CityRowId int identity(1,1),
CityCode  varchar(10) not null,
CityName varchar(30) not null,
ProvCode varchar(3)
)

--修改CityList表中字段ProvCode为 唯一性
alter table CityList
  add constraint unique_provcode
      unique(ProvCode)
--删除唯一性约束
alter table CityList
  drop constraint unique_provcode
--删除表CityList中的所有数据(可以加where条件)
delete  from CityList
--truncate table 语句可以删除表格中的所有数据,只留下一个表格的定义(不允许带where条件)
truncate table CityList
--添加数据
insert CityList ( CityCode,CityName,ProvCode) values('0010001','北京','001')

insert CityList  values
   --('0020001','上海','002')
   --('0030001','南京','003')
   --('0030002','无锡','003')
  --('0030003','苏州','003')
   --('0040001','杭州','004')
  -- ('0040002','绍兴','004')
    ('0050001','巢湖','005')
update CityList set ProvCode='003' where ProvCode='0023'

--创建CityProv（省份列表）
create table CityProv (
ProvRowId int identity(1,1),
ProvCode varchar(3) not null,
ProvName varchar(30) not null
)

insert CityProv values
            -- ('001','北京')
            -- ('002','上海')
            -- ('003','江苏')
            -- ('004','浙江')
             ('005','安徽')
delete from CityList where CityCode='0020001'
--查询数据
select * from CityList
select * from CityProv
select * from citylist inner join cityprov on citylist.ProvCode=cityprov.ProvCode
select * from cityprov  inner join citylist on citylist.ProvCode=cityprov.ProvCode

select * from citylist left outer join cityprov on citylist.ProvCode= cityprov.ProvCode
select * from cityprov left outer join citylist on citylist.ProvCode = cityprov.ProvCode

select * from citylist right outer join cityprov on citylist.ProvCode=cityprov.ProvCode
select * from cityprov right outer join citylist on citylist.ProvCode=cityprov.ProvCode

--查看表
sp_help CityList
--数据完整性的分类(实体完整性,值域完整性,引用完整性,用户定义的完整性)


--创建自定义的数据类型
exec sp_addtype typename --用户自定义数据类型的名字
                 system_data_type  --用户自定义数据类型所基于的系统数据类型
                 nulltype  --指定该用户定义数据类型是否可以为null
--删除用户自定义数据类型(若正在使用则不可以删除)
三，在oracle的子查询中使用some,any和all

用some,any和all对子查询中返回的多行结果进行处理。下面我们来简单介一下这几个关键词的含义。

• Some在此表示满足其中一个的意义，是用or串起来的比较从句。
• Any也表示满足其中一个的意义，也是用or串起来的比较从句，区别是any一般用在非“=”的比较关系中，这也很好理解，英文中的否定句中使用any肯定句中使用sone，这一点是一样的。
• All则表示满足其其中所有的查询结果的含义，使用and串起来的比较从句。

下面是一些例子
找出员工中，只要比部门号为10的员工中的任何一个员工的工资高的员工的姓名个工资。也就是说只要比部门号为10的员工中的那个工资最少的员工的工资高就满足条件。

select ename,sal
From emp
Where sal > any(select sal from emp where deptno = 10);

这里推荐用any，如果你非要用some也是没有任何问题的，结果是一样的，只是一般来讲some用在“=”的比较从句中。
select ename,sal
From emp
Where sal > some(select sal from emp where deptno = 10);

上面的用法完全OK的。
select ename,sal
From emp
Where sal = some(select sal from emp where deptno = 30) and deptno not in (select deptno from emp where deptno = 30);

上面才是some的正常用法。其含义是找到和30部门员工的任何一个人工资相同的那些员工。虽然没有找到。
最后一个关键字all的用法也很简单就是要与子查询的每一结果都要匹配。

select ename,sal
From emp
Where sal > all(select sal from emp where deptno = 20);

上面的SQL语句的意义与前面的就完全不一样了，其意义是找到比部门号为20的员工的所有员工的工资都要高的员工，也就是比那个工资最高的员工的还要高的员工
总的来说some和any用法意义是一样的，仅在词法上有不同，都表示对子查询结果集中“或”的比较关系，而all则是对子查询结果集总每一个结果“与”的关系