Getting Started with MySQL

After working for almost five years as a full-stack developer, I recently realized that my database skills are nowhere where they should be. Sure, I can create tables and use some basic insert, select, update, and delete statements to manipulate data, but anything complicated than that, I need help from more senior developers.

Another lesson I’ve learned after changing multiple jobs, programming languages, and frameworks is that no matter where I work, no matter which language or framework, the web application needs to work with a database. Relational databases have been around for 50 years and will be there for many more. So as a developer, I must know how to work with a database.

So this week, I decided that I needed to get better at databases and bought Database System Concepts. At $71.70, it’s quite expensive, but I think it’s worth it. I will be using relational databases throughout the rest of my career, so why not make this one-time investment in a good book?

The next question is, which database to use? After leaving .NET for Rails, I am not going back to SQL Server. Oracle? Not in my dreams. So the only two remaining options are Postgresql and MySQL.

I chose MySQL, as that’s the database I will be using at my new job. Also, even though it’s not getting much love and attention that Postgresql is getting, many prominent names in the Rails community such as Basecamp, GitHub, Shopify still use MySQL and seem pretty happy with it. So MySQL it is.

With that decided, I spent some time downloading and installing the community edition of MySQL on a MacBook. Compared to my previous experiences with Postgresql and SQL Server, getting up and running with MySQL was very simple. Here’re some of the commands you need when getting started with MySQL (I will try to keep the post updated as I learn more).

---

Install MySQL

brew install mysql

Start / Stop MySQL server

mysql.server start
mysql.server stop

Start MyCLI

MyCLI is an excellent command-line interface tool for MySQL. I haven't used a GUI tool since I switched to MyCLI. It's plenty fast, and also provides neat features like syntax highlighting and auto-completion.

mycli -u root -h localhost
mysql -u akshay -p (if you have created a user with a password)
mycli

List all users

select host, user from mysql.user;

Create a new user with password

create user 'akshay'@'localhost' identified by 'password';

Grant all privileges to the new user

grant all privileges on *.* to 'akshay'@'localhost';
flush privileges;

List all databases

show databases;

Show currently selected database

select database();

Start using a database

use db_name;

Show all tables in the database

show tables;

Show the complete information

MySQL akshay@localhost:gregs_list> status;
--------------
mycli 1.24.2, running on CPython 3.9.9

+----------------------+---------------------------+
| Connection id:       | 32                        |
| Current database:    | gregs_list                |
| Current user:        | akshay@localhost          |
| Current pager:       | less                      |
| Server version:      | 8.0.27 Homebrew           |
| Protocol version:    | 10                        |
| Connection:          | Localhost via UNIX socket |
| Server characterset: | utf8mb4                   |
| Db characterset:     | utf8mb4                   |
| Client characterset: | utf8mb3                   |
| Conn. characterset:  | utf8mb3                   |
| UNIX socket:         | /tmp/mysql.sock           |
| Uptime:              | 24 min 18 sec             |
+----------------------+---------------------------+

Connections: 1  Queries: 110  Slow queries: 0  Opens: 305  Flush tables: 3  Open tables: 226  Queries per second avg: 0.075
--------------
Time: 0.036s

Show the table columns and types

describe table_name; OR
desc table_name; 

Drop table

drop table table_name;

Show the SQL statement used to create the database or the table

show create database app_db;
show create table contacts;

That's all I've learned so far. Hope that helps. As I learn more about MySQL, I will update the post with more commands and post new articles. Let me know in the comments if you found any mistakes or know better ways to do something than what I list above.