引言
体系结构篇:https://blog.csdn.net/z929118967/article/details/128484699
关系篇:https://blog.csdn.net/z929118967/article/details/128493121
操作数据库使用SQL(structure query language) 结构化查询语言,根据SQL的功能不同分成:
- DQL:(数据查询语言,select)用来完成对数据库中的数据查询。
- DDL(数据定义语言,create,drop,truncate截断表)操作表结构。
- DML(数据库操作语言,insert,delete,update)操作数据
- TCL(事务控制语言,transient control language)commit/rollback/savepoint 操作事务 https://kunnan.blog.csdn.net/article/details/128387690
I 查询
- 优化时,在既有选择操作和链接操作时,应当先做选择操作,这样参加连接的元组就可以大大减少。(代数优化)
- 选择操作算法有全表扫描和索引扫描,应当选用索引扫描。
- 表的连接采用index join代价页较小。这是物理优化。
————————————————
版权声明:本文为CSDN博主「iOS逆向」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。原文链接:https://blog.csdn.net/z929118967/article/details/128493121
1.1 基本查询
select [distinct] 字段名称,字段名称 from 表名 --关键字和字段大小写不区分。
https://blog.csdn.net/z929118967/article/details/128456644?spm=1001.2014.3001.5501
- 别名,以空格 或者as连接,如果别名包含空格或者区分大小写,需要用双引号。
select 字段名 别名 form 表名;--as可以省略,别名与字段名分开
- 查询时字段进行算数运算
+* - /
select ename ,sal*12 from emp;
- null ,表示不确定,包含null值的算术运算,结果都为null 。
判断null值,is null
和is not null
- 字符连接用
||
select 字段1||字段2 from emp;
- 日期类型加整数,表示加几天,两个日期类型相减,结果为天数。
sysdate为当前日期时间
- 查询结果批量插入表中
inert into table(col1,col2,…)
select col1,col2,…
- 以查询结果创建表
create table myTable
as
select * from dm_swjg
1.2 条件查询
select 字段一,字段二 from 表名 where 限定条件
- 采用比较运算符过滤结果
> ,<,=,<>,!=
- 逻辑运算符
and,or ,not
一般与in
一起使用
select * form emp where sal>100 and comm>100;
--in
select * from emp where sal in(1500,3000);
between 较小的数 and 较大的数
is null
和is not null
select * from emp where comm is null;
- 值的绑定 ( 动态传值):
&变量名
select * from emp where sal between &no1 and &no2
select * from emp where job='&name';--如果不加单引号,可以在输入时加单引号。
like
通配符,%
代表零或多个字符,下划线_
代表单个字符。
模糊查询效率低,一般会
全表扫描
,建议不要将%放于查询条件的最前面。
--如果字段值包含有通配符,那么可以使用escape来设定转义字符。
select * from emp where ename like 'A\_%' escape '\';
--escape是用于声明转义字符。
distinct
关键字用于除去重复记录,只能紧随select 之后。
select distinct deptno from emp;
- 排序(order by ):
select swjg_dm dm,swjg_mc
from dm_swjg
order by swjg_dm
-- 也可以使用别名 order by dm
-- 还可以使用列的位置 order by 1,按照第一个字段排序,还要注意的是年份是按照大小来排序的。
-- 排序的时候,null值最大
1.3 子查询
https://kunnan.blog.csdn.net/article/details/128394109
Update table set col=(sub_query)
Where col = (sub_query)
Delete from table
Where col=(sub_query)
With子句,使用多个子查询,每个子查询的结果放入用户临时表中,方便使用及性能的提高。
with
bq as (select swjg_dm,se …),--本期入库
qs as (select swjg-dm,se …)--欠税
select bq.swjg_dm,bq.se,qs.se
from bq,qs
where bq.swjg_dm=qs.swjg_dm
1.4 查询表结构
desc 表名
: 显示字段、字段的类型以及长度和约束。
1.5 遍历树形数据
语法
select [level],col1,col2..
from table
start with 条件
connect by prior 条件
--level ,伪列,每条记录的层级
例子:递归查询鞍山市局的所有下级税务机构
select level, swjg_dm, swjg_mc
from dm_swjg
start with swjg_dm = '22103000000'--鞍山市局,往下找
connect by prior swjg_dm = sj_swjg_dm
加where过滤一些节点
select swjg_dm, swjg_mc
from dm_swjg
where yxws = 7 --县级
Start with swjg_dm = '22100000000' --省局开始
connect by prior swjg_dm = sj_swjg_dm
II 分组查询(group by)
- group by 可以使用表达式,但不可以使用别名。
- where 不可接分组函数
- 分组查询的执行顺序: 先执行from,在执行where,或分组,最好才执行select。
select 列表中的非聚合函数列,都必须出现在group by子句中。 但是,group by子句中的列,不一定要出现在select列表中。如果在select之后有某个字段名称,那么此字段必须作为分组的条件之一。
select deptno,max(sal) from emp group by deptno;
--在分组查询中,select 之后除了组函数以外,查询的字段名称和个数最好和group by 之后的字段名称和个数一致。
- 过滤分组查询可以使用having关键字,having用于过滤分组之后的结果。
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;--having不能单独使用,它必须出现在group by 之后。
2.1 rollup和group by一起使用
用来产生各分组的小计以及最后的合计
例子:统计税务机关的入库数,并添加合计。
select swjg_dm,sum(se) se from sb_zsxx group by rollup(swjg_dm)
grouping函数: 用于判断是否由rollup产生的
group(swjg_dm)=1
,表示由rollup产生。
select case
when grouping(swjg_dm) = 1 then '合计'
else swjg_dm
end swjg_dm,
sum(se) se
from sb_zsxx
group by rollup(swjg_dm)
2.2 条件表达式和group by一起使用
Decode函数: 分支判断
Decode(表达式, 条件1,结果1,
条件2,结果2,
…
,默认结果)
select decode(&no,1,'值1',2,'值2') from dual
Case 表达式 语法1:
case 表达式 when 值1 then 结果1
when 值2 then 结果2
…
else 默认结果
end
Case 语法2:
case when 条件1 then 结果1
when 条件2 then 结果2
…
else 默认结果
end
例子:交叉报表
select name, sum(case kechen when '语文' then fengshu end) yuwen, sum(case kechen when '数学' then fengshu end) shuxue, sum(case kechen when '化学' then fengshu end) huaxue from table group by name
2.3 Over和group by一起使用
Over (partition by col_list ... order by col_list...)
: 先把一组数据按照制定的字段进行分割成各种组,然后组内按照某个字段排序。
按照制定的字段,把相同值的排在一起,分为一个块,也就是分组,然后组内排序
sum() over()
分组求和
elect zsxm_dm,
sum(se) se, --收入
sum(sum(se)) over() zse, --总收入
round(sum(se) * 100 / sum(sum(se)) over(), 2) || '%' bz --比重
from sb_zsxx
group by zsxm_dm
first_value() over()
求分组第一条
row_number() over()
取编号第一条的也可以实现first_value() over()的效果
last_value() over()
求分组最后一条
III 直接操作外部数据库
3.1 使用外部数据库
- 建立
database link
create database link test_link --链接名
connect to 用户名 identified by 密码
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.6.3.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ctaiscs) --oracle9i以后推荐使用全局数据库名
)
)'
- 使用外部数据库
select * from d_g@test_link
- 删除
database link
drop database link test_link
3.2 从远程数据库导入数据
Create or replace procedure test_proc
IS
Begin
for x in (select * from dj_xx@test_link)
loop
insert into dj_rxx values x;
end loop;
commit;
end test_proc