number 16 6 oracle,Oracle数据库学习一

最新推荐文章于 2021-07-20 16:32:21 发布

沈学良

最新推荐文章于 2021-07-20 16:32:21 发布

阅读量433

点赞数

文章标签： number 16 6 oracle

1.简介

数据存储有哪些方式？电子表格，纸质文件，数据库。

那么究竟什么是关系型数据库？

目前对数据库的分类主要是关系型和非关系型两种，关系型的主要代表有oracle，db2，mysql，sqlserver等，非关系型数据库也称作nosql，

主要有mongodb,hbase等等。关系型数据库主要是以二维表的方式存数数据的，这是关系数据库最显著的特征，

什么是二维表呢？

Excel表格就是二维表，由行和列两个维度所组成，想想excel表格，你就会知道数据库里面的表是什么样子的，道理是一模一样的。

而非关系型数据库的数据组织方式就五花八门了，有星型的，网状的等等，他们都统称为非关系型数据库，也叫nosql。

关系型数据库需要一组操作符，实际上就是需要一套命令，或者说是语言，也就是我们现在要学习的sql，它是我们和数据库进行沟通的工具。

2.Structured Query Language 结构化查询语言

(1)包含4种类型语句：

数据操纵语言DML-Data Manipulation Language SELECT ,INSERT, UPDATE, DELETE

数据定义语言DDL-Data Definition Language CREATE, ALTER, DROP

数据控制语言DCL-Data Control Language GRANT REVOKE

事物控制语句TCL-Transacation Contrl Language COMMIT , ROLLBACK

(2)如何书写sql

大小写不敏感,但单引和双引内的大小写是敏感的.

关键字不能缩写select不能写成sel

字符串用单引比如 ename='SEKER'

列的别名含特殊字符用双引

可跨行,但不要将关键字和单引的内容跨行.

跨行是为了可读性,一般我们都把select子句和from子句分行写.

不要在自定义参数部分使用sql的关键字。

3.SQL语法学习

(1)整个学习SQL过程是使用SCOTT用户的表来学习的默认scott是被锁定的解锁的方法

原始状态下，这个用户是被锁定的，我们需要解锁这个用户

SQL> conn / as sysdba

SQL> alter user scott account unlock identified by tiger;

通过sys解锁scott用户并将scott的密码设置成tiger

SQL> conn scott/tiger

在以后的学习过程中 scott的表经常被修改如果想将scott环境恢复默认使用系统自带脚本即可

SQL> show user

USER is "SYS"

SQL> @?/rdbms/admin/utlsampl.sql

如果是windows版本则是 @?\rdbms\admin\utlsampl.sql

@是加载OS中的SQL保存文件

?是$ORACLE_HOME的替代

执行完脚本会自动退出重新登录再对scott解锁即可

(2)一个完整的SQL命令叫语句(statement),每个关键字和后面跟着的选项叫子句(clause)

select 指定查询的列

from 指定查询的表

where 过滤的条件

order by 排序的列

DESC|ASC 排序方法

连接到scott用户

1.scott用户拥有哪些表？

SQL>select * from tab;

查询表中所有行所有列

SQL>select * from dept;

SQL>select * from emp;

如果屏幕显示的内容串行是因为默认的显示的行长度是80字节而选出的内容超出了80字节可以修正一下

SQL> set linesize 100

如果有多个列标题是一页内行数默认14行也可以修正

SQL> set pagesize 1000

这个修改只是内存中的可以将命令保存住到文件实现永久配置

SQL> !ls $ORACLE_HOME/sqlplus/admin/g*

/u01/oracle/product/10.2.0/sqlplus/admin/glogin.sql

2.描述表结构

desc TABLE_NAME

SCOTT@ora10g> desc emp

Name Null?Type

----------------------------- -------- --------------------

EMPNO NOT NULL NUMBER(4)

ENAMEVARCHAR2(10)

JOBVARCHAR2(9)

MGRNUMBER(4)

HIREDATEDATE

SALNUMBER(7,2)

COMMNUMBER(7,2)

DEPTNO NUMBER(2)

SCOTT@ora10g> desc dept

SQL@ora10g> desc SALGRADE

desc描述表的结构所谓表的结构:就是表有多少列列的名称和类型及约束(非空)

desc emp

emp表(员工表)的结构介绍:

empno 员工工号列为整数,最大长度为4位.

ename 员工名字列为变长字符型,最大长度10个字符

job 出任职位列为变长字符型,最大长度9个字符

mgr 所属领导工号列为整数,最大长度为4位

hiredate入职日期列为日期类型

sal 工资列为浮点数,最大长度为7位,其中包含2位小数

comm 奖金列为浮点数,最大长度为7为,其中包含2位小数

deptno 部门号列为整数,最大长度为两位

