MySQL Tutorial

0. Installment

1. Connecting to and Disconnecting from the Server

MySQL installations allow users to connect as the anonymous (unnamed) user to the server running on the local host. If this is the case on your machine, you should be able to connect to that server by invoking mysql without any options:

shell> mysql

After you have connected successfully, you can disconnect any time by typing QUIT (or \q) at the mysql> prompt:

mysql> QUIT

Bye

On Unix, you can also disconnect by pressing Control-D.

Most examples in the following sections assume you are connected to the server. They indicate this by the mysql> prompt.

2. Entering Queries

Here's a simple command that asks the server to tell you its version number and the current date. Type it in as shown here following the mysql> prompt and press Enter:

------------------------

mysql> SELECT VERSION(), CURRENT_DATE;

+--------------+--------------+

| VERSION()    | CURRENT_DATE |

+--------------+--------------+

| 3.22.20a-log | 1999-03-19   |

+--------------+--------------+

1 row in set (0.01 sec)

mysql>

--------------------------------

This query illustrates several things about mysql:

A command normally consists of an SQL statement followed by a semicolon. (There are some exceptions where a semicolon may be omitted. QUIT, mentioned earlier, is one of them. We'll get to others later.)
When you issue a command, mysql sends it to the server for execution and displays the results, then prints another mysql> prompt to indicate that it is ready for another command.
mysql displays query output in tabular form (rows and columns). The first row contains labels for the columns. The rows following are the query results. Normally, column labels are the names of the columns you fetch from database tables. If you're retrieving the value of an expression rather than a table column (as in the example just shown), mysql labels the column using the expression itself.
mysql shows how many rows were returned and how long the query took to execute, which gives you a rough idea of server performance. These values are imprecise because they represent wall clock time (not CPU or machine time), and because they are affected by factors such as server load and network latency. (For brevity, the ``rows in set'' line is not shown in the remaining examples in this chapter.)
Keywords may be entered in any lettercase.

Here's another query. It demonstrates that you can use mysql as a simple calculator:

mysql> SELECT SIN(PI()/4), (4+1)*5;

+-------------+---------+

| SIN(PI()/4) | (4+1)*5 |

+-------------+---------+

|    0.707107 |      25 |

+-------------+---------+

The queries shown thus far have been relatively short, single-line statements. You can even enter multiple statements on a single line. Just end each one with a semicolon:

mysql> SELECT VERSION(); SELECT NOW();

+--------------+

| VERSION()    |

+--------------+

| 3.2.58       |

+--------------+

+---------------------+

| NOW()               |

+---------------------+

| 1004-01-09 23:18:23 |

+---------------------+

A command need not be given all on a single line, so lengthy commands that require several lines are not a problem. mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. (In other words, mysql accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.)

Here's a simple multiple-line statement:

mysql> SELECT

    -> USER()

    -> ,

    -> CURRENT_DATE;

+--------------------+--------------+

| USER()             | CURRENT_DATE |

+--------------------+--------------+

| zjiang@localhost   | 2004-02-19   |

+--------------------+--------------+

If you decide you don't want to execute a command that you are in the process of entering, cancel it by typing \c

mysql> SELECT

    -> USER()

    -> \c

mysql>

The following table shows each of the prompts you may see and summarizes what they mean about the state that mysql is in:

Prompt	Meaning
`mysql>`	Ready for new command.
`->`	Waiting for next line of multiple-line command.
`'>`	Waiting for next line, collecting a string that begins with a single quote (`'').
`">`	Waiting for next line, collecting a string that begins with a double quote (`"').
`>	Waiting for next line, collecting an identifier that begins with a backtick (``').

3. Creating and Using a Database

Now that you know how to enter commands, it's time to access a database.

Create a database
Create a table
Load data into the table
Retrieve data from the table in various ways
Use multiple tables

3.1 Using a database

Use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| mysql    |

| test     |

| tmp      |

+----------+

The list of databases is probably different on your machine, but the mysql and test databases are likely to be among them. The mysql database is required because it describes user access privileges. The test database is often provided as a workspace for users to try things out.

If the test database exists, try to access it:

mysql> USE test

Database changed

Note that USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if you like; it does no harm.) The USE statement is special in another way, too: it must be given on a single line.

You can use the test database (if you have access to it) for the examples that follow, but anything you create in that database can be removed by anyone else with access to it. For this reason, you should probably ask your MySQL administrator for permission to use a database of your own.

3.2 Creating a Table

Creating the database is the easy part, but at this point it's empty, as SHOW TABLES will tell you:

mysql> SHOW TABLES;

Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement. Suppose Diane gets a new hamster named Puffball. You could add a new record using an INSERT statement like this:

mysql> INSERT INTO pet

    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Note that string and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL directly to represent a missing value.

You could create a text file `pet.txt' containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N (backslash, capital-N). For example, the record for Whistler the bird would look like this (where the whitespace between values is a single tab character):

To load the text file `pet.txt' into the pet table, use this command:

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

3.3 Retrieving Information from a Table

The SELECT statement is used to pull information from a table. The general form of the statement is:

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

what_to_select indicates what you want to see. This can be a list of columns, or * to indicate ``all columns.'' which_table indicates the table from which you want to retrieve data. The WHERE clause is optional. If it's present, conditions_to_satisfy specifies conditions that rows must satisfy to qualify for retrieval.

3.3.1 Selecting All Data

mysql> SELECT * FROM pet;

3.3.2 Update Data

Fix only the erroneous record with an UPDATE statement:

·                mysql> UPDATE pet SET birth = "1999-08-31" WHERE name = "clws";

The UPDATE changes only the record in question and does not require you to reload the table.

3.3.3 Selecting Particular Rows

As shown in the preceding section, it is easy to retrieve an entire table. Just omit the WHERE clause from the SELECT statement. But typically you don't want to see the entire table, particularly when it becomes large. Instead, you're usually more interested in answering a particular question, in which case you specify some constraints on the information you want. Let's look at some selection queries in terms of questions about your pets that they answer.

You can select only particular rows from your table. For example, if you want to verify the change that you made to clws's birth date, select clws's record like this:

mysql> SELECT * FROM pet WHERE name = "clws";

String comparisons normally are case-insensitive.

You can specify conditions on any column, not just name. For example, if you want to know which animals were born after 1998, test the birth column:

mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";

You can combine conditions, for example, to locate female dogs:

mysql> SELECT * FROM pet WHERE species = "hamster" AND sex = "f";

The preceding query uses the AND logical operator. There is also an OR operator:

mysql> SELECT * FROM pet WHERE species = "cat" OR species = "bird";

mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")

-> OR (species = "dog" AND sex = "f");

3.3.4 Delete all

·                mysql> DELETE FROM pet;

3.3.5 Selecting Particular Columns

If you don't want to see entire rows from your table, just name the columns in which you're interested, separated by commas. For example, if you want to know when your animals were born, select the name and birth columns:

mysql> SELECT name, birth FROM pet;

To find out who owns pets, use this query:

mysql> SELECT owner FROM pet;

However, notice that the query simply retrieves the owner field from each record, and some of them appear more than once. To minimise the output, retrieve each unique output record just once by adding the keyword DISTINCT:

mysql> SELECT DISTINCT owner FROM pet;

You can use a WHERE clause to combine row selection with column selection. For example, to get birth dates for dogs and cats only, use this query:

mysql> SELECT name, species, birth FROM pet

-> WHERE species = "dog" OR species = "cat";

3.3.6 Sorting Rows

You may have noticed in the preceding examples that the result rows are displayed in no particular order. It's often easier to examine query output when the rows are sorted in some meaningful way. To sort a result, use an ORDER BY clause.

Here are animal birthdays, sorted by date:

mysql> SELECT name, birth FROM pet ORDER BY birth;

The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by:

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

You can sort on multiple columns, and you can sort columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query:

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

3.3.7 Date Calculations

MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates.

To determine how many years old each of your pets is, compute the difference in the year part of the current date and the birth date, then subtract one if the current date occurs earlier in the calendar year than the birth date. The following query shows, for each pet, the birth date, the current date, and the age in years.

mysql> SELECT name, birth, CURDATE(),

    -> (YEAR(CURDATE())-YEAR(birth))

    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))

    -> AS age

-> FROM pet;

Here, YEAR() pulls out the year part of a date and RIGHT() pulls off the rightmost five characters that represent the MM-DD (calendar year) part of the date. The part of the expression that compares the MM-DD values evaluates to 1 or 0, which adjusts the year difference down a year if CURDATE() occurs earlier in the year than birth. The full expression is somewhat ungainly, so an alias (age) is used to make the output column label more meaningful.

A similar query can be used to determine age at death for animals that have died. You determine which animals these are by checking whether the death value is NULL. Then, for those with non-NULL values, compute the difference between the death and birth values: (leave it for your homework)

Finding animals with birthdays in the upcoming month is easy, too. Suppose the current month is April. Then the month value is 4 and you look for animals born in May (month 5) like this:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

A different way to accomplish the same task is to add 1 to get the next month after the current one (after using the modulo function (MOD) to wrap around the month value to 0 if it is currently 12):

mysql> SELECT name, birth FROM pet

-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

3.3.8 Pattern Matching (Wild card)

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed.

SQL pattern matching allows you to use `_' to match any single character and `%' to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Note that you do not use = or <> when you use SQL patterns; use the LIKE or NOT LIKE comparison operators instead.

To find names beginning with `b':

mysql> SELECT * FROM pet WHERE name LIKE "b%";

To find names ending with `fy':

mysql> SELECT * FROM pet WHERE name LIKE "%fy";

To find names containing a `w':

mysql> SELECT * FROM pet WHERE name LIKE "%w%";

To find names containing exactly five characters, use fives instances of the `_' pattern character:

mysql> SELECT * FROM pet WHERE name LIKE "_____";

Some characteristics of extended regular expressions are:

`.' matches any single character.
A character class `[...]' matches any character within the brackets. For example, `[abc]' matches `a', `b', or `c'. To name a range of characters, use a dash. `[a-z]' matches any letter, whereas `[0-9]' matches any digit.
`*' matches zero or more instances of the thing preceding it. For example, `x*' matches any number of `x' characters, `[0-9]*' matches any number of digits, and `.*' matches any number of anything.
A REGEXP pattern match succeed if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)

To anchor a pattern so that it must match the beginning or end of the value being tested, use `^' at the beginning or `$' at the end of the pattern.

Prior to MySQL Version 3.23.4, REGEXP is case-sensitive, and the previous query will return no rows. In this case, to match either lowercase or uppercase `b', use this query instead:

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";

From MySQL 3.23.4 on, if you really want to force a REGEXP comparison to be case-sensitive, use the BINARY keyword to make one of the strings a binary string. This query will match only lowercase `b' at the beginning of a name:

mysql> SELECT * FROM pet WHERE name REGEXP BINARY "^b";

To find names ending with `fy', use `$' to match the end of the name:

mysql> SELECT * FROM pet WHERE name REGEXP "fy$";

To find names containing a `w', use this query:

mysql> SELECT * FROM pet WHERE name REGEXP "w";

To find names containing exactly five characters, use `^' and `$' to match the beginning and end of the name, and five instances of `.' in between:

mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";

You could also write the previous query using the `{n}' ``repeat-n-times'' operator:

mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";

3.3.9 Counting Rows

Databases are often used to answer the question, ``How often does a certain type of data occur in a table?'' For example, you might want to know how many pets you have, or how many pets each owner has, or you might want to perform various kinds of census operations on your animals.

Counting the total number of animals you have is the same question as ``How many rows are in the pet table?'' because there is one record per pet. COUNT(*) counts the number of rows, so the query to count your animals looks like this:

mysql> SELECT COUNT(*) FROM pet;

Earlier, you retrieved the names of the people who owned pets. You can use COUNT() if you want to find out how many pets each owner has:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

Note the use of GROUP BY to group together all records for each owner. Without it, all you get is an error message:

mysql> SELECT owner, COUNT(*) FROM pet;

ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)

