深入学习mysql数据库的查询技巧

　　MySQL数据库涉及到的查询技巧，其实是很多的，那么如何在查询数据的时候，有一些小技巧，包括了字符串的大小写问题，还有其他的null取值问题，更重要的还是涉及到了重复记录时是怎样避免取到重复值。

　　本节将讲述SELECT语句的一些高级功能。

　　列和表的别名

　　列的别名

　　精选输出的列可以用列名、列别名或列位置在ORDERBY和GROUPBY子句引用，列位置从1开始。

　　例如，我们从pet表中检索出宠物和种类，直接引用列名：

　　mysql>SELECTname,speciesFROMpetORDERBYname,species;

　　其输出为：

　　+----------+---------+

　　|name|species|

　　+----------+---------+

　　|Bowser|dog|

　　|Buffy|dog|

　　|Chirpy|bird|

　　|Claws|cat|

　　|Fang|dog|

　　|Fluffy|cat|

　　|Puffball|hamster|

　　|Slim|snake|

　　|Whistler|bird|

　　+----------+---------+

　　在子句中使用列的位置：

　　mysql>SELECTname,speciesFROMpetORDERBY1,2;

　　这条语句的输出与上面并无不同。

　　最后，你还可以为列命名：

　　mysql>SELECTnameASn,speciesASsFROMpetORDERBYn,s;

　　注意返回的结果：

　　+----------+---------+

　　|n|s|

　　+----------+---------+

　　|Bowser|dog|

　　|Buffy|dog|

　　|Chirpy|bird|

　　|Claws|cat|

　　|Fang|dog|

　　|Fluffy|cat|

　　|Puffball|hamster|

　　|Slim|snake|

　　|Whistler|bird|

　　+----------+---------+

　　返回的记录顺序并无不同。但是列的名字有了改变，这一点在使用CREATETABLE…SELECT语句创建表时是有意义的。

　　例如，我们想从pet表生成包括其中name，owner字段的表，但是想把name和owner字段的名字重新命名为animal和child，一个很笨的方法就是创建表再录入数据，如果使用别名，则仅仅一条SQL语句就可以解决问题，非常简单，我们要使用的语句使CREATETABLE：

　　mysql>CREATETABLEpet1

　　->SELECTnameASanimal,ownerASchild

　　->FROMpet;

　　然后，检索生成的表，看看是否打到目的：

　　mysql>SELECT*FROMpet1;

　　+----------+--------+

　　|animal|child|

　　+----------+--------+

　　|Fluffy|Harold|

　　|Claws|Gwen|

　　|Buffy|Harold|

　　|Chirpy|Gwen|

　　|Fang|Benny|

　　|Bowser|Diane|

　　|Whistler|Gwen|

　　|Slim|Benny|

　　|Puffball|Diane|

　　+----------+--------+

　　在子句中使用列的别名

　　你可以在GROUPBY、ORDERBY或在HAVING部分中使用别名引用列。别名也可以用来为列取一个更好点的名字：

　　mysql>SELECTspecies,COUNT(*)AStotalFROMpet

　　->GROUPBYspeciesHAVINGtotal>1;

　　+---------+-------+

　　|species|total|

　　+---------+-------+

　　|bird|2|

　　|cat|2|

　　|dog|3|

　　+---------+-------+

　　注意，你的ANSISQL不允许你在一个WHERE子句中引用一个别名。这是因为在WHERE代码被执行时，列值还可能没有终结。例如下列查询是不合法：

　　SELECTid,COUNT(*)AStotalFROMpetWHEREtotal>1GROUPBYspecies

　　会有下面的错误：

　　ERROR1054:Unknowncolumn'total'in'whereclause'

　　WHERE语句被执行以确定哪些行应该包括GROUPBY部分中，而HAVING用来决定应该只用结果集合中的哪些行。

　　表的别名

　　别名不仅可以应用于列，也可以引用于表名，具体方法类似于列的别名，这里不再重复。

　　列的别名经常用于表自身的连接中。你不必有2个不同的表来执行一个联结。如果你想要将一个表的记录与同一个表的其他记录进行比较，联结一个表到自身有时是有用的。例如，为了在你的宠物之中繁殖配偶，你可以用pet联结自身来进行相似种类的雄雌配对：

