If knowledge is power, then sharing knowledge is ultimate powertrip
PMP®, SAFe®5 POPM, SAFe®5 SSM, SAFe®5 SA, Prince2, MCP
You are here: Home > Databases


Feed Subscription

Difference between HAVING and WHERE Clause

The difference between having and where clause are given below. HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. A HAVING clause is ... Read More »

Difference between group by and order by in mysql

The difference between group by and order by in mysql is as follow: ORDER BY alters the order in which items are returned. GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc). Read More »

Difference between distinct and group by in mysql

The difference between DISTINCT and GROUP BY in Mysql is that they usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that ... Read More »

Full Text Search in MySQL

MySQL supports text searching by using the LIKE statement and regular expression. However, when the text column is large and the number of rows in a table is increased, using those methods has limitations: Performance: MySQL has to scan the whole table to find the exact text based on pattern in the LIKE statement or pattern in the regular expressions. Flexible search: with the LIKE statement and regular expression ... Read More »

MySQL Cheat Sheet

MySQL Cheat Sheet for your reference. Working with Database Create a database with a specified name if it does not exist in database server 1 CREATE DATABASE [IF NOT EXISTS] database_name Use database or change current database to another database you are working with 1 USE database_name Drop a database with specified name permanently. All physical file associated with the database is no longer exists. 1 DROP DATABASE [IF EXISTS] database_name Show all available databases ... Read More »

Triggers in MySql

Triggers in mysql  is a set of Sql statements stored in the database catalog. A Sql trigger is executed or fired whenever an event associated with a table occurs e.g.,  insert, update or delete. Triggers in mysql is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and ... Read More »

Difference between InnoDB and MyISAM

Few difference between InnoDB and MyISAM are as: MYISAM: MYISAM supports Table-level Locking MyISAM designed for need of speed MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI) MYISAM not supports transaction. You cannot commit and rollback with ... Read More »

Query Caching in MySql

In some installations of MySQL, the query caching in MySql is disabled by default so you will have to do a little configuration to set things up. The way to check is to log into MySQL and issue the following command: mysql> show variables like ‘query%’; +——————————+———+ | Variable_name                | Value   | +——————————+———+ | query_alloc_block_size       | 8192    | | query_cache_limit            ... Read More »

Finding rows with no match in another table

Here, I would like discuss about finding rows with no match in another table. The preceding sections focused on finding matches between two tables. But the answers to some questions require determining which records do not have a match (or, stated another way, which records have values that are missing from the other table). For example, you might want to ... Read More »

Scroll To Top