-------------------------------------------the sqlite3 command----------------------------------------------------------
1. Create a database
Sqlite3 Databasename.db < Test.sql or touch databasename.db
2. Display and Configuration
Sqlite>.help--Show usage instructions Help information
Sqlite>.tables--Lists the names of all tables in the library
Sqlite>.schema-Displays information for all tables, followed by specific table names
Sqlite>.databases--list additional database names and files
Sqlite>.show--View the default settings for the command prompt
Sqlite>.mode Column--Sets the display effect to be displayed as a column
Sqlite>.quit--Exit the database
3. Syntax Conversion command
Sqlite3 databasename.db "SELECT * from User;"--Find table user all information
4. Wildcard meaning
Wildcard characters for like lookup statements
<%>--a percent sign representing 0, one or more digits or symbols
<_>--underline represents a single number or character
Glob the wildcard character of a lookup statement, case sensitive
<*>--asterisks represent 0, one or more digits or characters
<?>--question mark represents a single number or character
5. Data type
Null-the value is a null value
Integer-The value is a signed integer
Real-value is a floating-point number stored as 8 bytes
Text-value is a text string
Blob-value is a blob of data
Common Syntax------------------------------------------------------------------------------------------------------------database
1. Create a table:
Sqlite>create table User (ID integer primary key, Name text not null,age integer);
Note: Creating a user table with the ID primary key, NOT NULL constraint means that the fields cannot be empty when creating records in the table, and the strings need to be enclosed in single or double quotes.
2. Delete the table:
Sqlite>drop table user;
3. Add Data:
Sqlite>insert into user values (2, ' Cheng ', 20);
Sqlite>insert into User (Name,age) VALUES (' Liu ', 24);
4. Delete data
Sqlite>delete from user; --Clear Table user all data
Sqlite>delete from user where id = 2; --delete the data with ID 2
5. Modify the data
Sqlite>update user set age=23,name= ' Wang ' where id = 3; --Modify the age and name of ID 3
Sqlite>update user set age=18; --Modify the table all the age in the user is 18
6. Find Data
Sqlite>select * from user; --Find all the contents of the table user
Sqlite>select * FROM user where id = 2; --Find all information in table user with ID 2 item
Sqlite>select * from user where id>2 and id<10; --All information in the lookup table exceeding the ID greater than 2 and less than 10 items
Sqlite>select Count (*) from user; --Statistics number of user records
Sqlite>select name,age from user where id=3; --Find the option for ID 3 in table user and display the name and age in
7. Replace data
Sqlite>replace into user values (1, ' Zhang ', 79); --ID 1 exists in the update name and age, does not exist on INSERT, note ID must be primary key, table can set multiple primary keys
8, in usage
Sqlite>select * from the user where age in (24,27); --Find all records with age 24 or 27
Sqlite>select * from the user where age is not in (24,27); --Find all records where age is neither 24 nor 27
9. Usage of like
Sqlite>select from the user where name like ' z_% '; --Find any value that starts with Z and contains at least two characters
Sqlite>select from user where the age is like '%2% '; --Find any value that contains 2 in any location
10. Usage of limit
Sqlite>select * from user limit 2; --Display the first two information in the table user
Sqlite>select * from user limit 2 offset 3; --show that 2 records are extracted starting from 4
11. Use of ORDER by
Sqlite>select * from the user order by the age Asc/desc; --Sort the information in the display table user in ascending/descending order of age
12, the use of distinct
Sqlite>select distinct name from user; --Displays the same name as the merge
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Sqlite3 Database Usage
