SQL

Structured Query Language is a standard to access databases. See https://www.w3schools.com/sql/default.asp

There are 2 major GPL implementations:

  1. Mysql that is client server based and suitable for networks and multi users

  2. SQLite that is targeted for embedded applications, easy to go and has all database in a single file. It is widely used, since android makes use of it.

SQLite

SQLite http://www.sqlite.org/is ready to be used and needs no administrative server/client configurations. It is ready to use (the 3 is important otherwise the incompatible version 2 is used):

sqlite3 my.db

then the sqlite> prompt pops up and commands can be entered to create the table adr:

CREATE TABLE adr(first VARCHAR(20), family VARCHAR(20));

INSERT INTO adr VALUES('Urs','Lindegger');

SELECT * FROM adr; the * stands for the column names, and means all columns, alternatively a list of column names separated by , can be applied

If some column got forgotten add it with

ALTER TABLE adr ADD COLUMN email VARCHAR(20);

SQLite is not case sensitive and follows human readable syntax, however to distinguish what is SQL and what are user terms upper and lowercase is commonly used to style the commands.

.exit, .quit .q or Ctrl + D exits and the file my.db is left behind containing the SQLite database.

Alternatively it can be worked without entering the interactive prompt by adding the command to the command line:

sqlite3 my.db ".dump" will dump the database to the screen. or to be more efficient, type all commands in a file and run .tables will show all tables in the database

Note

SQLite understands additional commands that have names starting with a dot and written in lowercase as .tables

sqlite3 my.db < mysqlcommands.sql

Many bindings to programming language exist as c and python (where it is even part of the standard library. pysqlite is an alternative).

There are also gui's as sqliteman, sqlitebrowser or just use gda-browser the database browser that might come with gnome (emerge libgda).

SQLite has triggers that can be configured to create events when something gets modified.

MySQL

MySQL is the GPL SQL database that is used by many applications. It is a key component in LAMP web severs. In fact it is just a program that can deal with numerous databases. Every database can have tables.

You need to login to the sql server, then you can select the database among the databases to be used.

MySQL can be operated via console. First start the sql server:

/etc/init.d/mysql start

Login as guest (press enter to not give a password)

mysql -u guest -h localhost -p

Or as root (press enter to not give a password, note that you can do this also without being Linux root)

mysql -u root -h localhost -p

Since this looks scary give it a password:

/usr/bin/mysqladmin -u root -h localhost password 'new-password'

Some commands

SHOW DATABASES;

CREATE DATABASE <my database>;

USE <my database>;

SHOW TABLES;

CREATE TABLE <name of table> ( <name of 1 column> VARCHAR(128), <name of 2 column> VARCHAR(128), <name of 3 coumn> VARCHAR(128));

DESCRIBE <name of table>;

and finally quit to get out

To get a gui application emerge mysqlnavigator

To get a web application that runs in a web browser: emerge phpmyadmin to have a gui. Read the post install instructions, how to create the config file:

mkdir /var/www/localhost/htdocs/phpmyadmin/config

chown apache:apache /var/www/localhost/htdocs/phpmyadmin/config

then go to http://localhost//phpmyadmin/scripts/setup.php

cp /var/www/localhost/htdocs/phpmyadmin/config/config.inc.php /var/www/localhost/htdocs/phpmyadmin/config.inc.php

rm -rf /var/www/localhost/htdocs/phpmyadmin/config

To work with it use: http://localhost/phpmyadmin/index.php

See https://wiki.gentoo.org/wiki/MySQL/Startup_Guide.

The config files are in /etc/mysql per default the data is in /var/lib/mysql.

To export to do a backup

mysqldump db_name > backup-file.sql

or for backups on the same machine use the faster way:

mysqlhotcopy


Linurs Servernest startpage