七、 查询数据

• 基本查询语句，select语句的基本格式是：
  select   {*|<字段列表>}
       [
               from <表1>,<表2>...
               [where <表达式>
               [group by <goup by definition>]
               [having <expression> [{<operator> <expression>}...]]
               [order by <order by definition>]
               [limit [<offset>,]<row count>]
       ]
  select [字段1，字段2，...，字段n] from [表或视图] where [查询条件]
• 单表查询
      distinct关键字应用于所有列而不仅是它后面的第一个指定列
      带in关键字的查询语句可以实现带or的多条件查询语句，并且使用in执行的速度快于or，更重要使用in操作符，可以执行更加复杂的嵌套查询。如下：

  select * from table1 where s_id=101 or s_id=102;
  <=>select * from table1 where s_id in(101,102)

  对查询结果进行排序：order by 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
  分组查询：分组查询是对数据按照某个或多个字段进行分组：基本语句形式为：
  [group by 字段] [having <条件表达式>]
  字段值为进行分组时所依据的列名称；having<条件表达式>指定满足表达式限定条件的结果将被显示。
  1、创建分组
  group by 关键字通常和集合函数一起使用。集合函数只能对结果集使用，这个时候就是集合函数对分成的逻辑结果组进行集合计算。
  MySQL中可以在group by字节中使用group_concat(字段)函数,将每个分组中各个字段的值显示出来。【如果字段值为null，则结果为null】
  2、使用having 过滤分组
  group by 可以和having一起限定显示记录所满足的条件，只有满足条件的分组才会被显示。
  注意： having关键字与where关键字都是用来过滤数据，两者有什么区别？
      having在数据分组之后进行过滤来选择分组（查询后），而where在分组之前用来选择记录（查询前）。另外where排除的记录不再包括在分组中。
  3、group by 子句中使用with rollup，在所有查询的分组纪录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
  4、对字段分组：group by关键后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左向右，即先按第一个字段分组，然后在第一个字段值相同的记录中，再根据第二个字段的值进行分组...,以此类推。
  5、group by和order by一起使用：order by用来对查询结果的记录排序，如果和group by一起使用可以完成对分组的排序。另外，rollup和order by是互相排斥的。
  查询订单价格大于100的订单号和总订单价格，并进行排序（o_num订单号，quantity数量,item_price单价）

  select o_nu,sum(quantity*item_price) as orderTotal
  from orderitems
  group by o_num
  having sum(quantity*item_price)>=100
  order by orderTotal;

• 使用集合函数（聚合函数）查询

  MySQL聚合函数

  函数	作用
  avg()	返回某列的平均值
  count()	返回某列的行数
  max()	返回某列的最大值
  min()	返回某列的最小值
  sum()	返回某列值的和

  
  count(*)计算表中总的行数，不管某列有数值或者为空值。
  count(字段名)计算指定列下总的行数，计算时将忽略空值的行。
   
• 连接查询
  
  1、内连接，inner join。内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。
  2、外链接。有时候返回查询结果集需要包含没有关联的行中数据。外连接分为左外连接和右外连接
       left join:返回包括左表中的所有记录和右表中连接字段相等的记录。如果左表中的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。
       right join:返回包括右表中的所有记录和左表中连接字段相等的记录。如果右表的某行在左表中没有匹配行，左表将返回空值。
       mysql不支持全连接，若要得到全连接查询结果集，可以使用左连接和右连接来完成。
  3、复合条件连接查询
      复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。

  select customers.c_id,orders.o_num
  from customers inner join orders
  on customers.c_id=orders.c_id 
  and customers.c_id=1001  #这行就是过滤条件

• 子查询
   子查询指一个查询语句嵌套在另一个查询语句内部的查询。在select子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或多个表。子查询中常用的操作符有any(some)、all、in、exists。子查询可以添加到select、update和delete语句中，而且可以多层嵌套。子查询也可以使用比较运算符。
  any和some关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中任何一个比较条件，就返回一个结果作为外层查询的条件。
  all关键字，使用all时需要同时满足所有内存查询条件。
  exists关键字后面的参数是一个任意的子查询，系统对子查询进行运算以及判断它是否返回行，如果至少返回一行，那么exists的结果为true，此时外层查询语句将进行查询；否则不进行查询。not exists与exists使用方法相同，返回的结果相反。【exists的结果只会取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。】
  in关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。
• 合并查询结果
  利用union关键字，可以给出多条select语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个select语句之间使用union或union all关键字分隔。union不使用关键字all，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字all的作用是不删除重复行也不对结果进行自动排序。【使用union all的功能是不删除重复行，加上关键字语句执行时所需要的资源少，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用union all以提高查询效率。】
• 为表和字段取别名
  表别名只在执行查询的时候使用，并不在返回结果中显示，而列别名定义之后，将返回给客户端显示，显示的结果字段为字段列的别名。
  • 使用正则表达式查询
    正则表达式强大而且灵活，可以应用于非常复杂的查询。MySQL中使用regexp关键字指定正则表达式的字符匹配模式

    正则表达式常用字符匹配列表

    选项	说明	例子	匹配值示例
    ^	匹配文本的开始字符	'^b'匹配以字母b开头的字符串	book,big,banana,bike
    $	匹配文本的结束字符	'st$'匹配以st结尾的字符串	test,resist,persist
    .	匹配任何单个字符	‘b.t’匹配任何b和t之间有一个字符	bit,bat,but,bite
    *	匹配零个或多个在它前面的字符	'f*n'匹配字符n前面有任何个字符f	fn,fan,faan,abcn
    +	匹配前面的字符1次或多次	'ba+'匹配以b开头后面紧跟至少有一个a	ba,bay,bare,battle
    <字符串>	匹配包含指定的字符串的文本,如要匹配多个字符串，多个字符串之间使用“|”隔开	'fa'	fan,afa,faad
    [字符集合]	匹配字符集合中的任何字符	'[xz]'匹配x或者z	dizzy,zebra,x-ray,extra
    [^]	匹配不在括号中的任何字符	‘[^abc]’匹配任何不包含a、b或c的字符串	desk,fox,f8ke
    字符串{n,}	匹配前面的字符串至少n次	b{2}匹配2个或更多的b	bbb,bbbb,bbbbbb
    字符串{n,m}	匹配前面的字符串至少n次，至多m次。如果n为0，此参数为可选参数	b{2,4}匹配最少2个，最多4个b	bb,bbb,bbb

    注意：1.什么时候使用引号?
          在查询的时候，会看到在where子句中使用条件，有的值加上了单引号，而有的值未加。单引号用来限定字符串，如果将值与字符串类型列进行比较，则需要限定引号；而用来与数值进行比较则不需要引号。
            2.在where子句中必须使用圆括号吗？使用括号明确操作符的次序，是一个好习惯。
            3.为什么使用通配符格式正确，却没有查找出符合条件的记录？
            MySQL中存储字符串数据时，可能会不小心把两端带有空格的字符串保存到记录中，而在查看表中记录时，MySQL不能明确的显示空格，数据库操作者不能直观的确定字符两端是否有空格。列如，使用like'%e'匹配以字母e结尾的水果名称，如果字母e后面多了一个空格，则like语句不能将该记录查找出来。解决的方法是使用trim函数，将字符串两端的空格删除之后再进行匹配。