数据表连接

-- 交叉连接 笛卡尔积
SELECT * from emp02 join dept;
-- 内连接  找出（过滤）在交叉连接的结果表中的表1的字段1的值等于表2的字段2的值的那些行。
SELECT * from emp02 join dept on emp02.did=dept.did;
SELECT * from emp02 join dept where emp02.did=dept.did;
-- 查询小红的部门经理
select mgr from emp02 join dept on emp02.did=dept.did where ename='小红';
-- 查询小蓝的部门电话
select phone from emp02 join dept on emp02.did=dept.did where ename='小兰';

-- 左外连接 内连接的查询结果+左表中不满足条件的数据，对应右边中字段自动补null
select * from product_type left join product on product.protype_id=product_type.protype_id;
-- 右外连接 内连接的查询结果+右表中不满足条件的数据，对应左边中字段自动补null
select *from product right join product_type on product.protype_id=product_type.protype_id;
-- 找出索尼4g手机所属类别名称
select protype_name from product join product_type on product.protype_id=product_type.protype_id where pro_name like('索尼%4G手机%');
select protype_name from  product_type where protype_id in (select protype_id from product where  pro_name like '索尼%4G手机%' )
-- 找出属于手机的数码产品
select pro_name from product join product_type on product.protype_id=product_type.protype_id where protype_name='手机数码';
select * from product where protype_id in (select protype_id  from product_type where
protype_name='手机数码');
/*子查询 把一个查询的结果当做另一个查询条件
 先按照条件查询主键
 再根据另一张表的外键查结果*/
-- 找出所有带电 字类别的产品

select pro_name from product where protype_id in (SELECT protype_id from product_type where protype_name like '%电%')

union 联合查询     [ALL | DISTINCT]     order  by .....  limit ....；

 

(select pro_name from product where protype_id in (SELECT protype_id from product_type where protype_name like '%电%') )union distinct(select  pro_name  from product join product_type on product.protype_id=product_type.protype_id where protype_name like '%电%')
order by pro_name desc
-- LIMIT 0,4;
LIMIT 4,4;

-- 创建副表product表
create table product(
	pro_id int primary key auto_increment,
	pro_name varchar(40),
	protype_id int,
	price double,
	pinpai varchar(20),
	chandi varchar(20),
	constraint ft_product_product_type foreign key (protype_id) references product_type(protype_id)
);
-- 创建主表product_type
create table product_type(
	protype_id int primary key auto_increment,
	protype_name varchar(20)
);
-- 添加主表数据
insert into product_type (protype_name) vaLUES('家用电器');
insert into product_type (protype_name) vaLUES('手机数码');
insert into product_type (protype_name) vaLUES('电脑办公');
insert into product_type (protype_name) vaLUES('图书音像');
insert into product_type (protype_name) vaLUES('家具家居');
insert into product_type (protype_name) vaLUES('服装配饰');
insert into product_type (protype_name) vaLUES('个护化妆');
insert into product_type (protype_name) vaLUES('运动户外');
insert into product_type (protype_name) vaLUES('汽车用品');
insert into product_type (protype_name) vaLUES('食品酒水');
insert into product_type (protype_name) vaLUES('营养保健');
-- 副表添加数据
insert into product (pro_name,protype_id,price,pinpai,chandi)values('康佳（KONKA）42英寸全高清液晶电视',1,1999,'康佳','深圳');
insert into product (pro_name,protype_id,price,pinpai,chandi)values('索尼(SONY)4G手机(黑色)',2,3238,'索尼','深圳');
insert into product (pro_name,protype_id,price,pinpai,chandi)values('海信(Hisense)55寸智能电视',1,4199,'海信','青岛');
insert into product (pro_name,protype_id,price,pinpai,chandi)values('联想(Lenovo)14.0英寸笔记本电脑',3,5499,'联想','北京');
insert into product (pro_name,protype_id,price,pinpai,chandi)values('索尼(SONY)13.3英寸触控超极本',3,11499,'索尼','天津');
insert into product (pro_name,protype_id,price,pinpai,chandi)values('索尼(SONY)60英寸全高清液晶电视',1,6999,'索尼','北京');
insert into product (pro_name,protype_id,price,pinpai,chandi)values('联想(Lenovo)14.0英寸笔记本电脑',3,2999,'联想','北京');
insert into product (pro_name,protype_id,price,pinpai,chandi)values('联想 双卡双待3G手机',2,988,'联想 ','北京');
insert into product (pro_name,protype_id,price,pinpai,chandi)values('惠普(HP)黑白激光打印机',3,1169,'惠普','天津');
-- 左外连接 内连接的查询结果+左表中不满足条件的数据，对应右边中字段自动补null
select * from product_type left join product on product.protype_id=product_type.protype_id;
-- 右外连接 内连接的查询结果+右表中不满足条件的数据，对应左边中字段自动补null
select *from product right join product_type on product.protype_id=product_type.protype_id; 
-- 找出索尼4g手机所属类别名称
select protype_name from product join product_type on product.protype_id=product_type.protype_id where pro_name like('索尼%4G手机%');
select protype_name from  product_type where protype_id in (select protype_id from product where  pro_name like '索尼%4G手机%' )
-- 找出属于手机的数码产品
select pro_name from product join product_type on product.protype_id=product_type.protype_id where protype_name='手机数码';
select * from product where protype_id in (select protype_id  from product_type where
protype_name='手机数码');
/*子查询 把一个查询的结果当做另一个查询条件
	先按照条件查询主键
	再根据另一张表的外键查结果*/
-- 找出所有带电 字类别的产品
(select pro_name from product where protype_id in (SELECT protype_id from product_type where protype_name like '%电%') )union distinct(select  pro_name  from product join product_type on product.protype_id=product_type.protype_id where protype_name like '%电%')
order by pro_name desc 
-- LIMIT 0,4;
LIMIT 4,4;
select *from product;
select *from product_type;