SQL学习之inner join关键字

参考源

• 简单教程
  • https://www.twle.cn/l/yufei/sql/sql-basic-join-inner.html
• 菜鸟教程
  • https://www.runoob.com/sql/sql-join-inner.html

SQL inner join 关键字

SQL inner join 关键字只会返回两个表中匹配的行，不匹配的行会被忽略

select column_name(s) from table1 inner join table2 on table1.column_name = table2.column_name;

在有些数据库中，INNER JOIN 又称之为 JOIN

select column_name(s) from table1 join table2 on table1.column_name = table2.column_name ;

在很多数据库中，有一种更加简单的写法

select column_name(s) from table1,table2 where table1.column_name = table2.column_name ;

注：

不接条件的两张表进行连表，得到的是笛卡尔积

图例

图源于简单教程https://www.twle.cn/l/yufei/sql/sql-basic-join-inner.html，很好理解

假设我们有两张表 A 和 B

可以看到每个表的最后一条记录是不匹配的，那么 INNER JOIN 的结果就是

示例数据

CREATE DATABASE IF NOT EXISTS hardy_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;

USE hardy_db;

DROP TABLE IF EXISTS `lesson`;
DROP TABLE IF EXISTS `lesson_views`;

CREATE TABLE `lesson` (
    id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(32) default '',
    views int(11) NOT NULL default 0,
    created_at TIMESTAMP
);

CREATE TABLE `lesson_views` (
    uniq bigint(20) primary key NOT NULL default '0' ,
    lesson_id int(11) default 0,
    date_at  int(11) NOT NULL default 0,
    views int(11) NOT NULL default 0
);

INSERT INTO lesson(id,name,views,created_at) VALUES
(1, 'Python3 基础教程',981,'2017-04-18 13:52:03'),
(3, 'Ruby 基础教程',199,'2017-05-01 06:16:14'),
(4, 'SQL 基础教程', 300,'2017-05-02 08:13:16');

INSERT INTO lession_vews(uniq, lesson_id, date_at, views) VALUES
(20170511000001,1,20170511,320),
(20170511000002,2,20170511,22),
(20170511000003,3, 20170511,49),
(20170512000001,1,20170512,220),
(20170512000002,2,20170512,12),
(20170512000003,3,20170512,63),
(20170513000001,1,20170513,441),
(20170513000002,2,20170513,39),
(20170513000003,3,20170513,87);

inner join使用

1. 假如只想查看lesson 和 lesson_views都有记录的数据，那么可以使用下面的 SQL 语句

select lesson.id,lesson.name,lesson_views.date_at, lesson_views.views from lesson inner join lesson_views on lesson.id=lesson_views.lesson_id order by lesson.id desc;

输出结果如下

mysql> select lesson.id,lesson.name,lesson_views.date_at, lesson_views.views from lesson inner join lesson_views on lesson.id=lesson_views.lesson_id order by lesson.id desc;
+----+---------------------+----------+-------+
| id | name                | date_at  | views |
+----+---------------------+----------+-------+
|  3 | Ruby 基础教程       | 20170511 |    49 |
|  3 | Ruby 基础教程       | 20170512 |    63 |
|  3 | Ruby 基础教程       | 20170513 |    87 |
|  1 | Python 基础教程     | 20170511 |   320 |
|  1 | Python 基础教程     | 20170512 |   220 |
|  1 | Python 基础教程     | 20170513 |   441 |
+----+---------------------+----------+-------+