Linux and UNIX MySQL Introduction with help and examples

Linux and UNIX MySQL Introduction with help and examples

Installing MySQL

There are many different ways to install MySQL. If you are on a system that uses a package manager like APT, RPM, or dpkg, you can install MySQL from a system-specific package. Or, you can install generic binaries, which are not optimized for your system but should work just fine. Or you can compile the source yourself.

At a minimum, you will need to install the MySQL client and the MySQL server. These can run on the same system or different systems.

Installing MySQL Using APT

If you are running Ubuntu, Debian, or another system that uses APT package management, the packages you will want to install are called mysql-client and mysql-server.

We will use apt-get to install the packages, and when installing, we will prefix our commands with sudo to run them with root privileges.

To install the client, we can use the command:

sudo apt-get install mysql-client

...and on the system which will function as the MySQL server, we can install the server software with:

sudo apt-get install mysql-server

...or, if both will be running on the same system, we can install them both with one command:

sudo apt-get install mysql-client mysql-server

Note: When the server package is installed, we will be prompted to set a MySQL Administrator password. Resetting this password is not simple, so once you set it, remember it carefully.

If you want to install a specific version of MySQL, specify the version number in the package name. For instance,

sudo apt-get install mysql-client-5.5

...will install the client for MySQL version 5.5. You can check which versions are available using the apt-cache tool to search for a partial package name, for instance:

apt-cache search mysql-client

...will list all packages with mysql-client in the name.

Once apt-get installs the packages and their dependencies, an initial database will be created, and a configuration file will be created at /etc/mysql/my.cnf. An init script for starting and stopping the MySQL service will be installed at /etc/init.d/mysql. The server will be started automatically after installation.

You can manually start and stop the server using the service command. For example:

sudo service mysql start

...will attempt to start the MySQL server, and:

sudo service mysql stop

...will attempt to stop it.

Installing MySQL with RPM

If you are running Red Hat, Fedora, CentOS, or another system that uses RPM package management, there are three main packages to install: mysql for the client tools,mysql-server for the server and associated tools, and mysql-libs for the MySQLlibraries, which are required if you are going to be connecting to the MySQL server using Perl, Python, or other languages.

To install the RPM packages, use the yum command. For instance,

sudo yum install mysql mysql-server mysql-libs

...will install all three packages on a single system. A sample configuration file will be installed at the location /etc/my.cnf, and an init script to start and stop the server will be installed to /etc/init.d/mysqld. To start the service (called mysqld, for MySQLdaemon), use the service command. For instance:

service mysqld start

...will start the MySQL server daemon, and:

service mysqld stop

...will stop it.

Installing MySQL From A Debian Package

If you want to install the MySQL binaries using a Debian package, you can download a.deb file from this URL. To install the package, use the dpkg utility.

For instance, to install a package named mysql-apt-config_0.3.2-1debian7_all.deb, we would use this command:

sudo dpkg -i mysql-apt-config_0.3.2-1debian7_all.deb

You may also need to install the libaio library if it is not already installed on your system. This provides necessary software for enabling asynchronous Input/Output. The easiest way to install this library is with apt-get, for instance:

sudo apt-get install libaio1

The other details of this type of installation will be identical to installing MySQL using APT (init script name and location, etc).

Installing Generic Binaries From A tar Archive

Installing MySQL generic binaries from a tarball is a bit more complicated, but if you don't use a package manager and you don't want to compile from source, here's how you should go about it.

First, download the tarball. This can be found at this URL. Select the appropriate software for your OS and CPU, and download the tar.gz file. Make sure that you have the appropriate version of C libraries (glibc) installed; for instance, MySQL version5.6.21 requires glibc 2.5. You can determine what version of glibc your OS is using with the following command:

ldd --version

This will give you output resembling the following:

ldd (Debian GLIBC 2.19-13) 2.19

Here, we have glibc version 2.19 installed, so any version of MySQL we download should also use that version. The version of glibc will be listed with the MySQL download.

If you can't find a version of MySQL that uses your current version of glibc, it's recommended you find another way to install MySQL, such the package manager methods listed above. (Manually upgrading glibc is very risky because so many other programs depend on it.)

If you find a version that matches your glibc version, however, here are the steps for configuring your system and installing MySQL from the tarball.

First, you will need to create the mysql group if it does not already exist. You can find out if it exists:

cut -d: -f1 /etc/group | grep mysql

If the mysql group already exists, the above command will print:

mysql

If the command does not give this result, you will need to create the mysql group:

sudo groupadd mysql

Now that the mysql group has been added, you will need to create a user namedmysql. First, check to see if it already exists:

cut -d: -f1 /etc/passwd | grep mysql

If this command does not return:

mysql

...then you will need to create the mysql user, and add it to the mysql group:

sudo useradd -r -g mysql mysql

Next we will unpack the MySQL tar archive. Here, will assume the file is named mysql-5.1.73-linux-x86_64-glibc23.tar.gz, and that it is located in the directory/home/yourusername/downloads.

First, choose where to install MySQL. Here, we will choose the directory /usr/local, which is a standard location. In our installation, making changes to this directory requires root privileges, so we will execute many of the following commands usingsudo.

First, we'll cd to /usr/local:

cd /usr/local

Now, we will unpack the tar archive into this directory, which will create an installation directory containing all the tarball's contents:

tar zxvf /home/yourusername/downloads/mysql-5.1.73-linux-x86_64-glibc23.tar.gz

Next we will create a symbolic link to this directory called mysql, using the lncommand:

ln -s mysql-5.1.73-linux-x86_64-glibc23 mysql

Now we will edit the configuration file, create the core databases, start the MySQL server, and set up passwords for the essential MySQL users.

First, we will cd into our base MySQL directory:

cd /usr/local/mysql

This directory contains several important subdirectories. The key directories for our purposes are bin, which contains the client and server programs, and scripts, which contains the mysql_install_db program, which we will use to initialize the database containing the grant tables which store the server access permissions.

We should add /usr/local/mysql to our PATH environment variable. If you are usingbash (the default command shell on most versions of Linux), you will want to add the line PATH=$PATH:/usr/local/mysql/bin to the .profile file in your home directory, which bash runs every time you log in:

echo 'PATH=$PATH:/usr/local/mysql/bin' >> ~/.profile

Or, if you want to add this to the PATH environment variable for all users on your system, you can use this command:

sudo echo 'PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile

Now let's make sure that the mysql user and group are the owners of everything we just installed:

sudo chown -R mysql /usr/local/mysql/.

sudo chgrp -R mysql /usr/local/mysql/.

Now we will run the mysql_install_db program to set up the initial MySQL grant tables which define how users are able to access the MySQL databases. (This process is performed automatically in other installation procedures, such as using a package manager. Here we will do it manually.)

sudo /usr/local/mysql/scripts/mysql_install_db --user=mysql

Note: If mysql_install_db does not correctly identify the locations of your installation and data directories, you can specify them manually by using this command:

sudo scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql \
                              --datadir=/usr/local/mysql/data

Here, the backslash ("\") indicates that the following line is part of the same command. You can omit this when running the command if you wish.

When mysql_install_db runs successfully, it will create the server's data directory withmysql as the owner. Under the data directory, it creates directories for the database which holds the grant tables and the test database that you can use to test MySQL. It also creates privilege table entries for the root and anonymous MySQL user accounts. These accounts have no password initially (we'll get to that in a moment). These default privileges permit the MySQL root account to do anything, and permit any MySQL user to create or use databases with a name of test or a name starting withtest_.

