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.

Transactions

  • 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.

Comments

Comments loading...
Loading