MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL CREATE TABLE: Creates A Data Table

In this tutorial, you'll learn how to create a new table in a MySQL database using the CREATE TABLE statement.

Prerequisites:

A MySQL server up and running
Access to a MySQL user account with privileges to create tables

Tutorial:

Connect to the MySQL server:

To start the mysql command-line client, open a terminal or command prompt, and enter:

mysql -u [username] -p

Replace [username] with your MySQL username and enter your password when prompted.

Select a database:

Select the database where you want to create the new table:

USE [database_name];

Replace [database_name] with the name of your database.

Create a new table:

To create a new table, use the CREATE TABLE statement with the following syntax:

CREATE TABLE [table_name] (
    [column_name1] [data_type1] [column_constraints1],
    [column_name2] [data_type2] [column_constraints2],
    ...
    [column_nameN] [data_typeN] [column_constraintsN]
);

Replace [table_name] with the name of your new table. For each column, replace [column_name] with the column name, [data_type] with the column's data type, and [column_constraints] with any constraints (e.g., NOT NULL, DEFAULT, PRIMARY KEY, etc.).

For example, to create a users table with the following columns:

id: integer, primary key, auto-incrementing
first_name: varchar(50), not null
last_name: varchar(50), not null
email: varchar(100), unique, not null

Use the following CREATE TABLE statement:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

Verify the new table:

To check if the new table has been created successfully, use the SHOW TABLES command:

SHOW TABLES;

You should see your new table listed among the tables in the selected database.

To view the structure of your new table, use the DESCRIBE command:

DESCRIBE [table_name];

Replace [table_name] with the name of your new table. You should see the columns and their respective data types and constraints.

Exit the MySQL command-line client:

EXIT;

Now you have successfully created a new table in a MySQL database using the CREATE TABLE statement. With a basic understanding of table creation, you can start designing and building the schema for your database to support various applications and data storage needs.

Creating MySQL tables with CREATE TABLE:

Syntax:

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

Example:

CREATE TABLE employees (
  emp_id INT,
  emp_name VARCHAR(255),
  emp_salary DECIMAL(10, 2)
);

Specifying primary key in MySQL CREATE TABLE:
- Set a primary key for the table to uniquely identify each record:
```
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(255)
);
```
Creating auto-incremented fields in MySQL table:
- Use the AUTO_INCREMENT attribute for auto-incremented fields:
```
CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(255)
);
```

MySQL CREATE TABLE example with multiple columns:

Define multiple columns in the CREATE TABLE statement:

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  PRIMARY KEY (order_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Defining default values in MySQL table creation:

Specify default values for columns:

CREATE TABLE contacts (
  contact_id INT,
  contact_name VARCHAR(255) DEFAULT 'Unknown'
);

Creating indexes in MySQL tables:

Add indexes to columns for faster data retrieval:

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(255),
  author VARCHAR(255),
  INDEX idx_author (author)
);

Using storage engines with MySQL CREATE TABLE:

Specify the storage engine (e.g., InnoDB or MyISAM):

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255)
) ENGINE=InnoDB;

Creating temporary tables in MySQL:
- Create temporary tables that exist only for the duration of the session:
```
CREATE TEMPORARY TABLE temp_data (
  temp_id INT,
  temp_value VARCHAR(255)
);
```