Oracle表间关系、SQL语句、基本函数
1.引言
可能你也发现了,这些文章讲的不是很细致,因为我尝试过多种数据库的开发,虽然使用的不是很深,但是对于这些数据库有一些了解,所以不会以数据库本身重要的知识点作为重点,而是把忘掉或者不了解的知识点作为核心。看完之后应该能够弥补在MySQL中学习的不足,因为同为市场占比比较高的数据库,二者在功能上必定存在较大的相似之处,可以弥补对MySQL了解上的不足。
2.数据库的收费问题
Oracle软件本身是免费的,所以任何人都可以从Oracle官方网站下载并安装Oracle的数据库软件,收费的是License,即软件授权,如果数据库用于商业用途,就需要购买相应Oracle产品的License。如果只是学习或教育用途那么是免费的。MySQL 的版本相对要多很多,分社区版和商业版。社区版是免费的,商业版要收费:
1、MySQL Community Server 社区版本,开源免费,但不提供官方技术支持(咱一般使用的就是这个)。
2、 MySQL Enterprise Edition 企业版本,需付费,可以试用30天。
3、 MySQL Cluster 集群版,开源免费。可将几个MySQL Server封装成一个Server。
4、 MySQL Cluster CGE 高级集群版,需付费。
3.数据库对SQL标准的兼容性
SQL其实是SQL 是一门 ANSI 的标准计算机语言,用来访问和操作数据库系统,SQL也像javascript一样有语言版本的迭代(这里主要是想说es6语法),最新的SQL为SQL99,比较有名的是SQL86、SQL92和SQL99。Oracle数据库的兼容性由于带有较为浓厚的商业色彩,兼容性相对于MySQL要好一些。Oracle支持SQL99标准,MySQL从5.7开始支持SQL99。SQL语言本身向下兼容,即高版本兼容低版本。另外SQL92版在MySQL中的支持效果相对差一些,比如虽然支持左关联、右关联,但是不支持全关联。
其次,需要明白一点,除了IOS推出的SQL标准外,某些数据库厂商可能会对SQL的功能进行一波私有扩展,但这会产生一些兼容性问题。
4.SQL语言的种类
整体上,SQL语言包含查询
、增加
、修改
、删除
,事务处理(commit
、rollback
、savepoint
等)、数据控制(或者称为权限管理,grant
、revoke
管理权限)。
5.Oracle中的HR用户
首先,明确一点HR用户不同于用于联系和测试的scott用户。HR用户默认是锁定状态
,并且密码单次有效。需要通过system级别用户
(sysdba或者system)来解除锁定,设置密码才能够使用。scott是演示用户,hr是示例用户,是在创建数据库的时候选中“示例数据库产生的”,实际就是模拟了一个人力资源部的数据库。本质上二者区别不是很大,都是为了学习使用。使用举例如下:
6.Oracle中基本的SQL语句的使用
接下主要是一些Oracle的HR靶场中使用操作数据库的方式,主要是为了复习数据库的操作方式,同时使自己更加数据Oracle数据库(被操作的数据均都是来自于HR靶场)。
(1)别名的使用,需要明确别名加不加as
都可以,别名默认直接会被转化成大写,如果在别名中需要区分大小写或者是存在空格等非法字符,可以使用引号包裹:
(2)连字符||
的使用,用于拼接两个字符串:
(3)distinct
去重的使用:
说明:需要注意的是distinct
需要紧跟在select
后面,并且distinct
影响的是后面的所有字段,也就是说如果是select distinct job_id,first_name
,表示的是(job_id,first_name)
合起来不一致:
(4)desc
显示某张表的表结构:
(5)小tips
,在书写SQL语句的时候,可以先写需要查询的表,这样SQL查询就能够提供相应的数据字段提示,方便我们更快速的书写SQL语句。
(6)在Oracle中,字符串和日期的值通常放在单引号
中(但是表名还是使用双引号,只要不冲突,一般二者都行),字符值区分大小写
,日期格式被固定为DD-MON-RR(日-月-年,并且年份采用两位计数的)
,中文版的Oracle和英文版的Oracle在日期的表示上有区别,中文版月份为:“1月
”,英文本对应的就是英文的拼写:“Jan
”,举例如下(查询08年的雇员信息):
(7)developer修改字体的方式:
(8)Oracle不等于的表达方式有三种:!=
、<>
和^=
(特别注意最后一种),例如查询工资不为2200的职员部分信息:
(9)between ... and...
表示介于两者之间,in
表示的是在什么范围之内,like
表示模糊查询,需要注意like中的%
(匹配的时候需要使用单引号定义规则
,表示任意0个或多个字符,前面使用的'%08'
匹配年份是没有问题的,因为多出字符没有使用%
也算为不匹配和_
(匹配单个字符):
(10)ESCAPE
指定转义符号,因为前面提到了like
语法中的匹配规则有%
和_
,但是可能有些需要被匹配的字符里面本来就是要匹配这两个符号,也就是需要转义。ESCAPSE就能够实现这个目的,指定like模板中使用到的转义符号,eg:like 'SA\_%'ESCAPE'\'
(这里表示的是指定\
为转义符,ESCAPE
和like模板
之间的空格可加可不加):
(11)为空判断is NULL
或者is NOT NULL
:
(12)使用NOT
逆转查询:
(13)优先级问题:算数运算>连字操作>比较操作>NULL判断和like in>between判断>NOT逻辑>and逻辑>or逻辑
,感觉容易模糊或者必要时可以使用()
来调整优先级。
(14)order by
排序:
7.Oracle中基本函数的使用
下面重头戏来了,主要介绍Oracle中的函数的使用,能够较大弥补自己在Mysql的学习中的不足(可能你像我一样,只知道嵌套查询,不知道MySQL中还有很多函数呢):
(1)大小写函数:
函数 | 结果 | 描述 |
---|---|---|
lower(‘hello WORLD’) | hello world | 全部转成小写 |
upper(‘hello WORLD’) | HELLO WORLD | 全部转成大写 |
initcap(‘hello WORLD’) | Hello World | 首字母大写 |
使用如下:
(2)字符串相关函数:
函数 | 结果 | 描述 |
---|---|---|
concat(‘hello’,‘world’) | hello world | 字符串拼接 |
substr(‘helloworld’,1,5) | hello | 字符串提取(下标(从1开始),长度) |
length(‘hello world’) | 11 | 返回字符串的长度 |
instr(‘helloworld’,‘w’) | 6 | 返回字符下标(从1开始) |
lpad(salary(假设这个值2400),10,‘*’) | ******2400 | 指定字符左补偿函数 |
rpad(salary(假设这个值2400),10,‘*’) | 2400****** | 指定字符右补偿函数 |
trim(‘H’ from ‘Hello world’) | ello world | 剔除首尾指定字符函数 |
举个例子:
(3)数字相关函数:
函数 | 结果 | 描述 |
---|---|---|
round(45.962,2) | 45.96 | 指定小数位四舍五入 |
trunc(45.962) | 45 | 截断小数部分,等价于[x] |
mod(1600,300) | 100 | 取模函数 |
(4)日期相关函数:
运算 | 结果 | 描述 |
---|---|---|
sysdate | 日期 | 获取当前日期 |
date+number | 日期 | 加上天数到达另外一个日期 |
date-number | 日期 | 减去天数到另外一个日期 |
date-date | 天数 | 获取两个日期的时间差 |
date-number/24 | 日期 | 减去小时得到日期 |
函数 | 结果 | 描述 |
---|---|---|
months_between(date1,date2) | 数字 | 获取两个日期之间的月份差 |
add_months | 日期 | 加上月份到达另外一个日期 |
next_day | 日期 | 指定日期的下一天日期 |
last_day | 日期 | 指定日期的上一天日期 |
round | 日期 | 四舍五入,第二个参数(‘mm’,‘yy’(‘rr’),‘dd’) |
trunc | 日期 | 截断日期,参数和上面的round一样 |
eg:
获取员工的工作周数
(5)隐式数据类型转换:
起始数据类型 | 隐式转换之后的类型 |
---|---|
varchar2 or char | number |
varchar2 or char | date |
number | varchar2 |
date | varchar2 |
需要明确的是,隐式转换有一定的缺陷:
a.隐式转换会导致索引失效(索引其实就是数据库自己包含的提高速度的对象),索引失效之后需要进行全表扫描,当数据量比较大的时候势必导致性能问题;
b.隐式转换不便于阅读,DBA很难明白到底发生了怎样的类型转换,特别是出错的时候,不容易轻易找出错误原因。
(6)显式类型转换:
a.日期到字符串的转换to_char(date,'format')
,format
参数如下:
format参数 | 结果 |
---|---|
YYYY | 四位全年 |
year | 年的英文拼写 |
mm | 月份的两位数表示 |
month | 月份的全名(中文就是汉字,eg:3月) |
mon | 月份的缩写(中文和month一样,eg:Jan) |
dy | 周中天的三个字母(中文和day一样,eg:Mon) |
day | 周中天的全名(中文即为:星期几) |
dd | 月的数字天数(两位) |
下面的不是常用 | 了解即可 |
scc或者cc | 返回世纪,带-服务器前缀B.C日期 |
SYYYY,SYEAR | 年,带-服务器前缀B.C日期 |
YYYY,YYY,YY,Y | 年的最后4,3,2,1个数字 |
Y,YYY | eg:2,022 |
AD,BC | 公元前缀,和S的功能差不多 |
RM | 罗马数字月数(即罗马文字) |
WW,W | 年,月中的周数 |
DDD,DD,D | 年,月,周中的天 |
说明:这里的服务器前缀估计是下面这样的,但是这个其实就是TIMESTAMP WITH TIME ZONE ,没用过,由于这里使用的oracle其实是桌面类不是服务器,不能体现这个功能。 |
b.数字到字符串的转换to_char(date,'format')
(一般是不需要的,但是有些时候我们需要转换并格式化),format
参数如下:
format参数 | 结果 |
---|---|
9 | 表示一个数,比如对178.88,使用模板999.9格式化就是保留一位小数,注意:模板表示的数据不能小于原始的数据 |
0 | 强制显示一个0 |
$ | 放置一个$符号 |
L | 使用浮动本地货币符号 |
. | 打印一个小数点 |
, | 打印一个千分指示位 |
fm | 去除前面多余的空格 |
举例如下:
c.字符串到数字的转换to_number(str,'format')
,format
参数和上面char转化数字一样:
format参数 | 结果 |
---|---|
9 | 表示一个数,比如对178.88,使用模板999.9格式化就是保留一位小数,注意:模板表示的数据不能小于原始的数据 |
$ | 放置一个$符号 |
L | 使用浮动本地货币符号 |
. | 打印一个小数点 |
注意:number由于类型特点是不支持, 、fm 的,因为这些属于字符串范畴。同样数据还是需要被转化数据小于模板表示的数据,并且由于转数字,这里模板的小数位>= 字符串数字。其次,对于货币的转换,$ 是不能直接转化成¥ 的。字符串中的货币符号需要与本地符号一致才能够使用L 格式化。 | |
d.字符串到日期的转换to_number(str,'format') ,format 参数和上面char |
转化日期一样:
(7)其他通用函数:
函数 | 描述 |
---|---|
NVL | NVL(exp1,exp2),即:exp1==NULL ? return exp2 |
NVL2 | NVL2(exp1,exp2,exp3),即:exp1!= NULL ? exp2:exp3 |
NULLIF | NULLIF(exp1,exp2),即:exp1== exp2 ? NULL:exp1 |
COALESCE | COALESCE(exp1,exp2,…,expm),返回表达式中的第一个非空表达式 |