Setting up a Simple Database
First you need to create a database to store your data. Make certain you have your MySQL daemon running. Replace DBNAME with whatever name you want to name your database. If you are using Windows, you need to execute any MySQL commands from C:\mysql\bin. For Linux, you can issue the commands from any shell prompt. To create your database, issue the following command:
prompt>mysqladmin create DBNAME
To set up the tables in your new database, you need to login to your MySQL server. This is done by using the MySQL command-line interface.
At the prompt, issue the following command:
At the MySQL prompt, enter the following command and be sure to replace DBNAME with your database's name.
mysql> use DBNAME;
The MySQL server responds with:
You've now selected your database. Any of the basic SQL queries you enter are directed to this database.
Now you can create the table you'll be using for the upcoming projects. Enter the following commands at the MySQL prompt. Hit Enter after each line. MySQL doesn't try to interpret the commands until it sees a semicolon (;), so the command itself isn't really executed on the server until you enter the last line.
mysql> CREATE TABLE news ( ->news_id INT NOT NULL AUTO_INCREMENT, -> heading VARCHAR(48), -> body TEXT, -> date DATE, -> author_name VARCHAR(48), -> author_email VARCHAR(48), -> PRIMARY KEY(news_id));
If the server gives you a big ERROR and spits out a bunch of garbage at you, just try again from the top. You need to enter each line in the sequence from the beginning, exactly as shown.
The _ prompt you see after entering a line in the MySQL server is telling you that it's waiting for more input before it does anything.
The server responds with:
Query OK, 0 rows affected (0.00 sec)
prompt> mysql u root
This command logs you into the server. From here you can create tables, delete (drop) tables, and modify the data in your tables. But first, you must specify which database on the server you want to use, since the MySQL server is capable of hosting multiple databases.
Congratulations! You've just created your first table in MySQL.