mysql练习_2_数据查询

1. select 语句的基本使用

(1) 查询 student 表中每个学生的所有数据
select * from student;
(2) 查询年龄在 17～19 岁之间的学生的姓名及年龄
select * from student where Sage between 17 and 19; #包含17和19
select * from student where Sage >17 and Sage<19; #不包含17和19
(3) 统计学生总人数
select count(*) from student;
(4) 查询信息系（MA）学生的姓名和性别
select Sname,Ssex from student where Sdept="MA";
(5) 查询所有姓“马”的学生的信息
select * from student where Sname like "马%";

2. 子查询的使用

(1) 查询与“马小燕”在同一个系的学生的姓名和所在系

select Sname,Sdept 
from student where Sdept in (
select Sdept from student where Sname="马小燕");

(2) 查询其他系中比 IS 系所有学生年龄都小的学生的姓名和年龄

select Sname,Sage from student where Sage < all (
select Sage from student where Sdept='IS');

3. 连接查询的使用

(1) 查询选修了 3 号课程且成绩在 85 分以上的学生的学号、姓名

select score.sno,student.sname
from student join score on student.sno = score.sno
where score.cno="003" and score.score > 85;

或者这样写：

select sno,sname from student where sno in (
select sno from score where cno="003" and score > 85);

(2) 查询有选课的学生的基本情况

select * from student where sno in (
select distinct sno from score);

4. GROUP BY、ORDER BY 子句的使用
   (1) 查找 student 中男生和女生的人数
   select Ssex,count(*) from student group by ssex;
   (2) 查找选修了 001号课程的学生的学号及其成绩，查询结果按成绩降序排列
   select sno,score from score where cno="001" order by score desc;

---

扩充练习

1. Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同

update course set ctime = (
select ctime from course where cname = "数据结构")
where cname = "程序设计";
# 会报错(只会在mysql中出现 oracel中不会)ERROR 1093 (HY000): You can't specify target table 'course' for update in FROM clause
# 错误的意思是 不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值
# 解决办法：将SELECT出的结果再通过中间表SELECT一遍 其中的中间结果表要指定别名！

update course set ctime = (
select ctime from
(select ctime from course where cname = "数据结构") as a
)where cname = "程序设计";

2. 删除学生表中学号以96开头的学生信息
   delete from student where sno like "96%";
3. 求IS 和CS系年龄在18岁到22岁(包含)的学生的姓名、系别和年龄
   select sname,sdept,sage from student where sdept in ("IS","CS") and sage between 18 and 22;
4. 求选修课程001或003，成绩在80至90之间的学生的学号、姓名、课程号、课程名和成绩。！！！！

select sno,sname,cno,cname,score 
from student 
natural join score 
natural join course 
where cno in ("001","003") and score between 80 and 90;

或者这样(会复杂些 要指明连接字段到底出自那个表)
select sno,sname,score.cno,cname,score.score 
from student natural join score 
join course on Score.cno = course.cno 
where score.cno in ("001","003") and score.score between 80 and 90;

或者这样(更复杂)
select student.sno,student.sname,score.cno,course.cname,score.score
from student join score on student.sno = score.sno
join course on Score.cno = course.cno
where score.cno in ("001","003") and score.score between 80 and 90;

亦或者这样(内连接 on指定的条件可以用where子句代替)
select student.sno,student.sname,score.cno,course.cname,score.score
from student,score,course
where student.sno = score.sno
and Score.cno = course.cno
and score.cno in ("001","003") 
and score.score between 80 and 90;

5.求缺少学习成绩的学生的学号和课程号
select sno,cno from score where score is null;
注意：是 is null！！！

6. 求选修003课程或004课程的学生的学号、姓名、课程号和分数，要求按课程号升序、分数降序的顺序显示结果

select sno,sname,cno,score from student natural join score
where cno in ("003","004")
order by cno asc,score desc;

7.求选修了课程的学生人数
select count(distinct sno) from score;
8.求各门课程的平均成绩和总成绩
select cno,cname,avg(score),sum(score) from score natural join course group by cno;
9.求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

select sname,score from student natural join score
where cno = "001" and
score < 70 or score >90;

10.选修了全部课程的学生的姓名！！

select sname
from student
where not exists
(
      select *
            from course
            where not exists
          (select *
                  from score
                  where sno=student.sno and cno=course.cno
           )
);

11.求成绩比所选修课程平均成绩高的学生的学号、课程号和成绩!!!!

select sno,cno,score,t_avg_score from score natural join
(select cno,avg(score) as t_avg_score from score group by cno) as t
where score > t_avg_score;

+-------+------+-------+-------------+
| sno   | cno  | score | t_avg_score |
+-------+------+-------+-------------+
| 96001 | 003  | 89.00 |   84.300000 |
| 96002 | 001  | 88.00 |   79.416667 |
| 96002 | 003  | 92.50 |   84.300000 |
| 96002 | 006  | 90.00 |   89.000000 |
| 97001 | 001  | 96.00 |   79.416667 |
| 97001 | 008  | 95.00 |   82.500000 |
| 96004 | 001  | 87.00 |   79.416667 |
| 96003 | 003  | 91.00 |   84.300000 |
| 97002 | 003  | 91.00 |   84.300000 |
| 97002 | 006  | 92.00 |   89.000000 |
| 97004 | 005  | 90.00 |   86.000000 |
+-------+------+-------+-------------+
11 rows in set (0.00 sec)

---