MySQL is one of the most popular database systems and many cloud providers offer it as part of their service. It uses commands with queries and data to perform tasks and functions by communicating with the database. This article covers the most commonly used MySQL statements that allow users to efficiently work with databases.
Using the MySQL Client
MySQL lets you connect to a server using a client like the command-line tool, mysql. Use the -u and -p flags to provide your username and password:
When you’re finished, exit the MySQL command-line client as follows:
Working With User Accounts
To create a new user account, open the new terminal to access MySQL as the root and create a new user as follows:
You can also set up a user account with restricted access by specifying a host that they must access the database from:
To specify a remote connection, you can replace the ‘localhost’ with the machine’s IP address as follows:
Lastly, you can delete an account with the following statement:
User Account Privileges
Before moving on, you’ll need to set the appropriate permissions on the new user account. This avoids the risk of unnecessary user access within the database.
You can work with user privileges in MySQL using statements such as GRANT, REVOKE, and ALTER. Depending on the actions you want a user to be able to carry out, you can assign all or some permissions. These permissions are ALL PRIVILEGES, SELECT, UPDATE, INSERT, DELETE, CREATE, DROP, AND GRANT OPTION.
You can assign the administrative privilege of inserting data to all tables belonging to any database:
However, you can also limit user access by specifying the database before the period. You can allow a user to select, insert, and delete data to and from all the tables inside a database as follows:
Similarly, you can restrict user access to a specific table by specifying a table name after the period.
You can grant all permissions to every table inside a specific database as follows:
To revoke permissions of a user from a single database:
You can revoke all user privileges from every database as follows:
Finally, you can set passwords like this:
Note the use of the PASSWORD function which hashes the plaintext password.
Working With Databases
You can create a new database with a name that does not already exist:
You can switch the current database to another that you want to work with:
Lastly, you can delete an entire database along with its tables as follows:
Working With Tables
A table is the main structural element of a MySQL database, grouping a set of related records as rows. Each row has columns with different data types that can be CHAR, VARCHAR, and TEXT among many others.
The general syntax to create a table is as follows:
You can also create a new table from an existing table by selecting specific columns as follows:
You can add data to a table using the following command:
To delete a table, use the DROP TABLE statement as follows:
Or you keep the table but delete all its data using:
Accessing Databases
Use the following statement to show all the available databases inside the MySQL DMS:
Similarly, you can list all tables in the current database:
To view all columns inside a table:
To display column information inside a table:
Querying Databases
MySQL allows you to use a SELECT statement to query data from the database. You can use various MySQL clauses to extend its base functionality.
The following statement returns a result set consisting of two columns from every row in a table:
Or display all columns as follows:
You can also query databases/tables and retrieve information using conditions as follows:
The SELECT statement also allows you to group the result set by one or more columns using the GROUP BY clause. You can then use aggregate functions to calculate summary data:
Updating Tables
You can modify data inside the table by using the UPDATE or ALTER statements. The UPDATE statement allows you to update existing single or multiple records/rows.
The following MySQL command changes the UserName and City of a single record where the UserID is 2:
While this example updates all UserNames for all records where the City is Munich:
You can add a column to a table like this:
To remove a column from the table, use the ALTER TABLE statement as follows:
MySQL for Beginners
In this article, you’ve seen the most common MySQL commands. They enable you to manage user accounts, change the structure of databases, and manipulate data.
Once you’re comfortable with the basics, it’s useful to learn about MySQL and security. Your database might hold valuable and sensitive personal data, so keeping it safe from prying eyes is vital.
