Oracle常用操作命令

1.Oracle支持的主要数据类型

类型	含义
CHAR（length)	存储固定长度字符串。length指定长度，存储的字符串长度小于指定长度用空格填充。默认长度1，最长不超过2000字节
VARCHAR2(length)	存储可变长度字符串。length指定最大存储长度，默认长度1，最长不超过4000字符
NUMBER(p,s)	可以存储浮点数和整数，p指定数字的最大位数（小数包括证书部分、小数部分和小数点，默认是38位），s指定小数位数
DATE	存储日期和时间
TIMESTAMP	不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区
CLOB	存储大的文本，比如存储非结构化的XML文档
BLOB	存储二进制对象，如图形、视频、声音等

2.命令窗口下查看表结构

DESC 表名;

3.增加新字段

ALTER TABLE student ADD username VARCHAR2(20);

4.添加注释

--给表添加注释
COMMENT ON TABLE student IS '学生表';
-- 给字段添加字段
comment on column student.username is '用户名';

5.修改字段

-- 修改数据类型
ALTER TABLE student MODIFY username VARCHAR2(256);
-- 修改字段名
alter table student rename column username to StuName;

6.删除字段

ALTER TABLE student DROP COLUMN username;

7.查询表中前10行记录

SELECT * FROM student WHERE ROWNUM <= 10;
或
SELECT * FROM student WHERE ROWNUM != 11;

注：ROWNUM的使用只能用<、<=和!=等比较运算符，不能用>、>=等运算符，这是因为ROWNUM从自然数1开始，条件“ROWNUM=1”是成立的，其可以作为WHERE子句的条件并返回表的第1行记录，但“ROWNUM=n（n>1）”是不成立的，不能作为条件直接写在WHERE子句中，否则无法返回正确结果。
使用ORDER BY 语句时，先排序后加ROWNUM条件取行数。

8.消除查询结果重复记录：DISTINCT关键字

SELECT DISTINCT username FROM student;

9.使用ESCAPE定义转义字符

-- 查询10%、20%、30%......的百分数
SELECT * FROM student WHERE sc like '%0!%' ESCAPE '!';
-- 转义字符不仅可以使“!”，也可以是“\”、“a”等

10.COUNT函数统计记录数

SELECT COUNT(字段名) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
-- COUNT和DISTINCT一起使用时，关键字DISTINCT应写在COUTN()函数内
SELECT COUNT(DISTINCT(username)) FROM student;

11.SUM函数求和

SELECT SUM(salary) FROM person;

12.AVG函数求平均值

SELECT AVG(salary) FROM person;

13.MAX函数返回最大值

SELECT MAX(salary) FROM person;

14.MIN函数返回最小值

SELECT MIN(salary) FROM person;

注：COUNT、SUM、AVG和MAX、MIN等聚合函数，不能在WHERE子句中使用，否则将出现错误。

15.REPLACE函数查找并替换字符串

-- 查找stept列中所有出现了“计算机”字符串的记录，并将其全部替换为“computer”
SELECT REPLACE(sdept,'计算机','computer') FROM student;

16.LENGTH函数返回字符串长度

SELECT LENGTH(username) FROM student;

17.ROUND函数确定精度:四舍五入

--查询平均工资，并精确到小数点后2位
SELECT ROUND(AVG(salary),2) FROM person;

ceil(n)函数，向上取整，取大于等于数值n的最小整数；
floor(n)函数，向下取整，取小于等于数值n的最大整数

CEIL(3.5)=4
FLOOR(3.5)=3

TRUNC(number[,decimals])函数处理数字，保留decimals位小数，默认0，其余的直接截去掉，而不像ROUND函数进行四舍五入

TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
-- 负数表示为小数点左边指定位数后面的部分截去

TRUNC（date,[fmt]）处理日期

trunc(sysdate,'yyyy') --返回当年第一天.2020-1-1
trunc(sysdate,'mm') --返回当月第一天.2020-9-1
trunc(sysdate,'d') --返回当前星期的第一天.

18.ADD_MONTHS函数,日期运算

ADD_MONTHS(d,n):d表示日期，n表示要加的月数。函数表示在某个日期d上，加上是定的月数n，返回计算后的新日期。
注：ADD_MONTHS函数的参数n应当是整数，给出小数时，正数被截为小于该数的最大整数，负数被截为大于该数的最小整数。
示例：SELECT ADD_MONTHS(SYSDATE,12) FROM dual;

19.类型转换
（1）TO_CHAR日期类型转换为字符串，实现日期格式化

