学习笔记-MySQL（多表查询）

/*
	多表(两张表以上)查询
	交叉查询
	
	内连接
		隐式内连接
		显示内连接
		
	外连接
		左外连接
		右外连接
*/
/*
	交叉查询
	select * from A,B;
*/
SELECT * FROM category,products;
/*
	隐式内连接
*/
SELECT * FROM category ,products WHERE category.cid = products.category_id;
/*
	显示内连接
	A [inner] join b  on条件
*/
SELECT * FROM category INNER JOIN products  ON category.cid = products.category_id;
/*
	别名方式查询
*/
SELECT * FROM category c ,products p WHERE c.cid = p.category_id AND c.cid = 'c003';
SELECT * FROM category c INNER JOIN products p ON c.cid = p.category_id WHERE c.cid <>'c003';

/*
	左外连接:A left [outer] join B on 条件  左表有的数据 都得查询出来 如果右边没有 用null代替
	右外连接:A right [outer] join B on 条件  = B left join A  on条件
*/
SELECT * FROM category c LEFT JOIN products p ON c.cid = p.category_id;
SELECT * FROM products p LEFT JOIN category c ON c.cid = p.category_id;

/*
	子查询
	一条查询语句的结果
	作为另一条查询语句的一部分(条件)
	查询所有化妆品的记录
*/
SELECT  * FROM products WHERE  category_id = 'c003';
SELECT cid FROM category WHERE cname = '化妆品';
/*
	子查询查询所有化妆品记录
	第二条sql语句的结果 正好是第一条语句需要的条件
*/
SELECT  * FROM products WHERE  category_id =
  (SELECT cid FROM category WHERE cname = '化妆品');
/*
	查询化妆品 和 家电的记录
*/
SELECT  * FROM products WHERE  category_id  IN
  (SELECT cid FROM category WHERE cname = '化妆品' OR cname = '家电');
/*
	使用内连接查询化妆品的记录
*/
SELECT * FROM category c,products  p WHERE c.cid = p.category_id AND c.cname = '化妆品';
/*
	子查询 查询化妆品记录 使用临时表的方式
*/
SELECT * FROM products p,
(SELECT * FROM category WHERE cname = '化妆品') c
WHERE p.category_id = c.cid;

附：sql查询小练习

准备数据：

/*
	多表查询准备数据
	category 分类表
	products 商品表
	一对多
*/
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price DOUBLE,
  flag VARCHAR(2),				#是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

练习：

--  1、按员工编号升序排列不在10号部门工作的员工信息

   
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列

   
-- 3、求每个部门的平均薪水
 
   
-- 4、求各个部门的最高薪水
  
   
-- 5、求每个部门每个岗位的最高薪水
  
   
   
-- 6、求平均薪水大于2000的部门编号
   
  
-- 7、将员工薪水大于1200且部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
   

-- 8、求最高薪水的员工信息
  
   
-- 9、求多于平均薪水的员工信息
  

-- 10、求各个部门薪水最高的员工信息

== =========================================================
   
-- 1.  查询和Scott相同部门的员工姓名和雇用日期


-- 2.	查询工资比公司平均工资高的员工的员工号，姓名和工资。

-- 3.	查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
 
-- 4.	查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
	
-- 5. 	查询在部门的loc为 DALLAS 的部门工作的员工的员工号，
 
-- 6. 	查询管理者是King的员工姓名和工资 
  
-- 7.	查询所有部门的名字，loc，员工数量 和 工资平均值 ()