Wednesday, August 11, 2010

MySQL Beginners' Guide

MySQL is the world's most popular GPL'ed open source database. You can imagine its popularity by the fact that its newsletter has over 1 million subscribers! Now, MySQL is fairly easy to install and operate provided you know the basic SQL queries. If not, then read on.
Following are some of the most basic MySQL queries.Check them out:

  • Database Operations:
    1. CREATE DATABASE: To create a database simply fire CREATE DATABASE database_name;
    2. SHOW DATABASES: This command lists all the databases in your MySQL server. Use it as SHOW DATABASES;
    3. USE: This query selects the database to be used for further operations. USE database_name;
    4. DROP DATABASE: This query will delete a database for the server. You can fire DROP DATABASE database_name;

  • Table Creation Operations:
    1. It'll be easier to explain if you can check out the example below first:
    CREATE TABLE payments (
    customerNumber int(11) NOT NULL,
    checkNumber (50)NOT NULL,
    paymentDate datetime NOT NULL,
    amount double NOT NULL,
    PRIMARY KEY(customerNumber, checkNumber)

    Here CREATE TABLE is being used to create a table named "payments". "int", "varchar", "double" and "datetime" are some of the most commonly used data types. Integers are denoted by "int" where as  "varchar" stands for variable length character string with the quantity in bracket signifying the maximum length of the same. Check out an advanced SQL tutorial if you are interested in knowing more details.
    2. SHOW TABLES: This query will display a list of all the tables present in the current database. For this fire SHOW TABLES; 
    3. DESCRIBE: If you are interested in knowing the details of a particular table just fire the query DESCRIBE table_name;

  • Data Operations on Table:
    1. INSERT: To insert data into table you can simply do INSERT INTO table_name ( field1, field2,...fieldN )VALUES (value1, value2,...valueN );
    2. DELETE: This query will delete certain tuples. Fire DELETE FROM table_name [WHERE Clause];
    3. SELECT: This is the most used query. It returns the tuples satisfying the given query. Fire SELECT field1, field2,...fieldN table_name1, table_name2...[WHERE Clause] [OFFSET M ] [LIMIT N];. Here LIMIT signify the number of tuples returned and OFFSET will set the point from where the tuples would be processed.