I will use this convention set throughout this book. Feel free to use whatever makes you comfortable.
The rules have been tried and tested over many databases and have proven time and again that
naming conventions are nice—even if it means a few extra key strokes.Entering the commands from the MySQL Monitor prompt is one way to create the schema of a
database. Another way to create your schema is with a script. Scripts are text files that contain all the
SQL commands required to build your database. This is probably the best way to create your database,
because you have the ability to recreate your database (minus the data) at any given time. It also allows
for code reuse—because, generally, computer people are a lazy bunch and the less work you have to
do, the better.To start this process, open your favorite text editor. In the text editor, type the following statements:
CREATE DATABASE Temp;
USE DATABASE Temp;
CREATE TABLE Test_Table
(Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Test_Name VARCHAR(30),
Test_Date DATETIME,
Test_Giver VARCHAR(30));
INSERT INTO Test_Table
(Test_ID, Test_Name, Test_Date, Test_Giver)
VALUES
(NULL, 'Test','2000-01-01','Glen');It is common practice to format your SQL statements in this way. I'm not saying that it is bad or good. I
like it because of the readability. It takes some getting used to, but it is really clear to see. Of course,
you can enter your commands in any way that you like, as long as they are in the same order and end
with a semicolon.
In this script, you create a database named Temp. You then make this the active database. After that,
you create a table called Test_Table. Add four columns to your table. Then add one row of data. If
you were to type this into a monitor session, it would take some time. And, when you end your session,
all your statements would be gone. Save this file as Temp.sql. You could use any name here, I chose
this name because it is easy to identify what the script does.
Before you can use this script, there are a few things you must do. First, make sure the mysqld
daemon is running. Second, ensure that you are in the mysql directory. Finally, to process the script,
type the following from the command line:
bin/mysql –p </complete path/Temp.sql
You will be prompted for a password. Use the root password. You must have CREATE authority to run
this script. After your file is processed, start up the monitor using Temp as the active database. Execute
a SHOW TABLE command. You should see the Test_Table table. Now type in the following command:
SELECT * FROM Test_Table;As you can see, this is a great way to execute SQL statements from a text file.Altering Existing Tables
Now that you have created your table, what if you need to go in and change something you have done?
Changing tables is just as easy as creating them, you just have to know what you want to change. The
column name is very different from the table's name. Changing the column's type is different from changing a
column's name. Check out the following examples to see how to alter a column's name, type, and the table's
name.