Rolling sum and average query results are possible by combining
the aggregate functions SUM() or AVG()
with the OVER() clause, making for powerful analytic
queries. I recently learned how to compute a rolling
average or sum of values by using the Windowing option
of the OVER() clause, applying the concepts to a
data set I am familiar with. I wanted to share my learning with
any readers who might be interested…
Image by Steve Buissinne from …
[Read more]
The Window Ranking functions: ROW_NUMBER(),
RANK(), and DENSE_RANK() each
rank rows with an increasing integer value. I wrote a
previous blog post, ROW_NUMBER() Window Function – find duplicate
values, where I covered how the ROW_NUMBER()
window function can be used to target any duplicate rows, with
the use of the PARTITION BY clause in the
OVER() clause. In this post, I cover the differences
between RANK(), and DENSE_RANK() in
handling any ties according to the sorting performed by the
ORDER BY clause with regards to the assigned
increasing integer. Continue reading and see examples…
…
[Read more]
Many times, we do not want duplicate rows or values in our SQL
tables. On the other hand, in some situations, it does not matter
if there are duplicates present. For whatever reason, suppose
duplicates have found their way into one of your tables. How can
you find them quickly and easily? The ROW_NUMBER()
Window function is a fantastic tool to use. Continue
reading and see example queries you can apply to your own tables
and find those duplicates…
Photo by Joe Green on Unsplash
OS and DB used:
These days, I mostly program in PHP with MySQL as the database,
which is just fine by me. I have had a long-time interest in
MySQL (SQL in general), and after several steady months
of programming in PHP, I must say I have really come into the
language and developed a fondness for it. All that being said, I
still enjoy using and learning the MySQL Shell in Python mode. As
Database Developers, we often need the LAST INSERT
ID value from a previous INSERT statement on
a column that has the AUTO_INCREMENT attribute.
MySQL Shell has a get_auto_increment_value() method
we can call against a Shell object result and retrieve that
value. Continue reading and see examples of the MySQL Shell
get_auto_increment_value() method used in Python
mode…
…
[Read more]
Like always I am sharing new things I learn here on my blog. I
was recently working on a requirement for a LAMP stack web
application reporting dashboard in which I needed to store – and
eventually – retrieve a .pdf file. I have read in several places
(this fantastic book is a great resource) that
a viable option is storing images or documents (.pdf in this
case) in the actual database table as opposed to on the server
file system. MySQL has the BLOB datatype that can be
used to store files such as .pdf, .jpg, .txt, and the like. In
this blog post, I cover how I accomplished uploading and storing
the actual .pdf file in a BLOB column in MySQL using
PHP. Any corrections, tips, pointers, and recommendations for
best practices are always welcome. We all learn as we go!!!
Photo by …
[Read more]
The MySQL UNIQUE constraint is often used in a
column definition in which we need each value for that column to
be distinct from the others. Perhaps it is an email column for an
on-line registration form and we want to ensure that users cannot
register twice for an account using the same email. Whatever the
case may be, UNIQUE is there to help us ensure this
type of data integrity or business requirement. What if the
target table already exists and you determine you need to add a
UNIQUE constraint to an existing column? In this
post, I will cover 2 ways you can implement a UNIQUE
constraint on existing columns using the phpMyAdmin web
interface…
Photo by Kaleidico on …
[Read more]I recently watched a fantastic Python Pandas library tutorial series on YouTube. Without a doubt, Pandas is great for all sorts of data stuff. On the same token, MySQL Shell in Python mode is quite powerful in the sense that Python and the MySQL Shell (version >= 8.0) are somewhat united in the same environment. Although Pandas is in a league all its own when it comes to data analysis, between the power of MySQL and Python, we can also perform some basic analysis easily in MySQL Shell Python mode. In this blog post, I will cover some basic data analysis using Python mode in the MySQL Shell. Continue reading to see examples…
Business vector created by freepik – www.freepik.com
OS, Software, and DB used:
Using the MySQL ALTER TABLE command, you can easily
change an existing columns’ name and datatype. With just a few
clicks, you can do the same in the phpMyAdmin visual web
interface. For many developers, this interface is the one they
lean on most while programming so it can’t hurt to know how to do
it yourself should you find yourself programming in this
environment…
Self-Promotion
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Suppose we have a table with these 3 columns:
Our goal is to rename the title column to paper_title and to also change the datatype …
[Read more]
There may come a time you need to rename one or more columns in
an existing MySQL table for a variety of reasons. Using
ALTER TABLE, to rename a column is an easy enough
command. But, suppose there are multiple tables in the same
database/schema that have the same-named column and all of those
columns need to be renamed. That could be a lot of ALTER
TABLE statements to type out. Save your energy and time,
avoiding all of those ALTER TABLE commands
all-together. If you are lucky enough to be working with a MySQL
version > 8.0 then the Shell is your salvation. With just a
few lines of Python code in \py mode, all of your
trouble(s) and headache(s) are no more…
Photo by Ibrahim Rifath on …
[Read more]
Date and time values are some of the most important datatypes in
an RDBMS. From tracking order dates to payroll hours,
DATE and DATETIME datatypes are used in
all types of applications. At times as a Developer, you may need
only certain portions of a DATE or
DATETIME value. In MySQL, the EXTRACT()
function can provide you with a specific component of a
DATE or DATETIME value depending on
which INTERVAL is given as a parameter. Continue
reading to see example EXTRACT() queries for
understanding…
Photo by Omar Al-Ghossen on Unsplash …
[Read more]
