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
??? ???
??