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]
PHP provides a handy function, empty(), that is used
to determine whether a variable is empty. Perhaps that
is a bit confusing to someone unfamiliar with the
empty() function and I can see how. In this blog
post, I will cover: what empty() means in PHP, what
the empty() function does, and a use case pairing up
empty() with the PHP ternary operator conditional
construct. Both used in combination with the MySQL
NULL value. Continue reading and see examples of
empty()…
Photo by Debby Hudson on …
[Read more]
In Use MySQL BLOB column with PHP to store .pdf
file, I covered an example of how to store a .pdf file in the
actual database table using the MySQL BLOB datatype
and PHP. Now that we have .pdf’s stored in the database, how do
we display them in the browser? This blog post answers that exact
question. Continue reading to see a working example using PHP…
Self-Promotion:
If you …
[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:
The MySQL DROP statement is one of many powerful DDL
commands. Be it ALTER TABLE some_table DROP
some_column or DROP some_table, this type of
command can drastically change your data landscape
because in executing MySQL DROP, you are completely
removing objects from the database! If you are using the phpMyAdmin web
interface, you can execute the MySQL DROP
statement with just a few mouse clicks. Continue reading to see
how…
Drops of water.
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!
Note: The …
[Read more]