　　mysql>SELECTp1.name,p1.sex,p2.name,p2.sex,p1.species

　　->FROMpetASp1,petASp2

　　->WHEREp1.species=p2.speciesANDp1.sex="f"ANDp2.sex="m";

　　+--------+------+--------+------+---------+

　　|name|sex|name|sex|species|

　　+--------+------+--------+------+---------+

　　|Fluffy|f|Claws|m|cat|

　　|Buffy|f|Fang|m|dog|

　　|Buffy|f|Bowser|m|dog|

　　+--------+------+--------+------+---------+

　　在这个查询中，我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪个表实例更直观。

　　取出互不相同的记录

　　有时候你可能希望取出的数据互不重复，因为重复的数据可能对你没有意义。

　　解决的办法是使用DISTINCT关键字，使用这个关键字保证结果集中不包括重复的记录，也就是说，你取出的记录中，没有重复的行。

　　例如，我们取出pet表中Benny所拥有的宠物的记录：

　　mysql>SELECTname,owner,species,sexFROMpetWHEREowner="Benny";

　　+------+-------+---------+------+

　　|name|owner|species|sex|

　　+------+-------+---------+------+

　　|Fang|Benny|dog|m|

　　|Slim|Benny|snake|m|

　　+------+-------+---------+------+

　　注意上面的结果，因为我们要使用它。

　　假定我们指定DISTINCT关键字，并返回列name，species，sex列：

　　mysql>SELECTDISTINCTname,species,sexFROMpetWHEREowner="Benny";

　　+------+---------+------+

　　|name|species|sex|

　　+------+---------+------+

　　|Fang|dog|m|

　　|Slim|snake|m|

　　+------+---------+------+

　　你可以看到有两条结果，这是因为返回的结果集中的行不同，如果我们做以下更改，只返回owner，sex列，你可以观察变化：

　　mysql>SELECTDISTINCTowner,sexFROMpetWHEREowner="Benny";

　　+-------+------+

　　|owner|sex|

　　+-------+------+

　　|Benny|m|

　　+-------+------+

　　DISTINCT关键字的存在，使查询只返回不同的记录行。

　　如果一个表中，有完全相同的行，你可以使用DISTINCT，以去除冗余的输出：

　　SELECTDISTINCT*FROMtbl_name

　　NULL值的问题

　　NULL值可能很奇怪直到你习惯于它。概念上，NULL意味着“没有值”或“未知值”，且它被看作有点与众不同的值。为了测试NULL，你不能使用算术比较运算符例如=、<或!=。为了说明它，试试下列查询：

　　mysql>SELECT1=NULL,1!=NULL,1<NULL,1>NULL;

　　+----------+-----------+----------+----------+

　　|1=NULL|1!=NULL|1<NULL|1>NULL|

　　+----------+-----------+----------+----------+

　　|NULL|NULL|NULL|NULL|

　　+----------+-----------+----------+----------+

　　很清楚你从这些比较中得到毫无意义的结果。相反使用ISNULL和ISNOTNULL操作符：

　　mysql>SELECT1ISNULL,1ISNOTNULL;

　　+-----------+---------------+

　　|1ISNULL|1ISNOTNULL|

　　+-----------+---------------+

　　|0|1|

　　+-----------+---------------+

　　在MySQL中，0意味着假而1意味着真。

　　NULL这样特殊的处理是为什么，在前面的章节中，为了决定哪个动物不再是活着的，使用deathISNOTNULL而不是death!=NULL是必要的：

　　mysql>SELECT*FROMpetWHEREdeathISNOTNULL;

　　+--------+-------+---------+------+------------+------------+

　　|name|owner|species|sex|birth|death|

　　+--------+-------+---------+------+------------+------------+

　　|Bowser|Diane|dog|m|1990-08-31|1995-07-29|

　　+--------+-------+---------+------+------------+------------+

　　NULL值的概念是造成SQL的新手的混淆的普遍原因，他们经常认为NULL是和一个空字符串''的一样的东西。不是这样的！例如，下列语句是完全不同的：

　　mysql>INSERTINTOmy_table(phone)VALUES(NULL);

