数据库笔记整理

一 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)

--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; 

select ename, job, sal, deptno from emp where sal>1000;

select * from emp where deptno != 20;
select * from emp where deptno <> 20;
select * from emp where not deptno = 20;

–先过滤后查询

select ename,job,sal*12 from emp  where sal*12 > 20000;

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(所有)

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);

select * from emp where job = 'SALESMAN';

select ename,sal from emp where sal=2000 or sal=3000;

select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;

select * from emp where job='CLERK' and deptno=20;

select * from emp where job='CLERK' or deptno=20;

select * from emp where job!='CLERK';
select * from emp where job<>'CLERK';
select * from emp where not job='CLERK';

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差集

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 模糊匹配查询
– 需要配合 %任意个任意字符 _一个任意字符

select * from emp where ename like 'A';--定值查询,比使用=效率低
select * from emp where ename like '%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');

SELECT * FROM EMP WHERE SAL=1500 OR SAL=2000 OR SAL=2500 OR SAL=5000;

SELECT * FROM EMP WHERE SAL IN (1500, 2000, 2500, 5000);

–排序 order by 排序字段1 desc降序|asc升序默认,排序字段2…
–对结果集中的数据做排序

select * from emp where deptno = 30 order by sal desc;

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);

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);

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);

select *
  from emp
 where deptno = (select deptno from dept where dname = 'SALES')
   and sal > 1500;

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;

– 日期可以直接±

select sysdate+2 from dual;

select ename,hiredate,hiredate+3 from emp;

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…最终如果以上都不相等,最终取默认值

select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十','无') from  dept;

select deptno,dname,loc,(case deptno when 10 then '十' when 20 then '二十' else '无' end) from  dept;

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';

select ename,sal,deptno,decode(deptno,20,sal*1.1,sal) 涨薪后 from emp;

select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*0.99,40,sal*3) 涨薪后 from emp;

1.6、聚合函数

– 组函数 |聚合函数 | 多行函数 :多条记录返回一个结果
– 确定要计算的数据然后才能使用组函数,对结果集,分组
– count() sum() max() min() avg()

–注意: 如果select后面一旦出现组函数的使用,不能与非分组字段一起使用

select count(*) from emp;
select count(empno) from emp;
select count(deptno) from emp;
select count(1) from emp;

select count(1) from dept;

select count(distinct deptno) from emp;

select count(1) from emp where deptno = 20;

select sum(sal) from emp;

select max(sal),min(sal) from emp;

select max(sal),min(sal) from emp where deptno = 30;

select avg(sal) from emp where deptno = 20;

select sum(comm) from emp;

–null值不参与组函数计算

select count(comm) from emp where comm is not null;

select max(sal) from emp;
select ename,sal from emp where sal = (最高薪水);
select ename,sal from emp where sal = (select max(sal) from emp);

select empno,ename,sal from emp where sal<(select avg(sal) from emp);

–思考

select avg(sal) from emp where deptno = 10;

select *
  from emp e1
 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

1.7、分组

– 分组 : 根据指定的规则对数据进行分组
– group by 分组字段
– 语法: select 数据 from 数据源 where 行过滤条件 group by 分组 having 组过滤信息 order by 排序字段;
– 执行流程 : from -> where -> group by -> having -> select -> order by
– 如果 select后面一旦出现组函数,不能使用非分组字段
– 如果一旦分组,只能使用分组字段或者组函数
– where中不能使用字段别名,不能使用组函数,因为执行流程的问题

select deptno,avg(sal) from emp group by deptno;

select deptno,max(sal) from emp group by deptno;

select  deptno,max(sal) from emp where deptno in(20,30) group by deptno;

select  deptno,max(sal) from emp group by deptno having deptno in(20,30);

select avg(sal) from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal) from emp where sal>1000 group by deptno having deptno in(10,20);

select deptno,max(sal) from emp group by deptno;

select deptno,avg(sal) from emp group by deptno having  avg(sal)>2000;

– 查询最低平均工资的部门编号

select deptno,avg(sal) from emp group by deptno;

select min(avg(sal)) from emp group by deptno;

select deptno,avg(sal) from emp group by deptno having avg(sal)=(最低平均工资);
select deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

select deptno,count(1) n from emp  group by deptno order by n ;

select job,max(sal) from emp group by job;

select deptno,avg(sal) from emp group by deptno having avg(sal) between 1500 and 2000;

1.8、行转列

/*
id name course score

1 张三 语文 81
2 张三 数学 75
3 李四 语文 81
4 李四 数学 90
5 王五 语文 81
6 王五 数学 100
7 王五 英语 90
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,‘张三’,‘语文’,81);
insert into tb_student values(2,‘张三’,‘数学’,75);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(4,‘李四’,‘数学’,90);
insert into tb_student values(5,‘王五’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(7,‘王五’,‘英语’,90);
commit;
drop table tb_student cascade constraints;
*/

select * from tb_student;

–使用一条sql语句，查询每门课都大于80分的学生姓名
–数据 : name
–来源 : tb_student
–条件 : 所考课程数=课程总数 and 所有成绩最小分数>80

select count(distinct course) from tb_student;

select name
  from tb_student
 group by name
