子查询

子查询也被称为内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。
ps：子语句可以与主语句所查询的表相同，也可以是不同表

语法

IN 用来判断某个值是否在给定的结果集中，通常结合子查询来使用

<表达式> [NOT] IN <子查询>

当表达式与子查询返回的结果集中的某个值相等时，返回TRUE，否则返回 FALSE。若启用了 NOT 关键字，则返回值相反。需要注意的是，子查询只能返回一列数据，如果需求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。多数 情况下，子查询都是与 SELECT 语句一起使用的

相同表示例

select name,score from info1 where id in (select id from info1 where score >80);
上述语句
主语句：select name,score from info1 where id
子语句（集合）：select id from info1 where score >80

ps：子语句中的sql语句是为了，最后过滤出一个结果集，用于主语句的判断条件
in ：将主表和子表关联/连接的语法

不同表/多表示例

select name,score from info1 where id in (select id from class3 where score >80);

子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。子查询的结果集可以通过 INSERT 语句插入到其他的表中

将class3表里面的数据全部删除，重新插入info1表的记录

select * from class3;
delete from class3;
insert into class3 select * from info1 where id in (select id from info1);

UPDATE语句也可以使用子查询。UPDATE内的子查询，在set更新内容时，可以是单独的一列，也可以是多列

update info1 set score=52 where id in (select id from class3 where id=2);

DELETE 也适用于子查询

delete from info1 where id in (select id where score>80);

在IN前面还可以添加NOT，其作用与IN相反，表示否定（即不在子查询的结果集里面）删除方式不是大于等于80的记录

select id,name,score from class3;
delete from class3 where id not in (select id where score>=80);

子查询（exists）

EXISTS 这个关键字在子查询时，主要用于判断 exists 之后的条件是否成立，如果成立，则正常执行主语句的匹配，如不成立，则不会执行主语句查询，如子查询结果集不成立的话，输出为 null。
注：count 为计数，sum 为求和，使用 sum 求和结合 exists，如子查询结果集不成立的话，输出为 null

查询如果存在分数等于 80 的记录则计算 info1 的字段数

select count(*) from info1 where exists(select id from info1 where score=80);

子查询，别名as

查询info1表id，name字段

select id,name from info1;

将结果集作为一张表进行查询的时候，我们也需要用到别名
从info1表中的id和name字段的内容作为“内容”输出id的部分

select id from (select id,name from info1);
ERROR 1248 (42000): Every derived table must have its own alias
 
##报错原因
select * from 表名 此为标准格式，而以上的查询语句，"表名" 的位置其实是一个结果集，
mysql 并不能直接识别，而此时给与结果集设置一个别名，以 "select a.id from a" 的方式查询将此结果集是为一张 "表"，就可以正常查询数据了，如下：
 
select a.id from (select id,name from info1) a;
相当于
select info1.id,name from info1;
select 表.字段，字段 from 表；

视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。
数据库中的虚拟表不包含真实数据，只是做了映射。镜花水月/倒影，动态保存结果集。

作用

• 使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件
• 增加数据的安全性，通过视图，用户只能查询和修改指定的数据
• 提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响
  总结：使用视图的大部分情况是为了保障数据安全性，提高查询效率
  通过视图定义展示的条件

需求

满足80分的学生展示在视图中（这个结果会动态变化，同时可以给不同的人群展示不同的视图）

create view v_score as select * from info1 where score>=80;
#创建名为 v_score 的视图
show table status\G
 
##查看视图
select * from v_score;

修改原表数据并查看视图的变化情况

update info1 set score='85' where name='wangwu';
 
##查看视图
select * from v_score;

总结：从两次视图表的查询结果可以看到，随着原表数据的变化，视图的数据也在实时变化。需要注意的是，不仅改原表视图会变，改视图原表也会变。

NULL值

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是 NULL 值。

null 值与空值的区别

• 空值长度为 0，不占空间；NULL 值的长度为 null，占用空间
• is null 无法判断空值
• 空值使用 “=” 或者 “<>” 来处理（！=）
• count() 计算时，NULL 会忽略，空值会加入计算
  插入一条记录，分数字段输入null，显示出来就是null

alter table info1 add column addr varchar(50);
update info1 set addr='nanjing' where score >=70;

统计数量：检测null是否会加入统计中

select count(addr) from info1;

将info1表中其中一条数据修改为空值

update info1 set address='' where name='wangwu';

统计数量，检查空值是会被添加到统计中

select count(address) from info1;

查询null值

select * from info1 where addr is null;
 
空值数据：select count(*) from YourTable where Your Column Name is null;

查询不为空的值

select * from info1 where address is not null;

运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符。

算术运算符

比较运算符

