Mysql Notes

SELECT

Show Tables

SHOW TABLES;

Unique

/* Select only the unique instances of "col_name". */
SELECT DISTINCT(col_name) FROM tbl_name;

-- If you select more than one column you must use
-- DISTINCT for the first column:
SELECT DISTINCT(col_name), other_col1, other_col2 FROM tbl_name;

Random

-- Select _one_ random quote.
SELECT QUOTE FROM DREQUOTES ORDER BY RAND() LIMIT 1

Maximum

SELECT MAX(COST) FROM TBL_WITH_COST_FIELD

-- Select the highest value that is held
-- in the COST field.

Tables

Rename Table

RENAME TABLE old_table_name TO new_table_name

Add Column

ALTER TABLE table_name ADD COLUMN column_name VARCHAR(20)

Drop Column

ALTER TABLE table_name DROP COLUMN column_name

Rename Column

ALTER TABLE table_name CHANGE old_column_name new_column_name VARCHAR(20)

Modify Column Data Type

ALTER TABLE table_name MODIFY column_name VARCHAR(20)

-- CAUTION!!!
-- If you change the column type drastically you can lose data.
-- For example: if you have a column of type VARCHAR(20) and you want to
-- change it to INT(5), any fields containing characters will be changed to
-- zeros (0).

Modify Column Default Value

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'your default'
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT

Make Column Unique

ALTER TABLE table_name ADD UNIQUE(column_name);

Copy Table

CREATE TABLE backupTbl LIKE possiblyOtherDB.tbl;

-- Copy data into that table:
INSERT backupTbl SELECT * FROM possiblyOtherDB.tbl;

User Administration

Users are unique by "Host" and "User".

Add/Modify Users

-- Create user "drwinchester" with password 'hazelhainsworth'.
GRANT SELECT ON CATALOG.* TO 'drwinchester'@'localhost' IDENTIFIED BY 'hazelhainsworth';

-- Remove Dr. Winchester's password:
GRANT SELECT ON CATALOG.* TO 'drwinchester'@'localhost' IDENTIFIED BY '';

-- Give Dr. Winchester more privileges to CATAGLOG:
GRANT ALL ON CATALOG.* TO 'drwinchester'@'localhost' IDENTIFIED BY '';

Database Administration

Create Database

CREATE DATABASE MyCoolDatabase

Drop Database

DROP DATABASE MyCoolDatabase

Backup Table

mysqldump --opt MyDataBaseName > tabledump.sql

Restore Table

mysql -A MyDataBaseName > tabledump.sql

Format

Dates