Creating Databases and Tables


The first logical use of SQL and MySQL will be to create a database. The syntax for creating a new database is

 CREATE DATABASE databasename 

The CREATE term is also used for making tables.

 CREATE TABLE tablename ( column1name description, column2name description ...) 

As you can see from this syntax, after naming the table, you define each columnin orderwithin parentheses. Each column-description pair should be separated from the next by a comma. Should you choose to create indexes at this time, you can add those at the end of the creation statement, but you can add indexes at a later time as well.

To create databases and tables

1.

Access the mysql client using the steps outlined previously.

Throughout the rest of this chapter, all of the SQL will be entered using the mysql client. Using the steps in the preceding section of this chapter, open the mysql client, entering the proper syntax (username, password, etc.) for your system and configuration. You can also use phpMyAdmin or any other interface tool.

2.

Create and select the new database (Figure 4.6).

 CREATE DATABASE sitename; USE sitename; 

Figure 4.6. I'll be using the sitename database throughout the remainder of this chapter.


This first line creates the database (assuming that you are logged into mysql as a user with permission to create new databases). The second line tells MySQL that you want to work within this database from here on out. Remember that within the mysql client, you must terminate every SQL command with a semicolon, although these semi-colons aren't technically part of SQL itself.

If you are using a hosting company's MySQL, they will probably create the database for you.

3.

Create the users table (Figure 4.7).

 CREATE TABLE users ( user_id MEDIUMINT UNSIGNED NOT NULL  AUTO_INCREMENT, first_name VARCHAR(15) NOT NULL, last_name VARCHAR(30) NOT NULL, email VARCHAR(40) NOT NULL, password CHAR(40) NOT NULL, registration_date DATETIME NOT NULL, PRIMARY KEY (user_id) ); 

Figure 4.7. This CREATE SQL command will make the users table.


This step takes the design for the users table and integrates that within the CREATE table syntax. The order in which you enter the columns here will dictate the order in which the columns appear in the table.

Because the mysql client will not run a query until it encounters a semicolon, you can enter statements over multiple lines as I did in Figure 4.7 (by pressing Return or Enter at the end of each line). This often makes a query easier to read and debug.

4.

Confirm the existence of the table (Figure 4.8).

 SHOW TABLES; SHOW COLUMNS FROM users; 

Figure 4.8. I can confirm the existence and layout of a table using the SHOW command.


The SHOW command reveals the tables in a database or the column names and types in a table, if named.

Also, you might notice in Figure 4.8 that the default value for user_id is NULL, even though this column was defined as NOT NULL. This is actually correct and has to do with user_id being an automatically incremented primary key.

Tips

  • Throughout the rest of this chapter, I will assume that you are using the mysql client or comparable tool and have already selected the sitename database with USE.

  • Although SQL is case-insensitive, I make it a habit to capitalize the SQL words, helping to separate them from the database, table, and column names. If you would rather not capitalize these terms, you have that option.

  • When creating a table, you have the option of specifying a type, with MyISAM, BDB, InnoDB, temporary, and HEAP being the most common. If you do not specify a table type, MySQL will automatically create the table using the default, most likely MyISAM. Chapter 5 discusses the various table types in more detail.

  • DESCRIBE tablename, which you might see in other resources, is the same statement as SHOW COLUMNS FROM tablename.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

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