mysql数据查询语言DQL（Data Query Language）

• 基本查询：
  • 语法形式：select [all | distinct] 字段或表达式列表 [from子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句]；
  • 基本含义与解释：select语句表示从某个或某些表中查询（取得）若干条数据。上述语法形式中，所有中括号中的部分都是可以省略的，但如果出现，则他们的顺序必须按上述给出的顺序书写。而且，逻辑上，后一子句都是在前一子句所获得的结果的基础上再进行其设定的功能操作，如图所示：。各部分的含义分别解释如下：
  • [all | distinct]表示是否返回取得的数据中的“重复行”。all表示会返回重复行，distinct表示不返回重复行，意思是，重复的若干行数据就只返回一行了。默认不写就是all，也是最常用的，因为通常数据都不会有重复行。
  • 字段或表达式列表：
    • 使用形式： 字段或表达式1 [as] [别名1]， 字段或表达式2 [as] [别名2]， ....
    • 这部分最常见的就是字段列表。也可以看出，select语句的各部分中，就这部分不可以省略。每一项之间用逗号分开，表示结果集中的一个字段值。字段就是表示某个或某些表的中的字段名，顺序可以按我们需要的写，而不必非得按数据表中字段的本来顺序。另外，也可以看出，还有一种情况是可以为“表达式”。另外，每一个字段或表达式都可以使用一个“别名”来重新命名。举例如下：
    • select id, userName, userPswd, realName from userInfo; #最常规情况
    • select id, userName as un, userPswd as up, realName from userInfo;#给其中的2个字段设定了别名，其中as关键字可以省略，但建议都写上。
    • select id，concat(userName, userPswd) from userInfo; #将userName和userPswd“连接起来”作为一个字符串字段，这就是表达式。
    • select id, concat(userName,'-',userPswd) as uu from userInfo; #连接了字段和其他非字段数据（这里是一个“-”），并给其一个别名
    • select id，5 as f1, 1+2*3 as f2 from userInfo; #一个字段值，一个固定值（别名为f1），一个计算值（别名为f2）
    • select 5 as f1, 1+2*3 as f2 ; #特别注意：如果我们要“取得”的数据根本不在表格中的时候，就无需from子句了。本句类似上一句，只是没有id这个字段的数据。
    • select 表名1.id, 表名2.id as id2, 表名1.userName, regTime, tele from 表1，表2； #字段前可以用表名来做限定，通常只有from子句有多个表，且不同表的名称有相同字段的时候需要。如果字段名本身就不同，也可以不写。
    • 注意：别名就可在后续的语法部分（比如group部分，order部分）当作字段名使用，但不能在where部分使用。
  • from子句：
    • 表示从哪个（或哪些）表中取得数据。根据上一项的含义可见，也可以不从任何表中取得数据，此时from子句就可以省略了。举例如下：
    • from 表名1； #这是最常用，最基本，也是最容易理解的情况。
    • from 表名1，表名2； #这是表示从这2个表中取得数据。具体到底怎么取，这就涉及到下面要学的“连接查询了”。
    • from 表名1 [连接方式] 表名2 on 连接条件；#这也是从2个表中取得数据并指定这两个表的连接方式和连接条件。
  • where子句：
    • where子句表示从给出的表的数据中，取出其中符合条件的数据，通常都是对字段数据的判断。除了特殊的null，各种条件判断表达式返回的结果都是true或false（实际输出是1或0）。而其中null其实也被当作false看待，因为是条件语句，所以终究只能说条件要么成立（true），要么不成立（false）。其中涉及到的有多方面的条件判断相关知识。如下：
    • 算术运算符：+, -， *， /， %。
    • 比较运算符： >， >=， <， <=， =（等于）， <>（不等于）， !=（不等于）
    • 逻辑运算符： and(与) or(或) not(非)。含义和用法同其他编程语言（只是不用那几个符号，而是用单词）。也可以用&&，||, ！这3个符号，但不推荐
    • 布尔值和空值（null）的判断：XX is true, XX is false, XX is null, XX is not null
    • between语法：字段名 between 小值 and 大值（表示该字段的值在给定的两个值之间，含该俩值）；字段名 not between 值1 and 值2（相反含义）。比如where id between 1 and 5，则会返回id>=1并且id<=5的数据，其实它也相当于： id>=1 and id<=5;
    • in 语法：字段名 in（值1，值2，...）。其表示的含义是，找出字段值等于所给定的那些值之一的数据行。也可以有not in语法：字段名 not in （值1，值2，...）。比如 where id in (5,7,8)，则会返回id等于5或7或8的数据。where id not(5,7,8)表示除了这3个的其他所有
    • like语法：字段名 like '字符'。含义是“像什么一样”，用于从字符串中找出含有指定字符的数据，即所谓的“模糊查找”。但这里字符，通常都不是直接写要找的字符，而是要附上一些表示模糊性的特殊字符。分解如下：
      • where realName like '罗六奇'; #找出realName等于'罗六奇'的记录，这里实际相当于where realName='罗六奇';所以like的这种用法几乎没有意义。
      • where realName like '罗'; #同上，找出realName='罗'的记录，实际可能没有这个记录，所以没有意义。
      • where realName like '罗%'; #这里“%”就是一个特殊字符，表示“任意个数的任意字符”，所以这里就可以找出realName为以下值的记录：罗六奇，罗哥哥，罗叔叔，罗大脑袋，罗伯斯比尔....，即姓罗的都能找出来。但找不出这样的记录：老罗，老罗六奇，彪悍的罗永浩....
      • where realName like '%罗%"; #这种形式就是典型的“模糊查找”，可以找出realName字段中的任意位置含“罗”这个字符的记录。
      • where realName like '罗_"; #这里“_”就是一个特殊字符，表示“一个任意字符”，所以这里就可以找出realName为以下值的记录：罗弟，罗哥，罗叔，罗胖....，但找不出以下记录：罗六奇，罗哥哥，老罗，老罗永浩....。
      • 要想匹配“％”（百分号）和“＿”（下划线），“'”（单引号）这三个字符，就需要对其进行“转义”处理，只需要在其前面加“\”就可以了，比如：like 'en\_%'（表示以“en_”开头的任何记录）， like '%__\%'(表示以“%”结尾并前面至少有2个字符的记录，比如类似这样的：'95%', '超出18%', '提升了50%')。
    • where的最终判断运算结果，跟php或js中的if判断运算结果一样，都是true或false（1或0），所以，最简情形的where语句是 where 1 或 where 0
    • where子句前面必须有from子句。虽然他们2者都可以省略，但有from可以没有where，而有where必须有from。
  • group by子句：
    • 使用形式： group by 字段名1 [ASC|DESC]，字段名2 [ASC|DESC]，....。其中ASC或DESC表示对该分组的字段同时进行排序，可以省略，则默认是ASC（正序）。DESC表示倒序，跟order by的用法含义其实是一样的。
    • 什么是分组？分组就是将一些数据以某种特征进行“归类”，比如一个班的学生信息，可以按省份进行分组，或按年龄分组，或按性别分组。要知道，分组完之后，结果集是某种“组”，可以理解为“小组”，其数量可能会急剧减少。比如全班60多个同学，如果按“省”分组，也许就只有10多个“组”了。如果按年龄分组，也许就只有7-8个组了（大约从20岁到26岁）。如果按性别分组，就只有2组了。即使公安部的全国10几亿的身份证信息，如果按年龄分组，最终也只能得到100多条的记录。group by 子句就是用于将前面取得的数据进行某种“分组”操作。
    • 分组查询的select 部分有特殊的要求：出现在select中的字段名要么是group by中用过的字段名，要么是使用“聚合函数”处理过的字段名。这是因为分组的结果数据是“一个一个组的信息”。比如有这样一个表：，则我们按商品品牌将其分组为：
    • 常用聚合函数有：取平均值avg(), 取个数count(), 取总和sum()，取最大值max(), 取最小值min()。
    • 举例（以此表为例：）：
      • select pinpai from product group by pinpai; #找出产品中的所有品牌
      • select pinpai, sum(price) as total，max(price) as maxPrice from product group by pinpai desc; #找出每个品牌的价格和，以及最高价
      • select pinpai, count(pinpai) as shuliang from product group by pinpai; #找出每种品牌的产品数量
  • having子句：
    • having的含义：其实having的含义跟where是一样的，就是进行“条件筛选”，但其只是针对分组查询（group by子句）而可用的条件筛选，所以group by子句后面才可以用。换句话说，如果使用having，就必须紧跟在group by后面使用。
    • 同时，因为having是对分组子句进行的条件判断，则其判断的条件都应该是“组”级别的数据，即要么对group by中的字段进行判断，要么对使用了聚合函数的字段进行判断。
    • 举例（以此表为例：）：
      • select pinpai from product group by pinpai having max(price) > 4000; #找出产品中的最高价大于4000的那些品牌
      • select pinpai, count(pinpai) as shuliang from product group by pinpai having shuliang>2; #找出产品数量超过2个的品牌
  • order by子句：
    • 使用形式：order by 字段名1 [ASC|DESC]，字段名2 [ASC|DESC]，....；
    • 含义说明：对前面取得的数据（可能有where条件并进行了分组）来进行排序操作。可以指定第一排序字段，第二排序字段，等等。后一排序字段都是在前一排序字段值已经相同的情况下的排序。排序可以按升序（ASC）或降序（DESC）进行，默认（省略）的话就是升序。
    • 举例：order by proType; order by proType desc; order by proType asc, price;
  • limit子句：
    • 使用形式：limit [起始行号s，] 取得行数n；
    • 含义说明：对前面取得的数据（可能有where条件并进行了分组而且做过了排序）来限定取得其中的某部分（从第s行开始取n行）。注意，数据的行号数是从0开始算起的。另外，第一个起始行号可以省略，则表示是从0行开始取得所指定的行数的。
