SELECT语句
1.      ftp上的student.mdfstudent_log.log2个数据库文件下载到C盘根目录下,打开SQLSERVER Manager Studio,附加数据库student
2.      数据库student上完成以下操作:
(1) 查找“stu_info”表中专业为“计算机网络”的同学,并把查找结果保存在表JSJWL中。
命令:select *
INTO JSJWL FROM stu_info
where majOr='计算机网络'
(2) 把“stu_grade”表中的成绩每人+2,并将结果保存到新表“成绩”中.
命令:select stu_id,course_id,grade+2 as grade
into 成绩from stu_grade
(3) 查找“stu_info”表中的所有的专业,去掉重复记录。
命令:select distinct major
from stu_info
 
(4)查询stu_info表中,入学成绩在550600范围内的学生信息
命令:select *
from stu_info
where mark between 550 and 600
查询入学成绩最高分的学生信息
命令:select *
from stu_info
where mark=all(select max(mark) from stu_info)
(5)从“stu_info”表中查找姓名中第2个字是的学生.
命令:select *
from stu_info
where stu_name LIKE '_'
 
 (6)从“stu_info”表中查找姓、姓、姓的学生.
命令:select *
from stu_info
where stu_name LIKE '[李方张]%'
 
 
(7)stu_info表中查找stu_id的最后一位为1-3的学生.
命令:select *
from stu_info
where stu_ID LIKE '%[1,2,3]'
(8)stu_info表中查找stu_id的最后一位不为13的学生.
命令:select *
from stu_info
where stu_ID not LIKE '%[1,3]'
(9) 查询“stu_info”表中,address广东南海”,性别为男的记录,要求显示姓名、性别、address3个字段
命令:select stu_name,sex,address
from stu_info
where address='广东南海' and sex=''
 
 (10) 在数据表stu_info中查询与方迪同乡的学生情况.
命令:select *
from stu_info
where address=(select address from stu_info where stu_name='')
(11) 列出stu_grade表中c01号课程成绩高于该课程平均成绩的学生信息
命令:select *
from stu_grade
where grade>=(select avg(grade) from stu_grade
where course_id='c01') and course_id='c01'
 
(12) 列出计算机网络专业学生的选课情况
命令:select *
from stu_grade
where stu_id in (select stu_id from stu_info where major='计算机网络')
 
(13) 列出计算机网络专业学生的学号、姓名、专业、成绩
命令:select *
from stu_info
where stu_id in (select stu_id from stu_info where major='计算机网络')
列出计算机网络专业学生的学号、姓名、专业、课程、成绩
select stu_info.stu_id,stu_name,major,course_name,grade
from stu_info,stu_grade,course_info
where stu_info.stu_id in (select stu_id from stu_info where major='计算机网络')and stu_info.stu_id=stu_grade.stu_id and course_info.course_id=stu_grade.course_id
 
 
(14) 根据课程和stu_grade表,查询选修了离散数学的学生的学号、课程名、成绩
命令:select stu_info.stu_id,stu_name,course_name,grade
from stu_info,stu_grade,course_info
where stu_info.stu_id in (select stu_id from stu_grade where course_name='离散数学')and course_info.course_id=stu_grade.course_id and stu_info.stu_id=stu_grade.stu_id