Oracle学习的第三天（使用DDL语句管理表_视图_序列_索引_PLSQL入门）

一、使用DDL语句管理表

1.Oracle的体系结构

数据库--> 数据库实例Orcl--> 表空间(创建用户，创建表)--> 数据文件

2.创建数据库表空间

--创建表空间
create tablespace datafile
--创建表空间文件的路径
datafile 'D:\oracle\io\datafile.dbf'  
--表空间文件的初始化大小
size 10M
--如果空间不够自动扩展
autoextend on
--自动扩展5M
next 5M
--删除表空间
drop tablespace datafile;

3.创建用户

--创建用户
create user jerry --用户名
identified by 123 --密码
default tablespace datafile; --用户默认存在的表空间
--给用户赋予权限
grant dba to jerry 

4.重新使用jerry 123 orcl nomal登录，然后查询select * from scott.emp;
5.字段常用的数据类型

char 固定长度字符串 最大长度2000 bytes    
varchar2 可变长度的字符串 最大长度4000 bytes  可做索引的最大长度749 
nchar 根据字符集而定的固定长度字符串 最大长度2000 bytes    
nvarchar2 根据字符集而定的可变长度字符串 最大长度4000 bytes   
number(总长度，小数长度)  数字类型
date  日期类型
Timestamp 时间戳  比date更加精确

select current_timestamp from dual

select current_date from dual

6.创建表

--使用普通的方式创建表
create table test1(
    name varchar(10),
    address char(10),
    age number(3)
)
-- 往test1表中插入数据
insert into test1 values('张三','长沙',25);
select * from test1
--使用子查询的方式创建表 create table 表名 as 查询语句
--注意：这种复制表结构的数据只会复制表的结构和数据，并不会复制表的约束
--如果只想复制表结构，那么as后面的查询语句可以是一个没有查询结果的语句
create table emp as select * from scott.emp;  --复制了表结构和数据，但不会复约束
create table emp1 as select * from scott.emp where 1=2; --由于这个查询语句查询不到结果所以只复制表结构
select * from emp1;

7.修改表:修改表包括添加字段，删除字段，修改字段的名称，修改字段的数据类型，修改表名称,删除表等等

create table stu(
    stuId number,
    sname varchar2(10)
)
--添加一列
alter table stu add address varchar2(30);
alter table stu add sex varchar2(10);
alter table stu add email varchar2(10);
--修改字段的数据类型
alter table stu modify sname varchar(15);
--修改列名
alter table stu rename column sex to gender;
--删除列名
alter table stu drop column email;
--修改表名
rename stu to student;

8.约束:用来约束表中数据的规则，约束主要有主键约束、非空约束、唯一约束、检查约束、外键约束。

单表约束

--删除表
drop table student;
--在创建表的同时添加约束
create table student(
    stuid number primary key, --主键约束
    sname varchar2(20) not null, --非空约束
    email varchar2(10) unique, --唯一约束
    gender varchar2(2) check(gender in ('男','女'))--检查约束
)
alter table student modify gender varchar2(4);
insert into student values(1,'张三','456@qq.com','男');
select * from student;

多表约束

--创建商品类别表
create table category(
    cid number primary key,
    cname varchar2(30) not null
)
--商品表
create table product(
    pid number primary key,
    pname varchar2(10) not null,
    cid number
)
--给商品表添加外键约束
alter table product add foreign key(cid) references category(cid);
--分别给两个表插入数据
insert into category values(1,'手机数码');
insert into product values(1,'redmiNote7',1)
--存在主外键关系的表，主键表是不能直接删除的，但是可以忽略外键约束强制删除
--强制删除的步骤是先删除约束，再删除表（不建议使用，这样会破坏表的完整性）
drop table category cascade constraint;
--可以在设置外键约束的时候设置级联删除
alter table product add foreign key(cid) references category(cid) on
delete cascade;
--级联删除：先去外键表查询是否存在关联数据，如果存在就先删除外键表中的关联数据，然后再删除主表中的数据
delete from category where cid=1

9.Delete和truncate删除数据的区别

语法：DELETE FROM 表名 WHERE 删除条件;
在删除语句中如果不指定删除条件的话会删除所有的数据
Truncate table 实现数据删除

比较truncate与delete实现数据删除？
delete删除的数据可以rollback,也可以闪回
delete删除可能会产生碎片，并且不释放空间
truncate是先摧毁表结构，再重构表结构

注意：插入、更新和删除会引起数据的变化，我们就必须考虑数据的完整性

二、视图

1.什么是视图：

视图是对查询结果的一个封装，能够封装复杂的查询结果，屏蔽表中的细节。视图里面所有的数据本身来源于查询的那张表。视图本身不存储任何的数据