Now we will start the MySQL server:

sudo bin/mysqld_safe --user=mysql &

At this point, anyone can access MySQL as the MySQL root user, because it has no password associated with it. This is very insecure, obviously, so we should set a password for the MySQL root user. To do this, we will launch the mysql client and issue the appropriate commands. Start the mysql client as the MySQL root user:

mysql -u root

After a brief hello message, you will be presented with the mysql> prompt. At this prompt, enter the following commands, replacing newrootpwd with the password you'd like to set for MySQL root, and thishostname with the hostname of your system:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newrootpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newrootpwd');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('newrootpwd');
mysql> SET PASSWORD FOR 'root'@'thishostname' = PASSWORD('newrootpwd');

You can now exit the mysql client by issuing the exit command:

mysql> exit

Bye

To check that the MySQL server is running, we can use the mysqladmin program using this command, for example:

/usr/local/mysql/bin/mysqladmin version -u root -p

Here, we are specifying that the command should be run as the MySQL root user (-u root) and that it should prompt us for that user's password (-p). (If you omit -p, MySQL will try to authenticate root without using a password, and will fail.) Enter the MySQL root password (which you just set in the above commands), and you should receive output resembling the following:

mysqladmin  Ver 8.42 Distrib 5.1.73, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version        5.1.73-1
Protocol version      10
Connection            Localhost via UNIX socket
UNIX socket           /var/run/mysqld/mysqld.sock
Uptime:               2 min 52 sec

Threads: 1  Questions: 120  Slow queries: 0  Opens: 171  Flush tables: 1  
Open tables: 41  Queries per second avg: 0.001

You should also verify that you can shut down the server, using this command:

sudo /usr/local/bin/mysqladmin -u root -p shutdown

(You will be prompted for the MySQL root user password.)

To start the server again, we can use the same command as above:

sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

Compiling MySQL From Source

Building MySQL from source allows you to fully customize and optimize your MySQL operation. This process is significantly more complicated (and time-consuming), and is usually not required; MySQL binary distributions come in many varieties to cover the vast majority of system hardware and operating system software. However, if you'd like to build from source, these are the steps you should follow.

The recommended compilation tool for building MySQL is cmake. If you don't already have cmake installed, you should download and install it. You can use a package manager to install it, for example, with apt-get:

sudo apt-get install cmake

For other ways to download and install cmake, you can reference the official instructions at http://www.cmake.org/install/.

There are two main source trees to choose from when building MySQL: the standard distribution, and the development tree. The development tree is the bleeding-edge, most-recent (and least-tested!) source code, and we will not cover that installation process here; if you're installing the development source yourself, you should do much more research than can be covered in this document. Here, we'll cover building from the standard distribution.

First, create a mysql user and group, as covered in the previous section:

sudo groupadd mysql

sudo useradd -r -g mysql mysql

Next, download a .tar.gz source archive from this URL suitable for your hardware and operating system, and make sure it uses your installed version of the glibc C libraries, as covered in the previous section. Extract the archive wherever you'd like:

tar zxvf mysql-VERSION.tar.gz

Change into the top-level directory of the distribution:

cd mysql-VERSION.tar.gz

Next, configure the source directory with cmake. If you'd like to use the default configuration, run:

cmake .

Or, if you'd like to use a custom configuration, you can specify various options when running cmake. For instance,

• to configure the source with the same build options used to produce binary distributions for official MySQL releases, run:

  cmake . -DBUILD_CONFIG=mysql_release

• ...or, to configure the distribution for installation to a particular custom location:

  cmake . -DCMAKE_INSTALL_PREFIX=custom-location-directory-name

• ...or, to configure the distribution for full debugging support:

  cmake . -DWITH_DEBUG=1

Next, once the source is configured, build it with make:

make

Then install the distribution with make install:

sudo make install

From here, the remainder of the steps are identical to the post-installation stepscovered in the section above: setting up the configuration file, creating core databases, starting the server, and setting the root password.

Connecting To And Disconnecting From The Server

Once MySQL is installed, the next basic step is connecting to (and disconnecting from) the server. This will require a username; if no other user has been configured, you can log in as root.

The general form of the login command is:

mysql -h hostname -u username -p

The -p means "log in with a password." You can enter the password in plaintext on the command line if you specify it after the -p (for instance, -pmypassword, with no space in between them), but this is very insecure since then your password will be visible in your command history and in the process name itself. It's better simply to use -p and let mysql prompt you to type in the password at a prompt.

If the server is running on the system you're currently logged into (localhost), you can omit the -h hostname option and connect with this simpler command:

mysql -u username -p

So, to log in to the server on localhost as root, the command would be:

mysql -u root -p

If this works, you will be given some introductory information and be presented with the mysql> prompt:

Server version: 5.5.39-1 (Debian)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If instead, you receive a message like this:

ERROR 2002 (HY000): Can't connect to local MySQL server through 
socket '/var/run/mysqld/mysqld.sock' (2)

...this indicates that the MySQL service is not running. As in the examples above, you can start the service by using the service command:

sudo service mysql start

...or, if your installation named the service mysqld (see the installation instructions):

sudo service mysqld start

Alternatively, you can invoke the mysqld_safe command:

sudo mysqld_safe --user=mysql &

Once the server is running, try to log in again, and it should work.

Once you are at the mysql> prompt, you can disconnect and exit the mysql client using the command QUIT:

mysql> QUIT

...and mysql will say:

Bye

...and return you to your shell's command prompt.

Tip: Under Linux (and other Unix-like operating systems), you can also press CTRL-Dat the mysql> prompt to disconnect.

Entering Queries

Once you are connected to the server and at the mysql> prompt, you can start to perform basic queries. Here, "querying" means to ask MySQL for information.

Here's an example of a simple query which asks MySQL for its version number, and the current date:

mysql> SELECT VERSION(), CURRENT_DATE;

Entering this query will return a result resembling the following:

+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.5.39-1  | 2014-11-23   |
+-----------+--------------+
1 row in set (0.00 sec)

...and then return you to another mysql> prompt.

There are a few important things to notice about the above example:

• A MySQL command is usually followed by a semicolon. There are a few exceptions, however, such as the QUIT and USE commands.
• When you issue a query, MySQL responds and then returns you to another mysql>prompt.
• MySQL dispays output in tabular form (in a table, with rows and columns). The first row contains labels for the contents of each column. Each row below that contains the results of the query. Normally, the labels are the names of the columns you requested from database tables. If, as in this example, your query requested the value of expressions, each label is the name of an expression in your query, and the values in the rows below represent the evaluation of that expression.
• MySQL tells you how many rows were returned and how long it took the server to return the result of the query. This gives you an idea about the server's response time, which is a crucial piece of information when evaluating the server's performance. Response time can be affected by factors such as server load and network latency.

When you're making a query, each keyword can be in uppercase, lowercase, or any mixture of the two. For instance, the following queries are all equivalent:

mysql> SELECT VERSION(), CURRENT_DATE;

mysql> select version(), current_date;

mysql> SeLeCt vErSiOn(), current_DATE;

