SQLite Data tables and views

First, create a data table:

    The syntax rules and usage of this command are basically the same as those of most relational databases, so we will use examples to demonstrate the various rules for creating tables in SQLite. But for some SQLite-specific rules, we will give additional explanation. Note: All the examples below are done in the command line tool that comes with sqlite.
    1). The simplest data table:
    sqlite> CREATE TABLE testtable (first_col integer);
    What needs to be explained here is that for custom data table table names, such as testtable, cannot start with sqlite_, because the table names defined by this prefix are used inside sqlite.

    2). Create a data table with default values:
    sqlite> CREATE TABLE testtable (first_col integer DEFAULT 0, second_col varchar DEFAULT ‘hello’);

    3). Create a table in the specified database:
    sqlite> ATTACH DATABASE ‘d: /mydb.db’ AS mydb;
    sqlite> CREATE TABLE mydb.testtable (first_col integer);
    Here an ATTACH DATABASE command is used to attach an existing database file to the current connection, and then a data table is created in the target database by specifying the database name, such as mydb.testtable. There are some additional instructions about this rule. If we did not specify a database name when creating the data table, the table will be created in the main database of the current connection, and there can only be one main database in a connection. If you need to create a temporary table, you do not need to specify the database name, as shown in the following example:
    --Create two tables, a temporary table and an ordinary table.
    sqlite> CREATE TEMP TABLE temptable (first_col integer);
    sqlite> CREATE TABLE testtable (first_col integer);
    -Export the cached data in the current connection to a local file, and exit the current connection.
    sqlite> .backup d: /mydb.db
    sqlite> .exit
    -Re-establish the sqlite connection, and re-import the database just exported as the main library.
    sqlite> .restore d: /mydb.db
    --Check the table information in the database, and you can see from the results that the temporary table has not been persisted to the database file.
    sqlite> .tables
    testtable

    4). "IF NOT EXISTS" clause:
    If the currently created data table name already exists, that is, it conflicts with the existing table name, view name, and index name, then this create operation will fail with an error. However, if the "IF NOT EXISTS" clause is added when creating a table, this create operation will have no effect, that is, no error will be thrown unless the current table name conflicts with an index name.
    sqlite> CREATE TABLE testtable (first_col integer);
    Error: table testtable already exists
    sqlite> CREATE TABLE IF NOT EXISTS testtable (first_col integer);

    5). CREATE TABLE ... AS SELECT:
    The data table created in this way will have the same Schema information as the result set returned by the SELECT query, but will not contain constraint information such as default values and primary keys. However, the newly created table will contain all the data returned by the result set.
    sqlite> CREATE TABLE testtable2 AS SELECT * FROM testtable;
    sqlite> .schema testtable2
    CREATE TABLE testtable2 (first_col INT);
    The .schema command is a built-in command of the sqlite3 command line tool for displaying the CREATE TABLE statement of the current data table.

    6). Primary key constraints:
    -Specify the primary key directly on the field definition.
    sqlite> CREATE TABLE testtable (first_col integer PRIMARY KEY ASC);
    -After all the fields have been defined, then define the number constraints of the table. What is defined here is the joint primary key based on first_col and second_col.
    sqlite> CREATE TABLE testtable2 (
       ...> first_col integer,
       ...> second_col integer,
       ...> PRIMARY KEY (first_col, second_col)
       ...>);
    Like other relational databases, the primary key must be unique.

    7). Uniqueness constraint:
    --Specify unique constraints directly on the definition of the field.
    sqlite> CREATE TABLE testtable (first_col integer UNIQUE);
    -After all the fields have been defined, define the uniqueness constraint of the table. What is defined here is the uniqueness constraint based on two columns.
    sqlite> CREATE TABLE testtable2 (
       ...> first_col integer,
       ...> second_col integer,
       ...> UNIQUE (first_col, second_col)
       ...>);
    In SQLite, a NULL value is considered to be different from any other value, so it includes and other NULL values, as 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 NULL values inserted twice were successfully inserted.

    8). NOT NULL constraint:
    sqlite> CREATE TABLE testtable (first_col integer NOT NULL);
    sqlite> INSERT INTO testtable VALUES (NULL);
    Error: testtable.first_col may not be NULL
    As can be seen from the output, first_col has been defined with a non-null constraint, so you cannot insert NULL values.

    9). Checking constraints:
    sqlite> CREATE TABLE testtable (first_col integer CHECK (first_col <5));
    sqlite> INSERT INTO testtable VALUES (4);
    sqlite> INSERT INTO testtable VALUES (20);-20 violates the check constraint of the field first_col (first_col <5)
    Error: constraint failed
    -As with other constraints, check constraints can be defined based on multiple columns in the table.
    sqlite> CREATE TABLE testtable2 (
       ...> first_col integer,
       ...> second_col integer,
       ...> CHECK (first_col> 0 AND second_col <0)
       ...>);

Modification of the table:

    SQLite has very limited support for the ALTER TABLE command, just modifying the table name and adding new fields. Other functions, such as renaming fields, deleting fields, and adding and removing constraints, are all supported.
    1). Modify the table name:
    It should be noted that the table name modification in SQLite can only be in the same database, and it cannot be moved to the Attached database. Another is that once the table name is modified, the existing indexes of the table will not be affected, but the views and triggers that depend on the table will have to redefine their definitions.
    sqlite> CREATE TABLE testtable (first_col integer);
    sqlite> ALTER TABLE testtable RENAME TO testtable2;
    sqlite> .tables
    testtable2
    As can be seen from the output of the .tables command, the table testtable has been modified to testtable2.

    2). New fields:
    sqlite> CREATE TABLE testtable (first_col integer);
    sqlite> ALTER TABLE testtable ADD COLUMN second_col integer;
    sqlite> .schema testtable
    CREATE TABLE "testtable" (first_col integer, second_col integer);
    As can be seen from the output of the .schema command, the definition of the table testtable already contains new fields.
    The last thing to note about ALTER TABLE is that the execution time of this command in SQLite will not be affected by the number of rows in the current table. The time is almost equal.
    
Third, the deletion of the table:

    If a table is deleted in SQLite, the indexes and triggers associated with it will also be deleted. This is not possible in many other relational databases. If you must delete related objects, you can only add a 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> DROP TABLE IF EXISTS testtable;
    As can be seen from the above example, if the deleted table does not exist, SQLite will report an error and output an error message. If we want to not throw an exception during execution, we can add an IF EXISTS clause, the semantics of which are exactly the same as in CREATE TABLE.
    
Fourth, create a view:

    Here we just give a simple example of SQL commands. For specific meaning and technical details, please refer to the above section of creating data tables, such as temporary views, "IF NOT EXISTS" clauses, etc.
    1). The simplest view:
    sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col> 100;
    
    2). Create Temporary view:
     sqlite> CREATE TEMP VIEW tempview AS SELECT * FROM testtable WHERE first_col> 100;
    
     3). "IF NOT EXISTS" clause:
     sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col> 100;
     Error: table testview already exists
     sqlite> CREATE VIEW IF NOT EXISTS testview AS SELECT * FROM testtable WHERE first_col> 100;
    
Five, delete the view:

     The syntax of this operation is basically the same as deleting the table, so here is just an example:
     sqlite> DROP VIEW testview;
     sqlite> DROP VIEW testview;
     Error: no such view: testview
     sqlite> DROP VIEW IF EXISTS testview;

Category: SQLite

SQLite Data tables and views