Figure 13.1. The client/server architecture.
!
!
!
13.1.2. Talking to the Database
To communicate with the MySQL server, you will need a language, and SQL (Structured Query Language) is the
language of choice for most modern multiuser, relational databases. SQL provides the syntax and language constructs
needed to talk to relational databases in a standardized, cross-platform structured way. We discuss how to use the SQL
language in the next chapter.
Like the English language with a variety of dialects (British, American, Australian, etc.), there are many different
versions of the SQL language. The version of SQL used by MySQL follows the ANSI (American National Standards
Institute) standard, meaning that it must support the major keywords (e.g., SELECT, UPDATE, DELETE,
INSERT,WHERE, etc.) as defined in the standard. As you can see by the names of these keywords, SQL is the language
that makes it possible to manipulate the data in a database.
13.1.3. MySQL Strengths and Weaknesses
From www.mysq.com/why-mysql:
The MySQL
®
database has become the world’s most popular open source database because of its consistent fast
performance, high reliability, and ease of use. It’s used in more than 8 million installations ranging from large
corporations to specialized embedded applications on every continent in the world. (Yes, even Antarctica!)
Not only is MySQL the world’s most popular open source database, it’s also become the database of choice for a new
generation of applications built on the LAMP stack (Linux, Apache, MySQL, PHP/Perl/Python). MySQL runs on more
than 20 platforms including Linux, Windows, OS/X, HP-UX, AIX, Netware, giving you the kind of flexibility that puts
you in control.
Having said that, like any tool, MySQL is right for certain types of applications and not as suitable for others. Let’s look
at what the strengths and weaknesses of MySQL are.
Easy to Use
MySQL is a relatively easy to use and administer database system. Large database systems with all the bells and
whistles often require a knowledgable database administrator (DBA) to set up and administer it. MySQL is a database
built for programmers with very little overhead in terms of maintenance.
Large Community of Developers
What makes MySQL so appealing is the large community of other developers who are building applications around it.
This makes it a relatively safe choice. If you ever need anything, chances are that someone already experienced that
issue and has it resolved. You can often find the solutions with a little searching online.
Open Source License
MySQL is free to use as long as you do not bundle it with your commercial product. As an application provider, you
can always tell your customers to download and set up their own MySQL database to which your application will
connect. This is a fairly easy procedure and there is no license cost involved, making it an attractive choice for
application developers.
Commercial License
When in fact you want to ship your application with a copy of the MySQL database server built into it, then you must
purchase the license from MySQL AB. This might not be an attractive feature for true believers in open source and
General Public License models, but for most of us, obtaining a license will not be an issue. For Web applications, the
database is rarely shipped as part of the application. Because customers who install server-side applications usually
have sufficient skills to perform the tasks of downloading and setting up databases, it is sufficient to document the setup
process with your application and leave the rest to them.
Scalability
Scalability refers to how well an application can support larger or smaller volumes of data and more or fewer users
without degrading performance and costing more. MySQL used to be regarded as a small database for small systems.
Over time, MySQL has become a serious RDBMS with its own way of managing scalability, claiming that it can handle
from small (a megabyte) to large (several terabytes) volumes of data with ultimate scalability. For example, there are
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
currently some very large sites in production with multiclusters of MySQL database servers. Scalability is beyond the
scope of this book but it is good to know that MySQL can handle your application as it grows in size.
13.2. The Anatomy of a Relational Database
What makes up a database? The main components of an RDBMS are:
a. The database server
b. The database
c. Tables
d. Records and fields
e. Primary key
f. Schema
We discuss each of these concepts in the next sections of this chapter. Figure 13.2 illustrates their relationship to each
other.
Figure 13.2. The database server, the database, and a table.
!
!
!
13.2.1. The Database Server
The database server is the actual server process running the databases. It controls the storage of the data, grants access
to users, updates and deletes records, and communicates with other servers. The database server is normally on a
dedicated host computer, serving and managing multiple clients over a network, but can also be used as a standalone
server on the local host machine to serve a single client (e.g., you might be the single client using MySQL on your local
machine, often referred to as “localhost” without any network connection at all). This is probably the best way to learn
how to use MySQL.
If you are using MySQL, the server process is the MySQL service on Windows or the mysqld process on Linux/UNIX
operating systems. The database server typically follows the client/server model where the front end is the client, a user
sitting at his or her workstation making database requests and waiting for results, and the back end is the database
server that grants access to users, stores and manipulates the data, performs backups, even talks to other servers. The
requests to the database server can also be made from a program that acts on behalf of a user making requests from a
Web page. In the following chapters, you will learn how to make requests from the MySQL command line first, and
then to connect to the database server from a PHP program using PHP built-in functions to make requests to the
MySQL database server.
13.2.2. The Database
A database is a collection of related data elements, usually corresponding to a specific application. A company might
have one database for all its human resource needs, perhaps another one for its sales staff, a third one for e-commerce
applications, and so on. Figure 13.3 lists the databases installed on a particular version of MySQL. The databases are
listed as “mysql,” “northwind,” “phpmyadmin,” and “test.”
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 13.3. MySQL databases.
!
13.2.3. Tables
Each database consists of two-dimensional tables. In fact, a relational database stores all of its data in tables, and
nothing more. All operations are performed on the table, which can then produce other tables, and so on.
One of the first decisions you will make when designing a database is what tables it will contain. A typical database for
an organization might consist of tables for customers, orders, and products. All these tables are related to one another in
some way. For example, customers have orders, and orders have items. Although each table exists on its own,
collectively the tables comprise a database. Figure 13.4 lists the tables in the database called “northwind,”
[2]
a fictional
database provided by Microsoft to serve as a model for learning how to manipulate a database. (This database is
included on the CD provided with this book.)
[2]
The Northwind Traders sample database typically comes as a free sample with Microsoft Access, but is available for
MySQL at http://www.flash-remoting.com/examples/.
Figure 13.4. Tables in the northwind database.
!
13.2.4. Records and Fields
A table has a name and consists of a set of rows and columns. It resembles a spreadsheet where each row, also called a
record, is comprised of vertical columns, also called fields. All rows from the same table have the same set of columns.
The “shippers” table from the “northwind” database has three columns and three rows, as shown in Figure 13.5.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 13.5. The rows (records) and columns (fields) from the “shippers” table in the “northwind” database.
!
There are two basic operations you can perform on a relational table. You can retrieve a subset of its columns and you
can retrieve a subset of its rows. Figures 13.6 and 13.7 are samples of the two operations.
Figure 13.6. Retrieving a subset of columns.
!
Figure 13.7. Retrieving a subset of rows.
!
!
!
Remember, a relational database manipulates only tables and the result of all operations are also tables. The tables are
sets, which are themselves sets of rows and columns. You can view the database itself as a set of tables.
You can also perform a number of other operations between two tables, treating them as sets: You can join information
from two tables, make cartesian products of the tables, get the intersection between two tables, add one table to another,
and so on. Later we show you how to perform operations on tables using the SQL language. SQL allows you to “talk”
to a database. Figures 13.6 and 13.7 use SQL commands to retrieve data.
Columns/Fields
When discussing tables, we must talk about columns because they are an integral part of the table. Columns are also
known as fields or attributes. Fields describe the data. Each field has a name. For example, the “shippers” table has
fields named “ShipperID,” “CompanyName,” and “Phone” (see Figure 13.7). The field also describes the type of data it
contains. A data type can be a number, a character, a date, a time stamp, and so on. In Figure 13.8 “ShipperID” is the
name of a field and the data type is an integer, and the shipper’s ID will not exceed 11 numbers. There are many data
types and sometimes they are specific to a particular database system; for example, MySQL might have different data
types available than Oracle. We will learn more about the MySQL data types in the next chapter.
Figure 13.8. Each field has a name and a description of the data that can be stored there.
!
!
!
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Rows/Records
A record is a row in the table. It could be a product in the product table, an employee record in the employee table, and
so on. Each table in a database contains zero or more records. Figure 13.9 shows us that there are three records in the
“shippers” table.
Figure 13.9. There are three records in the “shippers” table.
!
!
!
13.2.5. Primary Key and Indexes
A primary key is a unique identifier for each record. For example, every employee in the United States has a Social
Security number, every driver has a driver’s license, and every car has a license plate. These identifiers are unique. In
the world of database tables, we call the unique identifier a primary key. Although it is a good idea to have a primary
key, not every table has one. The primary key is determined when the table is created and is more in keeping with a
discussion on database design. In Figure 13.10, the “ShipperID” is the primary key for the “shippers” table in the
“northwest” database. It is a unique ID that consists of a number that will automatically be incremented every time a
new company (record) is added to the list of shippers.
Figure 13.10. The “ShipperID” is the primary key in the “shippers” table.
!
!
!
In addition to a primary key, one or more indexes are often used to enhance performance for finding rows in tables that
are frequently accessed. Indexes are like the indexes in the back of a book that help you find a specific topic more
quickly than searching through the entire book. When searching for a particular record in a table, MySQL must load all
the records before it can execute the query. An index, like the index of a book, is a reference to a particular record in a
table.
13.2.6. The Database Schema
Designing a very small database is not difficult, but designing one for a large Web-based application can be daunting.
Database design is both an art and a science and requires understanding how the relational model is implemented, a
topic beyond the scope of this book. When discussing the design of the database, you will encounter the term database
schema, which refers to the structure of the database. It describes the design of the database similar to a template or
blueprint; it describes all the tables, and their layout, but does not contain the actual data in the database. Figure 13.11
describes the schema for the tables in the “northwind” database.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 13.11. Database schema.
13.3. Connecting to the Database
Here we assume you have installed a database server and it is running. Downloading and installing MySQL is usually a
straightforward process. For details, see Appendix E.
The MySQL database system uses the client/server model described in “Client/Server Databases” on page 568. There
are a number of client applications available to connect to the database server, the most popular and most widely
available being the mysql command-line client shown in Example 13.1.
Example 13.1.
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.8-nt-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Regardless of the type of client you choose, you will always need to specify the username, and the host you are
connecting to. Most configurations expect you to have a password, although if just working by yourself, it is not
required. You have the option to specify the default database as well.
13.3.1. MySQL Command-Line Options
The mysql command-line client ships with the MySQL installation and is universally available. It is a mysql.exe
program located in the bin folder of your MySQL installation.
To run this command-line application, you must start the command-line prompt. In Windows, you go to the Start menu
and choose the Run option, then type cmd in the Run window. In Mac OS X, go to the Applications folder in your
Finder and then navigate to Utilities. You will find the Terminal application there. You should navigate to the location
where you installed MySQL and find the bin folder. With UNIX, type commands at the shell prompt in a terminal
window.
The mysql client executable is normally located in the bin folder.
To connect to a database using this client, you will enter information similar to the following line (see Figure 13.12):
mysql user=root password=my_password host=localhost
!
Figure 13.12. The mysql client.
!
!
!
Once you are successfully connected, you will get the mysql> prompt instead of your standard DOS/UNIX prompt.
This means you are now sending commands to the MySQL database server and not to your local computer’s operating
system.
There are many command-line options for the MySQL client. The most common are shown in Table 13.1.
Table 13.1. MySQL Command-Line Options
Short&
Format
Long&Format
Description
-?
help
"#$%&'(!)*#$!*+&%!',-!+.#)/
-I
help
0(,1,(2!314!-?/
-B
batch
"1!,1)!5$+!*#$)14(!3#&+/!"#$'6&+!#,)+4'7)#8+!6 +*' 8#14/!
9:,'6&+$! silent.)
-C
compress
;$+!712%4+$$#1,!#,!$+48+4<7&#+,)!%41)171&/
-#
debug[=#]
=*#$!#$!'!,1,-+65>!8+4$#1,/!?')7*!)*#$!',-!+.#)/
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Table 13.1. MySQL Command-Line Options
Short&
Format
Long&Format
Description
-D
database=name
"')'6'$+!)1!6+!5$+-/
!
delimiter=name
"+#)+4!)1!6+!5$+-/
-e
execute=name
:.+75)+!7122',-!',-!@5#)/!9"#$'6&+$! force!',-!*#$)14(!
3#&+/A
-E
vertical
B4#,)!)*+!15)%5)!13!'!@5+4(!941C$A!8+4)#7'&&(/
-f
force
?1,)#,5+!+8+,!#3!C+!>+)!',!$@&!+4414/
-i
ignore-spaces
D>,14+!$%'7+!'3)+4!35,7)#1,!,'2+$/
!
local-infile
:,'6&+!14!-#$'6&+!EFG"!"G=G!EF?GE!DHIDE:/
-b
no-beep
=54,!133!6++%!1,!+4414/
-h
host=name
?1,,+7)!)1!*1$)/
-H
html
B41-57+!J=KE!15)%5)/
-X
xml
B41-57+!LKE!15)%5)
!
line-numbers
M4#)+!&#,+!,526+4$!314!+4414$/
-L
skip-line-
numbers
"1!,1)!C4#)+!&#,+!,526+4!314!+4414$/!MGNHDHOP!-L!#$!
-+%4+7')+-Q!$1!5$+!&1,>!8+4$#1,!13!)*#$!1%)#1,!#,$)+'-/
!
no-tee
"#$'6&+!15)3#&+/!0++!#,)+4'7)#8+!*+&%!9\h)!'&$1/!MGNHDHOP!
F%)#1,!-+%4+7')+-R!5$+! disable-tee!#,$)+'-/
-n
unbuffered
I&5$*!6533+4!'3)+4!+'7*!@5+4(/
!
column-names
M4#)+!71&52,!,'2+$!#,!4+$5&)$/
-N
skip-column-
names
"1!,1)!C4#)+!71&52,!,'2+$!#,!4+$5&)$/!MGNHDHOP!-N!#$!
-+%4+7')+-Q!5$+!&1,>!8+4$#1,!13!)*#$!1%)#1,!#,$)+'-/
-o
one-database
F,&(!5%-')+!)*+!-+3'5&)!-')'6'$+/!=*#$!#$!5$+35&!314!
$S#%%#,>!5%-')+$!)1!1)*+4!-')'6'$+$!#,!) *+! 5%-')+!&1>/
-p
password[=name]
B'$$C14-!)1!5$+!C*+,!71,,+7)#,>!)1!$+48+4/!D3!%'$$C14-!#$!
,1)!>#8+,Q!#)!#$!'$S+-!3412!)*+!))(/
-W
pipe
;$+!,'2+-!%#%+$!)1!71,,+7)!)1!$+48+4/
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Table 13.1. MySQL Command-Line Options
Short&
Format
Long&Format
Description
-P
port=#
B14)!,526+4!)1!5$+!314!71,,+7)#1,/
!
prompt=name
0+)!)*+!mysql!%412%)!)1!)*#$!8'&5+/
-q
quick
"1!,1)!7'7*+!4+$5&)Q!%4#,)!#)!41C!6(!41C/!=*#$!2#>*)!$&1C!
-1C,!)*+!$+48+4!#3!)*+!15)%5)!#$!$5$%+,-+-/!"1+$!,1)!5$+!
*#$)14(!3#&+/
-r
raw
M4#)+!3#+&-$!C#)*15)!71,8+4$#1,/!;$+-!C#)*! batch.
!
reconnect
N+71,,+7)!#3!)*+!71,,+7)#1,!#$!&1$)/!"#$'6&+!C#)*!
disable-reconnect/!=*#$!1%)#1,!#$!+,'6&+-!6(!-+3'5&)/
-s
silent
T+!214+!$#&+,)/!B4#,)!4+$5&)$!C#)*!'!)'6!'$!$+%'4')14Q!+'7*!
41C!1,!'!,+C!&#,+/
-t
table
F5)%5)!#,!)'6&+!3142')/
-T
debug-info
B4#,)!$12+!-+65>!#,31!')!+.#)/
!
tee=name
G%%+,-!+8+4()*#,>!#,)1!15)3#&+/!0++!#,)+4'7)#8+!*+&%!9\h)!
'&$1/!"1+$!,1)!C14S!#,!6')7*!21-+/
-u
user=name
;$+4!314!&1>#,!#3!,1)!7544+,)!5$+4/
-U
safe-updates
F,&(!'&&1C!;B"G=:!',-!":E:=:!)*')!5$+$!S+($/
-U
i-am-a-dummy
0(,1,(2!314!1%)#1,! safe-updates.
-v
verbose
M4#)+!214+!9-v -v -v!>#8+$!)*+!)'6&+!15)%5)!3142')A/
-V
version
F5)%5)!8+4$#1,!#,3142')#1,!',-!+.#)/
!
13.3.2. Graphical User Tools
The phpMyAdmin Tool
The phpMyAdmin tool (see Figures 13.13 and 13.14) is written in PHP to handle the administration of MySQL over the
Web. It is used to create and drop databases, manipulate tables and fields, execute SQL statements, manage keys on
fields, manage privileges, and export data into various formats. See http://www.phpmyadmin.net/home_page/index.php.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 13.13. The phpMyAdmin tool.
!
!
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét