Creating a Database Using MySQL


We're going to create a sample database by using MySQL from the command line. You can run the MySQL server from the command line (a DOS window in Windows) by changing to the directory mysql/bin and entering this command:

 %mysqld --console 

This starts the server. Now we'll start a MySQL session that will connect to the server. Open a new command line window and change to the directory mysql/bin. If you set a username and password, start MySQL using -u and -p options:

 %mysql -u user -p Enter password: ******** 

If you don't have a username and password, enter mysql -u root, or just mysql, to start:

 %mysql -u root Welcome to the MySQL monitor.  Commands end with ; or \g. mysql> 

At the mysql> prompt that appears, enter SELECT VERSION(), CURRENT_DATE; to confirm that MySQL is working:

 mysql> SELECT VERSION(), CURRENT_DATE; +---------------+--------------+ | VERSION()     | CURRENT_DATE | +---------------+--------------+ | 4.0.20a       | 2004-09-06   | +---------------+--------------+ 1 row in set (0.05 sec) 

Want to see what databases MySQL already comes with? Enter the SHOW DATABASES command, and you'll get something like this:

 mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql    | | test     | +----------+ 2 rows in set (0.01 sec) 

MySQL comes with two databases built-inmysql, which holds administrative data for MySQL, and test, which is a sample database. To store data about a variety of fruits and vegetables, you might create a new database named, say, "produce," with the CREATE DATABASE command:

 mysql> CREATE DATABASE produce; Query OK, 1 row affected (0.01 sec) 

To make this database the default one to work with, enter the USE produce command:

 mysql> USE produce Database changed 

Are there any tables in the produce database yet? Try the SHOW TABLES command:

 mysql> SHOW TABLES; Empty set (0.01 sec) 

The response is "Empty set," which means there are no tables yet. To create a table named fruit, you need to create its data fields. There are various data types for fields; here are a few (we'll use strings in this example):

  • VARCHAR(length). Creates a variable length string

  • INT. Creates an integer

  • DECIMAL(totaldigits, decimalplaces). Creates a decimal value

  • DATETIME. Creates a date and time object, such as 2006-11-15 20:00:00

Here's how to create the fruit table with name and number fields, both stored in strings:

 mysql> CREATE TABLE fruit (name VARCHAR(20), number VARCHAR(20)) Query OK, 0 rows affected (0.13 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_produce | +-------------------+ | fruit             | +-------------------+ 1 row in set (0.00 sec) 

To get a description of this new table, use the DESCRIBE command:

 mysql> DESCRIBE fruit; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name   | varchar(20) | YES  |     | NULL    |       | | number | varchar(20) | YES  |     | NULL    |       | +--------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) 



    Spring Into PHP 5
    Spring Into PHP 5
    ISBN: 0131498622
    EAN: 2147483647
    Year: 2006
    Pages: 254

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net