Basic Syntax in MySQL
Previous Home Next

MySQL is a Case-insensitive language i.e commands can written in either lower or uppar letter case and query search also be independent of its case.

The basic commands can be categorise, they are

General Commands

  1. USE : we need to select to some database when we first start or connect to MySQL.
  2. Syntax

    USE database_name;
  3. SHOW: Listing all databases on the system.
  4. Syntax

  5. SHOW TABLES: Listing of all tables from the specified database given in the command.
  6. Syntax

    SHOW TABLES FROM databasename;
  7. DESCRIBE or DESC: This commands gives the description of all the columns from the given table name along with its type and other information.
  8. Syntax

    DESCRIBE (or DESC) table_name;

    SHOW FIELDS or COLUMNS FROM table_name gives the exact result as DESC command gives.

Table Commands

  1. CREATE: This command is used to create a new table or database.
  2. Syntax for creating database

    CREATE DATABASE database_name;

    Syntax for creating table

    CREATE TABLE table_name (create_clause1, create_clause2, ...);


    CREATE TABLE studadr (said int(5) not null auto_increment
     Primary key, aid int not null references studinfo(sid),
     blck varchar(5) not null,hno int(5) not null, 
     street varchar(20) not null, locatn varchar(20) not null,
    state varchar(10) not null, country varchar(20) not null default'India');

    In the above create command certain clause we have use whose description is given below:

    1. AUTO_INCREMENT : It is used to increase its value into which is assigned along with NOT NULL value.
    2. PRIMARY KEY : It is a uniquily identified key in every table, which helps in retrieving date. This column should also be NOT NULL.
    3. NOT NULL : No NULL values are allowed in this column.It generates an error message as the data is inserted into the table.
    4. DEFAULT value : If a NULL value is used in the data for this column, the default value is entered instead.
  3. DROP: Remove the table from the database we are in permanently. It can also remove full database, So be careful while using this command
  4. Syntax

    DROP TABLE table_name;
    DROP DATABASE database_name;
  5. ALTER: The type of alter define and perform it in this command.
  6. Syntax

    //Adds the define columns to the table.
    ALTER TABLE table_name ADD (create_clause1, create_clause2, ...);
    //Drops the define columns from the table. 
    ALTER TABLE table_name DROP column_name;
    //Modifiers define to a column 
    ALTER TABLE table_name MODIFY create_clause;
Data Commands
  1. INSERT: This command is use to insert a complete row of data for every column in the order in which the table is define.
  2. Syntax

    INSERT INTO table_name VALUES (value1, value2, ...);


    //inserting all values in table define order
    INSERT INTO studadr VALUES(409,103,'d',52,
    //inserting values in another way
    INSERT INTO studadr(said,aid,blck,hno,street,locatn,state, country)
  3. DELETE: Delete the entire row which meets the where clause.
  4. DELETE FROM table_name WHERE where_clause;


    DELETE FROM studadr WHERE said=410;

Privilege Commands

Most of these commands require MySQL root access

  1. Grant: It is used to provide access or privilages on the databases object to the users.
  2. Example

    GRANT privilegename
    ON objectname
    TO {username |PUBLIC |rolename}
  3. Revoke: It is used to remove access or privilages to the database object.
  4. Example

    REVOKE privilegename
    ON objectname
    FROM {username |PUBLIC |rolename} 
Previous Home Next