having count(1) = (select count(distinct course) from tb_student) and min(score) > 80;

select name,
       decode(course, '语文', score) 语文,
       decode(course, '数学', score) 数学,
       decode(course, '英语', score) 英语
  from tb_student;
  
select name,
       max(decode(course, '语文', score)) 语文,
       min(decode(course, '数学', score)) 数学,
       max(decode(course, '英语', score)) 英语
  from tb_student group by name;

1.9、连表查询92

–表连接查询 : 当要查询的数据来自于多个数据源
–92 99
–92语法 select 数据 from 数据源1,数据源2… where 行过滤条件 group by 分组字段1,分组字段2… having 组过滤信息 order by 排序字段1,… desc|asc;
–执行流程 : from --> where --> group by --> having --> select --> order by

–笛卡尔积 : 交叉连接
–查询所有员工的信息以及员工所在部门信息
–数据 : 员工信息* 部门信息*
–来源 : 员工表emp ,部门表 dept

select * from emp,dept;
select emp.*,dept.deptno,dept.dname from emp,dept;

select e.*,d.deptno,d.dname from emp e,dept d;

–表连接条件 : 过滤通过连表产生的不满足要求的表连接数据
–等值连接 : 判断两个数据源中的某个字段值相等或者不相等
–非等值连接 : 判断区间,判断范围的条件
–92语法中表 连接条件定义 在where后面
–如果要使用的字段来自于多个数据源中都存在,需要指明限定词|出处

select empno,ename,sal,e.deptno,dname from emp e,dept d where e.deptno=d.deptno;

select empno,ename,sal,e.deptno,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=20;

–非等值连接

select * from emp e,salgrade s where sal between losal and hisal;

select *
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal;

–自连接 : 一张表作为两个数据源使用
–查询有上级的员工信息以及上级经理人信息
–数据 : 员工信息 经理人信息
–来源 : 员工表 emp e1 ,经理人表 emp e2

select * from emp e1,emp e2 where e1.mgr=e2.empno;

–内连接 : 满足连接条件查询到不满足过滤掉
–外连接 : 某一个数据源中的数据不满足连接条件的时候也能显示
–左外连接 | 左连接 : from后面主表在左边
–右外连接 | 右连接 : from后面主表在右边
–主表 : 主表中的数据无论是否满足连接条件都能显示
–92语法 : 在连接条件位置,主表的对面添加(+)

–查询所有员工信息以及上级经理人信息
–主表 : 员工表

select * from emp e1,emp e2 where e1.mgr=e2.empno(+);
select * from emp e1,emp e2 where e1.mgr(+)=e2.empno;

1.10、连表查询99

–连表查询 99

–笛卡尔积 :

select * from emp,dept;

select * from emp cross join dept;

– 找出每个员工信息和所在部门信息

select ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptno;

–99
–等值连接
–自然连接 : 自动做等值连接(同名字段|主外键字段) natural join
–注意: 在自然连接中同名字段的不能使用限定词

select ename,deptno,dname from emp natural join dept;

–join …using(同名字段) 指定使用哪个同名字段做等值连接
–注意: 在join…using中同名字段的不能使用限定词

select ename,deptno,dname from emp join dept using(deptno);

–既能够做等值连接,也能做非等值连接
–数据源1 join 数据源2 on 连接条件;
–注意: 在join…on中同名字段的必须使用限定词

select ename,e.deptno,dname from emp e join dept d on e.deptno = d.deptno;

select ename, e.deptno, dname
  from emp e
  join dept d
    on e.deptno = d.deptno
 where e.deptno = 30;

–非等值连接
– 查询每个员工的工资，姓名，工种，工资等级

select ename,sal,job,grade from emp e join salgrade s on sal between losal and hisal;

select ename,sal,job,grade from emp e join salgrade s on sal between losal and hisal where deptno=20 and sal>1500;

select * from emp e1 inner join emp e2 on e1.mgr=e2.empno;

select *
  from emp e1
  join dept d
    on e1.deptno = d.deptno
  join salgrade sa
    on sal between losal and hisal
  join emp e2
    on e1.mgr = e2.empno
 where e1.deptno = 30
 order by e1.sal desc;

–内连接 : (inner) join
–外连接 : 主表中的数据无论是否满足连接条件都显示
–左外连接|左连接 : left join
–右外连接|右连接 : right join
–全连接 : 两张表都是主表 full join
–所有员工存在的员工信息 与上级信息

–主表 : 员工

select * from emp e1 left join emp e2 on e1.mgr=e2.empno;
select * from emp e1 right join emp e2 on e1.mgr=e2.empno;

select * from emp e1 full join emp e2 on e1.mgr=e2.empno;

1.11、rowid 与rownum

–rowid与rownum
–伪列
–rowid行记录的地址,行记录的唯一标识
–实现没有主键,唯一字段的表中完全相同数据的去重

select empno,ename,rowid from emp;

/* 测试数据
insert into tb_student values(1,‘张三’,‘语文’,81);
insert into tb_student values(2,‘张三’,‘数学’,75);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(4,‘李四’,‘数学’,90);
insert into tb_student values(5,‘王五’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(7,‘王五’,‘英语’,90);
commit;
*/

SELECT