YS_20190813_Oracle_02

约束

主键约束

主键约束primary key (mysql 通过Primary key实现主键约束)，但是Oracle、是通过序列实现主键约束

CREATE TABLE person(
Pid      VARCHAR(18)  PRIMARY  KEY,--主键约束
Name    VARCHAR(200) UNIQUE NOT NULL,--非空约束，唯一约束
Age      NUMBER(3),
Birthday  DATE ,
Sex     VARCHAR(2)   DEFAULT  '男');--默认

SELECT * FROM person;

insert into person values ('001','kk',15,'男');

外键约束

非空约束

not null

唯一约束

unique

检查约束

check ：使用检查约束来判断一个列中插入的内容是否合法

CREATE TABLE person_a(
Age     NUMBER(3)   CHECK( age BETWEEN 0  AND   150),
SEX     VARCHAR(2)   DEFAULT  ‘男’,  -- check('男');

主键的特性:

1. 主键具有唯一性 （主键 不可以重复）
2. 非空性

校验数据 check(支持运算符)

修改约束 （了解）

alter table 表名称 约束名称 1，约束名称2…

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型（约束字段）;

ALTER  TABLE  person  ADD  CONSTRAINT  person_pid_PK   PRIMARY KEY(pid);
ALTER  TABLE  person  ADD  CONSTRAINT  person_name_UK  UNIQUE(pid);
ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND  150);
ALTER  TABLE  person  ADD  CONSTRAINT person_sex_CK CHECK(sex IN (‘男’,’女’,’中’));

ROWNUM

表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现

select rownum, empno,ename,hiredate,sal from emp;--rownum作为列

select * from -- 选择表的最后四个
(select rownum rn,empno,ename,hiredate,sal from emp where rownum<=14) 
where rn>10;

select * from -- 选择表的中间五个
(select rownum rn,empno,ename,hiredate,sal from emp where rownum<=10) 
where rn>5;

序列*

nextval 表示的是将序列一行一行的读取
currval 表示的是返回当前序列的值
start with 1 — 表示的是序列的开始值.
increment by 1 – 每次序列递增几
maxvalue 1000 — 表示的是序列的最大内存空间
minvalue 1 – 表示的是序列的最小值
nocycle --表示的是不会产生序列缓存
cycle — 表示的是 序列发生缓存空间
cache 10; – 表示的是序列的缓存区域的大小

create sequence s1;
create table s2(
next  NUMBER,
curr  NUMBER
);
insert into s2(next,curr) values (s1.nextval,s1.currval);
select * from s2;
drop sequence s1;

create sequence s1 increment by 2;
drop table s2;
create table s2(
next NUMBER,
curr NUMBER
)
insert into s2(curr,next) values(s1.nextval,s1.currval);
select * from s2;


create sequence s3;
create table test1(
next NUMBER,
curr NUMBER
);
insert into test1(next,curr) values(s3.currval,s3.nextval); 
select * from test1;
drop sequence s3;
create sequence s3 start with 5 increment by 2; --从5开始，间隔两个
insert into test1(curr,next) values (s3.nextval,s3.currval);
select * from test1;

注意： 一旦利用nextval ，不能再使用currval

视图

一个视图实际上就是封装了一条复杂的查询语句
CREATE OR REPLACE 视图名称 AS 子查询

create or replace view eview
as
select * from scott.emp;

select * from eview;
insert into eview(empno,ename,job,mgr,hiredate)

在创建视图中使用此参数(不能更新视图的创建条件)
CREATE OR REPLACE VIEW empv20 AS
SELECT * FROM emp WHERE deptno=20 WITH CHECK OPTION;

修改视图中7369的雇员编号；
UPDATE empv20 SET ename=’ 史密斯’ WHERE empno=7369;

创建只读的视图：
CREATE OR REPLACE VIEW empv20 AS
SELECT * FROM emp WHERE deptno=20 WITH READ ONLY;

什么情况下利用视图？

