First, create the data table:
The syntax rules and usage of this command are essentially the same as most relational databases, so we illustrate the various rules for creating tables in SQLite. However, for some sqlite-specific rules, we will give additional instructions. Note: All of the following examples are done in SQLite with a command-line tool.
1. The simplest data table:
Copy Code code as follows:
Sqlite> CREATE TABLE testtable (First_col integer);
What you need to note here is that for a custom datasheet table name, such as TestTable, you cannot start with a sqlite_, because the table names defined with that prefix are used inside SQLite.
2. Create a data table with default values:
Copy Code code as follows:
Sqlite> CREATE TABLE testtable (First_col integer default 0, second_col varchar default ' hello ');
3. Create a table in the specified database:
Copy Code code as follows:
sqlite> ATTACH DATABASE ' d:/mydb.db ' as mydb;
Sqlite> CREATE TABLE mydb.testtable (First_col integer);
This is the first way to attach an existing database file into the current connection by using the Attach Database command, and then create the data table in the target database, such as mydb.testtable, by specifying the name of the databases. There are additional instructions for this rule, and if we do not specify a database name when we create the datasheet, the table will be created in the main database of the current connection, with only one main database in a connection. If you need to create a temporary table, you do not need to specify a database name, as shown in the following example:
--Create two tables, a temporary table, and a normal table.
Copy Code code as follows:
sqlite> CREATE TEMP TABLE temptable (First_col integer);
Sqlite> CREATE TABLE testtable (First_col integer);
-Export cached data from the current connection to a local file, while exiting the current connection.
Copy Code code as follows:
Sqlite>. Backup D:/mydb.db
Sqlite>. Exit
--Re-establish the SQLite connection and re-import the database that you just exported as the main library.
Copy Code code as follows:
sqlite>. Restore D:/mydb.db
--View the table information in the database, and you can see from the results that the temporary table is not persisted to the database file.
Copy Code code as follows:
Sqlite>. Tables
TestTable
4). "IF not EXISTS" clause:
If the currently created data table name already exists, which conflicts with the table name, view name, and index name that already exists, the create operation will fail with an error. However, if you add an "IF not EXISTS" clause to the table, the create operation will not have any effect, that is, no errors will be thrown unless the current table name and an index name conflict.
Copy Code code 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:
The data table created in this manner has the same schema information as the result set returned by the Select query, but does 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.
Copy Code code 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 for the Sqlite3 command-line tool that displays the CREATE TABLE statement for the current datasheet.
6). PRIMARY KEY constraint:
Copy Code code as follows:
--Specify the primary key directly on the definition of the field.
Sqlite> CREATE TABLE testtable (first_col integer PRIMARY KEY ASC);
-After all the fields have been defined, define the number constraints for the table, which defines a federated 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)
...>);
As with other relational databases, primary keys must be unique.
7). Uniqueness Constraint:
Copy Code code as follows:
--Specify uniqueness constraints directly on the definition of a field.
Sqlite> CREATE TABLE testtable (First_col integer UNIQUE);
-After all the fields have been defined, the uniqueness constraint for the table is defined, which is defined as a 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, including 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, the null value of two inserts is inserted successfully.
8). Null (NOT NULL) constraint:
Copy Code code as follows:
Sqlite> CREATE TABLE testtable (First_col integer not NULL);
Sqlite> INSERT into TestTable VALUES (NULL);
Error:testtable.first_col May is NULL
As you can see from the output, First_col has been defined as a non-empty constraint, so you cannot insert a null value.
9). Check constraint:
Copy Code code 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 violation of the CHECK Constraint for field first_col (First_col < 5)
Error:constraint failed
-As with any previous constraint, an inspection constraint can also be defined based on multiple columns in a table.
Sqlite> CREATE TABLE Testtable2 (
...> First_col Integer,
...> Second_col Integer,
...> CHECK (First_col > 0 and Second_col < 0)
...>);
Second, the table modification:
SQLite has very limited support for the ALTER TABLE command simply by modifying the table name and adding a new field. Other features, such as renaming a field, deleting a field, and adding a delete constraint, provide support.
1). Modify Table Name:
It should be noted that the table name modification in SQLite can only be in the same database and cannot be moved to the attached database. And again, once the table name has been modified, the index that already exists on the table will not be affected, but views and triggers that depend on the table will have to modify its definition.
Copy Code code as follows:
Sqlite> CREATE TABLE testtable (First_col integer);
sqlite> ALTER TABLE testtable RENAME to Testtable2;
Sqlite>. Tables
Testtable2
The output from the tables command shows that the table testtable has been modified to testtable2.
2). New fields:
Copy Code code 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 new fields are already included in the definition of table testtable.
The last thing you need to know 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, it takes almost the same amount of time to modify a table with 10 million rows of data and to modify a table that has only one piece of data.
third, the deletion of the table:
In SQLite, if a table is deleted, the indexes and triggers associated with it are also deleted. In many other relational databases, this is not possible, and if you have to delete related objects, you can only add the WITH CASCADE clause in the DELETE table statement. See the following example:
Copy Code code 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 you can see from the example above, if the deleted table does not exist, SQLite will complain and output the error message. If you want to execute without throwing an exception, we can add an IF EXISTS clause that has the same semantics as the CREATE table.
Create a view:
We are here to give a simple example of a SQL command, and the specific meaning and technical details can refer to the above creation Datasheet section, such as temporary view, "if not EXISTS" clause, and so on.
1. The simplest view:
Copy Code code as follows:
Sqlite> CREATE VIEW TestView as SELECT * from TestTable WHERE first_col > 100;
2). Create a temporary view:
Copy Code code as follows:
sqlite> CREATE TEMP VIEW Tempview as SELECT * from TestTable WHERE first_col > 100;
3). "IF not EXISTS" clause:
Copy Code code 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;
v. Delete view:
The syntax of the operation is essentially the same as the delete table, so here's just a sample presentation:
Copy Code code as follows:
sqlite> DROP VIEW TestView;
sqlite> DROP VIEW TestView;
Error:no such View:testview
sqlite> DROP VIEW IF EXISTS TestView;
