Friday, August 1, 2008

Managing MYSL on Linux

Important MYSQL commands

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.

  • 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


  • Field Types specific to MySQL
    TEXT - Allows up to 65535 characters
    DECIMAL(10,2) - Up to 10 digits before the point, 2 after.
    TO create the Database
    $ mysqladmin --user=ravi --password=xxx create database addressdb

    To use the Database
    $ mysql --user=ravi --password=xxx
    mysql> USE addressdb,

    To create Table
    mysql> CREATE TABLE p_addr (i INTEGER PRIMARY KEY,address TEXT,email VARCHAR(30),pincode DECIMAL(10),phone DECIMAL(15),website TEXT);

    To add a column "india" to the table
    mysql> ALTER TABLE p_addr ADD india VARCHAR(30);

    To insert value in Table
    mysql> INSERT INTO p_addr VALUES (1,"My, present, address","prakash@xyz.com",681024,2122536, "http://gadbu.blogspot.com","Prakash");

    To List the contents of the Table
    mysql> SELECT * FROM p_addr;

    To delete a row from Table
    mysql> DELETE FROM p_addr WHERE i=1;

    To 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
    List the tables in the database "addressdb"
    $ mysqlshow --user=ravi --password=xxx addressdb

    No comments: