Just Enough SQL To Get By

6:12p.m., Wed 13 May 2009

Here I present my cheat sheet of frequently used SQL commands. I only really use sql commands on the command line or for writing dmigrations. But here's the basic commands, just for my ease of reference really. I'll add to the list as mysql vocabulary expands.

Headings link to the relevant documentation on mysql.com.

Create, Drop & Use

DROP DATABASE <database-name>;

CREATE <database-name>;

USE <database-name>;

Show & Describe

SHOW DATABASES;

SHOW TABLES;

DESCRIBE <table-name>;

Select

SELECT * FROM <database-name>;

SELECT <column-1>, <column-2> FROM <database-name>;

SELECT * FROM <database-name> WHERE <column>=<value>;

Delete

DELETE FROM <table-name> WHERE <column-name>=29;

Update

UPDATE <table-name> SET <column-name>=NULL WHERE <column-name>=29

Insert

INSERT INTO <table-name> (<col1>, <col2>, <col3>) VALUES (<col1-val>, <col2-val>, <col3-val>);

Alter Table

ALTER TABLE <table-name> MODIFY <column-name> integer NOT NULL;

ALTER TABLE <table-name> ADD COLUMN <column-name> char(20);

ALTER TABLE <table-name> DROP COLUMN <column-name>;

You can also use 'alter' to rename a column - reference the existing column name first, followed by the new name

ALTER TABLE CHANGE <old-column-name> <new-column-name> int(4) NULL;

Rename

mysql> RENAME <table-name> TO <new-table-name>;

Adding A User

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

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION

NB: a "ERROR 1396" on the CREATE USER command means that the user already exists.

mysqldump

mysqldump isn't a mysql command - it's a command line-driven application.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

mysql -u [username] -p [password] [database_to_restore] < [backupfile]

I've had max_allowed_packet errors using this method though :( but there's a way around this. In mysql, change the max_allowed_packet setting to something suitably large and use the SOURCE command:

set global max_allowed_packet=1024000;
drop database <database-name>
create database <database-name>
source <absolute-path-to-sql-file>

More helpful links on mysql.com

• Documentation Home
• Tutorial (MySQL 5.1)
• Reference Manual