mysql_commands.md

Basic MySQL Commands (that you should know)

Create a database.

$ mysqladmin -u[username] create [database name]

Alt.

$ CREATE DATABASE my_db;

Dump a database.

$ mysqldump -u[username] [database name] > dump.sql 

Load a database dump.

$ mysql -u[username] [database name] < dump.sql

Login

$ mysql -u user -p;

Create user

$ create user db_user;

Grant privileges while assigning the password

$ grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password';

or

$ grant select, insert, delete on db_name.* to 'db_user'@'localhost' identified by 'db_password';

List all databases on the sql server

$ SHOW databases;

Use database

$ USE [db name];

Show table

$ SHOW tables;

Describe a table

$ DESCRIBE [table name];

Delete a table

$ DROP table [table name];

Delete a database

$ DROP database [database name];

Delete a column

$ ALTER table [table name] drop column [column name];

Add a colum

$ ALTER table [table name] ADD column [new column name] varchar(255);

Rename colum name

$ ALTER table [table name] CHANGE [old column name] [new column name] varchar(255);

Change colum

$ ALTER table [table name] MODIFY [column name] varchar(255);

Default ON DELETE behavior:

NO ACTION: [...] InnoDB rejects the delete or update operation for the parent table.

RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause. [...]

SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. [...]

The foreign column is set to NULL, provided it is not declared as NOT NULL (or InnoDB will not allow deletion or update).

CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. [...]

Cascade deletes (or updates) the foreign column.

SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

http://stackoverflow.com/questions/1027656/what-is-mysqls-default-on-delete-behavior