进度记录【Day 10-11】Oracle性能、数据类型、增删改、约束、正则表达式

Day 10-11

一、性能和语法

1、连接查询：

• 笛卡尔乘积实际上会先创造索引表再相乘
  1、内联
  2、外联
• 内联

select *
from student s inner join mark m on s.sid=m.sid

• 左外连接

select *
from student s left outer join mark m on s.sid=m.sid

此时左边为主表，右边为附表，右表条件不满足为空

• 右外连接类似，右表为主表，左表为附表，左表条件不满足为空
• 全外连接，同理：左表条件不满足为空且右表条件不满足为空

select *
from student s full outer join mark m on s.sid=m.sid

• 在外联查询中，where和on不等价；在内联查询中，where和on等价
• 三表连接：用连接查询显示姓名、课程名和成绩

select sname,cname,cmark
from student s join mark m on s.sid=m.sid join course c on c.cid=m.cid

2、case函数

case 表达式
	when .. then  结果1
	when .. then  结果2
	else          结果n
	end

• 用“||”连接字符串
• 学生性别表述

select sname||'is'||case ssex
	when '男' then  'male'
	when '女' then  'female'
	else			'unknown sex'
	end
from student

• 找出每个学生的均分，并且给出评判：

85分以上优秀，70分普通，70以下差，没有成绩则显示无成绩

select sname||case ssex
	when amk is null then '无成绩'
	when amk>=85 then '的均分为'||amk||'分，成绩优秀'
	when amk>=70 then '的均分为'||amk||'分，成绩普通'
	else '的均分为'||amk||'分，成绩较差'
	end
from student s left join (
	select sid,trunc(avg(cmark),2) amk from mark group by sid
) m on s.sid=m.sid

• 请统计每个学生的选课数目，显示两列：姓名和数目，如果没有选课，其数目为0

select sname,case 
			when c is null then 0
			else c
			end
from student s left join (
	select sid,count(*) c from mark m group by sid
) m on s.sid=m.sid

3、集合运算

• 集合三运算：交集，并集，补集（差集）

（1）并集运算

• 将两次查询用union连接获得并集
• union 自动进行去重，用union all 不去重
• 集合运算的必要条件：两个集合列数相同，数据类型相兼容

（2）交集运算

-将两次查询用intersect连接获得交集

（3）补集（差集）运算

-将两次查询用minus连接获得补集（差集）,左边（第一个查询）为主表，剩下的是左边和右边不相交的部分

二、数据类型

• 分为数值类型和字符类型
  1：数值类型包括整数，小数，时间
  2：字符串，定长和变长

1、数值类型

1：oracle早期版本向c语言对齐
byte，short，int，long
2、number为常用数值类型

-- 表示数据的有效位有5位，精确到小数点后2位，小数点后可以超过精度，自动四舍五入
-- 数据长度超过设定的有效位时，插入失败，number(5,2)即整数位不能超过3位
number(5,2)

3、date时间类型，数值类型，不属于字符串

-- 返回系统当前时间，格式为日月年
select sysdate from dual
-- 获得自定义格式的当前时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
-- 建表并插入数据
create table stu(
	sname	varchar2(90)
	birth	date
)
insert into stu values(
'张三',to_date('10-4-8','yyyy-mm-dd')
)
-- 根据当前时间修改的时间数据(当前时间，第二天，下一小时)
select sysdate,sysdate+1,sysdate+1/24 from dual

• to_char是将非字符串类型转换为字符串，比如可以将一个整数或时间转换为字符串
• to_date是将一个时间字符串转换为数值类型
  4、timestamp用于保存时差

2、字符串

1、 char类型

• 如果保存汉字类型字符串，一个汉字算3个长度
• 长度固定，未使用的空间用空格替代
• 比较浪费空间

2、varchar2类型

• varchar类型的数据特征：空间效率高，但时间效率低

eg.定义一张学生表，包括姓名，学号，性别和年龄的特征，分别用什么数据类型？

• 姓名 name varchar2()
• 学号 number char()
• 性别 sex char(3)
• 年龄 birth date

