MySQL Commands

 

1. CREATE DATABASE :

            CREATE DATABASE database_name;

 Explanation  : Creates a new database with the specified name.

2. USE:

        USE database_name ;

Explanation  : Selects a specific database as the current database for executing subsequent queries.

3. SHOW DATABASES:

     SHOW DATABASES;

Explanation  : Lists all the databases available on the MySQL server.

4. DROP DATABASE:

   DROP DATABASE database_name ;

Explanation  : Deletes the specified database, including all its tables and data. Be careful with this    command as it is irreversible.

5. CREATE TABLE:

   CREATE TABLE table_name (

       column1 datatype, column2 datatype, ...

   );

Explanation : Creates a new table within the selected database. You specify the table name, followed by a list of    columns and their data types.

6. SHOW TABLES:

   SHOW TABLES;

   Explanation  :  Lists all the tables in the currently selected database.

7. DESCRIBE or SHOW COLUMNS:

   DESCRIBE table_name;

   -- or

   SHOW COLUMNS FROM  table_name;

   Explanation  :  Displays the structure of the specified table, showing the columns and their data types.

8. INSERT INTO :

   INSERT INTO table_name (column1, column2, ...)

   VALUES (value1, value2, ...) ;

   Explanation  :  Inserts new records (rows) into an existing table. You specify the table name, the          columns to insert data into, and provide the corresponding values.

 

9. SELECT :

   SELECT column1, column2, ...

   FROM table_name

   WHERE condition ;

   Explanation   :  Retrieves data from one or more tables. You specify the columns to select, the table     name, and an optional condition to filter the results.

 

10. UPDATE:

    UPDATE table_name

    SET column1 = value1, column2 = value2, ...

    WHERE condition ;

    Explanation  :  Modifies existing records in a table. You specify the table name, set new values for      specific columns, and provide a condition to identify the records to be updated.

 

11. DELETE:

    DELETE FROM   table_name

    WHERE   condition;

    Explanation   : Deletes records from a table based on a specified condition. You specify the table      name  and the condition that identifies the records to be deleted.

 

12. ALTER TABLE :

    ALTER TABLE table_name

    ADD column_name datatype;

    Explanation  :  Modifies an existing table, allowing you to add new columns.

 

13. DROP TABLE :

    DROP TABLE  table_name ;

    Explanation  :  Deletes a specific table from the database, including all its data.

 

14. CREATE INDEX :

    CREATE INDEX  index_name  ON  table_name (column_name);

    Explanation   :  Creates an index on a specific column in a table. This improves the speed of data         retrieval operations.

 

15. DROP INDEX :

    DROP INDEX  index_name  ON  table_name;

    Explanation  :  Removes an existing index from a table.

 

16. GRANT :

    GRANT privileges ON database_name.table_name TO 'user'@'host';

    Explanation   :  Grants specific privileges (such as SELECT, INSERT, UPDATE, etc.) to a user on a     specified database or table.

 

17. REVOKE :

    REVOKE privileges ON database_name.table_name FROM 'user'@'host';

    Explanation  :  Removes specific privileges from a user on a database or table.

 

18. CREATE USER :

    CREATE USER 'username'@'host' IDENTIFIED BY 'password';

    Explanation   : Creates a new user account with the specified username and password, and optionally  restricts access to a specific host.

 

19. DROP USER  :

    DROP USER 'username'@'host';

    Explanation   :  Deletes a user account, revoking all privileges associated with that account.

 

20. FLUSH PRIVILEGES :

    FLUSH PRIVILEGES ;

    Explanation   : Reloads the MySQL privilege tables to apply changes made to the user accounts and    privileges.

 

21. SHOW GRANTS :

      SHOW GRANTS FOR 'username'@'host';

    Explanation  : Displays the privileges granted to a specific user on the MySQL server.

 

22. CREATE VIEW :

    CREATE VIEW view_name   AS

    SELECT  column1, column2, ...

    FROM  table_name

    WHERE condition ;

    Explanation     :  Creates a virtual table (view) based on the result of a SELECT query. Views can        simplify complex queries and provide a convenient way to access specific data  subsets.

 

23. DROP VIEW :

    DROP VIEW view_name ;

    Explanation  : Deletes a specific view from the database.

 

24. SHOW VIEWS :

    SHOW  FULL  TABLES  IN database_name WHERE  TABLE_TYPE LIKE 'VIEW' ;

1.     Explanation   : Lists all the views in the specified database.