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.
Top comments (0)