Section 7.2. Structured Query Language

7.2. Structured Query Language

Now that you've defined a table, you can add data to it. MySQL will keep track of all the details. To manipulate data, use the Structured Query Language (SQL) commands. Because it's been designed to easily describe the relationship between tables and rows, the database uses SQL to modify data in tables.

SQL is a standard language used with any database such as MySQL, Oracle, or Microsoft SQL Server. It was developed specifically as a language used to retrieve, add, and manipulate data that resides in databases. We'll get into the nitty gritty of MySQL in Chapter 8, but we'll start with some easy-to-use commands. We're going to start with creating tables.

Each database adds on its own extensions to the standard SQL. These are usually more advanced capabilities, such as an outer join. An outer join is a special way of linking two tables so that data from one of the tables is included even if there isn't a match in the other table. The syntax for outer joins for tables in Oracle is a plus within parentheses (+), whereas MySQL uses the syntax left join on to perform an outer table join.

7.2.1. Creating Tables

Use the create table command to specify the structure of new database tables. When you create a database table, each column has a few options, in addition to the column names and data types. Values that must be supplied when adding data to a table use the NOT NULL keyword. The PRIMARY KEY keyword tells MySQL which column to use as a key field. Then, you have MySQL automatically assign key values by using the AUTO_INCREMENT keyword.

To create these tables, paste the code into the MySQL command-line client.

Example 7-1 creates the books table using the data types from Table 7-8.

Example 7-1. Creating the books and authors tables

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

If everything is OK, you'll see output that instructs MySQL to create a table called "books," and it'll look like this (the time the query takes to run may be different than 0.06 sec):

 mysql> CREATE TABLE books (     -> title_id INT NOT NULL AUTO_INCREMENT,     -> title VARCHAR (150),     -> pages INT,     -> PRIMARY KEY (title_id)); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE authors (     -> author_id INT NOT NULL AUTO_INCREMENT,     -> title_id INT,     -> author VARCHAR (125),     -> PRIMARY KEY (author_id)); Query OK, 0 rows affected (0.06 sec) 

The code breaks down as follows:

• The first column, called title_id, is an integer. The auto_increment keyword is a unique value assigned to this field automatically during row insertion.

• The title column holds text up to 150 characters.

• The pages column is an integer.

• The PRIMARY KEY field tells MySQL which field is the key value. While this field isn't required, it allows MySQL to speed up access when you retrieve data from multiple tables or a specific row using the key value. MySQL does this by using a special data structure called an index. An index acts like a shortcut for finding a record, like a card catalog in a library. To verify your table columns, use DESCRIBE:

   DESCRIBE books; 

  
  

This returns:

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

And the following:

 describe authors; 

returns:

 +-----------+--------------+------+-----+---------+----------------+ | Field     | Type         | Null | Key | Default | Extra          | +-----------+--------------+------+-----+---------+----------------+ | author_id | int(11)      |      | PRI | NULL    | auto_increment | | title_id  | int(11)      |      |     | 0       |                | | author    | varchar(125) | YES  |     | NULL    |                | +-----------+--------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) 

Everything is as we specified in our description.

Notice that because we didn't specify the size of the integer columns, MySQL used the default of 11 places.

7.2.2. Adding Data to a Table

