Sqlite3 learning practice summary

[Practice environment]
Operating System: Mac 10.6

Tool: terminal, xcode

[Materials and practices]
Http://www.sqlite.org/index.html
Current version 3.7.7.1

On Mac 10.6, the version is 3.6.12.

InTerminalThe program name is sqlite3

Sqlite3-help

Frequently used:
Sqlite3 filename to enter a specific database file
Sqlite3-version: view the version

Good practice input:
Mac-Pro :~ User $ sqlite3 testdb

SQLite>

Standard DDL and DML statements can be used here, for example:
Create Table T1 ......;
Insert into T1 (...) values (...);
Select * from T1;

The command here uses; as the end symbol.

In addition to standard SQL statements, there are also some usage commands for management. These commands start with "." and can be read by entering ". Help ".

Here are some simple commands:
. Databases
. Tables
. Exit
. Quit
Use. Help to read other commands.

Speaking of this, using sqlite3 on a terminal is basically a practice

Back to development

ToC \ c ++ InterfaceFor example, the official information http://www.sqlite.org/capi3.html

1,
Sqlite3 only has the following types:
# Define sqlite_integer 1
# Define sqlite_float 2
# Define sqlite_text 3
# Define sqlite_blob 4
# Define sqlite_null 5

Involved functions:
Int sqlite3_column_type (sqlite3_stmt *, int icol); Return column type

2,
Open the SQLite database and close the SQLite database.
Int sqlite3_open (const char *, sqlite3 **);
Int sqlite3_open16 (const void *, sqlite3 **);
Int sqlite3_close (sqlite3 *);

The default encoding is UTF-8, and the second function uses the UTF-16
Returns an integer error code.
# Define sqlite_ OK 0/* successful result */
# Define sqlite_error 1/* SQL error or missing database */
# Define sqlite_internal 2/* an internal logic error in SQLite */
# Define sqlite_perm 3/* access permission denied */
# Define sqlite_abort 4/* callback routine requested an abort */
# Define sqlite_busy 5/* The database file is locked */
# Define sqlite_locked 6/* a table in the database is locked */
# Define sqlite_nomem 7/* A malloc () failed */
# Define sqlite_readonly 8/* attempt to write a readonly database */
# Define sqlite_interrupt 9/* operation terminated by sqlite_interrupt ()*/
# Define sqlite_ioerr 10/* some kind of disk I/O error occurred */
# Define sqlite_0000upt 11/* The database disk image is malformed */
# Define sqlite_notfound 12/* (internal only) table or record not found */
# Define sqlite_full 13/* insertion failed because database is full */
# Define sqlite_cantopen 14/* Unable to open the database file */
# Define sqlite_protocol 15/* database lock protocol error */
# Define sqlite_empty 16/* (internal only) database table is empty */
# Define sqlite_schema 17/* The database schema changed */
# Define sqlite_toobig 18/* too much data for one row of a table */
# Define sqlite_constraint 19/* abort due to contraint violation */
# Define sqlite_mismatch 20/* Data Type Mismatch */
# Define sqlite_misuse 21/* library used incorrectly */
# Define sqlite_nolfs 22/* uses OS features not supported on Host */
# Define sqlite_auth 23/* Authorization denied */
# Define sqlite_row 100/* sqlite_step () has another row ready */
# Define sqlite_done 101/* sqlite_step () has finished executing */

Practice code:
Sqlite3 * database;
Nsstring * path; // need you set

If (sqlite3_open ([path utf8string], & database) = sqlite_ OK)
{
Nslog (@ "DB created and opened ");
}

Sqlite3_close (database );

3,
Data manipulation statement-insert, update, delete

In SQLite version 3, The sqlite3_exec routine is just a wrapper around callto the prepared statement interface.
In sqlite3, The sqlite3_exec program [this job is in sqlite2] is packed and called in the preprocessing statement interface.

Typedef struct sqlite3_stmt sqlite3_stmt;
Int sqlite3_prepare (sqlite3 *, const char *, Int, sqlite3_stmt **, const char **);
Int sqlite3_prepare16 (sqlite3 *, const void *, Int, sqlite3_stmt **, const void **);
Int sqlite3_finalize (sqlite3_stmt *);
Int sqlite3_reset (sqlite3_stmt *);

After the pre-processing statement, you should use this function to execute:
Int sqlite3_step (sqlite3_stmt *);
The sqlite3_step () Routine return sqlite_row if it is returning a single row of the result set, or sqlite_done if execution has completed, either normally or due to an error.

If there is a complete SQL statement, it can be used to directly call
-(Bool) execsql :( char *) SQL
{
Bool isok = no;
Sqlite3_stmt * statement;
If (sqlite3_prepare_v2 (self-> _ database, SQL,-1, & statement, nil )! = Sqlite_ OK)
{
Isok = no;
}
Else
{
Int success = sqlite3_step (statement );
Sqlite3_finalize (statement );
If (success! = Sqlite_done)
{
Isok = no;
}
Else
{
Isok = yes;
}
}

Return isok;
}

There is also a method similar to the execution method of parameterized statements, which requires binding parameters and binding functions,
Int sqlite3_bind_blob (sqlite3_stmt *, Int, const void *, int N, void (*) (void *));
Int sqlite3_bind_double (sqlite3_stmt *, Int, double );
Int sqlite3_bind_int (sqlite3_stmt *, Int, INT );
Int sqlite3_bind_int64 (sqlite3_stmt *, Int, long INT );
Int sqlite3_bind_null (sqlite3_stmt *, INT );
Int sqlite3_bind_text (sqlite3_stmt *, Int, const char *, int N, void (*) (void *));
Int sqlite3_bind_text16 (sqlite3_stmt *, Int, const void *, int N, void (*) (void *));
Int sqlite3_bind_value (sqlite3_stmt *, Int, const sqlite3_value *);

