数据库复习 满满干货

数据库的概念
如何连接数据库
sql（结构化的查询语句）：
dcl（权限） ddl（数据库对象） dml（表的数据） dql（查询表格的数据）

create user 用户名 identifed by 密码;
grant connect,resource,dba to 用户名;

create table 表名(
列名 数据类型 约束条件
);

数据类型:
number
number(长度, 小数精度)
char(长度) 2000
varchar2(长度) 4000
date
blob 4GB
clob 4GB

约束条件:
primary key 只有一个，包含了非空和唯一两个条件
not null 非空
unique 唯一
check 检查，限制格子输入的数据范围
foreign key 外键，格子的内容一定来自另一个表

insert into 表 values(值);
insert into 表(列名) values(值);

update 表 set 列=新值 where 列=旧值;

delete from 表 where 列=旧值;

delete from 表;
truncate table 表;

select * from 表;
select 列名 from 表;
select 列名 from 表 where 列=值;

select 列名 from 表名 where 条件筛选；

数据的统计和计算：
group by 和 聚合函数的计算
对谁分组，就只能查看谁的信息

聚合函数：
count() 统计行数
sum() 求和
avg() 求平均值
max() 最大值
min() 最小值

having关键字：
对分组的结果进行筛选，用在group by之后

查询20号部门的员工数量：
–先筛选再分组
select deptno,count(empno) from scott.emp where deptno=20 group by deptno;
–先分组再筛选
select deptno,count(empno) from scott.emp group by deptno having deptno=20;

where 和 having 他们的区别是什么？

1. 先筛选再分组，用使用where筛选，速度上更快
2. where在group by 之前使用的，having是在group by之后使用的
3. where不能筛选聚合函数的结果，having可以

同时对多个列进行分组：
查询每个部门里面，每个工作岗位，平均工资是多少？
select deptno,job,avg(sal) from scott.emp group by deptno,job;

数据的排序： 升序 降序
order by 列名 asc|desc
order by 列名 默认是升序的排序

sql语句的嵌套查询（子查询）
将一个句子查询的结果，当成另一个句子查询的条件

查询和SMITH工作岗位相同的其他员工的信息
1.查询SMITH工作岗位
2.查询和他相同的员工

select * from scott.emp where job=(
select job from scott.emp where ename=‘SMITH’
);

表格的联合查询：
拼接多个表格，变成一张大的表格，然后对这个大的表格进行操作。
第一个句型：
select 列名 from a [inner | left | right | full ] join b
on a.xx=b.xx;

第二个句型：
select 列名 from a,b where a.xx=b.xx; --内连接
select 列名 from a,b where a.xx=b.xx(+); --左连接
select 列名 from a,b where a.xx(+)=b.xx; --右连接

内连接 [inner] join：
拼接左右两个表格，显示两个表格连接列共同拥有的数据
select * from scott.emp join scott.dept
on scott.emp.deptno=scott.dept.deptno;

select * from scott.emp,scott.dept where scott.emp.deptno=scott.dept.deptno;

左连接 left [outer] join：
首先先显示左右两个表格所有的共同数据，然后再显示左边那边表独有的数据，因为右边没有匹配数据，所以显示为空
select * from scott.dept left join scott.emp
on scott.emp.deptno=scott.dept.deptno;

select * from scott.dept,scott.emp where scott.dept.deptno=scott.emp.deptno(+);

右连接 right [outer] join：刚好和左连接相反，首先先显示左右两个表格所有的共同数据，然后再显示右边那边表独有的数据，因为左边没有匹配数据，所以显示为空
select * from scott.emp right join scott.dept
on scott.emp.deptno=scott.dept.deptno;

select * from scott.emp,scott.dept where scott.emp.deptno(+)=scott.dept.deptno;——number

全连接 full [outer] join：先显示两个表的共同数据，然后再显示左边表的独有数据，右边没有匹配就显示为空，然后再显示右边表的独有数据，左边没有匹配就显示为空。
select * from scott.emp full join scott.dept
on scott.emp.deptno=scott.dept.deptno;

select * from scott.emp,scott.dept where scott.emp.deptno(+)=scott.dept.deptno
union
select * from scott.emp,scott.dept where scott.emp.deptno=scott.dept.deptno(+);

