Oracle-单表查询

最简单查询方式

查询表中所有记录的所有字段数据：

select */1 from 表名;

select * from dep;

查询制定字段

只查询emp表中员工的 ID，姓名，工资，奖金这四个字段

select empid,ename,esalary,comm from emp;

查询显示别名

select 字段 as 别名 from 表名

select name as "姓名" , sex 性别 , id as ID ,  age as "年龄" from emp 

select name as "姓名" , sex 性别 , id as ID ,  age as "年龄" , msalary*12+4400 年收入 from emp

注意：别名中 as 可有可无，""双引号也可以可有可无，但是当别名中有空格必须加""

条件查询

获取部分记录

select 字段,... from 表名 where 条件

算数操作符

+（加），- （减），* （乘），/ ( / 是除号，结果是浮点数) ，mod(m,n)（m 对 n 求余）

select sal, sal*1.1, sal+1000, sal-1000, sal / 1000, mod(sla,1000) from emp;

比较操作符

=（等于），>（大于）< ，（小于） ， <=（大于等于），  >=（小于等于），    !=（不等于）， <> （不等于）  

select * from student where score <= 90

select * from student where score <> 90

注意：!=和<>都是不等于的意思

逻辑操作符

and （与） ，or（或） ，not（非）

between ... and ...（检查是否在两个值之间并且包括两个值），[ not ] in（与列表中的值匹配），[ not ] is null（检查是否为空）

select * from student where score between 70 and 90

相当于

select * from student where score <= 90 and score >= 70

连接操作符

 ||     用于将两个或者多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起

select ('我叫' || name || '年龄是' || age) as "自我介绍" from student

注意：括号内用单引号，括号后的 as 可以不加，"" 双引号中没有空格可以不加 ""，如果有空格要加 ""，比如 "自我介绍" 可以不加 ，但是"自我 介绍"必须加引号，（）可以不加，但是最好加上，方便阅读

去除重复行查询

distinct 

select distinct job from emp;

select distinct job,deptno from emp;

模糊查询

like（ _任意一个字符 ，%任意多个字符，ESCAPE '?' 可以把双引号中任意字符变成转义标识符 ）

--查询姓王的员工

select * from emp where ename like '王%' ----模糊匹配使用like   %任意多个字符

--查询名字中有王字的员工

select * from emp where ename like '%王%' ----只要包含王即可，位置无要求

--查询第二个字是小字的员工

select * from emp where ename like '_小%' ----只要第二是王 _任意一个字符

--出现第二个字是_(下划线)的员工

select * from emp where ename like '%\_%' ESCAPE '\'  ----ESCAPE '?' 可以把双引号中任意字符变成转义标识符

排序

order by 排序参考字段 desc/asc


--按照工资升序排列

select * from emp order by salary;（默认升序）

select * from emp order by salary asc;

--按照工资降序排列

select * from emp order by salary desc;

聚合函数/分组函数

sum()求和，avg()平均值，max()最大值，min()最小值，count(1)/count(*)总数


--获取所有员工的平均工资avg()，最高工资max()，最低工资min()，工资总额sum()，人数count()

select avg(sal),max(sal),min(sal),sum(sal),count(sal) from emp;

分组函数是对表中一组记录进行操作，每组值返回一个结果，即首先要对表记录进行分组，然后再进对表记录进行分组，然后在进行操作汇总，每组返回一个结果，分组是可能是整个表分为一个组，也可能根据条件分成多组。

• count(1)：如果不分组，得到是表的记录数量；如果有分组，在分组后，得到的是，每一个分组的数量
• sum(score)：如果不分组，得到是表的总分数；如果有分组，在分组后，得到的是，每一个分组的总分
• max(score)：如果不分组，得到是表中分数最大值；如果有分组，在分组后，得到的是，每一个分组中的最大分数
• min(score)：如果不分组，得到是表中分数最小值；如果有分组，在分组后，得到的是，每一个分组中的最小分数
• avg(score)：如果不分组，得到是表中分数平均值；如果有分组，在分组后，得到的是，每一个分组的平均分

分组查询

group by 分组依据字段 （用于将表划分为组，对查询结果按组进行聚合运算，为每组返回一个结果，把by后面的数据进行分组，如果后面是一个字段，就把一个字段当成一组，如果是两个字段就把两个字段当成一组，以此类推）

having 筛选条件（用来指定 group by 的检索条件，通常与 group by语句联合使用，用来过滤由 group by 语句返回的记录集，也就是说用来筛选分组后的信息）

--查询学生表中男生女生各自的数量（根据性别分组）

select ssex,count(ssex) from students group by ssex;

--查询平均分高于80分的学生记录（根据学号分组，再根据分组后平均成绩输出结果）