The insert command is used to add data. Its syntax is INSERT INTO table VALUES ([values]);. This syntax displays which table data needs to be added to, and a list of the values. They should be in the same order they were defined when the table was created (as long as you don't skip any column values). There are specific rules for how you handle data to populate your database using SQL commands.

• Numeric values shouldn't be quoted.

• String values should always be quoted.

• Date and time values should always be quoted.

• Functions shouldn't be quoted.

• NULL should always be quoted.

Lastly, if a row isn't given a value, it automatically is considered NULL. However, if a column can't have NULL, even if it was set to NOT NULL; if you don't specify a value, an error is created.

For example:

 INSERT INTO books VALUES (1,"Linux in a Nutshell",112); INSERT INTO authors VALUES (1,1,"Ellen Siever"); INSERT INTO authors VALUES (2,1,"Aaron Weber"); 

As long as there were no errors, you should get:

 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"); ES (2,1Query OK, 1 row affected (0.00 sec) ,"Aaron Weber"); mysql> INSERT INTO authors VALUES (NULL,1,"Aaron Weber"); Query OK, 1 row affected (0.00 sec) 

When adding data, you must specify all the columns even if you aren't supplying a value for each one. Even though we didn't supply the author_id field and we let MySQL assign it for us, we still had to leave a placeholder for it.

Likewise, we add the other book:

 INSERT INTO books VALUES (2,"Classic Shell Scripting",256); INSERT INTO authors VALUES (NULL,2,"Arnold Robbins"); INSERT INTO authors VALUES (NULL,2,"Nelson Beebe"); 

This gives us two rows in the books table. Now that you know how to create a table and enter data into it, you'll need to know how to view that information.

7.2.3. Querying the Database

Having data in tables doesn't do much good if you can't view what's in it. The SELECT command specifies which table(s) to query and which rows to view based on specific conditions. The syntax of SELECT is SELECT columns FROM tables [WHERE CLAUSE];.

Columns indicate a list of columns to display from the selected tables. The WHERE clause optionally restricts which rows are selected. WHERE provides limits to the results that are returned from a query. For example, rows can be rejected if a field doesn't equal a literal value or is less than or greater than a value. Fields from multiple tables can be forced to be equal. If multiple tables are included in a SELECT statement without a WHERE clause, the resultant set becomes the Cartesian product, in which every row in the first table is returned with all rows in the second table followed by the same thing for the second row in the first table. To put it another way, that's a lot of results!

The simplest query is to view all data in a table:

 SELECT * FROM books; 

This displays:

 +----------+-------------------------+-------+ | title_id | title                   | pages | +----------+-------------------------+-------+ |        1 | Linux in a Nutshell     |   112 | |        2 | Classic Shell Scripting |   256 | +----------+-------------------------+-------+ 2 rows in set (0.01 sec) 

And the following:

 SELECT * FROM authors; 

displays:

 +-----------+----------+-------------------+ | author_id | title_id | author            | +-----------+----------+-------------------+ |         1 |        1 | Ellen Siever      | |         2 |        1 | Aaron Weber       | |         3 |        2 | Arnold Robbins    | |         4 |        2 | Nelson Beebe      | +-----------+----------+-------------------+ 5 rows in set (0.01 sec) 

7.2.3.1. Limit results with WHERE

If you're only interested in the title Classic Shell Scripting, you can use a WHERE clause to restrict your query:

 SELECT * FROM books WHERE title=('Classic Shell Scripting'); 

This returns:

 +----------+-------------------------+-------+ | title_id | title                   | pages | +----------+-------------------------+-------+ |        2 | Classic Shell Scripting |   256 | +----------+-------------------------+-------+ 1 row in set (0.00 sec) 

You can also list out just the columns you're interested in from a table by using:

 SELECT pages FROM books WHERE title=('Classic Shell Scripting'); 

This returns:

 +-------+ | pages | +-------+ |   256 | +-------+ 1 row in set (0.00 sec) 

Conditions come after the WHERE clause and should be enclosed by parentheses (()). This forces the condition to be evaluated. Additionally, parentheses are a good idea, since you'll need them when you have nested conditions in complex queries. Getting into the habit of doing this from the beginning is best. At some point, you might want to display data from multiple tables in a query. You should also get into the habit of using the full TABLE.COLUMN reference. This prevents confusion when selecting columns if both tables have a column with the same name. For example, if two tables include a description field, it may not be clear which description to include in the query unless the full reference is included.

7.2.3.2. Specifying the order

The ORDER BY keyword can be used to change the order of the results from a query. The default for ORDER BY is ascending, so if you want alphabetical order for the author column, you would just type in ORDER BY author. To select in reverse order, add the DESC keyword after author. For example, to select the authors in alphabetical order:

 SELECT * FROM authors ORDER BY author; 

This displays:

 +-----------+----------+-------------------+ | author_id | title_id | author            | +-----------+----------+-------------------+ |         2 |        1 | Aaron Weber       | |         5 |        9 | Alex Martelli     | |         3 |        2 | Arnold Robbins    | |         1 |        1 | Ellen Siever      | |         4 |        2 | Nelson Beebe      | +-----------+----------+-------------------+ 

Next, we'll select from more than one table.

7.2.3.3. Joining tables together

The SELECT statement allows you to query more than one table at a time. Example 7-2 creates the purchases table and adds a couple of sample entries.

Example 7-2. The SQL to create and populate a purchases table that links user_ids and title_ids to a purchase_id

 CREATE TABLE `purchases` ( purchase_id int(11) NOT NULL auto_increment, user_id varchar(10) NOT NULL, title_id int(11) NOT NULL, purchased timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY  (purchase_id)); INSERT INTO `purchases` VALUES (1, 'mdavis', 2, '2005-11-26 17:04:29'); INSERT INTO `purchases` VALUES (2, 'mdavis', 1, '2005-11-26 17:05:58'); 

Example 7-2 returns:

 SELECT * FROM purchases; +-------------+---------+----------+---------------------+ | purchase_id | user_id | title_id | purchased           | +-------------+---------+----------+---------------------+ |           1 | mdavis  |        2 | 2005-11-26 17:04:29 | |           2 | mdavis  |        1 | 2005-11-26 17:05:58 | +-------------+---------+----------+---------------------+ 2 rows in set (0.00 sec) 

To create a query that lists the purchases, author, and pages, enter the following SELECT statement:

 SELECT books.*, author FROM books, authors WHERE books.title_id = authors.title_id; 

which produces:

 +----------+-------------------------+-------+-------------------+ | title_id | title                   | pages | author            | +----------+-------------------------+-------+-------------------+ |        1 | Linux in a Nutshell     |   112 | Ellen Siever      | |        1 | Linux in a Nutshell     |   112 | Aaron Weber       | |        2 | Classic Shell Scripting |   256 | Arnold Robbins    | |        2 | Classic Shell Scripting |   256 | Nelson Beebe      | +----------+-------------------------+-------+-------------------+ 4 rows in set (0.00 sec) 

The books.*, author portion tells the database to select all of the fields from the books table but only the author from the authors table. The WHERE books.title_id = authors.title_id portion links the tables together by the title_id.

You could have selected *, which includes all the fields from both tables. But the title_id field would be included twice, since it's in both tables. There's no limit to how many tables and columns you can join together.

As you've noticed, SQL is a combination of alpha characters and symbols used in mathematics. The structure in SQL means it uses English phrases to define an action but uses math-like symbols to make comparisons. Remember this analogy, and it should make it easier to remember that you always need a math-like symbol in your context. Then it will be easier for you to catch your errors!

7.2.3.4. Natural joins

You can specify the NATURAL JOIN keyword to accomplish the same query as above with less typing. With natural joining, MySQL can take two tables and automatically join the fields that have the same name. In the case of the two tables you're working with, that's the title_id field. It also knows not to display title_id twice and not to display the author_id for author. The following:

 SELECT * FROM books NATURAL JOIN authors; 

produces:

 +----------+-------------------------+-------+-----------+-------------------+ | title_id | title                   | pages | author_id | author            | +----------+-------------------------+-------+-----------+-------------------+ |        1 | Linux in a Nutshell     |   112 |         1 | Ellen Siever      | |        1 | Linux in a Nutshell     |   112 |         2 | Aaron Weber       | |        2 | Classic Shell Scripting |   256 |         3 | Arnold Robbins    | |        2 | Classic Shell Scripting |   256 |         4 | Nelson Beebe      | +----------+-------------------------+-------+-----------+-------------------+ 4 rows in set (0.00 sec) 

7.2.3.5. Aliases

Use aliases when listing which tables to include in your query. The AS keyword comes after the full table name and before the alias. In this example, "books" is aliased to b and "purchases" to p:

 SELECT * FROM books AS p,authors AS b WHERE b.title_id = p.title_id; 

This results in:

 +----------+-------------------------+-------+-----------+----------+--------+ | title_id | title                   | pages | author_id | title_id | author         | +----------+-------------------------+-------+-----------+----------+-------------+ |        1 | Linux in a Nutshell     |   112 |         1 |        1 | Ellen Siever |        1 | Linux in a Nutshell     |   112 |         2 |        1 | Aaron Weber |        2 | Classic Shell Scripting |   256 |         3 |        2 | Arnold Robbins |        2 | Classic Shell Scripting |   256 |         4 |        2 | Nelson Beebe +----------+-------------------------+-------+-----------+----------+-------------+ 4 rows in set (0.00 sec) 

Once you alias a table in a query, you must refer to the table as the alias everywhere in the query. Aliases are useful for replacing long table names with a short abbreviation. They also allow you to include the same table twice in a query and to be able to specify which instance of that table you're referencing.

7.2.4. Modifying Database Data

If you make a mistake, say, by entering the wrong number of pages for a book, you can change data by using the UPDATE command.

UPDATE uses the same WHERE clause as the SELECT statement but adds a SET command that specifies a new column value.

If you forget to include the WHERE clause for an update, it changes every record in the table.

For example, you'll update the books table:

 UPDATE books SET pages = 476 WHERE title = "Linux in a Nutshell"; 

The example returns:

 Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 

This changes any book with the title Linux in a Nutshell to 476 pages. Modifying the data cleans up any data errors you might have made.

 SELECT * FROM books; 

This returns:

 +----------+-------------------------+-------+ | title_id | title                   | pages | +----------+-------------------------+-------+ |        1 | Linux in a Nutshell     |   476 | |        2 | Classic Shell Scripting |   256 | +----------+-------------------------+-------+ 2 rows in set (0.00 sec) 

7.2.5. Deleting Database Data

This command is used to delete rows or records in a table. The DELETE command takes the same WHERE clause as UPDATE but deletes any rows that match. Without the WHERE clause, you'd have an "oops!" moment, because all records in the table would be deleted.

 DELETE FROM authors WHERE author= "Ellen Siever"; 

In this example, only Ellen Siever's book is deleted from the database.

7.2.6. Search Functions

As you have seen in the above examples, MySQL has the ability to find specific search data. However, we have not covered general search syntax. The % character in MySQL is the wildcard character and is used with the LIKE keyword. That is, it can literally represent anything. Sort of like searching in DOS, or even in the Windows Explorer Search field where *.doc means any document, regardless of the name before the .doc ending displays.

For example, to do a general search, you would use the following syntax:

 SELECT * FROM authors WHERE author LIKE "%b%"; 

This returns:

 +-----------+----------+-------------------+ | author_id | title_id | author            | +-----------+----------+-------------------+ |         2 |        1 | Aaron Weber       | |         3 |        2 | Arnold Robbins    | |         4 |        2 | Nelson Beebe      | +-----------+----------+-------------------+ 3 rows in set (0.00 sec) 

This results in finding anything with the letter b in the column. Notice that two % signs were used. This checks for anything before or after that letter. You can use just one if you like, but there is no hard and fast rule that one or two be used.

You can place the % sign anywhere within the query's LIKE string, as the search is based upon the placement of this character.

Another wildcard character is the _ character. It will match exactly one character. To use a literal wildcard character in your searches:

 SELECT * FROM authors WHERE author like "Aaron Webe_" 

This returns all the records containing an author name that starts with "Aaron Webe" and can have any letter for the last character of the name.

7.2.7. Logical Operators

The same logical operators that we discussed with PHP's conditional logic can also be used in the WHERE clause.

You can use AND, OR, and NOT in your query's WHERE clause.

 SELECT * FROM authors WHERE NOT (author = "Ellen Siever" ); 

This returns all records without Ellen Siever as the author. The parentheses are important, as they relate the NOT operator to the author comparison.

 SELECT *   FROM books, authors  WHERE (title = "Linux in a Nutshell")    AND (author = "Aaron Weber" ); 

This query returns all records with author names of either Aaron Weber or Ellen Siever.

 SELECT *   FROM books, authors  WHERE (author = "Aaron Weber")     OR (author = "Ellen Siever") 

Now that all the basics have been covered, start getting excited. In our next chapter, we'll walk through using PHP to connect and work with MySQL data. We're well on our way to creating that blog at the end of the book.