Oracle数据库DDL,DML,视图,PLSQL编程

--创建一个表空间--beijing
create tablespace beijing
datafile 'c:\beijing.dbf'
size 100m
autoextend on
next 10m;
--删除表空间
drop tablespace beijing;/*
创建用户*/
create user dashao
identified by dashao
default tablespace beijing;/*
授权 grant 角色 | 权限 to 用户
*/
grant connect to dashao;--授予dba的角色
grant dba to dashao;/*
创建表
create table 表名(
列名 列的类型 [列的约束],
列名 列的类型 [列的约束]
);
列的类型:
varchar ,在Oracle中,目前是支持的, 但是不保证以后还支持 varchar2(长度) 可变字符长度 varchar2(10) hello 占5个字符
char(长度) 固定长度字符 char(10) hello 占10个字符,用空格填充
number(总长度,小数长度) 数字类型 --小数长度不能大于等于总长度 date 年月日时分秒 2017/4/13 9:43:49
timestamp 时间戳, 比date类型更加精确 13-APR-17 09.44.08.272000 AM +08:00 LONG/CLOB : 存放一本小说
BLOB : 存放电影 java 存进去, 再读取出来 使用子查询的方式创建表 create table 表名 as 查询语句; 注意: 只会复制表结构和表中的数据,不会复制列的约束
如果查询语句有结果, 就是复制 表结构和数据
如果查询语句没有结果, 就是复制 表结构
*/
select * from scott.emp;create table test1(
name1 varchar2(10),
name2 char(10),
age number(2,3)
);insert into test1(name1,name2) values('hello','hello');select * from test1 where name1 like 'hello'; --可以查询数据
select * from test1 where name2 like 'hello'; --查不出数据insert into test1(age) values(2);select current_date from dual;select current_timestamp from dual;-- create table 表名 as 查询语句; 复制表
create table emp as select * from scott.emp;select * from emp;--如果查询语句是没有任何的结果
select * from scott.emp where 1=2;
create table emp1 as select * from scott.emp where 1=2;
select * from emp1;/*
修改表:
添加列
修改列
删除列
修改列名
重命名表 sql分类:
DDL : 数据定义语言, 修改的结构 alter create drop truncate
DML :数据操纵语言 , 操作表中数据 insert update delete
DCL : 数据控制语言 , grant
DQL : select
*/create table stu(
stuid number,
sname varchar2(10)
);alter table stu add phone varchar2(11);alter table stu add (
mobile varchar2(11),
sex varchar2(2)
);
--修改列的类型
alter table stu modify sex varchar2(4);--修改列的名 sex --->gender
alter table stu rename column sex to gender;--删除列
alter table stu drop column gender;select * from stu;--修改表名
rename stu to Student;
select * from student;--删除表
drop table Student;/*
表的五大约束
列的约束:约束主要是用来约束表中的数据的规则
主键约束:primary key 不能为空,必须唯一
非空约束
唯一约束
检查约束 check(条件) 在mysql中是可以写的,但是mysql直接忽略了检查约束 外键约束:
主要是用来约束从表a总的记录,必须是存在于主表b中
*/
--男,女,人妖
create table student(
stuid number primary key,
sname varchar2(10) unique,
age varchar2(10) not null,
gender varchar2(4) check( gender in ('男','女','人妖'))
);
--主键约束违反
insert into student values(1,'张三','','男');
insert into student values(1,'李四','','男');
--唯一约束违反
insert into student values(1,'徐立','','男');
insert into student values(2,'徐立','','男');
--非空约束
insert into student values(1,'徐立',null,'男');
--检查约束
insert into student values(1,'徐立','','男');
insert into student values(1,'徐立','','妖');/*
商品分类,商品表*/
--商品分类表
create table category(
cid number primary key,
cname varchar2(20)
);--创建一个商品表
create table product(
pid number primary key,
pname varchar2(20),
cno number
);
insert into category values(1,'手机数码');insert into product values(10,'锤子',11);--添加外键约束
alter table Product add foreign key(cno) references Category(cid);delete from Product where pid=10;
--1.首先主表中必须存在11号, 先往主表中插入数据,再往从表中插入数据
insert into Category values(2,'电脑办公');
insert into Product values(11,'外星人',2);select * from Category;
select * from Product;--删除Category
drop table Category;--被外键关联,无法删除--强制删除(不建议),先删除外键关联的外键约束,在删除自己,
drop table Category cascade constraint;--级联删除
----添加外键约束,使用级联约束 ,在删除的时候,使用级联删除
alter table product add foreign key(cno) references category(cid) on delete cascade; insert into category values(2,'电脑办公');
insert into product values(11,'外星人',2);--级联删除 : 首先去从表中找有没有 关联数据, 如果在从表中找到关联数据,先删除从表中关联数据,然后再删除表中的数据
delete from category where cid = 2;select * from Category;
select * from Product;/*
插入数据:
insert into 表名 values(所有列的值都要对应写上)
insert into 表名(列1,列2) values(值1,值2); 使用子查询插入数据
insert into 表名 查询语句
*/select * from emp1;
select * from emp;
--将emp中10号部门的员工信息,插入到emp1中
insert into emp1 select * from emp where deptno=10 ;/*
更新数据
update 表名 set 列名 = 列的值 [where 条件]
*/update emp1 set ename='HUAAN' where ename = 'KING';select * from emp1;
/*
删除数据:
delete from 表名 [where 条件] delete和truncate 区别 delete: truncate:
DML DDL
逐条删除 先删除表再创建表
支持事务操作 不支持事务操作,
执行效率要高
*/
/*
事务: 就是一系列的操作,要么都成功,要么都失败
四大特性: 原子性,隔离性,持久性,一致性 如果不考虑隔离级别: 脏读,虚读,不可重复读
MYSQL隔离级别: READ UNCOMMITTED , READ COMMITTED, REPEATABLE READ, SERIALIAZABLE
ORACLE隔离级别: READ COMMITTED SERIALIZABLE READ ONLY
默认隔离级别: READ COMMITTED 提交 : commit
事务的保存点/回滚点: savepoint 保存点的名称
回滚: rollback
*/
create table louti(
lou number primary key
);insert into louti values(1);
insert into louti values(2);
insert into louti values(3);
insert into louti values(4);
insert into louti values(5);
savepoint dangban;
insert into louti values(5); --主键约束会发生异常
insert into louti values(6);
rollback to dangban
commit;declarebegin
insert into louti values(1);
insert into louti values(2);
insert into louti values(3);
insert into louti values(4);
insert into louti values(5);
savepoint dangban;
insert into louti values(5); --这行代码会发生异常
insert into louti values(6);
commit;
exception --捕获异常
when others then
rollback to dangban;
commit;
end;select * from louti;/*
视图: 是对查询结果的一个封装
视图里面所有的数据,都是来自于它查询的那张表,视图本身不存储任何数据
1.能够封装复杂的查询结果
2.屏蔽表中的细节
语法:
create [or replace] view 视图的名称 as 查询语句 [ with read only] 注意: 通常不要通过视图去修改,视图创建的时候,通常要加上with read only
*/select * from emp;--创建一个视图
create or replace view view_test1 as select ename,job,mgr from emp;select * from view_test1;--通过视图修改数据
update view_test1 set ename = 'SMITH2' where ename='SMITH'
select * from emp;--创建一个只读视图
create or replace view view_test2 as select ename,job,mgr from emp with read only;
update view_test2 set ename='SMITH3' where ename = 'SMITH2';/*
序列: 生成类似于 auto_increment 这种ID自动增长 1,2,3,4,5....
auto_increment 这个是mysql 语法:
create sequence 序列的名称
start with 从几开始
increment by 每次增长多少
maxvalue 最大值 | nomaxvalue
minvalue 最小值 | nominvalue
cycle | nocycle 是否循环 1,2,3,1,2,3
cache 缓存的数量3 | nocache 1,2,3,4,5,6 如何从序列获取值
currval : 当前值
nextval : 下一个值 注意: currval 需要在调用nextval之后才能使用 永不回头,往下取数据, 无论发生异常, 回滚
*/
--创建一个 1,3,5,7,9......30
create sequence seq_test1
start with 1
increment by 2
maxvalue 20
cycle
cache 10;select seq_test1.nextval from dual;
select seq_test1.currval from dual;/*
索引:相当于是一本书的目录,能够提高我们的查询效率
如果某一列,你经常用来作为查询条件,那么就有必要创建索引,数据量比较的情况 语法:
create index 索引的名称 on 表名(列) 注意:主键约束自带主键索引, 唯一约束自带唯一索引 索引原理: btree balance Tree 平衡二叉树 如果某列作为查询条件的时候,可以提高查询效率,但是修改的时候,会变慢 索引创建好之后,过了一段,DBA都会去做重构索引 SQL调优:
1.查看执行计划F5
2. 分析里面的cost 和 影响行数, 想办法降低
*//*
DDL表空间操作
创建表空间
创建用户
授权 创建表
子查询创建表
修改表 : 添加列,删除列,修改列,修改列名, 修改表名 约束:
主键约束,唯一约束,非空约束,检查约束,外键约束 外键约束:
强制删除
级联删除 DML表中数据:
插入数据
子查询插入数据
更新数据
删除数据: delete 和 truncate 事务操作:
savepoint 保存点
rollback to 保存点
ORACLE事务隔离级别 : READ COMMITTED 视图: 就像窗户一样, 封装查询结果 , 通常视图创建只读视图
序列: 主要是用来实现ID自增长
索引: 相当于是书的目录,能够提高查询效率, 原理 平衡二叉树, 每隔一段时间DBA都需要去重建索引
同义词: create synonym 名称 for 对象的名称 *//*
PLSQL编程 : procedure Language 过程语言 Oracle对SQL的一个扩展
让我们能够像在java中一样写 if else else if 条件, 还可以编写循环逻辑 for while declare
--声明变量
变量名 变量类型;
变量名 变量类型 := 初始值;
vsal emp.sal%type; --引用型的变量
vrow emp%rowtype; --声明记录型变量
begin
--业务逻辑
end; dbms_output.put_line()相当于java中 syso
*/
declare
i varchar2(10) := '张三';
begin
dbms_output.put_line(i);
end;--查询7369的工资,并且打印出来
declare
vsal emp.sal%type;
begin
--将查询出的结果赋值给vsal
select sal into vsal from emp where empno = 7369; dbms_output.put_line(vsal);
end;--查询7369的员工信息,并且打印出来
select * from emp where empno = 7369;declare
vrow emp%rowtype;
begin
select * into vrow from emp where empno = 7369; dbms_output.put_line('姓名:'||vrow.ename || '工资'|| vrow.sal);
end;/*
PL条件判断 if then elsif then else end if;
*/
--根据不同年纪,输出相关内容
declare
age number := &aaa;
begin
if age < 18 then
dbms_output.put_line('小屁孩');
elsif age>=18 and age <=24 then
dbms_output.put_line('年轻人');
elsif age>24 and age < 40 then
dbms_output.put_line('老司机');
else
dbms_output.put_line('老年人');
end if;
end;/*
循环操作
while 循环
while 条件 loop end loop; for循环
for 变量 in [reverse] 起始值..结束值 loop end loop; loop循环
loop
exit when 条件
end loop;*/
--输出1~10
declare
i number :=1;
begin
while i<=10 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;--输出1~10
declarebegin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;--输出1~10
declare
i number :=1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;/* *
***
*****
***
*
输出 m
x : [-m,m]
y : [-m,m] 输出所有满足条件的 : abs(y)+abs(x) <=m m取值
*/
--使用PLSQL输出菱形
declare
m number := 10;
begin
for x in -m..m loop
for y in -m..m loop
if abs(y) + abs(x) <= m then
dbms_output.put('*');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.new_line();
end loop;
end;--使用PLSQL输出三角形,只要是三个角
declare
m number := 10;
begin
for x in reverse -m..m loop
for y in -m..m loop
if abs(y) + abs(x) <= m and x>=0 then
dbms_output.put('*');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.new_line();
end loop;
end;