mysql 三表子查询_mysql数据库的多表查询，子查询，联合查询

/*多表查询*/

/*交叉连接*(表1条数*表2条数)*/

SELECT * FROM emp JOIN dept;

/*内连接(在交叉连接基础上加条件)*/

SELECT * FROM emp JOIN dept ON deptid=id;

SELECT * FROM emp JOIN dept ON emp.deptid=dept.id;

/*左外连接*(以左表为主表)*/；

SELECT * FROM product_type LEFT JOIN product ON product.`protype_id`=product_type.`protype_id`;

/*右外连接*(以右表为主表)*/；

SELECT * FROM product_type RIGHT JOIN product ON product.`protype_id`=product_type.`protype_id`;

/*子查询：把一个查询结果当作另一个查询的条件*/

/*找出索尼手机所属类别*/

SELECT protype_id FROM product WHERE pro_name LIKE '%索尼%4G手机%';

SELECT protype_name FROM product_type WHERE protype_id=(

SELECT protype_id FROM product WHERE pro_name LIKE '%索尼%4G手机%'

);

/*找出所有属于手机数码的产品*/

SELECT * FROM product WHERE protype_id=(

SELECT protype_id FROM product_type WHERE protype_name='手机数码'

);

/*找出所有属于手机数码或者电脑办公的产品*/

SELECT protype_id FROM product_type WHERE protype_name='手机数码' OR protype_name='电脑办公';

SELECT * FROM product WHERE protype_id IN(

SELECT protype_id FROM product_type WHERE protype_name='手机数码' OR protype_name='电脑办公'

);

/*联合查询*/

CREATE TABLE app1(

aid INT PRIMARY KEY AUTO_INCREMENT,

aname VARCHAR(10),

adesc VARCHAR(50)

);

CREATE TABLE bpp1(

bid INT PRIMARY KEY AUTO_INCREMENT,

bname VARCHAR(10),

bdesc VARCHAR(50)

);

INSERT INTO app1(aname,adesc) VALUES('熊大','保护森林');

INSERT INTO app1(aname,adesc) VALUES('光头强','砍树');

INSERT INTO bpp1(bname,bdesc) VALUES('小红帽','傻吊');

INSERT INTO bpp1(bname,bdesc) VALUES('大灰狼','牛逼');

SELECT aid,adesc FROM app1

UNION

SELECT bname,bdesc FROM bpp1;