oracle基本查询01
/*数据库-----> 数据库实例----->表空间[逻辑单位]------>数据文件[物理单位]
通常情况下oracle数据库只会有一个实例ORCL
新建一个项目:
MYSQL:创建一个数据库,创建相应的表
ORACLE:创建一个表空间,创建用户,用户去创建表
MYSQL是多数据库的,ORACLE是多用户的。
*/
SQL结构化查询语言:
1:DDL数据定义语言 create alter drop truncate
2: DML数据操纵语言 insert update delete
3: DCL数据控制语言 grant revoke
4: DQL数据查询语言 select from子句 where子句
一:基本查询语句结构:
SELECT [列名] [*] FROM 表名 [where 条件] [group by 分组条件 ] [having 过滤] [order by 排序]
实例1:
select * from tab; --查询当前用户的表---->tab系统表
select * FROM emp; --scott用户下的表
/*dual表:dual是一个虚拟表,用来构成select的语法规则,oracle保证dual里面永远只有一条记录*/
select 1+1 from dual;
实例2:别名查询
/* 使用as 关键字,可以省略
别名中不能有特殊字符或者关键字:% 如果有就加双引号
*/
SELECT ename as 别名, SAL 工资 FROM emp;
SELECT ename as "姓 名", SAL "工#资" FROM emp;
实例3:去除重复数据 distinct
1:单列去重
select job from emp; --找到单重复字段
select distinct job from emp; --在字段前加 distinct 去重
2:多列去重:每一列都一样才算重复
select JOB,DEPTNO from emp; --2个重复字段
select distinct JOB,DEPTNO from emp; --在字段前加 distinct 去重
实例4:查询员工年薪=月薪* 12
select SAL *12 from emp;
实例5:查询员工年薪+奖金
select SAL *12 + COMM from emp; --有null值,null值代表不确定的内容,不可以做四则运算
select SAL *12 + nvl(COMM,0) from emp; --正确,nvl函数,2个参数,如果参数1为null,就返回参数2
字符串拼接:
/* python 使用 + 拼接
oracle 使用 || 拼接
oracle中 单引号主要是取值,是字符 ;双引号主要是取别名
oracle和mysql都可以使用函数:concat(str1,str2)
*/
1:查询员工格式为:姓名:scott --使用拼接符
select '姓名:'|| ename from emp; --正确
select "姓名:"|| ename from emp; --错误。不能用双引号
2:使用函数
SELECT concat('姓名:',ename) FROM emp;
二:条件查询 [where 后面的写法]
类型 | 举例 |
---|---|
关系运算符 | > >= = < <= != <> |
逻辑运算符 | and or not |
like 模糊查询 in(set) 在某个集合内 between ..and ...在某个区间内 is null 判断为空 is not null 判断不为空 |
1: 查询能得到奖金的员工的信息
SELECT * FROM emp WHERE COMM > 0; --不严谨
SELECT * FROM emp WHERE COMM is not null; --0.00也是可以有拿奖金的权力
2:查询薪水在1500-3000之间的员工信息
select * from emp where sal between 1500 and 3000; --闭区间
select * from emp where sal >=1500 and sal <=3000; --sal <=3000 sal不能少
3:查询名字在某个范围的员工的信息('JONES','SCOTT','FORD') :使用in 关键字
select * from emp where ename in ('JONES','SCOTT','FORD');
--模糊查询:like
/*
% 匹配多个字符
_匹配单个字符
*/
1:查询员工姓名第3个字母是o的员工信息
select * from emp where ename like '__O%'; --区分大小写
2:查询员工姓名中,包含%的员工信息
update emp set ename = 'FO%RD' where ename = 'FORD'; --先修改,原表没有包含%的员工姓名
select * from emp where ename like '%#%%' escape '#' ; --转义escape符号设置为#
select * from emp where ename like '%$%%' escape '$' ; --转义也可以设置为\,$
三:排序 [order by]
/*
升序:asc ascend
降序: desc descend
排序注意null值问题: 加上nulls first | last
*/
1:查询员工信息,按照薪水/奖金由高到低排序
select * from emp order by SAL desc; --薪水
select * from emp order by COMM desc; --奖金,但是空值会排序前面,不合理
select * from emp order by COMM desc nulls first ; --nulls 默认是first,还是不合理
select * from emp order by COMM desc nulls last ; --正常合理
2:查询员工信息,按照部门升序,薪水降序排序
select * from emp order by DEPTNO asc,SAL desc ;
四:函数
/*
函数必须要有返回值
单行函数:对某一行中的某个值进行处理
数值函数 ceil().floor(),round(pram1,pram2),trunc(pram1,pram2),mod(num1,num2)
字符函数
日期函数
转换函数
通用函数
多行/聚合函数:对某一列的所有行进行处理
max(),min(),count(),sum(),avg()
求和用累加 sum ,求行的个数用累计 count
*/
1:统计员工工资总和
select sum(SAL) from emp;
2:统计员工奖金总和
select sum(COMM) from emp; --奖金为2200,直接忽略null值
3:统计员工人数
select count(*) from emp;
4:求员工的平均奖金
select avg(COMM) from emp; -- 平均奖金为500.是错误的,2200/14=157.14因为奖金有空值
select sum(COMM)/count(*) from emp; --正确
select ceil(sum(COMM)/count(*)) from emp; --ceil()向上取整,单行函数
---开始单行函数---
1:数值函数
向上取整/向下取整
select ceil(45.926) from dual; --46
select floor(45.926) from dual; --45
四舍五入
select round(45.926,2) from dual; -- 45.93 四舍五入保留2位小数
select round(45.926,1) from dual; -- 45.9 四舍五入保留1位小数
select round(45.926,0) from dual; --46
select round(45.926,-1) from dual; --50 小数点-1位四舍五入,不保留小数
select round(45.926,-2) from dual; --0 不进位,为000--->0
截取
select trunc(45.926,2) from dual; --45.92
select trunc(45.926,1) from dual; --45.9
select trunc(45.926,0) from dual; --45
select trunc(45.926,-2) from dual; --0
select round(45.926,-2) from dual; --0
求余数mod
select mod(9,3) from dual; --余数为0
select mod(9,4) from dual; --余数为1
2:字符函数
--substr(str1,起始索引,长度) 当a等于0或1时,都是从第一位开始截取
select substr('abcdef',0,3) from dual; --abc
select substr('abcdef',1,3) from dual; --abc
--length(str1) :获取字符串长度
select length('abcdef' ) from dual; --6
--trim(str1)去除字符串左右两边的空格
select ' hello world ' from dual; --原
select trim(' hello world ') from dual; --去除空格
3:日期函数
--查询今天的日期
select sysdate from dual; --2019/12/4 22:43:13
--查询3个月后的今天的日期
select add_months(sysdate,3) from dual; --2020/3/4 22:46:19
--查询3天后的日期
select sysdate + 3 from dual; --2019/12/7 22:47:29
--查询员工的入职天数
select * from emp;
select sysdate - HIREDATE from emp;
select ceil(sysdate - HIREDATE) from emp; --向下取整
--查询员工入职的周数
select ceil((sysdate - HIREDATE)/7) from emp; --向下取整
--查询员工入职的月数:可以使用函数months_between
select months_between(sysdate,HIREDATE) from emp;
--查询员工入职的年份
select months_between(sysdate,HIREDATE)/12 from emp; --没有取整。推荐向下取整
4:转换函数
--字符转数值to_number(str) 单引号括起来
select 100 + '10' from dual; --110 默认转换了
select 100 + to_number('10') from dual; --110 标准写法
--数值转字符 to_char(num,格式)
select to_char(sal) || '$' from emp; --800$
select to_char(sal,'$9,999.99') from emp; -- $800.00
--日期转字符
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; --2019-12-04 11:15:52 不区分大小写,注意分钟是mi
select to_char(sysdate,'yyyy') from dual; --2019 只提取年
--提取日
select to_char(sysdate,'d') from dual; --4,代表一周中的2019年12月4日 星期三
select to_char(sysdate,'dd') from dual; --04 ,代表1个月中的第几天
select to_char(sysdate,'ddd') from dual; --338 ,代表一年中的第几天
select to_char(sysdate,'day') from dual; --星期三
select to_char(sysdate,'dy') from dual; --星期三 日期的简称
--字符转日期
select to_date('20191204','yyyy-mm-dd') from dual; --2019/12/4
--查询入职时间1981年到1985年的员工信息
select * from emp where HIREDATE between '1981' and '1985'; --error,文字或字符串格式不匹配
select * from emp where HIREDATE between to_date('1981','yyyy') and to_date('1985','yyyy'); --ok
5通用函数
--nvl(参数1,参数2):如果参数1==null,就返回参数2
--nvl2(参数1,参数2,参数3):如果参数1==null,就返回参数3,否则返回参数2
select nvl2(null,2,3) from dual; --3
--nullif(参数1,参数2):如果参数1==参数2,那么就返回null,否则返回参数1
select nullif(5,6) from dual; --5
--coalesce(参数1,参数2,参数3):返回第一个不为null的值
select coalesce(null,null,1,2,3) from dual; --1
五:条件表达式
/*
case 字段
when 值1 then 值
when 值2 then 值
else
默认值
end
*/
1:给表中姓名去中文名
select ename from emp;
select
case ename
when 'SMITH'then '诸葛村夫'
when 'SCOTT' then '司徒老二'
else
'小兵'
end
from emp; --注意使用单引号标识字符串,mysql和oracle都可以使用
/*oracle 特有的写法
decode(字段,if1,then1,if2,then2,else)
*/
SELECT decode(ename,'SMITH','诸葛村夫','SCOTT','司徒老二','小兵') FROM emp;
六:分组表达式
/*
分组表达式 group by
格式:select 分组的条件,分组之后的操作 FROM 表名 group by 分组的条件 having 条件过滤
*/
1:分组统计所有部门的平均工资,找出平均工资大于2000的部分
SELECT DEPTNO,avg(SAL) from emp group by DEPTNO;
SELECT DEPTNO,avg(SAL) from emp group by DEPTNO having avg(SAL) > 2000;
--SQL的编写顺序:SELECT..FROM .. WHERE..group by..having..order by..
--SQL的执行顺序:from, where, group by, having, select ,order by
--where 和 having的区别
-- where后面不能接聚合函数,可以接单行函数
--having 是在group by 之后执行,可以接聚合函数[sum,avg..]
七:序列
create sequence seq_test1; --创建一个序列,类似于mysql的自增长
create table test1(
tid number primary key,
tname varchar2(10)
); --创建一个表
insert into test1 values('1','刘备');
insert into test1 values('2','张三');
DELETE FROM test1 WHERE tid = 1; --删除某一行数据
--如果你要插入10条数据,tid就要写到10,太麻烦了,所以可以用序列
select * from test1;
insert into test1 values(seq_test1.nextval,'张三'); --执行10次
select * from test1;