Mysql 查询

数据查询不应只是简单查询数据库中存储的数据，还应该根据需要对数据进行筛选，以及确定数据以什么样的格式显示。

select 语句的基本格式

select 

{ * | <字段列表>}          如果要查询多个字段，用逗号分隔，最后一个不加逗号

  from <表1>,<表2>      查询数据的来源，可以有一个或多个

[ where <表达式> ] 限定条件

[group by<字段>] 如何显示查询出来的数据，并按照指定的字段分组

[ having ] 

[ order by <字段>]按什么顺序显示出来，升序ASC或降序DESC

[ limit [<offset>,] <row count>]每次显示查询出来的数据条数

单表查询

1. select * from 表名;    查询一个表的所有记录

2. select 列名 from 表名;  查询单个字段

3. select 字段名1，字段名2，...字段名n from 表名； 查询多个字段

4. select 字段名1，字段名2，...字段名n form 表名 where 查询条件；   查询指定记录

where 条件判断符 =相等，<>,!=不相等，<小于，<=小于等于，>大于,>=大于等于，between 位于两值之间。

5. 带 IN 关键字的查询 select id,name,price from fruits where s_id in (101,102) order by f_name; 查询id为101或102的记录，并按f_name排序

6. 带 between and 的范围查询   select name,price from fruits where price between 2.00 and 10.20;

7. 带 like 的字符匹配查询  如：select name from student where name  LIKE '王%';

a.百分号通配符 ‘%’，匹配任意长度的字符，甚至包括零字符

b. 下划线通配符 ‘_’ , 一次只能匹配任意一个字符

8. 查询空值 ，is  null ,与is null 相反的是 is not null .null 不同于0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加

9. 带and的多条件查询， 这样可以是查询的结果更精确，多个条件表达式用and分开

10. 带or的多条件查询，or也可以连接两个甚至多个查询条件，多个条件表达式之间用 or 分开。in操作符可以实现和or操作符相似的功能。or 和 and 可以一起使用，但要注意and的优先级高于or

11. 查询结果不重复 ，语法格式 select distinct 字段名 from 表名；

12对查询结果进行排序

a.单列排序  select name from fruits order by name;

b.多列排序select name ,price from fruits order by name,price;首先按第一列排序，然后按第二列排序

c.指定排序方向 ，通过在排序字段后面加 DESC 或 ASC（默认）

13.分组查询 ，mysql中使用group by 关键字对数据进行分组，基本语法为： [ group by 字段 ] [ having <条件表达式>]，字段值为进行分组是所依据的列的名称

如：select sex ,count(*) as total from student group by sex;对性别进行分组，并计算每个组的个数。

       select sex , group_concat(name) as names from student group by sex;对性别进行分组，并用group_concat()函数把分组中各个字段的值显示出来

14.goup by 关键字通常和集合函数一起使用，列如：max(),min(),count(),sum(),avg()

15.使用having过滤分组 ，group by 可以和having一起限定显示记录所要满足的条件。如select id ,group_concat(name) as names from fruits group by id having count(name)>1    根据id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息。

（having 和 where 都是用来过滤数据，区别：having 在数据分组之后进行过滤来选择分组，而where在分组之前用来选择记录，另外where排除的记录不再包括在分组中）

16.再 group by 子句中使用with rollup，之后，在所有查询出的分组记录之后添加一条记录，该巨鹿计算查询出的所有记录的总和，即统计记录数量

17.多字段分组，使用group by 可以对过个字段进行分组，guoup by 关键字后面跟需要分组的字段。mysql根据多个字段的值来进行层次分组，分组层次从左到右

18.group by 和order by 一起使用 ，可以完成对分组的排序。

19，使用rollup时，不能同时使用order by 字句进行结果排序。即rollup 和 order by 是互相排斥的。

20.使用limit 限制查询结果的数量，语法格式：limit [位置偏移量，]行数，第一个“位置偏移量”参数指示mysql 从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条是1，以此类推）；第二个参数“行数”指示返回的记录条数。mysql5.7中可以使用limit 3 offset 4 ; 意思是获取从第5条记录开始后面的3条记录，和 limit 4,3;返回的结果相同。

使用聚合函数查询

21.count()函数，统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。有两种用法：a.count(*)计算表中总的行数，不管某列中有数值或空值。b.count(字段名 )计算指定列下总的行数，计算时将忽略空值的行。前面 count（）和group by 一起使用

22.sum()函数：是一个求和函数，返回指定列值的总和。sum()函数在计算时，忽略列值为null的行

23.avg()函数：通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。avg()可以和group by一起使用，来计算每个分组的平均值。avg()函数使用时，其参数为要计算的列名称，如果要得到多个列的多个平均值，则要在每一列上使用avg()函数。

24.max()：返回指定列中的最大值，max()也可以和group by 一起使用，求每个分组中的最大值。max()函数不仅适用于查找数值类型，也可应用于字符类型

25.min()：返回查询列中的最小值。min()和max()类似

连接查询

26.内连接查询：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新记录。也就是说，在内连接查询中，只有满足条件的记录才会出现在结果关系中。如：select suppliers.s_id,s_name,f_name,f_price  from fruits,suppliers where fruits.s_id = suppliers.s_id;

如果两个表中的列名一样要是用完全限定名（格式为：“表名。列名”）

27.内连接查询：返回和上面一样的结果。使用Inner join 语法进行内连接查询。select suppliers.s_id,s_name,f_name,f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;在这里两个表的关系通过INNER JOIN 指定，使用这种语法的时候，连接的条件使用ON字句给出 而不是WHERE ，ON和WHERE

