Shulou(Shulou.com)06/01 Report--

This paper gives an example of MySQL join query. Share with you for your reference, the details are as follows:

Create the table suppliers:

CREATE TABLE suppliers (s_id int NOT NULL AUTO_INCREMENT, s_name char 50) NOT NULL, s_city char 50 NULL, s_zip char 10 NULL, s_call CHAR 50 NOT NULL, PRIMARY KEY (s_id)) INSERT INTO suppliers (s_id, s_zip, s_call) VALUES (101 Inc.','Zhongshan','528437','11111' FastFruit Inc.','Tianjin','300000','48075'), (102 Supplies','Chongqing','400000','44333' Supplies','Chongqing','400000','44333'), (103 Set','Taiyuang','030000', good Set','Taiyuang','030000', '2222') (106 recording just Eat Ours','Beijing','010', '45678'), (107 recording DK Inc.','Zhengzhou','450000', '33332')

Internal connection

SELECT suppliers.s_id, f_priceFROM fruits, suppliersWHERE fruits.s_id = suppliers.s_id

Use inner join syntax for inner join queries

SELECT suppliers.s_id, f_priceFROM fruitsINNER JOIN suppliers ON fruits.s_id name, suppliers.s_id

Create the table orders:

CREATE TABLE orders (o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL, PRIMARY KEY (o_num)); INSERT INTO orders (o_num, o_date, c_id) VALUES (30001, '2008-09-01, 10001), (30002,' 2008-09-12, 10003), (30003, '2008-09-30, 10004), (30004,2008-10-03, 10005), (30005,2008-10-08, 1001)

Left connection

SELECT customers.c_id, orders.o_numFROM customers LEFT OUTER JOIN ordersON customers.c_id = orders.c_id

Right connection

SELECT customers.c_id, orders.o_numfrom customers RIGHT OUTER JOIN ordersON customers.c_id = orders.c_id

Compound conditional join query

SELECT customers.c_id, orders.o_numFROM customers INNER JOIN ordersON customers.c_id = orders.c_id AND customers.c_id = 10001th select suppliers.s_id, f_priceFROM fruits INNER JOIN suppliersON fruits.s_id = suppliers.s_idORDER BY fruits.s_id

[example .46] use an inner join query between the fruits table and the suppliers table to view the structure of the two tables before the query

SELECT suppliers.s_id, f_priceFROM fruits, suppliersWHERE fruits.s_id = suppliers.s_id

[example .47] use INNER JOIN syntax to query internal joins between fruits and suppliers tables

SELECT suppliers.s_id, f_priceFROM fruits INNER JOIN suppliersON fruits.s_id name, suppliers.s_id

[example. 48] inquire about other types of fruits provided by fruit suppliers supplying fanciidfruit A1'

SELECT f1.f_id, f1.f_nameFROM fruits AS F1, fruits AS f2WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1'

[example. 49] in the customers table and the orders table, query all customers, including those without orders. The SQL syntax is as follows

SELECT customers.c_id, orders.o_numFROM customers LEFT OUTER JOIN ordersON customers.c_id = orders.c_id

[example .50] in customers and orders tables, query all orders, including orders without customers

SELECT customers.c_id, orders.o_numfrom customers RIGHT OUTER JOIN ordersON customers.c_id = orders.c_id

[example. 51] in customers table and orders table, use INNER JOIN syntax to query the order information of customers with ID 10001 in customers table.

SELECT customers.c_id, orders.o_numFROM customers INNER JOIN ordersON customers.c_id = orders.c_id AND customers.c_id = 10001

[example. 52] use INNER JOIN syntax to make internal join queries between fruits table and suppliers table, and sort the query results

SELECT suppliers.s_id, f_priceFROM fruits INNER JOIN suppliersON fruits.s_id name, suppliers.s_idORDER BY fruits.s_id

More readers who are interested in MySQL-related content can check out this site's special topics: "A Summary of MySQL Common functions", "A Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.