Category Archives: MySql

MySql

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: Configure Multiple database adapters

Step 1: Consider two databases db1 and db2 with table “countries” in each database.

Step 2: Configure multiple databases in ZF2 in config/global/global.php

Step 3: Create Application/Model/CountryTable.php class to manipulate country database.

Step 4: Create another class to access database table instance:

Step 5: Create invokable under “service_manager” key in global.php… Read More »

Zend Framework 2: Configure database

Creating an Adapter Creating an adapter is done by instantiating the Zend\Db\Adapter\Adapter class.

But this is not a better way to instantiate the adapter. By default, ZF2 considers a predefined configuration key “db” (Zend\Db\Adapter\AdapterServiceFactory) so that we can define database adapter configuration in global.php with key “db”. Also, we need to create a factory… Read More »

Zend Framework 2: MySql Query Support

Every developer has basic knowledge of MySql queries. But, ZF2 provides MySql support so that we can write queries in manageable way. Lets create two tables : countries(country_id,country_name) and states(state_id,country_id,state_name) I assume you have configured database settings in ZF2. 1) For transactions

2) For other queries support, refer below example and execute each statement… Read More »