长度不变的字符串用char
长度可变的字符串用varchar

3、CLOB BLOB

• CLOB：Character Large Object 保存文字类型大型数据
• BLOB：Binary Large Object 保存数值类型大型数据
• char 的max:1000
• varchar2 的max:4000

4、SQL = DDL + DML + DCL + DQL + TCL

• SQL 结构化查询语言 structure query language
• 数据库是用于保存数据和进行数据处理的，必须存在数据和结构
• 数据 数据库实例
• 结构 数据:实体
• DDL(create alter drop)：对结构的操作
• DML(insert delete update)：对数据（实体）的操作
• DQL(select)查询
• DCL(grant revoke)权限控制
• TCL(commit rollback)事务处理

三、表格结构的创建、删除和修改

• 创建表格

create table 表格名(
	属性名 属性类型,
	--逗号分开多个属性
)

• 删除表格

drop table 表格名

• 修改表格

--增加列
alter table 表格名 add 属性名 属性类型
--删除列
alter table 表格名 drop column 属性名

四、增删改数据

1、插入数据

insert into 表名 values('数据值1','数据值2','数据值3')

插入数据的值必须符合创建表格时的约束，包括主键、非空、唯一、数据类型等

• 插入数据带日期时

insert into stu values(10003,'王五','男',to_date('2015-4-18','yyyyy-mm-dd'))

• 插入数据缺省时，并且这种插入方式可以将属性任意顺序插入

insert into stu(sid,sname,ssex) values(10004,'赵六','男')

插入单行记录建议用这种表名后加上对应属性名的方法

• 插入多条记录

insert into stu(sid,sname,ssex)
select sid,sname,ssex from student

从他表查询插入当前表

• 进行表格备份，将原有的数据转入备份表中

create table oldStu as
select sid,sname,ssex,sclass from student

• 将每个学生成绩排名前三的课程记录备份至markHist表中

create table markHist as
select sid,cid,cmark from(
	select m.*,row_number() over (partition by sid order by mark desc) n from mark m
) where n <=3

• 将所有男生的均分，总分，最高分，最低分，选课数目备份至学生学习情况表scoreinfo

create table scoreinfo as
select sid,trunc(avg(cmark),2),sum(cmark),max(cmark),min(cmark),count(*) 
from mark,student
where s.sid=m.sid and s.ssex='男' 
group by m.sid

2、更新数据

update 表名
set 属性=...
where ...

• 将每个男生的数学成绩+5分

update mark
set cmark=cmark+5
where cid=(select cid from course where cname='数学') and sid in(
	select sid from student where ssex='男'
)

-如果学生的最低分对应的是数学课的话，将数学成绩+2分

update mark
set cmark=cmark+2
where sid in (
	select m.sid from mark m1,(select sid,min(cmark) t from mark group by sid) m 
	where m1.cmark=m.t and m1.sid=m.sid and m1.cid=(
	  select cid from course where cname='数学'
	)
) and cid=(
  select cid from course where cname='数学'
);

3、删除数据

delete
from 表名
where

• 删除学生表中性别为空的数据

delete
from stu
where ssex is null

• 删除学生表中重复的多余记录

delete
from student
where rowid not in(
	select min(rowid) from student group by sid
)

rownum 是记录所在的抽屉的编号
rowid 是一个hash code，近似认为这个就是记录所在内存中的“地址”

五、约束

1、主键约束 primary key

• 主键约束的效果：不可为空（非空性，非空约束），不可重复（唯一性，唯一约束）

create table stu(
	sid		number primary key,
	sname	varchar2(100),
	ssex	char(3)
);

-- 第二个插入违反了主键约束
insert into stu(sid,sname) value(101,'张三');
insert into stu(sid,sname) value(101,'李四');

-- 下列更新违反了主键约束
insert into stu(sid,sname) value(102,'李四');

update stu
set sid=101
where sname='李四'

• 约束分两种方式

