MariaDB/MySQL, PostgreSQL and SQLite3 - Comparing Command-Line Interfaces
The UPDATE Statement
Remember that typo? Well, it's time to fix it. This UPDATE line works for all three:
UPDATE books SET title = 'The Hobbit' WHERE title = 'The Hobbbit';
The DELETE Statement
Deleting rows also is the same across all three:
DELETE FROM books WHERE bookid = 7;
The above will delete the row in the books table that has a bookid of 8. If you've been following along, there should not be an entry with that bookid, so nothing will happen.
The ALTER Statement
Suppose I decide to remove the seriesid column from the books table. In MariaDB and PostgreSQL, the following statement will do it:
ALTER TABLE books DROP seriesid;
SQLite3, on the other hand, does not support the removal of columns from tables. You can add columns to a table, or modify columns, but the only way to remove a column is to create a new table without a seriesid column, transfer the data from the old table to the new table, drop the old table, and then rename the new table to the original name. It's not as annoying as you might think, thanks to some SQL INSERT trickery (well, I thought it was tricky the first time I saw it in action). The basic idea is to use the output of a SELECT statement as the input to an INSERT statement, like so:
CREATE TABLE books2 ( bookid integer PRIMARY KEY NOT NULL, title varchar(100) NOT NULL, authorid integer ); INSERT INTO books2 (bookid, title, authorid) SELECT bookid, title, authorid FROM books; DROP TABLE books; ALTER TABLE books2 RENAME TO books;
The above trick also works as written in MariaDB and PostgreSQL as long as you change the bookid line of the CREATE TABLE statement to the following:
bookid serial PRIMARY KEY,
But, that's an awful lot of work if you just want to drop a column from a table.
These examples should be enough SQL to give you a picture of how the three compare to each other.
When trying the SQL examples, you will notice the SQLite output is not
nearly as pretty as the output from MariaDB/MySQL or PostgreSQL. By
default, SQLite doesn't print column names or try to pad columns so that
they line up nice and fancy like the others do. To make SQLite do so for the
SELECT ... JOIN statement, enter the the following commands before the
.explain ON .mode column .width 30 10 10
.explain command instructs SQLite to display column headers;
.mode sets the output to display in columns, and the
sets the width of the columns. The only issue with doing this is that it
will mess up the output of future queries (unless they happen to look
fine with the
.width values you specified). To reset things back to
the default, set the output mode back to the default "list" with
list. Doing this also turns off
explain and resets the column widths
back to their defaults.
Single vs. Double Quotes
In the SQL examples I use single quotes (') for most things and double
quotes (") sparingly. MariaDB and SQLite allow you to use single or double
quotes interchangeably for most quoted text in queries. PostgreSQL is
pickier, because it tries to stay closer to the ANSI SQL standard, which
says single quotes should be used for values (for example:
'The Hobbbit'), and double quotes should be used for system identifiers
(field names, table names and so on—for example:
SELECT title AS "Book
Title"...). You can force MariaDB to obey the standard—and reject
double-quoted values—with the command
It is not hard to interact with databases on the command line. In my opinion, doing the tasks listed above is much easier on the command line than through a graphical database program.
Of course, manipulating your database by hand, whether on the command line or with a graphical program, probably should be avoided in many cases in favor of using an automated front end—for example, a PHP content management front end for the database that contains the content for your company Web site. However, for those times when you do need to dive in and tweak something manually, or for small projects that don't justify the time or expense of a custom front end, there is no need to be afraid of using the command-line client of your chosen database.
MariaDB Web Site: http://mariadb.org
MariaDB Documentation: http://kb.askmonty.org
MariaDB Downloads: http://downloads.askmonty.org
PostgreSQL Web Site: http://www.postgresql.org
PostgreSQL Documentation: http://www.postgresql.org/docs
PostgreSQL Downloads: http://www.postgresql.org/download
SQLite Web Site: http://www.sqlite.org
SQLite Documentation: http://www.sqlite.org/docs.html
SQLite Downloads: http://www.sqlite.org/download.html
SQLite SQL Syntax Diagrams: http://www.sqlite.org/syntaxdiagrams.html
Wikipedia Article on SQL: http://en.wikipedia.org/wiki/SQL
Wikibooks Article on Moving between MySQL and PostgreSQL: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL
Practical books for the most technical people on the planet. Newly available books include:
- Agile Product Development by Ted Schmidt
- Improve Business Processes with an Enterprise Job Scheduler by Mike Diehl
- Finding Your Way: Mapping Your Network to Improve Manageability by Bill Childers
- DIY Commerce Site by Reven Lerner
Plus many more.
- Handheld Emulation: Achievement Unlocked!
- Building a Multisourced Infrastructure Using OpenVPN
- Unikernels, Docker, and Why You Should Care
- Happy GPL Birthday VLC!
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- New Products
- Controversy at the Linux Foundation
- February 2016 Issue of Linux Journal
- Non-Linux FOSS: Snk
- Giving Silos Their Due