进度记录【Day 13】Oracle索引、视图、数据库知识点整理、程序

Day 13

索引

• 源代码都是字符串，字符串是计算机无法理解的内容，经过编译后变量名变成地址，常规的代码对数据的操作都是通过地址来访问空间，从而获得对应的数据
• 哈希算法：在一个有序的数据集中，找到所需的数值所在的地址。
• 插入的数据未必在表格的末尾，根据表的索引插入数据，表格有序
• 用delete可以回滚rollback

delete from student where 1=1
-- 每次只删一条数据，数据保存在历史记录中，可回退
-- 如果表数据较多，效率低，因为要保存在历史记录中

--高效删除整个表内数据
truncate table 表名
--实质为将所有数据设为非法，内存回收，无法回退

• 表结构对sage设定了顺序，这个就是所谓的索引，结构索引：聚集索引，聚合索引

delete from student where sid<10009 and sage>19
-- 这条删除语句在执行时，判断条件不是先看sid，而是看sage
-- 因为sage被设置为索引，找到满足sage条件的数据后再判断sid

• 如果表有主键，默认主键就是聚集索引
• 聚集索引最多只有一个，联合主键默认的索引也只有一个，而不是两个
• 创建非聚集索引，可以使查询时即有sid排序，也有sage排序

-- 表格默认sid主键为聚集索引，创建一个ssage的非聚集索引
create index idxSage on student(sage)
-- 这是上面这条语句创建的sage索引表，左边是地址，右边是sage
select rowid,s.sage from student s order by sage

• 聚集索引最多只能有一个，而非聚集索引可以有多个
• 方便了查询速度，但牺牲了空间，保存了多个索引表（空间换时间）
• 什么情况下对属性建立索引？查询最频繁的属性，性价比最高的
• 唯一索引：因为索引唯一，搜索速度优于非唯一索引

--唯一索引的前提是属性有unique约束
create unique index ... on student(sname)

视图

• 桌面快捷方式不保存任何数据，视图也不保存任何数据
• 视图的作用相当于查询表的快捷键

-- 数据库中权限控制的最小颗粒是表
select * from student;

-- 创建有三列属性的student表快速查询视图，相当于对外公开的属性只有这三列
create view vstu as
select sid,sname,ssex from student

-- 查看视图
select * from vstu

-- 通过视图实现分步骤查询
create view step1 as select sid,trunc(avg(cmark),2) amk, from mark group by sid;
select * from (step1);

• 利用视图模拟均分表，找出最高均分对应的学号

create view step1 as select sid,trunc(avg(cmark),2) amk, from mark group by sid

select sid from step1 where amk in(
	select max(amk) from step1
);

• 找出姓名，课程名，成绩

create view step2 as select sname,cname,cmark from student s,course c,mark m
where s.sid=m.sid and c.cid=m.cid;
select * from step2; 

• 视图能否进行增删改？

1、视图中的数据来自单表时可以增删改；
2、数据来自真实的数据，没有修改；
修改数据指类似trunc(amk,2)这样的函数操作。

• 往视图中插入数据错误范例

create table student(sid ... sname ... ssex char(3) not null);
create view stuv as select sid,sname from student;
insert into stuv(sid,sname) values(20001,'刘虎');

插入语句报错，原因是实际上插入语句不是将数据插入视图中，而是student表中，根据student表的ssex非空约束，插入失败

创造备份表和原表不同步，而视图永远和原表同步
对视图操作实际上是对表格操作

• 打印最后一天的日期，并且格式为 yyyy-mm-dd

select to_char(last_day(to_date('2015-3-18','yyyy-mm-dd')),'yyyy-mm-dd') from dual

• 现有学生表中显示每个学生的姓名和生日

select sname,add_months(sysdate,-12*sage) from student

• 创建一张学生表stu2，表中有姓名和生日
  往表中插入两条数据
  用查询语句获得学生姓名和年龄

create table stu2 (
	sname 	varchar2(20),
	birth 	date()
);
insert into stu2(sname,birth) values('张三',to_date('1997-11-05','yyyy-mm-dd'))
insert into stu2(sname,birth) values('李四',to_date('1997-10-06','yyyy-mm-dd'))

select sanme,(extract(year from sysdate) - extract(year from birth) + 1) sage from stu2

数据库知识点

时间

• 查询系统时间的两种格式

