https://www.yuque.com/docs/share/fe42dbdb-7d33-4ea9-9021-48bfcb67411b?#
一 SQL语句
1.1、基础语句
--注释
/*
多行注释
*/
-- 查询一张表中的所有数据的所有字段 select * from 数据源;
-- 查询语法 : select 查询的数据(*|字段名|字段1,字段 2...|伪列) from 数据源 别名;
--* 一条数据的所有字段值
--执行流程: from --> select
select * from dept; SELECT * FROM EMP;
--1)
--查询|检索|获取 所有员工的所有信息
--查询的数据 : 员工的所有信息 *
--数据的来源 : 员工表 emp
--条件 : 无
select * from emp;
--2)
--查询所有的员工名字
--查询的数据 : 员工的姓名 ename
--数据的来源 : 员工表 emp
--条件 : 无
SELECT ename FROM EMP;
--3)
--查询所有的员工编号和员工名称,员工上级的编号
select empno, ename, mgr from emp;
--4)
--查询所有的部门编号
select deptno from dept;
--查询所有员工所在的部门编号
--查询的数据 : 部门编号 deptno
--数据的来源 : emp,dept
--条件 : 无
--去重 distinct : 对结果集中完全相同的多条数据只显示一条
select distinct deptno from emp;
--5)
--查询出所有有员工存在的部门编号,每个部门编号只出现一次
select distinct deptno from emp; --select deptno from emp;
--6)
--查询出所有存在员工的部门的部门编号,以及员工名称
select distinct ename, deptno from emp;
--7)
--伪列 : 表达式,整数,字符串
--查询表达式 2*3
select distinct 123*456 from emp; select ename,sal,sal*12 from emp;
--8)
--取别名
-- select 数据 (as) 别名,数据 别名.. from 数据源 别名;
--"" 中的内容原封不动显示
--如果别名为英文,默认大写,如果想要原封不动显示,前后添加一对""
select ename 员工姓名,sal "empSal",sal*12 as "年 薪" from emp e; select e.* from emp e;
--9)
--字符串 ''
--作为伪列存在时,默认字段名字段值都为当前字符串
select deptno,dname,'xixihaha' "xixihaha" from dept; select deptno,dname,'yjxxt' 公司名称 from dept;
--10)
--字符串拼接 ||
select 'xixi'||'haha' from emp; select 'YJX-'||ename 员工姓名 from emp;
--11)
--伪列 : 数据源中不存在的字段,可以在select后面查询,假设存在
--伪列 : 表达式,整数,字符串
--12)
--虚表 : dual
select 789*654 from dual; select distinct 789*654 from emp;
--13)
--给每一个员工在原来的基础上+1块钱奖金
--null值与数字运算,结果为null select ename,sal,comm,comm+1 from emp; --null值与字符串拼接,结果为原字符串 select ename,sal,comm,comm||'1' from emp;
--处理null值 : nvl(值1,值2) 当值1为null,nvl函数的结果为值2,当值1不为null,nvl函数的结果为值1
select ename,sal,comm,nvl(comm,0)+1 from emp;
--13)
--一节结尾小练习
--查询所有员工的名字, 工种, 年薪(不带奖金)
select ename,job,sal from emp where comm is null;
1.2、条件查询
-- 条件查询 : select 要查询的数据 from 数据源 where 行过滤条件 order by 排序字段1,排序字段2...;
-- 执行流程 : from --> where --> select -> order by
-- 条件判断运算符 : = < > <= >= != <>
-- 条件连接符 : and or not
-- 区间判断 : 条件1 and 条件2 | between 值1 and 值2(相当于>=与<=)
-- 查询20部门的员工信息
-- 数据 : 员工信息 *
-- 来源 : 员工表 emp
-- 条件 : deptno = 20
select * from emp where deptno = 20;
-- 查询工资大于1000的员工的姓名,工作岗位,工资,所属部门编号
select ename, job, sal, deptno from emp where sal>1000;
-- 查询不在20部门工作的员工信息
select * from emp where deptno != 20; select * from emp where deptno <> 20; select * from emp where not deptno = 20;
--先过滤后查询
-- 查询员工的年薪大于20000的 员工名称、岗位 年薪
select ename,job,sal*12 from emp where sal*12 > 20000;
-- 注意: 在where不能使用字段别名,因为执行流程问题
select ename,job,sal*12 n from emp where n > 20000; --报错
--先查询后过滤
select ename,job,sal*12 r from emp;
--数据源可以为表也可以为结果集
select * from (select ename,job,sal*12 r from emp) where r>20000;
-- 查询 any(任意一个) some(任意一个) all(所有)
-- 查询工资比我们三个人都高的那些员工的信息 900,1000,1100
select * from emp where sal> all(900,1000,1100);
-- 查询工资比我们三个人都低的那些员工的信息
select * from emp where sal< all(900,1000,1100);
-- 查询比我们中随便一个工资高的员工信息
select * from emp where sal> some(900,1000,1100);
-- 查询 工种为’SALESMAN’的员工信息 (注意 内容区分大小写)
select * from emp where job = 'SALESMAN';
-- 检索 工资 2000, 3000员工名称 岗位 工资
select ename,sal from emp where sal=2000 or sal=3000;
-- 工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000; select * from emp where sal between 2000 and 3000;
---查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号,工资
select * from emp where job='CLERK' and deptno=20;
-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号,工资
select * from emp where job='CLERK' or deptno=20;
--查询 岗位 不是 CLERK 员工名称 部门编号,工资
select * from emp where job!='CLERK'; select * from emp where job<>'CLERK'; select * from emp where not job='CLERK';
-- 查询 岗位 不为 CLERK 并且部门编号不为 20的员工名称 部门编号,工资
select * from emp where job!='CLERK' and deptno!=20; select * from emp where not (job='CLERK' or deptno=20);
--存在佣奖金的员工名称
-- 判断null值需要使用is
select * from emp where comm is not null; select * from emp where not comm is null;
--不存在奖金的员工名称
select * from emp where comm is null;
--集合函数 : 对结果集进一步操作
--union并集去重、 union all并集不去重、 intersect交集 、minus差集
--查询工资大于1500 或 含有佣金的人员姓名
select * from emp where sal>1500 or comm is not null; select * from emp where sal>1500 union all select * from emp where comm is not null;
--查询显示不存在雇员的所有部门号。
select deptno from dept minus select distinct deptno from emp;
--like 模糊匹配查询
-- 需要配合 %任意个任意字符 _一个任意字符
--查询员工姓名中包含字符A的员工信息
select * from emp where ename like 'A';--定值查询,比使用=效率低 select * from emp where ename like '%A%';
--查询员工姓名中第二个字母为A的员工信息
select * from emp where ename like '_A%';
-- 测试数据
insert into emp(empno,ename,sal) values(1000,'t_%test',8989); insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);
--查询员工姓名中包含字符%的员工信息
select * from emp where ename like '%A%%' escape('A');
-- 查询工资为1500, 2000, 2500, 5000的员工的信息
SELECT * FROM EMP WHERE SAL=1500 OR SAL=2000 OR SAL=2500 OR SAL=5000;
--in (值列表) 判断值是否在in后面值列表中,如果在就满足条件,不在不满足
SELECT * FROM EMP WHERE SAL IN (1500, 2000, 2500, 5000);
--排序 order by 排序字段1 desc降序|asc升序默认,排序字段2...
--对结果集中的数据做排序
-- 查询30部门的员工信息, 并且按工资降序排序
select * from emp where deptno = 30 order by sal desc;
-- 查询30部门的员工信息, 并且按工资降序排序如果工资相同,则按照员工编号降序排序
select * from emp where deptno = 30 order by sal desc, empno desc;
-- 查询所有员工信息, 按照奖金升序排序 null
--nulls first 所有的null值在最前,nulls last所有null值在最后
select * from emp order by comm asc nulls first;
1.3、exists存在
--exists (结果集) 存在即保留,存在即合理
-- 从 from后数据源中拿出每一条数据,判断是否满足where后的条件,如果是exists就判断exists()中的结果集中是否存在数据,存在当前判断的这条数据就满足跳进,不存在就过滤
--执行流程 : from --> where --> select --> order by
-- 查询所有员工数据
select * from emp where exists (select * from dept);
-- 查询所有员工数据
select * from emp where exists (select * from emp where comm is not null);
-- 查询10,30部门 的员工信息
select * from emp where exists (select * from dept where deptno in (10,30) and dept.deptno = emp.deptno);
-- 别名
select * from emp e where exists (select * from dept d where deptno in (10, 30) and d.deptno = e.deptno);
-- 查询'SALES', 'ACCOUNTING'部门 的员工信息
select * from emp e where exists (select deptno, dname from dept d where dname in ('SALES', 'ACCOUNTING') and e.deptno = d.deptno);
-- 查询所有的员工信息
select * from emp e where exists (select deptno, dname from dept d where dname in ('SALES', 'ACCOUNTING') and e.deptno != d.deptno);
1.4、子查询
--查询所有行记录
select * from emp; select * from emp where 1=1;
--子查询 : 查询语句嵌套查询语句
--当条件与要查询的数据在不同的数据源,而两个数据源之间存在一定的关联方式,可以子查询中转查询
--部门名称为 SALES 或 ACCOUNTING 的雇员信息
--数据 : 员工信息 *
--来源 : 员工表 emp
--条件 : dname in ('SALES','ACCOUNTING')
--查询 SALES 或 ACCOUNTING的部门编号
select deptno from dept where dname in ('SALES','ACCOUNTING'); --在10,30部门的员工信息 select * from emp where deptno in (10,30) ; select * from emp where deptno in (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));
-- 查询工资等级为 2的员工信息
--数据 : 员工信息 *
--来源 : 员工表 emp
--条件 : 工资等级为 2
--查询2等级的最低薪与最高薪
select losal from salgrade where grade = 2; select hisal from salgrade where grade = 2;
--判断员工薪资在这个范围内的员工信息
select * from emp where sal between 1201 and 1400; select * from emp where sal between (select losal from salgrade where grade = 2) and (select hisal from salgrade where grade = 2);
-- 查询 销售部(SALES) 中 工资大于1500的员工信息
select * from emp where deptno = (select deptno from dept where dname = 'SALES') and sal > 1500;
-- 查询工资比SMITH高的同一部门的员工信息
select sal from emp where ename='SMITH'; select deptno from emp where ename='SMITH'; select * from emp where sal > (select sal from emp where ename = 'SMITH') and deptno = (select deptno from emp where ename = 'SMITH');
1.5、单行函数
-- 当前时间
select sysdate from dual; select current_date from dual;
-- 日期可以直接+-
-- 2天以后是几号
select sysdate+2 from dual;
-- 所有员工入职的3天后是几号
select ename,hiredate,hiredate+3 from emp;
-- 查询所有员工的试用期期到期(转正的日期) 3个月试用期
select ename,hiredate,hiredate+90 from emp; select ename,hiredate,add_months(hiredate,-3) from emp;
-- 查询所有员工到目前为止一共工作了几个月
select ename,months_between(sysdate,hiredate) from emp; -- trunc 取整 select ename,trunc(months_between(sysdate,hiredate)) from emp;
-- 查询当前月的最后一天
select ename,last_day(sysdate) from emp;
-- 下一个星期三是几号
select next_day(sysdate,'星期四') from dual;
--日期对象与日期字符串之间相互转换 ****
--to_char select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select sysdate,to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual; --to_date select sysdate,to_date('2022年02月23日','yyyy"年"mm"月"dd"日"') from dual;
--nvl(值1,值2) 对null值判断
--判定函数 decode(判定字段,值1,结果1,值2,结果2....,默认值) 对判定字段的值进行判定,如果值为值1,函数的结果为结果1,与值2相等,函数的结果为结果2...最终如果以上都不相等,最终取默认值
--给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...
select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十','无') from dept;
--case when then else end
select deptno,dname,loc,(case deptno when 10 then '十' when 20 then '二十' else '无' end) from dept;
-- 查询82年入职员工的信息
select * from emp where hiredate between to_date('1982-1-1','yyyy-mm-dd') and to_date('1982-12-31','yyyy-mm-dd'); select * from emp where to_char(hiredate,'yyyy') = '1982';
-- 给20部门的所有员工都涨薪10%,显示出员工的名称, 原来的薪水, 所属部门编号, 涨薪后的薪水
select ename,sal,deptno,decode(deptno,20,sal*1.1,sal) 涨薪后 from emp;
-- 10部门涨薪10%, 20涨薪20%,30降薪1% , 40部门翻倍3倍
select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*0.99,40,sal*3) 涨薪后 from emp;