数据库操作-3(单表查询)

1、检索:

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE sex='F' and age = 18;

2、去掉重复项:

SELECT DISTINCT age FROM Student_2;

3、确定范围:

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE age BETWEEN 18 AND 19;

4、模糊查询:

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE dept LIKE 'C%';

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE dept LIKE 'C%' or dept LIKE 'I%';

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE dept LIKE 'I_T';

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE dept LIKE 'I%T';

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE dept LIKE '%o_';

-- aabbccddefg

5、组合查询:

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE dept in ('CS', 'IoT');

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE dept not in ('CS', 'IoT');

6、对结果排序:

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE sex = 'F'
ORDER BY age;

SELECT sno, sname, sex, age, dept FROM Student_2
WHERE sex = 'F'
ORDER BY age DESC;

7、函数:

SELECT COUNT(*) FROM Student_2
WHERE sex = 'F';

SELECT COUNT(*) AS 'student_count' FROM Student_2
WHERE sex = 'F';

SELECT AVG(age) AS 'avg_age' FROM Student_2
WHERE sex = 'F';

SELECT COUNT(*) as 'count', AVG(age) as 'avg' FROM Student_2
WHERE sex = 'F';

SELECT COUNT(DISTINCT age) FROM Student_2
WHERE sex = 'F';

-- 查询学生的个数

SELECT MAX(age) FROM Student_2
WHERE sex = 'F';

SELECT MIN(age) FROM Student_2
WHERE sex = 'F';

SELECT SUM(age) FROM Student_2
WHERE sex = 'F';

-- SC表,查询最高成绩、最低成绩、平均成绩

8、分组查询:

SELECT age, COUNT(*) FROM Student_2
GROUP BY age;

SELECT  sno, AVG(grade) FROM SC2
GROUP BY sno;

SELECT  sno, AVG(grade) FROM SC2
GROUP BY sno;
HAVING avg(grade) > 70

-- 至少选修2门成绩的学生的学号
SELECT  sno FROM SC2
GROUP BY sno
HAVING count(*) > 2;

SELECT cno, AVG(grade) FROM SC2
GROUP BY cno;

SELECT cno, AVG(grade), count(*) FROM SC2
GROUP BY cno;

SELECT age, COUNT(*) as number FROM Student_2
GROUP BY age
HAVING number>5
ORDER BY age DESC;

-- 按照平均成绩降序且所有课程都及格的学号，及其平均成绩（不包含3号课）
SELECT sno, AVG(grade) as avg_g FROM SC2
WHERE cno <> '003'
GROUP BY sno
HAVING MIN(grade)>=60
ORDER BY avg_g DESC;