SQLite learning Manual (data tables and views)

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 integerDefault0, 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 TableTesttable2As 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 integerPrimary 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 integerUnique);
-- 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 integerNot 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 integerCheck(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> 0AndSecond_col <0)
...> );

Ii. Table modification:

SQLite only supports the alter table command by modifying the table name and adding new fields. Other functions, such as renaming fields, deleting fields, and adding and deleting constraints, are supported.
1). Modify the table name:
it must be noted 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 table testtable Rename to testtable2;
SQLite> . tables
testtable2
pass. the output of the tables command shows that the table testtable has been changed to testtable2.

2 ). new field:
SQLite> Create Table testtable (first_col integer );
SQLite> alter table testtable Add column second_col integer;
SQLite> . schema testtable
Create Table "testtable" (first_col inte GER, second_col integer);
the output of the. schema command shows that the testtable definition contains new fields.
the last note about alter table is, in SQLite, the execution time of this command 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.
3. 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 table testtable;
SQLite> drop table testtable;
error: no such table: testtable
SQLite> Dr OP table if exists testtable;
as shown in the preceding example, if the deleted table 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 ViewTestviewAsSelect * From testtable where first_col> 100;

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

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

5. Delete A View:

the syntax of this operation is basically the same as that of deleting a table. Therefore, the following is an example:
SQLite> drop view testview;
SQLite> drop view testview;
error: no such view: testview
SQLite> drop view if exists testview;