-- 年/月/日
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD') FROM dual;
-- 年/月/日12小时制时间
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH:MI:SS') FROM dual;
-- 年/月/日24小时制时间
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM dual;
-- 年
SELECT TO_CHAR(SYSDATE, 'yyyy') AS '年' FROM dual;
-- 月
SELECT TO_CHAR(SYSDATE, 'mm') AS '月' FROM dual;
-- 季
SELECT TO_CHAR(SYSDATE, 'q') AS '季' FROM dual;
-- 输出汉字星期几，例：星期一
SELECT TO_CHAR(SYSDATE, 'DAY') AS '星期几' FROM dual;
-- 输出数字星期几，取值（1~7），1对应星期日
SELECT TO_CHAR(SYSDATE, 'D') AS '星期几' FROM dual;
-- 返回一年中的第几天
SELECT TO_CHAR(SYSDATE, 'DDD') FROM dual;
-- 自定义格式
SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"') FROM dual;

（2）TO_CHAR数字类型转换为字符串类型

TO_CHAR(x[,fmt])

（3）T0_DATE,字符串转换为日期类型

（4）字符串类型转换为数字类型

to_number(x[,fmt])
-- 计算两个日期相差的天数
-- FLOOR函数取小于等于该相差值的最大整数
select floor(to_number(sysdate - to_date('2020-09-06','YYYY-MM-DD'))) as spandays from dual;
-- 计算两个日期相差的月数
-- CEIL函数获取大于等于该相差月份的最小整数
select ceil(months_between(sysdate,to_date('2020-08-15','YYYY-MM-DD'))) as spanmonths from dual;
-- 计算两个日期相差的年数
select floor(to_number(sysdate - to_date('2020-09-06','YYYY-MM-DD'))/365) as spanyears from dual;

（5）EXTRACT，实现特定日期时间的提取

EXTRACT(fmt FROM d)
-- 参数fmt有YEAR、MONTH、DAY、HOUR、MINUTE、SECON 6种。而YEAR、MONTH、DAY可以为DATE类型匹配，也可以与TIMESTAMP类型匹配；但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配。
--在使用EXTRACT函数提取当前系统时间的小时值的时候，HOUR匹配的结果中没有加上时区，因此在中国运行的结果小8小时。
select sysdate 当前时间,
extract(year from sysdate) 年份,
extract(month from sysdate) 月份,
extract(day from sysdate) 日,
extract(hour from systimestamp) 小时,
extract(minute from systimestamp) 分,
extract(second from systimestamp) 秒 
from dual;

20.instr字符查找函数，返回索引位置
返回要截取的字符串在源字符串中的位置，只检索一次。

格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)

select instr('1234321','3') from dual; --返回结果：3   第一次出现“3”的位置
select instr('1234321','43') from dual; --返回结果：4   同时出现“43”的位置

判断某个字符是否在字符串中存在:

-- 第一种
select * from students where instr(username,'王')>0;
-- 第二种
select * from students where contains(username,  '王');
-- 注：使用contains需要列username建立索引，否则会报错。 
-- 第三种
select * from students where username like'%王%';

 

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

select instr('1234321','3',2,1) from dual; -- 返回3，从第2个位置开始检索，第一次出现3的位置
select instr('1234321','3',2,2) from dual; -- 返回5，从第2个位置开始检索，第二次出现3的位置

21.decode函数，按条件返回值

语法：decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值)
含义：
IF条件=值1THEN
RETURN(返回值1)
ELSIF条件=值2THEN
RETURN(返回值2)
......
ELSIF条件=值nTHEN
RETURN(返回值n)
ELSE
RETURN(缺省值)
ENDIF

21.substr字符截取函数

格式： substr(string string, int a[, int b]);
 参数 a 表示截取字符串的开始位置
 参数b表示要截取的长度,b不写表示从第a个字符开始截取后面所有的字符串

22.将查到的一列数据值用逗号连接起来

select wmsys.wm_concat(username) FROM students;-- 将username列记录值用逗号连接起来

23.1 NVL函数

格式：NVL(expr1,expr2)
含义：如果第一个参数expr1为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

23.2 NVL2函数

格式：NVL2(expr1,expr2, expr3)
含义：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值

23.3 NULLIF函数

格式：NULLIF(exp1,expr2)
含义：如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

23.4 Coalesce函数

格式：Coalesce(expr1, expr2, expr3….. exprn)
表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。返回表达式中第一个非空表达式，如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。