All pages
Powered by GitBook
1 of 23

Quickstart Guides

Get started quickly with MariaDB Server using these quickstart guides. Follow step-by-step instructions to install, configure, and begin using MariaDB for your projects.

Installing MariaDB Server Guide

Quickstart Guide: Installing MariaDB Server

This guide provides a quick overview of how to install MariaDB Server on common operating systems. The specific steps may vary slightly depending on your Linux distribution or if you are installing on Windows.

For Linux (Ubuntu/Debian/Red Hat-based distributions)

The most common way to install MariaDB on Linux is through your system's package manager.

Steps:

  1. Update Package List:

    Before installing, it's a good practice to update your package index.

    • For Debian/Ubuntu:Bash

      sudo apt update
    • For Red Hat/CentOS/Fedora:Bash

      sudo yum update # For older systems
      sudo dnf update # For newer systems
  2. Install MariaDB Server:

    Install the MariaDB server and client packages.

    • For Debian/Ubuntu:Bash

      sudo apt install mariadb-server mariadb-client
    • For Red Hat/CentOS/Fedora:Bash

      sudo dnf install mariadb mariadb-server
  3. Secure the Installation:

    After installation, run the security script to set a root password, remove anonymous users, and disable remote root login.

    sudo mariadb-secure-installation

    Follow the prompts to configure your security settings.

  4. Start and Verify the Service:

    MariaDB typically starts automatically after installation. You can check its status and manually start it if needed.

    • Check status:

      sudo systemctl status mariadb
    • Start service (if not running):Bash

      sudo systemctl start mariadb
    • Verify installation by connecting as root:Bash

      mariadb -u root -p

      Enter the root password you set during the secure installation.

For Windows

For Windows, MariaDB provides an .msi installer for a straightforward graphical installation.

Steps:

  1. Download MariaDB:

    Visit the MariaDB downloads page to get the latest .msi installer.

  2. Run the Installer:

    Double-click the downloaded .msi file to start the installation wizard.

  3. Follow On-Screen Instructions:

    The installer will guide you through the process, including:

    • Accepting the end-user license agreement.

    • Selecting features and the installation directory.

    • Setting a password for the root user.

    • Configuring MariaDB as a service and setting the port (default is 3306).

    • Optionally, enabling UTF8 as the default server character set.

Important Notes:

  • Firewall: Ensure your firewall is configured to allow connections to MariaDB on the appropriate port (default 3306) if you need remote access.

  • Root Password: Always set a strong root password during the secure installation step.

  • Further Configuration: For production environments, you may need to adjust further settings in the MariaDB configuration files (e.g., my.cnf on Linux).

Additional Resources:

  • Get Started with MariaDB

  • How To Install MariaDB on Ubuntu 22.04 - DigitalOcean

  • Install MariaDB - MariaDBTutorial.com

Adding & Changing Data Guide

Adding and Modifying Data Guide

This guide explains how to add new data and modify existing data in MariaDB using INSERT, REPLACE, and UPDATE statements. Learn about various options for handling duplicates, managing statement priorities, inserting data from other tables, and performing conditional updates.

Adding Data with INSERT

The INSERT statement is used to add new rows to a table.

Basic Syntax:

If providing values for all columns in their defined order:

INSERT table1 VALUES('value1','value2','value3');

The number of values must match the number of columns in table1.

Specifying Columns:

It's good practice to specify the columns you are inserting data into, which also allows you to insert columns in any order or omit columns that have default values or allow NULL.

INSERT INTO table1 (col3, col1) VALUES('value_for_col3', 'value_for_col1');
  • The INTO keyword is optional but commonly used for readability.

  • If a column is not listed and is an AUTO_INCREMENT key, its value will be generated. For other omitted columns, their DEFAULT value will be used, or NULL if allowed. You can explicitly insert a default value using the DEFAULT keyword in the VALUES list for a specific column.

Multiple Row Inserts:

Insert multiple rows in a single statement for efficiency:

INSERT INTO table2 (id_col, data_col1, data_col2) VALUES
  ('id1', 'text_a', 'text_b'),
  ('id2', 'text_c', 'text_d'),
  ('id3', 'text_e', 'text_f');

The VALUES keyword is used only once, with each row's values enclosed in parentheses and separated by commas.

Handling Duplicates with INSERT IGNORE:

If you attempt to insert a row that would cause a duplicate value in a PRIMARY KEY or UNIQUE index, an error normally occurs, and the row (and potentially subsequent rows in a multi-row insert) might not be inserted.

Using IGNORE tells MariaDB to discard the duplicate row(s) and continue inserting any other valid rows without generating an error.

INSERT IGNORE INTO table2 (unique_id_col, data_col) VALUES
  ('id1', 'some_data'),        -- Will be inserted if new
  ('id2', 'other_data'),       -- Will be inserted if new
  ('id1', 'duplicate_data');  -- Will be ignored if 'id1' already exists or was just inserted

Managing INSERT Priority and Behavior

LOW_PRIORITY:

An INSERT statement normally takes priority over SELECT statements, potentially locking the table and making other clients wait. LOW_PRIORITY makes the INSERT wait until no other clients are reading from the table.

INSERT LOW_PRIORITY INTO table1 VALUES('value1','value2','value3');
  • Once the LOW_PRIORITY insert begins, it will lock the table as usual. New read requests that arrive while it's waiting will be processed before it.

DELAYED:

(Note: INSERT DELAYED is a feature that was primarily associated with the MyISAM storage engine. It is deprecated in older MariaDB/MySQL versions and removed in modern MariaDB versions (e.g., from MariaDB 10.5). Check your MariaDB version for support and consider alternatives if using a recent version.)

INSERT DELAYED allowed the server to queue the insert request and return control to the client immediately. Data was written when the table was not in use. Multiple DELAYED inserts were batched.

-- Syntax for historical reference; may not be supported
INSERT DELAYED INTO table1 VALUES('value1','value2','value3');

Flaws included no confirmation of successful insertion and potential data loss if the server crashed before data was written from memory.

Inserting Data from Another Table (INSERT...SELECT)

You can insert rows into a table based on data retrieved from another table (or tables) using INSERT ... SELECT.

INSERT INTO softball_team (last_name, first_name, telephone)
  SELECT name_last, name_first, tel_home
  FROM company_database.employees
  WHERE is_on_softball_team = 'Y';
  • The columns in the INSERT INTO softball_team (...) list must correspond in number and general data type compatibility to the columns in the SELECT list.

  • INSERT...SELECT statements generally cannot operate on the exact same table as both the target and the source directly without mechanisms like temporary tables or certain subquery structures.

Replacing Data with REPLACE

The REPLACE statement works like INSERT, but if a new row has the same value as an existing row for a PRIMARY KEY or a UNIQUE index, the existing row is deleted before the new row is inserted. If no such conflict exists, it acts like a normal INSERT.

REPLACE LOW_PRIORITY INTO table2 (id_col, data_col1, data_col2) VALUES
  ('id1', 'new_text_a', 'new_text_b'), -- If 'id1' exists, old row is deleted, this is inserted
  ('id2', 'new_text_c', 'new_text_d'), -- If 'id2' doesn't exist, this is inserted
  ('id3', 'new_text_e', 'new_text_f');
  • Flags like LOW_PRIORITY work similarly to INSERT.

  • REPLACE also supports the REPLACE ... SELECT syntax.

  • Because REPLACE performs a delete then an insert, any columns in the table not specified in the REPLACE statement will receive their default values for the newly inserted row, not values from the old row.

Modifying Data with UPDATE

Use the UPDATE statement to change data in existing rows.

Basic Syntax:

UPDATE table3
SET col1 = 'new_value_a', col2 = 'new_value_b'
WHERE id_column < 100;
  • The SET clause specifies which columns to modify and their new values.

  • The WHERE clause is crucial; it determines which rows are updated. Without a WHERE clause, all rows in the table will be updated.

  • LOW_PRIORITY and IGNORE (to ignore errors like unique key violations during update, allowing other valid row updates to proceed) can also be used with UPDATE.

Using Current Column Values in an Update:

You can use a column's current value in the calculation for its new value.

UPDATE table5
SET event_date = DATE_ADD(event_date, INTERVAL 1 DAY)
WHERE DAYOFWEEK(event_date) = 1; -- Example: Add 1 day if event_date is a Sunday

ORDER BY and LIMIT with UPDATE:

You can control the order in which rows are updated and limit the number of rows affected (for single-table updates).

UPDATE LOW_PRIORITY table3
SET col1 = 'updated_text_a', col2 = 'updated_text_b'
WHERE status_column = 'pending'
ORDER BY creation_date DESC
LIMIT 10;

This updates the 10 most recently created 'pending' rows.

Multi-Table UPDATE:

You can update rows in one table based on values from another table by joining them.

UPDATE products p
JOIN stock_levels s ON p.product_id = s.product_id
SET p.stock_count = s.current_stock
WHERE s.warehouse_id = 'WHA';
  • Here, products.stock_count is updated using values from stock_levels.

  • ORDER BY and LIMIT are generally not allowed with multi-table UPDATE statements in this direct join form.

Conditional Inserts or Updates (INSERT ... ON DUPLICATE KEY UPDATE)

This powerful feature allows you to INSERT a new row, but if a duplicate key (Primary or Unique) conflict occurs, it performs an UPDATE on the existing row instead.

INSERT INTO table1 (id, col1, col2, status_column)
VALUES ('1012', 'some_text', 'other_text', 'new')
ON DUPLICATE KEY UPDATE status_column = 'old', col2 = VALUES(col2);
  • If id '1012' does not exist, the row is inserted with status_column = 'new'.

  • If id '1012' already exists, the existing row is updated: status_column is set to 'old', and col2 is updated with the value that would have been inserted for col2 (using VALUES(col2)).

  • The IGNORE keyword can be used with INSERT ... ON DUPLICATE KEY UPDATE to ignore errors that might occur during the UPDATE part if the update itself causes a problem (though this is less common). If IGNORE is used with INSERT and ON DUPLICATE KEY UPDATE is also present, IGNORE only applies to the INSERT part, not the UPDATE part.

Further Data Modification Methods

Beyond these SQL statements, MariaDB offers bulk methods for adding data, such as:

  • LOAD DATA INFILE: For importing data from text files.

  • mariadb-import utility: A command-line tool that uses LOAD DATA INFILE. These are covered in "Bulk Data Importing Guide").

CC BY-SA / Gnu FDL

Essential Queries Guide

The Essential Queries Guide offers a concise collection of commonly-used SQL queries. It's designed to help developers and database administrators quickly find syntax and examples for typical database operations, from table creation and data insertion to effective data retrieval and manipulation.

Creating a Table

To create new tables:

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
CREATE TABLE student_tests (
 name CHAR(10), test CHAR(10),
 score TINYINT, test_date DATE
);

For more details, see the official CREATE TABLE documentation.

Inserting Records

To add data into your tables:

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (2), (4);
INSERT INTO student_tests
 (name, test, score, test_date) VALUES
 ('Chun', 'SQL', 75, '2012-11-05'),
 ('Chun', 'Tuning', 73, '2013-06-14'),
 ('Esben', 'SQL', 43, '2014-02-11'),
 ('Esben', 'Tuning', 31, '2014-02-09'),
 ('Kaolin', 'SQL', 56, '2014-01-01'),
 ('Kaolin', 'Tuning', 88, '2013-12-29'),
 ('Tatiana', 'SQL', 87, '2012-04-28'),
 ('Tatiana', 'Tuning', 83, '2013-09-30');

For more information, see the official INSERT documentation.

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute automatically generates a unique identity for new rows.

Create a table with an AUTO_INCREMENT column:

CREATE TABLE student_details (
 id INT NOT NULL AUTO_INCREMENT, name CHAR(10),
 date_of_birth DATE, PRIMARY KEY (id)
);

When inserting, omit the id field; it will be automatically generated:

INSERT INTO student_details (name,date_of_birth) VALUES
 ('Chun', '1993-12-31'),
 ('Esben','1946-01-01'),
 ('Kaolin','1996-07-16'),
 ('Tatiana', '1988-04-13');

Verify the inserted records:

SELECT * FROM student_details;
+----+---------+---------------+
| id | name    | date_of_birth |
+----+---------+---------------+
|  1 | Chun    | 1993-12-31    |
|  2 | Esben   | 1946-01-01    |
|  3 | Kaolin  | 1996-07-16    |
|  4 | Tatiana | 1988-04-13    |
+----+---------+---------------+

For more details, see the AUTO_INCREMENT documentation.

Querying from two tables on a common value (JOIN)

To combine rows from two tables based on a related column:

SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;

This type of query is a join. For more details, consult the documentation on JOINS.

Finding the Maximum Value

To find the maximum value in a column:

SELECT MAX(a) FROM t1;
+--------+
| MAX(a) |
+--------+
|      3 |
+--------+

See the MAX() function documentation. For a grouped example, refer to Finding the Maximum Value and Grouping the Results below.

Finding the Minimum Value

To find the minimum value in a column:

SELECT MIN(a) FROM t1;
+--------+
| MIN(a) |
+--------+
|      1 |
+--------+

See the MIN() function documentation.

Finding the Average Value

To calculate the average value of a column:

SELECT AVG(a) FROM t1;
+--------+
| AVG(a) |
+--------+
| 2.0000 |
+--------+

See the AVG() function documentation.

Finding the Maximum Value and Grouping the Results

To find the maximum value within groups:

SELECT name, MAX(score) FROM student_tests GROUP BY name;
+---------+------------+
| name    | MAX(score) |
+---------+------------+
| Chun    |         75 |
| Esben   |         43 |
| Kaolin  |         88 |
| Tatiana |         87 |
+---------+------------+

Further details are available in the MAX() function documentation.

Ordering Results

To sort your query results (e.g., in descending order):

SELECT name, test, score FROM student_tests 
 ORDER BY score DESC; -- Use ASC for ascending order
+---------+--------+-------+
| name    | test   | score |
+---------+--------+-------+
| Kaolin  | Tuning |    88 |
| Tatiana | SQL    |    87 |
| Tatiana | Tuning |    83 |
| Chun    | SQL    |    75 |
| Chun    | Tuning |    73 |
| Kaolin  | SQL    |    56 |
| Esben   | SQL    |    43 |
| Esben   | Tuning |    31 |
+---------+--------+-------+

For more options, see the ORDER BY documentation.

Finding the Row with the Minimum of a Particular Column

To find the entire row containing the minimum value of a specific column across all records:

SELECT name, test, score FROM student_tests 
 WHERE score = (SELECT MIN(score) FROM student_tests);
+-------+--------+-------+
| name  | test   | score |
+-------+--------+-------+
| Esben | Tuning |    31 |
+-------+--------+-------+

Finding Rows with the Maximum Value of a Column by Group

To retrieve the full record for the maximum value within each group (e.g., highest score per student):

SELECT name, test, score FROM student_tests st1
 WHERE score = (SELECT MAX(st2.score) FROM student_tests st2 WHERE st1.name = st2.name);
+---------+--------+-------+
| name    | test   | score |
+---------+--------+-------+
| Chun    | SQL    |    75 |
| Esben   | SQL    |    43 |
| Kaolin  | Tuning |    88 |
| Tatiana | SQL    |    87 |
+---------+--------+-------+

Calculating Age

Use the TIMESTAMPDIFF function to calculate age from a birth date.

To see the current date (optional, for reference):

SELECT CURDATE() AS today;
+------------+
| today      |
+------------+
| 2014-02-17 | -- Example output; actual date will vary
+------------+

To calculate age as of a specific date (e.g., '2014-08-02'):

SELECT name, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, '2014-08-02') AS age
  FROM student_details;
+---------+---------------+------+
| name    | date_of_birth | age  |
+---------+---------------+------+
| Chun    | 1993-12-31    |   20 |
| Esben   | 1946-01-01    |   68 |
| Kaolin  | 1996-07-16    |   18 |
| Tatiana | 1988-04-13    |   26 |
+---------+---------------+------+

To calculate current age, replace the specific date string (e.g., '2014-08-02') with CURDATE().

See the TIMESTAMPDIFF() documentation for more.

Using User-defined Variables

User-defined variables can store values for use in subsequent queries within the same session.

Example: Set a variable for the average score and use it to filter results.

SELECT @avg_score := AVG(score) FROM student_tests;
+-------------------------+
| @avg_score:= AVG(score) |
+-------------------------+
|            67.000000000 |
+-------------------------+
SELECT * FROM student_tests WHERE score > @avg_score;
+---------+--------+-------+------------+
| name    | test   | score | test_date  |
+---------+--------+-------+------------+
| Chun    | SQL    |    75 | 2012-11-05 |
| Chun    | Tuning |    73 | 2013-06-14 |
| Kaolin  | Tuning |    88 | 2013-12-29 |
| Tatiana | SQL    |    87 | 2012-04-28 |
| Tatiana | Tuning |    83 | 2013-09-30 |
+---------+--------+-------+------------+

Example: Add an incremental counter to a result set.

SET @count = 0;
SELECT @count := @count + 1 AS counter, name, date_of_birth FROM student_details;
+---------+---------+---------------+
| counter | name    | date_of_birth |
+---------+---------+---------------+
|       1 | Chun    | 1993-12-31    |
|       2 | Esben   | 1946-01-01    |
|       3 | Kaolin  | 1996-07-16    |
|       4 | Tatiana | 1988-04-13    |
+---------+---------+---------------+

