Oracle数据库SQL语句相关

--甲骨文公司：大型的关系数据库

--对表的操作：增、删、改、查

--查询表：在关系数据库使用SELECT语句

--开始-->运行-->sqlplus
/*
system
dhee
*/

--在Oracle中导入脚本：@脚本的路径，如果路径中有空格，路径必须放在一对双引号中
--MySQL中导入脚本：source 脚本路径

--基本SELECT语句
SELECT 列名[,列名,……]|*|算术表达式
FROM 表名

--查询employees表中所有员工的last_name,salary
SELECT last_name,salary
FROM employees;
/*
1.在ORACLE数据库中一个SELECT语句内FROM子句不能省略。
2.SQL语句不区分大小写。
3.SQL语句可以写成一行或多行。
4.只能直接使用半角的标点。
5.在工作中不建议使用*号查询所有的列。
*/

--查询departments表中所有的信息
SELECT *
FROM departments;

--算术运算符：+，-，*，/
SELECT 5/2 -- 结果为2.5
FROM dual;

SELECT '5'+'2'--正确，结果为7
FROM dual;

SELECT '5'+'a'--错误，a无法转换为数字
FROM dual;
--+:只有加法运算的功能，没有连接符的功能。

--查询employees表中所有员工的last_name,salary,年薪
SELECT last_name AS "user",salary,salary * 12 AS "ALL SAL"
FROM employees;

--列别名
/*
1.列名 列别名
2.列名 AS 列别名,建议使用
3.Oracle会将英文的列别名变为大写。
4.如果列别名区分大小写，或列别名中包含了特殊字符，或列别名为关键字时，需要将列别名放在一对双引号中
*/

SELECT 'A' "A",'A'
FROM dual;

--在关系数据库中单引号表示字符串。

--连接符：||
SELECT FIRST_NAME||'.'||LAST_NAME AS NAME
FROM EMPLOYEES;

--DISTINCT关键字：屏蔽结果中重复的数据，只能屏蔽所有列都相同的数据。
--查询employees表中所有员工的job_id
SELECT DISTINCT job_id, salary
FROM employees;
--通常情况下，一个SELECT语句中只能出现一个DISTINCT关键字。
--通常情况下，DISTINCT只能出现在第一个列的前面。

--过滤与排序
--过滤：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表中1997-8-17入职的员工的last_name,salary,hire_date
SELECT last_name,salary,hire_date
FROM employees
WHERE hire_date='17-8月-97';

--比较运算符：>,>=,<,<=,=,!=,<>
--查询employees表中工资大于等于10000的员工的last_name,salary
SELECT last_name,salary
FROM employees
WHERE salary>10000;

--查询1997之后入职的员工的last_name,hire_date
SELECT last_name,hire_date
FROM employees
WHERE hire_date>='01-1月-97';

--特殊比较运算符：
--BETWEEN..AND..:查询指定范围之内的数据（包含了边界值）
--查询employees表中工资在6000-12000之间的员工的last_name,salary,department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary BETWEEN 6000 AND 12000;

--查询employees表中97-99年入职的员工last_name,hire_date
SELECT last_name,hire_date
FROM employees
WHERE hire_date BETWEEN '01-1月-97' AND '31-12月-99';

--IN(值列表):使列与列表中任意一个值匹配
--查询employees表中在20或50号部门工作的员工的last_name,department_id
SELECT last_name,department_id
FROM employees
WHERE department_id IN (20,50);

--LIKE:模糊查询
--%：任意长度的任意字符。
--_:一个长度的任意字符。
--查询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 '/';--使用ESCAPE声明一个符号，通知LIKE在查询的条件中这个符号后面的一个_或%不再是转义字符，而是查询的内容。

SELECT 'a''s'
FROM dual;

--IS NULL/IS NOT NULL
--关系数据库提供一个与类型无关，表示未知或不确定的值。
--查询employees表中department_id为NULL的员工，显示last_name,hire_date,job_id,salary,department_id。
SELECT last_name,hire_date,job_id,salary,department_id
FROM employees
WHERE department_id is null;

--逻辑运算符：AND,OR,NOT
--查询employees表中工资在6000-12000之间的员工的last_name,salary,department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary BETWEEN 6000 AND 12000;

SELECT last_name,salary,department_id
FROM employees
WHERE salary>=6000 AND salary<=12000;

