数据库基础
一：数据库基础
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(左填充)
语法
编辑举例说明
编辑|
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;
----------
12
SQL> select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080229', 'yyyymmdd')) as months from dual;
MONTHS
----------
12
SQL> select months_between(to_date('20080229', 'yyyymmdd'), to_date('20070228', 'yyyymmdd')) as months from dual;
MONTHS
----------
12
SQL> select months_between(to_date('20100331', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
MONTHS
----------
1
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100128', 'yyyymmdd')) as months from dual;
MONTHS
----------
1
SQL> select months_between(to_date('20100327', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
MONTHS
----------
.967741935
SQL> select months_between(to_date('20100330', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
MONTHS
----------
1.06451613
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100130', 'yyyymmdd')) as months from dual;
MONTHS
----------
.935483871
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100131', 'yyyymmdd')) as months from dual;
MONTHS
----------
1
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100127', 'yyyymmdd')) as months from dual;
MONTHS
----------
1.03225806
30/31=0.96774193548387096774193548387097
33/31=1.0645161290322580645161290322581
29/31=0.93548387096774193548387096774194
32/31=1.032258064516129032258064516129
在ORACLE中计算月份的差值，都是月底或是日期相同，都会按整月计算。
如果计算结果不是整数，ORACLE会把31作为分母来计算。
oracle的日期函数last_day 意思是得到每月的最后一天,用这个函数,我们可以得到各种不同的日期.
1:得到当前月第一天与最后一天
|
1
2
3
4
5
6
7
8
9
|
SQL> selectto_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> SELECTto_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 |
select to_char (sysdate,'fmyyyy-mm-dd') from dual;
select to_char (sal,'l999,999,999') from emp;//金额前面带￥
select to_char (sysdate,'day') from dual;//返回星期几
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,case deptno
when 10 then '财务部'
when 20 then '研发部'
when 30 then '销售部'
else '未知部门'
end 部门
from emp
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）。
对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
- （+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
- 当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
- （+）操作符只适用于列，而不能用在表达式上。
- （+）操作符不能与OR和IN操作符一起使用。
- （+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接
否则要采用多行比较符：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
