Tables are the primary organizational structure in SQL databases. They comprise a number of columns that reflect individual attributes of each row, or record, in the table. Being such a fundamental aspect of data organization, it’s important for anyone who works with relational databases to understand how to create, change, and delete tables as needed.
In this guide, we’ll go over how to create tables in SQL, as well as how to modify and delete existing tables.
In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:
Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.
You’ll also need a database and table loaded with some sample data with which you can practice using wildcards. If you don’t have these, you can read the following Connecting to MySQL and Setting up a Sample Database section for details on how to create a database and table which this guide will use in examples throughout.
If your SQL database system runs on a remote server, SSH into your server from your local machine:
- ssh sammy@your_server_ip
Then open up the MySQL server prompt, replacing sammy
with the name of your MySQL user account:
- mysql -u sammy -p
Create a database named tablesDB
:
- CREATE DATABASE tablesDB;
If the database was created successfully, you’ll receive output like this:
OutputQuery OK, 1 row affected (0.01 sec)
To select the tablesDB
database, run the following USE
statement:
- USE tablesDB;
OutputDatabase changed
With that, you’re ready to follow the rest of the guide and begin learning about how to create and manage tables in SQL.
To create a table in SQL, use the CREATE TABLE
command, followed by your desired name for the table:
- CREATE TABLE table_name;
Be aware that, as with every SQL statement, CREATE TABLE
statements must end with a semicolon (;
).
This example syntax will create an empty table that doesn’t have any columns. To create a table with columns, follow the table name with a list of column names and their corresponding data types and constraints, bracketed by parentheses and separated by commas:
- CREATE TABLE table_name (
- column1_name column1_data_type,
- column2_name column2_data_type,
- . . .
- columnN_name columnN_data_type
- );
As an example, say you wanted to create a table to record some information about your favorite parks in New York City. After deciding what attributes you’d like to record about each park, you would then decide on column names for each of those attributes as well as the appropriate data type for each one:
parkName
: The name of each park. There is a wide variance in the length of park names, so the varchar
data type with a maximum length of 30
characters would be appropriate.yearBuilt
: The year the park was built. Although MySQL has the year
data type, this only allows values from 1901
to 2155
. New York City has several parks built before 1901, so you might instead use the int
data type.firstVisit
: The date of your first visit to each park. MySQL has the date
data type which you might use for this column. It stores data in the format of YYYY-MM-DD
.lastVisit
: The date of your most recent visit to each park. Again, you could use the date
type for this.To create a table named faveParks
with columns that have these names and data types, you would run the following command:
- CREATE TABLE faveParks (
- parkName varchar(30),
- yearBuilt int,
- firstVisit date,
- lastVisit date
- );
OutputQuery OK, 0 rows affected (0.01 sec)
Keep in mind that this only creates the table’s structure, as you haven’t added any data to the table.
You can also create new tables out of existing ones with the CREATE TABLE AS
syntax:
- CREATE TABLE new_table_name AS (
- SELECT column1, column2, . . . columnN
- FROM old_table_name
- );
Instead of following the new table’s name with a list of columns and their data types, you follow it with AS
and then, in parentheses, a SELECT
statement that returns whatever columns and data from the original table you’d like to copy over to the new table.
Note that if the original table’s columns hold any data, all that data will be copied into the new table as well. Also, for clarity, this example syntax includes a SELECT
query that only has the requisite FROM
clause. However, any valid SELECT
statement will work in this place.
To illustrate, the following command creates a table named parkInfo
from two columns in the faveParks
table created previously:
- CREATE TABLE parkInfo AS (
- SELECT parkName, yearBuilt
- FROM faveParks
- );
OutputQuery OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
If the faveParks
table had held any data, the data from its parkName
and yearBuilt
columns would have been copied to the parkInfo
table as well, but in this case both tables will be empty.
If you try creating a table using the name of an existing table, it will cause an error:
- CREATE TABLE parkInfo (
- name varchar(30),
- squareFootage int,
- designer varchar(30)
- );
OutputERROR 1050 (42S01): Table 'parkInfo' already exists
To avoid this error, you can include the IF NOT EXISTS
option in your CREATE TABLE
command. This will tell the database to check whether a database with the specified name already exists and, if so, to issue a warning instead of an error:
- CREATE TABLE IF NOT EXISTS parkInfo (
- name varchar(30),
- squareFootage int,
- designer varchar(30)
- );
OutputQuery OK, 0 rows affected, 1 warning (0.00 sec)
This command will still fail to create a new table, since the table named parkInfo
still exists. Notice, though, that this output indicates that the CREATE TABLE
statement led to a warning. To view the warning message, run the diagnostic SHOW WARNINGS
statement:
- SHOW WARNINGS;
Output| Level | Code | Message |
+-------+------+---------------------------------+
| Note | 1050 | Table 'parkInfo' already exists |
+-------+------+---------------------------------+
1 row in set (0.00 sec)
As this output indicates, the same error you received previously has been registered as a warning because you included the IF NOT EXISTS
option. This can be useful in certain cases, like when running transactions; an error will cause the entire transaction to fail, while a warning will mean only the statement that caused it will fail.
There are times when you may need to change a table’s definition. This is different from updating the data within the table; instead, it involves changing the structure of the table itself. To do this, you would use the ALTER TABLE
syntax:
- ALTER TABLE table_name ALTER_OPTION sub_options . . . ;
After beginning the ALTER TABLE
statement, you specify the name of the table you want to change. Then, you pass whichever options are available in your RDBMS to perform the alteration you have in mind.
For example, you may want to rename the table, add a new column, drop an old one, or change a column’s definition. You can continue reading to practice these examples on the faveParks
table created previously in the Creating Tables section.
To change the name of the faveParks
table, you could use the RENAME TO
syntax. This example changes the faveParks
table’s name to faveNYCParks
:
Warning: Be careful when renaming a table. Doing so can cause problems if an application uses the table or other tables in the database reference it.
- ALTER TABLE faveParks RENAME TO faveNYCParks;
OutputQuery OK, 0 rows affected (0.01 sec)
To add a new column, you’d pass the ADD COLUMN
option. The following example adds a column named borough
, which holds data of the varchar
type, but with a maximum length of 20
characters, to the faveNYCParks
table:
- ALTER TABLE faveNYCParks ADD COLUMN borough varchar(20);
OutputQuery OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
To delete a column and any data it holds from a table, you could use the DROP TABLE
syntax. This example command drops the borough
column:
- ALTER TABLE faveNYCParks DROP COLUMN borough;
Many SQL implementations allow you to change a column’s definition with ALTER TABLE
. The following example uses MySQL’s MODIFY COLUMN
clause, changing the yearBuilt
column to use the smallint
data type rather than the original int
type:
- ALTER TABLE faveNYCParks MODIFY COLUMN yearBuilt smallint;
Be aware that every RDBMS has different options for what you can change with an ALTER TABLE
statement. To understand the full scope of what you can do with ALTER TABLE
, you should consult your RDBMS’s official documentation to learn what ALTER TABLE
options are available for it.
Here’s the official documentation on the subject for a few popular open-source databases:
ALTER TABLE
DocumentationALTER TABLE
DocumentationALTER TABLE
DocumentationTo delete a table and all of its data, use the DROP TABLE
syntax:
Warning: Be careful when running the DROP TABLE
command, as it will delete your table and all its data permanently.
- DROP TABLE table_name;
You can delete multiple tables with a single DROP
statement by separating their names with a comma and a space, like this:
- DROP TABLE table1, table2, table3;
To illustrate, the following command will delete the faveNYCParks
and parkInfo
tables created earlier in this guide:
- DROP TABLE IF EXISTS faveNYCParks, parkInfo;
Note that this example includes the IF EXISTS
option. This has the opposite function of the IF NOT EXISTS
option available for CREATE TABLE
. In this context, IF EXISTS
will cause the DROP TABLE
statement to return a warning instead of an error message if one of the specified tables doesn’t exist.
By reading this guide, you learned how to create, change, and delete tables in SQL-based databases. The commands outlined here should work on any database management system that uses SQL. Keep in mind that every SQL database uses its own unique implementation of the language, so you should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.
If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
Structured Query Language — commonly known as SQL — is a language used to define, control, manipulate, and query data held in a relational database. SQL has been widely adopted since it was first developed in the 1970s, and today it’s the predominant language used to manage relational database management systems.
Ideal for managing structured data (data that can fit neatly into an existing data model), SQL is an essential tool for developers and system administrators in a wide variety of contexts. Also, because of its maturity and prevalence, candidates with SQL experience are highly sought after for jobs across a number of industries.
This series is intended to help you get started with using SQL. It includes a mix of conceptual articles and tutorials which provide introductions to various SQL concepts and practices. You can also use the entries in this series for reference while you continue to hone your skills with SQL.
Note: Please be aware that the tutorials in this series use MySQL in examples, but many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
There is a typo in this sentence
To delete a column and any data it holds from a table, you could use the
DROP TABLE
syntax. This example command drops theborough
column:It should be drop
DROP COLUMN