EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Query Commands
Secondary Sidebar
MySQL Tutorial
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL Query Commands

By Priya PedamkarPriya Pedamkar

MySQL-Query-Commands

Introduction to MySQL Query Commands

MySQL is an open-source relational database management system. It is one of the most widely used SQL databases, which provides speed, reliability, and usability. MySQL query is any command that used to retrieve the data from a table. MySQL can be used for querying the data, filtering data, sorting data, joining the tables, grouping data, modifying the data.

Basic MySQL Query Commands

The basic commands are listed below.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,171 ratings)

1. SELECT: This statement used to retrieve the data from the tables and views.

Syntax: SELECT * FROM [TABLE NAME];

Example: SELECT * FROM EMPLOYEE;

2. SELECT DISTINCT: This statement used to retrieve the distinct data from the table and view.

Syntax: SELECT DISTINCT [COLUMN NAME] FROM [TABLE NAME];

Example:SELECT DISTINCT EMP_NAME FROM EMPLOYEE;

3. WHERE: This MySQL Query command is used to filter the data for specific value

Syntax: SELECT * FROM [TABLE NAME] WHERE [CONDITION];

Example: SELECT * FROM EMPLOYEE WHERE EMP_ID=200;

4. AND: This condition is used to filter the data on the basis of conditions.

Syntax:SELECT [COLUMN NAMES] FROM [TABLE NAME] WHERE [CONDITION] AND [CONDITON];

Example:SELECT EMP_NAME, FROM EMPLOYEE WHERE EMP_ID=200 AND EMP_COUNTRY=”INDIA”;

5. OR: This MySQL Query Command combines the data from the table for the specific condition.

Syntax: SELECT [COLUMN NAMES] FROM [TABLE NAME] WHERE TRUE OR FALSE

Example: SELECT * FROM EMPLOYEE WHERE EMP_COUNTRY=”INDIA” OR EMP_COUNTRY =”USA”;

6. IN: This operator helps in filtering the data based on a value match.

Syntax: SELECT COLUMN1, COLUMN2… FROM [TABLE NAME] WHERE [COLUMN NAME] IN (‘val1’,’val2’);

Example:SELECT EMP_NAME, EMP_SALARY FROM EMPLOYEE WHERE EMP_COUNTRY IN (‘INDIA’,‘USA’, ‘NZ’);

7. ORDER BY: It is used to sort the data in a particular order for a particular column in ascending or descending order.

Syntax:SELECT COLUMN1, COLUMN2, FROM [TABLE NAME] ORDER BY Column1 desc, Column2 asc;

Example: SELECT EMP_NAME, EMP_ID FROM EMPLOYEE ORDER BY EMP_NAME desc, EMP_ID asc;

8. LIKE: This MySQL Query Command is used to retrieve the data from the table for the specific pattern.

Syntax:SELECT COLUMN1, COLUMN2 FROM [TABLE NAME] WHERE COLUMN1 Like’’;

Example: SELECT EMP_ID, EMP_NAME, EMP_SALARY FROM EMPLOYEE WHERE EMP_NAME like‘SA%’;

9. BETWEEN: It is used to range the data between the two conditions.

Syntax: SELECT Column1, Column2 FROM EMPLOYEE WHERE Column3 BETWEEN val1 AND val2;

Example: SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_SAL BETWEEN 2000 AND 5000;

10. IS NULL: This is used for checking the value or retrieving the data for the particular column is null.

Syntax:SELECT Column1, Column2 FROM [TABLE NAME] Column3 IS NULL;

Example: SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_SAL IS NULL;

Intermediate MySQL Query Commands

The intermediate commands are listed below.

11. INSERT: This statement allows you to insert one or more rows in the table.

Syntax:INSERT INTO TABLE NAME (Column1, Column2,..) VALUES (val1, val2..);

Example: INSERT INTO EMPLOYEE (EMP_NAME, EMP_SAL) Values (‘TOM’,’3000’);

12. UPDATE: This MySQL Query command is used to update the specific table and column for the particular record.

Syntax: UPDATE [TABLE NAME] SET COLUMN1 =’’ WHERE COLUMN2 =’’;

Example: UPDATE EMPLOYEE SET EMP_SAL=6000 WHERE EMP_ID=200;

13. DELETE: The Command is used to delete the record from the table for a particular value.

Syntax:DELETE FROM [TABLE NAME] WHERE CONDITION;

Example:DELETE FROM EMPLOYEE WHERE EMP_ID=154;

14. INNER JOIN: It allows you to retrieve the data from two table matches in one and other tables.

Syntax: SELECT COLUMN1, COLUMN2 FROM [TABLE 1] INNER JOIN [TABLE 2] ON Condition;

Example: SELECT EMP_NAME, EMP_COUNTRY, DEP_ID FROM EMPLOYEE EMP INNER JOIN DEPARTMENT DEP on EMP.DEP_ID= DEP.DEP_ID;

15. LEFT JOIN: It helps you in providing the data from two or more tables and it will retrieve all the columns from the left table and will provide the data from the right table that matches.

Syntax:SELECT T1.C1, T2.C2 FROM TABLE T1 LEFT JOIN TABLE T2 ON T1.C1= T2.C1;

Example: SELECT E.EMP_ID, D.DEP_ID FROM EMPLOYEE E LEFT JOIN DEP D ON E.DEP_ID = D.DEP_ID;

16. RIGHT JOIN: This MySQL Query command helps in retrieving the data from two or more tables, taking the full records from the right table, and matches the data with the left table to show the records.

Syntax:SELECT T1.C1, T2.C2 FROM TABLE T1 RIGHT JOIN TABLE T2 ON T1.C1= T2.C1;

Example:SELECT E.EMP_ID, D.DEP_ID FROM EMPLOYEE E RIGHT JOIN DEP D ON E.DEP_ID = D.DEP_ID;

17. CROSS JOIN: It will provide the Cartesian product of rows of joined tables like if 10 rows in each table it will simply multiply 10*10=100 records.

Syntax:SELECT * FROM T1 CROSS JOIN T2;

Example:SELECT * FROM EMPLOYEE EMP CROSS JOIN DEP WHERE EMP.DEP_ID= DEP.DEP_ID;

18. GROUP BY: This is used to get the data for the particular value in the combined form.

Syntax: SELECT Column1, Column2 FROM TABLE WHERE CONDITION Group by Col2;

Example: SELECT Count (*), EMP_STATUS FROM EMPLOYEE Group by EMP_STATUS;

19. UNION and UNION ALL: It’s allows you to retrieve the data of multiple queries.

Syntax: SELECT Col1, Col2 from T1 UNION SELECT Col1, Col2 FROM T2;

Example: SELECT ID FROM T1 UNION ALL SELECT ID FROM T2;

20. DROP: This statement is used for dropping the table from the database.

Syntax: DROP TABLE [TABLE NAME] Condition;

Example:DROP TABLE T1, DROP TABLE Like ‘%SA%’;

Advanced MySQL query commands

The advanced commands are listed below.

1. CTE (common table expression): These Commands are used to retrieve the data from the tables.

Syntax: WITH CTE_NAME (Column1, Column2) AS (QUERY)

SELECT * FROM CTE_NAME;

Example: WITH EMP_INDIA AS (SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_COUNTRY=’INDIA’) SELECT EMP_NAME FROM EMP_INDIA WHERE EMP_ID BETWEEN 1 AND 100;

2. SUBQUERY: It means a nested query used for retrieving the data.

Syntax: SELECT Col1, Col2 FROM TABLE T1 WHERE COL3 in (SELECT Col3 from Table T2 WHERE Condition);

Example: SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_SAL in (Select SAL FROM SALARY WHERE Location=’INDIA’)

3. TRANSACTION: This is used to start, commit, and rollback the particular query.

Syntax:START TRANSACTION STATEMENTS TO PERFORM COMMIT;

4. SET: Setting auto-commit off:

Syntax:SET autocommit= OFF; Or SET autocommit= 0;

5. CREATE DATABASE: This MySQL Query command is used to create the new database.

Syntax:CREATE DATABASE DATABASE_NAME;

Syntax:SHOW CREATE DATABASE EMP_SAL_CALCULATION;

To check the database available:

Syntax:SHOW DATABASES;

6. DROP DATABASE: It is used to drop the database from the server.

Syntax:DROP DATABASE database_name; Or DROP SCHEMA database_name;

Tips and Tricks to use

Some of the useful tips and tricks are given.

  • Identify slow queries to optimize and improve performance.
  • An auto-increment should be used on a column index.
  • Indexing should be used on a column of the table.
  • Partitioning MySQL tables.
  • Do not edit the dump files.
  • Use the alias to table and where should be used with Limit 1.

Conclusion

MySQL query commands are explained above that are mainly used to retrieve the data from the database. These commands one should know while doing any development and be comfortable as well. These also help you while preparing for the interviews and any working with other databases.

Recommended Articles

This has been a guide to MySQL Query Commands. Here we have discussed basic as well as advanced and some intermediate MySQL Query commands respectively. You may also look at the following article to learn more –

  1. Hive Commands with Examples
  2. Guide to Oracle Cross Join
  3. Different Examples of Oracle UNION ALL
  4. Guide to PostgreSQL Cross Join
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
4 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more