oracle sql 基础知识

下一篇: oracle sql 基础知识2.

基础知识

1. Number(4) 最大长度为4的数值类型
2. varchar2(10) 最大长度为10的字符串, varchar2用于存储可变长度的字符串,varchar2把空字符串等同于null处理, 而varchar仍按照空串处理, 建议oracle中使用varchar2
3. number(7,2) 数值类型 整数位占5位, 小数位占2位, 一共7位
4. date 时间类型
5. clob 大对象类型，表示大文本数据类型 可存4G
6. blob 大对象类型， 表示二进制数据， 可存4G

简单查询

1. 查询所有列数据

	select * from emp;
	select t.rowid, t.* from emp t;  -- rowid 可以直接编辑plsql中的数据

2. 别名使用

	select empno as 员工编号, ename as 员工姓名 from emp;  --一般不使用中文为别名
	select empno eno, ename en from emp; --常用

3. 去重合并数据 distinct

注意: 当查询列数据完全一样才可以去重

select distinct job from emp; 
select distinct ename, job, deptno from emp ;  --只有所有列全相同才被认为是相同的行可以合并

4. 字符串连接查询 双竖线("||")连接

select '员工编号是' || empno || '姓名是' || ename || '的工作是' || job from emp;

5. 查询四则运算(数值类型的才可以)

查询年薪
select ename , sal*12 from emp;  --不推荐在sql中使用, 影响性能

限定查询

1. 条件查询

查询工资大于1500的员工
select * from emp where sal > 1500; 

2. 非空和空的限制
   is not null --不为空
   is null --为空

select * from emp where comm is not nulll;  -- 查询奖金不为空的

3. 多个条件满足需要使用AND关键字

查询工资大于1500并且有奖金的员工
select * from emp where sal > 1500 and comm is not null; 

4. 多个条件或满足 使用OR关键字

查询工资大于1500或者有奖金的员工
select * from emp where sal > 1500 or comm is not null;

5. NOT 查询条件的使用

查询工资不大于1500和没有奖金的员工
select * from emp where not(sal > 1500 and comm is not null);

6. between…and 查询条件的使用

查询工资在1500到3000的员工
select * from emp where sal between 1500 and 3000;
等同于==
select * from emp where sal >= 1500 and sal <= 3000;

7. 模糊查询 LIKE 关键字
   在LIKE中主要使用以下两种通配符
   “%”: 可以匹配任意长度的内容
   “_”: 可以匹配一个长度的内容

查询员工姓名中第二个字符是M的
select * from emp where ename like  '_M%';

8. IN 关键字使用
   格式: 列名 in (值1, 值2, 值3 … )
   列名 not in (值1, 值2,值3 …)
   其值不仅可以是数值类型还可以是字符串

查询员工编号是7369, 7499. 7521的具体信息
select * from emp where empno in (7399,7499, 7521)

9. 不等于号的用法 <> 和 !=

select * from emp where empno != 7369

单行函数

字符函数

1. 小写转大写

select  upper('smith') from dual;

2. 大写转小写

select lower('SMITH') from dual;

3. 首字母大写

select initcap('smith') from dual;

4. 字符串连接

select concat('hello','world') from dual; --不建议使用,建议使用||连接字符串

5. 字符串截取

select substr(''hello','1','3') from dual;  --索引是从1开始的, 结果为hel

6. 字符串长度

select length('hello') from dual; --结果为5

7. 字符串替换

//参数一: 源字符串; 参数二:被替换的字符串; 参数三:替换字符串
select replace('hello','l','x') from dual; -- 结果为hexxo

数值函数

1. 四舍五入函数

select round(12.78) from dual; --结果为 13
select round(12.487,2) from dual; 结果为12.49, 保留两位小数 

2. 取整, 去掉小数点

select trunc(12.87) from dual; -- 结果为12
select trunc(12.635, 2) from dual; -- 结果为12.63, 保留两位小数

3.取余

select mod(10,3) from dual; --结果为1

日期函数

注意: oracle中提供了很多和日期相关的函数,包括日期的加减, 在日期加减有一些规律
日期 - 数字 = 日期
日期 + 数字 = 日期
日期 - 日期 = 数字(天)

1. 当前日期

select sysdate from dual;  --显示当前系统时间

2. 获取两个时间段中的月数 months_between()

//查询员工进入公司的月数
select ename, trunc(months_between(sysdate, hiredate)) from emp; 

3. 获取几个月后的日期add_months()

//12个月之后的日期是多少
select  add_months(sysdate, 12) from dual;

4. 指定给出下次某日期数next_day()

//求出下一个星期一 是什么日期
select next_day(sysdate, '星期一') from dual; 

5. 一个日期的最后一天 last_day()

//求出本月最后一天是几号
select last_day(sysdate) from dual;

转换函数

1. 字符串转换函数，将其转为字符串类型 to_char()

//格式化日期
select to_char(sysdate. 'yyyy-mm-dd HH:mi:ss') from dual; -- 结果为: 2021-06-06 22:03:55 
select to_char(sysdate, 'fmyyyy-mm-dd') from dual; --结果为: 2021-6-6 可以去掉0
//格式化数字
select to_char(10000,'99,999') from dual; --结果为: 10,000
//格式化金额符号
select to_char(10000,'$99,999') from dual; -- 结果为: $10,000
select to_char(10000,'l99,999') from dual; -- 结果为:  ¥10,000

2. 数值转换函数， 将其转换为number类型 to_number()

select to_number('10')+to_number('10') from dual； --结果为20

3. 日期转换函数, 将其转为日期类型 to_date()

selelct to_date('1985-04-22','yyyy-mm-dd') from dual; --结果为: 1985/4/22

通用函数

1. 空值处理 nvl()

//查询员工的年薪
select ename, sal*12+nvl(comm,0) from emp;

2. decode 函数

// decode 函数相当于if-else
select decode(2, 1, '我是1' , 2, '我是2' , '其他' ) from dual; --结果为: 我是2

3. case when 函数

select ename, 
		(case 
		when job = 'CLERK' then
			'业务员'
		when job = 'SALESMAN' then 
			'销售'
		when job = 'ANALYST' then 
			'分析员'
		else
			'无业'
		end)  cjob
from emp;