MySQL

Aveek Das
Using RANK and DENSE_RANK Functions in MySQL

Overview of MySQL Window Functions

October 1, 2021 by

In this article, we are going to learn about the different types of MySQL Window Functions and how to use them as per different use cases. MySQL is one of the most commonly used databases in the software world today. Almost every other web application running uses MySQL as a database, either on-premise or on the cloud. It becomes extremely necessary for developers and database users to master their querying abilities such that they can query the databases as per the requirements and produce suitable results. In this article, we are going to focus on the MySQL Window Functions and how to use them with practical examples. An important point to note is that most of the MySQL Window Functions are applicable only to versions 8 or higher.

Read more »
Nisarg Upadhyay
SELECT Query with space in columns name without errors

How to write SQL queries with spaces in column names

September 29, 2021 by

In this article, we are going to learn how we can write a SQL query with space in the column name. Blanks spaces are restricted in the naming convention of the database object’s name and column name of the table. If you want to include the blanks space in the object name or column name, the query and application code must be written differently. You must be careful and precise while writing dynamic SQL queries. This article explains how we can handle object names and columns with blank space in SQL Server and MySQL.

Read more »
Nisarg Upadhyay
View data from a movies_backup table

Learn MySQL: MySQL Copy table

September 7, 2021 by

This article explains the MySQL copy table process. In this article, you will learn how we can copy data from one table to another table. These tables could be in the same database or different databases. The MySQL copy table process can copy a specific dataset or all data from the source table to the destination table. We can use the MySQL copy table process to replicate the issues that occurred on production servers, which helps developers rectify the issues quickly.

Read more »
Aveek Das
CloudFormation Template on AWS Console

Spinning up MySQL instances on RDS using CloudFormation Templates

April 20, 2021 by

In this article, we are going to discuss how to set up a MySQL instance on AWS RDS using Cloud Formation templates. In my previous article, How to configure an Amazon RDS environment for MySQL, I have provided a detailed walkthrough of how to set up a MySQL instance on Amazon. You can use the AWS console to provide all the information required for setting up the instance and then use it. However, in this article, we will discuss an automated way of achieving the same functionalities using Cloud Formation templates.

Read more »
Nisarg Upadhyay
CASE expression in ORDER BY exmple

Learn MySQL: Control Flow functions

March 19, 2021 by

In this article, we are going to learn about the most common control flow functions. The control flow function evaluates the condition specified in it. The output generated by them can be a true, false, static value or column expression. We can use the control flow functions in the SELECT, WHERE, ORDER BY, and GROUP BY clause. Following are the most common functions:

Read more »
Frank Solomon
A CTE WHERE clause filter, that keeps the indicated rows and removes everything else, would really help.

MySQL Recursive Queries

March 18, 2021 by

Introduction

As we lever MySQL to build database solutions, we might need to build a MySQL recursive query. In an earlier Database Journal article, I showed how to solve an integer parsing problem with SQL Server recursion. This article will show how to solve that same problem with MySQL recursion, highlighting the strong and weak points of this MySQL feature.

Read more »
Nisarg Upadhyay
Server status screen

Learn MySQL: Run multiple instances of MySQL Server on Windows 10

March 11, 2021 by

This article explains how we can run multiple instances of the MySQL Server on a single machine. In How to install MySQL database server 8.0.19 on Windows 10 article, I have explained the installation process of MySQL on windows 10. For the demonstration, I have installed MySQL 8.0.19 on my workstation. We can view the status of the MySQL Server using MySQL workbench. To view the server status, open MySQL workbench connect to the MySQL instance Click on Server Select Server Status.

Read more »
Nisarg Upadhyay
MySQL logs are enable

Learn MySQL: An overview of MySQL Binary Logs

February 9, 2021 by

In this article, we are going to learn about the concept of MySQL Binary Logs and their architecture. I am covering the following details in this article:

  1. An overview of MySQL binary logs and their architecture
  2. How to enable and disable binary logging
  3. View and change the location of the binary logs
Read more »
Rajendra Gupta
stores the content on a new page

Database Cloning in Amazon Aurora

January 11, 2021 by

Amazon Aurora provides a MySQL and PostgreSQL compatible relation database with performance and feature enhancements over the existing databases. In the previous articles, we discussed the following features. You can refer to ToC at the bottom.

  • Backtrack
  • Global Database
  • Amazon Aurora Serverless

In this article, we will cover fast database cloning and its usage for aurora databases.

Read more »
Nisarg Upadhyay
create and drop temp table: create a new temp table using select query.

Learn MySQL: Create and drop temp tables

August 18, 2020 by

In this article, we are going to learn how to create a temp table and further drop these tables. The temporary tables are session-specific tables that are created within the session. When the session closes, the table is dropped by itself. A temporary table exists within a session only; therefore, a table created by one session is not visible to another session. For example, Session ID 501 has created a temporary table then session ID 502 cannot access it or manipulate it. Two different sessions can have a temporary table with the same name.

Read more »