4.1 SQL语言概述

4.1.1 SQL语言的特点

1、集合性

2、统一性

3、易于移植性

4.1.2 SQL语言的分类

1、数据查询语言（DQL）- select语句，查询数据

2、数据操纵语言（DML）- insert、update和delete语句，改变数据

3、事务控制语言（TCL）- commit、rollback和savepoint语句，维护数据一致性

4、数据定义语言（DDL）- create、alter和drop语句，改变数据库对象

5、数据控制语言（DCL）- grant和revoke语句，权限授予和回收

4.1.3 SQL语言的编写规则

1、关键字不区分大小写

2、字符值区分大小写

3、以分号作为结束符

 

4、2 用户模式

4.2.1 模式与模式对象

模式是一个数据库对象的集合

模式为一个数据库用户所有，并且具有与该用户相同的名称。

模式对象是由用户创建的逻辑结构，用以存储或引用数据。比如段，约束，视图，同义词，过程，程序包等。

不属于某个用户所拥有的数据库对象就不能称之为模式对象，比如角色，表空间，目录等。

4.2.2 实例模式SCOTT

SQL> conn scott/tiger

Connected.

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------DEPTEMPBONUSSALGRADE

 

SQL> select table_name from dba_tables where owner='SCOTT';

TABLE_NAME

------------------------------DEPTEMPBONUSSALGRADE

 

4.3 检索数据

语法格式：

select {[distinct|all]column|*}

[into table_name]

from {tables|views|other select}

[where conditions]

[group by columns]

[having conditions]

[order by columns]

4.3.1 简单查询

1、检索所有的（*）

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC

---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTON

 

SQL> select * from scott.dept,scott.salgrade;

    DEPTNO DNAME          LOC                GRADE      LOSAL      HISAL

---------- -------------- ------------- ---------- ---------- ----------        10 ACCOUNTING     NEW YORK               1        700       1200        10 ACCOUNTING     NEW YORK               2       1201       1400        10 ACCOUNTING     NEW YORK               3       1401       2000        10 ACCOUNTING     NEW YORK               4       2001       3000        10 ACCOUNTING     NEW YORK               5       3001       9999        20 RESEARCH       DALLAS                 1        700       1200        20 RESEARCH       DALLAS                 2       1201       1400        20 RESEARCH       DALLAS                 3       1401       2000        20 RESEARCH       DALLAS                 4       2001       3000        20 RESEARCH       DALLAS                 5       3001       9999        30 SALES          CHICAGO                1        700       1200

    DEPTNO DNAME          LOC                GRADE      LOSAL      HISAL

---------- -------------- ------------- ---------- ---------- ----------        30 SALES          CHICAGO                2       1201       1400        30 SALES          CHICAGO                3       1401       2000        30 SALES          CHICAGO                4       2001       3000        30 SALES          CHICAGO                5       3001       9999        40 OPERATIONS     BOSTON                 1        700       1200        40 OPERATIONS     BOSTON                 2       1201       1400        40 OPERATIONS     BOSTON                 3       1401       2000        40 OPERATIONS     BOSTON                 4       2001       3000        40 OPERATIONS     BOSTON                 5       3001       9999

20 rows selected.

 

2、检索指定的列

SQL> select job,ename,empno from emp;

JOB       ENAME           EMPNO

--------- ---------- ----------CLERK     SMITH            7369SALESMAN  ALLEN            7499SALESMAN  WARD             7521MANAGER   JONES            7566SALESMAN  MARTIN           7654MANAGER   BLAKE            7698MANAGER   CLARK            7782ANALYST   SCOTT            7788PRESIDENT KING             7839SALESMAN  TURNER           7844CLERK     ADAMS            7876

JOB       ENAME           EMPNO

--------- ---------- ----------CLERK     JAMES            7900ANALYST   FORD             7902CLERK     MILLER           7934

14 rows selected.

SQL> select rowid,job,ename from emp;

ROWID              JOB       ENAME

