| Export data | SELECT ... INTO OUTFILE '' | \o | .dump
| Exit the client | quit (or exit) | \q | .exit |
The first time you connect to a newly installed MariaDB or PostgreSQL database, you need to connect as the database superuser because you likely have not set up any other users.
To launch a freshly installed MariaDB mysql client, do the following:
mysql -u root -p
You will be prompted for the password you entered during the package install process.
To launch a freshly installed PostgreSQL psql client, do the following:
sudo su - postgres
psql
Creating and Deleting a DatabaseJust installing database clients and/or servers does not automatically give you a database to work with. For MariaDB and PostgreSQL, a database can be created either with the client or with an external utility.
In MariaDB and PostgreSQL, to create a database called library, the command is:
CREATE DATABASE library;
To connect to this newly created database in MariaDB, do:
USE library
In PostgreSQL, do:
\c library
To delete the newly created library database, drop it with:
DROP DATABASE library;
I shouldn't have to say this, but be careful with the above command. If you just dropped the library database, create it again. You'll need it later to follow along with the examples in this article.
In SQLite3, there is no database server, and databases are just regular files, often with a .db extension. To create a database, name it on the command line when you launch the client, and if it doesn't exist, the client will create it, like so:
sqlite3 library.db
To remove an SQLite3 database, just remove it like you would any other file (with rm or via your file manager).
Managing Users and PermissionsThere isn't space to go into the details of how to create and manage the permissions of database users here. Refer to the MariaDB and PostgreSQL documentation for details. I will continue to use the default superuser accounts for the examples here.
There is no internal database user or user permissions management with SQLite3. If local users have write access to the database file, they can do anything they want.
Common SQL OperationsThis article is about the command-line clients for MariaDB, PostgreSQL and SQLite, but one of the main things you do when using such clients is write SQL statements. So let's look at some of the basic SQL-related similarities and differences between the three.
The most common SQL statements are selects, inserts, updates and deletes. As a computer language, SQL is one of the more popular ones, and there is an official standard, ANSI SQL, which has gone through various revisions through the years. Most relational database management systems (RDBMSes) use SQL as their query language, but they differ in how closely they adhere to ANSI SQL. Of the three I'm exploring here, PostgreSQL sticks closest to the standard. MariaDB drifts from the standard in places to make it easier to use. SQLite3 doesn't pretend to support every feature of ANSI SQL. Instead, it supports only a subset. After all, it's supposed to be "Lite".
Some people would like to see SQL die and never be used again. I am not one of those people. SQL has issues, but so do most computer languages. I find SQL easy to read, flexible and well worth the time it takes to learn it. The examples below are simple, and I gloss over a lot of the complexity of SQL. I also don't explain every part of every statement. My goal here is to give you a taste of what SQL looks like in practice and to point out some of the similarities and differences between the three databases. The on-line documentation for each of these databases (and the in-client help for MariaDB and PostgreSQL) includes extensive information on SQL syntax. I found the SQLite syntax diagrams to be especially helpful for that database.
SQL statements can be written on a single line, or they can be broken up across many lines to make it easier to read. In the examples below, I do the latter. SQL statements usually end with a semicolon (;).
The CREATE TABLE StatementYou won't get very far in your database adventures without some tables. If you're not familiar with databases, think of database tables as spreadsheet sheets, without all the fonts and border styles.
Returning to our library example, the most common things in a library are books, so let's create a books table:
CREATE TABLE books (
bookid serial PRIMARY KEY,
title varchar(100) NOT NULL,
seriesid integer,
authorid integer
);
The above works for both MariaDB and PostgreSQL, but it doesn't work for SQLite3, because of the use of the SERIAL datatype, which often is used as the datatype for a PRIMARY KEY. See the "The SERIAL Datatype" sidebar for more information.
The SERIAL DatatypeA datatype is how you tell the database what type of data is in a column. Common datatypes include integer, text, varchar and date. The SERIAL datatype is a special one. In MariaDB, the SERIAL datatype is an alias for the following: Garrick, one line below.
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
That's quite a mouthful, but it does the job of creating a column suitable for use as a PRIMARY KEY. BIGINT is a large integer; UNSIGNED means no negative values; NOT NULL means it can't be empty; AUTO_INCREMENT means that if a specific value is not specified when a row is inserted, the value should be "the current highest value + 1"; and UNIQUE means that no other row in that table is allowed to have the same value in that column.
In PostgreSQL, the SERIAL datatype is an alias for this:
INTEGER NOT NULL DEFAULT nextval('tablename_colname_seq')
The strange nextval('tablename_colname_seq') bit is referring to an "ALTER SEQUENCE", specifically:
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
This is just PostgreSQL's way of creating an auto-incrementing column. Thankfully, when you create a column with type SERIAL, PostgreSQL creates the ALTER SEQUENCE for you. This column also is suitable for use as a PRIMARY KEY.
A common feature of many database tables is a PRIMARY KEY. This key uniquely refers to a single row of a table. The PRIMARY KEY can be a combination of two or more columns in a row (as long as the combination is guaranteed to be unique in that database table), but most commonly, there is a specific, auto-incrementing column that is used as the PRIMARY KEY.
Every row in an SQLite3 table automatically has a PRIMARY KEY column (SQLite calls it the RowID) created when you create the table. However, it is hidden unless you specify a column with a type of integer PRIMARY KEY. So for SQLite, change the bookid line in the CREATE TABLE statement above to this:
bookid integer PRIMARY KEY,
And, SQLite3 will create a table with equivalent settings to MariaDB and PostgreSQL.
The INSERT StatementNow that you have a table, it's time to enter (or INSERT) some information. Inserting data between the three databases is very similar, but there is one important difference. Both MariaDB and PostgreSQL allow you to insert multiple rows of information in one statement. SQLite3, on the other hand, lets you insert only a single row at a time.
For example, to insert some data into the books table you created earlier, use this SQL statement for both MariaDB and PostgreSQL:
INSERT INTO books (title, seriesid, authorid) VALUES
('The Fellowship of the Ring', 1, 1),
('The Two Towers', 1, 1),
('The Return of the King', 1, 1),
('The Sum of All Men', 2, 2),
('Brotherhood of the Wolf', 2, 2),
('Wizardborn', 2, 2),
('The Hobbbit', NULL, 1);
You may have noticed a typo in the last line. I did it on purpose so you would have something to fix later.
For SQLite3, each row that you are inserting needs to be done separately, like so:
INSERT INTO books (title, seriesid, authorid) VALUES
('The Fellowship of the Ring', 1, 1);
INSERT INTO books (title, seriesid, authorid) VALUES
('The Two Towers', 1, 1);
...and so on.
In the SQL statements above, I don't specify the bookid in the column names section. I do this because that column is set up as the PRIMARY KEY, and it is filled automatically by the database with the correct value.
The SELECT StatementSELECT is the most common database operation. The only reason I didn't talk about this first is because until the table was CREATE-ed and had data INSERT-ed into it, as you did in the previous sections, there was nothing to SELECT.
On all three of the databases, SELECT statements work pretty much the same. Basic SELECT statements, such as the following, will work on all three:
SELECT * FROM books;
SELECT title, authorid FROM books WHERE authorid = 1;
SELECT * FROM books ORDER BY authorid;
Joins also work very well across all three. Joins are where you combine information from two or more tables together. For example, here is a join that matches author names to their books based on the authorid number:
SELECT title AS "Book Title", givenname, surname
FROM books INNER JOIN authors USING (authorid)
ORDER BY surname;
The above SELECT statement presupposes the creation of an authors table and the insertion into it of at least a couple rows of data, like so:
On MariaDB and PostgreSQL:
CREATE TABLE authors (
authorid serial PRIMARY KEY,
surname varchar(100),
givenname varchar(100),
birthdate date
);
On SQLite3, change the authorid line to the following, and the CREATE TABLE statement will work properly:
authorid integer PRIMARY KEY,
Here is some data for the table, formatted to work on all three:
INSERT INTO authors (surname, givenname) VALUES
('Tolkien', 'J.R.R.');
INSERT INTO authors (surname, givenname) VALUES
('Farland', 'David');
Now, you can run the SELECT ... JOIN statement.
The UPDATE StatementRemember 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 StatementDeleting 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 StatementSuppose 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.
SQLite OutputWhen 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 statement:
.explain ON
.mode column
.width 30 10 10
The .explain command instructs SQLite to display column headers; .mode sets the output to display in columns, and the .width command 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 .mode list. Doing this also turns off explain and resets the column widths back to their defaults.
Single vs. Double QuotesIn 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: title = '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 SET sql_mode='ANSI_QUOTES'.
ConclusionIt 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.
ResourcesMariaDB 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
|
Figure 1. The MariaDB, PostgreSQL and SQLite3 Clients in Action 
No comments:
Post a Comment