1. 保护原始数据的完整性!
2. 提高sql的查询速度！
3. 试图可以虚拟化数据 —这个功能对于数据库的数据的备份与还原有很大的作用

emp表里面原本有oracle固定的14条数据 ，现在要求程序员手动添加一条。

select * from emp; ----用视图 ，不能直接插入数据

必须先创建视图！

create or replace view empview
as
select * from scott.emp; 
insert into empview(empno,ename,job,mgr,sal,comm) values(8001,'王老师','java',7788,10,1);
select * from scott.emp;
select * from emp;

索引*

索引的作用什么呢 ？
① ：可以在很大程度上提高sql语句执行效率 。 并且在sql的优化性能方面。做出很大的贡献.
② ：提高索引可以减少 数据在磁盘里面的内存空间，同时也可以减少磁盘碎片的产生 。
③ ：利用索引可以帮助 DBA更好的 维护 数据库的数据。确保数据的安全性 .

手动创建:用户可以在其它列上创建非唯一的索引，以加快sql查询速度

create table  student (sid number primary key,sname varchar(12),sage integer);
create index  stu2_index on student(sid);

select * from student;


create table  student2 (sid number,sname varchar(12),sage integer);

select * from student2
---索引组织表
create index student2_id on student2(sid);  --索引组织表

--重复索引
alter index student2_id rebuild;

--唯一索引
create unique index student3_id(index名称) on student(stu_id)(字段名称)

PLSQL语法

Plsql的作用 ：

1. Oracle的标准化的SQL语言对数据库进行各种操作，每次只能执行一条语句，同时效率底下—可以一次性执行多条sql
2. 结构化的查询语言对数据库的支持能力较弱----结构化数据（mysql Oracle）
3. 如果一些稍微复杂点的管理任务都要借助编程语言来实现话，对管理员来说是很大的负担
   解释：对于数据而言不能直接通过sql、语言查询，只能靠plsq语言支持sql。
4. Oracle公司在标准SQL语言的基础上发展了自己的PL/SQL语言，将变量、控制结构、过程和函数等结构化程序设计的要素引入了SQL语言。

基本语法：
declare—开始
if
for
begin
end----结束

---案例---------
declare ---定义一个变量
  age number:=3; -----赋值操作
  begin
      --打印输出
    DBMS_OUTPUT.put_line(age);
   end;
   
declare
age number:=3;
begin 
  DBMS_OUTPUT.put_line(age);
  end;

--重点关键字--*constant--
declare
PI constant number (9,3):=3.1415926535;

begin
  
DBMS_OUTPUT.put_line(PI);
end;


declare
d22 varchar(8);
begin 
  select dname into d22 from dept where deptno=30;--在dept表中选取deptno=30中dname列的值
  DBMS_OUTPUT.put_line(d22);
end;

--%type
declare 
wang scott.dept.dname%type;--%type类型转换机制，是底层自动转换。不需要程序员手动强制类型
begin
  select dname into wang from dept where deptno=40;
  dbms_output.put_line(wang);
end;
select * from dept;

-- %rowtype
declare
wang  dept%rowtype;   --  适合于海量数据的查询  匹配数据类型
begin  
 select * into wang from dept where deptno=20; 
 dbms_output.put_line(wang.dname); -- 需要变量名称指定输出那个值 
 dbms_output.put_line(wang.loc);  --通过变量.字段名称调用
 -- dbms_output.put_line(wang.sal)
end;
select * from dept;

 -- recored
declare
  type wang is record(   
  wang_id scott.dept.deptno%type,   -- wang_id  的类型 和   deptno对应一致
  wang_name scott.dept.dname%type,
  wang_loc scott.dept.loc%type
 );
mywang wang;
begin  
 select * into mywang from dept where deptno=30;
 dbms_output.put_line(mywang.wang_name);
 dbms_output.put_line(mywang.wang_id);
end;

mywang 直接调用wang，类型思想。