提示：此文章下的code示例皆在cmd下运行，未借助可视化工具
文章目录
- 一、Note
- 二、Code
- 1、查看表结构&设置显示样式
- 2、简单查询&查询伪列&查询虚表
- 3、dual表应用&算术运算&列别名
- 4、dual表应用&拼接&tochar()&处理空值NVL()&去重
- 5、使用where数据过滤&使用order by数据排序
- 6、比较运算符：any / all
- 7、SQL运算符：in / is null / between...and...
- 8、SQL运算符：like模糊查询&upper()&lower()
- 10、逻辑运算符：not / and /or
- 11、组函数 / 数据分组 / 分组条件&select列表关系
- 12、count() / min() / max() /日期格式/having子句筛选分组后的数据
一、Note
1、查看用户的数据库对象及表
-
查询当前用户所拥有的所有的数据库对象
SELECT object_name FROM user_objects ;SELECT count( object_name ) FROM user_objects ;
-
查询当前用户所拥有的所有的表
SELECT table_name FROM user_tables ;SELECT count( table_name ) FROM user_tables;
2、设置显示方式
2.1、显示/设置一页可以显示多少行
show pagesizeSET pagesize n- n最大值为50000，默认值为14
2.2、显示/设置当前sqlplus每行的长度(字节)
show linesizeSET linesize n- 有效范围是1-32767，默认情况下显示80个字符
2.3、设置列的格式
-
COL[UMN] 列名 FOR[MAT] An;（超过n个就换行） -
可以用A/a，表示针对字符串类型，n是一个数字
-
SQL>
col salary for $99,999.00- 表示此列的显示按千位一逗号的格式，最大显示数字99999.99
- 对于数字类型的列，不用an，而是以$开头
-
使用column命令的format子句的重新定义列的显式格式
An：设置非数值类型列(如char,vatchar,date,long等)为varchar类型的列的列内容设置宽度，如果内容超过指定的宽度，则内容自动换行；例如：A109：在数值类型列 (如NUMBER) 上禁止显式前导0；例如：999 999，代表列有6位数，不足5位数也不补00：在数值类型列 (如NUMBER) 上强制显式前导0；例如：000 000，代表列有6位数，不足5位数补0$：在数值类型列 (如NUMBER) 上显示美元符号；例如： $99L：在数值类型列 (如NUMBER) 上显示本地的货币符号；例如： L99.：指定数值类型列 (如NUMBER) 的小数点位置；例如： 9999.9,：指定数值类型列 (如NUMBER) 的千位分隔符 ；例如：9，999
2.4、改变列的显示名称，用heading
- SQL>
col 列名 heading '预定义名'
2.5、取消列名预定义
- SQL>
col 列名 clear
2.6、注意
- 只要不取消列的预定义，则在退出当前sqlplus前永久有效
- 当列的预定义与select中的列取别名冲突时，预定义优先
3、查看表结构
DESCRIBE 表名 ;DESC 表名 ;
4、简单查询
4.1、查询部分列
-
语法：
SELECT 列名 [ , ...] FROM 表名 ;-
举例：
SELECT table_name FROM user_tables ; -
举例：
SELECT object_name , object_type FROM user_objects ; -
user_tables和user_objects是数据字典，不是表 -
可以查询表、视图、数据字典
-
4.2、查询所有列
- 使用 通配符* 通配所有的列：
SELECT * FROM 表名; - 在 SELECT 子句中依次序书写所有列名：
SELECT 列名1, 列名2, ... FROM 表名;
4.3、伪列
-
ROWID -
在 Oracle 中，数据库表每一行数据都有一个唯一的行标识符
ROWID是Oracle数据库内部所使用的数据（它是一个 18位长度的字符串）ROWID可以理解为某行数据在 Oracle数据库中的地址- 所有表都有
ROWID列 ，它真实存在于表中，但只可查询不可修改，即只在查询结果里使用此列
-
ROWNUM -
在 查询结果 中用
ROWNUM列 来表示行号- 仅仅在 查询结果中 才存在
ROWNUM列 - 可以结合子查询实现 分页查询
- 仅仅在 查询结果中 才存在
4.4、虚表
-
为了方便查询 Oracle数据库管理系统中提供了一个名称为
dual的表，这个表被称作虚表 -
Oracle中的dual表是一个单行单列的虚拟表
-
dual表只有一个列（ 名称为dummy，类型为VARCHAR2(1) ）
-
dual表只有一行数据 X
-
-
提供dual表的主要目的是为了方便查询
- 所有的用户都可以通过dual来辅助查询（用户无需显式创建dual表就可以使用）
- dual表与数据字典一起自动创建
-
dual表主要用来选择系统变量或求一个表达式的值
-
应用
-
查询当前日期
SELECT sysdate FROM dual ;
-
实现简单的算术运算
SELECT 1+2, 2-3, 3*4, 4/5, mod(13,2) FROM dual;
-
使用contat函数拼接字符串
SELECT concat( 'hello' , 'world' ) FROM dual ;
-
-
Oracle 是一个严谨的数据库管理系统，不像MySQL可以将 SELECT sysdate 直接执行，为了能将 SELECT sysdate 凑成一个完整的 查询语句，则需要在其后增加 FROM语句，此时就可以使用dual表实现
-
虚表（dual）在通过user_tables、tab、user_objects查询时并不能查看到这个表
4.5、伪列和虚表是 oracle 特有的
4.6、算术运算
-
加法：+ ；x+y
-
减法：- ；x-y
-
乘法：* ；x*y
-
除法：/ ；x/y
-
求模：mod( x , y ) 表示x%y
4.7、列别名
- 在 SELECT 子句中可以为某些列指定别名，若指定的别名为 汉字，也不需要加引号；若加引号也是加双引号
- 指定别名时，可以使用 AS关键字，也可以使用 空格 将列名和别名隔开，建议使用 AS 关键字指定别名
- 别名 仅限在 查询结果 中使用，不是表中存在的数据，即表中不存在
4.8、拼接字符串
- 在 Java语言中可以用 + 连接字符串
- 在 Oracle 数据库管理系统中则使用 || 连接字符串
- 也可借助于 concat( string1 , string2 )字符连接函数，concat函数只能有两个参数
- 注意，字符串用 单引号 括起来
4.9、空值
- 数据库中的 空 不是空串、不是NULL、也不是 0，而是未知值
- 空 和 空 是不相等的 ( 不可以使用 等号 来比较 )，代表的意义不同
- Oracle 数据库管理系统中提供了 NVL函数 来处理空值
NVL( first , second )类似于 Java中的first==null?second:first- first指列名，second指 若first列中有空值，则用second值替换
- NVL函数的两个参数需要是 同种类型，并且由第一个参数决定它们应该是什么类型
4.10、去重
- 所有查询 默认 都是 SELECT [ALL] 列名 FROM … ; 查询结果全部显示，不剔除重复行
- 如果要剔除查询结果中的 重复行，则可以使用 SELECT DISTINCT 列名 FROM …
- 注意，使用DISTINCT剔除的是重复的行，不是重复的列
- 剔除重复数据仅仅对查询结果有效（不会影响表中的数据）
- distinct 可作用于单列，写在单列列名前
- distinct 可作用于多列，写在第一列列名前，若写在后面列的列名前，则报错
select distinct name , id from A ;- distinct对后面的name，id都起作用
- 类似于对select表达式中的列的数据拼接后取不重复的记录
- 若想在多列中，使distinct只对某一项生效，可利用组函数
select name , count(distinct id) from A ;
- distinct的去重替代方法
- 用unique替代
select unique name from A ;
- 用group by 替代
select name ,id from A group by id ;
- 用union和minus替代，这两个默认都是先distinct再做聚集
select num from A minus select 999 from dual ;select name from A union select name from A ;
- 用unique替代
5、数据过滤
5.1、实现方式
- 在查询语句中通过 WHERE 子句可以实现数据过滤
- 语法：
SELECT ... FROM ... WHERE ... - 先
FROM—>确定表；再WHERE—>确定列筛选条件；最后SELECT—>选择显示的列
5.2、比较运算符
-
= -
用于判断两个数值是否相等
- 与Java语言不同，数据库中是使用 单个= 比较
-
!=或<> -
不相等
-
<小于 -
>大于 -
<=小于等于 -
>=大于等于 -
any>any( x,y,z,...)- 大于列表中的任意值
- 只需要比列表中最小的值大即可
=any( x,y,z,...)- 等于列表中的任意值即可
<any( x,y,z,...)- 小于列表中的任意值
- 只需要比列表中最大的值小即可
-
all>all( x,y,z,...)- 大于列表中的所有值
- 实际是大于列表中的最大值
=all( x,y,z,...)- 等于列表中的所有值
- 此时列表中只有一个值时，可起作用
- 允许使用 =all( x,y,z,…)，运行不会出错，然而查不出行，因为不可能有值同时等于三个不同的值
<all( x,y,z,...)- 小于列表中的所有值
- 实际就是小于列表中的最小值
5.3、SQL运算符
in( x,y,z,...)- 判断是否属于列表中的某个值
- 作用等同于 =
any( x,y,z,... )
is null- 用于判断某个值是否是空
- 空值判断不能使用 =null
- 空 和 空 是不相等的 ( 不可以使用 等号 来比较 )，代表的意义不同
- 判断是否 非空 用
is not null
between ... and ...- 用于判断某个值是否属于某个区间 [x,y]
列名 BETWEEN x AND y- and 之前的x取值要小于等于 and 之后的y取值
- 可用 逻辑与
AND代替
like- 用于实现模糊查询
- 通配符
- 单个 下划线
_用于 匹配任意单个字符 - 单个 百分号
%用于 匹配任意多个字符
- 单个 下划线
- 可以使用
escape关键字 自定义转义字符like 'A\_%' ESCAPE '\'- 代表：匹配列中 以A开头，后跟 _ 字符，之后跟任意个字符 的字符
- 将 \ 定义为转义字符，使得 \ 后的 _ 代表的是一个字符 _ ，不再是通配符 _
- C中\本身就是转义字符，但在SQL里不是需要用escape关键字指定，escape也可以指定转义字符为$
- LIKE 之后的字符中，英文字符是区分大小写的
5.4、逻辑运算符
- 逻辑非：not
- 类似于Java中的 ! 运算符
- is null 取反后是 is not null
- 逻辑与：and
- 类似于Java中的 && 运算符
- 逻辑或：or
- 类似于Java中的 || 运算符
- 优先级：not>and>or
6、数据分组
6.1、组函数（分组函数/集合函数）
-
（1）常用组函数
-
MIN
- min()参数类型可以是字符型、日期型
-
MAX
- max()参数类型可以是字符型、日期型
-
AVG
- avg()参数类型必须是数值型
-
SUM
- sum()参数类型必须是数值型
-
COUNT
-
1、count()不能统计多个字段
- 如count(name,id)是错误写法
-
2、可嵌套查询
select count(*) from (select distinct name , id form B) as M ;
-
3、只有count()的参数可以为 通配符
* -
4、count(*) 、count(1)、count(‘啦啦啦字符’)
-
*、1、'啦啦啦字符'都是占位符，执行结果上没什么区别，统计表中所有行数，以行为单位，行不为空就+1；count(*)计算包括null行的所有行数（空行中rowid不为null，所以其实也不算空行） -
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL 的行
- 执行时会把 * 翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些
-
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
- 1并不是表示第一个字段，而是表示一个固定值。
- 其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1，即计算一共有多少符合条件的行。
-
count(列名)只包括列名那一列，在统计结果的时候，会忽略列字段为空的行（啥值也没有）
- 这里的空不是指空字符串或者0，而是表示null，即某个字段值为NULL时，不统计（也不恰当，啥值也没有的字段，那行也会被忽略）
-
执行效率
-
count(1) 和 count(*)
- 如果表只有一个字段，则 select count（*）最优。
- 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
- 其实实话讲，效率差不了多少，就是个占位符
-
count(1) 和 count(字段)
- 如果有主键，则 select count（主键）的执行效率是最优的
- 即列名为主键，count(列名)会比count(1)快
- 列名不为主键，count(1)会比count(列名)快
-
-
-
5、count(列名)
- 以列为参数，参数类型不作要求，统计表中列的非空数据的行数，以数据为单位，数据不为空就+1（先考虑此列是不是null（is null），非null就统计并做下一步操作，null就不做下一步操作）
-
-
-
（2）注意事项
-
组函数可以使用ALL和DISTINCT选项（默认是ALL）
- 不写就默认是ALL
select count(all name) from t_stu ;
select count(distinct name) form t_stu ;
- 不写就默认是ALL
-
组函数只能在 SELECT子句、ORDER BY子句、HAVING子句中使用
-
组函数绝对不能在WHERE子句和GROUP BY子句中使用
- where 是在初次筛选数据，group by是正在分组，两个都不好使用组函数对数据按组操作
-
所有组函数在计算时都忽略空值
-
6.2、分组
-
在查询语句中使用 GROUP BY 子句来实现分组
-
SELECT 列名 , ... FROM 表名 [ WHERE 条件] [GROUP BY 条件] ;
-
-
凡是在 SELECT 列表中出现的列，除非该列被组函数所处理，否则这些列必须出现在 GROUP BY 子句中（建议依次序，可读性强，不依次序也没关系）
-
在 GROUP BY 子句中使用的 分组条件，可以书写或者不书写在 SELECT 列表中
-
不书写即为SELECT列表中所有的列都被组函数处理，此时 GROUP BY 子句中的列名，就是根据此列进行分组
6.3、筛选
-
分组之前的数据可以通过 WHERE 筛选（也就是从 表中选择哪些数据）
-
对分组后的数据进行筛选可以在 GROUP BY 之后使用 HAVING 子句实现
-
SELECT 列名 , ... FROM 表名 [ WHERE 条件] [ GROUP BY 条件] [ HAVING 条件] ;
-
6.4、!!执行顺序!!
from确定表、where筛选分组前的数据、group by数据分组、having筛选分组后的数据(留下满足条件的组)、select选择查询的列、order by最后按查询的列中某一或某几列进行排序
- having在select前执行，因此having不可用select定义的列别名；
- order by在select后执行，因此order by可用select定义的列别名；
7、数据排序
-
实现方式
-
在 查询语句 中通过 ORDER BY 关键字来实现排序
-
ORDER BY 之后可以跟单个列，也可以跟多个列
- 多个列时是先按第一个列名排序，在此基础上再按第二个列名排序
-
升序使用 ASC 表示，降序使用 DESC表示，默认是升序排序
-
在 ORDER BY 之后可以使用
- 列名
- 别名
- 查询结果中 列的索引（索引从1开始计数）
- 组函数
-
-
语法
-
SELECT 列名 , ... FROM 表名 [ WHERE 条件] [ GROUP BY 条件] [ HAVING 条件] [ ORDER BY 条件] ;
-
二、Code
1、查看表结构&设置显示样式
-
DESC user_objects ;通过 DESCRIBE 查看 user_objects 的表结构 -
SET linesize 150设置每一行显示的长度 -
COLUMN object_name FORMAT A20 ;设置 名称为 object_name 的列的样式
2、简单查询&查询伪列&查询虚表
-
select rowid , * from myemp ;错误！ -
select rowid , myemp.* from myemp ;正确 -
在 SELECT 子句中可以使用 rowid 来查询 行标识符
SELECT rowid , empno , ename FROM emp ;
-
在查询结果中可以使用 ROWNUM 来统计行号
SELECT rownum , deptno , dname , loc FROM dept ;
-
Oracle 是一个严谨的数据库管理系统，不像 MySQL 一样，可以将 SELECT sysdate 直接执行， 为了能够将 SELECT sysdate 凑成一个完整的 查询语句，则需要在其后增加 FROM 语句，此时就可以使用 dual 表
-
虚表( dual ) 在通过 user_tables 、tab 、user_objects 查询时并不能查看到
SELECT object_name , object_type FROM user_objects ;
3、dual表应用&算术运算&列别名
-
清理屏幕
clear screen -
通过 dual 表实现简单的算术运算
SELECT 1 + 2 , 2 - 3 , 3 * 4 , 4 / 5 , 13 % 2 FROM dual ;
-
使用 AS 关键字 为 empno 、ename 、sal*12 指定别名
SELECT empno AS 工号 , ename AS 姓名 , sal * 12 AS 年薪 FROM emp ;
-
也可以使用 空格 将列名 和 别名隔开，不使用 AS 关键字指定列的别名
SELECT empno 工号 , ename 姓名 , sal * 12 年薪 FROM emp ;
4、dual表应用&拼接&tochar()&处理空值NVL()&去重
1、concat 函数拼接字符串
-
尝试使用 concat 函数拼接字符串（dual表应用）
SELECT concat( 'hello' , 'world' ) FROM dual ;
-
使用 || 将 名 和 姓 连接成一个字符串
SELECT first_name || last_name FROM s_emp ;
-
在 名 和 姓 中间 使用 空格隔开
SELECT first_name || ' ' || last_name AS name FROM s_emp ;
-
尝试使用 concat 将 s_emp 表中所有雇员的 名 和 姓 连接起来，并在 名 和 姓 中间使用空格隔开
SELECT concat( concat( first_name , ' ' ) , last_name ) FROM s_emp ;
2、借助于 Oracle 提供的 nvl 函数来处理空值
-
NVL 函数的两个参数需要是同种类型，并且第一个参数决定了他们应该都是什么类型
-
SELECT empno , ename , job , sal , NVL( comm , -1 ) FROM emp ; -
使用 to_char 函数可以将 数字 转换为 字符类型
SELECT empno , ename , job , sal , NVL( to_char( comm ) , '没有提成' ) FROM emp ;
-
将 emp 表中 mgr 为 空 的 记录 显示为 BOSS ( 就是该行数据的 mgr 列显示 BOSS )，指定别名
SQL> SELECT empno , ename , NVL( to_char(mgr) , 'BOSS' ) AS mgr FROM emp ;
3、如果需要剔除重复行，就在 SELECT 之后使用 DISTINCT
SELECT DISTINCT deptno FROM emp ;
5、使用where数据过滤&使用order by数据排序
-
查询 emp 表中 sal 大于 1000 的雇员信息 ( 工号、姓名、岗位、薪水 )
SELECT empno , ename , job , sal FROM emp WHERE sal > 1000 ;
-
查询就职于 20 部门 在 CLERK 岗位上工作的雇员信息
SELECT empno , ename , deptno , job FROM emp WHERE deptno = 20 AND job = 'CLERK' ;
-
查询 emp 表中所有雇员的 工号、姓名、年薪，并按照 月薪 排序（默认升序ASC）
-
SELECT empno , ename , sal * 12 FROM emp ORDER BY sal ; -
查询 emp 表中所有雇员的 工号、姓名、年薪，并按照 月薪 降序排列（DESC），order by后用列名
-
sal*12 是一个表达式
-
SELECT empno , ename , sal * 12 FROM emp ORDER BY sal DESC ;
-
-
order by后面按 年薪（sal*12） 排序，order by后用列名
SELECT empno , ename , sal * 12 AS 年薪 FROM emp ORDER BY sal*12 DESC ;
-
给 列sal*12 起 列别名，order by 按 年薪（列别名） 排序
SELECT empno , ename , sal * 12 AS 年薪 FROM emp ORDER BY 年薪 DESC ;
-
order by 后面按 年薪（查询结果中列的索引） 排序
SELECT empno , ename , sal * 12 AS 年薪 FROM emp ORDER BY 3 DESC ;
-
查询 emp 表中所有雇员的 工号、姓名、薪水，并按照 薪水降序排列，如果两个员工的月薪相同，则再根据提成排序
-
SELECT empno , ename , sal FROM emp ORDER BY sal DESC , comm DESC ; -
查询 20 部门中所有的雇员的 工号、姓名、岗位、薪水、提成，并按照薪水降序排列
SELECT empno , ename , job , sal , comm FROM emp WHERE deptno = 20 ORDER BY 4 DESC ;
6、比较运算符：any / all
测试 any 运算符
-
1、求 月薪 大于 1000 , 1500 , 2000 中任意值的雇员信息( 工号、姓名、岗位、薪水)
-
SELECT empno , ename , job , sal FROM emp WHERE sal >any ( 1000 , 1500 , 2000 ) ; -
2、列出部门号为 10 、20 、30 的雇员的信息( 工号、姓名、部门号 )
-
SELECT empno , ename , deptno FROM emp WHERE deptno =any ( 10 , 20 , 30 ) ; -
3.1、列出 工号 小于 7300 、7400 、7500 任意一个值的雇员信息( 工号、姓名 )
-
SELECT empno , ename FROM emp WHERE empno <any ( 7300 , 7400 , 7500 ) ; -
3.2、使用 or 运算符翻译为:
-
SELECT empno , ename FROM emp WHERE empno < 7300 OR empno < 7400 OR empno < 7500 ;
测试 all 运算符
-
1、列出薪水 大于 ( 3000 , 3500 , 4000 ) 中所有值的雇员信息( 查询 s_emp 表 )
SELECT id , first_name FROM s_emp WHERE salary >all ( 3000 , 3500 , 4000 ) ;
-
2、列出薪水 小于 ( 3000 , 3500 , 4000 ) 中所有值的雇员信息( 查询 s_emp 表 )
SELECT id , first_name FROM s_emp WHERE salary <all ( 3000 , 3500 , 4000 ) ;
-
3.1、查询 工号 等于 ( 10 , 20 , 30 ) 中所有值的雇员信息( s_emp )
SELECT id , first_name FROM s_emp WHERE id =all ( 10 , 20 , 30 ) ;- 结果是未选定行，因为不可能有一个 id 同时等于三个不同的值
-
3.2、查询 工号 等于 ( 10 , 10 , 10 ) 中所有值的雇员信息( s_emp )
-
SELECT id , first_name FROM s_emp WHERE id =all ( 10 , 10 , 10 ); -
SELECT id , first_name FROM s_emp WHERE id = 10;
-
SQL>
SQL> -- 测试 any 运算符
SQL>
SQL> -- any 表示任意的，比如 >any 就表示大于任意一个值
SQL> SELECT empno , ename , job , sal FROM emp WHERE sal >any ( 1000 , 1500 , 2000 ) ;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7934 MILLER CLERK 1300
9257 HUA'AN CLERK 1300
8526 QIU'XIANG ANALYST 1400
已选择14行。
SQL>
SQL> -- 列出部门号为 10 、20 、30 的雇员的信息( 工号、姓名、部门号 )
SQL> SELECT empno , ename , deptno FROM emp WHERE deptno =any ( 10 , 20 , 30 ) ;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
EMPNO ENAME DEPTNO
---------- ---------- ----------
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
已选择14行。
SQL> -- 列出 工号 小于 7300 、7400 、7500 任意一个值的雇员信息( 工号、姓名 )
SQL> SELECT empno , ename FROM emp WHERE empno <any ( 7300 , 7400 , 7500 ) ;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
SQL>
SQL> -- 使用 or 运算符翻译为:
SQL> SELECT empno , ename FROM emp WHERE empno < 7300 OR empno < 7400 OR empno < 7500 ;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
SQL>
SQL>
SQL>
SQL>
SQL> -- 测试 all 运算符
SQL>
SQL> -- all 表示所有的，比如 >all 就表示大于某个列表中的所有值
SQL> -- 列出薪水 大于 ( 3000 , 3500 , 4000 ) 中所有值的雇员信息( 查询 s_emp 表 )
SQL> SELECT id , first_name FROM s_emp WHERE salary >all ( 3000 , 3500 , 4000 ) ;
未选定行
SQL> -- 列出薪水 大于 ( 3000 , 3500 , 4000 ) 中所有值的雇员信息( 查询 emp 表 )
SQL> SELECT empno , ename , sal FROM emp WHERE sal >all ( 3000 , 3500 , 4000 ) ;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
SQL>
SQL> -- 使用 <all 可以表示小于指定列表中所有的值
SQL>
SQL> -- 列出薪水 小于 ( 3000 , 3500 , 4000 ) 中所有值的雇员信息( 查询 s_emp 表 )
SQL> SELECT id , first_name FROM s_emp WHERE salary <all ( 3000 , 3500 , 4000 ) ;
ID FIRST_NAME
---------- -------------------------
1 Carmen
2 LaDoris
3 Midori
4 Mark
5 Audry
6 Molly
7 Roberta
8 Ben
9 Antoinette
10 Marta
11 Colin
ID FIRST_NAME
---------- -------------------------
12 Henry
13 Yasmin
14 Mai
15 Andre
16 Elena
17 George
18 Akira
19 Vikram
20 Chad
21 Alexander
22 Eddie
ID FIRST_NAME
---------- -------------------------
23 Radha
24 Bela
25 Sylvie
已选择25行。
SQL> -- 是否允许使用 =all ？
SQL>
SQL> -- 查询 工号 等于 ( 10 , 20 , 30 ) 中所有值的雇员信息( s_emp )
SQL> SELECT id , first_name FROM s_emp WHERE id =all ( 10 , 20 , 30 ) ;
未选定行
SQL> -- 因为不可能有一个 id 同时等于三个不同的值
SQL>
SQL> -- 查询 工号 等于 ( 10 , 10 , 10 ) 中所有值的雇员信息( s_emp )
SQL> SELECT id , first_name FROM s_emp WHERE id =all ( 10 , 10 , 10 );
ID FIRST_NAME
---------- -------------------------
10 Marta
SQL> SELECT id , first_name FROM s_emp WHERE id = 10;
ID FIRST_NAME
---------- -------------------------
10 Marta
SQL> spool off
7、SQL运算符：in / is null / between…and…
1、测试 in 运算符
-
IN( list ) 作用上等同于 =ANY( list )
-
列出 emp 表中 月薪为 ( 1100 , 1400 , 1350 , 2000 ) 的雇员信息
SELECT empno , ename , sal FROM emp WHERE sal IN ( 1100 , 1400 , 1350 , 2000 );
2、测试 IS NULL 运算符 ( 判断是否为空值 )
-
错误的写法是 = NULL
-
列出 emp 表中没有 上司的 雇员的信息
SELECT empno , ename , mgr FROM emp WHERE mgr IS NULL ;
3、非空判断可以使用 IS NOT NULL
- 列出 s_emp 表中有提成的雇员的信息
SELECT id , first_name , last_name , commission_pct FROM s_emp WHERE commission_pct IS NOT NULL ;
- 列出 emp 表中有提成的雇员的信息 ( 有提成是指 comm 不为空 且 comm 不为零 )
SELECT empno , ename , job , sal , comm FROM emp WHERE comm IS NOT NULL AND comm != 0 ;
4、测试 BETWEEN … AND … 运算符
- 列出 月薪 在 1500 到 2000 之间的雇员的信息 ( emp )
SELECT empno , ename , sal FROM emp WHERE sal BETWEEN 1500 AND 2000 ;
- 可以使用 and 来替代 between … and …
SELECT empno , ename , sal FROM emp WHERE sal >= 1500 AND sal <= 2000 ;
SQL>
SQL> -- SQL运算符
SQL>
SQL>
SQL> -- 测试 in 运算符
SQL>
SQL> -- 列出 emp 表中 月薪为 ( 1100 , 1400 , 1350 , 2000 ) 的雇员信息
SQL> SELECT empno , ename , sal FROM emp WHERE sal IN ( 1100 , 1400 , 1350 , 2000 );
EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
8526 QIU'XIANG 1400
SQL>
SQL> -- 通过列出 emp 表中的所有数据予以验证
SQL> SELECT empno , ename , sal FROM emp ;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
EMPNO ENAME SAL
---------- ---------- ----------
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
9257 HUA'AN 1300
8526 QIU'XIANG 1400
已选择16行。
SQL> -- IN( list ) 作用上等同于 =ANY( list )
SQL>
SQL> SELECT empno , ename , sal FROM emp WHERE sal =ANY( 1100 , 1400 , 1350 , 2000 );
EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
8526 QIU'XIANG 1400
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- 测试 IS NULL 运算符 ( 判断是否为空值 )
SQL>
SQL> -- 列出 emp 表中没有 上司的 雇员的信息
SQL> SELECT empno , ename , mgr FROM emp WHERE mgr IS NULL ;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
SQL> -- 错误的写法是 = NULL
SQL> SELECT empno , ename , mgr FROM emp WHERE mgr = NULL ;
未选定行
SQL> -- 非空判断可以使用 IS NOT NULL
SQL>
SQL> -- 列出 s_emp 表中有提成的雇员的信息
SQL> SELECT id , first_name , last_name , commission_pct FROM s_emp WHERE commission_pct IS NOT NULL ;
ID FIRST_NAME LAST_NAME COMMISSION_PCT
---------- ------------------------- ------------------------- --------------
11 Colin Magee 10
12 Henry Giljum 12.5
13 Yasmin Sedeghi 10
14 Mai Nguyen 15
15 Andre Dumas 17.5
SQL>
SQL> -- 列出 emp 表中有提成的雇员的信息 ( 有提成是指 comm 不为空 且 comm 不为零 )
SQL> SELECT empno , ename , job , sal , comm
2 FROM emp
3 WHERE comm IS NOT NULL AND comm != 0 ;
EMPNO ENAME JOB SAL COMM
---------- ---------- --------- ---------- ----------
7499 ALLEN SALESMAN 1600 300
7521 WARD SALESMAN 1250 500
7654 MARTIN SALESMAN 1250 1400
SQL> -- 尝试不考虑 comm != 0 的情况
SQL> edit
已写入 file afiedt.buf
1 SELECT empno , ename , job , sal , comm
2 FROM emp
3* WHERE comm IS NOT NULL
SQL> /
EMPNO ENAME JOB SAL COMM
---------- ---------- --------- ---------- ----------
7499 ALLEN SALESMAN 1600 300
7521 WARD SALESMAN 1250 500
7654 MARTIN SALESMAN 1250 1400
7844 TURNER SALESMAN 1500 0
SQL>
SQL>
SQL>
SQL>
SQL> -- 测试 BETWEEN ... AND ... 运算符
SQL>
SQL> -- 列出 月薪 在 1500 到 2000 之间的雇员的信息 ( emp )
SQL> SELECT empno , ename , sal FROM emp WHERE sal BETWEEN 1500 AND 2000 ;
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7844 TURNER 1500
SQL> -- 可以列出 emp 表中所有数据予以验证
SQL> SELECT empno , ename , sal FROM emp ;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
EMPNO ENAME SAL
---------- ---------- ----------
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
9257 HUA'AN 1300
8526 QIU'XIANG 1400
已选择16行。
SQL> -- 可以使用 and 来替代 between ... and ...
SQL> SELECT empno , ename , sal
2 FROM emp
3 WHERE sal >= 1500 AND sal <= 2000 ;
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7844 TURNER 1500
SQL>
SQL>
SQL>
SQL> spool off
8、SQL运算符：like模糊查询&upper()&lower()
- 1.1、查询当前用户所拥有的表中，名称以 S_ 为前缀的表名
SELECT table_name FROM user_tables WHERE table_name LIKE 'S_%' ;SELECT table_name FROM user_tables WHERE table_name LIKE 'S\_%' ESCAPE '\' ;SELECT table_name FROM user_tables WHERE table_name LIKE 'S\_%' ;- 测试不使用 escape 指定转义字符
- 结果为：未选定行
SELECT table_name FROM user_tables WHERE table_name LIKE 'S$_%' ESCAPE '$' ;- 转义字符可以是任意字符
- 1.2、下划线 用于匹配任意单个字符，如果期望 下划线 不表示通配符，而是表示 下划线 本身，则需要使用转义字符，在 SQL 中可以使用
escape关键字来指定转义字符，转义字符可以是任意字符。 - 2、列出 s_emp 表中 first_name 中含有字母 a 的所有雇员的信息
SELECT id , first_name FROM s_emp WHERE first_name LIKE '%a%' ;
- 3.1、 LIKE 之后的字符中，英文字符是区分大小写的
- 3.2、列出 s_emp 表中 first_name 包含 字母 a 或 A 的雇员信息
SELECT id , first_name FROM s_emp WHERE first_name LIKE '%A%' OR first_name LIKE '%a%'
- 3.3、也可以借助于 lower 函数将 first_name 转小写后再比较
SELECT id , first_name FROM s_emp WHERE lower( first_name ) LIKE '%a%';
- 3.4、或者借助于 upper 函数将 first_name 转大写之后再比较
SELECT id , first_name FROM s_emp WHERE upper( first_name ) LIKE '%A%';
- 3.5、列出 s_emp 表中 first_name 中包含了 两个 a 的雇员信息 ( 不区分大小写 )
SELECT id , first_name FROM s_emp WHERE upper( first_name ) LIKE '%A%A%' ;
10、逻辑运算符：not / and /or
-
优先级：not>and>or
-
1、查询有上司的雇员信息
SELECT empno , ename , mgr FROM emp WHERE mgr IS NOT NULL ;
-
2、列出10部门中有上司的雇员信息
SELECT empno , ename , mgr , deptno FROM emp WHERE deptno = 10 AND mgr IS NOT NULL ;
-
3、列出 10 和 20 部门中有上司的雇员信息或工号大于1000的雇员信息
SELECT empno , ename , mgr , deptno FROM emp WHERE deptno IN ( 10 , 20 ) AND mgr IS NOT NULL OR empno > 1000 ;
-
4、列出 10 和 20 部门中有上司的雇员信息或工号大于7800的雇员信息
SELECT empno , ename , mgr , deptno FROM emp WHERE empno > 7800 OR deptno IN ( 10 , 20 ) AND mgr IS NOT NULL
-
5、列出 emp 表中 工号大于7800且有上司的雇员信息 或 属于部门10、部门20的雇员信息
SELECT empno , ename , mgr , deptno FROM emp WHERE empno > 7800 AND mgr IS NOT NULL OR deptno IN ( 10 , 20 )SELECT empno , ename , mgr , deptno FROM emp WHERE deptno IN ( 10 , 20 ) OR empno > 7800 AND mgr IS NOT NULL
11、组函数 / 数据分组 / 分组条件&select列表关系
1、使用组函数: max/min/avg/sum/count
-
求 emp 表中所有雇员的 最高月薪、最低月薪、平均月薪
SELECT max(sal) , min(sal) , avg(sal) FROM emp ;- 此时需要注意，我们将整个 emp 表中的所有雇员当做一个分组来对待，也就是说，这里没有对 emp 表中的雇员做分组，所有雇员是一个组，即整个公司
-
统计 emp 表中雇员的人数以及该公司每个月发放的工资总额
SELECT count(empno) , sum(sal) FROM emp ;
2、使用 GROUP BY 子句对 emp 表中的数据实现分组，GROUP BY 之后指定的就是分组的依据
-
统计每个部门的人数、平均月薪、最高月薪、最低月薪、工资总额
SELECT deptno , count(empno) , avg(sal) , max(sal) , min(sal) , sum(sal) FROM emp GROUP BY deptno ;
-
统计每个部门的人数、平均月薪、最高月薪、最低月薪、工资总额 (不统计没有部门的员工、最后的查询结果根据部门号升序排列)
SELECT deptno , count(empno) , avg(sal) , max(sal) , min(sal) , sum(sal) FROM emp WHERE deptno IS NOT NULL GROUP BY deptno ORDER BY 1;
-
尝试 在 group by 之后指定的 分组条件 与 select 之后查询的列不同
SELECT deptno , count(empno) , avg(sal) , max(sal) , min(sal) , sum(sal) FROM emp WHERE deptno IS NOT NULL GROUP BY job ORDER BY 1- 出现错误: ORA-00979: 不是 GROUP BY 表达式
-
凡是在 SELECT 列表中出现的列，除非该列被组函数所处理，否则这些列必须依次序出现在 GROUP BY 子句中，但是在有些不严谨如MySQL的数据库中仍然可以执行，虽可执行却无实际意义
-
在 GROUP BY 子句中使用的 分组条件，可以不书写在 SELECT 列表中（即select后的列全用组函数处理，group by后的列为分组依据）
SELECT count(empno) , avg(sal) , max(sal) , min(sal) , sum(sal) FROM emp WHERE deptno IS NOT NULL GROUP BY job ORDER BY 1
-
在 GROUP BY 子句中使用的 分组条件，也可以写在 SELECT 列表中
SELECT job , count(empno) , avg(sal) , max(sal) , min(sal) , sum(sal) FROM emp WHERE deptno IS NOT NULL GROUP BY job ORDER BY 1
-
列出 emp 表中每个部门的每个岗位的人数和平均月薪、月工资总额
(不统计没有部门的员工、最后的查询结果根据 部门号 和 岗位 升序排列)
SELECT deptno , job , count(empno) , avg(sal) , sum(sal) FROM emp WHERE deptno IS NOT NULL GROUP BY deptno , job ;
12、count() / min() / max() /日期格式/having子句筛选分组后的数据
1、count
*、1、'阿弥勒佛'都是占位符，没什么区别，统计表中所有行数，以行为单位，行不为空就+1；count(*)计算包括null行的所有行数（空行中rowid不为null，所以其实也不算空行）SELECT count(*) FROM emp ;SELECT count(1) FROM emp ;SELECT count('阿弥陀佛') FROM emp ;
count( 列名 )以列为参数，统计表中列的非空数据的行数，以数据为单位，数据不为空就+1- 统计emp表中多少雇员是有部门的
SELECT count( deptno ) FROM emp ;
- 统计emp表中多少雇员有上司
SELECT count(mgr) FROM emp ;
- 统计emp表中多少雇员的提成不是空
SELECT count(comm) FROM emp ;
2、测试 max 和 min 函数，参数类型为字符串/日期值
-
统计emp表所有雇员中入职最晚的时间和入职最早的时间
SELECT max( hiredate ) 最晚 , min( hiredate ) 最早 FROM emp ;
-
修改当前会话(Session)的日期格式（也可以用日期格式化）
-
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss' ;- 注意这里的 月份使用 mm ,分钟使用 mi（全是小写）
- 数据库中存储的日期是一个数字，表示天数，数字1代表1天；Java中存的日期也是数字，但是是毫秒值
-
凡是可以排序的都可以比较大小，rowid存着字符串，也可比较大小，hiredate存着日期，可排序即可用max\min比较大小
SELECT empno , ename , hiredate FROM emp ORDER BY hiredate ASC ;
-
统计 emp 表中 最小的 rowid 和 最大的 rowid
SELECT min( rowid ) , max( rowid ) FROM emp ;
-
将emp表按rowid排序
SELECT empno , ename , rowid FROM emp ORDER BY rowid ASC ;
-
列出每个部门每个岗位的最早入职日期
SELECT deptno , job , min( hiredate ) FROM emp WHERE deptno IS NOT NULL GROUP BY deptno , job ;SELECT deptno , job , min( hiredate ) , rowid FROM emp WHERE deptno IS NOT NULL GROUP BY deptno , job ;
SELECT deptno , job , min( hiredate ) , rowid *第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
3、对分组后的数据再度筛选，having子句
-
统计 emp 表中各个部门的平均工资，列出平均工资高于 2000 的部门 ( 不统计没有部门的员工 )，按工资降序排序
SELECT deptno , avg(sal) AS avgsal FROM emp WHERE deptno IS NOT NULL GROUP BY deptno HAVING avg(sal) > 2000 ORDER BY avg(sal) DESC ;（或ORDER BY 2 DESC或ORDER BY avgsal DESC）- 最后执行select语句时，avg(sal)会参照select中前面列出的deptno进行avg计算；而having中的avg(sal)会参照group by中分组的deptno进行计算，两个avg(sal)所计算的deptno不一样
-
执行顺序：from确定表、where筛选分组前的数据、group by数据分组、having筛选分组后的数据(留下满足条件的组)、select选择查询的列、order by最后按查询的列中某一或某几列进行排序
-
having在select前执行，因此having不可用select定义的列别名；
-
order by在select后执行，因此order by可用select定义的列别名；
-
SELECT deptno , avg(sal) avgsal FROM emp WHERE deptno IS NOT NULL GROUP BY deptno HAVING avgsal > 2000 ORDER BY avgsal DESC ;HAVING avgsal > 2000
*第 5 行出现错误: ORA-00904: “AVGSAL”: 标识符无效
-
-
统计 emp 表中各个部门的平均工资
SELECT deptno , avg(sal) FROM emp WHERE deptno IS NOT NULL GROUP BY deptno ;
368
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
