MySQL连接查询——内连接_mysql内连接查询

表准备

建立三张表 student , course, exame , sql语句如下：

create table student(
uid int unsigned primary key not null auto_increment,
name varchar(50) not null,
age tinyint unsigned not null,
sex enum('M', 'W') not null);

create table course(
cid int primary key not null auto_increment,
cname varchar(50) not null,
credit tinyint unsigned not null
)engine=INNODB default charset=utf8;
# 因为这张表会插入中文数据，所以默认字符集要设置为 utf8

create table exam(
uid int unsigned not null,
cid int unsigned not null,
time date not null,
score float not null,
primary key(uid, cid));

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

表结构如下：

插入数据的sql：

insert into student(name, age, sex) values
('zhangsan', 18, 'M'),
('gaoyang', 20, 'W'),
('chenwei', 22, 'M'),
('linfeng', 21, 'W'),
('liuxiang', 19, 'W');

insert into course(cname, credit) values
('C++基础课程', 5),
('C++高级课程', 10),
('C++项目开发', 8),
('C++算法课程', 12);

insert into exam(uid, cid, time, score) values
(1,1,'2021-04-09', 99.0),
(1,2,'2021-04-10', 80.0),
(2,2,'2021-04-10', 90.0),
(2,3,'2021-04-12', 85.0),
(3,1,'2021-04-09', 56.0),
(3,2,'2021-04-10', 93.0),
(3,3,'2021-04-12', 89.0),
(3,4,'2021-04-11', 100.0),
(4,4,'2021-04-11', 99.0),
(5,2,'2021-04-10', 59.0),
(5,3,'2021-04-12', 94.0),
(5,4,'2021-04-11', 95.0);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

查询例子1

做连接查询时会区分大表和小表（按数据量划分），小表永远时整表扫描，然后去大表搜索，所以在大表建立索引比较合适。

多表连接查询时，一般第一张表的字段是最全的，可以和剩余表进行连接.

查询uid = 1,cid = 2的学生考试成绩信息：

select a.uid, a.name, a.age, a.sex, b.cid, b.cname, b.credit, c.score
from exam c
inner join student a on a.uid = c.uid
inner join course b on b.cid = c.cid
where c.uid=1 and c.cid=2;
1
2
3
4
5

查询结果

查询例子2

查询每门课考试人数

select b.cid, b.cname, b.credit, count(*)
from exam c
inner join course b on b.cid = c.cid
group by c.cid;
1
2
3
4

选择字段数量进行limit查询的效率问题

由下图可知选择越少的字段查询效率越高：

不过这里和查询缓存也有关系

这里又想查多个字段但是又希望效率不差，可以用如下方法

select a.id,a.email,a.password from t_user a inner join(select id from t_user limit 1500000, 10) b on a.id = b.id;
1

说明：(select id from t_user limit 1500000, 10) 得到的是小表，全表扫描，在 t_user 大表中查询做内连接（这里可用索引）

可以看出时间和查询一列差不多