二、由[NOT]IN引发的子查询

1、使用[NOT]IN的子查询

语法结构：operand comparison_operator [NOT] IN (subquery）=ANY运算符与IN等效。

！=ALL或<>ALL运算符与NOT IN等效。

使用比较运算符的子查询

=、>、<、>=、<=、<>、!=、<=>

语法结构

operand comparison_operator subquery

SELECT AVG(goods_price) FROM tdb_goods;#查找表中商品的平均价格

SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;#查找表中商品的平均价格,并四舍五入保留两位小数。

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >=5636.36;

#查询表中价格大于等于平均价格的goods_id,goods_name,goods_price

把上上一个求平均价格的sql语句代入>=后面，用（）包裹成子查询语句：

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

用ANY、SOME或ALL修饰的比较运算符

operand comparison_operator ANY (subquery)

operand comparison_operator SOME (subquery)

operand comparison_operator ALL (subquery)

其中ANY跟SOME是等价的，满足其中一条就可以，ALL必须要满足所有条件

 SELECT goods_id,goods_name,goods_price FROM tdb_goods  WHERE goods_price >= ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC; # 查找价格比所有的超级本类型的商品价格还要大的商品

  #-- 查询价格大于或等于"超级本"价格的商品，并且按价格降序排列

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;

#-- 查询价格等于任意"超级本"价格的商品，并且按价格降序排列

子查询（Subquery)是指出现在其他SQL语句内的SELECT子句

例如：

SELECT * FEOM t1 WHERE col1 = (SELECT col2 FROM t2);

其中SELECT * FROM t1 成为Outer Query/Outer Statement

SELECT col2 FROM t2,成为SubQuery

子查询指嵌套在查询内部，且必须始终出现在圆括号内

子查询可以包含多个关键字或条件

        如DISTINCT， GROUP BY， ORDER BY， LIMIT ，函数等。

子查询的外层查询可以是SELECT, INSERT,UPDATE,SET或DO

子查询可以返回标量、一行、一列或子查询。

查看准备数据 

（1）SELECT * FROM tdb_goods\G; // 网格形式输出查询结果

（2）由于存储时使用的编码方式为utf-8格式；客户端默认的为GBK格式，所以需要使用 SET NAMES GBK; 设置读取信息的编码格式。（存储数据的属性没有不变化）

解决乱码的问题：

1.show variables like "%char%";

类似set character_set_results=utf8;，能改的都改为uft8

2.在要创建的表后加ENGINE=InnoDB DEFAULT CHARSET=utf8;

解决乱码的问题：

----------------------------------------------------------------------------------------------------------------------------------------

1.MySQL中默认字符集的设置有四级:服务器级，数据库级，表级。最终是字段级的字符集设置。注意前三种均为默认设置，并不代表你的字段最终会使用这个字符集设置。可以通过how create table table;或show full fields from tableName;来检查当前表中字段的字符集设置。

2.表创建后，再修改字段的字符集设置不影响已经插入的数据.

所以才会出在查看字段时出现中文乱码.此时就只能用:set NAMES‘utf8/gbk(用字段插入是所用的相应代码,且这个命令行并不影响服务器端数据)来查看中文内容 

当数据库和数据表的编码不一样时，可能插入中文会出现乱码！排插步骤如下:(以utf8为例)

第一步：SHOW CREATE DATABASE XXXX;（我的是SHOW CREATE DATABASE test;）

查看你创建的数据库是否是:DEFAULT CHARACTER SET utf8

第二步：show create table xxxx

查看你创建的数据表是否是DEFAULT CHARSET=UTF8

第三步:

打开my.ini

在[client]节点下添加

       default-character-set=utf8

    在[mysqld]节点下添加

       character-set-server=utf8

      collation-server=utf8_general_ci保存，重启mysql！

再 mysql>show variables like 'char%';查看是否是下面一样：

|character_set_client|utf8 

|character_set_connection|utf8 

|character_set_database|utf8 

|character_set_filesystem|binary

|character_set_results|utf8 

|character_set_server|utf8 

|character_set_system|utf8 

|character_sets_dir|/opt/mysql-cluster/share/mysql/charsets/

按以上步骤执行后，重新创建数据库imooc和表tdb_goods，就可以插入中文了!

数据记录的操作：简单的增、删、改、查

INSERT:可以通过三种方法进行记录的插入，分别为普通INSERT、INSERT SET、INSERT SELECT语句

insert [into] 表名 [字段名] {value|values}(...,...,...,);

insert [into] 表名 set col_name={expr|default},...

insert [into] tbl_name [(col_name,...)] select ...

UPDATE：实际有两种更新语句，单表更新和多表更新（详见第五章)

UPDATE [ LOW_PRIORITY] IGNORE] table_reference SET col_name1={ expr1lDEFAULT] L, col_name2={ expr2lDEFAULT].…

