SQLite


типы данных

арифметические операции


    +      -      *      /      % 

функции агрегации данных

 
  asb
  round

  sum
  count
  avg
  
  max
  min 

функции работы с текстовыми строками


  lower
  upper
  
  trim
  ltrim
  rtrim

  replace
  substr

временные метки

sqlite> select strftime ('%s', 'now') ;
1527913069
1527913069
1527913069
sqlite> select strftime ('%s%f', 'now') ;
152791307555.402
152791307555.402
152791307555.402
sqlite> select datetime ('now') ;
2018-06-02 04:25:11
2018-06-02 04:25:11
2018-06-02 04:25:11
sqlite> select date ('now') ;
2018-06-02
2018-06-02
2018-06-02
sqlite> select time ('now') ;
04:25:35
04:25:35
04:25:35

schema syntax

CREATE tblname (fieldname type [restriction], ....) ;

DROP tblname ;

команда ALTER TABLE позволяет переименовать таблицу или добавить новые столбцы, но не позволяет переименовать существующие столбцы, удалить столбец или удалить ограничения. костыль:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 type [ NULL | NOT NULL ],
  column2 type [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (col1, col2, ... col_n)
  SELECT col1, col2, ... col_n FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

FOREIGN KEYS constrains

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY (trackartist) REFERENCES artist(artistid)
);

CREATE INDEX trackindex ON track (trackartist);
  

if the application requires a stricter relationship between artist and track, where NULL values are not permitted in the trackartist column, simply add the appropriate "NOT NULL" constraint to the schema

foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately. the application uses a PRAGMA foreign_keys

sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> PRAGMA foreign_keys = OFF;
sqlite> PRAGMA foreign_keys;
0 

usually, the parent key of a foreign key constraint is the primary key of the parent table. if they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint

when SQLite concludes that deleting the row from the parent table would violate the foreign key constraint it returns an error. if these queries cannot use an index, they are forced to do a linear scan of the entire child table. in a non-trivial database, this may be expensive. so, in most real systems, an index should be created on the child key columns of each foreign key constraint. the child key index does not have to be (and usually will not be) a UNIQUE index


SQL query


     UPDATE table_name SET field_name = newvalue [WHERE ... ] 
  

ссылка на «выражение для вычисления значения столбца» может относиться к текущим значениям в изменяемой таблице. например, мы можем уменьшить все цены портативных компьютеров на 10 процентов с помощью следующего оператора:

    UPDATE Laptop SET price = price * 0.9 ;  

LIMIT

to show the first X number of results, or to show a range from X to Y results. X is the starting point (the first record is 0) and Y is the duration (how many records to display). examples:

  SELECT * FROM `your_table` LIMIT 0, 10
  SELECT * FROM `your_table` LIMIT 5, 5 ; -- it will show records 6, 7, 8, 9, and 10 

ORDER BY

  SELECT cname1, ... FROM tname ORDER BY cnameN ASC 

  SELECT cname1, ... FROM tname ORDER BY cnameN DESC 

IN

the expression "x IN (y, z, ...)" is equivalent to "x=y OR x=z or ..."

BETWEEN

BETWEEN condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement

expression BETWEEN value1 AND value2

"x BETWEEN y AND z" is equivalent to two comparisons "x >= y AND x <= z"

SELECT * FROM employees WHERE employee_id BETWEEN 1 AND 10;

SELECT * FROM employees WHERE employee_id NOT BETWEEN 20 AND 25;

LIKE

LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. this allows you to perform pattern matching

    ----------------------------------------------------------------------
    wildcard                             explanation
    ----------------------------------------------------------------------
    %            to match any string of any length (including zero length)
    _            to match on a single character
    ----------------------------------------------------------------------

SELECT * FROM employees WHERE last_name LIKE 'A%';

SELECT last_name, first_name FROM employees WHERE last_name LIKE 'H_nt';

GROUP BY

GROUP BY используется в SELECT для того чтобы собрать данные из многих записей в одну запись (агрегировать) - чаще всего с использованием матетматической операции или агрегированных функций

sqlite> .schema
CREATE TABLE t3 (name txt, qty int, price real);

sqlite> select * from t3 ;
nike   |100 |123.45
puma   |120 | 90.34
adidas | 80 |200.10
puma   |300 | 75.40
nike   |169 | 54.82
puma   | 45 |147.80

sqlite> select name, qty * price from t3 ;
nike   |12345.00
puma   |10840.80
adidas |16008.00
puma   |22620.00
nike   | 9264.58
puma   | 6651.00

sqlite> select name, qty * price from t3 group by name ;
adidas |16008.00
nike   | 9264.58
puma   | 6651.00

GROUP BY применяется тогда, когда в SELECT используются агрегатные функции, и результаты их вычислений нужно получить по определенным группам

