This page contains 11 of the most commonly used SQL statements, along with a basic example of usage.
SQL is the standard language for querying relational databases.
SQL SELECT Statement
The SELECT statement lets you retrieve data from the database. You can choose one or more tables, as well as which specific columns you want to select data from.
The following example selects two columns from the Artists table.
SELECT ArtistName, ArtistBio FROM Artists;
The following example selects all columns from the Artists table.
SELECT * FROM Artists;
See SQL SELECT for Beginners for more examples.
SQL INSERT Statement
The INSERT statement lets you insert new rows into a table (i.e. add new data).
Here, we insert a new row into the Albums table.
INSERT INTO Albums (AlbumName, ArtistId) VALUES
('Ziltoid the Omniscient', '12');
This can also be shortened to the following:
INSERT INTO Albums VALUES ( DEFAULT, 'Ziltoid the Omniscient', '12' );
Using the DEFAULT keyword will populate the first column with the default constraint that has been configured for that column (assuming one has been configured).
To insert multiple rows:
INSERT INTO Albums (AlbumName, ArtistId) VALUES
('Ziltoid the Omniscient', '12'),
('Flying in a Blue Dream', '7');
See SQL INSERT for Beginners for more examples.
SQL UPDATE Statement
The UPDATE statement allows you to update one or more records in the database.
In the following example, we update the value of the ArtistName column in the Artists table.
UPDATE Artists SET ArtistName = 'The Artist Formerly Known as...' WHERE ArtistName = 'Prince';
Although, it’s usually better practice to use the unique identifier if it’s known. So something like this:
UPDATE Artists SET ArtistName = 'The Artist Formerly Known as...' WHERE ArtistId = '7';
See SQL UPDATE for Beginners for more examples.
SQL DELETE Statement
The DELETE statement lets you delete specified rows from a table.
The following SQL statement deletes uses a WHERE clause to narrow down the deleted rows.
DELETE FROM Artists WHERE ArtistId = '6';
Omitting the WHERE clause will delete all rows from the table.
DELETE FROM Artists;
See SQL DELETE for Beginners for more examples.
SQL TRUNCATE Statement
The TRUNCATE statement is used to delete all rows from a table. It is faster than the DELETE statement and uses fewer system and transaction log resources.
The TRUNCATE statement is ideal on larger tables.
TRUNCATE TABLE Artists;
SQL CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new database.
CREATE DATABASE Movies;
The CREATE DATABASE statement is typically followed up with CREATE TABLE and other statements in order to create a complete database ready for use. See how to create a database from a script in MySQL for an example.
SQL ALTER DATABASE Statement
The ALTER DATABASE statement is used to modify a database, or the files and filegroups associated with the database.
The following SQL statement changes the collation of the Movies database to utf8_unicode_ci.
ALTER DATABASE Movies COLLATE utf8_unicode_ci ;
For the exact syntax, see SQL ALTER DATABASE Syntax – Listed by DBMS.
SQL DROP DATABASE Statement
The DROP DATABASE statement is used to drop (remove) a database.
DROP DATABASE Movies;
For the exact syntax, see SQL DROP DATABASE Syntax – Listed by DBMS.
SQL CREATE TABLE Statement
The CREATE TABLE statement allows you to create a table in a database. When you create a table, you also specify the columns and their datatypes, as well as any constraints and other settings that might be required.
Here’s an example of creating a table called Actors with two columns. We also set the ActorId column as the primary key.
CREATE TABLE Actors ( ActorId INT NOT NULL AUTO_INCREMENT, ActorName VARCHAR(255) NOT NULL, PRIMARY KEY (ActorId));
See SQL CREATE TABLE for Beginners for more examples.
SQL ALTER TABLE Statement
The ALTER TABLE statement is used to change the definition of a table.
The following SQL statement adds a column to a table and specifies its data type.
ALTER TABLE Movies ADD COLUMN YearReleased DATETIME;
See SQL ALTER TABLE for Beginners for more examples.
SQL DROP TABLE Statement
The DROP TABLE statement is used to drop (remove) a table. Simply add the name of the table and the whole table will be removed from the database.
DROP TABLE Actors;
See SQL DROP TABLE for Beginners for more examples.
Using the GUI
Most DBMSs allow you to do all of the above tasks via a graphical user interface (GUI). For example, you can use MySQL Workbench or SQL Server Management Studio to “point and click” your way through without having to remember the SQL code.
Further Reference
Also see: