Please note:

This material is adapted from Build Your Own Database Driven Website Using PHP and MySQL, by Kevin Yank. 2nd Edition, published by Sitepoint Pty. Ltd. ISBN 0-9579218-1-0. The book includes much more information, and you are encouraged to buy it!

Chapter 2 Notes, page 2

What is SQL?

The set of commands, or queries, is part of a standard called Structured Query Language, pronounced Ess-Cue-Ell, or See-Kwel. SQL is the standard language for interacting with most databases. Here is an important distinction: MySQL is the database server software you are using. SQL is the language that you use to interact with that database.

Creating a Database

At the prompt, type "create database (name you want);" - It's that easy! Then you must tell MySQL that you want to use the database you just created. Type "use songs" (in this case.)

mysql> create database songs;
Query OK, 1 row affected (0.03 sec)

mysql> use songs;
Database changed
mysql>

Then, we create a table:

• Type "create table", with a title, and then add some specifics, within parentheses:
• The first field will be called id, and it will be an integer.
• "Not null" means that you cannot leave it blank, and auto_increment will cause it to add numbers sequentially; this is what we want, because it is the unique record identifier, or "primary key," which we also specify.
• Each field is separated by a comma.
• The second field is called "songtitle" and it will consist of text.
• The third field is called "artist", and it also contains text.
• The last field will be for the date we enter the data, so it is "songdate", and it is in date format, and cannot be left blank - "not null."
• Finish up by closing the parentheses and adding a semicolon.

mysql> create table songs (id int not null auto_increment primary key,
    -> songtitle text,
    -> artist text,
    -> songdate date not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>

Note that you can use any case, but on a UNIX server, a MySQL server will be case-sensitive with database and table names. MySQL is not case sensitive otherwise, except that if you use table, columns and other names more than once within the same command, you must spell all instances alike.

Also: we assigned a specific data type to each field we just created. MySQL requires this. It helps keep data organized, and allows you to compare values in very useful ways. More on that later.

To see the table you created, type "show tables" - and then "describe songs."

mysql> show tables;
+-----------------+
| Tables_in_songs |
+-----------------+
| songs           |
+-----------------+
1 row in set (0.01 sec)

mysql> describe songs;
+-----------+---------+------+-----+------------+----------------+
| Field     | Type    | Null | Key | Default    | Extra          |
+-----------+---------+------+-----+------------+----------------+
| id        | int(11) |      | PRI | NULL       | auto_increment |
| songtitle | text    | YES  |     | NULL       |                |
| artist    | text    | YES  |     | NULL       |                |
| songdate  | date    |      |     | 0000-00-00 |                |
+-----------+---------+------+-----+------------+----------------+
4 rows in set (0.00 sec)

mysql>

Inserting Data Into a Table

Use the command "insert" as follows (there are actually two ways to use this command, but I am going to use one and let you go to the book to see the other):

mysql> insert into songs set
    -> songtitle = "Light My Fire",
    -> artist = "The Doors",
    -> songdate = "2004-08-07";
Query OK, 1 row affected (0.00 sec)