Home » Learn » MySQL Tutorial » MySQL CREATE TABLE

MySQL CREATE TABLE

A table stores your data in rows and columns. CREATE TABLE defines the table name and its columns.

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  city VARCHAR(50),
  marks INT,
  course_id INT
);
MySQL says

Query OK, 0 rows affected (0.03 sec)

  • id INT AUTO_INCREMENT PRIMARY KEY: a unique number that MySQL fills automatically.
  • name VARCHAR(50) NOT NULL: text up to 50 characters, and it cannot be empty.
  • city, marks, course_id: normal columns. They can be empty (NULL).

We will use this students table in the whole series. Make sure you have selected a database with USE first.

IF NOT EXISTS

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

Check the table

SHOW TABLES;
Result
Tables_in_school
students 

DESCRIBE students;
Result
Field Type Null Key Default Extra
id int NO PRI NULL auto_increment
name varchar(50) NO NULL
city varchar(50) YES NULL
marks int YES NULL
course_id int YES NULL

The courses table

For the JOIN chapters we also need a second table.

CREATE TABLE courses (
  id INT PRIMARY KEY,
  course_name VARCHAR(50)
);

Naming rules

  • Use small letters and underscores: order_items.
  • Use plural names for tables (students) and singular for columns (name).
  • Avoid spaces and MySQL keywords like order or group as names.

Summary

  • CREATE TABLE name (column type, ...); makes a table.
  • Check it with SHOW TABLES and DESCRIBE.
  • Next: MySQL Constraints.

Share this post:

Share on LinkedIn
Congratulations! Chapter Finished. Learn more about the similar topics:
Exercises & Assignments
No Content Found.
Interview Questions & Answers
No Content Found.