------------------ --------- ----------AAAR3xAAEAAAACXAAA CLERK     SMITHAAAR3xAAEAAAACXAAB SALESMAN  ALLENAAAR3xAAEAAAACXAAC SALESMAN  WARDAAAR3xAAEAAAACXAAD MANAGER   JONESAAAR3xAAEAAAACXAAE SALESMAN  MARTINAAAR3xAAEAAAACXAAF MANAGER   BLAKEAAAR3xAAEAAAACXAAG MANAGER   CLARKAAAR3xAAEAAAACXAAH ANALYST   SCOTTAAAR3xAAEAAAACXAAI PRESIDENT KINGAAAR3xAAEAAAACXAAJ SALESMAN  TURNERAAAR3xAAEAAAACXAAK CLERK     ADAMS

ROWID              JOB       ENAME

------------------ --------- ----------AAAR3xAAEAAAACXAAL CLERK     JAMESAAAR3xAAEAAAACXAAM ANALYST   FORDAAAR3xAAEAAAACXAAN CLERK     MILLER

14 rows selected.

 

3、带有表达式的select子句

 

SQL> select sal*(1+0.1),sal from emp;

SAL*(1+0.1)        SAL

----------- ----------        880        800       1760       1600       1375       1250     3272.5       2975       1375       1250       3135       2850       2695       2450       3300       3000       5500       5000       1650       1500       1210       1100

SAL*(1+0.1)        SAL

----------- ----------       1045        950       3300       3000       1430       1300

14 rows selected.

 

4、为列指定别名

可以使用as关键字，也可以直接指定

 

SQL> select empno as "员工编号" from emp;

员工编号

------------        7369        7499        7521        7566        7654        7698        7782        7788        7839        7844        7876

员工编号

------------        7900        7902        7934

14 rows selected.

SQL> select empno "员工编号" from emp;

员工编号

------------        7369        7499        7521        7566        7654        7698        7782        7788        7839        7844        7876

员工编号

------------        7900        7902        7934

14 rows selected.

 

5、显示不重复记录

使用distinct关键字去除重复记录

 

SQL> select distinct job from emp;

JOB

---------CLERKSALESMANPRESIDENTMANAGERANALYST

 

4.3.2 筛选查询

语法格式：

select columns_list

from table_name

where conditional_expression

1、比较筛选

基本比较筛选有以下6种情况:

• A=B

• A!B或A<>B

• A>B

• A>=B

• A<B

• A<=B

 

SQL> select empno,ename,sal from emp where sal>1500;

     EMPNO ENAME             SAL

---------- ---------- ----------      7499 ALLEN            1600      7566 JONES            2975      7698 BLAKE            2850      7782 CLARK            2450      7788 SCOTT            3000      7839 KING             5000      7902 FORD             3000

7 rows selected.

特殊比较筛选有以下两种情况：

A{operator}ANY(B):A与B中任何一个元素进行operator运算符的比较，只要有一个比较值为true，就返回数据行

A{operator}ALL(B):A与B中所有元素进行operator运算符的比较，只有所有元素比较值为true，才返回数据行

 

SQL> select empno,ename,sal from emp where sal<>all(3000,950,800);

     EMPNO ENAME             SAL

---------- ---------- ----------      7499 ALLEN            1600      7521 WARD             1250      7566 JONES            2975      7654 MARTIN           1250      7698 BLAKE            2850      7782 CLARK            2450      7839 KING             5000      7844 TURNER           1500      7876 ADAMS            1100      7934 MILLER           1300

10 rows selected.

 

2、使用特殊关键字筛选

（1）like关键字：字符串匹配

常用通配符：

_：代表任意一个字符

%：代表任意数量字符

 

SQL> select empno,ename,job from emp where ename like 'S%';

     EMPNO ENAME      JOB

---------- ---------- ---------      7369 SMITH      CLERK      7788 SCOTT      ANALYST

 

SQL> select empno,ename,job from emp where ename not like 'S%';

     EMPNO ENAME      JOB

---------- ---------- ---------      7499 ALLEN      SALESMAN      7521 WARD       SALESMAN      7566 JONES      MANAGER      7654 MARTIN     SALESMAN      7698 BLAKE      MANAGER      7782 CLARK      MANAGER      7839 KING       PRESIDENT      7844 TURNER     SALESMAN      7876 ADAMS      CLERK      7900 JAMES      CLERK      7902 FORD       ANALYST

     EMPNO ENAME      JOB

