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).

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; +--+--+--+-+-++ +--+--+--+-+-++ +--+--+--+-+-++ 3 rows in set (0.01 sec)
 * 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    |                |

mysql> describe authors; +---+--+--+-+-++ +---+--+--+-+-++ +---+--+--+-+-++ 3 rows in set (0.03 sec)
 * 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    |                |

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 authros;   // View only certain columns SELECT * FROM books WHERE title="Classic Shell Scripting"; // Limit retrieval by a condition SELECT books.page FROM books WHERE title="Classic Shell Scripting"; // Display only certain columns SELECT * from authors ORDER by authors; // Display results in a certain order