2018-06-22 第四十七天 oracle

  1. 云栖社区>
  2. 博客>
  3. 正文

2018-06-22 第四十七天 oracle

winniehu 2018-06-22 22:32:07 浏览653
展开阅读全文

SQL基本语句


一、单行函数

--创建scott 用户  为了后面操作scott 用户

--scott oracle 第一个员工  他有一只猫,叫tiger

create user scott identified by tiger;

--一个用户刚刚创建,连登录的权限都没有。需要给用户分配权限。

--oracle 中定义了角色这个概念。角色是权限的集合。

--connect Resource

--授予scott 指定的权限

grant connect, resource to scott;


--scott 用户一些权限去创建属于soctt 的四张表。

--四张表

--bonus 奖金表  空表

--dept 部门表   部门编号deptno   部门名称dname  部门所在地址loc

--emp  员工表  员工编号  empno  员工名称  ename 工种  job  上级编号  mgr  入职日期 hiredate  工资 sal  奖金 comm 所在部门 deptno

--SALGRADE  工资等级表   grade 等级   最低工资 losal  最高工资  hisal


---创建scott 用户,分配权限    scott 四张表


--基本的DQL语句


--注释 使用 --


--最简单的sql 语句

--查询所有员工的信息

--需要告诉DBMS查询那张表,说明查询表的那些字段的数据

--from 后跟表的名称   select 后跟的是 查询的字段的内容  * 是通配符,代表当前表的所有的字段。

select * from emp

--显示部分内容 ,需要显示的字段使用逗号分割

select empno, ename, job from emp


--使用算术表达式

--查询员工的姓名,工作,年薪

select * from emp

select ename, job,sal*12 from emp

--任何数据和null 空做运算,结果还是空¡¤

select ename, job,sal*12+comm*12  from emp

select ename,job,sal*12, comm*12 from emp


--使用别名 

--sql 中,通过使用单引号 表示字符串,唯独   使用别名的时候,可以使用双引号表示字符串,as 关键字可以省略。

--除了别名,其他的地方基本上不使用双引号。都是单引号。

select ename 姓名 , job as 职位 , sal*12 as "年薪", comm*12 as 年奖金 from emp


--使用连接符   ||

--显示   xxx员工的编号为XXXX,入职日期为 XXXX,年薪为XXXX

select * from emp

select ename || ' 员工的编号 ' || empno ||  ' , 入职日期为 ' || hiredate || ' ,年薪为 ' || sal* 12 from emp


--去除重复行  distinct

--显示所有的部门编号

select deptno from emp

select distinct deptno from emp

--显示工作和部门编号

select job, deptno from emp

--去除字段组合的重复行

select distinct job, deptno from emp


--排序

--显示员工的所有信息

select * from emp

--按照部门编号排序  默认是升序排列 asc  降序是 desc

select * from emp order by deptno asc

select * from emp order by deptno desc


--按照员工的年薪排序

select ename, job,sal,sal*12 年薪 from emp order by sal*12


--排序的时候,使用字段的别名

select ename,job,sal,sal*12 年薪 from emp order by 年薪


-- 显示员工的 姓名,工资,部门编号,部门编号按照升序排列,如果部门编号相同,按照工资降序排列

select ename,sal,deptno from emp order by deptno asc ,sal desc


--sql 中那些内容是大小写敏感的?哪些是不敏感的。

--关键字   大小写不敏感

SELECT * from emp

--字段名  大小写不敏感

select ENAME, JOB, sal from emp

--表名称  大小写不敏感

select * from EMP

--字段的内容   查询 员工名为 scott 的员工的所有的信息

--字段的内容大小写敏感

select * from emp  where ename='SCOTT'


--where 子句

--作用:进行行数据的过滤


--查询姓名  scott的员工的信息

select * from emp  where ename='SCOTT'


--查询入职日期为1981/4/2 的员工的信息

--1 :使用默认的日期的字符串形式 'DD-MON-RR 

select * from emp where hiredate='2-4-1981'

-- 2:将日期字符串转换成 日期对象  to_date()


--查询工资大于1600的员工的信息

select * from emp where sal > 1600 order by sal

select * from emp where sal >= 1600

select * from emp where sal < 1600

select * from emp where sal <= 1600

select * from emp where sal=1600

select * from emp where sal != 1600

select * from emp where sal <> 1600


--查询1982/1/23 日期之后入职的员工的信息

--日期可以比较大小,是否相等

select * from emp where hiredate < '23-1-1982'


--查询工资在1000-2000之间的所有的员工的信息   and  代表 并且  java 中的&&

select * from emp where sal >=1000 and sal<=2000


--between xx and xx  闭区间的

select * from emp where sal between 1000 and 2000


--查询 员工信息  工资是 1100 或者是 1600  or    或者的意思  java  ||

select * from emp where sal = 1100 or sal = 1600


--查询所有员工中工种为  clerk  manager  analyst  的员工的信息  ename job  deptno

--字符串的排序规则  java 类似

