数据查询之基本表的查询

因为是按照课本顺序，所以把数据查询放在数据更新之前，根据自身情况选择学习顺序。mysql和sqlite在表的查询上的语法几乎完全一样，所有文中只给出的大部分都是mysql中运行的截图。

先看下课本给出的格式：
　　select [all | distinct] <目标列表达式> [[as] <别名>] [,<目标列表达式> [[as] <别名>]]...
　　from <表名或视图名> [,<表名或视图名>...] | (<select 语句>) [as] <别名>
　　[where <条件表达式>]
　　[group by <列名> [having <条件表达式>]]
　　[order by <列名> [asc | desc]]
为了方便讲解，将其分为五个部分：即select,from,where,groupBy,orderBy
注意：这五个部分使用是有顺序的。例如：select * from test.test where TRUE order by id group by id;这样是违规的。

先看下select的部分：
　　select [all | distinct] <目标列表达式> [[as] <别名>] [,<目标列表达式> [[as] <别名>]]...
distinct表示把重复的行去掉，all则保留，all为默认值。
目标列表达式的格式有三种：
　　(1).    *    表示所有列
　　mysql/sqlite>select * from test.test;

　　　　 　　

　　(2). <列名>这个很简单了。当涉及到多个表时，要在列名前加上表名，格式为 “<表名>.<列名>",就像对象引用属性,很符合编程逻辑，列是表的属性，表是数据库的属性。
　　mysql/sqlite>select test.name as Name,test1.address as addr from test,test1;

　　　　

　　　　注意：这里的结果是是每个表的元组的笛卡尔积（即广义笛卡尔积，是关系代数中的概念，见课本关系代数部分）。

　　　(3).列名表达式，常量，聚集函数以及这三者组成的任意运算公式

　　　　例如：

　　　　mysql/sqlite>select id+1,'hello world' from test;

　　　　

　　　　主要的聚集函数：

　　　　　　　count(*)　　统计元组个数

　　　　　　　count([distinct] <列名>)　　统计一列中的非空值的个数

　　　　　　　sum/avg([distinct]<列名>)　　计算一列值总和/平均值（列必须是数值型）

　　　　　　　max/min([distinct]<列名>)　　求一列值中最大/最小值

　　　　

　　　　聚集函数与group by一起使用时会分组后的每组进行聚集运算，下面会详细讲解。

from部分比较简单，先看下语法：
　　from <表名或视图名> [,<表名或视图名>...] | (<select 语句>) [as] <别名>
可以看到<select 语句>比较特殊,这里只讲解这个的用法。
select 语句执行结果也是一个临时表，所以可以作为另个一select的输入表,但一定要给临时表一个别名。
注意：书中把包含在一个select语句中的select语句称为子查询。并且子查询只支持select .. from ... where..，即子查询中不能使用group by 和order by，但在mysql和sqlite中却支持完整的select子查询，即可以使用group和order。

　　

where部分比较多，但大部分都很好理解，从设计者角度出发，就是把from <表>送来的元组进行判断，满足条件就送到下一步处理，不满足抛弃，继续接受下一个元组。
看下语法：[where <条件表达式>]
条件表达式支持的查询条件：
　　<1>.比较    =，>,<,>=,<=,!=,<>,!>,!<;
　　<2>.确定范围     [not] between and
　　<3>.确定集合　  [not] in
　　<4>.字符匹配　　[not] like    书中只支持两个：%代表任意长度的字符串，_ 代表任意单个字符。
　　<5>.空值 　　[not] is NULL　　#sqlite中 is 相当于=，但在mysql中是按照定义的
　　　　
　　<6>.多重条件　　and,or,not
实际产品中可能会有还有自己实现的运算符，可自行查看官方手册，例如位运算符在注入中也可以使用到，过滤时要考虑全面。
操作数可以是常量，列名，子查询语句。
常量和列名的实例如下：

　　　　

　上面讲到子查询语句可以用在from中，因为select的结果其实也是表，从编程角度来讲，就是同一个类的对象。同样子查询语句也可以放在where和having中用作操作数。

　例如：where id in (select id from test)　　#子查询的结果必须为一列。

　　　　where id > (select id from test where name='test')　　#子查询的结果必须为一行一列，即只有一个元素。

　从编程的角度很好理解，在进行运算时如果类型不同要进行类型转换，只有一列的表可以转换成集合(可以使用in运算)，只有一个元素的表可以转换成一个常量.

　　　　