HAVING

HAVING используется вместе с GROUP BY для ограничения строк результата только теми, что имеют истинное условие на определенный атрибут:

SELECT ... from ... GROUP BY ... HAVING ... ;

WHERE позволяет задать условия выборки перед тем, как данные будут сгруппированы, а HAVING содержит условия, касающиеся данных уже непосредственно в самих группах полученных с помощью GROUP BY - такие данные, которых просто нет в таблице на момент получения базой запроса

вернемся к предыдущему примеру

sqlite> select name,  qty * price as value from t3 group by name having value > 15000 ;
adidas |16008.0

мы пытаемся получить информацию о том, какова стоимость товара для каждого производителя, при этом в результат мы хотим вывести только те компании, стоимость товаров которой превышают определенный предел. параметр GROUP BY сформировал три группы, соответствующие названиям компаний, для каждой из которых посчитана стоимость продукции. но параметр HAVING своим условием (>) отсек две записи из результирующей выборки оставив в окончательном результате только одну:

мы не можем добиться такого с помощью WHERE, потому что численные значения для value появляются ПОСЛЕ того как запрос начал выполняться

HAVING используется исключительно в связке с параметром GROUP BY и указывается сразу же после него (перед ORDER BY, если таковой имеется в запросе): оно и понятно, так как HAVING определяет условия для УЖЕ сгруппированных данных. в условии этого параметра можно использовать агрегатные функции

EXCEPT

EXCEPT извлечет во вторичном запросе все записи соответствующие WHERE а затем удалит из них те записи, которые являются результатом первичного запроса:

SELECT exp1, ... FROM table WHERE conds1 EXCEPT SELECT col1, ... FROM table WHERE conds2;

пример

sqlite> create table t1 (id int primary key not null, name text not null) ;
sqlite> create table t2 (id int primary key not null, name text not null) ;
sqlite> insert into t1 values (1,'aaa'), (2,'bbb'), (3 ,'ccc') ;
sqlite> insert into t2 values (1,'aaa'), (2,'bbb'), (3 ,'ccc'), (4,'ddd'), (5,'eee') ;
sqlite> select id from t2 except select id from t1 ;
4
5
sqlite> select name from t2 except select name from t1 ;
ddd
eee  

JOIN

селекция
выборка строк
проекция
выборка столбцов
сцеплене двух выборок
каждая строчка из первой, удовлетворяющая какому-либо условию, сопоставляется с каждой строчкой из второй, удовлетворяющей какому-либо условию
натуральное сцепление двух выборок
это сцепление, когда все условия являются условиями равенства
JOIN
возвращает строки, когда сопоставление успешно как в левой, так и в правой частях сцепки

  SELECT ...  FROM T1 JOIN T2 ON T1.C1 = T2.C1 AND T1.C2 = T2.C2;
  SELECT ...  FROM T1 JOIN T2 USING (C1, C2);

  <table1> JOIN <table2> ON <join_condition> [AND|OR <join_condition> ...] 

join_condition - это выражение, имещюее результатом булево значение которое определяет, какие строки считаются совпавшими

sqlite> create table tbl1 (f1 int, f2 text) ;
sqlite> create table tbl2 (a1 int, a2 text) ;
sqlite> insert into tbl1 values (1, "John") , (2, "Mary") ;
sqlite> insert into tbl2 values (1, "calls"), (2, "bets") ;
sqlite> select tbl1.f2, tbl2.a2 from tbl2 join tbl1 on tbl1.f1 = tbl2.a1 ;
John | calls
Mary | bets
sqlite> select tbl1.f2, tbl2.a2 from tbl1 join tbl2 on tbl1.f1 = tbl2.a1 ;
John | calls
Mary | bets
sqlite> select tbl1.f2, tbl2.a2 from tbl1 join tbl2 on tbl2.a1 = tbl1.f1 ;
John | calls
Mary | bets
sqlite> select tbl1.f2, tbl2.a2 from tbl2 join tbl1 on tbl2.a1 = tbl1.f1 ;
John | calls
Mary | bets

агрегированные функции

count(), count(*) - считает все записи запроса

count(col_name) - считает записи со значениями не равными NULL

sum(col_name)

max(col_name)

min(col_name)

avg(col_name)

group_concat(col_name) - слияние всех значений поля в одну строку с разделителем ","


CLI examples

sqlite> create table main (id integer primary key autoincrement, name text);  
sqlite> create table aux (qty int, stamp int, id int, foreign key (id) references main (id));
sqlite> .schema
CREATE TABLE main (id integer primary key autoincrement, name text);
CREATE TABLE sqlite_sequence (name,seq);
CREATE TABLE aux (qty int, stamp int, id int, foreign key (id) references main (id));