--查询employees表中在20或50号部门工作的员工的last_name,department_id
SELECT last_name,department_id
FROM employees
WHERE department_id IN (20,50);

SELECT last_name,department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;

--查询employees表中工资在5000-12000，并且在20号或50号部门工作的员工的last_name,job_id,salary,department_id
SELECT last_name,job_id,salary,department_id
FROM employees
WHERE salary BETWEEN 5000 AND 12000 AND department_id IN (20,50);

--AND的优先级大于OR
SELECT last_name,job_id,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%');

--排序
--ORDER BY子句
SELECT 3
FROM  1
[WHERE] 2
[ORDER BY 列] 4 --通常情况下，ORDER BY子句会出现在SELECT语句的最后。

/*
1.升序：使用ASC关键字，默认为升序
2.降序：使用DESC关键字
*/
SELECT last_name,salary
FROM employees
ORDER BY salary ASC;

SELECT last_name,salary
FROM employees
ORDER BY salary DESC;

SELECT last_name,hire_date
FROM employees
ORDER BY hire_date ASC;

SELECT last_name,hire_date
FROM employees
ORDER BY last_name ASC;

SELECT last_name,salary *12 AS "sal"
FROM employees
--WHERE sal>10000 --错误，WHERE中不能使用列别名
ORDER BY 2 ASC;

/*
1.ORDER BY子句中可以出现列名。
2.ORDER BY子句中可以出现列别名(注意大小写)。
3.ORDER BY子句中可以出现算术表达式。
4.ORDER BY子句中可以出现结果中列的序号。
*/

--ORDER BY子句中可以出现多个列。
--查询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放在最前面/最后面。

SELECT DISTINCT last_name--错误，ORDER BY中排序的列没有在SELECT子句中出现
FROM employees
ORDER BY salary ASC;

--当SELECT语句中出现了DISTINCT或GROUP BY时，ORDER BY子句中排序的列必须在SELECT子句中出现。

--创建表
--基本的语句格式：
CREATE TABLE 表名(
列名 数据类型[(长度)] [约束],
列名 数据类型[(长度)] [约束],
……
列名 数据类型[(长度)] [约束]
);

--常用数据类型：
--数字类型：NUMBER
--整数：NUMBER(6),长度可以省略，如果省略默认为38位的数字。
--浮点数：NUMBER(8,2),长度不能省略，整个数字为8位，其中有两位小数。

--字符类型：长度不能省略。
--CHAR:保存固定长度的字符串，如果内容的长度不够，使用空格在内容的后面补齐长度。
--VARCHAR2:保存可变长度的字符串，不会使用空格补齐长度。

--日期类型：不能指定长度。
--DATE：保存日期+时间。

--LONG:长字符串类型。
--大对象：
--BLOB:二进制的大对象。
--CLOB：文本大对象。
--BFILE：大文件，将文件保存在硬盘中。

CREATE TABLE STUDENT(
STUDENT_ID NUMBER(6),
STUDENT_NAME VARCHAR2(20 CHAR),
STUDENT_SEX CHAR(1 CHAR),
STUDENT_BIRTHDAY DATE
);

CREATE TABLE EMP(
EMP_ID NUMBER(3),
EMP_NAME VARCHAR2(25),--长度为25个字节
EMP_SAL NUMBER(8,2),
EMP_DEPT NUMBER(3)
);

--查看表结构：DESC 表名;

--数据操作：增，删，改
--添加数据：INSERT INTO语句
1.向表中添加一行新数据，并向新行中所有的列赋值。
格式：INSERT INTO 表名 VALUES(值,值,……);
INSERT INTO STUDENT VALUES(1,'AA','男','01-10月-95');

2.向表中添加一行新数据，并向新行中指定的列赋值。
格式：INSERT INTO 表名(列名,列名,……) VALUES(值,值,……);
INSERT INTO STUDENT(STUDENT_NAME,STUDENT_ID) VALUES('BB',2);

3.将其它表中的数据添加到指定表中。
格式：INSERT INTO 表名[(列名,列名,……)] SELECT语句;
INSERT INTO EMP SELECT employee_id,last_name,salary,department_id FROM employees;