-- sysdate 内置函数可以获取当前的系统日期和时间，返回 DATE
SELECT SYSDATE FROM DUAL
-- systimestamp 函数可以返回当前日期、时间和时区
SELECT SYSTIMESTAMP FROM DUAL

• sysdate默认显示日月年，可以使用to_char显示时分秒

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

• 指定日期和时间，并转换为DATE格式

TO_DATE('1909-08-09 06:30:10',' YYYY-MM-DD HH24:MI:SS')
-- 注意字符串和后面的格式要对应

• LAST_DAY(d)，返回指定日期当月的最后一天。

select SYSDATE,LAST_DAY(SYSDATE) from dual

• 计算出’2015-3-18’这一天所在月的最后一天，并且以 yyyy-mm-dd的格式打印

select to_char(last_day(to_date('2015-03-18','yyyy-mm-dd')),'yyyy-mm-dd') from dual

• EXTRACT(fmt FROM d)，提取日期中的特定部分。

fmt 为： YEAR、 MONTH、 DAY、 HOUR、 MINUTE、 SECOND。其中 YEAR、 MONTH、 DAY
可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、 MINUTE、 SECOND 必
须与 TIMESTAMP 类型匹配

select SYSDATE 时间,
       EXTRACT(YEAR from SYSDATE) 年,
       EXTRACT(MONTH from SYSDATE) 月,
       EXTRACT(DAY from SYSDATE) 日,
       EXTRACT(HOUR from SYSTIMESTAMP) 时,
       EXTRACT(MINUTE from SYSTIMESTAMP) 分,
       EXTRACT(SECOND from SYSTIMESTAMP) 秒,
from dual

• 现有的学生表中显示每个学生的姓名和生日

select sname,to_char(add_months(sysdate,-12*sage),'yyyy-mm-dd') from student

• 创建一张学生表stu2，这张表中有姓名和生日
  向这张表中插入两条数据
  用查询语句获得学生的姓名和年龄

create talbe stu2(
	sname varchar(50);
	birth date;
)
insert into stu2(sname,birth) values('张三',to_date('1997-11-05','yyyy-mm-dd'));
insert into stu2(sname,birth) values('李四',to_date('1998-04-03','yyyy-mm-dd'));
select sname,(extract(year from sysdate)-extract(year from birth) + 1) from stu2

数字函数

• 数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

ABS(x) x 绝对值 ABS(-3)=3
ACOS(x) x 的反余弦 ACOS(1)=0
COS(x) 余弦 COS(1)=1.57079633
CEIL(x) 大于或等于 x 的最小值 CEIL(5.4)=6
FLOOR(x) 小于或等于 x 的最大值 FLOOR(5.8)=5
LOG(x,y) x 为底 y 的对数 LOG(2,4)=2
MOD(x,y) x 除以 y 的余数 MOD(8,3)=2
POWER(x,y) x 的 y 次幂 POWER(2,3)=8
SQRT(x) x 的平方根 SQRT(4)=2

ROUND(x,y) x 在第 y 位四舍五入 ROUND(3.456,2)=3.46
ROUND(x) 相当于默认y=0，在个位四舍五入
y 是正整数，就是四舍五入到小数点后 y 位。 ROUND(5.654,2)=5.65
y 是负整数，四舍五入到小数点左边|y|位。 ROUND(351.654,-2)=400

TRUNC(x,y) x 在第 y 位截断 TRUNC(3.456,2)=3.45
TRUNC(x) 在缺省 y 时，默认 y=0；比如： TRUNC (3.56)=3
y 是正整数，就是四舍五入到小数点后 y 位。 TRUNC (5.654,2)=5.65
y 是负整数，四舍五入到小数点左边|y|位。 TRUNC (351.654,-2)=300

字符串

• ascii(‘x’) 返回字符x在ascii中的编码

-- 打印 0 a A 汉   的ascii编码
select ascii('0'),ascii('a'),ascii('A'),ascii('汉') from dual
-- 参数只能是一个字符，多个时只计算第一个字符的ascii编码

• 字符串连接符 ||

-- concat('ab','cd') 也是字符串连接，但||更常用
select '大家好，我的名字是'||sname||'，我来自'||sanativeplace||'，我今年'||sage||'岁' from student

• 找子串所在位置

select instr('hello world','ell') from dual
-- 返回结果为2，和java、c语言不同，返回0代表未找到
select instr('hello world','ell',0) from dual
-- 也可以人为设置起点为0，返回-1代表未找到

• 计算字符串长度

select length('abc') from dual

