select orderid,goodsname,quantity,ordersum,orderdate,firstname,lastname
from orders
inner join customers
on customers.customerid=orders.customerid
-----------查询orders表中按照customerID将货品订单与订货人对应显示。
select student_id,student_name,class_name
from student
right outer join class_info
on student.class_id=class_info.class_id
-----------以class_info表为主显示所有学生所对应的班级，显示结果以主表的所有内容为准。
select student_id,student_name,class_name
from student
left outer join class_info
on student.class_id=class_info.class_id
-------以student表为主显示所有学生所对应的班级，显示结果以主表的所有内容为准。
select student.student_id,student_name,coursename,grade
from student_course
right outer join student
on student.student_id=student_course.student_id
left outer join course
on student_course.course_id=course.courseid
-----------查询所有学生所对应的科目的考试成绩，其中student_course表中不符和条件的不显示以及course表中不符合条件的也不显示。其中right outer join 所对应的表中的记录会全部显示。成为主表。Left outer join 表中只显示符合条件的记录。
select student.student_id,student_name,class_name
from student
full outer join class_info
on student.class_id=class_info.class_id
----------显示所有学生所对应的班级，full outer join 会显示两张表中的所有记录。
select student_id,student_name,class_name
from student
inner join class_info
on student.class_id=class_info.class_id
--------显示学生所对应的班级以及学生姓名和学生ID. inner join 只显示两张表中互相符合条件的记录
select * from student where age>(select avg(age) from student)
---------查询平均年龄大于select avg(age) from student的学生信息。
select student_id from student_course where grade<(select avg(grade) from student_course)
-------按照学生编号查询平均分数小于select avg(grade) from student_course分数的学生编号。
select * from customers where customerid=(select customerid from orders where orderid=3)
----------查看orderid等于三的订货编号人的详细信息。
select * from student where student_id not in (select student_id from student_course)
--------------按照student_id查询没有参加考试的学生的详细信息。
if exists(select * from sys.sysobjects where xtype='U' and name='student')
drop table student
go
create table student(stu_id int identity primary key,stu_name varchar(10) not null,age int)
---------判断如果所有数据库中有student表，则删除此student表，然后重新创建student表。
本文转自 沐小七 51CTO博客，原文链接:http://blog.51cto.com/3088522/566622
