MySQL教程.doc-道客多多_道客多多docduoduo.com

资源描述

1、第 3 章：教程目录3.1. 连接与断开服务器 3.2. 输入查询 3.3. 创建并使用数据库 3.3.1. 创建并选择数据库 3.3.2. 创建表 3.3.3. 将数据装入表中 3.3.4. 从表检索信息3.4. 获得数据库和表的信息 3.5. 在批处理模式下使用 mysql 3.6. 常用查询的例子 3.6.1. 列的最大值 3.6.2. 拥有某个列的最大值的行 3.6.3. 列的最大值：按组 3.6.4. 拥有某个字段的组间最大值的行 3.6.5. 使用用户变量 3.6.6. 使用外键 3.6.7. 根据两个键搜索 3.6.8. 根据天计算访问量 3.6.9. 使用 AUTO_INCR

2、EMENT3.7. 孪生项目的查询 3.7.1. 查找所有未分发的孪生项 3.7.2. 显示孪生对状态的表3.8. 与 Apache 一起使用 MySQL本章通过演示如何使用 mysql 客户程序创造和使用一个简单的数据库，提供一个 MySQL 的入门教程。mysql（有时称为“终端监视器” 或只是“监视”）是一个交互式程序，允许你连接一个 MySQL 服务器，运行查询并察看结果。mysql 可以用于批模式：你预先把查询放在一个文件中，然后告诉 mysql 执行文件的内容。本章将介绍使用 mysql 的两个方法。要想查看由 mysql 提供的选择项目表，可以用-help 选项来调用：shell

3、 mysql -help本章假定 mysql 已经被安装在你的机器上，并且有一个 MySQL 服务器可以连接。否则，请联络 MySQL管理员。（如果你是管理员，则需要查阅本手册的其它章节，例如第 5 章：数据库管理。）本章描述建立和使用一个数据库的全过程。如果你仅仅对访问一个已经存在的数据库感兴趣，可以跳过描述怎样创建数据库及它所包含的表的章节。由于本章是一个教程，省略了许多细节。关于这里所涉及的主题的详细信息，请查阅本手册的相关章节。3.1. 连接与断开服务器为了连接服务器，当调用 mysql 时，通常需要提供一个 MySQL 用户名并且很可能需要一个密码。如果服务器运行在登录服

4、务器之外的其它机器上，还需要指定主机名。联系管理员以找出进行连接所使用的参数 (即，连接的主机、用户名和使用的密码)。知道正确的参数后，可以按照以下方式进行连接：shell mysql -h host -u user -pEnter password: *host 和 user 分别代表 MySQL 服务器运行的主机名和 MySQL 账户用户名。设置时替换为正确的值。* 代表你的密码；当 mysql 显示 Enter password:提示时输入它。如果有效，你应该看见 mysql提示符后的一些介绍信息：shell mysql -h host -u user -pEnter password:

5、 *Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 25338 to server version: 5.1.2-alpha-standardType help; or h for help. Type c to clear the buffer.mysqlmysql 提示符告诉你 mysql 准备为你输入命令。一些 MySQL 安装允许用户以匿名（未命名）用户连接到本地主机上运行的服务器。如果你的机器是这种情况，你应该能不带任何选项地调用 mysql 与该服务器连接：shel

6、l mysql成功地连接后，可以在 mysql提示下输入 QUIT (或q) 随时退出：mysql QUITBye在 Unix 中，也可以按 control-D 键断开服务器。在下列章节的大多数例子都假设你连接到了服务器。由 mysql提示指明。3.2. 输入查询确保你连接上了服务器，如在先前的章节讨论的。连接上服务器并布代表选择了任何数据库，但这样就可以了。知道关于如何查询的基本知识，比马上跳至创建表、给他们装载数据并且从他们检索数据更重要。本节描述输入命令的基本原则，使用几个查询，你能尝试了解 mysql 是如何工作的。这是一个简单的命令，要求服务器告诉它的版本号和当前日期。在 mysql

