首页 > 代码库 > oracle ocp知识点
oracle ocp知识点
1.如何确定数据库是否启动
su - oracle
ps -ef |grep ora_|head -2
两种关系数据库是ora或者是自动存储管理的asm开头的，
查看进程可以知道数据库实例至少已经启动，但是数据库是否挂载，是否打开，不得而知
asm
crs_stat -t
updb.db online
srvctl status database -d updb
db
sqlplus -s / as sysdba
select open_mode from database;
通过查询该视图可以知道数据库是否启动，该视图显示了数据的状态
asm
cat .asm
export ORACLE_HOME=/u01/grid/11g
export PATH=$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
export ORACLE_SID=+ASM
grep asm .bashrc
alias asm=". ~/.asm"
cre_stop ora.updb.db
srvctl status/stop database -d updb
srvctl status/stop/start database -d updb -o
2.如何确定数据库实例名
ps -ef |grep ora_|head -1 |cut -d ‘_‘ -f3
sqlplus / as sysdba
select instance_name from v$instance;
后台进程和数据库查询两种方式
export ORACLE_SID=updb
echo $ORACLE_SID
source !$
. !$
oracle_sid在windows不区分大小写，但是其他系统区分大小写
grep SID .db11g
ps -ef|grep ora_|cut -d ‘_‘ -f3|sort|uniq
多个实例，只需要通过export ORACLE_SID=updb切换
3.如何连接到sqlplus
[oracle@oracle ~]$ tail -l /etc/oratab
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
wyzc10g:/u01/oracle/10g:N
wyzc11g:/u01/oracle/11g:N # line added by Agent
wyzc:/u01/oracle/12c:N: # line added by Agent
export ORACLE_SID=wyzc10g
export ORACLE_HOME=/u01/oracle/10g
export PATH=
which sqlplus
sqlplus /nolog
解锁用户
show user
alter user scott indentified by tiger account unlock;
4.查看用户下有哪些表
select * from tab;
select table_name from user_tables;
5.如何查看表结构
set linesize 50
desc dept
6.select 语句的功能
列查询、行查询、多表查询
书写规则：
不区分大小写
可以分行书写
关键词不能简写或分割跨行
通常一个子句一行
双引号加起的语句必须区分大小写
sqldelevoper
/u01/oracle/11g/sqldevdloper/sqldeveloper.sh
7.默认列标题
sqlplus
字符型、日期靠左对齐
number 靠右对齐
开发工具
靠左
8.算数表达式
空值 defining a null value
null is value that is unavailable,unassigned,,unknown,or inapplicable
null is not the same as zero or a blank space
不能进行算数运算，否则为空
要进行算数运算，需要空值函数运算，nvl(字段,0)
9.别名
重命名列标题，有益于运算，直接跟在列名之后，as是可选的字符
包含空格、特殊字符、区分大小写使用""引起来
非法语句合法化，如下
create table e1 as select deptno,sum(sal) from emp group by deptno;执行此语句会提示错误
修改为create table e1 as select deptno,sum(sal) salcomm from emp group by deptno;
连接符：将列连接起来，最后的结果肯定为字符串 ||
字面字符：number,data,字符型,必须用单引号引起来
select ‘name: ‘|| empno from emp
name； 7900
name: 7800
select ename || ‘is work ‘|| job from emp;
两个单引号在一起表示一个单引号
select ename || ‘‘‘is work ‘|| job from emp;
select ename||q‘[‘s work ]‘||job from emp;
此处的符号[]可以替换为任意的符号，但是必须成对出现
10.去除重复行
select distinct deptno from emp; 关键字 考试一般选择此项
select distinct(deptno) from emp; 函数
select unique deptno from emp; 关键字
select unique(deptno) from emp; 函数
11.限制行的查询
select * from emp where condtion(s);where子句必须放在from之后
select * from emp where empno=‘7900‘;
字符与日期使用单引号引起来
字符区分大小写，日期格式敏感
默认日期显示格式DD-MON—RR
比较操作符
=, >, <, >=, <=, <> ^= !=, in, like, between and, is null,
取反操作
select ename,sal from emp where sal not between 2000 and 3000；
select ename,sal from emp where not sal between 2000 and 3000；两次运算，效率低下
select ename,sal from emp where sal not in(2000,3000);
select ename,sal from emp where not sal in(2000,3000);运算两次，
select ename from emp where ename like ‘__A%‘;
select ename from emp where ename like ‘A%‘;
select ename from emp where ename like ‘%S‘;
select ename from emp where ename like ‘%/_%‘ escape ‘/‘; 转义，转义的字符可以是任意的字符
select ename from emp where ename like ‘%/%%‘ escape ‘/‘;
select ename from emp where ename like ‘%1%%‘ escape ‘1‘;
字符串增减单引号，日期的注意格式
select sysdate from dual;
select ename,hiredate from emp where hiredate=‘1981-12-03‘;过滤时间时，确认系统的时间是什么样的
c/_/% 替换操作
逻辑运算
and,or,not
and 要求所有条件都为true
or 任意一个条件满足都为true
操作符优先级规则
7 not 8 and 9 or
排序数据使用order by asc 升序默认 desc 降序
***空值排序永远都是第一条，(null)***
***排序列用别名排序***
select ename,sal,comm,sal+nvl(comm,0) from emp order by sal+nvl(comm,0);两次运算，如何一次一运算，修改为如下
select ename,sal,comm,sal+nvl(comm,0) ts from emp order by ts;
***排序用列所在的数字位数***
select ename,sal,comm,sal+nvl(comm,0) from emp order by 4；
select ename,ename||‘ ‘||sal ename from emp order by 2;列名相同
多列排序
select ename,sal from emp order by sal desc;
替代变量
临时变量& 调用替代变量用&&(永久变量)
SQL> select ename,sal from emp where empno=&e;
Enter value for e: 7788
old 1: select ename,sal from emp where empno=&e
new 1: select ename,sal from emp where empno=7788
ENAME SAL
---------- ----------
SCOTT 3000
SQL> select &e,sal from emp where &&e=7788; //需要输入两次empno，sql语句执行从右到左，两次调用
Enter value for e: empno
Enter value for e: empno
old 1: select &e,sal from emp where &&e=7788
new 1: select empno,sal from emp where empno=7788
EMPNO SAL
---------- ----------
7788 3000
SQL> undefine e
SQL> select &&e,sal from emp where &e=7788;
Enter value for e: empno
old 1: select &&e,sal from emp where &e=7788
new 1: select empno,sal from emp where empno=7788
EMPNO SAL
---------- ----------
7788 3000
define 定义一个变量
define v1=7788
select ename,sal from emp where empno=&v1;
***set verfify on/off 显示新旧值设置
undefine 取消一个变量
undefine v1
unset NLS_LANG
11.单行函数
处理数据
单行函数：每行返回一个结果
字符函数：lower,uppper,initcap(注意此函数)
SQL> select initcap(‘king_scott‘) from dual;
INITCAP(‘K
----------
King_Scott
SQL> l
1* select initcap(‘king_scott‘) from dual
SQL> c/_/0
1* select initcap(‘king0scott‘) from dual
SQL> /
INITCAP(‘K
----------
King0scott
字符处理类：
concat 连接函数，只能连接两个表达式，结果为字符型
SQL> select concat(ename,‘is work‘),job from emp where deptno=10;
CONCAT(ENAME,‘ISW JOB
----------------- ---------
CLARKis work MANAGER
KINGis work PRESIDENT
MILLERis work CLERK
SQL> select concat(concat(ename,‘is work‘),job)from emp where deptno=10; 函数嵌套
CONCAT(CONCAT(ENAME,‘ISWOR
--------------------------
CLARKis workMANAGER
KINGis workPRESIDENT
MILLERis workCLERK
substr(expr,m,n) m从哪开始，n取多少个，n>0
SQL> select ename from emp where substr(ename,2,1)=‘A‘;
ENAME
----------
WARD
MARTIN
JAMES
SQL> select ename from emp where substr(ename,-2,1)=‘E‘;
ENAME
----------
ALLEN
JONES
TURNER
JAMES
MILLER
避免这种写法select ename from emp where substr(ename,1)=‘KING‘;
length 长度函数
SQL> select length(ename),ename from emp;
LENGTH(ENAME) ENAME
------------- ----------
5 SMITH
5 ALLEN
4 WARD
5 JONES
6 MARTIN
5 BLAKE
5 CLARK
5 SCOTT
4 KING
6 TURNER
5 ADAMS
lengthb,lengthc,length2,length4区别详见官网，中文能看见区别
SQL> select length(ename),lengthb(ename),lengthc(ename) from emp where deptno=10;
LENGTH(ENAME) LENGTHB(ENAME) LENGTHC(ENAME)
------------- -------------- --------------
5 5 5
4 4 4
6 6 6
instr查找指定字符在哪里出现
SQL> select instr(ename,‘A‘), ename from emp;
INSTR(ENAME,‘A‘) ENAME
---------------- ----------
0 SMITH
1 ALLEN
2 WARD
0 JONES
2 MARTIN
3 BLAKE
3 CLARK
0 SCOTT
0 KING
0 TURNER
1 ADAMS
SQL> select ename from emp where instr(ename,‘A‘)=1;
ENAME
----------
ALLEN
ADAMS
SQL> select ename,instr(ename,‘LL‘) from emp;
ENAME INSTR(ENAME,‘LL‘)
---------- -----------------
SMITH 0
ALLEN 2
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 0
rpad右边填充字符
SQL> select rpad(deptno,10,‘ ‘) deptno,dname,loc from dept;
DEPTNO DNAME LOC
---------------------------------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
lpad左边填充
col dname just right 行标题对齐方式
trim(both|leading|trailing ‘char‘ from expr)
SQL> select trim(‘A‘ from ‘AAABBBAAA‘) t from dual;
T
---
BBB
ltrim(expr,‘char‘),rtirm 版本大于等于10g才会有的
replace(expr,old,new);替换字符
chr(10)换行符 官方文档sql第五章
数字函数
round() 近似值
trunc() 截取
mod() 取余
abs() 绝对值
ceil()取整
4 5 6 . 7 8 9
-3 -2 -1 0 1 2 3
SQL> select round(456.789,2),trunc(456.789,2)from dual;
ROUND(456.789,2) TRUNC(456.789,2)
---------------- ----------------
456.79 456.78
SQL> c/2/-1
1* select round(456.789,-1),trunc(456.789,2)from dual
SQL> l
1* select round(456.789,-1),trunc(456.789,2)from dual
SQL> /
ROUND(456.789,-1) TRUNC(456.789,2)
----------------- ----------------
460 456.78
日期函数
default value DD-MON-RR
****0-49,50-99 加减一
current last 2 number ---<50
last 2 number ---0-49<50
50-99 -1 世纪
--->=50
>=50
<50 0-49 +1
SQL> select sysdate from dual;
SYSDATE
---------
19-JUL-14
2014 14
2014 14 2014
**SQL> show parameter nls_date_format;
**SQL> alter session set nls_date_format=‘yyyy-mm-dd‘
日期可以进行算数运算，也可以进行round,trunc运算，trunc(sysdate,‘day‘)
months_between
SQL> select ename,months_between(sysdate,hiredate) s from emp;
ENAME S
---------- ----------
SMITH 403.07913
ALLEN 400.982356
WARD 400.91784
JONES 399.563001
MARTIN 393.724291
BLAKE 398.595259
CLARK 397.337195
SCOTT 327
KING 392.07913
TURNER 394.369453
ADAMS 325.885582
add_months()
next_day()
last_day()
***注意日期的格式***
转换函数
=显式转化
to_char(date,‘format_model‘)
SQL> select to_char(hiredate,‘yyyy-mm-dd‘) from emp;
TO_CHAR(HI
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
SQL> l
1* select to_char(hiredate,‘yyyy-mm-dd‘)from emp
SQL> c/yyyy/fmyyyy
1* select to_char(hiredate,‘fmyyyy-mm-dd‘)from emp
SQL> /
TO_CHAR(HI
----------
1980-12-17
1981-2-20
1981-2-22
1981-4-2
1981-9-28
1981-5-1
1981-6-9
1987-4-19
1981-11-17
1981-9-8
1987-5-23
SQL> c/dd/fmdd
1* select to_char(hiredate,‘fmyyyy-mm-fmdd‘)from emp
SQL> /
TO_CHAR(HI
----------
1980-12-17
1981-2-20
1981-2-22
1981-4-02
1981-9-28
1981-5-01
1981-6-09
1987-4-19
1981-11-17
1981-9-08
1987-5-23
9 0 $ L . ,特殊符号
SQL> select ename,to_char(sal,‘9,999.00‘) sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SCOTT 3,000.00
KING 5,000.00
SQL> select ename,to_char(sal,‘9G999D00‘) sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SQL> select ename,to_char(sal,‘$9G999D00‘) sal from emp;
ENAME SAL
---------- ----------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
JONES $2,975.00
MARTIN $1,250.00
BLAKE $2,850.00
CLARK $2,450.00
SCOTT $3,000.00
SQL> select to_char(16,‘x‘) from dual;
TO
--
##
to_number(char[,‘format_model‘]);
SQL> select to_number(‘f‘,‘xxx‘) from dual;
TO_NUMBER(‘F‘,‘XXX‘)
--------------------
15
================================================
注意以下两个区别，列的对齐方式
SQL> select ename,sal from emp where deptno=10;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
SQL> c/sal/to_char(sal) sal/
1* select ename,to_char(sal) sal from emp where deptno=10
SQL> l
1* select ename,to_char(sal) sal from emp where deptno=10
SQL> /
ENAME SAL
---------- ----------------------------------------
CLARK 2450
KING 5000
MILLER 1300
==================================================================
to_date(char[,‘format_model‘]);
SQL> select to_date(‘19811111‘,‘yyyy/mm/dd‘) from dual;
TO_DATE(‘
---------
11-NOV-81
空值函数
nvl(expr1,expr2)
SQL> select ename,sal,comm,sal+nvl(comm,0) from emp;
ENAME SAL COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
nvl2(expr1,expr2,expr3)
SQL> select nvl2(2,1,0) from dual;
NVL2(2,1,0)
-----------
1
SQL> c/2/null
1* select nvl2(null1,0) from dual
SQL> /
SQL> select nvl2(null,1,0) from dual;
NVL2(NULL,1,0)
--------------
0
nullif(expr1,expr2)
coalesce(expr1,expr2,...,exprn)
不停地查找，返回不为空的值
=隐式转化 oracle服务器自动完成
字符串转number
字符串转日期
条件表达式(分支函数)
case expr when ... then ...
else ...
end
SQL> select ename,deptno,sal,case deptno when 10 then sal*1.1
2 when 20 then sal*1.3
3 else sal
4 end isal from emp;
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 1040
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3867.5
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SQL> l
1 select ename,deptno,sal, case when deptno=10 then sal*1.1
2 when deptno=20 then sal*1.2
3 else sal
4* end isal from emp
SQL> /
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 960
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3570
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SCOTT 20 3000 3600
decode 可读性没有case好
SQL> select ename,deptno,sal,decode(deptno,10,sal*1.1,20,sal*1.2,sal) isal from emp;
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 960
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3570
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SCOTT 20 3000 3600
SQL> select to_char(hiredate,‘yyyy‘),count(*) from emp group by to_char(hiredate,‘yyyy‘);
TO_C COUNT(*)
---- ----------
1987 2
1980 1
1982 1
1981 10
行转列
SQL> select sum(decode(to_char(hiredate,‘yyyy‘),‘1980‘,1)) "1980",sum(decode(to_char(hiredate,‘yyyy‘),‘1981‘,1)) "1981" from emp;
1980 1981
---------- ----------
1 10
多行函数：每个行组集返回一个结果
分组函数 group by having限制分组个数
统计函数不统计空值
SQL> select count(comm) from emp;
COUNT(COMM)
-----------
4
SQL> select count(nvl(comm,0)) from emp; //效率低下
COUNT(NVL(COMM,0))
------------------
14
avg(),min(),max(),sum()
SQL> select job,min(sal) from emp group by job;
JOB MIN(SAL)
--------- ----------
CLERK 800
SALESMAN 1250
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
SQL> select job,min(sal) from emp group by job having min(sal)>2000;
JOB MIN(SAL)
--------- ----------
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
多表查询语句(实践必须掌握优化技巧)
等值连接与不等值连接
A.外连接
左外连接
右外连接
B.自然连接 要求列名相同、数据类型相同
SQL> select ename,dname from emp natural join dept;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
列名不相同用以下语句
SQL> select ename,dname from emp join dept on emp.deptno=dept.id;
数据类型不同，做自然连接会报错，可以修改为以下
select ename,dname from emp join dept using(deptno);
join on ,join using,natural join n-1个连接条件
full join
cross join 交叉连接
子查询
在主查询执行之前，子查询会执行一次，子查询结果被用于主查询
SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
----------
KING
SQL> select ename from emp where sal in (select sal from emp where sal>2999);
ENAME
----------
FORD
SCOTT
KING
单行子查询
多行子查询 子查询结果为空，可能导致查询结果为空需要注意
any all in
SQL> select ename,sal from emp where sal > any(2500,3000);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000
SQL> c/any/all
1* select ename,sal from emp where sal > all(2500,3000)
SQL> /
ENAME SAL
---------- ----------
KING 5000
SQL> c/>/<
1* select ename,sal from emp where sal < all(2500,3000)
SQL> /
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
9 rows selected.
SQL> c/all/any
1* select ename,sal from emp where sal < any(2500,3000)
SQL> /
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
11 rows selected.
****some与any 等价****
exists
SQL> select ename from emp where empno in (select mgr from emp);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
性能低下，6次循环
SQL> select ename from emp e where exists (select 1 from emp where mgr=e.empno);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
性能高，一次全表扫描，此处的1为标签，
SQL> c/where/where not
1* select ename from emp e where not exists (select 1 from emp where mgr=e.empno)
SQL> /
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
set操作
交并补
union 去除重复行,排序,升序,union all 不去除重复行,不排序
intersect 交集,没有重复
minus 补集A-B
表达式在数量上匹配，数据类型前后必须一致
括号可变优先级，order by 语句可以出现在语句最后
SQL> select deptno,null job,max(sal) from emp group by deptno
2 union all
3 select null ,job,max(sal) from emp group by job;
DEPTNO JOB MAX(SAL)
---------- --------- ----------
30 2850
20 3000
10 5000
CLERK 1300
SALESMAN 1600
PRESIDENT 5000
MANAGER 2975
ANALYST 3000
以上是构造伪列
以下是10g以后才会支持的
SQL> c/setss/sets
1* select deptno,job,max(sal) from emp group by grouping sets(deptno,job)
SQL> /
DEPTNO JOB MAX(SAL)
---------- --------- ----------
CLERK 1300
SALESMAN 1600
PRESIDENT 5000
MANAGER 2975
ANALYST 3000
30 2850
20 3000
10 5000
su - oracle
ps -ef |grep ora_|head -2
两种关系数据库是ora或者是自动存储管理的asm开头的，
查看进程可以知道数据库实例至少已经启动，但是数据库是否挂载，是否打开，不得而知
asm
crs_stat -t
updb.db online
srvctl status database -d updb
db
sqlplus -s / as sysdba
select open_mode from database;
通过查询该视图可以知道数据库是否启动，该视图显示了数据的状态
asm
cat .asm
export ORACLE_HOME=/u01/grid/11g
export PATH=$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
export ORACLE_SID=+ASM
grep asm .bashrc
alias asm=". ~/.asm"
cre_stop ora.updb.db
srvctl status/stop database -d updb
srvctl status/stop/start database -d updb -o
2.如何确定数据库实例名
ps -ef |grep ora_|head -1 |cut -d ‘_‘ -f3
sqlplus / as sysdba
select instance_name from v$instance;
后台进程和数据库查询两种方式
export ORACLE_SID=updb
echo $ORACLE_SID
source !$
. !$
oracle_sid在windows不区分大小写，但是其他系统区分大小写
grep SID .db11g
ps -ef|grep ora_|cut -d ‘_‘ -f3|sort|uniq
多个实例，只需要通过export ORACLE_SID=updb切换
3.如何连接到sqlplus
[oracle@oracle ~]$ tail -l /etc/oratab
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
wyzc10g:/u01/oracle/10g:N
wyzc11g:/u01/oracle/11g:N # line added by Agent
wyzc:/u01/oracle/12c:N: # line added by Agent
export ORACLE_SID=wyzc10g
export ORACLE_HOME=/u01/oracle/10g
export PATH=
which sqlplus
sqlplus /nolog
解锁用户
show user
alter user scott indentified by tiger account unlock;
4.查看用户下有哪些表
select * from tab;
select table_name from user_tables;
5.如何查看表结构
set linesize 50
desc dept
6.select 语句的功能
列查询、行查询、多表查询
书写规则：
不区分大小写
可以分行书写
关键词不能简写或分割跨行
通常一个子句一行
双引号加起的语句必须区分大小写
sqldelevoper
/u01/oracle/11g/sqldevdloper/sqldeveloper.sh
7.默认列标题
sqlplus
字符型、日期靠左对齐
number 靠右对齐
开发工具
靠左
8.算数表达式
空值 defining a null value
null is value that is unavailable,unassigned,,unknown,or inapplicable
null is not the same as zero or a blank space
不能进行算数运算，否则为空
要进行算数运算，需要空值函数运算，nvl(字段,0)
9.别名
重命名列标题，有益于运算，直接跟在列名之后，as是可选的字符
包含空格、特殊字符、区分大小写使用""引起来
非法语句合法化，如下
create table e1 as select deptno,sum(sal) from emp group by deptno;执行此语句会提示错误
修改为create table e1 as select deptno,sum(sal) salcomm from emp group by deptno;
连接符：将列连接起来，最后的结果肯定为字符串 ||
字面字符：number,data,字符型,必须用单引号引起来
select ‘name: ‘|| empno from emp
name； 7900
name: 7800
select ename || ‘is work ‘|| job from emp;
两个单引号在一起表示一个单引号
select ename || ‘‘‘is work ‘|| job from emp;
select ename||q‘[‘s work ]‘||job from emp;
此处的符号[]可以替换为任意的符号，但是必须成对出现
10.去除重复行
select distinct deptno from emp; 关键字 考试一般选择此项
select distinct(deptno) from emp; 函数
select unique deptno from emp; 关键字
select unique(deptno) from emp; 函数
11.限制行的查询
select * from emp where condtion(s);where子句必须放在from之后
select * from emp where empno=‘7900‘;
字符与日期使用单引号引起来
字符区分大小写，日期格式敏感
默认日期显示格式DD-MON—RR
比较操作符
=, >, <, >=, <=, <> ^= !=, in, like, between and, is null,
取反操作
select ename,sal from emp where sal not between 2000 and 3000；
select ename,sal from emp where not sal between 2000 and 3000；两次运算，效率低下
select ename,sal from emp where sal not in(2000,3000);
select ename,sal from emp where not sal in(2000,3000);运算两次，
select ename from emp where ename like ‘__A%‘;
select ename from emp where ename like ‘A%‘;
select ename from emp where ename like ‘%S‘;
select ename from emp where ename like ‘%/_%‘ escape ‘/‘; 转义，转义的字符可以是任意的字符
select ename from emp where ename like ‘%/%%‘ escape ‘/‘;
select ename from emp where ename like ‘%1%%‘ escape ‘1‘;
字符串增减单引号，日期的注意格式
select sysdate from dual;
select ename,hiredate from emp where hiredate=‘1981-12-03‘;过滤时间时，确认系统的时间是什么样的
c/_/% 替换操作
逻辑运算
and,or,not
and 要求所有条件都为true
or 任意一个条件满足都为true
操作符优先级规则
7 not 8 and 9 or
排序数据使用order by asc 升序默认 desc 降序
***空值排序永远都是第一条，(null)***
***排序列用别名排序***
select ename,sal,comm,sal+nvl(comm,0) from emp order by sal+nvl(comm,0);两次运算，如何一次一运算，修改为如下
select ename,sal,comm,sal+nvl(comm,0) ts from emp order by ts;
***排序用列所在的数字位数***
select ename,sal,comm,sal+nvl(comm,0) from emp order by 4；
select ename,ename||‘ ‘||sal ename from emp order by 2;列名相同
多列排序
select ename,sal from emp order by sal desc;
替代变量
临时变量& 调用替代变量用&&(永久变量)
SQL> select ename,sal from emp where empno=&e;
Enter value for e: 7788
old 1: select ename,sal from emp where empno=&e
new 1: select ename,sal from emp where empno=7788
ENAME SAL
---------- ----------
SCOTT 3000
SQL> select &e,sal from emp where &&e=7788; //需要输入两次empno，sql语句执行从右到左，两次调用
Enter value for e: empno
Enter value for e: empno
old 1: select &e,sal from emp where &&e=7788
new 1: select empno,sal from emp where empno=7788
EMPNO SAL
---------- ----------
7788 3000
SQL> undefine e
SQL> select &&e,sal from emp where &e=7788;
Enter value for e: empno
old 1: select &&e,sal from emp where &e=7788
new 1: select empno,sal from emp where empno=7788
EMPNO SAL
---------- ----------
7788 3000
define 定义一个变量
define v1=7788
select ename,sal from emp where empno=&v1;
***set verfify on/off 显示新旧值设置
undefine 取消一个变量
undefine v1
unset NLS_LANG
11.单行函数
处理数据
单行函数：每行返回一个结果
字符函数：lower,uppper,initcap(注意此函数)
SQL> select initcap(‘king_scott‘) from dual;
INITCAP(‘K
----------
King_Scott
SQL> l
1* select initcap(‘king_scott‘) from dual
SQL> c/_/0
1* select initcap(‘king0scott‘) from dual
SQL> /
INITCAP(‘K
----------
King0scott
字符处理类：
concat 连接函数，只能连接两个表达式，结果为字符型
SQL> select concat(ename,‘is work‘),job from emp where deptno=10;
CONCAT(ENAME,‘ISW JOB
----------------- ---------
CLARKis work MANAGER
KINGis work PRESIDENT
MILLERis work CLERK
SQL> select concat(concat(ename,‘is work‘),job)from emp where deptno=10; 函数嵌套
CONCAT(CONCAT(ENAME,‘ISWOR
--------------------------
CLARKis workMANAGER
KINGis workPRESIDENT
MILLERis workCLERK
substr(expr,m,n) m从哪开始，n取多少个，n>0
SQL> select ename from emp where substr(ename,2,1)=‘A‘;
ENAME
----------
WARD
MARTIN
JAMES
SQL> select ename from emp where substr(ename,-2,1)=‘E‘;
ENAME
----------
ALLEN
JONES
TURNER
JAMES
MILLER
避免这种写法select ename from emp where substr(ename,1)=‘KING‘;
length 长度函数
SQL> select length(ename),ename from emp;
LENGTH(ENAME) ENAME
------------- ----------
5 SMITH
5 ALLEN
4 WARD
5 JONES
6 MARTIN
5 BLAKE
5 CLARK
5 SCOTT
4 KING
6 TURNER
5 ADAMS
lengthb,lengthc,length2,length4区别详见官网，中文能看见区别
SQL> select length(ename),lengthb(ename),lengthc(ename) from emp where deptno=10;
LENGTH(ENAME) LENGTHB(ENAME) LENGTHC(ENAME)
------------- -------------- --------------
5 5 5
4 4 4
6 6 6
instr查找指定字符在哪里出现
SQL> select instr(ename,‘A‘), ename from emp;
INSTR(ENAME,‘A‘) ENAME
---------------- ----------
0 SMITH
1 ALLEN
2 WARD
0 JONES
2 MARTIN
3 BLAKE
3 CLARK
0 SCOTT
0 KING
0 TURNER
1 ADAMS
SQL> select ename from emp where instr(ename,‘A‘)=1;
ENAME
----------
ALLEN
ADAMS
SQL> select ename,instr(ename,‘LL‘) from emp;
ENAME INSTR(ENAME,‘LL‘)
---------- -----------------
SMITH 0
ALLEN 2
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 0
rpad右边填充字符
SQL> select rpad(deptno,10,‘ ‘) deptno,dname,loc from dept;
DEPTNO DNAME LOC
---------------------------------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
lpad左边填充
col dname just right 行标题对齐方式
trim(both|leading|trailing ‘char‘ from expr)
SQL> select trim(‘A‘ from ‘AAABBBAAA‘) t from dual;
T
---
BBB
ltrim(expr,‘char‘),rtirm 版本大于等于10g才会有的
replace(expr,old,new);替换字符
chr(10)换行符 官方文档sql第五章
数字函数
round() 近似值
trunc() 截取
mod() 取余
abs() 绝对值
ceil()取整
4 5 6 . 7 8 9
-3 -2 -1 0 1 2 3
SQL> select round(456.789,2),trunc(456.789,2)from dual;
ROUND(456.789,2) TRUNC(456.789,2)
---------------- ----------------
456.79 456.78
SQL> c/2/-1
1* select round(456.789,-1),trunc(456.789,2)from dual
SQL> l
1* select round(456.789,-1),trunc(456.789,2)from dual
SQL> /
ROUND(456.789,-1) TRUNC(456.789,2)
----------------- ----------------
460 456.78
日期函数
default value DD-MON-RR
****0-49,50-99 加减一
current last 2 number ---<50
last 2 number ---0-49<50
50-99 -1 世纪
--->=50
>=50
<50 0-49 +1
SQL> select sysdate from dual;
SYSDATE
---------
19-JUL-14
2014 14
2014 14 2014
**SQL> show parameter nls_date_format;
**SQL> alter session set nls_date_format=‘yyyy-mm-dd‘
日期可以进行算数运算，也可以进行round,trunc运算，trunc(sysdate,‘day‘)
months_between
SQL> select ename,months_between(sysdate,hiredate) s from emp;
ENAME S
---------- ----------
SMITH 403.07913
ALLEN 400.982356
WARD 400.91784
JONES 399.563001
MARTIN 393.724291
BLAKE 398.595259
CLARK 397.337195
SCOTT 327
KING 392.07913
TURNER 394.369453
ADAMS 325.885582
add_months()
next_day()
last_day()
***注意日期的格式***
转换函数
=显式转化
to_char(date,‘format_model‘)
SQL> select to_char(hiredate,‘yyyy-mm-dd‘) from emp;
TO_CHAR(HI
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
SQL> l
1* select to_char(hiredate,‘yyyy-mm-dd‘)from emp
SQL> c/yyyy/fmyyyy
1* select to_char(hiredate,‘fmyyyy-mm-dd‘)from emp
SQL> /
TO_CHAR(HI
----------
1980-12-17
1981-2-20
1981-2-22
1981-4-2
1981-9-28
1981-5-1
1981-6-9
1987-4-19
1981-11-17
1981-9-8
1987-5-23
SQL> c/dd/fmdd
1* select to_char(hiredate,‘fmyyyy-mm-fmdd‘)from emp
SQL> /
TO_CHAR(HI
----------
1980-12-17
1981-2-20
1981-2-22
1981-4-02
1981-9-28
1981-5-01
1981-6-09
1987-4-19
1981-11-17
1981-9-08
1987-5-23
9 0 $ L . ,特殊符号
SQL> select ename,to_char(sal,‘9,999.00‘) sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SCOTT 3,000.00
KING 5,000.00
SQL> select ename,to_char(sal,‘9G999D00‘) sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SQL> select ename,to_char(sal,‘$9G999D00‘) sal from emp;
ENAME SAL
---------- ----------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
JONES $2,975.00
MARTIN $1,250.00
BLAKE $2,850.00
CLARK $2,450.00
SCOTT $3,000.00
SQL> select to_char(16,‘x‘) from dual;
TO
--
##
to_number(char[,‘format_model‘]);
SQL> select to_number(‘f‘,‘xxx‘) from dual;
TO_NUMBER(‘F‘,‘XXX‘)
--------------------
15
================================================
注意以下两个区别，列的对齐方式
SQL> select ename,sal from emp where deptno=10;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
SQL> c/sal/to_char(sal) sal/
1* select ename,to_char(sal) sal from emp where deptno=10
SQL> l
1* select ename,to_char(sal) sal from emp where deptno=10
SQL> /
ENAME SAL
---------- ----------------------------------------
CLARK 2450
KING 5000
MILLER 1300
==================================================================
to_date(char[,‘format_model‘]);
SQL> select to_date(‘19811111‘,‘yyyy/mm/dd‘) from dual;
TO_DATE(‘
---------
11-NOV-81
空值函数
nvl(expr1,expr2)
SQL> select ename,sal,comm,sal+nvl(comm,0) from emp;
ENAME SAL COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
nvl2(expr1,expr2,expr3)
SQL> select nvl2(2,1,0) from dual;
NVL2(2,1,0)
-----------
1
SQL> c/2/null
1* select nvl2(null1,0) from dual
SQL> /
SQL> select nvl2(null,1,0) from dual;
NVL2(NULL,1,0)
--------------
0
nullif(expr1,expr2)
coalesce(expr1,expr2,...,exprn)
不停地查找，返回不为空的值
=隐式转化 oracle服务器自动完成
字符串转number
字符串转日期
条件表达式(分支函数)
case expr when ... then ...
else ...
end
SQL> select ename,deptno,sal,case deptno when 10 then sal*1.1
2 when 20 then sal*1.3
3 else sal
4 end isal from emp;
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 1040
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3867.5
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SQL> l
1 select ename,deptno,sal, case when deptno=10 then sal*1.1
2 when deptno=20 then sal*1.2
3 else sal
4* end isal from emp
SQL> /
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 960
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3570
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SCOTT 20 3000 3600
decode 可读性没有case好
SQL> select ename,deptno,sal,decode(deptno,10,sal*1.1,20,sal*1.2,sal) isal from emp;
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 960
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3570
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SCOTT 20 3000 3600
SQL> select to_char(hiredate,‘yyyy‘),count(*) from emp group by to_char(hiredate,‘yyyy‘);
TO_C COUNT(*)
---- ----------
1987 2
1980 1
1982 1
1981 10
行转列
SQL> select sum(decode(to_char(hiredate,‘yyyy‘),‘1980‘,1)) "1980",sum(decode(to_char(hiredate,‘yyyy‘),‘1981‘,1)) "1981" from emp;
1980 1981
---------- ----------
1 10
多行函数：每个行组集返回一个结果
分组函数 group by having限制分组个数
统计函数不统计空值
SQL> select count(comm) from emp;
COUNT(COMM)
-----------
4
SQL> select count(nvl(comm,0)) from emp; //效率低下
COUNT(NVL(COMM,0))
------------------
14
avg(),min(),max(),sum()
SQL> select job,min(sal) from emp group by job;
JOB MIN(SAL)
--------- ----------
CLERK 800
SALESMAN 1250
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
SQL> select job,min(sal) from emp group by job having min(sal)>2000;
JOB MIN(SAL)
--------- ----------
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
多表查询语句(实践必须掌握优化技巧)
等值连接与不等值连接
A.外连接
左外连接
右外连接
B.自然连接 要求列名相同、数据类型相同
SQL> select ename,dname from emp natural join dept;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
列名不相同用以下语句
SQL> select ename,dname from emp join dept on emp.deptno=dept.id;
数据类型不同，做自然连接会报错，可以修改为以下
select ename,dname from emp join dept using(deptno);
join on ,join using,natural join n-1个连接条件
full join
cross join 交叉连接
子查询
在主查询执行之前，子查询会执行一次，子查询结果被用于主查询
SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
----------
KING
SQL> select ename from emp where sal in (select sal from emp where sal>2999);
ENAME
----------
FORD
SCOTT
KING
单行子查询
多行子查询 子查询结果为空，可能导致查询结果为空需要注意
any all in
SQL> select ename,sal from emp where sal > any(2500,3000);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000
SQL> c/any/all
1* select ename,sal from emp where sal > all(2500,3000)
SQL> /
ENAME SAL
---------- ----------
KING 5000
SQL> c/>/<
1* select ename,sal from emp where sal < all(2500,3000)
SQL> /
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
9 rows selected.
SQL> c/all/any
1* select ename,sal from emp where sal < any(2500,3000)
SQL> /
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
11 rows selected.
****some与any 等价****
exists
SQL> select ename from emp where empno in (select mgr from emp);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
性能低下，6次循环
SQL> select ename from emp e where exists (select 1 from emp where mgr=e.empno);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
性能高，一次全表扫描，此处的1为标签，
SQL> c/where/where not
1* select ename from emp e where not exists (select 1 from emp where mgr=e.empno)
SQL> /
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
set操作
交并补
union 去除重复行,排序,升序,union all 不去除重复行,不排序
intersect 交集,没有重复
minus 补集A-B
表达式在数量上匹配，数据类型前后必须一致
括号可变优先级，order by 语句可以出现在语句最后
SQL> select deptno,null job,max(sal) from emp group by deptno
2 union all
3 select null ,job,max(sal) from emp group by job;
DEPTNO JOB MAX(SAL)
---------- --------- ----------
30 2850
20 3000
10 5000
CLERK 1300
SALESMAN 1600
PRESIDENT 5000
MANAGER 2975
ANALYST 3000
以上是构造伪列
以下是10g以后才会支持的
SQL> c/setss/sets
1* select deptno,job,max(sal) from emp group by grouping sets(deptno,job)
SQL> /
DEPTNO JOB MAX(SAL)
---------- --------- ----------
CLERK 1300
SALESMAN 1600
PRESIDENT 5000
MANAGER 2975
ANALYST 3000
30 2850
20 3000
10 5000
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。