A practical guide to Fedora and Red Hat Enterprise Linux, 7th Edition (2014)

Part V: Programming Tools

Chapter 29. The MariaDB SQL Database Management System

In This Chapter

Terminology

Installing a MariaDB Server and Client

Client Options

Setting Up MariaDB

Creating a Database

Adding a User

Examples

Creating a Table

Adding Data

Backing Up a Database

Modifying Data


Objectives

After reading this chapter you should be able to:

Image Explain what SQL is and how it relates to MariaDB

Image Explain what a database, table, row, and column are and the relationship between them

Image Install a MariaDB server and client on the local system

Image Set up MariaDB, including a ~/.my.cnf file for a user

Image Create a database and add a user

Image Add data to and retrieve data from the database

Image Modify data in the database

Image Add a second table and write joins to retrieve data from both tables

History

MySQL

MySQL (My Structured Query Language) is an implementation of SQL. It is the world’s most popular open-source RDBMS (relational database management system). It is extremely fast and is used by some of the most frequently visited Web sites on the Internet, including Google, Facebook, Twitter, Yahoo, YouTube, and Wikipedia. Recently, however, some of these companies have moved to MariaDB, which is explained next. Fedora/RHEL has replaced MySQL in its repositories with MariaDB, and Wikipedia has also converted.

Michael “Monty” Widenius and David Axmark started development of MySQL in 1994. In 2008 Sun Microsystems bought MySQL.

MariaDB

In 2009, not happy with the development process of MySQL under Sun Microsystems, Mr. Widenius left Sun and founded a company named The Monty Program to work on a fork of MySQL named MariaDB. In 2010, when Oracle Corporation acquired Sun Microsystems, most of the MySQL developers left Sun to join the MySQL forks MariaDB and Drizzle (www.drizzle.org).

Compatibility

Today, MariaDB is a community-developed fork of MySQL that is dedicated to FOSS software (page 7) released under the GNU GPL (page 5). Currently, MariaDB is a drop-in replacement for MySQL and uses the same commands as MySQL; only the package names differ (but seemariadb.com/kb/en/mariadb-vs-mysql-compatibility). However, MariaDB is planning to introduce significant changes in the next version; at that time there might no longer be compatibility at the feature level. However, one would expect MariaDB to maintain protocol compatibility with MySQL into the future. Visit mariadb.com/blog/explanation-mariadb-100 for more information.


Tip: MariaDB or MySQL?

Because MariaDB is a fork of MySQL, almost all of the code is the same. The places where the code differs do not affect the examples or descriptions in this chapter. The examples in this chapter were tested against MariaDB. If you want to work with MySQL, install thecommunity-mysql package. Unless you explicitly install the community-mysql package, Fedora installs MariaDB. For more information visit fedoraproject.org/wiki/Features/ReplaceMySQLwithMariaDB.


Interfaces

Many programming languages provide interfaces and bindings to MariaDB, including C, Python, PHP, and Perl. In addition, you can access a MariaDB database using the industry-standard ODBC (Open Database Connectivity) API. You can also call MariaDB from a shell script or the command line. MariaDB is a core component of the popular LAMP (Linux, Apache, MariaDB, PHP/Perl/Python) open-source enterprise software stack.

Image Notes

MariaDB has a separate set of users from Linux: Users who have MariaDB accounts might not have Linux accounts, and vice versa. As installed, the name of the MariaDB administrator is root. Because the MariaDB root user is not the same as the Linux root user, it can (and should) have a different password.

MariaDB does not automatically create a database when you create a MariaDB user; users and databases are not rigidly bound.

SQL is free form with respect to whitespace and NEWLINEs.

Terminology

This section briefly describes some basic terms used when working with a relational database. See Figure 29-1.

Image

Figure 29-1 A few rows from the people table in the maxdb database

Database—A structured set of persistent data comprising one or more tables.

Row—An ordered set of columns in a table. Also record or tuple.

Column—A set of one type of values, one per row in a table. Certain columns might be designated as keys. Keys are indexed to speed up access to specific values in the column. Also field or attribute.

Join—Two (or more) rows, each from a different table, that are tied together by means of the relationships between values in two (or more) columns. For example, two rows, each from a different table, can be joined based on equal values in two columns.

RDBMS—Relational database management system. A database based on the relational model developed by E. F. Codd comprising tables of data. Codd used the term relations for what SQL calls tables, thus the name of the database.

SQL—Structured Query Language. An industry-standard language for creating, updating, and querying relational databases. SQL is not part of the relational model but is often associated with it.

MariaDB/MySQL—Software brands or implementations of SQL.

Table—A collection of rows in a relational database. Also relation.


Tip: MariaDB/MySQL is an implementation of SQL

This chapter is about SQL and MariaDB. It explains how to set up a MariaDB server and client. It also shows examples of how to run SQL queries under MariaDB.


Syntax and Conventions

An SQL program comprises one or more statements, each terminated by a semicolon (;) or \g. Although keywords in statements are not case sensitive, this book shows keywords in uppercase letters for clarity. Database and table names are case sensitive; column names are not.

