plsq数据库

表的基本元素

表
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';