Mysql 单表查询 子查询 关联查询

数据准备：

---

## 学院表
create table department(
d_id int primary key auto_increment,
d_name varchar(20) not null
);
insert into department(d_name ) values('计算机学院'),('外语学院');
insert into department(d_name ) values('机械学院'),('建工学院'),('艺术学院');

## 学生表
create table student(
s_id int primary key auto_increment,
s_name varchar(20) not null,
dept_id int ,
foreign key(dept_id) references department(d_id)
);
insert into student(s_name,dept_id ) values('佳能',2),('lucky',3);
insert into student(s_name,dept_id ) values('张三',1),('李四',1);
insert into student(s_name,dept_id ) values('张三丰',2),('小明',3),('小红',2);
insert into student(s_name) values('安迪'),('杰克');

## 学生详情
create table student_detail(
id int primary key, #学号
age int ,
sex char(4),
address varchar(20),
foreign key(id) references student(s_id)
);
insert into student_detail value(4,18,'女','北京');
insert into student_detail value(2,20,'男','湖南长沙');
insert into student_detail value(3,19,'男','广东佛山');
insert into student_detail value(1,21,'男','湖南长沙');
insert into student_detail value(5,17,'女','四川成都');
insert into student_detail value(6,23,'女','湖北武汉');

## 课程表
create table `cours`(
c_id int primary key auto_increment,
c_name varchar(20),
dept_id int not null, # 课程所属学院
foreign key(dept_id) references department(d_id)
);
insert into cours(c_name ,dept_id) values('python',1),('英语',2);
insert into cours(c_name ,dept_id) values('java',1),('工程造价',4);
insert into cours(c_name ,dept_id) values('机械设计',3),('土木',4);

课表

create table `select`(
s_id int,
c_id int,
primary key(s_id,c_id),
foreign key(s_id) references student(s_id),
foreign key(c_id) references cours(c_id)
);
insert into `select` values(2,4),(3,2),(1,1);
insert into `select` values(2,1),(4,2),(3,1);
insert into `select` values(2,3),(4,1),(2,2);

---

############################ Mysql 单表查询 ############################

---

############################  Mysql 子查询  ############################

---

############################ Mysql 关联查询 ############################

测试案例：

---

1 查出学生详情表性别为男,并同时年龄大于18的

select * from  student_detail where sex='男' and age>18;

2 根据上述的结果,查出学生表对应的 姓名， 年龄，家庭住址

select s.s_name,stu.age,stu.address from student s
left join student_detail stu on s.s_id = stu.id
where stu.sex='男' and stu.age > 18 ;

-----------------------------------------------------------------------------------

## 也可以使用子查询的方法
select s.s_name,stu.age,stu.address from student s
left join student_detail stu on s.s_id = stu.id
where s.s_id in ( select id from student_detail where sex='男' and age>18 ) ;

 

3 将学生详情，通过性别进行分组， 统计出各组的人数，并得出每组的年龄的平均值

select sex, count(id),avg(age) from student_detail group by( sex );

 

4.作为学生，选完课希望看到： 学生姓名， 所选课程名，开课学院

select s.s_name, c.c_name , d.d_name from `select` se
left join student s on se.s_id = s.s_id
left join cours c on se.c_id = c.c_id
left join department d on d.d_id = c.dept_id
where s.s_id =2;