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
Muppets Birthday Card
5:47p.m., 28 Nov
Emma loves The Muppets. She even has her own Muppet who we call Emma Too and who was born at ...Detecting Online Status In The Browser
11:55a.m., 28 Nov
I was just heading into a meeting when I was asked how our (mostly web-based) iOS application was going to ...Dropping Support for Internet Explorer 6
2:37p.m., 11 Oct
Microsoft's Internet Explorer 6 has long been the bane of every front-end developer's life. It's a 10-year old browser - ...Xfm Buzz - A Radio Hack
1:15p.m., 31 May
At Global Towers we developers have 10% time to go away and hack at something that might, ultimately, bring value ...