SQL查询技巧精讲-优快云博客

一、子查询

1.简单的子查询

前面所用的sql语句都是只有一个select ，它们的条件都是些简单或者直接的可以使用的条件；

而有些复杂查询中的条件不能直接得到，需要从表中查询到结果；再将结果做条件，使用投影来查询最终结果。

select * from t_name where 条件2=(select 条件2 from t_name where 条件1);

2.子查询中使用运算符

all(子查询)，满足子查询中所有的值才为真；

SELECT s_name,s_sco FROM t_stu WHERE s_chlas=1 AND s_sco>ALL(
SELECT s_sco FROM t_stu WHERE s_class=2
);

any(子查询)，满足子查询的结果中任何一个即为真；

SELECT s_name,s_sco FROM t_stu WHERE s_chlas=1 AND s_sco>ANY(
SELECT s_sco FROM t_stu WHERE s_class=2
);

exists(子查询)，子查询只要有返回值即为真；它放在where之后

SELECT s_name,s_sco FROM t_stu
WHERE EXISTS(SELECT s_name FROM t_stu WHERE t_class=2);

二、集合操作UNION

集合操作有 union 并集没有重复，union all 保留重复行，intersect 交集同时出现的，except 差集只在一个集合中出现；except在oracle中是MINUS。

集合操作

select 列1，列2 from t_name1

union

select 列3，列4 from t_name2;

列1和列3对应，他们的数据类型可以转换，3转换成1；列2和列4对应，他们的数据类型可以转换，2转换成4。

2个select 语句中的列数量要相同，保留第一个语句的列名，而且他们的数据类型要可以转化或者相同。

列子：

/*
创建以下表  
 菜（dish）信息表  列包括
 1、编号列 主键 自动增长
 2、菜的名称
 3、菜的价格
 4、菜的出品日期
 5、菜的类别
 
 完成以下要求
     用sql把以下信息添加到菜信息表中，主键自动增长
      猪肉   16   2012-9-10   肉类
      羊肉   26   2012-9-1    肉类
      鸡肉   20   2012-9-6    肉类
      芹菜   6   2012-9-7    蔬菜类
      白菜   2   2012-9-8    蔬菜类
      冬瓜   7   2012-9-9    蔬菜类
      西瓜   2   2012-8-10   水果类
      橙子   6   2012-8-10   水果类
      火龙果 13  2012-8-10   水果类
      香蕉   5   2012-6-10   水果类
      
  1、 将所有的肉类涨价1块
  2、 查询价格在 4 到20 的所有商品信息
  3、 查询菜名字中包含有瓜的菜信息
  4、 查询价格少于5块的商品有哪些
  5、 查询商品表中一共有多少个商品
  6、 查询肉类的商品平均价格是多少
  7、 查询哪些商品的价格，超过了所有商品的平均价格
  8  查询查询每类商品的数量是多少
  9、查询和橙子有相同价格的商品有哪些
  10、查询香蕉的出品时间是
  11、查询商品数量最低的类别（获得具体是那类或者是哪些类）
  12、查询羊肉和冬瓜的价格差
  13、查询商品数量超过3个的类别的名称
*/
CREATE DATABASE data_new;
USE data_new;
DROP TABLE t_dish;
DELETE FROM t_dish;
TRUNCATE TABLE t_dish;
create table t_dish(
	d_id int primary key auto_increment,
	d_name varchar(50) not null,
	d_price int not null,
	d_date date not null,
	d_sort varchar(100) not null default '肉类'
)CHARSET='utf8';
INSERT INTO t_dish VALUES
	(NULL,'猪肉',16,'2012-9-10','肉类'),
	(NULL,'羊肉',26,'2012-9-1','肉类'),
	(NULL,'鸡肉',20,'2012-9-6','肉类'),
	(NULL,'芹菜',6,'2012-9-7','蔬菜类'),
	(NULL,'白菜',2,'2012-9-8','蔬菜类'),
	(NULL,'冬瓜',7,'2012-9-9','蔬菜类'),
	(NULL,'西瓜',2,'2012-8-10','水果类'),
	(NULL,'橙子',6,'2012-8-10','水果类'),
	(NULL,'火龙果',13,'2012-8-10','水果类'),
	(NULL,'香蕉',5,'2012-6-10','水果类');

--  1、 将所有的肉类涨价1块
UPDATE t_dish SET d_price=d_price+1 WHERE d_sort='肉类';

--  2、 查询价格在 4 到20 的所有商品信息
SELECT * FROM t_dish WHERE d_price BETWEEN 4 AND 20;

--  3、 查询菜名字中包含有瓜的菜信息
SELECT *FROM t_dish WHERE d_name LIKE '%瓜%';

--  4、 查询价格少于5块的商品有哪些
SELECT * FROM t_dish WHERE d_price<5;

--  5、 查询商品表中一共有多少个商品
SELECT COUNT(*) AS 商品总数 FROM t_dish;

--  6、 查询肉类的商品平均价格是多少
SELECT AVG(d_price) AS 肉类平均价格 FROM t_dish WHERE d_sort='肉类';

--  7、 查询哪些商品的价格，超过了所有商品的平均价格
SELECT AVG(d_price) AS 所有商品平均价 FROM t_dish;
SELECT d_name AS 大于平均价格的商品,d_price FROM t_dish HAVING d_price>(
	SELECT AVG(d_price) FROM t_dish);
	
--  8  查询每类商品的数量是多少
SELECT DISTINCT d_sort AS 类别,COUNT(*) AS 数量 FROM t_dish GROUP BY d_sort;

-- 9、查询和橙子有相同价格的商品有哪些
SELECT d_name AS 和橙子价格相同的商品,d_price FROM t_dish WHERE d_price=(SELECT d_price FROM t_dish WHERE d_name='橙子');

--  10、查询香蕉的出品时间是
SELECT d_date AS 香蕉的出品时间 FROM t_dish WHERE d_name='香蕉';

--  11、查询商品数量最低的类别（获得具体是那类或者是哪些类）
SELECT d_sort AS 个数最少的商品类,GROUP_CONCAT(d_name) AS 商品 FROM t_dish GROUP BY d_sort HAVING COUNT(*)<=
	ALL(SELECT COUNT(*) FROM t_dish GROUP BY d_sort);
	
--  12、查询羊肉和冬瓜的价格差
SELECT (SELECT d_price FROM t_dish WHERE d_name='羊肉')-(SELECT d_price FROM t_dish WHERE d_name='冬瓜')
	AS 羊肉和冬瓜的差价;
	
--  13、查询商品数量超过3个的类别的名称
SELECT d_sort AS 数量超过3个的类别,GROUP_CONCAT(d_name) AS 其中的商品 FROM t_dish GROUP BY d_sort HAVING COUNT(*)>3;