Getting Started with MySQL
Published: 2022-01-23 20:24:35

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](https://www.amazon.ca/Database-System-Concepts-Sixth-Silberschatz/dp/0071289593/ref=monarch_sidesheet). 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?

![Database System Concepts](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/88eraw4arirsdzes778x.jpeg)
 
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**

```bash
brew install mysql
```

**Start / Stop MySQL server**

```mysql
mysql.server start
mysql.server stop
```

**Start MyCLI**

[MyCLI](https://www.mycli.net/) 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/54ac7l99v2wu211100dw.png)

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

**List all users**

```mysql
select host, user from mysql.user;
```

**Create a new user with password**

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

**Grant all privileges to the new user**

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

**List all databases**

```mysql
show databases;
```

**Show currently selected database**

```mysql
select database();
```

**Start using a database**

```mysql
use db_name;
```

**Show all tables in the database**

```mysql
show tables;
```

**Show the complete information**

```mysql
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**

```mysql
describe table_name; OR
desc table_name; 
```

**Drop table**

```mysql
drop table table_name;
```

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

```mysql
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.