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

---

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.

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.

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

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)

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

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

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)

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 sam, hourly 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 name, number, 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.

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.