sql查询实例

sql查询实例

首先存在数据库表：

1、查询选修了3门课程以上（含3门）的学生学号和选修课程数。

SELECT
	student_id,
	count( course_id ) 
FROM
	student_course
GROUP BY
	student_id
HAVING count( course_id ) >2 

HAVING 子句可以让我们筛选分组后的各组数据。

2、查询选修了课程名是“语文”的学生学号和姓名。
SELECT

	s.student_no,
	s.`name` 
FROM
	student s
	JOIN student_course sc ON sc.student_id = s.id
	JOIN course c ON sc.course_id = c.id 
WHERE
  c.`name` = '语文'

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

其中inner join（join）性能较高，mysql在执行sql语句时，会自动根据对应连接语句选出最优的主表。

---

3、查询与李四上过课的学生姓名和学号。
A:

SELECT 
DISTINCT( student.student_no ),
	student.`name` 
FROM
	student
	JOIN student_course sc ON sc.student_id = student.id 
WHERE
	sc.course_id IN (
	SELECT
		sc.course_id 
	FROM
		student_course sc
		JOIN student s ON sc.student_id = s.id 
WHERE
	s.`name` = '李四' ) AND student.`name` != '李四'

子查询语法：

• 如果子查询返回多个值，则需要使用in、ant、all等关键字
• 把子查询当做是where条件中的值，如果子查询返回单行、单列值，则被当成一个标量使用（大于> 等于= <小于）
  【where java_teacher > (select teacher_id from teacher_table where name = ‘name2’);】
• –把子查询当做是数据表
  【select *
  –放在from之后当成数据表
  from (select * from student_table) t
  where t.java_teacher>1;】

B:

SELECT
	s.* 
FROM
	student_course sc
	JOIN student s ON s.id = sc.student_id 
WHERE
	sc.course_id IN ( SELECT sc.course_id FROM student s JOIN student_course sc ON sc.student_id = s.id WHERE s.`name` = "李四" ) 
	AND s.`name` != "李四" 
GROUP BY
	s.id

---

4、修改李四的总成绩为200，学生表update_time修改为当前时间，以及李四的地理课成绩改为100，一条sql。

UPDATE student s
JOIN student_course sc ON s.id = sc.student_id
JOIN course c ON c.id = sc.course_id 
SET s.total_score = 200,
update_time = NOW(),
sc.score = 100 
WHERE
	s.`name` = "李四" 
	AND c.`name` = "地理"

join查询时 where后可以加多张表的条件，sql函数NOW()获取当前时间。

5、修改赵四的总成绩为每科成绩的总和

UPDATE student s 
SET s.total_score = (
	SELECT
		* 
	FROM
		( SELECT SUM( score ) FROM student_course sc JOIN student s ON sc.student_id = s.id WHERE s.`name` = "赵四" ) a 
	) 
WHERE
	s.`name` = "赵四"

不能先select出同一表中的某些值，再update这个表(在同一语句中)
所以在SELECT SUM( score ) FROM student_course sc JOIN student s ON sc.student_id = s.id WHERE s.name = “赵四” ，外层要通过另一层子查询虚拟出一个a表用来显示该条子查询的结果。