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):
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) |