See User-defined Variables for more.

View Tables in Order of Size

To list all tables in the current database, ordered by their size (data + index) in megabytes:

SELECT table_schema AS `DB`, table_name AS `TABLE`,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
  FROM information_schema.TABLES
  WHERE table_schema = DATABASE() -- This clause restricts results to the current database
  ORDER BY (data_length + index_length) DESC;
+--------------------+---------------------------------------+-----------+
| DB                 | Table                                 | Size (MB) | -- Example Output
+--------------------+---------------------------------------+-----------+
| your_db_name       | some_large_table                      |      7.05 |
| your_db_name       | another_table                         |      6.59 |
...
+--------------------+---------------------------------------+-----------+

Removing Duplicates

To remove duplicate rows based on specific column values, while keeping one instance (e.g., the instance with the highest id).

This example assumes id is a unique primary key and duplicates are identified by the values in column f1. It keeps the row with the maximum id for each distinct f1 value.

Setup sample table and data:

CREATE TABLE t (id INT, f1 VARCHAR(2));
INSERT INTO t VALUES (1,'a'), (2,'a'), (3,'b'), (4,'a');

To delete duplicate rows, keeping the one with the highest id for each group of f1 values:

DELETE t_del FROM t AS t_del
INNER JOIN (
    SELECT f1, MAX(id) AS max_id
    FROM t
    GROUP BY f1
    HAVING COUNT(*) > 1 -- Identify groups with actual duplicates
) AS t_keep ON t_del.f1 = t_keep.f1 AND t_del.id < t_keep.max_id;

This query targets rows for deletion (t_del) where their f1 value matches an f1 in a subquery (t_keep) that has duplicates, and their id is less than the maximum id found for that f1 group.

Verify results after deletion:

SELECT * FROM t;
+------+------+
| id   | f1   |
+------+------+
|    3 | b    |
|    4 | a    |
+------+------+

This page is licensed: CC BY-SA / Gnu FDL

Basics Guide

Cover

WEBINAR

MariaDB 101: Learning the Basics of MariaDB

Watch Now

The quickstart guide walks you through connecting to a MariaDB server, creating your initial database and table structures, and performing fundamental data operations. It's designed for new users or anyone needing a quick refresher on essential MariaDB commands and basic syntax.

Connecting to MariaDB Server

To interact with the MariaDB server, use a client program. The default command-line client is mariadb.

Connect to MariaDB in monitor mode from the Linux command-line:

mariadb -u root -p -h localhost

Common options:

  • -u username: Specifies the MariaDB user (e.g., root). This is not the OS user.

  • -p: Prompts for the password. If no password is set, press [Enter].

  • -h hostname_or_IP: Specifies the server's hostname or IP address if the client is on a different machine than the server. Often not needed if connecting locally.

If logged into Linux as root, you might only need:

mariadb -p

To exit the mariadb monitor, type quit or exit and press [Enter].

Creating a Database Structure

First, create and select a database.

CREATE DATABASE bookstore;
USE bookstore;

This creates a database named bookstore and sets it as the default for subsequent operations.

Next, create tables to hold data.

CREATE TABLE books (
    isbn CHAR(20) PRIMARY KEY,
    title VARCHAR(50),
    author_id INT,
    publisher_id INT,
    year_pub CHAR(4),
    description TEXT
);

This statement creates a books table with six columns:

  • isbn: CHAR(20), the primary key for unique identification.

  • title: VARCHAR(50), a variable-width string for the book title.

  • author_id, publisher_id: INT, for storing numeric IDs.

  • year_pub: CHAR(4), a fixed-width string for the publication year.

  • description: TEXT, for longer descriptive text (up to 65,535 bytes).

To view the structure of a created table:

DESCRIBE books;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| isbn         | char(20)    | NO   | PRI | NULL    |       |
| title        | varchar(50) | YES  |     | NULL    |       |
| author_id    | int(11)     | YES  |     | NULL    |       |
| publisher_id | int(11)     | YES  |     | NULL    |       |
| year_pub     | char(4)     | YES  |     | NULL    |       |
| description  | text        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

To modify an existing table, use the ALTER TABLE statement (see ALTER TABLE documentation). To delete a table and all its data (irreversibly), use DROP TABLE table_name; (see DROP TABLE documentation).

Example of another table, authors, using AUTO_INCREMENT for the primary key:

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    name_last VARCHAR(50),
    name_first VARCHAR(50),
    country VARCHAR(50)
);

The author_id will automatically generate a unique number for each new author.

SQL Syntax Notes

  • SQL statements typically end with a semicolon (;) or \G.

  • Statements can span multiple lines; execution occurs after the terminating character and [Enter].

  • To cancel a partially typed statement in the mariadb client, enter \c and press [Enter].

  • SQL reserved words (e.g., CREATE, SELECT) are often written in uppercase for readability but are case-insensitive in MariaDB.

  • Database and table names are case-sensitive on Linux systems (as they map to directories and files) but generally not on Windows. Column names are case-insensitive.

  • Using lowercase for table and column names is a common convention.

Entering Data

Use the INSERT statement (see INSERT documentation) to add new rows to a table.

INSERT INTO authors (name_last, name_first, country)
VALUES('Kafka', 'Franz', 'Czech Republic');

Since author_id in the authors table is AUTO_INCREMENT (see AUTO_INCREMENT documentation), its value is assigned automatically. If not all columns are being supplied with data, the column names must be listed, followed by their corresponding values in the VALUES clause.