--简写
create table stu(
	sid		number primary key,
	sname	varchar2(100),
	ssex	char(3)
);
-- 完整
-- constraint 约束名称 约束类型(约束对象)
create table stu(
	sid		number,
	sname	varchar2(100),
	ssex	char(3)
	constraint pk_sid primary key(sid)
);

• 修改表格，增加约束

alter table stu add constraint pk_sid primary key(sid);

• 修改表格，删除约束

alter table stu drop constraint pk_sid;

2、外键约束 foreign key

• 外键是两表关联的矢量中的起点，这个起点指向另外一张表的终点，这个终点必须是主键或者唯一约束
• 外键所指向的表格和其主键（或者唯一约束）必须在外键创建之前就存在

-- 如果没有stu表和course表，此表创建失败
create table mark1(
	sid number,
	cid number,
	cmark number,
	constraint pk_sidCid primary key(sid,cid),
	constraint fk_sid foreign key references stu(sid)
	constraint fk_cid foreign key references course(cid)
)

• 学生管理系统一共4张表，学生表，成绩表，课程表，教师表
  请说出这四张表的创建顺序（有主键和外键）

学生表、教师表、课程表、成绩表
删除表的顺序相反

3、非空约束

create table stu(
	sid		number primary key,
	sname	varchar2(100) not null,
	ssex	char(3) not null
)

有非空约束的属性在插入时不能为空

4、唯一约束

• 简写

create table stu(
	sid		number primary key,
	sname	varchar2(100) not null,
	ssex	char(3) not null
	tel 	char(11) unique
)

• 完整约束

create table stu(
	sid		number primary key,
	sname	varchar2(100) not null,
	ssex	char(3) not null
	tel 	char(11)
	constraint uk_tel unique(tel),
)

5、校验约束

• 完整约束

create table stu(
	sid		number primary key,
	sname	varchar2(100) not null,
	ssex	char(3) not null
	tel 	char(11)
	constraint uk_tel unique(tel),
	constraint ck_ssex check(ssex in('男','女'))
)

• 简写

create table stu(
	sid		number primary key,
	sname	varchar2(100) not null,
	ssex	char(3) not null check(ssex in('男','女'))
	tel 	char(11)
	constraint uk_tel unique(tel),
)

• 定义成绩表，成绩必须是0-100之间的数值

create table mark(
	sid number,
	cid number,
	cmark number not null check(cmark between 0 and 100)
)

六、正则表达式

• 可度量的模糊匹配字符串的方法

select 'ok' 结果 from dual
where regexp_like('ab5cd','ab\dcd')

上面的正则表达式是在母串中找符合条件的子串

like 中 下划线_表示任意字母
正则表达式中 \d 表示一个任意数字（0-9）
.用来描述一个任意字母
符号簇：
1、[[:alpha:]] 等价于.
2、[[:digit:]] 等价于\d
3、[[:alnum:]] 任意一个字母或数字
4、[[:space:]] 白字母：空格或回车或\t 等价于\s
5、[[:upper:]] 任意一个大写字母
6、[[:lower:]] 任意一个小写字母
7、[[:punct:]] 任意一个标点符号
8、[[:upper:]] 任意一个十六进制字母

1、字母集[]的表示方式：只能匹配字母集中的任意一个字母

select 'ok' 结果 from dual
where regexp_like('ab4cd','ab[12433]cd')

• 电话号码为133/138开头的

select * from stu
where regexp_like(tel,'13[38]\d(8)')

字母集中如果出现-，表达的是一个范围

• 字符串的匹配，不能进行数值匹配

• 字符为形态和编码两个属性组合，ASCII表中
  空格为32
  0-9的编码为48-57
  A-Z的编码为65-90
  a-z的编码为97-122

• 字母集[a-f]、[0-9]、[4-a]（实际上是ascii中52到97的字母）

• 匹配十六进制字符：[[:xdigit:]] [0-9a-fA-F]

• 字符串的大小取决于其字母的编码大小 → 字符串可以比大小

• 倒字母集

-- 匹配除了a-f的字母
[^a-f]

字符串的匹配()

where regexp_like('我喜欢吃苹果，这是我最爱水果','我喜欢吃(苹果|橘子|菠萝)，这是我最爱吃水果')

