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:
- A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
- MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This cheat sheet was verified with a non-root MySQL user, created using the process described in Step 3.
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.
Connecting To MySQL and Setting Up a Sample Database
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
- 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 tablesDB;
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
varchardata type with a maximum length of
30characters would be appropriate.
yearBuilt: The year the park was built. Although MySQL has the
yeardata type, this only allows values from
2155. New York City has several parks built before 1901, so you might instead use the
firstVisit: The date of your first visit to each park. MySQL has the
datedata type which you might use for this column. It stores data in the format of
lastVisit: The date of your most recent visit to each park. Again, you could use the
datetype 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
faveParks table had held any data, the data from its
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
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
- 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
- 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
- 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:
To 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
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.
Originally posted on DigitalOcean Community Tutorials
Author: Mark Drake