　　mysql>INSERTINTOmy_table(phone)VALUES("");

　　两个语句把值插入到phone列，但是第一个插入一个NULL值而第二个插入一个空字符串。第一个的含义可以认为是“电话号码不知道”，而第二个则可意味着“她没有电话”。

　　在SQL中，NULL值在于任何其他值甚至NULL值比较时总是假的（FALSE）。包含NULL的一个表达式总是产生一个NULL值，除非在包含在表达式中的运算符和函数的文档中指出。在下列例子，所有的列返回NULL：

　　mysql>SELECTNULL,1+NULL,CONCAT('Invisible',NULL);

　　+------+--------+--------------------------+

　　|NULL|1+NULL|CONCAT('Invisible',NULL)|

　　+------+--------+--------------------------+

　　|NULL|NULL|NULL|

　　+------+--------+--------------------------+

　　如果你想要寻找值是NULL的列，你不能使用=NULL测试。下列语句不返回任何行，因为对任何表达式，expr=NULL是假的：

　　mysql>SELECT*FROMmy_tableWHEREphone=NULL;

　　要想寻找NULL值，你必须使用ISNULL测试。下例显示如何找出NULL电话号码和空的电话号码：

　　mysql>SELECT*FROMmy_tableWHEREphoneISNULL;

　　mysql>SELECT*FROMmy_tableWHEREphone="";

　　在MySQL中，就像很多其他的SQL服务器一样，你不能索引可以有NULL值的列。你必须声明这样的列为NOTNULL，而且，你不能插入NULL到索引的列中。

　　当使用ORDERBY时，首先呈现NULL值。如果你用DESC以降序排序，NULL值最后显示。当使用GROUPBY时，所有的NULL值被认为是相等的。

　　为了有助于NULL的处理，你能使用ISNULL和ISNOTNULL运算符和IFNULL()函数。

　　对某些列类型，NULL值被特殊地处理。如果你将NULL插入表的第一个TIMESTAMP列，则插入当前的日期和时间。如果你将NULL插入一个AUTO_INCREMENT列，则插入顺序中的下一个数字。

　　大小写敏感性

　　1、数据库和表名

　　在MySQL中，数据库和表对应于在那些目录下的目录和文件，因而，内在的操作系统的敏感性决定数据库和表命名的大小写敏感性。这意味着数据库和表名在Unix上是区分大小写的，而在Win32上忽略大小写。

　　注意：在Win32上，尽管数据库和表名是忽略大小写的，你不应该在同一个查询中使用不同的大小写来引用一个给定的数据库和表。下列查询将不工作，因为它作为my_table和作为MY_TABLE引用一个表：

　　mysql>SELECT*FROMmy_tableWHEREMY_TABLE.col=1;

　　2、列名

　　列名在所有情况下都是忽略大小写的。

　　3、表的别名

　　表的别名是区分大小写的。下列查询将不工作，:因为它用a和A引用别名：

　　mysql>SELECTcol_nameFROMtbl_nameASa

　　WHEREa.col_name=1ORA.col_name=2;

　　4、列的别名

　　列的别名是忽略大小写的。

　　5、字符串比较和模式匹配

　　缺省地，MySQL搜索是大小写不敏感的(尽管有一些字符集从来不是忽略大小写的，例如捷克语)。这意味着，如果你用col_nameLIKE'a%'搜寻，你将得到所有以A或a开始的列值。如果你想要使这个搜索大小写敏感，使用象INDEX(col_name,"A")=0检查一个前缀。或如果列值必须确切是"A"，使用STRCMP(col_name,"A")=0。

　　简单的比较操作(>=、>、=、<、<=、排序和聚合)是基于每个字符的“排序值”。有同样排序值的字符(象E，e)被视为相同的字符！LIKE比较在每个字符的大写值上进行(“E”=”e”)。

　　如果你想要一个列总是被当作大小写敏感的方式，声明它为BINARY。

　　例如：

　　mysql>SELECT"E"="e","E"=BINARY"e";

　　+---------+----------------+

　　|"E"="e"|"E"=BINARY"e"|

　　+---------+----------------+

　　|1|0|

　　+---------+----------------+

　　检索语句与多个表的连接

