时间：2021-07-01 10:21:17 帮助过：7人阅读
一：数据库基础
1 数据库基本概念
数据库（Database,DB）
数据库管理系统（Database Management System ,DBMS）
数据库管理员（Database Administrator,DBA）
数据库系统（Database System ,DBS）
2 关系型数据库
主流关系型数据库：
Oracle Oracle;
IBM DB2;
IBM Informix;
MS SQL/Server;
SyBase SyBase;
MySQL Access;
3 SQL简介
DML语句（数据操作语言）：Insert ,update, delete,select.
DDL语句（数据定义语言）：Create, Alter ,Drop ,Truncate
DCL语句（数据控制语言）：Grant , Revoke
事务控制语句：Commit , Rollback , Savepoint
4 简单的select语句
5 使用算术表达式
6 连接运算符 ||
7 使用字段别名as
8 空值 is null
9 去除重复行 distinct
10 查询结果排序 order by asc(desc)
11 比较运算符><(!=or<>) between and
12 in 操作 not in
13 模糊查询 like
14 逻辑运算符 or and not
二： 单行函数
1 . Upper
select Upper(‘abcd‘) from dual;
select * from emp where ename = UPPER(‘smith‘);
2. Lower
select lower(‘ABCD‘) from dual;
3 . initcap(首字母大写)
select initcap(ename) from emp ;
4. concat(将两个字符串连接起来，形成一个单一的字符串)
select concat(‘a‘,‘b‘) from dual ;
select ‘a‘ || ‘b‘ from dual ;
5. substr
select substr(‘abcde‘,length(‘abcde‘)-2) from dual;
select substr(‘abcde‘, -3 , 3) from dual;
6. length
select length(ename) from emp ;
7. replace
select replace(ename ,‘a‘ ,‘A‘) from emp;
8. instr
select instr(‘hello world‘, ‘or‘) from dual;
9. Lpad(左填充)
lpad函数是Oracle数据库函数，lpad函数从左边对字符串使用指定的字符进行填充。从其字面意思也可以理解，l是left的简写，pad是填充的意思，所以lpad就是从左边填充的意思。| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
[sql]
DECLARE
--i NUMBER;
BEGIN
FOR i IN 1..13 LOOP
IF MOD(i,3) != 0 THEN
dbms_output.put_line(‘不是3的倍数的整数:‘||+i);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(Sqlcode||‘:‘||Sqlerrm);
END;
|
3. trunc
--Oracle trunc()函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
2.select trunc(sysdate, ‘mm‘) from dual --2013-01-01 返回当月第一天.
3.select trunc(sysdate,‘yy‘) from dual --2013-01-01 返回当年第一天
4.select trunc(sysdate,‘dd‘) from dual --2013-01-06 返回当前年月日
5.select trunc(sysdate,‘yyyy‘) from dual --2013-01-01 返回当年第一天
6.select trunc(sysdate,‘d‘) from dual --2013-01-06 (星期天)返回当前星期的第一天
7.select trunc(sysdate, ‘hh‘) from dual --2013-01-06 17:00:00 当前时间为17:35
8.select trunc(sysdate, ‘mi‘) from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
四： 日期函数
1. Months_between()
MONTHS_BETWEEN函数返回两个日期之间的月份数。
SQL> select months_between(to_date(‘20090228‘,
‘yyyymmdd‘), to_date(‘20080228‘, ‘yyyymmdd‘)) as months from
dual;
oracle的日期函数last_day 意思是得到每月的最后一天,用这个函数,我们可以得到各种不同的日期.
1:得到当前月第一天与最后一天
?| 1 2 3 4 5 6 7 8 9 |
SQL> select
to_char(trunc(sysdate,‘MONTH‘),‘yyyymmdd‘)
firstday
, to_char(last_day(trunc(sysdate,‘MONTH‘)),‘yyyymmdd‘) lastday
from dual;
FIRSTDAY LASTDAY
-------- --------
20130601 20130630
|
2:得到上月第一天与上月最后一天
?| 1 2 3 4 5 6 7 8 |
SQL> SELECT to_char( last_day(add_months(SYSDATE, -2)) + 1 ,‘yyyymmdd‘) firstday
,to_char(last_day(add_months(SYSDATE, -1)),‘yyyymmdd‘)
lastday
FROM dual;
FIRSTDAY LASTDAY
-------- --------
20130501 20130531
|
3:得到上上个月第一天与上上个月最后一天
?| 1 2 3 4 5 6 7 8 9 10 |
SQL> SELECT
to_char( last_day(add_months(SYSDATE, -3)) + 1 ,‘yyyymmdd‘)
firstday
,to_char(last_day(add_months(SYSDATE, -2)),‘yyyymmdd‘)
lastday
FROM dual;
FIRSTDAY LASTDAY
-------- --------
20130401 20130430
|
4:得到下个月第一天与下个月最后一天
?| 1 2 3 4 5 6 7 8 |
SQL> SELECT to_char( last_day(add_months(SYSDATE, 0)) + 1 ,‘yyyymmdd‘)
firstday
,to_char(last_day(add_months(SYSDATE, 1)),‘yyyymmdd‘)
lastday FROM dual;
FIRSTDAY LASTDAY
-------- --------
20130701 20130731
|
NVL (expr1, expr2)：expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ：expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2)：相等返回NULL，不等返回expr1
例如：
NVL：
SQL> select nvl(null, 1) from dual;
NVL(NULL,1)
-----------
1
SQL> select nvl(2, 1) from dual;
NVL(2,1)
----------
2
NVL2：
SQL> select nvl2(2, 1, 3) from dual;
NVL2(2,1,3)
-----------
1
SQL> select nvl2(null, 1, 3) from dual;
NVL2(NULL,1,3)
--------------
3
NULLIF：
SQL> select NULLIF(1,1) from dual;
NULLIF(1,1)
-----------
SQL> select NULLIF(1,2) from dual;
NULLIF(1,2)
-----------
1
4. coalesce()函数
依次考察各参数表达式，遇到非null值即停止并返回该值。
select empno, ename, sal,comm,coalesce(sal+comm,sal,0)总收入 from emp
如果sal+comm不为空，则返回sal+comm。如果为空，则考察sal,不为空则返回sal,否则返回0。
5. case表达式
select empno,ename,sal,
6. decode()函数
select empno ,ename,sal,
decode(deptno,10,‘财务部‘,
20,‘研发部‘,
30,‘销售部‘,
‘未知部门‘)
部门
from emp
case when 与decode()函数比较：
１，DECODE Oracle 特有;
２，CASE WHEN Oracle , SQL Server, MySQL 都可用;
３，DECODE 只能用做相等判断,但是可以配合sign函数进行大于，小于，等于的判断,CASE 可用于=,>=,<,<=,<>,is null,is not null 等的判断;
４，DECODE 使用其来比较简洁，CASE 虽然复杂但更为灵活;
七：分组函数
1. count
如果数据库表的没有数据，count(*)返回的不是null，而是0。
2. AVG,MAX,MIN,SUM
3.分组函数与空值
分组函数省略列中的空值
select avg(comm) from emp;
select sum(comm) from emp;
可使用NVL()函数强制分组函数处理空值
select avg(nvl(comm,0))from emp;
4. group by子句
<1>出现在select列表中的字段或者出现在order by后面的字段，如果不是包含在分组函数中，
那么该字段必须同时在group by子句中出现。
<2>包含在group by子句中的字段则不必须出现在select列表中
<3>可使用where子句限定查询条件
<4>可使用order by子句指定排序方式
<5>另：如果没有group by子句，select列表中不允许出现字段（单行函数）与分组函数混用的情况
select empno,initcap(ename),avg(sal) from emp;//非法
<6>另：不允许在where子句中使用分组函数
select deptno,avg(sal) from emp where avg(sal)>2000;//非法
5. having子句
select deptno,ename,avg(sal)
from emp
where hiredate>=(‘1981-05-01‘,‘yyyy-mm-dd‘)
group by deptno,job
having avg(sal)>1200
order by deptno,job;
6. 分组函数嵌套
select max(avg(sal))from emp group by deptno;
八：多表查询
1.笛卡尔集（Cross Join）
select * from emp,dept;
2.等值连接（Equijoin）(Natural join..on)
select empno,ename,sal,emp,deptno,dname from emp,dept where emp.deptno = dept.deptno;
3.非等值连接（Non-Equijoin）
select ename,empno,grade from emp,salgrade where sal between losal and hisal
4.自连接（Self join）
select e.empno,e.ename,m.empno ,m.ename from emp e,emp m where e.mgr = m.empno;
select e.empno,e.ename,m.empno ,m.ename from emp e,emp m where m.mgr = e.empno;
5.左外连接（Left Outer Join）
select empno,ename,dname from emp left outer join dept on emp.deptno = dept.deptno;
select empno,ename,dname from emp left join dept on emp.deptno = dept.deptno;
select empno,ename,dname from emp e, dept d where e.deptno= d.deptno(+);
6.右外连接（Right Outer Join）
select empno,ename,dname from emp right outer join dept on emp.deptno = dept.deptno;
select empno,ename,dname from emp right join dept on emp.deptno = dept.deptno;
select empno,ename,dname from emp e, dept d where e.deptno(+)= d.deptno;
Oracle 外连接(OUTER JOIN)
对应SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常省略OUTER关键字， 写成：LEFT/RIGHT/FULL JOIN。
在左连接和右连接时都会以一张A表为基础表，该表的内容会全部显示，然后加上A表和B表匹配的内容。 如果A表的数据在B表中没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。
对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
否则要采用多行比较符：IN、> ANY 、> ALL、< ANY、< ALL等。
select * from emp where sal>any(select avg(sal) from emp group by deptno);
select * from emp where sal>all(select avg(sal) from emp group by deptno);
select * from emp where job in (select job from emp where ename=‘MARTIN‘ or ename=‘SMITH‘)
4.TopN查询
select * from emp where rownum=1 or rownum=2;
select * from ( select * from emp order by sal desc) where rownum<=5;
5. 分页查询
select * from (select rownum no,e.* from
(select * from emp order by sal desc)e where rownum <=5)where no>=3；
十一：高级查询
1.随机返回5条记录
select * from (select ename , job from emp order by dbms_random.value())
where rownum <=5
2.处理空值排序
select * from emp order by comm desc nulls last(first);
3.查询跳过表中的偶数行
select ename from (select row_number() over(order by ename) rn ,ename
from emp)x where mod(rn,2)=1
4.查询员工信息与其中工资最高最低员工
select ename , sal,max(sal) over(),min(sal) over() from emp;
5.连续求和
select ename,sal,sum(sal) over(),sum(sal) over(order by ename)from emp;
sum(sal) over(order by ename)指的是连续求和，是以ename来排序的。
若有两个这样的窗口函数，以后面的排序为主。
6.分部门连续求和
select deptno ,sal,sum(sal)over(partition by deptno order by ename)as s from emp;
7.得到当前行上一行或者下一行的数据
select ename,sal,lead(sal) over(order by sal)aaa,lag(sal)over (order by sal)bbb from emp;
8.根据子串分组
select to_char (hiredate,‘yyyy‘),avg(sal) from emp group by to_char (hiredate,‘yyyy‘)
9.确定一年内的天数
select trunc(sysdate,‘y‘)from dual;//当前年第一天
select add_months(trunc(sysdate,‘y‘),12) from dual;//当前年的下一年第一天
select add_months(trunc(sysdate,‘y‘),12)-trunc(sysdate,‘y‘) from dual;//下一年减去当前年，结果为365.
10.查询EMP员工表下每个部门工资前两名的员工信息
select deptno, ename, sal
from emp e1
where (select count(*)
from emp e2
where e2.deptno = e1.deptno
and e2.ename != e1.ename
and e2.sal > e1.sal) < 2
order by deptno, sal desc；
第二种SQL:
select *
from (select deptno,
ename,
sal,
row_number() over(partition by deptno order by sal desc) rn
from emp)
where rn < 3
数据库基础
标签：基本 otto to_char mit 处理空值 eve 粘贴 union all 需要