7_mysql查询之where子句

where子句会根据条件对from子句的中间结果中的行，一行一行地进行判断，当条件为True的时候，一行就被包含到where子句的中间结果中。

1. 比较运算
   ① MySQL支持的比较运算符有：=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<=>（相等或都等于空）、<>（不等于）、!=（不等于）
   ② 当两个表达式均不为空值(Null)的时，除了" <=> "运算符，其它比较运算返回逻辑值True或False；而当两个表达式值中有一个为空值或都为空值时，将返回UNKNOWN
   ③ Mysql有个特殊的等于运算符" <=> "，当两个表达式彼此相等或都等于空值时，它的值为True，其中一个空值或都是非空值但不相等，这个条件就是False。没有UNKNOWN的情况。
   举例：查询学生表中年龄大于18岁的男生
   select * from student where Sage > 18 and Ssex = "男";
2. 模式匹配

• like运算符
  特殊符号："%"代表0个或多个字符；"_"代表单个字符
  指定转义字符关键字：escape
  举例一：查询xscj数据库xs表中，学号倒数第二个数字为0的学生的学号、姓名及专业名。

select 学号,姓名,专业名    
from xs
where 学号 like '%0_';

举例二：查询xs表中名字包含下画线的学生学号和姓名。

# 定义了"#"为转义字符以后，语句中在"#"后面的"_"就失去了它原来的特殊意义。
select 学号,姓名
from xs
where 学号 like '%#_%' escape '#';

• regexp运算符
  简单来说，就是正则匹配，REGEXP运算符的一个同义词是RLIKE。
  举例：查询学号以08开头，以08结尾的学生学号、姓名和专业名

select 学号,姓名,专业名    
from xs
where 学号 regexp '^08.*08$';

3. 范围比较
   用于范围比较的关键字有两个：BETWEEN和IN。
   BETWEEN A AND B 包含 A、B两个值
   IN 使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。
   举例一：查询xscj数据库xs表中不在1993年出生的学生情况。

select  学号, 姓名, 专业名, 出生日期
from xs
where 出生日期 not between '1993-1-1' and '1993-12-31';

举例二：查询xs表中专业名为“计算机”、“通信工程”或“无线电”的学生的情况。

select *
from xs
where 专业名 in  ('计算机', '通信工程', '无线电');

该语句与下句等价：

select *
from xs
where 专业名 ='计算机' or 专业名 = '通信工程' or 专业名 = '无线电';

4. 空值比较
   当需要判定一个表达式的值是否为空值时，使用IS NULL关键字
   举例：查询xscj数据库中总学分尚不定的学生情况

select *
from xs
where 总学分 is null;

5. 子查询
   ① 在查询条件中，可以使用另一个查询的结果作为条件的一部分
   ② 子查询可以用在select、insert、update、delete语句中
   ③ 子查询通常与 IN、EXIST谓词及比较运算符结合使用

• IN子查询
  IN子查询用于进行一个给定值是否在子查询结果集中的判断
  举例：查找在xscj数据库中选修了课程号为206的课程的学生的姓名、学号

select 姓名,学号
from xs
where 学号 in
    ( select 学号 
        from xs_kc  
        where 课程号 = '206' 
    );

解释！！！(超重要)：
（1）先执行where查询语句中的子查询，得到了选修课程号为206的所有学生的学号集(IN子查询只能返回一列数据，这里的in子查询得到了一个只含有学号列的表，对于比较复杂的查询，可以使用嵌套的子查询)；
（2）然后再进行外查询，对xs中的每条记录，判断其学号字段值是否在前面所求的学号集合中，在！IN谓词会返回True，将此记录的姓名、学号值取出作为where的结果集的一行，不在！IN谓词返回False，继续处理下一条记录；
（3）对xs表筛选处理完成后，将最终结果集进行显示。

• 比较子查询
  { < | <= | = | > | >= | != | <> } { ALL | SOME | ANY }
  ① 如果子查询的结果集只返回一行数据时，可以通过比较运算符直接比较
  ② ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时才返回TRUE，否则返回FALSE。
  ③ SOME或ANY是同义词，表示表达式只要与子查询结果集中的某个值满足比较的关系时候就返回true，否则返回false。
  举例一：查找选修了离散数学的学生学号。

select 学号  
from xs_kc
where 课程号 =
    (   select 课程号
        from kc
        where 课程名 ='离散数学'
    );

举例二：查找xs表中比所有计算机系的学生年龄都大的学生学号、姓名、专业名、出生日期。

select 学号, 姓名, 专业名, 出生日期
from xs     
where 出生日期 <all
    (   select 出生日期
        from xs
        where 专业名 ='计算机'
    );

举例三：
查找xs_kc表中课程号206的成绩不低于课程号101的最低成绩的学生的学号。

select 学号   
from xs_kc
where 课程号 = '206' and 成绩 >=any 
    (   select 成绩
        from xs_kc
        where 课程号 ='101'
    );

• EXISTS查询
  EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。
  举例：查找选修206号课程的学生姓名。

select 姓名    
from xs
where exists
    (   select *
        from xs_kc
        where 学号 = xs.学号 and 课程号 = '206' 
    );

解释：
（1）本例子在子查询的条件中使用了限定形式的列名引用xs.学号，表示这里的学号列出自表xs；
（2）前面的例子中，内层查询只处理一次，得到一个结果集，在依次处理外层查询；而本例子的内层查询要处理很多次，因为xs.学号列有不同的学号值；
（3）这类子查询称为相关子查询，因为子查询的条件依赖于外层查询中的某些值；
（4）处理过程：首先查找外层查询中xs表的第一行，根据该行的学号列值处理内层查询，若结果不为空，则where条件就为真，就把该行的姓名值取出来作为结果集的一行；然后再找xs表的第2、3、...行，重复上述处理过程，直到xs表的所有行都查找完为止。

---

mysql区分了四种类型的子查询：表子查询、行子查询、列子查询、标量子查询。

• 表子查询
  可以用在FROM子句中，但必须为子查询产生的中间表定义一个别名。
  举例：从xs表中查找总学分大于50分的男同学的姓名和学号。

select 姓名,学号,总学分
from    ( 
        select 姓名,学号,性别,总学分
        from xs
        where 总学分>50
        ) as student
where 性别='1';

• 标量子查询
  SELECT关键字后面也可以定义子查询
  标量子查询返回值只有一个值
  举例：从xs表中查找所有女学生的姓名、学号，以及与081101号学生的年龄差距。

# 这里的YEAR函数用于取出DATE类型数据的年份
select 学号, 姓名, year(出生日期)-year(
        (   select 出生日期
            from xs 
            where 学号='081101'
        )  
)   as 年龄差距
from xs
where 性别='0';

• 行子查询
  在WHERE子句中还可以将一行数据与行子查询中的结果通过比较运算符进行比较。
  举例：查找与081101号学生性别相同、总学分相同的学生学号和姓名。

select 学号,姓名 
from xs 
where  (性别,总学分)=( 
                    select 性别,总学分
                    from xs
                    where 学号 = '081101'
                    );

---