Mysql inner join on usage example (mandatory), innerjoin

Mysql inner join on usage example (mandatory), innerjoin

Syntax Rules

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

Create two tables first, 1. User, 2. user category

User table

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;

User category table

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

Insert some data to the user table

Insert into 'user' VALUES (1, 'xiaoming ', 1), (2, 'xiaohong', 1), (3, 'Han Han', 2 ); insert some data to the userkind table

Insert into 'userkind' VALUES (1, 'ordinary member '), (2, 'vip member ');

The following is an example of a console query:

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) Copyright (c) 2000,201 4, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> use join; Database changedmysql> select * from 'user '; + ---- + ------ + -------- + | id | name | kindid | + ---- + ------ + -------- + | 1 | Xiao Ming | 1 | 2 | Xiao Hong | 1 | 3 | Han | 2 | + ---- + ------ + -------- + 3 rows in set (0.00 sec) mysql> select * from 'userkind '; + ---- + ---------- + | id | kindname | + ---- + ---------- + | 1 | normal member | 2 | VIP member | + ---- + ---------- + 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 | James | 1 | 1 | common member | 2 | Xiaohong | 1 | 1 | common member | 3 | Han | 2 | 2 | VIP member | + ---- + ------ + -------- + ---- + ---------- + 3 rows in set (0.02 sec) mysql> select 'id' as 'user id', 'name' as 'username', 'kindname' as 'user category 'from 'user' inner join 'userkind' where user. kindid = userkind. id; ERROR 1052 (23000): Column 'id' in field list is ambiguousmysql> select 'user '. 'id' as 'user id', 'name' as 'username ', 'kindname' as 'user Class' from-> 'user' inner join 'userkind' where 'user '. 'kindid' = 'userkind '. 'id '; + -------- + ---------- + | user ID | user name | user type | + -------- + ---------- + | 1 | Xiaoming | common member | 2 | Xiaohong | common member | 3 | Han | VIP member | + -------- + ---------- + 3 rows in set (0.00 sec) mysql> select 'user '. 'id' as 'user id', 'name' as 'username', 'kindname' as 'user Class' from 'user' inner join 'userkind' on 'user '. 'kindid' = 'userkind '. 'id '; + -------- + ---------- + | user ID | user name | user type | + -------- + ---------- + | 1 | Xiaoming | common member | 2 | Xiaohong | common member | 3 | Han | VIP member | + -------- + ---------- + 3 rows in set (0.00 sec) mysql>

Note that:The on here is basically equivalent to where (I think)

When both the column (field) tables are separated but not clear, you need to use 'table name'. 'field name' to distinguish.

As is the alias. Let's see the example above!

The above Mysql inner join on usage example (mandatory) is all the content shared by the editor. I hope to give you a reference, and I hope you can provide more support for the customer's house.