• 连接查询：
  • 基本含义：将两个表以“并排”的方式，连接起来，成为“更多字段”的一个新表。根据连接的不同方式和条件的不同，得到的新表的行数会有不同。如图所示，两个独立表及连接示意图：，连接之后：。
  • 基本形式：from 表1 [连接方式] join 表2 [on 连接条件]；连接的结果可以当作一个“表”来使用。常用有以下几种连接方式：
  • 交叉连接：from 表1 [cross] join 表2 ；连接的结果其实是两个表中的所有数据“两两对接”。这种连接也叫做“笛卡尔积”。
  • 内连接：from 表1 [inner] join 表2 on 连接条件。inner关键字可以省略，也可以用cross代替。on连接条件无非是设定在连接后所得到的数据表中，设定一个条件以取得所需要的数据。通常连接都是指两个有关联的表，则连接条件就是这两个表的关联字段的一个关系（通常都是相等关系）。如果没有设定此条件，则表示将左表的 m 行数据跟右表 n 行数据进行“完全连接”，结果就会有 m*n 行数据，这通常都不是应用所需。看例子：
  • 左外连接：from 表1 left [outer] join 表2 on 连接条件；将左边的表的数据跟右边的表的数据以给定的条件连接，并将左边的表中无法满足条件的数据也一并取得——即左边的表的数据肯定都取出来了。注意：此连接的on条件是必须写的。看例子：
  • 右外连接：from 表1 right [outer] join 表2 on 连接条件；将左边的表的数据跟右边的表的数据以给定的条件连接，并将右边的表中无法满足条件的数据也一并取得——即右边的表的数据肯定都取出来了。注意：此连接的on条件是必须写的。看例子：
