SQL Server2000实用教程第4章 数据库查询.ppt

USE teachdb SELECT s_name，c_name，score FROM student AS s，course AS c，choice AS cho WHERE s.s_no=cho.s_no AND c.c_no=cho.c_no USE teachdb SELECT c2.s_no AS 学号，c1.c_name AS 未选修的课程， c1.c_no AS 课程编号 FROM course AS c1，choice AS c2 WHERE c1.c_noc2.c_no AND c2.s_no in(’103’,’104’) [例4.35] 显示选课表（choice）中选修相同课程的学生的学号和课程编号。 FROM 主表 LEFT OUTER JOIN 从表 ON…… WHERE 主表.列表达式 *= 从表.列表达式 FROM 从表 RIGHT OUTER JOIN 主表 ON…… WHERE 从表.列表达式 =* 主表.列表达式 FROM 表1 FULL OUTER JOIN 表2 ON…… USE teachdb SELECT choice.s_no,choice.score, student.s_name,student.s_department FROM choice LEFT OUTER JOIN student ON choice.s_no=student.s_no USE teachdb SELECT choice.s_no,choice.score, student.s_name,student.s_department FROM choice RIGHT OUTER JOIN student ON choice.s_no=student.s_no USE teachdb SELECT choice.s_no,choice.score, student.s_name,student.s_department FROM choice FULL OUTER JOIN student ON choice.s_no=student.s_no USE teachdb SELECT s_no，s_name，s_department FROM student UNION SELECT t_no，t_name，t_duty FROM teacher USE teachdb SELECT s_name, (SELECT COUNT(*) FROM choice WHERE choice.s_no=student.s_no) AS 选修课程 FROM student 该例显示student表中各位同学选修的课程数。在SELECT子句中 统计choice表中相应学号同学的记录数，并把它作为一个子查询嵌入。 USE teachdb SELECT DISTINCT s_no AS 分数低于平均分者 FROM choice WHERE score (SELECT AVG(score) FROM choice) USE teachdb SELECT s_no AS 学号，AVG（score） AS 平均分 FROM choice GROUP BY s_no HAVING s_no IN（SELECT s_no FROM student WHERE s_department=’计算机’) USE teachdb SELECT s_name,s_department FROM student WHERE s_no NOT IN(SELECT s_no FROM choice ) USE teachdb SELECT s_no,c_no,score FROM choice_x WHERE scoreANY(SELECT score FROM choice WHERE score60) [例4.45] 将【例4.44】中的ANY改为ALL， 用比较运算符加ALL引入子查询值列表。 本例只有当分数大于choice表中最高分者才满足要求。 WHERE [NOT] EXISTS（子查询） USE teachdb SELECT * FROM student WHERE s_department NOT LIKE ’计算机’ USE teachdb SELECT * FROM student WHERE s_name LIKE 李% USE teachdb SEL