Oracle数据库语句大全.docx
- 文档编号:12693757
- 上传时间:2023-04-21
- 格式:DOCX
- 页数:28
- 大小:24.08KB
Oracle数据库语句大全.docx
《Oracle数据库语句大全.docx》由会员分享,可在线阅读,更多相关《Oracle数据库语句大全.docx(28页珍藏版)》请在冰豆网上搜索。
Oracle数据库语句大全
Oracle数据库语句大全
ORACLE支持五种类型的完整性约束
NOTNULL(非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.
CHECK(检查)--检查在约束中指定的条件是否得到了满足.
UNIQUE(唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.
PRIMARYKEY(主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束.
POREIGNKEY(外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.ORACLE支持五种类型的完整性约束
NOTNULL(非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.
CHECK(检查)--检查在约束中指定的条件是否得到了满足.
UNIQUE(唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.
PRIMARYKEY(主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束.
POREIGNKEY(外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.
1--设置每行显示多少字符setlinesize300;
2设置每页显示多少条记录setpagesize30;
3用户名的切换:
如connsystem/tiger
Connsys/change_on_installassysdba(注意超级用户在后面加assysdba)
4在超级用户下查找普通用户的表是查不到的必须这样查找如select*fromscott.emp(普通用户下的emp表)
5查看当前是那个用户身份登录:
showuser;
6查看有多少张表:
select*fromtab;(注意不同用户下的表是不同的)
7查看表的结构:
descemp(emp为表名)
8取出重复的列(DISTINCT):
如SELECTDISTINCTJOBEMP(去掉job的重复的值)
9字符串的链接操作用:
||
10查询有奖金的员工:
select*fromempwherecommisnotnull;
11查询没有奖金的员工信息:
select*fromempwherecommisnull;
12两个条件以上就得用and如查询工资大雨1500和有奖金的员工select*fromempwheresal>1500andcommisnotnull;
13表示两个条件有一个满足就可就用:
or如查询工资大于1500或者没有奖金的员工信息
Select*fromempwheresal>1500orcommisnotnull;
14取反可以用not如查询员工工资不大于1500和有奖金的员工信息如:
Select*fromempwherenot(sal>1500orcommisnotnull);
15在什么什么之间用between----and----如查询工资在1500和3000之间的员工信息:
Select*fromempwheresalbetween1500and3000;
16查询员工编号是2323,4555,2222的员工具体信息:
如
Select*fromempwhereempnoin(2323,4555,2222);
17.l模糊查询like一般结合"%"和"_"使用其中%:
表示可以匹配任意长度的内容,"_"表示匹配一个长度放入内容如:
查询员工姓名中第二哥字母是M的员工信息:
Select*fromempwhereenameLIKE'_M%';
又如姓名中包含M的员工Select*fromempwhereenameLIKE'%M%';
18oracle中不等于有两种表示方式"<>"和"!
="
19排序用orderby{ascdesc}其中asc是升序排列如果不写就默认按升序排列desc是按降序排列排序语句放在sal语句的最后如:
按员工工资进行排序
Select*fromemporderbysalasc(升序)
Selecct*fromemporderbysaldesc(降序)
Select*fromempwheredeptno='10'orderbysaldesc,hiredateasc;(查询部门10的员工工资的升序排列如果工资相等就按员工的入职时间排序)
20.groupby用于对查询的结果进行分组统计:
显示每个部门的平均工资和最高工资如:
Selectavg(sal),max(sal)fromempgroupbydeptno;
Having子句用于限制分组显示结果:
显示平均工资大于2000的的部门号和他的平均工资?
如:
selectavg(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000;
2.单行函数:
1小写变大写:
upper如select*fromempwhereename=upper('smith');
讲一个字符串变为小写字母表示如:
selectlower('HELLOWORLD')FROMDUAL;
将单词的首字母变大写用INITCAP如:
SELECTINITCAP('HELLOWORLD')FROMDUAL;
2.字符串的操作
Substr()截取字符串length()字符串的长度replace()替换字符串
3数值函数
四舍五入:
round();
截断小数位:
trunc();
一.入门部分
1.创建表空间
createtablespaceschooltbsdatafile‘D:
\oracle\datasource\schooltbs.dbf’size10Mautoextendon;
2.删除表空间
droptablespaceschooltbs[includingcontentsanddatafiles];
3.查询表空间基本信息
select*||tablespace_namefromDBA_TABLESPACES;
4.创建用户
createuserlihua
identifiedbylihua
defaulttablespaceschooltbs
temporarytablespacetemp;
5.更改用户
alteruserlihua
identifiedby123
defaulttablespaceusers;
6.锁定用户
alteruserlihuaaccountlock|unlock;
7.删除用户
dropuserlihuacascade;--删除用户模式
8.oracle数据库中的角色
connect,dba,select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_database,imp_full_database,resource
9.授予连接服务器的角色
grantconnecttolihua;
10.授予使用表空间的角色
grantresourcetolihuawithgrantoption;--该用户也有授权的权限
11.授予操作表的权限
grantselect,insertonuser_tbltoscott;--当前用户
grantdelete,updateonlihua.user_tbltoscott;--系统管理员
12.修改表的结构(alter)
Altertable表名add(列的名称,列的类型);
二.SQL查询和SQL函数
1.SQl支持的命令:
数据定义语言(DDL):
create,alter,drop
数据操纵语言(DML):
insert,delete,update,select
数据控制语言(DCL):
grant,revoke
事务控制语言(TCL):
commit,savepoint,rollback
2.Oracle数据类型
字符,数值,日期,RAW,LOB
字符型
char:
1-2000字节的定长字符
varchar2:
1-4000字节的变长字符
long:
2GB的变长字符
注意:
一个表中最多可有一列为long型
Long列不能定义唯一约束或主键约束
long列上不能创建索引
过程或存储过程不能接受long类型的参数。
数值型
number:
最高精度38位
日期时间型
date:
精确到ss
timestamp:
秒值精确到小数点后6位
函数
sysdate,systimestamp返回系统当前日期,时间和时区。
更改时间的显示
altersessionsetnls_date_language=’american’;
altersessionsetnls_date_format=’yyyy-mm-dd’;
Oracle中的伪列
像一个表列,但没有存储在表中
伪列可以查询,但不能插入、更新和修改它们的值
常用的伪列:
rowid和rownum
rowid:
表中行的存储地址,可唯一标示数据库中的某一行,可以使用该列快速定位表中的行。
rownum:
查询返回结果集中的行的序号,可以使用它来限制查询返回的行数。
3.数据定义语言
用于操作表的命令
createtable
altertable
truncatetable
droptable
修改表的命令
altertablestu_tablerenametostu_tbl;--修改表名
altertablestu_tblrenamecolumnstu_sextosex;--修改列名
altertablestu_tbladd(stu_agenumber);--添加新列
altertablestu_tbldrop(sex);--删除列
altertablestu_tblmodify(stu_sexvarchar2
(2));--更改列的数据类型
altertablestu_tbladdconstraintpk_stu_tblprimarykey(id);--添加约束
4.数据操纵语言
select,update,delete,insert
利用现有的表创建表
createtablestu_tbl_logasselectid,stu_name,stu_agefromstu_tbl;--
选择无重复的行
selectdistinctstu_namefromstu_tbl;--
插入来自其他表中的记录
insertintostu_tbl_logselectid,stu_name,stu_agefromstu_tbl;
5.数据控制语言
grant,revoke
6.事务控制语言
commit,savepoint,rollback
7.SQL操作符
算术操作符:
+-*/
比较操作符:
=,!
=,<>,>,<,>=,<=,between-and,in,like,isnull等
逻辑操作符:
and,or,not
集合操作符:
union,unionall,intersect,minus
连接操作符:
||
示例中stu_tbl_log中的数据如下:
IDSTU_NAMESTU_AGE
----------------------------------------
1000李华20
1001accp20
1003nimda3
stu_tbl中的数据如下:
IDSTU_NAMESTSTU_AGE
------------------------------------------
1000李华男20
1001accp男20
1002admin男30
示例:
select(3+2)/2fromdual;--算术操作符,结果:
2.5
select*fromstu_tblwherestu_age>=20;--比较操作符
select*fromstu_tblwherestu_namelike'%a%';--比较操作符:
like
select*fromstu_tblwherestu_namelike'a___';--比较操作符:
like
select*fromstu_tblwherestu_agein(20,30);--比较操作符:
in
select*fromstu_tblwherestu_agebetween20and30;--比较操作符:
between
selectstu_namefromstu_tblunionall
selectstu_namefromstu_tbl_log;--集合操作符:
unionall,测试结果具体如下:
STU_NAME
-----------
李华
accp
admin
李华
accp
nimda
已选择6行。
selectstu_namefromstu_tblunion
selectstu_namefromstu_tbl_log;--集合操作符:
union,测试结果具体如下:
STU_NAME
---------
accp
admin
nimda
李华
selectstu_namefromstu_tblintersect
selectstu_namefromstu_tbl_log;--集合操作符:
intersect,测试结具体如下:
STU_NAME
----------
accp
李华
selectstu_namefromstu_tblminus
selectstu_namefromstu_tbl_log;--集合操作符:
minus,测试结果如下:
STU_NAME
----------
Admin
从中可以看出:
minus是获取第一张表独有的数据
intersect是获取两张表中都有的数据
union是整合两张表的数据,都有的只显示一次
unionall是纯粹的两张表数据整合
selectid,stu_name||''||stu_sexasname_sex,stu_age
fromstu_tbl;--连接操作符||,测试结果具体如下:
IDNAME_SEXSTU_AGE
-------------------------------------------
1000李华男20
1001accp男20
1002admin男30
8.SQL函数
单行函数:
从表中查询的每一行只返回一个值,可出现在select子句,where子句中
日期函数
数字函数
字符函数
转换函数:
ToChar(),ToDate(),ToNumber()
其他函数:
Nvl(exp1,exp2):
表达式一为null时,返回表达式二
Nvl2(exp1,exp2,exp3):
表达式一为null时返回表达式三,否则返回表达式二
Nullif(exp1,exp2):
两表达式相等时,返回null,否则返回表达式一
分组函数:
基于一组行来返回
Avg,Min,Max,Sum,Count
Groupby,having
分析函数
Row_number,rank,dense_rank
示例:
selectu.user_name,sum(oi.order_num*oi.order_price)astotal,row_number()over(orderbysum(oi.order_num*oi.order_price)desc)assortfromorder_item_tbl
oi,user_tblu,order_tblowhereoi.order_id=o.idando.user_id=u.idgroupbyu.user_name;
三.锁和数据库对象
1.锁:
数据库用来控制共享资源并发访问的机制。
锁的类型:
行级锁,表级锁
行级锁:
对正在被修改的行进行锁定。
行级锁也被称之为排他锁。
在使用下列语句时,Oracle会自动应用行级锁:
insert,update,delete,select……forupdate
select……forupdate允许用户一次锁定多条记录进行更新。
使用commitorrollback释放锁。
表级锁:
locktableuser_tblinmodemode;
表级锁类型:
行共享rowshare
行排他rowexclusive
共享share
共享行排他sharerowexclusive
排他exclusive
死锁:
两个或两个以上的事务相互等待对方释放资源,从而形成死锁
2.数据库对象
oracle数据库对象又称模式对象
数据库对象是逻辑结构的集合,最基本的数据库对象是表
数据库对象:
表,序列,视图,索引
序列
用于生成唯一,连续序号的对象。
创建语法:
createsequenceuser_id_seq
startwith1000
incrementby1
maxvalue2000
minvalue1000
nocycle
cache1000;--指定内存中预先分配的序号
访问序列:
selectuser_id_seq.currvalfromdual;
selectuser_id-seq.nextvalfromdual;
更改删除序列:
altersequenceuser_id_seqmaxvalue10000;--不能修改其startwith值
dropsequenceuser_id_seq;
在Hibernate中访问序列:
user_id_seq
视图
以经过定制的方式显示来自一个或多个表的数据
创建视图:
createorreplaceview
user_tbl_view(vid,vname,vage)
asselectid,user_name,agefromuser_tbl
[withcheckoption]|[withreadonly];
创建带有错误的视图:
createforceviewuser_tbl_force_viewas
select*fromuser_table;--此时user_table可以不存在
创建外联接视图:
createviewuser_stu_viewas
selectu.id,u.user_name,u.password,s.ddress
fromuser_tblu,stu_tbls
whereu.s_id(+)=s.id;--哪一方带有(+),哪一方就是次要的
删除视图:
dropuser_stu_view;
索引
用于提高SQL语句执行的性能
索引类型:
唯一索引,位图索引,组合索引,基于函数的索引,反向键索引
创建标准索引:
createindexuser_id_indexonuser_tbl(id)tablespaceschooltbs;
重建索引:
alterindexuser_id_indexrebuild;
删除索引:
dropindexuser_id_index;
创建唯一索引:
createuniqueindexuser_id_indexonuser_tbl(id);
创建组合索引:
createindexname_pass_indexonuser_tbl(user_name,password);
创建反向键索引:
createindexuser_id_indexonuser_tbl(id)reverse;
四.使用PL/SQL
可用于创建存储过程,触发器,程序包,给SQL语句的执行添加程序逻辑。
支持SQL,在PL/SQL中可以使用:
数据操纵命令
事务控制命令
游标控制
SQL函数和SQL运算符
支持面向对象编程(OOP)
可移植性
更佳的性能,PL/SQL经过编译执行
分为三个部分:
声明部分,可执行部分和异常处理部分
[declare
declarations]
begin
executablestatements
[exception
handlers]
end;
打开输出
setserverouton;
--根据输入编号获取某学员的成绩--if
declare
scoreuser_tbl.score%type;
begin
selectscoreintoscorefromuser_tblwhereid='&id';
ifscore>90then
dbms_output.put_line('优秀');
elsifscore>80then
dbms_output.put_line('良好');
elsifscore>60then
dbms_output.put_line('及格');
else
dbms_output.put_line('差');
endif;
end;
--根据学员姓名获取某学员的成绩--if
declare
scoreuser_tbl.score%type;
begin
selectscoreintoscorefromuser_tblwhereuser_name='&name';
ifscore>90then
dbms_output.put_line('优秀');
elsifscore>80then
dbms_output.put_line('良好');
elsifscore>60then
dbms_output.put_line('及格');
else
dbms_output.put_line('差');
endif;
end;
--case的使用
declare
gradeuser_tbl.grade%type;
begin
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 语句 大全