MySQL | Common MySQL Queries

MySQL server is a open-source relational database management system which is a major support for web based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly Facebook, Twitter and Google depends on MySQL data which are designed and optimized for such purpose. For all these reasons, MySQL server becomes the default choice for web applications.

MySQL server is used for data operations like querying, sorting, filtering, grouping, modifying and joining the tables. Before learning the commonly used queries, let us look into some of the advantages of MySQL.

Advantages of MySQL :



  • Fast and high Performance database.
  • Easy to use, mainitain and administer.
  • Easily available and maintain integrity of database.
  • Provides scalability, usability and reliability.
  • Low cost hardware.
  • MySQL can read simple and complex queries and write operations.
  • InnoDB is default and widely used storage engine.
  • Provides strong indexing support.
  • Provides SSL support for secured connections.
  • Provides powerful data encryption and accuracy.
  • Provides Cross platform compatability.
  • Provides minimized code repetition.

Queries can be understood as the commands which interacts with database tables to work around with data. Some of the commonly used MySQL queries, operators and functions are as follows :

  1. SHOW DATABASES

    This displays information of all the existing databases in the server.

    Output:

    Note : The databases ‘information_schema’, ‘mysql’ and ‘performance_schema’ are system databases which are used internally by MySQL server. A ‘test’ database is meant for testing purpose which is provided during installation.


  2. USE database_name
    database_name : name of the database
    This sets the database as the current database in the MySQL server.
    To display the current database name which is set, use syntax

    SELECT DATABASE(); 

  3. DESCRIBE table_name
    table_name : name of the table
    This describes the columns of the table_name with respect to Field, Type, Null, Key, Default, Extra.

  4. SHOW TABLES
    This shows all the tables in the selected database as a information.

  5. SHOW CREATE TABLE table_name
    table_name : name of the table
    This shows the complete CREATE TABLE statement used by MySQL for creating the table.

  6. SELECT NOW()
    MySQL queries mostly starts with SELECT statement.
    This query shows the current date and time.


    Output :

    2019-09-24 07:08:30 

  7. SELECT 2 + 4;

    Output :

    6 

    This executes SELECT statement without any table.
    SELECT can be used for executing an expression or evaluatin an in-built function.
    SELECT can also be used for more than one or many columns .

    Example :

    SELECT 2+4, CURDATE(); 

    Output :


  8. Comments
    Comments are of two types. Multi-line comments or single-line or end-of-line comment.

    /*  These are multi-line comments.  */ 
     #  This is single-line comment.
     -- This is also single-line comment.

  9. CREATE DATABASE database_name
    database_name : name of the database
    This statement creates a new database.

  10. DROP DATABASE database_name
    database_name : name of the database
    This statement deletes the database.
    Note : User has to be very careful before deleting a database as it will loose all the crucial
    information stored in the database.


  11. CREATE TABLE table_name(column1, column2, column3..)
    table_name : name of the table
    column1 : name of first column
    column2 : name of second column
    column3 : name of third column
    When the developer start building an application, he needs to create database tables.
    This statement creates a new table with the given columns.

    Example :

    CREATE TABLE  employee(
       'id' INTEGER  NOT NULL AUTO_INCREMENT,
       'name' VARCHAR(30) NOT NULL,
       'profile' VARCHAR(40) DEFAULT 'engineer',
       PRIMARY KEY ('id')
    )ENGINE = InnoDB; 

    Note : You have ‘id’ column as AUTO_INCREMENT with a primary key constraint which ensures that each id is incremented value, avoiding duplication. Storage engine selected is ‘InnoDB’ allowing foreign key constraint and related transactions.


  12. AUTO_INCREMENT
    It is used to generate a unique identification field for new row.

  13. DROP TABLE table_name
    table_name : name of the table
    This statement deletes the mentioned table.

  14. RENAME TABLE old_table_name TO new_table_name
    old_table_name : name of the previous table.
    new_table_name : name of the new table.
    This statement renames the table to a new name.

  15. ALTER TABLE table_name ADD(column1, column2, column3..)
    table_name : name of the existing table.
    column1 : name of first column.
    column2 : name of second column.
    column3 : name of third column.
    This statement adds columns to the existing table.

  16. ALTER TABLE table_name DROP(column1)
    table_name : name of the existing table.
    column1 : name of first column.
    This statement deletes specified columns from the existing table.

  17. INSERT INTO table_name (column1, column2, column3 . . ) VALUES(value1, value2, value3 . . )
    table_name : name of the existing table.
    column1 : name of first column.
    column2 : name of second column.
    column3 : name of third column.
    value1 : value for first column.
    value2 : value for second column.
    value3 : value for third column.
    This statement inserts a new record into a table with specified values.

  18. UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3.. WHERE condition
    table_name : name of the table.
    column1 : name of first column.
    column2 : name of second column.
    column3 : name of third column.
    value1 : value for first column.
    value2 : value for second column.
    value3 : value for third column.
    condition : the condition statement.
    This statement updates records in the table with the new given values for the columns.


    Note : WHERE clause in MySQL queries is used to filter rows for a specific condition.


  19. DELETE FROM table_name WHERE condition
    table_name : name of the table.
    condition : the condition statement.
    This statement deletes records from the table.

  20. SELECT column1, column2, column3.. FROM table_name WHERE condition
    table_name : name of the table.
    column1 : name of first column.
    column2 : name of second column.
    column3 : name of third column.
    condition : the condition statement.
    This statement executes and gives records from specific columns from the table which matches the condition after WHERE clause.

  21. SELECT * FROM table_name
    table_name : name of the table.
    Instead of specifying one column or many columns, you can use an asterisk (*) which represents all columns of table. This query retrieves all records from the table.

  22. COUNT
    The COUNT function is used to return total number of records matching a condition from any table.
    It is one of the known AGGREGATE function.

    Example :

    SELECT COUNT(*) from student; 

    Note : AGGREGATE functions allow you to run calculations on data and provide information by using
    a SELECT query.


  23. MAX
    It is used to get the maximum numeric value of a particular column of table.
    Example :

    SELECT MAX(marks) FROM student_report;  

    Note :The MIN and MAX functions work correctly on numeric as well as alphabetic values.


  24. MIN
    It is used to get the minimum numeric value of a particular column of table.
    Example :

    SELECT MIN(marks) FROM student_report;  

    Note : The above given example queries can also be nested with each other depending on the requirement.

    Example :

    SELECT MIN(marks) 
    FROM student_report 
    WHERE marks > ( SELECT MIN(marks) from student_report); 

  25. LIMIT
    It is used to set the limit of number of records in result set.

    Example :

    SELECT * 
    FROM student limit 4, 10; 

    This gives 10 records starting from the 5th record.


  26. BETWEEN
    It is used to get records from the specified lower limit to upper limit.
    This verifies if a value lies within that given range.

    Example :

    SELECT * FROM employee 
    WHERE age BETWEEN 25 to 45. 


  27. DISTINCT
    This is used to fetch all distinct records avoiding all duplicate ones.

    Example :

    SELECT DISTINCT profile 
    FROM employee; 

  28. IN clause
    This verfies if a row is contained in a set of given values.
    It is used instead of using so many OR clause in a query.

    Example :

    SELECT * 
    FROM employee 
    WHERE age IN(40, 50, 55); 

  29. AND
    This condition in MySQL queries are used to filter the result data based on AND conditions.

    Example :

    SELECT NAME, AGE 
    FROM student 
    WHERE marks > 95 AND grade = 7; 

  30. OR
    This condition in MySQL queries are used to filter the result data based on OR conditions.

    Example :

    SELECT * 
    FROM student 
    WHERE address = 'Hyderabad' OR address = 'Bangalore'; 

  31. IS NULL
    This keyword is used for boolean comparison or to check if the data value of a column is null.
    Example :

    SELECT * 
    FROM employee 
    WHERE contact_number IS NULL; 

  32. FOREIGN KEY
    It is used for pointing a PRIMARY KEY of another table.

    Example :

    CREATE TABLE Customers
     (
     id INT AUTO_INCREMENT PRIMARY KEY, 
     name VARCHAR(30) NOT NULL, 
    )
    
    CREATE TABLE Orders
    (
     order_id INT AUTO_INCREMENT PRIMARY KEY,
     FOREIGN KEY (id) REFERENCES Customers(id)
     ); 

    Note : This is not used in the MYISAM storage engine of MySQL server.
    InnoDB storage engines supports foreign key constraints.


  33. LIKE
    This is used to fetch records matching for specified string pattern.

    Example :

    SELECT * 
    FROM employee 
    WHERE name LIKE 'Sh%';
    
    SELECT * 
    FROM employee  
    WHERE name LIKE '%Sh%';   

    Note : Percentage signs (%) in the query represent zero or more characters.


  34. JOINS
    Joins are the joining of two or more database tables to fetch data based on a common field.
    There are various types of joins with different names in different databases.
    Commonly known joins are self join, outer join, inner join and many more.

    Regular Join :
    It is the join which gets all the records from both the tables which exactly match the given condition.
    Example :

    SELECT student.name, department.name 
    FROM student JOIN department ON student.department = department.name 

    Left Join :
    It is the join which gets all the records that match the given condition, and also fetch all the records from
    the left table.
    Example :

    SELECT student.name, department.name 
    FROM student LEFT JOIN department ON student.deptartment = department.name 

    Right Join :
    It is the join which gets all the records that match the given condition, and also fetch all the records from
    the right table.
    Example :

    SELECT student.name, department.name 
    FROM student RIGHT JOIN department on student.department = department.name 

  35. ADD or DROP a column
    A new column can be added on a database table, if required later on.

    Example :

    ALTER TABLE employee ADD COLUMN salary VARCHAR(25); 

    Similarly, any column can be deleted from a database table.

    Example :

    ALTER TABLE employee DROP COLUMN salary; 


  36. Conclusion :
    Running MySQL queries are the most commonly-performed tasks used to get data within the process of database management. There are many database managment tools like phpMyAdmin, that helps to perform queries and handle transactions comfortably with visual data results. You have scrolled the most common MySQL queries, which are used in daily coding practices. Developer can customize or enhance the queries with respect to a particular requirement.



    My Personal Notes arrow_drop_up

    Check out this Author's contributed articles.

    If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

    Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




    Article Tags :
    Practice Tags :


    1


    Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.