select students.name,subjects.title,scores.score from
scores inner join students on scores.stuid = students.id
inner join subjects on scores.subid = subjects.id;
第二种写法：
select students.name,subjects.title,scores.score from students
inner join scores on scores.stuid = students.id
inner join subjects on scores.subid = subjects.id;
第三种写法
select students.name,subjects.title,scores.score from subjects
inner join scores on scores.subid = subjects.id
inner join students on scores.stuid = students.id;
左链接 (left join)
select * from students left join scores on students.id =scores.stuid;
select * from scores left join students on students.id = scores.subid;
右链接 (rigth join)
select * from students right join scores on students.id =scores.stuid;
select * from scores right join students on students.id = scores.subid;
• 如果多个表中列名不重复可以省略“表名.”部分
select name,title,score from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;
• 如果表的名称太长，可以在表名后面使用’ as 简写名’或’ 简写名’，为表起个临时的简写名称
select name ,score from scores as sco
right join students as stu on stu.id = sco.stuid;
• 查询学生的姓名、平均分
select students.name as 姓名,avg(scores.score) as 平均分 from scores inner join students on scores.stuid = students.id group by students.name;
• 查询男生的姓名、总分
select students.name,sum(scores.score) from scores inner join students on students on scores.stuid = students.id where students.gender=1 group by students.name;
• 查询科目的名称、平均分
select subjects.title,avg(scores.score) from scores inner join subjects on
scores.stuid = sunjects.id where group by subjects.title;
• 查询未删除科目的名称、平均分、最高分
select subjects.title,avg(scores.score),max(scores.score) from scores inner join subjects on
scores.stuid = sunjects.id where subjects.isdelete=0 group by subjects.title;
自关联
1.引入自关联
创建areas地区表 ：
create table areas (
id int primary key auto_ increment not mull,
atitle varchar(40),
pid int(11),
foreign key(pid) references areas(id)
);
导入数据
1.把areas.sql文件拷贝到liunx 上，
2.在当前目录进入到Mysql 选择数据库，执行命令:source areas.sql
自关联表的数据的查询：
查询一共有多少个省级单位
select * from areas where pid is null;
select id,atitle from areas where pid is null;
查询省的名字为"“山西省"所有城市
1.列出的城市的inxi:city.*
2.隐含条件，city.pid=province.id 要取别名
3.where provice.atitle = “山西省”
4.总共一张表，有两中数据类型，模拟两张表
select city.* from areas as city inner join areas as province on city.pid=province.id where province.atitle = “山西省”;
查询市的名称为广州市的所有区县
1.虚拟三张表，provice,city,dis select dis.*
2.隐含条件，city.pid=province.id ,dis.pid=city.id 要取别名
where city.atitle=“广州市”
select dis* from areas as dis inner join areas as city on city.id = dis.pid inner join areas as province on procince.id = city.pid where provice.atitle = “广州市”;