with no GROUP columns is illegal if there is no GROUP BY clause

COUNT() and GROUP BY are useful for characterising your data in various ways. The following examples show different ways to perform animal census operations.

Number of animals per species:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

Number of animals per sex:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;

(In this output, NULL indicates that the sex is unknown.)

Number of animals per combination of species and sex:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

3.3.10 Using More Than one Table (Join, Union, inter, division)

Suppose you want to find out the ages at which each pet had its litters. We saw earlier how to calculate ages from two dates. The litter date of the mother is in the event table, but to calculate her age on that date you need her birth date, which is stored in the pet table. This means the query requires both tables:

mysql> SELECT pet.name,

-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,

-> remark

-> FROM pet, event

-> WHERE pet.name = event.name AND type = "litter";

There are several things to note about this query:

The FROM clause lists two tables because the query needs to pull information from both of them.
When combining (joining) information from multiple tables, you need to specify how records in one table can be matched to records in the other. This is easy because they both have a name column. The query uses WHERE clause to match up records in the two tables based on the name values.
Because the name column occurs in both tables, you must be specific about which table you mean when referring to the column. This is done by prepending the table name to the column name.

You need not have two different tables to perform a join. Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table. For example, to find breeding pairs among your pets, you can join the pet table with itself to produce candidate pairs of males and females of like species:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

-> FROM pet AS p1, pet AS p2

-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

Comments: using variable will make the program easy to read.