---------- ---------- ---------      7934 MILLER     CLERK

12 rows selected.

 

(2)in关键字：测试一个数据值是否匹配一组目标值中的一个

 

SQL> select empno,ename,job from emp where job in('PRESIDENT','MANAGER','ANALYST');

     EMPNO ENAME      JOB

---------- ---------- ---------      7566 JONES      MANAGER      7698 BLAKE      MANAGER      7782 CLARK      MANAGER      7788 SCOTT      ANALYST      7839 KING       PRESIDENT      7902 FORD       ANALYST

6 rows selected.

 

SQL> select empno,ename,job from emp where job not in('PRESIDENT','MANAGER','ANALYST');

     EMPNO ENAME      JOB

---------- ---------- ---------      7369 SMITH      CLERK      7499 ALLEN      SALESMAN      7521 WARD       SALESMAN      7654 MARTIN     SALESMAN      7844 TURNER     SALESMAN      7876 ADAMS      CLERK      7900 JAMES      CLERK      7934 MILLER     CLERK

8 rows selected.

 

(3)between关键字：返回某一个数据值是否位于两个给的的值之间

 

SQL> select empno,ename,sal from emp where sal between 2000 and 3000;

     EMPNO ENAME             SAL

---------- ---------- ----------      7566 JONES            2975      7698 BLAKE            2850      7782 CLARK            2450      7788 SCOTT            3000      7902 FORD             3000

 

SQL> select empno,ename sal from emp where sal not between 2000 and 3000;

     EMPNO SAL

---------- ----------      7369 SMITH      7499 ALLEN      7521 WARD      7654 MARTIN      7839 KING      7844 TURNER      7876 ADAMS      7900 JAMES      7934 MILLER

9 rows selected.

 

（4）is null 关键字：检测是否为空

 

SQL> select empno,ename,comm from emp where comm is null;

     EMPNO ENAME            COMM

---------- ---------- ----------      7369 SMITH      7566 JONES      7698 BLAKE      7782 CLARK      7788 SCOTT      7839 KING      7876 ADAMS      7900 JAMES      7902 FORD      7934 MILLER

10 rows selected.

 

3、逻辑筛选：AND,OR,NOT

 

SQL> select empno,ename,sal from emp where sal>=2000 and sal<=3000;

     EMPNO ENAME             SAL

---------- ---------- ----------      7566 JONES            2975      7698 BLAKE            2850      7782 CLARK            2450      7788 SCOTT            3000      7902 FORD             3000

SQL> select empno,ename,sal from emp where sal<2000 or sal>3000;

     EMPNO ENAME             SAL

---------- ---------- ----------      7369 SMITH             800      7499 ALLEN            1600      7521 WARD             1250      7654 MARTIN           1250      7839 KING             5000      7844 TURNER           1500      7876 ADAMS            1100      7900 JAMES             950      7934 MILLER           1300

9 rows selected.

 

4.3.3 分组查询

语法格式：

select columns_list

from table_name

[where conditional_expression]

group by columns_list

 

SQL> select deptno,job from emp group by deptno,job order by deptno;

    DEPTNO JOB

---------- ---------        10 CLERK        10 MANAGER        10 PRESIDENT        20 ANALYST        20 CLERK        20 MANAGER        30 CLERK        30 MANAGER        30 SALESMAN

9 rows selected.

 

SQL> select deptno as 部门编号,avg(sal) as 平均工资 from emp group by deptno;

部门编号 平均工资

------------ ------------          30   1566.66667          20         2175          10   2916.66667

group by列表需要包括非聚集表达式内的所有列或者与选择列表表达式完全匹配

 

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

    DEPTNO   AVG(SAL)

---------- ----------        30 1566.66667

 

4.3.4 排序查询

语法格式：

select columns_list

from table_name

[where conditional_expression]

[group by columns_list]

order by {order_by_expression[asc|desc]}[,...n]

 

SQL> select deptno,empno,ename from emp order by deptno,empno;

    DEPTNO      EMPNO ENAME