等号（=）
是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1(true)，如果不相等则返回 0（flase)。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同；
字符串（字母）比较：（‘a’>‘b’）其实比较的就是底层的 ASCII 码；
需要关注的是 ASCII 码有：a、A、0（97、65、48）；
与 linux 返回值表达相反，linux 中运行正常返回值是 0，运行异常返回值是非 0。

• 如果两个都是整数，则按照整数值进行比较
• 如果一个整数一个字符串，则会自动将字符串转换为数字，在进行比较。（在程序中，一般是不会把这两者进行想比较的）
• 如果两者都是字符串，则按照字符串进行比较
• 如果两者中至少有一个值是NULL，则比较的结果是NULL。

不等于（<>,!=）
不等于号有两种写法，分别是<>或者!=，用于针对数字，字符串和表达式不相等的比较。如果不相等则返回1，如果相等则返回0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断NULL

大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回1，否则返回0，同样不能用于判断NULL。
小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回1，否则返回0，同样不能用于判断NULL。
大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回0，不能用于判断NULL。
小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回0，不能用于判断NULL。


BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。
例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间

Beyween and 覆盖的范围是 >= 和 <= 关系

least 和 greatest（取最小值、取最大值）
LEAST：当有两个或多个参数时，返回其中的最大值，如果其中一个值为NULL，则返回结果就为NULL。
GREATEST：当有两个或多个参数时，返回其中的最大值，如果其中一个值为NULL，则返回结果就为NULL。
若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和 GREATEST 来实现

IN 判断一个值是否在对应的列表中，如果是返回1，否则返回0。
NOT IN 判断一个值是否不在对应的列表中，如果不是返回1，否则返回0

*LIKE 用来匹配字符串，如果匹配成功则返回1，反之返回0
LIKE支持两种通配符:'%'用于匹配任意数目的字符（匹配的是前面一个字符），而’_'只能匹配一个字符。
NOT LIKE 正好跟LIKE相反，如果没有匹配成功则返回1，反之返回0。

逻辑运算符（布尔值）

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回1，否则返回0，真和假也可以用 TRUE 和 FALSE 表示。MySQL中支持使用的逻辑运算符有四种。

逻辑非

逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用NOT 或 ! 表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果NOT 后面的操作数为0时，所得值为1；如果操作数为非0时，所得值为0；如果操作数为NULL时，所得值为NULL。

逻辑与（and）

当所有的操作都为非0值且不为null时，返回值为1，否则为0
（null与0比较特殊）
逻辑与使用 AND 或者 && 表示

由结果可以看出
and 和 && 的作用相同
1 and -1 没有0 或 null，所以返回值为1
1 and 0 中有0，所以返回值为0
1 and null 有null，所以 返回值为null
null and 0 返回值为0

逻辑或（or）

逻辑或通常使用 or
逻辑或表示包含的操作数，任意一个为非零值且不是 NULL 值时，返回1，否则返回0。
当有一个操作数为null时，如果另一个操作数为非0值，则返回值为1，否则为null
如两个操作数均为null，则返回值为null

使用或运算符or进行逻辑判断

逻辑异或（xor）

两个非NULL 值的操作数，如果两者都是0或者都是非0，则返回0；
如果一个为0，另一个为非0，则返回结果为1；
当任意一个值为NULL时，返回值为NULL

位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL内运算会先将操作数变为二进制格式（10101111），然后进行运算，最后在将计算结果
从二进制变回到十进制格式，方便用户查看。MySQL支持6 种位运算符，具体如表所示。

10转换位二进制数是 1010，15转换位二进制是1111

• 按位与运算（&），是对应的二进制位都是1的，它们的运算结果位1，否则为0，所以 10 & 15 结果位10。
• 按位或运算（|），是对应的二进制位有一个或两个位1的，运算结果位1，否则为0，所以10 | 15 的结果为 15。
• 按位异或运算（^），是对应的二进制位不相同时，运算结果1，否则为0，所以10 ^ 15 的结果为5
• 按位取反（~），是对应的二进制数逐位反转，即1取反后变为0，0取反变为1。数字1的二进制是0001，取反后变成1110，数字5的二进制是0101，将1110和0101进行求与操作，其结果是二进制的0100，转换为十进制就是4

以上不管哪种运算符，在使用过程中都会有优先级问题，运算符的优先级决定了不同的运算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同，MySQL会按照顺序从左到右依次进行计算，优先级如下表所示

连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的 共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接

1、内连接

• MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。
• 通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用 关键字 JOIN。
• 同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表
  内连查询：通过inner join的方式将俩张表指定的相同字段的记录行输出出来

select 表名1.字段1,表名1.字段2 from 表名1 inner join 表名2 on 表名1.字段 = 表名2.字段;

2、左连接

• 左连接也可以被称为左外连接，在FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。
• 左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

select * from info1 left join class3 on info1.name=class3.name;

3、右连接

• 右连接也被称为右外连接，在FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。
• 右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。

select * from info1 right join class3 on info1.name=class3.name;