Basic data type

type instructions
Int (integer) Minus 2 to the 31(-2,147,483,648) to 2 to the 31(2,147,483,647)
float -1.79E+308 to 1.79E+308 variable precision numbers
real -3.04E+38 to 3.04E+38 Variable precision number
char Fixed length non-Unicode character data, maximum length 8000
varchar Variable-length non-Unicode character data with a maximum length of 8000
text Variable-length non-Unicode character data with a maximum length of 2^31-1(2G)

Ex. :

Date of Birth Name: Gender: Hobby (hobby)
char(16) varchar(256) char(16) text
19940801 The small white male C++
19940802 The little black female PHP

SQL general purpose statement

Note: Added at the end of the command line statement
;(semicolon)

Create a database

  • .open database name

Note: SQLite3 provides command-line functional statements, not SQL generic statements 

.open person.db

Create a data table

  • Create table name (field name data type, field name data type, field name data type);
create table child(date char(16), name varchar(256), gender char(16), hobby text);

Insert data

  • Insert into table values(‘ id ‘, ‘id ‘,’ id ‘, ‘id ‘,’ id ‘);

Note: If the data type is CHAR, VARCHAR, TEXT data, you must use a ” or “” reference 

Insert into child values('19940801', 'C++', 'C++'); INSERT INTO CHILD VALUES ('19940802', 'Ctrl ',' Ctrl ', 'Ctrl ');

Query data

  • Select field name,… , name from table name;

Note: field names can be separated by a “, “comma if they are multiple, and by a” * “asterisk if they are all

Query all:

sqlite> select * from child; 19940801 | | small white male little black female | | 19940802 | | c + + PHP

Query multiple:

sqlite> select name, date from child; The small white little black | | 19940801 19940802
  • Select field name,… Select * from table name where name = 1;
Sqlite > select * from child where gender=' female '; The little black female | | 19940802 | PHP
  • Fuzzy condition query using like (“%” wildcard)
sqlite> select * from child where date like '%1'; 19940801 | | small white male | c + +
  • And (both conditions are true)
sqlite> select * from child where date like '%1' and hobby like 'C%'; 19940801 | | small white male | c + +
  • Or (one condition is true)
sqlite> select * from child where date like '%1' or hobby like 'P%'; 19940801 | | small white male little black female | | 19940802 | | c + + PHP

Update the data

  • Update table name set field 1= field 1, field 2= field 2… update table name set field 1= field 2… WHERE conditional expression
SQLite > update Child Set Hobby ='JAVA' WHERE name =' Hobby '; sqlite> select * from child; 19940801 | | small white male little black female | | 19940802 | | c + + JAVA

Delete the data

Delete form name; DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DELETE FROM TABLE NAME WHERE DELETE FROM TABLE NAME;

.table child sqlite> select * from child; 19940801 | | small white male little black female | | 19940802 | | c + + JAVA sqlite > delect the from child where hobby = 'JAVA'; sqlite> select * from child; 19940801 | | small white male | c + +
.table
child

delete from child;
sqlite> select * from child;
sqlite>

sqlite> .table
child
.table
child

sqlite> drop table child;
sqlite> .table
sqlite>

Query the create table command

  • The schema name of the table

Note: SQLite3 provides command-line functional statements, not SQL generic statements 

sqlite> .schema child
CREATE TABLE child(date char(16), name varchar(256), gender char(16), hobby text);

Add fields

  • Alter table name add column name alter table name add column name
sqlite> select * from child; 19940801 | | small white male little black female | | 19940802 | | c + + PHP sqlite > alter table child add column height int. sqlite> select * from child; 19940801 | | small white male little black female | | | | 19940802 | c + + PHP | sqlite > update the child set height = 171; sqlite> select * from child; 19940801 | small white | | 19940802 | | 171 c + + black male female | | | 171 PHP sqlite > alter table child add column addr varchar (256) the default 'nanjing'; sqlite> select * from child; 19940801 | small white male | | 19940802 | | 171 | nanjing c + + black female | | 171 | | PHP nanjing

Query table structure information

  • Pragma table_info(name of table);
sqlite> pragma table_info(child); 0|date|char(16)|0||0 1|name|varchar(256)|0||0 2|gender|char(16)|0||0 3|hobby|text|0||0 4|height|int|0||0 5 | addr | varchar (256) | | 0 'nanjing' | 0

Set the field constraints when creating the table

attribute instructions
integer promary key autoincrement As the primary key, automatically increment
not NULL Cannot be NULL
unique Only, can not repeat
default The default value

Ex. :

id name status online
1 led1 0 0
2 led2 0 0
3 led3 0 0
4 led4 0 1 
Mysql > create table device(id integer primary key autoincrement); // set name unique status int not NULL default 0, status int not NULL 0 online int not NULL); // Set online not to be empty

Ex. : 

sqlite> crate table device(id integer primary key autoincrement, name varchar(256) unique, status int not NULL default 0, online int not NULL);

sqlite> .table
device

sqlite> create table device(id integer primary key autoincrement, name varchar(256) unique, status int not NULL default 0, online int not NULL);
Error: table device already exists
  • If NOT EXISTS, create a table if not exists
sqlite> .table device sqlite> create table if not exists device(id integer primary key autoincrement, name varchar(256) unique, status int not NULL default 0, online int not NULL); // Pay attention here!! sqlite> create table if not exists device(id integer primary key autoincrement, name varchar(256) unique, status int not NULL default 0, online int not NULL); // Pay attention here!! sqlite> .table device
  • Insert data

Uniqueness of primary keys; The uniqueness of the unique attribute 

sqlite> select * from device; sqlite> insert into device values(1, 'led1', 0, 0); sqlite> select * from device; 1|led1|0|0 sqlite> insert into device values(1, 'led1', 0, 0); // Pay attention here!! Error: UNIQUE constraint failed: device.id sqlite> insert into device values(2, 'led1', 0, 0); // Pay attention here!! Error: UNIQUE constraint failed: device.name sqlite> insert into device values(2, 'led2', 0, 0); sqlite> select * from device; 1|led1|0|0 2|led2|0|0 sqlite>

Default value usage (specified field (column) inserts, default value will be used if not specified) 

sqlite> select * from device; 1|led1|0|0 2|led2|0|0 sqlite> insert into device (name, online) values('led3', 0); // Pay attention here!! sqlite> insert into device (name, online) values('led4', 1); // Pay attention here!! sqlite> select * from device; 1|led1|0|0 2|led2|0|0 3|led3|0|0 4|led4|0|1