笛卡尔积
表连接的过程：
对a和b的所有数据进行匹配，先找出所有数据的笛卡尔积，然后在所有的集合结果中找出符合的数据

统计行数：
count(列名)：统计的是这一列中不为空的数量
count(1)等同于count(*)：以行为单位，只要整行中有一列有值，那么就统计数量

面试 干货
左连接的时候，数据筛选使用and和where的区别？
a表 b表
1 a 1 A
2 b 2 B
3 c 4 D

select * from a left join b on a.id=b.id where a.id=2;
先连接，在连接的基础上进行筛选，数据只剩1行
2 b 2 B

select * from a left join b on a.id=b.id and a.id=2;
先筛选a表，用筛选后的结果和b表进行连接，数据有3行
2 b 2 B
1 a null null
3 c null null

什么是函数：
具有固定功能的、功能单一的、可以反复使用的代码块
函数的使用：
函数名字(要被计算的数据)

单行函数：每一行数据单独的进行运算

数字相关

abs(x) 取绝对值
select abs(-99) from dual;

round(x, 小数精度) 四舍五入
select round(1.2345678,4) from dual;

round(x) 四舍五入，默认是只取值到整数
select round(4.3678) from dual;

trunc(x, 小数精度) 截取到小数精度的位置
select trunc(1.2345678,4) from dual;

trunc(x) 截取到整数
select trunc(4.5678) from dual;

字符串相关

substr(x, idx, len) 截取字符串的一部分 substr(字符串, 开始位置, 连续取值的长度)

select substr(‘abcdefghijklmn’,2,4) from dual;
select substr(‘abcdefghijklmn’,-3,2) from dual; 开始位置为负数，表示从后往前数
select substr(‘abcdefghijklmn’,-3) from dual; 连续取值为空，表示一直取到最后

concat(x1, x2) 两个字符串的拼接

select concat(‘hello’,‘world’) from dual;
select concat(concat(‘hello’,‘-’),‘world’) from dual;
select concat(‘’‘’,concat(deptno,‘’‘’)) from scott.emp;

字符串的拼接可以使用管道符来实现，两个管道符可以实现字符的拼接
select ename||‘:’||deptno from scott.emp;
replace(x, old, new) 字符串内容的替换
select ename,replace(ename,‘A’,‘.’) from scott.emp;
length(x) 进行字符串长度的计算
select length(ename) from scott.emp;
select length(‘abcdefg’) from dual;

日期相关
sysdate 系统关键字，获取当前的时间日期

select sysdate from dual;
last_day(x) 计算日期的最后一天
select last_day(sysdate) from dual;
months_between(x1, x2) 计算两个日期之间的时间间隔
select months_between(date’2020-9-2’,date’2019-9-1’) from dual;
add_months(x, m) 计算日期的月份偏移
select add_months(sysdate,11) from dual;
日期可以直接加减的，直接加减是天数的偏移
select date’2020-10-1’+10 from dual;

数据类型转换相关
to_number() 只能转换全是数字的字符串，例如 ‘100’
select to_number(‘100’) from dual;
to_char() 任何东西都可以转成字符串
select to_char(100) n from dual;
日期转换成字符串，是对日期的内容进行提取
select to_char(sysdate,‘yyyy’) from dual;
select to_char(sysdate,‘mm’) from dual;
select to_char(sysdate,‘dd’) from dual;
select to_char(sysdate,‘hh’) from dual;
select to_char(sysdate,‘mi’) from dual;
select to_char(sysdate,‘ss’) from dual;select *from zz.Emp where sal>

(Select max(sal) from emp where deptno =30 )
select to_char(sysdate,‘day’) from dual; --提取星期几
to_date() 只有时间格式的字符串可以转成日期，例如 ‘2020-10-1 9:38:12’
select to_date(‘2020-9-1 10:21:09’,‘yyyy-mm-dd hh24:mi:ss’) from dual;

练习：

1. 写出永远是明天 早上10点钟的时间
   select to_date(to_char(sysdate+1,‘yyyy-mm-dd’)||’ 10:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) from dual;

2. 查询emp表中，所有星期四入职的员工
   select * from scott.emp where to_char(hiredate,‘day’)=‘星期四’;

