lucnce oracle_【oracle ocp知识点一】

最新推荐文章于 2022-05-04 11:20:07 发布

weixin_39887846

最新推荐文章于 2022-05-04 11:20:07 发布

阅读量81

点赞数

文章标签： lucnce oracle

本文链接：https://blog.csdn.net/weixin_39887846/article/details/111490717

版权

1.怎样确定数据库是否启动

su - oracle

ps -ef |grep ora_|head -2

两种关系数据库是ora或者是自己主动存储管理的asm开头的，

查看进程能够知道数据库实例至少已经启动。可是数据库是否挂载，是否打开。不得而知

asm

crs_stat -t

updb.db online

srvctl status database -d updb

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; keyword 考试一般选择此项

select distinct(deptno) from emp; 函数

select unique deptno from emp; keyword

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

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;

---

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_number(char[,'format_model']);

SQL> select to_number('f','xxx') from dual;

TO_NUMBER('F','XXX')

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

================================================

注意下面两个差别，列的对齐方式

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)

-----------

SQL> c/2/null

1* select nvl2(null1,0) from dual

SQL> /

SQL> select nvl2(null,1,0) from dual;

NVL2(NULL,1,0)

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

nullif(expr1,expr2)

coalesce(expr1,expr2,...,exprn)

不停地查找，返回不为空的值

=隐式转化 oracleserver自己主动完毕

字符串转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)

-----------

SQL> select count(nvl(comm,0)) from emp; //效率低下

COUNT(NVL(COMM,0))

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

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

weixin_39887846

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
lucnce oracle_【oracle ocp知识点一】

1.怎样确定数据库是否启动su - oracleps -ef |grep ora_|head -2两种关系数据库是ora或者是自己主动存储管理的asm开头的，查看进程能够知道数据库实例至少已经启动。可是数据库是否挂载，是否打开。不得而知asmcrs_stat -tupdb.db onlinesrvctl status database -d updbdbsqlplus -s / as sysdb...
复制链接

扫一扫