Don't be afraid of using your chosen database's command-line client.I might as well say this up front: I don't like using GUI (aka non-command-line or graphical) tools with my databases. This is likely because when I first learned it was with command-line tools, but even so, I think command-line database tools often are the best way to interact with a database manually.
Two of the most popular databases in use on Linux are MySQL and PostgreSQL. Each of them have very useful, if slightly different, command-line clients. If you ever need to move between these two databases, or if you're new to databases in general, a comparison of the two is helpful.
But, because a two-horse race isn't as thrilling as a three-horse one, I wanted to include a third database command-line client in my comparison. I chose SQLite, because it is arguably the most popular database in the world. You probably have several SQLite databases on your local computer right now. The command-line client is nice too.
Also, I use MariaDB instead of MySQL in my examples, because that's what I have installed, and because I like the improvements MariaDB includes in both the command-line client and in the database server. MariaDB and MySQL are very compatible, and my examples are simple, so whenever I mention MariaDB, you can assume it applies to MySQL as well.
To Serve...or NotPostgreSQL and MariaDB have what is known as a client/server architecture. Clients connect to the server, and although client and server often are installed together and you may think of them as a single entity, they actually are not. The client does not need to be run on the same machine as the server. The MariaDB server is called mysqld, and it always is running while the server is up. Likewise, the PostgreSQL server is called postgres.
SQLite does not have a client/server architecture. There is just the database you are using, which is a local file, and client programs, which can interact with it.
InstallationI won't go into how to install MariaDB, MySQL, PostgreSQL or SQLite3 here. Most distributions have packages for them, and in the case of MariaDB, there are packages for Debian, Ubuntu, Red Hat and a generic Linux binary available from its download page. See the documentation for each and your distribution's documentation for instructions.
On Ubuntu, you can install all three with the following:
sudo apt-get install mariadb-server postgresql sqlite3
Other Linux distributions are just as easy for the most part. (You need to have added the appropriate MariaDB Ubuntu repository for the above to work. Instructions are on the MariaDB downloads page.)
Figure 1. The MariaDB, PostgreSQL and SQLite3 Clients in Action
Basic Client CommandsThe client programs for MariaDB, PostgreSQL and SQLite3 are mysql, psql and sqlite3, respectively. I've listed several useful commands for each client in Table 1. The first entry shows the basic command used to connect to a database; however, each client has several options. These include (in the case of MariaDB and PostgreSQL) options for specifying the user, password and database host server. You will need these often, so refer to the man pages for the clients for what they are and how to use them. Some of the commands listed in Table 1 have extended options; refer to the documentation for details.
|Connect to a database|| || || |
|Client help|| || || |
|SQL help|| || ||n/a|
|List databases|| || || |
|Change database|| || ||n/a|
|List tables|| || || |
|Show table info|| || || |
|Load data|| || |