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;