SQLite Learning Manual (datasheets and views)

How to list all tables in a SQLite database

The information in the SQLite database exists in a built-in table sqlite_master that can be used in a query

SELECT * from Sqlite_master to see if all table names are listed, just one statement:

SELECT name from Sqlite_master WHERE type= ' table ' ORDER by name because the table column type is fixed to ' table '

First, create the data table:

The syntax and usage of this command is basically the same as for most relational databases, so let's demonstrate the various rules for creating tables in SQLite in a sample way. But for some sqlite-specific rules, we will give additional instructions. Note: All of the following examples are done in SQLite's own command-line tool.
1). The simplest data sheet:
Sqlite>CREATE TABLETestTable (First_col integer);
It is necessary to note that for custom data table table names, such as TestTable, you cannot start with Sqlite_ because the table names defined in this prefix are used within 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 the first way to attach an existing file into the current connection before creating a data table in the target database by specifying the name of the database, such as mydb.testtable. There is some additional explanation for this rule, and if we do not specify a database name when we create the data table, the table will be created in the main database of the current connection, and there can be only one main database in a connection. If you need 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 normal table.
Sqlite>CREATE TEMP TABLETempTable (First_col integer);
Sqlite>CREATE TABLETestTable (First_col integer);
--exports the cached data from the current connection to a local file and exits the current connection.
Sqlite>. BackupD:/mydb.db
Sqlite>. Exit
-Re-establish the connection to SQLite and re-import the database you just exported as the main library.
Sqlite>. RestoreD:/mydb.db
--View the table information in the database, and the result shows that the temporary table is not persisted to the database file.
Sqlite>. Tables
TestTable

4). "IF not EXISTS" clause:
If the currently created data table name already exists, which conflicts with the existing table name, view name, and index name, the creation will fail with an error. However, if you add an "if not EXISTS" clause when creating the table, the creation will have no effect, that is, there will be no error 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:
Data tables created in this manner will have the same schema information as the result set returned by the Select query, but do 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 TABLETesttable2As SELECT* fromTestTable;
Sqlite>. SchemaTesttable2
CREATE TABLE testtable2 (First_col INT);
The. Schema command is a built-in command for the Sqlite3 command-line tool that displays the CREATE TABLE statement for the current data table.

6). PRIMARY KEY constraint:
--Specify the primary key directly on the definition of the field.
Sqlite>CREATE TABLETestTable (First_col integerPRIMARY KEY ASC);
-After all the fields have been defined, define the number constraints for the table, which defines the federated primary key based on First_col and Second_col.
Sqlite>CREATE TABLETesttable2 (
...> First_col Integer,
...> Second_col Integer,
...>PRIMARY KEY(First_col,second_col)
...>);
As with other relational databases, the primary key must be unique.

7). Uniqueness Constraints:
--Specify uniqueness constraints directly on the definition of the field.
Sqlite>CREATE TABLETestTable (First_col integerUNIQUE);
--After all the fields have been defined, in defining the uniqueness constraints of the table, this is defined as a Uniqueness constraint based on two columns.
Sqlite>CREATE TABLETesttable2 (
...> First_col Integer,
...> Second_col Integer,
...>UNIQUE(First_col,second_col)
...>);
In SQLite, a null value is considered different from any other value, so include 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
Thus, a null value of two insertions is inserted successfully.

8). A null (NOT NULL) constraint:
Sqlite>CREATE TABLETestTable (First_col integerNot NULL);
Sqlite> INSERT into TestTable VALUES (NULL);
Error:testtable.first_col May is not NULL
As can be seen from the output, First_col has been defined as a non-null constraint, so null values cannot be inserted.

9). Check the constraint:
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 CHECK Constraint for field first_col (First_col < 5)
Error:constraint failed
--As with the previous constraints, the check constraint 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)
...>);

second, the modification of the table:

SQLite has very limited support for ALTER TABLE commands, simply by modifying the table name and adding new fields. Other features, such as renaming a field, deleting a field, and adding a delete constraint, are all supported.
1). Modify the table name:
It should be noted that the modification of table names in SQLite is only possible in the same database and cannot be moved to the attached database. And then again, once the table name is modified, the indexes that already exist on the table will not be affected, but the views and triggers that depend on the table will have to re-modify their definitions.
Sqlite> CREATE TABLE testtable (First_col integer);
Sqlite>ALTER TABLETestTableRENAME toTesttable2;
Sqlite>. Tables
Testtable2
The output from the. Tables command shows that the table testtable has been modified to testtable2.

2). New field:
Sqlite> CREATE TABLE testtable (First_col integer);
Sqlite>ALTER TABLETestTableADD COLUMNSecond_col integer;
Sqlite>. SchemaTestTable
CREATE TABLE "TestTable" (First_col integer, Second_col integer);
As you can see from the output of the. Schema command, the new fields are already included in the definition of table testtable.
The last thing to note about ALTER TABLE is that the execution time of the 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 and to modify a table with only one data is almost equal.

third, the deletion of the table:

In SQLite, if a table is deleted, the associated indexes and triggers are deleted as well. In many other relational databases, this is not possible, and if you have to delete related objects, you can only include the WITH CASCADE clause in the DELETE table statement. See the example below:
Sqlite> CREATE TABLE testtable (First_col integer);
Sqlite>DROP TABLETestTable;
Sqlite>DROP TABLETestTable;
Error:no such table:testtable
Sqlite>DROP TABLE IF EXISTSTestTable;
As can be seen from the above example, if the deleted table does not exist, SQLite will error and output the wrong message. If you want to execute without throwing an exception, we can add an IF EXISTS clause that has the same semantics as in CREATE table.

Iv. Creating a View:

Here we just give a simple example of SQL commands, the specific meaning and technical details can be referenced above to create a data table section, such as temporary view, "if not EXISTS" clause and so on.
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 EXISTS testview as SELECT * from TestTable WHERE first_col > 100;

V. Delete a view:

The syntax for this operation is basically the same as the delete table, so here's just an example:
sqlite> DROP VIEW TestView;
sqlite> DROP VIEW TestView;
Error:no such View:testview
sqlite> DROP VIEW IF EXISTS testview;

SQLite Learning Manual (datasheets and views)