四、查询和对表的相关操作

1、  单行子查询：

多行子查询是指返回单列多行数据，都是针对单列而言

情形1）

select * from student where sal>(select sal from  student where xuehao=12);

备注：

子查询要包含在括号内；将子查询放在比较条件的右侧；

单行操作符对应单行子查询，多行操作符对应多行子查询；

单行子查询，只返回一行，使用单行比较操作符

情形2）在子查询中使用组函数：

select * from student where sal=(select min(sal) from  student where xuehao=12);

情形3）子查询中的 HAVING 子句

select classid,min(sal) from student

group by classid

having min(sal)>

       (select sal from student where xuehao=11);

--select中要是进行xuehao的查询会报：xuehao,不是group by 表达式

备注：group by 后面的字段一定要出现在select 后面的

 

2、多行子查询

         返回多行、使用多行比较操作符

IN       等于列表中的任意一个

ANY     和子查询返回的某一个值比较

ALL   和子查询返回的所有值比较

情形1）all表示的是：满足比最大的都大的

select xingming,xuehao,sal from student

where sal>all(select sal from student where classid=1);

情形2）any表示的是：只需满足比最小的大

select xingming,xuehao,sal from student

where sal>any(select sal from student where classid=1);

情形3）in表示的是：在其中即可

select xingming,xuehao,sal from student

where sal in (select sal from student where classid=1);

 

3、单列子查询

单列子查询是指子查询只返回单列、单行数据；

--单列子查询

select * from student where sal=(select sal from student where xuehao=1);

 

4、多列子查询

多列子查序则是指查询返回多个列数据的子查询语言；

--多列子查询：

查询与Smith的部门和岗位完全相同的所有雇员；

select * from emp where (deptno,job)=(select deptno,job from emp where ename='Smith');

 

5、多表连接查询：

1）相等连接：显示满足相当条件的记录

Select  ename, b.id, dname

From  employee a,dept b  

Where  a.id=b.id

备注：不在两个表中同时出现的列，前面的表名前缀可以省略

 

2）外连接：显示不满足相等条件的记录

左连接方法一(推荐使用，简洁)：

SELECT ename, b.id, dname

FROM employee a,dept b  

WHERE a.id(+)=b.id

 

左连接方法二：

SELECT ename, b.id, dname

FROM dept b

LEFT JOIN employee a ON a.id=b.id

注意：不管dept是否存在，employee都会显示

 

备注：外连接操作符为(+)，可以出现在相当条件的左侧和右侧

 

左连接与右连接的区别：左连接where只影向右表，右连接where只影响左表

1）左连接：Left Join影响的是右边的表    

select * from tbl1 Left Join tbl2 where tbl1.ID = tbl2.ID

左连接后的检索结果是显示tbl1的所有数据和tbl2中满足where 条件的数据，表tbl1中有，tbl2中没有以null，显示

 

2）右连接：Right Join影响的是左边的表       

 select * from tbl1 Right Join tbl2 where tbl1.ID = tbl2.ID

 检索结果是tbl2的所有数据和tbl1中满足where 条件的数据

3）内连接或者称为有效连接：Inner join，两张表中都有的数据才会显示

select * FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID

功能和select * from tbl1,tbl2 where tbl1.id=tbl2.id相同

4）全连接（完全外连接）：full join 两张表中所有数据都显示

5）自连接（一般用在树形权限结构中）：就是一个表，自身同自身进行连接，通过两个不同的别名区分两个相同的表

多用于对一个表中2个字段的组合进行查询输出：

SELECT worker.ename||' 的经理是 '||manager.ename AS 雇员经理  

FROM employee worker, employee manager 

WHERE worker.mgr = manager.empno;

 

 

备注：左连接和右连接的另外一种写法：是与(+)在等号的哪边相反的

a.id1(+)=b.id2 这是右外边接

a.id1=b.id2(+) 这是左外连接，总之是左还是右，

 

6、合并查询：

1)union

该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行，并排序输出；

查询的表也可以是不同表，但是要查询的字段是相同的

select ename,sal,job from emp where sal>2500

union

slect ename,sal,job from emp where job='MANAGER';

 

2)union all

该操作赋予union 相似，但是它不会取消重复行，而且不会排序；

select ename,sal,job from emp where sal>2500

union all

select ename,sal,job from emp where job='MANAGER';

 

3)intersect

使用该操作符用于取得两个结果集的交集；

select ename,sal,job from emp where sal>2500

intersect

select ename,sal,job from emp where job='MANAGER';

 

4)minus

使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据；

select ename,sal,job from emp where sal>2500

minus

select ename,sal,job from emp where job='MANAGER';

 

7、匹配查询

    select xingming,sal,birthday from student

    union

    select xingming,sal,to_date(null) from t_emp;

   

    --使用相对位置进行排序

    select 'sing' as "Mydream",3 a_dummy from dual

union

select 'id like to music', 1 from dual

    union

select 'the world to',2 from  dual

order by 2;--其中的2表示按照第二例进行排序，默认的是按照第一列进行排序的

 

7、对表的操作

--创建student表：

create table student(

    xh number(4),

    xm varchar2(20),

    sex char(2),

    birthday date,

    sal number(7,2)     --表示薪水总共是7位数字，两位是小数    -99999.99到99999.99

);

 

 

--疯狂往表中添加数据

insert into student select * from student where xuehao='16';

 

--如何连接字符串(||)

select xingming||'的学号是'||xuehao from student where classid='1';

 

--按照多个字段进行排序

select * from student order by sex,sal;

 

--显示某员工FORD的上级领导的姓名

select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='ford';

 

--在多行子查询中使用all 操作符；

如何显示工资比部门30号的所有员工的工资高的员工的姓名、工资和部门号；

方法一：select ename,sal,dept from emp where sal> all(select sal from emp where deptno=30);

方法二：select ename,sal,dept form emp where sal>(select max(sal) from emp where deptno=30);

方法三：select ename,sal,dept from emp where sal>any(select sal from emp where deptno=30);

--分页查询：

select rn,s.* from (select rownum rn,stu.* from student stu where rownum<=10) s where rn>=5;

--用查询的结果创建表：

create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp

8）update：用一张表单某列值更新另外一张表

UPDATE INT_T_D_ANALYSE T
SET    T.CUST_NAME =
       (SELECT M.CTNM FROM T_D_MAIN M WHERE T.DEAL_ID = M.DEAL_ID);