MySQL（十一） DQL多表链接查询基本使用

MySQL DQL多表链接查询基本使用

多表查询思路：

• join 是 inner join 简写

1.数据来自多张表，优先想到多表连接join ON

2.关联表写join两端

3.on条件写两表的关联列

4.所有查询条件select后，注意表名和别名

5.where过滤条件写最后

• **注释：**INNER JOIN 与 逗号多表 是相同的。

  select * from student,sc where student.sno=sc.sno;   '相同'   select * from student inner join sc on student.sno=sc.sno;
  

• inner join是内连接,显示符合连接条件的记录，连接条件已经指明了。与直接使用where 条件过滤条件一样的两个表符合条件的数据。

• left join 左连接查询结果是以左边的表为主,右表的数据匹配不到的字段为null;

• right join 右连接查询结果以右边的表为主,左表的数据匹配不到的字段为null;

MySQL官方示例库

下载地址

备用下载地址

两张表查询：

#查询一下世界上人口数量小于100人的城市名和国家名
select city.countrycode as daihao, country.name as guojia,city.name as chengshi,city.population as renkou from city join country on country.code=city.countrycode where city.population<100;

#查询城市shenyang，城市人口，所在国家名（name）及国土面积（surfacearea）
select country.name,city.name,country.SurfaceArea from city join country on city.countrycode=country.code where city.name='shenyang';

#查询城市为heze，城市人口，所在国家名（name），及国土面积（surfacearea）
select country.name,city.name,city.population,country.surfacearea from city join country on city.countrycode=country.code where city.name='heze';

---

案例：

创建school库：

CREATE DATABASE school CHARSET utf8;
USE school

创建学生表：

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage  TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

student(sno,sname,sage,ssex) 学生表 
sno：  学号;
sname：学生姓名;
sage： 学生年龄;
ssex： 学生性别;

创建课程表：

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

course(cno,cname,tno) 课程表 
cno：  课程编号；
cname：课程名字；
tno：  教师编号 ；

创建成绩表：

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

sc(sno,cno,score) 成绩表 
sno：  学号；
cno：  课程编号；
score：成绩 ；

创建教师表：

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

teacher(tno,tname)  教师表 
tno：  教师编号； 
tname：教师名字；

学生表中录入N为学生：

INSERT INTO student(sno,sname,sage,ssex) VALUES(1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex) VALUES (2,'zhang4',18,'m'),(3,'li4',18,'m'),(4,'wang5',19,'f');

INSERT INTO student VALUES(5,'zh4',18,'m'),(6,'zhao4',18,'m'),(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex) VALUES('olda',20,'m'),('oldgirl',20,'f'),('oldp',25,'m');

教师表中录入三位老师：

INSERT INTO teacher(tno,tname) VALUES(101,'olda'),(102,'hesw'),(103,'oldguo');

课程表中录入三门课程：

INSERT INTO course(cno,cname,tno) VALUES(1001,'linux',101),(1002,'python',102),(1003,'mysql',103);

成绩表中录入学生成绩：

INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SQL练习题：

group_concat()函数是以一行显示所有查询到的值

统计zhang3,学习了几门课：

select st.sname,count(sc.sno) from student as st join sc on st.sno=sc.sno where st.sname='zhang3';

查询zhang3,学习的课程名称有哪些：

select st.sname,co.cname from student as st join sc on st.sno=sc.sno join course as co on sc.cno=co.cno where st.sname='zhang3';

或

select st.sname,group_concat(co.cname) from student as st join sc on st.sno=sc.sno join course as co on sc.cno=co.cno where st.sname='zhang3';

查询olda老师教的学生名：

select te.tname,group_concat(st.sname) from teacher as te join course as co on te.tno=co.tno join sc on co.cno=sc.cno join student as st on st.sno=sc.sno where te.tname='olda';

查询olda所教课程的平均分数：

select te.tname,avg(sc.score) from teacher as te join course as co on te.tno=co.tno join sc on co.cno=sc.cno where te.tname='olda';

查询olda所教的80分以上的学生姓名：

select te.tname,st.sname,sc.score from teacher as te join course as co on te.tno=co.tno join sc on co.cno=sc.cno join student as st on sc.sno=st.sno where te.tname='olda' and sc.score>=80;

olda所教的80分以上的学生姓名：

select te.tname,st.sname,sc.score from teacher as te join course as co on te.tno=co.tno join sc on co.cno=sc.cno join student as st on sc.sno=st.sno where te.tname='olda' and sc.score>=80;