desc dept

dept表(部门表)的结构介绍

deptno 部门号列为整数,最大长度为两位

dname 部门名字列为变长字符型,最大长度为14个字符

loc 部门所在地理位置列为变长字符型,最大长度为13个字符

DESC SALGRADE

SALGRADE表(薪水等级表)的结构介绍三列都是数值型

GRADE 薪水等级

LOSAL 所在等级中薪水底线

HISAL所在等级中薪水上限

3.查询表中指定的列

SQL>select ename,sal From emp;

select ename,sal from emp;

错误语法: select *,sal from emp; 星号不可以与单列同时存在

4.在sql中使用算术表达式

select ename,sal*12 from emp;

select ename,(500+sal)*12 from emp;

算术运算符优先级:

先乘除后加减,同优先级自左至右

小括号提高优先级,多重括号则自内而外

5.在查询中为列命名别名

select ename,sal*12 as annual_salary from emp;

select ename,sal*12 annual_salary from emp;

6.在别名中使用特殊字符要用双引号

select ename,sal*12 "annual salary" from emp;

7.表别名对emp表取了个简单别名e 这样就可以在引用表名时简化输入

select ename,sal from emp e;

同时也解决了星号和列同时出现的语法错误别名的本质就是将非法的内容合法化

select e.*,sal from emp e;

8.连接操作符

select ename,job from emp;

select ename||' is a '||job from emp;

SQL> select ename||q'['s sal is]'||sal from emp;

9.去重复值

select deptno from emp;

select distinct deptno from emp;

多列去重

select distinct deptno,job from emp;

10.日期的显示格式

select hiredate from emp;

默认的日期格式是 DD-MON-RR

修改系统参数

alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

需要sysdba权限，静态参数，需要重启数据库生效

修改会话参数

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

11. 虚表

虚表 oracle自动创建的一张表可以理解为是一张假想表目的是为了使select语义完成

借助虚表我们可以完成很多事

查看当前用户

select user from dual;

查看当前时间(OS时间)

select sysdate from dual;

计算

select 9+8 from dual;

获取随机数

select dbms_random.random from dual;

等等...以后还有很多查询需要借助虚表完成.

12.过滤条件

select empno,ename,sal from emp where sal >= 1500;

WHERE是关键字后面跟限制条件

条件由列名,字符串,算术表达式,常量和比较运算符组成;

比较运算符:

> >= < <= = <> !=

逻辑运算符:

and or not

oracle提供的比较运算符

between and in like is null

数值比较直接写数值

SQL> select ename,sal from emp where sal >= 800 and sal <= 1100;

SQL> select ename,sal from emp where sal between 800 and 1100;

字符串比较字符串要加单引号默认存储模式就是大写模式

SQL> select ename,sal from emp where ename='KING';

时间格式比较

日期区间表示法

先修改日期显示为我们习惯的格式否则写系统默认的时间格式 DD-MON-RR

alter session set NLS_date_format='YYYY-MM-DD HH24:MI:SS';

select ename,hiredate from emp where hiredate between '1981-01-01' and '1981-05-31';

13.oracle的与and 或or 非not

and(与)

select ename,sal from emp where sal<=1300 and sal >=900;

or(或)

select ename,sal from emp where sal<=1000 or sal >=4000;

not(非)

select ename,sal from emp where not sal >=4000;

与 ==> 两端都为真返回真若有一端或两端为假则为假

或 ==> 若有一端或两端为真则为真两端都为假返回假

但oracle中又引入了NULL

AND运算表

T and F = F T and T = T T and NULL is NULL

F and F = F F and T = F F and NULL = F

NULL and F = F NULL and T is NULL NULL and NULL is NULL

idle> select ename,sal from emp where sal >2000 and sal <3000;

OR运算表

T or T = T T or F = T200 T or NULL = T

F or T = T F or F = F F or NULL IS NULL

NULL or T = T NULL OR F is NULL NULL OR NULL IS NULL

idle> select ename,sal,comm from emp where sal = 1250 or comm is not null;

14.oracle提供的运算符

like between and in not like

is not null not between and

运算符的优先级不用记忆想提高优先级加小括号即可

SQL> select *from scott.emp where sal>2000 and deptno=20 or deptno=30;

SQL> select *from scott.emp where sal>2000 and (deptno=20 or deptno=30);

用and or 来代替 between and 和 in

idle> select empno,ename,sal from emp where sal between 1000 and 1300;

idle> select empno,ename,sal from emp where sal >=1000 and sal <= 1300;

idle> select empno,ename,sal from emp where ename in ('SCOTT','KING','ADAMS');

idle> select empno,ename,sal from emp where ename = 'SCOTT' or ename = 'KING' or ename = 'ADAMS';

