Basic SQL Statements
This page lists the most important SQL statements and contains links to their documentation pages. If you need a basic tutorial on how to use the MariaDB database server and how to execute simple commands, see A MariaDB Primer.
Also see Common MariaDB Queries for examples of commonly-used queries.
Defining How Your Data Is Stored
- CREATE DATABASE is used to create a new, empty database.
- DROP DATABASE is used to completely destroy an existing database.
- USE is used to select a default database.
- CREATE TABLE is used to create a new table, which is where your data is actually stored.
- ALTER TABLE is used to modify an existing table's definition.
- DROP TABLE is used to completely destroy an existing table.
- DESCRIBE shows the structure of a table.
Manipulating Your Data
- SELECT is used when you want to read (or select) your data.
- INSERT is used when you want to add (or insert) new data.
- UPDATE is used when you want to change (or update) existing data.
- DELETE is used when you want to remove (or delete) existing data.
- REPLACE is used when you want to add or change (or replace) new or existing data.
- TRUNCATE is used when you want to empty (or delete) all data from the template.
- START TRANSACTION is used to begin a transaction.
- COMMIT is used to apply changes and end transaction.
- ROLLBACK is used to discard changes and end transaction.
A Simple Example
CREATE DATABASE mydb; USE mydb; CREATE TABLE mytable ( id INT PRIMARY KEY, name VARCHAR(20) ); INSERT INTO mytable VALUES ( 1, 'Will' ); INSERT INTO mytable VALUES ( 2, 'Marry' ); INSERT INTO mytable VALUES ( 3, 'Dean' ); SELECT id, name FROM mytable WHERE id = 1; UPDATE mytable SET name = 'Willy' WHERE id = 1; SELECT id, name FROM mytable; DELETE FROM mytable WHERE id = 1; SELECT id, name FROM mytable; DROP DATABASE mydb; SELECT count(1) from mytable; gives the number of records in the table
The first version of this article was copied, with permission, from http://hashmysql.org/wiki/Basic_SQL_Statements on 2012-10-05.