I like to use sqlite in most of my projects that require simple/small databases. It has a C/C++ interface library, very simple to use (because no server required), contained in a single file and even available on Android! So, here are my notes on using them… mainly for my own personal reference.

Main documentation can be found here.

Most SQL commands in SQLite are similar to other SQL found in major database software (e.g. MariaDB, PostgreSQL, MySQL). So, if you have done SQL before, this should be very familiar.

To create a new table:

CREATE TABLE IF NOT EXISTS tablename (col-name col-TYPE [,col-name col-TYPE]);

Notice that IF NOT EXISTS is optional.

to be continued…

Assuming we want to modify rows (with column col that contains decimal points) in table tablename:

UPDATE tablename SET col=substr(col,1,LENGTH(col)-3) WHERE col LIKE '%.00';

Selecting specific column (operations) within specific time range:

SELECT col1,(col2*2.0),datetime(col3,'8 hours') FROM table1
  WHERE id=1 AND datetime(col3,'8 hours')<datetime('2016-10-15T23:59:59');

One thing to note is that there is no database concept - a file IS a database. But we can attach another database for simultaneous access.

First open a DB file:

sqlite3 'file1.sqlite'

Within sqlite, this file will be known as the main database. There is another system database called temp (so you should not use this name as an alias for the other database).

So, to access another database:

ATTACH 'file2.sqlite' AS NEXT;

You can now access any table like:

SELECT * FROM NEXT.tablename;

To copy from/to the same table (exactly same schema) between both database:

INSERT INTO main.tablename SELECT * FROM NEXT.tablename;

Or to copy selective columns:

INSERT INTO main.tablename(column1, column2) SELECT (column3, column4) FROM NEXT.tablename;

start installation

# mysql_install_db --user=mysql

if forgot to supply user, make sure permission for /var/lib/msql is given to user mysql

# chown -R mysql:mysql /var/lib/mysql

start daemon

# sh /etc/rc.d/rc.mysqld start

as the name implies, run

# /usr/bin/mysql_secure_installation

create database

$ mysql -u root -p

mysql> CREATE DATABASE app_db;
mysql> GRANT ALL privileges ON app_db.* TO 'user_app'@'localhost' IDENTIFIED BY 'pass_app';
mysql> SET password FOR 'user_app'@'%' = password('pass_app');
mysql> FLUSH privileges;

note not sure if i need that set password line… will test that

recover root password

$ /etc/rc.d/rc.mysqld stop
$ mysqld_safe --skip-grant-tables &
$ mysql -u root

mysql> USE mysql;
mysql> UPDATE USER SET password=PASSWORD('<newpass>') WHERE USER='root';
mysql> FLUSH privileges;
mysql> exit

show permission / grants

$ mysql -u root -p -e "show grants for 'user_app'@'localhost';"

delete database

$ mysql -u root -p -e "drop database app_db;"

dumping this here for now…. not sure why i have this in my note

select db,user,select_priv from mysql.db where db='my1vmsdb' \g