　　SELECT语句不仅可以从单个表中检索数据，也可以通过连接多个表来检索数据。这里将介绍全连接和左连接的作用。

　　我们创建两个表作为例子。

　　mysql>CREATETABLEfirst

　　->(

　　->idTINYINT,

　　->first_nameCHAR(10)

　　->);

　　录入如下数据：

　　+------+-----------+

　　|id|first_name|

　　+------+-----------+

　　|1|Tom|

　　|2|Marry|

　　|3|Jarry|

　　+------+-----------+

　　mysql>CREATETABLElast

　　->(

　　->idTINYINT,

　　->last_nameCHAR(10)

　　->);

　　录入数据

　　+------+-----------+

　　|id|last_name|

　　+------+-----------+

　　|2|Stone|

　　|3|White|

　　|4|Donald|

　　+------+-----------+

　　全连接

　　全连接：在检索时指定多个表，将每个表用都好分隔，这样每个表的数据行都和其他表的每行交叉产生所有可能的组合，这样就是一个全连接。所有可能的组和数即每个表的行数之和。

　　那么观察下面的检索的结果：

　　mysql>SELECT*FROMfirst,last;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|1|Tom|2|Stone|

　　|2|Marry|2|Stone|

　　|3|Jarry|2|Stone|

　　|1|Tom|3|White|

　　|2|Marry|3|White|

　　|3|Jarry|3|White|

　　|1|Tom|4|Donald|

　　|2|Marry|4|Donald|

　　|3|Jarry|4|Donald|

　　+------+------------+------+-----------+

　　你可以看到输出的结果集中共有3×3=9行，这就是全连接的结果。

　　你也可以这样使用SQL语句：

　　mysql>SELCTfirst.*,last.*FROMfirst,last;

　　输出结果与上面的例子相同，并无二致。记录集的输出的排序是以FROM子句后的表的顺序进行，即先排列位置靠前的表，即使你改变记录集中列的显示顺序，例如下面的例子：

　　mysql>SELECTlast.*,first.*FROMfirst,last;

　　+------+-----------+------+------------+

　　|id|last_name|id|first_name|

　　+------+-----------+------+------------+

　　|2|Stone|1|Tom|

　　|2|Stone|2|Marry|

　　|2|Stone|3|Jarry|

　　|3|White|1|Tom|

　　|3|White|2|Marry|

　　|3|White|3|Jarry|

　　|4|Donald|1|Tom|

　　|4|Donald|2|Marry|

　　|4|Donald|3|Jarry|

　　+------+-----------+------+------------+

　　上面的例子是两个非常小的表的例子，如果是几个非常大的表的全连接，例如，两个行数分别为1000的表，这样的连接可以产生非常大的结果集合1000×1000＝100万行。而实际上你并不需要这么多行的结果，通常你需要使用一个WHERE从句来限制返回的记录集的行数：

　　mysql>SELECT*FROMfirst,lastWHEREfirst.id=last.id;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|2|Marry|2|Stone|

　　|3|Jarry|3|White|

　　+------+------------+------+-----------+

　　左连接

　　左连接：全连接给出FROM子句中所有表都有匹配的行。对于左连接，不仅匹配类似前面的行记录，而且还显示左边的表有而右边的表中无匹配的行。对于这样的行，从右边表选择的列均被显示为NULL。这样，每一匹配的行都从左边的表被选出，而如果右边表有一个匹配的行，则被选中，如果不匹配，行仍然被选中，不过，其中右边相应的列在结果集中均设为NULL。即，LEFTJOIN强制包含左边表的每一行，而不管右边表是否匹配。

　　语法：SELECTFROMtable_referenceLEFTJOINtable_referenceONconditional_expr

　　其中table_reference为连接的表，ON子句后接类似WHERE子句的条件。

　　下面我们详细讲述左连接的使用：

　　首先，返回一个全连接的结果集：

　　mysql>SELECT*FROMfirst,last;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|1|Tom|2|Stone|

　　|2|Marry|2|Stone|

　　|3|Jarry|2|Stone|

　　|1|Tom|3|White|

　　|2|Marry|3|White|

　　|3|Jarry|3|White|

　　|1|Tom|4|Donald|

　　|2|Marry|4|Donald|

