Mysql数据库的查询操作（一）

```sql
# 创建学生表  学号  姓名  生日  性别
create table student(
sid varchar(10) primary key,
sname varchar(20) not null,
sbirthday date not null,
ssex char(3) not null default '男'
);

# 添加数据
insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');

```

```sql
# 教师表 编号 姓名
create table teacher(
tid varchar(10) primary key,
tname VARCHAR(20) not null
);

# 添加数据
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');

```

```sql
# 课程表 编号 课程名  代课老师编号
create table course(
cid varchar(10),
cname VARCHAR(20) not null,
tid varchar(10) not null,
PRIMARY KEY(cid)
);

# 添加数据
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
```

```sql
# 成绩表 学生编号 课程编号 成绩
create table score(
sid varchar(10) ,
cid varchar(10) ,
score FLOAT(4,2)
);

# 添加数据
insert into score values('01' ,'01' , 80);
insert into score values('01' ,'02' , 90);
insert into score values('01' ,'03' , 99);
insert into score values('02' ,'01' , 70);
insert into score values('02' ,'02' , 60);
insert into score values('02' ,'03' , 80);
insert into score values('03' ,'01' , 80);
insert into score values('03' ,'02' , 80);
insert into score values('03' ,'03' , 80);
insert into score values('04' ,'01' , 50);
insert into score values('04' ,'02' , 30);
insert into score values('04' ,'03' , 20);
insert into score values('05' ,'01' , 76);
insert into score values('05' ,'02' , 87);
insert into score values('06' ,'01' , 31);
insert into score values('06' ,'03' , 34);
insert into score values('07' ,'02' , 89);
insert into score values('07' ,'03' , 98);
```

1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

```sql
select s1.sid from score s1 join score s2 
on s1.sid = s2.sid and s1.cid = '01' and s2.cid='02'
where s1.score > s2.score;
```

2.查询平均成绩大于60分的学生的学号和平均成绩

```sql
select * from (select student.sid,avg(score.score) as avg_s from student join score on student.sid = score.sid group by student.sid) as t
where t.avg_s > 60;

--简单方法
select sid,avg(score.score) as avg_s from  score group by sid having avg_s>60;
```

3.查询所有学生的学号、姓名、选课数、总成绩

```sql
select student.sid, sname, count(cid) 选课数, sum(ifnull(score, 0)) 总成绩
from student left join score on student.sid = score.sid group by sid;

```

4.查询姓“张”的老师的个数

```sql
select count(*) 个数 from teacher where left(tname, 1) = '张';
```

5.查询没学过“张三”老师课的学生的学号、姓名

```sql
-- 连接查询  统计每个学生 选课的老师姓名  
-- 再此基础上进行筛选 老师们的名字中没有张三
select student.sid, sname from student left join score on student.sid = score.sid left join course on course.cid=score.cid left join teacher on course.tid=teacher.tid
group by sid having !instr(ifnull(group_concat(tname), ''), '张三');
```

6.查询学过“张三”老师所教的所有课的同学的学号、姓名

```sql
 select student.sid, sname from score join student on score.sid=student.sid where cid in (select cid from course join teacher on course.tid=teacher.tid where tname='张三');
```

7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

```sql
select * from student s1 join score c1 join student s2 join score c2 on s1.sid = c1.sid and c2.sid =s2.sid and c1.cid=01 and c2.cid=02
where s1.sid = s2.sid;
-- 简化
select s1.sid, sname from score as s1 join score as s2 join student on s1.sid=s2.sid and s1.cid='01' and s2.cid='02' and student.sid = s1.sid;
```

8.查询课程编号为“02”的总成绩

```sql
select cid,sum(score) from score where cid =02;
```

9.查询所有课程成绩小于60分的学生的学号、姓名

```sql
select student.sid, sname from score right join student on score.sid=student.sid group by sid having max(ifnull(score, 0)) < 60;

```

10.查询没有学全所有课的学生的学号、姓名

```sql
-- 查到所有课程的数量 
select count(*) from course;
-- 统计每个同学的选课数 筛选 选课数 《 总课程数   
select student.sid, sname from student left join score on student.sid = score.sid
group by sid having count(cid) < (select count(*) from course);
```

Mysql数据库的查询操作（一）

评论已关闭