like 通配符: % 任意长度(包括零长度)通配符

_ 单个字符通配符

部门名像 SAL开头的

select ename,job from emp where job like 'SAL%';

任意一个字符后跟A之后任意字符串的名字

select ename,job from emp where ename like '_A%';

取反

select ename,job from emp where ename not like '_A%';

like中使用转义

如果查询的字符串包含_或%字面字符串则需要转义

转义符号需要在子句中用escape指明

select * from dept_tmp where dname like 'IT_%';

select * from dept_tmp where dname like 'IT\_%' escape '\';

15.order by 排序输出

排序一定要放在sql语句的最后

排序格式化了输出确给SQL执行增加了负担

默认是从小到大(升序) ASC; 逆序(降序)DESC

select ename,sal from emp where sal < 1500 order by sal;

select ename,sal from emp where sal < 1500 order by sal asc;

select ename,sal from emp where sal < 1500 order by sal desc;

order by中使用表达式

select ename,sal*12 from emp where sal < 1500 order by sal*12;

order by中使用别名

select ename,sal*12 "abc" from emp where sal < 1500 order by "abc";

order by中使用列号列号必须是select子句中已经选择的列的顺序号

select ename,sal*12 "abc" from emp where sal < 1500 order by 2;

order by中使用多列先按职位A-Z的顺序再按年薪小到大的顺序

select ename,job,sal*12 "abc" from emp where sal < 1500 order by 2,3;

order by中使用select子句中未选择的列

select ename,job,sal*12 "abc" from emp where sal < 1500 order by deptno;

4.单行函数

什么是函数?

通俗的讲任何东西,只要它能接收输入,对输入进行加工并产生输出,它就可以被称之为函数

例如:牛是函数,它吃的是草(输入),挤出的是奶(输出)

函数是一种程序设计结构,它可以有一个或多个输入,但只能有一个输出.

函数只有一个出口,使用函数组成的程序很容易调试,也很容易被重用

1.字符类型的函数

字符型

ASCII CHR LOWER UPPER INITCAP CONCAT SUBSTR

LENGTH INSTR TRIM dump lpad rpad REPLACE

ASCII('字符')

返回字符的ASCII码值

SCOTT>>select ascii('a') from dual;

ASCII('a')

----------

CHR('n')

返回n的字符值 n是ASCII码数

SCOTT>>select chr(97) from dual;

但是求单引号的ASCII码写法很特殊两个单引带表一个单引

