oracle学习 sql基本语法(三),Oracle学习 sql基本语法（三）

子查询

当一步不能求解时，可以使用子查询；

分为：

单行的子查询

多行子查询

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

可以再主查询的select ，from，where ，having 都可以放子查询

不可以在主查询的group by中放子查询

在select中放子查询时，要求只能是单行子查询

in：

any ：

小于某集合中的任意一个值，就是小于集合中最大值

大于某集合中的任意一个值， 就是大于最小值

例：select *from emp where sal < any (select sal from emp where deptno=10)

all：

小于某集合中的所有值，就是小于集合中的最小值；

大于集合中的所有值，就是大于集合中的最大值；

例：select *from emp where sal < all (select sal from emp where deptno=10) order by sal asc

题：

1.查询工资最低的员工信息

select *from emp where sal=(select min(sal) from emp );

2. 找到薪水大于本部门平均薪水的员工

select *from emp a1 where sal > (select avg(sal) from emp a2 where a1.deptno=a2.deptno)

查看每一个部门的平均工资：select deptno 部门编号,avg(sal) 部门平均工资 from emp group by deptno

3.找到员工表中工资最高的前三名

在oracle中有一个：

rownum，是一个伪列，表示查询结果的行号

1. 一旦生成就不会变化(会先按没有排序的时候生成rownum)

2.杜宇rownum ，只能使用或>=与=

select rownum, empno,ename,sal

from(

select empno,ename,sal from emp order by sal desc

)

where rownum<4

思路： 因为rownum 一旦生成不能改变，所以先将sal排好序。

最后条件限制时才使用rownum；

1，函数

字符函数：

lower    全部转为小写

upper    全部转为大写

initcap    首字母转为大写

-------

concat    连接两个字符串

substr    在字符串str中从第m个位置开始取n个字符(位置从1开始)

length    求长度

instr    从字符串strA中找出str所在的位置(返回第找到的1个，位置从1开始)

lpad    把字符串str补齐到n个长度，不足就在左边加指定字符c；

如果str.length>n就取str的前n个字符

rpad    把字符串str补齐到n个长度，不足就在右边加指定字符c；

如果str.length>n就取str的前n个字符(也是从前面取)

trim    从str的两端去掉字符，要是指定的字符时才去掉，使用方式特殊：

例：trim('a' from 'aaITCASTaa') 结果为 'ITCAST'

trim('C' from 'aaITCASTaa') 结果为 'aaITCASTaa'

注意：前面只能指定一个字符。

replace    例：replace('aaITCASTaa', 'a', '=') 结果为 '==ITCAST=='

数字函数：

round    四舍五入，例 round('45.923', 2) 表示保存两位小数，保留的位数可以指定正、零、负数。

trunc    截断，舍掉后面的数，保留的位数可以指定正、零、负数。

mod        求余，如 mod(12, 5) 结果为2

日期函数：

日期可以相减，但不能相加，因为没有意义。

select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;

select ename,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年

add_months

next_day    从某个日期算起，下一个出现该星期几的日期是哪天： select next_day(sysdate,'星期三') from dual;

last_day    当月的最后一天

round

trunc

转换函数：

隐式数据类型转换 与 显式数据类型转换

to_char(date, format)

to_date(string [,format])

to_char(number, format)

to_number(string [,format]) 如： to_number('22') 或 to_number('$22', '$99')

空值处理函数：

NVL (expr1, expr2)

可以使用的数据类型有日期、字符、数字

NVL2 (expr1, expr2, expr3)

expr1不为NULL，返回expr2；为NULL，就返回expr3( expr1 != null ? expr2 : expr3 )

条件表达式：

用于实现 IF-THEN-ELSE 逻辑。

CASE 表达式：SQL99的语法，比较繁琐。

DECODE 函数：Oracle自己的语法，类似Java，比较简洁。

例：

根据员工的职位涨工资：总裁1000 经理800 其他400

PRESIDENT

MANAGER

函数嵌套：

嵌套函数的执行顺序是由内到外。

=================================================

2，集合运算

查询属于部门10与部门20的所有员工信息，还可以这样查

select * from emp where deptno=10

加上(这里写集合运算符)

select * from emp where deptno=20;

这就是集合运算。

并集：

UNION        集合a + b的结果，没有重复记录。

UNION ALL    集合a + b的结果，保留所有重复的记录。(用的比较少)

例：查询属于部门10与部门20的所有员工信息。