后面指定的条件相同。(INNER JOIN 与 JOIN 是相同的)

28.如果在一个连接查询中，设计的两个表都是同一个表，这种查询称为自连接查询。自连接查询是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。如：SELECT f1.f_id , f1.f_name FROM fruits AS f1,fruits AS f2 WHERE f1.s_id = f2.s_id AND f2.s_id = 'a1' ;

29.外链接查询：外链接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含连接条件的行，而且还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个连接表（全外连接）中的所有数据。外连接分为左连接，和右连接

LEFT JOIN（左连接）:返回包括左表中的所有记录和右表中连接字段相等的记录。

RIGHT JOIN （右连接）:返回包括右表中的所有记录和左表中连接字段相等的记录

30.LEFT JOIN （左连接）：的结果包括LEFT OUTER 字句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某些行在右表中没有匹配的行，则在相关联的结果行中，右表的所有选项列表列均为空值。如：SELECT customers.c_id,orders.o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id = orders.c_id;

31.RIGHT JOIN（右连接）是左连接的反向连接

32.复合条件连接查询：是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。  如连接之后又添加了限制条件

如：SELECT customers.c_id,orders.o_num FROM customers INNER JOIN orders ON customers.c_id = orders.c_id AND customers.c_id = 10001;

子查询

33.子查询值一个查询语句嵌套在另一个查询语句内部的查询。子查询中常用的操作符有ANY（SOME），ALL,IN,EXISTS。子查询可以添加到SELECT,UPDATE

和DELETE语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如<,<=,>,>=和 !=

34.带ANY、SOME关键字的子查询。ANY和SOME是同义词，表示满足其中任意一个，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。如：SELECT num1 FROM tbl1 WHERE num1>ANY（SELECT num2 FROM tbl2）;

35.带ALL关键字的子查询：ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。如上

36.带EXISTS关键字的子查询：EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true,此时外层查询语句将进行查询，否则，外层语句将不进行查询。  EXISTS关键字可以和条件表达式一起使用。  NOT EXISTS与EXISTS使用方法相同，返回的结果相反。EXISTS 与 NOT EXISTS 的结果只取决于是否会返回行，而不取决与这些行的内容，所以这个子查询输入列表通常是无关紧要的

37.带IN关键字的子查询：IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。先执行内层子查询，再进行外层查询，内层子查询的结果作为外部查询的比较条件。NOT IN 的作用域IN正好相反。

38.子查询的功能也可以通过连接查询完成，但是子查询使得MySQL代码更容易阅读和编写

39.带比较运算符的子查询：前面介绍的带ANY、ALL关键字的子查询时使用了“>”比较运算符，子查询时还可以使用其他的比较运算符，如<,<=,=,>=,!=,<>

合并查询结果

40.利用UNION关键字，可以给出多条SELECT语句，并将它们的结果合并层单个结果集。合并时，两个表对应的列数和数据类型必须相同，各个SELECT语句使用UNION或NUION ALL关键字分割。UNION执行的时候删除所有重复的记录，所有返回的行都是唯一的；使用ALL的作用是不删除重复的行也不对结果进行排序。

为表和字段取别名

41.为表取别名：语法结构：表名 [AS] 表别名 ，AS 关键字为可选参数，MySQL可以同时为多个表取别名，而且表别名可以放在不同的位置，到要保证不能与数据库中的其他表的名称冲突。

42..为字段起别名：语法结构：列名[AS] 列别名，“列名”为表中字段定义的名称，“列别名”为字段新名称，AS关键字为可选关键字。也可以为SELECT字句中的计算字段取别名，列如用COUNT聚合函数或者CONCAT等系统函数执行的结果字段取别名。

表别名只在执行查询的时候使用，并不在返回结果中显示，而列别名定义之后，将返回给客户端显示，显示的结果字段为字段列的别名。

使用正则表达式查询

43.正则表达式强大而且灵活，可以应用于非常复杂的查询。mysql中使用regexp关键字指定正则表达式的字符匹配模式。

44.^匹配文本的开始字符，“^b”匹配以字母b开头的字符串。

45.$匹配文本的结束字符，"st$"匹配以st结束的字符串。

46.  .匹配任何单个字符，“b.t”匹配任何b和t之间有一个字符。总长度不限

47. * 匹配零个或多个在它前面的字符，‘f * n’匹配字符n前面有任意个字符f, 如：fn ,abcn, n

48.+匹配前面的字符1次或多次，'ba+'匹配以b开头后面紧跟至少有一个a

49.<字符串> 匹配包含指定的字符串文本，‘fa’,如fan,afa,faad

50,[字符集合] 匹配字符集合中的任何一个字符，'[xz]'匹配x或者z,如 dizzy,zebra,x-ray。[456][abc...z]可以写成[4-6],[a-z]

51.[^] 匹配不在括号中的任何字符，'[^abc]'匹配任何不包括a,b,c的字符串

52.字符串{n,}匹配前面的字符串至少n次，b{2}匹配2个或更多的b

53,字符串{n,m} 匹配前面的字符串至少n次，至多m次，如果n为0，此参数为可选参数。如b{2,4}匹配最少2个，最多4个b

54.如果要匹配多个字符串，多个字符串之间使用分隔符“|”隔开， 如：'on | ap'查询包含字符串“on”或者“up”的字符串

55.LIKE运算符也可以匹配指定的字符串，但与regexp不同，LIKKE匹配的字符串如果在文本中间出现，则找不到它，相应的行也不会返回。REGEXP可以。