Oracle SQL 基础

–关系数据库

–开始–>运行–>sqlplus 用户名/密码

system
password

–Oracle执行脚本：@脚本路径

基本的SELECT语句

–在关系数据库中查询表中的数据使用SELECT语句

SELECT 列名 [, 列名 ,……]|*

FROM 表名 ;

/*

1.Oracle数据库中SELECT语句的FROM子句不能省略。

2.SQL语句不区大小写。

3.SQL语句可以写成一行或多行。

4.通常情况下，SQL语句使用分号表示结束。

5.只能直接使用半角标点。

6.不建议使用*，查询的效率低。

*/

算术运算符：+，-，*，/

–查询employees表中所有员工的last_name,salary,年薪

SELECT LAST_NAME,SALARY,SALARY*12

FROM EMPLOYEES;

列别名

/*

1.列名 列别名

2.列名 AS 列别名

3.当列别名区分大小写时，或别名中包含关键字时，或别名中包含特殊字符时，需要将列别名放在一对双引号中。

4.关系数据库中双引号表示别名。

*/

–设置4中last_name列别名为 USER name，年薪列别名 年薪

SELECT LAST_NAME AS “USER NAME”,SALARY,SALARY*12 AS 年薪

FROM EMPLOYEES;

连接符：||

–连接employees表中last_name和first_name 为一列，列别名为NAME

SELECT LAST_NAME||’.’||FIRST_NAME AS NAME

FROM EMPLOYEES;

DISTINCT关键字

屏蔽查询结果中重复的数据

–查询employees表中所有员工的JOB_ID，并且屏蔽重复的数据

SELECT DISTINCT JOB_ID

FROM EMPLOYEES;

–通常情况下，一个SELECT语句中只能有一个DISTINCT关键字。

–通常情况下，DISTINCT关键字只能出现在SELECT与第一个列之间。

​ SELECT DISTINCT JOB_ID,SALARY

​ FROM EMPLOYEES;

过滤与排序

过滤：WHERE子句

SELECT

FROM

[WHERE 条件]

查询条件为数字类型

–查询employees表中在50号部门工作的员工的last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID=50;

查询条件为字符串类型

值必须放在一对单引号中,并且查询英文时区分大小写。

–查询employees表中last_name为fay的员工的employee_id,last_name,hire_date

SELECT EMPLOYEE_ID,LAST_NAME,HIRE_DATE

FROM EMPLOYEES

WHERE LAST_NAME=’Fay’;

查询条件为日期类型

值必须放在一对单引号中,值的格式必须为Oracle默认的日期格式：DD-MON-RR

–查询employees表中哪些员工在1994-6-7入职，显示这些员工的last_name,hire_date

SELECT LAST_NAME,HIRE_DATE

FROM EMPLOYEES

WHERE HIRE_DATE=’7-6月-94’;

比较运算符：>,>=,<,<=,=,(!=,<>)

–查询employees表中salary大于10000的员工，显示last_name,salary

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY>10000;

–查询employees表中在97年以后(不包含97年)入职的员工，显示员工的last_name,hire_date

SELECT LAST_NAME,HIRE_DATE

FROM EMPLOYEES

WHERE HIRE_DATE>’31-12月-97’;

特殊的比较运算符

BETWEEN..AND..：查询在指定范围中的数据(包含边界值)。

–查询employees表中salary在6000-12000之间的员工，显示last_name,job_id,salary

SELECT LAST_NAME,JOB_ID,SALARY

FROM EMPLOYEES

WHERE SALARY BETWEEN 6000 AND 12000;

–查询employees表中97-98年入职的员工，显示last_name,hire_date

SELECT LAST_NAME,HIRE_DATE

FROM EMPLOYEES

WHERE HIRE_DATE BETWEEN ‘1-1月-97’ AND ‘31-12月-98’;

IN(值列表)

与列表中某个值相等的数据。只能判断是否相等。

–查询employees表中在20号或50号部门工作员工的last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (20,50);

LIKE模糊查询

/*

1.%:任意长度的任意字符

2._:一个长度的任意字符

*/

–查询employees表中last_name中包含a的员工，显示last_name

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%a%’;

–查询employees表中last_name倒数第二个字母为n的员工，显示last_name

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%n_’;

–查询employees表中job_id包含A_的员工的last_name,job_id

SELECT LAST_NAME,JOB_ID

FROM EMPLOYEES

WHERE JOB_ID LIKE ‘%A/_%’ ESCAPE ‘/‘;

IS NULL/IS NOT NULL

判断是否为NULL

关系数据库需要提供一个与类型无关，表示未知或不确定的值。

–查询employees表中没有部门的员工，显示last_name,salary

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE DEPARTMENT_ID IS NULL;

逻辑运算符:AND,OR,NOT

–查询employees表中salary在6000-12000之间的员工，显示last_name,job_id,salary

SELECT LAST_NAME,JOB_ID,SALARY

FROM EMPLOYEES

WHERE SALARY BETWEEN 6000 AND 12000;

SELECT LAST_NAME,JOB_ID,SALARY

FROM EMPLOYEES

WHERE SALARY>=6000 AND SALARY<=12000;

–查询employees表中在20号或50号部门工作员工的last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (20,50);

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID=20 OR DEPARTMENT_ID=50;

–查询employees表中salary在5000-12000之间，并且在20号或50号部门工作的员工，显示last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE SALARY BETWEEN 5000 AND 12000 AND DEPARTMENT_ID IN(20,50);

AND的优先级大于OR

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE (SALARY>=5000 AND SALARY<=12000) AND (DEPARTMENT_ID=20 OR DEPARTMENT_ID=50);

–查询employees表中last_name不包含a的员工的last_name

SELECT LAST_NAME

FROM EMPLOYEES

WHERE NOT LAST_NAME LIKE ‘%a%’

–查询employees表中last_name包含a(不区分大小写)的员工的last_name

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%a%’ OR LAST_NAME LIKE ‘%A%’;

排序

ORDER BY子句及执行顺序

