Basic SQL Statements

From Hashmysql
Jump to: navigation, search

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


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