sqlite> insert into main (name) values ('john'), ('jack'), ('jane');
sqlite> select * from main;
1|john
2|jack
3|jane

sqlite> insert into aux values (100, strftime ('%s', 'now'), 1);
sqlite> select * from aux;
100|1527913769|1
sqlite>   

sqlite> .schema
CREATE TABLE t1 (id1 int unique, id2 int, v text);
CREATE TABLE t2 (id1 int, id2 int, v text);

sqlite> select * from t1;
1|1|.
2|1|..
3|1|...

sqlite> select * from t2;
1|1|aaa
1|2|bbb
1|3|ccc
2|1|xxx
2|2|yyy
2|3|zzz
3|1|sss
3|2|vvv
3|3|ttt

sqlite> select t2.v from t1 join t2 using (id1) group by t2.id1 having min (t2.id2) ;
aaa
xxx
sss

sqlite> select t2.v from t1 join t2 using (id1) group by t2.id1 having max (t2.id2);
ccc
zzz
ttt 


C-API example
/* gcc -o a.out -lsqlite3 agregate.c */

#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_SIZE 0x1024

int gcount = 0 ; 
int games[MAX_SIZE] ;

int 
gamesIDsFunc (void *notUsed, int argc, char **argv, char **colName) 
{ 
  games[gcount++] = atoi (argv[0]) ;
  return 0 ; 
} 

static int 
PlayersMovesFunc (void *notUsed, int argc, char **argv, char **colName)
{
  printf ("%s|%s\n", argv[0], argv[1]) ; 
  return 0;
}

/* param : path to db file */
int 
main (int argc, char **argv)
{
  sqlite3          *db ;
  char             req[MAX_SIZE] ;

  if ( argc!=2 ) { puts ("bad args") ; return 1 ; }

  if ( sqlite3_open (argv[1], &db) ) { puts ("cant open db") ; return 2; }

  sprintf (req, "%s", "select UniqueGameID from Game where Startmoney=10000;") ;  

  sqlite3_exec (db, req, gamesIDsFunc, 0, 0);

  int   tcount = 0 ; 
  int   gameID ;

  while  (gameID = games[tcount++]) 
  {
    sprintf (req, "%s %i %s", \
        "select Player.Player, Action.Action from Player join Action \
        on Player.UniqueGameID = Action.UniqueGameID and Player.Seat = Action.Player \
        where Action.UniqueGameID = ", gameID, "and Action.HandID < 30 and Action.BeRo = 0 \
        and Action.Action in ('is all in with', 'folds', 'checks', 'calls', 'bets');") ;

    sqlite3_exec (db, req, PlayersMovesFunc, 0, 0);
  } ;

  sqlite3_close (db);

  return 0;
}

C API

opening a database

  int 
  sqlite3_open_v2 (
    const char *filename,   /* database filename (UTF-8) */
    sqlite3 **ppDb,         /* SQLite db handle          */
    int flags,              /* flags                     */
    const char *zVfs        /* name of VFS module to use */
  );
  

a database connection handle is usually returned in *ppDb, even if an error occurs. the only exception is that if SQLite is unable to allocate memory to hold the sqlite3 object, a NULL will be written into *ppDb instead of a pointer to the sqlite3 object

if the database is opened (and/or created) successfully, then SQLITE_OK is returned. otherwise an error code is returned

the sqlite3_errmsg() routine can be used to obtain an description of the error following a failure of the sqlite3_open() routine

the default encoding for the database will be UTF-8

whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

the flags parameter to sqlite3_open_v2() can take one of the following three values, optionally combined with the SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_FULLMUTEX, SQLITE_OPEN_SHAREDCACHE, SQLITE_OPEN_PRIVATECACHE, and/or SQLITE_OPEN_URI flags:

SQLITE_OPEN_READONLY
the database is opened in read-only mode. if the database does not already exist, an error is returned
SQLITE_OPEN_READWRITE
the database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system. in either case the database must already exist, otherwise an error is returned
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
the database is opened for reading and writing, and is created if it does not already exist

if the SQLITE_OPEN_NOMUTEX flag is set, then the database connection opens in the multi-thread threading mode as long as the single-thread mode has not been set at compile-time or start-time. if the SQLITE_OPEN_FULLMUTEX flag is set then the database connection opens in the serialized threading mode unless single-thread was previously selected at compile-time or start-time. the SQLITE_OPEN_SHAREDCACHE flag causes the database connection to be eligible to use shared cache mode, regardless of whether or not shared cache is enabled using sqlite3_enable_shared_cache(). the SQLITE_OPEN_PRIVATECACHE flag causes the database connection to not participate in shared cache mode even if it is enabled

the fourth parameter to sqlite3_open_v2() is the name of the sqlite3_vfs object that defines the operating system interface that the new database connection should use. if the fourth parameter is a NULL pointer then the default sqlite3_vfs object is used

