< tHis Is yOungMusiC.oRg >

MySQL Guide

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 good 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<password> 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';  

Get 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';