• 子查询：
  • 基本含义：
    • 所谓查询，就是一个select语句（不管其后面带了哪些子句）。一个select语句最终执行的结果就是一个“数据集”(数据表)。该数据集(表)可大可小，可一行可多行，可一列可多列，最简情况就是一行一列，极端情况就是空数据(没有数据)，
    • 所谓子查询，就是一个select语句中的某些位置（select部分，from部分，where部分等）上，又出现了“内部select语句”，此内部select语句就是子查询。相应的，外部的select语句就是主查询（也可以称为外部查询）。
    • 一个主查询可以使用什么样的子查询，跟其所需要使用的位置和运算符有关。形式化后就是这样：
      • selelct 字段或表达式或子查询 [as 别名] from 表名或子查询 where 字段或表达式或子查询的条件判断
      • 一个子查询的查询结果要符合其所放在的主查询的所在位置的数据要求。
  • 按返回结果分类：
    • 表子查询 ： 一个子查询返回的结果理论上是“多行多列”的时候。此时可以当做一个“表”来使用，通常是放在from后面。
    • 行字查询 ： 一个子查询返回的结果理论上是“一行多列”的时候。此时可以当做一个“行”来使用，通常放在“行比较语法”中。
    • 列子查询 ： 一个子查询返回的结果理论上是“多行一列”的时候。此时可以当做“多个值”使用，类似这种：(5, 17, 8, 22)。
    • 标量子查询：一个子查询返回的结果理论上是“一行一列”的时候。此时可以当做“一个值”使用，类似这种：select 5 as c1; 或select ...where a = 17，或select ... where b > 8;
  • 按使用场合分类：
    • 作为主查询的结果数据：select c1,(select f1 from tab2) as f11 from tab1; #这里子查询应该只有一个数据（一行一列，标量子查询）
    • 作为主查询的条件数据：select c1 from tab1 where c1 in (select f1 from tab2); #这里子查询可以是多个数据（多行一列，列子查询）
    • 作为主查询的来源数据：select c1 from (select f1 as c1, f2 from tab2) as t2; #这里子查询可以是任意查询结果（表子查询）。
  • 常见子查询及相关关键字举例：
    • 比较运算符中使用子查询，形式为： 操作数 比较运算符 (标量子查询)；此时子查询的结果应该是一个值（标量子查询）。 操作数可以是一个字段名（通常情形）或一个具体字面值，下同。
      • ... where 'abc' = (select c1 from tab2 limit 1);
      • ... where age > (select max(age) from tab2 where jiguan='北京');
    • 使用in的子查询：操作数 in (列子查询); 此时子查询应该是多行一列值（列子查询），其相当于： 操作数 in (值1，值2，...)。
      • 比如这样的数据表结构：，要找出其中商品数量超过2个的所有类别的信息，语句如下：
      • select * from product_type where protypeID in(select protypeid from product group by protypeid having count(protypeid) >2);
    • 使用any的子查询：操作数 比较运算符 any(列子查询)。其含义是：如果子查询返回的数据中有其中任何一个值，该比较结果为TRUE，则最终结果是TRUE。应用举例（还以前面产品数据为例）：找出产地和品牌均相同的产品：select * from product where row(chandi,pinpai)=any(select chandi,pinpai from product group by chandi,pinpai having count(*)>=2);
    • 使用some的子查询：some是any的“同义词”，用法和含义一样。
    • 使用all的子查询：操作数 比较运算符 all(列子查询)。其含义是：如果子查询返回的数据中的所有值，该比较结果都为TRUE，则最终结果是TRUE。all的一个典型应用是用来找到在一个字段中的最大（最小）值的数据，类似这样：找出价格最贵的商品：select * from product where price >= all(select price from 表名)。
    • 使用exists的子查询：exists(表子查询)。含义是：如果该子查询有任何数据结果（即大于等于1行），则该判断就是true，否则为false。例如：找出具有在售商品的那些类别：select * from product_type as pt where exists(select * from product as p where p.protype_id=pt.protype_id);
    • 使用not exists子查询：not exists(表子查询)。含义是：如果该子查询没有任何数据结果（即0行），则该判断就是true，否则为false。例如：找出没有在售商品的那些类别：select * from product_type as pt where exists(select * from product as p where p.protype_id=pt.protype_id);
