我的MySQL学习笔记（五）

联表查询

先创建两张表备用

---

#考试表
create table exam(
Id int(5) ZEROFILL NOT NULL PRIMARY KEY auto_increment,
Name VARCHAR(20) not null,
chinese double(5,2),
math double(5,2),
english double(5,2),
music double(4,2),
Age int(2),
sex CHAR(1),
class int(3) not null
);
#插入数据
insert into exam(name,chinese,math,english,music,age,sex,class)
VALUES('张三',121,88,70,60,17,'男',2),
('李四',119,98,83,60,17,'男',1),
('王舞',89,88,70,60,16,'女',2),
('赵灵儿',128,112,95,87,15,'女',2),
('钱多多',121,88,70,48,18,'女',1),
('孙悟空',40,140,60,5,19,'男',1);

create table extea(
tid int(5) ZEROFILL NOT NULL PRIMARY KEY auto_increment,
Name VARCHAR(20) not null,
sex char(1),
class int(3) not null
); 

insert into extea(name,sex,class)
VALUES('赵','女',2),
('王','女',1),
('韩','男',2),
('周','男',2),
('公孙','男',1),
('上官','女',1);

---

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -联表查询- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---

# 添加外键，在students表的stuid字段上添加外键，对应teacher表的id
ALTER TABLE exam ADD FOREIGN KEY ( class ) REFERENCES teacher ( class );
# 删除外键
ALTER TABLE exam DROP FOREIGN KEY class;
# 查看表结构
DESC exam;

# 连表查询-简单
SELECT
	* 
FROM
	exam
	INNER JOIN extea ON exam.id = extea.tid;
	
# 查询学生语文成绩及总成绩， 附带查询老师姓名
SELECT
	e.name 学生,
	e.class 班级,
	e.chinese 语文,
	t.name 教师,
	chinese+math+english+music 总分
FROM exam e
	INNER JOIN extea t ON t.tid = e.id 
	group by e.id
ORDER BY
	e.id DESC;
	
# 查询学生成绩及班级总分和平均分并按照总分排名 附带查询老师姓名
SELECT
	e.id,
	e.NAME 学生,
	e.class 班级,
	e.chinese 语文,
	e.math 数学,
	e.english 英语,
	e.music 音乐,
	chinese + math + english + music 总分,
	( chinese + math + english + music ) / 4 平均分,
	t.`Name` 教师
FROM
	exam e
	INNER JOIN extea t ON t.tid = e.id 
ORDER BY
	e.chinese + math + english + music DESC;

---

总结 *重点

---

# 1.普通内链接查询
     inner join .. on 条件;
# 2.隐式内链接查询
     select * from A,B where 条件；
# 3.左外链接查询，查询出的结果先把左表所有的数据全部都查询出来，再把两个表中有关联的数据查询出
     left outer join ... on
# 4.右外链接查询，查询出的结果先把右表所有的数据全部都查询出来，再把两个表中有关联的数据查询出
     right outer join ... on