To insert data for a book, referencing author_id 1 (assuming Kafka's author_id became 1):

INSERT INTO books (title, author_id, isbn, year_pub)
VALUES('The Castle', '1', '0805211063', '1998');

Multiple rows can be inserted with a single INSERT statement:

INSERT INTO books (title, author_id, isbn, year_pub)
VALUES('The Trial', '1', '0805210407', '1995'),
      ('The Metamorphosis', '1', '0553213695', '1995'),
      ('America', '1', '0805210644', '1995');

Retrieving Data

Use the SELECT statement (see SELECT documentation) to query data from tables.

To retrieve all book titles:

SELECT title FROM books;

To limit the number of rows returned (e.g., to 5) using LIMIT (see LIMIT documentation):

SELECT title FROM books LIMIT 5;

To retrieve data from multiple tables, use a JOIN (see JOIN documentation). This example lists book titles and author last names by joining books and authors on their common author_id column:

SELECT title, name_last
FROM books
JOIN authors USING (author_id);

To filter results, use the WHERE clause. This example finds books by 'Kafka' and renames the title column1 in the output to 'Kafka Books' using AS (an alias):

SELECT title AS 'Kafka Books'
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Kafka';
+-------------------+
| Kafka Books       |
+-------------------+
| The Castle        |
| The Trial         |
| The Metamorphosis |
| America           |
+-------------------+

Changing & Deleting Data

To modify existing data, use the UPDATE statement (see UPDATE documentation). Always use a WHERE clause to specify which rows to update.

UPDATE books
SET title = 'Amerika'
WHERE isbn = '0805210644';

This changes the title for the book with the specified isbn. Multiple columns can be updated by separating column = value assignments with commas within the SET clause.

To remove rows from a table, use the DELETE statement (see DELETE documentation). Use WHERE to specify which rows to delete.

DELETE FROM books
WHERE author_id = '2034'; -- Assuming '2034' is the author_id to be deleted

This deletes all books associated with author_id '2034'.

This page is licensed: CC BY-SA / Gnu FDL

Altering Tables Guide

This guide provides essential instructions for modifying existing table structures. Learn how to add, drop, and change columns, manage indexes and default values, and rename tables, along with key precautions for these operations when working with your database.

Before You Begin: Backup Your Tables

Before making any structural changes to a table, especially if it contains data, always create a backup. The mariadb-dump utility is a common and effective tool for this.

Example: Backing up a single table

Suppose you have a database db1 and a table clients. Its initial structure is:

DESCRIBE clients;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cust_id     | int(11)     |      | PRI | 0       |       |
| name        | varchar(25) | YES  |     | NULL    |       |
| address     | varchar(25) | YES  |     | NULL    |       |
| city        | varchar(25) | YES  |     | NULL    |       |
| state       | char(2)     | YES  |     | NULL    |       |
| zip         | varchar(10) | YES  |     | NULL    |       |
| client_type | varchar(4)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

To back up the clients table from the command-line:

mariadb-dump --user='your_username' --password='your_password' --add-locks db1 clients > clients.sql
  • Replace 'your_username' and 'your_password' with your actual MariaDB credentials.

  • --add-locks: Locks the table during the backup and unlocks it afterward.

  • db1 clients: Specifies the database and then the table.

  • > clients.sql: Redirects the output to a file named clients.sql.

Restoring from a backup

If you need to restore the table:

mariadb --user='your_username' --password='your_password' db1 < clients.sql

This command uses the mariadb client to execute the SQL in clients.sql, which will typically drop the existing table (if it exists) and recreate it from the backup. Ensure no critical data has been added to the live table since the backup if you intend to overwrite it.

For the examples that follow, we'll assume structural changes are being made, sometimes on an empty table for simplicity, but the backup step is always recommended for tables with data.

Adding Columns

Use the ALTER TABLE statement with the ADD COLUMN clause.

Add a column to the end of the table:

To add a status column with a fixed width of two characters:

ALTER TABLE clients
ADD COLUMN status CHAR(2);

Add a column after a specific existing column:

To add address2 (varchar 25) after the address column:

ALTER TABLE clients
ADD COLUMN address2 VARCHAR(25) AFTER address;

Add a column to the beginning of the table:

ALTER TABLE clients
ADD COLUMN new_first_column VARCHAR(50) FIRST;

(Assuming new_first_column is the one to be added at the beginning).

After additions, the table structure might look like (excluding new_first_column for consistency with original example flow):

DESCRIBE clients;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cust_id     | int(11)     |      | PRI | 0       |       |
| name        | varchar(25) | YES  |     | NULL    |       |
| address     | varchar(25) | YES  |     | NULL    |       |
| address2    | varchar(25) | YES  |     | NULL    |       |
| city        | varchar(25) | YES  |     | NULL    |       |
| state       | char(2)     | YES  |     | NULL    |       |
| zip         | varchar(10) | YES  |     | NULL    |       |
| client_type | varchar(4)  | YES  |     | NULL    |       |
| status      | char(2)     | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Changing Column Definitions

Use ALTER TABLE with CHANGE or MODIFY.

Change column type (e.g., to ENUM):

The status column name is specified twice even if not changing the name itself when using CHANGE.

ALTER TABLE clients
CHANGE status status ENUM('AC','IA');

Change column name and keep type:

To change status to active while keeping the ENUM definition:

ALTER TABLE clients
CHANGE status active ENUM('AC','IA');

When using CHANGE, the current column name is followed by the new column name and the complete type definition.

Modify column type or attributes without renaming:

Use MODIFY if you are only changing the data type or attributes, not the name.

ALTER TABLE clients
MODIFY address1 VARCHAR(40); -- Assuming 'address1' is an existing column

Complex Changes (e.g., ENUM migration with data):

Changing ENUM values in a table with existing data requires careful steps to prevent data loss. This typically involves:

  1. Temporarily modifying the ENUM to include both old and new values.

  2. Updating existing rows to use the new values.

  3. Modifying the ENUM again to remove the old values.

Example of changing address to address1 (40 chars) and preparing active ENUM for new values 'yes','no' from 'AC','IA':

ALTER TABLE clients
    CHANGE address address1 VARCHAR(40),
    MODIFY active ENUM('yes','no','AC','IA'); -- Temporarily include all

Then, update the data:

UPDATE clients
SET active = 'yes'
WHERE active = 'AC';

UPDATE clients
SET active = 'no'
WHERE active = 'IA';

Finally, restrict the ENUM to new values:

ALTER TABLE clients
MODIFY active ENUM('yes','no');

Dropping Columns

To remove a column and its data (this action is permanent and irreversible without a backup):

ALTER TABLE clients
DROP COLUMN client_type;

Managing Default Values

Set a default value for a column:

If most clients are in 'LA', set it as the default for the state column:

ALTER TABLE clients
ALTER state SET DEFAULT 'LA';

Remove a default value from a column:

This reverts the default to its standard (e.g., NULL if nullable, or determined by data type).

ALTER TABLE clients
ALTER state DROP DEFAULT;

This DROP DEFAULT does not delete existing data in the column.

Managing Indexes

Indexes are separate objects from columns. Modifying an indexed column often requires managing its index.

View existing indexes on a table:

The \G displays results in a vertical format, which can be easier to read for wide output.

SHOW INDEX FROM clients\G

Example output:

*************************** 1. row ***************************
           Table: clients
      Non_unique: 0
        Key_name: PRIMARY
    Seq_in_index: 1
     Column_name: cust_id
       Collation: A
     Cardinality: 0
        Sub_part: NULL
          Packed: NULL
         Comment:

Changing an indexed column (e.g., Primary Key):

Attempting to CHANGE a column that is part of a PRIMARY KEY without addressing the key might result in an error like "Multiple primary key defined". The index must be dropped first, then the column changed, and the key re-added.

ALTER TABLE clients
    DROP PRIMARY KEY,
    CHANGE cust_id client_id INT PRIMARY KEY;

The order is important: DROP PRIMARY KEY first.

Changing a column with another index type (e.g., UNIQUE):

If cust_id had a UNIQUE index named cust_id_unique_idx (Key_name from SHOW INDEX):

ALTER TABLE clients
    DROP INDEX cust_id_unique_idx, -- Use the actual Key_name
    CHANGE cust_id client_id INT UNIQUE;

If the Key_name is the same as the Column_name (e.g. for a single column UNIQUE key defined on cust_id where cust_id is also its Key_name):

ALTER TABLE clients
    DROP INDEX cust_id, -- If cust_id is the Key_name
    CHANGE cust_id client_id INT UNIQUE;

Changing index type and handling duplicates (e.g., INDEX to UNIQUE):

If changing from an index type that allows duplicates (like a plain INDEX) to one that doesn't (UNIQUE), and duplicate data exists, the operation will fail. To force the change and remove duplicates (use with extreme caution):

ALTER IGNORE TABLE clients
    DROP INDEX cust_id_idx, -- Assuming cust_id_idx is the name of the old INDEX
    CHANGE cust_id client_id INT UNIQUE;

The IGNORE keyword causes rows with duplicate key values (for the new UNIQUE key) to be deleted. Only the first encountered row is kept.

Renaming and Shifting Tables

Rename a table:

To change the name of clients to client_addresses:

RENAME TABLE clients TO client_addresses;

Move a table to another database (can be combined with renaming):

To move client_addresses to a database named db2:

RENAME TABLE client_addresses TO db2.client_addresses;

Re-sort data within a table (MyRocks/Aria, not typically InnoDB):

For some storage engines (excluding InnoDB where tables are ordered by the primary key), you can physically reorder rows. This does not usually apply to InnoDB unless the ORDER BY columns form the primary key.

ALTER TABLE client_addresses
ORDER BY city, name;

After this, SELECT * FROM client_addresses (without an ORDER BY clause) might return rows in this new physical order, until further data modifications occur.

Key Considerations

  • Backup First: Always back up tables before making structural alterations, especially on production systems.

  • Data Integrity: Be mindful of how changes (e.g., type changes, ENUM modifications, dropping columns) can affect existing data. Test changes in a development environment.

  • Irreversible Actions: Operations like DROP COLUMN or DROP TABLE are generally irreversible without restoring from a backup. There's typically no confirmation prompt.

  • Indexes: Understand that indexes are distinct from columns. Modifying indexed columns often requires separate steps to manage the associated indexes.

  • Performance: ALTER TABLE operations on large tables can be time-consuming and resource-intensive, potentially locking the table and impacting application performance. Plan these operations during maintenance windows if possible.

CC BY-SA / Gnu FDL

Connecting to MariaDB Guide

Cover

WEBINAR

MariaDB 101: Learning the Basics of MariaDB

Watch Now

This guide details the parameters for connecting to a MariaDB server using client programs like mariadb. Learn about default connection behaviors and how to use various command-line options to customize your connection, including secure TLS configurations.

While the examples focus on the mariadb command-line client, the concepts apply to other clients like graphical interfaces or backup utilities (e.g., mariadb-dump). If you are completely new to MariaDB, refer to A MariaDB Primer first.

Default Connection Behavior

When a connection parameter is not explicitly provided, a default value is used. To connect using only default values with the mariadb client:

mariadb

In this scenario, the following defaults typically apply:

  • Host name: localhost

  • User name: Your Unix login name (on Unix-like systems) or ODBC (on Windows).

  • Password: No password is sent.

  • Database: The client connects to the server but not to a specific database by default.

  • Socket: The default socket file is used for connection.

Overriding Defaults

You can override these defaults by specifying parameters. For example:

mariadb -h 166.78.144.191 -u username -ppassword database_name

In this example:

  • -h 166.78.144.191: Specifies the host IP address instead of localhost.

  • -u username: Specifies username as the MariaDB user.

  • -ppassword: Specifies password as the password.

    • Note: For passwords, there must be no space between -p and the password value.

    • Security Warning: Providing a password directly on the command line is insecure as it can be visible to other users on the system. It's more secure to use -p without the password value, which will prompt you to enter it.

  • database_name: This is the name of the database to connect to, provided as the first argument after all options.

  • The connection will use the default TCP/IP port (usually 3306).

Connection Parameters

The following are common connection parameters:

host

  • --host=name

  • -h name

Connects to the MariaDB server on the given host.

Default: localhost.

MariaDB typically does not permit remote logins by default; see Configuring MariaDB for Remote Client Access.

password

  • --password[=passwd]

  • -p[passwd]

Specifies the password for the MariaDB account.

  • Security Best Practice: For improved security, use the -p or --password option without providing the password value directly on the command line. You will be prompted to enter it, preventing it from being visible in command history or process lists.

pipe

  • --pipe

  • -W

(Windows only) Connects to the server using a named pipe, if the server was started with the --enable-named-pipe option.

port

  • --port=num

  • -P num

Specifies the TCP/IP port number for the connection.

Default: 3306.

protocol

  • --protocol=name

Specifies the connection protocol. Possible values (case-insensitive): TCP, SOCKET, PIPE, MEMORY. The default protocol is typically the most efficient for the operating system (e.g., SOCKET on Unix).

  • TCP: TCP/IP connection (local or remote). Available on all OS.

  • SOCKET: Unix socket file connection (local server on Unix systems only). If --socket is not specified, the default is /tmp/mysql.sock.

  • PIPE: Named-pipe connection (local or remote). Windows only.

  • MEMORY: Shared-memory connection (local server on Windows systems only).

shared-memory-base-name

  • --shared-memory-base-name=name

(Windows only) Specifies the shared-memory name for connecting to a local server started with the --shared-memory option. The value is case-sensitive.

Default: MARIADB.

socket

  • --socket=name

  • -S name

For connections to localhost:

  • On Unix: Specifies the Unix socket file to use. Default: /tmp/mysql.sock.

  • On Windows: Specifies the name (case-insensitive) of the named pipe if the server was started with --enable-named-pipe. Default: MARIADB.

user

  • --user=name

  • -u name

Specifies the MariaDB user name for the connection.

Default: Your Unix login name (on Unix-like systems) or ODBC (on Windows).

See the GRANT command for information on creating MariaDB user accounts.

TLS Options

These options control the use of TLS (Transport Layer Security) for secure connections. For comprehensive details, see Secure Connections Overview and TLS System Variables.

  • --ssl: Enable TLS for the connection. Automatically enabled if other --ssl-* flags are used. Disable with --skip-ssl.

  • --ssl-ca=name: CA (Certificate Authority) file in PEM format. (Implies --ssl).

  • --ssl-capath=name: Directory containing CA certificates in PEM format. (Implies --ssl).

  • --ssl-cert=name: Client X.509 certificate in PEM format. (Implies --ssl).

  • --ssl-cipher=name: Specific TLS cipher(s) to use for the connection. (Implies --ssl).

  • --ssl-key=name: Client X.509 private key in PEM format. (Implies --ssl).

  • --ssl-crl=name: Certificate Revocation List (CRL) file in PEM format. (Implies --ssl).

  • --ssl-crlpath=name: Directory containing CRL files. (Implies --ssl).

  • --ssl-verify-server-cert: Verifies the server's certificate "Common Name" against the hostname used for connecting. Disabled by default.

Option Files

Connection parameters and other options can also be set in option files (configuration files), which most MariaDB clients read upon startup. To see which option files a client reads and the option groups it recognizes, typically run the client with the --help option.

Troubleshooting Connection Issues Guide

The guide helps diagnose and resolve common issues encountered when connecting to a MariaDB server. Identify causes for errors like 'Can't connect to local server' or access denied messages, and learn steps to effectively troubleshoot these connection problems.

If you are completely new to MariaDB and relational databases, you may want to start with A MariaDB Primer. Also, ensure you understand the connection parameters discussed in the Connection Parameters Guide.

Server Not Running or Incorrect Location

Symptoms:

You receive errors similar to:

ERROR 2002 (HY000): Can't connect to local MySQL server through
  socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

or

mariadb -u someuser -p --port=3307 --protocol=tcp
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost'
  (111 "Connection refused")

Causes & Solutions:

  • Server Not Running: The MariaDB server process may not be running.

  • Incorrect Parameters: The server is running, but not on the specified host, port, socket, pipe, or protocol. Verify your connection parameters.

  • Socket File Mismatch (Unix): The socket file path might be non-standard or inconsistent between server and client configurations.

    • Check your my.cnf (or my.ini) configuration file. Ensure the socket option has the identical value in both the [mysqld] (server) section and the [client] (or [mysql]) section.

    • To find the running Unix socket file, you can try commands like:

      netstat -ln | grep mysqld

      Example output:

      unix  2      [ ACC ]     STREAM     LISTENING     33209505 /var/run/mysqld/mysqld.sock
  • See also: Troubleshooting Installation Issues.

Unable to Connect from a Remote Location

Symptoms:

You can connect locally, but not from a remote machine, possibly seeing errors like:

./client/mysql --host=myhost --protocol=tcp --port=3306 test
ERROR 2002 (HY000): Can't connect to MySQL server on 'myhost' (115)

You can use telnet (if available) to test basic network connectivity to the port:

telnet myhost 3306

A "Connection refused" message from telnet indicates a network or firewall issue, or that MariaDB is not listening for TCP/IP connections or on that specific interface/port.

The perror utility can interpret OS error codes:

perror 115

Example output:

OS error code 115: Operation now in progress

Causes & Solutions:

  • By default, MariaDB often does not accept remote TCP/IP connections or is bound only to localhost (127.0.0.1).

  • Solution: See Configuring MariaDB for Remote Client Access for detailed instructions on how to enable remote connections by adjusting the bind-address server variable and ensuring user accounts are configured correctly for remote hosts.

Authentication Problems

Symptoms:

Connection is established, but authentication fails (e.g., "Access denied for user...").

Causes & Solutions:

  • Unix Socket Authentication (MariaDB 10.4.3+): On Unix-like systems, the unix_socket authentication plugin is enabled by default for local connections via the Unix socket file. This plugin uses operating system user credentials.

    • See the unix_socket authentication plugin documentation for connection instructions and how to switch to password-based authentication if needed.

    • For an overview of authentication changes in MariaDB 10.4, see Authentication from MariaDB 10.4.

  • Incorrect Username/Host Combination: Authentication is specific to a username@host combination. For example, 'user1'@'localhost' is distinct from 'user1'@'166.78.144.191'. Ensure the user account exists for the host from which you are connecting.

    • See the GRANT article for details on granting permissions.

  • Password Hashing: When setting or changing passwords using SET PASSWORD, ensure the PASSWORD() function is used if the server expects hashed passwords.

    • Example: SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

    • Rather than: SET PASSWORD FOR 'bob'@'%.loc.gov' = 'newpass'; (which might store the password as plain text, potentially leading to issues depending on the authentication plugin).

Problems Exporting Query Results or Loading Data

Symptoms:

You can run regular queries but get authentication or permission errors when using SELECT ... INTO OUTFILE, SELECT ... INTO DUMPFILE, or LOAD DATA INFILE.

Causes & Solutions:

  • These operations require the FILE privilege on the server.

  • Solution: Grant the necessary FILE privilege to the user. See the GRANT article.

Access Denied to a Specific Database

Symptoms:

You can connect to the MariaDB server, but attempting to USE or query a specific database results in an error:

USE test;
ERROR 1044 (42000): Access denied for user 'youruser'@'yourhost' to database 'test'

Or, connecting with mariadb -u user -p db1 works, but mariadb -u user -p db2 fails for db2.

Causes & Solutions:

  • The user account has not been granted sufficient privileges for that particular database.

  • Solution: Grant the required privileges (e.g., SELECT, INSERT, etc.) on the specific database to the user. See the GRANT article.

Issues Due to Option Files or Environment Variables

Symptoms:

Unexpected connection behavior or parameter usage that you didn't explicitly provide on the command line.

Causes & Solutions:

  • Option files (e.g., my.cnf, .my.cnf) or environment variables (e.g., MYSQL_HOST) might be supplying incorrect or overriding connection parameters.

  • Troubleshooting:

    • Check the values in any option files read by your client. See Configuring MariaDB with Option Files and the documentation for the specific client you are using (listed under Clients and Utilities).

    • You can often suppress the reading of default option files by using a --no-defaults option (if supported by the client):Bash

      mariadb-import --no-defaults ...

Unable to Connect / Lost Root Password

Symptoms:

You cannot connect to a running server, often because the root (or other administrative) password is lost or unknown.

Causes & Solutions:

  • Solution: You can start the MariaDB server with the --skip-grant-tables option. This bypasses the privilege system, granting full access. Use this with extreme caution and only temporarily.

    1. Stop the MariaDB server.

    2. Restart the server manually from the command line, adding the --skip-grant-tables option.

    3. Connect to the server (no password will be required for root@localhost).

    4. Execute FLUSH PRIVILEGES; to reload the grant tables (they are now active again).

    5. Change the password for the necessary account, e.g.:

      SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_strong_password');
    6. Stop the server and restart it normally (without --skip-grant-tables).

localhost vs. % Wildcard Host Issues

Symptoms:

You've created a user like 'melisa'@'%' but cannot log in as melisa when connecting from localhost.

-- User created with '%' host
CREATE USER 'melisa'@'%' IDENTIFIED BY 'password';

-- Checking users in mysql.user table
SELECT user, host FROM mysql.user WHERE user='melisa' OR user='';

Example output showing the problem:

+--------+-----------+
| user   | host      |
+--------+-----------+
| melisa | %         |
|        | localhost | -- An anonymous user for localhost
+--------+-----------+

Causes & Solutions:

  • MariaDB's user authentication prioritizes more specific host matches. If an anonymous user (''@'localhost') exists, it can take precedence over 'melisa'@'%' when connecting from localhost.

  • Solutions:

    1. Create a specific user for localhost:SQL

      CREATE USER 'melisa'@'localhost' IDENTIFIED BY 'password_for_melisa_localhost';
      GRANT ALL PRIVILEGES ON yourdatabase.* TO 'melisa'@'localhost'; -- Grant necessary privileges
      FLUSH PRIVILEGES;
    2. Remove the anonymous user for localhost (use with caution):SQL

      DROP USER ''@'localhost';
      FLUSH PRIVILEGES;

      Ensure this doesn't break other intended anonymous access if any.

This page is licensed: CC BY-SA / Gnu FDL

Doing Time Guide

Date and Time Handling Guide

This guide covers effective ways to work with date and time information in MariaDB. Learn about temporal data types, essential functions for recording current date/time, extracting specific parts, and formatting your date/time values for display or analysis.

Temporal Data Types

While dates and times can be stored as character strings, using specific temporal data types allows you to leverage MariaDB's built-in functions for manipulation and formatting.

  • DATE: For dates only. Format: YYYY-MM-DD.

  • TIME: For time only. Format: HHH:MM:SS (hours can range beyond 24).

  • DATETIME: For combined date and time. Format: YYYY-MM-DD HH:MM:SS.

  • TIMESTAMP: Similar to DATETIME, but with a more limited range and automatic update capabilities (not covered here). Range typically from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. From MariaDB 11.5 (64-bit), this range extends to 2106-02-07.

  • YEAR: For years only. Format: YY or YYYY.

Recording Current Date and Time

MariaDB provides several functions to get the current date and time.

Current Date: Use CURRENT_DATE (no parentheses) or CURDATE() (with parentheses).

INSERT INTO billable_work (doctor_id, patient_id, session_date)
VALUES ('1021', '1256', CURRENT_DATE);

To see the ID of the last inserted row (if the primary key is AUTO_INCREMENT):

SELECT rec_id, doctor_id, patient_id, session_date
FROM billable_work
WHERE rec_id = LAST_INSERT_ID();
+--------+-----------+------------+--------------+
| rec_id | doctor_id | patient_id | session_date |
+--------+-----------+------------+--------------+
|   2462 | 1021      | 1256       | 2025-05-28   | -- Example date
+--------+-----------+------------+--------------+

Current Time: Use CURRENT_TIME or CURTIME().

UPDATE billable_work
SET session_time = CURTIME()
WHERE rec_id = '2462';

SELECT patient_id, session_date, session_time
FROM billable_work
WHERE rec_id = '2462';
+------------+--------------+--------------+
| patient_id | session_date | session_time |
+------------+--------------+--------------+
| 1256       | 2025-05-28   | 13:03:22     | -- Example time
+------------+--------------+--------------+

Current Date and Time (Timestamp): Use CURRENT_TIMESTAMP, NOW(), or SYSDATE(). These functions return the current date and time in YYYY-MM-DD HH:MM:SS format, suitable for DATETIME or TIMESTAMP columns.

Extracting Date and Time Parts

Extracting from DATE types:

  • YEAR(date_column): Extracts the year.

  • MONTH(date_column): Extracts the month number (1-12).

  • DAYOFMONTH(date_column): Extracts the day of the month (1-31). Also DAY().

SELECT
    MONTH(session_date) AS Month,
    DAYOFMONTH(session_date) AS Day,
    YEAR(session_date) AS Year
FROM billable_work
WHERE rec_id = '2462';
+-------+------+------+
| Month | Day  | Year |
+-------+------+------+
|     5 |   28 | 2025 | -- Example output
+-------+------+------+

(The AS keyword is used to provide an alias for the output column name.)

Day of the Week:

  • DAYOFWEEK(date_column): Returns the weekday index (1=Sunday, 2=Monday, ..., 7=Saturday).

  • WEEKDAY(date_column): Returns the weekday index (0=Monday, 1=Tuesday, ..., 6=Sunday).

Example using IF() to determine a billing rate based on the day of the week (Saturday = day 7 for DAYOFWEEK):

SELECT
    patient_id AS 'Patient ID',
    session_date AS 'Date of Session',
    IF(DAYOFWEEK(session_date) = 7, 1.5, 1.0) AS 'Billing Rate'
FROM billable_work
WHERE rec_id = '2462';

The IF(condition, value_if_true, value_if_false) function allows conditional logic.

Other Date Part Functions:

  • DAYOFYEAR(date_column): Returns the day of the year (1-366).

  • QUARTER(date_column): Returns the quarter of the year (1-4).

Example: Selecting sessions in a specific quarter (e.g., Q2):

SELECT patient_id, session_date
FROM billable_work
WHERE QUARTER(session_date) = 2;

User variables can be used for dynamic queries:

SET @target_quarter := 2;
SELECT patient_id, COUNT(*) AS num_sessions
FROM billable_work
WHERE QUARTER(session_date) = @target_quarter AND doctor_id = '1021'
GROUP BY patient_id;

Extracting from TIME types:

  • HOUR(time_column): Extracts the hour.

  • MINUTE(time_column): Extracts the minute.

  • SECOND(time_column): Extracts the second.

SELECT
    HOUR(session_time) AS Hour,
    MINUTE(session_time) AS Minute,
    SECOND(session_time) AS Second
FROM billable_work
WHERE rec_id = '2462';
+------+--------+--------+
| Hour | Minute | Second |
+------+--------+--------+
|   13 |     03 |     22 | -- Example output
+------+--------+--------+

Using EXTRACT() for DATETIME or TIMESTAMP types: The EXTRACT(unit FROM datetime_column) function extracts a specified unit from a date/time value. Common units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Combined units: YEAR_MONTH, DAY_HOUR, HOUR_MINUTE, etc.

SELECT
    patient_name AS Patient,
    EXTRACT(HOUR FROM appointment) AS Hour,
    EXTRACT(MINUTE FROM appointment) AS Minute
FROM billable_work
JOIN patients ON billable_work.patient_id = patients.patient_id
WHERE doctor_id = '1021'
  AND EXTRACT(MONTH FROM appointment) = 5
  AND EXTRACT(DAY FROM appointment) = 28;

(For details on joining tables, refer to relevant SQL documentation or a guide like "Essential Queries Guide".)

Using a combined unit:

SELECT
    patient_name AS Patient,
    EXTRACT(HOUR_MINUTE FROM appointment) AS AppointmentHM
FROM billable_work
JOIN patients ON billable_work.patient_id = patients.patient_id
WHERE doctor_id = '1021';

Output for HOUR_MINUTE might be like 1303 (for 13:03).

Formatting Dates and Times for Display

Wordier Date Formats:

  • MONTHNAME(date_column): Returns the full name of the month (e.g., 'May').

  • DAYNAME(date_column): Returns the full name of the day (e.g., 'Wednesday').

Example using CONCAT() to combine parts:

SELECT
    patient_name AS Patient,
    CONCAT(
        DAYNAME(appointment), ' - ',
        MONTHNAME(appointment), ' ',
        DAYOFMONTH(appointment), ', ',
        YEAR(appointment)
    ) AS Appointment
FROM billable_work
JOIN patients ON billable_work.patient_id = patients.patient_id
WHERE doctor_id = '1021' AND DATE(appointment) = '2025-05-28'
LIMIT 1;
+-------------------+------------------------------+
| Patient           | Appointment                  |
+-------------------+------------------------------+
| Michael Zabalaoui | Wednesday - May 28, 2025     | -- Example
+-------------------+------------------------------+

Using DATE_FORMAT(datetime_column, format_string): This function provides extensive formatting options. Syntax: DATE_FORMAT(date_value, 'format_options_and_literals').

SELECT
    patient_name AS Patient,
    DATE_FORMAT(appointment, '%W - %M %e, %Y') AS Appointment
FROM billable_work
JOIN patients ON billable_work.patient_id = patients.patient_id
WHERE doctor_id = '1021' AND DATE_FORMAT(appointment, '%c') = 5 -- Filter by month 5 (May)
LIMIT 1;

Common format specifiers:

  • %W: Full weekday name

  • %M: Full month name

  • %e: Day of the month, numeric (1-31)

  • %d: Day of the month, 2 digits (01-31)

  • %Y: Year, 4 digits

  • %y: Year, 2 digits

  • %c: Month, numeric (1-12)

  • %r: Time in 12-hour format (hh:mm:ss AM/PM)

  • %T: Time in 24-hour format (hh:mm:ss)

  • %H: Hour (00-23)

  • %h or %I: Hour (01-12)

  • %i: Minutes (00-59)

  • %s or %S: Seconds (00-59)

  • %p: AM or PM

Example with time:

SELECT
    DATE_FORMAT(appointment, '%W - %M %e, %Y at %r') AS Appointment
FROM billable_work
LIMIT 1;
+-------------------------------------------------+
| Appointment                                     |
+-------------------------------------------------+
| Wednesday - May 28, 2025 at 01:03:22 PM         | -- Example
+-------------------------------------------------+

For a complete list of options, see the official DATE_FORMAT() documentation.

Using TIME_FORMAT(time_column, format_string): Similar to DATE_FORMAT(), but uses only time-related format options.

SELECT
    patient_name AS Patient,
    TIME_FORMAT(appointment, '%l:%i %p') AS AppointmentTime
FROM billable_work
JOIN patients ON billable_work.patient_id = patients.patient_id
WHERE doctor_id = '1021'
  AND DATE(appointment) = CURDATE();
+-------------------+-----------------+
| Patient           | AppointmentTime |
+-------------------+-----------------+
| Michael Zabalaoui |     1:03 PM     | -- Example
+-------------------+-----------------+

Here, %l is hour (1-12) and %p adds AM/PM.

Tips for Effective Date/Time Handling

  • Use Appropriate Data Types: Choose temporal data types (DATE, TIME, DATETIME, TIMESTAMP, YEAR) over string types for date/time data to leverage built-in functions and ensure data integrity.

  • Leverage Built-in Functions: MariaDB offers a rich set of functions for date/time manipulation. Use them within your SQL queries to avoid complex logic in your application code.

  • Test Queries: When dealing with complex date/time logic or formatting, test your SQL statements directly in a MariaDB client (like the mariadb command-line tool) to verify results before embedding them in applications.

  • Be Aware of Time Zones: TIMESTAMP values are stored in UTC and converted to/from the session's time zone, while DATETIME values are stored "as is" without time zone conversion. Understand how your server and session time zones are configured if working with data across different regions. (Time zone handling is a more advanced topic not fully covered here).

This page is licensed: CC BY-SA / Gnu FDL

Importing Data Guide

Bulk Data Importing Guide

This guide introduces methods and tools for efficiently importing bulk data into MariaDB. Learn to prepare your data, use LOAD DATA INFILE and the mariadb-import utility, handle common data import challenges, and manage potential constraints.

Preparing Your Data File

The most common approach for bulk importing is to use a delimited text file.

  1. Export Source Data: Load your data in its original software (e.g., MS Excel, MS Access) and export it as a delimited text file.

    • Delimiter: Use a character not commonly found in your data to separate fields. The pipe symbol (|) is often a good choice. Tab () is also common.

    • Record Separator: Use line feeds () to separate records.

  2. Align Columns (Recommended for Simplicity): Ideally, the order and number of columns in your text file should match the target MariaDB table.

    • If the table has extra columns not in your file, they will be filled with their default values (or NULL).

    • If your file has extra columns not in the table, you'll need to specify which file columns to load (see "Mapping File Columns to Table Columns" below) or remove them from the text file.

  3. Clean Data: Remove any header rows or footer information from the text file unless you plan to skip them during import (see IGNORE N LINES below).

  4. Upload File: Transfer the text file to a location accessible by the MariaDB server.

    • Use ASCII mode for FTP transfers to ensure correct line endings.

    • For security, upload data files to non-public directories on the server.

Using LOAD DATA INFILE

The LOAD DATA INFILE statement is a powerful SQL command for importing data from text files. Ensure the MariaDB user has the FILE privilege.

Basic Syntax:

First, connect to MariaDB using the mariadb client and select your target database:

USE sales_dept; -- Or your database name

Then, load the data:

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|';
  • Replace /tmp/prospects.txt with the actual path to your data file on the server. On Windows, paths use forward slashes (e.g., 'C:/tmp/prospects.txt').

  • prospect_contact is the target table. You can also specify database_name.table_name.

  • FIELDS TERMINATED BY '|' specifies the field delimiter. For tab-delimited, use '\t'.

  • The default record delimiter is the line feed ().

Specifying Line Terminators and Enclosing Characters:

If your file has custom line endings or fields enclosed by characters (e.g., quotes):

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|' ENCLOSED BY '"'
LINES STARTING BY '"' TERMINATED BY '"\r\n';
  • ENCLOSED BY '"': Specifies that fields are enclosed in double quotes.

  • LINES STARTING BY '"': Indicates each line starts with a double quote.

  • TERMINATED BY '"\r\n': Indicates each line ends with a double quote followed by a Windows-style carriage return and line feed.

  • To specify a single quote as an enclosing character, you can escape it or put it within double quotes: ENCLOSED BY '\'' or ENCLOSED BY "'".

Handling Duplicate Rows

When importing data, you might encounter records with primary key values that already exist in the target table.

  • Default Behavior: MariaDB attempts to import all rows. If duplicates are found and the table has a primary or unique key that would be violated, an error occurs, and subsequent rows may not be imported.

  • REPLACE: If you want new data from the file to overwrite existing rows with the same primary key:SQL

    LOAD DATA INFILE '/tmp/prospects.txt'
    REPLACE INTO TABLE prospect_contact
    FIELDS TERMINATED BY '|';
  • IGNORE: If you want to keep existing rows and skip importing duplicate records from the file:SQL

    LOAD DATA INFILE '/tmp/prospects.txt'
    IGNORE INTO TABLE prospect_contact
    FIELDS TERMINATED BY '|';

Importing into Live Tables

If the target table is actively being used, importing data can lock it, preventing access.

  • LOW_PRIORITY: To allow other users to read from the table while the load operation is pending, use LOW_PRIORITY. The load will wait until no other clients are reading the table.SQL

    LOAD DATA LOW_PRIORITY INFILE '/tmp/prospects.txt'
    INTO TABLE prospect_contact
    FIELDS TERMINATED BY '|';

    Without LOW_PRIORITY or CONCURRENT, the table is typically locked for the duration of the import.

Advanced LOAD DATA INFILE Options

Binary Line Endings:

If your file has Windows CRLF line endings and was uploaded in binary mode, you can specify the hexadecimal value:

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|'
LINES TERMINATED BY 0x0d0a; -- 0x0d is carriage return, 0x0a is line feed

Note: No quotes around the hexadecimal value.

Skipping Header Lines:

To ignore a certain number of lines at the beginning of the file (e.g., a header row):

SQL

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|'
IGNORE 1 LINES; -- Skips the first line

Handling Escaped Characters:

If fields are enclosed by quotes and contain embedded quotes that are escaped by a special character (e.g., # instead of the default backslash \):

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|'
    ENCLOSED BY '"'
    ESCAPED BY '#'
IGNORE 1 LINES;

Mapping File Columns to Table Columns:

If the order or number of columns in your text file differs from the target table, you can specify the column mapping at the end of the LOAD DATA INFILE statement.

Assume prospect_contact table has: (row_id INT AUTO_INCREMENT, name_first VARCHAR, name_last VARCHAR, telephone VARCHAR).

And prospects.txt has columns in order: Last Name, First Name, Telephone.

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|' -- Or your actual delimiter, e.g., 0x09 for tab
ENCLOSED BY '"'
ESCAPED BY '#'
IGNORE 1 LINES
(name_last, name_first, telephone);
  • MariaDB will map data from the file's first column to name_last, second to name_first, and third to telephone.

  • The row_id column in the table, not being specified in the list, will be filled by its default mechanism (e.g., AUTO_INCREMENT or DEFAULT value, or NULL).

Using the mariadb-import Utility

The mariadb-import utility (known as mysqlimport before MariaDB 10.5) is a command-line program that acts as a wrapper for LOAD DATA INFILE. It's useful for scripting imports.

Syntax:

mariadb-import --user='your_username' --password='your_password' \
    --fields-terminated-by='|' --lines-terminated-by='\r\n' \
    --replace --low-priority --fields-enclosed-by='"' \
    --fields-escaped-by='#' --ignore-lines='1' --verbose \
    --columns='name_last,name_first,telephone' \
    sales_dept '/tmp/prospect_contact.txt'
  • This command is run from the system shell, not within the mariadb client.

  • Lines are continued with \ for readability here; it can be a single line.

  • --password: If the password value is omitted, you'll be prompted.

  • The database name (sales_dept) is specified before the file path.

  • File Naming: mariadb-import expects the text file's name (without extension) to match the target table name (e.g., prospect_contact.txt for table prospect_contact). If your file is prospects.txt and table is prospect_contact, you might need to rename the file or import into a temporary table named prospects first.

  • --verbose: Shows progress information.

  • You can list multiple text files to import into correspondingly named tables.

Dealing with Web Hosting Restraints

Some web hosts disable LOAD DATA INFILE or mariadb-import for security reasons. A workaround involves using mariadb-dump:

  1. Prepare Data Locally: Prepare your delimited text file (e.g., prospects.txt).

  2. Local Import: If you have a local MariaDB server, import the text file into a local table (e.g., local_db.prospect_contact) using LOAD DATA INFILE as described above.

  3. Local Export with mariadb-dump: Export the data from your local table into an SQL file containing INSERT statements.

    mariadb-dump --user='local_user' --password='local_pass' --no-create-info local_db prospect_contact > /tmp/prospects.sql
    • --no-create-info (or -t): Prevents the CREATE TABLE statement from being included, outputting only INSERT statements. This is useful if the table already exists on the remote server.

  4. Upload SQL File: Upload the generated .sql file (e.g., prospects.sql) to your web server (in ASCII mode).

  5. Remote Import of SQL File: Log into your remote server's shell and import the SQL file using the mariadb client:

    mariadb --user='remote_user' --password='remote_pass' remote_sales_dept < /tmp/prospects.sql

Handling Duplicates with mariadb-dump Output:

mariadb-dump does not have a REPLACE flag like LOAD DATA INFILE. If the target table might contain duplicates:

  • Open the .sql file generated by mariadb-dump in a text editor.

  • Perform a search and replace operation to change all occurrences of INSERT INTO to REPLACE INTO. The syntax for INSERT and REPLACE (for the data values part) is similar enough that this often works. Test thoroughly.

Key Considerations

  • Flexibility: MariaDB provides powerful and flexible options for data importing. Understanding the details of LOAD DATA INFILE and mariadb-import can save significant effort.

  • Data Validation: While these tools are efficient for bulk loading, they may not perform extensive data validation beyond basic type compatibility. Cleanse and validate your data as much as possible before importing.

  • Character Sets: Ensure your data file's character set is compatible with the target table's character set to avoid data corruption. You can specify character sets in LOAD DATA INFILE.

  • Other Tools/Methods: For very complex transformations or ETL (Extract, Transform, Load) processes, dedicated ETL tools or scripting languages (e.g., Python, Perl with database modules) might be more suitable, though they are beyond the scope of this guide.

This page is licensed: CC BY-SA / Gnu FDL

Essentials of an Index Guide

An index on Last_Name organizes the records by surname, enhancing search efficiency without altering the original table order. Indices can be created for any column, such as ID or first name, to enable quick lookups based on different criteria.

Imagine you've created a table with the following rows:

+----+------------+-----------+-------------------------+---------------------------+--------------+
| ID | First_Name | Last_Name | Position                | Home_Address              | Home_Phone   |
+----+------------+-----------+-------------------------+---------------------------+--------------+
|  1 | Mustapha   | Mond      | Chief Executive Officer | 692 Promiscuous Plaza     | 326-555-3492 |
|  2 | Henry      | Foster    | Store Manager           | 314 Savage Circle         | 326-555-3847 |
|  3 | Bernard    | Marx      | Cashier                 | 1240 Ambient Avenue       | 326-555-8456 |
|  4 | Lenina     | Crowne    | Cashier                 | 281 Bumblepuppy Boulevard | 328-555-2349 |
|  5 | Fanny      | Crowne    | Restocker               | 1023 Bokanovsky Lane      | 326-555-6329 |
|  6 | Helmholtz  | Watson    | Janitor                 | 944 Soma Court            | 329-555-2478 |
+----+------------+-----------+-------------------------+---------------------------+--------------+

Now, imagine you've been asked to return the home phone of Fanny Crowne. Without indexes, the only way to do it is to go through every row until you find the matching first name and surname. Now imagine there are millions of records and you can see that, even for a speedy database server, this is highly inefficient.

The answer is to sort the records. If they were stored in alphabetical order by surname, even a human could quickly find a record amongst a large number. But we can't sort the entire record by surname. What if we want to also look a record by ID, or by first name? The answer is to create separate indexes for each column we wish to sort by. An index simply contains the sorted data (such as surname), and a link to the original record.

For example, an index on Last_Name:

+-----------+----+
| Last_Name | ID |
+-----------+----+
| Crowne    |  4 |
| Crowne    |  5 |
| Foster    |  2 |
| Marx      |  3 |
| Mond      |  1 |
| Watson    |  6 |
+-----------+----+

and an index on Position

+-------------------------+----+
| Position                | ID |
+-------------------------+----+
| Cashier                 |  3 |
| Cashier                 |  4 |
| Chief Executive Officer |  1 |
| Janitor                 |  6 |
| Restocker               |  5 |
| Store Manager           |  2 |
+-------------------------+----+

would allow you to quickly find the phone numbers of all the cashiers, or the phone number of the employee with the surname Marx, very quickly.

Where possible, you should create an index for each column that you search for records by, to avoid having the server read every row of a table.

See CREATE INDEX and Getting Started with Indexes for more information.

This page is licensed: CC BY-SA / Gnu FDL

Getting Started with Indexes Guide

Indexing Guide

This guide explains the different types of indexes in MariaDB, their characteristics, and how they are used. Learn to create and manage Primary Keys, Unique Indexes, and Plain Indexes, along with key considerations for choosing and maintaining effective indexes for optimal query performance.

In MariaDB, the terms KEY and INDEX are generally used interchangeably in SQL statements.

Index Types Overview

There are four main kinds of indexes:

  • Primary Keys: Unique and not NULL.

  • Unique Indexes: Must be unique but can contain NULL values.

  • Plain Indexes (or Regular Indexes): Not necessarily unique.

  • Full-Text Indexes: Used for full-text searching capabilities.

Primary Key

A primary key uniquely identifies each record in a table. Its values must be unique, and it cannot contain NULL values. Each table can have only one primary key.

InnoDB Considerations:

  • In InnoDB tables, the primary key is included as a suffix in all other indexes. Therefore, keeping the primary key compact (e.g., using an appropriate integer type) is important for performance and storage efficiency.

  • If a table has no explicitly defined primary key and no UNIQUE indexes, InnoDB automatically creates an invisible 6-byte clustered index.

Using AUTO_INCREMENT: The AUTO_INCREMENT attribute is commonly used with numeric primary keys to automatically generate a unique ID for each new row.

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`ID`)
);

