1 SQL基本查询
1.1 from子句 别名的使用 where子句 select子句
- SELECT <* , column [ alias ] , …> FROM table;
- SELECT 用于指定要查询的列
- FROM 指定要从哪个表中查询
注意:
(1)如果要查询所有列,可以在SELECT后面使用*号
(2)如果只查询特定的列,可以直接在SELECT后面指定列名,列名之间用逗号隔开。SELECT * FROM emp;
SELECT ename,sal,job FROM emp;
别名
- 在SQL语句中可以通过使用列的别名改变标题的显示样式,或者表示计算结果的含义
- 使用语法是列的别名跟在列名后,中间可以加或不加一个“AS”关键字
- 如果希望别名中区分大小写字符,或者别名中包含字符或空格,则必须用双引号引起来
❤️ 注意:
(1)别名本身不区分大小写,而且不能含有空格。
(2)若希望别名区分大小写或含有空格,那么可以在别名上使用双引号括起来------这两句的效果是一样的
SELECT ename,sal*12 sal FROM emp;
SELECT ename,sal*12 AS sal FROM emp ;
------起别名
SELECT ename,sal*12 AS "s a l " FROM emp ;
WHERE子句
- 在SELECT语句中,可以在WHERE子句中使用比较操作符限制查询结果
- 如果和数字比较,可以使用单引号引起来,也可以不用
- 如果和字符及日期类型的数据比较,则必须用单引号引起来
-----查询部门10下的员工信息 SELECT * FROM emp WHERE deptno=10;
-----查询职员表中职位是“SALESMAN”的职员
SELECT ename,sal,job FROM emp WHERE job='SALESMAN';
SELECT子句
- 如果只查询表的部分列,需要在SELECT后指定列名
- SELECT ename,sal FROM emp;
1.2 查询条件
使用>,< ,>=,<=,!=,<>,=
-----查询员工表中薪水低于2000元的职员信息
SELECT ename,sal FROM emp WHERE sal<2000;
使用AND,OR关键字
- 在SQL操作中,如果希望返回的结果必须满足多个条件,应该使用AND逻辑操作符连接这些条件
- 在SQL操作中,如果希望返回的结果满足多个条件之一即可,应该使用OR逻辑操作符连接这些条件
- AND的优先级高于OR,可以通过括号来提高OR的优先级
----查询薪水大于1000并且职位是‘CLERK’的职员信息
SELECT ename,sal FROM emp WHERE job = 'CLERK' AND sal > 1000;
使用LIKE条件(模糊查询)
- LIKE 用于模糊匹配字符串,支持两个通配符:
_:单一的一个字符
%:任意个字符----查看名字第二个字母是L最后一个字母是N的
SELECT ename FROM emp WHERE ename LIKE '_L%N';
使用IN和NOT IN
- 比较操作符IN(list)用来取出符合列表范围中的数据
- List表示值列表,当列或表达式匹配于列表中的任何一个值时,条件为TRUE,该条记录则被显示出来
- IN也可以理解为一个范围比较操作符,只不过这个范围是一个指定的值列表
- NOT IN(list)取出不符合此列表中的数据记录
----查询职位是MANAGER或CLERK的员工
SELECT ename,job FROM emp WHERE job IN('MANAGER','CLERK');
----查询不是部门10或20的员工
SELECT ename,deptno,job FROM emp WHERE deptno NOT IN (10,20);
BETWEEN … AND…
- BETWEEN … AND…操作符用来查询符合某个值域范围条件的数据
- 最常见的是使用在数字类型的数据范围上,但对字符类型和日期类型数据也同样适用
----查询工资在1500到3000之间的员工(小的在左边大的在右边)
SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;
IS NULL 和 IS NOT NULL
空值NULL是一个特殊的值,比较的时候不能使用“=”号,必须使用IS NULL,否则得不到正确的结果
----查询那些职员的奖金数量为NULL
SELECT ename,sal,comm FROM emp WHERE comm IS NULL;
ANY和ALL
- ANY和ALL不能单独使用,需要配合单行比较操作符>,>=,<,<=一起使用
- >ANY(list):大于列表中最小的
- >ALL(list):大于列表中最大的
- <ANY(list):小于列表中最大的
- <ALL(list):小于列表中最小的
- ANY和ALL常用于子查询
DISTINCT关键字
- 数据表中有可能存储相同数据的行,当执行查询操作时,默认情况会显示所有行,不管查询结果是否有重复数据
- 当重复数据没有实际意义,经常会需要去掉重复值,使用DISTINCT实现
----查看公司共有多少种职位
SELECT DISTINCT job FROM emp;
----多字段去重,是对这些字段值的组合进行去重
SELECT DISTINCT job, deptno FROM emp;
1.3 排序
ASC和DESC
ORDER BY可以根据其后指定的字段对结果集按照该字段的值进行升序或者降序排列。
- ASC:升序,不写默认就是升序
- DESC:降序
----升序(ASC可以省略,默认按照升序排列)
SELECT ename,sal FROM emp ORDER BY sal ASC;
ORDER BY子句
- ORDER BY按照多个字段排序
- ORDER BY首先按照第一个字段的排序方式对结果集进行排序,当第一个字段有重复值时才会按照第二个字段排序方式进行排序,以此类推,每个字段都可以单独指定排序方式。
- 排序的字段中含有NULL值,NULL被认作最大值
SELECT ename,deptno,sal FROM emp ORDER BY deptno DESC , sal DESC;
多个列排序
- 当以多列作为排序标准时,首先按照第一列进行排序,如果第一列数据相同,再以第二列排序,以此类推
- 多列排序时,不管正序还是倒序,每个列需要单独设置排序方式
1.4 聚合函数
MAX和MIN
MAX,MIN求给定字段的最大值或最小值
----查看公司的最高工资和最低工资?
SELECT MAX(sal),MIN(sal) FROM emp;
AVG,SUM
- AVG,SUM求平均值和总和
- 只能操作数字类型
- 忽略NULL值
----AVG,SUM求平均值和总和
SELECT AVG(sal),SUM(sal) FROM emp;
COUNT 函数
- COUNT 函数不是对给定的字段的值进行统计的,而是对给定字段不为NULL的记录数统计的
- 实际上所有聚合函数都忽略NULL值统计
----通常查看表的记录数可以使用COUNT(*)
SELECT COUNT(*) FROM emp;
1.5 分组
GROUP BY子句
- 当希望得到每个部门的平均薪水,而不是整个机构的平均薪水
- 把整个数据表按部门划分成一个个小组,每个小组中包含一行或多行数据,在每个小组中再使用分组函数进行计算,每组返回一个结果
- 划分的小组有多少,最终的结果集行数就有多少
----查看每个部门的平均工资
SELECT AVG(sal) ,deptno FROM emp GROUP BY deptno;----查看每个职位的最高工资
SELECT MAX(sal),job FROM emp GROUP BY job;分组查询
GROUP BY也可以根据多个字段分组,分组原则为这几个字段值都相同的记录看做一组。
----查看同部门同职位的平均工资
SELECT AVG(sal) ,deptno,job FROM emp GROUP BY deptno,job;
HAVING 子句
- HAVING子句用来对分组后的结果进一步限制,比如按部门分组,得到每个部门的最高薪水,可以继续限制输出结果
- HAVING子句必须在GROUP BY后面,不能单独存在
--查看平均工资高于2000的部门的最高工资和最低工资分别是多少?
SELECT MAX(sal),MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;
查询语句执行顺序(面试重点)
查询语句的执行顺序依下列子句次序:
(1)FROM子句:执行顺序为从后往前、从右往左
❤️ 注意:数据量较少的表尽量放在后面(2)WHERE子句:执行顺序为自下而上、从右往左
❤️ 注意:将能过滤掉最大数量记录的条件写在WHERE子句的最右(3)GROUP…BY…子句:执行顺序从左往右分组
❤️ 注意:最好在GROUP BY 前使用WHERE将不需要的记录在GROUP BY之前过滤掉(4) HAVING 子句:消耗资源
❤️ 注意:尽量避免使用,HAVING会在检索出所以记录之后才对结果进行过滤,需要排序等操作(5)SELECT子句:少于 * 号,尽量取字段名称
❤️ 注意:
Oracle在解析的过程中,通过查询数据字典将*号依次转换成所有的列名,消耗时间(6)ORDER BY 子句:执行顺序为从左到右排序,消耗资源
2 关联查询
- 当两张表有同名字段时,SELECT子句中必须明确指定该字段来自哪张表。
- 在关联查询中,表名也可以添加别名,这样可以简化SELECT语句的复杂度。
----查看每个员工的名字以及其所在部门的名字
SELECT e.ename,d.dname FROM emp e,dept d
WHERE e.deptno=d.deptno;
笛卡尔积
- 笛卡尔积指做关联操作的每个表的每一行都和其他表的每一行做组合,假设两个表的记录条数分别是X和Y,笛卡尔积将返回X*Y条记录。
- 关联查询要添加连接条件,否则会产生笛卡尔积
- 笛卡尔积通常是一个无意义的结果集,它的记录数是所有参与查询的表的记录数乘积的结果
- 要避免出现,数据量大时极易出现内存溢出等现象
- N张表关联查询至少要有N-1个连接条件
SELECT e.ename,d.dname FROM emp e,dept d
----查看在NEW YORK工作的员工
SELECT e.ename,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK';
内连接
- 内连接返回所有满足连接条件的记录
左外连接:以JOIN左侧表作为驱动表(所有数据都会被查询出来),那么当该表中的某条记录不满足连接条件时来自右侧表中的字段全部填NULL。
----实现左外连接的一种简单写法
SELECT e.ename,e.deptno,d.deptno,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno(+);
右外连接:以JOIN右侧表作为驱动表(所有数据都会被查询出来),那么当该表中的某条记录不满足连接条件时来自左侧表中的字段全部填NULL。
----实现右外连接的一种简单写法
SELECT e.ename,e.deptno,d.deptno,d.dname
FROM emp e JOIN dept d
ON e.deptno(+)=d.deptno;
❤️ 简记:
(1)(+)写哪边,哪边加NULL
(2)全外连接不能采用这种写法
子查询
子查询根据结果集的不同可以分为:
- 单行单列子查询:常用于过滤条件,可以配合 = ,> , >= , < , <= 使用
- 多行单列子查询:常用于过滤条件,由于查询出多个值,在判断=时要用IN,判断>,>= 等操作时要配合ANY,ALL
- 多行多列子查询:常当做一张表看待。
查询与SALESMAN同部门的其他职位员工
SELECT deptno FROM emp WHERE job='SALESMAN';
SELECT ename,job,deptno FROM emp
WHERE deptno IN(*) AND job != 'SALESMAN';
SELECT ename,job,deptno
FROM emp
WHERE deptno IN (SELECT deptno
FROM emp
WHERE job='SALESMAN')
AND job!='SALESMAN';
查看比CLERK和SALESMAN工资都高的员工?
SELECT sal FROM emp WHERE job IN('CLERK','SALESMAN');
SELECT ename,job,sal FROM emp WHERE sal>ALL(*);
SELECT ename,job,sal
FROM emp
WHERE sal>all(
SELECT sal
FROM emp
WHERE job IN('CLERK','SALESMAN')
);
子查询在WHERE子句中
- 在SELECT查询中,在WHERE查询条件中的限制条件不是一个确定的值,而是来自于另外一个查询的结果
- 为了给查询提供数据而首先执行的查询语句叫做子查询
- 子查询是嵌入在其他SQL语句中的SELECT语句,大部分时候出现在WHERE子句中
- 子查询嵌入的语句称作主查询或父查询
- 主查询可以是SELECT语句,也可以是其他类型的语句比如DML或DDL语句
创建表时若子查询中的字段有别名则该表对应的字段就使用该别名作为其字段名,当子查询中的一个字段含有函数或表达式,那么该字段就必须起别名
--查看谁的工资高于CLARK
SELECT ename,sal FROM emp WHERE ename='CLARK';
SELECT ename,sal FROM emp WHERE sal>2450;
SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='CLARK');
--查看与CLARK同职位的员工?
SELECT ename,job FROM emp WHERE ename='CLARK';
SELECT ename,job FROM emp WHERE job=(SELECT job FROM emp WHERE ename='CLARK');
--查看与CLARK同部门的员工?
SELECT ename,deptno FROM emp e WHERE ename='CLARK';
SELECT ename,deptno FROM emp
WHERE deptno=(SELECT deptno FROM emp e WHERE ename='CLARK');
--将CLOERK所在部门的所有员工删除
DELETE FROM employee
WHERE deptno=(SELECT deptno FROM employee WHERE name = 'CLERK');
SELECT * FROM employee;
EXISTS关键字
EXISTS后面跟一个子查询,当该子查询可以查询出至少一条记录时,则EXISTS表达式成立并返回true。
--有员工的部门
SELECT deptno,dname FROM dept d
WHERE EXISTS (SELECT * FROM emp e
WHERE d.deptno=e.deptno );
子查询在HAVING子句中
/*查看每个部门的最低薪水是多少?前提是该部门的最低薪水要高于30号部门的最低薪水*/ SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30 );
子查询在FROM子句中的使用
当一个子查询是多列子查询,通常将该子查询的结果集当做一张表看待并基于它进行二次查询。
--查看比自己所在部门平均工资高的员工 SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno) t WHERE e.deptno=t.deptno AND e.sal>t.avg_sal;
子查询在SELECT部分
在SELECT子句中使用子查询,可以将查询的结果当做外层查询记录中的一个字段值显示。
SELECT e.ename,e.sal,(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno ) deptno FROM emp e;
分页查询
Oracle中的分页是基于伪列ROWNUM实现的
ROWNUM
ROWNUM不存在于任何一张表中,但是所有的表都可以查询该字段。
该字段的值是随着查询自动生成的,方式是:每当可以从表中查询出一条记录时,
该字段的值即为该条记录的行号,从1开始逐次增加。SELECT ROWNUM rn ,empno,ename,sal,job FROM emp
在使用ROWNUM对结果集进行编号的查询过程中不能使用ROWNUM做大于1以上数字的判断,否则将查询不出任何数据。
SELECT * FROM (SELECT ROWNUM rn ,empno,ename,sal,job FROM emp) WHERE rn BETWEEN 6 AND 10 ;
使用子查询进行分页
- 分页策略:每次只取一页的数据。每次换页,取下一页的数据。
- 在Oracle中利用ROWNUM的功能可用来进行分页
计算区间公式:
pageSize:每页显示的条目数
page:页数star:(page-1)* pageSize +1
end: pageSize * page
decode函数
DECODE函数基本语法
- DECODE(expr,search1,result1 [search 2 , result2] [ ,default] )
- DECODE用于比较参数expr的值,如果匹配到哪一个search条件,就返回对应的result结果
- 可以有多组search和result的对应关系,如果任何一个search条件都没有匹配到,则返回最后default的值
- default参数是可选的,如果没有提供default参数值,当没有匹配到时,将返回NULL。
/*需求:查询职员表,根据职员的职位计算奖励金额,当职位分别是 ’MANAGER’、‘ANALYST’、'SALESMAN’时,奖励金额分别是薪水的 1.2倍、1.1倍、1.05倍,如果不是这三个职位,则奖励金额取薪水值。*/ SELECT ename,sal,job, DECODE(job,'MANAGER',sal*1.2, 'ANALYST',sal*1.1, 'SALESMAN',sal*1.05,sal) FROM emp;
CASE语句
和DECODE函数功能相似的有CASE语句,实现类似于if-else的操作。
SELECT ename,job,sal, CASE job WHEN 'MANAGER' THEN sal*1.2 WHEN 'ANALYST' THEN sal*1.1 WHEN 'SALESMAN' THEN sal*1.05 ELSE sal END bonus /*起别名*/ FROM emp;
排序函数
排序函数允许对结果集按照指定的字段分组,在组内再按照指定的字段排序,最终生成组内编号
ROW_NUMBER
- ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2)
- 表示根据col1分组,在分组内根据col2排序
- 此函数计算的值就表示每组内部排序后的顺序编号,组内连续且唯一
- Rownum是伪列,ROW_NUMBER功能更强,可以直接从结果集中取出子集
/*查看每个部门的工资排名*/ SELECT ename,sal,deptno, ROW_NUMBER() OVER ( PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;
RANK
- RANK () OVER(PARTITION BY col1 ORDER BY col2)
- 表示根据col1分组,在分组内部根据col2给予等级标识
- 等级标识即排名,相同的数据返回相同排名
- 跳跃排名,如果有相同的数据,则排名相同,比如并列第二,则两行数据都标记为2,但下一位将是第四名
- 和ROW_NUMBER的区别是有重复值,而ROW_NUMBER没有
SELECT ename,sal,deptno, RANK() OVER ( PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;
DENSE_RANK
- DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col2)
- 表示根据col1分组,在分组内根据col2给予等级标识即排名,相同的数据返回相同的排名
- 连续排序,如果有并列第二,下一个排序将是三,这一点是和RANK的不同,RANK是跳跃排序
SELECT ename,sal,deptno, DENSE_RANK() OVER ( PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;
集合操作
为了合并多个SELECT语句的结果,可以使用集合操作符,实现集合的并,交,差
- 多条作集合操作操作的SELECT语句的列的个数和数据类型必须匹配
- ORDER BY子句只能放在最后的一个查询语句中
UNION、UNION ALL
- 用来获取两个或两个以上结果集的并集
- UNION操作符会自动去掉合并后的重复记录
- UNION ALL返回两个结果集中的所有行,包括重复的行
- UNION操作符对查询结果排序,UNION ALL不排序
/*合并职位是‘MANAGER’的员工和薪水大于2500的 员工集合,查看两种方式的结果差别*/ SELECT ename,job,sal FROM emp WHERE job='MANAGER' UNION SELECT ename,job,sal FROM emp WHERE sal>2500;
INTERSECT
- 获得两个结果集的交集,只有同时存在于两个结果集中的数据,才被显示输出
- 使用INTERSECT操作符后的结果集会以第一列的数据作升序排列
/*/显示职位是’MANAGER’的员工和薪水大于2500的员工的交集*/ SELECT ename,job,sal FROM emp WHERE job='MANAGER' INTERSECT SELECT ename,job,sal FROM emp WHERE sal>2500;
MINUS
- 获取两个结果集的差集
- 只有在第一个结果集中存在,在第二个结果集中不存在的数据,才能够被显示出来。也就是结果集一减去结果集二的结果
/*列出职位是MANAGER但薪水低于2500的员工记录*/ SELECT ename,job,sal FROM emp WHERE job = 'MANAGER' MINUS SELECT ename,job,sal FROM emp WHERE sal>=2500
高级分组函数
- ROLLUP、CUBE和GROUPING SETS运算符是GROUP BY子句的扩展,可以生成与使用UNION ALL来组合单个分组查询时相同的结果集,用来简化和高效的实现统计查询
- GROUP BY ROLLUP(a,b,c)
- GROUP BY CUBE(a,b,c)
- GROUP BY GROUPING SETS((a),(b))
ROLLUP
ROLLUP():分组原则,参数逐次递减,一直到所有参数都不要,每一种分组都统计一次结果,并且并在一个结果集显示。
GROUP BY ROLLUP(a,b,c) 等价于: GROUP BY a , b , c UNION ALL GROUP BY a , b UNION ALL GROUP BY a UNION ALL 全表
/*查看每天、每月、每年以及总共的营业额*/ SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY ROLLUP(year_id,month_id,day_id)
CUBE
- GROUP BY CUBE(a,b,c)
- 对cube的每个参数,都可以理解为取值为参与分组和不参与分组两个值的一个维度,所有维度取值的集合就是分组后的集合。
- 对于n个参数的cube,有2^n次分组
- GROUP BY CUBE(a,b,c),首先对(a,b,c)进行GROUP BY ,然后依次是(a,b)、(a,c)、(a)、(b,c)、(b)、(c),最后对全表进行GROUP BY操作,一共是2^3=8次分组
GROUPING SETS
- GROUPING SETS运算符可以生成与使用单个GROUP BY 、ROLLUP或CUBE运算符所生成结果集相同的结果集,但GROUPING SETS使用起来更灵活
- 如果不需要获得由完备的ROLLUP、CUBE运算符生成的全部分组,则可以使用GROUPING SETS仅指定所需的分组
- GROUPING SETS列表可以包含重复的分组
GROUPING SETS:每个参数是一种分组方式,然后将这些分组统计后并在一个结果集显示。/*查看每天与每月的营业额*/ SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id));
索引
- 索引是一种允许直接访问数据表中某一数据行的树型结构,为了提高查询效率而引入,是独立于表的对象,可以存放在与表不同的表空间(TABLESPACE)中
- 索引记录中存有索引关键字和指向表中数据的指针(地址)
- 对索引进行的I/O操作比对表进行操作要少很多
- 索引一旦被建立就将Oracle系统自动维护,查询语句中不用指定使用哪个索引
- 索引是一种提高查询效率的机制
❤️ 注意:
索引是数据库对象之一,索引是为了提高查询效率。索引的统计与应用是数据库自动完成的,只要数据库认为可以使用某个已创建的索引时就会自动应用。索引的类型
- 唯一索引: 唯一索引确保在定义索引的列中没有重复值
Oracle 自动在表的主键列上创建唯一索引
使用CREATE UNIQUE INDEX语句创建唯一索引语法:create unique index index_name on table_name (column_name);
具体列值: 索引相关列上的值必须唯一,但可以不限制NULL值。
- 组合索引: 组合索引是在表的多个列上创建的索引
索引中列的顺序是任意的
如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列,则可以提高检索速度语法:create index index_name on table_name (column_name1,column_name2);
具体列值:该表中的元组由两列共同确定一行,例如班级号 学号 唯一确定一个学生。
- 反向键索引: 反向键索引反转索引列键值的每个字节,为了实现索引的均匀分配,避免b树不平衡
通常建立在值是连续增长的列上,使数据均匀地分布在整个索引上
创建索引时使用REVERSE关键字语法:create index index_name on table_name (column_name) reverse;
具体列值: 适用于某列值前面相同,后几位不同的情况,例如
sno: 1001 1002 1003 1004 1005 1006 1007
索引转化:1001 2001 3001 4001 5001 6001 7001
- 位图索引: 位图索引适合创建在低基数列上
位图索引不直接存储ROWID,而是存储字节位到ROWID的映射
节省空间占用
如果索引列被经常更新的话,不适合建立位图索引
总体来说,位图索引适合于数据仓库中,不适合OLTP中语法:create bitmap index index_name on table_name (column_name);
具体列值: 不适用于经常更新的列,适用于条目多但取值类别少的列,例如性别列。
- 基于函数的索引: 基于一个或多个列上的函数或表达式创建的索引
表达式中不能出现聚合函数
不能在LOB类型的列上创建
创建时必须具有 QUERY REWRITE 权限语法:create index index_name on table_name (函数(column_name));
具体列值: 不能在LOB类型的列上创建,用户在该列上对该函数有经常性的要求。
例如:用户不知道存储时候姓名是大写还是小写,使用
select * from student where upper(sname)=‘TOM’;
本文参考博客: