1 #查询student第2到第4条数据
2 SELECT * FROM student limit 1,4;
3
4 #从student表查询所有学生的学号，姓名和院系
5 SELECT id,`name`,Department FROM student;
6
7 #从Student表中查询计算机系和英语系的学生
8 SELECT `name` ,Department FROM student WHERE Department IN ('计算机系','英语系');
9
10 #从Student表中查询年龄在18~22岁的学生信息
11 SELECT id,`name`,sex,2019-birth AS age,department,address FROM student WHERE 2019-birth BETWEEN 18 AND 22;
12
13 #从student表中查询每个院系有多少人
14 SELECT department, COUNT(id) FROM student GROUP BY department;
15
16 #从Score表中查询每个科目的最高分
17 SELECT c_name,MAX(grade) FROM score GROUP BY c_name;
18
19 #查询李四的考试科目
20 SELECT c_name, grade FROM score WHERE stu_id=(SELECT id FROM student WHERE name= '李四' );
21
22 #用连接的方式查询所有学生的姓名、院系、科目和考试成绩
23 SELECT student.Id,`name`,sex,birth,department,address,c_name,grade FROM student,score WHERE student.id=score.stu_id;
24
25 #计算每个学生的总成绩
26 SELECT student.Id,`name` ,SUM(grade) FROM student,score WHERE student.Id=score.stu_id GROUP BY ID;
27
28 #计算每个考试科目的平均成绩
29 SELECT c_name,avg(grade) FROM score GROUP BY c_name;
30
31 #查询计算机成绩低于95分的学生信息
32 SELECT *FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name='计算机' AND grade <95);
33
34 #查询同时参加计算机和英语考试的学生信息
35 SELECT * FROM student WHERE id=ANY(SELECT stu_id FROM score WHERE stu_id IN (SELECT stu_id FROM score WHERE c_name='计算机')AND c_name='英语');
36
37 #将计算机考试成绩按照从高到低进行排序
38 SELECT stu_id, grade FROM score WHERE c_name= '计算机' ORDER BY grade DESC;
39
40 #从student表和score表中查询出学生的学号，然后合并查询结果
41 SELECT id FROM student UNION SELECT stu_id FROM score;
42
43 #查询姓张或者姓王的同学的姓名、院系和考试科目以及成绩（提示，模糊查询关键字like 例如查询以A开头的姓名 selec * from 表名 where name like ‘A%’）
44 SELECT student.Id ,`name` ,sex,Birth,Department,Address ,c_name,grade FROM student,score WHERE (`name` LIKE '张%' OR `name` LIKE '王%')AND student.id=score.stu_id ;
45
46 #查询都是湖南的学生的姓名、年龄、院系和考试科目以及成绩
47 SELECT student.Id ,`name` ,sex,Birth,Department,Address ,c_name,grade FROM student,score WHERE Address LIKE '湖南%' AND student.id=score.stu_id ;
