DAY 178 oracle基础

一、 数据库介绍

数据库

广义数据库

• 是物理操作系统和磁盘的集合

狭义数据库

• 数据库+数据库操作系统

Oracle：位于物理内存的数据结构，是有操作系统的多个后台进程一个共享的内存池锁做成，共享的内存可以被所有进程方森

存储结构：

• 物理存储

• 逻辑结构

    数据库 --> 表空间 --> 段 --> 区 --> Oracle 数据块
  

SQL 语言

1、 DDL （data definition languages）

• 数据定义语言
• 定义了不同数据段、数据库、表、列、索引等数据库对象
• CREATE DROP ALTER RENAME TRUNCATE
• 常常有数据库管理员（DBA)使用
• 用在定义或改变表的结构、数据类型、表之间的连接和约束等初始化工作上，大多数在表的建立时使用
• 操作

1. 创建表

-- 创建一张表 
create table name( 字段名 类型（长度），.....);
-- 从其他表拷贝结构
create table name1 as select 字段列表 from 已有表 where 1!=1;

2. 修改表结构

-- 修改表名
rename 原表名 to 新表名
-- 修改列名
alter table 表名 rename column 列名 to 新列名
-- 修改字段类型
alter table 表名 modify(字段 类型）
-- 添加列
alter table 表名 add 字段 类型
-- 删除列
alter table 表名 drop column 字段

3. 删除表

drop table 表名;

2、 DML （data manipulation language）

• 数据操控语句
• 用于添加、删除、更新和查询数据库记录，并检查数据库完整性
• insert,delete,update,select
• 对表内进行操作

3、 DCL（data control language）

• 数据控制用于
• 用于控制不同数据段直接的许可和访问级别的语句
• 定义了数据库、表、字段、用户访问权限和安全级别
• grant、revoke回收权利、commit提交事务、rollback回滚事务

二、 创建用户和表结构

1 sys登录

conn sys/root as sysdba
select instance_name from v$instance;

2 创建表空间

create tablespace 表空间名 datafile ' 路径文件名.dbf' size 200m;

创建一个 tds 文件

3 创建用户

create user scott identified by tiger default tablespace scott_tb_space;
create user 用户名 identified by 密码 default tablespace 表空间; -- 创建用户并制定表空间

4 授权权限

grand dba to scott;
grand dba to 用户名;

5 普通用户登录

conn scott/tiger@xe
select * from dual;

三、 表设计

表

数据库中以表为组织单位存储数据，用来存储一些事物的信息；

表名+存储信息

约束

主键约束

primary key
用于定位表中单个行的方式，可唯一确定表的一行
唯一且非空
一个表只能由一个主键

唯一性约束

unique
确保每一行的唯一性，但允许有多空值

非空约束

not null
非空

外键约束

foreign key
主表：被参考的表
从表 参考的表，必须按照主表要求来

检查约束

check

四、 select 语句

1. 表示查询

select  *|colname[,...] from table [alians]
-- * 可以用所有内容的具体信息替代
select deptno,dname from dept;

2. 去重

关键词：instinct

select distinct deptno from emp

3. 别名

方法 1：用 as 关键词

select ename as 姓名， sal as 工资 from emp；

方法 2：省略关键词

select ename 姓名，sal 工资 from emp;

4. 排序

order by 关键词

select ename,sal from emp order by sal desc;
select ename , sal , deptno from emp order by deptno asc, sal desc;

5. 执行顺序

五、 处理空值

方式 1 nvl()函数

--  第一个参数不为空时返回的数，第二个参数，为空时返回的数
select ename, sal, comm, sal+somm as 月收入, from emp; -- 当 comm 为空时，sal 也为空，影响数据结果
select ename, sal, comm, sal+somm + nvl(comm,0)  as 月收入, from emp;
select nvl(1,100) from dual;
select nvl(null,100) from dual;
select * from emp where vvl(comm,0)<=0;

方式 2

nulls first/nulls last 用于排序

select * from emp order by comm desc nulls first/nulls last;

空值判断

1. is null
2. is not null
3. not ... is null

六、 伪列和虚表

伪列

本不应存在的一列，然后根据需求再临时添加一列

-- 求年薪
select ename, sal, 1,from emp;
select ename , sal, sal*12 as 年薪 from emp;

虚表

• dual 虚拟表
• 用于构成 select 的语法规则，Oracle 保证 dual 永远只有一条数据
• 该表只有一行一列，用于选择系统变量或者求一个表达式的值
• 可执行插入更新、删除和 drop 操作，————但如果执行 drop 的话，会造成系统崩溃
• select 完成一些信息，需要借助一个对象时，可以用这个表

如

select 9899*888 from dual;

七、 查询条件

• 条件查询

分类：

• >,>,=,>=,<=
• !=,<>,^=
• between ...and： 闭区间
• not

select * from table where
select * from emp where deptno =10
select ename, deptno  from emp where deptno !=10
select * from emp where ename ='SMITH'

• 条件连接运算符
  • and
  • or
  • not

八、 模糊查询

• % :位数不定的模糊字符
• like

select * from emp where like ’%S%'

select * from emp where ename like '%a%%' escape ('a');
select * from emp where ename like '%aaa%%' escape('a')
select * from emp where ename like '%a%%a_%' escape('a'）

九、 where 子句

select * from where group by ... having ... order by ...
select * from emp where deptno = (select deptno from dep where dname = 'SALES')
select * from salgrade where sal between (select losal from salgrade where grade  = 2) and (select hisal from salgrad where grade =2)

十、 group by 分组

select ... from .... where ... group by ...
select avg(sal) from emp group by deptno;
select count(*) from emp group by deptno;

十一、 having 过滤

select deptno. coumt(*), from emp where sal >2000 group by deptno having count(*) >=2;

十二、 函数

字符串拼接 ||

select ename from emp;
select ename, ename ||"a"  别名 from emp;
select ename ,comm,ename || comm  as test from emp -- 当有 null 时，会不进行拼接

单行函数

• 在对应表记录时，一条记录返回一个结果

-- 字符串函数

-- concat(x,y)  连接字符串x 和 y
select ename||job as namejob from emp;
select concat(ename,job) from emp;

-- instr（x,str,start,n)；在x中寻找str，可以指定从start开始，可以从指定从第n次开始，返回字符串的位置
select instr('helloworld' ,'e') from dual
select instr('helloworld' ,'a') from dual
select ename, instr(ename,'A') from emp;

-- length(x)：返回x的长度

-- lower（x）：x转化为小写
-- upper（x）：x转化为大写

-- ltrim（x, trim_str)：把x左边截去strim_str字符串，缺省截去空格
select ltrim('   abc  abc   ') from dual

