SQL Reference

CAUTION: this page is a collection of notes and not verified reference material — rely on it at your own risk!

Database Setup

CREATE DATABASE monsters;

Creates a database named 'monsters'

CREATE TABLE zombies(name VARCHAR(9), age INT(2));

Creates a table named 'zombies' with columns for name and age

CREATE TABLE vampires(
name VARCHAR(15) NOT NULL,
age CHAR(2),
epitaph TINYTEXT,
eulogy TEXT,
thumbnail BLOB,
weight INT,
height FLOAT,
cash FLOAT(6,2),
license INT(9) UNSIGNED ZEROFILL,
id INT AUTO_INCREMENT KEY,
filepoint TIMESTAMP
dob DATE
sunset TIME
arrival DATETIME
epoch YEAR
);

Creates a table named 'vampires' with columns for
name [flexible-size string, max 15 characters; required field]
age [fixed-length string (padded with spaces) max 2 characters]
epitaph [partially indexable 255-byte string]
eulogy [partially indexable 65 KB string]
thumbnail [65KB binary data file]
weight [integer]
height [floating-point number]
cash [6-digit floating-point number to 2 decimal places]
license [9-digit unsigned integer, padded with zeroes]
id [automatically set key integer]
filepoint [automatically set time in the 0000-00-00 00:00:00 format]
dob [date in the 0000-00-00]
sunset [time in the 00:00:00 format]
arrival [date and time in the 0000-00-00 00:00:00 format]
epoch [year in the 0000 or 00 format]

ALTER TABLE imps AUTO_INCREMENT=100000;

Will populate the automatic column with integers beginning with 100000

CREATE TABLE imps(seen DATE DEFAULT '2017-10-31');

Will populate the 'seen' column with the default date if none is specified

CREATE TABLE imps(name VARCHAR(15), UNIQUE(name));

Will prevent the same value being assigned to 'name' in multiple rows

CREATE TABLE imps(height FLOAT, CHECK(height < 5));

Will limit the values entered in the height column to 'less than 5'