select sid, count(course), avg(score) from student group by sid having avg(score)>=80;

注意：带有 group by 子句的查询语句中，在 select 列表中指定的字段要么是 group by 子句中指定的字段，要么包含聚组函数

where 和 having 区别

where 是分组之前的筛选，having 是分组之后的筛选，注意语句执行顺序

having 中可以出现分组函数/聚合函数，where 中不可以，因为和执行顺序有关，注意语句执行顺序

筛选条件可以放入where，也可以放入having ，建议选择where

语句的执行顺序

from > where > group by > having > select > order by

！！！！！！！！order by 永远是最后执行

语句的书写顺序

select > from > where > group by > having > order by

函数

字符函数

----获取ascii值------ascii( )

select ascii('a') from dual --97

select ascii('A') from dual --65


----连接字符串------concat( )

select concat(ename,ejob) from emp

select concat('Hello','World') from dual


----查找字符串------instr(从哪里查找，需要查找的字符)

select instr('Hello World','orld') from dual


----字符串长度------length( )

select length(ejob),ejob from emp


----大写输出------upper( )

select upper(ejob),ejob from emp


----小写输出------lower( )

select lower(ejob),ejob from emp



----去除左边匹配的字符------ltrim( ' ',' ' )

select ltrim('---lucy---','-')from dual


----去除右边匹配的字符------rtrim( ' ' ,' ' )

select rtrim('---lucy---','-')from dual


----去除匹配的字符------trim(' ' from ' ')

select trim('-'from '---lucy---')from dual


----替换字符------replace(' ',' ',' ')

select replace('Hello World','World','Oracle') from dual


----截取字符串------substr('', , )--------H是1

select substr('Hello Oracle',1,7)from dual

select substr('Hello Oracle',3)from dual

数字函数

----取绝对值------abs（）

select abs(-1) from dual;


----余弦------cos（）

select cos(0.6) from dual


----反余弦------acos（）

select acos(1.05) from dual


----正弦------sin（）

select sin(0.6) from dual


----向上取整------ceil（）

select ceil(1.2) from dual;


----向下取整------floor（）

select floor(1.2) from dual;


----对数------log()

select log(10,100) from dual


----四舍五入------round()

select round(1.4) from dual;

select round(1.4999999) from dual;

select round(1.5) from dual;


----取余------mod()

select mod(30,10) from dual


----幂次------power()

select power(2,2) from dual


----平方根------sqrt()

select sqrt(4) from dual


----保留几位小数------trunc()

select trunc(1.1234566,3) from dual

日期函数

-----查询系统时间，系统时间戳

select sysdate,systimestamp from dual


----加月份-----add_months(时间，添加月)    !!!!!!!

select sysdate,add_months(sysdate,3) from dual


----某个月最后一天------last_day()    !!!!!!

select last_day(sysdate) from dual


----四舍五入------round()

select round(sysdate,'year') from daul

select round(add_months(sysdate,-3),'year') from dual


----提取日期------extract()  

--------提取年月日用 sysdate ，提取时分秒用 systimestamp

select sysdate ,

extract(year from sysdate) year,

extract(month from sysdate) month,

extract(day from sysdate) day,

extract (hour from systimestamp) hour,   ----- 获取的是原时区的时间

extract (minute from systimestamp) minute,

extract (second from systimestamp) second

from dual;

转换函数

----转化为字符类型------to_char()

select to_char(sysdate,'yyyy-mm-dd') from dual

select to_char(sysdate,'yyyy"年"mm"月"dd"日" HH24:MI:SS') from dual ----单引号中双引号

select 45678.1234,to_char(45678.1234,'$999,999.999999') from dual

select 45678.1234,to_char(45678.1234,'L000,000.000000') from dual


----转化为日期类型------to_date()

select to_date('2018-10-02 11:11:11','yyyy"-"mm"-"dd HH24:MI:SS') from dual--


----转化为数值类型------to_number()

select to_number('$123','$2342') from dual


----处理null数据

------nvl(m,value) 若m为空返回value值，若不为空返回m值

------nvl2(m,value1,value2) 若m非空返回value1，否自返回value2

--查询员工年收入，如果没有奖金则奖金为1000，有奖金奖金就为该数值

select eid,ename,esalary*12+nvl(ecomn,1000) from emp

--查询员工年收入，如果没有奖金奖金为2000，有奖金奖金加500

select eid,ename,esalary*12+nvl2(ecomn,ecomn+500,2000) from emp

-- nvl(comm,1000) == if (comm is null) then comm = 1000 if单分支

-- nvl2(comm,ecomn+500,2000) == if (comm is not null) then ecomn+500 else 2000 if双分支