sql1992

sql分类

1.笛卡尔积（表乘表）

2.等值连接表的连接条件使用“=”

3.非等值连接表的连接条件使用“>、>=、 <、<=、!=、any等”

4.自连接自己连接自己

5.外连接

1.左外连接，“(+)”在等号右边

2.右外连接，“(+)”在等号左边

3.“(+)”在哪一边的列，该表就补充null

sql1999

sql分类

1.cross join 交叉连接（笛卡尔积），不需要on关键字

2.natural join 自然连接（找两个表中相同的列，进行等值匹配），不需要on关键字

3.inner join 内连接

1)必须有on关键字，on表示连接条件

2)inner关键字可以省略

4.outer join 外连接，outer关键字可以省略

1) left outer join

2) right outer join

3) full outer join

行转列（连表查询的应用）

create table STUDENT_SCORE ( name VARCHAR2(20), subject VARCHAR2(20), score NUMBER(4,1) ); insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0); insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0); insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0); insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0); insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0); insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0); insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0); insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0); insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

select name "姓名", max(decode(subject, '语文', score)) "语文", max(decode(subject, '数学', score)) "数学", max(decode(subject, '英语', score)) "英语" from (select name, subject, (case when score < 60 then '不及格' when score < 80 then '及格' else '优秀' end) score from STUDENT_SCORE) group by name