一、DDL数据库定义语言
1、表空间
创建表空间:(表空间是一个逻辑单位)
create tablespace bkjname --表空间名称
datafile 'E:\bkjname.dbf' --文件存放的路径
size 100m --大小
autoextend on --自动扩展
next 10m --每次扩展的大小
删除表空间:
drop tablespace bkjname; --只删除逻辑关系,本地的表空间文件需要手动删除
2、用户
创建用户:
create user yh --用户名
identified by yh --密码
default tablespace bjkname; --表空间
给用户授权:
--grant 角色 | 权限 to yh
grant connect to yh; --赋予连接登录的权限
grant dba to yh; --最高权限dba
grant resource to yh; --通常给开发人员授权resource
3、表
创建表:
create table 表名(
列名 列的类型(列的约束),
列名 列的类型(列的约束)
);
列的类型:
varchar:在Oracle中目前支持,但不保证以后还支持
varchar2(长度):可变字符长度
char(长度):固定字符长度
number(总长度,小数长度):数字类型,注意小数长度要小于总长度
date:年月日时分秒
timestamp:时间戳,比date更加精准
long/clob:存放一本小说
blob:存放电影
给表添加字段:
alter table stu add (
phone varchar2(11),
gender varchar2(2)
);
修改列类型:
alter table stu modify gender varchar2(4)
修改列名:
alter table stu rename column phone to telephone;
删除列:
alter table stu drop column gender;
修改表名:
rename stu to student;
删除表:
drop table student;
4、约束
(1)主键约束:primary key,不能为空,必须唯一
(2)非空约束:not null,不能为空
(3)唯一约束:unique
(4)检查约束:check(条件)
-
在MySQL中可以写,但是MySQL直接忽略了检查约束
(5)外键约束:foreign key
-
主要用来约束从表A的数据,必须是存在于主表中
-
添加外键约束
-
alter table student add foreign key(sno) references people(pid);
-
首先主表必须存在11号pid,从表才能插入11号学生
-
删除主表(不推荐)
-
drop table people; --表中记录被关联无法删除 drop table people cascade constraint; --强制删除外键约束和表
-
-
-
级联删除(常用)
-
添加级联约束:
-
alter table student add foreign key(sno) references people(pid) on delete cascade;
-
-
删除:
delete from people where pid=2
-
首先在从表中找有没有关联的数据,若有则删除从表中sno=2的数据然后删除主表pid=2的数据
-
二、DML数据操纵语言
1、增删改
1.插入数据:
insert into 表名 values(所有列的值)
insert into 表名(列1,列2) values(值1,值2)
--使用子查询插入数据:
insert into 表名 查询语句
2.更新数据:
update 表名 set 列名=值 (where条件)
3.删除数据:
delete from 表名 (where条件)
delete和truncate 区别
delete:DML语言,逐条删除,支持事务操作
truncate:DDL语言,先删除表再创建表(效率高),不支持事务操作
2、事务
事务就是一系列的操作,要么都成功,要么都失败
四大特性:原子性、隔离性、持久性、一致性
MySQL隔离级别:read uncommitted(读未提交数据), read committed(读已提交数据), repeatable read(可重复读), serializable(串行化)
Oracle隔离级别:read committed, serializable, read only
如果不考虑隔离级别:脏读、虚读、不可重复读
保存点savepoint
当事务报错时可rollback to 事务点
3、视图
是对查询结果的一个封装
能够封装复杂的查询结果(视图里面的所有数据都来自查询的表,视图本身不存储任何数据)
屏蔽一些东西不给看
语法:
create [or replace] view 视图的名称 as 查询语句 [with read only]
通过视图修改数据:(一般视图都会加只读权限)
update 视图名称 set 字段=值 where(条件)
4、同义词
create synonym 同义词名称 for 视图名称
5、序列sequence
生成类似MySQL中的
auto_increment
ID自增长
-
语法:
create sequence 序列名称
start with 从几开始
increment by 每次增长多少
maxvalue 最大值/nomaxvalue
minvalue 最小值/nominvalue
cycle/nocycle 是否循环
cache 缓存数量3/nocache
-
从序列中取值:
-
currval:当前值
-
select seq1.currval from dual;
-
nextval:下一个值
-
select seq1.nextval from dual;
-
6、索引
相当于是一本书的目录
如果某一列经常被用作查询条件,且数据量大的情况下,有必要创建索引
索引的原理:
btree balance tree平衡二叉树
如果某列作为查询条件的时候可以提高查询效率,但是修改时会变慢
-
创建索引:
create index 索引名称 on 表名(列)
-
注解约束自带主键索引,唯一约束自带唯一索引
-
创建复合索引:
create index 索引名称 on 表名(列,列)
7、SQL优化
- F5查看执行计划
- SQL优化:主要是减少CPU调用次数和影响行数。所以SQL优化的方式是增加索引
三、PLSQL语言
1、procedure language 过程语言
基本语法:
declare
--声明变量
变量名 变量类型;
变量名 变量类型:=初始值;
vsal emp.sal%type; --引用型的变量
vrow emo%rowtype; --声明记录型变量
age number := &aaa --弹出输入框(类似scanner)
begin
--业务逻辑
select sal into vsal from emp where empno=7369 --将查询的值赋给vsal
dbms_output.put_line(sal); --输出
dbms_output.put_line(1); --输出1
dbms_output.put_line('*'); --输出*
end;
PL条件语句:
--放在begin下
if then
elsif then
else
end if;
PLSQL循环:
--for循环
for 变量 in (reverse倒序) 起始值..结束值 loop
...
end loop;
--while循环
while 条件 loop
...
end loop;
--loop循环
loop
exit when 条件
end loop;
2、游标cursor
是用来操作查询结果集,相当于是JDBC中的ResultSet
普通游标
cursor 游标名 is 查询结果集
开发步骤:
-
声明游标
-
打开游标:
open
-
从游标中取数据:
fetch 游标名 into 变量
-
游标名%found
:找到数据 -
游标名%notfound
:没找到数据
-
-
关闭游标:
close
-
示例:
-
输出员工表中所有的员工姓名和工资(不带参数游标)
-
declare
--游标:所有员工
cursor vrows is select * from emp;
--声明变量,记录一行数据
vrow emp%rowtype;
begin
--1.打开游标
open vrows;
--2.从游标中取数据
loop
fecth vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名'||vrow.ename||'工资'||vrow.sal);
end loop;
--3.关闭游标
close vrows;
end;
指定游标(带参数)
cursor 游标名[(游标名,游标类型)] is 查询结果集
- 示例:输出指定部门下的员工姓名和工资
-
游标:指定部门的所有员工
-
declare
--声明游标
cursor vrows(dno number) is select * from emp where deptno=dno;
--声明变量
vrow emp%rowtype;
begin
--1.打开游标,指定10号部门
open vrows(10);
--2.循环遍历取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名'||vrow.ename||'工资'||vrow.sal);
end loop;
--3.关闭游标
close vrows;
end;
系统引用游标
-
步骤:
-
声明游标:游标名 sys_refcusor
-
打开游标:open 游标名 for 结果集
-
从 游标中取数据
-
关闭游标
-
-
示例:
declare
--声明系统引用游标
vrows sys_refcusor;
--声明一个变量
vrow emp%rowtype;
begin
--1.打开游标
open vrows for select * from emp;
--2.取数据
loop
fecth vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名'||vrow.ename||'工资'||vrow.sal);
end loop;
--3.关闭游标
close vrows;
end;
- 扩展:使用for循环遍历游标
declare
--声明一个游标
cursor vrows is select * from emp;
begin
for vrow in vrows loop
dbms_output.put_line('姓名'||vrow.ename||'工资'||vrow.sal);
end loop;
end;
- for循环遍历游标:
-
不需要声明额外变量:游标里是什么类型,变量就是什么类型
-
不需要打开游标
-
不需要关闭游标
-
3、例外(意外/异常)
1.系统例外
程序运行的过程发生异常
declare
--声明变量
begin
--业务逻辑
exception
--异常处理
when 异常1 then
...
when 异常2 then
...
when others then
...处理其他异常
dbms_output.put_line('发生了其他异常'||sqlerrm);
end;
- 异常定义
-
zero_divide:除零异常
-
value_error:类型转换异常
-
too_many_rows:查询出多行数据,但是赋值给了rowtype记录一行数据变量
-
no_data_found:没有找到数据
-
2.自定义异常
-
示例:查询指定编号的员工,如果没有则抛出自定义异常,有则保存
-
错误示例:
declare --1.声明一个变量 %rowtype vrow emp%rowtype; --2.声明一个自定义的异常 no_emp exception; begin --查询员工信息,保存 select * into vrow from emp where empno=8888; --这句就抛出no_data_found异常 if vrow.sal is null then raise no_emp; --抛出自定义异常 end if; exception when no_emp then dbms_output.put_line('发生了自定义异常'); when others then dbms_output.put_line('发生了其他异常'||sqlerrm); end;
-
正确示例:(用游标来判断)
declare --1.声明游标 cursor vrows is select * from emp where empno=8888; --2.声明一个变量 %rowtype vrow emp%rowtype; --3.声明一个自定义异常 no_emp exception; begin --a.打开游标 open vrows; --b.取数据 fecth vrows into vrow; --c.判断游标是否有数据 if vrows%notfound then raise no_emp; end if; --d.关闭游标 close vrows; exception when no_emp then dbms_output.put_line('发生了自定义异常'); when others then dbms_output.put_line('发生了其他异常'||sqlerrm); end;
4、存储过程
实际上是封装在服务器上的一段PLSQL代码片段,已经编译好了的代码
客户调取存储过程,执行效率高
create [or replace] procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型)
is|as
--声明部分
begin
--业务逻辑
end;
Java调用:
call 存储过程名称(参数,参数);
5、存储函数
与存储过程的区别
- 函数存在的意义是给过程调用:存储过程里面调用存储函数
- 存储函数有返回值
create [or replace] function 存储函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return 参数类型
is|as
--声明部分
begin
--业务逻辑
end;
6、触发器trigger
当用户执行了Insert、update、delete等操作后,可以触发一系列其他的动作
常在动作执行之前或之后,触发业务处理逻辑(如插入数据前做一些校验)
create [or replace] trigger 触发器的名称
before|after
insert|update|delete
on 表名
[for each row]
declare
...
begin
...
end;