The second argument is the index of the SQL parameter to be set. The leftmost SQL parameter has an index of 1.
The second parameter isIndex value of the SQL parameter, starting with 1

Check this Code:
-(Void) savembkey :( nsstring *) Key
{
Bool isok = no;

Sqlite3_stmt * statement;

Static char * SQL = "insert into mbkey (key) values (?); ";

Int success = sqlite3_prepare_v2 (self-> _ database, SQL,-1, & statement, null );
If (success! = Sqlite_ OK)
{
Isok = no;
}
Else
{
Sqlite3_bind_text (statement, 1, [Key utf8string],-1, sqlite_transient );

Success = sqlite3_step (statement );
Sqlite3_finalize (statement );
}

If (success = sqlite_error)
{
Isok = no;
}
Else
{
Isok = yes;
}

Return;
}

4,
Query and fill. This is similar to the above. The unique difference requires column value binding.

When sqlite3_step returns sqlite_row, the following functions are required to receive data:

Const void * sqlite3_column_blob (sqlite3_stmt *, int icol );
Int sqlite3_column_bytes (sqlite3_stmt *, int icol );
Int sqlite3_column_bytes16 (sqlite3_stmt *, int icol );
Double sqlite3_column_double (sqlite3_stmt *, int icol );
Int sqlite3_column_int (sqlite3_stmt *, int icol );
Sqlite3_int64 sqlite3_column_int64 (sqlite3_stmt *, int icol );
Const unsigned char * sqlite3_column_text (sqlite3_stmt *, int icol );
Const void * sqlite3_column_text16 (sqlite3_stmt *, int icol );
Int sqlite3_column_type (sqlite3_stmt *, int icol );
Sqlite3_value * sqlite3_column_value (sqlite3_stmt *, int icol );

These routines form the "result set" interface.

The second argument is the index of the column for which information shocould be returned. The leftmost column of the result set has the index 0.
Note:The start value of the icol parameter is 0.

Let's take a look at the following practical code:
-(Void) getlist :( nsmutablearray *) keyslist
{
Bool isok = no;

Sqlite3_stmt * statement;

Static char * SQL = "select keyid, key from mbkey order by keyid ;";

Int success = sqlite3_prepare_v2 (self-> _ database, SQL,-1, & statement, null );
If (success! = Sqlite_ OK)
{
Isok = no;
}
Else
{
// In the query result set, all records are traversed one by one. The number corresponds to the column value.
While (sqlite3_step (statement) = sqlite_row)
{
Int KID = sqlite3_column_int (statement, 0 );
Char * Key = (char *) sqlite3_column_text (statement, 1 );

Keyinfo * keyinfo = [[keyinfo alloc] init];
If (key)
[Keyinfo. Key setstring: [nsstring stringwithuf8string: Key];
Keyinfo. keyid = kid;

[Keyslist addobject: keyinfo];
[Keyinfo release];
}
Sqlite3_finalize (statement );
}

If (success = sqlite_error)
{
Isok = no;
}
Else
{
Isok = yes;
}

Return;
}

Note: In the current practice environment, inner join is supported, left join is supported, and right out join and full join are not supported.
Select * from a inner join B on A. ID = B. ID;
Select * from a join B on A. ID = B. ID;
Select * from a left join B on A. ID = B. ID;
Select * from a right Outer Join B on A. ID = B. ID; // error message, not supported

Well, you can use programming to perform basic operations on the SQLite database. Basically, you can develop small dB programs.

Reference SQLite does not support SQL syntax documentation: http://wenku.baidu.com/view/823c3e92daef5ef7ba0d3cd5.html

/****************/
Sqlite3 file format description address:
Http://www.sqlite.org/fileformat2.html

/****************/

5. SQL Character Processing
A, substr (x, y, z), X field name, y takes 1 as the start Character index value, the abbreviation function is substr (x, y)
B. Like statement. The '_' and '%' Wildcards are supported.
C. Use "|" for character concatenation. For example: Select 'A' | 'B' from C; Output AB
[ABC,]

6. Date Data Processing
We recommend that you use '% Y-% m-% d' or' % Y-% m-% d % H: % m: % s' to store date data ', the monthly and daily values are two places, which is not enough to fill the high positions

See the supported formats on the official website:
YYYY-MM-DD
YYYY-MM-DD hh: Mm.
YYYY-MM-DD hh: mm: Ss.
YYYY-MM-DD hh: mm: Ss. Sss.
YYYY-MM-DDTHH: mm
YYYY-MM-DDTHH: mm: SS
YYYY-MM-DDTHH: mm: Ss. Sss
Hh: mm
Hh: mm: SS
Hh: mm: Ss. Sss

Examples of commonly used SQLite date formats:

Select date ();
2012-07-04

Select datetime ();
2012-07-04 02:47:23

Select strftime ('% y', 'right ')
2012

Select strftime ('% m', 'Now ')
07

Select strftime ('% d', 'right ')
04

Select strftime ('% s', 'Now ')
1341371148 // returns the number of seconds from January 1 ,.

Select date ('now ', 'start of month',' + 1 month', '-1 Day ');
2012-07-31 // the last day of the current month

Select julianday (date ()-julianday ('2017-07-01 ');
3 // The number of days from

Good luck