--修改数据：UPDATE语句。
格式：UPDATE 表名 SET 列=值[,列=值,……] [WHERE 条件];
--修改EMP表中EMP_ID大于200的员工，将这些员工的EMP_SAL增加500元。
UPDATE EMP SET EMP_SAL=EMP_SAL+500 WHERE EMP_ID>200;
--将EMP表中EMP_DEPT为NULL的员工的工资减1000，并将这些员工的EMP_DEPT改为50.
UPDATE EMP SET EMP_SAL = EMP_SAL-1000,EMP_DEPT=50 WHERE EMP_DEPT IS NULL;

--删除数据：DELETE语句
 格式:DELETE [FROM] 表名 [WHERE 条件];
--删除EMP表中EMP_ID为149的员工信息
DELETE FROM EMP WHERE EMP_ID=149;

UPDATE EMP SET EMP_DEPT = NULL ;

--约束：
1.主键约束：PRIMARY KEY。通过主键可以在表中找到唯一的一行数据。主键不能重复，不能为NULL。
2.非空约束：NOT NULL。
3.检查约束：CHECK。当向列中添加数据或修改列中的数据时，检查数据是否满足条件。检查条件必须放在一对小括号中，检查条件中的值不能是变量。
4.默认值：  DEFAULT。当没有向列中添加数据时，ORACLE自动向列中添加的数据。
5.唯一约束：UNIQUE。列不能重复，可以为NULL。
6.外键约束：FOREIGN KEY。 

DROP TABLE USERS;

CREATE TABLE USERS(
USER_ID NUMBER(5) PRIMARY KEY,
USER_NAME VARCHAR2(20) NOT NULL,
USER_SEX CHAR(1 CHAR) CHECK(USER_SEX='男' OR USER_SEX='女'),
USER_LOGIN DATE DEFAULT SYSDATE,
USER_EMAIL VARCHAR2(30) UNIQUE
);

INSERT INTO USERS(USER_ID,USER_NAME,USER_SEX,USER_EMAIL) VALUES(2,'BB','女','BB@163.COM');
INSERT INTO USERS(USER_ID,USER_NAME,USER_SEX,USER_EMAIL) VALUES(3,'CC','女','CC@163.COM');


CREATE TABLE S(
S_ID NUMBER(6) PRIMARY KEY,
S_NAME VARCHAR2(10 CHAR) 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(6) 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);

DELETE FROM C WHERE C_ID=1;

--事务：单位时间内的一系列的操作，这些操作要么全部成功，要么全部失败。
COMMIT:提交事务。将对数据的操作确认到表中。
ROLLBACK:回退事务（回滚事务）。将数据还原为最初或最后一次提交的状态。

--提交事务：
/*
1.执行DDL语句
2.执行DCL语句
3.当正常结束会话时，ORACLE会自动提交事务。
4.当异常结束会话时，ORACLE会自动回退事务。
*/

--视图：
--基本格式：
CREATE [OR REPLACE] VIEW 视图名
AS
SELECT 语句；

CREATE OR REPLACE VIEW V1
AS
SELECT last_name AS name,job_id,salary,department_id
FROM employees
WHERE (salary>=5000 AND salary<=12000) AND (department_id = 20 OR department_id = 50);

SELECT *
FROM V1;

--视图的作用：
1.可以简化查询。
2.可以提高数据库的安全性。

--视图中没有数据，视图中的数据来自于表。

--序列：可以产生一个数字。通常用于自动生成主键。
--创建序列的格式
CREATE SEQUENCE 序列名
[INCREMENT BY N]--每次序列改变时加N或减N
[START WITH N]--起始值
[MAXVALUE N|NOMAXVALUE]--最大值为N|没有最大值
[MINVALUE N|NOMINVALUE]--最小值为N|没有最小值
[CYCLE|NOCYCLE]--循环|不循环（默认）
[CACHE N|NOCACHE]--当服务器启动时将序列的后N个值放在缓存中（默认）|当服务器启动时不将序列的值放在缓存中。

CREATE SEQUENCE S_ID_SEQ;

INSERT INTO S VALUES(S_ID_SEQ.NEXTVAL,'AA');

--序列属性：
1.NEXTVAL:获得序列的下一个值，每次调用此属性，序列的值都会发生改变。
2.CURRVAL:获得序列的当前值，每次调用此属性，序列的值不发生改变。

--序列的值无法ROLLBACK。