另外子查询还可以支持any(或some)和all谓词，以及存在量词exists:

　　any/all必须和比较运算符使用。例如： where id < any(select id from test)　　#表示小于集合中的某一个即为真

　　where id > all(select id from test)　　#表示大于集合中的所有才为真

　　所以从上面可以看到，这里子查询必须是只有一列才可以转换成集合。

　　

　　exists对子查询没有什么要求，用法也很简单。例如：where exitsts (select id,name from test)

　　子查询表非空返回真，为空返回假

group by的部分会涉及到聚集函数，看下语法:
　　[group by <列名> [having <条件表达式>]]
书中定义的语句中只有一个列名，但现实产品基本都可以加多列，例如：group by id,name;
group by子句将经过where判断后的送来的元组分组，值相等的为一组，并且分组后聚集函数(如果select部分使用了的话)将作用于每一组，即每一组都有一个函数值。
结合例子就很好理解：

　　　　

　没有group by，就相当于只有一组。使用聚集函数结果肯定只有一行。

　having可以加上判断条件，从group by中满足条件的组，在送往下一步处理（where子句是把从from送来的元组进行判断),所以having中可以使用聚集函数，而where中不能使用聚集函数；

　　　

having中的条件判断表达式和where中是一样的">/</!=...in ... like ... and/or/not..."都支持。

需要注意的是，使用group by和聚集函数时，查询结果表的行数(不考虑limit子句)就是分组个数，所以最好不要使用无关列。

例如：select id,name from test group by id;　　#这里仅对id进行了分组，name是无关列。

　　　　

对id分组后得到两组，所以结果有两行，但每一组name的值其实有多个，一般选每个分组的第一个元组。

order by部分是最简单的，即对上面的部分处理过后的表进行排序；
　　语法：[order by <列名> [asc | desc]]
同样的，实际产品中order by也可以加多列,可以使用别名。asc/desc 表示升/降序，升序为默认值。

　　　

除了书中定义的上面五个部分，现实中的产品中普遍还有一个 limit 部分，作用在order by之后，用来从最终表中选取特定几行。
语法：
　　limit [偏移量,] <选取行数> | <选取行数> offset [偏移量];
偏移量就是从第几行开始选取。默认偏移量是0，代表从第一行开始，这里偏移量就像数组下表。
例如：mysql/sqlite> select * from test limit 1,2;
　　　等价于> select * from test limit 2 offset 1;
都表示从偏移量为1(即第二行)开始，选取两行。

　　　　

　mysql/sqlite>select * from test limit 3;　　#从偏移量为0(这里没指定，使用默认值)选取三行

　　　　

 

 除了上面的部分，书中还有两个重要的部分，连接查询和集合查询，在注入中也经常用到。这也是关系代数中的部分，在关系代数中把概念搞清，理解起来很简单。
先看下连接查询：
　　上面介绍from子句时，涉及到两个表时，结果是两个表元组的广义笛卡尔积，这样的查询时无意义的。所以要使用连接运算。书中给出连接概念是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。
　　这里主要探讨msyql和sqlite对连接的实现语法。
　　内连接：from <表1> [inner] join <表2> on|where <属性间表达式>
　　　　也可以使用书中的语法 from <表1>,<表2> where <属性间表达式>

　　　　

　　外连接：mysql 支持 left,right,full外连接，sqlite只支持left外连接

　　　　语法： from <表1> <left | right | full> [outer] join <表2> on <属性间表达式>

　　　　mysql中外连接只能用on添加属性间表达式（mysql使用外连接时where必须在on后面)，而sqlite中也可以使用where。

　　　　

　　　　

　　使用连接时最好用on来添加表属性之间的表达式，where用来添加各个表单独满足的属性表达式。

　　连接部分就这些，再看下集合查询：对多个select语句的结果进行集合操作。有交，并，差，就是数学中对集合操作。

　　集合操作的前提条件：参加集合运算的各元组的列数必须相同，且对应的数据类型必须相同。

　　语法: select <列1>[,<列2>...] from <表1> <union | intersect | except > select <列1> [,<列2>...] from <表2>;

　　

　　注意：mysql中使用集合查询时，可用null代替任何类型。例如select id,name from test union select null,null;虽然id是int型，name是char型，但这样也是合法的。这点在注入时可利用来获得列数。

上篇：数据定义之基本表定义　　　　　　　　　　下篇：数据更新之基本表数据操纵

 

转载于:https://www.cnblogs.com/apsycho/p/10707116.html