/*
达内学习 Oracle day40 2013-10-28
*/
E,多表查询
执行脚本 :进入sqlplus @路径/脚本名
不写多表查询where 结果是笛卡尔积
把部门的名称和对应地区名称列出来
select d.name,r.name from s_dept d,s_region where region_id = r.id; 有别名以后就只能用别名
from 执行早于 where,where 早于 select
from where 多表查询的另一种写法
a 表 join b 表 on 连接条件
select d.name,r.name from s_dept d join s_region r on region_id = r.id;
select e.dept_id,d.name from s_emp e join s_dept d on e.dept_id = d.id;
>>> 多表查询的关键就是找到两张表的关系 用等号连接的叫等值连接
>>> 表达两张表的连接条件 不是用等值 叫非等值连接
select s_emp.id,salary,salgrade.id from s_emp,salgrade where s_emp.salary >= salgrade.losal and s_emp.salary <= salgrade.hisal;
>>> 特殊的连接 领导是员工 员工不一定是领导
一张表中有多层含义的数据,就需要逻辑上把一张表看成多张表。
使用表的别名
select distinct m.id,m.first_name from s_emp e,s_emp m where e.manager_id = m.id;
----------------------------------------------------------
等值连接、非等值连接、自连接、这些连接 都符合 符合where条件的数据就被选中,不符合where条件的数据被过滤掉。 这个连接称为内连接
列出所有的部门名 和部门对应的地区名
s_dept:name,region_id;
s_region: id,name
select d.name,r.name from s_dept d,s_region r where r.id = d.region_id;
新增一个部门
insert into s_dept values(100,'test',NULL);
commit;
列出所有部门名 和对应的地区 名 没有地区名的部门 也要显示
外连接: 外连接的结果集,等于内连接的结果集,加上匹配不上的记录, 一个也不能少
oracle 中使用(+) 把不匹配的记录找回来;显示(+)对面没有匹配的数据。
select d.name,r.name from s_dept d,s_region where s.region_id = r.id(+);
列出员工的first_name和员工所在的部门名
select e.first_name, d.name from s_emp e,s_dept d where e.dept_id = d.id;
列出部门号不出现在部门表中的员工的名字 部门显示 no dept
select first_name,nvl(name,'no dept') from s_emp e,s_dept d
where dept_id = d.id(+) and d.id is NULL;
------------------------------------------------------
找出所有的领导
select distinct e.first_name ,e.id from s_emp e,s_emp m where e.id = m.manager_id;
找出所有普通员工
select distinct m.id,m.first_name from s_emp e,s_emp m where
e.manager_id(+) = m.id and e.manager_id is null;
----------------------------------------------------
列出 员工的工资 和 工资所在的工资级别
select s_emp.id,salary,salgrade.id from s_emp,salgrade where salary between losal and hisal order by e.id;
update s_emp set salary = 12500 where id = 10 commit;
列出所有员工的工资和工资所在的工资级别
select e.id,salary,s.id from s_emp e,salgrade s where salary between losal(+) and hisal(+) order by e.id;
----------------------------------------------------------
SQL99规定了内外连接的标准
内连接
a 表 join b 表 on 连接条件 where 过滤条件
a 表 inner join b 表 on 连接条件 where 过滤条件
外连接:
a 表 left outer join b on 连接条件 where 过滤条件 ;左外连接
a right outer join b on 连接条件 where 过滤条件 ;右外连接
a full outer join b on 连接条件 where 过滤条件 ;全连接
select e.id,salary ,s.id from s_emp e left outer join salgrade
on salary between
要把哪张表的数据匹配出来,则让哪张发起连接,左边用left 右边用right
全外连接:只是逻辑概念,全外连接的结果等于做外连接的结果集, 加上右外连接的结果集然后减去重复的记录。
oracle不直接支持全连接, 不是通过两端加(+)的方法,而是使用 union 合并两个结果集,并排重,union all 合并两个结果集。
F,组函数和分组
1,组函数的概念,对一组数据处理之后得到一个结果。
2,常见的组函数 count max min sum avg
3,统计s_emp表中有多少员工,员工的最高工资
select count(id),max(salary),min(salary) from s_emp;
4,统计s_emp 表中工资和工资的平均值
select sum(salary),sum(distinct salary),avg(salary),avg(distinct salary) from s_emp;
5,组函数 对 null 值的处理方式
组函数对 null 的处理方式是 当他不存在
分组:
1, 按照一定的标准把数据分成若干组
where 子句 group by 分组标准 ;位置在where之后
2,举例
按照部门号 把员工分组 统计每个部门的人数
select dept_id ,count(id) from s_emp where 1= 1 group by dept_id; -- 单独把空值分一组
按照部门号分组 统计几每个部门的平均工资 和工资的和
select dept_id,avg(salary),sum(salary) from s_emp group by dept_id;
3, 按照部门号分组 统计每个部门的平均工资 和 工资的和 要求 工资的和大于3000的
select dept_id,avg(salary),sum(salary) from s_emp where 1=1 group by dept_id having sum(salary)>3000 order by sal;
-- 执行顺序
from
where
group by
having
select
order by
4,按照部门号分组 统计每个部门的人数 列出大于两个人的部门
select count(id) ,dept_id from s_emp where 1 =1 group by dept_id having count(id) >2;
5,按照部门号分组 统计每个部门的人数 列出大于2个人的部门,要求显示部门名
select dept_id,count(e.id),name from s_emp e,s_dept d where e.dept_id = d.id group by dept_id,name having count(e.id) > 2;
在分组语句中select 后的字段要么是分组标准,要么是经过合适的组函数(max,min)处理过的。
select deptid,count(d.id),max(name) from s_emp e,s_dept d where e.dept_id = d.id group by dept_id, name having count(e.id) >2;
---------------------------------------------------------------------------------
G,子查询
把一个查询的结果 作为另一个查询的基础
1,where 后
select id,first_name from s_emp where id in(select distinct manager_id from s_emp);
select id,first_name from s_emp where id not in(select distinct nvl(manager_id,0) from s_emp); --小心null
2,having 后
求平均工资大于42部门平均工资的部门 列出部门id,平均工资
select dept_id ,avg(salary) from s_emp group by dept_id having avg(salary) > (select avg(salary) from s_emp where dept_id = 42);
3,from 后
任何一条sql,都可以看成一张内存表
select id ,first_name,salary from s_emp;
---------------------------------------------------------------------
DDL
1,添加表 create table tab_name
(
eno number(7),
fname varchar2(10),
sname char(10) -- 最后一个不用逗号
);
2,删除表
drop table tab_name;
3, 数据类型
number 数字类型
number(7) 最大是七位数
number(7,2)总共七位,小数点占两位,整数最大5位
number(5,-2) 有效共5位,小树2位,整数7位 1234567 --------->1234500
char 定长字符串
varchar2(10)可变长度
4,日期类型
a. date 类型
和系统语言相关
NLS_LANG
系统默认的日期格式 ‘dd-MON-yy’(英语) ‘dd-n月-yy’(汉语)
b. select start
create table order_10019 (
oid varchar2(30),
oname varchar2(30),
omoney number,
odate date
);
c,打破时间的默认表现
to_char(par1,par2)
par1 要处理的日期类型的数据
par2 要表现的日期格式字符串
yyyy四位年 mm两位月 dd两位日 hh时(12)hh24(24) mi分 ss秒
mon 三位英文月 month月全拼 day星期几 pm上午am 下午pm
select to_char (odate,'yyyy-mm-dd pm hh24:mi:ss day' from
系统默认放入的日期 十分秒信息都是0
d,如何放入带十、分、秒 信息的日期
sysdate 可以直接获得系统当前时间
to_date(par1,par2) 把日期字符串转换成日期函数
par1 要转换的日期字符串,
par2 根据日期字符串 指定日期格式
yyyy-mm-dd hh24:mi:ss
insert into table_name values('2008','test',2800,to_date('2008-9-9 20:09:8','yyyy-mm-dd hh24:mi:ss')) ;
select to_char (odate,'yyyy-mm-dd hh24:mi:ss pm day'from mytable where oid = 'bj2008' ;
inser into table_name values('aa','bbb',200,to_date('2012-12-20 23:56:56','yyyy-mm-dd hh24:mi:ss'));
f,日期的运算
如何按照天来调整
select to_char(sysdatem,'yyyy-mm-dd hh24:mi:ss')from dual;
select to_char (sysdate+1,'yyyy-mm-dd hh24:mi:ss') from dual; --调整到明天
select to_char (sysdate+1/24,'yyyy-mm-dd hh24:mi:ss') from dual;
---向后调整一小时
select to_char (sysdate+1/24/60/60,'yyyy-mm-dd hh24:mi:ss') from dual; --向后调整一秒
g,特殊调整
把时间向后调整一个月
add_months(日期,n) n是正数,向后调 n是负数向前提调
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(add_months(sysdate+3),'yyyy-mm-dd hh24:mi:ss') from dual;
last_day(日期) 得到日期的当月的最后一天的日期时间
next_day(日期,'星期几') -- 下个星期几的时间点
select next_day(sysdate,'monday') from dual;
select to_char(next_day(sysdate,'monday'),'yyyy-mm-dd hh24:mi:ss') from dual;
h.对日期的一些数学操作
months_between 两个日期之间相差多少个月
round 对日期进行四舍五入
1,默认以天位单位进行四舍五入
select to_char(round(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual;
2,指定四舍五入的单位
select to_char(round(sysdate,'dd'),'yyyy-mm-dd hh24:mi:ss') from dual; --以天为单位,天后面是否进一
trunc 对日期进行截取
select to_char(trunc(sysdate,'mm'),'yyyy-mm-dd hh24:mi:ss') from dual; --以月为单位,月后面的截断,回到月初
给定一个日期 计算日期对应月的最后一天的最后一秒的时间点
select to_char(trunc(add_months(sysdate,1),'mm')-1/24/60/60,'yyyy-mm-dd hh24:mi:ss') from dual;
-------------------------------------------------------------------
DML
account
create table accountwang
(
aid number,
aname varchar2(30),
apasswd varchar2(30)
);
1,增加数据
a. insert into tab_name values(val1,val2);
b. 选择一些字段进行插入,必须包含所有的非空字段
insert into tab_name(字段1,字段3) values(值1,值2);
如果一个字段没有被选择,则这个字段的值是null
2,删除数据
delete from tab_name where
3, 更新数据
update tab_name set 字段名 = 值1,字段名 = 值2 where 条件