Tag Archives: mysql

Laravel: Configure database – MySQL

Laravel supports four databases: MySQL PostgreSQL SQLite SQL Server  Configuration The database configuration file of the application is located at config/database.php. In this file you may define all of your database connections along with the default connection. Here, we are considering MySQL database connection.

Here, we can see that some environment variables e.g. DB_DATABASE. You can… Read More »

MySQL: DISTINCT clause and its use

There are many cases where we get duplicate rows in the result set. We can remove these duplicate rows using DISTINCT clause in the SELECT statement. NOTE: DISTINCT clause must be the first in the SELECT list. Before we start, we need some data on which we perform DISTINCT clause queries. Table: users

Data:… Read More »

MySQL: GROUP BY clause

GROUP BY clause is used to group values from a column(s). Many calculations can be performed on grouped values of that column(s). AGGREGATE functions (like COUNT, SUM, AVG, etc., functions) can be used on the grouped column(s). Let’s discuss GROUP BY in details. But, before we need to create data on which we shall run… Read More »

MySql: Query related to fetch (N)th maximum salary

Hi All, Here we shall discuss some queries related to nth maximum salary among the employees. Let’s create a table “emp” first:

Now, insert some data in table “emp“:

Get (N)th highest salary of employee using LIMIT:

Get (N)th highest salary of employee without using LIMIT:

Other ways to get the… Read More »

Mysql: Swap two values of a column while other values remain unchanged

Lets consider a table “colors” like below:

Please insert some values(blue, red, and green) for column “color” in random order. Now, its time to write a query to change red into blue and vice versa. But green color will remain unchanged.

That’s it

MySql: Swapping values between two columns

Hello friends, We can swap values of two columns in a table as mentioned below: Lets create a table “example”: example: {x varchar(45), y varchar(45)} Now insert some values in table “example”. Now run the below query to swap values of columns x and y:

Now, its time to swap values of columns from… Read More »

Mysql: Important questions related to duplicate content in database

Hello friends, Following are the important questions related to duplicate content in database: Find duplicate records id in a table: Assume a table: users: {id, name, email} Now insert some duplicate records based on email.

Delete all dupliacte rows except one: Assume a table: users: {id, email} Now insert some duplicate records based on… Read More »

MySQL: Difference between InnoDB and MyISAM

Hello friends, Following are the main differences between mysql’s two important storage engines (MyISAM and InnoDB): 1. MYISAM supports Table-level Locking while InnoDB supports Row-level Locking. 2. MyISAM designed for need of speed while InnoDB designed for maximum performance when processing high volume of data. 3. MyISAM does not support foreign keys while InnoDB supports… Read More »

Zend Framework 2: Use of UNION/UNION ALL and combine() method

There are some cases where we use to display the information collected from different tables specially in the case of generating reports. In that case, we use JOINS, UNION AND UNION ALL. For UNION AND UNION ALL, we can use combine() method. This can be done in ZF2 as mentioned below: