The 10 Most Common MySQL Queries
September 23, 2010
MySQL queries are usually written once and then wrapped in class functions to minimize code repetition. This article lists the 10 queries used most often in MySQL.
1. Create Table
You would not use the create table query every time the script executes in normal scenarios. However, when you start building an application, you need to create database tables. To do so with a primary key, you can use the following query.
The above query creates a table "emp" in the selected database. We will have an "id" column as auto increment and with a PRIMARY KEY constraint, which ensures an incremented integer value is added every time a new row is inserted; the constraint checks for non-duplicate value. The column "dept" is populated by default values ("sales" if no value is supplied). You can specify the "Engine" to be used while creating the table. Otherwise, the default engine will be used. We used "InnoDB" here because it allows FOREIGN KEY and transactions.
2. Insert Query
Now that you have a table in your database, let us see how you can insert values into that table. You might have used insert query many times, but have you used an insert query that not only inserts data but can also updates data if need be? This is achieved by using the keyword "on DUPLICATE KEY." Here is an example:
Simple insert query
Insert and Update query
In the above query, if the value of "id" passed already exists, then we can update the value of the row instead of inserting a new row. This is more useful when you need to check columns other than auto-incremented ones.
Joins are supposed to be complicated because there is so much variety in them. Joins are known by different names in different databases: self join, outer join, inner join, natural join, and so many others. We will use a join between two tables, which is generally used to pull data.
Let us assume we have another table called "departments", which has a list of all the departments. If you want to pull employee data including departments, the query would be:
The regular join will get all the records that match the condition in both tables. The left join will match records that match the condition as above, but it will also get all the unmatched records from the left table. On the other hand, the right join will get all the unmatched records from the right table.
4. Create and Drop Constraints
Often times you need to edit/add/delete constraints applied on a table. You can do so using the following queries:
Add a primary key:
Drop a Primary key:
While adding a primary key, we first drop the already added keys and then add the key to a new column. Otherwise it won't allow you to add the key.
5. Order By, Having Clauses
Order by is used to arrange data by a specific column and then
In the above query the result set would contain data organized by departments and those who have salaries greater than $10,000. It's worth noting that when we use the "having" clause without "order by" it works more like the "where" clause.
6. Adding Indexing and Search Queries
Creating indexes for a column undoubtedly increases incoming queries to the database, and indexes are created on a column basis. For example, in our sample table "emp", if most of the queries fetch data using the column id, then it would be a wise decision to create an index for the id column.
Upon creating indexes, MySQL will first search for ID in the created index and if none is found it will run the query on the table level. So, that speeds up the process if indexes are created wisely.
To create an Index, the following query is used.
Create index at time of creating table
Create Index for an existing table
7. Aggregate Function Queries
Aggregate functions are used to perform mathematical operations on the data
retrieved from the database. These types of functions include
The above query will return the name of the employee and count in the department, and it is then arranged in descending order of the count returned.
7. Alter and Update Columns of a Table
Tables are created and then edited quite often. In the following few lines we will see how to add/change columns of an existing table.
Add a column
Edit a column
Salary should be in integer instead of having varchar, so let's change its datatype to int. Note that there may be loss of data if there is a compatibility issue.
Rename a column
Drop a column
8. Create a New Table with Non-Duplicate Values
Suppose you have thousands of records in your database table and when you started it the data wasn't entered correctly and contains repetitions. Now you plan to clean up all the junk and create a new table. Let's see how we do it.
So it will create a new table "emp2" with only the employees having unique "last names". You can sort your table with a different approach.
9. Creating Dump of Your Database and Optimizing the Table
There may be times when your application needs to schedule backups and send them over email or keep them somewhere safe. You can write a script to do so and set a cron for the same thing. That will save a lot of work with backing up your work and avoid running into some unanticipated trouble. Let's see how you can create a backup of your database as well as optimize the table.
This will create a sql dump file, which can be then imported again to restore the database at some level.
10. Optimizing a Table
It's pretty straightforward to optimize a table in MySQL, as the name indicates:
You have learned to use the most common queries in MySQL ,which are used in day-to-day coding. You can enhance these queries to suit your particular scenario.