3. 查询每一年有多少员工入职
   select to_char(hiredate,‘yyyy’),count(empno) from scott.emp group by to_char(hiredate,‘yyyy’);

分析函数：
over() 开窗函数
计算函数部分() over(partition by 列名 order by 列名 asc|desc)

查询每个部门中工资最高的员工信息

1. 每个部门的最高工资是多少 max()
2. 将这个查询的分组结果和原来表格进行拼接 over(partition by 列名)
3. 对拼接之后的数据进行对比 where

over()：对整个表格进行计算
select a.*,
max(sal) over()
from scott.emp a;

over(partition by 列名)：在每个小的分组中，进行数据的计算
select a.*,
max(sal) over(partition by job)
from scott.emp a;

over(order by 列名 asc|desc)：对整个表，对排序的列进行依次的累计运算，并列的名次和数据，会当成一个整体进行计算
select a.*,
sum(sal) over(order by sal)
from scott.emp a;

over(partition by 列名 order by 列名 asc|desc)：在每个分组中，对排序的列进行依次的累计运算，并列的名次和数据，会当成一个整体进行计算
select a.*,
avg(sal) over(partition by deptno order by sal)
from scott.emp a;

max() | min() | count() | sum() | avg() over(partition by 组名 order by 列名 asc|desc)

排名函数+over()：
row_number()：根据某个列，按照顺序进行排序 1 2 3 4
select a.*,
row_number() over(partition by deptno order by sal desc) r
from scott.emp a;

rank()：根据某个列，按照顺序进行排序，如果值相同，会出现并列的名次，会跳过占用的名次 1 2 2 4
select a.*,
rank() over(order by sal desc) r
from scott.emp a;

dense_rank()：根据某个列，按照顺序进行排序，如果值相同，会出现并列的名次，不会跳过名次 1 2 2 3
select a.*,
dense_rank() over(order by sal desc) r
from scott.emp a;

在此附上一道笔试题

平移函数：在工作中一般用来实现数据的同比和环比的计算
同比：今年的4月和去年的4月相比，在两个时间范围内，取相同时间节点的数据进行比较
环比：今年的4月和今年的3月相比，在同一个时间范围内，取相邻的时间节点的数据进行比较

create table sale_info(
y number,
m number,
amount number(7,2)
);
insert into sale_info values(2018,1,2342);
insert into sale_info values(2018,2,1234);
insert into sale_info values(2018,3,3545);
insert into sale_info values(2018,4,3456);
insert into sale_info values(2018,5,2342);
insert into sale_info values(2018,6,4534);
insert into sale_info values(2018,7,3453);
insert into sale_info values(2018,8,2342);
insert into sale_info values(2018,9,4352);
insert into sale_info values(2018,10,1312);
insert into sale_info values(2018,11,3453);
insert into sale_info values(2018,12,1235);
insert into sale_info values(2019,1,3453);
insert into sale_info values(2019,2,1233);
insert into sale_info values(2019,3,3445);
insert into sale_info values(2019,4,1233);
insert into sale_info values(2019,5,1231);
insert into sale_info values(2019,6,4234);
insert into sale_info values(2019,7,1231);
insert into sale_info values(2019,8,2131);
insert into sale_info values(2019,9,1231);
insert into sale_info values(2019,10,3421);
insert into sale_info values(2019,11,1231);
insert into sale_info values(2019,12,1231);
commit;

lag() 将数据往下平移一行
计算2019年每一个月和2018年同月份的数据增长率。
select y,m,round((amount-lo)/lo100,2)||‘%’ from
(select a.,
lag(amount) over(partition by m order by y) lo
from sale_info a) b;
在m这个组，根据y排序，将amount这个列，数据往下平移一行

使用表连接的方法，也可以实现同样的效果
select b.y,b.m,round((b.amount-a.amount)/a.amount*100,2)||‘%’ 增长率 from
(select * from sale_info where y=2018) a
join
(select * from sale_info where y=2019) b
on a.m=b.m;

查询2019年的每一个月，比上个月增长了百分之多少？
select y,m,round((amount-lo)/lo100,2)||‘%’ from
(select a.,
lag(amount) over(order by m ) lo
from sale_info a where y=2019) b;