8、mysql 用表格 (行和列)方式显示查询输出。第一行包含列的标签，随后的行是查询结果。通常，列标签是你取自数据库表的列的名字。如果你正在检索一个表达式而非表列的值(如刚才的例子) ，mysql 用表达式本身标记列。 mysql 显示返回了多少行，以及查询花了多长时间，它给你提供服务器性能的一个大致概念。因为他们表示时钟时间(不是 CPU 或机器时间) ，并且因为他们受到诸如服务器负载和网络延时的影响，因此这些值是不精确的。（为了简洁，在本章其它例子中不再显示“集合中的行” 。）能够以大小写输入关键词。下列查询是等价的：mysql SELECT VERSION(), CURRENT_DATE;

9、mysql select version(), current_date;mysql SeLeCt vErSiOn(), current_DATE;这是另外一个查询，它说明你能将 mysql 用作一个简单的计算器：mysql SELECT SIN(PI()/4), (4+1)*5;+-+-+| SIN(PI()/4) | (4+1)*5 |+-+-+| 0.70710678118655 | 25 |+-+-+1 row in set (0.02 sec)至此显示的命令是相当短的单行语句。你可以在一行上输入多条语句，只需要以一个分号间隔开各语句：mysql SELECT VERSION(); S

10、ELECT NOW();+-+| VERSION() |+-+| 5.1.2-alpha-log |+-+1 row in set (0.00 sec)+-+| NOW() |+-+| 2005-10-11 15:15:00 |+-+1 row in set (0.00 sec)不必全在一个行内给出一个命令，较长命令可以输入到多个行中。mysql 通过寻找终止分号而不是输入行的结束来决定语句在哪儿结束。（换句话说，mysql 接受自由格式的输入：它收集输入行但直到看见分号才执行。）这里是一个简单的多行语句的例子：mysql SELECT- USER()- ,- CURRENT_DATE;+-+

11、-+| USER() | CURRENT_DATE |+-+-+| jonlocalhost | 2005-10-11 |+-+-+在这个例子中，在输入多行查询的第一行后，要注意提示符如何从 mysql变为-，这正是 mysql 如何指出它没见到完整的语句并且正在等待剩余的部分。提示符是你的朋友，因为它提供有价值的反馈，如果使用该反馈，将总是知道 mysql 正在等待什么。如果你决定不想执行正在输入过程中的一个命令，输入c 取消它：mysql SELECT- USER()- cmysql这里也要注意提示符，在你输入c 以后，它切换回到 mysql，提供反馈以表明 mysql 准备接受一个新命令

12、。下表显示出可以看见的各个提示符并简述它们所表示的 mysql 的状态：提示符含义mysql 准备好接受新命令。- 等待多行命令的下一行。等待下一行，等待以单引号(“”)开始的字符串的结束。“ 等待下一行，等待以双引号(“”)开始的字符串的结束。等待下一行，等待以反斜点()开始的识别符的结束。/* 等待下一行，等待以/*开始的注释的结束。当你打算在一个单行上发出一个命令时，通常会“偶然” 出现多行语句，但是没有终止分号。在这种情况中，mysql 等待进一步输入：mysql SELECT USER()-如果出现这种情况(你认为输完了语句，但是只有一个 -提示符响应)，很可能 mysql 正

13、在等待分号。如果你没有注意到提示符的提示，在意识到你需要做什么之前，你可能会呆坐一会儿。输入一个分号完成语句，mysql 将执行：mysql SELECT USER()- ;+-+| USER() |+-+| jonlocalhost |+-+在字符串收集期间将出现和 “ 提示符（提示 MySQL 正等待字符串的结束）。在 MySQL 中，可以写由或“字符括起来的字符串 (例如，hello或“goodbye“) ，并且 mysql 允许输入跨越多行的字符串。当看到一个或 “ 提示符时，这意味着已经输入了包含以或“括号字符开始的字符串的一行，但是还没有输入终止字符串的匹配引号。这显示你粗心地

14、省掉了一个引号字符。例如：mysql SELECT * FROM my_table WHERE name = Smith AND age 如果你输入 SELECT 语句，然后按 Enter（回车）键并等待结果，什么都没有出现。不要惊讶，“为什么该查询这么长呢？”，注意“提示符提供的线索。它告诉你 mysql 期望见到一个未终止字符串的余下部分。（你看见语句中的错误吗？字符串“Smith 丢掉了第二个引号。）走到这一步，你该做什么？最简单的是取消命令。然而，在这种情况下，你不能只是输入c，因为 mysql作为它正在收集的字符串的一部分来解释它！相反，应输入关闭的引号字符(这样 mysql 知道你

15、完成了字符串)，然后输入c：mysql SELECT * FROM my_table WHERE name = Smith AND age cmysql提示符回到 mysql，显示 mysql 准备好接受一个新命令了。提示符类似于和“ 提示符，但表示你已经开始但没有结束以开始的识别符。知道和“提示符的含义很重要，因为如果你错误地输入一个未终止的字符串，任何后面输入的行将要被mysql 忽略-包括包含 QUIT 的行！这可能令人相当困惑，特别是如果取消当前命令前还不知道你需要提供终止引号。3.3. 创建并使用数据库3.3.1. 创建并选择数据库 3.3.2. 创建表 3.3.3. 将数据

16、装入表中 3.3.4. 从表检索信息知道怎样输入命令，便可以访问数据库了。假定在你的家(你的“动物园”) 中有很多宠物，并且你想跟踪关于它们各种类型的信息。你可以通过创建表来保存你的数据并根据所需要的信息装载他们，然后你可以从表中检索数据来回答关于动物不同种类的问题。本节显示如何做到所有这些事情：创建数据库创建数据库表装载数据到数据库表以各种方法从表中检索数据使用多个表动物园数据库很简单(特意的)，但是不难把它想象成可能用到类似数据库的真实世界情况。例如，农夫可以使用这样的一个数据库来追踪家畜，或者兽医可以用它跟踪病畜记录。从 MySQL 网址上可以获得后面章节中将用到的含有部分查询

17、和样例数据的动物园分发。有 tar 压缩格式 (http:/ Zip 压缩格式 (http:/ SHOW 语句找出服务器上当前存在什么数据库：mysql SHOW DATABASES;+-+| Database |+-+| mysql | test | tmp |+-+可能你的机器上的数据库列表是不同的，但是很可能有 mysql 和 test 数据库。mysql 是必需的，因为它描述用户访问权限，test 数据库经常作为用户试身手的工作区。请注意如果没有 SHOW DATABASES 权限，则不能看见所有数据库。参见 13.5.1.3 节，“GRANT 和REVOKE 语法” 。如果 test

18、数据库存在，尝试访问它：mysql USE testDatabase changed注意，USE ，类似 QUIT，不需要一个分号。（如果你喜欢，你可以用一个分号终止这样的语句；这无碍）USE 语句在使用上也有另外一个特殊的地方：它必须在一个单行上给出。你可列在后面的例子中使用 test 数据库( 如果你能访问它)，但是你在该数据库创建的任何东西可以被访问它的其它人删除，因此，你应该询问 MySQL 管理员许可你使用自己的一个数据库。假定你想要调用你的menagerie，管理员需要执行这样一条命令：mysql GRANT ALL ON menagerie.* TO your_mysql_na

19、meyour_client_host;这里 your_mysql_name 是分配给你的 MySQL 用户名，your_client_host 是所连接的服务器所在的主机。3.3.1. 创建并选择数据库如果管理员在设置权限时为你创建了数据库，你可以开始使用它。否则，你需要自己创建数据库：mysql CREATE DATABASE menagerie;在 Unix 下，数据库名称是区分大小写的( 不像 SQL 关键字) ，因此你必须总是以 menagerie 访问数据库，而不能用 Menagerie、MENAGERIE 或其它一些变量。对表名也是这样的。（在 Windows 下，该限制不适用，尽

20、管你必须在一个给定的查询中使用同样的大小写来引用数据库和表。但是，由于多种原因，作为最好的惯例，一定要使用与数据库创建时的同样的大小写。）创建数据库并不表示选定并使用它，你必须明确地操作。为了使 menagerie 成为当前的数据库，使用这个命令：mysql USE menagerieDatabase changed数据库只需要创建一次，但是必须在每次启动 mysql 会话时在使用前先选择它。你可以根据上面的例子执行一个 USE 语句来实现。还可以在调用 mysql 时，通过命令行选择数据库，只需要在提供连接参数之后指定数据库名称。例如：shell mysql -h host -u user

21、-p menagerieEnter password: *注意，刚才显示的命令行中的 menagerie 不是你的密码。如果你想要在命令行上在-p 选项后提供密码，则不能插入空格(例如，如-pmypassword，不是-p mypassword)。但是，不建议在命令行输入密码，因为这样会暴露密码，能被在机器上登录的其它用户窥探到。3.3.2. 创建表创建数据库是很容易的部分，但是在这时它是空的，正如 SHOW TABLES 将告诉你的：mysql SHOW TABLES;Empty set (0.00 sec)较难的部分是决定你的数据库结构应该是什么：你需要什么数据库表，各数据库表中有什

22、么样的列。你将需要一个包含你每个宠物的记录的表。它可称为 pet 表，并且它应该包含，最少，每个动物的名字。因为名字本身不是很有趣，表应该包含另外的信息。例如，如果在你豢养宠物的家庭有超过一个人，你可能想要列出每个动物的主人。你可能也想要记录例如种类和性别的一些基本的描述信息。年龄呢？那可能有趣，但是存储到一个数据库中不是一件好事情。年龄随着时间流逝而变化，这意味着你将要不断地更新你的记录。相反, 存储一个固定值例如生日比较好，那么，无论何时你需要年龄，可以以当前日期和出生日期之间的差来计算它。MySQL 提供了日期运算函数，因此这并不困难。存储出生日期而非年龄还有其它优点：你可以使用数据库

23、完成这样的任务，例如生成即将到来的宠物生日的提示。（如果你认为这类查询有点蠢，注意，这与从商务数据库来识别出不久要发给生日祝贺的客户是同一个问题，因为计算机帮助私人联络。）你可以相对于日期而不止是当前日期来计算年龄。例如，如果你在数据库存储死亡日期，你能很容易地计算出一只宠物死时有多大。你可能想到 pet 表中其它有用的其它类型信息，但是到目前为止这些已经足够了：名字、主人、种类，性别、出生和死亡日期。使用一个 CREATE TABLE 语句指定你的数据库表的布局：mysql CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),- sp

24、ecies VARCHAR(20), sex CHAR(1), birth DATE, death DATE);VARCHAR 适合于 name、owner 和 species 列，因为列值是变长的。这些列的长度不必都相同，而且不必是 20。你可以挑选从 1 到 65535 的任何长度，从中选择一个最合理的值。（如果选择得不合适，后来证明你需要一个更长的字段，MySQL 提供一个 ALTER TABLE 语句。）可以用多种类型的值来表示动物记录中的性别，例如，“m“和“f“，或 “male“和“female“ 。使用单字符“m“和“f“是最简单的方法。很显然，birth 和 death 列应选

27、惯的不同。）name owner species sex birth deathFluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1979-08-31 1995-07-29Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29 因为你是从一个空表开始的，

28、填充它的一个简易方法是创建一个文本文件，每个动物各一行，然后用一个语句将文件的内容装载到表中。你可以创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以CREATE TABLE 语句中列出的列次序给出。对于丢失的值( 例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用 NULL 值。为了在你的文本文件中表示这些内容，使用 N（反斜线，字母 N）。例如，Whistler 鸟的记录应为(这里值之间的空白是一个定位符) ：name owner species sex birth deathWhistler Gwen bird N 1997-12-09 N要想

29、将文本文件“pet.txt”装载到 pet 表中，使用这个命令：mysql LOAD DATA LOCAL INFILE /path/pet.txt INTO TABLE pet;请注意如果用 Windows 中的编辑器（使用 rn 做为行的结束符）创建文件，应使用：mysql LOAD DATA LOCAL INFILE /path/pet.txt INTO TABLE pet- LINES TERMINATED BY rn;（在运行 OS X 的 Apple 机上，应使用行结束符r。）如果你愿意，你能明确地在 LOAD DATA 语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行

30、符。这对读入文件“pet.txt”的语句已经足够。如果该语句失败，可能是你安装的 MySQL 不与使用默认值的本地文件兼容。关于如何更改请参见 5.6.4节，“LOAD DATA LOCAL 安全问题”。如果想要一次增加一个新记录，可以使用 INSERT 语句。最简单的形式是，提供每一列的值，其顺序与CREATE TABLE 语句中列的顺序相同。假定 Diane 把一只新仓鼠命名为 Puffball，你可以使用下面的INSERT 语句添加一条新记录：mysql INSERT INTO pet- VALUES (Puffball,Diane,hamster,f,1999-03-30,NULL);

31、注意，这里字符串和日期值均为引号扩起来的字符串。另外，可以直接用 INSERT 语句插入 NULL 代表不存在的值。不能使用 LOAD DATA 中所示的的N。从这个例子，你应该能看到涉及很多的键入用多个 INSERT 语句而非单个 LOAD DATA 语句装载你的初始记录。3.3.4. 从表检索信息3.3.4.1. 选择所有数据 3.3.4.2. 选择特殊行 3.3.4.3. 选择特殊列 3.3.4.4. 分类行 3.3.4.5. 日期计算 3.3.4.6. NULL 值操作 3.3.4.7. 模式匹配 3.3.4.8. 计数行 3.3.4.9. 使用 1 个以上的表SELECT 语句用来从

32、数据表中检索信息。语句的一般格式是：SELECT what_to_selectFROM which_tableWHERE conditions_to_satisfy;what_to_select 指出你想要看到的内容，可以是列的一个表，或*表示 “所有的列”。which_table 指出你想要从其检索数据的表。WHERE 子句是可选项，如果选择该项，conditions_to_satisfy 指定行必须满足的检索条件。3.3.4.1. 选择所有数据SELECT 最简单的形式是从一个表中检索所有记录：mysql SELECT * FROM pet;+-+-+-+-+-+-+| name | ow

33、ner | 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 | 1979-08-31 | 1995-07-29 | Chirpy | G

35、979。至少有两种修正方法：编辑文件“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;UPDATE 只更改有问题的记录，不需要重新装载数据库表。3.3.4.2. 选择特殊行如

37、+-+-+-+-+-+| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |+-+-+-+-+-+-+输出证实正确的年份记录为 1989，而不是 1979。字符串比较时通常对大小些不敏感，因此你可以将名字指定为“bowser“、“BOWSER“等，查询结果相同。你可以在任何列上指定条件，不只仅仅是 name。例如，如果你想要知道哪个动物在 1998 以后出生的，测试 birth 列：mysql SELECT * FROM pet WHERE birth 1998-1-1;+-+-+-+-+-+-+| name | owner | speci

41、 death |+-+-+-+-+-+-+| Claws | Gwen | cat | m | 1994-03-17 | NULL | Buffy | Harold | dog | f | 1989-05-13 | NULL |+-+-+-+-+-+-+3.3.4.3. 选择特殊列如果你不想看到表中的所有行，就命名你感兴趣的列，用逗号分开。例如，如果你想要知道你的动物什么时候出生的，选择 name 和 birth 列：mysql SELECT name, birth FROM pet;+-+-+| name | birth |+-+-+| Fluffy | 1993-02-04 | Claws

展开阅读全文