DEV Community

Akshay Khot
Akshay Khot

Posted on • Originally published at akshaykhot.com

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?

Database System Concepts

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
Enter fullscreen mode Exit fullscreen mode

Start / Stop MySQL server

mysql.server start
mysql.server stop
Enter fullscreen mode Exit fullscreen mode

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

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

List all users

select host, user from mysql.user;
Enter fullscreen mode Exit fullscreen mode

Create a new user with password

create user 'akshay'@'localhost' identified by 'password';
Enter fullscreen mode Exit fullscreen mode

Grant all privileges to the new user

grant all privileges on *.* to 'akshay'@'localhost';
flush privileges;
Enter fullscreen mode Exit fullscreen mode

List all databases

show databases;
Enter fullscreen mode Exit fullscreen mode

Show currently selected database

select database();
Enter fullscreen mode Exit fullscreen mode

Start using a database

use db_name;
Enter fullscreen mode Exit fullscreen mode

Show all tables in the database

show tables;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Show the table columns and types

describe table_name; OR
desc table_name; 
Enter fullscreen mode Exit fullscreen mode

Drop table

drop table table_name;
Enter fullscreen mode Exit fullscreen mode

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

show create database app_db;
show create table contacts;
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (0)