Mysql inner join on的用法实例(必看)

数据运维 2023-04-28 大猫手机阅读

语法规则 SELECT column_name(s)FROM table_name1INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name 先创建两个表，1.用户，2.用户类别用户表 CREATE TABLE user ( id int(32) NOT NULL AUTO_INCREMENT,

                        <p>语法规则 </p>

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

先创建两个表，1.用户，2.用户类别

用户表

CREATE TABLE user ( id int(32) NOT NULL AUTO_INCREMENT, name varchar(16) NOT NULL, kindid int(32) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

用户类别表

CREATE TABLE userkind ( id int(32) NOT NULL AUTO_INCREMENT, kindname varchar(16) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

插入一些数据到user表

INSERT INTO `user` VALUES (1,'小明',1),(2,'小红',1),(3,'涵涵',2);插入一些数据到 userkind表

INSERT INTO `userkind` VALUES (1,'普通会员'),(2,'VIP会员');

如图：

下面是控制台的查询例子：

Enter password: **** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.5.40 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use join; Database changed mysql> select * from user; +----+------+--------+ | id | name | kindid | +----+------+--------+ | 1 | 小明 | 1 | | 2 | 小红 | 1 | | 3 | 涵涵 | 2 | +----+------+--------+ 3 rows in set (0.00 sec)

mysql> select * from userkind; +----+----------+ | id | kindname | +----+----------+ | 1 | 普通会员 | | 2 | VIP会员 | +----+----------+ 2 rows in set (0.00 sec)

mysql> select * from user inner join userkind on user.kindid=userkind.id; +----+------+--------+----+----------+ | id | name | kindid | id | kindname | +----+------+--------+----+----------+ | 1 | 小明 | 1 | 1 | 普通会员 | | 2 | 小红 | 1 | 1 | 普通会员 | | 3 | 涵涵 | 2 | 2 | VIP会员 | +----+------+--------+----+----------+ 3 rows in set (0.02 sec)

mysql> select id as 用户ID,name as 用户名,kindname as 用户类别 from user inner join userkind where user.kindid=userkind.id; ERROR 1052 (23000): Column 'id' in field list is ambiguous mysql> select user.id as 用户ID,name as 用户名,kindname as 用户类别 from -> user inner join userkind where user.kindid=userkind.id; +--------+--------+----------+ | 用户ID | 用户名 | 用户类别 | +--------+--------+----------+ | 1 | 小明 | 普通会员 | | 2 | 小红 | 普通会员 | | 3 | 涵涵 | VIP会员 | +--------+--------+----------+ 3 rows in set (0.00 sec)

mysql> select user.id as 用户ID,name as 用户名,kindname as 用户类别 from user inner join userkind on user.kindid=userkind.id; +--------+--------+----------+ | 用户ID | 用户名 | 用户类别 | +--------+--------+----------+ | 1 | 小明 | 普通会员 | | 2 | 小红 | 普通会员 | | 3 | 涵涵 | VIP会员 | +--------+--------+----------+ 3 rows in set (0.00 sec)