Search Results for: mysql

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

This tutorial covers limiting the number of results from a MySQL database query using the LIMIT clause and skipping results using the OFFSET clause. This is especially useful when wanting to paginate results in web applications – spreading them out over several pages by retrieving a subset of the database results. Examples Examples will use the following table and data: table_people id name 1 Bob 2 Ted 3 Jim 4 Pip 5 Zak 6 Tim LIMIT Here’s how to limit the results to 2 records using LIMIT: SELECT * FROM table_people LIMIT … Read more

There are two things called CASE in MySQL (and, by extension, the compatible MariaDB). The CASE Operator, for use in queries. The CASE Statement, for use in stored programs and procedures. Both have similar syntax and the same purpose. Both MySQL CASE Operator and Statement provide control over the results based on a set of conditions. Examples in this article will use the following test data: table_people id name 1 Bob 2 Ted 3 Jim 4 Pip 5 Zak 6 Tim CASE Operator The CASE operator is used … Read more

There is a lot of weird-sounding language around databases. Most of it centers on the names of the popular database software which is available. It can make it tough to choose. Each strangely named database solution offers features making it suitable for different tasks. This article should shed some light on what’s what. SQL Is a Language SQL (pronounced ESS-QUE-ELL or sequel depending on which side of the raging debate you side with). It stands for Structured Query Language. It is not a piece of software – it’s the … Read more

Knowing which version of MySQL you are running is vital when making sure your code is compatible and that you are using supported features. Here’s how to find out which you are running. For example, older versions of MySQL lack features for handling JSON data and modern character sets (including emojis!), so targeting the right MySQL version or making sure your MySQL version is up to date is pretty important if you want to support these popular functions! MariaDB is a drop-in replacement for MySQL, so … Read more

MySQL SELECT queries are commands that pull data from your database tables according to conditions each record must meet. Complex queries will often need to combine the results from two or more SELECT queries – this is what the UNION operator does. UNION Syntax SELECT column [, column2, column3…] FROM first_table UNION SELECT column [, column2, column3…] FROM second_table; Note that: Each SELECT statement must have the same number of columns in its results Data type in each of the columns must match The columns … Read more

In this guide, we cover foreign key constraints in MySQL and MariaDB, along with a number of useful examples. MySQL (and its fork MariaDB) are Relational Database Management Systems (RDBMS) – Database Systems which hold data in tables which can be related to each other. Tables in an RDBMS are organized into columns that contain data in rows (also called tuples). Each row will usually have a Primary Key – a unique value to identify the row in the table. To define relationships between two rows in two different … Read more