[ WHERE where_condition]

DELETE:存在两种语法结构，单表删除、多表删除。

单表删除：DELETE FROM tbl_name [WHERE where_condition]

select ：

SELECT select expr L, select_expr..J FROM table_references

[ WHERE where_condition] 查询条件

[ GROUP BY { col_name I position}[ ASC IDESC].…] 进行记录的分组

[ HAVING where_condition]对分组进行条件的设定

[ ORDER BY { col name I expr I position}[ ASC | DESC],.…]对结果进行排序 

[ LIMIT{[ offset,] row_count I row_count OFFSET offset)]限制返回结果的数量

限制查询结果返回的数量

[ LIMIT { [ offset, ] row_count OFFSET offset } ]

SELECT * FROM users LIMIT 2;返回前两条数据

SELECT * FROM users LIMIT 2，2;是指从下标2（下标从0开始算）（即是第三条记录开始）开始，返回两条记录

insert [into] 表名 set 字段={...},...

INSERT test (username) SELECT username FROM users WHERE age>=30;

将users表中年龄大于等于30的结果插入test表中

insert 目的表（字段名） select 字段名 from 原表名 where 另一字段 >=30;   把select后查出表中满足条件的字段数据插入到目的表中

limit 0，2     ---0代表第一行，2代表显示几行

对查询结果进行排序

[ORDER BY { col_name | expr | position }[ ASC | DESC ], ... ]

order by语句对查询结果排序：

SELECT * FROM users ORDER BY id DESC;查询结果按id的值降序排序

SELECT * FROM users ORDER BY age,id DESC;查询结果按age升序，id的值降序排序，当前一个条件不能满足时才按第二个条件进行排序（如第一个条件的查询结果字段值相等）。

分组条件

[ HAVING where_condition ]

having语句设置分组条件：

可以只对某一部分进行分组

SELECT sex,age FROM users GROUP BY 1 HAVING age >35;

having语句规定having后面的要么是一个聚合函数，要么该字段需要在select后面的字段中出现，否则会报错

聚合函数则max、min等

查询结果分组 GROUP BY 

 [GROUP BY {col_name|position} [ASC|DESC],...] 

ASC：升序，默认 

DESC：降序 

position：SELECT语句中列的序号 

mysql中GROUP BY语句对查询结果分组：

SELECT sex FROM users GROUP BY sex;   #按照sex字段进行分组查询

SELECT sex FROM users GROUP BY 1； #按照目标分组字段sex在select语句中出现的顺序的进行分组查询

一般去指定字段名称而非位置

条件表达式

对记录进行过滤，如果没有指定WHERE子句，则显示所有记录

在WHERE表达式中，可以使用MySQL支持的函数或运算符

查找记录

SELECT select_expr [,select_expr...]

[

FROM table_references

[WHERE where_condition]

[GROUP BY {col_name|position}] [ASC|DESC],...]

[HAVING where_condition]

[ORDER BY{col_name|expr|position}[ASC|DESC],...]

[LIMIT{[offset,]row_count|row_count OFFSET offset}]

]

查询表达式 select_expr

每一个表达式表示想要的一列，必须有至少一个

多个列之间英文逗号分隔

星号(*)表示所有列。tbl_name.*可以表示命名表的所有列

查询表达式可以使用[AS]alias_name为其赋予别名

别名可用于GROUP BY,ORDER BY或HAVING子句

1、查询表达式：

SELECT username,id FROM users;

SELECT id AS userid FROM users;

select查询语句中字段的顺序将会影响查询结果的字段顺序；

select查询语句中字段的别名将会影响查询结果的字段名称；

AS可以省略，但是最好不省略

删除记录(单表删除)

DELETE FROM tbl_name [WHERE where_condition]

Demo: 

DELETE FROM users WHERE id = 6;

1、删除记录DELETE：分为单表删除和多表删除 

2、单表删除：DELETE FROM tbl_name [WHERE where_conditon];

3、若不添加WHERE则删除【全部记录】 

删除后再插入，插入的id号从最大的往上加，而不是填补删除的

更新记录(单表更新)

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr|DEFAULT}[,col_name2={expr|DEFAULT}]...[WHERE where_condition]

UPDATE users set age+=5;   #所有的记录年龄都改成原来的年龄加5

UPDATE users set age=age-id ,sex=0;  #同时更新age和sex字段

 UPDATE users set age=age+10 where id%2=0;  #带条件的更新

插入记录

INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},..

与第一种方式区别在于，此方法可以使用子查询(SubQuery)

此方法只能一次性插入一条记录

INSERT SET-SELECT语句：

可以使用子查询，但是一次性只能插入一条记录

将A表语句插入到B表中

INSERT B SELECT 语句名 FROM A [WHERE 条件]