MySQL之select语句

通用语法如下

SELECT column_name,column_name

FROM table_name

[WHERE Clause]

[LIMIT N][ OFFSET M]

• 查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
  
• SELECT 命令可以读取一条或者多条记录（一行或多行）
  
• 使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
  
• 使用 WHERE 语句来包含任何条件。
  
• 使用 LIMIT 属性来设定返回的记录数。
  
• 通过OFFSET指定SELECT语句开始查询的数据偏移量。即开始查询的位置，默认为0
  

limit用法

只给一个参数，表示记录数

# 检索前5条记录(1-5)

select*from yuangong limit5;

给两个参数，第一个参数表示offset（开始查询的位置，从0开始），第二个参数表示记录数

# 检索前5条记录(1-5)

select*from yuangong limit0,5;

另一种用法

# 查询第四条到第五条记录

select*from yuangong limit3,2;

select*from yuangong limit2offset3;

子查询

1. 定义

子查询是将一个查询语句嵌套在另一个查询语句中；在特定情况下，一个查询语句的条件需要另一个查询语句来获取，内层查询（inner query）语句的查询结果，可以为外层查询（outer query）语句提供查询条件。

2. 规范

1. 子查询必须放在小括号中
   
2. 子查询一般放在比较操作符的右边，以增强代码可读性
   
3. 子查询(小括号里的内容)可出现在几乎所有的SELECT子句中(如：SELECT子句、FROM子句、WHERE子句、ORDER BY子句、HAVING子句……)
   

3. 相关/不相关子查询分类

1. 标量子查询(scalar subquery)：返回1行1列（一个特定的值）
   
2. 行子查询(row subquery)：返回的结果集是 1 行 N 列
   
3. 列子查询(column subquery)：返回的结果集是 N 行 1列
   
4. 表子查询(table subquery)：返回的结果集是 N 行 N 列
   

一个子查询会返回一个标量（就一个值）、一个行、一个列或一个表，这些子查询称之为标量、行、列和表子查询

4. 操作符

可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS

1. 如果子查询返回一个标量值（就一个值），那么外部查询就可以使用：=、>、<、>=、<=和<>符号进行比较判断；
   
2. 如果子查询返回的不是一个标量值，而外部查询使用了比较符和子查询的结果进行了比较，那么就会抛出异常。
   

in

in常用于where表达式中，作用是查询某个范围内的数据

实例：

# 条件：d_name是人事部或科研部

# 在bumen表中查询出符合条件的d_id属性值

select d_id from bumen where d_name in('人事部','科研部');

any与all的区别

相同点：都是关键字，且不能单独使用，必须加上比较操作符

不同点：

1. x>Any 表示存在一个值使得x大于它，即x大于最小值（存在量词）
   
2. x>All 表示任意值都小于x。即x大于最大值（全称量词）
   

实例：从 yuangong 表中查询人事部和科研部的员工的信息。先从 bumen 表查询出人事部和科研部的部门号。然后到 yuangong 表中去查询员工的信息。

# 条件：d_name是人事部或科研部

# 在bumen表中查询出符合条件的d_id属性值

select d_id from bumen where d_name in('人事部','科研部');

# 下面三个查询语句作用相同

select*from yuangong where d_id=any(

   select d_id from bumen

   where d_name in('人事部','科研部'));

select*from yuangong where d_id in(

   select d_id from bumen

   where d_name in('人事部','科研部'));

select*from yuangong where d_id in(

   select d_id from bumen

   where d_name ='人事部'or d_name='科研部');

5. 不相关子查询

不相关，主查询和子查询是不相关的关系。也就是意味着在子查询中没有使用到外部查询的表中的任何列。先执行子查询，然后执行外部查询

1. 标量子查询

标量子查询结果是一个值。可以使用 = > < >= <= <> 操作符对子查询的结果进行比较：

