MySQL is an open-source relational database management system with support for Structured Query Language (SQL). It helps in the development of a wide range of web-based applications and online content. MySQL runs on all OS platforms like Linux/UNIX, macOS, Windows and is an important component of the LAMP stack.
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:
mysql -u [username] -p
mysql -u [username] -p [database]
When you’re finished, exit the MySQL command-line client as follows:
exitWorking 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:
$ sudo mysql -u root -p
...
mysql> CREATE USER 'username' IDENTIFIED BY 'password';
You can also set up a user account with restricted access by specifying a host that they must access the database from:
CREATE USER 'user'@'localhost';
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';To specify a remote connection, you can replace the ‘localhost’ with the machine’s IP address as follows:
CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';Lastly, you can delete an account with the following statement:
DROP USER 'user'@'ip_address' IDENTIFIED BY 'password';
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:
GRANT INSERT ON *.* TO 'username'@'ip_address';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:
GRANT SELECT, INSERT, DELETE ON database.* TO 'user'@'ip_address' IDENTIFIED BY 'password';Similarly, you can restrict user access to a specific table by specifying a table name after the period.
GRANT SELECT, INSERT, DELETE ON database.table_name TO 'user'@'ip_address' IDENTIFIED BY 'password';You can grant all permissions to every table inside a specific database as follows:
GRANT ALL PRIVILEGES ON database.* TO 'user'@'ip_address' IDENTIFIED BY 'password';To revoke permissions of a user from a single database:
REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'ip_address'; You can revoke all user privileges from every database as follows:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'ip_address'; Finally, you can set passwords like this:
SET PASSWORD FOR 'user'@'ip_address' = PASSWORD('new_password');
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:
CREATE DATABASE database_name;
You can switch the current database to another that you want to work with:
USE database_name;
Lastly, you can delete an entire database along with its tables as follows:
DROP DATABASE database_name;
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:
CREATE TABLE table_name (column_1 data_type1, column_2 data_type2);
You can also create a new table from an existing table by selecting specific columns as follows:
CREATE TABLE new_table_name AS SELECT column_1, column_2 FROM existing_table_name;
You can add data to a table using the following command:
INSERT INTO table_name (column_1, column_2) VALUES (value_1, value_2);
To delete a table, use the DROP TABLE statement as follows:
DROP TABLE table_name;
Or you keep the table but delete all its data using:
TRUNCATE TABLE table_name;
Accessing Databases
Use the following statement to show all the available databases inside the MySQL DMS:
SHOW DATABASES;
Similarly, you can list all tables in the current database:
SHOW TABLES;To view all columns inside a table:
DESCRIBE table_name;
To display column information inside a table:
DESCRIBE table_name column_name;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:
SELECT column1, column2 FROM table_name;
Or display all columns as follows:
SELECT * FROM table_name;
You can also query databases/tables and retrieve information using conditions as follows:
SELECT column1, column2 FROM table_name WHERE condition;
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:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;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:
UPDATE Users SET UserName = 'Alfred James', City= 'Munich' WHERE UserID = 2;
While this example updates all UserNames for all records where the City is Munich:
UPDATE Users SET UserName='Juan' WHERE City='Munich';
You can add a column to a table like this:
ALTER TABLE table_name ADD COLUMN column_name;
To remove a column from the table, use the ALTER TABLE statement as follows:
ALTER TABLE table_name DROP COLUMN column_name; 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.
