BLOG: Mitch Wilson, Austin TX Web & Multimedia Developer – Austin, TX

MySQL Cheatsheet

My personal MySQL cheatsheet.

MySQL 5

Create table documentation

Example MySQL code:

CREATE TABLE `events` (

  `id` int(11) NOT NULL auto_increment,

  `datetime` datetime default NULL,

  `title` varchar(255) collate latin1_general_ci default NULL,

  `other` varchar(255) collate latin1_general_ci default NULL,

  `body` text collate latin1_general_ci,

  `created_on` datetime default NULL,

  `created_at` datetime default NULL,

  `updated_on` datetime default NULL,

  `updated_at` datetime default NULL,

  `caption` varchar(255) collate latin1_general_ci default NULL,

  `published` tinyint(1) default '0',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Backtick

The backtick character ` allows the use of odd characters like spaces in MySQL database, table and column names. It is optional but is good practice.

MySQLDump

On Windows, type the following in a Command window, then enter your MySQL password when prompted:

mysqldump --lock-tables my_database_name -r my_database_dump.txt  -u username -p

Note: Many Windows examples show using the > character in MSDOS like:

mysqldump my_database_name > my_database_dump.txt -u username -p

While this does dump the database, it also converts the MySQL line returns '\n' to Windows line returns '\r\n' in the output saved to the resulting dump.txt file. To preseve the original line breaks, do not use the > character to dump to a text file in MSDOS. Use the MySQL argument -r to specify the output file. If, however, you want to just view the dump (you don't actually plan to import it) then it is helpful to use > on Windows since it will add the Windows line return characters making the file more readable (but not as clean for importing to another MySQL database).

Tip: After dumping your database to a text file, open it from the Command line using:

notepad my_database_dump.txt

Creating a database and importing your MySQL dump

Creating a database is very easy. On the computer you want to host the database, you just login and run a single command. For example, you want a database named wordpress_blog. Assuming your are logged into mysql and that you have the permissions to create new databases, simply type:

create database wordpress_blog;

Next, import your dump into the new wordpress_blog database.

Importing the dump

You will want to logout of mysql in order to run this from the command line itself. Assuming your username is smartypants and you are importing into the wordpress_blog database, type this then your password.

mysql -usmartypants -p wordpress_blog < my_database_dump.txt

Then log back into mysql and type show databases. You will see the new wordpress_blog database in the list of databases.

Next, you need to grant users access to the new database.

MySQL Users and grant permissions

MySQL Account Management

grant all on `databasename`.* to 'username'@'localhost' identified by 'password';

Note: Backtick characters are used around databasename. Single quote characters are used around username and localhost.

Connecting to MySQL with PHP

See my PHP/MySQL article.

Deleting records

<?php
// Connect to MySQL

// Delete Bobby from the "example" MySQL table
mysql_query("DELETE FROM example WHERE age='15'")
or die(mysql_error());
?>

Creating a database

create database nameofdatabase;

Deleting a database

drop database nameofdatabase;