分类: Mysql/postgreSQL
2008-05-14 23:22:01
来源: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
既然你知道怎样输入命令,现在是存取一个数据库的时候了。 假定在你的家(你的“动物园”)中有很多宠物,并且你想追踪关于他们各种各样类型的。你可以通过创建表来保存你的数据并根据所需要的信息装载他们做到,然后你可以通过从表中检索数据来回答关于你的动物不同种类的问题。本节显示如何做到所有这些事情:
动物园数据库将会是简单的(故意的),但是不难把它想象成可能用到相似类型数据库的真实世界情况。例如,这样的一个数据库能被一个农夫用来追踪家畜,或由一个兽医追踪病畜记录。 使用 mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+ 数据库列表可能在你的机器上是不同的,但是 如果 mysql> USE test Database changed 注意, 你可列在后面的例子中使用 mysql> GRANT ALL ON menagerie.* TO your_mysql_name; 这里 如果在设置你的权限时,管理员为你创建了数据库,你可以开始使用它。否则,你需要自己创建它: mysql> CREATE DATABASE menagerie; 在Unix下,数据库名字是区分大小写的(不像SQL关键词),因此你必须总是以 创建了一个数据库并不选定以使用它,你必须明确地做这件事。为了使 mysql> USE menagerie Database changed 你的数据库只需要创建一次,但是你必须在每次启动一个 shell> mysql -h host -u user -p menagerie Enter password: ******** 注意, 创建数据库是容易的部分,但是在这时它是空的,正如 mysql> SHOW TABLES; Empty set (0.00 sec) 较难的部分是决定你的数据库结构应该是什么:你将需要什么数据库表,和在他们中有什么样的列。 你将需要一个包含你每个宠物的记录的表。它可称为 年龄呢?那可能有趣,但是在一个数据库中存储不是一件好事情。年龄随着时间流逝而变化,这意味着你将要不断地更新你的记录。相反, 存储一个固定值例如生日比较好,那么,无论何时你需要年龄,你可以以当前日期和出生日期之间的差别来计算它。MySQL为日期运算提供了函数,因此这并不困难。存储出生日期而非年龄也有其他优点:
你可能想到 使用一个 mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
动物性表可以用许多方法表示,例如, 为 既然你创建了一个表, mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | pet | +---------------------+ 为了验证你的表是按你期望的方式被创建,使用一个 mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 你能随时 在你创建表后,你需要充实它。 假定你的宠物纪录描述如下。(观察到MySQL期望日期时以
因为你是从一张空表开始的,充实它的一个容易方法是创建包含为你的动物各一行一个文本文件,然后用一个单个语句装载文件的内容到表中。 你可以创建一个文本文件“pet.txt”,每行包含一个记录,用定位符(tab)把值分开,并且以在
为了装载文本文件“pet.txt”到 mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; 如果你愿意,你能明确地在 当你想要一次增加一个新记录时, mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); 注意,这里字符串和日期值被指定为引号扩起来的字符串。另外,用 从这个例子,你应该能看到涉及很多的键入用多个
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy
指出你想要看到的,这可以是列的一张表,或* 表明“所有的列”。which_table 指出你想要从其检索数据的表。WHERE 子句是可选的,如果它在,conditions_to_satisfy 指定行必须满足的检索条件。
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 | +----------+--------+---------+------+------------+------------+ 如果你想要考察整个表,这种形式的 至少有一些修正它的方法:
如上所示,检索整个表是容易的,但是一般你不想那样做,特别地当表变得很大时。相反,你通常对回答一个特别的问题更感兴趣,在这种情况下你在你想要的信息上指定一些限制。让我们看一些他们回答有关你宠物的问题的选择查询。 你能从你的表中只选择特定的行。例如,如果你想要验证你对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。 字符串比较通常是大小些无关的,因此你可以指定名字为 你能在任何列上指定条件,不只是 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 | +-------+--------+---------+------+------------+-------+ 上面的查询使用 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 | +----------+-------+---------+------+------------+-------+
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 | +-------+--------+---------+------+------------+-------+ 如果你不想要看到你的表的整个行,就命名你感兴趣的列,用逗号分开。例如,如果你想要知道你的动物什么时候出生的,精选 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 | +--------+ 然而,注意到查询简单地检索每个记录的 mysql> SELECT DISTINCT owner FROM pet; +--------+ | owner | +--------+ | Benny | | Diane | | Gwen | | Harold | +--------+ 你能使用一个 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 | +--------+---------+------------+ 你可能已经注意到前面的例子中结果行没有以特定的次序被显示。然而,当行以某个有意义的方式排序,检验查询输出通常是更容易的。为了排序结果,使用一个 这里是动物生日,按日期排序: 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 | +----------+------------+ 为了以逆序排序,增加 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 | +----------+---------+------------+ 注意 8.4.4.5 日期计算MySQL提供几个函数,你能用来执行在日期上的计算,例如,计算年龄或提取日期的部分。 为了决定你的每个宠物有多大,用出生日期和当前日期之间的差别计算年龄。通过变换2个日期到天数,取差值,并且用365除(在一年里的天数): mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 FROM pet; +----------+-------------------------------------+ | name | (TO_DAYS(NOW())-TO_DAYS(birth))/365 | +----------+-------------------------------------+ | Fluffy | 6.15 | | Claws | 5.04 | | Buffy | 9.88 | | Fang | 8.59 | | Bowser | 9.58 | | Chirpy | 0.55 | | Whistler | 1.30 | | Slim | 2.92 | | Puffball | 0.00 | +----------+-------------------------------------+ 尽管查询可行,关于它还有能被改进的一些事情。首先,如果行以某个次序表示,其结果能更容易被扫描。第二,年龄列的标题不是很有意义的。 第一个问题通过增加一个 mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age -> FROM pet ORDER BY name; +----------+------+ | name | age | +----------+------+ | Bowser | 9.58 | | Buffy | 9.88 | | Chirpy | 0.55 | | Claws | 5.04 | | Fang | 8.59 | | Fluffy | 6.15 | | Puffball | 0.00 | | Slim | 2.92 | | Whistler | 1.30 | +----------+------+ 为了按 mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age -> FROM pet ORDER BY age; +----------+------+ | name | age | +----------+------+ | Puffball | 0.00 | | Chirpy | 0.55 | | Whistler | 1.30 | | Slim | 2.92 | | Claws | 5.04 | | Fluffy | 6.15 | | Fang | 8.59 | | Bowser | 9.58 | | Buffy | 9.88 | +----------+------+ 一个类似的查询可以被用来确定已经死亡动物的死亡年龄。你通过检查 mysql> SELECT name, birth, death, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age; +--------+------------+------------+------+ | name | birth | death | age | +--------+------------+------------+------+ | Bowser | 1989-08-31 | 1995-07-29 | 5.91 | +--------+------------+------------+------+ 差询使用 如果你想要知道哪个动物下个月过生日,怎么办?对于这类计算,年和天是无关的,你简单地想要提取 mysql> SELECT name, birth, MONTH(birth) FROM pet; +----------+------------+--------------+ | name | birth | MONTH(birth) | +----------+------------+--------------+ | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Fang | 1990-08-27 | 8 | | Bowser | 1989-08-31 | 8 | | Chirpy | 1998-09-11 | 9 | | Whistler | 1997-12-09 | 12 | | Slim | 1996-04-29 | 4 | | Puffball | 1999-03-30 | 3 | +----------+------------+--------------+ 用下个月的生日找出动物也是容易的。假定当前月是4月,那么月值是 mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; +-------+------------+ | name | birth | +-------+------------+ | Buffy | 1989-05-13 | +-------+------------+ 当然如果当前月份是12月,就有点复杂了。你不是只把加1到月份数( 你甚至可以编写查询以便不管当前月份是什么它都能工作。这种方法你不必在查询中使用一个特定的月份数字, mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH)); 完成同样任务的一个不同方法是加 mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1; 注意,
mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 很清楚你从这些比较中得到毫无意义的结果。相反使用 mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ 在MySQL中,0意味着假而1意味着真。
MySQL提供标准的SQL模式匹配,以及一种基于象Unix实用程序如 SQL的模式匹配允许你使用“_”匹配任何单个字符,而“%”匹配任意数目字符(包括零个字符)。在 MySQL中,SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时,你不能使用 为了找出以“b”开头的名字: mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 为了找出以“fy”结尾的名字: mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 为了找出包含一个“w”的名字: mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 为了找出包含正好5个字符的名字,使用“_”模式字符: mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 由MySQL提供的模式匹配的其他类型是使用扩展正则表达式。当你对这类模式进行匹配测试时,使用 扩展正则表达式的一些字符是:
为了说明扩展正则表达式如何工作,上面所示的 为了找出以“b”开头的名字,使用“^”匹配名字的开始并且“[bB]”匹配小写或大写的“b”: mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 为了找出以“fy”结尾的名字,使用“$”匹配名字的结尾: mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 为了找出包含一个“w”的名字,使用“[wW]”匹配小写或大写的“w”: mysql> SELECT * FROM pet WHERE name REGEXP "[wW]"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 既然如果一个正规表达式出现在值的任何地方,其模式匹配了,就不必再先前的查询中在模式的两方面放置一个通配符以使得它匹配整个值,就像如果你使用了一个SQL模式那样。 为了找出包含正好5个字符的名字,使用“^”和“$”匹配名字的开始和结尾,和5个“.”实例在两者之间: mysql> SELECT * FROM pet WHERE name REGEXP "^.....$"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 你也可以使用“{n}”“重复 mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 数据库经常用于回答这个问题,“某个类型的数据在一张表中出现的频度?”例如,你可能想要知道你有多少宠物,或每位主人有多少宠物,或你可能想要在你的动物上施行各种类型的普查。 计算你拥有动物的总数字与“在 mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ 在前面,你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物,你可以使用 mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ 注意,使用 mysql> SELECT owner, COUNT(owner) FROM pet; ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
每种动物数量: mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ | species | COUNT(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ 每中性别的动物数量: mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; +------+----------+ | sex | COUNT(*) | +------+----------+ | NULL | 1 | | f | 4 | | m | 4 | +------+----------+ (在这个输出中, 按种类和性别组合的动物数量: mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | NULL | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ 当你使用 mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE species = "dog" OR species = "cat" -> GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | +---------+------+----------+ 或,如果你仅需要知道已知性别的按性别的动物数目: mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE sex IS NOT NULL -> GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+
给出了这些考虑,为 mysql> CREATE TABLE event (name VARCHAR(20), date DATE, -> type VARCHAR(15), remark VARCHAR(255)); 就象
象这样装载记录: mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event; 基于你从已经运行在 当他们有了一窝小动物时,假定你想要找出每只宠物的年龄。 mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark -> FROM pet, event -> WHERE pet.name = event.name AND type = "litter"; +--------+------+-----------------------------+ | name | age | remark | +--------+------+-----------------------------+ | Fluffy | 2.27 | 4 kittens, 3 female, 1 male | | Buffy | 4.12 | 5 puppies, 2 female, 3 male | | Buffy | 5.10 | 3 puppies, 3 female | +--------+------+-----------------------------+ 关于该查询要注意的几件事情:
你不必有2个不同的表来执行一个联结。如果你想要将一个表的记录与同一个表的其他记录进行比较,联结一个表到自身有时是有用的。例如,为了在你的宠物之中繁殖配偶,你可以用 mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+ 在这个查询中,我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪个表实例更直观。 |