正文
Oracle数据库之二
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
SELECT查询
函数分为：
单行函数 -- 一条记录进入，一条记录输出
多行函数（分组函数）-- 多条记录进入，按组输出
单行函数：
select id,first_name,nvl(commission,0) from s_emp;
多行函数：
select count(*) from s_emp;
单行函数分为：
字符函数、数字函数、日期函数、转换函数和其他函数
字符函数：
lower--转换成小写字符
upper--转换成大写字符
initcap--首字母大写，其他小写（较少用）
substr --取字符串的一部分（子字符串）,第三个参数是子字符串的长度
concat--连接字符串（与||功能重复，不使用）
length--字符串的长度
oracle数据库有一个虚表dual（测试表），只有一个字段，但支持任意类型。
select 1+1 from dual;
select sysdate from dual;
select 'hello' from dual;
select lower('Hello') from dual;
select upper('Hello') from dual;
select initcap('hello') from dual;
select length('Hello') from dual;
select substr('Hello',1,3) from dual;
select substr('Hello',2,3) from dual;
练习：
不使用or实现模糊查询：
从s_emp表中查询所有first_name包含a的员工
显示id first_name salary 信息
select id,first_name,salary from s_emp where lower(first_name) like lower('%a%');
练习：
显示s_emp表中first_name 4个字符以后的内容
select id, substr(first_name,5,length(first_name)-4),first_name from s_emp;
select id, substr(first_name,5),first_name from s_emp;
trim() 可以去掉首尾的空格（针对char）
select length('abc ') from dual;
select length(trim('abc ')) from dual;
数字函数：
round--四舍五入
trunc--光舍不入
select round(45.935,0) from dual;
select round(45.935,2) from dual;
select round(45.935,-1) from dual;
select trunc(45.935,0) from dual;
select trunc(45.935,2) from dual;
select trunc(45.935,-1) from dual;
转换函数
日期和字符之间的转换：to_date/to_char
数字和字符之间的转换：to_number/to_char
to_char(数字，‘格式’)
格式中允许：
9 代表一个数字
0 代表一个数字，如果没有的话加0
$ 货币
L 本地货币
. 小数点
, 千分为
select to_char(4853.8823,'$99,999.99') from dual;
select to_char(4853.8823,'L00,000.00000') from dual;
to_number格式和to_char差不多
select to_number('$1,834.45','$99,999.99') from dual;
单行函数可以无限次的嵌套
多表连接 -- 有时候数据不是存在一个表中，需要使用多表连接把数据从多个表中取出
显示 员工ID、first_name、部门名称
select e.id,name,dept_id,d.id from s_emp e,s_dept d where e.dept_id = d.id;
oracle数据库在查询时，首先做一个迪卡尔乘积，就是把所有表的记录总数相乘，得到结果的总数。因此多表连接不能过多，一般4张表就是极限
迪卡尔乘积对于结果来说是没有意义的，需要从迪卡尔乘积中获得有效数据，加上查询条件。有n个表，至少需要n-1个条件（连接条件）
在多表连接时，尽量避免迪卡尔乘积。
在超过3张表的连接时，表和条件的位置有时候会影响性能
练习：
显示员工编号、first_name、部门名称、区域名称 信息从s_emp/s_dept/s_region表中
s_emp表：
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(7)
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
USERID VARCHAR2(8)
START_DATE DATE
COMMENTS VARCHAR2(255)
MANAGER_ID NUMBER(7)
TITLE VARCHAR2(25)
DEPT_ID NUMBER(7)
SALARY NUMBER(11,2)
COMMISSION_PCT NUMBER(4,2)
s_dept表：
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
REGION_ID NUMBER(7)
s_region表：
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(50)
select e.id,first_name,d.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;
SQL plus 工具设置行大小：
set linesize 200
多表连接由4种方式：
等值连接：用‘=’做链接表的条件
非等值连接： 不用‘=’做连接表的条件
外连接：（+）包括空字段
自然连接：自己表连接自己表（单表做多表连接）
90%的多表链接都是等值连接
非等值连接
显示员工的first_name，薪水和薪水等级
salgrade表：
Name Null? Type
---------------------------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
select e.first_name,e.salary,s.grade from s_emp e,salgrade s
where e.salary between s.losal and s.hisal;
自连接--表连接自己
select id,first_name,manager_id from s_emp;
显示员工的id,first_name,管理者的first_name
select e.id,e.first_name,e.manager_id,m.first_name from s_emp e,s_emp m
where e.manager_id = m.id;
外链接--包括匹配值 + 空值
select e.id,name,dept_id,d.id from s_emp e,s_dept d
where e.dept_id = d.id(+);
dept_id = id 或 dept_id = NULL 或 dept_id = id 中不存在的
select e.id,name,dept_id,d.id from s_emp e,s_dept d
where d.id(+) = e.dept_id;
（+）不能同时写在 = 两边
select e.first_name,e.salary,s.grade from s_emp e,salgrade s
where e.salary between s.losal(+) and s.hisal(+);
select e.first_name,e.salary,s.grade from s_emp e,salgrade s
where e.salary(+) between s.losal and s.hisal;
slq99对多表连接有新的一套写法，也支持原有写法。sql99多了一个全外连接。
在SQL: 1999中,内连接只返回满足连接条件的数据。
两个表在连接过程中除了返回满足连接条件的行以外还
返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外联接。
两个表在连接过程中除了返回满足连接条件的行以外还
返回两个表中不满足条件的行 ,这种连接称为满 外联接。
多表连接用join，连接条件用on
外连接：
left outer join -- 左外连接
right outer join-- 右外连接
full outer join--全外连接
迪卡尔积：cross join
select e.id,first_name,name from s_emp e cross join s_dept;
两个表的连接：
select e.id,first_name,name from s_emp e join s_dept d
on(e.dept_id = d.id);
select e.id,first_name,name from s_emp e left join s_dept d
on(e.dept_id = d.id);//省略了outer
select e.id,first_name,name from s_emp e right join s_dept d
on(e.dept_id = d.id);
select e.first_name,e.salary,s.grade from s_emp e left join salgrade s
on(e.salary between s.losal and s.hisal);
select e.first_name,e.salary,s.grade from s_emp e right join salgrade s
on(e.salary between s.losal and s.hisal);
select e.first_name,e.salary,s.grade from s_emp e full join salgrade s
on(e.salary between s.losal and s.hisal);
最全面的select语句是：
select 子句
from 子句
[where 子句]
[group by 子句]
[having 子句]
[order by 子句]
常用的分组函数5个：
count--计算总数，包括所有类型
avg--计算平均值，只能用于数值类型
sum--计算总和，只能用于数值类型
max--最大值，包括所有类型类型
min--最小值，包括所有类型
查询员工总数：
select count(*) from s_emp;
显示commission_pct的平均值。
select avg(commission_pct) from s_emp;
分组函数自动忽略空值，因此有时候需要用nvl转换空置。
select avg(nvl(commission_pct,0)) from s_emp;
显示各部门的平均工资（分组，一个部门算一组）
select dept_id,avg(salary) from s_emp group by dept_id;
练习:
显示每个管理者的管理人数
select manager_id,count(*) from s_emp group by manager_id;
select first_name,dept_id,avg(salary) from s_emp group by dept_id;
first_name 不能输出，因为一组（一个部门）的first_name 有很多个。
使用分组时，select后面只能跟：分组函数或者group by后面出现的字段
select first_name,dept_id,avg(salary) from s_emp group by dept_id,first_name;
显示41,42两个部门的平均工资
select dept_id,avg(salary) from s_emp where dept_id in(41,42) group by dept_id;
练习：
显示除了41和42所有部门的平均工资
select dept_id,avg(salary) from s_emp where dept_id not in(41,42) group by dept_id;
显示每个管理者的管理人数，要求按照管理者id排序
select manager_id,count(*) from s_emp group by manager_id order by manager_id;
在排序时，空值被看出最大。
显示所有平均工资超过5000的部门编号和平均工资
select dept_id,avg(salary) from s_emp where avg(salary) > 5000 group by dept_id; 错
分组函数不能出现在where子句中。
分组函数做查询的条件需要使用having子句。
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>5000;
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>5000 order by avg(salary);
having理论上可以取代where，但having效率较低，所以having只用于分组函数做查询条件时。
