数据查询不应只是简单查询数据库中存储的数据,还应该根据需要对数据进行筛选,以及确定数据以什么样的格式显示。
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可以。