Basic SQL Statements

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.

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