例：查询工资在500~1500或在1000~2000范围的员工信息(这是两个工资级别)。

交集

Intersect    既属于集合a又属于集合b的记录。

例：查询工资在500~1500又在1000~2000范围的员工信息(这是两个工资级别)。

差集

Minus        集合a - b的结果，即从a中去除所有属于集合b的元素，注意a-b与b-a的结果是不一样的。

例：查询属于500~1500但不属于1000~2000范围的员工信息。

注意：

1，Select语句中参数类型和个数要一致

如果不一致，需要想办法补齐。

例如要补个字符串，不能写个'a'、'b'等，要不影响结果，应补一个null，还要指定类型。

如果是字符串，可以写 to_char(null);

如果要补数字类型，则写 to_number(null)

2，结果集采用第一个select的表头作为表头。

在第一个select上起别名才有用。

在后面的select上起别名就没有用。

3，如果有order by子句

必须放到最后一句查询语句后。

=================================================

3，多表查询

类型：

1. 等值连接

2. 不等值连接

3. 外连接

4. 自连接

等值连接：

例：查询员工信息，要求显示员工的编号，姓名，月薪和部门名称

select e.empno,e.ename,e.sal,d.dname

from emp e,dept d

where e.deptno=d.deptno;

不等值连接：

例：查询员工的工资级别：编号 姓名 月薪和级别

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

from emp e,salgrade s

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

过渡用的例子：

按照部门统计员工的人数，要求显示：部门号，部门名称，员工人数

select d.deptno,d.dname,count(e.empno)

from emp e,dept d

where e.deptno=d.deptno

group by d.deptno,d.dname;

结果：

DEPTNO DNAME          COUNT(E.EMPNO)

---------- -------------- --------------

10 ACCOUNTING                  3

20 RESEARCH                    5

30 SALES                       6

SQL> select * from dept;

DEPTNO DNAME          LOC

---------- -------------- -------------

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

SQL> select * from emp where deptno=40;

未选定行

期望效果：当连接条件不成立时，仍然希望在结果中包含某些不成立的记录。这就要用到外连接。

外连接：

左外连接：where e.deptno=d.deptno  当连接条件不成立时，等号左边所代表的表的信息仍然显示

右外连接：where e.deptno=d.deptno  当连接条件不成立时，等号右边所代表的表的信息仍然显示

左外连接的写法： where e.deptno=d.deptno(+)

右外连接的写法： where e.deptno(+)=d.deptno

实现上面的效果：

select d.deptno,d.dname,count(e.empno)

from emp e,dept d

where e.deptno(+)=d.deptno

group by d.deptno,d.dname

order by 1;

自连接：利用表的别名，将同一张表视为多张表

例：查询员工信息：xxx的老板是yyy

select e.ename||'的老板是'||b.ename

from emp e, emp b

where e.mgr=b.empno;

使用SQL99标准的连接查询(JOIN..ON..)

内连接

只返回满足连接条件的数据(两边都有的才显示)。

select e.*, d.*

from emp e

inner join dept d

on e.deptno=d.deptno

-- 也可以省略inner关键字。

左外连接

左边有值才显示。

select e.*, d.*

from emp e

left outer join dept d

on e.deptno=d.deptno

-- 也可以省略outer关键字

右外连接

右边边有值才显示。

select e.*, d.*

from emp e

right outer join dept d

on e.deptno=d.deptno

-- 也可以省略outer关键字

满外联接

任一边有值就会显示。

select e.*, d.*

from emp e

full outer join dept d

on e.deptno=d.deptno

-- 也可以省略outer关键字

交叉连接：

叉集，就是笛卡尔积

select e.*, d.*

from emp e

cross join dept d

-- 没有连接条件

=================================================

4，处理数据(DML，增删改)

DML，Data Manipulation Language，数据操作语言

插入数据(Insert into)：

插入全部列

插入部分列

插入空值

使用 & 变量(创建脚本)

例：

SQL> insert into emp (empno, ename, sal) values (&empno, &ename, &sal)

SQL> insert into emp (empno, ename, sal) values (&empno, '&ename', &sal)

SQL> update emp set ename='&new_name' where empno=&empno

SQL> select empno,ename,&col from emp;

可以在命令行sqlplus或是iSQL*Plus中演示。

从其它表中拷贝数据

insert into mytable

select mycolums from ...

更新数据(Update)

删除数据(Delete)

删除所有的记录：

Delete

Truncate