• 联合查询：
  • 基本含义：将两个“字段一致”的查询语句所查询到的结果以“纵向堆叠”的方式合并到一起，成为一个新的结果集。结果集是两个独立select查询语句的结果行数的和。如图所示：
  • 基本形式：select语句1 union [ALL | DISTINCT] select语句2；
  • 含义说明：
    • 两个select语句的查询结果的字段需要保持一致：个数必须相同，对应顺序上的字段类型也应该相同。
    • ALL | DISTINCT表示两表的数据联合后是否需要消除相同行（数据）。ALL表示不消除（全部取得），DISTINCT表示要消除。默认不写就会消除。
    • 应该将这个联合查询的结果理解为最终也是一个“表格数据”，且默认使用第一个select语句中的字段名；
    • 如果要对整个查询结果进行order by或limit操作，则需要将两个select语句用括号括起来，类似这样：(select...)union(select...) order by XXX limit m,n;
    • 如果第一个select语句中的列有别名，则order by子句中就必须使用该别名。
  • 举例：
    • select id , f1, f2 from jointable1 union select id2, c1, c2 from jointable2; #这里会自动“消除重复行”
    • select id , f1, f2 from jointable1 union all select id2, c1, c2 from jointable2; #这里明确声明不要消除重复行
    • (select id, f1, f2 from jointable1) union (select id2, c1, c2 from jointable2) order by id; #使用排序，这里不能使用id2
    • (select id as nid, f1, f2 from jointable1) union (select id2, c1, c2 from jointable2) order by nid; #这里不能使用id了