select_list::=t_alias Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.以上这段不知道怎么翻译,或许需要其他方面的技术基础 其实是整个select_list语法图掌握的很肤浅
网上关于子查询的 子查询: 使用子查询的原则 1.一个子查询必须放在圆括号中。 2.将子查询放在比较条件的右边以增加可读性。 子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句, 并且如果指定了它就必须放在主 SELECT 语句的最后。 ORDER BY 子句可以使用,并且在进行 Top-N 分析时是必须的。 3.在子查询中可以使用两种比较条件:单行运算符和多行运算符。 子查询的类型 单行子查询:从内 SELECT 语句只返回一行的查询 多行子查询:从内 SELECT 语句返回多行的查询 单行子查询 单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。幻灯片中列出了单行操作符。
例 显示那些 job ID 与雇员 141 相同的雇员。 SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141); SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143); 显示 job ID 与雇员 141 相同,并且薪水 高于雇员 143 的那些雇员。 注:外和内查询可以从不同的表中取得数据。 SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); 求所有人谁的工资最小。 SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50); 求每个部门的最小工资,但是要高于50号部门的工资。 SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id); 问题出现在:单行子查询返回了多个查询值; 应改为: SELECT employee_id, last_name FROM employees WHERE salary in (SELECT MIN(salary) FROM employees GROUP BY department_id);SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas'); 如果子查询返回的是零值,不会对主程序造成影响; 如果子查询返回的是空值,那么会影响主程序的返回值; SELECT employee_id, last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees)SELECT employee_id, last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL) 放在select下的子查询的返回值必须是一个具体值, from后面也可以加子查询; having后面也可以加子查询; order by后面也可以;多列子查询适应于:成对比较;非成对比较。SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (178,174)) AND employee_id NOT IN (178,174);输出:176 149 80只有要查询的东西和你子查询返回的东西一一对应上了,你的查询才能成功。 如果有一个 对应不上那么你的查询不会成功。非成对的子查询: SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141)) AND employee_id NOT IN(174,141); 输出:144 124 50 143 124 50 142 124 50 176 149 80 上面两个程序就是成对子查询和非成对子查询两者之间的区别。 如果我想去显示员工信息,要求:员工的工资高于本部门的平均工资。 SELECT a.last_name, a.salary, a.department_id FROM employees a WHERE a.salary > (SELECT AVG(salary) FROM employees b WHERE b.department_id = a.department_id);in line view(内联视图)SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg;
楼上基本可以说明这一段了,“This alias is required if the select list references any object type attributes or object type methods”,这个还没有例子
先定义,才能引用 先引用,才能使用 不能定义完了就使用 例如下面语句是错误的: with emp as (select first_name from employees) select emp.first_name,t.last_name from employees t 应该是: with emp as (select first_name from employees) select emp.first_name,t.last_name from employees t,emp
JOIN The JOIN keyword explicitly states that a join is being performed. You can use this syntax to replace the comma-delimited table expressions used in WHERE clause joins with FROM clause join syntax.谁知道红色字体的内容神马意思?
joins穷举: 1(inner) join...on... 条件不限,同名列可重复出现 2(inner) join...using... 各个using表达式中同名列的等值条件,using表达式中的同名列不可重复出现 3cross join 笛卡尔积 4natural (inner) join 所有同名列的等值条件,所有同名列都不可重复出现,相当于2中的using表达式中列出所有同名列 5full (outer) join on 1的基础上,累加左、右侧表中满足条件的记录 6full (outer) join using 2的基础上,累加左、右侧表中满足条件的记录 7left (outer) join on 1的基础上,累加左侧表中满足条件的记录 8left (outer) join using 2的基础上,累加左侧表中满足条件的记录 9right (outer) join on 1的基础上,累加右侧表中满足条件的记录 10right (outer) join using 2的基础上,累加右侧表中满足条件的记录 11natural join 这个跟4的关系,你懂得 12natural full (outer) join 4的基础上,累加左、右侧表中满足等值条件的记录 13natural left (outer) join 4的基础上,累加左侧表中满足等值条件的记录 14natural right(outer) join 4的基础上,累加右侧表中满足等值条件的记录
Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.以上这段不知道怎么翻译,或许需要其他方面的技术基础
其实是整个select_list语法图掌握的很肤浅
子查询:
使用子查询的原则
1.一个子查询必须放在圆括号中。
2.将子查询放在比较条件的右边以增加可读性。
子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句,
并且如果指定了它就必须放在主 SELECT 语句的最后。
ORDER BY 子句可以使用,并且在进行 Top-N 分析时是必须的。
3.在子查询中可以使用两种比较条件:单行运算符和多行运算符。 子查询的类型
单行子查询:从内 SELECT 语句只返回一行的查询
多行子查询:从内 SELECT 语句返回多行的查询 单行子查询
单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。幻灯片中列出了单行操作符。
例
显示那些 job ID 与雇员 141 相同的雇员。
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141); SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);
显示 job ID 与雇员 141 相同,并且薪水 高于雇员 143 的那些雇员。 注:外和内查询可以从不同的表中取得数据。 SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);
求所有人谁的工资最小。
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
求每个部门的最小工资,但是要高于50号部门的工资。
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
问题出现在:单行子查询返回了多个查询值;
应改为:
SELECT employee_id, last_name
FROM employees
WHERE salary in
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
如果子查询返回的是零值,不会对主程序造成影响;
如果子查询返回的是空值,那么会影响主程序的返回值;
SELECT employee_id,
last_name
FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees)SELECT employee_id,
last_name
FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
放在select下的子查询的返回值必须是一个具体值,
from后面也可以加子查询;
having后面也可以加子查询;
order by后面也可以;多列子查询适应于:成对比较;非成对比较。SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (178,174))
AND employee_id NOT IN (178,174);输出:176 149 80只有要查询的东西和你子查询返回的东西一一对应上了,你的查询才能成功。
如果有一个 对应不上那么你的查询不会成功。非成对的子查询:
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN (SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
输出:144 124 50
143 124 50
142 124 50
176 149 80
上面两个程序就是成对子查询和非成对子查询两者之间的区别。
如果我想去显示员工信息,要求:员工的工资高于本部门的平均工资。
SELECT a.last_name,
a.salary,
a.department_id
FROM employees a
WHERE a.salary >
(SELECT AVG(salary)
FROM employees b
WHERE b.department_id = a.department_id);in line view(内联视图)SELECT a.last_name, a.salary,
a.department_id, b.salavg
FROM employees a, (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
subquery_factoring_clause
query_name引用处:
query_table_expression
query_name使用处:
select_list
先引用,才能使用
不能定义完了就使用
例如下面语句是错误的:
with emp as (select first_name from employees) select emp.first_name,t.last_name from employees t
应该是:
with emp as (select first_name from employees) select emp.first_name,t.last_name from employees t,emp
The JOIN keyword explicitly states that a join is being performed. You can use this syntax to replace the comma-delimited table expressions used in WHERE clause joins with FROM clause join syntax.谁知道红色字体的内容神马意思?
1(inner) join...on...
条件不限,同名列可重复出现
2(inner) join...using...
各个using表达式中同名列的等值条件,using表达式中的同名列不可重复出现
3cross join
笛卡尔积
4natural (inner) join
所有同名列的等值条件,所有同名列都不可重复出现,相当于2中的using表达式中列出所有同名列
5full (outer) join on
1的基础上,累加左、右侧表中满足条件的记录
6full (outer) join using
2的基础上,累加左、右侧表中满足条件的记录
7left (outer) join on
1的基础上,累加左侧表中满足条件的记录
8left (outer) join using
2的基础上,累加左侧表中满足条件的记录
9right (outer) join on
1的基础上,累加右侧表中满足条件的记录
10right (outer) join using
2的基础上,累加右侧表中满足条件的记录
11natural join
这个跟4的关系,你懂得
12natural full (outer) join
4的基础上,累加左、右侧表中满足等值条件的记录
13natural left (outer) join
4的基础上,累加左侧表中满足等值条件的记录
14natural right(outer) join
4的基础上,累加右侧表中满足等值条件的记录