• 用两种方法匹配
  hello,every one
  Hello,every one

-- 方法一
select 'ok' result from dual
where regexp_like('hello,every one','[Hh]ello,every one')
-- 方法二
select 'ok' result from dual
where regexp_like('hello,every one','(H|h)ello,every one')

• 系统量词

* 表示任意多个
+ 表示至少一个
? 表示有1个或0个
(n,m) 表示最少n个，最多m个
(n) 表示n个
. 表示任意一个字母
-- eg_1
select 'ok' result from dual
where regexp_like('a57b','a\d+b')
-- eg_2
select 'ok' result from dual
where regexp_like('ab','a\d?b')
-- eg_3
select 'ok' result from dual
where regexp_like('a356b','a\d(3,5)b')
-- 如果想要精确的定义到字符. 用到转义符
-- eg_3
select 'ok' result from dual
where regexp_like('a.b','a\.b')

• 转义符的作用：
  1、将一个普通字母进行特殊含义化，将其变成“特殊”功能字母，比如\n，\a，\b
  2、将一个特殊字母的“特殊”功能去除，将其还原成普通字母，比如\，.

• 匹配十六进制的MAC地址

select 'ok' result from dual
where regexp_like('    物理地址 . . . . . . . . . . . . ： CC-2F-71-4B-AC-0D','  *(物理地址) (\. )+：([0-9A-Fa-f](2)-)(5)[0-9A-Fa-f](2) *')

• ^ 前导符加在开头表示必须以当前串开头
• $ 后缀符加在结尾表示必须以当前串结束
• 匹配当前字符串

select 'ok' result from dual
where regexp_like('    IPv4 地址 . . . . . . . . . . . . ： 192.168.43.18（首选）','  *IPv4 地址 (\. )+：(\d(1,3)\.)(3)\d(1,3)\(首选\)*')

• 考虑到ip地址中每三位数的范围在0-255

分成如下几种可能：
个位数：0-9
十位数：1-9和个位的0-9之间的组合
百位数：100和199之间、200和249之间、250-255