select a.y,a.m,round((a.amount-b.amount)/b.amount*100,2)||‘%’ from
(select * from sale_info where y=2019) a
join
(select * from sale_info where y=2019) b
on a.m=b.m+1;

lead() 将数据往上平移一行
select a.*,
lead(amount) over(order by m ) lo
from sale_info a where y=2019;

distinct和group by都可以去重，那么场景下用哪一种，效率更高？

1. 数据量非常大的时候，group by的速度更快
2. 数据量不大的时候，两者的速度差不多，如果去重的列，有大量的重复的信息的组成的，那么group by更快，如果列里面大部分的信息都不重复，只有少部分需要去重的数据，那么distinct更快

伪列：在创建表格的时候，没有直接写上去的列名，但是每个表都一定会有的信息，效果等同于row_number()
rownum：分页查询，必须从1开始查询才会有结果
select a.,rownum r from scott.emp a;
如果需要从中间的序号开始查询，就需要做表格的嵌套
select * from
(select a.,rownum r from scott.emp a) b
where r>=3 and r<=6;

要先排序，然后再分页
找出公司里面，工资最高的员工信息
select a.*,rownum from
(select * from scott.emp order by sal desc) a
where rownum=1;

rowid：数据去重，表里面每一行数据，在数据库中的唯一编号，不会出现重复的数据
–对重复的数据分组
–找到每个组里面最小或者最大的rowid
–删除除了这些rowid之外的其他rowid所在行

delete from student where rowid not in
(select min(rowid) from student
group by sno,sname,sage,ssex);

行列转换：
现在需要以行的方式去查看维度的数据
10 20 30
2916 2177 1566

select * from (select 需要查看的列名 from 表名)
pivot(聚合函数(计算的列名) for 维度的列名 in (维度列的值));

select * from (select deptno,sal from scott.emp)
pivot(avg(sal) for deptno in (10,20,30));

什么是维度什么是指标
维度 ：计算的范围， 指标：计算结果

空值的默认值设置：
nvl(列名, 如果这列为空时设置的默认值)
nvl(comm,0) 如果comm这列为空，那么默认值是0，如果不为空，显示原来的值
nvl2(列名, 如果不为空显示的内容, 如果为空显示的内容)
select ename,sal,comm,nvl2(comm,sal+comm,sal) from scott.emp;
如果comm这列为空，那么显示sal本身，如果不为空，显示sal+comm

decode(列名, 判断条件1, 条件1为真的时候, 判断条件2, 条件2为真的时候, 判断条件3, 条件3为真的时候, … , 所有条件都为假的情况)

如果 怎么样
那么执行什么语句
否则 怎么样
那么执行什么语句
…
剩下的情况
那么执行什么语句

select ename,sal,comm,
decode(comm,null,‘没有奖金’,0,‘没有奖金’,‘有奖金’)
from scott.emp;
如果没有奖金，显示为0,显示提示没有，如果是0，也提示没有，否则，提示有奖金。

在decode里面，只能写精确的值的判断，不能写范围的判断，也不能写取反的值的判断等等，如果要实现范围的判断，需要结合sign()函数。

sign()函数：是用来比较数据的大小的
sign(x-y)：如果x比y大，结果就是1，两者相等，结果是0，x比y小，结果就是-1

查询emp表里面的员工工资，小于2000，等级为C，2000-3000等级为B，3000以上等级为A
select ename,sal,
decode(sign(sal-2000)+sign(sal-3000),-2,‘C’,2,‘A’,‘B’)
from scott.emp;

判断相关的语句：case when 语句
case
when 条件判断1 then 条件判断1为真的时候
when 条件判断2 then 条件判断2为真的时候
when 条件判断3 then 条件判断3为真的时候
else 所有条件都为假的时候
end

select sno,cno,score,
case
when score>=60 then ‘及格’
else ‘不及格’
end
from sc;

查询出及格和不及格分别有多少人，显示效果如下：
jige bujige
7 1
select * from (
select sno,case when score>=60 then ‘jige’ else ‘bujige’ end c
from sc
)
pivot(count(sno) for c in (‘jige’,‘bujige’));

select
sum(case when score>=60 then 1 else 0 end) jige,
sum(case when score<60 then 1 else 0 end) bujige
from sc;