MySQL in a Nutshell [14]
PATH=$PATH:/usr/local/mysql/bin
export PATH
Assuming that everything is working, you will need a MySQL username and password. If you’re not the administrator, you must obtain these from her. If MySQL was just installed and the root password is not set yet, its password is blank. To learn how to set the root password and to create new users and grant them privileges, see Chapter 2 for starting points and Chapter 4 for more advanced details.
From a shell prompt, log in to MySQL like this:
mysql -h host -u user -p
If you’re logging in locally—that is, from the server itself—either physically or through a remote login method, such as SSH (secure shell), you can omit the -h host argument. This is because the default host is localhost, which refers to the system you are on. In other circumstances, where your commands actually have to travel over a network to reach the server, replace the argument host with either a hostname that is translatable to an IP address or the actual IP address of the MySQL server. You should replace the argument user with your MySQL username. This is not necessarily the same as your filesystem username.
The -p option instructs mysql to prompt you for a password. You can also add the password to the end of the -p option (e.g., enter -prover where rover is the password); if you do this, leave no space between -p and the password. However, entering the password on the command line is not a good security practice, because it displays the password on the screen and transmits the password as clear text through the network, as well as making it visible whenever somebody gets a list of processes running on the server.
When you’re finished working on the MySQL server, to exit mysql, type quit or exit, and press the Enter key.
Creating a Database and Tables
Assuming that you have all of the privileges necessary to create and modify databases on your server, let’s look at how to create a database and then tables within a database. For the examples in this chapter, we will build a database for a fictitious bookstore:
CREATE DATABASE bookstore;
In this brief SQL statement, we have created a database called bookstore. You may have noticed that the commands or reserved words are printed here in uppercase letters. This isn’t necessary; MySQL is case-insensitive with regard to reserved words for SQL statements and clauses. Database and table names are case-sensitive on operating systems that are case-sensitive, such as Linux systems, but not on systems that are case-insensitive, such as Windows. As a general convention, though, reserved words in SQL documentation are presented in uppercase letters and database names, table names, and column names in lowercase letters. You may have also noticed that the SQL statement shown ends with a semicolon. An SQL statement may be entered over more than one line, and it’s not until the semicolon is entered that the client sends the statement to the server to read and process it. To cancel an SQL statement once it’s started, enter \c instead of a semicolon.
With our database created, albeit an empty one, we can switch the default database for the session to the new database like this:
USE bookstore
This saves us from having to specify the database name in every SQL statement. MySQL by default will assume the current database, the one we last told it to use. No semicolon is given with the USE statement because it’s a client-based SQL statement.
Next, we will create our first table, in which we will later add data. We’ll start by creating a table that we’ll use to enter basic information about books, because that’s at the core of a bookstore’s business:
CREATE TABLE books (
book_id INT,
title VARCHAR(50),
author VARCHAR(50));
This SQL statement creates the table books with three columns. Note that the entire list of columns is contained within parentheses.
The first column is a simple identification number for each record, which represents one book. You can specify the data