Use MySQL UNIQUE Constraint in phpMyAdmin

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…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]

Basic Data Analysis with MySQL Shell Python mode

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…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]

ALTER TABLE ADD COLUMN – MySQL Shell Python style

We all know as SQL professionals that a common use of the ALTER TABLE command is that we can change a tables’ structure in a myriad number of ways. And, that’s a good thing too because chances are, you won’t always nail down the initial structure. Due to changing business or application requirements, you may even have to add additional columns that were not considered during the schema design phase. Suppose you have many tables that are structured similarly and they all need a specific column added to their already-existing design. Under certain circumstances, using the MySQL Shell in Python mode (\py), can reduce the number of manual ALTER TABLE statements you have to type. Continue reading to see examples in the MySQL Shell…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]

MySQL IFNULL() function usage in SELECT queries

We all know as SQL professionals that the NULL marker is a special case. Oftentimes, you have NULL‘s stored in your tables, and that’s fine. Suppose you want to present an alternative value in query results where some of the columns have NULL? This is a perfect opportunity (but not the only) to use the IFNULL() function. I find IFNULL() quite useful when exporting query results to a CSV file or other type of flat file, providing something more meaningful than the NULL word itself. However, you only have one substitute for the NULL value when using IFNULL(), so keep that in mind. Continue reading and see examples using IFNULL() in SELECT queries…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]

MySQL Shell Python mode for multiple ALTER TABLE statements – easily

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…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]