软件开发架构师

MySQL(二)数据的检索和过滤

数据库 16 2019-06-12 15:10

使用频率最高的SQL语句应该就是select语句了,它的用途就是从一个或多个表中检索信息,使用select检索表数据必须给出至少两条信息:想选择什么,以及从什么地方选择

 

一、检索数据

1、检索单个列

select column from table;

该SQL语句的检索结果将返回表中的所有行,数据没有过滤(过滤将得出结果集的一个子集),也没有排序(如没有明确排序查询结果,则返回数据的顺序没有特殊意义,只要返回相同数目的行,就是正确的)

MySQL如同大多数DBMS一样,不需要单条SQL语句后加分号,但特定DBMS可能必须在单条SQL语句后加分号;如果是多条SQL语句必须加分号(;),如果使用的是MySQL命令行,则必须用分号结束SQL语句

SQL语句不区分大小写(对所有SQL关键字使用大写,对所有列和表明使用小写,这样更易于阅读和调试)

2、检索多个列

select column1,column2,column3 from table;

在检索多个列时,要在列名之间加上逗号(,),最后一个列名不用加

SQL语句一般返回原始的、无格式的数据,数据的格式只是一个表示问题,而不是检索问题;因此表示方式一般在显示该数据的应用程序中规定,一般很少使用实际检索出的原始数据(没有应用程序提供的格式)

3、检索所有列

select * from table;

给定通配符*,则检索数据时返回表中所有列

一般除非确实需要检索表中的每个列,否则最好别使用*通配符;虽然使用*可能自己比较省事,但检索不需要的列通常会降低检索和应用程序的性能

优点在于:由于不明确指定列名,所以可以检索出名字未知的列

4、检索不同的行

select distinct column from table;

distinct告诉MySQL只返回不同的值;它必须直接放在列名的前面(distinct作用于所有列)

5、限制结果

select column from table limit N;

limit告诉MySQL只返回它指定值的第一行或前几行,N表示行的数量

select column from table limit X,Y;

limit X, Y告诉MySQL返回从行X开始的Y行;X为开始位置,Y为要检索的行数(limit带一个值总是从第一行开始,给出的数为返回的行数;带两个值可以指定从行号为第一个值的位置开始)

检索出来的第一行为行0而不是行1,因此,limit1,1将检索出第二行而不是第一行(在行数不够时,MySQL将只返回能返回的最大行数)

6、使用完全限定的表名

select table.column from database.table;

该SQL语句中检索的表名以及列名是完全限定的

 

二、排序检索数据

子句(clause):SQL语句由子句构成,有些子句是必需的,有些是可选的;一个子句通常由一个关键字和所提供的数据组成

1、排序单个列

order by子句:取一个或多个列的名字,据此对输出进行排序(order by位于from子句之后;如果使用limit,它必须位于order by 之后)

select column from table order by column;

该列指示MySQL对column进行排列(通常order by子句中使用的列将是为显示所选择的列)

2、按多个列排序

select column1,column2,column3 from table order by column1,column2;

仅在多个行具有相同的column1时才对column2进行排序,如果指定的column1是唯一的,则不会按照column2排序

3、指定排序方向

select column1,column2,column3 from table order by column1 desc;

desc:desc关键字只应用到直接位于其前面的列名,作用是使其按照降序排列(与其相反的是asc关键字,在升序排序时可以用到,但一般情况下升序是默认的

如果想在多个列上进行降序排序,则必须对每个列指定desc关键字

4、检索最高或最低值

select column from table orderby column desc limit 1;

column desc告诉MySQL按照降序排列,limit 1告诉MySQL只返回一行的数据

 

三、过滤数据

数据库包含大量的数据,很少需要检索表中所有航,通常会根据特定操作或报告需要提取表数据的子集;

只检索所需数据需要指定搜索条件(search criteria),搜索条件你也被称为过滤条件(filter condition)

1、where子句

select语句中,数据根据where子句中指定的搜索条件进行过滤,where子句在表名(from子句)之后给出

select column from table where column = N;

该语句的意思为从table表中筛选出column=N的行;采用了最简单的相等测试,检查一个列是否具有指定的值据此进行过滤

如果同时使用order by和where子句,order by位于where之后(否则会出错)

2、where子句操作符

=:等于      <>:不等于      !=:不等于      <:小于      <=:小于等于      >:大于      >=:大于等于      between:在指定的两个值之间

3、不匹配检查

select column from table where column <> N;

select column from table where column != N;

where子句中,对过滤的值,有的用单引号,有的不用,原因在于:单引号用于限定字符串,如果将值与串类型的列进行比较,则需要,如用来与数值列比较,则不用引号

3、范围值检查

select column from table where column between X and Y;

为了检查某个范围的值,可以使用between操作符。使用between操作符需要两个值:范围的开始值和结束值(上面例子中X和Y就是开始和结束值) 

between匹配范围内的所有的值,包括指定的开始值和结束值

4、空值检查

select column from table where column1 is null;

建表时,设计人员可以指定其中的列是否可以不包含值,在一个列不包含值时,称其为包含空值NULL(无值{no value}:它与字段包含0、空字符串或仅仅包含空格不同)

is null子句就是用来检查表中具有null值的列(在过滤数据选择出不具有特定值的行时,一定要验证返回数据中确实给出了被过滤列具有null的行)

 

四、使用操作符过滤数据

操作符(operator):用来联结或改变where子句中的子句的关键字。也称为逻辑操作符(logical operator)

1、and操作符

select column1, column2,column3 from table where column1 = X and column <=Y;

and,用在where子句中的关键字,用来指示检索满足所有给定条件的行;即:and指示DBMS只返回满足所有给定条件的行(可添加多个过滤条件,每添加一条就要使用一个and)

2、or操作符

select column1, column2 from table where column1 = X or column <=Y;

or,用在where子句中的关键字,用来表示检索匹配任一给定条件的行;即:or告诉DBMS匹配任一条件而不是同时匹配两个条件

3、计算次序

select column1,column2 from table where column1 =X or column1 = Y and column2 >= N;

该SQL语句的意思是从table表中过滤出column2为N(含)以上且column1=X或Y的的所有行(实际中是先计算column1 = Y and column2 >= N然后计算column1 =X)

where可包含任意数目的and和or操作符;允许两者结合以进行复杂和高级的过滤(需要说明的是:and在计算次序中优先级高于or

如果希望and和or之间计算次序不被错误组合,可将or操作符所对应的子句使用圆括号()括起来,以明确的分组相应的操作符

圆括号具有较and或or更高的计算次序,DBMS首先过滤圆括号内的条件

PS:任何时候使用具有and和or操作符的where子句,都应该使用圆括号明确的分组操作符!

4、in操作符

select column1, column2 from table where column1 in (X,Y) order by column2;

该SQL语句的意思是从table表中检索所有column=X和Y的column2的行(in操作符用来指定匹配值的清单的关键字,功能和or相当

圆括号在where子句中还有另一种用法,in操作符用来指定条件范围,范围中的每个条件都可以进行匹配;in取合法值的由逗号分隔的清单,全都括在圆括号中;

in操作符优点如下:

使用长的合法选项清单时,in操作符的语法更清楚且更直观

使用in操作符,计算的次序更容易管理(使用的操作符更少)

in操作符一般比or操作符清单执行更快

in最大的优点是可以包含其他select语句,使得能够更动态的建立where子句

5、not操作符

select column1, column2 from table where column1 not in (X,Y) order by column2;

该SQL语句的意思是从table表中列出除column1为X,Y之外的所有column2的行(not操作符用来否定后跟条件的关键字

not操作符有且只有一个功能,就是否定它之后所跟的任何条件

MySQL支持使用not对in、between和exists子句取反,这与其他多数DBMS允许使用not对各种条件取反有很大差别

 

五、使用通配符过滤数据

通配符(wildcard):用来匹配值的一部分的特殊字符,利用通配符可以创建比较特定数据的搜索模式(实际上是SQL的where子句中带有特殊含义的字符)

搜索模式(search pattern):由字面值、通配符或两者组合构成的搜索条件

为在搜索子句中使用通配符,必须使用like操作符;like指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

1、百分号(%)通配符

在搜索串中,%表示任何字符出现任意次数;通配符可以在搜索模式中任意位置使用,并且可以使用多个通配符

select column1, column2 from table where column1 like ‘test%’

上面的SQL语句表示任意以test起头的词;%告诉MySQL接受test之后的任意字符,不管它有多少字符

test%:所有以test起头的值/   %test%:匹配任何包含test的值/   te%st:所有以te开头以st结尾的值

PS:%通配符不可以匹配null

2、下划线(_)通配符

select column1, column2 from table where column1 like ‘_test ’

下划线通配符作用和%一样,但下划线通配符只匹配单个字符而不是多个字符(与%能匹配0个字符不一样,总是匹配一个字符)

3、使用通配符的技巧

不要过度使用通配符(如果其他操作符能达到同样目的,应使用其他操作符)

在确实需要使用通配符时,除非绝对有必要,否则不要把通配符用在搜索模式开始处(这样做是最慢的)

注意通配符所在的位置

 

文章评论