Starting the MySQL Server

Type in the root user’s MySQL password as prompted. (If no password has been set, you can skip the -p option.) The mysql> prompt appears, ready to accept commands for working with the mysql default database on the localhost. If you are connecting to the MySQL server from another host computer, add -h hostname to the command line (where hostname is the name or IP address of the computer on which the MySQL server is running). Remember, you can also log in as any valid mysql login you created, regardless of which Linux login account you are currently logged in under.

As the MySQL monitor welcome text notes, be sure to end each command that you type with a semicolon ( ; ) or \g . If you type a command and it appears to be waiting for more input, it’s probably because you forgot to put a semicolon at the end.

Before you begin using the mysql interface to create databases, try checking the status of the MySQL server using the status command. The following is an example of output from the status command:

mysql>

status

-------------- mysql Ver 11.18 Distrib 3.23.58, for pc-linux-gnu (i686) Connection id: 43 Current database: mysql Current user: root@localhost Current pager:

stdout

Using outfile: '' Server version: 3.23.58 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 1 day 2 hours 57 min 19 sec Threads: 1 Questions: 136 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.001 --------------

The status information tells you about the version of the MySQL server (11.18) and the distribution (3.23.58). The output also reminds you of the current database ( mysql ) and your user name ( root@localhost ). You can see how long the server has been up ( Uptime ). You can also see how many threads are currently active and how many commands have been run to query this server (Questions).

Creating a database with mysql

Within an interactive mysql session, you can create and modify databases and tables. If you are not already connected to a mysql session, type the following command ( assuming the mysql user name of root):

$ mysql -u root -p

Enter password:

*******

mysql>

The general steps for creating a MySQL database include creating the database name, identifying the new database as the current database, creating tables, and adding data to the tables. While you are connected to a mysql session, you can run the following procedure to create a sample database.

