SQLite learning Manual

1. Create a data table:

The syntax rules and usage of this command are basically the same as those of most relational databases. Therefore, we use examples to demonstrate the rules for creating tables in SQLite. However, we will provide additional instructions for some SQLite-specific rules. Note: All the following examples are completed in the SQLite built-in command line tool.
1). the simplest data table:
SQLite>Create TableTesttable (first_col integer );
It should be noted that for a custom table name, such as testtable, it cannot start with SQLite _, because all the table names defined with this prefix are used inside SQLite.

2) create a data table with default values:
SQLite>Create TableTesttable (first_col integer
Default0, second_col varcharDefault'Hello ');

3). Create a table in the specified database:
SQLite>Attach Database'D:/mydb. db'
AsMydb;
SQLite>Create TableMydb. testtable (first_col integer );
The attach database command is used to attach an existing database file to the current connection, and then create a data table in the target database by specifying the database name, such as mydb. testtable. This rule also requires some additional instructions. If we do not specify a database name when creating a data table, the table will be created in the currently connected main database, there can be only one main database in a connection. To create a temporary table, you do not need to specify the database name. See the following example:
-- Create two tables: a temporary table and a common table.
SQLite>Create temp tableTemptable (first_col integer );
SQLite>Create TableTesttable (first_col integer );
-- Export the cached data in the current connection to a local file and exit the current connection.
SQLite>. BackupD:/mydb. DB
SQLite>. Exit
-- Re-establish the SQLite connection and re-import the exported database as the master database.
SQLite>. RestoreD:/mydb. DB
-- View the table information in the database. The result shows that the temporary table is not persisted to the database file.
SQLite>. Tables
Testtable

4). "If not exists" clause:
If the name of the currently created data table already exists, that is, it conflicts with the existing table name, view name, and index name, this creation operation will fail and report an error. However, if you add the "if not exists" clause when creating a table, the operation will not be affected, that is, no error will be thrown, unless the current table name conflicts with an index name.
SQLite>Create TableTesttable (first_col integer );
Error: Table testtable already exists
SQLite>Create Table if not existsTesttable (first_col integer );

5). Create Table... as select:
A data table created in this way has the same schema information as the result set returned by the SELECT query, but does not contain constraints such as the default value and primary key. However, the newly created table will contain all the data returned by the result set.
SQLite>Create TableTesttable2
As select*FromTesttable;
SQLite>. SchemaTesttable2
Create Table testtable2 (first_col INT );
The. schema command is a built-in command of sqlite3 command line tool to display the create table statement of the current data table.

6). Primary Key constraints:
-- Specify the primary key directly on the field definition.
SQLite>Create TableTesttable (first_col integer
Primary Key ASC);
-- After all the fields have been defined, define the number constraint of the table. Here we define the joint primary key based on first_col and second_col.
SQLite>Create TableTesttable2 (
...> First_col integer,
...> Second_col integer,
...>Primary Key(First_col, second_col)
...> );
Like other relational databases, primary keys must be unique.

7). uniqueness constraints:
-- Specify the uniqueness constraint directly on the field definition.
SQLite>Create TableTesttable (first_col integer
Unique);
-- After all fields have been defined, the uniqueness constraint of the table is defined. Here, the uniqueness constraint of the two columns is defined.
SQLite>Create TableTesttable2 (
...> First_col integer,
...> Second_col integer,
...>Unique(First_col, second_col)
...> );
In SQLite, the null value is considered to be different from any other value, including other null values, as shown in the following example:
SQLite> Delete from testtable;
SQLite> select count (*) from testtable;
Count (*)
----------
0
SQLite> insert into testtable values (Null);
SQLite> insert into testtable values (Null);
SQLite> select count (*) from testtable;
Count (*)
----------
2
It can be seen that the two inserted null values are successfully inserted.

8). Empty (not null) constraints:
SQLite>Create TableTesttable (first_col integer
Not null);
SQLite> insert into testtable values (null );
Error: testtable. first_col may not be null
From the output, we can see that first_col has been defined as a non-null constraint and therefore cannot insert a null value.

9). Check constraints:
SQLite>Create TableTesttable (first_col integer
Check(First_col <5 ));
SQLite> insert into testtable values (4 );
SQLite> insert into testtable values (20 );
-- 20 violation of the checking constraint of the field first_col (first_col <5)
Error: constraint failed
-- Like other constraints, check constraints can also be defined based on multiple columns in the table.
SQLite>Create TableTesttable2 (
...> First_col integer,
...> Second_col integer,
...>Check(First_col> 0
AndSecond_col <0)
...> );

Ii. Table modification:

SQLite only supports the alter table command to modify the table name and add new fields. Other functions, such as renaming fields, deleting fields, and adding and deleting constraints, are supported.
1). Modify the table name:
Note that the table name in SQLite can only be modified in the same database and cannot be moved to the attached database. Once the table name is modified, the existing indexes of the table will not be affected. However, the views and triggers dependent on the table have to be modified again.
SQLite> Create Table testtable (first_col integer );
SQLite>ALTER TABLETesttable
RenameTesttable2;
SQLite>. Tables
Testtable2
The output of the. Tables command shows that the testtable has been changed to testtable2.

2). Add a field:
SQLite> Create Table testtable (first_col integer );
SQLite>ALTER TABLETesttable
Add ColumnSecond_col integer;
SQLite>. SchemaTesttable
Create Table "testtable" (first_col integer, second_col integer );
The output of the. schema command shows that the testtable definition contains new fields.
The last thing to note about alter table is that the execution time of this command in SQLite is not affected by the number of rows in the current table, that is, the time required to modify a table with 10 million rows of data is almost the same as the time required to modify a table with only one row of data.

Iii. Table deletion:

If a table is deleted in SQLite, the related indexes and triggers are also deleted. This is not allowed in many other relational databases. To delete related objects, you must add the with cascade clause to the delete TABLE statement. See the following example:
SQLite> Create Table testtable (first_col integer );
SQLite>Drop tableTesttable;
SQLite>Drop tableTesttable;
Error: no such table: testtable
SQLite>Drop table if existsTesttable;
As shown in the preceding example, if the table to be deleted does not exist, SQLite reports an error and outputs an error message. If you do not want to throw an exception during execution, you can add the if exists clause. The syntax of this clause is exactly the same as that in create table.

4. Create a view:

Here is just a simple SQL command example. For the specific meaning and technical details, refer to the section above for creating data tables, such as temporary views and "if not exists" clauses.
1). the simplest View:
SQLite>Create ViewTestview
AsSelect * From testtable where first_col> 100;

2). Create a temporary View:
SQLite>Create temp ViewTempview
AsSelect * From testtable where first_col> 100;

3). "If not exists" clause:
SQLite>Create ViewTestview
AsSelect * From testtable where first_col> 100;
Error: Table testview already exists
SQLite>Create view if not existsTestview
AsSelect * From testtable where first_col> 100;

5. Delete A View:

The syntax for this operation is basically the same as that for deleting a table, so here is just an example:
SQLite>Drop ViewTestview;
SQLite>Drop ViewTestview;
Error: no such view: testview
SQLite>Drop view if existsTestview;

 

C: \> sqlite3 mydatabase. DB
SQLite> Create Table user (ID integer, username text, Password text );
SQLite> insert into user values (1, 'King', 'King ');
SQLite> select * from user;
SQLite> Update user set username = 'Kong ', password = 'Kong' Where id = 1;
SQLite> Delete from user where username = 'Kong ';