3.1 Create Operation
In SQLite, as with SQL Server and MySQL, the creation operations include creating tables, views, and indexes. Details as follows:
1. Create a table
create table name (field name 1 field type 1, field name 2 field type 2, ...);
// You can not specify the field type
We tend to specify types.
E.g:
CREATE TABLE person1 (a, b, c); // The end of this statement must use ";"
CREATE TABLE person2 (id int primary key autoincrement, name varchar (20));
Note: primary key: This field is the primary key. It cannot be duplicated if it has a unique value.
create table if not exists table name (field name 1 field type 1, field name 2 field type 2, ...);
CREATE [[TEMP | TEMPORARY] TABLE table_name (column_definitions, [constraints]);
Square brackets indicate optional items. Vertical bars mean one of the multiple options. {} Contains a list of options, indicating that one must be selected
TEMP: declares that the created table is a temporary table and only lives in the current session. Once the connection is disconnected, it will be automatically deleted.
table_name: Enter the table name here.
column_definitions: Represents a comma-separated column definition (also called a field list), each field includes a field name, field constraints and field type. If there are multiple constraints, the constraints are separated by spaces.
[, constraints]: Represents constraints. For example, you can use UNIQUE constraints to specify that the value of a field in all records should be different.
2. Create a view
E.g:
sqlite> CREATE VIEW Westview AS SELECT * FROM testable WHERE first_col> 50;
// Create the simplest view
sqlite> CREATE TEMP VIEW tempview AS SELECT * FROM testtable WHERE first_col> 50; // Create a temporary view
Create index
E.g:
sqlite> create index test_idx on mytable (value);
3.2 Modify Table Structure Operation
Similar to the SQL server and MySQL, SQLite's modification table is also an operation to add, delete, modify, and check the tables in the database. Often these operations also apply to the where operation, that is, the use of conditional statements. If you just want to update or delete certain table fixed records, you must add some conditions after the DML statement to achieve. The following table 1 lists some common conditional statements and common formats:
Table 1 SQLite Modified Table Structure Common Conditional Statements
Common conditional statements
|
note
|
where Column name = value
|
Cannot use two =
|
where Column name is value
|
is Equivalent to =
|
where Column name != Some value
|
!= Means not equal
|
where Column name is not value
|
is not Equivalent to !=
|
where Column name > value
|
Means greater than a certain value
|
where Column name 1 = value and Field 2 > value
|
and Equivalent to && in C
|
where Column name 1 = value or Field 2 = value
|
or Equivalent to || in C
|
3.3 Data Query Operation
1. Exact query
Exact lookup, that is, using the exact conditions to find the corresponding data in the table or view or index.
E.g:
select * from table1 where id=1; // This means to find the data with id 1 in the test table
sprintf (show, "select word from history where username = '% s';", Classmsg.name);
2. Fuzzy query
The fuzzy query is usually to give a range of data and then use like followed by conditions that require data to find related data.
select * from <table name>; // all data;
select * from table1 where name like "s%"; //% means any number of characters
select * from table1 where name like "s_"; // _ means any character
select * from table2 limit 0,3; // 0 means starting from // first line, 3 means querying three records
select * from student where age> 10; // conditional query
The select statement is relatively complex in SQL, and it is also the essence of SQL. Syntactically, the select statement is composed of a series of words, and each word can complete a specific operation. The select statement except select Except for the sentence, all the remaining words are optional.
3.4 Delete Operation
1. Delete data
delete from <table name> where condition;
If the sentence does not have a where part, then executing the statement will delete the entire table's data.
E.g:
delete from table name;
delete from table1 where id=1; // Delete the data with id 1 in the test table
delete from table1 where score <90; // Delete data in test table with a score less than 90
In the delete operation, you can also use where and like to perform precise deletion and fuzzy deletion. The use of where and like in delete is the same as that. Therefore, no specific description is given here, but only a brief description in the above example. The same applies to views and indexes.
2. Delete the table (the same method for views and indexes)
E.g:
sqlite> DROP TABLE test; // Delete the test table
sqlite> DROP TABLE IF EXISTS test;
3.5 Increase Operation
The insert statement is used to increase the data. When using this statement, pay attention to the column names and values must be corresponding, you can not confuse the order, otherwise, the data insertion is prone to errors.
insert into <table name> (field list) values (corresponding values); // when using a string, you can add double or single quotes
E.g:
insert into test (name, age) values ("mike", 21); // Insert information named mike, age 21
sqlite> insert into test (id, value) values (3, 'Rose'); // Insert information with id 3 named Rose
3.6 Update Data
update table name set field 1 = value of field 1, field 2 = value of field 2, ...;
E.g:
update test set name = ‘Mike’, age = 22; // Change the name of all records in the test table to Mike and age to 22.
Similarly, conditional statements can also be used when updating data. The method of use is to add conditional statements after the statement. The method is similar to the previous one, so it will not be described
3.7 Other SQLite output commands
As shown in Table 2 below [9-10].
Table 2 Other SQLite output commands
SQLite output command
|
Effect
|
.schema [table]
|
Structure of the display table
|
.tables
|
Get all tables and views
|
.indices [table ]
|
Get the index list of the specified table
|
.output [filename ]
|
Export database to SQL file
|
.read [filename ]
|
Import database from SQL file
|
.output [filename.csv ]
|
Format output data to CSV format
|
.import [filename.csv ] newtable
|
Import data from a CSV file into a table
|
sqlite3 [database] .dump > [filename]
|
Backup database
|
sqlite3 [database ] < [filename ] *
|
Restore the database
|