Note: The column defined as a primary key (or part of it) must be explicitly declared as NOT NULL.

Adding a Primary Key to an Existing Table: Use ALTER TABLE. You cannot create a primary key with CREATE INDEX.

ALTER TABLE Employees ADD PRIMARY KEY(ID);

Finding Tables Without Primary Keys: This query uses the information_schema database to find tables lacking primary keys:

SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.TABLES AS t
LEFT JOIN information_schema.KEY_COLUMN_USAGE AS c
ON t.TABLE_SCHEMA = c.CONSTRAINT_SCHEMA
   AND t.TABLE_NAME = c.TABLE_NAME
   AND c.CONSTRAINT_NAME = 'PRIMARY'
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
  AND c.CONSTRAINT_NAME IS NULL;

Unique Index

A unique index ensures that all values in the indexed column (or combination of columns) are unique. However, unlike a primary key, columns in a unique index can store NULL values. Each key value uniquely identifies a row, but not every row needs to be represented if NULLs are allowed.

Behavior (MariaDB 10.5+):

  • If the index type is not specified, UNIQUE typically creates a BTREE index, usable by the optimizer.

  • If a key exceeds the maximum length for the storage engine and the engine supports long unique indexes, a HASH key might be created to enforce uniqueness.

Creating Unique Indexes: During table creation:

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `Employee_Code` VARCHAR(25) NOT NULL,
  `First_Name` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_EmpCode` (`Employee_Code`) -- Naming the unique key is good practice
);

After table creation using ALTER TABLE:

ALTER TABLE Employees ADD UNIQUE `UK_HomePhone` (`Home_Phone`);

After table creation using CREATE UNIQUE INDEX:

CREATE UNIQUE INDEX `IX_Position` ON Employees(Position);

Multi-Column Unique Indexes: An index can span multiple columns. MariaDB can use the leftmost part(s) of such an index if it cannot use the whole index (except for HASH indexes).

CREATE TABLE t1 (a INT NOT NULL, b INT, UNIQUE (a,b));
INSERT INTO t1 VALUES (1,1), (2,2);
INSERT INTO t1 VALUES (2,1); -- Valid: (2,1) is unique, though '2' in 'a' and '1' in 'b' are not individually unique here.
SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 2 |    2 |
+---+------+

NULL Values in Unique Indexes: A UNIQUE constraint allows multiple NULL values because in SQL, NULL is never equal to another NULL.

INSERT INTO t1 VALUES (3,NULL), (3, NULL); -- Both rows are inserted
SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 2 |    2 |
| 3 | NULL |
| 3 | NULL |
+---+------+

Verification:

SELECT (3, NULL) = (3, NULL);
+-----------------------+
| (3, NULL) = (3, NULL) |
+-----------------------+
|                     0 | -- 0 means false
+-----------------------+

Conditional Uniqueness with Virtual Columns: You can enforce uniqueness over a subset of rows using unique indexes on virtual columns. This example ensures user_name is unique for 'Active' or 'On-Hold' users, but allows duplicate names for 'Deleted' users:

CREATE TABLE Table_1 (
  user_name VARCHAR(10),
  status ENUM('Active', 'On-Hold', 'Deleted'),
  del CHAR(0) AS (IF(status IN ('Active', 'On-Hold'), '', NULL)) PERSISTENT,
  UNIQUE(user_name, del)
);

Trailing Pad Characters: If a unique index is on a column where trailing pad characters are stripped or ignored (e.g., CHAR vs VARCHAR behavior), inserts where values differ only by the number of trailing pad characters can result in duplicate-key errors.

Long Keys and HASH Indexes (MariaDB 10.5+): For engines like InnoDB, UNIQUE can be used with various column types and numbers. If a key's length exceeds the engine's maximum, a HASH key may be created.

-- Example table definition (simplified for brevity)
CREATE TABLE t_long_keys (
  a INT PRIMARY KEY,
  b BLOB,
  c1 VARCHAR(1000),
  UNIQUE KEY `uk_b` (b),
  UNIQUE KEY `uk_c1` (c1)
) ENGINE=InnoDB;

-- SHOW CREATE TABLE might reveal 'USING HASH' for uk_b or uk_c1 if they exceed length limits
SHOW CREATE TABLE t_long_keys\G

Example output snippet showing USING HASH:

...
  UNIQUE KEY `uk_b` (`b`) USING HASH,
...

Plain Indexes (Regular Indexes)

Plain indexes do not enforce uniqueness; they are primarily used to speed up data retrieval.

CREATE TABLE t2 (a INT NOT NULL, b INT, INDEX `idx_a_b` (a,b));
INSERT INTO t2 VALUES (1,1), (2,2), (2,2); -- Duplicate (2,2) is allowed
SELECT * FROM t2;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
| 2 |    2 |
+---+------+

Full-Text Indexes

Full-text indexes are used for performing full-text searches on text data. For details, see the Full-Text Indexes documentation.

Choosing Indexes

  • Index for Queries: Add indexes that match the WHERE clauses, JOIN conditions, and ORDER BY clauses of your application's queries.

  • Avoid Over-Indexing: Extra indexes consume storage and can slow down INSERT, UPDATE, and DELETE operations.

  • Impact of Table Size: Indexes provide more significant speed-ups on large tables (larger than buffer sizes) than on very small tables.

  • Use EXPLAIN: Analyze your queries with the EXPLAIN statement to determine if indexes are being used effectively and identify columns that might benefit from indexing.

  • LIKE '%word%': Queries using a leading wildcard in a LIKE clause (e.g., LIKE '%word%') typically cannot use standard BTREE indexes effectively and may result in full table scans unless a full-text index is used.

  • Delayed Writes: For tables with many reads and writes, consider storage engine options or server configurations related to delayed writes to potentially improve performance by batching disk I/O. (This is an advanced topic.)

  • Creating Indexes on Existing Tables: Use CREATE INDEX index_name ON table_name (column_list);

  • Large Tables: For very large tables, it's often faster to load data into the table first and then create indexes, rather than creating indexes on an empty table and then loading data.

Viewing Indexes

  • SHOW INDEX FROM table_name;: Displays information about all indexes on a table.SQL

    SHOW INDEX FROM Employees;
  • SHOW CREATE TABLE table_name;: Shows the CREATE TABLE statement, which includes definitions for all indexes.SQL

    SHOW CREATE TABLE Employees\G

When to Remove an Index

Remove an index if:

  • It is rarely or never used. Unused indexes still incur overhead during data modification operations.

  • Identifying Unused Indexes:

    • If user statistics are enabled, query the information_schema.INDEX_STATISTICS table.

    • If the slow query log is enabled and the log_queries_not_using_indexes server system variable is ON, queries performing full table scans will be logged, which can indicate missing or ineffective indexes.

This page is licensed: CC BY-SA / Gnu FDL

Joining Tables with JOIN Clauses Guide

Basic Joins Guide

This guide offers a simple, hands-on introduction to three basic JOIN types in MariaDB: INNER JOIN, CROSS JOIN, and LEFT JOIN. Use these examples to understand how different joins combine data from multiple tables based on specified conditions.

Setup: Example Tables and Data

First, create and populate two simple tables, t1 and t2, to use in the JOIN examples:

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (2), (4);

JOIN Examples and Output

Below are examples of different JOIN types using the tables t1 and t2.

INNER JOIN

An INNER JOIN produces a result set containing only rows that have a match in both tables for the specified join condition(s).

SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;

Output:

+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

Explanation: Only the row where t1.a (value 2) matches t2.b (value 2) is returned.

CROSS JOIN

A CROSS JOIN produces a result set in which every row from the first table is joined to every row in the second table. This is also known as a Cartesian product.

SELECT * FROM t1 CROSS JOIN t2;

Output:

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
+------+------+
6 rows in set (0.00 sec)

Explanation: Each of the 3 rows in t1 is combined with each of the 2 rows in t2, resulting in 3 * 2 = 6 rows. Note: In MariaDB, the CROSS keyword can often be omitted if no ON clause is present (e.g., SELECT * FROM t1 JOIN t2; or SELECT * FROM t1, t2; would also produce a Cartesian product).

LEFT JOIN (t1 LEFT JOIN t2)

A LEFT JOIN (or LEFT OUTER JOIN) produces a result set with all rows from the "left" table (t1 in this case). If a match is found in the "right" table (t2), the corresponding columns from the right table are included. If no match is found, these columns are filled with NULL.

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b;

Output:

+------+------+
| a    | b    |
+------+------+
|    1 | NULL |
|    2 |    2 |
|    3 | NULL |
+------+------+
3 rows in set (0.00 sec)

Explanation: All rows from t1 are present. For t1.a = 1 and t1.a = 3, there are no matching t2.b values, so b is NULL. For t1.a = 2, a match is found (t2.b = 2), so b is 2.

LEFT JOIN (t2 LEFT JOIN t1) - Simulating a RIGHT JOIN

This example uses a LEFT JOIN but with t2 as the left table. This effectively demonstrates how a RIGHT JOIN would behave if t1 were the left table and t2 the right. A RIGHT JOIN includes all rows from the "right" table and NULLs for non-matching "left" table columns.

SELECT * FROM t2 LEFT JOIN t1 ON t1.a = t2.b;

Output:

+------+------+
| b    | a    |
+------+------+
|    2 |    2 |
|    4 | NULL |
+------+------+
2 rows in set (0.00 sec)

Explanation: All rows from t2 are present. For t2.b = 2, a match is found (t1.a = 2), so a is 2. For t2.b = 4, there is no matching t1.a value, so a is NULL.

Older (Implicit) JOIN Syntax

The first two SELECT statements (INNER JOIN and CROSS JOIN) are sometimes written using an older, implicit join syntax:

  • Implicit INNER JOIN:

    SELECT * FROM t1, t2 WHERE t1.a = t2.b;

    This is equivalent to SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;.

  • Implicit CROSS JOIN (Cartesian Product):

    SELECT * FROM t1, t2;

    This is equivalent to SELECT * FROM t1 CROSS JOIN t2;.

While this syntax works, the explicit JOIN syntax (INNER JOIN, LEFT JOIN, etc.) with an ON clause is generally preferred for clarity and to better distinguish join conditions from filtering conditions (WHERE clause).

Understanding JOIN Types Summary

  • INNER JOIN: Returns rows only when there is a match in both tables based on the join condition.

  • CROSS JOIN: Returns the Cartesian product of the two tables (all possible combinations of rows).

  • LEFT JOIN (Outer Join): Returns all rows from the left table, and the matched rows from the right table. If there is no match, NULL is returned for columns from the right table.

  • RIGHT JOIN (Outer Join): Returns all rows from the right table, and the matched rows from the left table. If there is no match, NULL is returned for columns from the left table. (The example SELECT * FROM t2 LEFT JOIN t1 ... shows this behavior from t1's perspective).

Joining Multiple Tables

JOIN clauses can be concatenated (chained) to retrieve results from three or more tables by progressively joining them.

This page is licensed: CC BY-SA / Gnu FDL

Configuring MariaDB for Remote Client Access Guide

Remote Access Configuration Guide

This guide explains how to configure your MariaDB server to accept connections from remote hosts. Learn to adjust crucial network settings like bind-address, grant appropriate user privileges for remote connections, and configure essential firewall rules.

Understanding Key Network Directives

Two main configuration directives control MariaDB's network accessibility:

  • skip-networking: If this directive is enabled, MariaDB will not listen for TCP/IP connections at all. All interaction must be through local mechanisms like Unix sockets or named pipes.

  • bind-address: This directive specifies the IP address the server listens on.

    • By default, for security, many MariaDB packages bind to 127.0.0.1 (localhost). This means the server will only accept connections originating from the server machine itself via the loopback interface. Remote connections will fail.

    • If bind-address is set to 127.0.0.1, attempting to connect from another host, or even from the same host using a non-loopback IP address, will result in errors like:

      ERROR 2002 (HY000): Can't connect to MySQL server on 'myhost' (115)

      A telnet myhost 3306 test would likely show "Connection refused."

    • To allow connections from other hosts, you must either comment out the bind-address directive (making MariaDB listen on all available network interfaces, i.e., 0.0.0.0 for IPv4), or set it to a specific public IP address of the server.

    • MariaDB 10.11 and later: bind-address can accept multiple comma-separated IP addresses, allowing the server to listen on specific interfaces while excluding others.

Connecting via localhost typically works even if bind-address is 127.0.0.1 (using the loopback interface):

./client/mariadb --host=localhost --protocol=tcp --port=3306 test

Locating the MariaDB Configuration File

To change these network settings, you need to edit MariaDB's configuration file (often named my.cnf or my.ini).

  • See Configuring MariaDB with my.cnf for comprehensive details.

  • Common Locations:

    • /etc/my.cnf (Unix/Linux/BSD)

    • /etc/mysql/my.cnf (Common on Debian/Ubuntu)

    • $MYSQL_HOME/my.cnf (Unix/Linux/BSD, where $MYSQL_HOME is MariaDB's base directory)

    • SYSCONFDIR/my.cnf (Compile-time specified system configuration directory)

    • DATADIR\my.ini (Windows, in the data directory)

    • ~/.my.cnf (User-specific configuration file)

  • Identifying Loaded Files: To see which configuration files your mariadbd server instance reads and in what order, execute:

    Bash

    mariadbd --help --verbose

    Look for a line similar to: Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Modifying the Configuration File for Remote Access

  1. Open the File: Use a text editor to open the primary configuration file identified (e.g., /etc/mysql/my.cnf).

  2. Locate [mysqld] Section: Find the section starting with [mysqld].

  3. Adjust Directives:

    • If skip-networking is present and enabled (not commented out with #), comment it out or set it to 0:Ini, TOML

      #skip-networking

      orIni, TOML

      skip-networking=0
    • If bind-address = 127.0.0.1 (or another loopback/specific IP that's too restrictive) is present:

      • To listen on all available IPv4 interfaces: Comment it out entirely (#bind-address = 127.0.0.1) or set bind-address = 0.0.0.0.

      • To listen on a specific public IP address of your server: bind-address = <your_server_public_ip>.

      • Alternatively, to effectively disable binding to a specific address and listen on all, you can add skip-bind-address. Example changes:

      [mysqld]
      ...
      #skip-networking
      #bind-address = 127.0.0.1
      ...

      Or, to be explicit for listening on all interfaces if bind-address was previously restrictive:

      [mysqld]
      bind-address = 0.0.0.0
  4. Save and Restart: Save the configuration file and restart the MariaDB server service.

    • See Starting and Stopping MariaDB for instructions.

  5. Verify Settings (Optional): You can check the options mariadbd is effectively using by running:

    ./sql/mariadbd --print-defaults  # Adjust path to mariadbd if necessary

    Look for the effective bind-address value or the absence of skip-networking. If multiple [mysqld] sections or skip-bind-address are used, the last specified prevailing value is typically what counts.

Granting User Privileges for Remote Connections

Configuring the server to listen for remote connections is only the first step. You must also grant privileges to user accounts to connect from specific remote hosts. MariaDB user accounts are defined as 'username'@'hostname'.

  1. Connect to MariaDB:

    mariadb -u root -p
  2. View Existing Remote Users (Optional):SQL

    SELECT User, Host FROM mysql.user 
    WHERE Host <> 'localhost' AND Host <> '127.0.0.1' AND Host <> '::1';
  3. Grant Privileges: Use the GRANT statement to allow a user to connect from a remote host or a range of hosts.

    • Syntax Elements:

      • Privileges (e.g., ALL PRIVILEGES, SELECT, INSERT, UPDATE)

      • Database/tables (e.g., database_name.* for all tables in a database, *.* for all databases)

      • Username

      • Host (IP address, hostname, or subnet with wildcards like %)

      • Password (using IDENTIFIED BY 'password')

    • Example: Grant root-like access from a specific LAN subnet: It's highly discouraged to allow root access from all hosts ('root'@'%') directly to the internet. Instead, restrict it to trusted networks if necessary.

      SQL

      GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%'
        IDENTIFIED BY 'my-very-strong-password' WITH GRANT OPTION;
      FLUSH PRIVILEGES;

      This allows the root user to connect from any IP address in the 192.168.100.x subnet. Replace 'my-very-strong-password' with a strong, unique password.

    • For creating less privileged users or more granular permissions, see the GRANT documentation.

Configuring Your Firewall

Even if MariaDB is configured for remote access, a firewall on the server (software or hardware) might block incoming connections on MariaDB's port (default is 3306).

  • RHEL/CentOS 7 Example (using firewall-cmd):

    sudo firewall-cmd --add-port=3306/tcp --permanent
    sudo firewall-cmd --reload

    The first command adds the rule, the second makes it persist after reboots and applies the changes. Consult your OS/firewall documentation for specific commands.

Important Considerations and Reverting Changes

  • Security: Opening MariaDB to remote connections, especially to the internet, increases security risks. Always use strong passwords, grant minimal necessary privileges, and restrict host access as much as possible. Consider using TLS/SSL for encrypted connections (see Secure Connections Overview).

  • Reverting: To disable remote access and revert to a more secure local-only setup:

    1. Edit your MariaDB configuration file.

    2. Ensure skip-networking is not enabled (or is 0).

    3. Set bind-address = 127.0.0.1 explicitly, or remove any skip-bind-address directive if you previously added it to listen on all interfaces. The goal is to have bind-address=127.0.0.1 as the effective setting.

    4. Restart the MariaDB server.

    5. Review and revoke any unnecessary remote GRANT privileges.

The initial version of this article was copied, with permission, from Remote_Clients_Cannot_Connect on 2012-10-30.

This page is licensed: CC BY-SA / Gnu FDL

Getting Data Guide

SELECT Statement Guide

This guide explains how to retrieve data from MariaDB using the SELECT statement, progressing from basic syntax to more involved queries. Learn to select specific columns, limit results, filter with WHERE, sort with ORDER BY, join tables, and use various helpful options and functions.

Setup: Creating and Populating Example Tables

To follow the examples, first create and populate the books and authors tables:

CREATE OR REPLACE TABLE books (
    isbn CHAR(20) PRIMARY KEY,
    title VARCHAR(50),
    author_id INT,
    publisher_id INT,
    year_pub CHAR(4),
    description TEXT
);

CREATE OR REPLACE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    name_last VARCHAR(50),
    name_first VARCHAR(50),
    country VARCHAR(50)
);

INSERT INTO authors (name_last, name_first, country) VALUES
  ('Kafka', 'Franz', 'Czech Republic'),
  ('Dostoevsky', 'Fyodor', 'Russia');

INSERT INTO books (title, author_id, isbn, year_pub) VALUES
 ('The Trial', 1, '0805210407', '1995'),
 ('The Metamorphosis', 1, '0553213695', '1995'),
 ('America', 2, '0805210644', '1995'), -- Note: Original data had author_id 2 for 'America', Dostoevsky is author_id 2.
 ('Brothers Karamozov', 2, '0553212168', ''),
 ('Crime & Punishment', 2, '0679420290', ''),
 ('Crime & Punishment', 2, '0553211757', ''),
 ('Idiot', 2, '0192834118', ''),
 ('Notes from Underground', 2, '067973452X', '');

Basic Data Retrieval

Selecting All Columns:

Use * to select all columns from a table.

SELECT * FROM books;

Output (example):

+------------+------------------------+-----------+--------------+----------+-------------+
| isbn       | title                  | author_id | publisher_id | year_pub | description |
+------------+------------------------+-----------+--------------+----------+-------------+
| 0192834118 | Idiot                  |         2 |         NULL |          | NULL        |
| 0553211757 | Crime & Punishment     |         2 |         NULL |          | NULL        |
... (other rows)
| 0805210644 | America                |         2 |         NULL | 1995     | NULL        |
+------------+------------------------+-----------+--------------+----------+-------------+
8 rows in set (0.001 sec)

Selecting Specific Columns:

List the column names separated by commas.

SELECT isbn, title, author_id FROM books;

Output (example):

+------------+------------------------+-----------+
| isbn       | title                  | author_id |
+------------+------------------------+-----------+
| 0192834118 | Idiot                  |         2 |
| 0553211757 | Crime & Punishment     |         2 |
... (other rows)
+------------+------------------------+-----------+
8 rows in set (0.001 sec)

Limiting the Number of Rows with LIMIT:

  • To get the first N rows:

    SELECT isbn, title, author_id FROM books LIMIT 5;

    Output (example):

    +------------+--------------------+-----------+
    | isbn       | title              | author_id |
    +------------+--------------------+-----------+
    | 0192834118 | Idiot              |         2 |
    | 0553211757 | Crime & Punishment |         2 |
    | 0553212168 | Brothers Karamozov |         2 |
    | 0553213695 | The Metamorphosis  |         1 |
    | 0679420290 | Crime & Punishment |         2 |
    +------------+--------------------+-----------+
    5 rows in set (0.001 sec)
  • To get N rows starting from an offset (offset is 0-indexed):SQL

    SELECT isbn, title, author_id FROM books LIMIT 5, 10; -- Skip 5 rows, show next 10 (or fewer if less remain)

    Output (example, assuming only 3 more rows exist after offset 5):

    +------------+------------------------+-----------+
    | isbn       | title                  | author_id |
    +------------+------------------------+-----------+
    | 067973452X | Notes from Underground |         2 |
    | 0805210407 | The Trial              |         1 |
    | 0805210644 | America                |         2 |
    +------------+------------------------+-----------+
    3 rows in set (0.001 sec)

Filtering and Ordering Results

Filtering with WHERE:

Use the WHERE clause to specify conditions for row selection.

SELECT isbn, title
FROM books
WHERE author_id = 2
LIMIT 5;

Output (example):

+------------+------------------------+
| isbn       | title                  |
+------------+------------------------+
| 0192834118 | Idiot                  |
| 0553211757 | Crime & Punishment     |
| 0553212168 | Brothers Karamozov     |
| 0679420290 | Crime & Punishment     |
| 067973452X | Notes from Underground |
+------------+------------------------+
5 rows in set (0.000 sec)

Ordering with ORDER BY:

Use ORDER BY column_name [ASC|DESC] to sort the result set.

SELECT isbn, title
FROM books
WHERE author_id = 2
ORDER BY title ASC
LIMIT 5;

Output (example):

+------------+--------------------+
| isbn       | title              |
+------------+--------------------+
| 0805210644 | America            |
| 0553212168 | Brothers Karamozov |
| 0553211757 | Crime & Punishment |
| 0679420290 | Crime & Punishment |
| 0192834118 | Idiot              |
+------------+--------------------+
5 rows in set (0.001 sec)
  • ASC (ascending) is the default order. DESC is for descending order.

  • You can order by multiple columns: ORDER BY col1 ASC, col2 DESC.

  • Clause Order: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT .... MariaDB generally processes WHERE, then ORDER BY, then LIMIT.

Working with Multiple Tables (JOINs) and Functions

Joining Tables:

Use JOIN to combine rows from two or more tables based on a related column.

SELECT isbn, title, CONCAT(name_first, ' ', name_last) AS author
FROM books
JOIN authors USING (author_id) -- Assumes 'author_id' column exists in both tables
WHERE name_last = 'Dostoevsky'
ORDER BY title ASC
LIMIT 5;

Output (example):

+------------+--------------------+-------------------+
| isbn       | title              | author            |
+------------+--------------------+-------------------+
| 0805210644 | America            | Fyodor Dostoevsky |
| 0553212168 | Brothers Karamozov | Fyodor Dostoevsky |
| 0553211757 | Crime & Punishment | Fyodor Dostoevsky |
| 0679420290 | Crime & Punishment | Fyodor Dostoevsky |
| 0192834118 | Idiot              | Fyodor Dostoevsky |
+------------+--------------------+-------------------+
5 rows in set (0.00 sec)
  • Alternative JOIN syntax: ... JOIN authors ON books.author_id = authors.author_id .... For more on joins, see the JOIN Syntax documentation or a "Basic Joins Guide".

  • CONCAT(str1, str2, ...): Concatenates strings.

  • AS alias_name: Assigns an alias to an output column.

Pattern Matching with LIKE:

Use LIKE in the WHERE clause for pattern matching. % is a wildcard for zero or more characters.

SELECT isbn, title, CONCAT(name_first, ' ', name_last) AS author
FROM books
JOIN authors USING (author_id)
WHERE name_last LIKE 'Dostoevsk%'
ORDER BY title ASC
LIMIT 5;

Output (example, same as above if only Dostoevsky matches):

+------------+--------------------+-------------------+
| isbn       | title              | author            |
+------------+--------------------+-------------------+
| 0805210644 | America            | Fyodor Dostoevsky |
| 0553212168 | Brothers Karamozov | Fyodor Dostoevsky |
| 0553211757 | Crime & Punishment | Fyodor Dostoevsky |
| 0679420290 | Crime & Punishment | Fyodor Dostoevsky |
| 0192834118 | Idiot              | Fyodor Dostoevsky |
+------------+--------------------+-------------------+
5 rows in set (0.001 sec)

SELECT Statement Modifiers

Place these modifiers immediately after the SELECT keyword.

  • ALL vs DISTINCT:

    • ALL (default): Returns all rows that meet the criteria.

    • DISTINCT: Returns only unique rows for the selected columns. If multiple identical rows are found for the specified columns, only the first one is displayed.

    SELECT DISTINCT title
    FROM books
    JOIN authors USING (author_id)
    WHERE name_last = 'Dostoevsky'
    ORDER BY title;

    Output (example, showing one "Crime & Punishment"):

    +------------------------+
    | title                  |
    +------------------------+
    | America                |
    | Brothers Karamozov     |
    | Crime & Punishment     |
    | Idiot                  |
    | Notes from Underground |
    +------------------------+
  • HIGH_PRIORITY:

    Gives the SELECT statement higher priority over concurrent data modification statements (use with caution as it can impact write performance).

    SQL

    SELECT DISTINCT HIGH_PRIORITY title
    FROM books
    JOIN authors USING (author_id)
    WHERE name_last = 'Dostoevsky'
    ORDER BY title;
  • SQL_CALC_FOUND_ROWS and FOUND_ROWS():

    To find out how many rows a query would have returned without a LIMIT clause, use SQL_CALC_FOUND_ROWS in your SELECT statement, and then execute SELECT FOUND_ROWS(); immediately after.

    SELECT SQL_CALC_FOUND_ROWS isbn, title
    FROM books
    JOIN authors USING (author_id)
    WHERE name_last = 'Dostoevsky'
    ORDER BY title -- Order before limit to ensure consistent FOUND_ROWS() for a given query logic
    LIMIT 5;

    Output (example for the first query):

    +------------+------------------------+
    | isbn       | title                  |
    +------------+------------------------+
    | 0805210644 | America                |
    | 0553212168 | Brothers Karamozov     |
    | 0553211757 | Crime & Punishment     |
    | 0679420290 | Crime & Punishment     |
    | 0192834118 | Idiot                  |
    +------------+------------------------+
    5 rows in set (0.001 sec)

    Then, to get the total count:

    SELECT FOUND_ROWS();

    Output (example, if 6 Dostoevsky books in total):

    +--------------+
    | FOUND_ROWS() |
    +--------------+
    |            6 |
    +--------------+
    1 row in set (0.000 sec)

    The value from FOUND_ROWS() is temporary and specific to the current session.

This page is licensed: CC BY-SA / Gnu FDL

Basic SQL Statements Guide

Core SQL Statements Guide

This guide provides a quick overview of essential SQL statements in MariaDB, categorized by their function in data definition, data manipulation, and transaction control. Find brief descriptions and links to detailed documentation for each statement, along with a simple illustrative example sequence.

(If you need a basic tutorial on how to use the MariaDB database server and execute simple commands, see A MariaDB Primer. Also see Essential Queries Guide for examples of commonly-used queries.)

Defining How Your Data Is Stored (Data Definition Language - DDL)

  • CREATE DATABASE: Used to create a new, empty database.

  • DROP DATABASE: Used to completely destroy an existing database.

  • USE: Used to select a default database for subsequent statements.

  • CREATE TABLE: Used to create a new table, which is where your data is actually stored.

  • ALTER TABLE: Used to modify an existing table's definition (e.g., add/remove columns, change types).

  • DROP TABLE: Used to completely destroy an existing table and all its data.

  • DESCRIBE (or DESC): Shows the structure of a table (columns, data types, etc.).

Manipulating Your Data (Data Manipulation Language - DML)

  • SELECT: Used when you want to read (or select) your data from one or more tables.

  • INSERT: Used when you want to add (or insert) new rows of data into a table.

  • UPDATE: Used when you want to change (or update) existing data in a table.

  • DELETE: Used when you want to remove (or delete) existing rows of data from a table.

  • REPLACE: Works like INSERT, but if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

  • TRUNCATE TABLE: Used to quickly remove all data from a table, resetting any AUTO_INCREMENT values. It is faster than DELETE without a WHERE clause for emptying a table.

Transactions (Transaction Control Language - TCL)

  • START TRANSACTION (or BEGIN): Used to begin a new transaction, allowing multiple SQL statements to be treated as a single atomic unit.

  • COMMIT: Used to save all changes made during the current transaction, making them permanent.

  • ROLLBACK: Used to discard all changes made during the current transaction, reverting the database to its state before the transaction began.

A Simple Example Sequence

This example demonstrates several of the statements in action:

-- Create a new database
CREATE DATABASE mydb;

-- Select the new database to use
USE mydb;

-- Create a new table
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- Insert some data
INSERT INTO mytable VALUES (1, 'Will');
INSERT INTO mytable VALUES (2, 'Marry');
INSERT INTO mytable VALUES (3, 'Dean');

-- Select specific data
SELECT id, name FROM mytable WHERE id = 1;

-- Update existing data
UPDATE mytable SET name = 'Willy' WHERE id = 1;

-- Select all data to see changes
SELECT id, name FROM mytable;

-- Delete specific data
DELETE FROM mytable WHERE id = 1;

-- Select all data again
SELECT id, name FROM mytable;

-- Drop the database (removes the database and its tables)
DROP DATABASE mydb;

Common Query: Counting Rows

To count the number of records in a table:

SELECT COUNT(*) FROM mytable; -- Or SELECT COUNT(1) FROM mytable;

(Note: This query would typically be run on an existing table, for example, before it or its database is dropped.)

This page is licensed: CC BY-SA / Gnu FDL

Basic SQL Debugging Guide

SQL Query Design Guide

This guide offers conventions and practical tips for designing SQL queries that are easier to read, understand, and debug. Learn about effectively using whitespace, choosing meaningful aliases, correctly placing JOIN conditions, and strategies for identifying and resolving common syntax errors.

Following a few conventions makes finding errors in queries a lot easier, especially when asking for help from people who might know SQL but know nothing about your particular schema. A query easy to read is a query easy to debug.

Using Whitespace

A query that is hard to read is hard to debug. Whitespace is free; use new lines and indentation to make queries easy to read, particularly when constructing a query inside a scripting language where variables might be interspersed.

Example: Hard-to-read query with a syntax error

Can you find the error quickly in this?

SELECT u.id, u.name, alliance.ally FROM users u JOIN alliance ON (u.id=alliance.userId) JOIN team ON (alliance.teamId=team.teamId WHERE team.teamName='Legionnaires' AND u.online=1 AND ((u.subscription='paid' AND u.paymentStatus='current') OR u.subscription='free') ORDER BY u.name;

Same query with better whitespace:

Code snippet

SELECT
    u.id,
    u.name,
    alliance.ally
FROM
    users u
    JOIN alliance ON (u.id = alliance.userId)
    JOIN team ON (alliance.teamId = team.teamId  -- Error: Missing ')'
WHERE
    team.teamName = 'Legionnaires'
    AND u.online = 1
    AND (
        (u.subscription = 'paid' AND u.paymentStatus = 'current')
        OR
        u.subscription = 'free'
    )
ORDER BY
    u.name;

The missing closing parenthesis ) after team.teamId in the second JOIN condition is much easier to spot with clear formatting. The exact style (commas before or after selected items, tabs vs. spaces) is less important than overall legibility.

Table and Field Aliases

Aliases rename tables and fields within a query, useful for long names or when required (e.g., self-joins, some subqueries). Poorly chosen aliases, however, can hinder debugging. Aliases should ideally reflect the original table name.

Bad Example (arbitrary aliases):

Code snippet

SELECT *
FROM
    financial_reportQ_1 AS a
    JOIN sales_renderings AS b ON (a.salesGroup = b.groupId)
    JOIN sales_agents AS c ON (b.groupId = c.group)
WHERE
    b.totalSales > 10000
    AND c.id != a.clientId;

As the query grows, it's hard to remember what a, b, or c refer to without looking back.

Better Example (meaningful aliases):

To correct the SQL code, it should be properly formatted and structured:

SELECT *
FROM financial_report_Q_1 AS frq1
JOIN sales_renderings AS sr ON frq1.salesGroup = sr.groupId
JOIN sales_agents AS sa ON sr.groupId = sa.group
WHERE sr.totalSales > 10000
AND sa.id != frq1.clientId;

This query selects all data from financial_report_Q_1 and joins sales_renderings and sales_agents using specified conditions. It filters for total sales greater than 10,000 and excludes records where the sales agent's ID matches


Using initials or recognizable abbreviations (e.g., `frq1` for `financial_report_Q_1`) makes the query more understandable.

## Placing JOIN Conditions

The `ON` clause of a `JOIN` should specify the conditions that link the tables. Avoid using it for filtering rows that belong in the `WHERE` clause. Conversely, avoid placing all join logic in the `WHERE` clause (as common with older, implicit join syntax).

**Bad Example (join condition mixed in `WHERE`):**

```sql
SELECT *
FROM
    family,
    relationships
