联表查询
先创建两张表备用
#考试表
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