Oracle笔记1

第一天

一、Oracle(DDL语言)

1. 撤消权限：revoke 权限｜角色 from 用户；
2. 赋予权限：grant 权限｜角色 to 用户；
3. 删除用户：drop user 用户名；

 

二、SQL的五种语言

1．DDL数据定义语言：create, drop, alter

2．DML数据操作语言：insert, update , delete

3．DQL数据查询语言：select...from...where...order by ...ASC|DESC...group by(分组)...

4．DCL数据控制语言：grant授权, revoke撤权

5．TCL事物控制语言：commit提交, rollback回滚, savepoint保存点

 

三、Oracle数据类型

1．字符型

（1）char() 固定长度

（2）varchar2()  可变长度

2．数字型

（1）number（p,s） p代表数字的最大位数，s代表小数的位数

3．日期型

（1）date  年月日时分秒

（2）timestamp 时间戳 包含时区 可精确到秒后6位

4．文件型

（1）clob 文本文件 :储存大的文本，比如非结构化的XML文档

（2）blob 二进制文件：储存二进制对象。    

 

四、SQL语句

1．select * from tab;查看当前用户的表

2．desc 表名; 查看该表的结构

 

 

第二天

一、列级约束和表级约束

1．列级约束 ：not null

2．表级约束：primary key, foreign key, check, unique

 

二、修改表

1．增加列：alter table 表名 add 列名 数据类型[约束]

2．修改列: alter

3.查看有哪些用户select * from all_users;

 

三、高级查询

1．消除重复的行

（1）select distinct deptno from emp;

2．判断是否为空值  is null

（1） select * from emp where comm is not null;

 

四、集合运算

1．Intersect交集

2．union、union all 并集

3．minus 补集

 

五、多行插入数据

1．Insert into 表名

select 字段1，字段2，字段3  from dual

union

select 字段1，字段2，字段3  from dual

union

select 字段1，字段2，字段3  from dual

2．insert all

into 表名(字段1，字段2...)  values(1,2....)

into 表名(字段1，字段2...)  values(1,2....)

.......

select * from dual;  

 

 

 第三天

一、多表连接查询

1. 内连接查询

(1) inner join on

(2) Where t1.x=t2.y and t2.y=t3.z

1. 外链接查询

(1) Left join no 左连接

(2) Right join no右连接

1. 完全连接查询：...full join...on...
2. 多行子查询: any 、all、 in

 

二、伪列

1．Rowid 表中行的物理地址 （插入记录时生成）

2．Rownum 查询结果集中行的编号（查询数据时生成）

 

三、导出/倒入数据

1．在cmd中：exp 输出数据

2．Imp倒入数据

 

四、Oracle函数

1、字符函数

 

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。

 

函数	说明
ASCII(x)	返回字符x的ASCII码。
CONCAT(x,y)	连接字符串x和y。
INSTR(x, str [,start] [,n)	在x中查找str，可以指定从start开始，也可以指定从第n次开始。
LENGTH(x)	返回x的长度。
LOWER(x)	x转换为小写。
UPPER(x)	x转换为大写。
LTRIM(x[,trim_str])	把x的左边截去trim_str字符串，缺省截去空格。
RTRIM(x[,trim_str])	把x的右边截去trim_str字符串，缺省截去空格。
TRIM([trim_str FROM] x)	把x的两边截去trim_str字符串，缺省截去空格。
REPLACE(x,old,new)	在x中查找old，并替换为new。
SUBSTR(x,start[,length])	返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。

表1  字符函数

 

示例	示例结果
SELECT ASCII('a') FROM DUAL	97
SELECT CONCAT('Hello', ' world') FROM DUAL	Hello world
SELECT INSTR('Hello world'，'or') FROM DUAL	8
SELECT LENGTH('Hello') FROM DUAL	5
SELECT LOWER('hElLO') FROM DUAL;	hello
SELECT UPPER('hello') FROM DUAL	HELLO
SELECT LTRIM('===HELLO===', '=') FROM DUAL	HELLO===
SELECT '=='||LTRIM('   HELLO===') FROM DUAL	==HELLO===
SELECT RTRIM('===HELLO===', '=') FROM DUAL	===HELLO
SELECT '='||TRIM('   HELLO   ')||'=' FROM DUAL	=HELLO=
SELECT TRIM('=' FROM '===HELLO===') FROM DUAL	HELLO
SELECT REPLACE('ABCDE','CD','AAA') FROM DUAL	ABAAAE
SELECT SUBSTR('ABCDE',2) FROM DUAL	BCDE
SELECT SUBSTR('ABCDE',2,3) FROM DUAL	BCD

表2  字符函数示例

 

2、数字函数

 

数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

 

函数	说明	示例
ABS(x)	x绝对值	ABS(-3)=3
ACOS(x)	x的反余弦	ACOS(1)=0
COS(x)	余弦	COS(1)=1.57079633
CEIL(x)	大于或等于x的最小值	CEIL(5.4)=6
FLOOR(x)	小于或等于x的最大值	FLOOR(5.8)=5
LOG(x,y)	x为底y的对数	LOG(2,4)=2
MOD(x,y)	x除以y的余数	MOD(8,3)=2
POWER(x,y)	x的y次幂	POWER(2,3)=8
ROUND(x[,y])	x在第y位四舍五入	ROUND(3.456,2)=3.46
SQRT(x)	x的平方根	SQRT(4)=2
TRUNC(x[,y])	x在第y位截断	TRUNC(3.456,2)=3.45

表3  数字函数

 

 

3、日期函数

 

日期函数对日期进行运算。常用的日期函数有：

1. ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。
2. LAST_DAY(d)，返回指定日期当月的最后一天。

 

4、转换函数

TO_CHAR对日期的处理

代码解析：

SQL> SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date"  ①

  2  FROM DUAL;

 

date

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

2009年08月11日 12:06:00

 

 

 

5、其他单行函数

 

1. NVL(x,value)

如果x为空，返回value，否则返回x。

1. NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2。

 

 

 

 

6、聚合函数

 

聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。

 

名称	作用	语法
AVG	平均值	AVG(表达式)
SUM	求和	SUM(表达式)
MIN、MAX	最小值、最大值	MIN(表达式)、MAX(表达式)
COUNT	数据统计	COUNT(表达式)

 

AVG函数下的分组查询

SQL> SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;

 

DEPTNO   AVG(SAL)

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

    30    1566.66666

    20    2175

    10    2916.66666