-- rtrim（x, trim_str)：把x右边截去strim_str字符串，缺省截去空格
select rstrim('   abc   abc   ')||'a' from dua;
select rstrim(lstrim('   abc   abc   ')) from dual
select concat(rstrim(lstrim()),'a') from dual;


-- replace(x,old ,new)：从x中查找old ，并替换为new

-- substr( x, start,length)：返回x字符串，从start开始，截取length个字符，缺省length，默认到结尾

-- 数学函数
-- abs（x)： 取绝对值
-- ceil(x) ： 向上取整
-- floor
-- mod(x,y)

-- 日期函数

-- sysdate :当前系统时间，无括号
select sysdate from dual;
select sysdate+10 from dual;

-- current_date 返回当前系统日期，无括号

-- add_months(d1,n1)返回在di基础上再加上n1个月以后的新日期
selct empno, ename, hiredate, add_months(hiredate,3) from emp;
selct empno, ename, hiredate, add_months(hiredate,-3) from emp;

-- last_day(d1) 返回日期 d1 所在鱼粉最后一天的日期
select last_day(hireday) ,hiredauy from emp;

-- months_between(d1,d2) 返回日期d1和日期d2之间的月数
select sysdate, hiredate, months_betweem( sysdate, hiredate)from emp;
select sysdate, hiredate, months_betweem(hiredate, sysdate )from emp;

-- next_day(d1,[,c1]) 返回日期d1在下周，星期几c1，的日期
select next_day(sysdate,'星期一')  as 入职时间 from dual;

-- 转换函数
-- to_char(x,c)将日期或数字x按照c的格式转化成char数据类型
select hiredate ,to_char(hiredate, 'mm'-'dd'-'yyyy') from emp;
select hiredae, to_char(hiredate, 'mm"月"dd"日" yyyy "年"') from emp;

-- to_date(x,c) 将字符串x按照c的格式转化成日期
select to_date('1900/1/1', 'yyyy//mm/dd')

-- co_number(x) 将字符串x转化成数字型
select to_number('11') +1 from dual;

多行函数

• 也叫聚合函数或组合函数
• 可以同时对多条记录进行操作，并返回一个结果
• AVG,SUM,MIN,MAX,COUNT
• 注 null 不能参与运算

select count(nvl(comm,0)) from emp ;

十三、 分页

• 列表内容太多，用分页进行显示
• 方案
  • 一次查询出数据库的所有数据，然后在每页汇总显示指定的记录 —— 假分页
  • 对数据库进行多次查询，每次获得本页的数据并显示