WHERE
    family.personId = relationships.personId -- Join condition
    AND relationships.relation = 'father';   -- Filtering condition

It's unclear how family and relationships are linked without parsing the entire WHERE clause.

Better Example (clear separation):

SELECT *
FROM
    family
    JOIN relationships ON (family.personId = relationships.personId) -- Join condition
WHERE
    relationships.relation = 'father'; -- Filtering condition

The table relationship is obvious in the JOIN ... ON clause. The WHERE clause is reserved for filtering the result set. Always use explicit JOIN keywords (INNER JOIN, LEFT JOIN, etc.) instead of commas for joining tables, and do not mix these styles.

Finding Syntax Errors

MariaDB's error messages usually point to where the parser got confused. Check the query around the indicated point.

Interpreting the "Empty Error"

An error like ERROR 1064: ... syntax to use near '' at line 1 can be tricky. The empty ' ' often means the parser reached the end of the statement while expecting more tokens.

  • Check for missing closing characters like quotes ' or parentheses ).SQL

    SELECT * FROM someTable WHERE field = 'value -- Missing closing quote
  • Look for incomplete clauses, often indicated by a trailing comma.SQL

    SELECT * FROM someTable WHERE field = 1 GROUP BY id, -- Incomplete GROUP BY

Checking for Reserved Keywords

