PHP and MySQL by Example- P12

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

Thêm vào BST

Báo xấu

95
lượt xem 10
download

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

Tham khảo tài liệu 'php and mysql by example- p12', công nghệ thông tin, kỹ thuật lập trình phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

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

Lưu

Nội dung Text: PHP and MySQL by Example- P12

Figure 12.22. Using a regular expression to find a zip code. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
12.3. Chapter Summary Because PHP is tightly integrated with HTML and receives input from forms, regular expressions provide an excellent tool for validating incoming data. They are also useful for finding patterns in data coming from files or databases. This chapter was designed to teach you how to use regular expressions and the PHP functions that handle them, and to provide short examples to show you how to use the often mysterious regular expression metacharacters. 12.3.1. What You Should Know Now that you have finished this chapter you should be able to answer the following questions: 1. What is PCRE? 2. What is meant by POSIX style? 3. What is the difference between preg_grep() and preg_match()? 4. What are regular expression metacharacters used for? 5. What is meant by anchoring? 6. What is capturing? 7. What is greed (when talking about regular expressions)? 8. What are metasymbols? Why are they useful? 9. What is the function of the e modifier? Which function uses it? 10. What is a character class? 11. What is a delimiter? 12. What is a positive lookahead? 12.3.2. What’s Next? In the next chapter, we start our discussion of the MySQL relational database system and describe the client/server model, anatomy of a database, schema, and the MySQL privilege system, along with the strengths and weaknesses of MySQL. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 12 Lab Open the datebook file (found on the CD) to perform the following exercises. Each exercise requires a separate open and close of the file. 1. • Print all lines containing the pattern Street (case insensitive). • Print firsts and last names in which the first name starts with letter B. • Print last names that match Ker. • Print phones in the 408 area code. • Print Lori Gortz’s name and address. • Print Ephram’s name in capital letters. • Print lines that do not contain a number 4. • Change William’s name to Siegfried. • Print Tommy Savage’s birthday. • Print lines that end in exactly five digits. • Print the file with the first and last names reversed. 2. • Print the city and state where Norma lives. • Give everyone a $250.00 raise. • Calculate Lori’s age (just by year, not month and day). • Print lines 2 through 6. • Print names and phone numbers of those in the 408 area code. • Print names and salaries in lines 3, 4, and 5. • Print a row of asterisks after line 3. • Change CA to California. • Print the file with a row of asterisks after the last line. • Print the names of the people born in March. • Print all lines that don’t contain Karen. • Print all cities in California and the first names of those people who live there. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 13. Introduction to MySQL 13.1. About Databases Whether you are running a bank, a hospital, a gas station, or a Web store, good record keeping and organized data are crucial to the success of any business. One way to store data might be in a text file, but as the amount of data increases, a database might be a better choice for storing and managing your data. Different types of databases determine what kind of structure will be used to store and retrieve the data. The most basic type uses a flat file structure, storing the data in a big table, but this type is difficult to modify and really best suited for simple applications. Another type of database is one in which the data is organized in a hierarchy or network, much like the structure of a directory tree, a parent– child model, but these kinds of databases are hard for end users to grasp. Then in the 1980s relational databases became the “in” thing because the relational model made data manipulation easier and faster for the end user and easier to maintain by the administrator. At the core of this model is the concept of a table (also called a relation) in which all data is stored. Each table is made up of records consisting of horizontal rows and vertical columns or fields, like a two- dimensional array. Unlike the hierarchical model, the relational model made it easy for the user to retrieve, insert, update, and delete data without having to understand the underlying structure of the data in the database. Due to the popularity of relational databases, known as relational database management systems (RDBMS), a number of relational databases are used today, among them, Oracle, Sybase, PostgreSQL Informix, DB2, SQL Server, and MySQL. MySQL is the most commonly used database program for developing database-driven Web sites with PHP. As we mentioned in Chapter 1, “Introduction,” MySQL is an open source database (it is free[1]) that runs on a majority of operating systems, such as UNIX, Linux, Macintosh, and Windows. PHP and MySQL fit very well together. They are both reasonably easy to use, fairly scalable and reliable and have a good set of features for small- and medium-sized Web applications. Although PHP can be used with any database through its set of ODBC functions, it comes loaded with MySQL specific functions. This set of specific functions makes for a tight integration between the PHP language and the MySQL database. [1] Although maintained by MySQL AB, a commercial company, MySQL comes with a GPL (GNU Public License) open source license as well as a commercial license. 13.1.1. Client/Server Databases If your Web site is to be up and available to customers around the world, and you are using a database management system to manage the data, the type of relational database best suited for the task is a client/server database, where the database server runs around the clock to handle client requests as they come in, no matter what the time zone. Today MySQL is one of the most popular client/server database systems in the open source community for serving Web pages. Figure 13.1 shows the model for a client/server architecture. The user requests a page from the browser (e.g., Internet Explorer, Netscape, Firefox), and an HTTP connection is made to the Web server (Apache, ISS) where the request is received and handled. If the action is to start up a PHP program, the Web server starts up the PHP interpreter and PHP starts processing the script. If the PHP script contains an instruction to connect to a database, in this case MySQL, then once the connection is made and a database selected, the PHP program has access to the database through the MySQL server. The MySQL server receives requests, called queries, from the PHP program and sends back information collected from the database. Once PHP gets the information from the MySQL server, it can then format it into nice tables using HTML tags, and send it back to the Web server where it is then relayed to the browser where the whole process started. In this example, we have a client/server relationship between the browser and Web server and a client/server relationship between the PHP program and the MySQL database server. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 Display this help and exit. -I --help Synonym for -?. -B --batch Do not use history file. Disable interactive behavior. (Enables --silent.) -C --compress Use compression in server/client protocol. -# --debug[=#] This is a nondebug version. Catch this and exit. 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 Database to be used. --delimiter=name Delimiter to be used. -e --execute=name Execute command and quit. (Disables --force and history file.) -E --vertical Print the output of a query (rows) vertically. -f --force Continue even if we get an sql error. -i --ignore-spaces Ignore space after function names. --local-infile Enable or disable LOAD DATA LOCAL INFILE. -b --no-beep Turn off beep on error. -h --host=name Connect to host. -H --html Produce HTML output. -X --xml Produce XML output --line-numbers Write line numbers for errors. -L --skip-line- Do not write line number for errors. WARNING: -L is numbers deprecated, so use long version of this option instead. --no-tee Disable outfile. See interactive help (\h) also. WARNING: Option deprecated; use --disable-tee instead. -n --unbuffered Flush buffer after each query. --column-names Write column names in results. -N --skip-column- Do not write column names in results. WARNING: -N is names deprecated, use long version of this option instead. -o --one-database Only update the default database. This is useful for skipping updates to other databases in the update log. -p -- Password to use when connecting to server. If password is password[=name] not given, it is asked from the tty. -W --pipe Use named pipes to connect to server. 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=# Port number to use for connection. --prompt=name Set the mysql prompt to this value. -q --quick Do not cache result, print it row by row. This might slow down the server if the output is suspended. Does not use history file. -r --raw Write fields without conversion. Used with --batch. --reconnect Reconnect if the connection is lost. Disable with -- disable-reconnect. This option is enabled by default. -s --silent Be more silent. Print results with a tab as separator, each row on a new line. -t --table Output in table format. -T --debug-info Print some debug info at exit. --tee=name Append everything into outfile. See interactive help (\h) also. Does not work in batch mode. -u --user=name User for login if not current user. -U --safe-updates Only allow UPDATE and DELETE that uses keys. -U --i-am-a-dummy Synonym for option --safe-updates. -v --verbose Write more (-v -v -v gives the table output format). -V --version Output version information and exit. 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.
Figure 13.14. After checking the “categories” box in the left frame, the structure of that table is displayed in the phpMyAdmin main window. The MySQL Query Browser The MySQL Query Browser is a graphical user interface (GUI) client available from mysql.com used to connect to the MySQL database server. Once you download it and follow the simple installation wizard, you can start the application from the Start menu under Windows. The MySQL Query Browser then displays a connection dialog box. You must specify the MySQL server where you want to connect, the credentials needed for authorization on that server, which machine that server runs on (and which port it listens to), and the default database (called the “Schema”) you will be using. There are also a number of additional options you can specify if necessary. You must choose a default database to issue queries. Although it is possible to choose a default database after connecting to the server, setting the default from the connection dialog box can save time on subsequent connections. The information to enter is very similar to the command-line client: username, password, and the server host where the database server is running. You can optionally enter the database name and port number (3306 is the default for MySQL) and save the connection information as a bookmark under the Stored Connection section (see Figure 13.15). Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 13.15. The MySQL Query Browser connection dialog box. By using the familiar tree-like navigation structure on the right side of the application window, you can also navigate through the various databases in the MySQL Query Browser (see Figure 13.16). Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 13.16. Navigating with the MySQL Query Browser. 13.4. The MySQL Privilege System With a drivers’ license, “authentication” means verifying that it is really you who owns the license by checking your picture and expiration date, and “authorization” means validating what type of vehicle you are authorized to drive, such as a car, a large truck, or a school bus. Similarly, the primary purpose of the MySQL privilege system is to authenticate that the user and password are valid to connect to the specified host, as demonstrated in the previous examples in both the command-line and graphical client. The second purpose of the privilege system is to specify what the user, once connected to the database, is authorized to do. For example, some users might be authorized to only select and view the data from a specific database, but not make any changes to it. Some might be able to delete records, but not tables. Once you have installed MySQL, it is time to understand some basic guidelines of how to administer a MySQL database server, such as setting up the users and the privileges they have on certain databases. You can use either the mysql command-line tool or the mysqladmin tool for performing administrative tasks. Although there are some Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
graphical administrative tools avaialable, we use the command-line tools because they are always available and work the same way regardless of your operating system whether it is Windows, Macintosh, or Linux. The next section assumes you have basic SQL skills, such as familiarity with INSERT/ UPDATE/ DELETE/ SELECT statements. If not, the SQL language is summarized in Chapter 14, “SQL Language Tutorial.” 13.4.1. Logging into the Database Server When MySQL is installed, the mysql database is created with tables, called grant tables that define the initial user accounts and privileges. The first account is that of a user named “root,” also called the superuser. The superuser can do anything, meaning anyone logging onto the database as root is granted all privileges. Initially the root account has no password, making it easy for anyone to log on as the superuser. The other type of accounts created are anonymous user accounts, also without a password. For both the root and anonymous accounts, Windows gets one each and UNIX gets two. Either way, to avoid security problems, the first thing you should do, once the MySQL server starts, is to set a password on the root account and the anonymous accounts. MySQL keeps track of its own users and passwords separate from the operating system where it is running. All the privileges for the MySQL database server are stored in the “mysql” database (the database with name “mysql”). For administration purposes, you should have root access rights to your server. The mysqladmin utility is useful for creating passwords as well as performing other MySQL administrative tasks. In the next example it is used to set the password for the root user. When working with MySQL, a number of like-name terms are used. Table 13.2 is provided to help clarify the use of these terms. Table 13.2. MySQL Terminology Term Description mySQL The actual software for the database management system mysqld The mySQL daemon or server process mysql The monitor where MySQL commands are issued (command-‐line monitor interpreter) mysql The name of the database MySQL uses to manage access privileges mysqladmin A MySQL utility program for administering the database Example 13.2. 1 $ mysqladmin -u root -h localhost password quigley1 2 $ mysql -uroot -hlocalhost -pquigley1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 to server version: 5.0.21-community-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. Explanation 1 The mysqladmin program is used to set the password for root user on the localhost. The password is quigley1. 2 This logs the root user into the database server. The -u switch is followed by the user or login name (no spaces between -u and the username). This user is logging in as root. Similarly, the -p switch is followed by the actual password, in this case quigley1. If a password is not provided, you will be prompted to enter one. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
the user or login name (no spaces between -u and the username). This user is logging in as root. Similarly, the -p switch is followed by the actual password, in this case quigley1. If a password is not provided, you will be prompted to enter one. 13.4.2. Finding the Databases The database server keeps a list of available databases that can be displayed as a table by issuing the show command at the mysql prompt, as shown in Example 13.3. Typically when you install MySQL it comes with two databases: “test” and “mysql”. The “test” database is used for testing various features or creating sample databases. You normally do not need to have any special permissions to be able to do anything in that database. The “mysql” database is a special database where the MySQL server stores various access permissions. We look at the contents of the “mysql” database in the next section. Example 13.3. C:\>mysql -uroot -ppassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.1.11-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +--------------+ | Database | +--------------+ | authority | | best | | jsf | | marakana_cms | | mysql | | northwind | | test | +--------------+ 7 rows in set (0.69 sec) mysql> Explanation The show databases command gives us the list of all the databases on this server. Typically, when you install MySQL, you will be given the “mysql” database and the “test” database. The “test” database is just for testing purposes and is empty. The “mysql” database contains all the MySQL server privilege information. Example 13.4. Code View: 1 mysql> use mysql Database changed 2 mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
| columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 15 rows in set (0.19 sec) Explanation 1 The use mysql command tells the server to switch to the “mysql” database and make that the current database. 2 The show tables command displays all the database tables in the current “mysql” database. This database contains 15 tables. The tables we are concerned with now are “host,” “user,” and “db.” 13.4.3. The “user” Table The “user” table specifies the users who are allowed to log into the database server and from what host. It also holds their passwords and global access privileges. Let’s look at the fields of the “user” table: Code View: mysql> describe user; +-----------------------+-----------------------------------+ | Field | Type | +-----------------------+-----------------------------------+ | Host | varchar(60) | | User | varchar(16) | | Password | varchar(41) | | Select_priv | enum('N','Y') | | Insert_priv | enum('N','Y') | | Update_priv | enum('N','Y') | | Delete_priv | enum('N','Y') | | Create_priv | enum('N','Y') | | Drop_priv | enum('N','Y') | | Reload_priv | enum('N','Y') | | Shutdown_priv | enum('N','Y') | | Process_priv | enum('N','Y') | | File_priv | enum('N','Y') | | Grant_priv | enum('N','Y') | Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.