MySQL Quick Reference

---

What databases exist on server:

SHOW databases;

Create a new database:

CREATE DATABASE database_name;

Select a database to use:

USE database_name;

What tables exist in current database:

SHOW tables;

Describe (DESC for short) the table. Displays field names, types and parameters:

DESCRIBE tblName;

Produce the CREATE TABLE SQL for a given table.

SHOW CREATE TABLE tblName;

---

Create a new local user:

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';

Delete a new local user:

DROP USER 'user_name'@'localhost';

Grant a user all privileges on all databases (and all tables):

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost';

Grant a user specific privileges (eg. SELECT, UPDATE and DELETE) on all tables of a single database:

GRANT SELECT, UPDATE, DELETE ON database_name.* TO 'user_name'@'localhost';

Grant a user all privileges on a specific table within a database:

GRANT ALL PRIVILEGES ON database_name.tblName TO 'user_name'@'localhost';

---

Most ALTERs will only work if the change doesn't make an extant value disobey the updated params! For example, attempting to change a field which contains NULLs to be NOT NULL will result in an "ERROR 1138 (..): Invalid use of NULL value".

Alter table after the fact - add column:

ALTER TABLE tblName ADD COLUMN fldName VARCHAR(255);

Alter table after the fact - add column in specific place:

ALTER TABLE tblName ADD COLUMN fldName VARCHAR(255) AFTER fldOtherName;

Alter table after the fact - change field parameters:

ALTER TABLE tblName CHANGE fldToChange fldToChange INT(11) NULL;

Alter table after the fact - redeclare field parameters:

ALTER TABLE tblName MODIFY fldName INT(11) NOT NULL DEFAULT 0;

---

Simple update row:

UPDATE tblPost SET fldTitle='lalala' WHERE fldId='30';

---

Select all:

SELECT * FROM tblName;

Select specifc fields:

SELECT fldName, fldOtherName FROM tblName;

Limit results to N rows:

SELECT * FROM tblName LIMIT N;

Select distinct values:

SELECT DISTINCT fldName FROM tblName;

Select based on matching with LIKE:

SELECT * FROM tblName WHERE fldName LIKE 'string';

Select all from a list of values with LIKE:

SELECT * FROM tblName WHERE fldName IN (val1, val2, val3, val4);

Select the number of rows returned and name output:

SELECT count(fldName) as fldCount FROM tblName WHERE fldName='string';

Select both the total number of a single tag, and the tag name from a many-to-many relationship between tag table and post table. Only include posts that have 'fldDisplay' set true:

SELECT count(pt.fldTId) as 'fldCount', t.fldTitle
FROM tblPostTag pt
JOIN tblTag t on pt.fldTId = t.fldId
JOIN tblPost p on pt.fldPId = p.fldId
WHERE p.fldDisplay = 1
GROUP BY t.fldTitle;

Select all posts which have a specific tag specified in the many-to-many relationship table tblPostTag and sort by date created:

SELECT p.*
FROM tblPost p
JOIN tblPostTag pt ON p.fldId = pt.fldPId
JOIN tblTag t ON pt.fldTId = t.fldId
WHERE t.fldTitle = 'specific_tag' AND p.fldDisplay = 1
ORDER BY `fldDate` DESC;

---

Create a basic blog post table:

CREATE TABLE `tblPost` (
    `fldId` int(11) NOT NULL AUTO_INCREMENT,
    `fldPostContent` text NOT NULL,
    `fldDatePosted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `fldLikes` int(11) NOT NULL DEFAULT '0',
    `fldDislikes` int(11) NOT NULL DEFAULT '0',
    `fldAuthor` varchar(255) NOT NULL,
    PRIMARY KEY (`fldId`)
);

Create a basic blog tag table:

CREATE TABLE `tblTag` (
    `fldId` int(11) NOT NULL AUTO_INCREMENT,
    `fldTag` varchar(255) NOT NULL,
    `fldDateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`fldId`)
);

Create a basic many-to-many relationship between posts and tags for a blog:

CREATE TABLE `tblPostTag` (
    `fldPostId` int(11) NOT NULL,
    `fldTagId` int(11) NOT NULL,
    PRIMARY KEY (`fldPostId`,`fldTagId`)
);

---

Delete a row by field:

DELETE FROM tblName WHERE fldName='value';

Drop (delete) a table:

DROP tblName;

Drop (delete) a table if it exists (try):

DROP TABLE IF EXISTS tblName;

---

Lock table for writing:

LOCK TABLES tblName WRITE;
-- Perform Writes;
UNLOCK TABLES;