SELECT                   3

FROM 1

WHERE     2

ORDER BY 列名     4

通常ORADER BY子句会出现在SELECT语句的最后

/*

1.升序：使用ASC关键字，默认。 Ascend 上升

2.降序：使用DESC关键字。 Descend 下降

*/

/*

1.ORDER BY子句中可以出现列名。

2.ORDER BY子句中可以出现列别名(注意大小写)。

3.ORDER BY子句中可以出现表达式与函数。

4.ORDER BY子句中可以出现结果中列的序号。

*/

–查询employees表中年薪大于100000的员工的last_name,年薪, 结果根据年薪（第二列）降序排序

SELECT LAST_NAME,SALARY*12 AS 年薪

FROM EMPLOYEES

WHERE SALARY*12>100000

ORDER BY 2 DESC; 这里的“2”指的是SALARY*12

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

ORDER BY 2 ASC,3 DESC

WHERE子句中不能使用列别名

–查询employees表中所有员工的last_name,job_id,department_id,salary,结果根据department_id升序,salary降序排序。

SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,SALARY

FROM EMPLOYEES

ORDER BY DEPARTMENT_ID ASC NULLS FIRST, SALARY DESC;

Oracle认为NULL是最大的

SQL Server认为NULL是最小的

NULLS FIRST/NULLS LAST:在不影响排序规则的前提下，将NULL放在结果的最前/后面。

–当使用GROUP BY子句或DISTINCT关键字时，ORDER BY子句中排序的列必须在SELECT子句中出现。

​ SELECT DISTINCT JOB_ID

​ FROM EMPLOYEES

​ ORDER BY SALARY DESC;

单行函数

与字符类型相关的单行函数

大小写转换

–查询employees表中所有员工的last_name且全部大写（小写）

SELECT LAST_NAME,UPPER(LAST_NAME)–大写

FROM EMPLOYEES;

SELECT LAST_NAME,LOWER(LAST_NAME)–小写

FROM EMPLOYEES;

–查询employees表中last_name包含a(不区分大小写)的员工的last_name

SELECT LAST_NAME

FROM EMPLOYEES

WHERE UPPER(LAST_NAME) LIKE ‘%A%’;

SUBSTR

–从employees表中截取last_name字符

–从指定的位置截取字符串，直到字符串的最后

SELECT LAST_NAME,SUBSTR(LAST_NAME,3)

FROM EMPLOYEES;

–从指定的位置截取指定长度的字符

SELECT LAST_NAME,SUBSTR(LAST_NAME,3,2)

FROM EMPLOYEES;

–从倒数第N位开始截取字符串，直到字符串的最后

SELECT LAST_NAME,SUBSTR(LAST_NAME,-3) SUBSTR(LAST_NAME,-100,2)

FROM EMPLOYEES;

–从倒数第N位开始截取指定长度的字符串

SELECT LAST_NAME,SUBSTR(LAST_NAME,-3,2)

FROM EMPLOYEES;

字符数和字节数

–返回“中国“的字符数和字节数

SELECT LENGTH(‘中国’)–返回字符数

FROM DUAL;

SELECT LENGTHB(‘中国’)–返回字节数

FROM DUAL;

LPAD()/RPAD()

–LPAD()/RPAD():显示第一个参数的值，使用第二个参数设置第一个参数显示的长度，如果第一个参数的长度不够，使用第三个参数在第一个参数的左/右面补齐长度

SELECT LPAD(EMPLOYEE_ID,6,0),LAST_NAME

FROM EMPLOYEES;

INSTR()

–INSTR():在第一个参数中查找第二个参数首次出现的位置，没找到返回0

SELECT LAST_NAME,INSTR(LAST_NAME,’a’)

FROM EMPLOYEES;

–查询employees表中last_name中包含a的员工的last_name(不使用LIKE)

SELECT LAST_NAME

FROM EMPLOYEES

WHERE INSTR(LAST_NAME,’a’)!=0;

–将’www.baidu.com'中出现的所有的'w'，用'm'替换

SELECT REPLACE(‘www.baidu.com','w','m')--将第一个参数中出现的所有的第二个参数，用第三个参数替换

FROM DUAL;

TRIM()

–TRIM():去掉字符串两端的空格

SELECT TRIM(‘ ABC ABC ‘) AS A

FROM DUAL;

–去掉’AAAAABACAAAAA’两端的’A’

SELECT TRIM(‘A’ FROM ‘AAAAABACAAAAA’)

FROM DUAL;

与数字类型相关的单行函数

round(四舍五入)

描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果

参数:

number : 欲处理之数值

decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )

select round(123.456, 0) from dual; --返回123
select round(123.456, 1) from dual; --返回123.5
select round(-123.456, 2) from dual; --返回-123.46
select round(-153.456, -2) from dual; --返回-200
select round(-153.456, -20) from dual; --返回0

trunc(截断)

　　1）trunc函数处理数字

　　　　　　TRUNC（number[,decimals]）

　　　　　　其中：

　　　　　　number 待做截取处理的数值

　　　　　　decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分。

　　　　　　trunc就是处理数字的显示位数，如果decimals为负数，就处理整数部分，处理完为0，-1就是各位为零，-2就到了十位，如果超过了 整数部分 长度，则整个数字为0；

select trunc(123.456, 0) from dual; --返回123
select trunc(123.456, 1) from dual; --返回123.4
select trunc(-123.456, 2) from dual; --返回-123.45
select trunc(-153.456, -2) from dual; --返回-100
select trunc(-153.456, -20) from dual; --返回0

　　2）处理日期

　　　　详见与日期相关的单行函数 trunc

ceil和floor

ceil(n) ：向上取整，取大于等于数值n的最小整数。

floor(n) ：向下取整，取小于等于数值n的最大整数。

select ceil(1.5) a from dual;  --返回2
select ceil(-1.5) a from dual; --返回-1

select floor(1.5) a from dual;  --返回1
select floor(-1.5) a from dual; --返回-2

MOD

mod(m,n)：

