Just Enough SQL To Get By
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
Latest Posts
Walking In Andorra
5:24p.m., 11 Jul
I've just returned from a week in Arinsal, Andorra. It's a popular ski resort, but almost the whole town seems ...A New Mix In The Cloud
5:27p.m., 30 May
I uploaded a new mix to mixcloud.com yesterday. The notion for the mix came from the grunge podcast we recorded ...League One Round-Up, Player Of The Year Awards 2009-2010
5:53p.m., 19 May
I've enjoyed this 2009/2010 football season. Huddersfield Town have managed to put together a young, hungry side with a smattering ...Wii Sports Resort Table Tennis - Tips To Beat Lucia
7:02p.m., 25 Apr
I love my Nintendo Wii. Partly it's because of the Wiimote, but mostly I love games that are simple to ...