--索引：提高查询的效率，但会降低增删改的效率。
/*
1.自动创建：当列为主键或唯一约束时，ORACLE会为列添加唯一索引。
2.手动创建：
格式：CREATE INDEX 索引名 ON 表名(列名[,列名,……]);
*/

CREATE INDEX EMP_NAME_INDEX ON EMPLOYEES(LAST_NAME);

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

--截断表：TRUNCATE TABLE 表名;删除表中所有的数据，但不删除表结构。
--DROP TABLE、TRUNCATE TABLE、不带WHERE条件的DELETE语句，请写出它们的相同点与不同点？
/*
相同点：删除表中所有的数据。
不同点：
1.DROP TABLE、TRUNCATE TABLE删除的数据不能回退。DELETE删除的语句可以回退。
2.DROP TABLE、TRUNCATE TABLE在删除数据时无法触发表的触发器，DELETE可以触发表的触发器。
3.速度：DROP TABLE>TRUNCATE TABLE>DELETE
*/

--查询当前用户中所有的表
SELECT * FROM tab;

SELECT table_name FROM user_tables;

--单行函数：
--字符类型，数字类型，日期类型，类型转换，通用
--字符类型单行函数：
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 last_name LIKE '%a%' OR last_name LIKE '%A%';

SELECT last_name
FROM employees
WHERE UPPER(last_name) LIKE '%A%';

SELECT last_name
FROM employees
WHERE LOWER(last_name) LIKE '%a%';

SELECT last_name,SUBSTR(last_name,3)--从指定的位置截取字符串，直到字符串的最后
FROM employees;

SELECT last_name,SUBSTR(last_name,3,2)--从指定的位置开始截取指定长度的字符串
FROM employees;

SELECT last_name,SUBSTR(last_name,-3)--从倒数第N位开始截取字符串，直到字符串的最后。
FROM employees;

SELECT last_name,SUBSTR(last_name,-3,2)--从倒数第N位开始截取指定长度的字符串
FROM employees;

SELECT last_name,LENGTH(last_name)
FROM employees;

SELECT LENGTH('中国')--获得参数的字符数
FROM dual;

SELECT LENGTHB('中国')--获得参数所字节数
FROM dual;

--在第一个参数中查找第二个参数首次出现的位置，没找到返回0
SELECT last_name,INSTR(last_name,'a')
FROM employees;

----查询employees表中last_name中包含a(不区分大小写)的员工的last_name（不允许使用LIKE）.
SELECT last_name
FROM employees
WHERE INSTR(LOWER(last_name),'a')!=0;

SELECT TRIM('    ABC ABC    ') AS A--去掉字符串两端的空格
FROM dual;

SELECT TRIM('A' FROM 'AAAABACDEAAAA')--去掉字符串两端出现的指定的字符
FROM dual;

--LPAD()/RPAD():
SELECT 'DHEE'||LPAD(employee_id,6,0),last_name
FROM employees;

--REPLACE():将第一个参数中出现的第二个参数用第三个参数替换。
SELECT REPLACE('www.dhee.com','w','W')
FROM dual;

--数字类型单行函数：
--四舍五入：
SELECT ROUND(256.6547),ROUND(256.6547,2),ROUND(256.6547,0),ROUND(256.6547,-3)
FROM dual;

--截断数字：
SELECT TRUNC(256.6547),TRUNC(256.6597,2),TRUNC(256.6547,0),TRUNC(256.6547,-1)
FROM dual;

--MOD():取余数
SELECT MOD(15,2)
FROM dual;

--日期类型单行函数
--获得数据库服务器当前日期+时间。
SELECT SYSDATE - 10000
FROM dual;

--查询employees表中所有员工的last_name,hire_date
SELECT last_name,hire_date,TRUNC((SYSDATE-hire_date)/30) AS 月数
FROM employees;

--ORACLE的日期计算
/*
1.日期+天数=日期
2.日期-天数=日期
3.日期-日期=天数
4.日期不能加日期
*/

--MONTHS_BETWEEN(D1,D2):获得两个日期相差的月数。
SELECT last_name,hire_date,TRUNC(MONTHS_BETWEEN(SYSDATE,hire_date)) AS 月数
FROM employees;


--ADD_MONTHS(D1,N1):在指定的日期加N个月
SELECT last_name,hire_date, ADD_MONTHS(hire_date,36)
FROM employees;

SELECT ADD_MONTHS(SYSDATE,1)
FROM DUAL;

--类型转换单行函数
/*
1.自动转换：ORACLE有时会自动将一个类型转换为另一个类型。
2.手动转换：TO_CHAR,TO_DATE,TO_NUMBER

字符类型与日期类型可以相互转换。
字符类型与数字类型可以相互转换。
在ORACLE中日期类型与数字类型不能相互转换。
*/

SELECT last_name,department_id
FROM employees
WHERE department_id = '50';

--TO_CHAR(D,F):将日期类型D根据模板F转换为字符类型的值。
SELECT last_name,TO_CHAR(hire_date,'FMYYYY-MM-DD')
FROM employees;

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS DAY DY')
FROM dual;

SELECT last_name,TO_CHAR(hire_date,'FMYYYY"年"MM"月"DD"日"')
FROM employees;

SELECT TO_CHAR(SYSDATE,'D DD DDD')
FROM dual;

--查询employees表中所有5月入职的员工的last_name,hire_date
SELECT last_name,hire_date
FROM employees
WHERE TO_CHAR(hire_date,'MM')='05';

SELECT last_name,hire_date
FROM employees
WHERE TO_CHAR(hire_date,'MON')='5月 ';

--TO_CHAR(N,F):将数字类型N根据模板F转换为字符类型的值。
SELECT 
	TO_CHAR(25478.967,'FM9999999.99999'),
	TO_CHAR(25478.967,'99'),
	TO_CHAR(25478.967,'FM0000000.00000'),
	TO_CHAR(25478.967,'FM99,999.00'),
	TO_CHAR(25478.967,'FM$99,999.00'),
	TO_CHAR(25478.967,'FML99,999.00')
FROM dual;

--TO_DATE(C,F):根据模板F将字符类型C，转换为日期类型。
SELECT TO_DATE('1999-10-10','YYYY-MM-DD')
FROM dual;

SELECT TO_DATE('2015-10-1','YYYY-MM-DD') - SYSDATE
FROM dual;

INSERT INTO STUDENT VALUES(S_ID_SEQ.NEXTVAL,'Tom','男',TO_DATE('1995-1-1','YYYY-MM-DD'));

--通用单行函数
--NVL():当第一个参数不为NULL时同，返回第一个参数。当第一个参数为NULL时，返回第二个参数。两个参数所类型必须保持一致。
--查询employees表中所有员工的last_name,commission_pct
SELECT last_name,NVL(TO_CHAR(commission_pct,'FM0.00'),'没有佣金')
FROM employees;

--当算术运算中出现了NULL时，结果一定为NULL。
--查询employees表中所有员工的last_name,job_id,salary,commission_pct,年薪,年收入（年薪+年薪*佣金）
SELECT last_name,job_id,salary,commission_pct,salary*12 AS 年薪,(salary*12)+(salary*12*NVL(commission_pct,0)) AS 年收入
FROM employees;

--NVL2():当第一个参数不为NULL，返回第二个参数。当第一个参数为NULL在，返回第三个参数。
SELECT last_name,NVL2(commission_pct,'有佣金','没有佣金')
FROM employees;

--选择语句
--CASE表达式:结果的类型必须保持一致。
--查询employees表中所有员工的last_name,job_id,salary,新工资，当JOB_ID为ST_CLERK时，salary增加10%，当JOB_ID为IT_PROG时，工资增加15%，当JOB_ID为SA_REP时，工资增加20%，其它职位工资不变。
SELECT last_name,job_id,salary,
CASE
WHEN JOB_ID='ST_CLERK' THEN salary*1.10
WHEN JOB_ID='IT_PROG' 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 'ST_CLERK' THEN salary*1.10
WHEN 'IT_PROG' THEN salary*1.15
WHEN 'SA_REP' THEN salary*1.20
ELSE salary
END AS 新工资
FROM employees;

--DECODE():只能判断是否相等。
SELECT last_name,job_id,salary,
DECODE(
	job_id,
	'ST_CLERK',
	salary*1.10,
	'IT_PROG',
	salary*1.15,
	'SA_REP',
	salary*1.20,
	salary
) AS 新工资
FROM employees;

--查询employees表中所有员工的last_name,hire_date(格式为：1999-1-1),入职日期的星期几
SELECT last_name,TO_CHAR(hire_date,'FMYYYY-MM-DD') AS 入职时间,TO_CHAR(hire_date,'DAY') AS 星期
FROM employees
ORDER BY TO_CHAR(hire_date - 1,'D') ASC;

--多表连接
--等值连接（内连接）：只能查询出满足连接条件的数据。
--查询员工的last_name,department_name
SELECT emp.last_name,emp.department_id,dept.department_name
FROM employees emp,departments dept
WHERE emp.department_id=dept.department_id AND emp.department_id = 50;

--笛卡尔集：
--行数：表行数的乘积。
--笛卡尔集产生的原因：没有连接条件或连接条件不正确。

--通常情况下，连接条件的个数是表的个数减1
--查询员工的last_name,department_name,city
SELECT emp.last_name,dept.department_name,loc.city
FROM employees emp,departments dept,locations loc
WHERE emp.department_id = dept.department_id 
AND dept.location_id=loc.location_id; 

--SQL-1999标准多表连接
--查询员工的last_name,department_name
SELECT emp.last_name,dept.department_name
FROM employees emp INNER JOIN departments dept 
ON emp.department_id = dept.department_id 
WHERE emp.department_id = 50;

--查询员工的last_name,job_title,department_name,city
SELECT emp.last_name,job.job_title,dept.department_name,loc.city
FROM employees emp JOIN departments dept ON emp.department_id = dept.department_id
JOIN jobs job ON job.job_id = emp.job_id
JOIN locations loc ON loc.location_id = dept.location_id;

--外连接：查询出满足连接条件与不满足连接条件的数据。

--左外连接
--查询所有员工的last_name,department_name
SELECT emp.last_name,dept.department_name
FROM employees emp LEFT JOIN departments dept 
ON emp.department_id = dept.department_id;

--右外连接
SELECT emp.last_name,dept.department_name
FROM employees emp RIGHT OUTER JOIN departments dept 
ON emp.department_id = dept.department_id;

--全外连接
SELECT emp.last_name,dept.department_name
FROM employees emp FULL OUTER JOIN departments dept 
ON emp.department_id = dept.department_id;

--Oracle中特有的外连接语法:(+)
--左外连接
SELECT emp.last_name,dept.department_name
FROM employees emp , departments dept
WHERE emp.department_id = dept.department_id(+);
--右外连接
SELECT emp.last_name,dept.department_name
FROM employees emp , departments dept
WHERE emp.department_id(+) = dept.department_id;

--SQL Server中特有的外连接语法：*
--左外连接
SELECT emp.last_name,dept.department_name
FROM employees emp , departments dept
WHERE emp.department_id *= dept.department_id;
--右外连接
SELECT emp.last_name,dept.department_name
FROM employees emp , departments dept
WHERE emp.department_id =* dept.department_id;

--组函数（聚合函数，多行函数）
--常用的组函数：
--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;

SELECT COUNT(department_id)--返回指定列中非NULL的值的个数
FROM employees;

--组函数与NULL:所有的组函数会自动忽略NULL
SELECT AVG(NVL(commission_pct,0))
FROM employees;

--GROUP BY子句：分组，统计，报表。
SELECT 4
FROM 1
[WHERE] 2
[GROUP BY] 3
[ORDER BY] 5

--查询每个部门的人数，显示部门编号与人数
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id
ORDER BY department_id ASC;

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

--查询每个部门中每个职位的人数，显示department_id,job_id,人数
SELECT department_id,job_id,COUNT(*)
FROM employees
GROUP BY department_id,job_id
ORDER BY department_id ASC;

SELECT COUNT(*)--正确，GROUP BY中的列可以在SELECT中省略
FROM employees
GROUP BY job_id;

--HAVING子句:过滤分组的结果，使用HAVING子句时必须使用GROUP BY子句。
SELECT 5
FROM 1
[WHERE] 2
[GROUP BY] 3
[HAVING] 4
[ORDER BY] 6

--WHERE子句中不能使用组函数作为过滤条件。
--HAVING子句可以使用组函数作为过滤条件。

--查询平均工资大于10000的department_id,平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)>10000
ORDER BY department_id ASC;

--查询最高工资大于6000，并且职位中不包含REP的job_id与最高工资，结果根据工资降序排序。
SELECT job_id,MAX(salary)
FROM employees
WHERE NOT job_id LIKE '%REP%'
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) DESC;

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

--组函数嵌套
--查询所有部门的最高的平均工资
SELECT MAX(AVG(salary)),MIN(AVG(salary))
FROM employees
GROUP BY department_id;

