Unit5Database

1、Unit 5 DatabaseText A Intriduction MySQLlThe MySQL database system uses a client-server architecture that centers around the server. lMYSQL数据库系统使用的是客户端-服务器模式，这种模式以服务器为中心。 lThe server is the program that actually manipulates databases.l服务器是真正操作数据库的程序。 lClient programs dont do that directly. l客户端程序不直接

2、操作。l Instead, they communicate your intent to the server by means of statements written in Structured Query Language (SQL). l然而，客户端通过写结构化查询语言（SQL）语句的方法来满足你的意图。 l Client programs are installed locally on the machine from which you want to access MySQL, but the server can be installed anywhere, as lon

3、g as clients can connect to it. l客户端程序安装在你想访问MySQL的本地计算机上，但是服务器可以安装在任何地方，只要客户机能连接上它。 lMySQL is an inherently networked database system, so clients can communicate with a server that is running locally on your machine or one that is running somewhere else, perhaps on a machine on the other side of th

4、e planet. lMySQL是与生俱来地网络数据库系统，所以，客服端能和你本地机上的或者运行在其他地方的服务器通信，可能这台机器在地球的另一端。 lClients can be written for many different purposes, but each interacts with the server by connecting to it, sending SQL statements to it to have database operations performed, and receiving the statement results from it. l客户

5、端可以写很多不同的命令，但是每次是通过连接服务器来相互影响，客户端发送SQL语句去执行数据库操作，以及从服务器接收语句结果。 lOne such client is the mysql program that is included in MySQL distributions. l一个这样的客户端是一个包括在MySQL分布的mysql程序。 l When used interactively, mysql prompts you for a statement, sends it to the MySQL server for execution, and then displays th

6、e results. l当交互式地使用时，mysql将通过语句提醒你，把语句发送到MySQL服务器去执行，此时显示结果。 lThis capability makes mysql useful in its own right, but its also a valuable tool to help you with your MySQL programming activities. l这个能力使得mysql在它自己的领域非常有用，而且它也是一个有价值的工具去帮你使用你的MySQL程序。 lIts often convenient to be able to quickly review

7、the structure of a table that youre accessing from within a script, to try a statement before using it in a program to make sure that it produces the right kind of output, and so forth. l它能方便快速地查看结构化表，这个表你从脚本中获得，试着使用某个脚本，能使得程序得到正确的输出。 lmysql is just right for these jobs. lmysql非常适合这些工作。 lmysql also

8、can be used noninteractively; for example, to read statements from a file or from other programs. lmysql同时也能使用在非交互方式；比如，从文件或者从其他程序阅读语句。 lThis enables you to use mysql from within scripts or cron jobs or in conjunction with other applications.l使用mysql让你有能力使用原稿或者计划中的工作或者和其他程序同时发生。lThis chapter describ

9、es mysqls capabilities so that you can use it more effectively:l这一章是介绍mysql的章节，所以你可以更有效地使用它：lStarting and stopping mysql l开始和停止mysqllSpecifying connection parameters and using option filesl说明连接参数和使用选择文件 lSetting your PATH variable so that your command interpreter can find mysql (and other MySQL prog

10、rams)l设置你的路径可变，所以你的命令注释器能发现mysql（和其他MySQL程序） lIssuing SQL statements interactively and using batch filesl交互式地发出SQL语句和使用批文件 lCanceling and editing statementsl取消或编辑语句lControlling mysql output format l控制mysql输出格式 lTo use the examples shown in this book, youll need a MySQL user account and a database to

11、 work with. l为了演示书上的实验，你需要一个MySQL用户账号和一个数据库。 lThe first two sections of the chapter describe how to use mysql to set these up. l这章的前两节介绍的是怎么使用mysql去实现这些。 lFor demonstration purposes, the examples assume that youll use MySQL as follows: l作为示范，这些例子假定你将使用MySQL如下：lThe MySQL server is running on the loca

12、l hostlMySQL的服务器是运行在本地机上lYour MySQL username and password are cbuser and cbpass l你的MySQL用户名和密码是cbuser和cbpasslYour database is named cookbook l你的数据库命名为cookbooklFor your own experimentation, you can violate any of these assumptions. Your server need not be running locally, and you need not use the use

13、rname , password, or database name that are used in this book. l为了（达到）你自己的实践（要求），你可以不按照这些假设（的条件）。你的服务器不必运行在本地机，以及你不必使用在这本书中的用户名，密码，以及数据库名。 lNaturally, if you use different default values on your system, youll need to change the examples accordingly.l自然地，如果你在你的系统中使用不同系统默认值，你需要改变示例。lEven if you do not

14、 use cookbook as the name of your database, I recommend that you create a database to be dedicated specifically to trying the examples shown here, rather than trying them with a database that youre using currently for other purposes. l甚至你使用cookboo做为你数据库名字，我推荐你创建一个专用的数据库去用这里的例子，或者是用在其它的目的。 lOtherwise

