Basics Guide

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

Connecting to MariaDB Server

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

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

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:

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

Creating a Database Structure

First, create and select a database.

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

Next, create tables to hold data.

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

To view the structure of a created table:

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

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

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

SQL Syntax Notes

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

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

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

Entering Data

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

Since author_id in the authors table is AUTO_INCREMENT (see ), 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):

Multiple rows can be inserted with a single INSERT statement:

Retrieving Data

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

To retrieve all book titles:

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

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

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

Changing & Deleting Data

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

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 ). Use WHERE to specify which rows to delete.

This deletes all books associated with author_id '2034'.

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