---------- ---------- ----------        10       7782 CLARK        10       7839 KING        10       7934 MILLER        20       7369 SMITH        20       7566 JONES        20       7788 SCOTT        20       7876 ADAMS        20       7902 FORD        30       7499 ALLEN        30       7521 WARD        30       7654 MARTIN

    DEPTNO      EMPNO ENAME

---------- ---------- ----------        30       7698 BLAKE        30       7844 TURNER        30       7900 JAMES

14 rows selected.

 

4.3.5 多表关联查询

1、表别名

 

SQL> select e.empno as  员工编号,e.ename as 员工名称,d.dname as 部门

  2  from emp e,dept d  3  where e.deptno=d.deptno  4  and e.job='MANAGER';

员工编号 员工名 部门

------------ ---------- --------------        7782 CLARK      ACCOUNTING        7566 JONES      RESEARCH        7698 BLAKE      SALES

 

2、内连接

语法格式：

select columns_list

from table_name1 [inner] join table_name2

on join_condition;

 

SQL> select e.empno,e.ename,d.dname

  2  from emp e inner join dept d  3  on e.deptno=d.deptno;

     EMPNO ENAME      DNAME

---------- ---------- --------------      7782 CLARK      ACCOUNTING      7839 KING       ACCOUNTING      7934 MILLER     ACCOUNTING      7566 JONES      RESEARCH      7902 FORD       RESEARCH      7876 ADAMS      RESEARCH      7369 SMITH      RESEARCH      7788 SCOTT      RESEARCH      7521 WARD       SALES      7844 TURNER     SALES      7499 ALLEN      SALES

     EMPNO ENAME      DNAME

---------- ---------- --------------      7900 JAMES      SALES      7698 BLAKE      SALES      7654 MARTIN     SALES

14 rows selected.

 

3、外连接

分类：

左外连接：关键字为left outer join或left join，会包含左表中不满足连接条件的数据行

右外连接：关键字为right outer join或right join，会包含右表中不满足连接条件的数据行

完全外连接：关键字为full outer join或full join

 

SQL> insert into emp(empno,ename,job) values(9527,'EAST','SALESMAN');

1 row created.

SQL> select e.empno,e.ename,e.job,d.deptno,d.dname

  2  from emp e left join dept d  3  on e.deptno=d.deptno  4  ;

     EMPNO ENAME      JOB           DEPTNO DNAME

---------- ---------- --------- ---------- --------------      7934 MILLER     CLERK             10 ACCOUNTING      7839 KING       PRESIDENT         10 ACCOUNTING      7782 CLARK      MANAGER           10 ACCOUNTING      7902 FORD       ANALYST           20 RESEARCH      7876 ADAMS      CLERK             20 RESEARCH      7788 SCOTT      ANALYST           20 RESEARCH      7566 JONES      MANAGER           20 RESEARCH      7369 SMITH      CLERK             20 RESEARCH      7900 JAMES      CLERK             30 SALES      7844 TURNER     SALESMAN          30 SALES      7698 BLAKE      MANAGER           30 SALES

     EMPNO ENAME      JOB           DEPTNO DNAME

---------- ---------- --------- ---------- --------------      7654 MARTIN     SALESMAN          30 SALES      7521 WARD       SALESMAN          30 SALES      7499 ALLEN      SALESMAN          30 SALES      9527 EAST       SALESMAN

15 rows selected.

SQL> select e.empno,e.ename,e.job,d.deptno,d.dname

  2  from emp e right join dept d  3  on e.deptno=d.deptno;

     EMPNO ENAME      JOB           DEPTNO DNAME

---------- ---------- --------- ---------- --------------      7782 CLARK      MANAGER           10 ACCOUNTING      7934 MILLER     CLERK             10 ACCOUNTING      7839 KING       PRESIDENT         10 ACCOUNTING      7566 JONES      MANAGER           20 RESEARCH      7369 SMITH      CLERK             20 RESEARCH      7788 SCOTT      ANALYST           20 RESEARCH      7902 FORD       ANALYST           20 RESEARCH      7876 ADAMS      CLERK             20 RESEARCH      7521 WARD       SALESMAN          30 SALES      7844 TURNER     SALESMAN          30 SALES      7499 ALLEN      SALESMAN          30 SALES

     EMPNO ENAME      JOB           DEPTNO DNAME

