MySQL is a free database solution that you can use in private or enterprise applications. MySQL is open-source, so it’s widely adopted by developers around the world. If you plan to work with databases or developer for web-based applications, you will need to know how to work with MySQL.
MySQL Command Basics
MySQL is mostly controlled through command line input. You can also download a GUI application that lets you work with MySQL, which makes the process of getting a list of users easier. You can get a list of users through the GUI or the command line.
You need elevated rights to get a list of users. You can also group users, sort them and get the user list with the corresponding host information. The only field you can’t see is the password field. MySQL encrypts the password. You can, however, change the user’s password if you need to. For instance, if you have a user that has access to a database and that user leaves the company without giving you the password, you can reset the password to gain access to the database.
The following is the basic MySQL command to get a list of users:
SELECT User FROM mysql.user;
When you run this statement, MySQL returns a list of users stored in the database’s user table. The “User” parameter is the name of the field that displays from the “mysql.user” table.
If you have several users in your database tables, this will list all users in an unordered list. If you need to find one user from hundreds, it would be difficult to sift through all of the records. The following code sorts the list of users in alphabetical order:
SELECT User FROM mysql.user ORDER BY User;
You can use the “ORDER BY” clause with any field to order your list. You can also sort the list in reverse alphabetical order. The following MySQL statement orders the list in descending order:
SELECT User FROM mysql.user ORDER BY User DESC;
You might also want a list of users with their corresponding host name. This is beneficial when you have hundreds of users from several different hosts. For instance, if you host a MySQL database for a hosting company, there could be thousands of MySQL users on the database. The following MySQL command lists the user names and the host information as well:
SELECT User, Host FROM mysql.user ORDER BY User;
You can also make a list easier to read by concatenating the host and the user. Essentially, the user and the host name are the user’s login credentials. For example, you might need to make a list of users and their host names in a readable spreadsheet instead of simple output. The following code gives you the user, the host and then concatenates a string comprised of the user name and the host:
SELECT User, Host, CONCAT(QUOTE(user),’@’,QUOTE(host)) FROM mysql.user ORDER BY User;
Using the MySQL Workbench GUI
The MySQL Workbench is a configurable graphical interface you can download from free and install it on your desktop. You can connect to your MySQL database and work with many of the command line functions in an interface that’s more intuitive than remembering command line statements.
After you’ve downloaded and installed the software on your desktop, configure the application to connect to your MySQL database. It’s best to connect to the database with the root level account, because you can freely view users and edit them if you need to.
With the MySQL database opened, right-click the User’s table. A list of options displays in a pop-up. Click the option labeled “Select Rows – Limit 1000.” A list of users is displayed in the Workbench. Notice at the top of the Workbench, notice the MySQL statement is automatically generated. You can edit this statement to change the way the records display. For instance, add the “ORDER BY User” clause at the end of the statement and click the “Run” button. The list is graphically displayed and sorted using the new MySQL statement.
MySQL is a powerful database engine, and knowing these simple statements helps you learn the engine and become more advanced. If you ever plan to run a website, it will benefit you to learn MySQL.