Lesson 2: Schema objects

Schema objects

Learning objectives

Databases, tables and default schemas

Case sensitivity

Depending on Operating System, File System and lower_case_table_names

Set before starting a project (strongly recommended)!

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

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

Databases

Database aka Schema

CREATE DATABASE world;

Tables

Stores rows of structured data organised by typed columns

Each corresponds to a metadata file (.frm) and data file(s), dependent on storage engine
(i.e. InnoDB tablespaces)

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

Columns

Column attributes

Columns have strict type definitions

Can specify DEFAULT value for column

Only 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

Indexes

Constraints

Views

Stored routines

Default schemas

information_schema

mysql

Stores information on:

performance_schema

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,
MODIFY COLUMN col8 VARCHAR(10);

Basic Syntax Example for ALTER TABLE Statement

Temporal tables

TypeTracksSample Use Cases
System-VersionedChange historyAudit, forensics, IoT temperature tracking
Application-Time PeriodTime-limited valuesSales offers, subscriptions
BitemporalTime-limited values with historySchedules, decision support models

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;

Data types and built-in functions

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 rounds (sql_mode)

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

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

URL: https://mariadb.com/kb/en/mariadb/data-types-numeric-data-types/

Numeric data types

Auto increment

0 | default, Traditional

1 Consecutive

2 Interleaved

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 |

Special data types

Built-in functions

Types: String, Date and Time, Aggregate, Numeric, Control Flow

Secondary functions such as Bit Functions and Operators, Encryption, Hashing and Compression, and Information Functions

Special Functions such as Dynamic Columns, Geographic, JSON, Spider and Window Functions

Manipulating date & time

Functions for date and time manipulation

ADDDATE()
ADDTIME()
CONVERT_TZ()
CURDATE()
CURTIME()
DATE()
DATE_ADD()
DATE_FORMAT()
DATE_SUB()
DATEDIFF()
DAYNAME()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT()
FROM_DAYS()
FROM_UNIXTIME()
GET_FORMAT()
HOUR()
LAST_DAY()
MAKEDATE()
MAKETIME()
MICROSECOND()
MINUTE()
MONTH()
MONTHNAME()
NOW()
PERIOD_ADD()
PERIOD_DIFF()
QUARTER()
SEC_TO_TIME()
SECOND()
STR_TO_DATE()
SUBDATE()
SUBTIME()
SYSDATE()
TIME()
TIME_FORMAT()
TIME_TO_SEC()
TIMEDIFF()
TIMESTAMP()
TIMESTAMPADD()
TIMESTAMPDIFF()
TO_DAYS()
UNIX_TIMESTAMP()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()
WEEK()
WEEKDAY()
WEEKOFYEAR()
YEAR()
YEARWEEK()

Documentation on Date and Time Functions: https://mariadb.com/kb/en/mariadb/date-and-time-functions/

Examples of date & 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 dts=str_to_date(@dt1,'%d/%m/%Y');

Manipulating strings

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;

Aggregate functions

Used for Summary Operations

Aggregate Functions Reduce a Set of Values to a Single Value

DISTINCT Removes Duplicate Values before Aggregation (e.g., with COUNT() and GROUP_CONCAT())

SELECT COUNT(*)
FROM City;
+----------+
| COUNT(*) |
+----------+
| 4079     |
+----------+

SELECT COUNT(DISTINCT CountryCode)
FROM City;
+-----------------------------+
| COUNT(DISTINCT CountryCode) |
+-----------------------------+
| 232                         |
+-----------------------------+

Aggregate multiplication

Using logarithm logic:

A chart shows the rate of return for a publicly traded company over 10 years

With an initial investment of $1,000 in 2009, calculate the value of the investment by 2019

Aggregate multiplication

Calculating compound interest: Find each year’s ROR:

SELECT YEAR, 1+ROR AS multiplier, LOG(1+ROR) 
FROM investment;
+------+-----------+---------------------+
| YEAR | multiplier | LOG(1+ROR/100)     |
+------+-----------+---------------------+
| 2010 |     1.44  |     0.36464311193222|
| 2011 |     0.75  |    -0.28768207245178|
| 2012 |     1.08  |     0.07961013948044|
.. 
| 2017 |     1.11  |     0.10436001478726|
| 2018 |     0.92  |    -0.083381606995421|
| 2019 |     0.79  |    -0.23572232522169 |
+------+-----------+---------------------+
10 rows in set (#.## sec)

Aggregate multiplication

Calculating compound interest:

  1. Add all the logarithmic results together

    SELECT SUM(LOG(1+ROR))
FROM investment;
+--------------------+
| SUM(LOG(1+ROR))    |
+--------------------+
| 0.68010598814117   |
+--------------------+

  2. Determine the return percentage with compound interest

    SELECT EXP(SUM(LOG(1+ROR)))
FROM investment;
+------------------------+
| EXP(SUM(LOG(1+ROR)))   |
+------------------------+
|     1.9740869509493    |
+------------------------+

Aggregate multiplication

Calculating compound interest:

SELECT EXP(SUM(LOG(1+ROR)))*1000 FROM investment;

+-------------------------+
| EXP(SUM(LOG(1+ROR)))*1000 |
+-------------------------+
| 1974.0869509493          |
+-------------------------+

Running total query

In the course of almost every analytical presentation, someone will request that a running total be presented!

The following SQL example uses a self-join on the investment table to create the running total

SELECT a.YEAR, a.ROR,
    EXP(SUM(LOG(1+b.ROR)))*1000 AS balance
FROM investment a
JOIN investment b ON (a.YEAR >= b.YEAR)
GROUP BY a.YEAR, a.ROR;

Running total query

Notice how this statement works

SELECT a.YEAR, a.ROR,
EXP((LOG(1+b.ROR)))*1000 AS balance
FROM investment a
JOIN investment b ON (a.YEAR >= b.YEAR)
ORDER BY a.YEAR, a.ROR;

+------+----------+------------------+
| YEAR | ROR      | balance          |
+------+----------+------------------+
| 2000 |  0.0     | 1439.99999761581 |
| 2011 | -0.25    | 1439.99999761581 |
| 2011 | -0.25    | 1439.99999761581 |    750 |
| 2012 |  0.08    | 1439.99999761581 |
...
| 2019 | -0.21    | 1439.99999761581 |
| 2019 | -0.21    | 1439.9999821186 |    750 |
| 2019 | -0.21    | 1079.9999821186 |
+------+----------+------------------+

Control flow functions

IF(expr1,expr2,expr3)

IFNULL(expr1,exprNULL) returns expr1, or exprNULL if expr1 is NULL

Do not mix up with NULLIF(expr1,expr2) which returns expr1 if expr1<>expr2 otherwise NULL

Some numeric functions

Views, triggers and events

Views

An example of a view

CREATE VIEW emp_names AS
    SELECT emp_id, name_first, name_last
    FROM employees;

ALTER VIEW emp_names AS
    SELECT emp_id, name_first, name_middle, name_last,
    FROM employees;

SHOW FULL TABLES WHERE Table_type = 'VIEW';

SHOW CREATE VIEW emp_names \G

DROP VIEW emp_names;

Lesson summary

Lab exercises

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