Mysql your visual blueprint for creating open source databases- P1

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:20

Thêm vào BST

Báo xấu

99
lượt xem 7
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Mysql your visual blueprint for creating open source databases- P1:The show tables command displays a list of tables available in the currently selected database. You can use this command when you are unsure of the exact name of a table within the database.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Mysql your visual blueprint for creating open source databases- P1

MySQL ™ Your visual blueprint ™ to open source database management by Michael Moncur TM From ® &
MySQLTM: Your visual blueprintTM to open source database management Published by FOR PURPOSES OF ILLUSTRATING THE CONCEPTS Wiley Publishing, Inc. 909 Third Avenue AND TECHNIQUES DESCRIBED IN THIS BOOK, THE New York, NY 10022 AUTHOR HAS CREATED VARIOUS NAMES, COMPANY Published simultaneously in Canada NAMES, MAILING, E-MAIL AND INTERNET ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR Copyright © 2003 Wiley Publishing, Inc., Indianapolis, Indiana INFORMATION, ALL OF WHICH ARE FICTITIOUS. ANY Certain designs and text Copyright © 1992-2003 maranGraphics, Inc., used with RESEMBLANCE OF THESE FICTITIOUS NAMES, maranGraphics' permission. ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR maranGraphics, Inc. INFORMATION TO ANY ACTUAL PERSON, COMPANY 5755 Coopers Avenue AND/OR ORGANIZATION IS UNINTENTIONAL AND Mississauga, Ontario, Canada PURELY COINCIDENTAL. L4Z 1R9 Important Numbers No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, For U.S. corporate orders, please call maranGraphics at recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission 800-469-6616 or fax 905-890-9434. of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, For general information on our other products and services (978) 750-8400, fax (978) 750-4744. Requests to the Publisher for permission or to obtain technical support, please contact our Customer should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Care Department within the U.S. at 800-762-2974, outside Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, E-mail: permcoordinator@wiley.com. the U.S. at 317-572-3993, or fax 317-572-4002. Library of Congress Control Number: 2002110260 ISBN: 0-7645-1692-2 Permissions Manufactured in the United States of America maranGraphics 10 9 8 7 6 5 4 3 2 1 Certain text and illustrations by maranGraphics, Inc., used 1V/SR/RQ/QS/IN with maranGraphics' permission. Trademark Acknowledgments Was it a monument? A temple? Perhaps the giant Pyramid of Kukulkán, called El Castillo by Spanish visitors to the ancient Mayan city of Wiley, the Wiley Publishing logo, Visual, the Visual logo, Chichén-Itzá, was both, and more. Built with mathematical precision, it dominates the city from every direction, and is among the most Simplified, Master VISUALLY, Teach Yourself VISUALLY, Visual recognized ancient landmarks. Explore the mysteries of Chichén-Itzá in Blueprint, In an Instant, Read Less – Learn More and related Frommer's Cancún, Cozumel and the Yucatán, available wherever books trade dress are trademarks or registered trademarks of Wiley are sold or at www.frommers.com. Publishing, Inc. in the United States and other countries and may not be used without written permission. The maranGraphics logo is a trademark or registered trademark of maranGraphics, Inc. MySQL is a trademark or registered trademark of MySQL AB Company. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. and maranGraphics, Inc. are not associated with any product or vendor mentioned in this book. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHOR HAVE USED THEIR BEST EFFORTS IN PREPARING THIS BOOK, THEY MAKE NO REPRESENTATIONS OR is a trademark of WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS Wiley Publishing, Inc. OF THE CONTENTS OF THIS BOOK AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE U.S. Corporate Sales U.S. Trade Sales ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A PROFESSIONAL Contact maranGraphics Contact Wiley WHERE APPROPRIATE. NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL at (800) 469-6616 or at (800) 762-2974 DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, fax (905) 890-9434. or fax (317) 572-4002. CONSEQUENTIAL, OR OTHER DAMAGES.
MySQL TM Your visual blueprintTM to open source database management
maranGraphics is a family-run business located near Toronto, Canada. At maranGraphics, we believe in producing great rebuild every screen shot to provide the best clarity computer books — one book at a time. possible, making our screen shots the most precise and easiest to read in the industry. We strive for perfection, maranGraphics has been producing high-technology and believe that the time spent handcrafting each products for over 25 years, which enables us to offer the element results in the best computer books money computer book community a unique communication can buy. process. Thank you for purchasing this book. We hope you Our computer books use an integrated communication enjoy it! process, which is very different from the approach used in other computer books. Each spread is, in essence, a Sincerely, flow chart — the text and screen shots are totally incorporated into the layout of the spread. Introductory Robert Maran text and helpful tips complete the learning experience. President maranGraphics' approach encourages the left and right maranGraphics sides of the brain to work together — resulting in faster Rob@maran.com orientation and greater memory retention. www.maran.com Above all, we are very proud of the handcrafted nature of our books. Our carefully-chosen writers are experts in their fields, and spend countless hours researching and organizing the content for each topic. Our artists
CREDITS Acquisitions, Editorial, and Production Media Development Book Design Project Editor maranGraphics® Sarah Hellert Production Coordinator Acquisitions Editor Nancee Reeves Jen Dorsey Layout Product Development Supervisor Beth Brooks Lindsay Sandman Melanie DesJardins Copy Editor Carrie Foster Jill Mazurczyk LeAndra Johnson Kristin McMullan Technical Editor Heather Pope Scott Hofmann Erin Zeltner Editorial Manager Screen Artists Rev Mengle Jill A. Proll Permissions Editor Cover Illustration Carmen Krikorian David E. Gregory Media Development Specialist Proofreader Megan Decraene Laura L. Bowman Manufacturing Quality Control Allan Conley John Bitter Linda Cook Dave Faust Paul Gilchrist John Greenough Jennifer Guynn Angel Perez Dwight Ramsey Indexer Liz Cunningham Special Help Cricket A. Krengel ACKNOWLEDGMENTS Wiley Technology Publishing Group: Richard Swadley, Vice President and Executive Group Publisher; Bob Ipsen, Vice President and Executive Publisher; Barry Pruett, Vice President and Publisher; Joseph Wikert, Vice President and Publisher; Mary Bednarek, Editorial Director; Mary C. Corder, Editorial Director; Andy Cummings, Editorial Director. Wiley Production for Branded Press: Debbie Stailey, Production Director
ABOUT THE AUTHOR Michael Moncur is the author of many books on system administration and Web development topics. He has recently written books about JavaScript and DHTML. He works as an independent network consultant and Web programmer. Michael also manages and maintains several popular Web sites. His oldest and most popular site, The Quotations Page, has been online since 1994. Most of the site's content is stored in a MySQL database, and presented using PHP. Michael lives with his wife, Laura, in a suburb of Salt Lake City, Utah. AUTHOR'S ACKNOWLEDGMENTS I am continually amazed at how complicated it is to write and publish a computer book. Fortunately, a great deal of this complexity was handled smoothly by the team at Wiley. This began with Jen Dorsey, the acquisitions editor, who was instrumental in getting the project started and keeping it running smoothly. The project editor, Sarah Hellert, communicated with me constantly, managed every detail of the production, and helped me figure out the intricacies of the visual format I have grown to love. The copy editor, Jill Mazurczyk, saved me from many embarrassing errors and kept the style consistent. The technical editor, Scott Hofmann, painstakingly tested all of the examples, helped me avoid technical errors, and made some valuable suggestions about content and coverage. Thanks are also due to the many people in the graphics and production departments who handled the final details. I'd also like to thank Neil Salkind at Studio B, for his help in getting this project lined up and keeping it going, and David and Sherry Rogelberg, Jessica Richards, and the rest of the team at Studio B. Finally, thanks to my wife, Laura, and the rest of my friends and family.
Dedicated to my wife, Laura, my parents, and everyone else who sees less of me while I'm busy writing.
TABLE OF CONTENTS HOW TO USE THIS BOOK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .XIV 1) INTRODUCING MYSQL Understanding MySQL ........................................................................................................2 MySQL Terminology ............................................................................................................4 Other Database Systems ....................................................................................................5 Structured Query Language (SQL) ....................................................................................6 Download MySQL ..............................................................................................................8 Install MySQL under Linux from a Package ....................................................................10 Install MySQL under UNIX from Source ........................................................................12 Install MySQL under Windows ........................................................................................14 Start the MySQL Server ....................................................................................................16 Test the MySQL Installation ..............................................................................................17 Using the MySQL Monitor ................................................................................................18 View the Server Status ......................................................................................................20 Try an SQL Query ..............................................................................................................21 Configure a MySQL User ..................................................................................................22 Specify a Multiple-Line Query ........................................................................................24 Edit a Long Command ......................................................................................................26 Configure MySQLGUI ......................................................................................................28 2) MANAGE DATABASES AND TABLES Design a Database ............................................................................................................30 Create and Drop Databases ............................................................................................32 Create a Database ..............................................................................................................33 Show Available Databases ................................................................................................34 Select a Database ..............................................................................................................35 Create and Drop Tables ....................................................................................................36 Create a Simple Table ........................................................................................................38 Show Table Information ....................................................................................................40 Numeric Column Types ....................................................................................................42 Text and Date Column Types ............................................................................................44 Using Text Columns ..........................................................................................................46 Using Sets and Enumerations ..........................................................................................48 Create an Indexed Table ..................................................................................................50 Delete Tables and Databases ............................................................................................52 viii
BOOK TITLE: MYSQL: Your visual blueprint toforthest Your visual blueprint open source database management of the book title 3) MODIFY TABLES Using ALTER TABLE Queries ..............................................................................................54 Add a Column to a Table ..................................................................................................56 Add an Index ......................................................................................................................58 Add a Primary Key or Unique Index ................................................................................60 Add a Timestamp Column ................................................................................................62 Add an Auto-Increment Column ....................................................................................64 Rename a Column ............................................................................................................66 Change a Column Type ....................................................................................................68 Delete a Column ................................................................................................................70 Delete an Index or Primary Key ......................................................................................72 Rename a Table ..................................................................................................................74 Change a Table Type ..........................................................................................................75 4) ADD AND DELETE DATA Using INSERT and DELETE Queries ..................................................................................76 Add a Row to a Table ........................................................................................................78 Specify Columns for a New Row ....................................................................................80 Specify Insert Priority ........................................................................................................81 Using Auto-Increment Columns ......................................................................................82 Using Timestamp Columns ..............................................................................................84 Replace an Existing Row ....................................................................................................86 Insert Rows from Another Table ......................................................................................88 Delete a Specific Row ......................................................................................................90 Delete All Table Rows ........................................................................................................92 Limit the Number of Deleted Rows ................................................................................93 Delete Data by Date ..........................................................................................................94 5) UPDATE DATA IN TABLES Using UPDATE Queries ......................................................................................................96 Modify a Single Row ..........................................................................................................98 Modify Multiple Rows ....................................................................................................100 Update All Table Rows ....................................................................................................102 ix
TABLE OF CONTENTS Limit the Number of Rows to Update ..........................................................................103 Update Multiple Columns ..............................................................................................104 Update Depending on Column Values ..........................................................................106 Increment a Column Value ............................................................................................108 6) USING SELECT QUERIES Using SELECT Queries ....................................................................................................110 Display Data with SELECT ..............................................................................................112 Using the WHERE Clause ................................................................................................114 Specify Multiple WHERE Conditions ............................................................................116 Using the LIMIT Clause ..................................................................................................118 Sort Data with the ORDER BY Clause ..........................................................................120 Using the GROUP BY Clause ..........................................................................................122 Using Multiple Clauses with SELECT ............................................................................123 Count Database Rows ....................................................................................................124 Calculate Totals and Averages ........................................................................................126 Display Data from Multiple Tables ................................................................................128 Using JOIN Options ........................................................................................................130 Display Data with MySQLGUI ........................................................................................132 7) USING MYSQL FUNCTIONS Math Functions ................................................................................................................134 Using Math Functions ......................................................................................................136 Compare Numeric Values ..............................................................................................138 Understanding String Functions ....................................................................................140 Compare String Values ....................................................................................................142 Using String Functions ....................................................................................................144 Work with Substrings ......................................................................................................146 Understanding Date and Time Functions ....................................................................148 Work with Dates and Times ..........................................................................................150 Display Formatted Dates ................................................................................................152 Add and Subtract Dates and Times ..............................................................................153 Miscellaneous Functions ................................................................................................154 x
MYSQL: Your visual blueprint to open source database management 8) IMPORT AND EXPORT DATA Import and Export Tools ................................................................................................156 Export to a Text File with SELECT ..................................................................................158 Format an Import File ......................................................................................................160 Import from a Text File ....................................................................................................162 Export Data from a Spreadsheet ....................................................................................164 Export Data from Microsoft Access ..............................................................................166 Back up Data from a Table ..............................................................................................168 Restore a Backup File ......................................................................................................169 Manage Automated Backups ..........................................................................................170 Back up Table Structure ..................................................................................................172 Create a Copy of a Table ................................................................................................173 9) MANAGE THE MYSQL SERVER MySQL Administration Tools ..........................................................................................174 Check Server Status ........................................................................................................176 Check the MySQL Server Version ..................................................................................177 Start and Stop the MySQL Server under UNIX ............................................................178 Start and Stop the MySQL Server Under Windows ....................................................180 View and Manage Running Processes ..........................................................................182 Using WinMySQLadmin ..................................................................................................184 Manage MySQL with MySQLGUI ..................................................................................186 10) OPTIMIZE AND TROUBLESHOOT MYSQL Optimize MySQL ............................................................................................................188 Check a Table for Errors ..................................................................................................190 Optimize and Repair Tables ............................................................................................192 MySQL Configuration Options ......................................................................................194 Display Server Performance Information ......................................................................196 Edit the Configuration File in UNIX ..............................................................................198 Edit the Configuration File in Windows ........................................................................199 xi
TABLE OF CONTENTS View MySQL Log Files ....................................................................................................200 Improve Performance with Indexes ..............................................................................202 Manage Table Locking ....................................................................................................204 11) CONFIGURE MYSQL SECURITY The Importance of Security ............................................................................................206 MySQL Security Basics ....................................................................................................207 Grant Privileges to Users ................................................................................................208 Modify User Passwords ..................................................................................................210 View a User's Privileges ..................................................................................................212 View Security Tables ........................................................................................................214 Control Network Access ................................................................................................216 Advanced MySQL Security ............................................................................................217 12) USING MYSQL WITH PHP Introducing PHP ..............................................................................................................218 Test the PHP Installation ................................................................................................220 Install and Test phpMyAdmin ........................................................................................222 Manage Data with phpMyAdmin ..................................................................................224 Manage the MySQL Server with phpMyAdmin ............................................................226 Connect to a MySQL Server ..........................................................................................228 Display Query Results ....................................................................................................230 Store Query Results as Objects ......................................................................................232 Insert a Record from PHP ..............................................................................................234 Delete Records Using PHP ..............................................................................................236 Create a Form to Add Records ......................................................................................238 Create a Database Search Form ....................................................................................240 13) USING MYSQL WITH PERL Introducing Perl ................................................................................................................242 Install Perl under UNIX ....................................................................................................244 Install Perl under Windows ............................................................................................246 xii
MYSQL: Your visual blueprint to open source database management Install the Perl DBI ..........................................................................................................248 Install the MySQL DBD ..................................................................................................249 Connect to a MySQL Server ..........................................................................................250 Display Query Results ....................................................................................................252 Insert a Record from Perl ................................................................................................254 Delete Records Using Perl ..............................................................................................255 Work with Web Forms ....................................................................................................256 APPENDIX A Glossary..............................................................................................................................258 APPENDIX B Create and Delete Tables and Databases ......................................................................260 Modify Tables with ALTER TABLE ..................................................................................262 Add and Delete Data ......................................................................................................263 Modify Existing Data with UPDATE ................................................................................264 Retrieve Data with SELECT ............................................................................................265 Display Status Information ............................................................................................266 Administrative Commands ..............................................................................................267 Command-Line Utilities ..................................................................................................268 APPENDIX C What's on the CD-ROM ..................................................................................................270 Using the E-Version of this Book ....................................................................................272 Wiley Publishing Inc. End User License Agreement ......................................................274 GNU General Public License ..........................................................................................276 INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .280 xiii
HOW TO USE THIS BOOK MySQL: Your visual blueprint to open source database The Conventions in This Book management uses simple, straightforward examples to A number of typographic and layout styles have been used teach you how to create powerful and dynamic programs. throughout MySQL: Your visual blueprint to open source To get the most out of this book, you should read each database management to distinguish different types of chapter in order, from beginning to end. Each chapter information. introduces new ideas and builds on the knowledge learned in previous chapters. When you become familiar with MySQL: Your visual blueprint to open source database Courier Font management, you can use this book as an informative desktop reference. Indicates the use of MySQL commands, command-line utilities, HTML tags and attributes, and commands in PHP or Perl. Who This Book Is For If you are interested in creating databases, applications, and data-driven Web sites using MySQL, MySQL: Your Bold visual blueprint to open source database management is Indicates information that you must type. the book for you. This book introduces you to the SQL language that forms the foundation of MySQL as well as the specific Italics commands, utilities, and features unique to MySQL. It Indicates a new term. also covers the use of MySQL with the popular PHP and Perl languages for creating dynamic, database-backed Web pages. Although this book requires no prior experience with databases or SQL, a basic familiarity with either Windows or UNIX servers is an asset. An Apply It section usually contains a segment of code that takes the lesson you just learned one step further. What You Need to Use This Book Apply It sections offer inside information and pointers that you can use to enhance the functionality of your code. To perform the tasks in this book, you need a computer to run the MySQL server. This can be either a machine running Windows 98, ME, NT, 2000, or XP; or a machine running a UNIX-based operating system such as Linux. An Extra section provides additional information about the The MySQL server software is available at no charge, and task you just accomplished. Extra sections often contain installing it on your system is detailed in Chapter 1. interesting tips and useful tricks to make working with MySQL easier and more efficient. Please note that the majority of the tasks in this book are performed using the MySQL monitor. Chapter 1 explains how to start the MySQL monitor. The tasks in later chapters assume that the reader knows how to start the monitor or another MySQL client. xiv
MYSQL: Your visual blueprint to open source database management The Organization of this Book Chapter 9, "Manage the MySQL Server," describes the commands you can use to start and stop the server, and MySQL: Your visual blueprint to open source database check version information and server status. It also management contains 13 chapters and three appendices. introduces Windows-based utilities that provide a friendly The first chapter, "Introducing MySQL," introduces the administration interface. basics of MySQL and describes the components of a Chapter 10, "Optimize and Troubleshoot MySQL," shows MySQL system. It shows you how to install MySQL's client you how to optimize tables, check for errors and repair and server software and to start client software, such as damaged tables, and check on and improve the the MySQL monitor. performance of a MySQL server. Chapter 2, "Manage Databases and Tables," introduces Chapter 11, "Configure MySQL Security," describes you to the MySQL commands for creating and deleting MySQL's security features and shows how you can use databases and tables. It describes the column types them to configure users, control privileges, and manage supported in MySQL tables and shows you how to passwords. create tables using them. Chapter 12, "Using MySQL with PHP," describes how you Chapter 3, "Modify Tables," introduces the MySQL can use the PHP language with MySQL to create database- commands for modifying tables and demonstrates various driven Web sites and other applications. changes you can make to existing tables, as well as indexes and table options. Chapter 13, "Using MySQL with Perl," shows how you can use the Perl language to connect to MySQL databases and Chapter 4, "Add and Delete Data," introduces the create dynamic Web sites and applications. commands you can use in MySQL to add data to tables and delete existing data. You are also shown how to use Appendix A is a glossary of common MySQL and database some special column types in MySQL tables. terms, which you'll find useful as you work your way through this book. Chapter 5, "Update Data in Tables," describes the MySQL commands for modifying data in existing tables. Tasks Appendix B is a reference with detailed information about demonstrate a variety of common procedures for updating the most important MySQL commands and utilities. the data in a table. Appendix C includes detailed information about the Chapter 6, "Using SELECT Queries," explains how you can CD-ROM included with this book. use SELECT commands to retrieve data from MySQL tables in a variety of ways. It begins with basic queries and proceeds to advanced options, such as multiple-table What's on the CD-ROM queries. The CD-ROM included in this book contains SQL files you Chapter 7, "Using MySQL Functions," introduces you to can use to create the example tables used throughout this the functions included with MySQL. You can use these book. It also includes several open-source software with any MySQL command to process numeric and text applications, including MySQL itself. Finally, the PHP and data and to test conditions. Perl code used in Chapters 12 and 13 is included in its entirety. An e-version of the book and all the URLs Chapter 8, "Import and Export Data," describes how to use mentioned in the book are also available on the disc. MySQL commands to export data to text files and import data into a MySQL table. It also explains how to export data to MySQL from applications like Microsoft Access and Excel. xv
MySQL UNDERSTANDING MYSQL advantages. A database system allows you to store and work W henever you need to store large amounts of data, whether for a corporate accounting system or a with many types of data, and a database server handles large Web site, a database provides many many of the data management tasks in an efficient way. DATABASE BASICS MySQL is a relational database management system, or This table includes three rows, or individual entries. Each RDBMS. This type of system stores one or more one stores a person's name and address information. databases, each of which can contain tables of data of Each item of information, such as an address or name, is various types. Here is a simple example of a table with called a column, also known as a field. names and addresses: NAME ADDRESS CITY STATE Henry J. Tillman 321 Elm St. Sacramento CA John Smith 122 Oak St. New York City NY Amy Johnson 333 Poplar Lane Chicago IL Relational Databases Client-Server Systems While it is quite useful to store data in simple tables like MySQL, like most RDBMS systems, is a client-server this, the strength of a relational database system is that system. In this type of system, a server manages the you can link data between multiple tables. For example, actual data storage. One or more clients can connect to you may have a separate table of names and phone the server, send it data or request data, and obtain their numbers: results from the server. NAME PHONE Client-server systems are powerful because the server can focus on data management without worrying about Henry J. Tillman 713-555-2395 the user interface or other issues. Different types of John Smith 212-555-9344 clients or multiple clients can connect to the server simultaneously. Amy Johnson 312-555-2904 The MySQL client sends a request, or query, to the Because this table uses the same names as the address server. This may be a request for a certain record or table, you can easily find the address and phone group of records from a database, a request to add data number for a name by connecting information in the to the database, or any of several other types of two tables. By looking at several different tables, the requests. The server receives the query and sends a database server can manage complex systems of data result back to the client. easily. 2
INTRODUCING MYSQL 1 HOW MYSQL WORKS MySQL consists of client components and server components. The basic components of MySQL are described in the sections below. Hardware Client Software The MySQL server runs on many different types of A simple MySQL client, the MySQL monitor, is included hardware. PC systems running Linux or 32-bit Windows with the server software. You can also use any client are the most common hardware used. The hardware that knows the correct protocols to communicate with required for the server depends on how busy the server the server. Another such client, MySQLGUI, is described will be with client requests. later in this chapter. Server Software Programming Languages The MySQL server software is available from MySQL In addition to the existing MySQL clients, you can use AB. You can find their Web page at www.mysql.com/. programming languages such as PHP and Perl to create The MySQL server is available free of charge for non- applications that can communicate with a MySQL commercial use under the open source Gnu Public server. This allows you to create custom client software License (GPL). You can download the software and for virtually any purpose. install it on a Windows, Linux, or other machine. MYSQL AND THE WEB MySQL's most popular use is as a back-end system for Web sites. This is largely due to the fact that most of the systems that run the popular Apache Web server can also run the MySQL server. MySQL is also included with many Linux distributions, and can be installed as an option during the installation of Linux. Simple Web Sites Database-Backed Web Sites A basic Web site uses simple, flat HTML (hypertext When you use a database to manage a Web site, all of markup language) files and has no need for a database. the data is divided into convenient pieces and stored on While this is sufficient for a simple home page, if your the MySQL server. You can then use a language such as site needs to display large amounts of data, the number Perl or PHP to dynamically build the HTML pages for of HTML files quickly becomes difficult to manage. the site based on the data from the server. Web Languages This has several advantages over a simple Web site: first, the data is stored in a central location and is easy to PHP is a popular language for use with MySQL. It uses a back up, restore, or move to a different server. Second, friendly syntax, has powerful features, and has support the data can be dynamically assembled as needed — for all of MySQL's functions built in. Creating MySQL rather than static pages, you can easily create search applications using PHP is simple, and is described in engines and other interactive features. Third, rather Chapter 12. than editing HTML files when you need to update the Perl is another popular Web language, and one of the site, you can use a simple MySQL client to enter or oldest languages used on the Web. While its syntax is a bit modify data from a friendly interface. more difficult to master, Perl is a powerful language. Like Most importantly, a database allows you to separate the PHP, it supports all of the MySQL client functions. You can content of the site from the format it is displayed in. When learn more about using Perl with MySQL in Chapter 13. you want to change the look of the site, you can make some simple changes to the program that displays the data, and the look of thousands of pages can be changed. 3
MySQL MYSQL TERMINOLOGY D atabases in general, and MySQL in particular, use a few terms you may be unfamiliar with. The following is an overview of some of the MySQL terms you will encounter throughout this book. MySQL Server Row (or Record) The MySQL server is a software service that runs on a Each entry in the database is a row, and each row includes computer, typically a UNIX or Windows machine. The a data item for each column. For example, in a mailing list MySQL server accepts requests from clients and sends table, each combination of name and address would form results back to them. one row of the table. Rows can be added, removed, or modified at any time. When clients request data, it is MySQL Client returned in rows. Rows are also known as records. A MySQL client is an application that sends requests to a MySQL server and works with the resulting data. A Primary Key simple command-line client comes with the MySQL In order to access a single record in a database table, the server software. Applications written in Web languages server needs a unique identifier for each row. The primary such as PHP and Perl can act as customized MySQL key is a column chosen to be this unique identifier. For clients. example, the name would be an appropriate primary key for an address database. Each table can have one primary Database key consisting of one or more columns. A database is a file structure for organizing data, and consists of one or more tables. The MySQL server can Index store any number of databases, and clients can work The database server stores extra information about the with them concurrently. values used in the primary key field in an index. This allows for faster searching by this field. You can also Table specify that other fields in a table be indexed if they A table is a unit of data storage within a database. Each will be searched on frequently. These indexes are database can contain one or more tables. Tables are known as secondary keys. defined with a set number and type of columns, and can contain any number of rows. Query Each request that a MySQL client sends to a server is Column (or Field) called a query. Queries use SQL, or Structured Query The columns, or fields, define the types of data stored Language. A query can request one or more rows of in a database table. For example, a table that stores a data, or request that an action be performed — for mailing list may include columns called Name, Address, example, adding a new row or deleting an existing row. City, and State. A table can have one or more columns. Each column is defined with the type of data it will SQL store — for example, a number, a string of text, or a SQL, or Structured Query Language, is the language used date. in MySQL queries. SQL is a standardized language that uses commands to perform various functions on the database server. Examples of SQL commands include INSERT to add a row to a table and SELECT to find one or more rows within a table. 4
INTRODUCING MYSQL 1 OTHER DATABASE SYSTEMS MySQL is neither the simplest nor the most sophisticated of W hile this book focuses on MySQL, it is actually just one of several database management systems, database systems. The following are some of the other also known as DBMS, that you may find useful. popular systems, ranging from simpler than MySQL to far Many of these systems use similar languages and can be more sophisticated. used interchangeably, and you can choose whichever server fits the needs of your application. Flat File Database PostgreSQL The most basic of databases does not use a DBMS at all. PostgreSQL is another popular DBMS and an alternative Applications can simply store data in one or more files. to MySQL. It is free and open source software. This is the traditional approach used by many simple PostgreSQL supports some features of the SQL applications such as spreadsheets. Languages such as language beyond what MySQL supports, and offers Perl have features that allow you to use DBMS-like features like transaction support that may make it a features to manage simple files. The advantage of this better choice for high-end applications. PostgreSQL can approach is that it can run on many platforms and does be run on most Linux and UNIX systems. not require a database server, but it offers few of the advantages of a true database management system. Microsoft SQL Server While Access is a simple desktop database, Microsoft Microsoft Access SQL Server is a full-featured client-server DBMS. The Microsoft Access is by far the most popular desktop server software runs exclusively on Windows NT, 2000, database software and is available as part of the and XP systems. The advantages of SQL Server include Microsoft Office suite. Access uses an internal DBMS its wide support in Windows-based applications and its that stores data in local files, and the ability to share files reliability, which has led to its use by many between networked computers is limited. Nevertheless, corporations, banks, and even stock exchanges. it is useful for simple applications and has a friendly user interface. Access can also work as a client for Microsoft Oracle SQL Server, and includes tools to simplify the migration Oracle is a high-end DBMS popular for use in corporate of Access databases to SQL Server. databases and in larger and more critical Web sites. It supports transactions and other features for high mSQL reliability, availability, and speed. Its server software Mini SQL from Hughes Technologies, known more runs under UNIX or Windows systems. Visit Oracle's popularly as mSQL, is another popular choice as the Web site at www.oracle.com for more information. back end of Web sites. Similar to MySQL in language and functionality, mSQL's advantages are also similar to DB2 those of MySQL: Its server software is lightweight, can IBM's DB2 is a DBMS with a long history in mainframe run on machines with limited resources, and is computing. The latest version, DB2 Universal Database, supported by many programming languages. mSQL is a runs under AIX, Windows, and most UNIX systems. It is commercial product, but free copies are available for another popular choice for banks and other corporations educational users and nonprofit organizations. with critical data storage needs, and includes features that make it work well with Web standards such as Java and XML. 5
MySQL STRUCTURED QUERY LANGUAGE (SQL) The following is an overview of some of the most important S QL (Structured Query Language) was first developed by IBM and is now a standard maintained by the commands in MySQL. You can use these commands from ANSI, or American National Standards Institute. Most any MySQL client, such as the MySQL Monitor introduced current DBMS systems follow the SQL standard to some later in this chapter. Each of these commands is explained in degree. MySQL's query language is based on this standard. detail with examples in later chapters. CREATE DATABASE SHOW DATABASES The CREATE DATABASE command creates the file The SHOW DATABASES command displays a list of structure for a new database and assigns it the the databases that have been created on the name you specify. This command does not by itself MySQL server. The SHOW command can also be store any data, but you must create a database used to display tables for a database and other before you can use it. categories of information. Example: Example: CREATE DATABASE testdb; SHOW DATABASES; DROP DATABASE CREATE TABLE The DROP DATABASE command deletes an existing The CREATE TABLE command creates a table database. Do not expect a friendly warning when within the currently selected database. To create a using this command: The database is deleted table, you specify the fields (columns) it will contain immediately, without confirmation, including all and a field type for each one. The CREATE TABLE tables and rows. Use this command with extreme command can optionally include information about caution. the keys and indexes available in the table. This command often requires multiple lines. Example: DROP DATABASE testdb; Example: CREATE TABLE address ( name CHAR(100), USE address CHAR(120), phone CHAR(10) ); The USE command selects a database for use by other commands. The database you specify with USE must already exist. The commands that work DROP TABLE with tables and rows of data will only work if you have selected a database using this command. The DROP TABLE command deletes a table from the currently selected database. All of the data Example: stored in the table is permanently deleted and, as USE testdb; with the DROP DATABASE command, this happens instantly and without confirmation. Use this command with caution. Example: DROP TABLE address; 6