if the filename is ":memory:", then a private, temporary in-memory database is created for the connection. This in-memory database will vanish when the database connection is closed

if the filename is an empty string, then a private, temporary on-disk database will be created. this private database will be automatically deleted as soon as the database connection is closed

closing connection

    int sqlite3_close_v2(sqlite3*);  

call to sqlite3_close_v2() returns SQLITE_OK if the sqlite3 object is successfully destroyed and all associated resources are deallocated

if sqlite3_close_v2() is called with unfinalized prepared statements and unfinished sqlite3_backups, then the database connection becomes an unusable "zombie" which will automatically be deallocated when the last prepared statement is finalized. the sqlite3_close_v2() interface is intended for use with host languages that are garbage collected, and where the order in which destructors are called is arbitrary

applications should finalize all prepared statements, close all BLOB handles, and finish all sqlite3_backup objects associated with the sqlite3 object prior to attempting to close the object. if sqlite3_close_v2() is called on a database connection that still has outstanding prepared statements, BLOB handles, and/or sqlite3_backup objects then it returns SQLITE_OK but the deallocation of resources is deferred until all prepared statements, BLOB handles, and sqlite3_backup objects are also destroyed

if an sqlite3 object is destroyed while a transaction is open, the transaction is automatically rolled back

the C parameter to sqlite3_close_v2(C) must be either a NULL pointer or an sqlite3 object pointer obtained from sqlite3_open_v2() and not previously closed

One-Step Query Execution Interface

int sqlite3_exec (
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
) ;

the sqlite3_exec() interface runs zero or more UTF-8 encoded, semicolon-separate SQL statements passed into its 2nd argument, in the context of the database connection passed in as its 1st argument. if the callback function of the 3rd argument to sqlite3_exec() is not NULL, then it is invoked for each result row coming out of the evaluated SQL statements. the 4th argument to sqlite3_exec() is relayed through to the 1st argument of each callback invocation. if the callback pointer to sqlite3_exec() is NULL, then no callback is ever invoked and result rows are ignored

if an error occurs while evaluating the SQL statements passed into sqlite3_exec(), then execution of the current statement stops and subsequent statements are skipped. if the 5th parameter to sqlite3_exec() is not NULL then any error message is written into memory obtained from sqlite3_malloc() and passed back through the 5th parameter. to avoid memory leaks, the application should invoke sqlite3_free() on error message strings returned through the 5th parameter of sqlite3_exec() after the error message string is no longer needed. if the 5th parameter to sqlite3_exec() is not NULL and no errors occur, then sqlite3_exec() sets the pointer in its 5th parameter to NULL before returning

if an sqlite3_exec() callback returns non-zero, the sqlite3_exec() routine returns SQLITE_ABORT without invoking the callback again and without running any subsequent SQL statements

the 2nd argument to the sqlite3_exec() callback function is the number of columns in the result. the 3rd argument to the sqlite3_exec() callback is an array of pointers to strings obtained as if from sqlite3_column_text(), one for each column. if an element of a result row is NULL then the corresponding string pointer for the sqlite3_exec() callback is a NULL pointer. the 4th argument to the sqlite3_exec() callback is an array of pointers to strings where each entry represents the name of corresponding result column as obtained from sqlite3_column_name()

if the 2nd parameter to sqlite3_exec() is a NULL pointer, a pointer to an empty string, or a pointer that contains only whitespace and/or SQL comments, then no SQL statements are evaluated and the database is not changed

restrictions:


example
    /*   file mysqlite.c                     */
    /* gcc -o a.out -lsqlite3 mysqlite.c     */
    
#include <stdio.h>
#include <sqlite3.h>

static int 
callback (void *notUsed, int argc, char **argv, char **colName)
{
  int i;

  for ( i = 0 ; i < argc ; i++ )
  {
    printf ("%s = %s\n", colName[i], argv[i] ? argv[i] : "NULL") ;
  }
  printf("\n");

  return 0;
}

int 
main (int argc, char **argv)
{
  sqlite3 *db;
  char *errMsg = 0;
  int rc;

  if ( argc!=3 )
  {
    fprintf (stderr, "usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
    return 1;
  }

  rc = sqlite3_open (argv[1], &db);

  if ( rc )
  {
    fprintf (stderr, "can't open database: %s\n", sqlite3_errmsg (db));
    sqlite3_close (db);
    return 2;
  }

  rc = sqlite3_exec (db, argv[2], callback, 0, &errMsg);

  if( rc != SQLITE_OK )
  {
    fprintf (stderr, "SQL error: %s\n", errMsg);
    sqlite3_free (errMsg);
    sqlite3_close (db);
    return 3;
  }

  sqlite3_close (db);

  return 0;
}