Oracle-ocp-051

查看Oracle用户可以使用那些表数据字典表(管理用户所有表)

desc user_tables;(查看可以操作的表)
select tables_name from user_tables;(SCOTT用户可以使用四个表)
字符类型都是左对齐，数字类型的都是右对齐
空值是不可用的、未分配的、未知的或不使用的值空值不同于零或空格

设置显示行间距

set linesize 200;宽度为200

set pagesize 50;每页现实50行
create table test(hiredate varchar(20));创建表

查看当前用户可以使用的表

select table_name from user_tables;

标题的默认设置

字符和日期列标题的对齐方式为：左对齐
数字列标题的对齐方式：右对齐
默认的标题显示方式：大写
空值不等同于零或者空格
空值带入四则运算仍然还是空值

定义别名

列别名具有以下特征
可重命名标题、有助于计算、紧跟在列名后（列名和别名之间也可以加上可选关键词AS）如果别名包括空格或特殊字符、或者区分大小写则需要双引号

链接字符串

select ename||job from emp;
把两个列名合并一块显示出来

重复行

select distinct deptno from emp;
distinct (去重)

只显示空值

select * from emp where mgr is null ;

逻辑判断

AND 如果两个条件都为真，则返回true(两个条件都为真时则返回值)
OR 如果其中一个条件为真。则返回true
NOT 如果条件为假则返回true

sqlplus 下清屏 clear screen

优先级规则

运算符	含义
加减乘除	算数运算符
管道符号	链接运算符
where	比较运算符
空值	IS 、[not]、 null、like、[not]、in
5	[not] DETWEEN（在什么什么之间、在什么什么之外）
！=	不等于
条件为假则返回true	NOT 逻辑条件
和	AND 逻辑条件
或	OR 逻辑条件

使用ORDER BY 句子

使用order by 子句可以对检索进行排序
ASC:升序 （默认升序）从小到大
DESC:降序
order by 子句位于select 语句的最后

排序可以用别名或者数字
select ename，sal gz from emp order by 2;
结果就是工资为降序排列
也可按章多个列进行排序
select ename,sal,deptno from emp order by 3,2 desc;
已sal降序排列（从大到小）

关闭数据库和开启

shutdown normal （关闭）
startup mount （开启）

替代变量

使用单与号（&）及双（&&）（多次）可代替临时存储值where条件order by子句
列表达式、表名、整个select语句
缓存在内存中
指定列名、表达式和文本
变量字符类型要加单引号

使用DEFIN 命令

使用define命令可以创建变量并为其赋值
使用undefine 可以删除变量
先进行赋值
define abc=20
select * from emp where depton=&abc;(调用赋值的变量)
删除变量undefine abc=20
再出调用则搜懂输入

SQL 函数

单行函数（每一个返回一个结果）
多行函数（每一行集返回一个结果）
处理数据项、接受参数并返回一个值、对每个返回进行处理、为每个行返回一个结果、可能会修改数据类型、可以嵌套、接受参数、这些参数可以是列或表达式
单行函数
字符、数字、常规、日期、转换

字符函数

大小写转化函数
LOWER、UPPER、INITCAP、
字符处理函数
CONCAT、SUDSTR、LENGTH、

函数	结果
LOWER（‘SQL Course’）	(sql course)全部小写
UPPER（‘SQL Course’）	(SQL COURSE)全部大写
INITCAP(‘SQL Course’)	(Sql Course)首字母大写

dual 伪表（存在内存中的不存在数据库中）

字符处理

函数	结果
CONCAT（‘Hello’,'World’）	HelloWorld(拼接在一块)支持两个字符串
SUDSTR (‘HelloWorld’,1,5)	Hello(1到5)字符串截取
LENGTH (‘HelloWorld’)	10(字符串长度)
INSTR(‘HelloWorld’,‘W’)	6 (定位字符位置)
LPAD(salary,10,’*’)	******24000(左填充)
RPAD(salary,10,’*’)	24000******(右填充)
REPLACE (‘JACK and JUE’,‘J’,‘BL’)	BLACK and BLUE
TRIM('H’FROM ‘HelloWorld’)	elloWorld(去除指定字符)

数字函数

ROUND:将值舍入到指定的小数位(-1精确的个位)
select round(45.923,2) from dual;
返回45.92
TRUNC:将值截断到指定的小数位
返回40
MOD:返回除法运算的余数
select mob(10,3) from dual;
返回1

处理日期

oracle DB 以内部数字格式存储日期：世纪、年、月、日、小时、分钟、秒
默认的日期格式DD(月2位字符)-MON(月1月到12月)-RR(两位的寄点法)世纪21世纪还是20世纪
通过仅指定年份的后两位可以在20世纪存储21世纪的日期、也可以在21世纪存储20世纪的日期

RR日期格式

当前年份	指定的日期	RR格式	YY格式
1995	27-OCT-95	1995	1995
1995	27-OCT-17	2017	1997
2001	27-OCT-17	2017	2017
2001	27-OCT-95	1995	2095

• 如果当前年份的两位数为0-49返回日期当前世纪中
• 如果当前年份的两位数为50-99返回日期当前世纪的下一个世纪中
• 如果指定的两位数年份为0-49返回日期当前世纪中
• 如果指定的两位数年份为50-99返回日期当前世纪的下一个世纪中

sysdate 是返回一下对象的函数：-日期-时间

日期处理函数

函数	结果
MONTHS_DETWEEN	两个日期之间的月数
ADD_MONTHS	将日历月添加到日期
NEXT_DAY	指定日期之后的下一个日期
LAST_DAY	当月最后一天
ROUND	舍入日期
TRUNC	截断日期
MONTHS_BETWEEN(‘01-sep-95’,‘11-jan-94’)	19.6774194
ADD_MONTHS(‘31-jan-96’,1)	29-feb-96
NEXT_DAY(‘01-SEP-95’,FRIDAY)	08-SEP-95
LAST_DAY(‘01-FEB-95’)	28-FEB-95
ROUND(SYSDATE,‘MONTH’)(month月)假设sysdate=‘25-jul-03’	01-AUG-03(大于等于15则月份进一)
ROUND(SYSDATE,‘YEAR’)(year年)	01-JAN-04(年份进行进一（月）大于等于6则年份进一月份大于等)
TRUNC(SYSDATE,‘MONTH’)	01-JUL-03
TRUNC(SYSDATE,‘YEAR’)	01-JAN-03

转换函数和条件表达式

• 隐式数据类型转换
• 显示数据类型转换
• 数字类型、字符类型、时间类型的转换

使用TO_CHAR 函数处理日期

必须放在单引号内
区分大小写
可以包含任何有效的日期格式元素
具有一个fm元素、用于删除填充的空格或隐藏前导零
与日期值之间用逗号分隔

元素	结果
YYYY	用数字表示的完整年份
YEAR	用英文表示的年份
MM	月份的俩位数值
MONTH	月份的完整名称
MON	月份的三位字母缩写
DY	一周中某日的三个字母缩写
DAY	一周中某日的完整名称
DD	用数字表示的月份中某日
9	代表一个数字
0	强制显示0
$	放置一个浮动的美元符号
L	使用浮动的本地货币符号
.	显示小数点
，	显示作为千位指示的逗号

嵌套函数

• 单行函数可以嵌套到任意层
• 嵌套函数的计算顺序是从最内层到最外层
• 嵌套函数从内到外

常规函数、且合适使用空值的场合
将空值转换为实际值、可以使用的数据类型为日期、字符和数字、数据类型必须匹配：
NVL(expr1,exrp2)
NVL2(EXPR1,EXRP2,EXRP3)
NULLIF(expr1,expr2)

组函数报告聚集数据

AVG	计算平均值
count	一个字段有多少行
max	最大值
min	最小值
sum	一个字段的和
count	显示行数
distinct	去除重复行

创建数据组

group by 分组
select deptno,avg(sal) from emp group by deptno;
分组求平均数

• 多列分组

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

• HAVING子句限定组结果

select deptno,max(sal) from emp group by hacing max(sal)>=3000;

• 使用联接显示多个表的数据

使用outer 联接查询通常不满足联接条件的数据
生成两个或多个表中所有行的笛卡尔积

• 联接类型

自然链接
natural join 子句
using 子句
outer链接（外链接）
left outer join 
right outer join
full outer join
交叉链接

• 创建自然链接

natural join 子句以两个表中具有相同名称的所有列为基础

它从两个表中选择在所有匹列中具有相同值得哪些行
如果名称相同的列具有不同的数据类型，则返回一个错误

• select deptno from dept natural join emp

两个查询内容一样则显示公有值

• 使用using语句创建链接

如果多个列具有相同的名称，但数据类型不匹配，请使用using子句指定等值联接的列
当有多个列想匹配时，使用using 子句可仅与一列相匹配
natural loin 和using 语句是互相排斥的。

• select ename,deptno feom emp join dept using(deptno);
• 在using子句中使用表别名

不要对using 子句中使用的列加以限定
如果在sql语句的另一个位置使用了同一列，则不要对其设置别名

• 使用ON子句创建链接

自然联接的基本联接条件是对具有相同名称的所有列进行等值链接
使用ON子句可指定任意条件或指定要联接的列
联动条件独立于其他搜索条件
使用ON子句可以代码易于理解

inner联接与outer联接

在sql:1999中，如果两个表的联接只返回相匹配的行，则称联接inner
两个表直接的联接不仅返回inner联接的结果，还返回左（或右）表中不匹配的行，则称为该联接为左（或右）outer链接
两个表之间的联接不仅返回inner链接的结果，还返回左和右联接的结果，则称该链接为完全outer联接

• 子查询

先执行子查询（内部查询），再执行主查询（外部查询）主查询会使用子查询的结果

• 当行子查询

仅返回一行

运算符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

• 多行子查询
• 集合操作符

联合运算
交集预算
普及运算
select 列表中的表达式在数量上必须匹配
第二个查询中每一列的数据类型必须与第一个查询中列的数据类型相匹配
可以使用括号更改执行顺序
order dy 子句只能出现在语句的末尾
Oracle server 和集合运算符
除非使用union all 运算符、否则会自动删除重复行
第一个查询汇总的列名将显示在结果中
除非使用union all 运算符，否则默认情况下输出按照升序进行排序

• union 运算符

uninon 运算符从两个查询中分会不包括重复行的那些行

数据操纵语句

DML语句

增删改查

• 增加 insert into
• 修改 update 必须要跟条件
• 删除行 delete 必须要跟条件
  delet from (表) where=(条件)

DDL语句

数据库事务处理由于以下语句组成
用于对数据进行一次一致更改的DML语句
一条DDL语句一条数据控制语句（DCL）语句

在执行第一条DML SQL 语句时开始
在发生下列事件之一时结束：
发出COMMIT或ROLLDACK语句
执行DDL或DCL语句（自动提交）
用户推出SAQL Developer或SQL*Plus 系统崩溃
使用commit和rolldack提交、回滚
确保数据的一致性

数据库事务处理

使用savepoint （a） 语句可在当前事务处理中创建一个标记
使用rollback to savepoint 语句可以回退到该标记
先还原B 在还原A
如先还原A则B也消失

执行COMMIT之后的数据状态

数据更改已保存在数据库中
已改写以前的数据状态
搜游用户都可以查询看的结果
受影响行上的锁已被释放、其他用户可以进行处理
搜游保存都被清除

执行ROLLBACK操作之后的数据状态

数据已还原到以前状态
受影响行上的锁一杯释放
delete from copy_emp;
rollback;