select ename, job, deptno  from emp where job='CLERK' or job='MANAGER' or job='ANALYST' order by job


-- 在集合中的某一个值就可以  in ()

select ename,job,deptno from emp where job in ('CLERK','MANAGER','ANALYST')


--模糊查询  like

-- 通配符 % :代表任意个字符

--通配符 _ :代表任意一个字符


--查询员工姓名中包含 S 的员工的信息  

select * from emp where ename like '%S%'


--查询员工姓名 第二个字符为A的员工的信息

select * from emp where ename like '_A%'


--查询名字中包含 _ 的员工的信息

select * from emp

--escape 转义指定字符为转义字符

select * from emp where ename like '%\_%' escape '\'



--查询名称中不包含 S 的员工的信息  not like

select * from emp where ename not like '%S%'


--空判断,显示所有奖金是 null 的员工的信息

select * from emp where comm is null


--非空判断  显示所有奖金是 不是 null 的员工的信息

select * from emp where comm is not null


--工作是 clerk 或者 manager 并且 sal 大于 2000

select * from emp where job='MANAGER'

--条件连接符 and  or  的连接的 优先级 不同  and 的优先级 大于 or  ,需要配合小括号控制结合的顺序

select ename, job,sal from emp where (job='CLERK' or job='MANAGER') and sal >2500 order by job



---伪表  dual   也成为虚表

--当需要做一些不依赖于任何一张存在的表的操作的时候,就可以使用虚表了 dual

-- 查询系统日期,和当前用户

select sysdate from dual

select user from dual

select * from dual

select 1+1 from dual


--函数,就是方法  直接使用即可

--函数的分类:

           --1:单行函数:对一个查询结果的记录产生对应的一个计算结果。

           --2:多行函数:对一个查询结果的多条记录产生一个计算结果。


--单行函数  字符串处理的函数   数学相关的函数   日期函数

--emp表中所有的员工的姓名全部小写输出

select ename , lower(ename) as 小写 from  emp


--ppt 中所有的涉及到的字符函数和数值函数,依次测试。


-- 查询所有员工的名字 和入职天数 

select ename 名字, sysdate-hiredate 入职天数 from emp

select ename ,round(sysdate-hiredate) 入职天数 from emp


-- 查询所有员工的名字 和入职月数,要求整月输出。

select ename, round(months_between(sysdate,hiredate)) from emp


-- 查询下周三的日期

select next_day(sysdate,'星期三') from dual


-- 查询本月最后一天的日期

select last_day(sysdate) from dual


-- 查询下周一后的星期二的日期  嵌套函数使用

select next_day(next_day(sysdate,'星期一') , '星期二') from dual


-- 查询所有员工的入职的星期数,年数,使用别名显示

select (sysdate-hiredate)/7  入职星期数 , (sysdate-hiredate)/365  入职年数 from emp


--转换函数  to_number  to_char  to_date


--to_number :  字符串 -->数值的转换

--to_char :数值--->字符串 的转换     日期 --->字符串的转换

--to_date : 字符串--->日期的转换 


--数值和字符串之间的相互转换

--java  Integer.toString(int)    Integer.parseInt(String)

--日期对象和字符串之间的相互转换

--sdf  String  format(Date)   Date  parse(String)      


--自动转换 

--字符串自动转换为数值形式

select '2'+'1' from dual--3  

--数值向字符串的自动转换

select 1 || '2' from dual


--函数转换

--to_char

--数值--->字符串 的转换  

--to_char(number, format)  结果是一个字符串

-- 格式化特殊符号的意义:

--9 :整数部分,如果被转换的数值的位数少于9 的个数,那么多余的9 的位数,忽略不显示的。小数部分,多余的9使用0强制补齐。

--如果9 的个数不够,那么按照9的个数进行强制显示。

select to_char(123.4566,'999.999') from dual

--0: 小数部分和 9 一样,整数部分,多余的0 显示的时候强制补0

select to_char(123.567,'000,000.000000') from dual

--$直接显示

select to_char(123.678,'$999.99999') from dual

--L本地货币的符号

select to_char(123.678,'L999.99999') from dual


--日期-->字符串

--to_char(date,format)

--将所有员工的受雇日期,按照指定的格式显示

select ename,hiredate,to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') 入职日期 from emp


-- 得到当前年份的字符串形式减去 受雇日期的字符串形式,返回受雇的年数  自动类型转换

select ename,to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY') from emp


--to_date : 字符串到日期对象

--查询 XXXX时间之后入职的员工信息 1981/4/2

select ename,hiredate from emp where hiredate>to_date('1981/4/2','YYYY/MM/DD')


--查询 指定 日期之间的入职的员工的信息   1981/4/2  1982/1/23

select * from emp where hiredate > to_date('1981/4/2','YYYY/MM/DD') and hiredate < to_date('1982/1/23','YYYY/MM/DD')

select * from emp where hiredate between to_date('1981/4/2','YYYY/MM/DD') and to_date('1982/1/23','YYYY/MM/DD')


