Lesson 2: Schema Objects

Schema Objects

Learning Objectives

Collations and Character Sets

Listing Character Sets

MariaDB [(none)]> SHOW CHARACTER SET;

| Charset | Description                 | Default collation | Maxlen |
|---------|-----------------------------|-------------------|--------|
| big5    | Big5 Traditional Chinese    | big5_chinese_ci   | 2      |
| dec8    | DEC West European           | dec8_swedish_ci   | 1      |
| cp850   | DOS West European           | cp850_general_ci  | 1      |
| hp8     | HP West European            | hp8_english_ci    | 1      |
| koi8r   | KOI8-R Relcom Russian       | koi8r_general_ci  | 1      |
| latin1  | cp1252 West European        | latin1_swedish_ci | 1      |
| latin2  | ISO 8859-2 Central European | latin2_general_ci | 1      |
| swe7    | 7bit Swedish                | swe7_swedish_ci   | 1      |
| ascii   | US ASCII                    | ascii_general_ci  | 1      |
[...]

Listing Collations

MariaDB [(none)]> SHOW COLLATION LIKE 'utf8%';
+-----------------------+---------+-----+---------+----------+---------+
| Collation             | Charset | Id  | Default | Compiled | Sortlen |
+-----------------------+---------+-----+---------+----------+---------+
| utf8mb3_general_ci    | utf8mb3 |  33 |    Yes  |    Yes   |       1 |
| utf8mb3_bin           | utf8mb3 |  83 |         |    Yes   |       1 |
| utf8mb3_unicode_ci    | utf8mb3 | 192 |    Yes  |    Yes   |       8 |
| utf8mb3_icelandic_ci  | utf8mb3 | 193 |         |    Yes   |       8 |
| utf8mb3_latvian_ci    | utf8mb3 | 194 |         |    Yes   |       8 |
| utf8mb3_romanian_ci   | utf8mb3 | 195 |         |    Yes   |       8 |
| utf8mb3_slovenian_ci  | utf8mb3 | 196 |         |    Yes   |       8 |
| utf8mb3_polish_ci     | utf8mb3 | 197 |         |    Yes   |       8 |
| utf8mb3_estonian_ci   | utf8mb3 | 198 |         |    Yes   |       8 |
| utf8mb3_spanish_ci    | utf8mb3 | 199 |         |    Yes   |       8 |
...
+-----------------------+---------+-----+---------+----------+---------+

Databases, Tables, and Default Schemas

Case Sensitivity

Depends on operating system, file system and lower_case_table_names configuration

Set before starting a project (strongly recommended!)

Usually case sensitive by default on Linux, but not on Windows and MacOS

Adopt a convention such as always creating and referring to databases and tables using lowercase names

Most are limited to 64 characters

Databases

Database aka Schema

Highest level object

Corresponds to a directory within the data directory

All other objects reside within user-created databases however certain objects such as user accounts, roles, stored procedures and plugins reside within system databases.

-- create new database with name 'world'
CREATE DATABASE `world`;

-- list of exists databases
SHOW DATABASES;

Tables

Columns

Set of typed data values

Qualified by a table name
(i.e. table.column)

Stored together in each row (MariaDB Server) within pages, within tablespaces or data files

Generated columns are stored if they are created as PERSISTENT | STORED though VIRTUAL columns are not stored

Index

Indexes

Constraints

AUTO_INCREMENT

012
TraditionalConsecutiveInterleaved
Default
Holds table-level lock for all INSERTs until end of statementHolds table-level lock for all bulk INSERTs (such as LOAD DATA or INSERT ... SELECT) until end of statementNo table-level locks are held ever
For simple INSERTs, no table-level lock heldFastest and most scalable
Not safe for statement-based replication
Generated IDs are not always consecutive

Column Attributes

Columns have strict type definitions

Can specify DEFAULT value for column

Only the primary key can be automatically incremented

CREATE TABLE people (
    id INT AUTO_INCREMENT KEY,
    name VARCHAR(20) DEFAULT 'unknown'
);

Columns can be NULL, unless defined NOT NULL

Views

Using Views

Customers Table

| ID | FirstName | LastName |
|----|-----------|----------|
| 1  | Alice     | Evans    |
| 2  | Bob       | Smith    |

Addresses Table

| CustomerID | Address1          | City    |
|------------|-------------------|---------|
| 1          | 123 Main Street   | Anytown |
| 2          | 456 Spruce Street | Anyburg |

Create CustomerAddresses View as select from Customers and Addresses tables:

CREATE VIEW CustomerAddresses AS
SELECT
    CONCAT(c.FirstName, ' ', c.LastName) as FullName,
    CONCAT(a.Address1, ', ', a.City) as FullAddress
FROM Customers c
JOIN Addresses a ON c.id = a.CustomerId;

Fetch data from CustomerAddresses View

SELECT FullName, FullAddress FROM CustomerAddresses;

| FullName    | FullAddress               |
|-------------|---------------------------|
| Alice Evans | 123 Main Street, Anytown  |
| Bob Smith   | 456 Spruce Street, Anyburg|

