MySQL / SQLite

The MySQL shell client

Assuming that the MySQL installation directory's bin subdiretory in your PATH, you can use the mysql executable to connect to and deliver SQL commands to your MySQL database via a command-line SQL interpreter.

The MySQL adminstrator's user name is "root". The MySQL DBMS contains an adminstrative database named mysql in which all access information is stored. The root user has global priviledges to do any modifications or additions.

We only ever want to use the administrative database, mysql, for it's intended purpose, to control access to other databases. Furthermore, we usually want to avoid accessing the MySQL DBMS as root user because there is always the remote possibility that we inadvertently alter the tables in the mysql database and thereby foul up the DBMS — not every author shares my reticence of being MySQL root user.

To run some basic tests it's useful to have an unpriviledged, password-less, "guest" user which can access a test database. On other systems, the MySQL initialization may create the test database which can be accessed by any MySQL user. An anonymous user may also often created.

The following sequence of commands are meant to create some basic setup features and show the effects (highlighted ones most important): There are a number of repeated commands which can easily be accessed by using the "up arrow" key.
mysql -u root              (assuming this gets you into the root user)
mysql> show databases;
mysql> create database test;                         (it may already exist)
mysql> show databases;
mysql> select user,host,password from mysql.user;
mysql> create user guest@localhost;                  (it may already exist)
mysql> select user,host,password from mysql.user;
mysql> select user,host from mysql.db;
mysql> grant all on test.* to guest@localhost;
mysql> select user,host from mysql.db;
mysql> quit
Test the effectiveness by accessing the test database as the guest user:
mysql -u guest test -p
Password: ENTER
Compare the differences in priviledges between the root and guest users (the \G statement terminator is used to field information in list form instead of tabular form):
mysql -u root mysql
mysql> select * from user where user='root' and host='localhost' \G
mysql> select * from user where user='guest' and host='localhost' \G

SQL syntax learning examples

