MySQL 查询练习

数据准备过程

--数据准备
INSERT INTO students values('101','曾华','男','1997-09-01','95033');
INSERT INTO students values('102','阿明','男','1998-05-21','95031');
INSERT INTO students values('103','王丽','男','1997-04-21','95033');
INSERT INTO students values('104','李俊','男','1997-03-11','95033');
INSERT INTO students values('105','王芳','男','1997-04-23','95033');
INSERT INTO students values('106','陆军','男','1997-09-30','95031');
INSERT INTO students values('107','王五','男','1997-03-02','95033');
INSERT INTO students values('108','张三','男','1997-12-01','95031');
INSERT INTO students values('109','李四','男','1997-09-09','95031');

UPDATE students SET sex = '女' WHERE id = '103' OR id = '105';

SELECT * FROM students;

INSERT  INTO teachers values('804','李承','男','1958-12-02','副教授','计算机系');
INSERT  INTO teachers values('856','张旭','男','1969-03-03','讲师','计算机系');
INSERT  INTO teachers values('825','王平','女','1978-12-22','讲师','计算机系');
INSERT  INTO teachers values('831','刘冰','女','1988-06-07','助教','计算机系');

SELECT * FROM teachers;

INSERT INTO courses values('3-105','计算机导论','825');
INSERT INTO courses values('3-245','操作系统','804');
INSERT INTO courses values('6-166','数字电路','856');
INSERT INTO courses values('9-888','高等数学','831');

SELECT * FROM courses;

INSERT INTO scores values('103','3-105','92');
INSERT INTO scores values('103','3-245','86');
INSERT INTO scores values('103','6-166','85');
INSERT INTO scores values('105','3-105','88');
INSERT INTO scores values('105','3-245','75');
INSERT INTO scores values('105','6-166','79');
INSERT INTO scores values('109','3-105','76');
INSERT INTO scores values('109','3-245','68');
INSERT INTO scores values('109','6-166','81');

SELECT * FROM scores;

查询练习

1. 查询students中所有name,sex,class的记录

SELECT * FROM students;

2.查询教师不重复的department

SELECT DISTINCT department FROM teachers;
DISTINCT 为去重关键字

3.查询scores中分数在60-80之间的记录

SELECT * FROM scores WHERE score BETWEEN 60 AND 80;
或
SELECT * FROM scores WHERE score >= 60 and score <= 80;

4.以id升序,score降序查询scores表中的所以记录

select * from scores order by student_id asc,score desc;
ORDER BY column_name 为按某项排序，默认为升序排列
ASC 为升序排序， DESC为降序排列。

5.查询‘95031’ 班的人数

  SELECT COUNT(*) FROM students WHERE class='95031';

  COUNT() 为 统计数目函数

6.查询最高分学生学号与课程号

SELECT student_id,course_id,score FROM scores WHERE score = (SELECT MAX(score) FROM scores);

复合语句，先寻找最大分数，再查找分数为最高分的项

7.查询每门课的平局成绩

select avg(score) from scores where course_id = '3-105';
select avg(score) from scores where course_id = '3-245';
select avg(score) from scores where course_id = '6-166';
或
select avg(score) from scores group by course_id;
AVG()函数为计算平均值。
GROUP BY 为 分组函数

8.查询scores中至少两名学生选修的并且以3开头的课程的平均分数

SELECT course_id,avg(score) FROM scores GROUP BY course_id 
HAVING COUNT(course_id) AND course_id LIKE '3%';

分段表达
首先查询课程以及均分，再以having添加查询条件

LIKE为模糊查询 '3%'代表以3开头

查询所有学生的students_name,course_name和score列。