MYSQL学习笔记教程.doc

第3章：教程目录3.1. 连接与断开服务器3.2. 输入查询3.3. 创建并使用数据库3.3.1. 创建并选择数据库3.3.2. 创建表3.3.3. 将数据装入表中3.3.4. 从表检索信息3.4. 获得数据库和表的信息3.5. 在批处理模式下使用mysql3.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_increment3.7. 孪生项目的查询3.7.1. 查找所有未分发的孪生项3.7.2. 显示孪生对状态的表3.8. 与apache一起使用mysql本章通过演示如何使用mysql客户程序创造和使用一个简单的数据库，提供一个mysql的入门教程。mysql（有时称为“终端监视器”或只是“监视”）是一个交互式程序，允许你连接一个mysql服务器，运行查询并察看结果。mysql可以用于批模式：你预先把查询放在一个文件中，然后告诉mysql执行文件的内容。本章将介绍使用mysql的两个方法。要想查看由mysql提供的选择项目表，可以用-help选项来调用：shell mysql -help本章假定mysql已经被安装在你的机器上，并且有一个mysql服务器可以连接。否则，请联络mysql管理员。（如果你是管理员，则需要查阅本手册的其它章节，例如第5章：数据库管理。）本章描述建立和使用一个数据库的全过程。如果你仅仅对访问一个已经存在的数据库感兴趣，可以跳过描述怎样创建数据库及它所包含的表的章节。由于本章是一个教程，省略了许多细节。关于这里所涉及的主题的详细信息，请查阅本手册的相关章节。3.1.连接与断开服务器为了连接服务器，当调用mysql时，通常需要提供一个mysql用户名并且很可能需要一个 密码。如果服务器运行在登录服务器之外的其它机器上，还需要指定主机名。联系管理员以找出进行连接所使用的参数 (即，连接的主机、用户名和使用的密码)。知道正确的参数后，可以按照以下方式进行连接：shell mysql -h host -u user -penter password: *host和user分别代表mysql服务器运行的主机名和mysql账户用户名。设置时替换为正确的值。* 代表你的密码；当mysql显示enter password:提示时输入它。如果有效，你应该看见mysql提示符后的一些介绍信息：shell mysql -h host -u user -penter password: *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与该服务器连接：shell mysql成功地连接后，可以在mysql提示下输入quit (或q)随时退出：mysql quitbye在unix中，也可以按control-d键断开服务器。在下列章节的大多数例子都假设你连接到了服务器。由mysql提示指明。3.2.输入查询确保你连接上了服务器，如在先前的章节讨论的。连接上服务器并布代表选择了任何数据库，但这样就可以了。知道关于如何查询的基本知识，比马上跳至创建表、给他们装载数据并且从他们检索数据更重要。本节描述输入命令的基本原则，使用几个查询，你能尝试了解mysql是如何工作的。这是一个简单的命令，要求服务器告诉它的版本号和当前日期。在mysql提示输入如下命令并按回车键：mysql select version(), current_date;+-+-+| version() | current_date |+-+-+| 5.1.2-alpha-log | 2005-10-11 |+-+-+1 row in set (0.01 sec)mysql这询问说明mysql的几个方面: 一个命令通常由sql语句组成，随后跟着一个分号。（有一些例外不需要分号。早先提到的quit是一个例子。后面我们将看到其它的例子。） 当发出一个命令时，mysql将它发送给服务器并显示执行结果，然后显示另一个mysql显示它准备好接受其它命令。 mysql用表格(行和列)方式显示查询输出。第一行包含列的标签，随后的行是查询结果。通常，列标签是你取自数据库表的列的名字。如果你正在检索一个表达式而非表列的值(如刚才的例子)，mysql用表达式本身标记列。 mysql显示返回了多少行，以及查询花了多长时间，它给你提供服务器性能的一个大致概念。因为他们表示时钟时间(不是 cpu 或机器时间)，并且因为他们受到诸如服务器负载和网络延时的影响，因此这些值是不精确的。（为了简洁，在本章其它例子中不再显示“集合中的行”。）能够以大小写输入关键词。下列查询是等价的：mysql select version(), current_date;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(); select 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;+-+-+| user() | current_date |+-+-+| jonlocalhost | 2005-10-11 |+-+-+在这个例子中，在输入多行查询的第一行后，要注意提示符如何从mysql变为-，这正是mysql如何指出它没见到完整的语句并且正在等待剩余的部分。提示符是你的朋友，因为它提供有价值的反馈，如果使用该反馈，将总是知道mysql正在等待什么。如果你决定不想执行正在输入过程中的一个命令，输入c取消它：mysql select - user() - cmysql这里也要注意提示符，在你输入c以后，它切换回到mysql，提供反馈以表明mysql准备接受一个新命令。下表显示出可以看见的各个提示符并简述它们所表示的mysql的状态：提示符含义mysql准备好接受新命令。-等待多行命令的下一行。等待下一行，等待以单引号(“”)开始的字符串的结束。等待下一行，等待以双引号(“”)开始的字符串的结束。等待下一行，等待以反斜点()开始的识别符的结束。/*等待下一行，等待以/*开始的注释的结束。当你打算在一个单行上发出一个命令时，通常会“偶然”出现多行语句，但是没有终止分号。在这种情况中，mysql等待进一步输入：mysql select user() -如果出现这种情况(你认为输完了语句，但是只有一个-提示符响应)，很可能mysql正在等待分号。如果你没有注意到提示符的提示，在意识到你需要做什么之前，你可能会呆坐一会儿。输入一个分号完成语句，mysql将执行：mysql select user() - ;+-+| user() |+-+| jonlocalhost |+-+在字符串收集期间将出现 和 提示符（提示mysql正等待字符串的结束）。在mysql中，可以写由或字符括起来的字符串 (例如，hello或goodbye)，并且mysql允许输入跨越多行的字符串。当看到一个 或 提示符时，这意味着已经输入了包含以或括号字符开始的字符串的一行，但是还没有输入终止字符串的匹配引号。这显示你粗心地省掉了一个引号字符。例如：mysql select * from my_table where name = smith and age 如果你输入select语句，然后按enter（回车）键并等待结果，什么都没有出现。不要惊讶，“为什么该查询这么长呢？”，注意提示符提供的线索。它告诉你mysql期望见到一个未终止字符串的余下部分。（你看见语句中的错误吗？字符串smith丢掉了第二个引号。）走到这一步，你该做什么？最简单的是取消命令。然而，在这种情况下，你不能只是输入c，因为mysql作为它正在收集的字符串的一部分来解释它！相反，应输入关闭的引号字符(这样mysql知道你完成了字符串)，然后输入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. 将数据装入表中3.3.4. 从表检索信息知道怎样输入命令，便可以访问数据库了。假定在你的家(你的“动物园”)中有很多宠物，并且你想跟踪关于它们各种类型的信息。你可以通过创建表来保存你的数据并根据所需要的信息装载他们，然后你可以从表中检索数据来回答关于动物不同种类的问题。本节显示如何做到所有这些事情： 创建数据库 创建数据库表 装载数据到数据库表 以各种方法从表中检索数据 使用多个表动物园数据库很简单(特意的)，但是不难把它想象成可能用到类似数据库的真实世界情况。例如，农夫可以使用这样的一个数据库来追踪家畜，或者兽医可以用它跟踪病畜记录。从mysql网址上可以获得后面章节中将用到的含有部分查询和样例数据的动物园分发。有tar压缩格式 (/downloads/contrib/examples/menagerie.tar.gz)和zip压缩格式 (/downloads/contrib/examples/menagerie.zip)。使用show语句找出服务器上当前存在什么数据库：mysql show databases;+-+| database |+-+| mysql | test | tmp |+-+可能你的机器上的数据库列表是不同的，但是很可能有mysql和test数据库。mysql是必需的，因为它描述用户访问权限，test数据库经常作为用户试身手的工作区。请注意如果没有show databases权限，则不能看见所有数据库。参见节，“grant和revoke语法”。如果test数据库存在，尝试访问它：mysql use testdatabase changed注意，use，类似quit，不需要一个分号。（如果你喜欢，你可以用一个分号终止这样的语句；这无碍）use语句在使用上也有另外一个特殊的地方：它必须在一个单行上给出。你可列在后面的例子中使用test数据库(如果你能访问它)，但是你在该数据库创建的任何东西可以被访问它的其它人删除，因此，你应该询问mysql管理员许可你使用自己的一个数据库。假定你想要调用你的menagerie，管理员需要执行这样一条命令：mysql grant all on menagerie.* to your_mysql_nameyour_client_host;这里your_mysql_name是分配给你的mysql用户名，your_client_host是所连接的服务器所在的主机。3.3.1.创建并选择数据库如果管理员在设置权限时为你创建了数据库，你可以开始使用它。否则，你需要自己创建数据库：mysql create database menagerie;在unix下，数据库名称是区分大小写的(不像sql关键字)，因此你必须总是以menagerie访问数据库，而不能用menagerie、menagerie或其它一些变量。对表名也是这样的。（在windows下，该限制不适用，尽管你必须在一个给定的查询中使用同样的大小写来引用数据库和表。但是，由于多种原因，作为最好的惯例，一定要使用与数据库创建时的同样的大小写。）创建数据库并不表示选定并使用它，你必须明确地操作。为了使menagerie成为当前的数据库，使用这个命令：mysql use menageriedatabase changed数据库只需要创建一次，但是必须在每次启动mysql会话时在使用前先选择它。你可以根据上面的例子执行一个use语句来实现。还可以在调用mysql时，通过命令行选择数据库，只需要在提供连接参数之后指定数据库名称。例如：shell mysql -h host -u user -p menagerieenter password: *注意，刚才显示的命令行中的menagerie不是你的 密码。如果你想要在命令行上在-p选项后提供 密码，则不能插入空格(例如，如-pmypassword，不是-p mypassword)。但是，不建议在命令行输入密码，因为这样会暴露 密码，能被在机器上登录的其它用户窥探到。3.3.2.创建表创建数据库是很容易的部分，但是在这时它是空的，正如show tables将告诉你的：mysql show tables;empty set (0.00 sec)较难的部分是决定你的数据库结构应该是什么：你需要什么数据库表，各数据库表中有什么样的列。你将需要一个包含你每个宠物的记录的表。它可称为pet表，并且它应该包含，最少，每个动物的名字。因为名字本身不是很有趣，表应该包含另外的信息。例如，如果在你豢养宠物的家庭有超过一个人，你可能想要列出每个动物的主人。你可能也想要记录例如种类和性别的一些基本的描述信息。年龄呢？那可能有趣，但是存储到一个数据库中不是一件好事情。年龄随着时间流逝而变化，这意味着你将要不断地更新你的记录。相反, 存储一个固定值例如生日比较好，那么，无论何时你需要年龄，可以以当前日期和出生日期之间的差来计算它。mysql提供了日期运算函数，因此这并不困难。存储出生日期而非年龄还有其它优点： 你可以使用数据库完成这样的任务，例如生成即将到来的宠物生日的提示。（如果你认为这类查询有点蠢，注意，这与从商务数据库来识别出不久要发给生日祝贺的客户是同一个问题，因为计算机帮助私人联络。） 你可以相对于日期而不止是当前日期来计算年龄。例如，如果你在数据库存储死亡日期，你能很容易地计算出一只宠物死时有多大。你可能想到pet表中其它有用的其它类型信息，但是到目前为止这些已经足够了：名字、主人、种类，性别、出生和死亡日期。使用一个create table语句指定你的数据库表的布局：mysql create table pet (name varchar(20), owner varchar(20), - species 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列应选用date数据类。创建了数据库表后，show tables应该产生一些输出：mysql show tables;+-+| tables in menagerie |+-+| pet |+-+为了验证你的表是按你期望的方式创建，使用一个describe语句：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 | |+-+-+-+-+-+-+你可以随时使用describe，例如，如果你忘记表中的列的名称或类型时。3.3.3.将数据装入表中创建表后，需要填入内容。通过load data和insert语句可以完成该任务。假定你的宠物纪录描述如下。（假定在mysql中期望的日期格式是yyyy-mm-dd；这可能与你习惯的不同。）nameownerspeciessexbirthdeathfluffyharoldcatf1993-02-04clawsgwencatm1994-03-17buffyharolddogf1989-05-13fangbennydogm1990-08-27bowserdianedogm1979-08-311995-07-29chirpygwenbirdf1998-09-11whistlergwenbird1997-12-09slimbennysnakem1996-04-29因为你是从一个空表开始的，填充它的一个简易方法是创建一个文本文件，每个动物各一行，然后用一个语句将文件的内容装载到表中。你可以创建一个文本文件“pet.txt”保啃邪桓黾锹迹枚环?(tab)把值分开，并且以create table语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用null值。为了在你的文本文件中表示这些内容，使用n（反斜线，字母n）。例如，whistler鸟的记录应为(这里值之间的空白是一个定位符)：nameownerspeciessexbirthdeathwhistlergwenbirdn1997-12-09n要想将文本文件“pet.txt”弊霸氐絧et表中，使用这个命令：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语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。这对读入文件“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);注意，这里字符串和日期值均为引号扩起来的字符串。另外，可以直接用insert语句插入null代表不存在的值。不能使用load data中所示的的n。从这个例子，你应该能看到涉及很多的键入用多个insert语句而非单个load data语句装载你的初始记录。3.3.4.从表检索信息. 选择所有数据. 选择特殊行. 选择特殊列. 分类行. 日期计算. null值操作. 模式匹配. 计数行. 使用1个以上的表select语句用来从数据表中检索信息。语句的一般格式是：select what_to_selectfrom which_tablewhere conditions_to_satisfy;what_to_select指出你想要看到的内容，可以是列的一个表，或*表示“所有的列”。which_table指出你想要从其检索数据的表。where子句是可选项，如果选择该项，conditions_to_satisfy指定行必须满足的检索条件。.选择所有数据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 | 1979-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，而不是1979。至少有两种修正方法： 编辑文件“pet.txt”备恼砦螅缓笫褂肈elete和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只更改有问题的记录，不需要重新装载数据库表。.选择特殊行如上所示，检索整个表是容易的。只需要从select语句中删掉where子句。但是一般你不想看到整个表，特别地当表变得很大时。相反，你通常对回答一个具体的问题更感兴趣，在这种情况下在你想要的信息上进行一些限制。让我们看一些他们回答的有关你宠物的问题的选择查询。可以从表中只选择特定的行。例如，如果你想要验证你对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，而不是1979。字符串比较时通常对大小些不敏感，因此你可以将名字指定为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可以混用，但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 |+-+-+-+-+-+-+.选择特殊列如果你不想看到表中的所有行，就命名你感兴趣的列，用逗号分开。例如，如果你想要知道你的动物什么时候出生的，选择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 |+-+-+-+.分类行你可能已经注意到前面的例子中结果行没有以特定的顺序显示。然而，当行按某种方式排序时，检查查询输出通常更容易。为了排序结果，使用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 |+-+-+在字符类型列上，与所有其他比较操作类似，分类功能正常情况下是以区分大小写的方式执行的。这意味着，对于等同但大小写不同的列，并未定义其顺序。对于某一列，可以使用binary强制执行区分大小写的分类功能，如：order by binary col_name. 默认排序是升序，最小的值在第一。要想以降序排序，在你正在排序的列名上增加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-