(\d|[1-9]\d|1\d(2)|2[0-4]\d|25[0-5])
替换上面的(\d(1,3)

七、课后练习

1、通过create语句完成整个图书馆管理系统的创建,要求代码可以连续运行；完成后最后一部分添加drop table的语句,将之前创建的所有表格清理干净

图书馆管理系统软件需求：
1、图书馆系统支持图书信息的录入，修改和删除，信息至少包括 ：图书名称、图书价格
图书所属出版社，出版社联系方式，出版社地址，出版社名称 ，图书折旧率
2、图书信息中包含一类信息：图书类别，比如《电子机械》书籍 属于[电子类]和[机械类]
此书只有购买了[电子类]或者[机械类]身份的读者才能借阅， 一般书籍都是[普通类]，任何读者皆可借阅
3、系统支持读者注册、注销和信息修改，至少包含：读者姓名、
性别、年龄、籍贯、vip等级以及所购买的读者身份（见需求2 ）
4、读者的vip等级决定该读者一次可以借阅的书籍数目，借阅的最
大时长，过期后缴纳滞纳金的折扣
5、系统支持借阅和归还图书，并能够根据读者或者书籍查询相关
借阅历史
6、归还图书如果过期需缴纳滞纳金，和书籍借阅过期时间、
读者的滞纳金折扣、书籍价格、书籍折旧率相关

create table book_message (book_id  varchar(10) primary key,book_name varchar(20),book_price number(6,2),book_publisher_id varchar(18));
comment on table book_message is '图书信息';
comment  on column book_message.book_id is '图书编号';
comment on column book_message.book_name is '图书名称';
comment on column book_message.book_price is '图书价格';
comment on column book_message.book_publisher_id is '出版社编号';
describe book_message;
select * from book_message;

create table book_manage_message (bar_code varchar(20) primary key,book_name varchar(20),depreciation_rate number(3,2));
comment on table book_manage_message is '图书管理信息';
comment  on column book_manage_message.bar_code is '条形码';
comment on column book_manage_message.depreciation_rate is '折旧率';
describe book_manage_message;
select * from book_manage_message;

create table publisher_message (book_publisher_id varchar(18) primary key,publisher_name varchar(20),publisher_address varchar(30),publisher_phone number(11));
comment on table publisher_message is '出版社信息';
comment  on column publisher_message.publisher_name is '出版社名称';
comment on column publisher_message.publisher_address is '出版社地址';
comment on column publisher_message.publisher_phone is '出版社联系方式';
describe publisher_message;
select * from publisher_message;

create table classify_message  (classify_id varchar(18) primary key,classify_name varchar(18),classify_descripition varchar(25));
comment on table classify_message is '分类信息';
comment on column classify_message.classify_id is '分类编号';
comment on column classify_message.classify_name is '分类名称';
comment on column classify_message.classify_descripition is '分类描述';
describe classify_message;
select * from classify_message;

create table book_classify_message  (book_id varchar(10), classify_id varchar(18) ,classify_descripition varchar(25),primary key (book_id,classify_id));
comment on table book_classify_message is '图书分类信息';
describe book_classify_message;
select * from book_classify_message;

create table reader_message (reader_id varchar(10) primary key,reader_name varchar(15),reader_sex char(2),reader_birth date,vip_id number(7));
comment on table reader_message is '读者信息';
describe reader_message;
select * from reader_message;

create table vip_message (vip_id number(7) primary key,vip_name varchar(10),one_borrow_count number(3),one_borrow_time date,late_fee_discount number(3,2));
comment on table vip_message is 'vip信息';
describe vip_message;
select * from vip_message;

create table reader_classify_message (reader_id varchar(10),classify_id varchar(18),classify_description varchar(25),primary key(reader_id,classify_id));
comment on table reader_classify_message is '读者分类信息';
describe reader_classify_message;
select * from reader_classify_message;

create table borrow_message (borrow_id varchar(10) primary key,reader_id varchar(10),bar_code varchar(20),borrow_time date,return_time date,late_fee number(5));
comment on table borrow_message is '借阅信息';
describe borrow_message;
select * from borrow_message;



drop table book_message;
drop table book_manage_message;
drop table publisher_message;
drop table classify_message;
drop table book_classify_message;
drop table reader_message;
drop table vip_message;
drop table reader_classify_message;
drop table borrow_message;

2、将之前设计的数据库四张表格中每张都添加对应的约束,比如主键,外键

尤其是学生表,请添加属性:手机号码,身份证号码,以及邮箱.并且为其设定约束(正则表达式)
特别提醒：生日字段中不要使用sysdate来进行校验日期的合法性

create table stu(
	sid		number primary key,
	sname		varchar2(100) not null,
	ssex		char(3) not null
	birth		date()
	tel 		char(11)
	idnumber 	char(18)
	email		varchar2(50)
	constraint uk_tel unique(tel),
	constraint ck_ssex check(ssex in('男','女'))
	constraint ck_idnumber check(regexp_like(idnumber,'/d(17)[01X]'))
	constraint ck_email check(regexp_like(email,'*@*\.com'))
);

create table mark1(
	sid number,
	cid number,
	cmark number,
	constraint pk_sidCid primary key(sid,cid),
	constraint fk_sid foreign key references stu(sid)
	constraint fk_cid foreign key references course(cid)
);


create table markHist as
select sid,cid,cmark from(
	select m.*,row_number() over (partition by sid order by mark desc) n from mark m
) where n <=3;
alter table markHist add constraint pk_sidCid primary key(sid,cid);
alter table markHist add constraint fk_sid foreign key references stu(sid);
alter table markHist add constraint fk_cid foreign key references course(cid);

create table scoreinfo as
select sid,trunc(avg(cmark),2),sum(cmark),max(cmark),min(cmark),count(*) 
from mark,student
where s.sid=m.sid and s.ssex='男' 
group by m.sid;
alter table markHist add constraint pk_sid primary key(sid);
alter table markHist add constraint fk_sid foreign key references stu(sid);