Here are some examples you can use to help learn basic SQL commands. The test database accessible by the guest user without password is assumed. Start a command shell and, from the command-line, execute:
mysql -u guest -p      (you're into MySQL, but not any database)             
mysql> use test;
mysql> create table things (item varchar(10), qty int);
mysql> show tables;
mysql> describe things;
mysql> insert into things values ( 'book', 10 );
mysql> insert into things values ( 'pencil', 4 ), ( 'book', 5 );
mysql> select * from things;
mysql> delete from things where item='book';
mysql> select * from things;
mysql> insert into things values  ('table', 2), ('chair',12);
mysql> select * from things;
mysql> update things set qty=qty-1 where qty>10;
mysql> select * from things;
mysql> update things set qty=qty+1 where item='table';
mysql> select * from things;
mysql> drop table things;
mysql> quit

SQLite command-line client

The SQLite equivalent of the mysql command-line client is sqlite3. This is readily available for Windows, MAC or Linux.

For Windows. You can download and install it from the SQLite site:
http://www.sqlite.org/
From the downloads page, look for a recent archive (here, from the Comp. Sci. server):
https://sqlite.org/2017/sqlite-tools-win32-x86-3160200.zip
This extracts as the directory:
sqlite-tools-win32-x86-3160200
in which you can find the executable sqlite3.exe. Unlike the mysql client program which accesses the MySQL database wherever it is called, it is easiest to be "right next" to the SQLite database file when sqlite3 is used. Therefore, to make effective use of this executable, you need to install this into a folder which is accessible to the system PATH. I would recommend making a dedicated folder for such binaries:
C:\bin
and moving sqlite3.exe into this folder. Then add the folder C:\bin into the PATH. From the Control Panel, select
System ⇾ Advanced system settings ⇾ Environment variables.
Edit for Path in the System Variables or User Variables list. Append this text to the end:
;C:\bin
or prepend this to the beginning:
C:\bin;
For MAC OSX, the executable sqlite3 may already be on your system. If not, there is an executable which can be downloaded from the SQLite site.

For Ubuntu Linux, install it as:
$ sudo apt-get install sqlite3

Client usage

The sqlite3 client behaves much like mysql. The most obvious difference is the meta-commands (show tables, describe, etc.), which are achieved through commands starting with a period. Try this, creating the database file sample.sqlite:
sqlite3 sample.sqlite
sqlite> .help
sqlite> create table things (item varchar(10), qty integer);
sqlite> .schema things
sqlite> insert into things values ( 'pencil', 4 ), ( 'book', 5 );
sqlite> select * from things;
sqlite> .headers on
sqlite> select * from things;
sqlite> .quit
A very useful feature is working with efficient in-memory databases. For example, try this:
sqlite3
sqlite> create table things (item varchar(10), qty integer);
sqlite> insert into things values ( 'pencil', 4 ), ( 'book', 5 );
sqlite> .save sample1.sqlite
sqlite> .quit

GUI Tools

The variety of free MySQL GUI tools depends on the operating system, with Windows coming out on top.

MySQL Workbench

MySQL itself offers a multi-platform GUI tool
MySQL Workbench
Linux distributions most likely will have this available as a package for installation. For Ubuntu,
$ sudo apt-get install mysql-workbench

PhpMyadmin

This already comes with the XAMPP software. If you have a web server (preferably Apache) which can access the MySQL, a good, multi-platform choice is
phpMyAdmin

Navicat

The Navicat software is excellent and is available for multiple platforms and multiple DBMSs. Unfortunately, it is not free in its current incarnation. Prior to 2011, a free stripped-down version called Navicat Lite was provided as an inducement to buy the full version. This frozen-in-time version is still free and available from various download sites and still suits most of our needs.

Download Navicat Lite from the course web site:
Windows: navicat100_lite_en.exe
MAC: navicat100_lite_en.dmg
Linux: navicat100_lite_en.tar.gz
The Windows and MAC installations are straightforward. On Linux you will need to have the wine package installed. Any installation location is suitable, but I tend to favor the root-owned /usr/local directory. Extract as follows (assuming the download file is in the standard Linux location):
$ cd ~/Downloads
$ sudo tar xvozf navicat100_lite_en.tar.gz -C /usr/local
The executable is
/usr/local/navicat100_lite_en/start_navicat
A good idea is to make a "desktop launcher" out of this — the procedure to do so depends on your Linux distribution. No icon is provided so you can use this one (navicat.png):

Navicat MySQL usage

After installation, start it up and create a root connection to your MySQL installation. On MAC OX X, you have to initially open it through the Applications folder by right-clicking. Thereafter you can open it normally.

Press the Connection button.
  1. Select MySQL from the choices.
  2. Set the Connection Name to root. Everything else should be in place already. If your password is not empty, enter it into the password field.
  3. Click the Test Connection button to double-check.
  4. Make sure the Save Password checkbox is checked.
Double-click on the root connection on the left-hand side to open it and see the databases (mysql, perhaps test, etc.).

SQLite GUI Tools

Navicat Lite supports SQLite as well as MySQL, so you may be able to use this as well, although I have experienced this to not work on some Linux systems.

Firefox has an SQLite Manager Add-On which can be used as GUI Tool for SQLite. One installed, Tools ⇾ SQLite Manager, when opened is an independent software tool. You simply have to connect use the Database ⇾ Connect Database and navigate to the SQLite database file. Once connected, you can use the Browse and Search tab for a selected table name to view the contents.

MySQL Discussion points

MySQL Command-line administration

MySQL software provides a number of useful commands to manipulate its databases, including: For example, try these commands:
$ mysqlshow -u root
$ mysqlshow -u root mysql
$ mysqlshow -u root mysql user
$ mysqldump -u root mysql user
To ensure that changes made to the mysql database are picked up, do one of this::
$ mysqladmin -u root reload
There's also the "refresh" command which has a somewhat different outcome.

Backup and reload

The mysql commands provide an excellent scheme by which a database can be "backed up" and then "reloaded". Do the backup like this:
$ mysqldump -u root somedb > somedb.sql
The somedb.sql file contains the data in all the tables plus the commands needed to recreate these tables. A restoration from an non-existent database would be done like this:
$ mysql -u root 
mysql> create somedb
mysql> use somedb
mysql> source somedb.sql

MySQL access principles

MySQL is a network-oriented DBMS. Client programs may reside on different hosts than the server. The access rights of MySQL client has to a MySQL DBMS database is determined at the beginning by three factors managed by the administrative mysql database: These tables are consulted to determine access rights:
  1. user table: When the client on host connects to the MySQL server, the pair
    (user, host)
    
    is matched against the (user,host) values in the rows of the user table. If no such user or host exists, an empty is used to match. If the password field is non-empty the client must provide the password. The client obtains global privileges from the remaining fields in the record.
  2. db table: Each database, with user access information is listed in the db table.
    (user, database)
    
    is used to match against the entries in the db table, and therefore further augment the privileges available to the client as this particular user.
Of these tables, the db table is the most common place where privileges are assigned for non-root users since it is the most specific to the database.

Further refinements, which we will omit the discuss of these access privileges, governed by dedicated tables in the mysql database: A few tests exhibit some of the ideas discussed here:
$ mysql -u root mysql
mysql> describe user;
mysql> select user,host,password from user;
mysql> describe host;
mysql> select host,db from host;
mysql> describe db;
mysql> select user,db from db;
mysql> select * from db where db='test' \G          (mostly 'Y')
The root user has all global privileges and the anonymous user has none. Nevertheless, the anonymous user gains privileges for the test database by matching an entry in the db table.


© Robert M. Kline