Databases

This article will show you how to use the MySQL/MariaDB UPDATE statement to update existing database records. MySQL/MariaDB UPDATE Syntax The syntax for the MySQL UPDATE statement requires the use of two keywords, UPDATE, and SET, and is as follows: UPDATE table SET column = value WHERE conditions; Note that: table is the name of the table which contains the records to be updated column = value defines which column to update and what the new value for that column should be Multiple column/value pairs can be defined, separated by a … Read more

This article will explain and demonstrate the use of aliases in MySQL (and MariaDB). MySQL queries can get pretty gnarly – especially if you’re selecting multiple columns from multiple tables. An alias statement is a great tool for simplifying these queries. An alias is just another name for the column or table in question, which you can use to refer to the column or table by. It’s a nickname that can be used to quickly refer to something complex to save time when writing queries. MySQL Column Alias … Read more

This article will demonstrate the usage of the SQL CHECK Constraint to limit column values, as used in MySQL/MariaDB. The SQL CHECK constraint allows you to define limitations to the value which can appear in a table column. It also allows you to apply constraints to a whole table, letting you restrict a column’s values based on the values of other columns in the table row. This behavior is useful for making sure you are only getting valid data inserted into your database. For example, you might … Read more

This article will show you how to find the default or current MySQL (or MariaDB) port and how to change it. What is a Port? A port is a numerical identifier for a communication endpoint on a computer network. Or, put simpler, it’s a unique number on your computer that points to a specific program that is running so that other programs can connect to it. MySQL/Maria DB, being networked database services, expose themselves to other computers via a configured port, allowing other computers to connect to and … Read more

This article will show you the BEST way to copy a table, with or without the data in it, in MySQL and MariaDB. Copying the Table with All Data in MySql The following code will copy a table including all data using the CREATE TABLE LIKE statement: CREATE TABLE new_table_name LIKE database_name.old_table_name; INSERT new_table_name SELECT * FROM database_name.old_table_name; There are other methods, some single line, but this is probably the best and simplest one. Why did I choose this method? Because it copies the table … Read more

There are one of two things you might be looking for – merging two MySQL tables, or the MERGE TABLE syntax – this article explains both, with examples. Merging Tables in MySQL (Moving Data From One Table Into Another) A note before I get to the code – if you’re moving data from one table to another, you should take into account the contents of the data and ensure that it will still be valid when merged with the second data set. This is particularly important if … Read more

This article will show you, with examples, how to show what database privileges users have in MySQL and MariaDB. List All Users To show the privileges for a user, you need to be able to query the user’s name. Here’s how to generate a list of all users on a MySQL server: SELECT user FROM mysql.user; …and here’s how to list all users, with the host they are allowed to connect from: SELECT user, host FROM mysql.user; Both of the above queries pull information from … Read more

This article shows you how to use the MySQL SHOW INDEX statement to list the details of indexes and keys in a table. To use the SHOW INDEX statement, you will need to know which database and table you wish to view index information for. Looking to list the databases and tables on your system? What Are Indexes? Indexes are a tool that allows a database to quickly lookup data in a table for a certain column. Usually, when searching a table, the database software must read every row in … Read more

The MySQL DISTINCT operator returns only unique values from a column from a database table; duplicates are not returned.  Here’s how to use it. The DISTINCT operator can be useful in many scenarios; for example, you may want to generate a list of countries you have shipped to, or you may want to provide a drop-down menu containing unique product options for the user to select. Example Usage of MySQL DISTINCT Examples will use the following table: table_orders: order_id shipping_country product_category 1 Australia soap 2 China … Read more

MySQL (and its functional equivalent, MariaDB) have two things named TRUNCATE – so watch out which one you want to use or suffer dire consequences! This article outlines the difference and usages of the MySQL TRUNCATE function and TRUNCATE TABLE statement – they share similar names but do very different things. TRUNCATE Function – Truncating a Number to a Certain Number of Decimal Places First, the TRUNCATE function. The TRUNCATE function reduces the number of decimal places for a given number. MySQL TRUNCATE Function Syntax TRUNCATE(number, decimals) Note that: number is a number decimals is an integer … Read more