(1)MOD返回m除以n的余数，如果n是0，返回m。
(2)这个函数以任何数字数据类型或任何非数值型数据类型为参数，可以隐式地转换为数字数据类型。

Oracle用最高的数字优先级来确定参数，隐式地将剩下的参数转换为该数据类型，并返回数据类型。

与日期类型相关的单行函数

标准周

ISO 标准周 :

• 一周的开头：星期一
• 一周的结尾：星期日
• 第一周：包含当年第一个星期四的那一周是第一个周
• 最后一周：最后一周是第52或53周，在下一年的第一周之前

oracle 自定义标准周 ：

• 一周的开头：每年或每月1日对应的星期，不管是星期几
• 一周的结尾：一周的开头 + 6天
• 第一周：每年或每月的1日 至 7日
• 最后一周：每年或每月的最后一天截止

格式化相关知识

D : 当前周的第一天 (星期日)。

IW : ISO 标准周 ，当前周的第一天 (星期一)。

WW : ORACLE标准周 ，本年当前周的第一天 (星期=本年1月1日)。

W : ORACLE标准周 ，本月当前周的第一天 (星期=本月1日)。

FMWW : 和WW相同，只不过获取周数时WW返回两位数。如第1周，WW返回 01，FMWW返回 1。

官方格式化相关知识: Format Models

sysdate

–SYSDATE:获得数据库服务器当前日期+时间

SELECT SYSDATE

FROM DUAL;

/*

1.日期+天数=日期

2.日期-天数=日期

3.日期-日期=天数

4.日期不能加日期

*/

–查询1000天后的日期，（1000天前的日期）

SELECT SYSDATE+1000

FROM DUAL;

SELECT SYSDATE-1000

FROM DUAL;

TRUNC(截断)

TRUNC(X, [FORMAT]) : 截断日期，返回日期。FORMAT 中与周相关的有D，IW，WW，W，FMWW。

select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'year') from dual; --当前年第1天：2020/1/1
select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy') from dual; --本年第1天：2020/1/1
select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yyy') from dual; --本年第1天：2020/1/1
select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yy') from dual; --本年第1天：2020/1/1
select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'y') from dual; --本年第1天：2020/1/1

select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'month') from dual; --本月第1天：2020/10/1
select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'mon') from dual; --本月第1天：2020/10/1
select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'mm') from dual; --本月第1天：2020/10/1

