数据库基本操作二(子查询)

---

##子查询:单列单行(父查询用:in,not in,>,>=,<,<=,!=)

select ct_id from commoditytype where ct_name = '书籍';
	仅仅得到一个书籍的id
	+-------+
	| ct_id |
	+-------+
	|     3 |
	+-------+
	 
select c_name from commodity where c_type=( select ct_id from commoditytype where ct_name = '书籍');
	查询商品中所有书籍的名称

##子查询:单列多行(父查询用in,not in,>=any,>=all)

select ct_id from commoditytype where ct_name != '书籍';
	查询商品类型中除了书籍以外所有商品的id
	+-------+
	| ct_id |
	+-------+
	|     1 |
	|     2 |
	|     4 |
	+-------+
	查询除了出了书籍的所有商品
	select c_name from commodity where c_type in (select ct_id from                       commoditytype where ct_name != '书籍');
	 
	select * from table_a where sal IN (select sal from table_b;); 
		查询结果在返回字段结果内的

	select * from table_a where sal NOT IN (select sal from table_b;); 
		查询结果不在返回字段结果内的

	select * from table_a where sal >=ANY (select sal from table_b;); 
		查询结果在返回字段结果内任意满足

	select * from table_a where sal >=ALL (select sal from table_b;); 
		查询结果在返回字段结果内全部满足

注意:on后面其实跟的就是外键约束中相对应的两个列名！

##子查询:单行多列

select * from table_a where (sal,name) = (select sal,name from table_b where name = 'TomCat');

先从b表中查询出tomcat的售价,输出的结果有2个列属于单行多列,分别是售价和名字,那么再通过这两个值去查询和这两个值相等的所有商品；

注意：这里的返回值和查询的列名顺便必须一致！