1.3.3 Databases

• SELECT:

  Returns a subset of records from the database.

  SELECT * (wildcard)

  SELECT columnList FROM table

  WHERE column operator value (e.g town = “Colchester”, postcode LIKE “CO%”, age BETWEEN 18 AND 65)
• INSERT INTO:

  INSERT INTO table_name (lastname, postcode) // for specifying the columns

  INSERT INTO table_name // for not specifying the columns

  VALUES (“Smith”, “CO13 0SN”)
• UPDATE:

  UPDATE Person (table_name) // if specifying the record

  SET Password = “resetMe”

  WHERE Town = “Colchester”
• DELETE:

  DELETE FROM Person WHERE LastName = “Brown” // for specifying the rows

  DELETE FROM Person // for deleting all data
• CREATE:

  CREATE DATABASE

  CREATE TABLE table_name(column_name1, column_name2…)
• ALTER:

  ALTER TABLE table_name

  ADD/ALTER COLUMN name datatype;

  ALTER TABLE table_name 

  DROP COLUMN column_name // deletes column

  ALTER TABLE table_name

  RENAME COLUMN old_name to new_name // renames the column
• JOIN

  Allows query to read data from multiple tables.

  SELECT fieldList

  FROM tableA INNER JOIN tableB

  ON tableA.field=tableB.field