Tuesday, September 7, 2010

MySQL - Cheat Sheet

 MySQL is a small, fast and highly configurable DBMS. It supports a number of different table fileformats, depending on the requirements of the user.

These are the main MySQL clients and processes (mysqld):

  • mysqld - MySQL server daemon
  • safe_mysqld - Server process monitor
  • mysqlaccess - Tool for creating MySQL users
  • mysqladmin - Utility for administering MySQL
  • mysqldump - Tool for dumping the contents of a MySQL database. Useful for backing up a database from within the console.
  • mysql - Command line interface to MySQL
  • mysqlshow - List all MySQL database
  • Mysql Administrator - This is a GUI tool which makes administering mysql database a painless task. Read more about it here.
Field Types in SQL

INTEGER - A whole number
VARCHAR(10) - Up to 10 characters.
CHAR(10) - Fixed number of characters
DATE - A date
DATETIME - Date and time
FLOAT - Floating point numbers
Field Types specific to MySQL

TEXT - Allows up to 65535 characters
DECIMAL(10,2) - Up to 10 digits before the point, 2 after.
Create a database

$ mysqladmin --user=ravi --password=xxx create database addressdb
Using the database

$ mysql --user=ravi --password=xxx
mysql> USE addressdb
Create a table

mysql> CREATE TABLE p_addr (i INTEGER PRIMARY KEY,address TEXT,email VARCHAR(30),pincode DECIMAL(10),phone DECIMAL(15),website TEXT);
Add a column called "name" to the table

mysql> ALTER TABLE p_addr ADD name VARCHAR(30);
Inserting values into table

mysql> INSERT INTO p_addr VALUES (1,"My, present, address","ravi@localhost",681024,2122536, "http://linuxhelp.blogspot.com","Ravi");
List the contents of the table

mysql> SELECT * FROM p_addr;
Delete a row from the table

mysql> DELETE FROM p_addr WHERE i=1;
Rename a column in the table from "address" to "home_address"

mysql> ALTER TABLE p_addr CHANGE address home_address INTEGER;
Note: You cannot use this method to rename a column which is a primary key.

Change an existing record in the table

mysql> UPDATE p_addr SET name="Sumitra" WHERE i=2;
Delete the table from the database

mysql> DROP TABLE p_addr;
List the databases

$ mysqlshow --user=ravi --password=xxx
| Databases |
| addressdb |
| myblog    |
| mysql     |
| test      |
List the tables in the database "addressdb"

$ mysqlshow --user=ravi --password=xxx addressdb

Database: addressdb
| Tables  |
| p_addr  |
| mytble  |
| phonebk |
These are only a subset of the commands in mysql. But this will be enough for creating and maintaining a simple database.