四、Oracle 序列、常用函数、多表连接

一.序列
定义：是oracle数据库专门用来产生连续且自动增长的数字的对象
创建语法：create sequence 序列名(sq_表名) nocache(无缓存)
　　create sequence sq_student nocache
使用方法：通过两个属性来访问：nextval(下一个值),currval(当前值)
使用场景：一般是用来维护主键的自动增长：
　　insert into student_info values(sq_student.nextval,'哈哈',1,22,null)

二.常用函数：
1.字符函数：
Lower：转换小写
upper：转换大写
length：获取字符串长度
nvl：空值转换

2.数字函数
round：四舍五入的取整
trunc：截取取整，不四舍五入
mod：取余运算 mod(5,2) = 1

3.日期函数：
trunc(日期):截取日期的年月日部分。

4.转换函数：
to_char:转换成字符串 to_char(日期,'yyyy/MM/dd')
to_date:转换成日期
to_number:转换成数字

 

例：

select sq_student.nextval from dual
select sq_student.currval from dual

select * from student_info where length(student_name)=2

insert into student_info values(sq_student.nextval,'哈哈',1,22,null)

select lower('ABVCDFG') from dual

select * from score_info where trunc(score_time)=to_date('2017/12/4','yyyy/MM/dd')

select * from score_info where to_char(trunc(score_time),'yyyy/MM/dd')='2017/12/04'

select add_date(score_time,2) from score_info

insert into score_info values(3,1,100,sysdate,2)

select to_char(trunc(score_time),'yyyy/MM/dd') from score_info

select to_number('abc') from dual

三.多表连接

作用：是从多表中去获取数据

1.内连接：
特点：只显示完全匹配的数据
语法：
　　select a.class_name,b.stu_name,b.stu_age from classinfo a, student b
　　where a.class_id=b.class_id；
　　select a.class_name,b.stu_name,b.stu_age from classinfo a inner join student b
　　on a.class_id=b.class_id；

2.外连接：
会有主从关系，主表信息全部显示，从表中匹配不上的信息以null显示
a.左外联：left join ...on...
b.右外联:right join ...on...
c.全外联:full outer join ...on...
d:简易写法：(+) 加号在哪边，哪边就是从表，加号表示1~多
案例：
　　select a.class_name,b.stu_name,b.stu_age from student b ,classinfo a
　　where b.class_id(+)=a.class_id；
　　select a.class_name,b.stu_name,b.stu_age from classinfo a full outer join student b
　　on a.class_id=b.class_id；

3.集合操作
注意：必须是两个查询结果集列数和数据类型相匹配
a.union:拼接两个结果集，去除重复数据
b.union all:完全拼接，不去除重复
c.minus:集合1减去交集部分
d.intersect:取交集部分

4.oracle数据库的sequence（序列）
作用：用来生成连续的且唯一的数字序列，一般用来维护主键
创建序列：create sequence 序列名 nocache 不使用缓存
使用：序列名.currval访问当前值 序列名.nextval获取下一个值
维护主键：insert into classinfo values(seq_classinfo.nextval,'ui1班',1)

 

5.oracle的伪列（常用来做分页）
a.rownum:反应查询结果集的行序号，用来做行数条件查询
例子：select * from (select stu_name,rownum num_ from student) a where a.num_>=3 and a.num_<=5
b.rowid:是当前行的内存地址

 

 作业：

1.创建四张表：
班级信息表：主键，班级名
学员信息表：主键，姓名，性别，年龄，班级外键
科目信息表：主键，科目名
成绩信息表：主键，成绩，考试时间，学员外键，科目外键
关系：班级和学员1对多，科目和成绩 1对多 学员和成绩是1对多

2.插入数据：用序列来维护主键
班级表两条数据 （1.java1班 2.java2班）
学员表十条数据，其中有两条班级外键为null
科目表4条数据 （1.java 2.oracle 3.j2ee 4.html）
成绩表20条数据，其中有两条数据学员外键为null，然后4号科目没有成绩信息,不一定每一个人都有考试成绩

3.查询
a.查询得到如下报表
班级名 学员名 科目名 成绩 考试时间
b.查询java1班谁没有参加oracle考试的学员信息
select b.class_name,a.student_name,c.score_grade,d.project_name from student_info a
inner join class_info b on a.class_id=b.class_id and b.class_name='java6班'
left join score_info c on a.student_id = c.student_id
inner join project_info d on c.project_id = d.project_id and
d.project_name='java' and c.score_grade is null
c.查询java2班的j2ee考试成绩信息
班级名 学员名 科目名 成绩
d.查询还没有分班的学员信息
e.查询作废的成绩信息，即找不到匹配学员信息的成绩
f.查询考试时间为'2017-7-1'这天的java1班的java考试成绩信息
班级名 学员名 科目名 成绩 考试时间
g.查询成绩信息第10条到20条的数据
select * from (select rownum num_,a.* from score_info a) t where t.num_>=2 and t.num_<=3