目录
ORACLE连接
以USER_1和USER_2为例子
内连接等效于等值连接
1.内连接和等值连接的效果是一样的,内连接oracle在处理的时候不会出现笛卡尔积现象,实际开发中建议选择内连接。
内连接
SELECT * FROM USER_1 INNER JOIN USER_2 ON USER_1.ID=USER_2.ID;-----内连接找出的是ID好相等的两张表的所有记录,必须加ON
SELECT * FROM USER_1 JOIN USER_2 ON USER_1.ID=USER_2.ID;
SELECT * FROM USER_1 INNER JOIN USER_2 USING(ID);----内连接加USING会将条件列去重
SELECT * FROM USER_1 INNER JOIN USER_2 USING(ID) WHERE USER_2.NIDE IS NOT NULL;
SELECT * FROM USER_1 INNER JOIN USER_2 ON USER_1.ID=USER_2.ID WHERE USER_1.ID='2'AND USER_2.NIDE IS NOT NULL;
SELECT * FROM USER_1 JOIN USER_2 ON USER_1.ID=USER_2.ID WHERE USER_1.ID='2';---------------加了条件的内连接
等值连接
SELECT * FROM USER_1,USER_2 WHERE USER_1.ID=USER_2.ID;
SELECT * FROM USER_1 JOIN USER_2 ON USER_1.ID=USER_2.ID;
SELECT * FROM USER_1 JOIN USER_2 USING(ID);------等值连接加USING去重
外连接
外连接分为左外连接和右外连接,全连接
左连接也叫左外连接,右连接也叫右外连接。左连接显示左边表的全部数据,右连接显示右边表的全部数据。一下演示作外连接(LEFT JOIN/LEFT OUTER JOIN)。
左外连接
SELECT * FROM USER_1,USER_2 WHERE USER_1.ID=USER_2.ID(+);
SELECT * FROM USER_1 LEFT JOIN USER_2 ON USER_1.ID=USER_2.ID;
SELECT * FROM USER_1 LEFT OUTER JOIN USER_2 ON USER_1.ID=USER_2.ID;
SELECT * FROM USER_1 left OUTER JOIN USER_2 USING(ID);------外连接加USING去重
SELECT * FROM USER_1 left JOIN USER_2 USING(ID);
SELECT * FROM USER_1 LEFT OUTER JOIN USER_2 ON USER_1.ID=USER_2.ID WHERE USER_2.ID IS NOT NULL;--------加了条件的外连接
全连接
select * from user_1 FULL OUTER JOIN USER_2 ON user_1.ID=user_2.ID;------全外连接是显示A表和B表的所有数据
select * from user_1 FULL OUTER JOIN USER_2 ON user_1.ID=user_2.ID WHERE USER_2.ID IS NOT NULL;-------------加了限制条件的全外连接
SELECT * FROM USER_1 FULL JOIN USER_2 USING(ID) ;---------------全外连接加USING后去重
交叉连接
交叉连接相当于笛卡尔积
SELECT * FROM USER_1,USER_2 ;
SELECT * FROM USER_1 CROSS JOIN USER_2;
自然连接
自然连接不用指定列,也不能使用on子句,自然连接会自动比较两张表的相同列,然后给列去重。
自然连接不能用USING
SELECT * FROM USER_1 NATURAL JOIN USER_2 ;
ORACLE子查询
子查询是一次性视图,在select语句执行完之后就会消失。
非关联子查询
SQL运行顺序:先运行子查询,再运行子查询外面的语句。子查询和主查询互不干扰。
单行子查询:
SELECT * FROM ISN WHERE ISN IN(SELECT ISN FROM MO_D WHERE MO='24099937');----多行子查询,子查询ISN返回多个值
SELECT * FROM ISN WHERE (ISN,MODEL) IN(SELECT ISN,MODEL FROM MO_D WHERE MO='24099937');----多行多列子查询
关联子查询
查找每个课程内大于对应课程平均成绩的学生。
SELECT 学号,课程号,成绩
FROM score AS s1
WHERE 成绩>(SELECT AVG(成绩)
FROM score AS s2
WHERE s1.课程号=s2.课程号);
关联子查询语句的执行顺序:
1、先执行外层查询,查找出score表里所有的学号、课程号、成绩,结果如下:
SELECT 学号,课程号,成绩
FROM score AS s1
2、上图中的第一条结果进入子查询,因为子查询里通过where语句关联了两个表,子查询得出课程号0001的平均成绩80。
(SELECT AVG(成绩)
FROM score AS s2
WHERE 课程号=0001)
3、将子查询得出的结果80返回外层查询where语句中进行比较,得出执行结果。
可以看出第一条记录不满足查询条件,查询结果中将不显示此条记录。
SELECT 学号,课程号,成绩
FROM score AS s1
WHERE 学号=0001 AND 课程号=001 AND 成绩>80;
4、score表中后面的每行数据进行同样的执行顺序,得出最终结果。
标量子查询
什么时候用标量子查询:当我们需要单一值的时候。
注意事项:不能返回多行结果。
标量子查询必须且只能返回一行一列的结果。返回单一的值。所以可以和比较运算符一起使用。
标量子查询的运用
SELECT 学号,成绩
FROM score
WHERE 成绩>(SELECT AVG(成绩)
FROM score);