Stored Routines

Creating Your First Table

CREATE TABLE city (
    ID INT(11) NOT NULL AUTO_INCREMENT,
    Name CHAR(35) NOT NULL DEFAULT '',
    CountryCode CHAR(3) NOT NULL DEFAULT '',
    District CHAR(20) NOT NULL DEFAULT '',
    Population INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (ID),
    KEY CountryCode (CountryCode),
    CONSTRAINT FOREIGN KEY (CountryCode)
        REFERENCES country (Code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Altering Tables

ALTER TABLE IS USED TO CHANGE A TABLE’S SCHEMA

ADD COLUMN to add a column

DROP COLUMN to drop a column (deletes data!)

CHANGE COLUMN and MODIFY COLUMN to alter a column

ALTER TABLE table1
ADD COLUMN col5 CHAR(8),
DROP COLUMN col3,
CHANGE COLUMN col4 col6 DATE,  # new column name col6
MODIFY COLUMN col8 VARCHAR(10);

Basic syntax example for ALTER TABLE statement

Temporal Tables

| Type                   | Tracks                          | Sample Use Cases                         |
|------------------------|---------------------------------|------------------------------------------|
| System-Versioned       | Change history                  | Audit, forensics, IoT temperature tracking |
| Application-Time Period| Time-limited values             | Sales offers, subscriptions              |
| Bitemporal             | Time-limited values with history| Schedules, decision support models       |

mariadb-dump does not read historical rows from versioned tables, and so historical data will not be backed up.

Temporal Tables

System-Versioned Example

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    amount INT
) WITH SYSTEM VERSIONING;

Application-Time Period Example

CREATE TABLE coupons (
    id INT UNSIGNED,
    date_start DATE,
    date_end DATE,
    PERIOD FOR valid_period(date_start, date_end)
);

Bitemporal Example

CREATE TABLE coupons_new (
    id INT UNSIGNED,
    name VARCHAR(255),
    date_start DATE,
    date_end DATE,
    PERIOD FOR valid_period(date_start, date_end)
) WITH SYSTEM VERSIONING;

Default Schemas

information_schema

mysql

Stores information on:

performance_schema

Default Schemas

sys_schema

Information Schema Details

  CHARACTER_SETS  
  COLLATION_CHARACTER_SET_APP  
  LICABILITY  
  COLLATIONS  
  COLUMN_PRIVILEGES  
  COLUMNS  
  ENGINES  
  EVENTS  
  FILES  
  GLOBAL_STATUS  
  GLOBAL_VARIABLES  
  KEY_COLUMN_USAGE  
  PARTITIONS  
  PLUGINS  
  PLUGINS  
  PROCESSLIST SCHEMATA  
  PROFILING  
  REFERENTIAL_CONSTRAINTS  
  ROUTINES  
  SCHEMA_PRIVILEGES  
  SESSION_STATUS  
  SESSION_VARIABLES  
  STATISTICS  
  TABLE_CONSTRAINTS  
  TABLE_PRIVILEGES  
  TABLES  
  TRIGGERS  
  USER_PRIVILEGES  
  VIEWS

Methods of Accessing the Information Schema

Accessing Directly (Using SELECT from relevant tables)

SELECT
    TABLE_SCHEMA,
    ENGINE,
    COUNT(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema')
GROUP BY TABLE_SCHEMA, ENGINE;

SELECT *
FROM information_schema.global_status
WHERE VARIABLE_NAME LIKE '%qcache%';

Using Show Statements

SHOW STATUS LIKE '%qcache%';

Crash Safe System Tables

mysql Schema

SELECT ENGINE, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA="mysql";

Data Types

Data Types

Types: Binary, Numeric, String, Temporal, and User Defined

Use the most suitable data type to store all possible, required values
Will truncate silently and round depending on sql_mode

MariaDB [(none)]> help INT;
Name: 'INT'
Description: INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. 
The signed range is -2,147,483,648 to 2,147,483,647.
The unsigned range is 0 to 4,294,967,295.

Numeric Data Types

String data types

MariaDB provides several string data types to store character data efficiently. These types are used for everything from single-character codes to large-scale text documents, and they are defined by their maximum length, character set, and collation.

String data attributes

String columns are defined by a Character Set and a Collation. These can be inherited from the server or database, or set specifically at the table or column level.

Column table and column collation may be defined on table creation. Example below shows couple of variants:

CREATE TABLE t (
    -- colums with charset latin1 and defalult collation
    latin_name text CHARSET latin1,
    -- column with default charset and utf8mb4_general_ci collation
    utf8mb4_name text COLLATE utf8mb4_general_ci,
    -- column with certain charset and collation
    utf8mb3_name text CHARSET utf8mb3 COLLATE utf8mb3_general_ci  
);

How to inspect Character Set and Collation? One of methods - call CREATE TABLE <table name>; command.

|-------|---------------------------------------------------------------------------------------|
| Table | Create Table                                                                          |
|-------|---------------------------------------------------------------------------------------|
| t     | CREATE TABLE `t` (                                                                    |
|       |   `latin_name` text CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,      |
|       |   `utf8mb4_name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,  |
|       |   `utf8mb3_name` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL   |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci                 |

Try this on SQLize.online

Binary Data Types

Temporal Data Types

SELECT CURTIME(4);

+---------------+
| CURTIME(4)    |
+---------------+
| 05:33:09.1061 |
+---------------+

JSON Data Type

CREATE TABLE city (
  Name VARCHAR(35) NOT NULL,
  Info JSON DEFAULT NULL
);

INSERT INTO city VALUES (
  'New York',
  JSON_OBJECT(
    'Population','8008278',
    'Country', 'USA'
  )
);

SELECT
  Name, JSON_VALUE(Info,'$.Population') AS Population FROM city;

+---------+------------+
| Name    | Population |
+---------+------------+
| New York|  8008278   |
+---------+------------+

SELECT * FROM city;

+----------+--------------------------------------------+
| Name     | Info                                       |
+----------+--------------------------------------------+
| New York | {"Population": "8008278", "Country":"USA"} |
+----------+--------------------------------------------+

Special Data Types

Built-in Functions

MariaDB Date and Time Functions:

Examples of Date and Time Functions

Used in Queries and Data Manipulation Statements

SELECT NOW() + INTERVAL 1 DAY
           - INTERVAL 1 HOUR
       AS 'Day & Hour Earlier';

+---------------------+
| Day & Hour Earlier  |
+---------------------+
| 2020-06-02 08:32:44 | 
+---------------------+

Used in WHERE Clauses

UPDATE table1
SET col3 = 'today', col4 = NOW()
WHERE col5 = CURDATE();

Used in Bulk Load

load data local infile '/tmp/test.csv' into
table test fields terminated by ','
ignore 1 lines (id,@dt1)
set dt=str_to_date(@dt1,'%d/%m/%Y');

Manipulating Strings

MariaDB Functions For String Manipulation

Documentation on String Functions: https://mariadb.com/kb/en/library/string-functions/

An Example of a String Function

Used in Queries and Data Manipulation Statements

SELECT domain, domain_count
FROM (
    SELECT
        SUBSTRING(email_address, LOCATE('@', email_address) +1 ) AS domain,
        COUNT(*) AS domain_count
    FROM clients_email
    GROUP BY domain
) AS derived1
WHERE domain_count > 200
LIMIT 100;

Working with the JSON Data Type

MariaDB JSON Functions:

Examples of JSON Functions

JSON_VALUE Used to pull a scalar value from JSON data

SELECT 
    name, 
    latitude, 
    longitude, 
    JSON_VALUE(attr, '$.details.foodType') AS food_type 
FROM locations 
WHERE type = 'R';

| name   | latitude   | longitude   | food_type |
|--------|------------|-------------|-----------|
| Shogun | 34.1561131 | -118.131943 | Japanese  |

JSON_QUERY Used to return entire JSON object data

SELECT 
    name, 
    latitude, 
    longitude, 
     JSON_QUERY(attr, '$.details') AS details 
FROM locations 
WHERE type = 'R';

| name   | latitude   | longitude   | details                                                                                   |
|--------|------------|-------------|-------------------------------------------------------------------------------------------|
| Shogun | 34.1561131 | -118.131943 | {"foodType": "Japanese", "menu": "https://www.restaurantshogun.com/menu/teppan-1-22.pdf"} |

JSON_VALID Used to validate JSON values

CREATE TABLE locations (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    type CHAR(1) NOT NULL,
    latitude DECIMAL(9,6) NOT NULL,
    longitude DECIMAL(9,6) NOT NULL,
    attr LONGTEXT CHARACTER SET utf8mb4
    COLLATE utf8mb4_bin DEFAULT NULL
    CHECK (JSON_VALID('attr'))
    PRIMARY KEY (id)
);

JSON_INSERT Used to insert fields

UPDATE locations
    SET attr = JSON_INSERT(attr,'$.nickname','The Bean') WHERE id = 8;

JSON_ARRAY Used to create new arrays

UPDATE locations
    SET attr = JSON_INSERT(attr, '$.foodTypes',
    JSON_ARRAY('Asian', 'Mexican'))
    WHERE id = 1;

JSON_TABLE Used to convert data

SELECT l.name, d.food_type, d.menu
FROM locations AS l,
     JSON_TABLE(l.attr,
       '$' COLUMNS(
         food_type VARCHAR(25) PATH '$.foodType',
         menu VARCHAR(200) PATH '$.menu')
     ) AS d
WHERE id = 2;

| name   | food_type | menu                                                  |
|--------|-----------|-------------------------------------------------------|
| Shogun | Japanese  | https://www.restaurantshogun.com/menu/teppan-1-22.pdf |

Lesson Summary

For academic and non-commercial usage, licensed under CC BY-NC-SA 4.0 by MariaDB plc.