2.视图与表之间的区别：

1、表需要占用磁盘空间，视图不需要
2、视图不能添加索引(所以查询速度略微慢点)
3、使用视图可以简化，复杂查询
4、视图的使用利于提高安全性

3.Oracle的视图大约可以分为以下几类

1、简单视图，基于单个表所建视图，不包含任何函数、表达式及分组数据的视图。
2、复杂视图，包含函数、表达式或者分组数据的视图。
3、连接视图，基于多表所建立的视图。
4、只读视图，只允许执行查询操作。
5、内联视图（Inline View），也叫内嵌视图、临时视图、行内视图、或内建视图，它是出现在FROM子句中的子查询，内联视图不属于数据库对象。

4.语法:create [or replace] view 视图的名称 as 查询语句 [with read only]

注意：通过不要通过视图去修改，视图创建的时候，通常要加上with read only

5.应用

--创建一个视图
create or replace view view_test1 as select ename,job,mgr from emp;
--直接从视图中去查就行
select * from view_test1
--注意：通常不需要通过视图去修改数据，视图创建的时候一般设置成只读状态（with read only）
create or replace view view_test2 as select ename,job,mgr from emp with read only;
--查询视图
select * from view_test2;
--不能修改视图
update view_test2 set mgr = 7901 where ename='SMITH';
--创建同义词
create synonym view_test3 for view_test2;
select * from view_test3;

三、序列

1.序列：类似设置auto_increment这种ID自动增长

-- 调用序列
select seq_test1.nextval from dual;  --序列最开始是指向0，必须调用nextval才能显示1
select seq_test1.currval from dual;  --如果一开始就调用当前序列是不行的，因为指针还没指向1，所以会报错

--序列最常用的写法
create sequence seq_test2;
select seq_test2.nextval from dual;
--如何使用序列
create table test_sequence(
    pid number primary key,
    pname varchar2(10) not null
)
insert into test_sequence values(seq_test2.nextval,'sunny')
select * from test_sequence;
--删除序列
drop table test_sequence;
--删除表
drop sequence seq_test2;

2.oracle序列为什么不是从1开始

当我们使用序列作为插入数据时，如果使用了“延迟段”技术，则跳过序列的第一个值
Oracle从 11.2.0.1版本开始，提供了一个“延迟段创建”特性
当我们创建了新的表(table)和序列(sequence)，
在插入(insert)语句时，序列会跳过第一个值(1)。
所以结果是插入的序列值从2(序列的第二个值) 开始，而不是1开始。

想要解决这个问题有两种方法： 
1.更改数据库的“延迟段创建”特性为false（需要有相应的权限）
	ALTER SYSTEM SET deferred_segment_creation=FALSE;
2.在创建表时让seqment立即执行
CREATE TABLE tbl_test(
    test_id NUMBER PRIMARY KEY, 
    test_name VARCHAR2(20)
)
SEGMENT CREATION IMMEDIATE;

四、索引

1.什么是索引

2.添加索引会影响增删改效率。
3.索引分为单列索引和复合索引。

单行索引的触发规则，条件必须是索引列中的原始值，单行函数。模糊查询都会影响索引的触发。
复合索引的触发规则，复合索引中的第一列为优先检索列，必须要包含优先检索列中的原始值

五、PLSQL入门

declare
  i varchar2(30):='张三';
begin
  dbms_output.put_line(i);
end;
--查询7639的工资并打印出来
declare 
  vsal emp.sal%type;
begin
  select sal into vsal from emp where empno=7369;
  dbms_output.put_line(vsal);
end;
--查询7369的员工信息并打印出来
declare
  vrow emp%rowtype;
begin
  select * into vrow from emp where empno=7369;
  dbms_output.put_line('姓名'||vrow.empno||'工资'||vrow.sal);
end;

//根据不同的年龄，输出不同的内容
declare
  age number:=12;
begin
  if age<=18 then
    dbms_output.put_line('小屁孩');
  elsif age>18 and age<=30 then
    dbms_output.put_line('年轻');
  elsif age>31 and age<=50 then
    dbms_output.put_line('老司机');
  else
    dbms_output.put_line('老年人');
  end if;
end;

--while循环
declare 
  i number:=1;
begin 
  while i<=100 loop
    dbms_output.put_line(i);
    i:=i+1;
  end loop;
end;

--for循环
declare
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

-loop循环
declare
  i number:=1;
begin
  loop
    exit when i>200; --跳出循环的条件
    dbms_output.put_line(i);
    i:=i+1;
  end loop;
end;

--反序输出
declare
begin
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;