7.2. Structured Query LanguageNow 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.
7.2.1. Creating TablesUse 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
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:
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.
7.2.2. Adding Data to a TableThe 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.
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 DatabaseHaving 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 WHEREIf 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 orderThe 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 togetherThe 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
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 joinsYou 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. AliasesUse 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 DataIf 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.
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 DataThis 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 FunctionsAs 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 OperatorsThe 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. |