-- 对每一个结果集中的每一条记录的编号，从1开始
select ename , sal, deptno ,rownum from emp;
select ename, sal,deptno, rownum from emp where deptno = 30;


select ename, sal,deptno from emp where rownum <=5;-- 查询第一页数据，每页 5 条数据
select ename , sal, deptno from emp where rownum <= 5; -- 然后查询第二页，rownum 永远从 1 开始

select ename, sal, deptno, rownum from emp; -- 产生衣蛾伪列
select * from (select ename , sal, deptno,rownum as rw from emp) where rw >1;
select * from (select ename , sal, deptno,rownum as rw from emp) where rw >5 and rw <=10;


select ename, sal, deptno, r1 r2 from)
select ename, sal, deptno r1, rownum   r2 from(
select ename, sal, deptno, rownum r1 from emp order by sal desc
)) where r2 <=3;

十四、 去重

• rowid

用于定位数据库中一条记录的一个相对位移的地址值
通常情况下，该值在该行数据插入到数据库表是即被确定却唯一
是一个伪列，并不存在于表中
在oracle在读取表中数据行是，根据每一行数据的物理地址信息编码而成的一个伪列
根据一行数据的rowid 能找到一行数据的物理地址信息，从而快速定位到数据行
rowid是进行单记录定位速度最快的

• 重复记录的查找

create table copy as select * from dept;
select * from copy;
select deptno, dname, loc, rowid from copy order by deptno;
insert into copy select * from dept;
commit;

select min(rowid) from copy group bu deptno,dname,loc;
select * from copy where row not in (select min(rowid) from copy group by deptno,dname,loc);
delete from copy where rowid not in (
select min(rowid) from copy group bu deptno,dname,loc
);
commit;

十五、 表链接

• 一个表的行根据指定的条件与另个表的行连接起来形成新的过程

• 92 语法

select .. from t1,t2,t3,... where ...
简化表名
可能存在自连接的情况
原理： 按照from 后面表的出现顺序，前面的表作为内存的for循环，后出现的表作为外城的for循环
笛卡尔积

等值连接与非等值连接（!= 、>、 <、 <>、 between and）
select ename, sal, hiredate, grade from emp e, salgrade s 
where e.sal between losal and hisal;

自连接：使用比价运算符根据每个表共有的列匹配两个表中的行
select * from emp as e,emp as m where e.mgr = m.empno;

外连接：可以左向外连接、右向外连接或完整外部链接

select * from dept as d, (select count(*), deptno from emp group by deptno) as c where d.deptno = c.deptno(+);  -- ”+“ 代表非主表
select d.deptno, dname, loc, nul(cc,0) from dept d, 
(select count(*) cc, deptno from emp group by deptno) c where d.deptno = c.deptno
select * from emp e, emp m where e.mgr = m.empno(+);

• 99 语法

-- cross join 交叉连接，实现笛卡尔积
select * from dept cross join emp;

-- natural join ：自然连接，做等值连接，要求同名列或者主外键
select ename, empno, deptno, dname from emp natural join dept;
select ename,deptno, dname from emp natural join dept where deptno = 10;

-- join using ：等值连接，必须有同名列进行连接
select enmae,empno,deptno,dname from emp join dept using (deptnu);
select ename,deptno, dname from emp join using (dept no) where deptno = 10;

-- join on ：可做等值连接、非等值连接、自连接、解决一切连接，关系列必须要区分
select ename, empno, e.deptno, dname from emp e join dept d on e.deptno = d.deptno

select ename, sal, e.deptno, grade, dname from
emp e 
join dept d on e.deptno = d.deptno
 join salgrade on e.sal between losal and hisal
where e,deptno = 30;

-- outer join ：外连接，有主表和从表
left [outer] join on
left [outer] join using
right [outer] join on 
right [outer] join using

-- full join on | using

• 92语法与 99 语法的区别

