Phpmysql Tutorial: mysql connection query

Cartesian product: refers to the multiplication of the number of records of two tables without any conditions. it has a large amount of data. Therefore, we use conditions to restrict connections. Use commas to separate tables and then use the where condition

1. internal connection

Cartesian product: refers to the multiplication of the number of records of two tables without any conditions. it has a large amount of data. Therefore, we use conditions to restrict connections. Use commas to separate tables and then use the where condition

 select goods_id,goods_name,cat_name from goods,category where

 goods.cat_id = category.cat_id;

Common Writing methods:

Inner join ……where select goods_id,goods_name,cat_name from goods inner join 

category where goods.cat_id=categor.cat_idInner join ……onselect goods_id,goods_name,cat_name from goods inner join

 category on goods.cat_id=category.cat_idInner join ……usingselect goods_id,goods_name,cat_name from goods inner join category 

using(cat_id);

Further: query the product name, topic name, brand name, and price of each item? Tip: When using, make sure that the using fields have the same names in the two tables.

Join three tables,

 select goods_id,goods_name,cat_name,brand_name from goods inner join

 category using(cat_id) inner join brand using(brand_id);

2. left and right connections

 Lef join……on  <=> left outer join……on

For left join, use the table on the left of left join as the preparation for matching. if there is a match, the result is output. if there is no match, the result is filled with NULL. Left is very suitable for solving the problem of "which values are missing.

 select boy_name,girl_name from boy left join girl using(flower); select boy_name,girl_name from boy left join girl using(flower) where

 girl_name is null;

Note: Avoid ambiguity

3. Self-connection

A table is connected to itself.

For example, find the upper-level department of each department

The first step is to take an alias to distinguish different tables.

 select * from dept as dept_a inner join dept as dept_b;

Step 2: Find your own department:

 select * from dept as dept_a inner join 

dept as dept_b on dept_a.pid = dept_b.id;

Step 3: find the top-level department:

select dept_a.dept_name,dept_b.dept_name as top_dept from

 dept as dept_a left join 

dept as dept_b on dept_a.pid = dept_b.id having top_dept is null;

Self-connection is still very useful in actual development.