交叉联结 cross join

select last_name,dep_name from employees,departments;
等同于
select last_name,dep_name from employees cross join departments;

自然联结 naturl join
select dep_id,dep_name,location_id,city from dep natural join locations;
等同于
select dep_id,dep_name,location_id,city from dep,locations where dep.location_id = locations.location_id;

通过on子句联结
select t.name,t.id,s.res_id,s.path,s.image from inch_cinema_res t join inch_cinema_partres s on (t.id =s.res_id)

left outer join 左联 没匹配时显示左表的值
right outer join 右联
full outer join 全联

五 使用分组函数聚集数据
1.max min avg sum
select max(click_num),min(click_num),avg(click_num),sum(click_num) from inch_cms_resource where type_id =14
2.count(expr) 返回expr列不为空的记录数
select count(hasserverids) from inch_cms_resource where type_id =14
count(distinct expr) 返回expr列不重复，不为空的记录数
select count(hasserverids) from inch_cms_resource where type_id =14
3.AVG SUM/990 10行空值
select avg(click_num) from inch_cms_resource where type_id =14
AVG(NVL（）) 忽略空值的行 强制平均 SUM/1000 10行空值
4.group by
select type_id,avg(click_num) from inch_cms_resource group by type_id order by type_id
5.having 条件限制 不能用where
显示平均点击数超过5000的各栏目中平均点击数
select type_id,avg(click_num) from inch_cms_resource having avg(click_num) > 5000 group by type_id order by type_id
显示最大点击数超过5000的各栏目中最大点击数
select type_id,max(click_num) from inch_cms_resource having max(click_num) > 5000 group by type_id order by type_id
显示栏目中最大平均点击数的栏目
select max(avg(click_num)) from inch_cms_resource having max(click_num) > 10000 group by type_id order by type_id

六 子查询
显示比栏目最大平均点击量高的所有视频
select * from inch_cms_resource where click_num > (select max(avg(click_num)) from inch_cms_resource having max(click_num) > 10000 group by type_id )

1条select语句只能使用1条order by 子句 并且是最后的子句

单行子查询 可用 = < > <= >= <> 连接
多行子查询 可用 in any all
any 高于最低值 =any 等同in
all 高于最高

in any all 可配合 not 使用

七 sql*plus 不常用 简单略过

1.替换变量

&column_value 单个变量 可用户自行输入 查询

define undefine
define column_value = aaa 默认值
undefine column_value 取消

自定义sql*plus环境
set system_variable value
set echo on

show echo
show all

八 数据处理

insert into table (column,...) values (value,...)
~输入时常见错误
~缺少not null 列的必需值
~重复值违反唯一性约束
~违反外键约束
~违反check约束
~日期类型不匹配
~值太常 无法放入列中

从其它表中复制值
insert into TEMP_SYS_RECORD(fathertype_id,type_id,Typename,REs_NUM,SUMCLICK_NUM,STARTTIME,ENDTIME)
select a.fathertype_id,a.type_id,a.type_name,b.res_num-a.res_num,b.sumclick_num-a.sumclick_num,v_startTime,v_endTime from
(select t.fathertype_id,t.type_name,s.type_id,s.res_num,s.sumclick_num from
inch_sys_record s,inch_cms_type t where s.type_id in
(select type_id from inch_cms_type where type_id = typ.column_value or fathertype_id = typ.column_value )
and counttime = to_date(to_char(v_startTime,'yyyy-mm-dd'),'yyyy-mm-dd') and s.type_id = t.type_id)a,

(select t.fathertype_id,t.type_name,s.type_id,s.res_num,s.sumclick_num from
inch_sys_record s,inch_cms_type t where s.type_id in
(select type_id from inch_cms_type where type_id = typ.column_value or fathertype_id = typ.column_value )
and counttime = to_date(to_char(v_endTime,'yyyy-mm-dd'),'yyyy-mm-dd') and s.type_id = t.type_id)b
where a.type_id =b.type_id order by a.fathertype_id asc;
~TEMP_SYS_RECORD 临时表
~fathertype_id,type_id,Typename,REs_NUM,SUMCLICK_NUM,STARTTIME,ENDTIME 临时表字段
~select type_id from inch_cms_type where type_id = typ.column_value or fathertype_id = typ.column_value )
and counttime = to_date(to_char(v_startTime,'yyyy-mm-dd'),'yyyy-mm-dd' 查询类别表里 符合条件的类别id
~select t.fathertype_id,t.type_name,s.type_id,s.res_num,s.sumclick_num from
inch_sys_record s,inch_cms_type t where s.type_id in 联立两表查询所需字段
~select a.fathertype_id,a.type_id,a.type_name,b.res_num-a.res_num,b.sumclick_num-a.sumclick_num,v_startTime,v_endTime from 插入临时表所需的值 有常量和联立查询后的结果值

不使用values select列数要匹配

修改表字段
update table set columa = value where condition;

删除
delete from table where
没有where删除所有

default 采用默认值

更新匹配

merge into copy_emp c
using emp e
on(c.emp_id = e.emp_id)
when matched then
update set
c...=e...
...
when mot matched then
insert values(e.,e...);

事务处理
commit 数据更改永久化
rollback 放弃更改

savepoint col;创建保存点
roll back to col;