---------- ---------- --------- ---------- --------------      7900 JAMES      CLERK             30 SALES      7654 MARTIN     SALESMAN          30 SALES      7698 BLAKE      MANAGER           30 SALES                                        40 OPERATIONS

15 rows selected.

SQL> select e.empno,e.ename,e.job,d.deptno,d.dname

  2  from emp e full join dept d  3  on e.deptno=d.deptno;

     EMPNO ENAME      JOB           DEPTNO DNAME

---------- ---------- --------- ---------- --------------      7369 SMITH      CLERK             20 RESEARCH      7499 ALLEN      SALESMAN          30 SALES      7521 WARD       SALESMAN          30 SALES      7566 JONES      MANAGER           20 RESEARCH      7654 MARTIN     SALESMAN          30 SALES      7698 BLAKE      MANAGER           30 SALES      7782 CLARK      MANAGER           10 ACCOUNTING      7788 SCOTT      ANALYST           20 RESEARCH      7839 KING       PRESIDENT         10 ACCOUNTING      7844 TURNER     SALESMAN          30 SALES      7876 ADAMS      CLERK             20 RESEARCH

     EMPNO ENAME      JOB           DEPTNO DNAME

---------- ---------- --------- ---------- --------------      7900 JAMES      CLERK             30 SALES      7902 FORD       ANALYST           20 RESEARCH      7934 MILLER     CLERK             10 ACCOUNTING      9527 EAST       SALESMAN                                        40 OPERATIONS

16 rows selected.

 

4、自然连接

使用natural join关键字，很少使用，不能为列指定限定词（即表名或者表的别名）

SQL> select empno,ename,job,dname

  2  from emp natural join dept  3  where sal > 2000;

     EMPNO ENAME      JOB       DNAME

---------- ---------- --------- --------------      7782 CLARK      MANAGER   ACCOUNTING      7839 KING       PRESIDENT ACCOUNTING      7902 FORD       ANALYST   RESEARCH      7788 SCOTT      ANALYST   RESEARCH      7566 JONES      MANAGER   RESEARCH      7698 BLAKE      MANAGER   SALES

6 rows selected.

 

5、自连接

 

SQL> select em2.ename manager,em1.ename employee

  2  from emp em1 left join emp em2  3  on em1.mgr=em2.empno  4  order by em1.mgr;

MANAGER    EMPLOYEE

---------- ----------JONES      FORDJONES      SCOTTBLAKE      JAMESBLAKE      TURNERBLAKE      MARTINBLAKE      WARDBLAKE      ALLENCLARK      MILLERSCOTT      ADAMSKING       CLARKKING       BLAKE

MANAGER    EMPLOYEE

---------- ----------KING       JONESFORD       SMITH           EAST           KING

15 rows selected.

 

6、交叉连接

不需要任何连接条件的连接，关键字cross join,执行结果是一个笛卡尔积。

语法格式：

select columns_list

from table_name1 cross join table_name2

 

SQL> select count(*)

  2  from dept cross join emp;

  COUNT(*)

----------        60

 

4.4 Oracle常用系统函数

4.4.1 字符类函数

1、ascii(c)函数和chr(i)函数

字符与ascii码的转换

SQL> select ascii('Z')Z,ascii('H')H,ascii('D')D,ascii(' ')space from dual;

         Z          H          D      SPACE

---------- ---------- ---------- ----------        90         72         68         32

SQL> select chr(90),chr(72),chr(68),chr(32)S from dual;

C C C S

- - - -Z H D

 

2、concat(s1,s2)函数

将字符串s2连接到字符串s1后面

 

SQL> select concat('hello ','world') information from dual;

INFORMATION

-----------hello world

 

3、initcap(s)函数

将字符数s的每个单词的第一个字母大写，其他字母小写。

 

SQL> select initcap('oh my god!') information from dual;

INFORMATIO

----------Oh My God!

 

4、instr(s1,s2[,i][,j])函数

返回字符s2在字符串s1中第j次出现时的位置，搜索从字符串s1的第i个字符开始，当没有发现要查找的字符时，返回0。其中s1和s2均为字符串，i和j均为整数，默认为1。

 

SQL> select instr('oracle 11g','1',3,2) abc from dual;

       ABC

