一、 简单查询
- 简单查询
SELECT 字段[,字段] FROM 表;
#例子:
SELECT ename, empno FROM emp;
- 字段和表起别名
字段 [as] 别名;表名 别名
注:别名可用双引号括起来(但因不可用,一般在表述数据值的时候使用,不在字段名、表名、关键字使用),此时所见即所得。当然不用也可以,但英文自动转换为大写。
SELECT ename as name from emp person;
- 删除重复复数据(整行而言)DISTINCT
SELECT DISTINCT ename,deptno FROM emp;
- 字段查询允许使用函数和运算
SELECT (sal+300)*12 from emp;
- 连接字段:有点像格式化文本,使用 ||
SELECT ('雇员的编号是:' || empno || ',顾明姓名:' || ename) as 输出 FROM emp;
- 查询日期列:和查询其他类型的列并没有区别,需要注意的
是显示格式:每种环境是不一样的,在 sqlplus 中默认的显示格式是 DD-MON-RR,即两位日-月-两位年,可以用函数转to_date换输出格式。
二、 条件查询
限定查询就是在之前语法的基础上增加了一个 WHERE 子
句,用于指定限定的条件,在 WHERE 子句之后可以增加多个条件,最常见的条件就是基本的关系运算: >、 >=、 <、<=、 !=(<>)、BETWEEN…AND、 LIKE、 IN、 IS NULL 、 AND、 OR、 NOT,数字和字符串都可以作为条件,此时格式如下:
SELECT [DISTINCT] 字段1 AS 别名1 [,字段2 AS 别名2] FROM 表名 [WHERE 条件s]
- 关系查询
要求查询出基本工资高于 1500 的所有雇员信息
SELECT * FROM emp WHERE sal > 1500;
查询出所有职位是办事员的雇员信息
SELECT * FROM emp WHERE job = 'CLERK' #对于数据是大小写敏感的
多条件之间可以使用 AND或者 OR 进行连接操作
查询工资在 1500-3000 之间的全部雇员信息
SELECT * FROM emp WHERE sal > 1500 and sal < 3000;
查询出职位是办事员或者是销售人员的全部信息,并且要求这些雇员的工资大于 1200
SELECT * FROM emp WHERE (job = 'CLERK' OR job = 'SALEMAN') AND sal > 1200;
- 范围判断: BETWEEN…AND…,可以进行取反操作,数字和日期都可用
要求查询出基本工资在 1500-3000 的雇员信息
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;必须前边的数小于后边
要求查询出基本工资不在 1500-3000 的雇员信息
SELECT * FROM emp WHERE sal NOT BETWEEN 1500 AND 3000;必须前边的数小于
要求查询出在 1981 年雇佣的全部雇员信息
SELECT * FROM emp WHERE hiredate BETWEEN to_date('01-01-1981','DD-MM-YY') AND to_date('31-12-1981','DD-MM-YY');
- 判断是否为空:IS (NOT) NULL
查询出所有领取奖金的雇员信息
SELECT * FROM emp WHERE comm IS NOT NULL
- 指定范围的判断: IN,可进行取反操作,可以是数字也可以是字符串
查询出雇员号是 7369、 7566、 7799 的雇员信息
SELECT * FROM emp WHERE empno IN (7369,7566,7799)
注意:如果现在使用了 IN 操作符,查询的范围之中存在了 NULL,不影响查询;如果现在使用的是 NOT IN 操作符,如果查询的范围之中有了 NULL,则不会有任何的查询结果返回。
- 模糊查询:LIKE,支持去反操作,可在日期使用(查询时内部把日期列转换为字符串)。效率低,一把不用在数据库中,会占用资源。
匹配单个字符: _ ;匹配任意多个字符: %
要求查询雇员姓名中以字母 A 开头的全部雇员信息
SELECT * FROM emp WHERE ename LIKE 'A%'
要求查询出雇员姓名中第二个字母是 A 的全部雇员信息
SELECT * FROM emp WHERE ename LIKE '_A%'
要求查询出雇员姓名中带有字母 A 的雇员
SELECT * FROM emp WHERE ename LIKE '%A%'
要求查询出在 1981 年雇佣的雇员信息
SELECT 8 from WHERE hiredate LIKE '%81%'
在开发之中,数据库的模糊查询肯定使用 LIKE 子句,但是在使用 LIKE 子句的时候有一个最大的注意点:如果在模糊查询上不设置任何的查询的关键字(%%)的话,则表示查询全部记录:这个特点可以帮助用户节约很多的代码,所以一定要记住。
- 运算符的优先级规则:比较操作符>NOT>AND>OR
SELECT ename,sal,job FROM emp
WHERE job='SALESMAN' OR job='ANALYST' AND sal>2000;
先判断符合job='ANALYST' AND sal>2000,在聚合符合条件job='SALESMAN'的结果
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
WARD 1250 SALESMAN
MARTIN 1250 SALESMAN
TURNER 1500 SALESMAN
FORD 3000 ANALYST
三、数据排序
基本语法:
SELECT [DISTINCT] *| 字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
[WHERE 条件(s)];
[ORDER BY 字段 [ASC|DESC] [,字段 [ASC|DESC],...]];
ORDER BY子句是写在所有的 SQL 语句最后的内容排序的时候可以指定多个排序的字段(按字段顺序依次进行),默认升序(ASC),降序(DESC)。
- 升序降序排列
按照工资排序
SELECT * FROM emp ORDER BY sal [ASC];
显示所有的销售人员的工资,按降序排列
SELECT * FROM emp WHERE job = 'SALEMAN' ORDER BY sal DESC;
- 使用非选择列表排序:作为排序的依据,但是 SELECT 列表中
也可以不出现排序的列 - 使用表达式或者别名排序:
SELECT sal*12+nvl(comm,0) AS 年薪 FROM emp ORDER BY 年薪;
- 使用多列排序:首先按照第一列进行排序,如果第一列数据相同,再以第二列排序,以此类推。
三、单行函数
SQL的函数分为单行函数和多行函数,单行函数对于每一行数据进行计算后得到一行输出结果;多行函数是多行参与运算得到一行输出结果。
- 字符函数
函数名 | 作用 |
---|---|
UPPER(字符串/列) | 将输入的字符串变为大写返回 |
LOWER(字符串/列) | 将输入的字符串变为小写返回 |
INITCAP(字符串/列) | 开头首字母大写 |
LENGTH(字符串/列) | 求出字符串的长度 |
REPLACE(字符串/列,原始值,替换值) | 进行替换 |
SUBSTR(字符串/列,开始点,[长度]) | 字符串截取,不写长度默认截取到尾巴 |
CONCAT(字符串1,字符串2) | 字符串连接 |
INSTR(字符串1,字符串2[, n [,m]]) | 在字符串1中搜索字符串2,从 n 的位置开始搜索,如果没有指定 n,就从第一个字符开始搜索。 m 用于指定子串的第 m 次出现的次数,如果不指定取值为 1。如果在字符串1中没有找到子串,则返回 0 |
TRIM(字符串2 FROM 字符串1) | 在字符串1两端去掉字符串2,但字符串2只能是一个字符 |
LTRIM(字符串1 [,字符串2)] | 在字符串1左端去掉字符串2,字符串2时默认去空格 ,但字符串2只能是一个字符 |
RTRIM(字符串1 [,字符串2)] | 在字符串1右端去掉字符串2,字符串2时默认去空格 ,但字符串2只能是一个字符 |
ASCII(字符) | 返回字符的ASCII码值,若输入是字符串只去第一个字符 |
CHAR(n) | 返回n所对应ASCII的字符 |
Oracle 之中有一点比较麻烦,即使要验证字符串,也必须编写完整的 SQL 语句,from后必须加表。所以Oracle 数据库之中为了用户的查询方便,所以专门提供了一个”dual”的虚拟表。
&提示内容:表示让用户输入相应内容。&提示内容有时加引号表示输入的文字转换为字符串,当然若客户输入时加上了单引号,这里就可以不用加。
将输入的字符串变为大写返回
SELECT * FROM emp where ename = UPPER('&str')
将每一个雇员姓名和职位的开头首字母大写
SELECT INITCAP(ename) FROM emp;
查询出每个雇员姓名以及姓名的长度
SELECT ename,LENGTH(ename) FROM emp;
要求查询出姓名长度正好是 5 的雇员信息
SELECT * FROM emp WHERE LENGTH(ename)=5;
将Hello中的ll替换为kk
SELECT REPLACE('Hello','ll','kk') FROM dual;
要求从第三个字母开始截取每个雇员姓名
SELECT SUBSTR(ename,3) FROM EMP;
要求截取每个雇员姓名的前三个字母
SELECT SUBSTR(ename,1,3) FROM EMP;在oracle起始位0和1没有区别,一般为1,传0内部会变为1
要求截取每个雇员姓名的后三个字母
SELECT SUBSTR(ename,-3 [,3]) FROM EMP;
将表中的姓名重复两遍输出
SELECT CONCAT(ename,ename) FROM emp;
- 数字函数
函数名 | 作用 |
---|---|
FLOOR(n) | 返回小于或是等于 n 的最大整数 |
CEIL(n) | 返回大于或是等于 n 的最小整数 |
ABS(n) | 返回数字 n 的绝对值 |
ACOS(n)、ASIN(n)、ATAN(n) | 返回数字的反余弦值、反正弦值、反正切值 |
EXP(n) | 返回 e 的 n 次幂 |
LOG(m,n) | 返回对数值 |
POWER(m,n) | 返回 m 的 n 次幂 |
- 日期函数
在所有的开发之中,如果是日期操建议使用以下的函数,因为这些函数可以避免闰年的
问题。
1) 日期+数字=日期,表示若干天之后的日期
SELECT SYSDATE+10 from dual;
2) 日期-数字=日期,表示若干天之前的日期
SELECT SYSDATE-10 from dual;
3) 日期-日期=数字,表示的是两个日期间的天数,但是肯定是大日期-小日期
SELECT sysdate-hiredate FROM emp;
注:国一星期:周日到周六,周日为中国的周一,周六为周七
4)LAST_DAY(日期):求出指定日期的最后一天
5)NEXT_DAY(日期,星期数):求出下一个指定星期 X 的日期
SELECT next_day(sysdate,7) FROM dual;下一个星期六
6)ADD_MONTHS(日期,数字):求出若个月之后的日期
SELECT add_months(sysdate,4) FROM dual;
7)MONTHS_BETWEEN(日期 1,日期 2):求出两个日期之间所经历的月份,第一个参数为大日期,还包括开始和结束的当月
- 转换函数
主要功能是完成这几个数据间的互相转换操作。
函数名 | 作用 |
---|---|
TO_CHAR(字符串/列,格式字符串) | 参考 |
TO_DATE(字符串/列,格式字符串) | 参考 |
TO_NUMBER(字符串/列,格式字符串) | 将字符串变为数字显示 |
TO_NUMBER()基本不用,因为Oracle 之中真的很智能,所以以上功能不使用 TO_NUMBER()也可完成。Select ‘123’+’456’ from dual
- 通用函数:函数主要有两个: NVL()、 DECODE(),这两个函数就是 Oracle 自己的特色函数。
函数名 | 作用 |
---|---|
NVL(expr1,expr2) | 如果 expr1 是 null,则用 expr2 值代替。 |
NVL2(expr1,expr2,expr3) | 如果 expr1 不是 null,用 expr2 值代替,否则 null 用 expr3 代替 |
DECODE(数值/列, 判断值 1, 显示值 1 [判断值 2,显示值 2]) | 多数值判断,不合符规范要求的用null代替 |
将CLERK用办事员显示,PRESIDENT用总裁显示
SELECT DECODE(JOB,'CLERK','办事员','predident','总裁')FROM emp;
四、多表查询
- 基本概念
**笛卡尔积:**CROSS JOIN
SELECT * FROM 表1,表2;
SELECT * FROM 表1 CROSS JOIN 表2;
内连接:
情形一:取相同字段的行
查询结果之中已经消除了笛卡儿积,但是现在只是属于显示上的消除,而真正卡儿积依然存在,因为数据库的操作机制就是属于逐行的进行数据判断。因此照此理解,如果现在假设两张表的数据量都很大的话,那么使用这种多表查询的性能不高。开发过程中,不到万不得已不会用多表查询。
SELECT * FROM 表1 [INNER] JOIN 表2 ON 表1.字段 = 表2.字段;
等价于
SELECT * FROM 表1, 表2 ON 表1.字段 = 表2.字段;笛卡尔积后取匹配字段行
情形二:取字段不同的行
SELECT * FROM 表1 [INNER] JOIN 表2 ON 表1.字段 !=[<>] 表2.字段;
或者
SELECT * FROM 表1 FULL/LEFT/RIGHT [OUTER] JOIN 表2 ON 表1.字段 !=[<>] 表2.字段;
全外连接:相互匹配加上相互没有匹配上的行,并用NULL填充。
SELECT * FROM 表1 FULL [OUTER] JOIN 表2 ON 表1.字段 = 表2.字段;
左外链接:以左表为基准,匹配右表,无法匹配时右表对应字段填充NULL.
SELECT * FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1.字段 = 表2.字段;
右外链接:以右表为基准,匹配左表,无法匹配时左表对应字段填充NULL.
SELECT * FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1.字段 = 表2.字段;
例题:
注:连接后的表不可以直接起别名。
每一位雇员的姓名、职位、雇员的直接上级领导的姓名
SELECT e1.ename, e1.job, e2.ename FROM emp e1 inner join emp e2 on e1.mgr = e2.empno;
查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。
select h.empno from (SELECT e1.empno, e1.ename, e1.sal, e1.job,e1.deptno, e2.ename leader FROM emp e1 inner join emp e2 on e1.mgr = e2.empno) e3 left join dept d on e3.deptno = d.deptno;
上述很麻烦,因为是两个构成一张表在进行连接,Oracle是支持多表连接的。
select e1.empno, e1.ename, e1.sal, e1.job,e1.deptno, e2.ename leader, d.dname, d.loc FROM emp e1 inner join emp e2 on e1.mgr = e2.empno left join dept d on e1.deptno = d.deptno;
注:在 Oracle 之外的数据库都使用以上的 SQL:1999 语法操作
- 交叉连接(CROSS JOIN):用于产生笛卡尔积
- 自然连接(NATURAL JOIN) :自动找到匹配的关键字段,消除笛卡尔积
- JOIN … USING 子句:用户自己指定一个消除笛卡尔积的关键字段
- JOIN …ON 子句:用户自己指定一个可以消除笛卡尔积的关联条件
- 左(外)连接: LEFT OUTER JOIN … ON
- 右(外)连接: RIGHT OUTER JOIN … ON
- 全(外)连接: FULL OUTER JOIN … ON
五、统计函数和分组查询
进行分组的话,则 SELECT 子句之后,只能出现分组的字段和统计函数,这是因为如果你按某一字段进行了分组,但是你却要全显示另外一组字段,那它只能以一一展开向原始表一样,只是将原始表按分组字段进行重排序,这样是没有意义的。分组本身就是一组聚合,即将该组成员的整体情况进行分析,合多化一,为此其他字段不可以逐一显示,但可以用聚合函数反应整体值,分组字段也可以显示。
- 统计函数:
函数名 | 作用 |
---|---|
COUNT( ) | 查询有多少记录 |
AVG( ) | 求平均值 |
SUM( ) | 求和 |
MAX() | 求最大 |
MIN() | 求最小 |
注:
COUNT( ) 函数的主要功能是进行数据统计,但是在进行数据统计的候,如果一张表中没有统计记录, COUNT()也会返回数据,只是这个数据是“0”。如果使用的是其它函数,则有可能返回的是 null,但是 COUNT()永远都会返回一个具体的数字,这一点以后在开发之中都会使用到。
统计出公司的所有员工,每个月支付的平均工资及总工资
SELECT COUNT(ename), AVG(sal), SUM(sal) FROM emp;
- 分组统计:
分组之中有一个不成文的规定:当数据重复的时候分组才有意义,因为一个人也可以分组(没什么实际意义), 而如果要想分组,则需要使用 GROUP BY 子句完成,语法格式如下:
SELECT [DISTINCT] * | 分组字段 1 [别名][,分组字段 2 [别名], …] | 统计函数
FROM 表名称[别名], [表名称[别名], ……]
[WHERE 条件(s)]
[GROUP BY 分组字段 1 [,分组字段 2,…]]
[ORDER BY 排序字段 ASC|DESC] [,排序字段 ASC|DESC,……]]
分组后的注意事项:
- SELECT 子句之后,只能出现分组的字段和统计函数。
- 分组函数允许嵌套,但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段。这是因为,嵌套是二次组聚集,如果在查询其他字段或分组,为难数据库构造一张表来显示其信息。
按照职位分组,统计平均工资最高的工资
SELECT MAX(AVG(sal)) FROM emp GROUP BY job;
但是,嵌套之后不允许再出现其他字段,即使是分组字段或者其他统计字段。
SELECT MAX(AVG(sal)),job from emp group by job;---错
- 不管是否是实体表还是虚拟表,如多表连接,只要有重复,那么就直接进行分组。
查询出每个部门的名称、部门的人数、平均工资
SELECT d.dname, COUNT(e.ename), AVG(e.sal) FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno GROUP BY d.dname;
- 可以实现多字段分组,多字段分组其实就是在分组字段的所有组合情况下分组。
select d.deptno,d.dname,d.loc,count(e.deptno),avg(e.sal) from (dept d left join emp e on e.deptno=d.depto) group by d.dname,d.deptno,d.loc having avg(e.sal)>2000;
这里的分组字段是dname、deptno、loc,我们发现,这三个是都是唯一对应的对任意一个分组都对其他两个的结果一样,但是对某一个分组时就不一查询另外的字段,次是为了可以查询就加入了其他两个字段。当三者并不唯一对应时,此时多字段分组和单字段分组下的结果是不一样的。
- 一般来说显示进行where筛选需要的数据,然后进行分组,若想分组后继续进行过滤,此时必定用到了分组字段或者是分组函数,则应当那使用Having子句完成。因为Where不能使用统计函数,仅能从全部的数据之中取出部分的数据。
SELECT [DISTINCT] * | 分组字段 1 [别名][,分组字段 2 [别名], …] | 统计函数
FROM 表名称[别名], [表名称[别名], ……]
[WHERE 条件(s)]
[GROUP BY 分组字段 1 [,分组字段 2,…]]
[HAVING 分组后的过滤条件(可以使用统计函数) ]
[ORDER BY 排序字段 ASC|DESC] [,排序字段 ASC|DESC,……]]
显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事
同一工作的雇员的月工资合计大于$5000,输出结果按月工资的合计升序排列。
SELECT e.job, SUM(sal) FROM emp e WHERE e.job != 'SALESMAN' GROUP BY e.job HAVING SUM(sal)>5000 ORDER BY SUM(sal) ASC;
六、子查询
子查询 = 简单查询+限定查询+多表查询+统计查询的综合体。
子查询解决关联查询的性能问题,很多表关联关联时使得表很大造成性能问题(形成很大的大表放入内存)。子查询相当于sql语句分别执行。所以多表查询不建议大家使用,因为性能很差,但是多表查询最有利的替代者就是子查询,所以子查询在实际的开发之中使用的相当的多。子查询指的就是在一个查询之中嵌套其他的若干查询,理论上子查询可以出现在查询语句的任意位置上,但是从个人而言,子查询出现自WHERE 和 FROM 子句之中比较多。
6.1where
单行单列:
查询出工资比 SMITH 还要高的全部雇员信息
select * from emp where emp.sal>(select emp.sal from emp where emp.ename='SMITH')
多行单列:涉及做个值,需要使用三种判断符判断了: IN、 ANY、 ALL
- IN 操作符:用于指定一个子查询的判断范围,使用 IN 的时候还要注意 NOT IN 的问题,如果使用 NOT IN 操作,在子查询之中,如果有一个内容是 null,则不会查询出任何的结果。
找出和经理一样工资的员工信息
select * from emp where sal in (select sal from emp where emp.job='MANAGER')
- ANY 操作符:与每一个内容相匹配,有三种匹配形式
1) =ANY: 功能与 IN 操作符是完全一样的
2)>ANY: 比子查询中返回记录最小的还要大的数据
3)<ANY: 比子查询中返回记录的最大的还要小 - ALL 操作符:与每一个内容相匹配,有两种匹配形式
1)>ALL:比子查询中返回的最大的记录还要大
2)<ALL:比子查询中返回的最小的记录还要小 - 子查询返回的一般是多行多列的数据,当作一张临时表出现
6.2from
FROM 子句中出现的子查询,这个子查询一般返回的是多行多列的数据,当做一张临时表的方式来处理的。
查询出每个部门的编号、名称、位置、部门人数、平均工资
select d.deptno,d.dname,d.loc,count(e.deptno),avg(e.sal) from (dept d left join emp e on e.deptno=d.depto) group by d.dname,d.deptno,d.loc;
这里dept4条数据,emp14条数据,笛卡尔积56条数据。也就是说此时内存要放56条数据以供分组等各个操作。
利用子查询:
select d.deptno,d.dname,d.loc,person_num, avg_sal from dept d left join (select deptno, avg(sal) avg_sal, count(deptno) person_num from emp group by deptno) e1 on d.deptno = e1.deptno;
子查询需要14条数据,展示了4条数据,跟dept的3条数据进行笛卡尔积产生12条数据,共计内存放26条数据进行操作。
子查询的确要比使用多表查询更加节省性能,所以在开发之中子查询出现时最多的, 而且再给出一个开发不成文的规定:大部分情况下,如果最
终的查询结果之中需要出现 SELECT 子句,但是又不能直接使用统计函数的时候,就在子查询中统计信息,即:有复杂统计的地方大部分都需要子查询。
七、数据伪列
数据伪列指的是用户不需要处理的列,而是由 ORACLE 自行维护的数据列,在 ORACLE之中有两个数据伪列: ROWNUM、 ROWID。
- ROWNUM:ROWNUM 为每一个显示的记录都会自动的随着查询生成行号,ROWNUN 行号并不是永久固定的,是每次动态重新生成的,那么既然有了 ROWNUM 之后,下面可以实现数据的部分显示。同时,rownum不能以任何基表的名称作为前缀。
关于ROWNUM不支持>、>=、=、between…and”运算符解释,参考
查询行号
SELECT ROWNUM, e.* FROM emp e; *已经表示所有数据了,但不包含行号,所以有行号时需要别名
查询前 5 条记录
SELECT ROWNUM, e.* from emp e WHERE ROWNUM<5;
rownum不支持大于和小于同时使用,所以是用不了between...and..
先查询前 6-10 条记录,要依靠子查询完成
SElECT * FROM (SELECT ROWNUM as rm, e.* from emp e WHERE ROWNUM<11) e1 WHERE e1.rm>5;
注意错误写法:
SElECT * FROM (SELECT ROWNUM, e.* from emp e WHERE ROWNUM<11) e1 WHERE e1.ROWNUM>5;
这里e1.ROWNUM错误的,因为ROWNUM不能以任何基表的名称作为前缀,为此需要把这一列变为表本身的列,利用起别名即可。
分页语法:
startNum = (pageNo - 1)* pageSize
endNum = (pageNO * pageSize)+ 1
select * from (select rownum as rm,e.* from emp e where rownum<to_number('&endNum')) b where b.rm > to_number('&startNum');
- ROWID:ROWID 表示的是每一行数据保存的物理地址的编号每一条记录的 ROWID 都不会重复,所以即便表中所有列的数据内容都重复了, ROWID也是不会重复的。
ROWID 组成:
●数据对象号:前六位
●相对文件号:随后3位
●数据块号:随后6位
●数据行号:后三位
小结,在所有的伪列之中,只有 ROWNUM 是以后开发之中最为重要的部分,一定要掌握,而对于 ROWID 知道就行了,不必做太深入的了解。