select trunc(to_date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'day') from dual; --本周第1天（星期日）：2019/12/29
select trunc(to_date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'd') from dual; --本周第1天（星期日）：2019/12/29
select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'dd') from dual; --本月第1天：2020/10/1
select trunc(to_date('2020-10-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'ddd') from dual; --2020/10/1

select trunc(to_date('2020-09-30 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 'hh24') from dual; --当前时间精确到小时：2020/9/30 12:00:00
select trunc(to_date('2020-09-30 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 'mi') from dual; --当前时间精确到分钟：2020/9/30 12:12:00
select trunc(to_date('2020-09-30 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 'ss') from dual; --精度错误，没有精确到秒的格式

-- IW : ISO标准周
select trunc(to_date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'IW') from dual; --本周第1天：2019/12/30
select trunc(to_date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'IW') + 6 from dual; --本周第7天：2020/1/5
select trunc(to_date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'IW') - 7 from dual; --上周一：2019/12/23
select trunc(to_date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'IW') - 1 from dual; --上周日：2019/12/29

-- WW、W : ORACLE标准周
select trunc(to_date('2020-01-07 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'WW') from dual; --本周第1天：2020/1/1
select trunc(to_date('2020-01-07 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'W') from dual; --本周第1天：2020/1/1

select trunc(to_date('2020-09-30 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual; --本季度第1天：2020/7/1

ROUND(四舍五入)

ROUND(X, [FORMAT]) : 四舍五入日期，返回日期。FORMAT 中与周相关的有D，IW，WW，W，FMWW。

上述 TURNC 示例都可替换为 ROUND ，并返回日期。

30秒钟以后进位1分钟

select round(to_date('2020-01-01 12:00:29', 'yyyy-mm-dd hh24:mi:ss'), 'mi') from dual --2020/1/1 12:00:00
select round(to_date('2020-01-01 12:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'mi') from dual --2020/1/1 12:01:00

30分钟以后进位1小时

select round(to_date('2020-01-01 12:29:29', 'yyyy-mm-dd hh24:mi:ss'), 'hh24') from dual --2020/1/1 12:00:00
select round(to_date('2020-01-01 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'hh24') from dual --2020/1/1 13:00:00

12:00:00以后进位1天

select round(to_date('2020-01-01 11:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'dd') from dual --2020/1/1
select round(to_date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'dd') from dual --2020/1/2

每周第04天12:00:00以后进位一周

select round(to_date('2020-01-16 11:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'IW') from dual --2020/1/13
select round(to_date('2020-01-16 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'IW') from dual --2020/1/20

select round(to_date('2020-01-01 11:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'd') from dual --2019/12/29
select round(to_date('2020-01-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'd') from dual --2020/1/5

每月第16天00:00:00以后进位一月

select round(to_date('2020-01-15 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'mon') from dual --2020/1/1
select round(to_date('2020-01-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'mon') from dual --2020/2/1

每季度第2个月的第16天00:00:00以后进位一个季度

select round(to_date('2020-02-15 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual --2020/1/1
select round(to_date('2020-02-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual --2020/4/1

select round(to_date('2020-05-15 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual --2020/4/1
select round(to_date('2020-05-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual --2020/7/1

select round(to_date('2020-08-15 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual --2020/7/1
select round(to_date('2020-08-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual --2020/10/1

select round(to_date('2020-11-15 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual --2020/10/1
select round(to_date('2020-11-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual --2021/1/1

每年7月1日以后进位一年

select round(to_date('2020-06-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy') from dual --2020/1/1
select round(to_date('2020-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy') from dual --2021/1/1

TO_CHAR(格式化)

推荐链接： Oracle to_char格式化函数

TO_CHAR(X, [FORMAT]) : 格式化日期，返回字符串。将X按FORMAT格式转换成字符串。X是一个日期，FORMAT是一个规定了X采用何种格式转换的格式字符串，FORMAT 中与周相关的有D，IW，WW，W，FMWW。

select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'year') from dual; --本年：'twenty twenty'
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy') from dual; --本年：'2020'
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'syyyy') from dual; --本年：' 2020'
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yyy') from dual; --本年后三位：'020'
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'yy') from dual; --本年后两位：'20'
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'y') from dual; --本年后一位：'0'

select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'month') from dual; --本月：'october  '
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'mon') from dual; --本月：'oct'
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'mm') from dual; --本月：'10'

select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'day') from dual; --星期几：'thursday '
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'd') from dual; --本周第几天（星期日为第一天）：'5'
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'dd') from dual; --本月第几天：'15'
select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'ddd') from dual; --本年第几天：'289'

select to_char(to_date('2020-10-15 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 'hh24') from dual; --当前小时：'12'
select to_char(to_date('2020-10-15 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 'mi') from dual; --当前分钟：'12'
select to_char(to_date('2020-10-15 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 'ss') from dual; --当前秒钟：'12'

select to_char(to_date('2020-10-13 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'IW') from dual; --ISO标准周，本年第几周：'42'
select to_char(to_date('2020-10-13 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'WW') from dual; --ORACLE标准周，本年第几周：'41'
select to_char(to_date('2020-10-13 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'W') from dual; --ORACLE标准周，本月第几周：'2'

select to_char(to_date('2020-10-15 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'q') from dual; --本年第几季度：'4'

MONTHS_BETWEEN

–MONTHS_BETWEEN():获得两个日期相差的月数

select trunc(months_between(to_date('2020-1-5', 'yyyy-mm-dd'), to_date('2020-11-15', 'yyyy-mm-dd'))) from dual --返回'-10'

ADD_MONTHS

–ADD_MONTHS():在指定的日期加上指定的月数

select add_months(to_date('2020-01-05', 'yyyy-mm-dd'), 7) from dual --2020/8/5
select add_months(to_date('2020-01-05', 'yyyy-mm-dd'), -7) from dual --2019/6/5

NEXT_DAY

–NEXT_DAY():表示下个星期几

select next_day(to_date('2020-01-01 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 'MONDAY') from dual; --2020/1/6 12:12:12
select next_day(to_date('2020-01-01 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), '星期一') from dual;
select next_day(to_date('2020-01-01 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 1) from dual; --1表示是星期日，2020/1/5 12:12:12
select next_day(to_date('2020-01-01 12:12:12', 'yyyy-mm-dd hh24:mi:ss'), 2) from dual; --2表示是星期一，2020/1/6 12:12:12

与类型转换相关的单行函数

官方格式化相关知识: Format Models

/*

1.字符串与日期可以相互转换

2.字符串与数字可以相互转换

3.日期与数字不能相互转换

*/

TO_CHAR

推荐链接： Oracle to_char格式化函数

TO_CHAR(D,F)

将日期类型D根据模板F转换为字符串类型的值

FM 放在数字前面 去掉数字前面的空格

放在日期前面 去掉日期前面的0

select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss:ff') time1,
       to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff') time2
  from dual;

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss day d dd ddd') from dual
select to_char(sysdate, 'fmyyyy"年"mm"月"dd"日"') from dual

TO_CHAR(N,F)

将数字类型N根据模板F转换为字符串类型的值

SELECT TO_CHAR(25698.5997),
       TO_CHAR(25698.5997, '999999.99'),
       TO_CHAR(25698.5997, '999'),
       TO_CHAR(25698.5997, '000000.00'),
       TO_CHAR(25698.5997, 'FM$999,999.00'),
       TO_CHAR(25698.5997, 'FML999,999.00')
  FROM DUAL;

TO_DATE(C,F)

根据模板F将字符类型C转换为日期类型

SELECT TO_DATE('2020-01-01', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('2020-01-01', 'YYYY-MM-DD') - SYSDATE FROM DUAL;

TO_NUMBER(C,F)

根据模板F将字符类型C转换为数字类型的值

SELECT TO_NUMBER('1,000.00', '9,999.99') FROM DUAL;

通用的单行函数

NVL()

–NVL():当第一个参数不为NULL，返回第一个参数。当第一个参数为NULL，返回第二个参数。两个参数的类型必须一致。

SELECT LAST_NAME,NVL(TO_CHAR(COMMISSION_PCT,’FM0.00’),’没有佣金’) 不加FM是居中显示，加FM是靠左侧显示

FROM EMPLOYEES;

–当算术运算中出现了NULL时，结果一定为NULL.

–查询employees表中所有员工的last_name,salary,commission_pct,年薪,年收入(年薪+年薪*佣金)

SELECT LAST_NAME,SALARY,COMMISSION_PCT,SALARY12 AS 年薪,(SALARY12)+(SALARY12NVL(COMMISSION_PCT,0)) AS 年收入

FROM EMPLOYEES;

NVL2()

–NVL2():当第一个参数不为NULL,返回第二个参数。当第一个参数为NULL,返回第三个参数。

SELECT LAST_NAME,NVL2(COMMISSION_PCT,’有佣金’,’没有佣金’)

FROM EMPLOYEES;

case…when…then…else…end ‘别名’

–CASE表达式与DECODE()函数:分支选择 很重要

–与decode()相比，能加条件判断

–查询employees表中所有员工的last_name,job_id,salary,新工资。如果job_id为IT_PROG时，工资增加10%。当job_id为ST_CLERK时，工资增加15%。当job_id为SA_REP时，工资增加20%，其它job_id工资不变。

SELECT LAST_NAME,JOB_ID,SALARY,

CASE

WHEN JOB_ID=’IT_PROG’ THEN SALARY*1.10

WHEN JOB_ID=’ST_CLERK’ THEN SALARY*1.15

WHEN JOB_ID=’SA_REP’ THEN SALARY*1.20

ELSE SALARY

END AS 新工资

FROM EMPLOYEES;

SELECT LAST_NAME,JOB_ID,SALARY,

CASE JOB_ID

WHEN ‘IT_PROG’ THEN SALARY*1.10

WHEN ‘ST_CLERK’ THEN SALARY*1.15

WHEN ‘SA_REP’ THEN SALARY*1.20

ELSE SALARY

END AS 新工资

FROM EMPLOYEES;

decode

–与 case…when…then…else…end 相比，只能判断是否相等

SELECT LAST_NAME,JOB_ID,SALARY,

DECODE(

​ JOB_ID,

​ ‘IT_PROG’,SALARY*1.10,

​ ‘ST_CLERK’,SALARY*1.15,

​ ‘SA_REP’,SALARY*1.20,

​ SALARY

)AS 新工资

FROM EMPLOYEES;

Oracle中常用的数据类型

数字类型

1.整数：NUMBER,长度可以省略，如果省略默认为38位的数字。

2.浮点数：NUMBER(8,2),整个数字为8位其中有两位小数。

字符类型

不能省略长度。

1.CHAR:保存固定长度的字符串，如果内容的长度不够，使用空格在内容后面补齐长度。

2.VARCHAR2:保存可变长度的字符串，不会使用空格补齐长度。

日期类型

不能设置长度。

1.DATE:日期+时间

–创建STUDENTS表,列名 STU_ID 长度6，STU_NAME 20字符，STU_SEX 1字符，STU_BIRTHDAY

CREATE TABLE STUDENTS(

STU_ID NUMBER(6),

STU_NAME VARCHAR2(20 CHAR), //不写char默认byte类型

STU_SEX CHAR(1 CHAR),

STU_BIRTHDAY DATE

);

数据操作

增

INSERT INTO语句;

1.向表中添加一行新的数据，并向新行所有的列赋值。

格式:INSERT INTO 表名 VALUES(值,值,……);

例:INSERT INTO STUDENTS VALUES(1,’张三’,’男’,TO_DATE(‘1990-5-11’,’YYYY-MM-DD’));

2.向表中添加一行新的数据，并向新行中指定的列赋值。

格式:INSERT INTO 表名(列名,列名,……) VALUES(值,值,……);

例:INSERT INTO STUDENTS(STU_NAME,STU_ID,STU_SEX) VALUES(‘李四’,2,’女’);

3.将其它表中的数据复制到指定的表中。

格式:INSERT INTO 表名[(列名,列名,……)] SELECT语句;

例:INSERT INTO STUDENTS(STU_ID,STU_NAME) SELECT EMPLOYEE_ID,LAST_NAME FROM EMPLOYEES;

删

DELETE语句;

格式:DELETE [FROM] 表名 [WHERE 条件];

例:DELETE FROM STUDENTS WHERE STU_SEX IS NULL;

–删除STUDENTS表中STU_SEX列中所有的值。

UPDATE STUDENTS SET STU_SEX=NULL;

改

UPDATE语句;

格式:UPDATE 表名 SET 列名=值[,列名=值,……] [WHERE 条件];

例：

UPDATE STUDENTS SET STU_BIRTHDAY=SYSDATE WHERE STU_ID=2;

UPDATE STUDENTS SET STU_SEX=’女’,STU_BIRTHDAY=TO_DATE(‘1992-3-27’,’YYYY-MM-DD’) WHERE STU_ID<120;

UPDATE EMPLOYEES SET SALARY=SALARY+500 WHERE SALARY=2500;

SET LINESIZE 200;

表

CREATE TABLE 表名(

列名 数据类型[(长度)] [约束],

列名 数据类型[(长度)] [约束],

……

列名 数据类型[(长度)] [约束]

);

–查看表结构：DESC 表名

DESC STUDENTS;

–删除表：DROP TABLE 表名;

DROP TABLE USERS;

–截断表：删除表中所有数据，但不删除表。删除的数据不能ROLLBACK.

TRUNCATE TABLE 表名;

TRUNCATE TABLE STUDENTS;

约束

1.主键约束：PRIMARY KEY,主键不能重复，不能为NULL。通过主键可以在表中找到唯一的一行数据。

2.非空约束：NOT NULL。必填项。

3.检核约束：CHECK。当向列中添加与修改数据时，检查列中的数据是否合法。

4.默认值：DEFAULT。当没有向列中添加数据时，Oracle自动为列添加的数据。

5.唯一约束：UNIQUE。列中的数据不能重复，可以为NULL。

6.外键约束：FOREIGN KEY

CREATE TABLE USERS(

USER_ID NUMBER(6) PRIMARY KEY,

USER_NAME VARCHAR2(20) NOT NULL,

USER_AGE NUMBER(2) CHECK(USER_AGE BETWEEN 20 AND 50),

USER_SEX CHAR(1 CHAR) DEFAULT ‘女’

);

INSERT INTO USERS VALUES(1,’张三’,19);

INSERT INTO USERS VALUES(1,’张三’,25,NULL);

INSERT INTO USERS(USER_ID,USER_NAME,USER_AGE) VALUES(2,’BB’,20);

CREATE TABLE S(

S_ID NUMBER(5) PRIMARY KEY,

S_NAME VARCHAR2(20) NOT NULL

);

CREATE TABLE C(

C_ID NUMBER(3) PRIMARY KEY,

C_NAME VARCHAR2(100) NOT NULL

);

CREATE TABLE SC(

SC_ID NUMBER(8) PRIMARY KEY,

SC_S_ID NUMBER(5) NOT NULL,

SC_C_ID NUMBER(3) NOT NULL,

CONSTRAINT SC_SID_FK FOREIGN KEY(SC_S_ID) REFERENCES S(S_ID),

CONSTRAINT SC_CID_FK FOREIGN KEY(SC_C_ID) REFERENCES C(C_ID)

);

–向处键中添加数据：先添加主表中的数据，再添加子表中的数据。

INSERT INTO S VALUES(1,’AA’);

INSERT INTO C VALUES(1,’Java’);

INSERT INTO SC VALUES(1,1,1);

INSERT INTO SC VALUES(2,1,2);–错误

–删除外键中的数据：先删除子表中的数据，再删除主表中的数据。

DELETE FROM SC WHERE SC_S_ID=1;

DELETE FROM S WHERE S_ID=1;

–联合主键

–一个表只能有一个主键，但一个主键可以由多个列组成。

CREATE TABLE TEST(

T_NAME VARCHAR2(20),

T_SEX CHAR(1 CHAR),

CONSTRAINT TEST_PK PRIMARY KEY(T_NAME,T_SEX)

);

SQL语句的分类

1.DML:数据操纵语言。SELECT,INSERT,UPDATE,DELETE

2.DDL:数据定义语言。CREATE,DROP,TRUNCATE等

3.DCL:数据控制语言。COMMIT,ROLLBACK,SAVEPOINT等

事务

单位时间内的一系列的操作，这些操作要么全部成功，要么全部失败。

COMMIT:提交数据，将对表的操作保存到表中。

ROLLBACK:回退数据，将数据还原到最初或最后一次提交的状态。

/*

事务的组成

1.一个事务中可以有多个DML。

2.一个事务中只能有一个DDL语句或DCL语句。

*/

当执行DDL或DCL语言时，Oracle会自动提交事务。（也就是说当执行另外一个事务时，Oracle就会自动提交事务。）

–当异常结束Oracle会话时，Oracle会自动回退事务。

–当正常结束Oracle会话时，Oracle会自动提交事务。

序列

可以产生一个数字。

通常用于自动生成主键。

序列的值不能ROLLBACK。

–创建序列基本格式：

CREATE SEQUENCE 序列名;

CREATE SEQUENCE STUDENT_ID_SEQ;

–序列常用的属性：

1.NEXTVAL:获得序列的下一个值，每次调用些属性，序列的值都会发生改变。

2.CURRVAL:获得序列的当前值，调用此属性不会改变序列的值。

INSERT INTO STUDENTS(STU_ID,STU_NAME) VALUES(STUDENT_ID_SEQ.NEXTVAL,’AA’);

SELECT STUDENT_ID_SEQ.CURRVAL

FROM DUAL;

视图

–视图：伪表，视图中没有数据，视图的数据来自于表。

–格式：

CREATE [OR REPLACE] VIEW 视图名

AS

SELECT语句;

例：

CREATE OR REPLACE VIEW V1

AS

SELECT * FROM DEPARTMENTS;

–视图的作用

1.简化查询

2.提高数据库的安全性

索引

可以提高查询的效率，但会降低增删改的效率。

CREATE INDEX EMP_NAME_INDEX ON EMPLOYEES(LAST_NAME);

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%a%’;

多表连接

资源：

​ https://www.runoob.com/sql/sql-join.html

​ https://www.w3schools.com/sql/sql_join.asp

​ https://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5

交叉连接

(CROSS JOIN)

生成笛卡尔集，不使用任何匹配条件，直接将一个数据源中的每一行与另一个数据源的每一行匹配。

–笛卡尔集：

行数：表行数的乘积。

原因：没有连接条件或连接条件不正确。

select *
  from tab_kq_stuclass stuc, tab_kq_class c

内连接

相等连接

方式一：

格式：

SELECT

FROM 表名,表名

WHERE 连接条件

通常情况下，连接条件的个数是表的个数减一

缺点：只能查询出满足连接条件的数据。

多表连接时，建议每列的前面都加上表的前缀，可以提高查询的效率。

select stuc.*, c.*
  from tab_kq_stuclass stuc, tab_kq_class c
 where stuc.class_no = c.bh

方式二：

格式：

​ SELECT

​ FROM 表名

(INNER) JOIN 表名 ON 条件

(INNER) JOIN 表名 ON 条件

(INNER) JOIN 表名 ON 条件

​ WHERE 过滤条件

优点：过滤条件加到where中，可读性强。

select stuc.*, c.*
  from tab_kq_stuclass stuc
  join tab_kq_class c
    on stuc.class_no = c.bh

自然连接

(natural join)

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，返回所有符合条件的结果，并删除连接表中的重复列。

注意事项：

​ 1、如果做自然连接的两个表的有多个字段都满足 数据类型和列名都相同，那么他们会被作为自然连接的条件。

​ 2、如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。varchar与number类型之间会自动转换。

​ 3、查询列不能使用表名

我们也可以将自然连接理解为内连接的一种。

select *
  from tab_kq_stuclass stuc
  natural join tab_kq_class c

不等连接

在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

select *
  from tab_kq_stuclass stuc, tab_kq_class c
 where stuc.class_no != c.bh

外连接

查询出满足连接条件的数据与不满足连接条件的数据

左外连接

left join是以左表的记录为基表（基表、驱动表）,示例中tab_kq_stuclass可以看成左表,tab_kq_class可以看成右表,它的结果集是tab_kq_stuclass表中的数据，在加上tab_kq_stuclass表和tab_kq_class表匹配的数据。换句话说,左表(tab_kq_stuclass)的记录将会全部表示出来,而右表(tab_kq_class)只会显示符合搜索条件的记录。tab_kq_class表记录不足的地方均为NULL。

select stuc.*, c.*
  from tab_kq_stuclass stuc
  left outer join tab_kq_class c
    on stuc.class_no = c.bh

右外连接

和left join的结果刚好相反,是以右表(tab_kq_class)为基表, 显示tab_kq_class表的所以记录，在加上tab_kq_stuclass和tab_kq_class匹配的结果。 tab_kq_stuclass表不足的地方用NULL填充.

select stuc.*, c.*
  from tab_kq_stuclass stuc
 right outer join tab_kq_class c
    on stuc.class_no = c.bh

全外连接

左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。 全外连接不支持 (+) 这种写法。

select stuc.*, c.*
  from tab_kq_stuclass stuc
  full outer join tab_kq_class c
    on stuc.class_no = c.bh

Oracle特有的外连接语句：(+)

左外连接(左边的表不加限制)

(+) 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在右表，左表就是全部显示，故是左连接。

select stuc.*, c.*
  from tab_kq_stuclass stuc, tab_kq_class c
 where stuc.class_no = c.bh(+)

右外连接(右边的表不加限制)

(+) 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在左表，右表就是全部显示，故是右连接。

select stuc.*, c.*
  from tab_kq_stuclass stuc, tab_kq_class c
 where stuc.class_no(+) = c.bh

SQL Server中特有的外连接语句：*

左外连接

select stuc.*, c.*
  from tab_kq_stuclass stuc, tab_kq_class c
 where stuc.class_no* = c.bh

右外连接

select stuc.*, c.*
  from tab_kq_stuclass stuc, tab_kq_class c
 where stuc.class_no = *c.bh

自连接

(self join)

自连接是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

示例：

​ employee表中的每一名员工都有自己的manager(经理)，并且每一名经理也是公司的员工，也有自己的经理。

​ 下面我们需要将每一名员工自己的名字和经理的名字都找出来。这时候我们该怎么做呢？

如果我们有两张这样的表分别叫 employee 和 manager ，那么我们就很好写SQL语句。

select emp.name empName, mgr.name mgrName
  from employee emp, manager mgr
 where emp.mgrId = mgr.id

但现在我们只有一张 employee 表。我们可以采用自连接，自连接的本意就是将一张表看成多张表来做连接。我们可以这样来写SQL语句:

select emp.name empName, mgr.ename mgrName
  from employee emp, employee mgr
 where emp.mgrId = mgr.id
 order by emp.name

组函数(聚合函数)

–AVG()：求平均值

​ SELECT AVG(SALARY)

​ FROM EMPLOYEES;

–SUM()：求总和

​ SELECT SUM(SALARY)

​ FROM EMPLOYEES;

–MAX()/MIN()：求最大/最小值

​ SELECT MAX(SALARY),MIN(SALARY)

​ FROM EMPLOYEES;

​ SELECT MAX(HIRE_DATE),MIN(HIRE_DATE)

​ FROM EMPLOYEES;

​ SELECT MAX(LAST_NAME),MIN(LAST_NAME)

​ FROM EMPLOYEES;

–COUNT()：

返回查询结果的行数

​ SELECT COUNT(*)

​ FROM EMPLOYEES;

返回指定列中不为NULL的值的个数

​ SELECT COUNT(DEPARTMENT_ID)

​ FROM EMPLOYEES;

–组函数与DISTINCT关键字

SELECT COUNT(DISTINCT JOB_ID)

FROM EMPLOYEES;

–组函数与NULL

组函数会自动忽略NULL

SELECT AVG(NVL(COMMISSION_PCT,0))

FROM EMPLOYEES;

–组函数的嵌套

SELECT MAX(AVG(SALARY)),MIN(AVG(SALARY)),COUNT(*)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID;

/*

1.组函数嵌套时必须使用GROUP BY子句。

2.组函数嵌套时，SELECT子句中只能出现组函数嵌套的列(COUNT除外)。

3.组函数嵌套只能直接出现在SELECT子句中

*/

82、–统计每年入职的人数，显示年与人数

SELECT TO_CHAR(HIRE_DATE,’YYYY’),COUNT(*)

FROM EMPLOYEES

GROUP BY TO_CHAR(HIRE_DATE,’YYYY’)

ORDER BY TO_CHAR(HIRE_DATE,’YYYY’) ASC;

GROUP BY子句：分组

SELECT                    5

FROM                      1

[WHERE]                  2

[GROUP BY]            3

[HAVING]                 4

[ORDER BY]             6

当使用GROUP BY子句时，SELECT子句中非组函数的列必须出现在GROUP BY子句中参加分组。

HAVING子句:过滤分组结果，通常情况下使用HAVING子句时必须使用GROUP BY子句。

WHERE 和 HAVING 的区别：

WHERE子句不能使用组函数作为过滤条件

HAVING子句可以使用组函数作为过滤条件

当条件即可以放在WHERE子句中，也可以放在HAVING子句中时，建议放在WHERE子句中，可以提高查询效率。

SELECT JOB_ID,MIN(SALARY)

FROM EMPLOYEES

WHERE JOB_ID NOT LIKE ‘%REP%’

GROUP BY JOB_ID

HAVING MIN(SALARY)>6000 ;

子查询

又叫嵌套查询。

普通子查询

先执行子查询，再执行主查询

查询哪些员工的工资大于176号员工的工资，显示员工的last_name,salary.

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY>(SELECT SALARY

​ FROM EMPLOYEES

​ WHERE EMPLOYEE_ID=176);

/*

1.从Oracle8i开始除了GROUP BY子句以外，其它子句都可以使用子查询。

2.无论子查询出现在哪个子句中，子查询必须放在一对小括号内。

3.如果子查询作为条件，尽量将子查询放在运算符的右边，可以提高查询效率。

4.如果子查询作为条件，子查询中查询的列的个数与类型，必须与主查询条件中列的个数与类型保持一致。

5.除非执行TOP N的操作，否则不要在子查询中使用ORDER BY子句。

*/

子查询与组函数

–查询收入最高的员工的last_name,salary

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES);

–查询入职最早的员工的last_name,hire_date(格式为：YYYY-MM-DD)

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,’YYYY-MM-DD’)

FROM EMPLOYEES

WHERE HIRE_DATE=(SELECT MIN(HIRE_DATE) FROM EMPLOYEES);

在HAVING子句中使用子查询

–查询平均工资大于公司平均工资的部门，显示department_id,平均工资

SELECT DEPARTMENT_ID,AVG(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING AVG(SALARY)>(SELECT AVG(SALARY) FROM EMPLOYEES);

–查询人数最多的部门，显示department_id与人数

SELECT DEPARTMENT_ID,COUNT(*)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING COUNT()=(SELECT MAX(COUNT())

​ FROM EMPLOYEES

​ GROUP BY DEPARTMENT_ID);

多行子查询:ALL,ANY,IN

–查询哪些员工的工资大于60号部门所有员工的工资，显示last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE SALARY>(SELECT MAX(SALARY)

​ FROM EMPLOYEES

​ WHERE DEPARTMENT_ID=60);

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE SALARY**>ALL**(SELECT SALARY

​ FROM EMPLOYEES

​ WHERE DEPARTMENT_ID=60);

–查询哪些员工的工资大于60号部门任意一名员工的工资，显示last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE SALARY>(SELECT MIN(SALARY)

​ FROM EMPLOYEES

​ WHERE DEPARTMENT_ID=60);

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE SALARY**=ANY**(SELECT SALARY

​ FROM EMPLOYEES

​ WHERE DEPARTMENT_ID=60);

IN只能判断是否相等

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE SALARY IN (SELECT SALARY

​ FROM EMPLOYEES

​ WHERE DEPARTMENT_ID=60);

在SELECT子句中使用子查询

子查询只能返回一个值(一行一列)。

–查询每个部门的department_id,人数,此部门人数占公司总人数的百分比。

SELECT DEPARTMENT_ID,COUNT(),(COUNT()/(SELECT COUNT() FROM EMPLOYEES))100||’%’ AS 百分比

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

ORDER BY 1 ASC;

什么时候使用单行子查询什么时候使用多行子查询

91、–查询与入职最早的员工在一个部门工作的员工的last_name,hire_date,department_id

SELECT LAST_NAME,HIRE_DATE,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID

​ FROM EMPLOYEES

​ WHERE HIRE_DATE=(SELECT MIN(HIRE_DATE) FROM EMPLOYEES));

用可能多个人同一天入职，但是在不同部门，所以用IN。

伪列

–伪列：Oracle自动为表添加的列。以下两个

–ROWID:数据在硬盘或内存中的地址。唯一且不改变。

–ROWNUM:从查询结果返回的行的编号。这个伪列可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。

SELECT ROWNUM,LAST_NAME,SALARY

FROM EMPLOYEES

ORDER BY SALARY ASC;

–查询employees表中收入最低的前5名员工的last_name,salary

SELECT LAST_NAME,SALARY

FROM (SELECT LAST_NAME,SALARY

​ FROM EMPLOYEES

​ ORDER BY SALARY ASC)

WHERE ROWNUM<=5;

SQL Server

93、–查询employees表中收入最低的前5名员工的last_name,salary

SELECT TOP 5 LAST_NAME,SALARY

FROM EMPLOYEES

ORDER BY SALARY ASC;

MySQL

94、–查询employees表中收入最低的前5名员工的last_name,salary

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

ORDER BY SALARY ASC

LIMIT 5;

排名次

SELECT

​ ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS 名次1,

​ RANK() OVER(ORDER BY SALARY DESC) AS 名次2,

​ DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 名次3,

​ LAST_NAME,SALARY

FROM EMPLOYEES;

–ORDER BY SALARY DESC;

翻页功能(每页显示5行数据)

–不排序

SELECT R,LAST_NAME,SALARY

FROM (SELECT ROWNUM AS R,LAST_NAME,SALARY FROM EMPLOYEES)

WHERE R BETWEEN 1 AND 5;

–排序

SELECT R,LAST_NAME,SALARY

FROM (SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS R,LAST_NAME,SALARY FROM EMPLOYEES)

WHERE R BETWEEN 6 AND 10;

高级子查询

–成对子查询

–查询每个部门中工资最高的员工，显示last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE (NVL(DEPARTMENT_ID,0),SALARY) IN (SELECT NVL(DEPARTMENT_ID,0),MAX(SALARY)

​ FROM EMPLOYEES

​ GROUP BY DEPARTMENT_ID);

–相关子查询(关联子查询):先执行主查询，再执行子查询。

–查询工资大于所在部门平均工资的员工，显示last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES EMP

WHERE SALARY>(SELECT AVG(SALARY)

​ FROM EMPLOYEES

​ WHERE DEPARTMENT_ID=EMP.DEPARTMENT_ID);

集合运算：并集，交集，补集

并集：UNION/UNION ALL

–查询employees表中收入最高与最低的员工，显示last_name,salary

SELECT LAST_NAME AS NAME,SALARY

FROM EMPLOYEES

WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES)

UNION

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY=(SELECT MIN(SALARY) FROM EMPLOYEES)

ORDER BY SALARY ASC;

/*

1.集合运算时，集合中列的个数与类型的顺序必须保持一致。

2.集合运算时，第一个集合决定结果中列的标题。

3.集合运算时，最后一个集合决定排序的规则。

*/

–UNION:会自动去重复值。

–UNION ALL:不自动去重复值，在不考虑重复值的情况下，建议使用UNION ALL，可以提高查询效率。

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

UNION ALL

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

UNION ALL

SELECT LAST_NAME,SALARY

FROM EMPLOYEES;

交集：INTERSECT

–查询employees表中工资大于3000，并且在50号部门工作的员工的last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE SALARY>3000

INTERSECT

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID=50;

补集：MINUS

A:1 2 3 4 5 6

B:3 4 7

A - B = 1 2 5 6

B - A = 7

–查询employees表中工资最高的6-10名员工。

SELECT R,LAST_NAME,SALARY

FROM (SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS R,LAST_NAME,SALARY FROM EMPLOYEES)

WHERE R BETWEEN 6 AND 10;

SELECT LAST_NAME,SALARY

FROM (SELECT LAST_NAME,SALARY

​ FROM EMPLOYEES

​ ORDER BY SALARY DESC)

WHERE ROWNUM<=10

MINUS

SELECT LAST_NAME,SALARY

FROM (SELECT LAST_NAME,SALARY

​ FROM EMPLOYEES

​ ORDER BY SALARY DESC)

WHERE ROWNUM<=5

ORDER BY SALARY DESC;