U

MySQL Installation Instructions U.1 Installation To download MySQL, visit www.mysql.com and click the Downloads tab. Under MySQL database, select the version you wish to download. TcX recommends downloading the most recent version because it contains bug fixes for previous versions. Figure U.1 shows a portion of the screen that appears. You have the option to download either the binary or the source code files for different operating systems. The simplest and most efficient way to attain MySQL is to download the binary files (i.e., contain the compiled results of the source code). Although it is relatively simple to download binary files, they have limited customization capabilities. If you know in advance that some customization will be required to perform necessary tasks, then download the source code. But beware, the installation process is more of a hassle and you will need a C compiler, certain libraries and other development tools. [Note: We assume you will be downloading MySQL for educational purposes and provide instructions for installing only the binary files.] Scroll down to the Standard binary (tarball) distributions section. Select the version for your operating system. Based on your operating system, click on a program file to download. We select Windows 95/98/NT/2000 (Intel).

© Copyright 1992–2002 by Deitel & Associates, Inc. All Rights Reserved. 8/24/01

2

MySQL Installation Instructions

Fig. U.1

Appendix U

Selecting the appropriate binary file. (Courtesy of MySQL.)

The File Download dialog (Fig. U.2) allows you to either save the file to your computer or open it from its current location. We select the Save this file to disk radio button. Click OK.

Fig. U.2

File Download dialog.

Save the file to a desired location. We save to the Desktop (Fig. U.3). Click Save.

© Copyright 1992–2002 by Deitel & Associates, Inc. All Rights Reserved. 8/24/01

Appendix U

Fig. U.3

MySQL Installation Instructions

3

Saving the MySQL installation file.

Once the binary file has been downloaded, navigate to the file. To open the file, you need an archiving program, such as WinZip. Extract (unzip) the files. [Note: To obtain WinZip, visit www.winzip.com]. Upon extraction, double-click the Setup.exe file to install the MySQL program and follow the step-by-step instructions.

U.2 Running MySQL Once the setup is complete, you will be redirected to the desktop. One way to start the MySQL server is to navigate to the folder that contains the recently installed files. By default, MySQL is installed in the C:\mysql directory. To start and shutdown the MySQL server, open the MS-DOS Command Prompt window. For Windows 95/98, start the server by typing the following line at the C:\ prompt: mysql\bin\mysqld

The server will run in the background; no screen or GUI appears to notify you that MySQL is running in the background. To shutdown the server, type: mysql\bin\mysqladmin --u root shutdown

For Windows 2000/NT, install MySQL prior to starting the server. To install the database, type (at the C:\ prompt): mysql\bin\mysqld-nt --install

Now type the following either to start or stop the server: NET START mysql NET STOP mysql © Copyright 1992–2002 by Deitel & Associates, Inc. All Rights Reserved. 8/24/01

4

MySQL Installation Instructions

Appendix U

[Note: To utilize the server, you will need a TCP/IP connection and a Winsock service pack. Windows 95/98 require Winsock 2, whereas, Windows 2000/NT require Winsock 3. By default, Windows 95 comes with the old Winsock, but you can obtain the latest package through Microsoft. Windows 98 comes with Winsock 2. If additional assistance is need to install MySQL, then please refer to the MySQL Reference Manual] Once MySQL is properly installed, use the command prompt or the graphical user interface (GUI) to create and manipulate databases. Although recent versions of MySQL (e.g., version 3.23.36 or higher) come with a GUI, called MySQLManager (Fig. U.4), that interacts with the underlying database(s), the user must know how to use the command prompt to fully access MySQL’s features.

Fig. U.4

MySQLManager graphical user interface (GUI). (Courtesy of MySQL.)

21.2.1 Creating a Database To access the database server, open the MS-DOS Command Prompt (located in the Programs menu of the Start menu). Creation and manipulation of MySQL databases occurs at the mysql prompt. While at the C:\ prompt, change directories by typing (Fig. U.5): cd mysql\bin

At the C:\mysql\bin prompt, enter: mysql

© Copyright 1992–2002 by Deitel & Associates, Inc. All Rights Reserved. 8/24/01

Appendix U

Fig. U.5

MySQL Installation Instructions

5

Accessing the mysql prompt.

If done properly, a "welcome message" as well as the mysql> monitor prompt appears on the screen. Now, the user can enter commands and queries. The first command that we discuss is the CREATE DATABASE command. The syntax is: CREATE DATABASE FCVCDCUGAPCOG;

where FCVCDCUGAPCOG can be any user-defined name. It is common practice to assign it a name that succinctly describes the inherent characteristic of the database. For instance, a database containing the four tables—Authors, Publishers, AuthorISBN and Titles— should most likely be entitled Books as it contains the authors, publishers and titles of various books. It is important to note that MySQL is case-insensitive, so the following commands yield the same result: CREATE DATABASE FCVCDCUGAPCOG; create database FCVCDCUGAPCOG; Create Database FCVCDCUGAPCOG;

Depending on the operating system being used, FCVCDCUGAPCOG may or may not be case-sensitive (i.e., the MS-DOS command prompt is case-insensitive while UNIX is casesensitive). Another important point to note is that all queries in MySQL need to terminate with a semicolon (;) or with a \g. These terminators notify the database that the entire query has been entered and to perform the necessary action. But do not worry if you accidentally forget to include one of them because MySQL reminds you! If the command/query is properly typed with a semicolon (;) or a \g after it, then the mysql> prompt (as well as for the query results) appears after pressing Enter. However, if Enter is pressed without typing a semicolon (;) or a \g, then the following prompt appears: ->. If the -> prompt appears after the query has been completely typed, then enter a semicolon (;) or a \g. The result set should now appear. The advantage of the -> prompt, other than to act as a reminder, is that it permits queries to span multiple lines. The following is an acceptable MySQL query: mysql> -> -> -> ->