15、, the names of your existing tables may conflict with those used in the examples, and youll have to make modifications to the examples that are unnecessary when you use a separate database.l否则，你目前表的名字可能和现在的例子有了冲突，这时你得分开使用数据库。lIf you have another favorite client program to use for issuing queries, so

16、me of the concepts covered in this chapter may not apply. l如果你有其他的客户端程序需要进行查询的话，这章的一些概念可能不适合。 lFor example, you might prefer the graphical MySQL Query Browser program, which provides a point-and-click interface to MySQL databases. l比如，你可能喜欢图形化MySQL Query Browser程序，它向MySQL数据库提供了一个（point-and-click）接口。

17、 lIn this case, some of the principles will be different, such as the way that you terminate SQL statements. l在这种情况下，一些原则可能不同，例如你终止SQL语句。 lIn mysql, you terminate statements with semicolon (;) characters, whereas in MySQL Query Browser there is an Execute button for terminating statements. l在mysql中，

18、终止语句使用分号，然而在MySQL Query Browser有一个Execute按钮是用来执行终止语句的。 lAnother popular interface is phpMyAdmin, which enables you to access MySQL through your web browser.l另一个流行接口是phpMyAdmin，它能让你通过网路浏览器访问MySQL。Text B Performing TransactionslThe MySQL server can handle multiple clients at the same time because it i

19、s multithreaded. lMySqL服务器能在同一时间并联处理多客户端，因为它是多线程的。 lTo deal with contention among clients, the server performs any necessary locking so that two clients cannot modify the same data at once. l为了处理与客户端连接，服务器需要上锁，避免两个客户端程序同时修改相同数据。 lHowever, as the server executes SQL statements, its very possible that

20、 successive statements received from a given client will be interleaved with statements from other clients. l然而，当服务器执行SQL语句，从一个客户端接收的不间断的语句可能被来自其它的客户端的语句相互交替。lIf a client issues multiple statements that are dependent on each other, the fact that other clients may be updating tables in between those

21、statements can cause difficulties. l如果一个客户端发出彼此不相关联的多条语句，事实是其他的客户端可能对表进行更新，这样就造成麻烦。 lStatement failures can be problematic, too, if a multiple-statement operation does not run to completion. l语句的失败会带来问题，例如，如果一个多行语句操作没有完全运行的话。 lSuppose that you have a flight table containing information about airline

22、 flight schedules and you want to update the row for Flight 578 by choosing a pilot from among those available. l假如你有一个表包含飞机飞行时间表的信息，你通过选择一个飞行员更新航班578这一行。 lYou might do so using three statements as follows:l你可能按照下面三条语句执行：lThe first statement chooses one of the available pilots, the second marks the

23、pilot as unavailable, and the third assigns the pilot to the flight. l第一条语句选择飞行员，第二条语句是对不可获得的飞行员设置标志，第三条语句分配飞行员去飞行。lThats straightforward enough in practice, but in principle there are a couple of significant difficulties with the process:l在实践中就是这么简单，但是理论上有两个重要的难点去处理： lConcurrency issuesl并发问题lIf two

24、 clients want to schedule pilots, its possible that both of them would run the initial SELECT query and retrieve the same pilot ID number before either of them has a chance to set the pilots status to unavailable. l如果两个客户端想去安排飞行员，那很有可能两个都去运行最初的SELECT查询，并且对同一个飞行员的ID号进行检索，这样的话，两端都有可能将飞行员的状态设置为不可获取。lIf

25、 that happens, the same pilot would be scheduled for two flights at once. l如果这样的话，同个飞行员可能被安排同一时间飞行两次。lIntegrity issuesl完整性话题lAll three statements must execute successfully as a unit. l这三条语句必须作为整体一次性执行成功。 l For example, if the SELECT and the first UPDATE run successfully, but the second UPDATE fails,

26、 the pilots status is set to unavailable without the pilot being assigned a flight. l比如，如果SELECT和第一个UPDATE执行成功，但是第二个UPDATE失败，飞行员的状态被设置为不可得，且飞行员也没有分配一次飞行。 lThe database will be left in an inconsistent state.l这个数据库将会是不一致的状态。 lTo prevent concurrency and integrity problems in these types of situations,

27、transactions are helpful. l为了防止并行与完整性问题的发生， transactions是需要的。lA transaction groups a set of statements and guarantees the following properties: l一个事务将一系列语句与属性进行了组合：lNo other client can update the data used in the transaction while the transaction is in progress; l没有其他客户机能更新那些被用在处理中的数据； lFor example,

28、 other clients cannot modify the pilot or flight records while youre booking a pilot for a flight. l比如，当你为飞机预定一个飞行员后，其他客户端程序不能修改飞行员或者飞行记录。 lBy preventing other clients from interfering with the operations youre performing, transactions solve concurrency problems arising from the multiple-client nature of the My