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 DATABASE world;

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

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 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;

CustomerAddresses View

| 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

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

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

Numeric Data Types

String Data Types

All String Data Types Have A Character Set

String Data Types

Character Set May Be Global or For Schema, Table, or Column

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

Special Data Types

Built-in Functions

Manipulating Date and Time

Functions for Date and Time Manipulation

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

Functions For String Manipulation

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

JSON Functions

JSONPath Expressions
JSON_ARRAY
JSON_ARRAYAGG
JSON_ARRAY_APPEND
JSON_ARRAY_INSERT
JSON_COMPACT
JSON_CONTAINS
JSON_CONTAINS_PATH
JSON_DEPTH
JSON_DETAILED
JSON_EQUALS
JSON_EXISTS
JSON_EXTRACT
JSON_INSERT
JSON_KEYS
JSON_LENGTH
JSON_LOOSE
JSON_MERGE
JSON_MERGE_PATCH
JSON_MERGE_PRESERVE
JSON_NORMALIZE
JSON_OBJECT
JSON_OBJECTAGG
JSON_OVERLAPS
JSON_QUERY
JSON_QUOTE
JSON_REMOVE
JSON_REPLACE
JSON_SEARCH
JSON_SET
JSON_TABLE
JSON_TYPE
JSON_UNQUOTE
JSON_VALID
JSON_VALUE

Examples of JSON Functions

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  |

Used to return entire JSON object data

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

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

Examples of JSON Functions

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)
);

Used to insert fields

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

Used to create new arrays

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

Examples of JSON Functions

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.