MySQL Guide

From YoungMusic

Database creation, deleting and backups

CREATE DATABASE name;
USE name;

CREATE TABLE name (ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
                   field2 CHAR(25),
                   field3 CHAR(10),
                   datefield DATE,
                   choices ENUM('one','two'));

Use indexes to speed up searching, even on parts of fields or on combined fields:

CREATE TABLE name (ID INT,
                   field2 INT,
                   INDEX keyIndex(field2));

CREATE TABLE name (ID INT,
                   field2 CHAR(100),
                   INDEX keyIndex(field2(20)));

CREATE TABLE name (ID INT,
                   field2 INT,
                   field3 CHAR(100),
                   INDEX compoundIndex(field2, field3(20)));

 CREATE TABLE name (ID INT,
                   field2 INT,
                   UNIQUE INDEX keyIndex(field2));

SHOW DATABASES;
SHOW TABLES;
DESCRIBE tablename;
DROP TABLE tablename;
DROP DATABASE name;

Also handy to know is how to make a backup of your database:

mysqldump --add-drop-table -u loginname -p databasename > file.sql

The extra option --add-drop-table is handy because if you don't use it, you'll have to manually remove the old database if you want to restore it.

This is how to restore one:

mysql -u loginname -p databasename < file.sql

How to enter and edit values

INSERT INTO tablename (field2, field3, datefield, choices)
  VALUES ('text', 'more text', '1970-21-31', 'two');

UPDATE tablename SET
  fieldname='content'
  WHERE fieldname='content'

Ask results from the database

For a random result:

SELECT * FROM tablename ORDER BY RAND() LIMIT 1

Nearest match:

SELECT * FROM tablename WHERE fieldname > 666 ORDER BY fieldname LIMIT 1
SELECT * FROM tablename WHERE fieldname < 666 ORDER BY fieldname DESC LIMIT 1

From multiple tables:

SELECT * FROM table1, table2 WHERE table1.fieldname = table2.fieldname 
SELECT * FROM table1, table2 WHERE table1.fieldname = table2.fieldname AND table1.fieldname = 'whatever'