Structured Query Language

Even though we will be using PHPMyAdmin to manage our databases, it is useful to review some of the main elements of Structured Query Language (SQL).

Contents

Creating Tables

CREATE TABLE books (                     // Creating a table named books
title_id INT NOT NULL AUTO_INCREMENT,    // The title_id column takes an INT and can't be left blank
title VARCHAR (150),                     // The title field is a string of up to 150 characters
pages INT,                               // The pages field is an INT
PRIMARY KEY (title_id));                 // The primary key for this field is the title_id

CREATE TABLE authors (
author_id INT NOT NULL AUTO_INCREMENT,
title_id INT NOT NULL,
author VARCHAR (125),
PRIMARY KEY (author_id));

Describing the Table Structure

The DESCRIBE command can be used to report a table's structure:

mysql> describe books;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| title_id | int(11)      | NO   | PRI | NULL    | auto_increment | 
| title    | varchar(150) | YES  |     | NULL    |                | 
| pages    | int(11)      | YES  |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> describe authors;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| author_id | int(11)      | NO   | PRI | NULL    | auto_increment | 
| title_id  | int(11)      | NO   |     | NULL    |                | 
| author    | varchar(125) | YES  |     | NULL    |                | 
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

Adding Data to Tables

The INSERT command is used to add data to a table. Each insert adds a row to the table. The command has the following syntax:

INSERT INTO table COLUMNS ([an array of column names]) VALUES ([an array of values]);

Numeric values, NULL, and functions are never quoted in the values array, but strings, dates, and time values are quoted:

mysql> INSERT INTO books VALUES (1, "Linux in a Nutshell", 112);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO authors VALUES(NULL, 1, "Ellen Siever");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO authors VALUES(NULL, 1, "Aaron Weber");
Query OK, 1 row affected (0.00 sec)

Changing Table Structure

It is possible to change the definition of tables using the ALTER command:

ALTER TABLE books RENAME publications;            // Change the name of a table
ALTER TABLE authors MODIFY author VARCHAR(150);   // Change a column's data type
ALTER TABLE authors MODIFY author varchar(125) AFTER author_id; // Changing columns order
ALTER TABLE publications ADD time TIMESTAMP;      // Adding a new column
ALTER TABLE authors CHANGE author author_name varchar(125);  // Renaming a column
ALTER TABLE publications DROP COLUMN pages;       // Deleting a column
DROP TABLE test_table;        // Deleting an entire table

Querying the Database

To retrieve information from the database we use the SELECT command:

SELECT * FROM books;                                // View all columns of a table
SELECT author_id, title_id, author FROM authors;    // View only certain columns
SELECT * FROM books WHERE title="Classic Shell Scripting";  // Limit retrieval by a condition
SELECT books.pages FROM books WHERE title="Classic Shell Scripting"; // Display only certain columns
SELECT * from authors ORDER by author;  // Display results in a certain order