MySql - Creating databases and tables

Creating Databases and Tables in MySQL

MySQL is one of the most widely used open-source relational database management systems. Creating databases and tables is a fundamental part of working with MySQL. Whether you're building a simple web app or a complex enterprise system, understanding how to define the structure of your database is crucial. This guide explores the syntax, best practices, constraints, examples, and considerations when creating databases and tables in MySQL.

1. Introduction to MySQL Database

What is a Database?

A database is an organized collection of data. In MySQL, a database is a namespace/container that holds one or more tables. Each database can contain tables, views, indexes, stored procedures, functions, and triggers.

Accessing the MySQL Server

You typically use a MySQL client (like the MySQL command-line tool or GUI tools such as MySQL Workbench, phpMyAdmin) to connect to the server and execute SQL statements.

mysql -u root -p

2. Creating a Database

Syntax

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

Example

CREATE DATABASE IF NOT EXISTS company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

Explanation

• IF NOT EXISTS: Prevents error if the database already exists.
• CHARACTER SET: Defines default character encoding.
• COLLATE: Specifies rules for character string comparison.

Viewing Databases

SHOW DATABASES;

Using a Database

USE company_db;

Dropping a Database

DROP DATABASE company_db;

3. Creating Tables

Basic Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Example

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id INT
);

Explanation

• INT, VARCHAR, DATE, DECIMAL: Data types.
• PRIMARY KEY: Uniquely identifies each row.
• AUTO_INCREMENT: Automatically generates the next ID value.
• NOT NULL: Disallows NULL values.
• UNIQUE: Ensures the column contains unique values.

Checking Table Structure

DESCRIBE employees;

Viewing All Tables

SHOW TABLES;

Dropping a Table

DROP TABLE employees;

4. Data Types Overview

Numeric Types

• INT, SMALLINT, TINYINT, BIGINT
• DECIMAL(p,s): Fixed point, useful for currency.
• FLOAT, DOUBLE: Floating-point numbers.

String Types

• CHAR(n): Fixed-length string.
• VARCHAR(n): Variable-length string.
• TEXT, MEDIUMTEXT, LONGTEXT: Large texts.

Date and Time Types

• DATE, DATETIME, TIMESTAMP
• TIME, YEAR

Boolean Type

• BOOLEAN or TINYINT(1) are used to represent true/false values.

5. Table Constraints

Primary Key

Ensures uniqueness and non-nullability of a column or set of columns.

PRIMARY KEY (employee_id)

Foreign Key

Links two tables together.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Unique

Ensures column values are unique.

email VARCHAR(100) UNIQUE

Not Null

Prevents NULL values.

first_name VARCHAR(50) NOT NULL

Check

Validates data based on a condition (supported in MySQL 8.0+).

salary DECIMAL(10,2) CHECK (salary > 0)

6. Auto-Incrementing Columns

Syntax

employee_id INT AUTO_INCREMENT PRIMARY KEY

Use Cases

• Automatically generate unique values for primary keys.

Resetting Auto Increment

ALTER TABLE employees AUTO_INCREMENT = 1000;

7. Altering Tables

Add Column

ALTER TABLE employees ADD phone_number VARCHAR(15);

Drop Column

ALTER TABLE employees DROP COLUMN phone_number;

Modify Column

ALTER TABLE employees MODIFY salary DECIMAL(12,2);

Rename Table

RENAME TABLE employees TO staff;

8. Temporary Tables

Definition

Temporary tables exist during the session and are automatically dropped when the session ends.

Syntax

CREATE TEMPORARY TABLE temp_data (
    id INT,
    value VARCHAR(100)
);

Use Case

• Storing intermediate results.

9. Views and Virtual Tables

Definition

A view is a virtual table based on the result of a SELECT query.

Syntax

CREATE VIEW employee_view AS
SELECT first_name, last_name, department_id
FROM employees;

Benefits

• Encapsulation and abstraction of complex queries.
• Improved security and access control.

Creating databases and tables in MySQL is a critical step in designing robust data storage solutions. Understanding how to define and enforce structure using data types, constraints, and relationships ensures data integrity and scalability. This guide has covered everything from simple table creation to more advanced topics like normalization, constraints, and best practices. With this foundational knowledge, you're now equipped to model and implement relational databases effectively using MySQL.