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： 只能对数值型的数据进行计算， sum，avg
--2： 可以对任意类型的进行计算，max，min，count 。
-- 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 deptno，job
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
