sql学习

Sql 连接

1、 数据库脚本

Table.sql data.sql

2、 各种连接

a)         内连接

                         i.              笛卡尔连接

select * from student_info , student_course

查询结果为两个表的笛卡尔积。

                       ii.              等值连接

select * from student_info , student_course where student_info . student_id = student_course . student_id

查询结果为每个学生的选课记录。

                      iii.              不等值连接

b)        外连接

                         i.              左外连接

 

 

 

select * from class_info , student_info where class_info . CLASS_ID = student_info . CLASS_ID (+)

查询结果为所有的班级，以及班级下的学生，包括没有学生的班级。

                       ii.              右外连接

select * from class_info , student_info where class_info . CLASS_ID (+) = student_info . CLASS_ID

查询出所有的学生，包括不属于任何班级得学生。

 

Exist

 

select * from student_info

where exists (

select * from student_course

where student_info.STUDENT_ID = student_course.STUDENT_ID and student_course.COURSE_ID = 1

)

select a.student_id,a.student_name,a.class_id

from student_info a,student_course

where a.student_id = student_course.student_id and student_course.course_id = 1

select * from student_info

where student_id in (select student_course.student_id from student_course where student_course.COURSE_ID = 1)

Union

完成或的功能

SELECT student_id FROM student_course WHERE course_id = 1 UNION （ all ） SELECT student_id FROM student_info

Intersect

完成与的功能

SELECT student_id FROM student_course WHERE course_id = 1 INTERSECT   SELECT student_id FROM student_course WHERE course_id = 2

Minus

完成减的功能

SELECT student_id FROM student_course WHERE course_id = 1 MINUS   SELECT student_id FROM student_course WHERE course_id = 2