If an identifier (table name, column name, alias) is a MariaDB reserved word, it must be enclosed in backticks (`) to avoid ambiguity.

SELECT * FROM actionTable WHERE `DELETE` = 1; -- `DELETE` is a reserved word

A text editor with SQL syntax highlighting can help spot these. Common identifiers that are also keywords include:

  • DESC (often for "description", but means "descending" in ORDER BY)

  • DATE, TIME, TIMESTAMP (data types)

  • ORDER (used in sales contexts, but is an SQL clause)

It's a good practice to quote any identifier that is also a keyword, even if MariaDB might allow some unquoted in certain contexts.

Version-Specific Syntax

SQL syntax evolves. Features and syntax available in newer MariaDB versions might not work in older ones, and vice-versa (though less common).

  • New syntax in old versions: Web hosts may run older MariaDB versions. A query working on your newer local setup might fail in an older production environment.

    • Subqueries (e.g., WHERE someId IN (SELECT id FROM ...)) were added in MySQL 4.1.

    • Early JOIN syntax did not always allow an ON clause.

  • Old syntax in new versions: Sometimes, changes in operator precedence or syntax deprecation can cause issues. For example, the precedence of the comma operator relative to JOIN changed in MySQL 5.0. A query like SELECT * FROM a, b JOIN c ON a.x = c.x; that worked before might fail or produce different results.

Always check the MariaDB server version you are targeting and consult the manual for that version to ensure syntax compatibility. The manual usually indicates when specific syntax features became available.

This page is licensed: CC BY-SA / Gnu FDL

MariaDB String Functions Guide

String Functions Guide

This guide explores a variety of MariaDB's built-in string functions essential for effective data manipulation. Learn how to format text for display, extract specific substrings, replace content, and utilize various expression aids to enhance your string operations in SQL queries.

Formatting Strings

Several functions are available for formatting text and numbers for display or processing.

Concatenating Strings:

  • CONCAT(str1, str2, ...): Joins two or more strings together.

    SQL

    SELECT CONCAT(name_first, ' ', name_last) AS Name FROM contacts;

    This displays a full name by combining name_first, a space, and name_last.

  • CONCAT_WS(separator, str1, str2, ...): Joins strings with a specified separator between each.

    SQL

    SELECT CONCAT_WS('|', col1, col2, col3) FROM table1;

    This creates a pipe-delimited string from col1, col2, and col3.

Formatting Numbers:

  • FORMAT(number, decimal_places): Formats a number with commas every three digits and a specified number of decimal places.SQL

    SELECT CONCAT('$', FORMAT(col5, 2)) AS Price FROM table3;

    This prepends a dollar sign to a number formatted with commas and two decimal places (e.g., $100,000.00).

Changing Case:

  • UCASE(str) or UPPER(str): Converts a string to all upper-case letters.

  • LCASE(str) or LOWER(str): Converts a string to all lower-case letters.SQL

    SELECT UCASE(col1) AS Upper_Col1, LCASE(col2) AS Lower_Col2 FROM table4;

Padding Strings:

  • LPAD(str, len, padstr): Left-pads str with padstr until it is len characters long.

  • RPAD(str, len, padstr): Right-pads str with padstr until it is len characters long.SQL

    SELECT RPAD(part_nbr, 8, '.') AS 'Part Nbr.', LPAD(description, 15, '_') AS Description FROM catalog;

    Example: RPAD('H200', 8, '.') might produce H200..... LPAD('hinge', 15, '_') might produce __________hinge.

Trimming Strings:

  • LTRIM(str): Removes leading spaces.

  • RTRIM(str): Removes trailing spaces.

  • TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str): Removes leading, trailing, or both occurrences of remstr (or spaces if remstr is not given). BOTH is the default if no specifier is given before remstr. If only str is provided, trims leading and trailing spaces.

    SELECT
        TRIM(LEADING '.' FROM col1) AS Trimmed_Leading_Dots,
        TRIM(TRAILING FROM col2) AS Trimmed_Trailing_Spaces, -- Trims spaces
        TRIM(BOTH '_' FROM col3) AS Trimmed_Both_Underscores,
        TRIM(col4) AS Trimmed_Spaces -- Trims leading and trailing spaces
    FROM table5;

Extracting Substrings

These functions help extract specific parts of a string.

  • LEFT(str, len): Returns the leftmost len characters from str.

  • RIGHT(str, len): Returns the rightmost len characters from str.

    SELECT LEFT(telephone, 3) AS area_code, RIGHT(telephone, 7) AS tel_nbr
    FROM contacts
    ORDER BY area_code;

    This extracts the first 3 characters as area_code and the last 7 as tel_nbr.

  • SUBSTRING(str, pos, [len]) or MID(str, pos, [len]): Returns a substring len characters long from str, starting at position pos. MID() is a synonym for SUBSTRING(). If len is omitted, returns the rest of the string from pos.

    SELECT CONCAT('(', LEFT(telephone, 3), ') ',
                  SUBSTRING(telephone, 4, 3), '-',
                  MID(telephone, 7)) AS 'Telephone Number'
    FROM contacts
    ORDER BY LEFT(telephone, 3);

    This formats a 10-digit phone number like (504) 555-1234.

Manipulating Strings

Functions for changing or generating strings.

  • REPLACE(str, from_str, to_str): Replaces all occurrences of from_str within str with to_str.

    SELECT CONCAT(REPLACE(title, 'Mrs.', 'Ms.'), ' ', name_first, ' ', name_last) AS Name
    FROM contacts;

    This replaces "Mrs." with "Ms." in the title column.

  • INSERT(str, pos, len, newstr): Replaces the substring in str starting at pos and len characters long with newstr. If len is 0, newstr is inserted at pos without overwriting.

  • LOCATE(substr, str, [pos]): Returns the starting position of the first occurrence of substr within str. An optional pos specifies where to start searching. Returns 0 if substr is not found.

    -- Example: Change 'Mrs.' to 'Ms.' where title is embedded in a 'name' column
    SELECT INSERT(name, LOCATE('Mrs.', name), LENGTH('Mrs.'), 'Ms.')
    FROM contacts
    WHERE name LIKE '%Mrs.%';

    This finds 'Mrs.' in the name string, and replaces it with 'Ms.'. LENGTH('Mrs.') (which is 4) is used for len. If LOCATE() returns 0, INSERT() with a position of 0 typically returns the original string unchanged.

  • REVERSE(str): Reverses the characters in str.

    SELECT REVERSE('MariaDB'); -- Output: BDeiraM
  • REPEAT(str, count): Repeats str count times.

    SELECT REPEAT('Ha', 3); -- Output: HaHaHa

String Expression Aids

Functions that provide information about strings or assist in specific comparisons/conversions.

  • CHAR_LENGTH(str) or CHARACTER_LENGTH(str): Returns the length of str in characters.

    SELECT COUNT(school_id) AS 'Number of Students'
    FROM table8
    WHERE CHAR_LENGTH(school_id) = 8;

    This counts rows where school_id has exactly 8 characters.

  • INET_ATON(ip_address_str): Converts an IPv4 address string (e.g., '10.0.1.1') into a numeric representation suitable for numeric sorting.

  • INET_NTOA(numeric_ip_representation): Converts the numeric representation back to an IPv4 address string.

    To correctly sort IP addresses numerically instead of lexically:

    SELECT ip_address
    FROM computers
    WHERE server = 'Y'
    ORDER BY INET_ATON(ip_address)
    LIMIT 3;

    Lexical sort of 10.0.1.1, 10.0.11.1, 10.0.2.1 might be 10.0.1.1, 10.0.11.1, 10.0.2.1.

    Numeric sort (using INET_ATON) would correctly be 10.0.1.1, 10.0.2.1, 10.0.11.1.

  • STRCMP(str1, str2): Performs a case-sensitive comparison of str1 and str2.

    • Returns 0 if strings are identical.

    • Returns -1 if str1 is alphabetically before str2.

    • Returns 1 if str1 is alphabetically after str2.

    SELECT col1, col2
    FROM table6
    WHERE STRCMP(col3, 'text') = 0; -- Finds exact case-sensitive match for 'text'
  • SUBSTRING_INDEX(str, delim, count): Returns a substring from str before or after count occurrences of the delimiter delim.

    • If count is positive, returns everything to the left of the count-th delimiter (from the left).

    • If count is negative, returns everything to the right of the abs(count)-th delimiter (from the right).

    -- Get the first two elements from a pipe-delimited string
    SELECT SUBSTRING_INDEX('elem1|elem2|elem3|elem4', '|', 2); -- Output: elem1|elem2
    
    -- Get the last two elements
    SELECT SUBSTRING_INDEX('elem1|elem2|elem3|elem4', '|', -2); -- Output: elem3|elem4

This page is licensed: CC BY-SA / Gnu FDL

Restoring Data from Dump Files Guide

Data Restoration Guide

This guide explains how to restore your MariaDB data from backup files created with mariadb-dump. Learn the basic restoration process using the mariadb client and a specific technique for selectively restoring a single table while minimizing data loss on other tables.

It's important to understand that mariadb-dump is used for creating backup (dump) files, while the mariadb client utility is used for restoring data from these files. The dump file contains SQL statements that, when executed, recreate the database structure and/or data.

Basic Restoration Process

To restore a dump file, you direct the mariadb client to execute the SQL statements contained within the file.

mariadb --user your_username --password < /path/to/your/backupfile.sql
  • Replace your_username with your MariaDB username and /path/to/your/backupfile.sql with the actual path to your dump file.

  • You will be prompted for the password for your_username.

  • The < symbol is a standard input (STDIN) redirect, feeding the contents of backupfile.sql to the mariadb client.

  • Often, the dump file itself contains CREATE DATABASE IF NOT EXISTS and USE database_name; statements, so a specific database doesn't always need to be named on the command line during restore. If your dump file restores to a specific database, ensure that user has permissions to it. If the dump file does not specify a database, you might need to create the database first and then run:

    mariadb --user your_username --password your_database_name < /path/to/your/backupfile.sql

Important Considerations Before Restoring

  • Data Overwriting: Restoring a dump file will execute the SQL statements within it. If the dump file contains DROP TABLE and CREATE TABLE statements (common for full backups), existing tables with the same names will be dropped and recreated, leading to loss of any data added or changed since the backup was made.

  • Backup Age: If your dump file is several days old, restoring it entirely could revert all data in the affected tables/databases to that older state. This can be disastrous if only a small portion of data was lost and the rest has been actively updated.

Always ensure you understand the contents of the dump file and the potential impact before initiating a restore, especially on a production system. Consider testing the restore on a non-production environment first if possible.

Restoring a Single Table Selectively

If only one table has been lost or corrupted and your backup file contains an entire database (or multiple tables), a full restore might overwrite recent, valid data in other tables. Here’s a method to restore only a specific table using a temporary user with restricted privileges:

  1. Create a Temporary User: Create a MariaDB user specifically for this restore operation.

  2. Grant Limited Privileges:

    • Grant this temporary user the minimal privileges needed for the dump file to execute up to the point of restoring your target table. This might be SELECT on all tables in the database if the dump file checks other tables, or simply the ability to USE the database.

    • Then, grant ALL PRIVILEGES (or specific necessary privileges like CREATE, DROP, INSERT, SELECT) only on the specific table you want to restore.

    Example SQL to create a temporary user and grant permissions (replace placeholders):

    -- Connect to MariaDB as an administrative user (e.g., root)
    CREATE USER 'admin_restore_temp'@'localhost' IDENTIFIED BY 'its_very_secure_pwd';
    
    -- Grant general SELECT on the database (might be needed if dump file structure requires it)
    -- Or, if not needed, ensure the user can at least USE the database.
    GRANT SELECT ON your_database_name.* TO 'admin_restore_temp'@'localhost';
    
    -- Grant full privileges ONLY on the table to be restored
    GRANT ALL PRIVILEGES ON your_database_name.table_to_restore TO 'admin_restore_temp'@'localhost';
    
    FLUSH PRIVILEGES;
  3. Restore Using the Temporary User and --force:

    Use the mariadb client with the temporary user and the --force option. The --force option tells MariaDB to continue executing statements in the dump file even if some SQL errors occur. Errors will occur for operations on tables where admin_restore_temp lacks permissions, but operations on table_to_restore (where permissions were granted) should succeed.

    Bash

    mariadb --user admin_restore_temp --password --force your_database_name < /path/to/your/fulldumpfile.sql

    You will be prompted for the password of admin_restore_temp.

  4. Verify Restoration: Check that table_to_restore has been correctly restored.

  5. Clean Up: Drop the temporary user once the restoration is confirmed:

    DROP USER 'admin_restore_temp'@'localhost';

This method helps to isolate the restore operation to the intended table, protecting other data from being inadvertently reverted to an older state.

Changing Times in MariaDB

Modifying Dates and Times Guide

This guide explores MariaDB functions for performing calculations and modifications on date and time values. Learn to use functions like DATE_ADD, DATE_SUB, TIME_TO_SEC, and SEC_TO_TIME to accurately add or subtract intervals and manage date/time changes that cross midnight or month/year boundaries.

(For foundational knowledge on date and time data types and basic retrieval, please refer to the "Date and Time Handling Guide".)

Calculating Time Across Midnight

When adding hours to a TIME value, calculations might exceed 24 hours. For example, if a task is entered at 23:00 and is promised 2 hours later, a simple addition can be problematic.

Consider an INSERT statement for a tickets table with entered and promised TIME columns:

-- Example: Calculating a promised time 2 hours (7200 seconds) from current time
INSERT INTO tickets (client_id, urgency, trouble, ticket_date, entered, promised)
VALUES ('some_client', 'ASAP', 'Issue details',
        CURDATE(), CURTIME(),
        SEC_TO_TIME(TIME_TO_SEC(CURTIME()) + 7200));
  • TIME_TO_SEC(time) converts a time value to seconds.

  • SEC_TO_TIME(seconds) converts seconds back to a time format (HHH:MM:SS).

If CURTIME() is 23:00:00 (82,800 seconds), 82800 + 7200 = 90000 seconds. SEC_TO_TIME(90000) would result in 25:00:00. While MariaDB can store this, it doesn't represent a standard clock time for the next day.

Modulo Arithmetic for Time Rollover:

To handle time wrapping around the 24-hour clock (86,400 seconds in a day) for TIME columns, use the modulo operator (%):

-- Corrected calculation for 'promised' TIME, wraps around 24 hours
SEC_TO_TIME((TIME_TO_SEC(CURTIME()) + 7200) % 86400)

If current time is 23:00, (82800 + 7200) % 86400 becomes 90000 % 86400, which is 3600 seconds. SEC_TO_TIME(3600) correctly results in 01:00:00.

Tracking Date Changes with Time: Using DATETIME

The modulo arithmetic above gives the correct time of day but doesn't indicate if the promised time falls on the next calendar day. For calculations where the date might change, it's essential to use DATETIME (or TIMESTAMP) data types.

If your table initially used separate DATE and TIME columns (e.g., ticket_date, entered_time, promised_time), you would typically alter the table to use DATETIME columns (e.g., entered_datetime, promised_datetime) to store both date and time information accurately. This often involves:

  1. Adding new DATETIME columns.

  2. Populating them by combining the old date and time columns (e.g., using CONCAT(ticket_date, ' ', entered_time)).

  3. Dropping the old separate date and time columns. (Always back up your data before such structural changes.)

With DATETIME columns, NOW() can be used to get the current date and time.

Adding Durations with DATE_ADD

The DATE_ADD(date, INTERVAL expr unit) function is the most robust way to add a duration to a date, time, or datetime value. It correctly handles rollovers across days, months, and years.

  • date: A DATE, DATETIME, or TIME value.

  • expr: The value of the interval to add.

  • unit: The unit of the interval (e.g., HOUR, MINUTE, DAY, MONTH, YEAR, etc.).

Adding Hours (handles date change):

If entered and promised are DATETIME columns:

INSERT INTO tickets (client_id, urgency, trouble, entered, promised)
VALUES ('some_client', 'ASAP', 'Issue details',
        NOW(),
        DATE_ADD(NOW(), INTERVAL 2 HOUR));

If NOW() is 2025-06-03 23:00:00, promised will correctly be 2025-06-04 01:00:00.

Adding Combined Hours and Minutes:

Use HOUR_MINUTE as the unit. The expr is a string 'hours:minutes'.

-- Add 2 hours and 30 minutes
DATE_ADD(NOW(), INTERVAL '2:30' HOUR_MINUTE)

If NOW() is 2025-06-03 23:00:00, this results in 2025-06-04 01:30:00.

Date Calculations Across Months and Years with DATE_ADD

DATE_ADD also correctly handles date changes across month and year boundaries, including leap years.

Adding Days:

-- Add 5 days
DATE_ADD(NOW(), INTERVAL 5 DAY)

If NOW() is 2025-02-27, this would result in 2025-03-04 (assuming 2025 is not a leap year).

Adding Combined Days and Hours:

Use DAY_HOUR as the unit. The expr is a string 'days hours'.

-- Add 2 days and 6 hours
DATE_ADD(NOW(), INTERVAL '2 6' DAY_HOUR)

Adding Combined Years and Months:

Use YEAR_MONTH as the unit. The expr is a string 'years-months'.

-- Add 1 year and 2 months
DATE_ADD(NOW(), INTERVAL '1-2' YEAR_MONTH) -- Note: Original text used '1 2', '1-2' is common for YEAR_MONTH

If NOW() is 2025-09-15 23:00:00, this results in 2026-11-15 23:00:00. This type of interval typically does not affect the day or time components directly, only the year and month.

Subtracting Durations

Using DATE_ADD with a Negative Interval:

You can subtract durations by providing a negative value for expr.

-- Subtract 5 days
DATE_ADD(NOW(), INTERVAL -5 DAY)

Using DATE_SUB(date, INTERVAL expr unit):

This function is specifically for subtracting durations.

-- Subtract 5 days
DATE_SUB(NOW(), INTERVAL 5 DAY)

Note: With DATE_SUB, expr is positive for subtraction. A negative expr would result in addition.

Making Backups with mariadb-dump Guide

Data Backup with mariadb-dump Guide

This guide explains how to use the mariadb-dump utility to create essential backup (dump) files of your MariaDB data. Learn to effectively back up all databases, specific databases, or individual tables, ensuring your data is protected and can be restored when needed.

About mariadb-dump

mariadb-dump is a command-line utility included with MariaDB for creating logical backups of your databases. It was previously known as mysqldump, which often still works as a symbolic link.

Key Advantages:

  • No Server Shutdown: Backups can be performed while the MariaDB server is running.

  • SQL Output: It generates a .sql file (a "dump file") containing SQL statements (CREATE TABLE, INSERT, etc.) necessary to reconstruct the databases and data.

All mariadb-dump commands are executed from your system's command-line shell, not within the mariadb client.

Backing Up All Databases

To export all databases managed by your MariaDB server:

mariadb-dump --user=admin_backup --password --lock-tables --all-databases > /data/backup/dbs_alldatabases.sql
  • --user=admin_backup: Specifies the MariaDB user performing the backup (this user needs appropriate privileges, typically at least SELECT and LOCK TABLES).

  • --password: Prompts for the user's password. For use in scripts where prompting is not possible, you can use --password=yourpassword (note the absence of a space and the security implication of having the password in a script or command history).

  • --lock-tables (or -x): Locks all tables across all databases before starting the backup to ensure data consistency. The lock is released once the dump is complete for each table. For transactional tables like InnoDB, using --single-transaction is often preferred as it provides a consistent snapshot without prolonged locking of all tables.

  • --all-databases (or -A): Specifies that all databases should be dumped.

  • > /data/backup/dbs_alldatabases.sql: Redirects the output (the SQL statements) to the specified file. Ensure the path exists and the user running the command has write permissions.

Commonly Used Option for Efficiency:

  • --extended-insert (or -e): Creates INSERT statements that include multiple rows per statement. This generally results in a smaller dump file and faster restores. This option is often enabled by default but can be explicitly stated.

Example with long options and password in script:

mariadb-dump --user=admin_backup --password=yoursecurepassword --lock-tables --extended-insert --all-databases > /data/backup/dbs_alldatabases.sql

Backing Up a Single Database

Backing up databases individually can result in smaller, more manageable dump files and allow for more flexible backup schedules.

mariadb-dump --user=admin_backup --password --lock-tables --extended-insert --databases your_database_name > /data/backup/your_database_name.sql
  • --databases (or -B): Followed by the name of the database to dump.

  • To back up multiple specific databases, list their names separated by spaces after the --databases option:

    mariadb-dump --user=admin_backup --password --lock-tables --extended-insert --databases db1_name db2_name > /data/backup/selected_databases.sql

Backing Up Specific Tables

For very large databases, or if only certain tables change frequently, you might back up individual tables.

mariadb-dump --user=admin_backup --password --lock-tables --extended-insert your_database_name table_name1 table_name2 > /data/backup/your_database_name_selected_tables.sql
  • First, specify the database name (your_database_name).

  • Then, list one or more table names (table_name1, table_name2) separated by spaces.

  • Note that the --databases option is not used when dumping specific tables in this manner.

Important Considerations and Best Practices

  • User Privileges: The MariaDB user specified with --user needs at least SELECT privileges for the tables being dumped. LOCK TABLES privilege is needed if using --lock-tables. RELOAD or FLUSH_TABLES might be needed for options like --flush-logs or --master-data. For --single-transaction, PROCESS and RELOAD might be required. A user with global SELECT, LOCK TABLES, SHOW VIEW, EVENT, and TRIGGER privileges is often used for backups.

  • Consistency with InnoDB: For databases primarily using InnoDB tables, consider using the --single-transaction option instead of --lock-tables. This option starts a transaction before dumping and reads data from a consistent snapshot without locking the tables for extended periods, allowing concurrent reads and writes.Bash

    mariadb-dump --user=admin_backup --password --single-transaction --extended-insert --databases your_innodb_database > /data/backup/your_innodb_database.sql
  • Practice Makes Perfect: mariadb-dump is powerful but can have many options. Practice using it on a test database or server to become comfortable with its usage and to verify that your backup strategy works as expected.

  • Test Your Backups: Regularly test your backup files by restoring them to a non-production environment to ensure they are valid and can be used for recovery.

  • Restoration: To learn how to restore data from these dump files, see the "Data Restoration Guide".

  • Security: Store backup files in a secure location. If passwords are included in scripts, ensure the script files have restricted permissions.

This page is licensed: CC BY-SA / Gnu FDL

A MariaDB Primer Guide

MariaDB Primer

Cover

WEBINAR

MariaDB 101: Learning the Basics of MariaDB

Watch Now

This primer offers a quick jump-start for beginners using an existing MariaDB database via the mariadb command-line client. Learn how to log in, understand basic database concepts, and perform essential SQL operations like creating tables, inserting data, and retrieving or modifying records.

Logging into MariaDB

To begin, log into your MariaDB server from your system's command-line:

mariadb -u user_name -p -h ip_address db_name
  • Replace user_name with your MariaDB username.

  • Replace ip_address with the hostname or IP address of your MariaDB server. If you are accessing MariaDB from the same server you're logged into (i.e., locally), you can usually omit the -h ip_address part.

  • Replace db_name with the name of the database you wish to access (e.g., test). Some setups may have a test database by default; others might not, or it might have been removed (e.g., by mariadb-secure-installation). If unsure, or if you want to connect without selecting a specific database initially, you can omit db_name.

You will be prompted to enter your password. If your login is successful, you will see a prompt similar to this:

MariaDB [test]>

The "MariaDB" indicates you are connected to a MariaDB server. The name within the brackets (e.g., test) is your current default database. If no database was specified or successfully connected to, it might show [(none)].

Understanding Database Basics and Setup

SQL (Structured Query Language): This is the language used to interact with MariaDB. An SQL statement that requests data is called a query.

Tables: Databases store information in tables, which are structured like spreadsheets with rows and columns, but are much more efficient for data management.

Example Setup:

If the test database is empty or doesn't exist, you can run the following SQL statements to create and populate tables for the examples in this primer. Copy and paste these into the mariadb client prompt.

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE IF NOT EXISTS books (
  BookID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Title VARCHAR(100) NOT NULL,
  SeriesID INT,
  AuthorID INT
);

CREATE TABLE IF NOT EXISTS authors (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
  -- You would typically add more columns like name, etc.
);

CREATE TABLE IF NOT EXISTS series (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
  -- You would typically add more columns like series_name, etc.
);

INSERT INTO books (Title, SeriesID, AuthorID) VALUES
  ('The Fellowship of the Ring', 1, 1),
  ('The Two Towers', 1, 1),
  ('The Return of the King', 1, 1),
  ('The Sum of All Men', 2, 2),
  ('Brotherhood of the Wolf', 2, 2),
  ('Wizardborn', 2, 2),
  ('The Hobbbit', 0, 1); -- Note: "Hobbbit" is intentionally misspelled for a later example
  • Semicolons (;): The mariadb client allows complex SQL statements over multiple lines. It sends the statement to the server for execution only after you type a semicolon (;) and press [Enter].

Exploring Your Database Structure

Listing Tables:

To see the tables in your current database:

SHOW TABLES;

Output (example):

+----------------+
| Tables_in_test |
+----------------+
| authors        |
| books          |
| series         |
+----------------+
3 rows in set (0.00 sec)

Describing a Table:

To get information about the columns in a table (like their names and types):

DESCRIBE books;

Output (example):

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| BookID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| Title    | varchar(100) | NO   |     | NULL    |                |
| SeriesID | int(11)      | YES  |     | NULL    |                |
| AuthorID | int(11)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

The Field column lists the column names, which you'll need to retrieve specific data.

Retrieving Data (SELECT)

To retrieve data from a table, use the SELECT statement.

SELECT * FROM books;
  • The asterisk (*) is a wildcard meaning "all columns." Output (example):

+--------+----------------------------+----------+----------+
| BookID | Title                      | SeriesID | AuthorID |
+--------+----------------------------+----------+----------+
|      1 | The Fellowship of the Ring |        1 |        1 |
|      2 | The Two Towers             |        1 |        1 |
|      3 | The Return of the King     |        1 |        1 |
|      4 | The Sum of All Men         |        2 |        2 |
|      5 | Brotherhood of the Wolf    |        2 |        2 |
|      6 | Wizardborn                 |        2 |        2 |
|      7 | The Hobbbit                |        0 |        1 |
+--------+----------------------------+----------+----------+
7 rows in set (0.00 sec)

Adding Data (INSERT)

To add new rows to a table, use the INSERT statement.

INSERT INTO books (Title, SeriesID, AuthorID)
VALUES ("Lair of Bones", 2, 2);
  • After INSERT INTO table_name, list the columns you are providing data for in parentheses.

  • The VALUES keyword is followed by a list of values in parentheses, in the same order as the listed columns. Output:

Query OK, 1 row affected (0.00 sec)

You can run SELECT * FROM books; again to see the newly added row.

Modifying Data (UPDATE)

To change existing data in a table, use the UPDATE statement. Let's correct the spelling of "The Hobbbit".

UPDATE books
SET Title = "The Hobbit"
WHERE BookID = 7;
  • SET Title = "The Hobbit" specifies the column to change and its new value.

  • WHERE BookID = 7 is crucial; it specifies which row(s) to update. Without a WHERE clause, UPDATE would change all rows in the table. Output:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Run SELECT * FROM books WHERE BookID = 7; to see the correction.

Using MariaDB involves understanding SQL syntax. It doesn't allow for typing mistakes or clauses in the wrong order, but with practice, it becomes straightforward.

See Also

  • MariaDB Basics

Creating & Using Views Guide

Introduction to Views Guide

This guide introduces SQL Views in MariaDB, virtual tables based on the result-set of a stored query. Learn how views simplify complex queries, enhance data security by restricting access, and provide an abstraction layer over your database tables through practical examples.

Prerequisites

  • A basic understanding of SQL, particularly JOIN operations. (You may want to refer to guides like "Basic Joins Guide" or "More Advanced Joins" if available.)

  • Access to a MariaDB database.

  • Privileges to CREATE TABLE and CREATE VIEW.

Setup: Example Employee Database

First, we'll create and populate two tables, Employees and Hours, to use in our examples. If you have already completed a tutorial using this database structure (e.g., from a "More Advanced Joins" guide), you might be able to skip this setup.

Employees Table:

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM;

INSERT INTO `Employees` (`First_Name`, `Last_Name`, `Position`, `Home_Address`, `Home_Phone`)
VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478');

Hours Table:

CREATE TABLE `Hours` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `Clock_In` DATETIME NOT NULL,
  `Clock_Out` DATETIME NOT NULL
) ENGINE=MyISAM;

INSERT INTO `Hours`
VALUES ('1', '2005-08-08 07:00:42', '2005-08-08 17:01:36'),
  ('1', '2005-08-09 07:01:34', '2005-08-09 17:10:11'),
  ('1', '2005-08-10 06:59:56', '2005-08-10 17:09:29'),
  ('1', '2005-08-11 07:00:17', '2005-08-11 17:00:47'),
  ('1', '2005-08-12 07:02:29', '2005-08-12 16:59:12'),
  ('2', '2005-08-08 07:00:25', '2005-08-08 17:03:13'),
  ('2', '2005-08-09 07:00:57', '2005-08-09 17:05:09'),
  ('2', '2005-08-10 06:58:43', '2005-08-10 16:58:24'),
  ('2', '2005-08-11 07:01:58', '2005-08-11 17:00:45'),
  ('2', '2005-08-12 07:02:12', '2005-08-12 16:58:57'),
  ('3', '2005-08-08 07:00:12', '2005-08-08 17:01:32'),
  ('3', '2005-08-09 07:01:10', '2005-08-09 17:00:26'),
  ('3', '2005-08-10 06:59:53', '2005-08-10 17:02:53'),
  ('3', '2005-08-11 07:01:15', '2005-08-11 17:04:23'),
  ('3', '2005-08-12 07:00:51', '2005-08-12 16:57:52'),
  ('4', '2005-08-08 06:54:37', '2005-08-08 17:01:23'),
  ('4', '2005-08-09 06:58:23', '2005-08-09 17:00:54'),
  ('4', '2005-08-10 06:59:14', '2005-08-10 17:00:12'),
  ('4', '2005-08-11 07:00:49', '2005-08-11 17:00:34'),
  ('4', '2005-08-12 07:01:09', '2005-08-12 16:58:29'),
  ('5', '2005-08-08 07:00:04', '2005-08-08 17:01:43'),
  ('5', '2005-08-09 07:02:12', '2005-08-09 17:02:13'),
  ('5', '2005-08-10 06:59:39', '2005-08-10 17:03:37'),
  ('5', '2005-08-11 07:01:26', '2005-08-11 17:00:03'),
  ('5', '2005-08-12 07:02:15', '2005-08-12 16:59:02'),
  ('6', '2005-08-08 07:00:12', '2005-08-08 17:01:02'),
  ('6', '2005-08-09 07:03:44', '2005-08-09 17:00:00'),
  ('6', '2005-08-10 06:54:19', '2005-08-10 17:03:31'),
  ('6', '2005-08-11 07:00:05', '2005-08-11 17:02:57'),
  ('6', '2005-08-12 07:02:07', '2005-08-12 16:58:23');

Building a Complex Query (Example: Employee Tardiness)

Let's say Human Resources needs a report on employees who are late (clock in after 7:00:59 AM) and do not make up the time at the end of their shift (work less than 10 hours and 1 minute).

Initial Query (Helmholtz's Lateness):

This query finds instances where Helmholtz was late within a specific week:

SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`
FROM `Employees`
INNER JOIN `Hours` ON `Employees`.`ID` = `Hours`.`ID`
WHERE `Employees`.`First_Name` = 'Helmholtz'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59';

Output:

+------------+-----------+---------------------+---------------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           |
+------------+-----------+---------------------+---------------------+
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |
+------------+-----------+---------------------+---------------------+

Refined Query (Policy Violators):

This query identifies all employees who were late and whose shift duration was less than 10 hours and 1 minute (601 minutes).

SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`,
  (601 - TIMESTAMPDIFF(MINUTE, `Hours`.`Clock_In`, `Hours`.`Clock_Out`)) AS Difference -- Corrected Difference Calculation
FROM `Employees`
INNER JOIN `Hours` USING (`ID`) -- Simplified JOIN condition
WHERE DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') BETWEEN '2005-08-08' AND '2005-08-12'
  AND TIME(`Hours`.`Clock_In`) > '07:00:59'
  AND TIMESTAMPDIFF(MINUTE, `Hours`.`Clock_In`, `Hours`.`Clock_Out`) < 601;

Output of Refined Query (example structure):

+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | Difference |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |          4 |
... (other rows matching the criteria)
+------------+-----------+---------------------+---------------------+------------+

Creating and Using a View

The refined query is becoming complex. Storing this query logic in application code makes it harder to manage and means changes to table structures require application code changes. Views can simplify this.

A view is a virtual table based on the result-set of a stored query.

Creating the Employee_Tardiness View:

We use the refined query to create a view. SQL SECURITY INVOKER means the view runs with the permissions of the user querying it.

CREATE SQL SECURITY INVOKER VIEW Employee_Tardiness AS
SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`,
  (601 - TIMESTAMPDIFF(MINUTE, `Hours`.`Clock_In`, `Hours`.`Clock_Out`)) AS Difference
FROM `Employees`
INNER JOIN `Hours` USING (`ID`)
WHERE DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') BETWEEN '2005-08-08' AND '2005-08-12'
  AND TIME(`Hours`.`Clock_In`) > '07:00:59'
  AND TIMESTAMPDIFF(MINUTE, `Hours`.`Clock_In`, `Hours`.`Clock_Out`) < 601;

Querying the View:

Now, retrieving the tardiness data is much simpler:

SELECT * FROM Employee_Tardiness;

This will produce the same results as the complex "Refined Query" above.

You can also apply further conditions when querying the view:

SELECT * FROM Employee_Tardiness WHERE Difference >= 5;

Output (example structure, showing those at least 5 minutes short):

+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | Difference |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |          5 |
... (other rows where Difference >= 5)
+------------+-----------+---------------------+---------------------+------------+

Other Benefits and Uses of Views

  • Simplifying Complex Queries: As demonstrated, views hide complex joins and calculations.

  • Restricting Data Access (Column-Level Security): Views can expose only a subset of columns from underlying tables, preventing users or applications from seeing sensitive information (e.g., Home_Address, Home_Phone were not included in our Employee_Tardiness view).

  • Implementing Row-Level Security: A view can include a WHERE clause that filters rows based on the user querying it or other criteria, effectively providing row-level access control. For updatable views, defining them with WITH CHECK OPTION (or the similar effect of a CASCADE clause mentioned in original text, usually WITH CASCADED CHECK OPTION) can ensure that INSERTs or UPDATEs through the view adhere to the view's WHERE clause conditions.

  • Pre-emptive Optimization: Complex, frequently used queries can be defined as views with optimal join strategies and indexing considerations. Other users or applications query the already optimized view, reducing the risk of running inefficient ad-hoc queries.

  • Abstracting Table Structures: Views provide a consistent interface to applications even if the underlying table structures change (e.g., tables are normalized, split, or merged). The view definition can be updated to map to the new structure, while applications continue to query the unchanged view.

Summary of View Advantages

Views offer a powerful way to:

  • Simplify data access: Make complex queries easier to write and understand.

  • Abstract database logic: Separate application code from the complexities of the database schema.

  • Enhance security: Control access to specific rows and columns.

  • Improve maintainability: Changes to underlying tables can often be managed by updating the view definition without altering application queries.

This page is licensed: CC BY-SA / Gnu FDL