Most developers use some form of auto-incrementing integer counter for a given database table, ensuring uniqueness among the rows. Several of the popular SQL dialects implement this facility. For instance, MySQL’s AUTO_INCREMENT attribute is used to provide a unique identity for a table row. What exactly is the behavior of AUTO_INCREMENT? Can you explicitly use a value of your choosing for it if you need to? How does it count? Continue reading and know the answers to these questions and more…

Image by Gerd Altmann from …

CSV imports with MySQL Workbench, is super simple. Since CSV’s are probably the most common data interchange format, it goes without saying that importing CSV data into MySQL is a staple task for all DBA’s and Developers. Continue reading to learn how easy it is using MySQL Workbench…

Image by OpenClipart-Vectors from Pixabay

Self-Promotion:

If you enjoy the content written here, by all means, …

Some believe the most important part of SQL is the ability to query data. Queries typically retrieve data by joining many tables together into useful result sets. This tutorial takes the position that visibility into the data helps those new to SQL understand how joins work. To that end, the queries use Common Tabular Expressions (CTEs) instead of tables.

Default behavior of a JOIN without a qualifying descriptor is not simple because it may return:

• A CROSS JOIN (or Cartesian Product) when there is no ON or USING subclause, or
• An INNER JOIN when you use an ON or USING subclause.

The following query uses JOIN without a qualifier or an ON or USING subclause. It also uses two copies of the single CTE, which is more or less a derived table and the result of a subquery held in memory. This demonstrates the key …

MySQL membership conditions are in the MySQL 8 Documentation. They’re found in the 13.2.11.3 Subqueries with ANY, IN, or SOME section. The IN and =ANY operators both perform equality matches with one twist. The IN operator works with a set of values or a subquery but the =ANY operator only works with a subquery.

I created the digits, letters, and words tables for this example. They hold the following values respectively:

• The numbers table holds the values of 1, 2, 3, and 4
• The letters table holds the values of 'a', 'b', 'c', and 'd'
• The words table holds the values of 'Captain America', 'Iron …

I recently published a blog post about a portfolio project I am developing using the LAMP stack, Bootstrap 4, jQuery, and the MVC (Model-View-Controller) design pattern in core PHP. In this post, I will introduce an additional feature I integrated into the existing project.

Image by xresch from Pixabay

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and …

While you can download MySQL as a DMG package, a number of users would prefer to install it as a Docker instance. You won’t find the macOS downloads on the same web site as other downloads. You can use the following macOS download site.

After installing Docker on your macOS, you can pull a copy of the current MySQL Server with the following command:

docker pull mysql/mysql-server

You should create a mysql directory inside your ~/Documents directory with this command:

mkdir ~/Documents/mysql

Then, you should use the cd command to change into the ~/Documents/mysql directory and run this command:

pwd

It should return the following directory: …

Introduction Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java. Articles From version 2.12, Percona PMM uses Victoria Metrics instead of Prometheus. Victoria Metrics provides better disk I/0 utilization and less memory usage. For more details about this change and its benefits, check out this article. By default, the MySQL JDBC Driver only emulates prepared statements. If you wonder whether server-side prepared statements perform better... Read More

The post High-Performance Java Persistence Newsletter, Issue 22 appeared first on …

If you use SQL on a regular basis, then you are well aware that Window Functions are powerful. They allow us to simplify queries that would otherwise be quite the mess. We can provide meaningful insight across rows of data without collapsing the results into a single value. I have written numerous blog posts on Window Functions, many here recently. I decided to make this blog post a compilation of all the Window Function posts I have written, providing a one-stop source for any readers interested in learning more about Window Functions…

Image by Free-Photos from …

Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…

Image by …

I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.

In fact, he adds a prequel_film_id column to the film table in the sakila database and then a single row to demonstrate a minimal self-join query. I wanted to show them how to view a series of rows interconnected by a self-join, like the following:

SELECT   f.title AS film
,        fp.title AS prequel
FROM     film f LEFT JOIN film fp
ON       f.prequel_id = fp.film_id
WHERE    f.series_name = 'Harry Potter'
AND      fp.series_name = 'Harry Potter'
ORDER BY f.series_number;

It returns the following result set:

 …