----------         9

 

5、length(s)函数

返回字符串s的长度

 

SQL> select e.empno,e.ename,d.dname

  2  from emp e inner join dept d  3  on e.deptno=d.deptno  4  where length(e.ename)>5;

     EMPNO ENAME      DNAME

---------- ---------- --------------      7654 MARTIN     SALES      7844 TURNER     SALES      7934 MILLER     ACCOUNTING

 

6、lower(s)函数和upper(s)函数

分别返回字符串s的小写形式和大写形式，这两个函数经常出现在where子句中。

 

SQL> select empno,ename,job from emp where lower(job) like 's%';

     EMPNO ENAME      JOB

---------- ---------- ---------      7499 ALLEN      SALESMAN      7521 WARD       SALESMAN      7654 MARTIN     SALESMAN      7844 TURNER     SALESMAN      9527 EAST       SALESMAN

 

7、ltrim(s1,s2)函数，rtrim(s1,s2)函数和trim(s1,s2)函数

分别用来删除字符串s1左边的字符串s2，删除字符串s1右边的字符串s2，删除字符串s1左右两端字符串s2，如果不指定字符串s2，则删除相应方位的空格。

 

SQL> select ltrim('###east###','#') ltrim from dual;

LTRIM

-------east###

 

SQL> select rtrim('###east###','#') rtrim from dual;

RTRIM

-------###east

 

SQL> select trim('#' from '###east###') from dual;

TRIM

----east

 

8、replace(s1,s2[,s3])函数

使用s3字符串替换出现在s1字符串中的所有s2字符串，并返回替换后的新字符串，其中，s3字符串默认为空字符串。

 

SQL> select replace('bad luck bad girl','bad','good') from dual;

REPLACE('BADLUCKBAD

-------------------good luck good girl

 

9、substr(s,i,[j])函数

从字符串s的第i个位置开始截取长度为j的子字符串。如果省略参数j，则直接截取到尾部。

 

SQL> select substr('messagebox',8,3) from dual;

SUB

---box

 

4.4.2 数字类函数

1、ceil(n)函数

大于或等于数值n的最小整数，天花板函数

 

SQL> select ceil(7.3),ceil(7),ceil(-7.3) from dual;

 CEIL(7.3)    CEIL(7) CEIL(-7.3)

---------- ---------- ----------         8          7         -7

2、round(n1,n2)函数

返回舍入小数点右边n2位的n1的值，n2默认为0

 

SQL> select round(3.1415926,2) from dual;

ROUND(3.1415926,2)

------------------              3.14

3、power(n1,n2)函数

返回n1的n2次方

 

SQL> select power(2,3) from dual;

POWER(2,3)

----------         8

 

4.4.3 日期和时间类函数

1、sysdate()函数

返回系统当前的日期

 

SQL> select sysdate from dual;

SYSDATE

------------01-DEC-13

 

2、add_months(d,i)函数

返回日期d加上i个月之后的结果

 

SQL> select add_months(sysdate,6) from dual;

ADD_MONTHS(S

------------01-JUN-14

 

4.4.4 转换类函数

1、to_char(x[,format])函数

将表达式转换成字符串，format表示字符串格式

 

SQL> select sysdate,to_char(sysdate,'YYYY-MM-DD') from dual;

SYSDATE      TO_CHAR(SY

------------ ----------01-DEC-13    2013-12-01

 

2、to_number(s[,format[lan]])函数

返回字符串s代表的数字

 

SQL> select to_number('18','xxx') from dual;

TO_NUMBER('18','XXX')

---------------------                   24

 

4.4.5 集合类函数

 

SQL> select count(empno) as sum,round(avg(sal),2) as avg from emp;

       SUM        AVG

---------- ----------        14    2073.21

 

4.5 子查询的用法

4.5.1 子查询

 

SQL> select empno,ename,job from emp

  2  where deptno=(select deptno from dept where dname='RESEARCH');

     EMPNO ENAME      JOB

---------- ---------- ---------

      7369 SMITH      CLERK

      7566 JONES      MANAGER

      7788 SCOTT      ANALYST

      7876 ADAMS      CLERK

      7902 FORD       ANALYST