plsq数据库
2017-12-15 本文已影响0人
Lijy_李俊阳
表的基本元素
表
1.表名
- 归属用户
- 注释（comment on table表名 is ‘注释内容’）
2.列 - 列名
- 类型（varchar2、number、date）
- 是否可为空（not null）
- 默认值（default 值）
- 注释（comment on column 表名.字段名 is ‘注释内容’）
3.字段约束（键） - 主键（不为空，不重复，primary key ）
- 唯一（不重复，空可以出现1次，unique）
- 外键（该字段的值，必须在指定外部表中存在，foreign key）
4.索引（index） - 主键索引（默认的，不用写）
- 普通索引
5.权限（o用户：owner所有权用户、c用户：customer使用客户用户；grant）
把表的增删改查权限有选择性的赋权给其它用户（比如程序连接的数据库用户名）
1.表，数据的操作
| 对象 | 操作 | 语句 |
|---|---|---|
| 表 | 增 | create 表明（属性） |
| 表 | 删 | drop table 表名 |
| 表 | 改 | alter table 表名 |
| 表 | 查 | select * from user_tables t ； |
| 数据 | 增 | insert into 表名（字段名，字段名）values（字段值，字段值）； |
| 数据 | 删 | delete 表名 where 条件；（或者全删 truncat table 表名，这个会释放空间降低水位线）； |
| 数据 | 改 | update 表名 set 字段名=字段值，字段名=字段值 where 条件 |
| 数据 | 查 | select * from 表名 where 条件 ； |
2.索引
原理
1.1 将索引字段单独生成一个二叉树B-TREE（字段值=数据地址，类似java的变量名）
1.2 如果查询条件用到了该字段，先到索引树里面查找
1.3 索引树中查到节点后，取出数据所在地址
1.4 按照地址，到表中取数据
image.png
表跟索引，是2个独立的对象，各存各的数据
3.连接
image.png
1.内连接（B）
2.左连接（A+B）
3.右连接（C+B）
4.全连接（A+B+C）
image.png
内连接
select *from student a ,score b where a.sno=b.sno and b.score>60;
select *from student a inner join score b on a.sno=b.sno where score>=60;
image.png
左连接
select *from student a left join score b on a.sno=b.sno where score>=60;
image.png
右连接
select *from student a right join score b on a.sno =b.sno where b.score>=60;
image.png
全连接
select * from student a full join score b on a.sno=b.sno where b.score>=60;
image.png
3.SQL组合
image.png
1.minus(A)
sql1 minus sql2
2.union(A+B+C)
sql1 union sql2
3.union all(A+2B+C)
sql1 union all sql2
4.intersect(B)
sql1 intersect sql2
4.数据的基本元素
image.png
1.组合因子
1 .视图，可以是表、可以是sql处理结果
2.操作方法
- from
- join on
- where
- group by
- having
- select ：rownum、rowid
- distinct
- order by
2.组合规则
1.操作方法传入一个视图，输出一个视图
2.操作方法可以套接成串执行，但是有固定的先后顺序
3.sql和sql的结果可以做加减运算（minus、union、union all、intersect）
例子1
1.去重
1. 查询tb_student表，去掉重复数据
## 不去重
select t.password,t.student_name from tb_student t ;
## 去重distinct
select distinct t.password,t.student_name from tb_student t ;
2.按照姓名字段排序
## 不排序
select * from tb_student t ;
## 按姓名排序（正序）
select * from tb_student t order by t.student_name ;
或
select * from tb_student t order by t.student_name asc;
## 按姓名排序（倒序）
select * from tb_student t order by t.student_name desc;
## 按照按照班级倒序、姓名正序排序
select * from tb_student t order by t.sclass desc,t.student_name ;
例子2: 统计学生表中每个班的人数
| 序号 | 步骤 | SQL | 读题 |
|---|---|---|---|
| 1 | from | select *from tb_student t | 根据题目中所有用到的字段，查找需要哪几张表 |
| 2 | join on | 判断是否需要左、右、全连接 | |
| 3 | where | 读题，看有没有对字段做限制条件 | |
| 4 | group by | group by t.sclass | 读题，看有没有聚合函数 |
| 5 | having | 读题，看对聚合函数是否有限制条件 | |
| 6 | select | t.sclass,count(*) | 读题，看需要展现的字段 |
| 7 | distinct | 读题，看是否需要去重 | |
| 8 | order by | 读题，看是否需要排序 | |
| 9 | rownum | 读题，看是否需要分页，或限制条数 |
1.group by后的视图，只有group by的字段+5个聚合函数，select要展现的字段，只能从group by后面的字段+5个聚合函数中取，*也不能用
2.如果要展现的字段不在group by后面的字段和5个聚合函数内，缺失的字段，再引入新表进行关联
select a.sclass, b.cname, a.scount
from (select t.sclass, count(*) scount
from tb_student t group by t.sclass) a,
tb_class b
where a.sclass = b.cid;
分页查询
1.生成数据视图：查询排序，生成视图1
2.添加行号：最对视图1做查询，添加rownum列，列名rn
3.限制最大行号：对视图2用where限制rownum的最大值，生成视图2
4.限制最小行号：再对视图2做查询，where限制rn最小值
##总语句
select *
from (select h.*, rownum rn
from (select t.*
from tb_student t
order by t.sclass desc, t.student_name) h
where rownum <= 10)
where rn > 5;
##查询排序，生成视图1
select * from tb_student t order by t.student_name ,t.sclass desc；
##添加行号：最对视图1做查询，添加rownum列，列名rn
select h.*, rownum rn
from (select * from tb_student t order by t.student_name, t.sclass desc) h；
##对视图2用where限制rownum的最大值，生成视图2
select h.*, rownum rn
from (select * from tb_student t order by t.student_name, t.sclass desc) h
where rownum <= 10;
##限制最小行号：再对视图2做查询，where限制rn最小值
select *
from (select h.*, rownum rn
from (select t.*
from tb_student t
order by t.sclass desc, t.student_name) h
where rownum <= 10)
where rn > 5;
查询排序，生成视图1
image.png
添加行号：最对视图1做查询，添加rownum列，列名rn
image.png
对视图2用where限制rownum的最大值，生成视图2
image.png
限制最小行号：再对视图2做查询，where限制rn最小值
image.png
错误示范
最对视图1做查询，添加rownum列，列名rn
select t.*, rownum rn from tb_student t order by t.student_name ,t.sclass desc;
视图
image.png
解释：rownum rn 是在select阶段执行，order by是在select之后执行 索引加让你列后又给排序了，所以打乱了，（SQL语句执行顺序很重要！！！）
日期
| 变量类型 | 变量名 | 变量值 |
|---|---|---|
| varchar(2) | name | guoyasoft |
| number | age | 23 |
| date | 6个变量 | 封装成对象 |
| 内容 | 变量名 |
|---|---|
| 年 | yyyy |
| 月 | mm |
| 日 | dd |
| 时 | hh或者hh24 |
| 分 | mi |
| 秒 | ss |
时间内容：
| 内容 | 变量名 |
|---|---|
| 年 | yyyy |
| 月 | mm |
| 日 | dd |
| 时 | hh或者hh24 |
| 分 | mi |
| 秒 | ss |
| sql | 解释 | 例子 |
|---|---|---|
| to_char() | 将date转换为string格式 | to_char(t.hiredate,'yyyy')>='1987' |
| to_date() | 将String转为date格式 | to_date('2017-12-15','yyyy-mm-dd') |
--查询在1987年及以后入职的员工
select * from emp t where to_char(t.hiredate,'yyyy')>='1987';
--查询在2月份入职的员工
select * from emp t where to_char(t.hiredate,'mm')='02';
--查询员工信息，生日按照yyyy-mm-dd hh:mi:ss
select to_char(t.hiredate,'yyyy-mm-dd hh:mi:ss') from emp t ;
--新增一条记录，员工生日是2017年12月15日
insert into emp(empno,hiredate)values(1000,to_date('2017-12-15','yyyy-mm-dd'));
查询在1987年及以后入职的员工
image.png
视图
1.sql的每一步，都会生成视图
2.有些复杂的视图，可以把sql存起来，当表用
3.存的类型，就是视图
查询当前用户的所有表
select * from user_tables;
其中user_tables就是系统自带的一个视图，简化用户查询语句，视图的sql
create or replace view sys.user_tables as
select o.name,
decode(bitand(t.property,2151678048), 0, ts.name,
decode(t.ts#, 0, null, ts.name)),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
decode(bitand(t.property, 17179869184), 17179869184,
ds.initial_stg * ts.blocksize,
s.iniexts * ts.blocksize),
decode(bitand(t.property, 17179869184), 17179869184,
ds.next_stg * ts.blocksize,
s.extsize * ts.blocksize),
decode(bitand(t.property, 17179869184), 17179869184,
ds.minext_stg, s.minexts),
decode(bitand(t.property, 17179869184), 17179869184,
ds.maxext_stg, s.maxexts),
decode(bitand(ts.flags, 3), 1, to_number(NULL),
decode(bitand(t.property, 17179869184), 17179869184,
ds.pctinc_stg, s.extpct)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1,
decode(bitand(t.property, 17179869184), 17179869184,
decode(ds.frlins_stg, 0, 1, ds.frlins_stg),
decode(s.lists, 0, 1, s.lists)))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1,
decode(bitand(t.property, 17179869184), 17179869184,
decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
decode(s.groups, 0, 1, s.groups)))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 3),
1, 'KEEP', 2, 'RECYCLE', 'DEFAULT')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 12)/4,
1, 'KEEP', 2, 'NONE', 'DEFAULT')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 48)/16,
1, 'KEEP', 2, 'NONE', 'DEFAULT')),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
case when (bitand(t.property, 32) = 32) then
null
when (bitand(t.property, 17179869184) = 17179869184) then
decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED')
else
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')
end,
case when (bitand(t.property, 32) = 32) then
null
when (bitand(t.property, 17179869184) = 17179869184) then
decode(bitand(ds.flags_stg, 4), 4,
case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC'
when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP'
else decode(ds.cmplvl_stg, 1, 'QUERY LOW',
2, 'QUERY HIGH',
3, 'ARCHIVE LOW',
'ARCHIVE HIGH') end,
null)
else
decode(bitand(s.spare1, 2048), 0, null,
case when bitand(s.spare1, 16777216) = 16777216 -- 0x1000000
then 'OLTP'
when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000
then 'QUERY LOW'
when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000
then 'QUERY HIGH'
when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000
then 'ARCHIVE LOW'
when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000
then 'ARCHIVE HIGH'
else 'BASIC' end)
end,
decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
decode(bitand(t.trigflag, 2097152), 2097152, 'YES', 'NO'),
decode(bitand(t.property, 17179869184), 17179869184, 'NO',
decode(bitand(t.property, 32), 32, 'N/A', 'YES')),
decode(bitand(t.property,16492674416640),2199023255552,'FORCE',
4398046511104,'MANUAL','DEFAULT')
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.deferred_stg$ ds, sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv,
x$ksppi ksppi
where o.owner# = userenv('SCHEMAID')
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.obj# = ds.obj# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled';
