- 查询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 ‘湖南%’;