WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

MySQL Create Table

Previous Next

MySQL CREATE TABLE Statement


The CREATE TABLE statement in MySQL is used to define a new table within a database, specifying column names, data types, and constraints.



Syntax


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


  • table_name: Name of the table.
  • column1, column2, ...: Column names.
  • datatype: Defines the type of data (e.g., INT, VARCHAR, DATE).
  • constraints: Rules like PRIMARY KEY, NOT NULL, UNIQUE, etc.
Example: Creating a Students Table
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT CHECK (age >= 18),
email VARCHAR(100) UNIQUE,
enrollment_date DATE DEFAULT CURRENT_DATE
);

✅ student_id is an AUTO_INCREMENT PRIMARY KEY (unique ID for each student).

✅ name cannot be NULL (NOT NULL).

✅ age must be 18 or older (CHECK (age >= 18)).

✅ email must be unique (UNIQUE).

✅ enrollment_date defaults to current date (DEFAULT CURRENT_DATE).

Check All Tables in a Database


SHOW TABLES;


View Table Structure


DESC students;

MySQL CREATE TABLE Using Another Table


In MySQL, you can create a new table using the structure or data from an existing table.


1. Create a Table with the Same Structure (Without Data)


To create a new table with the same structure as an existing table without copying data, use:


CREATE TABLE new_table LIKE existing_table;
Example
CREATE TABLE students_backup LIKE students;
  • This creates students_backup with the same columns and data types as students but without any data.

2. Create a Table and Copy Data from Another Table


To create a table and copy data from an existing table:


Syntax:


CREATE TABLE new_table AS SELECT * FROM existing_table;
Example
CREATE TABLE students_copy AS SELECT * FROM students;
  • This creates students_copy with the same structure and all data from students.

3. Copy Specific Columns from Another Table


To copy only specific columns into the new table:

Example
CREATE TABLE new_students AS SELECT student_id, name FROM students;
  • This creates new_students with only student_id and name from students.

4, Copy Data with a Condition


To copy only specific rows into the new table:

Example
CREATE TABLE adult_students AS SELECT * FROM students WHERE age >= 18;
  • This creates adult_students containing only students 18 years or older.

Key Differences Between LIKE & AS SELECT


Method

Copies Structure

Copies Data

Copies Constraints (e.g., PRIMARY KEY, AUTO_INCREMENT)

CREATE TABLE new_table LIKE existing_table;

✅ Yes

❌ No

✅ Yes

CREATE TABLE new_table AS SELECT * FROM existing_table;

✅ Yes

✅ Yes

❌ No

Note

✔ Use LIKE when you only want the structure.

✔ Use AS SELECT when you need both structure and data but without constraints.


Previous Next