【0816测试】

#查询student第2到第4条数据
SELECT * FROM student limit 1,4;

#从student表查询所有学生的学号，姓名和院系
SELECT id,`name`,Department FROM student;

#从Student表中查询计算机系和英语系的学生
SELECT `name` ,Department FROM student WHERE Department IN ('计算机系','英语系');

#从Student表中查询年龄在18~22岁的学生信息
SELECT id,`name`,sex,2019-birth AS age,department,address FROM student WHERE  2019-birth BETWEEN  18 AND 22;

#从student表中查询每个院系有多少人
SELECT department, COUNT(id) FROM student GROUP BY department;

#从Score表中查询每个科目的最高分
SELECT c_name,MAX(grade) FROM score GROUP BY c_name;

#查询李四的考试科目
SELECT c_name, grade FROM score WHERE stu_id=(SELECT id FROM student  WHERE name= '李四' );

#用连接的方式查询所有学生的姓名、院系、科目和考试成绩
SELECT student.Id,`name`,sex,birth,department,address,c_name,grade FROM student,score WHERE student.id=score.stu_id;

#计算每个学生的总成绩
SELECT student.Id,`name` ,SUM(grade) FROM student,score  WHERE student.Id=score.stu_id GROUP BY ID;

#计算每个考试科目的平均成绩
SELECT c_name,avg(grade) FROM score GROUP BY c_name;

#查询计算机成绩低于95分的学生信息
SELECT *FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name='计算机' AND grade <95);

#查询同时参加计算机和英语考试的学生信息
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='英语');

#将计算机考试成绩按照从高到低进行排序
SELECT stu_id, grade FROM score WHERE c_name= '计算机' ORDER BY grade DESC;

#从student表和score表中查询出学生的学号，然后合并查询结果
SELECT id  FROM student UNION SELECT stu_id FROM score;

#查询姓张或者姓王的同学的姓名、院系和考试科目以及成绩（提示，模糊查询关键字like 例如查询以A开头的姓名  selec * from 表名 where name like ‘A%’）
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 ;

#查询都是湖南的学生的姓名、年龄、院系和考试科目以及成绩
SELECT student.Id ,`name` ,sex,Birth,Department,Address ,c_name,grade FROM student,score WHERE Address LIKE '湖南%' AND student.id=score.stu_id ;