当前位置:Gxlcms > 数据库问题 > 数据库基础

数据库基础

时间: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就是从左边填充的意思。

语法

编辑 语法格式如下: lpad( string, padded_length, [ pad_string ] ) string 准备被填充的字符串; padded_length 填充之后的字符串长度,也就是该函数返回的字符串长度,如果这个数量比原字符串的长度要短,lpad函数将会把字符串截取成从左到右的n个字符; pad_string 填充字符串,是个可选参数,这个字符串是要粘贴到string的左边,如果这个参数未写,lpad函数将会在string的左边粘贴空格.

举例说明

编辑 示例1: SQL> select lpad(‘abcde‘,10,‘x‘) from dual; LPAD(‘ABCDE‘,10,‘X‘) -------------------- xxxxxabcde 示例2: SQL> select lpad(‘abcde‘,10,‘oq‘) from dual; LPAD(‘ABCDE‘,10,‘OQ‘) --------------------- oqoqoabcde 示例3: SQL> select lpad(‘abcde‘,2) from dual; LPAD(‘ABCDE‘,2) --------------- ab ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 与lpad函数对应的是rpad函数: rpad函数从右边对字符串使用指定的字符进行填充,语法格式与lpad格式相同: rpad(string,padded_length,[pad_string]) string 被填充的字符串 padded_length 字符的长度,是返回的字符串的数量,如果这个数量比原字符串的长度要短,rpad函数将会把字符串截取成从左到右的n个字符; pad_string 是个可选参数,这个字符串是要粘贴到string的右边,如果这个参数未写,rpad函数将会在string的右边粘贴空格。 例如: rpad(‘tech‘, 7); 将返回‘tech ‘ rpad(‘tech‘, 2); 将返回‘te‘ rpad(‘tech‘, 8, ‘0‘); 将返回‘tech0000‘ rpad(‘tech on the net‘, 15, ‘z‘); 将返回 ‘tech on the net‘ rpad(‘tech on the net‘, 16, ‘z‘); 将返回 ‘tech on the netz‘     三: 数值函数   1. round(round函数通常用于四舍五入求值,通常函数中会传入两个参数,第一个参数是要四舍五入的数字。        第二个参数就是位数,按此位数对第一个参数进行四舍五入。)       select round(412,-2) from dual;       select round(412.313, 2) from dual ; 2. mod oracle中取余是用函数mod(numer1,number2)   oracle中取余是用函数mod(numer1,number2),其返回的值为其余数值。如: 输出1~13中所有不是3的倍数的整数数值。
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;

   MONTHS
----------
        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作为分母来计算。 2 . add_months() select add_months(sysdate,-1) from dual;//当前时间前一个月 select add_months(sysdate,1) from dual;//当前时间后一个月 3. next_day select next_day(sysdate,‘星期一‘)from  dual;//下个星期一是什么时间 4. last_day select last_day(sysdate)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
五:  转换函数   1.  to_char   select to_char (sysdate,‘yyyy‘)from dual;
  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;//返回星期几   2. to_number    select to_number(‘13‘)+to_number(‘12‘) from dual; 3. to_date     select to_date(‘20170710‘,‘yyyyMMdd‘) from dual;   六:  通用函数   1. NVL()函数 select NVL(comm,0) from emp; 2. NULLIF()函数   如果表达式exp1与exp2的值相等则返回null,否则返回exp1的值 select  NULLIF(‘222‘,‘221‘) from dual; 3. nvl2()函数 select empno,ename,sal,comm,nvl2(comm,sal+comm,sal) total from emp 如果comm不为空,则返回sal+comm,如果comm为空,则返回sal.  

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()函数比较:

    1,DECODE  Oracle 特有;
 2,CASE WHEN  Oracle ,  SQL Server,  MySQL 都可用;
 3,DECODE 只能用做相等判断,但是可以配合sign函数进行大于,小于,等于的判断,CASE  可用于=,>=,<,<=,<>,is null,is not null 等的判断;

    4,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)。

对于外连接, 也可以使用“(+) ”来表示。 关于使用(+)的一些注意事项:

  1. (+)操作符只能出现在WHERE子句中,并且不能与OUTER JOIN语法同时使用。
  2.  当使用(+)操作符执行外连接时,如果在WHERE子句中包含有多个条件,则必须在所有条件中都包含(+)操作符。
  3. (+)操作符只适用于列,而不能用在表达式上。
  4. (+)操作符不能与OR和IN操作符一起使用。
  5. (+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接
 7.满外连接(全连接)(Full Outer Join) select empno,ename,dname from emp full outer join dept on emp.deptno = dept.deptno; 九:集合操作 union:并集,所有的内容查询,重复的显示一次 union all :并集,所有的内容都显示,包括重复的 intersect:交集,只显示重复的 minus:差集:只显示对方没有的(跟顺序是有关系的) 首先创建一张只包含20部门员工信息的表: create table emp20 as select * form emp where deptno = 20; 1.验证union及union all union:select * from emp union select * from emp20;//重复的内容不显示 union all :select * from emp union all select * from emp20;//重复的内容依然显示 2.验证intersect select * from emp intersect select * from emp20; 只显示了两个表中彼此重复的记录 3.minus:返回差异的记录 select * from emp minus select * from emp20; 十:子查询 1.单行子查询 select * from emp where sal>(select sal from emp where empno=7566) 2.子查询空值/多值问题 如果子查询未返回任何行,则主查询也不会返回任何结果 select * from emp where sal>(select sal from emp where empno=8888);  //空值 如果子查询返回单行结果,则为单行子查询,可以在主查询中对其使用相应的单行记录比较运算符 select * from emp where sal>(select sal from emp where empno=7566);//正常 如果子查询返回多行结果,则为多行子查询,此时不允许对其使用单行记录比较运算符 select * from emp where sal>(select avg(sal) from emp group by deptno);//多值,报单行子查询返回多行错误。 3.多行子查询   不管在什么情况下子查询能够保证放回值是1<=1行,则可采用单行比较符:=、<、>、<=、>=等;
否则要采用多行比较符: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   需要   

人气教程排行