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

多表查询思路:
  • joininner 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;

「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论