# train-sql

**Repository Path**: JeniGui/train-sql

## Basic Information

- **Project Name**: train-sql
- **Description**: sql练习
- **Primary Language**: Java
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 2
- **Created**: 2020-03-24
- **Last Updated**: 2021-11-03

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# train-sql
MySQL练习使用习题

## 一、使用工具
+ Navicat Premium

## 二、练习资料
### 2.1 sql脚本
> table.sql
```mysql
-- 学生表

CREATE TABLE `Student`(

`s_id` VARCHAR(20),

`s_name` VARCHAR(20) NOT NULL DEFAULT '',

`s_birth` VARCHAR(20) NOT NULL DEFAULT '',

`s_sex` VARCHAR(10) NOT NULL DEFAULT '',

PRIMARY KEY(`s_id`)

);

-- 课程表

CREATE TABLE `Course`(

`c_id`  VARCHAR(20),

`c_name` VARCHAR(20) NOT NULL DEFAULT '',

`t_id` VARCHAR(20) NOT NULL,

PRIMARY KEY(`c_id`)

);

-- 教师表

CREATE TABLE `Teacher`(

`t_id` VARCHAR(20),

`t_name` VARCHAR(20) NOT NULL DEFAULT '',

PRIMARY KEY(`t_id`)

);

-- 成绩表

CREATE TABLE `Score`(

`s_id` VARCHAR(20),

`c_id`  VARCHAR(20),

`s_score` INT(3),

PRIMARY KEY(`s_id`,`c_id`)

);
```
> 插入数据
> data.sql
```mysql
-- 插入学生表测试数据

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' , '女');

-- 课程表测试数据

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');



-- 教师表测试数据

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');



-- 成绩表测试数据

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);
```
## 三、习题
> 50练习题

```mysql
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
-- 6、查询"李"姓老师的数量 
-- 7、查询学过"张三"老师授课的同学的信息 
-- 8、查询没学过"张三"老师授课的同学的信息 
-- 8、查询没学过"张三"老师授课的同学的信息 
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- ...
-- ...

-- 47、查询本周过生日的学生
-- 48、查询下周过生日的学生
-- 49、查询本月过生日的学生
-- 50、查询下月过生日的学生
```

## 四、参考答案

> answer.sql

```mysql
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
-- 思路: "01"的表1, "02"的表2, 然后表1与表2的并表查询  
select stu1.*,cou1.c_id,sco1.s_score,cou2.c_id,sco2.s_score from student stu1,
score sco1,course cou1,student stu2,score sco2,course cou2
where stu1.s_id=sco1.s_id 
and sco1.c_id=cou1.c_id

and stu2.s_id=sco2.s_id 
and sco2.c_id=cou2.c_id

and cou1.c_id='01'
and cou2.c_id='02'
AND sco1.s_score>sco2.s_score

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
-- 思路:参考题1
select stu1.*,cou1.c_id,sco1.s_score,cou2.c_id,sco2.s_score from student stu1,
score sco1,course cou1,student stu2,score sco2,course cou2
where stu1.s_id=sco1.s_id 
and sco1.c_id=cou1.c_id

and stu2.s_id=sco2.s_id 
and sco2.c_id=cou2.c_id

and cou1.c_id='01'
and cou2.c_id='02'
AND sco1.s_score<sco2.s_score

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
-- 思路: 1.并表查询每个同学的平均成绩(每个人的avg需要分组,by stu.s_id) 2.再条件筛选 3. 没有where+ 函数的写法
-- 并表
select stu.s_id,stu.s_name,AVG(sco.s_score) as 'avg_score' from student stu,
score sco
where stu.s_id=sco.s_id
GROUP BY stu.s_id
HAVING AVG( sco.s_score) >= 60

-- join
select stu.s_id,stu.s_name,AVG(sco.s_score) as 'avg_score' from student stu
INNER JOIN score sco
on stu.s_id=sco.s_id
GROUP BY stu.s_id
HAVING AVG( sco.s_score) >= 60

-- ...
```



## 五、鸣谢

感谢