Skip to main content

MySQL Cheat Sheet V1

MySQL is a small, fast and highly configurable DBMS. It supports a number of different table fileformats, depending on the requirements of the user.
  • 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


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=TEST --password=xxx create database addressdb

Using the database

$ mysql --user=TEST --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",
       "test@localhost",681024,2122536, "http://BLOGGER.SHIBURAJ.COM","test");

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="WEBSITE" WHERE i=2;

Delete the table from the database

mysql> DROP TABLE p_addr;

List the databases

$ mysqlshow --user=TEST --password=xxx
+-----------+
| Databases |
+-----------+
| addressdb |
| myblog    |
| mysql     |
| test      |
+-----------+

List the tables in the database "addressdb"

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

Database: addressdb
+---------+
| Tables  |
+---------+
| p_addr  |
| mytble  |
| phonebk |
+---------+

Comments

Popular posts from this blog

Direct PCB Printing at Home using Inkjet Printers

This page details the use of Epson InkJet printers with pigment based inks to feed and directly print resist patterns to copper clad printed circuit board stock, ready for etching. Once you can feed the PCB through the printer for etch resist, you can then feed it back through the printer for solder mask (yes! Several people report that it works quite well!) and for a component "silk-screen".

The Laravel 5 Package for Indian Payment Gateways. Currently Supported Gateway: CCAvenue, PayUMoney, EBS, CitrusPay.

The Laravel 5 Package for Indian Payment Gateways. Currently supported gateway: CCAvenuePayUMoneyEBS,CitrusPay. Use one unified api for all the supported frameworks. When you use Indipay for your payment gateway processing its extremely to switch between payment gateway providers without changing your source code.