SQL Statements

SQL Statement Classes

  • SQL schema statements — used to define the data structures stored in the database. Ex: create table.
  • SQL data statements — used to manipulate the data structures previously defined using SQL schema statements. Ex: insert, update, delete, and select.
  • SQL transaction statements — used to begin, end, and roll back transactions.
SELECT /* one or more things */ ...
FROM /* one or more places */ ...
WHERE /* one or more conditions apply */ ...

Common CLI commands

show character set;

show databases;
use <DATABASE>; # OR mysql -u <USER> -p <DATABASE>;
show tables;
desc <TABLE>;
SELECT now(); # 2019-04-04 20:44:26

CREATE TABLE …

ALTER TABLE … MODIFY …

set foreign_key_checks=0; # disable the foreign key constraint on the favorite_food
ALTER TABLE person
		MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
set foreign_key_checks=1;

DROP TABLE 

DROP TABLE favorite_food;

INSERT INTO … VALUES …

INSERT INTO person
		(person_id, fname, lname, eye_color, birth_date)
VALUES (null, 'William','Turner', 'BR', '1972-05-27');

UPDATE … SET … WHERE …

UPDATE person
SET street = '1225 Tremont St.',
		city = 'Boston',
		state = 'MA',
		country = 'USA',
		postal_code = '02138'
WHERE person_id = 1;

DELETE … FROM … WHERE

DELETE FROM person
WHERE person_id = 2;

When Good Statements Go Bad

  • Nonunique Primary Key
    • mysql> INSERT INTO person
        ->  (person_id, fname, lname, eye_color, birth_date)
        -> VALUES (1, 'Charles','Fulton', 'GR', '1968-01-15');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  • Nonexistent Foreign Key
    • mysql> INSERT INTO favorite_food (person_id, food)
        -> VALUES (999, 'lasagna');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails ('sakila'.'favorite_food', CONSTRAINT 'fk_fav_food_person_id' FOREIGN KEY
('person_id') REFERENCES 'person' ('person_id'))
  • Column Value Violations
    • mysql> UPDATE person
        -> SET eye_color = 'ZZ'
        -> WHERE person_id = 1;
ERROR 1265 (01000): Data truncated for column 'eye_color' at row 1
  • Invalid Date Conversions
    • mysql> UPDATE person
        -> SET birth_date = 'DEC-21-1980'
        -> WHERE person_id = 1;
ERROR 1292 (22007): Incorrect date value: 'DEC-21-1980' for column 'birth_date'
at row 1
    ‣
    Use this instead

SELECT … FROM … WHERE

How data is retrieved, joined, filtered, grouped, and sorted.

SELECT person_id, fname, lname, birth_date
FROM person
WHERE person_id = 1;
SuperMade with Super