前言:我这里还整理了一些oracle的sql语句练习的题目,大家看完笔记后可以适当做一下,自己掌握了多少ξ( ✿>◡❛)
下面是链接
oracle SQL语句基础练习题
一、常见的数据库:
mysql sqlServer oracle db2 sybase
今天我要分享的是 oracle数据库
二、Oracle的一个认知:
oracle是一家公司的名字(甲骨文公司),世界上屈指可数的,是国际上非常大的巨型IT公司,是全球最大的企业级软件公司,总部位于美国加利福尼亚州的红木滩。1989年正式进入中国市场。2013年,甲骨文已超越IBM,成为继Microsoft后成为全球第二大软件公司,最有名的产品是DB,database,RDBMS,关系型数据库系统。世界第一大数据库提供商,上海研发中心,五角场,离复旦大学近。主营oracle数据库软件,全世界第二大ERP提供商,大型企业级内部管理系统,包括人力资源管理等等。Oracle是Oracle公司第一个也是最成功的一个产品,发展经历过一个漫长的过程,到1997年6月,Oracle第八版发布。Oracle支持面向对象的开发以及新的多媒体应用,这个版本也成为支持Internet、网络计算等奠基定了基础。同时这一版本开始具有同时处理大量用户和海量数据的特性。1998年9月,oracle公司正式发布oracle 8i。"i"代表Internet,这一版本中添加了大量为支持Internet而设计的特性,这一版本为数据库用户提供了全方位的Java支持,Oracle 8i成为第一个完全整个了本地Java运行时环境的数据库。然后有9i,10g,"g"代表”grid“,网格。这一版本最大的特性就是加入了网格计算功能。然后是11g,现在发展到12c,c代表cloud,也就是现在炒的非常火的云概念。
三、Oracle简介
1. Oracle 服务
不推荐开机启动。设置为手动启动。
OracleService[Orcl] oracle服务实例
Oracleora…Listener oracle监听服务
2.表空间
数据库最大逻辑单位。
创建表空间:
create tablespace 表空间名
datafile ‘数据库文件路径’ [size 文件大小 K|M] [autoextend on|off],
‘数据库文件路径’ [size 文件大小 K|M] [autoextend on|off],
…
示例:
create tablespace tp_xz
datafile ‘D:\xz1.dbf’ size 10M,
‘D:\xz2.dbf’ size 30M autoextend on;
查看表空间数据文件:
select *
from dba_data_files
where tablespace_name=‘表空间名’;
删除表空间:
drop tablespace 表空间名; --只删除表空间
drop tablespace 表空间名
including contents and datafiles cascade constraints;
–删除表空间,同时包含内容,数据文件,对象,约束相关级联删除。
示例:
drop tablespace tp_xz
including contents and datafiles cascade constraints;
3.常用命令
show 用户名 – 显示当前用户
conn[ect] 用户名/密码 – 切换用户
password --更改当前用户密码
alter user 用户名 identified by 密码 --更改指定用户的密码
4.默认用户
sys : 超级管理员,拥有所有权限。默认密码:change_on_install
必须加上: as sysdba
如: conn sys/密码 as sysdba
System:系统管理员,除了create database 权限。默认密码:manager
scott :示例用户。 默认密码:tiger
5.用户的相关内容
创建用户语法:
create user 用户名
identified by 密码
[default tablespace 默认表空间名]
[temporary tablespace 临时表空间名]
注意:新创建用户是没有任何权限。
示例:
create user xz
identified by 123
default tablespace tp_xz
temporary tablespace temp;
对象: 表,视图,索引,同义词 ...
角色:一组权限的集合。简化权限的操作。
connect : 临时用户
resource : 正式用户
dba : 管理员
权限分类: 系统权限 和 对象权限
系统权限:对数据库操作的权限。 (连接,创建表,视图,索引...)
赋予系统权限语法:
grant 系统权限 to 用户名 [with admin option] ;
grant 系统权限 to public; --赋予所有用户权限
示例:
grant create session to xxz; --创建会话权限
grant create table to xxz;
grant create view to xxz;
grant connect,resource to xxz;
ss 撤销系统权限语法:
revoke 系统权限 from 用户名;
示例:
revoke create table from xxz;
对象权限:对数据库对象操作的权限。(增加,删除,修改,查询)
赋予权限语法:
grant 对象权限 on 对象 to 用户名;
示例:
grant select on scott.emp to xxz;
grant insert on scott.emp to xxz;
grant update on scott.emp to xxz;
grant update(ename) on scott.emp to xxz;
grant delete on scott.emp to xxz;
grant all on scott.emp to xxz;
撤销语法:
revoke 对象权限 on 对象 from 用户名;
删除用户
drop user 用户名; --删除用户
drop user 用户名 cascade; --删除用户,同时删除用户下所有的对象
锁定|解锁 用户
alter user 用户名 account lock|unlock;
四、Sql命令
1:sql命令类别
数据定义语言(DDL):create ,alter,drop,truncate
数据操作语言(DML):select,insert,update,delete
事务控制语言 (TCL) :commit,rollback,savepoint
数据控制语言 (DCL) :grant,revoke
2: 数据类型
数值类型:number 位数 38位
number : 代表38位的整数
number(3): 代表3位的整数 999
number(5,2):代表总位数5位,其中小数位2位。 999.99
字符类型:
char 固定长度字符 1-2000个字节
varchar2 可变长度字符 1-4000个字节
long 2GB (不推荐)--->CLOB
日期类型:
date: 日期+时间 ,精确到秒
timestamp: 日期+时间+时区 ,秒精确到小数点后6位
LOB:大对象数据,大小4GB
CLOB:字符数据 (小说,新闻稿...)
BLOB:二进制数据 (图片,音频,视频...)
Bfile: 二进制文件 (外部)
伪列:不存在于表中,但是可以进行查询。
rowId: 存储数据行的地址,通过rowid快速找到对应行。
不能增加,删除,修改。
rownum: 返回结果集的行号。始终从1开始。
作为条件不能与= > >=
3:创建表
create table 表名(
列名 数据类型 约束表达式,
列名 数据类型 约束表达式,
…
);
示例:学生表
学号 number
姓名 varchar2
性别 char 只能为男或女,默认男
出生日期 date
4: 修改表结构
添加列:
alter table 表名 add(列名 数据类型 约束类型);
示例:
alter table student add(codeId varchar2(18));
修改列:
alter table 表名 modify(列名 数据类型 约束类型);
删除列:
alter table 表名 drop column 列名;
5: 追加约束
约束名命名规范:
主键: PK_表名
唯一键:UK_表名_列名
外键: FK_表名_列名
check: CK_表名_列名
alter table 表名 add constraint 约束名 约束类型(列名);
示例:
追加主键
alter table student
add constraint PK_student primary key(stuNo);
追加外键:
alter table student
add constraint FK_表名_列名 foreign key(列名)
references 表(主键列) ;
删除约束:
alter table student drop constraint 约束名;
6: 删除表
drop table 表名 [purge]; --删除表,放入回收站
select * from recyclebin; --查看回收站
flashback table 表名 to before drop [rename to 新表名];将表从回收站还原
purge table 表名; --清空回收站指定的表
purge recyclebin; – 清空回收站
7:利用现有表创建表
create table 表名
as
select 查询语句;
8:插入表的数据来自于另一张表 (批量)
insert into 表名
select 查询语句;
9:事务
将多条sql作为一个整体去执行,要么一起成功,要么一起失败。
特性: 原子性,一致性,持久性,隔离性
默认DML语言,不自动提交或回滚。
以下情况事务会完成:
1:commit ,rollback
2:遇到DDL,DCL语言
3:关闭会话,出现异常终止
事务关键字:
commit 提交
rollback 回滚 rollback to 回滚点
savepoint 设置保存点,用于回滚
10:集合操作符
将两个查询结果组合成一个结果返回
union : 返回两张表所有的行,去掉重复的行。
union all:返回两张表所有的行,不去掉重复的行。
intersect :返回两张表相同的行
minus :返回第一张表的行,去掉第二张表重复行。
11: 连接操作符 ||
示例:
select ‘姓名:’||ename||’,职位:’||job
from myemp;
五、sql函数
1:sql 函数
单行函数:每一行返回一个结果
1:字符串函数
initcap():首字母转大写
lower():转小写
upper():转大写
length():获取字符的长度
instr():查找字符出现的位置。从1开始,没有找到返回0
substr(str,start,len):截取子字符串。start:起始位置为负数,代表从后面开始截取
replace():替换字符
translate(str,from,to):将字符串from中的字符替换成to中对应位置的字符。
2:数值函数
ceil():向上取整
floor():向下取整
round():四舍五入
trunc():截断
mod():求余
3:日期函数
sysdate :返回系统当前日期
add_months():对月份进行加减操作
months_between():两个日期之间月份的差值
last_day():本月的最后一天
next_day():返回下周对应的日期
extract(year|month|day from d1):提取日期中指定的数据
4:转换函数
to_char():
to_date():
to_number():
5:其他函数
nvl(expr1,expr2):如果expr1为null,返回expr2。否则expr1。
注意:expr1,expr2必须类型一致
NVL2(expr1,expr2, expr3):如果expr1不为null,返回expr2。
如果expr1为null,返回expr3。
nullif(expr1, expr2):expr1和expr2相等返回NULL,不相等返回expr1
decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值):等价于switch…case
多行函数:多行操作返回一个函数
6:聚组函数
max() min() avg() count() sum()
7:分析函数
按照指定排序规则,添加行号
row_number() over(排序规则): 序号连续,不重复
rank() over(排序规则): 可以重复,可能会间断
dense_rank() over(排序规则):连续,可以重复
六、数据库对象
注意:oracle沒有标识列,自动增长。
1:序列
作用:产生唯一,连续的序号。
创建序列语法:
create sequence 序列名
start with number --起始值,默认从1开始
increment by number --步长 ,默认为 1
maxvalue number |nomaxvalue --最大值
minvalue number |nominvalue --最小值
cycle|nocycle --是否循环
cache number|nocache --是否缓存,默认20
修改序列:
alter sequence 序列名
increment by number --步长 ,默认为 1
maxvalue number |nomaxvalue --最大值
minvalue number |nominvalue --最小值
cycle|nocycle --是否循环
cache number|nocache --是否缓存,默认20
访问序列:
序列名.nextval
序列名.currval
删除序列:
drop sequence 序列名;
2: 同义词
给当前对象取别名
分类:
私有同义词:只能在当前模式下使用,不可以与当前模式下的对象同名。
语法:
create synonym 同义词 for 用户名.对象名
示例:
create synonym sy_emp for scott.emp;
公有同义词:所有用户都可以访问
create public synonym 同义词 for 用户名.对象名
示例:
create public synonym sy_emp for scott.emp;
删除同义词:
drop [public] synonym 同义词;
注意:需要当前用户赋予创建|删除同义词权限。
3:视图
是一张虚拟表,不存储数据,存储select语句的结构。
作用:
1:保证基表数据的安全
2:存储复杂的业务功能sql语句
语法:
create [or replace] view 视图名
[(列别名)]
as
select语句
[with check option 列名]
[with read only];
查看:
select * from 视图名;
删除:
drop view 视图名;
4: 索引
优点:提高查询效率
缺点:占用空间,影响增加,删除,修改效率
分类:
B树索引:(标准索引)高基数的列(很多不同值的列,分布均匀)
create index 索引名 on 表(列);
唯一索引:列值唯一
create unique index 索引名 on 表(列);
组合索引:多列组合的索引 ,经常使用的放前面。
create index 索引名 on 表(列,列);
反向键索引:( B树索引的分支),适用于自动增长的列
create index 索引名 on 表(列) reverse;
位图索引:低基数的列(列值有很重复的值)
create bitmap index 索引名 on 表(列);
函数索引:
create index 索引名 on 表(函数(列));
删除索引:
create index 索引名;
适用于添加索引情况:数据量大,经常使用的列
不适用于添加索引情况:数据量少,不使用的列
七、PL/SQL
1:PL/SQL 过程语言和结构化查询语言组合的编程语言。是SQL扩展.
PL/SQL块结构语言,将一组sql语言放入一个结构块中。
PL/SQL中可以放DML语言,事务,函数,控制语言…
不能直接放DDL语言,但是动态sql。
2:PL/SQL 语法 组成:声明部分,执行部分,异常处理部分
declare
–声明部分,变量,常量,游标,异常类型
begin
–执行部分
exception
–异常处理部分
end;
3:声明变量和常量
变量名 数据类型[(长度)] [:=值]; – name varchar2(10);
变量名 contant 数据类型[(长度)] :=值; --contant:常量
4:变量赋值方式
1:方式一
变量名:=值;
2: 方式二
select 列名列表 into 变量列表 from …
注意:
1:列名列表与变量列表 顺序,个数,数据类型一致
2:select into 只能返回一行数据,数据放到变量中。
返回多行或者O行都会报错。
5:数据类型
标量类型:数值,字符,日期 …
属性类型:
%type: 返回与表的列类型或变量类型一致。–emp.ename%type
%rowtype:行的记录类型。 --emp%rowtype
boolean:布尔类型 true,false,null
6: 异常处理
异常:在运行过程中可能出现的错误。导致运行的终止。
异常分类:预定义异常 和 用户定义异常
预定义异常:Oracle定义,隐式引发。
用户定义异常:用户自定义,显式引发 raise
1:预定义异常
no_data_found: 没有找到数据
to_many_rows : select…into 返回多行数据时
others : 没有明确捕获异常,补充异常完整
2:用户定义异常 raise
declare
异常类型 exception; --声明异常
begin
--执行部分
--显式引发用户定义异常
raise 异常类型;
...
exception
when 异常类型 then
--异常处理 错误号:-20000~-20999 错误信息:2048个字节
raise_application_error(错误号,错误信息);
end;
7:PL/SQL的控制结构
1:条件控制
if 条件表达式 then
–sql语句块
end if;
--------------------------------------------
if 条件表达式 then
–sql语句块
else
–sql语句块
end if;
-----------------------------------
if 条件表达式 then
–sql语句块
elsif 条件表达式 then
–sql语句块
elsif 条件表达式 then
–sql语句块
…
else
–sql语句块
end if;
-------------------------------
case
when 条件表达式 then --sql语句块
when 条件表达式 then --sql语句块
…
else
–sql语句块
end case;
-----------------------------
case 条件参数名
when 值1 then --sql语句块
when 值2 then --sql语句块
…
else
–sql语句块
end case;
2:循环控制
1.LOOP
LOOP
语句;
EXIT WHEN <条件>
END LOOP;
2.WHILE LOOP
WHILE <条件>
LOOP
语句;
END LOOP;
3.FOR reverse:反向降序
FOR <循环变量> IN [reverse] 下限..上限
LOOP
语句;
END LOOP;
8:动态sql
动态sql 在运行时才确定,运行之前不编译,不执行。执行DDL语言
语法
declare
–声明变量
begin
执行动态sql
execute immediate ‘动态sql’
[into 变量列表]
[using (参数列表)];
end;
八、子程序 存储过程和函数
1:子程序
已命名的PL/SQL,编译并存储到数据库中。
分类:
存储过程:执行操作。
函数:执行操作并返回值。
2:存储过程
创建:
create or replace procedure 存储过程名
[(参数列表)]
as|is
局部变量声明
begin
执行部分
exception
异常处理部分
end;
参数类型:(3种)
in : 输入参数(默认) (将值传给存储过程)
out: 输出参数 (存储过程返回的值)
in out:输入/输出参数
执行过程:
1:程序PL/SQL块中
begin
存储过程名(参数列表);
end;
2: 命令行 sql> set serveroutput on;
exec[ute] 存储过程名(参数列表);
赋予执行存储过程的权限:
grant execute on 存储过程 to 用户名;
删除存储过程:
drop procedure 存储过程名;
3:函数
创建函数:
create or replace function 函数名
[(参数列表)]
return 数据类型
as|is
局部变量声明
begin
执行部分
return 值;
exception
异常处理部分
end;
调用函数
方式一
select addFun(10,20) from dual;
方式二
declare
v_sum number;
begin
v_sum:=addFun(10,20);
dbms_output.put_line(‘和为:’||v_sum);
end;
4: 调试
1:赋予 dubug conect session 的权限
grant debug connect session to scott;
2:存储过程–>右键–>添加测试信息
3: 创建一个测试窗体,测试代码复制
4:启动测试(F9),进行单步调试
九、游标
1: 游标
将表中多行数据存储到游标中(内存).
游标中有一个指针,默认指针位于第一行数据的上方。
通过fetch提取游标中的一行数据,进行处理。(提取一行,处理一行)。
2:游标分类
隐式游标
显式游标
ref游标
3:隐式游标
在pl/sql执行DML语句时,会自动创建的游标。游标名sql
自动创建,自动打开,自动提取,自动关闭。
属性:
%found: 如果有受影响的行数,返回true。
%notfound:如果没有受影响的行数,返回true。
%rowcount: 返回受影响的行数。
%isopen :判断游标是否打开。隐式游标中始终为false。
访问属性: 游标名%属性名
4:显式游标 (处理查询的多行数据的结果)
需要显式声明,手动打开,手动提取数据,手动关闭
步骤:
1:声明显式游标
cursor 游标名 is select 语句;
2:打开游标
open 游标名;
3: 提取数据 (一次提取一行)
fetch 游标名 into 变量列表|行记录类型
4: 关闭游标
close 游标名;
带参数的显示游标:
1:声明显式游标
cursor 游标名(参数列表) is select 语句;
2:打开游标
open 游标名(实参列表);
游标修改数据:
1:声明显式游标
cursor 游标名(参数列表) is select 语句 for update;
2: where 条件
where current of 游标名;
循环简化游标:(不需要打开,提取,关闭)
for 游标一条记录的类型名 in 游标名
loop
end loop;
5: ref 游标(动态游标)
执行时确定的sql语句。
步骤:
1:声明游标的数据类型
type 数据类型名 is ref cursor [return type];
如果没有加return,说明是一个弱类型的引用游标,可以打开任意表。
如果加return,说明是一个强类型的引用游标,只能打开指定类型的表.
如: return emp%rowtype;–只能打开emp的表。
2:声明数据类型变量
游标变量名 数据类型名;
3: 打开游标时确定sql语句
open 游标变量名 for select 语句;
十、触发器
1:触发器
当前触发该事件时,自动调用的特殊的存储过程。
作用:复杂业务功能,日志记录操作。
触发事件:
DDL事件:create,drop,alter
DML事件:delete,update,insert
数据库事件: startup,startdown
执行DML触发操作
1:确定触发事件:
insert|update|delete ---触发事件
2:确定触发的时机
before|after|instead of
3:确定作用的表和视图
4:触发器类型
行级触发: for each row
语句触发:
5: 为了保存之前和之后的数据,提供了old和new记录类型的变量
insert: new
update: old new
delete: old
如果old和new放在when后面使用,直接使用
否则:需要用 :old 和 :new
6:DML触发器中不能写DDL语句
7:DML触发器中不能用事务语句commit|rollback
DML触发器语法:
create or replace trigger 触发器名
before|after|instead of
insert|update [of 列名]|delete
on 表名|视图名
[for each row] --行级触发
[when [条件] ]
begin
PL/SQL
end;
----删除触发器
drop trigger 触发器名;
----禁用触发器
alter trigger 触发器名 disable;
----启用触发器
alter trigger 触发器名 enable;
十一、程序包 和 备份,恢复
1: 程序包
将一组相关类型,存储过程,函数,游标,变量,常量,异常封装。
便于管理。
2:程序包组成:规范和主体
程序包规范:声明程序包包含公有的对象,变量,没有实现。
程序包主体:对包中声明的对象提供具体的实现。
3: 程序包语句
–程序包规范
create or replace package 程序包名
as
–声明变量|常量
变量名 [constant] 数据类型 :=值;
–声明过程
procedure 过程名 [(参数列表)];
–声明函数
function 函数名 [(参数列表)] return 返回类型;
–声明游标 ,必须加return 指定游标返回的记录类型
cursor 游标名[(参数列表)] return returnType;
end;
--程序包主体
create or replace package body 程序包名
as
begin
--过程主体
procedure 过程名 [(参数列表)]
as|is
begin
--过程执行部分
end;
--函数主体
function 函数名 [(参数列表)] return 返回类型
as|is
begin
--过程执行部分
return 值;
end;
--游标主体
cursor 游标名[(参数列表)] return returnType
is select语句;
end 程序包名;
4: 备份和恢复
导出: exp
exp help=yes; 查看参数
1:交互式
exp 提示导出
2:命令行参数导出
exp userid=scott/tiger tables=(emp,dept) rows=yes file=D:\emp.dmp
exp userid=scott/tiger tablespaces=(users) rows=yes file=D:\emp.dmp
exp userid=scott/tiger owner=(scott) rows=yes file=D:\emp.dmp
exp userid=scott/tiger full=yes rows=yes file=D:\emp.dmp
到这里介绍了~