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)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT@ora10g> desc dept
SQL@ora10g> desc SALGRADE
desc描述表的结构 所谓表的结构:就是表有多少列 列的名称和类型及约束(非空)
desc emp
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')
----------
97
CHR('n')
返回n的字符值 n是ASCII码数
SCOTT>>select chr(97) from dual;
C
-
a
但是求单引号的ASCII码写法很特殊 两个单引带表一个单引
SCOTT>>select ascii(''') from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SCOTT>>select ascii('''') from dual;
ASCII('''')
-----------
39
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')
----------------
6
LENGTHB(列名|表达式)
返回字符串所占用的字节数
INSTR (列名|表达式,'字符串',[m],[n])
从表达式或列中搜索给定的字符串的所处位置,m代表从第几个字符开始搜,n代表要搜索的字符第几次出现. m和n默认都是1
SQL> select instr('abcdddd','d') from dual;
INSTR('ABCDDDD','D')
--------------------
4
SQL> select instr('abcdddd','d',5) from dual;
INSTR('ABCDDDD','D',5)
----------------------
5
SQL> select instr('abcdddd','d',5,2) from dual;
INSTR('ABCDDDD','D',5,2)
------------------------
6
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>
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;
CH
--
戴
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)
----------
1
SQL> select mod(3,10) from dual;
MOD(3,10)
----------
3
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)
-----------------
4
least(expr1,expr2.......) 求出所列出的表达式或者值中的最小值
SQL> select least(1,2,3,4)from dual;
LEAST(1,2,3,4)
--------------
1
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 sunday sun
以上例子中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
----------
61
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')
----------------------
10
SQL> select to_number(61,'xxxxx')from dual;
TO_NUMBER(61,'XXXXX')
---------------------
97
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 1250 500
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 1250 500 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 1250 500
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 1250 500
COALESCE(expr1,expr2,expr3,...)
返回第一个非空的表达式
SQL> select coalesce('','','a','','','b','','')from dual;
C
-
a
SQL> select coalesce('','','','','','b','','')from dual;
C
-
b
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(*)
----------
14
SQL>
SQL>
SQL> select count(comm)from emp;
COUNT(COMM)
-----------
4
SQL> select count(distinct deptno)from emp;
COUNT(DISTINCTDEPTNO)
---------------------
3
SQL> select count(deptno)from emp;
COUNT(DEPTNO)
-------------
14
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
