MySQL Useful tips and reference project

MySQL is the most popular DB for several years. Yet many users struggles working with MySQL even for easy tasks like:

• change root password
• get count of null values per column
• get min/max/average per column

In this post:

• MySQL General tips
• MySQL Install and uninstall
• MySQL Administration
• MySQL selects
• MySQL updates

My idea is to collect an useful SQL queries and tips in one place which to be useful for newbies or advanced users ( if they want to do a reference to the latest changes). The form of the reference project should be a shortest possible solution in well understandable way.

Reference project

The MySQL reference project is organized in several sections like:

• DML - Data Manipulation Language (DML) statements are used for managing data within schema objects. The operation included in these sections are:
  • SELECT
  • UPDATE
  • INSERT
  • DELETE
• DDL - Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
  • CREATE
  • ALTER
  • DROP
  • TRUNCATE

MySQL Useful tips

Below you can find some tricky and very useful examples. Some of them can be solved in minutes other could require hours of thinking. All this examples are based on my experience with DB - more than 15 years since the first time when I heard RDBMS and why I should learn it.

This list will be frequently changed and updated. If you have any recommendations or suggestions please do share them.

MySQL General

• MySQL vs MongoDB or SQL vs NoSQL
• MySQL 5.7 vs MySQL 8 Do you need to upgrade to MySQL 8
• MySQL SQL Loader
• Java 8 connect to MySQL and do select/insert
• Python import json in MySQL
• MySQL export an ER diagram
• Remote connection to MySQL
• Rename MySQL schema
• MySQL Back up
• SQL prepare for SQL interview

MySQL Install and uninstall

• MySQL install uninstall or reinstall on Ubuntu 16
• MySQL 8 - How to install on Ubuntu 16

MySQL Administration

• MySQL reset user password
• MySQL create alter and drop table
• MySQL update insert delete

MySQL select

• MySQL select max and min length per column
• MySQL select N max values per group
• MySQL joins explained for beginners
• MySQL select max and return many values
• MySQL find the highest and lowest value
• MySQL count null and not null values
• MySQL query table columns
• MySQL transpose/pivot or convert column to row
• MySQL group by and string concatenation
• MySQL coalesce, exists, union and having

MySQL update

• MySQL update with embedded select
• MySQL update with embedded select from several tables