MySQL中文参考手册-创造并使用数据库 (4)
发表于:2007-05-26来源:作者:点击数:
标签:
4 从一个 数据库 表检索信息 SELECT语句被用来从一张桌子拉出信息。语句的一般格式是: SELECT what_to_select FROM which_table WHERE conditions_to_satisfy what_to_select指出你想要看到的,这可以是列的一张表,或*表明所有的列。which_table指出你想要
4 从一个数据库表检索信息
SELECT语句被用来从一张桌子拉出信息。语句的一般格式是:
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy
what_to_select指出你想要看到的,这可以是列的一张表,或*表明“所有的列”。which_table指出你想要从其检索数据的表。WHERE子句是可选的,如果它在,conditions_to_satisfy指定行必须满足的检索条件。
4.1 选择所有数据
SELECT最简单的形式是从一张表中检索每样东西:
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
如果你想要考察整个表,这种形式的SELECT是很有用的。例如,在你刚刚给它装载了你的初始数据集装以后。当它发生时,刚才显示的输出揭示了在你的数据文件的一个错误:在Bowser死了以后,它好象要出生了!请教你原来的家谱,你发现正确的出生年是1989,而不是1998。
至少有一些修正它的方法:
编辑文件“pet.txt”改正错误,然后使用DELETE和LOAD DATA弄空表并且再次装载它:
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
然而, 如果你这样做,你必须重新输入Puffball记录。
用一个UPDATE语句仅修正错误记录:
mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";
如上所示,检索整个表是容易的,但是一般你不想那样做,特别地当表变得很大时。相反,你通常对回答一个特别的问题更感兴趣,在这种情况下你在你想要的信息上指定一些限制。让我们看一些他们回答有关你宠物的问题的选择查询。
4.2 选择特定行
你能从你的表中只选择特定的行。例如,如果你想要验证你对Bowser的出生日期所做的改变,像这样精选Bowser的记录:
mysql> SELECT * FROM pet WHERE name = "Bowser";
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
输出证实年份现在正确记录为1989,而不是1998。
字符串比较通常是大小些无关的,因此你可以指定名字为"bowser"、"BOWSER"等等,查询结果将是相同的。
你能在任何列上指定条件,不只是name。例如,如果你想要知道哪个动物在1998以后出生的,
测试birth列:
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
你能组合条件,例如,找出雌性的狗:
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
上面的查询使用AND逻辑操作符,也有一个OR操作符:
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
AND和OR可以混用。如果你这样做,使用括号指明条件应该如何被分组是一个好主意:
mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
-> OR (species = "dog" AND sex = "f");
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
4.3 选择特定列
如果你不想要看到你的表的整个行,就命名你感兴趣的列,用逗号分开。例如,如果你想要知道你的动物什么时候出生的,精选name和birth列:
mysql> SELECT name, birth FROM pet;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
找出谁拥有宠物,使用这个查询:
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
然而,注意到查询简单地检索每个记录的owner字段,并且他们中的一些出现多次。为了使输出减到最少,通过增加关键词DISTINCT检索出每个唯一的输出记录:
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+
你能使用一个WHERE子句把行选择与列选择相结合。例如,为了只得到狗和猫的出生日期,使用这个查询:
mysql> SELECT name, species, birth FROM pet
-> WHERE species = "dog" OR species = "cat";
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+
4.4 排序行
你可能已经注意到前面的例子中结果行没有以特定的次序被显示。然而,当行以某个有意义的方式排序,检验查询输出通常是更容易的。为了排序结果,使用一个ORDER BY子句。
这里是动物生日,按日期排序:
mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
为了以逆序排序,增加DESC(下降 )关键字到你正在排序的列名上:
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+----------+------------+
你能在多个列上排序。例如,按动物的种类排序,然后按生日,首先是动物种类中最年轻的动物,使用下列查询:
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
注意DESC关键词仅适用于紧跟在它之前的列名字(birth);species值仍然以升序被排序。
原文转自:http://www.ltesting.net