网站开发雇佣平台,搜索网站 模板,做动画 的 网站,东莞市国外网站建设平台文章目录 Oracle数据库体系结构简介补充SQL初步导入sql文件别名连接符distinct去重的坑 过滤和排序数据日期格式比较运算其它比较运算符逻辑运算优先级排序 单行函数SQL中不同类型的函数单行函数字符数值日期转换通用 使用条件表达式嵌套查询 多表查询等值连接非等值连接左外连… 文章目录 Oracle数据库体系结构简介补充SQL初步导入sql文件别名连接符distinct去重的坑 过滤和排序数据日期格式比较运算其它比较运算符逻辑运算优先级排序 单行函数SQL中不同类型的函数单行函数字符数值日期转换通用 使用条件表达式嵌套查询 多表查询等值连接非等值连接左外连接满外连接自连接 聚合分组函数子查询创建和管理表创建表 数据处理约束NOT NULL列约束和表约束添加或删除约束 视图视图基础Top-N分析 其它数据对象序列索引同义词 控制用户权限及练习授权收回对象权限 SET集合运算符高级子查询多列子查询在FROM 子句中使用子查询在 SQL 中使用单列子查询相关子查询使用 EXISTS 和 NOT EXISTS 操作符使用子查询删除和更新数据使用 WITH 子句 Oracle数据库体系结构简介
Oracle实例是存在于物理内存上的一种数据结构用来管理和访问Oracle数据库由系统提供的多个后台进程和一个共享内存池组成共享的内存池可以被所有进程访问 实际上oracle实例也就是平常所说的数据库服务 区别实例可以操作任何数据库任何时刻一个实例只能关联一个数据库但是一个数据库可以被多个实例同时操作【RAC】 在双十一同款商品可以被多个用户访问单个用户只能操作一件商品 -- 插入 sql 文件
d:/del_data.sql;
d:/hr_cre.sql;
d:/hr_popul.sql;SQL语句主要分为三大类
种类含义DMLData Manipulation Language数据库操纵语言修改和查询数据记录INSERTDELETEUPDATESELECTDDLData Manipulation Language数据库定义语言定义数据库的结构创建修改和删除CREATE TABLECREATE INDEXDROP TABLEDROP INDEXALTER TABLE修改表结构添加、删除和修改列长度DCLData Control Language数据库控制语言用来控制数据库的访问GRANT授权REVOKE撤销授权COMMIT提交事务ROLLBACK事务回退SAVEPOINT设置保存点LOCK锁定特定部分数据库
补充SQL初步
--查询表结构
desc employees;算术运算符作用于时间时间只能加减不能乘除和数字
--查询前天今天和明天日期
select sysdate-1, sysdate, sysdate1
from dual;NULL值无效为指定的值并不是空格或者0。凡是NULL值参与了运算结果都是NULL
导入sql文件
d:/xxx.sql别名
--别名使用双引号字符串是单引号
SELECT sysdate-1 AS 昨天, sysdate AS 今天, sysdate1 AS 明天
FROM dual需要注意的是oracle中字符串是单引号别名用的是双引号 连接符
和MySQL的 CONCAT(str1, str2)不同的是把列于列列与字符连接在一起用 || 表示合成列
--oracle中拼接字符串时 ||
SELECT EMPLOYEE_NAME || s ID is: || EMPLOYEE_ID
FROM employeedistinct去重的坑
--查询出的字段需要数量上相对
SELECT last_name, distinct(department_id)
FROM employee报错原因是 last_name 需要和 去重后的部门ID 数量相对应否则就是表达式缺失 过滤和排序数据 WHERE 子句紧跟随在 FROM 后边 日期格式
字符和日期要包含在单引号中字符大小写敏感日期格式敏感默认的日期格式是 DD-MON月-RR
--查询的日期格式
SELECT last_name, hire_date FROM employees WHERE hire_date 7-6-1994
--查询出的hire_date字段数据格式1994/6/7 星期
SELECT last_name, hire_date FROM employees WHERE to_char(hire_date, yyyy-mm-dd) 1994-06-07比较运算
操作符含义小于小于等于等于大于等于大于!不等于也可以是
其它比较运算符
操作符含义BETWEEN…AND…闭区间内的值INT(SET)等于值列表中的一个LIKE模糊查询IS NULL空值IS NOT NULL非空值
--模糊查询过程中转义字符问题查询名字中含有_的员工
SELECT last_name, department_id, salary
FROM employees
WHERE last_name LIKE %\_%a EXCAPE (\)逻辑运算
操作符含义AND并OR或NOT否
优先级
优先级1算术运算符2连接运算符3比较运算符4IS [NOT] NULLINLIKE5[NOT] BETWEEN…AND…6NOT7AND8OR
排序
默认生升序asc降序desc一级排序相同则会自动跳到二级排序
单行函数
SQL中不同类型的函数
单行函数
字符 大小写控制函数
--小写和大写
SELECT LOWER(hand-china.COM) AS LOWER小写, UPPER(hand-china.COM) AS UPPER大写, INITCAP(hand-china.COM) AS INITCAP大驼峰
FROM dual字符控制函数
函数结果CONCAT(‘Hello’, ‘World’)HelloWorldSUBSTR(‘HelloWorld’, 2, 4)elloLENGTH(‘HelloWorld’)10INSTR(‘HelloWorld’, ‘W’)6LPAD(salary, 10, ‘*’)******7500RPAD(salary, 10, ‘*’)7500******TRIM(‘H’ FROM ‘HelloHWorldH’)elloHWOrldREPLACE(‘abcdab’, ‘b’, ‘m’)amcdam
数值 --日期或者数字进行截断
SELECT TRUNC(435.45, 1), TRUNC(435.45), TRUNC(435.45, -1)
435.4 435 430日期 --两个日期之间相差的天数
SELECT title, level, SYSDATE-create_time AS WorkedDays FROM cxf_44569;
--两个日期之间相差的月数
SELECT title, level, SYSDATE-create_time)/30 AS WorkedDays1, MONTHS_BETWEEN(SYSDATE, create_time) AS WOrkedDays2 FROM cxf_44569;
--指定月数加上月数指定日期下一个星期对应的日期
SELECT ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, -3), NEXT_DAY(SYSDATE, 星期六) FROM dual;
--每个月的倒数第二天创建的题目信息
SELECT * FROM cxf_44569 WHERE create_time LAST_DAY(create_time)-1;
--日期的四舍五入和截断
SELECT SYSDATE, ROUND(SYSDATE, MONTH), ROUND(SYSDATE, MM), TRUNC(SYSDATE, HH) FROM dual;转换 TO_CHAR 和 TO_DATE
--日期转字符串
SELECT title, TO_CHAR(create_time, yyyy年mm月dd日)
FROM cxf_44569
WHERE 2023年08月30日 ! TO_CHAR(create_time, yyyy年mm月dd日)
--字符串转日期
SELECT title, TO_CHAR(create_time, yyyy年mm月dd日)
FROM cxf_44569
WHERE create_time ! TO_DATE(2023年08月31日, yyyy年mm月dd日)TO_CHAR 和 TO_NUMBER
--按照数据本地货币格式查询
SELECT TO_NUMBER(001,234,567.89, L000,000,999.99)
FROM dual货币类型符号需要相对应 通用
适用于任何数据类型同时也适用于空值
NVL(expr1, expr2)expr1为NULL则返回expr2NVL2(expr1, expr2, expr3)expr1不为NULL则返回expr2否则返回expr3NULLIF(expr1, expr2)如果expr1和expr2相等则返回NULL否则返回expr1COALESCE(expr1, expr2, …exprN)如果第一个表达式为NULL则返回第二个如果第二个还为NULL则返回第三个
--NUL查询奖金提成后的工资
SELECT employee_id, last_name, salary*12(1NVL(commission_pct, 0)) FROM employees使用条件表达式 --CASE xxx WHEN xxx END
SELECt employee_id, last_name, department_id, CASE department_id WHEN 10 THEN salary*1.1,WHEN 20 THEN salary*1.2,ELSE THEN salary*1.3 END AS New_Salary
FROM employee
WHERE department_id IN(10, 20, 30)--DECODE()
SELECt employee_id, last_name, department_id, DECODE(department_id, 10, salary*1.1,20, salary*1.2,30, salary*1.3) AS NewSalary
FROM employees
WHERE department_id IN(10, 20, 30)嵌套查询
单行函数可以嵌套查询嵌套函数的执行顺序是先内后外
多表查询
等值连接
两个表中的数据有有交集
--查询员工信息部门信息地址信息
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id d.department_id
AND d.location_id l.location_id非等值连接
两个表中的数据互不交集
--查询运功信息和工作信息【两个表没有关联关系】
SELECT e.employee_id, e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal
AND j.highest_sal左外连接
--查询员工信息和部门信息
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id d.department_id()
--查询员工信息和部门信息
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
WHERE e.department_id d.department_id满外连接
-- 有个员工没有部门ID但满外连接也会把该员显示出来
SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL JOIN departments d
WHERE e.department_id d.department_id自连接
--查询员工及员工老板的信息
SELECT emp.last_name, manaer.last_name, manager.salary, manager.email
FROM employee emp, employee manager
WHERE emp.manager_id manager.employee_id
AND TO_CHAR(emp.last_name) chen聚合分组函数
SUMAVGMINMAX在统计字段的时候自动会舍去NULL值
--查询各个部门不同岗位的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id
--查询各部门平均工资大于6k的部门和部门平均工资
SELECT department_id, avg(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) 6000如果要使用 WHERE 的话需要紧跟 FROM 之后包含在 SELECT 中的一定要出现在 GROUP BY 之后出现在GROUP BY 之后的不一定需要出现在 SELECT 中WHERE 中不能使用聚合函数HAVING 中可以使用聚合函数 子查询
--查询job_id与141号员工相同但salary比143号员工多的员工信息
SELECT last_name, employee_id, salary
FROM employeee
WHERE job_id (SELECT job_id FROM employee WHERE employee_id 141) AND salary (SELECT salary FROM employee FROM employee WHERE employee_id 143)--查询部门中工资比本部门平均工资高的员工号姓名和工资
SELECT employee_id, last_name, salary
FROM employee e1
WHERE salary (SELECT AVG(salary)FROM employee e2WHERE e1.employee_id e2.employee_idGROUP BY department_id)创建和管理表 --查询用户定义的表
SELECT * FROM user_tables
SELECT table_name FROM user_tables
--查询用户定义的数据对象
SELECT * FROM user_objects
SELECT DISTINCT object_type FROM user_objects
--查询用户定义的表视图同义词和序列
SELECT * FROM user_catalog创建表
--白手起家
CREATE TABLE emp1 ( id NUMBER ( 10 ), name VARCHAR2 ( 25 ), hire_date TIMESTAMP, salary NUMBER ( 10, 2 ) )
--复制表结构数据
CREATE TABLE emp2 AS SELECT
id,
name AS n a m e,
hire_date,
salary
FROMemp1;
--复制表结构
CREATE TABLE emp3 AS SELECT
id,
name AS name,
hire_date,
salary
FROMemp1
WHERE1 2;--白手起家
CREATE TABLE emp1 ( id NUMBER ( 10 ), name VARCHAR2 ( 25 ), hire_date TIMESTAMP, salary NUMBER ( 10, 2 ) )
--复制表结构数据
CREATE TABLE emp2 AS SELECT
id,
name AS n a m e,
hire_date,
salary
FROMemp1;
--复制表结构
CREATE TABLE emp3 AS SELECT
id,
name AS name,
hire_date,
salary
FROMemp1
WHERE1 2;
--新增一字段
ALTER TABLE emp1 ADD ( email VARCHAR ( 20 ) DEFAULT 无 );
--修改一个字段
ALTER TABLE emp1 MODIFY ( id NUMBER ( 15 ) );
--删除一个列
ALTER TABLE emp1 DROP email;
--重命名一个列
ALTER TABLE emp1 RENAME COLUMN sal TO s a l;
--删除一个表
DROP TABLE emp3;
--清空表数据但表结构还在【增删改可以回滚DELETE可以回滚】
TRUNCATE TABLE emp2;
DELETE FROM emp2;
--修改表名
RENAME emp2 TO employees2;数据处理
--插入一条数据
INSERT INTO emp1 ( id, name, hire_date, sal )
VALUES( 10001, 张三, SYSDATE, 10000 );
--从其它地方拷贝数据进行复制【不用 VALUES但需要子查询中的值列表与 INSERT 子句中的列表对应】
INSERT INTO emp1 ( id, name, hire_date, sal ) SELECT
id 1,
name,
hire_date,
sal
FROMemp1;
--更改数据修改114号员工的工作和工资和205号员工相等
UPDATE emp1
SET job_id ( SELECT job_id FROM emp1 WHERE employee_id 205 )
AND salary ( SELECT salary FROM emp1 WHERE employee_id 205 )
WHEREemployee_id 114;
--删除部门名称含有Public字符的部门ID
DELETE
FROMemp1
WHEREdepartment_id ( SELECT department_id FROM emp1 WHERE department_name LIKE %Public% );
--控制事务
DELETE FROM emp1 WHERE id 1;
SAVEPOINT A;
DELETE FROM emp1 WHERE id2;
SAVEPOINT B;
ROLLBACK TO SAVEPOINT A;
ROLLBACK;约束 NOT NULL
CREATE TABLE emp
(id NUMBER ( 10 ) CONSTRAINT emp_id_pk PRIMARY KEY,name VARCHAR2 ( 20 ) CONSTRAINT emp_name_nn NOT NULL,salary NUMBER ( 10, 2 ),email VARCHAR2(20),CONSTRAINT emp_email_uk UNIQUE(email)
);列约束和表约束
CREATE TABLE emp2
(--列级约束id NUMBER ( 10 ) CONSTRAINT emp2_id_pk FPRIMARY KEY,name VARCHAR2 ( 20 ) CONSTRAINT emp2_name_nn NOT NULL,salary NUMBER ( 10, 2 ) CONSTRAINT emp2_salary_ CHECK ( salary 1500 AND salary 30000 ) NOT NULL,email VARCHAR ( 20 ),deparment_id NUMBER ( 10 )--表级约束CONSTRAINT emp2_email_uk UNIQUE ( email )--唯一约束--主键约束-- CONSTRAINT emp2_id_pk FPRIMARY KEY(id),--外键约束CONSTRAINT emp2_dept_id_fk FOREIGN KEY ( department_id ) REFERENCES departments ( department_id ) ON DELETE CASCADE --级联删除-- CONSTRAINT emp2_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ON DELETE SET NULL--级联置空
);对于 UNIQUE 的NOT NULL约束则允许插入的数据为NULL不会违反唯一值约束对于 NOT NULL 只能添加在列级上 在创建表级外键约束的时候需要添加上 FOREIGN KEY 关键字
--列级约束
department NUMBER(10) CONSTRAINT emp2_dpt_id_fk REFERENCES dpt2(department_id)
--表级约束
CONSTRAINT FOREIGN KEY emp2_dpt_id_fk REFERENCES dpt2(department_id)添加或删除约束
--NULL约束只能用MODIFY修改
ALTER TABLE emp2 MODIFY(sal NUMBER(10,2, NOT NULL));
--删除一个约束
ALTER TABLE emp2 DROP CONSTRAINT emp2_name_uk;
--添加一个约束【添加之前需要把符合当前数据】
ALTER TABLE emp2 ADD CONSTRAINT emp2_name_uk UNIQUE(name);
--无效化一个约束
ALTER TABLE emp2 DISABLE CONSTRAINT emp2_nmae_uk;
--有效化一个约束
ALTER TABLE emp2 ENABLE CONSTRAINT emp2_name_uk;
--查询约束
SELECT constraint_name, constraint_type, search_condition FROM user_constraints WHERE table_name emp2;
--查询定义约束的列
SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name emp2;视图
视图基础 --创建数据库
CREATE TABLE emp
( id NUMBER ( 10 ) CONSTRAINT emp_id_pk PRIMARY KEY,name VARCHAR2 ( 20 ) CONSTRAINT emp_name_nn NOT NULL,salary NUMBER ( 10, 2 ),email VARCHAR2(30),CONSTRAINT emp_email_uk UNIQUE(email)
);
--插入数据
INSERT INTO emp ( id, name, salary, email ) VALUES ( 1, 张三, 6000, zhangsanqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 2, 李四, 7500, lisiqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 3, 王五, 9000, wangwuqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 4, 老六, 4500, laoliuqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 5, 小七, 5500, xiaoqiqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 6, 甲, 3500, jiaqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 7, 乙, 6500, yiqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 8, 丙, 9500, bingqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 9, 丁, 3500, dingqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 10, 戊, 1500, wuqq.com );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 11, 戎, 2500, rongqq.com );
--创建视图
CREATE VIEW emp_view AS
SELECT id, name, salary, email FROM emp;
--查询视图
SELECT * FROM emp_view;
--查询视图
SELECT * FROM emp_view;
--查询表【发现此时视图已经修改了表数据】
SELECT * FROM emp;
--还可以给视图字段更换别名
CREATE VIEW emp_view1 AS
SELECT id AS ID, name AS 名称, salary AS 薪资, email AS 邮箱 FROM emp;
--修改视图
CREATE OR REPLACE VIEW emp_view1 AS
SELECT id AS ID, name AS 名称, salary AS 薪资, email AS 邮箱 FROM emp;
--创建只读视图【此时就无法修改视图】
CREATE OR REPLACE VIEW emp_view1 AS SELECTid AS ID,name AS 名称,salary AS 薪资,email AS 邮箱
FROMemp WITH READ ONLY;
--删除视图
DROP VIEW emp_view1;创建视图过程中使用了分组函数就是复杂视图复杂视图的 DML 无法进行操作 Top-N分析
SELECT ROWNUM, name, salary FROM
(SELECT name, salary FROM emp ORDER BY salary DESC)
WHERE ROWNUM 3;对 ROWNUM 只能使用 “” 或 “”而用 “”“”“” 都将不能返回任何数据
--查找4~6之间的员工薪资
SELECT ROWNUM, name, salary FROM
(SELECT name, salary FROM emp ORDER BY salary DESC)
WHERE ROWNUM 6 AND ROWNUM 4;--查找4~6之间的员工薪资
SELECT rn, name, salary
FROM( SELECT ROWNUM AS rn, name, salaryFROM(SELECT name, salary FROM emp ORDER BY salary DESC))
WHERE rn 4 AND rn 6;其它数据对象 序列 --创建序列
CREATE SEQUENCE empseq
INCREMENT BY 1 --每次增长1
START WITH 1 --从1开始增长
MAXVALUE 100 --提供的最大值NOMAXVALUE无限制
CYCLE --需要循环
CACHE 50; --不需要缓存登录
--使用之前一定要先获取下一个值【迭代器到11为止】
SELECT empseq.nextval FROM dual;
--获取当前值
SELECT empseq.currval FROM dual;
--使用序列
INSERT INTO emp(id, name, salary) VALUES(empseq.nextval, 戌, 1500, xuqq.com);
--修改序列的增量最大值最小值循环选项或是否装入内存
ALTER SEQUENCE empseq
INCREMENT BY 2
MAXVALUE 100
NOCYCLE
NOCACHE
--查看当前有哪些序列
SELECt sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences;
--删除序列
DROP SEQUENCE empseq;修改序列只有后续插入的值才会改变改变序列的初始值只能通过删除序列之后重建序列的方法实现将序列值装入内存可以提高访问效率序列在下列情况下会出现 裂缝 回滚系统异常多个表同时使用同一序列 如果不将序列的值装入内存(NOCACHE)可使用表 USER_SEQUENCES 查看序列当前的有效值
索引 --删除序列
CREATE INDEX emp_id_index ON emp(id);什么时候创建索引
列中数据值分布范围很广列经常在 WHERE 子句或连接条件中出现表经常被访问而且数据量很大访问的数据大概占据数据总量的2%~4%
什么时候不要创建索引
表很小表经常更新查询的数据大于2%~4%列不经常作为连接条件或出现在WHERE子句中 索引在查询的时候速度快了但是插入速度也就慢了 同义词
使用同义词访问相同的对象
方便访问其它用户对象缩短对象名字长度
--添加同义词
--添加同义词
CREATE SYNONYM e FOR emp;
SELECT * FROM e;
--删除同义词
DROP SYNONYM e;控制用户权限及练习
授权
数据库安全性 系统安全性数据安全性 系统权限对于数据库的权限对象权限操作数据库对象的权限 以下创建用户和赋予权限都是利用 SYSTEM 用户创建 cvter 用户 --创建用户【用户是cvter并不是C##cvter因为oracle官网说明了创建用户之前需要加C##或者c##】
CREATE USER C##cvter IDENTIFIED BY qwe123
--修改密码
ALTER USER C##cvter IDENTIFIED BY ewq321;创建用户之后应该赋予权限
创建会话CREATE SESSION创建表CREATE TABLE创建视图CREATE VIEW创建序列CREATE SEQUENCE创建过程CREATE PROCEDURE
--赋予权限
GRANT CREATE SESSION TO C##cvter;--远程登陆数据库权限
GRANT CREATE TABLE TO C##cvter;创建用户表空间 用户拥有 CREATE TABLE 权限之外还需要发呢配相应的表空间才可以开辟存储空间用于创建表
--创建用户表空间
ALTER USER C##cvter QUOTA UNLIMITED ON users;--无限制表空间大小
ALTER USER C##cvter QUOTA 5M ON users;--限制表空间大小为5MB创建角色并赋予权限
--创建角色
CREATE ROLE C##my_role;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO C##my_role;
CREATE USER C##cvter02 IDENTIFIED BY qwe123;
GRANT C##my_role TO C##cvter02;对象授权 还需要对新建的用户开放 system.emp 的 select和update 权限而其它权限无法使用
--对象权限
GRANT select, update ON system.emp TO C##cvter;
--其它权限无法使用
DROP TABLE system.emp;WITH GRANT OPTION 和 PUBLIC 关键字
--WITH GRANT OPTION 使用户同样具有分配权限的权利
GRANT select, update ON system.emp TO C##cvter WITH GRANT OPTION;
--向数据库中所有用户分配权限
GRANT select ON system.emp TO PUBLIC;收回对象权限 --查询用户拥有的权限
SELECT * FROM user_tab_privs_recd;
--收回cvter的select和update权限
REVOKE select, update ON system.emp FROM C##cvter;SET集合运算符 查询的数据列数和数据类型应该精准对应 UNION ALL 会返回全部的集合。而UNION会返回去重后的集合
高级子查询
多列子查询
--查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id
SELECTemployee_id,manager_id,department_id
FROMemployees
WHERE(manager_id, department_id) IN ( SELECT manager_id, department_id FROM employees WHERE employee_id IN ( 141, 174 ) ) AND employee_id NOT IN ( 141, 174 );在FROM 子句中使用子查询
--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
SELECT last_name, department_id, salary, (SELECT AVG(salary) FROM employees e3 WHERE e1.department_id e3.department_id GROUP BY department_id)
FROM employees e1
WHERE salary (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id e2.department_id GROUP BY department_id);SEELCT last_name, e1.department_id, e2.avg_sal ) FROM employees e1, (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) e2 WHERE e1.department_id e2.department_id;在 SQL 中使用单列子查询
--显式员工的employee_id,last_name和location。其中若员工department_id与location_id为1800的department_id相同则location为’Canada’,其余则为’USA’
SELECT employee_id, last_name,( CASE department_id WHEN ( SELECT department_id FROM departments WHERE location_id 1800 ) THEN Canada ELSE USA END ) location
FROMemployees;--查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name FROM departments d WHERE e.department_id d.department_id);相关子查询 --若employees表中employee_id与job_history表中employee_id相同的数目不小于2输出这些相同id的员工的employee_id,last_name和其job_id
SELECT employee_id, last_name, job_id
FROM employees e1
WHERE 2 (SELECT COUNT(*) FROM job_history WHERE employee_id e1.employee_id);使用 EXISTS 和 NOT EXISTS 操作符 EXISTS
--查询公司管理者的employee_id, last_name, job_id, department_id信息
--方案一
SELECT e1.employee_id, e1.last_name, e1.last_name, e1.job_id, e1.department_id
FROM employees e1
WHERE e1.employee_id IN (SELECT manager_id FROM employees e2 WHERE e1.employee_id e2.manager_id );
--方案二
SELECT DISTINCT e1.employee_id, e1.last_name, e1.last_name, e1.job_id, e1.department_id
FROM employees e1, employees e1
WHERE e1.employee_id e2.manager_id;
--方案三
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS (SELECT A FROM employees e2 WHERE e1.employee_id e2.manager_id);NO EXISTS
--查询departments表中不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE (SELECT CFROM employeesWHERE department_id d.department_id);--差集
SELECT department_id, department_name
FROM departments d
MINUS
SELECT department_id, department_name
FROM employees使用子查询删除和更新数据
--更新
UPDATE emp
SET department_name ( SELECT department_name FROM departments WHERE emp.department_id department_id );
--删除
DELETE
FROMemployees e
WHEREemployee_id IN ( SELECT employee_id FROM emp_history WHERE employee_id e.employee_id );使用 WITH 子句 --查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH dept_sumsal AS ( SELECT department_name, SUM( salary ) sum_sal1, sumsal, FROM departments d, employees e WHERE d.department_id e.department_id GROUP BY department_name ),
dept_avgsal AS (SELECT SUM(sum_sal)/COUNT(*) avg_sum_sal2 FROM dept_sumsal)
SELECT * FROM dept_sumal WHERE sum_sal1 (SELECT avg_sum_sal2 FROM dept_avgsal) ORDER BY department_name;
