My Web Training

📖 MySQL Create a Database and Tables

Creating a database is a precise activity that takes planning and careful construction. The tables and fields must be designed in a way that supports the business model. Here we will see how to create the database and its objects.

Add a Database

CREATE DATABASE [IF NOT EXISTS] db_name;

Delete a Database

DROP DATABASE [IF EXISTS] db_name;

Select or Use a Database

USE DATABASE db_name;

Creating a Table Within a Database

When creating a table you specify the table name, the column names for the table, the data_types for each column, and any attributes you might assign to a specific column.

Example

CREATE TABLE students (
    student_id INT AUTO_INCREMENT,
    student_name VARCHAR(75) NOT NULL UNIQUE,
    PRIMARY KEY (student_id)
);

In the above example, the table would not allow a duplicate of student_id, but it would also not allow for two students named “Maria Sanchez”. Additionally, when a student entity is added to the table it would not allow a manually assigned student_id number if it is not specified.

Common Column Attributes

NOT NULL

When inserting rows in the table the column must contain data.

UNIQUE

The value stored in the column must be different from any other rows’ corresponding value.

DEFAULT

If no value is passed to the column, assign a value to it.

AUTO_INCREMENT

Generate a unique number automatically. Only one column in a table may possess this attribute. Often used when a primary key is not readily apparent from the data.

Table/column Constraints

Primary Key Constraint

A primary key is a unique identifier for each record in the database table. It may be assigned as a column constraint if the column serves as the unique identifier of each record.

Foreign Key Constraint

Used on the many side of a one-to-many relationship to define the relationship between tables and to enforce referential integrity.

Referential Integrity

A record may not be added to a table that contains a foreign key unless there is a corresponding record in the linked table.

A record cannot be deleted if any related entries exist with that foreign key.

In MySQL, referential integrity is only enforced if using InnoDB storage engines for your tables.

Example

CREATE TABLE orders (
    order_num INT PRIMARY KEY,
    customer_id INT REFERENCES customers (customer_id),
    amount DOUBLE NOT NULL
);

This constraint would prevent deletion of a row in the customers table if there is an order related to that customer. In most cases you want to enforce referential integrity in this type of situation, otherwise you encounter a situation involving orphaned records. In the example above a row in the orders table would not contain information about the customer that placed the order.

ON DELETE CASCADE Clause in a Foreign Key Constraint

In the rare instance when you would want the related rows in the foreign key table(s) to automatically delete when the row in the primary key table is deleted use this syntax.

CREATE TABLE orders (
    order_num INT PRIMARY KEY,
    customer_id INT,
    amount DOUBLE NOT NULL,
    CONSTRAINT orders_fk_customers FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE CASCADE
);

This statement enforces referential integrity, which means that an order cannot be created for a non-existent customer but the above statement would delete any orders for a customer if the row in the customers table is deleted.

Character Sets and Collations

Character sets determines which characters can be stored and how many bytes are used to store the characters. Because these characters are stored coded and latin1 is a subset of utf8, any application using a different character set from the database may have unexpected characters displayed at times. The character set used by the database should always match (or at least be compatible with) the character set of the application connected to it.

• Latin1 – default character set in older MySQL installation. One byte stores each character. This incorporates almost all characters used in Western European languages. Limitation is that other characters are not stored.
• UTF8 – incorporates all characters in the UNICODE character set. Most language’s characters are mapped to a numeric representation of that character. Downside is that it stores character data up to 3 bytes.

According to the MySQL 8.0 Reference docs: "The default MySQL server character set and collation are utf8mb4 and utf8mb4_0900_ai_ci, but you can specify character sets at the server, database, table, column, and string literal levels. The default collation for utf8mb4 differs between MySQL 5.7 and 8.0 (utf8mb4_general_ci for 5.7, utf8mb4_0900_ai_ci for 8.0)."

Collation refers to the way a character set is sorted where in utf8mb4_general_ci the _ci indicates that the character sorts are not case sensitive.

Collation Suffix Meanings

Suffix	Meaning
_ai	Accent-insensitive
_as	Accent-sensitive
_ci	Case-insensitive
_cs	Case-sensitive
_ks	Kana-sensitive
_bin	Binary

To view the database settings for character set and collation, use the following commands.

SHOW CHARSET;
-- displays all available character sets on a server

SHOW COLLATION;
-- displays all available collations for a server

SHOW VARIABLES LIKE 'character_set_database';
-- displays default character set for a server

SHOW VARIABLES LIKE  'collation_database';
-- displays default collation for a server

Storage Engines

Primary difference is that InnoDB supports foreign key constraints and transactions.

InnoDB

The default storage engine for MySQL 5.5 and later.

MyISAM

The default storage engine for previous versions of MySQL.

To display or view all storage engines for a server.

SHOW ENGINES;

Table Status

The SHOW TABLE STATUS command can show us the table attributes including the character set, collation and db engine as well as much more information.

Format

SHOW TABLE STATUS
    [FROM db_name]
    [LIKE 'pattern']
-- db_name must be from an accessible database
-- A table name can be used in the LIKE clause 

Example

SHOW TABLE STATUS
    FROM `classdb`
    LIKE 'artist'
-- Notice the back ticks (optional) for the table name and the single quotes (required) for the column name in the LIKE clause