数据库测试

- 查询student表中所有记录

SELECT * FROM student;

• 查询student表中的第2条到第4条记录

SELECT * FROM student ORDER BY id ASC LIMIT 1,3;
或者
SELECT * FROM student WHERE id>901 AND id<905;

• 从student表中查询所有学生的学号（id）、姓名（name）和院系（depatment）的信息

SELECT id,name,department FROM student;

• 从student表中查询计算机系和英语系的学生的信息（用IN关键字）

SELECT * FROM student WHERE department IN (‘计算机系’,’英语系’);

• 从student表中查出年龄在26-29岁学生的信息（用BETWEEN AND ）

SELECT * FROM student WHERE birth BETWEEN 1988 AND 1991;

• 从student表中查询每个院系有多少人

SELECT department,COUNT(*) FROM student GROUP BY department;

• 从score表中查出每个科目成绩的最高分

SELECT c_name , M AX (grade) FROM score GROUP BY c_name;

• 查询李四的考试科目(c_name)和考试成绩(grade)

SELECT c_name,grade FROM score
INNER JOIN student ON student.id = score.stu_id
WHERE student.name = ‘李四’;

• 用连接的方式查询所有学生的信息和考试信息

SELECT * FROM student,score
WHERE student.id = score.stu_id;

• 计算每个学生的总成绩

SELECT name,SUM(grade) FROM score
INNER JOIN student ON student.id = score.stu_id
GROUP BY student.name;

• 计算每个考试科目的平均成绩

SELECT c_name, AVG(grade) FROM score GROUP BY c_name;

• 查询计算机成绩低于95分的学生信息

SELECT student.id ,name,sex,birth,department,address FROM student
INNER JOIN score ON student.id = score.stu_id
WHERE grade < 95
AND c_name = ‘计算机’;

• 查询同时参加计算机和英语考试的学生的信息

SELECT student.id ,name,sex,birth,address FROM student
INNER JOIN score sc1 ON student.id = sc1.stu_id
INNER JOIN score sc2 ON student.id = sc2.stu_id
WHERE sc1.c_name =’计算机’ AND sc2.c_name =’英语’;

• 将计算机考试成绩从高到低进行排序

SELECT c_name,grade FROM score
WHERE c_name = ‘计算机’
ORDER BY grade DESC;

• 从student表和score表中查出学生的学号，然后合并查询结果

SELECT student.id,score.stu_id FROM student
INNER JOIN score ON student.id = score.stu_id;

• 查询姓张或姓王的同学的姓名、院系、考试科目和成绩

SELECT name,department,c_name,grade FROM student
LEFT JOIN score ON student.id = score.stu_id
WHERE name LIKE ‘张%’ OR name LIKE ‘王%’;

• 查询都是湖南的学生的姓名、年龄、院系、考试科目和成绩

SELECT name,(‘2017’-birth) AS age,department,c_name,grade FROM student
LEFT JOIN score ON student.id = score.stu_id
WHERE address LIKE ‘湖南%’;