/*
1.当组函数嵌套时必须使用GROUP BY子句.
2.当组函数嵌套时，SELECT子句中尽量不要出现非组函数嵌套的列。
3.组函数嵌套只能直接出现在SELECT子句中。
*/

--子查询（嵌套查询）
--查询employees表中哪些员工的工资大于176号员工，显示这些员工的last_name,salary
SELECT last_name,salary
FROM employees
WHERE salary>(SELECT salary
			  FROM employees
			  WHERE employee_id=176);

/*
1.从ORACLE8开始除了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);

--HAVING与子查询
--查询人数大于等于3人的部门编号与人数
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*)>=3;

--查询人数最多的部门编号与人数
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*)=(SELECT MAX(COUNT(*))
				 FROM employees
				 GROUP BY department_id);

--SELECT子句与子查询:子查询只能返回一个值（一行一列）
--查询每个部门的department_id,人数,此部门人数占公司总人数的百分比
SELECT department_id,COUNT(*),(COUNT(*)/(SELECT COUNT(*) FROM employees))*100||'%' AS 百分比
FROM employees
GROUP BY department_id;

--伪列：ORACLE自动为表添加的列。
--ROWID:数据行在硬盘或内存中的地址。
--ROWNUM:行号。只能小于或小于等于一个值，或等于1。否则返回0行数。

SELECT ROWID,last_name,salary
FROM employees;

SELECT ROWNUM,last_name,salary
FROM employees
ORDER BY salary DESC;

--FROM子句与子查询
--查询employees收入最低的前5名
SELECT last_name,salary
FROM (SELECT last_name,salary
	  FROM employees
	  ORDER BY salary ASC)
WHERE ROWNUM<=5;
	  
--SQL Server
SELECT TOP 5 last_name,salary
FROM employees
ORDER BY salary ASC;

--MySQL
SELECT last_name,salary
FROM employees
ORDER BY salary ASC 
LIMIT 5;

SELECT last_name,salary
FROM employees
WHERE ROWNUM>10;

--多行子查询:ANY,ALL,IN
--查询哪些员工的工资大于60号部门任意一名员工的工资，显示员工的last_name,salary
SELECT last_name,salary
FROM employees
WHERE salary>(SELECT MIN(salary)
			  FROM employees
			  WHERE department_id = 60);

SELECT last_name,salary
FROM employees
WHERE salary>ANY(SELECT salary
			     FROM employees
			     WHERE department_id = 60);
--只能判断是否相等。
SELECT last_name,salary
FROM employees
WHERE salary IN (SELECT salary
			     FROM employees
			     WHERE department_id = 60);

--高级子查询
--成对子查询
--查询每个部门最早入职的员工，显示last_name,department_id,hire_date(YYYY-MM-DD)
SELECT last_name,department_id,TO_CHAR(hire_date,'YYYY-MM-DD')
FROM employees
WHERE (department_id,hire_date) IN (SELECT department_id,MIN(hire_date)
									FROM employees
									GROUP BY department_id);

--相关子查询（关联子查询）:先执行主查询，再执行子查询
--查询哪些员工的工资大于所在部门的平均工资，显示last_name,department_id,salary
SELECT last_name,department_id,salary
FROM employees emp
WHERE salary>(SELECT AVG(salary)
			  FROM employees
			  WHERE department_id = emp.department_id);

--集合运算：并集，交集，补集
--并集：UNION/UNION ALL
--查询收入最高与收入最低的员工的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.最后一个集合决定结果排序的规则。
*/

SELECT last_name,salary
FROM employees
UNION--自动去重复
SELECT last_name,salary
FROM employees;

SELECT last_name,salary
FROM employees
UNION ALL--不去重复，在工作中如果不考虑重复值的问题，建议UNION ALL
SELECT last_name,salary
FROM employees;

--交集：INTERSECT
--查询在50号部门工作并且工资大于3000的员工的信息
SELECT last_name,department_id,salary
FROM employees
WHERE department_id = 50
INTERSECT
SELECT last_name,department_id,salary
FROM employees
WHERE salary>3000;

--补集:MINUS
A: 1 2 3 4 5 6
B: 2 3 4 7
A-B=1 5 6
B-A=7
--查询收入最高的6-10名员工的last_name,salary
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 2 DESC;