SQLite tutorial (III): Introduction to data tables and views, sqlite View

SQLite tutorial (III): Introduction to data tables and views, sqlite View

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:
 The Code is as follows:
Sqlite> create table testtable (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:
The Code is as follows:
Sqlite> create table testtable (first_col integer DEFAULT 0, second_col varchar DEFAULT 'hello ');

3). Create a table in the specified database:
 The Code is as follows:
Sqlite> attach database 'd:/mydb. db' AS mydb;
Sqlite> create table mydb. 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.
The Code is as follows:
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.
 The Code is as follows:
Sqlite>. backup d:/mydb. db
Sqlite>. exit
 
-- Re-establish the sqlite connection and re-import the exported database as the master database.
The Code is as follows:
Sqlite>. restore d:/mydb. db
 
-- View the table information in the database. The result shows that the temporary table is not persisted to the database file.
The Code is as follows:
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.
The Code is as follows:
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:
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.
The Code is as follows:
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 sqlite3 command line tool to display the create table statement of the current data TABLE.

6). Primary Key constraints:
The Code is as follows:
-- 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, define the number constraint of the table. Here we define 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, primary keys must be unique.

7). uniqueness constraints:
The Code is as follows:
-- Specify the uniqueness constraint directly on the field definition.
Sqlite> create table testtable (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 table testtable2 (
...> 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:
The Code is as follows:
Sqlite> create table testtable (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:
The Code is as follows:
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 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 table testtable2 (
...> First_col integer,
...> Second_col integer,
...> CHECK (first_col> 0 AND second_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.
The Code is as follows:
Sqlite> create table testtable (first_col integer );
Sqlite> alter table testtable rename to testtable2;
Sqlite>. tables
Testtable2
 
The output of the. tables command shows that the testtable has been changed to testtable2.

2). Add a field:
 The Code is as follows:
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 );
 
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:
The Code is as follows:
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 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:

The Code is as follows:
Sqlite> create view testview as select * FROM testtable WHERE first_col> 100;

2). Create a temporary View:
The Code is as follows:
Sqlite> create temp view tempview as select * FROM testtable WHERE first_col> 100;

3). "if not exists" clause:
The Code is as follows:
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;

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:
The Code is as follows:
Sqlite> drop view testview;
Sqlite> drop view testview;
Error: no such view: testview
Sqlite> drop view if exists testview;