Here is another query which asks for the value of two expressions. This time, we're doing some simple math:

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

+------------------+---------+
| SIN(PI()/4)      | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 |      25 |
+------------------+---------+
1 row in set (0.02 sec)

This example hopefully illustrates the "L" in "MySQL": at its core, MySQL is a language, just like any scripting or programming language.

You can combine more than one statement in a single line, by ending each one with a semicolon. For example:

mysql> SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 5.5.39-1  |
+-----------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2014-11-24 00:04:49 |
+---------------------+
1 row in set (0.00 sec)

Notice how this output differs from our previous examples, where two evaluations were part of a single query statement. There, the output was given in one table with two tables; here, the output is given in two separate tables of one column each.

It's also possible to give a single command on more than one line. MySQL determines where your statement ends by the semicolon, not by the end of the line.

Here's an example of a multi-line statement:

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;

+----------------+--------------+
| USER()         | CURRENT_DATE |
+----------------+--------------+
| root@localhost | 2014-11-24   |
+----------------+--------------+
1 row in set (0.03 sec)

In this example, we typed the following: SELECT (enter) USER() (enter) , (enter)CURRENT_DATE; (enter). Notice how the first three times we pressed enter, MySQL changed the prompt to -> and allowed us to keep typing. This is how it indicates that it has not yet seen a complete statement and is waiting for the rest.

If you're in the midst of typing a statement and you want to abort it, you can end it with \c and press enter. MySQL will ignore everything you've typed so far, and give you a fresh prompt to start over. For example:

mysql> SELECT
    -> USER()
    -> \c
mysql>

Think of \c as the opposite of a semicolon: it indicates the end of a command, but tells MySQL to ignore the command rather than execute it.

We've seen two types of MySQL prompts so far: mysql>, indicating that MySQL is ready for a new statement, and ->, indicating that MySQL is ready for you to continue the statement from the previous line. Here is a handy list of the prompts you will see while using MySQL, and a description of what they indicate:

mysql>	Ready for the next command
->	Waiting for the next line of a multi-line command
'>	Waiting for the next line of a multi-line command, and expecting a matching, closing single-quote (') of a quoted string
">	Waiting for the next line of a multi-line command, and expecting a matching, closing double-quote (") of a quoted string
`>	Waiting for the next line of a multi-line command, and expecting a matching, closing backtick (`) of an identifier
/*>	Waiting for the next line of a multi-line command, and expecting the completion of a comment which began with /*

Multi-line statements will happen if you press enter and forget to use the terminating semicolon. If you are entering a command, and forget the semicolon:

mysql> SELECT()
    ->

...you can just enter a semicolon at the -> prompt to complete the command:

mysql> SELECT USER()
    -> ;

+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

The '> and "> prompts occur during string collection. They indicate that you've started a string with a single- or double-quote on a previous line, and haven't terminated the string with a corresponding quote. For instance, the following command has been typed incorrectly:

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '>

In the above command, the string Smith should have been enclosed in single quotes, but we forgot to type in the second quote. Therefore when we pressed enter, MySQL responded with '> telling us that it's waiting for us to close the string. We need to cancel this command — how do we do that?

We can't just type \c at the '> prompt, because MySQL will think it's part of the string we started with our first single-quote. So, to cancel the command we should type in'\c like this:

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '> '\c
mysql>

This terminates the string and instructs MySQL to cancel the command. We can see by the fresh mysql> prompt that it's ready for a new command.

The '>, ">, and `> prompts are important to understand because they let you know that until you terminate string or identifier that you began with ', ", or `, MySQL will not understand your commands — even the QUIT command.

Creating And Using A Database

Now that you know how to enter commands, let's go over how a database is created and accessed.

A database is, in essence, a table of information. For instance, let's say you have a lot of pets: cats, dogs, hamsters, birds, sugar gliders, ferrets, and the like. You want to store a bunch of information about them in a logical way. You can store this information in a database, and then query the database for that information later. The database server will look up the information based on the criteria you provide, and give you the answer to the question you're asking.

To create our database, we will need to know how to:

• create a database
• create a table in that database
• load data into the table
• retrieve data from the table in various ways
• use multiple tables

First, let's see what databases already exist on our server. To do this, we'll use theSHOW command.

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.09 sec)

information_schema and performance_schema are built-in databases which MySQL uses to monitor itself. mysql is a built-in database which describes user access privileges. These are databases just like any that we'll create ourselves, and when we create databases they'll be listed right along with these.

Let's create a database of our own, and call it pets. We'll use this to store information about our fictional menagerie of domesticated animals.

mysql> CREATE DATABASE pets;

Query OK, 1 row affected (0.00 sec)

Database names are case-sensitive, unlike MySQL commands. So once we've named our database pets, we will always refer to it with lowercase letters; "Pets", "PETS", etc., will not work.

Note: If you try to create a database and receive an error like "ERROR 1044 (42000): Access denied for user 'yourusername'@'localhost' to database 'pets'", it's because the MySQL user you're logged in as does not have privileges to create databases. However, if you're logged in as root, as we are in this example, this will not be an issue.

We've created our database pets, but we can't use it yet; it hasn't been selected for use. We must tell MySQL that it's the database we want to use in our subsequent commands:

mysql> USE pets

Database changed

Notice that there was no semicolon at the end of our USE statement; USE, like QUIT, is one of a handful of special MySQL commands which is not terminated with a semicolon.

Your database only needs to be created once, but it needs to be selected for use with a USE statement each session before it can be accessed. Alternatively, you can select the database on the command line when you launch the MySQL client; just specify the database name after any connection options. For instance:

mysql -h hostname -u username -p pets

This is equivalent to launching mysql and entering USE pets at the mysql> prompt.

At any time when you're running mysql, you can determine which database you're using with this command:

SELECT DATABASE();

Which will return, for example:

+------------+
| DATABASE() |
+------------+
| pets       |
+------------+
1 row in set (0.00 sec)

Creating A Table

At this point, our database is empty (it contains no tables). We can check this with the SHOW TABLES; command:

mysql> SHOW TABLES;

Empty set (0.00 sec)

Before we start populating our database with information, we need to decide what structure should take: what tables it should have, and what columns should appear in each table.

At a minimum, we should probably include the name of each pet. Species is another good candidate, as well as gender. We could also include the color of the pet, which is an immediately noticable identifying quality.

What about the age of each pet? This information is less good, because it changes often and it would require us to update the information whenever it changes. However, we could store each pet's birthday. MySQL provides functions for performing date arithmetic, so it would not be difficult to calculate the date of any of our pets on the fly. Storing birthday rather than age has other advantages, too:

• You can use the database for tasks such as generating reminders for upcoming pet birthdays.
• You can calculate age in relation to dates other than the current date. For example, if one of our pets passes away, we can record the date it died and use their birthday to calculate their age when they passed.

That's a good set of information to start with: name, species, gender, color, birth date, and death date.

Let's create our table. We can call it pet. We accomplish this with the commandCREATE TABLE, specifying the layout of the table in the command:

mysql> CREATE TABLE pet (name VARCHAR(20), species VARCHAR(20), 
    -> gender CHAR(1), color VARCHAR(20), birth DATE, death DATE);

Query OK, 0 rows affected (0.47 sec)

VARCHAR(20) means that those variables will be a string of characters of variable length, up to 20 characters. CHAR(1) means that our gender will be represented by a single character (M or F, probably). DATE is a special variable type which will hold a date. If we want to change any of these types later, we can use MySQL's ALTER TABLE command.

We can verify that our table has been created with the SHOW TABLES; command:

mysql> SHOW TABLES;

+----------------+
| Tables_in_pets |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

To verify that the table was created the way we specified, we can use the DESCRIBEcommand:

mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| gender  | char(1)     | YES  |     | NULL    |       |
| color   | varchar(20) | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.16 sec)

Loading Data Into A Table

Now we need to populate our table with data. The LOAD DATA and INSERTstatements will get this done.

Let's say this is the information we want to plug into our pet table:

name	species	gender	color	birth	death
Steven	cat	M	butterscotccch	2010-02-05
Fluffy	dog	F	golden	2007-06-02
Grendel	sugar glider	M	gray	2012-04-08
Blue	ferret	M	brown	2010-12-25
Alfred	hamster	M	beige	2013-09-04
Spike	parrot	F	blue	2011-11-12
Sandy	goldfish	F	orange	2014-03-22	2014-06-15
Polenta	python	F	green	2012-10-01

Note that the dates are formatted as YYYY-MM-DD; this is how MySQL expects them, so make sure to format your dates this way.

The easiest way to quickly populate this information into our table is to create a text file containing all the information and import it. Let's name the text file pets.txt, and edit it to contain all of this information with each row of data on its own line with the fields delimited by a tab character. Our text file will look like this:

Steven    cat             M    butterscotch    2010-02-05    \N
Fluffy    dog             F    golden          2007-06-02    \N
Grendel   sugar glider    M    gray            2012-04-08    \N
Blue      ferret          M    brown           2010-12-25    \N
Alfred    hamster         M    beige           2013-09-04    \N
Spike     parrot          F    blue            2011-11-12    \N
Sandy     goldfish        F    orange          2014-03-22    2014-06-15
Polenta   python          F    green           2012-10-01    \N

(Here, the information is separated not by spaces, but by a single tab character. So keep in mind that if you recreate this example and view it, it may look slightly different on your screen, since tabs can be displayed in different ways. It's important that you use tabs, not spaces, in between the information fields; if you use spaces, they will be interpreted literally by MySQL and the data will not be imported correctly.)

Note the \N entries; those represent NULL, and tell MySQL that there is no entry (yet) for that information. Note also that tab is merely the default delimiter for imported information; if necessary, you can specify a different delimiter character when usingLOAD DATA.

Save and close the file; now, let's import it into the table using LOAD DATA:

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

Query OK, 8 rows affected (0.18 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

NOTE: If you try to execute this command and receive an error like "ERROR 1148 (42000): The used command is not allowed with this MySQL version", you may be running a (newer) version of MySQL which disables local infile data loads by default. If so, you can fix this by loading mysql with the --enable-local-infile option.

This is merely the most convenient way to load data into your table. If instead you prefer to load the data manually row by row, you can use the IMPORT command, like this:

mysql> INSERT INTO pet
    -> VALUES ('Steven','cat','M','butterscotch','2010-02-05',NULL);

Note that when you add data with INSERT, strings (even single-character strings) are quoted, and NULL is inserted directly, rather than with the string \N.

We can verify that our data has been added to the table by retrieving all data with the command:

mysql> SELECT * FROM pet;

+---------+--------------+--------+--------------+------------+------------+
| name    | species      | gender | color        | birth      | death      |
+---------+--------------+--------+--------------+------------+------------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL       |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL       |
| Grendel | sugar glider | M      | gray         | 2012-04-08 | NULL       |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL       |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL       |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL       |
| Sandy   | goldfish     | F      | orange       | 2014-03-22 | 2014-06-15 |
| Polenta | python       | F      | green        | 2012-10-01 | NULL       |
+---------+--------------+--------+--------------+------------+------------+
8 rows in set (0.04 sec)

Retrieving data from a table is covered in greater detail in the next section.

Retrieving Information From A Table

The SELECT statement is used to pull information from a database table. It takes this general form:

SELECT what-to-select FROM which-table WHERE conditions-to-satisfy

• what-to-select specifies the columns of information you want to pull. This can be a list of column names or an asterisk ("*") meaning "all columns".
• which-table names the table you want to pull data from.
• The WHERE clause is optional. If you include it, conditions-to-satisfy represents one or more conditions the information must satisfy to qualify for retrieval.

Selecting All Data

The simplest way to select data is with SELECT *, as we did above. Let's look at it again:

mysql> SELECT * FROM pet;

+---------+--------------+--------+--------------+------------+------------+
| name    | species      | gender | color        | birth      | death      |
+---------+--------------+--------+--------------+------------+------------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL       |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL       |
| Grendel | sugar glider | M      | gray         | 2012-04-08 | NULL       |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL       |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL       |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL       |
| Sandy   | goldfish     | F      | orange       | 2014-03-22 | 2014-06-15 |
| Polenta | python       | F      | green        | 2012-10-01 | NULL       |
+---------+--------------+--------+--------------+------------+------------+
8 rows in set (0.04 sec)

This is a useful way to look at all the data in your table if (for instance) you want to verify that it has imported correctly. Let's say we just imported our data and we realized that Grendel was born in May, not April, so we need to change Grendel's birth date. Here are the two simplest ways to fix this:

• Edit pets.txt, fix the error, and save and close the file. Back in mysql, empty the table with DELETE:
  
  

  mysql> DELETE FROM pet;

  Query OK, 8 rows affected (0.04 sec)

  Optional: verify that the table is now empty:
  
  

  mysql> SELECT * FROM pet;

  Empty set (0.00 sec)

  ...and import the revised data from pet.txt with LOAD DATA:
  
  

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

• Or, you can fix the erroneous entry with an UPDATE statement:
  
  

  mysql> UPDATE pet SET birth = '2012-05-08' 
      -> WHERE name = 'Grendel';

  Query OK, 0 rows affected (0.07 sec)
  Rows matched: 1  Changed: 0  Warnings: 0

Now let's verify that Grendel's birthday has been updated:

mysql> SELECT * FROM pet;

+---------+--------------+--------+--------------+------------+------------+
| name    | species      | gender | color        | birth      | death      |
+---------+--------------+--------+--------------+------------+------------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL       |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL       |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL       |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL       |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL       |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL       |
| Sandy   | goldfish     | F      | orange       | 2014-03-22 | 2014-06-15 |
| Polenta | python       | F      | green        | 2012-10-01 | NULL       |
+---------+--------------+--------+--------------+------------+------------+
8 rows in set (0.07 sec)

Selecting Particular Rows

Usually you don't want to view an entire table, just a particular entry or entries. For instance, it would be easier to verify the change to Grendel's birthday if we just viewed Grendel's row in the table:

mysql> SELECT * FROM pet WHERE name='Grendel';

+---------+--------------+--------+-------+------------+-------+
| name    | species      | gender | color | birth      | death |
+---------+--------------+--------+-------+------------+-------+
| Grendel | sugar glider | M      | gray  | 2012-05-08 | NULL  |
+---------+--------------+--------+-------+------------+-------+
1 row in set (0.03 sec)

Here, we've selected every item in the row whose name matches the string 'Grendel'. This is an example of string comparison: MySQL has determined which items match our query by comparing our string with the strings in the name column. String comparisons are case-insensitive by default, so the following searches:

mysql> SELECT * FROM pet WHERE name='grendel';

mysql> SELECT * FROM pet WHERE name='GRENDEL';

mysql> SELECT * FROM pet WHERE name='gREnDeL';

...all return the same result:

+---------+--------------+--------+-------+------------+-------+
| name    | species      | gender | color | birth      | death |
+---------+--------------+--------+-------+------------+-------+
| Grendel | sugar glider | M      | gray  | 2012-05-08 | NULL  |
+---------+--------------+--------+-------+------------+-------+
1 row in set (0.00 sec)

We can specify any of our columns as a condition:

mysql> SELECT * FROM pet WHERE gender = 'F';

+---------+----------+--------+--------+------------+------------+
| name    | species  | gender | color  | birth      | death      |
+---------+----------+--------+--------+------------+------------+
| Fluffy  | dog      | F      | golden | 2007-06-02 | NULL       |
| Spike   | parrot   | F      | blue   | 2011-11-12 | NULL       |
| Sandy   | goldfish | F      | orange | 2014-03-22 | 2014-06-15 |
| Polenta | python   | F      | green  | 2012-10-01 | NULL       |
+---------+----------+--------+--------+------------+------------+
4 rows in set (0.00 sec)

...we can also combine conditions, for instance with the boolean AND operator:

mysql> SELECT * FROM pet WHERE gender = 'F' AND species = 'goldfish';

+-------+----------+--------+--------+------------+------------+
| name  | species  | gender | color  | birth      | death      |
+-------+----------+--------+--------+------------+------------+
| Sandy | goldfish | F      | orange | 2014-03-22 | 2014-06-15 |
+-------+----------+--------+--------+------------+------------+
1 row in set (0.01 sec)

...or the OR operator:

mysql> SELECT * FROM pet WHERE name = 'Sandy' OR gender = 'M';

+---------+--------------+--------+--------------+------------+------------+
| name    | species      | gender | color        | birth      | death      |
+---------+--------------+--------+--------------+------------+------------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL       |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL       |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL       |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL       |
| Sandy   | goldfish     | F      | orange       | 2014-03-22 | 2014-06-15 |
+---------+--------------+--------+--------------+------------+------------+
5 rows in set (0.01 sec)

AND and OR can also be mixed. It's a good idea to use parentheses to make clear the order of operations, for instance:

mysql> SELECT * FROM pet WHERE (gender = 'F' AND species = 'dog') 
    -> OR (gender = 'M' AND color = 'gray');

+---------+--------------+--------+--------+------------+-------+
| name    | species      | gender | color  | birth      | death |
+---------+--------------+--------+--------+------------+-------+
| Fluffy  | dog          | F      | golden | 2007-06-02 | NULL  |
| Grendel | sugar glider | M      | gray   | 2012-05-08 | NULL  |
+---------+--------------+--------+--------+------------+-------+
2 rows in set (0.00 sec)

Selecting Particular Columns

Until now we've been using SELECT * to select "all columns" from our tables. We can also select specific columns by specifying them as a comma-separated list afterSELECT:

mysql> SELECT name,color FROM pet;

+---------+--------------+
| name    | color        |
+---------+--------------+
| Steven  | butterscotch |
| Fluffy  | golden       |
| Grendel | gray         |
| Blue    | brown        |
| Alfred  | beige        |
| Spike   | blue         |
| Sandy   | orange       |
| Polenta | green        |
+---------+--------------+
8 rows in set (0.14 sec)

It's okay to use a space after the comma. For example:

mysql> SELECT species, birth FROM pet;

+--------------+------------+
| species      | birth      |
+--------------+------------+
| cat          | 2010-02-05 |
| dog          | 2007-06-02 |
| sugar glider | 2012-05-08 |
| ferret       | 2010-12-25 |
| hamster      | 2013-09-04 |
| parrot       | 2011-11-12 |
| goldfish     | 2014-03-22 |
| python       | 2012-10-01 |
+--------------+------------+
8 rows in set (0.00 sec)

Selecting Unique Data With DISTINCT

Sometimes you will want to suppress repeated instances of a result to your query. For instance, let's select just the gender data of our pets:

SELECT gender FROM pet;

+--------+
| gender |
+--------+
| M      |
| F      |
| M      |
| M      |
| M      |
| F      |
| F      |
| F      |
+--------+
8 rows in set (0.00 sec)

This might be what we want, but we can also use the DISTINCT keyword to suppress any result which is not unique. In other words, regardless of how many times they appear in the table, values will appear only once each in our query response:

mysql> SELECT DISTINCT gender FROM pet;

+--------+
| gender |
+--------+
| M      |
| F      |
+--------+
2 rows in set (0.01 sec)

In our list, each animal is a different species. But if we had many dogs and many cats, for instance, we could quickly generate just a list of unique species types by using theDISTINCT keyword to filter out repeated results.

It should be clear that we can combine SELECT and WHERE clauses to precisely choose which rows and columns we want to select:

mysql> SELECT species, birth FROM pet WHERE gender = 'M';

+--------------+------------+
| species      | birth      |
+--------------+------------+
| cat          | 2010-02-05 |
| sugar glider | 2012-05-08 |
| ferret       | 2010-12-25 |
| hamster      | 2013-09-04 |
+--------------+------------+
4 rows in set (0.00 sec)

Sorting Rows

In all our examples so far, results were listed in no special order. It's usually more useful to have information sorted in some way. To do this, use the ORDER BY clause in your command.

Let's list our pets in order of birthday:

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

+---------+------------+
| name    | birth      |
+---------+------------+
| Fluffy  | 2007-06-02 |
| Steven  | 2010-02-05 |
| Blue    | 2010-12-25 |
| Spike   | 2011-11-12 |
| Grendel | 2012-05-08 |
| Polenta | 2012-10-01 |
| Alfred  | 2013-09-04 |
| Sandy   | 2014-03-22 |
+---------+------------+
8 rows in set (0.11 sec)

The default sort order is ascending, with smaller values listed first.

We can reverse the sort order with the DESC keyword, which sorts items in descending order:

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

+---------+------------+
| name    | birth      |
+---------+------------+
| Sandy   | 2014-03-22 |
| Alfred  | 2013-09-04 |
| Polenta | 2012-10-01 |
| Grendel | 2012-05-08 |
| Spike   | 2011-11-12 |
| Blue    | 2010-12-25 |
| Steven  | 2010-02-05 |
| Fluffy  | 2007-06-02 |
+---------+------------+
8 rows in set (0.00 sec)

You can sort by more than one column, and you can sort columns in different directions. To sort our list of pets by gender and then by name:

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

+---------+--------+------------+
| name    | gender | birth      |
+---------+--------+------------+
| Fluffy  | F      | 2007-06-02 |
| Spike   | F      | 2011-11-12 |
| Polenta | F      | 2012-10-01 |
| Sandy   | F      | 2014-03-22 |
| Steven  | M      | 2010-02-05 |
| Blue    | M      | 2010-12-25 |
| Grendel | M      | 2012-05-08 |
| Alfred  | M      | 2013-09-04 |
+---------+--------+------------+
8 rows in set (0.02 sec)

Here, F comes before M in the alphabet, so our female pets are listed first. After that, results are sorted by birthday, with earlier birthdays first.

Let's flip the order of the genders by specifying DESC after the column name genderin our ORDER BY clause:

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

+---------+--------+------------+
| name    | gender | birth      |
+---------+--------+------------+
| Steven  | M      | 2010-02-05 |
| Blue    | M      | 2010-12-25 |
| Grendel | M      | 2012-05-08 |
| Alfred  | M      | 2013-09-04 |
| Fluffy  | F      | 2007-06-02 |
| Spike   | F      | 2011-11-12 |
| Polenta | F      | 2012-10-01 |
| Sandy   | F      | 2014-03-22 |
+---------+--------+------------+
8 rows in set (0.00 sec)

Notice how the DESC keyword only affects the gender column. Let's add it after birthto sort birthdays in descending order as well:

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

+---------+--------+------------+
| name    | gender | birth      |
+---------+--------+------------+
| Alfred  | M      | 2013-09-04 |
| Grendel | M      | 2012-05-08 |
| Blue    | M      | 2010-12-25 |
| Steven  | M      | 2010-02-05 |
| Sandy   | F      | 2014-03-22 |
| Polenta | F      | 2012-10-01 |
| Spike   | F      | 2011-11-12 |
| Fluffy  | F      | 2007-06-02 |
+---------+--------+------------+
8 rows in set (0.01 sec)

Calculating Dates

In our table, we have stored the birthday of each of our pets. We can use this information to calculate how many years ago they were born. The functions we will use to do this calculation are TIMESTAMPDIFF() and CURDATE():

mysql> SELECT name, birth, CURDATE(), 
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;

+---------+------------+------------+------+
| name    | birth      | CURDATE()  | age  |
+---------+------------+------------+------+
| Steven  | 2010-02-05 | 2014-11-26 |    4 |
| Fluffy  | 2007-06-02 | 2014-11-26 |    7 |
| Grendel | 2012-05-08 | 2014-11-26 |    2 |
| Blue    | 2010-12-25 | 2014-11-26 |    3 |
| Alfred  | 2013-09-04 | 2014-11-26 |    1 |
| Spike   | 2011-11-12 | 2014-11-26 |    3 |
| Sandy   | 2014-03-22 | 2014-11-26 |    0 |
| Polenta | 2012-10-01 | 2014-11-26 |    2 |
+---------+------------+------------+------+
8 rows in set (0.01 sec)

Here, AS age tells MySQL to use the alias age to represent the result ofTIMESTAMPDIFF(YEAR,birth,CURDATE()). We can sort by this alias:

mysql> SELECT name, birth, CURDATE(), 
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet ORDER BY age;

+---------+------------+------------+------+
| name    | birth      | CURDATE()  | age  |
+---------+------------+------------+------+
| Sandy   | 2014-03-22 | 2014-11-26 |    0 |
| Alfred  | 2013-09-04 | 2014-11-26 |    1 |
| Grendel | 2012-05-08 | 2014-11-26 |    2 |
| Polenta | 2012-10-01 | 2014-11-26 |    2 |
| Blue    | 2010-12-25 | 2014-11-26 |    3 |
| Spike   | 2011-11-12 | 2014-11-26 |    3 |
| Steven  | 2010-02-05 | 2014-11-26 |    4 |
| Fluffy  | 2007-06-02 | 2014-11-26 |    7 |
+---------+------------+------------+------+
8 rows in set (0.02 sec)

But — isn't Sandy the goldfish actually dead? Here is a query which only calculates the age of a pet if it has died:

mysql> SELECT name, birth, death, 
    -> TIMESTAMPDIFF(YEAR,birth,death) AS age 
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

+-------+------------+------------+------+
| name  | birth      | death      | age  |
+-------+------------+------------+------+
| Sandy | 2014-03-22 | 2014-06-15 |    0 |
+-------+------------+------------+------+
1 row in set (0.01 sec)

...then again, for Sandy it might be more appropriate to list her age in days, since she didn't even make it to her first birthday:

mysql> SELECT name, birth, death, 
    -> TIMESTAMPDIFF(DAY,birth,death) AS age_in_days 
    -> FROM pet WHERE death IS NOT NULL ORDER BY age_in_days;

+-------+------------+------------+-------------+
| name  | birth      | death      | age_in_days |
+-------+------------+------------+-------------+
| Sandy | 2014-03-22 | 2014-06-15 |          85 |
+-------+------------+------------+-------------+
1 row in set (0.01 sec)

Note: In the last two examples, we compared the entry in the death column with the value NULL using the comparison IS NOT. This is distinct from the <> ("is not equal to") operator used when comparing other values. NULL is a special value in this way; a value either IS or IS NOT NULL.

Now, let's say we want to calculate which of our pets has a birthday next month. To figure this out, year and day of birth are irrelevant; all we want is the month component of the birth date. MySQL provides functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). Let's compare birth andMONTH(birth):

mysql> SELECT birth, MONTH(birth) FROM pet;

+------------+--------------+
| birth      | MONTH(birth) |
+------------+--------------+
| 2010-02-05 |            2 |
| 2007-06-02 |            6 |
| 2012-05-08 |            5 |
| 2010-12-25 |           12 |
| 2013-09-04 |            9 |
| 2011-11-12 |           11 |
| 2014-03-22 |            3 |
| 2012-10-01 |           10 |
+------------+--------------+
8 rows in set (0.05 sec)

Okay. So, we want to query the database to find out which pets have birthdays next month. We have a representation of the month as a number, and we can always just add one to it — but if the month wraps from December to January, we need the number to wrap from 12 to 1.

MySQL provides a function, DATE_ADD() which allows you add an interval to a given date. We can use it to add one month to CURDATE(), then extract the month part with MONTH(). Here's what the query looks like, and its result:

mysql> SELECT name, species, CURDATE(), birth FROM pet WHERE 
    -> MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

+------+---------+------------+------------+
| name | species | CURDATE()  | birth      |
+------+---------+------------+------------+
| Blue | ferret  | 2014-11-26 | 2010-12-25 |
+------+---------+------------+------------+
1 row in set (0.00 sec)

As you can see, it's currently November, and Blue the ferret is our only pet with a birthday next month.

Pattern Matching

MySQL can do pattern matching using SQL-like patterns, or using extended regular expressions similar to the expressions used by vi, grep, and sed. Either will work, but they have slightly different rules.

SQL-style pattern matching

Using SQL-style pattern matching, you can use an underscore ("_") to match any single character, and a percent sign ("%") to match an arbitrary number of characters, including zero characters. SQL patterns are case-insensitive by default. The comparison operators for this style of pattern are LIKE and NOT LIKE.

For example, this pattern matches any name which begins with the letter s:

mysql> SELECT * FROM pet WHERE name LIKE 's%';

+--------+----------+--------+--------------+------------+------------+
| name   | species  | gender | color        | birth      | death      |
+--------+----------+--------+--------------+------------+------------+
| Steven | cat      | M      | butterscotch | 2010-02-05 | NULL       |
| Spike  | parrot   | F      | blue         | 2011-11-12 | NULL       |
| Sandy  | goldfish | F      | orange       | 2014-03-22 | 2014-06-15 |
+--------+----------+--------+--------------+------------+------------+
3 rows in set (0.00 sec)

...and this pattern matches any color that begins with a b or a g:

mysql> SELECT * FROM pet WHERE (color LIKE 'b%') 
    -> OR (color LIKE 'g%');

+---------+--------------+--------+--------------+------------+-------+
| name    | species      | gender | color        | birth      | death |
+---------+--------------+--------+--------------+------------+-------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL  |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL  |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL  |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL  |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL  |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL  |
| Polenta | python       | F      | green        | 2012-10-01 | NULL  |
+---------+--------------+--------+--------------+------------+-------+
7 rows in set (0.00 sec)

...and this pattern matches any name with exactly six characters (we accomplish this by using exactly six underscores in our pattern):

mysql> SELECT * FROM pet WHERE name LIKE '______';

+--------+---------+--------+--------------+------------+-------+
| name   | species | gender | color        | birth      | death |
+--------+---------+--------+--------------+------------+-------+
| Steven | cat     | M      | butterscotch | 2010-02-05 | NULL  |
| Fluffy | dog     | F      | golden       | 2007-06-02 | NULL  |
| Alfred | hamster | M      | beige        | 2013-09-04 | NULL  |
+--------+---------+--------+--------------+------------+-------+
3 rows in set (0.00 sec)

Extended Regular Expression Pattern Matching

MySQL also recognizes extended regular expressions. When comparing against a regular expression you should use the comparison operators REGEXP and NOT REGEXP (or RLIKE and NOT RLIKE, which are synonyms).

In brief, here are some of the major syntactical characteristics of extended regular expressions:

• A dot "." 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 succeeds 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.

You should note that when MySQL attempts to match one of its strings to an extended regular expression, by default it will perform a case-insensitive match.

Here are the same pattern matches we performed above, but using extended regular expressions:

mysql> SELECT * FROM pet WHERE name REGEXP '^s';

+--------+----------+--------+--------------+------------+------------+
| name   | species  | gender | color        | birth      | death      |
+--------+----------+--------+--------------+------------+------------+
| Steven | cat      | M      | butterscotch | 2010-02-05 | NULL       |
| Spike  | parrot   | F      | blue         | 2011-11-12 | NULL       |
| Sandy  | goldfish | F      | orange       | 2014-03-22 | 2014-06-15 |
+--------+----------+--------+--------------+------------+------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM pet WHERE (color REGEXP '^b') 
    -> OR (color REGEXP '^g');

+---------+--------------+--------+--------------+------------+-------+
| name    | species      | gender | color        | birth      | death |
+---------+--------------+--------+--------------+------------+-------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL  |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL  |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL  |
| Blue    | ferret       | M      | brown        | 2010-12-25 | NULL  |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL  |
| Spike   | parrot       | F      | blue         | 2011-11-12 | NULL  |
| Polenta | python       | F      | green        | 2012-10-01 | NULL  |
+---------+--------------+--------+--------------+------------+-------+

mysql> SELECT * FROM pet WHERE name REGEXP '......';

+---------+--------------+--------+--------------+------------+-------+
| name    | species      | gender | color        | birth      | death |
+---------+--------------+--------+--------------+------------+-------+
| Steven  | cat          | M      | butterscotch | 2010-02-05 | NULL  |
| Fluffy  | dog          | F      | golden       | 2007-06-02 | NULL  |
| Grendel | sugar glider | M      | gray         | 2012-05-08 | NULL  |
| Alfred  | hamster      | M      | beige        | 2013-09-04 | NULL  |
| Polenta | python       | F      | green        | 2012-10-01 | NULL  |
+---------+--------------+--------+--------------+------------+-------+
5 rows in set (0.03 sec)

Counting Rows

Databases are obviously used to retrieve specific data ("What are the names of my dogs? What color are they?") ... but it is often useful just to provide a count of data ("How many pets do I have? How many of them are female? How many were born in 2012?").

Assuming that we've listed all our pets in our pet table, counting "How many pets do I have?" is the same as asking "How many rows are in my pet table?". To find the answer to this, we can use the COUNT() function in our query, using the formCOUNT(*) to count every single row.

mysql> SELECT COUNT(*) FROM pet;

+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.15 sec)

If we want to break down this count even further, we can use the GROUP BY clause, specifying how we want to group these results:

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

+--------+----------+
| gender | COUNT(*) |
+--------+----------+
| F      |        4 |
| M      |        4 |
+--------+----------+
2 rows in set (0.00 sec)

You can GROUP BY more than one column to break down the count by more than one criterion. Here we will get a count which groups our pets according to gender and year of birth:

mysql> SELECT gender, YEAR(birth), COUNT(*) FROM pet 
    -> GROUP BY gender, YEAR(birth);

+--------+-------------+----------+
| gender | YEAR(birth) | COUNT(*) |
+--------+-------------+----------+
| F      |        2007 |        1 |
| F      |        2011 |        1 |
| F      |        2012 |        1 |
| F      |        2014 |        1 |
| M      |        2010 |        2 |
| M      |        2012 |        1 |
| M      |        2013 |        1 |
+--------+-------------+----------+
7 rows in set (0.39 sec)

As you can see, the only overlap is male pets born in 2010, of which we have two.

Using More Than One Table

Now let's make another table, which we will use alongside our pet table. Let's create this one to store information about important events in the life of our pets, and let's call the table event.

We'll want to use the following columns: name (which pet was involved in the event?), date (when did the event happen?), type (what type of event was it?), anddetails (a detailed description of the event).

mysql> CREATE TABLE event (name VARCHAR(20), date DATE, 
    -> type VARCHAR(20), details VARCHAR(255));

Query OK, 0 rows affected (5.28 sec)

The easiest way to populate our table with data is to create another text file where each line represents a row, and data is tab-delimited. We'll call our file events.txt:

Steven     2010-06-06    vet         Neutered
Fluffy     2011-08-08    kennel      At kennel while we took a vacation
Spike      2012-02-15    vet         Routine checkup, prescribed prozac
Fluffy     2008-06-02    birthday    Ribeye steak, new chewtoy
Polenta    2013-11-06    other       escaped
Polenta    2013-11-08    other       found
Grendel    2013-01-06    vet         Routine checkup
Steven     2010-09-04    vet         Gave birth to six kittens, two survived
Grendel    2013-04-08    birthday    Tangerine slices and a ginger cookie

Save and close the file. Now we'll import it into our table using LOAD DATA:

mysql> LOAD DATA LOCAL INFILE 'events.txt' INTO TABLE event;

Query OK, 9 rows affected (0.37 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

Now let's verify that our data has imported using SELECT *:

mysql> SELECT * FROM event;

+---------+------------+----------+-----------------------------------------+
| name    | date       | type     | details                                 |
+---------+------------+----------+-----------------------------------------+
| Steven  | 2010-06-06 | vet      | Neutered                                |
| Fluffy  | 2011-08-08 | kennel   | At kennel while we took a vacation      |
| Spike   | 2012-02-15 | vet      | Routine checkup, prescribed prozac      |
| Fluffy  | 2008-06-02 | birthday | Ribeye steak, new chewtoy               |
| Polenta | 2013-11-06 | other    | escaped                                 |
| Polenta | 2013-11-08 | other    | found                                   |
| Grendel | 2013-01-06 | vet      | Routine checkup                         |
| Steven  | 2010-09-04 | vet      | Gave birth to six kittens, two survived |
| Grendel | 2013-04-08 | birthday | Tangerine slices and a ginger cookie    |
+---------+------------+----------+-----------------------------------------+
9 rows in set (0.01 sec)

Now we're going to perform a query which requires both of our tables, pet and event. For instance, let's query all the events for each pet, and the age of each pet when it happened. This requires the birth column from pet, as well as the information inevent. We will use the clause INNER JOIN to unite the results:

mysql> SELECT pet.name, 
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) 
    -> AS age, details FROM pet INNER JOIN event 
    -> ON pet.name = event.name 
    -> WHERE event.name = 'Polenta';

+---------+------+---------+
| name    | age  | details |
+---------+------+---------+
| Polenta |    1 | escaped |
| Polenta |    1 | found   |
+---------+------+---------+
2 rows in set (0.02 sec)

This is definitely the most complicated query we've seen so far. Here are some important things to note about it:

• The FROM clause joins two tables using INNER JOIN because we need to use two tables to complete the query.
• When joining the results from more than one table, we need to specify how records in one table can be matched to records in another. This is easy here because both tables have a name column. We use an ON clause to specify thename columns in both tables, referring to the column with the form "tablename.columnname".
• The actual joining is done by INNER JOIN. This permits rows from either table to appear in the result if and only if both tables meet the conditions specified with the ON clause. In this example, the ON clause specifies that the name column in the pet table must match the name column in the event table. If a name appears in one table but not the other, the row will not appear in the result because the condition in the ON clause fails.
• Because the name column appears in both tables, we must be specific about which table we mean when referring to the column. This is why we prepend the table name before the column name with a period.

Note that we don't need two tables to perform a JOIN. Sometimes it's useful to JOINa table to itself, if we want to compare records in a table to other records in that same table. For example, let's say we want to list every combination of male and female pet in our menagerie. We can do this by JOINing records from within the pet table:

mysql> SELECT p1.name, p1.gender, p2.name, p2.gender 
    -> FROM pet AS p1 INNER JOIN pet AS p2 
    -> ON p1.gender = 'F' AND p2.gender = 'M';

+---------+--------+---------+--------+
| name    | gender | name    | gender |
+---------+--------+---------+--------+
| Fluffy  | F      | Steven  | M      |
| Spike   | F      | Steven  | M      |
| Sandy   | F      | Steven  | M      |
| Polenta | F      | Steven  | M      |
| Fluffy  | F      | Grendel | M      |
| Spike   | F      | Grendel | M      |
| Sandy   | F      | Grendel | M      |
| Polenta | F      | Grendel | M      |
| Fluffy  | F      | Blue    | M      |
| Spike   | F      | Blue    | M      |
| Sandy   | F      | Blue    | M      |
| Polenta | F      | Blue    | M      |
| Fluffy  | F      | Alfred  | M      |
| Spike   | F      | Alfred  | M      |
| Sandy   | F      | Alfred  | M      |
| Polenta | F      | Alfred  | M      |
+---------+--------+---------+--------+
16 rows in set (0.00 sec)

Getting Information About Databases And Tables

To find out which database is currently selected, use the DATABASE() function with the SELECT command:

mysql> SELECT DATABASE();

+------------+
| DATABASE() |
+------------+
| pets       |
+------------+
1 row in set (0.01 sec)

If there is no database currently selected for use, the result is NULL.

To find out what tables the currently-selected database contains, use the SHOW TABLES; command:

mysql> SHOW TABLES;

+----------------+
| Tables_in_pets |
+----------------+
| event          |
| pet            |
+----------------+
2 rows in set (0.00 sec)

The name of the column in this query result is always Tables_in_tablename.

If you want to find out how a table is structured, use the DESCRIBE command:

mysql> DESCRIBE event;

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | YES  |     | NULL    |       |
| date    | date         | YES  |     | NULL    |       |
| type    | varchar(20)  | YES  |     | NULL    |       |
| details | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.19 sec)

Field indicates the name of each column, Type indicates the variable type held by entries in that column, Null indicates whether the column can hold NULL values, Keyindicates whether the column is indexed, and Default indicates the column's default value. Extra displays special information about columns: If a column was created with the AUTO_INCREMENT option, the value will be auto_increment rather than empty.

The DESCRIBE command can be abbreviated as DESC if you like.

Finding The Maximum Value Of A Column

To select the maximum value of a column, use the MAX() function:

mysql> SELECT MAX(birth) FROM pet;

+------------+
| MAX(birth) |
+------------+
| 2014-03-22 |
+------------+
1 row in set (0.02 sec)

Let's look at how many days old our pets are:

mysql> SELECT name, TIMESTAMPDIFF(DAY,birth,CURDATE()) 
    -> AS days_old FROM pet;

+---------+----------+
| name    | days_old |
+---------+----------+
| Steven  |     1757 |
| Fluffy  |     2736 |
| Grendel |      934 |
| Blue    |     1434 |
| Alfred  |      450 |
| Spike   |     1112 |
| Sandy   |      251 |
| Polenta |      788 |
+---------+----------+
8 rows in set (0.00 sec)

Now let's find who's the oldest. We can do this by SELECTing WHERE the result is the same as the result of a SELECT MAX query:

mysql> SELECT name, TIMESTAMPDIFF(day,birth,CURDATE()) AS days_old 
    -> FROM pet WHERE 
    -> TIMESTAMPDIFF(day,birth,CURDATE()) = 
    -> (SELECT MAX(TIMESTAMPDIFF(day,birth,CURDATE())) 
    -> FROM pet);

+--------+----------+
| name   | days_old |
+--------+----------+
| Fluffy |     2736 |
+--------+----------+
1 row in set (0.00 sec)

User-Defined Variables

You can store the result of certain queries into variables and use them later in the same session. For example:

mysql> SELECT @max_days_old:=MAX(TIMESTAMPDIFF(DAY,birth,CURDATE())) 
    -> FROM pet;

+--------------------------------------------------------+
| @max_days_old:=MAX(TIMESTAMPDIFF(DAY,birth,CURDATE())) |
+--------------------------------------------------------+
|                                                   2736 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT name FROM pet WHERE 
    -> TIMESTAMPDIFF(DAY,birth,CURDATE()) = @max_days_old;

+--------+
| name   |
+--------+
| Fluffy |
+--------+
1 row in set (0.03 sec)

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can automatically increment a column value every time a new row is added. For example:

mysql> CREATE TABLE animals (
    -> id MEDIUMINT NOT NULL AUTO_INCREMENT,
    -> name CHAR(30) NOT NULL,
    -> PRIMARY KEY (id));

Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO animals (name) VALUES
    -> ('dog'),('cat'),('penguin'),
    -> ('sugar glider'),('whale'),('ostrich');

Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM animals;

+----+--------------+
| id | name         |
+----+--------------+
|  1 | dog          |
|  2 | cat          |
|  3 | penguin      |
|  4 | sugar glider |
|  5 | whale        |
|  6 | ostrich      |
+----+--------------+
6 rows in set (0.00 sec)

That's it!



                                                We hope you enjoy this.





             If you like our posts please share our posts with your Friend's and invite them to visit us.




                                                           THANK YOU








If you face any Problem then you can Contact us:  


Our Facebook Page:


https://www.facebook.com/flames.technology









 Prepared By: FAHAD WASEEM

SHARE

About Fahad Waseem