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.
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.
The most common way to install MariaDB on Linux is through your system's package manager.
Steps:
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
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
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.
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, MariaDB provides an .msi
installer for a straightforward graphical installation.
Steps:
Download MariaDB:
Visit the MariaDB downloads page to get the latest .msi
installer.
Run the Installer:
Double-click the downloaded .msi
file to start the installation wizard.
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.
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).
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.
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
INSERT
Priority and BehaviorLOW_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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
To find the minimum value in a column:
SELECT MIN(a) FROM t1;
+--------+
| MIN(a) |
+--------+
| 1 |
+--------+
See the MIN() function documentation.
To calculate the average value of a column:
SELECT AVG(a) FROM t1;
+--------+
| AVG(a) |
+--------+
| 2.0000 |
+--------+
See the AVG() function documentation.
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.
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.
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 |
+-------+--------+-------+
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 |
+---------+--------+-------+
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.
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.
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 |
...
+--------------------+---------------------------------------+-----------+
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
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.
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].
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 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.
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');
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 |
+-------------------+
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
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 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.
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 | |
+-------------+-------------+------+-----+---------+-------+
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:
Temporarily modifying the ENUM to include both old and new values.
Updating existing rows to use the new values.
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');
To remove a column and its data (this action is permanent and irreversible without a backup):
ALTER TABLE clients
DROP COLUMN client_type;
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.
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.
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.
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
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.
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.
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).
The following are common connection parameters:
--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[=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
-W
(Windows only) Connects to the server using a named pipe, if the server was started with the --enable-named-pipe
option.
--port=num
-P num
Specifies the TCP/IP port number for the connection.
Default: 3306.
--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=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=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=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.
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.
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.
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.
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.
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.
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).
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.
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.
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 ...
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.
Stop the MariaDB server.
Restart the server manually from the command line, adding the --skip-grant-tables
option.
Connect to the server (no password will be required for root@localhost
).
Execute FLUSH PRIVILEGES;
to reload the grant tables (they are now active again).
Change the password for the necessary account, e.g.:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_strong_password');
Stop the server and restart it normally (without --skip-grant-tables
).
localhost
vs. %
Wildcard Host IssuesSymptoms:
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:
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;
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
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.
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
.
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 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).
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.
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
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.
The most common approach for bulk importing is to use a delimited text file.
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.
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.
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).
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.
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 "'"
.
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 '|';
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.
LOAD DATA INFILE
OptionsBinary 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
).
mariadb-import
UtilityThe 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.
Some web hosts disable LOAD DATA INFILE
or mariadb-import
for security reasons. A workaround involves using mariadb-dump
:
Prepare Data Locally: Prepare your delimited text file (e.g., prospects.txt
).
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.
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.
Upload SQL File: Upload the generated .sql
file (e.g., prospects.sql
) to your web server (in ASCII mode).
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.
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
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
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.
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.
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;
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 NULL
s 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 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 are used for performing full-text searches on text data. For details, see the Full-Text Indexes documentation.
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.
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
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
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.
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);
Below are examples of different JOIN
types using the tables t1
and t2
.
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.
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).
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
.
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 NULL
s 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
.
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).
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).
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
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.
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
Open the File: Use a text editor to open the primary configuration file identified (e.g., /etc/mysql/my.cnf
).
Locate [mysqld]
Section: Find the section starting with [mysqld]
.
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
Save and Restart: Save the configuration file and restart the MariaDB server service.
See Starting and Stopping MariaDB for instructions.
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.
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'
.
Connect to MariaDB:
mariadb -u root -p
View Existing Remote Users (Optional):SQL
SELECT User, Host FROM mysql.user
WHERE Host <> 'localhost' AND Host <> '127.0.0.1' AND Host <> '::1';
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.
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.
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:
Edit your MariaDB configuration file.
Ensure skip-networking
is not enabled (or is 0
).
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.
Restart the MariaDB server.
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
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.
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', '');
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 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
.
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)
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
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.)
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.).
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.
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.
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
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.
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.
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.
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
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.
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;
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
.
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
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
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.
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
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.
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:
Create a Temporary User: Create a MariaDB user specifically for this restore operation.
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;
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
.
Verify Restoration: Check that table_to_restore
has been correctly restored.
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.
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".)
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
.
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:
Adding new DATETIME
columns.
Populating them by combining the old date and time columns (e.g., using CONCAT(ticket_date, ' ', entered_time)
).
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.
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_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.
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.
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.
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.
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 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
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.
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
MariaDB Primer
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.
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)]
.
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].
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.
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)
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.
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.
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.
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
.
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');
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)
+------------+-----------+---------------------+---------------------+------------+
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)
+------------+-----------+---------------------+---------------------+------------+
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 INSERT
s or UPDATE
s 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.
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