--to_number

--将字符串转换为 数值 

select to_number('$123.123','$000.000')+1 from dual

select to_number('123.123','L000.000')+1 from dual


--通用函数

--nvl (exp1,exp2) : 如果exp1 null 那么返回 exp2 如果不是null 就返回自身

--显示所有员工的年薪

select ename,sal*12+comm*12 年薪  from emp

select ename,sal *12 + nvl(comm,0)*12 年薪 from emp


--nvl2(exp1,exp2,exp3) : 参数的意义 :如果exp1 null 就返回 exp3 ,否则返回 exp2

select ename, sal*12 + nvl2(comm, comm , 0) *12 年薪 from emp


--decode (value, key0,value0,key1,value 1,..... ,valuen)

--参数的意思,如果 value 的值  key0 整个函数返回 value0 ,如果值是key1就返回value1,以此类推,如果都没有找到合适,最后返回 valuen


--把所有的工作的名称先全部显示

select distinct job from emp


--emp 表中所有的员工的名字 工作 以及工作的中文显示 

select ename,job,decode(job,'CLERK','职员','SALESMAN','销售员','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师') 工种 from emp

select ename,job,decode(job,'SALESMAN','销售员','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师','职员') 工种 from emp








二、多行函数

--多行函数 可以分为两类

--1 只能对数值型的数据进行计算, sumavg

--2 可以对任意类型的进行计算,maxmincount


-- avg 所有有奖金的人的奖金的平均值 

--忽略了null  comm

select avg(comm) from emp


--avg 所有员工的奖金的平均值

select avg(nvl(comm,0)) from emp


--多行函数对于 null 的数据,直接忽略,不会进行统计


--count(*) : 使用这样的形式去统计有多少条记录,会自动的优化,对效率最高的字段进行统计。

--count(*) :永远返回的是一个 >=0 的值,如果是一个空表,那么就返回0.

select count(*) from bonus


--统计部门编号是 10 的员工的总数

select count(*) from emp where deptno=10

select count(*) from emp where deptno=20

select count(*) from emp where deptno=30


select distinct deptno from emp


--统计部门编号是 10 的员工的总数,并显示部门编号

--多行函数和单行函数不能一起使用。多行函数不能和字段一起显示(除非使用该字段进行多行)

select deptno, count(*) from emp where deptno=10-----不可以。


三、分组函数

--统计排除10部门的,其他部门的总人数,工资的总和,工资的平均值,工资的最大值,工资的最小值,使用工资的总和排序 desc

select deptno, count(*),sum(sal) 工资总和 ,avg(sal),max(sal),min(sal) 

from emp

where deptno <> 10

group by deptno

order by 工资总和 desc


--sql 书写顺序有严格的要求,必须遵守

--执行的顺序?

---from---->where--->group by---->select---->order by

--从指定的表中去查数据,然后使用where 过滤满足条件的数据。


---统计每个部门的总人数,工资的总和,工资的平均值,工资的最大值,工资的最小值,使用工资的总和排序,只显示工资的平均值 >= 2000

select deptno, count(*),sum(sal),avg(sal),max(sal),min(sal) 

from emp

where avg(sal)>=2000---- where 只能对 行数据过滤,不能过滤分组的数据 如果想进行分组条件过滤,having

group by deptno

order by sum(sal) desc


select deptno, count(*),sum(sal),avg(sal),max(sal),min(sal) 

from emp

--where avg(sal)>=2000---- where 只能对 行数据过滤,不能过滤分组的数据 如果想进行分组条件过滤,having

group by deptno

having avg(sal)>=2000

order by sum(sal) desc


--sql 书写顺序有严格的要求,必须遵守

--执行顺序

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


--having  后可以进行条件判断的内容,和where 是一样的。可以使用 关系运算符 like

--使用别名排序,过滤


--统计部门人数,平均工资,排除10部门,和平均工资> 1000的部门

select deptno, count(*), avg(sal) 

from emp 

where deptno <> 10 

group by deptno

having avg(sal) >1000

order by avg(sal) desc

--使用别名

select deptno 部门编号, count(*) 部门人数 , avg(sal) 平均工资

from emp 

where deptno <> 10 

group by deptno

having 平均工资 >1000----sql 的执行的顺序,select 的执行在  having 之后,别名不能 having 中使用。只要在 select 执行之前的语句,都不能使用别名

order by 平均工资 desc


--平均工资大于1200的部门和工作的组合--多字段分组  

--deptno   job 组合不重复  分组 group by deptnojob

select deptno, job from emp group by deptno,job


--统计部门人数小于4的部门的平均工资  人数

select deptno , count(*),avg(sal)

from emp

group by deptno

having count(*) < 4


--统计各部门最高工资,排除最高工资小于3000的部门

select deptno,max(sal)

from emp

group by deptno

having max(sal) >= 3000


网友评论

登录后评论
0/500
评论
winniehu
+ 关注