Commands to create and maintain simple MySQL Databases:-

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

Using the Database

$ mysql --user=john --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","john@localhost",123456,9874563, "http://mydomain.com","John");

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

Delete the table from the database

mysql> DROP TABLE p_addr;

List the databases

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

List the tables in the database "addressdb"

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

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