内容	92	99
内连接	select ... from t1, t2 where t1.a = t2.b and t1.c = 1	select ... from t1 cross join t2 where... select ... from t1 natural join t2 where... select ... from t1 join t2 using (同名字段） where... select ... from t1 join t2 on 连接条件 where ...
外连接	select ... from t1, t2 where t1.a= t2.b(+)	select ... from t1 left/ right [outer] join t2 on/using 连接条件
全连接		两个表都是主表 select t1 full join t2 on 连接条件 where

十六、 集合操作

集合分类

union

并集、去重、对两个结果集合进行并集操作，不包括重复行，默认排序按照规则

union all

全集、不去重，对两个结果集合进行并集操作，包括重复行，不排序

interset

交集，找到重复，对两个结果集合进行交集操作，不包括重复行，默认规则排序

minus

差集，减去重复，对两个结果集合进行差集操作，不包括重复行，默认规则排序

要求

两个结果集合，要求字段个数和字段类型以一对应

select 'a','b' from dual;
select 'c','d' from dual;


select 'a','b' from dual
union
select 'c','d' from dual
select 'a','b' from dual; -- 求并集，ab 、有两个，去重

select 'a','b' from dual
union
select 'c','d' from dual
union all
select 'a' , 'b' from dual; -- 全集不去重

select 'a','b' from dual
union
select 'c','d' from dual
union all
select 'a' , 'b' from dual; -- ab


（select 'a','b' from dual
union
select 'c','d' from dual）
minus
（ select 'a','b' from dual 
union 
select 'e','f' from dual） -- cd

十七、 数据类型

• VARCHAR2(size）

  可变长度字符串，1：4000

• NVARCHAR2(size）

  可变长度字符串，根据所需国家字符集来定义最大长度 必须指定长度

• NUMBER

• LONG

• DATA

• RAW(size）

• LONG RAW

• CHAR(size）

• NCHAR(size）

• CLOB

• NCLOB

• BLOB

• BFILE

十八、 创建带有约束的表

create table t1(
userid number(5) primary key,
username varchar2(30) check(length(username between 4 and 20) not null,
userpwd varchar2(20) not null check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age> =18),
gender char(3) default('男') check (gender in ('男','女')),
email varchar2(30) unique,
regtime date default(sysdate)
);

create table t2 (
txtid number(5) primary key, -- 主键约束
title varchar2(32) not null check(length(title)>=4 and length(title) <= 30,
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) reference t1(userid) on delete set null
)

创建带有名字的约束

create table t1(
userid number(5) ,
username varchar2(30) contraint user_name not null ,
userpwd varchar2(20) constraint not null ,
age number(3) default(18) ,
gender char(3) default('男') ,
email varchar2(30) ,
regtime date default(sysdate)
constraint ke_uyser_id primary key(userid),
constraint ck_user_name check(length(username) between 4 and 20)
constraint ck_user_pwd check(length(userpwd) between 4 and 18),
constraint ck_user_age check(age> =18),
constraint ck_user_gender check (gender in ('男','女')),
constraint ck_user_email unique(email)
);  


create table t2 (
txtid number(5) ,
title varchar2(32) nn_txt_title not null,
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5),
constraint pt_txt_id primary key(txid),
 constraint ck_txt_title check (length(title)>=4 and length(title) <= 30
constraint fk_txt_user_id foreign key(userid) references tb_user(userid ) on delete set null
)

创建并追加约束

create table t1(
userid  number(5)，
username varchar2(30),
userpassword varhcar2(20),
age number(3),
gender char(2),
email varchar2(30),
regtime time default(sysdate)
);

alter table t1 add constraint pk_us_id primary key(userid);
alter table t1 add constraint ck_user_name check(length(username) between 4 and 28);
alter table t1 add constraint ck_user_pwd check(length(userpwd) between 4 and 18);
alter table t1 add constraint ck_user_age check(age>=18);
alter table t1 add constraint ck_user_gender check(gender in(‘男','女'));
alter table t1 add constraint uq_user_email unique(email);
alter table t1 modify(username constraint nn_user_name not null);
alter table t1 modify(userpawd constraint nn_user_pwd not null);
alter table t1 modify (age default(18)
alter table t1 modify

create table t2 (
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);

alter table t2 add constraint pk_txt_id primary key(txtid);
later table t2 add constraint ck_txt_id check(length(title) >=4 and length(title)<=30);
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid);-- 强制不删除
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid) on delete set null; -- 自动设为空
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid) on delete cascade; -- 联级删除

禁用和启用约束

1. 启动禁用：enable，disable，是否对新变更的数据启动验证约束
2. 验证，非验证：validate、novalidate，是否对表中以客观存在的数据进行约束验证

• enable validate：默认的约束组合状态，无法添加违反约束的数据行，数据表中也不能存在违反约束的数据行
• enable novalidate：无法添加违反约束的数据行，但对已存在的违反约束的数据行不做验证
• disable validate ：可以添加违反约束的数据行，但对已存在的违反约束的数据行不做验证
• disable novalidate：可以添加违反约束的数据行，对已经存在的违反约束的数据行也不做验证

删除约束

alter table t2 drop constraint uq_user_email cascade;

十九、DML

数据控制语句 操作数据库对象中包含的数据

• insert：向数据表中插入一条记录
• update：修改已经存在和表中的记录的内容
• delete：删除数据表中的一条或多条记录