The following example shows a multiline SQL statement (query) that includes both the primary interpreter prompt (MariaDB [maxdb]>maxdb is the name of the selected database) and the secondary interpreter prompt (–>). SELECT, FROM, and WHERE are keywords. This statement displays the value of the name column from the table named people in rows where the value in the store column is 4.

MariaDB [maxdb]> SELECT          name
    ->                   FROM    people
    ->                   WHERE   store = 4;

Comments

You can specify a comment in one of three ways in an SQL program. These techniques work in SQL command files and when you are working with MariaDB interactively.

1. A hash sign (#) marks the beginning of a comment that continues to the end of the line (the NEWLINE character).

# The following line holds an SQL statement and a comment
USE maxdb;    # Use the maxdb database

2. A double hyphen (––) marks the beginning of a comment that continues to the end of the line (the NEWLINE character). The double hyphen must be followed by whitespace (one or more SPACEs and/or TABs).

-- The following line holds an SQL statement and a comment
USE maxdb;    -- Use the maxdb database

3. As in the C programming language, you can surround a (multiline) comment with /* and */.

/* The line following this multiline
comment holds an SQL statement
and a comment */
USE maxdb;    /* Use the maxdb database */

Data Types

When you create a table, you specify the name and data type of each column in the table. Each data type is designed to hold a certain kind of data. For example, data type CHAR holds strings of characters while DATE holds dates. The examples in this section use the following data types, a small sampling of those available under MariaDB.

• CHAR(n)—Stores a string of up to n characters where 0 <= n <= 255. You must enclose strings within single or double quotation marks. When you store a string in a type CHAR column, MariaDB right-pads the string with SPACEs to make an n-character string. When you retrieve a type CHAR string, MariaDB strips the trailing SPACEs. Occupies n bytes (CHAR has a fixed length).

• VARCHAR(n)—Stores a string of up to n characters where 0 <= n <= 65,535. You must enclose strings within single or double quotation marks. MariaDB does not pad strings stored in type VARCHAR columns. With a string length of L, occupies L + 1 bytes if 0 <= L <= 255 and L + 2 bytes if L > 255. (VARCHAR has a variable length.) See page 1130 for an example.


Tip: VARCHARs might slow large queries

MariaDB converts VARCHAR columns to CHAR for operations that generate temporary tables (e.g., sorting, including ORDER BY and GROUP BY). Thus declaring large VARCHAR values [e.g., VARCHAR(255)] for columns that require frequent sorting will result in very large temporary tables that tend to slow queries.


• INTEGER—Stores a 4-byte integer.

• DATE—Stores a date. MariaDB sets a DATE variable to 0 if you specify an illegal value. Occupies 3 bytes.

• BOOL—A synonym for TINYINT. A value of 0 evaluates as false and 1–255 evaluate as true. Occupies 1 byte. When you specify data type BOOL, MariaDB changes it to a TINYINT.

More Information

Home pages: mariadb.org, www.mysql.com

Documentation: mariadb.com/kb/en/MariaDBdev.mysql.com/doc

Introduction: mariadb.com/kb/en/getting-starteddev.mysql.com/tech-resources/articles/mysql_intro.html

Fedora moves to MariaDB: fedoraproject.org/wiki/Features/ReplaceMySQLwithMariaDB

MariaDB/MySQL compatibility: mariadb.com/kb/en/mariadb-vs-mysql-compatibility

MariaDB next version: mariadb.com/blog/explanation-mariadb-100

Command syntax: dev.mysql.com/doc/refman/5.6/en/sql-syntax.html

Data types: dev.mysql.com/doc/refman/5.6/en/data-types.html

Joins: www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html, mysqljoin.com

ODBC: dev.mysql.com/downloads/connector/odbc

Security: blog.mariadb.org/tag/securitywww.kitebird.com/articles/ins-sec.html (dated)

Backing up databases: webcheatsheet.com/SQL/mysql_backup_restore.phpwww.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump

Installing a MariaDB Server and Client

This section briefly covers installing the MariaDB client and server packages and starting the server running.


Tip: You must remove anonymous MariaDB users

When you install the MariaDB server, the MariaDB database is set up to allow anonymous users to log in and use MariaDB. The examples in this chapter will not work unless you remove these users from the MariaDB database. See “Removing Anonymous Users” on page1119.


Install the following packages:

• mariadb

• mariadb-server

Working as a privileged user under Fedora, give the following commands to always start the mariadb service when the system enters multiuser mode and to start the mariadb service immediately:

systemctl enable mariadb.service
systemctl start mariadb.service

Client Options

This section describes some of the options you can use on the MariaDB client command line. The options preceded by a single hyphen and those preceded by a double hyphen are equivalent.

––disable-reconnect

Does not attempt to connect to the server again if the connection is dropped. See ––reconnect.

––host=hostname

–h hostname

Specifies the address of the MariaDB server as hostname. Without this option MariaDB connects to a local unix socket (/var/lib/mysql/mysql.sock).

––password[=passwd]

–p[passwd]

Specifies the MariaDB password as passwd. For improved security, do not specify the password on the command line. With this option and no password, MariaDB prompts for a password. By default MariaDB does not use a password. The short form of this option does not accept a SPACE between the –p and passwd.

––reconnect

Attempts to connect to the server again if the connection is dropped (default). Disable this behavior using ––disable-reconnect.

––skip-column-names

Does not display column names in results.

––user=usr

–u usr

Specifies the MariaDB user as usr. Without this option, usr defaults to the username of the user running the MariaDB command.

––verbose

–v

Increases the amount of information MariaDB displays. Use this option multiple times to increase verbosity. This option displays MariaDB statements as they are executed when running from a command file.

Setting Up MariaDB

You must remove the anonymous users from the MariaDB database before MariaDB will allow you to run commands working as yourself (or as Max if you follow the examples in this section). You can work as the MariaDB user named root without removing the anonymous users. To run commands as the MariaDB user named root in cases where this user does not have a password, you can either not specify a password or else press RETURN when prompted for a password. In a production environment, you can improve security of a MariaDB database by giving a password to the MariaDB user named root.

Of the following three steps, only the second, “Removing Anonymous Users,” is required. The first step, “Assigning a Password to the MariaDB User Named root,” makes a database more secure and is a good idea in a production environment.

You can use the third step, “Running the Secure Installation Script,” in place of the first two steps. This script removes anonymous users, assigns a password to the MariaDB user named root, and takes other actions to make MariaDB more secure.

Assigning a Password to the MariaDB User Named root

The following command, run as a nonprivileged user, assigns mysql-password as the password for the MariaDB user named root.

mysqladmin -u root password 'mysql-password'

Removing Anonymous Users

The following commands, which you can run as a nonprivileged user, remove the anonymous users from the MariaDB database, yielding a more secure system. The –u option causes MariaDB to run as the MariaDB user named root. The –p option causes MariaDB to prompt for the password (for the MariaDB user named root). In response to the prompt, enter the password you assigned to the MariaDB user named root.

mysql -u root -p
Enter password:
...
MariaDB [(none)]> DELETE FROM     mysql.user
    ->                   WHERE    user='';
Query OK, 2 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye

Running the Secure Installation Script

In place of the two preceding steps, you can run the following command, which allows you to assign a password to the MariaDB user named root, removes anonymous users, disallows remote MariaDB root logins, and removes the test database that ships with MariaDB.

mysql_secure_installation

~/.my.cnf: Configures a MariaDB Client

You can use the ~/.my.cnf file to set MariaDB client options. The following example shows Max’s .my.cnf file. The [mysql] specifies the MariaDB group. The user line is useful if your Linux and MariaDB usernames are different. Setting user to max when Max’s Linux username is max is not necessary. The password line sets Max’s MariaDB password to mpasswd. With this setup, Max does not have to use –p on the command line; MariaDB logs him in automatically. The database line specifies the name of the MariaDB database you want to work with so you do not need a USE statement at the beginning of a MariaDB program or session. Do not add the database line to this file until after you create the database (next) or you will not be able to use MariaDB.

cat /home/max/.my.cnf
[mysql]
user="max"
password="mpasswd"
database="maxdb"

Because this file can hold a password and other sensitive information, setting permissions on this file so the user in whose home directory the file resides owns the file and only the owner can read the file makes the MariaDB data more secure.

~/.mysql_history: Stores Your MariaDB History

MariaDB writes each of the statements it executes to a file named ~/.mysql_history. Because MariaDB can write passwords to this file, setting permissions on this file so the user in whose home directory the file resides owns the file and only the owner can read the file makes the MariaDB data more secure. If you do not want to store your MariaDB history, delete this file and re-create it as a symbolic link to /dev/null.

rm ~/.mysql_history
ln -s /dev/null ~/.mysql_history

Creating a Database

If the MariaDB username you add is the same as your Linux username, you will not have to specify a username on the MariaDB command line. In the following example, Max works as the MariaDB user named root (–u root). The –p option causes MariaDB to prompt for the password. In response to the Enter password prompt, Max supplies the password for the MariaDB user named root. If the MariaDB user named root does not have a password, press RETURN in response to the prompt.

CREATE DATABASE, SHOW DATABASES

Max uses a CREATE DATABASE statement to create a database named maxdb and a SHOW DATABASES statement to display the names of all databases. This command shows the name of the maxdb database and several system databases.

mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.31-MariaDB MariaDB Server
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

MariaDB [(none)]> CREATE DATABASE maxdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| maxdb              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

If you try to create a database that already exists, MariaDB displays an error message.

MariaDB [(none)]> CREATE DATABASE maxdb;
ERROR 1007 (HY000): Can't create database 'maxdb'; database exists

USE

You must tell MariaDB the name of the database you want to work with. If you do not give MariaDB this information, you must prefix the names of tables with the name of the database. For example, you would need to specify the people table in the maxdb database as maxdb.people. When you specify the maxdb database in the ~/.my.cnf file (page 1120) or with a USE statement, you can refer to the same table as people. In the following example, Max uses a USE statement to specify maxdb as the database he is working with. Once you specify a database, MariaDB includes its name in the prompt.

mysql
MariaDB [(none)]> USE maxdb;
Database changed
MariaDB [maxdb]>

Adding a User

Before starting to work with the database, create a user so you do not have to work as the MariaDB user named root. You must work as the MariaDB user named root to create a MariaDB user.

Continuing with his previous MariaDB session, Max adds the MariaDB user named max with a password of mpasswd. The GRANT statement gives Max the permissions he needs to work (as the user named max) with the maxdb database. When using the MariaDB interpreter, Query OKindicates that the preceding statement was syntactically correct. Within an SQL statement, you must surround all character and date strings with quotation marks.

MariaDB [(none)]> GRANT           ALL PRIVILEGES
    ->                    ON      maxdb.* to 'max'
    ->                    IDENTIFIED BY 'mpasswd'
    ->                    WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT          user, password
    ->                    FROM    mysql.user;
+------+-------------------------------------------+
| user | password                                  |
+------+-------------------------------------------+
| root | *96D4C5B9348F896B0B593EA4DC1B653156799FDD |
...
| max  | *34432555DD6C778E7CB4A0EE4551425CE3AC0E16 |
+------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye

In the preceding example, after setting up the new user, Max uses a SELECT statement to query the user table of the mysql database and display the user and password columns. The password column displays encrypted passwords. Two users now exist: root and max. Max gives the command quit to exit from the MariaDB interpreter.

Working as the MariaDB user max, Max can now set up a simple database to keep track of people. He does not need to use the –u option on the command line because his Linux username and his MariaDB username are the same.

Image Examples

This section follows Max as he works with MariaDB. You must follow the steps described under “Setting Up MariaDB” on page 1119 to remove anonymous users and under “Adding a User” on page 1122 to create the maxdb database and add the MariaDB user named max. If you do not follow these steps, you will not be able to use MariaDB as described in this section.

Logging In

You must log in to MariaDB before you can use it interactively.

You can specify a MariaDB username in the ~/.my.cnf file (page 1120) or by using the ––user (–u) option on the command line. If you do not specify a user in one of these ways, MariaDB assumes your MariaDB username is the same as your Linux username. The examples in this section assume Max is logging in to MariaDB as max (and has not specified a username in the ~/.my.cnf file).

If a MariaDB account has a password, you must specify that password to log in. You can specify a password in the ~/.my.cnf file or by using the ––password (–p) option on the command line. Without specifying his password in his ~/.my.cnf file, Max would log in on the MariaDB interactive interpreter using the following command:

mysql -p
Enter password: mpasswd
...

With his password specified in the ~/.my.cnf file as shown on page 1120, Max can log in without the –p option:

mysql
...

Creating a Table

This section assumes Max has specified his password in his ~/.my.cnf file.

CREATE TABLE

Before you can work with a database, you must create a table to hold data. To get started, Max uses the following CREATE TABLE statement to create a table named people in the maxdb database. This table has four columns. The USE statement specifies the name of the database.

mysql
MariaDB [(none)]> USE maxdb;
Database changed

MariaDB [maxdb]> CREATE TABLE    people (
    ->                   name    CHAR(10),
    ->                   hired   DATE,
    ->                   store   INTEGER,
    ->                   hourly  BOOL
    ->                   );
Query OK, 0 rows affected (0.01 sec)

SQL is free form with respect to whitespace and NEWLINEs. Max could have written the preceding statement as follows:

MariaDB [maxdb]> CREATE TABLE people (name CHAR(10),
    ->           hired DATE, store INTEGER, hourly BOOL);

SHOW TABLES

After creating the table, Max uses a SHOW TABLES statement to display a list of tables in the maxdb database.

MariaDB [maxdb]> SHOW TABLES;
+-----------------+
| Tables_in_maxdb |
+-----------------+
| people          |
+-----------------+
1 row in set (0.00 sec)

DESCRIBE

Next, Max uses a DESCRIBE statement to display a description of the table named people.

MariaDB [maxdb]> DESCRIBE people;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| name   | char(10)   | YES  |     | NULL    |       |
| hired  | date       | YES  |     | NULL    |       |
| store  | int(11)    | YES  |     | NULL    |       |
| hourly | tinyint(1) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Figure 29-1 on page 1115 shows part of the people table after data has been entered in it.

ALTER TABLE

Max decides that the hourly column should default to true. He uses an ALTER TABLE statement to modify the table so he does not have to delete the table and create it again. He then checks his work using a DESCRIBE statement; the hourly column now defaults to 1, which evaluates astrue.

MariaDB [maxdb]> ALTER TABLE     people
    ->                   MODIFY  hourly BOOL DEFAULT TRUE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [maxdb]> DESCRIBE people;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| name   | char(10)   | YES  |     | NULL    |       |
| hired  | date       | YES  |     | NULL    |       |
| store  | int(11)    | YES  |     | NULL    |       |
| hourly | tinyint(1) | YES  |     | 1       |       |
+--------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Adding Data

This section describes several ways to enter information into a database.

Image INSERT INTO

Next, Max uses an INSERT INTO statement to try to add a row of data to the people table. Following the first command, MariaDB displays an error saying it does not know about a column named topsy; Max forgot to put quotation marks around the string topsy so MariaDB parsed topsy as the name of a column. Max includes the quotation marks in the second command.

MariaDB [maxdb]> INSERT INTO     people
    ->                   VALUES ( topsy, '2012/11/01', 4, FALSE);
ERROR 1054 (42S22): Unknown column 'topsy' in 'field list'

MariaDB [maxdb]> INSERT INTO     people
    ->                   VALUES ( 'topsy', '2012/11/01', 4, FALSE);
Query OK, 1 row affected (0.00 sec)

The preceding INSERT INTO statement did not specify which columns the values were to be inserted into; it specified values for all four columns. The next INSERT INTO statement specifies values to be added to the name and store columns; MariaDB sets the other columns in the new rows to their default values.

MariaDB [maxdb]> INSERT INTO     people (name, store)
    ->                   VALUES ( 'percy', 2 ),
    ->                          ( 'bailey', 2 );
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [maxdb]> QUIT
Bye

LOAD DATA LOCAL INFILE

The preceding examples showed how to work with MariaDB interactively. The next example shows how to run MariaDB with statements in a command file. At the beginning of the preceding interactive session, Max gave the command USE maxdb so MariaDB knew which database the following commands worked with. When you specify commands in a file, you must tell MariaDB which database the commands work with; each file of commands must start with a USE statement or MariaDB will return an error. Alternately, you can specify the name of the database in your~/.my.cnf file (page 1120).

The following example adds three rows to the people table from a text file named addfile. In addfile, each line holds data for a single row, with a single TAB separating each column from the next. The \N specifies a null value. The file is not terminated with a NEWLINE; if it were, MariaDB would insert a row of NULLs. Unlike interactive mode, when you run MariaDB from a command file, it does not display a message if everything worked properly. You can use the –v (verbose) option to cause MariaDB to display information about the commands it is executing. Multiple –voptions display more information.

To run SQL commands from a file, enter the command mysql and redirect input to come from the command file. The following mysql command uses two –v options so MariaDB displays statements as it executes them and displays the results of statements after it executes them; input is redirected to come from load.

cat load
USE maxdb;

LOAD DATA LOCAL INFILE '/home/max/addfile'
        INTO TABLE people;

cat addfile
max     \N      4       0
zach    09-03-24        6       0
sam     2008-01-28      6       1

mysql -vv < load
--------------
LOAD DATA LOCAL INFILE '/home/max/addfile'
        INTO TABLE people
--------------

Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Bye

Image Retrieving Data

SELECT

The SELECT statement queries the database and displays the data the query returns. Within a SELECT statement, SQL interprets an asterisk (*) to mean all columns in a table. The following interactive query displays all columns of all rows of the people table in the maxdb database.

mysql
MariaDB [(none)]> USE maxdb;
MariaDB [maxdb]> SELECT          *
    ->                   FROM    people;
+--------+------------+-------+--------+
| name   | hired      | store | hourly |
+--------+------------+-------+--------+
| topsy  | 2012-11-01 |     4 |      0 |
| percy  | NULL       |     2 |      1 |
| bailey | NULL       |     2 |      1 |
| max    | NULL       |     4 |      0 |
| zach   | 2009-03-24 |     6 |      0 |
| sam    | 2008-01-28 |     6 |      1 |
+--------+------------+-------+--------+
6 rows in set (0.00 sec)

Image ORDER BY

When you run queries from a command file, MariaDB does not line up in columns the data it outputs, but rather simply separates each column from the next using a TAB. This minimal formatting allows you to redirect and format the output as you please. Typically, if you want to redirect the data, you will not specify any –v options so MariaDB does not display any messages. The next command includes an ORDER BY clause in the SELECT statement to sort the output. It uses the sel2 command file and sends the output through tail to strip the header.

cat sel2
use maxdb;
SELECT          *
        FROM    people
        ORDER BY name;

mysql < sel2 | tail -n +2
bailey  NULL    2       1
max     NULL    4       0
percy   NULL    2       1
sam     2008-01-28      6       1
topsy   2012-11-01      4       0
zach    2009-03-24      6       0

Image WHERE

The next example shows a SELECT statement with a WHERE clause. A WHERE clause causes SELECT to return only those rows that match specified criteria. In this example, the WHERE clause causes SELECT to return only rows in which the value in the store column is 4. In addition, this SELECT statement returns a single column (name). The result is a list of the names of the people who work in store number 4.

MariaDB [maxdb]> SELECT          name
    ->                   FROM    people
    ->                   WHERE   store = 4;
+-------+
| name  |
+-------+
| topsy |
| max   |
+-------+
2 rows in set (0.00 sec)

The next example shows the use of a relational operator in a WHERE clause. Here the SELECT statement returns the names of people who work in stores with numbers greater than 2.

MariaDB [maxdb]> SELECT          name
    ->                   FROM    people
    ->                   WHERE   store > 2;
+-------+
| name  |
+-------+
| topsy |
| max   |
| zach  |
| sam   |
+-------+
4 rows in set (0.00 sec)

LIKE

You can also use the LIKE operator in a WHERE clause. LIKE causes SELECT to return rows in which a column contains a specified string. Within the string that follows LIKE, a percent sign (%) matches any string of zero or more characters while an underscore (_) matches any single character. The following query returns rows in which the name column contains an m.

MariaDB [maxdb]> SELECT          name,
    ->                           store
    ->                   FROM    people
    ->                   WHERE   name LIKE '%m%';
+------+-------+
| name | store |
+------+-------+
| max  |     4 |
| sam  |     6 |
+------+-------+
2 rows in set (0.00 sec)

Backing Up a Database

mysqldump

The mysqldump utility can back up and restore a database. Backing up a database generates a file of SQL statements that create the tables and load the data. You can use this file to restore the database from scratch. The next example shows how Max can back up the maxdb database to a file named maxdb.bkup.sql.

mysqldump -u max -p maxdb > maxdb.bkup.sql
Enter password:

Be careful: The following restore procedure will overwrite an existing database. Before you can restore a database, you must create the database as explained on page 1121. After creating the maxdb database, Max runs MariaDB with input coming from the file that mysqldump created. After giving the following command, the maxdb database is in the same state it was in when Max backed it up.

mysql -u max -p maxdb < maxdb.bkup.sql
Enter password:

Modifying Data

Image DELETE FROM

The DELETE FROM statement removes one or more rows from a table. The next example deletes the rows from the people table where the name column holds bailey or percy.

MariaDB [maxdb]> DELETE FROM     people
    ->                   WHERE   name='bailey'
    ->                           OR name='percy';
Query OK, 2 rows affected (0.00 sec)

Image UPDATE

The UPDATE statement changes data in a table. The following example sets the hourly column to TRUE (1) in the rows where the name column contains sam or topsy. The MariaDB messages show the query matched two rows (sam and topsy) but changed only one row. It changed hourlyin the row with topsy; in the row with samhourly was already set to TRUE.

MariaDB [maxdb]> UPDATE          people
    ->                   SET     hourly = TRUE
    ->                   WHERE   name = 'sam' OR
    ->                           name = 'topsy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

CURDATE()

The CURDATE() function returns today’s date. The next example sets the hired column to today’s date in the row where name contains max.

MariaDB [maxdb]> UPDATE          people
    ->                   SET     hired = CURDATE()
    ->                   WHERE   name = 'max';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The next query shows the results of the preceding DELETE FROM and UPDATE statements.

MariaDB [maxdb]> select * from people;
+-------+------------+-------+--------+
| name  | hired      | store | hourly |
+-------+------------+-------+--------+
| topsy | 2012-11-01 |     4 |      1 |
| max   | 2012-02-12 |     4 |      0 |
| zach  | 2009-03-24 |     6 |      0 |
| sam   | 2008-01-28 |     6 |      1 |
+-------+------------+-------+--------+
4 rows in set (0.00 sec)

Creating a Second Table

Next, the setup.stores SQL command file creates, populates, and displays the stores table in the maxdb database.

mysql -vv < setup.stores
--------------
CREATE TABLE    stores (
                name VARCHAR(20),
                number INTEGER,
                city VARCHAR(20)
                )
--------------

Query OK, 0 rows affected

--------------
INSERT INTO     stores
        VALUES  ( 'headquarters', 4, 'new york' ),
                ( 'midwest', 5, 'chicago' ),
                ( 'west coast', 6, 'san francisco' )
--------------

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

--------------
SELECT          *
        FROM    stores
--------------

name    number  city
headquarters    4       new york
midwest 5       chicago
west coast      6       san francisco
3 rows in set

VARCHAR

The stores table has columns named namenumber, and city. The data type for the name and city columns is VARCHAR. A VARCHAR column stores a variable-length string without the padding required by a CHAR column. In this example, the name and city columns can store up to 20 characters each.

The size of a VARCHAR is the sum of the length prefix plus the number of characters in the string being stored. The length prefix for a column declared to hold fewer than 255 characters is 1 byte; larger columns have a 2-byte length prefix. In contrast, a CHAR column always occupies the number of characters it was declared to be.

The number in parentheses in a VARCHAR declaration specifies the maximum number of characters any row in the column can hold. In the example, the city column is declared to be VARCHAR(20) so a row in that column cannot hold more than 20 characters. The row with new york in thecity column takes up 9 bytes of storage (8 + 1). If the column had been declared to be CHAR(20), each row would occupy 20 bytes, regardless of the length of the string it held.

Image Joins

A join operation combines rows from/across two or more related tables in a database according to some relationship between these tables. Take the maxdb database, for example: The people table has columns that hold information about employee name, hire date, the store where the employee works, and whether the employee is hourly (as opposed to salaried); the stores table has columns that hold information about the store name, number, and city it is located in. With this setup, you cannot determine which city an employee works in by querying either the people orstores table alone; you must join the tables to get this information.

You join tables using a SELECT statement that specifies a column in each of the tables and the relationship between those columns. The query that would satisfy the example specifies the store column in the people table and the number column in the stores table; the relationship between those columns is equality because both of those columns hold the store number. When you equate like values in these columns, you can display stores information for each name in the people table (or people information for each [store] number in the stores table).

The following query selects rows from the people and stores tables and joins the rows where the value of store in the people table equals the value of number in the stores table (store = number). This query uses an implicit join; it does not use the JOIN keyword. The examples following this one use JOIN. Using this keyword makes no difference in the results of the query, but you must use JOIN in certain types of queries.

MariaDB [maxdb]> SELECT          *
    ->                   FROM    people, stores
    ->                   WHERE   store = number;
+-------+------------+-------+--------+--------------+--------+---------------+
| name  | hired      | store | hourly | name         | number | city          |
+-------+------------+-------+--------+--------------+--------+---------------+
| topsy | 2012-11-01 |     4 |      1 | headquarters |      4 | new york      |
| max   | 2012-02-12 |     4 |      0 | headquarters |      4 | new york      |
| zach  | 2009-03-24 |     6 |      0 | west coast   |      6 | san francisco |
| sam   | 2008-01-28 |     6 |      1 | west coast   |      6 | san francisco |
+-------+------------+-------+--------+--------------+--------+---------------+
4 rows in set (0.00 sec)

Inner join

The preceding query demonstrates an inner join. If you like, you can use the keywords INNER JOIN in place of JOIN in this type of query. In each returned row, the value in people.store equals the value in stores.number. However, the stores table has a row with the value of 5 in thenumber column but no row exists in the people table with this value in the store column. As a result, these queries do not return a row with people.store and stores.number equal to 5.

Table names

When working with a relational database, you can refer to a column in a specific table as table_name.column_name (e.g., stores.name in the example database). While you are working with a single table, a column name is sufficient to uniquely identify the data you want to work with. When you work with several tables, you might need to specify both a table name and a column name to uniquely identify certain data. Specifically, if two columns have the same name in two tables that are being joined, you must specify both the table and the column to disambiguate the columns. The following query demonstrates the problem.

MariaDB [maxdb]> SELECT          *
    ->                   FROM    people, stores
    ->                   WHERE   store = number and
    ->                           name = 'max';
ERROR 1052 (23000): Column 'name' in where clause is ambiguous

In the preceding query, the column name is ambiguous: A column named name exists in both the people and stores tables. MariaDB cannot determine which table to use in the WHERE clause. The next query solves this problem. It uses people.name in the WHERE clause to specify that MariaDB is to use the name column from the people table.

MariaDB [maxdb]> SELECT          *
    ->                   FROM    people, stores
    ->                   WHERE   store = number and
                                 people.name = 'max';
+------+------------+-------+--------+--------------+--------+----------+
| name | hired      | store | hourly | name         | number | city     |
+------+------------+-------+--------+--------------+--------+----------+
| max  | 2012-05-01 |     4 |      0 | headquarters |      4 | new york |
+------+------------+-------+--------+--------------+--------+----------+
1 row in set (0.01 sec)

Even though it might not be necessary, it can make code and comments clearer to specify both a table and a column as in the following examples.

Table aliases

An alias for a table is another, usually shorter, name for the table. Aliases can make a SELECT statement easier to read. You declare table aliases in the FROM clause of a query. For example, the following clause declares p to be an alias for the people table and s to be an alias for the storestable.

FROM    people p JOIN stores s

With these aliases in place, you can refer to the people table as p and the stores table as s in the rest of the query.

The next example rewrites an earlier query using aliases and the JOIN keyword. In place of a comma between the table (and alias) names, this syntax uses the keyword JOIN. In place of WHERE it uses ON. Specifying p.store and s.number makes it clear that the store column of the peopletable (p.store) is being joined with the number column of the stores table (s.number). This query returns the same results as the preceding one.

SELECT          *
        FROM    people p JOIN stores s
        ON      p.store = s.number;

Outer join

An outer join can return rows in which the common column has a value that does not exist in both tables. You can specify a LEFT OUTER JOIN (or just LEFT JOIN) or a RIGHT OUTER JOIN (or just RIGHT JOIN). Left and right refer to the tables specified on the left and right of the keyword JOIN, respectively.

The next example demonstrates a right outer join. The outer table is stores, which has a value in the number column that does not have a match in the store column of the people table. SQL inserts null values in the columns from people in the joined row that does not have a match (stores.number = 5).

MariaDB [maxdb]> SELECT          *
    ->                   FROM    people p RIGHT JOIN stores s
    ->                   ON      p.store = s.number;
+-------+------------+-------+--------+--------------+--------+---------------+
| name  | hired      | store | hourly | name         | number | city          |
+-------+------------+-------+--------+--------------+--------+---------------+
| topsy | 2012-11-01 |     4 |      1 | headquarters |      4 | new york      |
| max   | 2012-02-12 |     4 |      0 | headquarters |      4 | new york      |
| zach  | 2009-03-24 |     6 |      0 | west coast   |      6 | san francisco |
| sam   | 2008-01-28 |     6 |      1 | west coast   |      6 | san francisco |
| NULL  | NULL       |  NULL |   NULL | midwest      |      5 | chicago       |
+-------+------------+-------+--------+--------------+--------+---------------+
5 rows in set (0.00 sec)

The following query performs an inner join to list the city that each person works in. A right outer join would show the cities that have no one working in them. A left outer join would show people who are not assigned to a store.

MariaDB [maxdb]> SELECT          p.name,
    ->                           city
    ->                   FROM    people p JOIN stores s
    ->                   ON      p.store = s.number;
+-------+---------------+
| name  | city          |
+-------+---------------+
| topsy | new york      |
| max   | new york      |
| zach  | san francisco |
| sam   | san francisco |
+-------+---------------+
4 rows in set (0.00 sec)

Subqueries

A subquery is a SELECT statement within the WHERE clause of another SELECT statement. The subquery returns a value or values that restrict the main query.

In the next example, the subquery returns the value of the number column from the stores table where the city column has a value of new york. The main query returns the value(s) from the name column of the people table where the value of the store column is equal to the value returned by the subquery. The result is a query that returns the names of people who work in New York.

cat sel4
use maxdb;

SELECT          name
        FROM    people
        WHERE   store =
       (SELECT          number
                FROM    stores
                WHERE   city = 'new york'
       );

mysql < sel4
name
topsy
max

The final example is a bash script that queries the MariaDB database created in this chapter. If you have not run the example commands it will not work. First the script checks that the user knows the database password. Then it displays a list of employees and asks which employee the user is interested in. It queries the database for information about that employee and displays the name of the store the employee works in and the city the store is located in.

cat employee_info
#! /bin/bash

#
# Script to display employee information
#

# Make sure user is authorized: Get database password
#
echo -n "Enter password for maxdb database: "
stty -echo
read pw
stty echo
echo
echo

# Check for valid password
#
mysql -u max -p$pw maxdb > /dev/null 2>&1 < /dev/null
if [ $? -ne 0 ]
    then
        echo "Bad password."
        exit 1
fi

# Display list of employees for user to select from
#
echo "The following people are employed by the company:"
mysql -u max -p$pw --skip-column-names maxdb <<+
SELECT name FROM people ORDER BY name;
+
echo
echo -n "Which employee would you like information about? "
read emp

# Query for store name
#
storename=$(mysql -u max -p$pw --skip-column-names maxdb <<+
SELECT stores.name FROM people, stores WHERE store = number AND people.name = "$emp";
+
)

# if null then user entered bad employee name
#
if [ "$storename" = "" ]
    then
        echo "Not a valid name."
        exit 1
fi

# Query for city name
#
storecity=$(mysql -u max -p$pw --skip-column-names maxdb <<+
SELECT city FROM people, stores WHERE store = number AND people.name = "$emp";
+
)

# Display report
#
echo
echo $emp works at the $storename store in $storecity

Chapter Summary

System administrators are frequently called upon to set up and run MariaDB databases. MariaDB/MySQL is the world’s most popular open-source RDBMS (relational database management system). It is extremely fast and is used by some of the most frequently visited Web sites on the Internet. Many programming languages provide interfaces and bindings to MariaDB, including C, PHP, Python, and Perl. You can also call MariaDB directly from a shell script or use it in a pipeline on the command line. MariaDB is a core component of the popular LAMP (Linux, Apache, MariaDB, PHP/Perl/Python) open-source enterprise software stack.

Exercises

1. List two ways you can specify the MariaDB password for a user to access MariaDB.

2. Using MariaDB interactively, create a database named dbsam that the user named sam can modify and grant privileges on. Set up Sam’s password to be porcupine. The MariaDB user named root has the password five22four.

3. What is a table? A row? A column?

4. Which commands would you use to set up a table in dbsam (created in exercise 2) named shoplist with the following columns of the specified types: day [DATE], store [CHAR(20)], lettuce [SMALLINT], soupkind [CHAR(20)], soupnum [INTEGER], and misc [VARCHAR(40)]?

5. Where can you find a list of MariaDB commands you have previously run?

Advanced Exercises

6. List two ways you can specify the name of a specific MariaDB database to work with.

7. What does a join do? When is a join useful?

8. Assume you are working with the people table in the maxdb database described in this chapter. Write a query that lists the names of all the people and their hire dates sorted by their names.

9. Assume you are working with the people and stores tables in the maxdb database described in this chapter. Write a query that sets up the aliases q and n for the people and stores tables, respectively. Have the query join the tables using the store column in the people table with thenumber column in the stores table. From left to right, have the query display the name of the city the person works in, the name of the person, and the person’s hire date. Sort the output by city name.