What is a database? Structured Query Language (SQL) is a structured query language used to define and manipulate data in relational databases
What is an SQLite database?
SQLite encyclopedia
Data Definition Language (DDL) – Data Definition Language (DDL
| command description | | | — — — — — — — — — — — — – | : — — — — — — — — — — — — — — — — — — : | | CREATE | CREATE a new table, a table view, or other objects in the database | | the ALTER | modifying an existing database objects in the database, Such as a table | | DROP | delete the entire table, table or view, or other objects in the database |
* 2. Data Manipulation Language (DML) – Data Manipulation statements
| command description | | | — — — — — — — — — — — — – | : — — — — — — — — — — — — — — — — — — : | | INSERT new data | | | UPDATE | modifying an existing database objects in the database, such as a table | | DELETE | DELETE the entire table, View or table, or other objects in the database |
* 3. Data Query Language (DQL) – Data Query statement
| command description | | | — — — — — — — — — — — — – | : — — — — — — — — — — — — — — — — — — : | | | | the SELECT query data # # # 4, the introduction of conditional statements If want to update or delete some fixed record, Then after the DML statements must add some conditions common format of conditional statement # # # # 1. The WHERE | WHERE annotation judging | | | | — — — — — — — — — — — — – | : — — — — — — — — — — — — — — — — — — : | | WHERE | = | field in a certain value Can’t use the = = (equal) | | the where is a value | | field is equivalent to = | | where | field! = a value | to | | the where is not a value | | field is not equal to! = | | where | field > a value | to | | where | field 1 = 2 > a value and field a value | and equivalent to the C language of && |
In addition:where2 = 1 = a value or field a value / / or equivalent C | in the | example t_zorn table name is equal to rip and phone is not equal to the records of 110 update t_zornset name = 'zorn' wherenumber ! = 110; ` ` `####2.LIKE| | statements describe | | -- -- -- -- -- -- -- -- -- -- -- -- - | : -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- : | | WHERE number LIKE'110%'| search in 110 at the beginning of any value | | WHERE number LIKE'% 110%'| looking for any position contains 110 arbitrary value | | WHERE number LIKE'_10%'| search for second and third places for 10 arbitrary value | | WHERE number LIKE'0 _ _ % %'| search begin with 0, and the length is at least 3 characters of arbitrary value | | WHERE number LIKE'% 112'| search in 112 at the end of the arbitrary value | | WHERE number LIKE'_1%2'| to find the second is 1, and 2 at the end of the arbitrary value | | WHERE number LIKE'2 __3| search length is 5 digits, and start with 2 to 3 at the end of the arbitrary value |Copy the codeSelect * from table_name select * from table_name LIMIT 1, 2; select * from table_name select * from table_name LIMIT 1, 2; Select * from t_zorn limit 4, 8;
Skip the first four statements, and then select 8 records limit is used for paging queries, e.g., 5 records per page
| | page LIMIT numerical | | | — — — — — — — — — — — — – | : — — — — — — — — — — — — — — — — — — : | | | 1 page LIMIT | 0, 5 | | | 2 page LIMIT | 5, 5 | | page 3 | LIMIT | 10, 5 | |… | | | | | n page limit | 5 * (n – 1), 5 |
“` select * from t_zorn limit 7 ; Select * from t_student limit 0, 7; Takes the first seven records
SQLite syntax introduction
#####1)SQLite divides data into the following storage types:
Copy the codeInteger Specifies an integer value stored in 1, 2, 3, 4, 6, or 8 bytes, depending on the size of the value. A real floating point value, stored as an 8-byte IEEE floating point number. Text A character string that is stored using the database encoding (UTF-8, UTF-16BE, or UTF-16LE). Blob binary data, stored according to its input. ` ` `
Create table t_zorn(name, number); create table t_zorn(name, number); Tip: In order to maintain good programming practices and facilitate communication between programmers, it is best to write construction statements with the specific type of each field #####2) introduction to SQLite methods
Sqlite3_open () sqlite3_open(), created when no database is available. Sqlite3_exec (), executes the non-query SQL statement Sqlite3_step(), which is used to move through the recordset after sqlite3_prepare is called. Sqlite3_stmt * STMT sqlite3_stmt (); sqlite3_column_text(); Sqlite3_column_blob (), which takes bloB data sqlite3_column_int(), which takes int data# # # # # 3) SQLite statements
Copy the codeSQLITE_API int SQLITE_STDCALL sqlite3_open(const char filename, / database file path (utF-8) */ sqlite3 *ppDb/database
SQLITE_API int SQLITE_STDCALL sqlite3_exec(sqlite3*, /* an open database instance/const char SQL, /* callback (void,int,char,char**); /* callback (void,int,char **); /* callback (void, char,char**); / The first argument to the callback function */ char *errmsg/error message (for debugging) */
SQLITE_API int SQLITE_STDCALL sqlite3_prepare_v2(sqlite3 db, / database instance */ const char zSql, SQL statement to check, utF-8 encoded/int nByte, / SQL statement maximum length */ sqlite3_stmt *ppStmt, / OUT: */ const char *pzTail/OUT: pointer to the unused part of the SQL statement */);
Int sqlite3_step(sqlite3_stmt*); // If a row is queried, SQLITE_ROW is returned
Double sqlite3_column_double(sqlite3_stmt*, int iCol); // Float data const unsigned char sqlite3_column_text(sqlite3_stmt, int iCol); Int sqlite3_column_int(sqlite3_stMT *, int iCol); Sqlite3_int64 sqlite3_column_int64(sqlite3_stMT *, int iCol); // Const void sqlite3_column_blob(sqlite3_stmt, int iCol); // Binary text data
6. Basic operationsImport libsqlite3 TBD! [libsqlite3-1.png](http://upload-images.jianshu.io/upload_images/2330091-502d2836f666efa8.png? imageMogr2/auto-orient/strip%7CimageView2/2/w/1240) ! [libsqlite3-2.png](http://upload-images.jianshu.io/upload_images/2330091-6d172e6243fcdc2a.png? imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)####1. Create table
Copy the codeFormat create table Table name; Create table if not exists Table name; Create table t_zorn; create table if not exists t_zorn;
####2. Insert data
Copy the codeInsert into table name (insert into table name, insert into table name, insert into table name) Values (field 1 value, field 2 value,…) ; Insert into T_student (name, number) values (‘zorn’, 123567); Note that the contents of strings in the database should be enclosed in single quotes
####3. Drop tables
Copy the codeFormat Drop table Table name. Drop table if exists Specifies the name of the table. Example drop table t_zorn; drop table if exists t_zorn ; Note that the above example deletes all records from the T_zorn table
####4. Specify a location to delete data (delete)
Copy the codeDelete from table where 1 = 1; Delete from t_zorn where id = 1;
####5. Query data (select)
Copy the codeSelect field 1, field 2… From the name of the table; Select * from table_name; Select name, age from t_zorn; select * from t_zorn ; select * from t_zorn where number > 110 ; // Conditional query ‘ ‘
####6. Update data
Format Update table namesetField 1 = the value of field 1, field 2 = the value of field 2... ; The sample update t_zornset name = 'ren', numer = 112 ; Note that the above example changes the name of all records in the T_zorn table to ren and the number to 112 [additionablewhere(the equivalent ofif) Judge.]Copy the code### Summary: ####SELECT other uses
Select field 1 alias, field 2 alias,... From table name alias; Select 1 as alias, 2 as alias,... From 表名 as alias; Select the alias. Field 1, alias. Field 2,... From table name alias; Select name myname, number mynumber from t_zorn t_Ren; Select w.name, w.number from t_student s; select w.name, w.number from t_student s; Select count (*) from table name; select count (*) from table name; Select count (*) from table_name; Select count (number) from t_zron; select count ( * ) from t_zornwherenumber = 110; Select * from t_zorn order by select * from t_zorn order by; select * from t_zorn order by age ; Select * from t_zorn order by age desc; select * from t_zorn order by age desc; Select * from t_zorn order by age ASc; Select * from t_zorn order by age ASc, height desc; Sort by age (ascending) first, and by height (descending) if age equals### Attach the download address of SQLite management tool (for reference only) :! [SQLite Professional](http://upload-images.jianshu.io/upload_images/2330091-77fb35aeab2dd571.png? ImageMogr2 / auto - received/strip % 7 cimageview2/2 / w / 1240) - > download] [SQLite management tool (http://www.cr173.com/mac/123368.html)# of actual combat:-> [SQLite3Demo](https://github.com/ZornHuang/SQLite3) ! [Runtime main interface](http://upload-images.jianshu.io/upload_images/2330091-b145d01ab3840d6c.png? imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)Copy the code