Create Database and Create Table in SQL
Before you can store data you need a database to hold your tables and a table with the right columns. This lesson creates a school database and a students table from scratch, inspects the result, and shows the shortcuts every developer uses: IF NOT EXISTS and CREATE TABLE ... AS SELECT.
- Create and select a database with
CREATE DATABASEandUSE - Define a table with columns, data types and a primary key
- Inspect a table with
DESCRIBEandSHOW TABLES - Copy query results into a new table with
CREATE TABLE AS SELECT
CREATE DATABASE
A database (MySQL also calls it a schema) is a named container for tables. Create one and check that it exists:
CREATE DATABASE school;SHOW DATABASES;| Database |
|---|
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
CREATE DATABASE schoolcreates an empty database calledschool.- MySQL answers
Query OK, 1 row affected: it reports one database created. SHOW DATABASESlists it next to the four system databases that every server has.
Running it twice fails, because the name is taken:
CREATE DATABASE school;CREATE DATABASE school;ERROR 1007 (HY000): Can't create database 'school'; database exists
Add IF NOT EXISTS to make the statement safe to re-run. MySQL then skips it with a warning instead of an error:
CREATE DATABASE school;CREATE DATABASE IF NOT EXISTS school;Set the character set when you create a database that stores names or text in several languages: CREATE DATABASE school CHARACTER SET utf8mb4; In MySQL 8.4 utf8mb4 is already the default.
USE: select the database
Tables always live inside a database. USE makes one the current database, so you can write students instead of school.students:
CREATE DATABASE school;USE school;SELECT DATABASE() AS current_db;| current_db |
|---|
| school |
USE schoolswitches the session to theschooldatabase.DATABASE()returns the name of the current database, so the result isschool.
CREATE TABLE syntax
CREATE TABLE table_name ( column1 datatype [constraints], column2 datatype [constraints], ... [table constraints]);Each column needs a name and a data type (see SQL Data Types). Constraints such as NOT NULL and PRIMARY KEY add rules the data must follow.
Create a table
CREATE DATABASE school;USE school;CREATE TABLE students ( student_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, email VARCHAR(100) UNIQUE, birth_date DATE, grade INT DEFAULT 1);DESCRIBE students;| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| student_id | int | NO | PRI | NULL | |
| full_name | varchar(60) | NO | NULL | ||
| varchar(100) | YES | UNI | NULL | ||
| birth_date | date | YES | NULL | ||
| grade | int | YES | 1 |
CREATE TABLE students (...)creates the table with five columns.student_id INT PRIMARY KEY: a whole number that must be unique and not empty. It identifies each student.full_name VARCHAR(60) NOT NULL: text up to 60 characters that must always be filled.email VARCHAR(100) UNIQUE: two students cannot share an email.birth_date DATEstores a calendar date, andgrade INT DEFAULT 1gets the value 1 when an insert leaves it out.DESCRIBE studentsshows each column’s type, whether it accepts NULL (Null), its key (PRI= primary key,UNI= unique) and its default.
Insert rows and read them back
A new table is empty. Add three rows with INSERT and read them:
CREATE TABLE students ( student_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, grade INT DEFAULT 1);INSERT INTO students (student_id, full_name, grade) VALUES (1, 'Aarav Patel', 3), (2, 'Diya Menon', 2);INSERT INTO students (student_id, full_name) VALUES (3, 'Kabir Ali');SELECT * FROM students;| student_id | full_name | grade |
|---|---|---|
| 1 | Aarav Patel | 3 |
| 2 | Diya Menon | 2 |
| 3 | Kabir Ali | 1 |
CREATE TABLEmakes an emptystudentstable.- The first
INSERTadds two rows in one statement:2 rows affected. - The second
INSERTleaves outgrade, so Kabir gets the default value 1. SELECT *shows all three rows.
CREATE TABLE IF NOT EXISTS
Creating a table that already exists is an error:
CREATE TABLE departments (dept_id INT);ERROR 1050 (42S01): Table 'departments' already exists
Setup scripts use IF NOT EXISTS so they can run again safely. The existing table is left untouched, and MySQL raises a note you can read with SHOW WARNINGS:
CREATE TABLE IF NOT EXISTS departments (dept_id INT);SHOW WARNINGS;| Level | Code | Message |
|---|---|---|
| Note | 1050 | Table 'departments' already exists |
SHOW TABLES and DESCRIBE
SHOW TABLES;| Tables_in_company |
|---|
| customers |
| departments |
| employees |
| order_items |
| orders |
| products |
DESCRIBE products;| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| product_id | int | NO | PRI | NULL | |
| product_name | varchar(50) | NO | NULL | ||
| category | varchar(30) | YES | NULL | ||
| price | int | YES | NULL | ||
| stock | int | YES | NULL |
SHOW TABLESlists every table in the current database (company).DESCRIBE products(orDESC products) shows the columns of one table.product_idis the primary key, andproduct_namedoes not allow NULL.
CREATE TABLE AS SELECT
CREATE TABLE ... AS SELECT creates a new table and fills it with the result of a query. It is a quick way to save a report or make a backup copy before you change data:
CREATE TABLE high_earners ASSELECT emp_id, first_name, salaryFROM employeesWHERE salary >= 100000; SELECT * FROM high_earners;| emp_id | first_name | salary |
|---|---|---|
| 100 | Meera | 250000 |
| 101 | Asha | 125000 |
| 105 | Vikram | 105000 |
- The
SELECTfinds employees earning 100,000 or more: three rows. CREATE TABLE high_earners AScreates a table with the same three columns and copies those rows in.- The new table is independent: later changes to
employeesdo not change it.
CREATE TABLE ... AS SELECT copies columns and data, but not the primary key, indexes or foreign keys. To copy the structure exactly, use CREATE TABLE new_table LIKE old_table; and then INSERT INTO new_table SELECT * FROM old_table;.
Exercise: Create a courses table
Create a table courses with course_id (whole number, primary key), title (text up to 80 characters, required) and fee (whole number, default 0). Insert two courses, one without a fee, and show all rows.
| course_id | title | fee |
|---|---|---|
| 1 | SQL for Testers | 4999 |
| 2 | Intro to Databases | 0 |
Hint 1
Use INT PRIMARY KEY, VARCHAR(80) NOT NULL and INT DEFAULT 0.
Hint 2
Leave fee out of the column list in the second insert.
CREATE TABLE courses ( course_id INT PRIMARY KEY, title VARCHAR(80) NOT NULL, fee INT DEFAULT 0);INSERT INTO courses (course_id, title, fee) VALUES (1, 'SQL for Testers', 4999);INSERT INTO courses (course_id, title) VALUES (2, 'Intro to Databases');SELECT * FROM courses;| course_id | title | fee |
|---|---|---|
| 1 | SQL for Testers | 4999 |
| 2 | Intro to Databases | 0 |
Quick quiz
1. What does USE school; do?
After USE, table names without a database prefix refer to school.
2. A column is defined as grade INT DEFAULT 1. What does an insert that leaves out grade store?
The default value is used when the column is not given.
3. Which statement copies a query result into a new table?
CREATE TABLE new AS SELECT ... creates and fills the table in one step.
4. What does IF NOT EXISTS change?
The existing table is kept as it is.
CREATE TABLE FAQ
What is the difference between a database and a schema in MySQL?
In MySQL they are the same thing: CREATE SCHEMA is a synonym for CREATE DATABASE. In PostgreSQL and SQL Server a schema is a namespace inside a database.
How do I see the structure of a table in MySQL?
Use DESCRIBE table_name; for a column list, or SHOW CREATE TABLE table_name; for the full CREATE TABLE statement including indexes and foreign keys.
How do I copy a table in SQL?
CREATE TABLE copy AS SELECT * FROM original; copies columns and data. CREATE TABLE copy LIKE original; copies the exact structure with indexes but no rows.
How do I create a table only if it does not exist?
Write CREATE TABLE IF NOT EXISTS table_name (...). If the table exists, MySQL leaves it unchanged and issues a note instead of an error.