• 字符串大小写

insert into student(sid,sname) values(11111,'Abc')
--找不到上面这条插入的数据，因为字符串没有匹配→区分大小写
select * from student where sname='abc'
-- 可以在插入数据时用upper/lower函数将字符串转化为大/小写

• 修剪字符串

insert into student(sid,sname) values(11111,trim('    AB  C    '))
-- 得到的结果为 AB  C，即去除了左右两侧的空格
-- 类似的还有LTRIM和RTRIM去除左/右的空格

• 字符串截取获得子串

select substr('abcdefghi',2,3) from dual
--返回bcd

序列

• 用来产生连续的数字，序列常常用来作为主键中增长列。
• 序列中的可以升序生成，也可以降序生成。

create sequence sequence_name
[start with num]				--从num这个数字开始生成
[INCREMENT BY increment]		--每次增长increment的增量
[MAXVALUE num|NOMAXVALUE]		--设置最大值
[MINVALUE num|NOMINVALUE]		--设置最小值
[CYCLE|NOCYCLE]					--默认不循环
[CACHE num|NOCACHE]				--预生成一组序列，提高效率

create sequence sq_sid
start with 2000 increment by 2;

select sq_sid.nextval from dual;	--用序列生成序号，此时为2000

编程

• 数据库的代码结构

set serveroutput on
declare
--变元定义，这里的内容都是编译阶段的代码，不能在这赋值，可以赋初值
begin
--代码体，语句体，这里都是在运行阶段执行的内容
dbms_output.pin_line('hello world!');	--需要提前开启打印开关 
exception
--异常处理
end;

• 程序只能脚本输出，不能查询输出
• 赋值和等号

c java		=叫做赋值号		==叫等号
数据库		=叫等号			:=叫赋值号

• 程序提示用户输入学生姓名
  程序根据学生姓名找到学生的年龄

set serveroutput on
declare
 name	varchar2():='&请输入您的姓名';
 age	number;
begin
 select sage into age from student where sname=name;
 dbms_output.put_line(name||'的年龄为'||age||'岁');
end;

• 程序提示用户输入学生姓名
  程序根据学生姓名找到学生的最高分，均分和总分
  程序打印“XX的总分为…均分为…最高分为…”

set serveroutput on
declare
 age	number;
 name	varchar2():='&请输入您的姓名';
 avg	number;
 sum	number;
 max	number;
begin
 select sage into age from student where sname=name;
 select trunc(avg(cmark),2),max(cmark),sum(cmark) into avg,max,sum
 from mark where sid=(
	select sid from student where sname=name
 ) group by sid;
 dbms_output.put_line(name||'的均分为'||avg||'，总分为'||sum||'最高分为'||max);
end;

• 程序提示用户输入学生姓名
  程序根据学生姓名找到学生的均分，并判断成绩情况

set serveroutput on
declare
  nm	varchar2()='&请输入学生姓名';
  amk	number(5,2);
begin
  select avg(cmark) into amk from mark
  where sid=(
	select sid from student where sname=nm
  ) group by sid;
  if amk>=90 then
	dbms_output.put_line(nm||'的成绩优秀：'||amk);
  elsif amk>=70 then
	dbms_output.put_line(nm||'的成绩普通：'||amk);
  else
	dbms_output.put_line(nm||'的成绩较差：'||amk);
  end if
end;

• 程序提示输入学生姓名，程序校验学生最高分和最低分，如果两个分数差值超过15分，提示学生存在严重偏科，XX课程急需提升；如果分数差值在10分以内，提示学生学习非常稳定和均衡，否则提示学生的XX科目还有提升空间。

set serveroutput on
declare
  nm	varchar2(100)='&请输入学生姓名';
  max	number(5,2);
  min	number(5,2);
  cnmin	varchar2(200);
begin
  select max(cmark),min(cmark) into max,min from mark where sid=(
	select sid from student where sname=nm	
  ) group by sid;
  select cname into cnmin from course where cid=(
	select cid from mark where cmark=min and sid=(
		select sid from student where sname=nm
	)
  )
  if max-min > 15 then
	dbms_output.put_line(nm||'偏科严重，'||cnmin||'急需提升');
  elsif max-min < 10  then
	dbms_output.put_line(nm||'学习非常稳定和均衡');
  else
	dbms_output.put_line(nm||'的'||cnmin||'还有提升空间');
  end if
end;

转载：https://blog.csdn.net/qq_41103187/article/details/105674309