CREATE TABLE imps(name VARCHAR(25), INDEX(name(5));

Will include an index on the first 5 characters of the name column

CREATE TABLE imps(id INT NOT NULL, PRIMARY KEY (id));

Will use the id column as a primary key

CREATE TABLE imps(id INT NOT NULL, name VARCHAR(15), CONSTRAINT indexvalue PRIMARY KEY (id, name));

Will use the id and name columns as a primary key

CREATE TABLE imps(name VARCHAR(15), dominion VARCHAR(20), PRIMARY KEY (name), FOREIGN KEY (dominion) REFERENCES dominions(place));

Sets up the connection between the 'imps' and 'dominions' tables
('place' has to be the primary key in the 'dominions' table and the value in the 'dominion' column will have to be somewhere in the 'place' column)

CREATE TABLE imps(name VARCHAR(15), dominion VARCHAR(20), PRIMARY KEY (name), CONSTRAINT idlink FOREIGN KEY (dominion) REFERENCES dominions(place));

Sets up a named connection between the 'imps' and 'dominions' tables
('place' has to be the primary key in the 'dominions' table and the value in the 'dominion' column will have to be somewhere in the 'place' column)

ALTER TABLE spectres RENAME ghosts;

Renames the 'spectres' table to 'ghosts'

ALTER TABLE ghosts ADD haunts INT(15);

Adds the 'haunts' column to the 'ghosts' table

ALTER TABLE ghosts MODIFY haunts VARCHAR(15);

Changes the 'haunts' column to the VARCHAR data type

ALTER TABLE ghosts CHANGE haunts haunted VARCHAR(15);

Changes the 'haunts' column to the 'haunted' column

ALTER TABLE imps ADD CHECK (height < 5);

Will limit the values entered in the height column to 'less than 5'

ALTER TABLE imps ADD CONSTRAINT impsarealwayslittle CHECK (height < 5 AND weight < 100);

Will limit the values entered in the height and weight columns

ALTER TABLE imps DROP CHECK impsarealwayslittle;

Remove the 'impsarealwayslittle' CHECK constraint

ALTER TABLE imps ADD UNIQUE (name);

Will prevent the same value being assigned to 'name' in multiple rows

ALTER TABLE imps ADD CONSTRAINT uq UNIQUE (name, vice);

Will prevent the same value set being assigned to 'name' and 'vice'

ALTER TABLE imps DROP INDEX uq;

Remove the 'uq' UNIQUE constraint

ALTER TABLE imps ADD PRIMARY KEY (id);

Will use the id column as a primary key (may need to fix existing data first)

ALTER TABLE imps ADD CONSTRAINT i PRIMARY KEY (id,nom);

Will use the id and nom columns as a primary key

ALTER TABLE imps DROP PRIMARY KEY;

Remove the primary key

ALTER TABLE imps ADD FOREIGN KEY
(dominion) REFERENCES dominions(place);

Sets up a connection between the 'imps' and 'dominions' tables
('place' has to be the primary key in the 'dominions' table and the value in the 'dominion' column will have to be somewhere in the 'place' column)

ALTER TABLE imps ADD CONSTRAINT idlink FOREIGN KEY (dominion) REFERENCES dominions(place);

ALTER TABLE imps DROP FOREIGN KEY idlink;

Removes the foreign key connecting the 'imps' and 'dominions' tables

ALTER TABLE ghosts DROP chains;

Removes the 'chains' column from the 'ghosts' table

CREATE TABLE ogres_copy AS SELECT * FROM ogres;

Copies all rows from the original table to the new table

~~SELECT * INTO ogres_copy FROM ogres;~~

~~Copies all rows from the original table to the new table~~

~~SELECT * INTO ogres_copy IN 'backups.mdb' FROM ogres;~~

~~Copies all rows from the original table to the new table and database~~

~~SELECT * INTO ogres_template FROM ogres WHERE 5 > 10;~~

~~Copies the structure but no data from the original table to the new table~~

TRUNCATE TABLE gremlins;

Deletes all rows from a table but leaves the table setup intact

DROP TABLE gryphons;

Deletes the 'gryphons' table

DROP DATABASE monsters;

Deletes the entires 'monsters' database

Descriptions

SHOW databases;

Get a list of the existing databases

SHOW tables;

Get a list of the existing tables

DESCRIBE zombies;

Get the name and characteristics of the rows of the 'zombies' table

EXPLAIN SELECT from dragons WHERE scales='rhomboid';

Provides characteristics of a query to assess its efficiency

Indexes

CREATE INDEX wrappings ON mummies (tomb);

Index the 'mummies' table by the 'tomb' column

CREATE INDEX wrappings ON mummies (name, tomb);

Index the 'mummies' table by the 'name' and 'tomb' columns

ALTER TABLE mummies ADD INDEX(tomb(10));

Index the 'mummies' table by the first ten places in the 'tomb' column

ALTER TABLE vampires ADD FULLTEXT(alias, epitaph);

Index the entire text of the 'alias' and 'epitaph' columns

Note that words like 'and', 'of' and 'the' are excluded from the indexed text

SELECT alias, epitaph FROM vampires WHERE
MATCH (alias, epitaph) AGAINST ('tall dark');

Pull all entries that have both 'tall' and 'dark' in them
- requires a FULLTEXT index of the 'alias' and 'epitaph' fields -

SELECT * FROM vampires WHERE MATCH (alias, epitaph)
AGAINST ('"tall dark"' IN BOOLEAN MODE);

Pull all entries that have the exact text 'tall dark' in them
- requires a FULLTEXT index of the 'alias' and 'epitaph' fields -

SELECT * FROM vampires WHERE MATCH (alias, epitaph)
AGAINST ('+cute -cuddly' IN BOOLEAN MODE);

Pull all entries that have 'cute' but not 'cuddly' in them
- requires a FULLTEXT index of the 'alias' and 'epitaph' fields -

ALTER TABLE mummies DROP INDEX wrappings;

Delete the 'wrappings' index from the 'mummies' table

ALTER TABLE vampires ENGINE = MyISAM;

Try this if the full text index isn't working

Inserting Data

INSERT INTO zombies(name, age) VALUES('Bob', 34);

Add an entry for name:Bob age:34 to the 'zombies' table

INSERT INTO zombies(name, age) VALUES('Joe', 58), ('Gustavious', 47), ('Mickey', 77);

Add entries for Joe, Gus and Mickey to the 'zombies' table

UPDATE zombies SET name = 'Bob' WHERE name = 'Robert';

Reset all entries with name:Robert to name:Bob

DELETE FROM zombies WHERE name = 'Bunny';

Remove all entries for name:Bunny from the 'zombies' table

INSERT INTO allflyingmonsters SELECT * FROM dragons;

Copies all data from the original table to the new table

INSERT INTO minis SELECT * FROM dragons WHERE size<15;

Copies selected rows from the original table to the new table

INSERT INTO roster (name, place) SELECT name, village FROM dragons;

Copies selected columns from the original table to the new table

Queries

SELECT * FROM dragons;

Pull all data from the 'dragons' table

SELECT claws, scales FROM dragons;

Pull all data in the 'claws' and 'scales' columns from the 'dragons' table

SELECT * FROM dragons WHERE color = 'purple';

Pull all entries for purple dragons from the 'dragons' table

SELECT * FROM dragons WHERE NOT color = 'purple';

Pull all entries but purple dragons from the 'dragons' table

SELECT * FROM vampires WHERE age > 200;

Pull all entries with ages over 200 from the 'vampires' table (or <= etc.)

SELECT * FROM vampires WHERE age BETWEEN 100 AND 200;

Pull all entries with ages from 100 to 200 from the 'vampires' table

SELECT * FROM vampires WHERE age IS NULL;

Pull all entries from the 'vampires' table with nothing in the age column

SELECT * FROM vampires WHERE age IS NOT NULL;

Pull all entries from the 'vampires' table with something in the age column

SELECT * FROM dragons WHERE color LIKE 'bl%';

Pull all entries for dragons of any color that begins with 'bl'

SELECT * FROM mummies WHERE name LIKE '%r%';

Pull all entries for any mummy with an 'r' in it

SELECT * FROM vampires
WHERE name LIKE '%v%' OR alias LIKE '%v%';

SELECT * FROM vampires
WHERE name NOT LIKE '%v%' AND alias NOT LIKE '%v%';

AND OR and NOT can be used to refine selection criteria

(the first example pulls entries with a v anywhere in the searched columns and the second pulls entries with no v anywhere in them)

SELECT * FROM vampires WHERE (name LIKE '%x%' OR
name LIKE '%z%') AND name NOT LIKE '%e%';

Parentheses may be necessary to clarify the application of AND & OR

(this will pull anything with either an 'x' or a 'z', but no 'e'—instead of anything with an 'x', along with anything with a 'z' and no 'e')

SELECT * FROM vampires WHERE type IN ('a', 'b', 'ab');

Instead of OR, can use IN and give a list for multiple match options

SELECT * FROM goblins ORDER BY weight;

Returns the 'goblins' entries from lightest to heaviest

SELECT * FROM goblins ORDER BY weight DESC;

Returns the 'goblins' entries from heaviest to lightest

SELECT name, job FROM goblins ORDER BY nation, weight;

Returns the 'goblins' entries by nationality, then lightiest to heaviest within those groups—so skinny English goblins, then fat English goblins, then skinny French goblins etc. (also, the ORDER BY part is always last)

SELECT DISTINCT color FROM dragons;

Pull all data in the 'color' column excluding repeats

SELECT DISTINCT color, scales FROM dragons;

Pull each combination of values in the 'color' and 'scales' columns excluding repeats (e.g. both 'red pointy' and 'green pointy' but only one of each even if more than one dragon has green pointy scales)

SELECT * FROM ogres LIMIT 5;

Pull the first 5 entries from the 'ogres' table

SELECT * FROM ogres LIMIT 10, 5;

Pull the next 5 entries from the 'ogres' table after skipping the first 10

SELECT name, lord FROM basilisks AS b, principalities AS p WHERE b.climate = p.climate;

AS can be used to declare abbreviations for use in long queries

Query Functions

SELECT CONCAT (color, ' ', scales) AS type, pillaged FROM dragons;

Pulls a 'type' field (e.g. green pointy) with values from two columns plus a space in the middle, and the pillaged column, from the 'dragons' table

SELECT COUNT(*) AS n FROM ogres;

Pull a count of the rows in the 'ogres' table

Note: Most math functions won't work with a space before '('

SELECT type, COUNT(name) AS n FROM ogres GROUP BY type;

Pull a count of the how many ogres there are of each type

SELECT type, COUNT(name) AS nomenclature FROM ogres
GROUP BY type HAVING COUNT(name) >= 2;

Pull a count of the how many ogres there are of each type,
excluding types of which there are fewer than two

SELECT COUNT(DISTINCT name) AS n FROM ogres;

Pull a count of how many different names are used by ogres

SELECT AVG (age) AS average FROM vampires;

Provide the average of all the values in the age column

SELECT MIN(age) AS youngest FROM vampires;

Provide the lowest of all the values in the age column

SELECT MIN(name) AS first FROM vampires;

Provide the first in sort order of all the values in the name column

SELECT MAX(age) AS oldest FROM vampires;

Provide the highest of all the values in the age column

SELECT MAX(name) AS last FROM vampires;

Provide the last in sort order of all the values in the name column

SELECT SUM(kills) AS casualties FROM zombies;

Provide the sum of all the values in the kills column

SELECT UPPER (name) AS styled_name FROM dragons;

SELECT LOWER (name) AS styled_name FROM dragons;

Provides the name in all-caps, and no-caps, respectively

SELECT LTRIM(name), pillaged FROM dragons;

SELECT RTRIM(name), pillaged FROM dragons;

SELECT TRIM(name), pillaged FROM dragons;

Removes extra spaces from the left, right and both sides, respectively

SELECT name, age, CURDATE() AS today FROM zombies;

Creates a 'today' field with the current date in YYYY-MM-DD format

SELECT name, city FROM zombies WHERE YEAR(died) = 2000;

Extracts the year value from a column with a date-type value

SELECT name, 2017-age AS born FROM vampires;

Creates a 'born' field from the results of a calculation on the age column

SELECT name, 2017-IFNULL(age, 18) AS b FROM vampires;

Substitutes 18 if there is no value in the age column

SELECT name, 2017-COALESCE(age, 18) AS b FROM vampires;

Substitutes 18 if there is no value in the age column

SELECT name, id FROM ogres
WHERE SOUNDEX(name) = SOUNDEX('sidney');

Pulls entries with names that sound like 'sidney'

Query Joins

SELECT name, size, lord FROM dragons JOIN villages ON dragons.pillaged = villages.realm;

By joining two tables that both have a column for the same thing, data from the second table (lord) can be added to data from the first (name and size); each returned row will have the name and size of the dragon, along with the lord connected to it by the match of the place it pillaged per the dragons table to the place listed as realm in the villages table

SELECT realm, COUNT(name) AS dc
FROM villages JOIN dragons
ON villages.realm = dragons.pillaged
GROUP BY villages.realm;

Will go through the values in the realm column & return a count of matches from the dragons table for each, excluding realms with no matches

SELECT name, size, lord FROM dragons, villages WHERE dragons.pillaged = villages.realm;

SELECT name, lord, crest FROM dragons, villages, rulers WHERE dragons.pillaged = villages.realm AND villages.lord = rulers.title;

Multiple tables can be joined and data selected from some or all

SELECT name, age FROM vampires WHERE age = (SELECT age FROM vampires WHERE name = 'Dracula');

Find all the vampires the same age as Dracula by joining a table to itself

SELECT v1.name, v1.age FROM vampires AS v1, vampires AS v2 WHERE v1.age = v2.age AND v2.name = 'Dracula');

Find all the vampires the same age as Dracula by joining a table to itself

SELECT mummy, country FROM mummies NATURAL JOIN tombs;

If both tables have a column with the same name, then the join can be done automatically in order to pull data from both

SELECT villages.realm, dragons.name
FROM villages LEFT OUTER JOIN dragons
ON villages.realm = dragons.pillaged;

Will go through all the values in the realm column and return a row for each match from the dragons table; each realm that has no match will also return a row (with a null value where the dragon name would be)

SELECT villages.realm, dragons.name
FROM villages RIGHT OUTER JOIN dragons
ON villages.realm = dragons.pillaged;

Will go through all the values in the pillaged column and return a row for each match from the villages table; the starting column is the one on the right for RIGHT OUTER JOIN and the one on the left for LEFT OUTER JOIN

SELECT realm, COUNT(name) AS dc
FROM villages LEFT OUTER JOIN dragons
ON villages.realm = dragons.pillaged
GROUP BY villages.realm;

Will go through all the values in the realm column and return a count of matches from the dragons table; realms with no matches will show 0

Subqueries and Unions

SELECT name, city, job FROM zombies WHERE job IN (SELECT occupation FROM gigs WHERE field = 'politics');

Pull a list of 'occupations' in the 'politics' field from one table and uses the result as selection criteria to match 'jobs' in another table

SELECT occupation, field, (SELECT COUNT(*) FROM zombies WHERE zombies.job = gigs.occupation) AS z FROM gigs;

Pull data from one table and include a count of matches from another table

SELECT name, tomb FROM mummies UNION SELECT alias, residence FROM vampires;

Returns a single set of data with 'name' and 'tomb' columns containing the specified values from both tables' columns (which need to be compatible)

SELECT name, age FROM vampires WHERE age>200 UNION SELECT name, age FROM vampires WHERE nat = 'Flemish';

Returns a single set of data with duplicate results removed

SELECT name, age FROM vampires WHERE age>200 UNION ALL SELECT name, age FROM vampires WHERE nat = 'Flemish';

Returns a single set of data with duplicate results included

SELECT name FROM dragons WHERE EXISTS (SELECT lord FROM villages WHERE realm = dragons.pillaged AND age>1000);

After checking for any matches to the subquery (and returning TRUE), it will return rows for any dragons that pillaged a place over 1000 [years] old

SELECT name, city, job FROM zombies WHERE job = ANY (SELECT occupation FROM gigs WHERE hourly > 15);

After checking for matches in the subquery, if ANY rows in 'gigs' match, it will return TRUE and list the zombies that make more than $15 an hour

SELECT name, city, job FROM zombies WHERE job = ALL (SELECT occupation FROM gigs WHERE hourly > 15);

After checking for matches in the subquery, if ALL rows in 'gigs' match, it will return TRUE and list the zombies that make more than $15 an hour

Synthetic Constructs

CREATE VIEW nobility AS SELECT name, lord, crest FROM dragons, villages, rulers WHERE dragons.pillaged = villages.realm AND villages.lord = rulers.title;

Create a view named 'nobility' which can be queried like a table

SELECT name FROM nobility WHERE crest LIKE '%feather%';

Query the 'nobility' view which accesses all the data selected from the table

CREATE OR REPLACE VIEW nobility AS SELECT lord, castle, realm, crest, familyname FROM villages, rulers WHERE villages.lord = rulers.title;

Update the view named 'nobility' with a replacement query

DROP VIEW nobility;

Delete the view named 'nobility' when it is no longer needed

PREPARE stopro1 FROM 'SELECT * FROM ogres LIMIT ?';
SET @x = 10;
EXECUTE stopro1 USING @x;

Creates a stored procedure with the requirement to supply the value to be used as the LIMIT parameter, sets a variable to 10, then executes the stored command with the assigned value inserted using the variable

SET @query1 = 'SELECT * FROM ogres LIMIT ?';
PREPARE stopro2 FROM query1;

Creates a stored procedure using a string assigned to a variable

SET query2 = CONCAT('SELECT * FROM', @t1, 'LIMIT ?');

Populates a variable with a combination of values

DEALLOCATE PREPARE stopro1;

Deletes the stored procedure when finished with it

Transactions

BEGIN;
UPDATE ledger SET count=count-1 WHERE name='j';
UPDATE ledger SET count=count+1 WHERE name='k';
COMMIT;

Implements all changes between BEGIN and COMMIT at once

BEGIN;
UPDATE ledger SET count=count-1 WHERE name='j';
UPDATE ledger SET count=count+1 WHERE name='k';
ROLLBACK;

None of the changes made since BEGIN will be implemented

BEGIN;
UPDATE ledger SET count=count-1 WHERE name='j';
SAVEPOINT interval1;
UPDATE ledger SET count=count+1 WHERE name='k';
ROLLBACK TO interval1;

None of the changes made since the specified point will be implemented

User Credentialing

CREATE USER 'lost'@'localhost' IDENTIFIED BY 'city';

GRANT ALL ON *.* TO 'lost'@'localhost'
IDENTIFIED BY 'city';

Optional

Set the user ID 'lost' up for all access with password 'city'

GRANT SELECT ON monsters.* TO 'lost'@'localhost';

Set the user ID 'lost' up to select data from the entire 'monsters' database

GRANT INSERT ON monsters.zombies TO 'lost'@'localhost';

Set the user ID 'lost' up to adds rows the 'zombies' table

ALTER USER 'lost'@'localhost' ACCOUNT LOCK;

Lock the access for the user ID 'lost'

ALTER USER 'lost'@'localhost' ACCOUNT UNLOCK;

Unlock the access for the user ID 'lost'

REVOKE SELECT ON monsters.hex FROM 'lost'@'localhost';

Prevent the user ID 'lost' from selecting from the 'hex' table

Vital Structural Basics

Ignore previous text (use instead of backspacing out mistakes)

monsters.zombies

Syntax for specifying both database and table

zombies.name

Syntax for specifying both table and column

USE monsters;

Targets subsequent commands to 'monsters' database
[alternately, in PHP, database would already be specified when connecting]

Note: PHP/MySQL interaction syntax is on the PHP page

SELECT ❦ FROM ❦ WHERE ❦ GROUP BY ❦ HAVING ❦ ORDER BY ❦

Standard order for components of a query

SELECT height, fur, realm FROM bunnymen, villages;

Creates exponentially increased data output — DON'T DO THIS

;

There is no escaping it...the statement-ending semicolon is always with us

MySQL Command Reference