CREATE TABLE Authors (AuthorID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, FirstName VARCHAR(15), LastName VARCHAR(20), YearBorn VARCHAR(4));

© Copyright 1992–2002 by Deitel & Associates, Inc. All Rights Reserved. 8/24/01

6

MySQL Installation Instructions

Appendix U

To manipulate a particular database, the command is: USE FCVCDCUGAPCOG;

Before accessing and/or modifying any tables, the database in which the table resides needs be explicitly stated. Although the default database in MySQL is mysql, the USE command must be used to access any of its tables.

21.2.2 Books Sample Database Now, let us create the Books database and use it to create the Authors table (Fig. U.6). To perform these tasks, type the following lines at the monitor prompt: CREATE DATABASE Books; USE Books; CREATE TABLE Authors (AuthorID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, FirstName VARCHAR(15), LastName VARCHAR(20), YearBorn VARCHAR(4));

Fig. U.6

Commands to create the Books database and the Authors table.

The Authors table contains four columns: AuthorID, FirstName, LastName and YearBorn. The integer value of AuthorID is the primary key for this table and cannot have a null value—each record must have a value associated with AuthorID. It would be tedious, not to mention error-prone, to create an unique primary key value each time we insert a new record into the table, so we use AUTO_INCREMENT, a column modifier. AUTO_INCREMENT increases a column’s value by adding 1 to the current maximum value. Unless specified otherwise, AUTO_INCREMENT starts at 1. The first record in a table is assigned the value 1, the second record is assigned 2, and so on. The columns FirstName, LastName and YearBorn are of string type VARCHAR (variable character). The number inside the parentheses following the data type specifies the maximum number of characters used to represent each column. For instance, the value of FirstName can have a maximum length of 15. It can always have less, but never more than 15 characters. MySQL also supports the CHAR(X) string type, where X is an integer value © Copyright 1992–2002 by Deitel & Associates, Inc. All Rights Reserved. 8/24/01

Appendix U

MySQL Installation Instructions

7

between 1 and 255. The difference between the two string types is that VARCHAR is of variable length while CHAR is of fixed length. If we declare a column as VARCHAR(20) and all values within that column contain 10 characters at most, then the total amount of allocated storage space is 11 bytes (the length plus one). On the other hand, if we declare that same column as CHAR(20) and all the values within that column contain 10 characters at most, then the total amount of allocated storage space is 20 bytes (a waste of 9 bytes). To display the attributes of a table, use the DESCRIBE command. The DESCRIBE command lists the following attributes of a table: the column name, the data type (int, VARCHAR, etc.), if it can contain null values or not (fields that can contain null values state YES, those that cannot contain null values are left blank), the type of key (primary key or foreign key), the default value of the field and any additional information To display the attributes of the Authors table, type the following command at the monitor prompt (Fig. U.7): DESCRIBE Authors;

Fig. U.7

Description of the Authors table.

21.2.3 Importing and Exporting Data To insert records into the table columns we may use the INSERT INTO command. However, inserting vast amount of records using the INSERT INTO command is a tedious, exhaustive, and erroneous way of populating tables within a large database. Fortunately, MySQL offers a more efficient command, LOAD DATA INFILE. The syntax for this command is: LOAD DATA INFILE "RCVJACPFAPCOGAQHAHKNG" INTO TABLE VCDNGAPCOG;

where RCVJACPFAPCOGAQHAHKNG gives the symbolic link to the file containing the data to be imported and VCDNGAPCOG is where the data will reside. For example, assume that we have a file, Authors.txt, in the C:\ directory that contains the names and birth years of various authors that we want to import into the Authors table (the first column, AuthorID, in the text file contains NULL as the value for each record). Instead of using multiple INSERT INTO commands to manually type in the data, we can use the LOAD DATA INFILE © Copyright 1992–2002 by Deitel & Associates, Inc. All Rights Reserved. 8/24/01

8

MySQL Installation Instructions

Appendix U

command (Fig. U.8). Remember to change into the Books database as it contains the Authors table. [Note: When importing files from the hard drive, remember to provide an extra backslash because the backslash character (\) is an escape character in MySQL.]

Fig. U.8

Command to import a file into an existing table.

MySQL has a similar command that allows us to export data from an existing database table to a file. Data might need to be exported from various tables so that a back-up exists (i.e., in case we accidentally delete a table). The command to export a table is: SELECT * INTO OUTFILE "RCVJACPFAPCOGAQHAHKNG" FROM VCDNGAPCOG;

where RCVJACPFAPCOGAQHAHKNG gives the location where the exported file will reside and VCDNGAPCOG states where the data resides. For instance, if we want to store the contents of the Authors table in a back-up file called Authors_backup.txt on the C:\ directory, then we type the following (Fig. U.9): SELECT * INTO OUTFILE "C:\\Authors_backup.txt" FROM Authors;

Fig. U.9

Command to export a file from an existing table.

Once again, remember to include a double backslash in the directory path. [Note: This procedure does not delete the records from the database table; it just makes a copy.]

© Copyright 1992–2002 by Deitel & Associates, Inc. All Rights Reserved. 8/24/01