mysql>select num,name

   ->from employee

   ->where d_id=(

   ->  　select d_id

   ->  　from department

   ->  　where d_name='科技部');

mysql>SELECT  playerno,town,sex

   ->FROM  PLAYERS

   ->WHERE(town,sex)=((SELECT town FROM PLAYERS WHERE playerno=7),

   ->                     (SELECT sex FROM PLAYERS WHERE playerno=44));

(列,列,…)叫做行表达式，比较时是比较列的组合

2. 行子查询

行子查询的结果集是1行N列

使用行表达式进行比较，可以使用 = > < >= <= <> in操作符

mysql> select * from employee

   -> where d_id in

   ->  　(select d_id from department);

解析：此处首先查询出department表中所有d_id字段的信息，并将结果作为条件，接着查询employee表中以d_id为条件的所有字段信息；NOT IN的效果与上面刚好相反。

3. 列子查询

列子查询返回1列N行，必须使用 IN、ANY 、 ALL 操作符对子查询返回的结果进行比较

注意：ANY 和 ALL 操作符不能单独使用，其前面必须加上单行比较操作符= > < >= <= <>

1. 带ANY关键字的子查询

ANY关键字表示满足其中任何一个

mysql> select * from employee

   -> where d_id !=any

   ->  　(select d_id from department);

2. 带ALL关键字的子查询

ALL关键字表示满足其中所有条件

mysql> select * from employee

   -> where d_id >=all

   ->  　(select d_id from department);

注意

如果子查询的结果集中有null值，使用>ALL 和not in操作符时，必须去掉子查询结果集中的null值，否则查询结果错误

4. 表子查询

返回的结果集是 N 行 N 列，必须使用 IN、ANY 和 ALL 操作符对子查询返回的结果进行比较

示例：在committee_members表中，得到任职日期和卸任日期与具有Secretary职位的一行相同的所有行

mysql> select *

   -> from COMMITTEE_MEMBERS

   -> where (begin_date,end_date) in

   -> (

   ->   select begin_date,end_date

   ->   from COMMITTEE_MEMBERS

   ->   where position='Secretary'

   -> );

6. 相关子查询

correlated subquery：在子查询中使用到了外部查询的表中的任何列。

先执行外部查询，然后执行子查询

相关子查询的执行步骤

1. 先执行外部查询，得到的行叫做候选行
   
2. 使用某个候选行来执行子查询
   
3. 使用子查询的返回值来决定该候选行是出现在最终的结果集中还是被丢弃
   
4. 重复以上步骤2和3，将所有的候选行处理完毕，得到最终的结果
   

示例：得到项目是‘研发产品’的雇员的编号

mysql> select num

   -> from employee

   -> where '研发产品'=(

   ->  　select function

   ->  　from department

   ->  　where d_id=employee.d_id);

+------+

| num  |

+------+

|    1 |

|    2 |

+------+

解析：

1. 主查询得到候选行，每行依次执行子查询
   
2. 主查询表employee的候选行的d_id和子查询的d_id匹配，返回值进行where过滤
   
3. 符合，加入最终结果集
   
4. 不符合，将候选行丢弃，接着进行处理下一个候选行
   

EXISTS关键字

exists表示存在，专门判断子查询的结果集是否不为空：

1. 非空空返回true
   
2. 空返回false
   

当返回的值为true时，外层查询语句将进行查询，否则不进行查询

EXISTS关键字可以与其他的查询条件一起使用，条件表达式与EXISTS关键字之间用AND或者OR来连接

mysql> select * from employee

   -> where age>24 and exists

   -> (select d_name from department where d_id=1003);

+------+------+--------+------+------+--------------------+

| num  | d_id | name   | age  | sex  | homeaddr           |

+------+------+--------+------+------+--------------------+

|    1 | 1001 | 张三    |   26 | 男   | 北京市海淀区         |

|    3 | 1002 | 王五    |   25 | 男   | 江西省赣州市         |

+------+------+--------+------+------+--------------------+