1. To create a new database name, use the CREATE DATABASE command at the mysql> prompt. For example, to create a database named allusers, type the following:

   mysql>
   
   CREATE DATABASE allusers;
   
   

   This action creates a database called allusers in the /var/lib/mysql directory. (While you don't have to use capitals for the commands just shown, it makes it easier to distinguish the commands from the database entries.)

   Note

   Alternatively, you could create a database from the command line using the mysqladmin command. For example, to create the database named allusers with mysqladmin , you could type the following: mysqladmin -u root -p create allusers

2. To see what databases are available for your mysql server, type the following at the mysql> command prompt:

   mysql>
   
   SHOW DATABASES;
   
   +----------+  Database  +----------+  allusers   mysql   test  +----------+ 3 rows in set (0.00 sec)
   

   The databases shown here are named allusers , mysql , and test . The allusers database is the one created in the previous step. The mysql database contains user access data. The test database is created automatically for creating test mysql databases.

3. To work with the database you just created ( allusers ), you need to make allusers your current database. To do that, type the following at the mysql> command prompt:

   mysql>
   
   USE allusers;
   
   Database changed
   

4. Creating a table for your database requires some planning and understanding of table syntax. You can type in the following commands and column information to try out creating a table. For more detailed information on creating tables and using different data types, refer to the section “Understanding MySQL Tables” later in this chapter.

   To create a table called names , use the following CREATE TABLE command at the mysql> prompt:

   mysql>
   
   CREATE TABLE names (
   
   ->
   
   
   firstname
   
   varchar(20) not null,
   
   ->
   
   lastname varchar(20) not null,
   
   ->
   
   streetaddr varchar(30) not null,
   
   ->
   
   city varchar(20) not null,
   
   ->
   
   state varchar(20) not null,
   
   ->
   
   
   zipcode
   
   varchar(10) not null
   
   ->
   
   );
   
   Query OK, 0 rows affected (0.00 sec)
   

You have now created a table called names for a database named allusers . It contains columns called firstname, lastname, streetaddr, city, state, and zipcode. Each column allows record lengths of between 10 and 30 characters . Although MySQL supports several different database formats, because none is specified here, the default MyISAM database type is used.

With a database and one table created, you can now add data to the table.

Adding data to a MySQL database table

After the database is created and the structure of a database table is in place, you can begin working with the database. You can add data to your MySQL database by manually entering each record during a mysql session or by adding the data into a plain-text file and loading that file into the database.

Manually entering data

To do the procedure in this section, I assume you have an open interactive mysql session and that you have created a database and table as described in the previous section. If you are not already connected to a mysql session, type the following command (assuming the mysql user name of root):

$ mysql -u root -p

Enter password:

*******

mysql>

To add data to an existing MySQL database, the following procedure describes how to view the available tables and load data into those tables manually. The next section describes how to create a plain-text file containing database data and how to load that file into your database.

1. To make the database you want to use your current database (in this case, allusers ), type the following command from the mysql> prompt:

   mysql> USE allusers; Database changed
   

2. To see the tables that are associated with the current database, type the following command from the mysql > prompt:

   mysql> SHOW tables; +--------------------+  Tables_in_allusers  +--------------------+  names  +--------------------+ 1 row in set (0.00 sec)
   

   You can see that the only table defined so far for the allusers database is the one called names .

3. To display the format of the names table, type the following command at the mysql > prompt:

   mysql>
   
   DESCRIBE names;
   
   +-----------+------------+------+-----+---------+-------+  Field  Type  Null  Key  Default  Extra  +-----------+------------+------+-----+---------+-------+  firstname  varchar(20)      lastname  varchar(20)      streetaddr varchar(30)      city  varchar(20)      state  varchar(20)      zipcode  varchar(10)     +-----------+------------+------+-----+---------+-------+
   

4. To add data to the new table, you can use the INSERT INTO command from the mysql> prompt. Here is an example of how to add a person’s name and address to the new table:

   mysql>
   
   INSERT INTO names
   
   ->
   
   VALUES ('Jerry','Wingnut','167 E Street',
   
   ->
   
   'Roy','UT','84103');
   
   

   In this example, the INSERT INTO command identifies the names table. Then it indicates that values for a record in that table include the name Jerry Wingnut at the address 167 E Street, Roy, UT 84103.

5. To check that the data has been properly entered into the new table, type the following command from the mysql> prompt:

   mysql>
   
   SELECT * FROM names;
   
   +-----------+----------+-------------+-------+-------+---------+  firstname  lastname  streetaddr  city  state  zipcode  +-----------+----------+-------------+-------+-------+---------+  Jerry  Wingnut  167 E Street Roy  UT  84103  +-----------+----------+-------------+-------+-------+---------+
   

The resulting output shows the data you just entered, displayed in the columns you defined for the names table. If you like, you can continue adding data in this way.

Typing each data item individually can be tedious . As an alternative, you can add your data to a plain-text file and load it into your MySQL database, as described in the following section.

Loading data from a file

Using the LOAD DATA command during a mysql session, you can load a file containing database records into your MySQL database. Here are a few things you need to know about creating a data file to be loaded into MySQL.

• You can create the file using any Linux text editor.

• Each record, consisting of all the columns in the table, must be on its own line. (A line feed indicates the start of the next record.)

• Separate each column by a Tab character.

• You can leave a column blank for a particular record by placing a \N in that column.

• Any blank lines you leave in the file result in blank lines in the database table.

In this example, the following text is added into a plain-text file. The text is in a format that can be loaded into the names table created earlier in this chapter. To try it out, type the following text into a file. Make sure that you insert a Tab character between each value.

Chris Smith 175 Harrison Street Gig Harbor WA 98999 John Jones 18 Talbot Road NW Coventry NJ 54889 Howard Manty 1515 Broadway New York NY 10028

When you are done entering the data, save the text to any filename that is accessible to the mysql server daemon (for example, /tmp/name.txt ). Remember the filename so that you can use it later. If you are not already connected to a mysql session, type the following command (assuming mysql is the user name root):

$ mysql -u root -p

Enter password:

*******

mysql>

Next, identify the database ( allusers in this example) as the current database by typing the following:

mysql> USE allusers; Database changed

To actually load the file into the names table in the allusers database, type the following command to load the file (in this case, /tmp/name.txt ) from the mysql> prompt.

mysql>

LOAD DATA INFILE "/tmp/name.txt" INTO TABLE names;

Query OK, 3 rows affected (0.02 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

SELECT * FROM names;

+----------+---------+--------------------+------------+-------+-------+  firstname lastname streetaddr  city  state zipcode +----------+---------+--------------------+------------+-------+-------+  Chris  Smith  175 Harrison Street Gig Harbor  WA  98999   John  Jones  18 Talbot Road NW  Coventry  NJ  54889   Howard  Manty  1515 Broadway  New York  NY  10028  +----------+---------+--------------------+------------+-------+-------+