The MySQL Cheatsheet We All Need

All the essential MySQL commands you always forget

Photo by Safar Safarov on Unsplash

As passionate about data science as I am, I love databases. My first contact with them was in a course in my third year of university, where we learned to create and manage databases with MySQL. I learned a lot, and all that knowledge has been very useful for me these last years. However, SQL syntax can be easy to forget. In order to avoid spending more time googling how to do this or that than actually doing it, I preferred to make a little cheat sheet, which I want to share with you today.

Connect to a MySQL Server

The first step to start working with a MySQL database is to connect to it. From a terminal window, type:

mysql [-h machine] -u <user> -p [db_name]

If the server and the client are running on the same machine, the -h flag is not needed. db_name is also optional, since as we’ll see now, you can create or change the current database with a single command.

Once connected to the server, there are some interesting commands we can run at any moment from the shell:

• \T: prints all the output into a text file
• \s: prints some info about the MySQL server
• \c: removes the current sentence
• \.somefile.sql: runs all the SQL sentences stored into a file
• help: shows the manual pages
• help create table: shows manual page about the “CREATE TABLE” command
• \q finishes connection with the MySQL server

Create a User and a Database

MySQL comes with a default root user, but it should be used just for managing the database, not for data manipulation. That’s why we should create a standard user.

CREATE USER 'mike' IDENTIFIED BY 'difficultpassword'
CREATE USER 'mike'@'localhost' IDENTIFED BY 'difficultpassword'

In this snippet, we’re not creating two users but one which can be used whether the client is inside or outside the server.

Now we’ll see how to create a database and assign different permissions to…