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
------------------------------
DEPT
EMP
BONUS
SALGRADE
SQL> select table_name from dba_tables where owner='SCOTT';
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
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 7369
SALESMAN ALLEN 7499
SALESMAN WARD 7521
MANAGER JONES 7566
SALESMAN MARTIN 7654
MANAGER BLAKE 7698
MANAGER CLARK 7782
ANALYST SCOTT 7788
PRESIDENT KING 7839
SALESMAN TURNER 7844
CLERK ADAMS 7876
JOB ENAME EMPNO
--------- ---------- ----------
CLERK JAMES 7900
ANALYST FORD 7902
CLERK MILLER 7934
14 rows selected.
SQL> select rowid,job,ename from emp;
ROWID JOB ENAME
------------------ --------- ----------
AAAR3xAAEAAAACXAAA CLERK SMITH
AAAR3xAAEAAAACXAAB SALESMAN ALLEN
AAAR3xAAEAAAACXAAC SALESMAN WARD
AAAR3xAAEAAAACXAAD MANAGER JONES
AAAR3xAAEAAAACXAAE SALESMAN MARTIN
AAAR3xAAEAAAACXAAF MANAGER BLAKE
AAAR3xAAEAAAACXAAG MANAGER CLARK
AAAR3xAAEAAAACXAAH ANALYST SCOTT
AAAR3xAAEAAAACXAAI PRESIDENT KING
AAAR3xAAEAAAACXAAJ SALESMAN TURNER
AAAR3xAAEAAAACXAAK CLERK ADAMS
ROWID JOB ENAME
------------------ --------- ----------
AAAR3xAAEAAAACXAAL CLERK JAMES
AAAR3xAAEAAAACXAAM ANALYST FORD
AAAR3xAAEAAAACXAAN 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
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
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 FORD
JONES SCOTT
BLAKE JAMES
BLAKE TURNER
BLAKE MARTIN
BLAKE WARD
BLAKE ALLEN
CLARK MILLER
SCOTT ADAMS
KING CLARK
KING BLAKE
MANAGER EMPLOYEE
---------- ----------
KING JONES
FORD 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
子查询必须用括号（）括起来
子查询中不能包括order by子句
子查询允许嵌套多层，但不能超过255层
转载于:https://blog.51cto.com/stonebox/1342104
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
