Episode 4 : Adding tables to a database with SQL queries

You can use the CREATE query to add tables to a database. The query begins with the CREATE TABLE statement, as follows:

CREATE TABLE tablename

Then, you add a list of column names with definitions. Separate the information for each column from the information for the following column by a comma. Enclose the entire list in parentheses. Follow each column name by its data type and any other definitions required. The last item in a CREATE TABLEquery indicates which column or combination of columns is the primary key. You specify the primary key by using the following format:

PRIMARY KEY(columnname)

Enclose the columnnamein parentheses. If you’re using a combination of columns as the primary key, include all the column names in the parentheses, separated by commas. For instance, you could designate the primary key as

PRIMARY KEY (columnname1,columnname2).

A complete CREATE TABLE query has the following format:

CREATE TABLE tablename (

columnnamedatatype definition1 definition2 ...,

columnnamedatatype definition1 definition2 ...,

...,

PRIMARY KEY(columnname) )

Listing shows the CREATE TABLE query used to create the Customer table of the CustomerOrderInformation database. You could enter this query on a single line if you wanted to. MySQL doesn’t care how many lines you use. The format shown in Listing  simply makes the query easier for you to read. This human-friendly format also helps you spot typos.

Listing  An SQL Query for Creating a Table

CREATE TABLE Customer (

CustomerID    SERIAL,

lastName      VARCHAR(50),

firstName     VARCHAR(40),

street        VARCHAR(50),

city          VARCHAR(50),

state         CHAR(2),

zip           CHAR(10),

email         VARCHAR(50),

phone         CHAR(15),

fax           CHAR(15),

PRIMARY KEY(customerID) )

Written by ‘Shojib’.

Episode 3 : Connecting to the Database

Before you can connect to the database, you need four things:

❑ Database name

❑ Host Server name

❑ Username

❑ Password

Connect to the database using the following command (in PHP):

$connection = mysql_connect(“servername”, “username”, “password”);

You then need to select the appropriate database by using the following command (in PHP):

<?php

$database = mysql_select_db(“databasename”, $connection)

or die(“couldn’t find the database”);

?>

Written by ‘Shojib’.

Episode 2 : Database Manipulation Commands

Use the following commands to create and make changes to your database and tables.

Command (Blue color) and What It Does

CREATE databasename Creates the database.

CREATE tablename (field1, field2,.field3, and so on PRIMARY KEY(field)) Creates a new table

ALTER TABLE tablename WHERE condition  Modifies a table in the database.

RENAME TABLE oldtablename  TO Renames a table in the database. newtablename

INSERT INTO tablename (field1, field2, . . .) VALUES (“value1”,“value2” . . .) Inserts information into the table.

UPDATE tablename SET field1=value1, field2=value2 . . . WHERE condition the table. Changes information already stored in

DELETE FROM tablename WHERE condition Deletes a record from the specified

table.

DROP tablename Deletes the table.

DROP database Deletes the database.

LOAD DATA INFILE “filename” INTO TABLE tablename. Loads a large quantity of data into the database

Written by ‘Shojib’.

Episode 1 : Introducing MySQL

Many dynamic Web sites require a backend database. The database can contain information that the Web pages display to the user. Or, the purpose of the database might be to store information provided by the user. In some applications, the database both provides available information and stores new information. MySQL, the most popular database for use in Web sites, was developed to be fast and small, specifically for Web sites. MySQL is particularly popular for use with Web sites that are written in PHP, and PHP and MySQL work well together.

How MySQL Works

The MySQL software consists of the MySQL server, several utility programs that assist in the administration of MySQL databases, and some supporting software that the MySQL server needs. The heart of the system is the MySQL server. The MySQL server is the manager of the database system. It handles all your database instructions. For instance, if you want to create a new database, you send a message to the MySQL server that says “create a new database and call it newdata.” The MySQL server then creates a subdirectory in its data directory, names the new subdirectory newdata, and puts the necessary files with the required format into the newdata subdirectory. In the same manner, to add data to that database, you send a message to the MySQL server, giving it the data and telling it where you want the data to be added. Before you can pass instructions to the MySQL server, it must be running and waiting for requests. The MySQL server is usually set up so that it starts when the computer starts and continues running all the time. This is the usual setup for a Web site. However, it’s not necessary to set it up to start when the computer starts. If you need to, you can start it manually whenever you want to access a database. When it’s running, the MySQL server listens continuously for messages that are directed to it.

Written by ‘Shojib’.