MySQL Create Database and Tables

Introduction

In MySQL, creating databases and tables is the first step towards organizing and managing your data. In this article, we will explore the process of creating databases and tables in MySQL with examples to illustrate the syntax and usage.

Create a Database

To create a database in MySQL, you can use the following SQL statement:

CREATE DATABASE mydatabase;

This statement creates a new database named "mydatabase".

Create a Table

Tables are used to store data in MySQL. To create a table, you need to specify the table name and define the columns (fields) and their respective data types. Here's an example:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary DECIMAL(10, 2)
);

This statement creates a table named "employees" with four columns: "id" (integer type, primary key), "name" (variable-length string type with a maximum length of 50 characters), "age" (integer type), and "salary" (decimal type with a precision of 10 and scale of 2).

Adding Constraints to Columns

You can add constraints to the columns to enforce rules and ensure data integrity. Here are some commonly used constraints:

• PRIMARY KEY: Ensures the uniqueness of values in the column.
• NOT NULL: Ensures that the column cannot have a NULL (empty) value.
• UNIQUE: Ensures that each value in the column is unique.
• FOREIGN KEY: Establishes a relationship with another table.

Here's an example that demonstrates the usage of constraints:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  price DECIMAL(10, 2),
  category_id INT,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

This statement creates a table named "products" with four columns, including a foreign key constraint that establishes a relationship with the "categories" table.

Conclusion

Creating databases and tables is a fundamental aspect of managing data in MySQL. By following the examples provided in this article, you can create databases and define tables with columns and constraints according to your specific requirements. Understanding these concepts is essential for organizing and structuring your data effectively in MySQL.