SCOTT>>select ascii(''') from dual;

ERROR:

ORA-01756: quoted string not properly terminated

SCOTT>>select ascii('''') from dual;

ASCII('''')

-----------

LOWER(列名|表达式)

小写转换

idle> select lower('ABC') from dual;

LOW

---

abc

UPPER(列名|表达式)

大写转换

SCOTT>>select upper('abc')from dual;

UPP

---

ABC

INITCAP(列名|表达式)

每个词的词头大写其他小写

SCOTT>>select initcap('abc xyz qwe')from dual;

INITCAP('AB

-----------

Abc Xyz Qwe

CONCAT(列名|表达式,列名|表达式)

将第一个字符串和第二个字符串连接

SQL> select concat('abc','xyz') from dual;

CONCAT

------

abcxyz

该合数与||功能一样，但是我们常用的是后者

SQL> select 'abc'||'xyz' from dual;

'ABC'|

------

abcxyz

SUBSTR(列名|表达式,m,[n])

返回指定子串,该子串是从第m个字符开始,其长度为n,不指定n值则从m到最后

SQL> select substr('abcdefg',4,3)from dual;

SUB

---

def

SQL> select substr('abcdefg',4)from dual;

SUBS

----

defg

LENGTH(列名|表达式)

返回字符串的长度

SQL> select length('abcdef')from dual;

LENGTH('ABCDEF')

----------------

LENGTHB(列名|表达式)

返回字符串所占用的字节数

INSTR (列名|表达式,'字符串',[m],[n])

从表达式或列中搜索给定的字符串的所处位置,m代表从第几个字符开始搜,n代表要搜索的字符第几次出现. m和n默认都是1

SQL> select instr('abcdddd','d') from dual;

INSTR('ABCDDDD','D')

--------------------

SQL> select instr('abcdddd','d',5) from dual;

INSTR('ABCDDDD','D',5)

----------------------

SQL> select instr('abcdddd','d',5,2) from dual;

INSTR('ABCDDDD','D',5,2)

------------------------

TRIM([leading|]trailing|both 要去掉的字符 FROM 源字符串)

从源字符串中去掉指定的字符可以用leading tailing来修饰去掉的字符串是在开头或结尾,默认是两者都

默认截取的是空格，生产上有时候有的字段会产生空格，这个会给应用带来很大的麻烦，用这个函数就

可以很容易的，除去字段两头的空格，是非常实用的。

SQL> select trim(leading 'a' from 'aaabbbbbaaaa')from dual;

TRIM(LEAD

---------

bbbbbaaaa

SQL> select trim(trailing 'a' from 'aaabbbbbaaaa')from dual;

TRIM(TRA

--------

aaabbbbb

SQL> select trim(both 'a' from 'aaabbbbbaaaa')from dual;

TRIM(

-----

bbbbb

SQL> select trim('a' from 'aaabbbbbaaaa')from dual;

TRIM(

-----

bbbbb

SQL> select trim(' aaabbbbbaaaa ')from dual;

TRIM('AAABBB

------------

aaabbbbbaaaa

左补齐lpad 右补齐rpad

将不足20个字符的位置用指定符号填充.

SQL> select lpad('abc',20,'-'),rpad('abc',20,'-') from dual;

LPAD('ABC',20,'-') RPAD('ABC',20,'-')

-------------------- --------------------

-----------------abc abc-----------------

REPLACE(源字符串,OLD字符串,NEW字符串)

从源字符串中找到搜索的old字符串,替换成new字符串

SQL> select replace('abcdefxyz','def','DEF')from dual;

REPLACE('

---------

abcDEFxyz

dump('str'[,FMT[,S,E]])

用于转换进制格式

str 被转换的字符串

FMT 格式

默认是十进制的

8:用八进制方式显示

16：使用16进制方式显示

1016:把数据库当前字符集显示出来

S str的开始字符位置

E S开始的后续结束字符位置

SQL> select dump('abcd') from dual;

DUMP('ABCD')

--------------------------

Typ=96 Len=4: 97,98,99,100

SQL>

SQL> select dump('abcd',2) from dual;

DUMP('ABCD',2)

--------------------------

Typ=96 Len=4: 97,98,99,100

SQL> select dump('abcd',10) from dual;

DUMP('ABCD',10)

--------------------------

Typ=96 Len=4: 97,98,99,100

SQL> select dump('abcd',8) from dual;

DUMP('ABCD',8)

-----------------------------

Typ=96 Len=4: 141,142,143,144

SQL> select dump('abcd',16) from dual;

DUMP('ABCD',16)

-------------------------

Typ=96 Len=4: 61,62,63,64

SQL> select dump('abcd',1010) from dual;

DUMP('ABCD',1010)

------------------------------------------------

Typ=96 Len=4 CharacterSet=ZHS16GBK: 97,98,99,100

SQL> select dump('戴')from dual;

DUMP('戴')

---------------------

Typ=96 Len=2: 180,247

SQL> select 180*power(256,1)+247 from dual;

180*256+247

-----------

46327

SQL> select chr(46327) from dual;

戴

SQL> select dump(987654321) from dual;

DUMP(987654321)

-------------------------------

Typ=2 Len=6: 197,10,88,66,44,22

197-193=4

10-1=9*power(100,(4-0))=900000000

88-1=87*power(100,(4-1))=87000000

66-1=65*power(100,(4-2))=650000

44-1=43*power(100,(4-3))=4300

22-1=21*power(100,(4-4))=21

2.数值型函数

ROUND

TRUNC

MOD

CEIL

power

greatest

least

ROUND(列名|表达式,n)

四舍五入到小数点后的n位

SQL> select round(456.789,0),round(456.789,1),round(456.789,-1)from dual;

ROUND(456.789,0) ROUND(456.789,1) ROUND(456.789,-1)

---------------- ---------------- -----------------

457 456.8 460

TRUNC(列名|表达式,n)

截取到小数点后的n位

SQL> select trunc(456.789,0),trunc(456.789,1),trunc(456.789,-1) from dual;

TRUNC(456.789,0) TRUNC(456.789,1) TRUNC(456.789,-1)

---------------- ---------------- -----------------

456 456.7 450

MOD(m,n)

求m除以n的余数

SQL> select mod(10,3) from dual;

MOD(10,3)

----------

SQL> select mod(3,10) from dual;

MOD(3,10)

----------

CEIL 取整向上补1 和trunc相反

SQL> select ceil(456.001)from dual;

CEIL(456.001)

-------------

457

power(底数,指数) 求次方

SQL> select power(10,3) from dual;

POWER(10,3)

-----------

1000

greatest(expr1,expr2.......) 求出所列出的表达式或者值中的最大值

SQL> select greatest(1,2,3,4)from dual;

GREATEST(1,2,3,4)

-----------------

least(expr1,expr2.......) 求出所列出的表达式或者值中的最小值

SQL> select least(1,2,3,4)from dual;

LEAST(1,2,3,4)

--------------

3.日期类型的函数

修改当前会话的日期显示格式

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

使用函数查看系统当前时间，这个时间来自于操作系统，数据库里面是不存储当前时间的

SQL> select sysdate from dual;

SYSDATE

-------------------

2016-05-20 13:35:57

日期的运算

默认情况下，参与运算的单位是‘天’

SQL> select sysdate-10 from dual;

SYSDATE-10

-------------------

2016-05-10 13:39:09

如果想让小时参与运算，可以处以24

SQL> select sysdate-10/24 from dual;

SYSDATE-10/24

-------------------

2016-05-20 03:48:42

以此类推，分钟，秒都可以这样计算

与日期有关的函数

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND 和 TRUNC 对日期的取舍

MONTHS_BEWTEEN(日期1,日期2)

计算两个日期间所相差的月份

日期1大于日期2返回正数，日期1小于日期2返回负数

SQL> select months_between('2016-07-21','2016-01-01') from dual;

MONTHS_BETWEEN('2016-07-21','2016-01-01')

-----------------------------------------

6.64516129

ADD_MONTHS(日期,n)

把n个月加到日期上

SQL> select add_months(sysdate,5)from dual;

ADD_MONTHS(SYSDATE,

-------------------

2016-10-20 13:58:57

NEXT_DAY(日期,星期)

从给定日期算起，下一个指定的星期几是几号

SQL> select next_day(sysdate,'MONDAY')FROM DUAL;

NEXT_DAY(SYSDATE,'M

-------------------

2016-05-23 14:01:19

SQL> select next_day('2016-05-12','MONDAY')FROM DUAL;

NEXT_DAY('2016-05-1

-------------------

2016-05-16 00:00:00

LAST_DAY(日期)

返回该日期的所在月的最后一天

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDATE)

-------------------

2016-05-31 14:03:17

我们修改一下日期显示的格式

ROUND(date,'[day|month|year]') 和 trunc(date,'[day|month|year]') 应用于日期型数据

数字的进位和截取是以小数点为中心,而日期的进位和截取是以年月日时分秒为中心

round 截取月份以16号为分割，16号及其以后的日期都入到下一个月

16号之前的日期都返回月初的日期

SQL> select round(to_date('2016-05-20'),'month')from dual;

ROUND(TO_DATE('2016

-------------------

2016-06-01 00:00:00

年的四舍五入要所输入的月份，六月及其以前的日期，都返回年初，六月之后的日期返回下一年年初

SQL> select round(to_date('2016-06-16'),'year')from dual;

ROUND(TO_DATE('2016

-------------------

2016-01-01 00:00:00

以周三中午12点为分界线，周三之前返回本周日的0:00,周三之后的日期，返回下周日的0:00

西方以周日为一周的开始

SQL> select round(to_date('2016-07-20 11:01:01'),'day') from dual;

ROUND(TO_DATE('2016

-------------------

2016-07-17 00:00:00

SQL> select round(to_date('2016-07-21 11:01:01'),'day') from dual;

ROUND(TO_DATE('2016

-------------------

2016-07-24 00:00:00

4.类型转换函数

不同数据类型之间的转换

在赋值语句中数据类型会隐式转换,隐式转换应当尽量避免,因为写出来的SQL难理解,随着时间流逝自己都很难理解

特别是嵌入到大型程序中时,另外,oracle升级会修改隐式转换的规则,这会使程序移植遇到问题.

尤其是索引列不能使用隐式转换那样就不走索引了

赋值语句中oracle的自动转换规则:

1.将变长字符型(varchar2)或定长字符型(char)转换成数值型(number)

2.将变长字符型(varchar2)或定长字符型(char)转换成日期型(date)

3.将数值型(number)转换成变长字符型(varchar2)

4.将日期型(date)转换成变长字符型(varchar2)

表达式中oracle的自动转换规则:

1.将变长字符型(varchar2)或定长字符型(char)转换成数值型(number)

2.将变长字符型(varchar2)或定长字符型(char)转换成日期型(date)

转换成功的条件:

在将字符型数据转换成数字型时,要保证字符型数据为有效的数.

在将字符型数据转换成日期型时,要保证字符型数据为有效的日期.

SQL> select ename,sal from emp where sal>'4000';

ENAME SAL

---------- ----------

KING 5000

以上例子中oracle自动将字符型的数据转换成了数值型

SQL> select ename,hiredate from emp where hiredate='1981-11-17';

ENAME HIREDATE

---------- -------------------

KING 1981-11-17 00:00:00

以上例子中oracle自动将自负类型转换为了日期类型

注意：不能直接转换的情况

数值型和日期型之间不能直接转换,必须将其中之一先转换为字符型,之后再转换为另一种类型

显示转换函数:

to_char

to_number

to_date

to_char 两种情况：

1.to_char(数字) 目的是将数字格式化货币显示格式

2.to_char(日期) 目的是将日期格式化需要的格式

避免隐式转换

to_number 不常用，因为数值可以直接输入

to_date 很常用，因为日期是不可以输入只能转

to_char(日期,'fmt')

将日期型数据转换成字变长字符串.fmt为日期格式

日期格式必须用单引号扩起来

常用的时间格式:

YYYY 数字年 YEAR 英文年

MM 数字月 MONTH 英文月

DY 缩写的星期 DAY 完整的星期

DD 数字日期 hh 12小时

HH24 24小时 MI 分钟

SS 秒

SQL> select ename,

to_char(hiredate,'yyyy-mm-dd year month day dy') hiredate

from emp where ename='SCOTT';

ENAME HIREDATE

---------- --------------------------------------------------------------------------------

SCOTT 1987-04-19 nineteen eighty-seven april sundaysun

以上例子中scott的入职日期被以各种维度显示出来了

to_char(数字,'fmt')

将数字转换成变长字符串.fmt为数字格式

9一个数字给定的9个数不足会显示#

0显示前导0

$美元符号

L本地货币符号

.小数点

,千位符

SQL> select to_char(sal,'L99999')from emp where ename='SCOTT';

TO_CHAR(SAL,'L99999')

--------------------------------

￥3000

SQL> select to_char(sal,'L99999','nls_currency=rmb')from emp where ename='SCOTT';

TO_CHAR(SAL,'L99999','NLS_CURREN

--------------------------------

rmb3000

to_char 还可以将10进制数转换成16进制数

SQL> select to_char(97,'xxxx') from dual;

TO_CHAR(97

----------

to_number('字符串','数字格式')

这个数字格式体现的是前面字符串的格式

字符串一定要符合数字的格式

SQL> select to_number('$123.456','$99999.999')from dual;

TO_NUMBER('$123.456','$99999.999')

----------------------------------

123.456

to_number 也可以将16进制转换成10进制

SQL> select to_number('a','xxxxx')from dual;

TO_NUMBER('A','XXXXX')

----------------------

SQL> select to_number(61,'xxxxx')from dual;

TO_NUMBER(61,'XXXXX')

---------------------

TO_DATE('字符串','日期格式')

字符串一定要符合日期格式

SQL> select to_date('20-MAY-16','DD-MON-RR')from dual;

TO_DATE('20-MAY-16'

-------------------

2016-05-20 00:00:00

简化输入的操作，必须使用yyyy-mm-dd格式，并且只能精确到天

SQL> select date'2016-07-22' from dual;

DATE'2016-07-22'

-------------------

2016-07-22 00:00:00

5.NULL值处理函数

null值

是一个很特别的值,既不是0也不是空格.它的值是没有定义,不确定的未知值

比如一个案件的追踪表,警方在对犯罪分子一无所知,但在犯罪分子性别一栏不是男就是女,

只是此时还不确定

就可以把性别栏设置为未知,当案件侦破到一定程度,警方知道了犯罪分子的性别,

既从未知变成了已知.也就是由NULL变成男或女

SQL> select empno,ename,sal,comm from emp where ename in('SCOTT','WARD');

EMPNO ENAME SAL COMM

---------- ---------- ---------- ----------

7521 WARD 1250500

7788 SCOTT 3000

以上输出内容的奖金一列存在空值，如果我们统计总收入的话，那么就会出现下面的情况

奖金为null的员工最终的工资还是null，这是不合理的

SQL> select empno,ename,sal,comm,sal+comm total_sal from emp where ename in('SCOTT','WARD');

EMPNO ENAME SAL COMM TOTAL_SAL

---------- ---------- ---------- ---------- ----------

7521 WARD 1250500 1750

7788 SCOTT 3000

SQL> select ename,sal from emp where comm=null;

no rows selected

想要找到奖金为null的员工信息，却没有任何结果

这是由null的特殊性决定的，null不参与运算，因此只能用is null来描述它

SQL> select ename,sal from emp where comm is null;

ENAME SAL

---------- ----------

SMITH 800

JONES 2975

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

ADAMS 1100

JAMES 950

FORD 3000

MILLER 1300

SQL> select ename,sal,comm from emp where comm is not null;

ENAME SAL COMM

---------- ---------- ----------

ALLEN 1600 300

WARD 1250 500

MARTIN 1250 1400

TURNER 1500 0

要使null能够参与运算，需要用函数来将null值进行转化，相关函数有

NVL

NVL2

NULLIF

COALESCE

NVL(表达式1,表达式2)

如果表达式1是NULL,则返回表达式2

如果表达式1非NULL,则返回表达式1

表达式1和表达式2可以是数字,字符串,日期格式,1和2的数据类型必须一致

SQL> select ename,sal,comm,sal+nvl(comm,0)from emp where ename in('SCOTT','WARD');

ENAME SAL COMM SAL+NVL(COMM,0)

---------- ---------- ---------- ---------------

WARD 1250 500 1750

SCOTT 3000 3000

NVL2(expr1,expr2,expr3)

如果expr1为空,则返回expr3,否则返回expr2

SQL> select ename,sal,comm,sal+nvl2(comm,comm,0)from emp where ename in('SCOTT','WARD');

ENAME SAL COMM SAL+NVL2(COMM,COMM,0)

---------- ---------- ---------- ---------------------

WARD 1250 500 1750

SCOTT 3000 3000

NULLIF(expr1,expr2)

如果expr1和expr2相同,则返回空,否则返回expr1

SQL> select empno,ename,NULLIF(ename,'SCOTT')FROM EMP where ename in ('SCOTT','KING');

EMPNO ENAME NULLIF(ENA

---------- ---------- ----------

7788 SCOTT

7839 KING KING

空值的排序升序会排在最后降序排在最前

SQL> select empno,ename,sal,comm from emp where ename in('SCOTT','WARD') ORDER BY COMM;

EMPNO ENAME SAL COMM

---------- ---------- ---------- ----------

7521 WARD 1250500

7788 SCOTT 3000

SQL> select empno,ename,sal,comm from emp where ename in('SCOTT','WARD') ORDER BY COMM DESC;

EMPNO ENAME SAL COMM

---------- ---------- ---------- ----------

7788 SCOTT 3000

7521 WARD 1250500

COALESCE(expr1,expr2,expr3,...)

返回第一个非空的表达式

SQL> select coalesce('','','a','','','b','','')from dual;

SQL> select coalesce('','','','','','b','','')from dual;

emp表中有奖金的展示奖金，没有奖金的展示工资

6.分支函数

分支函数

decode函数

decode(expr,search1,result1,search2,result2......,default)

这个函数是返回一个表达式在各种可能取值的情况下的值

例如，expr满足search1,则返回result1，满足search2则返回result2，以此类推，

最终如果没有满足任何比对条件，则返回default值

SQL> select empno,ename,deptno,decode(deptno,10,'AAA',20,'BBB','CCC') FROM EMP;

EMPNO ENAME DEPTNO DEC

---------- ---------- ---------- ---

7369 SMITH 20 BBB

7499 ALLEN 30 CCC

7521 WARD 30 CCC

7566 JONES 20 BBB

7654 MARTIN 30 CCC

7698 BLAKE 30 CCC

7782 CLARK 10 AAA

7788 SCOTT 20 BBB

7839 KING 10 AAA

7844 TURNER 30 CCC

7876 ADAMS 20 BBB

7900 JAMES 30 CCC

7902 FORD 20 BBB

7934 MILLER 10 AAA

练习:

按部分编号涨工资(只打印) 10号部门涨10% 20号部分涨20% 其他部分涨30%

SQL> select ename,sal deptno,case when deptno=10 then sal+sal*0.1

2 when deptno=20 then sal+sal*0.2

3 else sal+sal*0.3 end up_sal from emp;

case when 子句

case when 子句分为简单和搜索两种

简单case when子句的语法：

case expr when comparation_expr then return_expr...... else else_expr end

简单的case when子句可以实现等值比较，与decode一样

SQL> select ename,sal,case deptno when 10 then 'AAA' when 20 then 'BBB' else 'CCC' end from emp;

ENAME SAL CAS

---------- ---------- ---

SMITH 800 BBB

ALLEN 1600 CCC

WARD 1250 CCC

JONES 2975 BBB

MARTIN 1250 CCC

BLAKE 2850 CCC

CLARK 2450 AAA

SCOTT 3000 BBB

KING 5000 AAA

TURNER 1500 CCC

ADAMS 1100 BBB

JAMES 950 CCC

FORD 3000 BBB

MILLER 1300 AAA

14 rows selected.

搜索case when可以实现不等值的比较

case when condation then return_expr...... else else_expr end

SQL> select ename,sal,case

when sal<1000 then sal+1

when sal>=1000 and sal<2000 then sal+2

when sal>=2000 then sal+3

else sal+4 end up_sal

from emp

ENAME SAL UP_SAL

---------- ---------- ----------

SMITH 800 801

ALLEN 1600 1602

WARD 1250 1252

JONES 2975 2978

MARTIN 1250 1252

BLAKE 2850 2853

CLARK 2450 2453

SCOTT 3000 3003

KING 5000 5003

TURNER 1500 1502

ADAMS 1100 1102

JAMES 950 951

FORD 3000 3003

MILLER 1300 1302

14 rows selected.

7.聚集函数

多行函数聚集函数

常用聚集函数

是对一组或一批数据进行综合操作后返回一个结果

count行总数

avg 平均数

sum列值的和

max最大值

min 最小值

count([{distinct|all} '列名'|*) 为列值时空不在统计之内，为*时包含空行和重复行

SQL> select count(*) from emp;

COUNT(*)

----------

SQL>

SQL> select count(comm)from emp;

COUNT(COMM)

-----------

SQL> select count(distinct deptno)from emp;

COUNT(DISTINCTDEPTNO)

---------------------

SQL> select count(deptno)from emp;

COUNT(DEPTNO)

-------------

SQL> select max(sal),avg(sal),min(sal),count(sal) from emp;

MAX(SAL) AVG(SAL)MIN(SAL) COUNT(SAL)

---------- ---------- ---------- ----------

5000 2073.21429 800 14

上面执行的聚集函数都是对所有记录统计，一次只返回一行记录

如果想分组统计(比如统计部门的平均值)需要使用group by

为了限制分组统计的结果需要使用having过滤

GROUP BY 分组统计 9I要排序 10G不排序

求出每个部门的平均工资

SQL> select deptno,avg(sal) from emp group by deptno;

DEPTNO AVG(SAL)

---------- ----------

30 1566.66667

20 2175

10 2916.66667

还可以按照给定字段进行排序

SQL> select deptno,avg(sal) from emp group by deptno order by deptno;

DEPTNO AVG(SAL)

---------- ----------

10 2916.66667

20 2175

30 1566.66667

分组排序可以使用未被选择的列

SQL> select avg(sal) from emp group by deptno order by deptno;

AVG(SAL)

----------

2916.66667

2175

1566.66667

如果在查询中使用了分组函数,任何不在分组函数中的列或表达式必须在group by子句中

SQL> select deptno,avg(sal) from emp;

select deptno,avg(sal) from emp

ERROR at line 1:

ORA-00937: not a single-group group function

group by 的过滤

group by的过滤使用having子句，放在group by 子句的后面，

和where子句不同，having子句是在分组计算完成后进行的过滤，而where

子句是在分组计算前做的过滤,where 条件里面只能出现单行处理函数，而having

子句可以出现聚集函数

SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

DEPTNO AVG(SAL)

---------- ----------

20 2175

10 2916.66667

分组函数的注意事项:

1.分组函数只能出现在选择列,order by,having子句中

2.分组函数会忽略NULL 除了count(*)

3.分组函数中可以使用ALL或distinct;ALL是默认值,统计所有.加上distinct则只统计不同

4.如果选择的列里有普通列,表达式和分组列那么普通列和表达式都必须出现在group by中

行转列

create table t4(id int,name varchar2(10),subject varchar2(20),grade number);

insert into t4 values(1,'ZORRO','语文',70);

insert into t4 values(2,'ZORRO','数学',80);

insert into t4 values(3,'ZORRO','英语',75);

insert into t4 values(4,'SEKER','语文',65);

insert into t4 values(5,'SEKER','数学',75);

insert into t4 values(6,'SEKER','英语',60);

insert into t4 values(7,'BLUES','语文',60);

insert into t4 values(8,'BLUES','数学',90);

insert into t4 values(9,'PG','数学',80);

insert into t4 values(10,'PG','英语',90);

commit;

SQL> select * from t4;

ID NAME SUBOBJECT GRADE

---------- ---------- -------------------- ----------

1 ZORRO 语文 70

2 ZORRO 数学 80

3 ZORRO 英语 75

4 SEKER 语文 65

5 SEKER 数学 75

6 SEKER 英语 60

7 BLUES 语文 60

8 BLUES 数学 90

9 PG 数学 80

10 PG 英语 90

10 rows selected.

SQL> select name,sum(case when SUBJECT='语文' then GRADE else 0 end) "语文",sum(case when SUBJECT='数学' then GRADE else 0 end) "数学",sum(case when SUBJECT='英语' then GRADE else 0 end) "英语" from t5 group by name;

NAME 语文数学英语

-------------------------------------------------- ---------- ---------- ----------

SEKER 65 75 60

BLUES 60 90 0

PG 0 80 80

ZORRO 70 80 75

沈学良

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
number 16 6 oracle,Oracle数据库学习一

1.简介数据存储有哪些方式？电子表格，纸质文件，数据库。那么究竟什么是关系型数据库？目前对数据库的分类主要是关系型和非关系型两种，关系型的主要代表有oracle，db2，mysql，sqlserver等，非关系型数据库也称作nosql，主要有mongodb,hbase等等。关系型数据库主要是以二维表的方式存数数据的，这是关系数据库最显著的特征，什么是二维表呢？Excel表格就是二维表，由行和列两个...
复制链接

扫一扫