　　|3|Jarry|4|Donald|

　　+------+------------+------+-----------+

　　注意上面的结果，下面的例子要与这个例子对照。

　　我们在给出一个限制条件的查询：

　　mysql>SELECT*FROMfirst,lastWHEREfirst.id=last.id;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|2|Marry|2|Stone|

　　|3|Jarry|3|White|

　　+------+------------+------+-----------+

　　这个结果类似于是从上一个全连接中选择出first.id>last.id的行。

　　现在我们给出一个真正的左连接的例子，你可以仔细观察它的结果，要了解检索的记录顺序：

　　mysql>SELECT*FROMfirstLEFTJOINlastONfirst.id=last.id;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|1|Tom|NULL|NULL|

　　|2|Marry|2|Stone|

　　|3|Jarry|3|White|

　　+------+------------+------+-----------+

　　上面的结果，即用左边表的每一行与右边表匹配，如果匹配，则选择到结果集中，如果没有匹配，则结果集中，右边表相应的列置为NULL。

　　为了进一步理解这一点，我们给出一个有点奇怪的例子：

　　mysql>SELECT*FROMfirstLEFTJOINlastONfirst.id=1;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|1|Tom|2|Stone|

　　|1|Tom|3|White|

　　|1|Tom|4|Donald|

　　|2|Marry|NULL|NULL|

　　|3|Jarry|NULL|NULL|

　　+------+------------+------+-----------+

　　因为，在结果的最后两行有似乎你不希望的结果。记住，如果只有ON子句的条件，那么左边表的每一行都会返回，只是如果没有匹配的右边表（虽然本例没有约束右边表的列），则记录集中显示为NULL。

　　前面只是帮助你理解左连接，下面LEFTJOIN的一些有用的技巧。LEFTJOIN最常见的是与WHERE子句共同使用。

　　使用ISNULL或者ISNOTNULL操作符可以筛选NULL或者非NULL值的列，这是最常见的技巧。

　　例如，选出first.id=last.id的组合,并且剔除其中没有右表的匹配记录：

　　mysql>SELECT*FROMfirstLEFTJOINlastONfirst.id=last.id

　　->WHERElast.idISNOTNULL;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|2|Marry|2|Stone|

　　|3|Jarry|3|White|

　　+------+------------+------+-----------+

　　你可以看到这样做的例子结果与语句

　　SELECT*FROMfirst,lastWHEREfirst.id=last.id

　　的输出是相同的。

　　又如，检索id值只在左边表出现，而不再右边表出现的记录：

　　mysql>SELECTfirst.*FROMfirstLEFTJOINlastONfirst.id=last.id

　　->WHERElast.idISNULL;

　　+------+------------+

　　|id|first_name|

　　+------+------------+

　　|1|Tom|

　　+------+------------+

　　这个语句是不能用功能相同的带WHERE子句的全连接代替的。

　　注意：全连接和左连接的结果集排列顺序是不同的，例如：

　　mysql>SELECT*FROMfirst,lastWHEREfirst.id!=last.id;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|1|Tom|2|Stone|

　　|3|Jarry|2|Stone|

　　|1|Tom|3|White|

　　|2|Marry|3|White|

　　|1|Tom|4|Donald|

　　|2|Marry|4|Donald|

　　|3|Jarry|4|Donald|

　　+------+------------+------+-----------+

　　mysql>SELECT*FROMfirstLEFTJOINlastONfirst.id!=last.id;

　　+------+------------+------+-----------+

　　|id|first_name|id|last_name|

　　+------+------------+------+-----------+

　　|1|Tom|2|Stone|

　　|1|Tom|3|White|

　　|1|Tom|4|Donald|

　　|2|Marry|3|White|

　　|2|Marry|4|Donald|

　　|3|Jarry|2|Stone|

　　|3|Jarry|4|Donald|

　　+------+------------+------+-----------+

　　总结

　　本节的内容非常繁杂，各小节之间可能没有什么联系，但是本节所述的都是检索数据时很常用的技巧，主要的一些内容如下：

　　1、为表和列使用别名

　　2、注意NULL值在查询中的使用

　　3、注意表名、列名、别名和字符串的大小写问题

　　4、如何避免取出重复的记录