Creation, destruction and modification of sqlite-sql-tables

To Create a table:

creat table <table_name> (field Type,field type..);

sqlite3 types of data stored

null: Identifies a null value

interger: Integer type

REAL: Floating point

TEXT: String

BLOB: Binary number

sqlite3 constraints for storing data
The usual constraints for SQLite are as follows:
PRIMARY key -PRIMARY key:---: Understanding: Data for some format, attribute (e.g. ID)
1) The value of the primary key must be unique to identify each record, such as student's number
2) Primary key is also an index, the primary key to find records faster
3) If the primary key is an integer type, the value of the column can grow automatically
Not null-non-null:
Constraint column record cannot be empty, otherwise an error
Unique-Single:
The value of the data that constrains the other columns is unique except for the primary key
Check-condition checking:
The value that constrains the column must meet the criteria before it can be deposited
Default-Defaults:
The values in the column data are basically the same, so the field column can be set to the default value

Destroy table:

Drop <table_name>;

Change table name:

ALTER TABLE <table_name> Rename to <new_name>;

To Modify a table structure:

To add a column:

ALTER TABLE <table_name> add column <field>;
To delete a column:

(because Sqlite3 does not support drop column, see the end of this article for details)

Operation Steps:

A.alter table people Rename to temp;
B.create table People (id,name,age);

C.insert to people select Id,name,age from temp;

----------------------------------------------------------------------------

For more information, look down:

Sqlite--record: PRIMARY KEY constraint-uniqueness test

T_table3:

Sqlite> CREATE TABLE T_table3 (gas_1_0 real,gas_2_0 real,primary key (GAS_1_0,GAS_2_0));

sqlite> INSERT into t_table3 values (0.9,2.2);

sqlite> INSERT into t_table3 values (1.2,2.0);

sqlite> INSERT into t_table3 values (1.2,1.9);

Sqlite> select * from T_table3;

Gas_1_0 Gas_2_0

----------  ----------

0.9 2.2

1.2 2.0

1.2 1.9

sqlite> INSERT into t_table3 values (1.2,1.9);

Error:unique constraint Failed:t_table3.gas_1_0, t_table3.gas_2_0

gasdata_table:

Sqlite> CREATE TABLE gasdata_table as SELECT * from T_table3;

Sqlite> SELECT * from gasdata_table

...>;

Gas_1_0 Gas_2_0

----------  ----------

0.9 2.2

1.2 2.0

1.2 1.9

sqlite> INSERT into gasdata_table values (1.2,1.9);

Sqlite> select * from Gasdata_table;

Gas_1_0 Gas_2_0

----------  ----------

0.9 2.2

1.2 2.0

1.2 1.9

1.2 1.9

: By Create TABLE ... A data table created as a select method will have the same schema information as the result set returned by the Select query, and will contain all the data returned by the result set, but does not contain constraint information such as default values and primary keys.

-------------------------------------------------------------

Note: (from the network)

Today in the database upgrade, encountered to the original database in a table of a field name to modify, but with:

ALTER TABLE tablename Rename column oldcolumnname to Newcolumnname;

Always unsuccessful, check back for relevant information:

SQLite supports a limited subset of ALTER TABLE. The ALTER Table command in SQLite allows the user to rename a table or to add a new column to an existing TABLE. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

SQLite supports a limited subset of the contents of a change table, meaning that only the user is allowed to rename the table name or add one more column to a table in the command of the SQLite change table. It is not possible to rename a field name and delete a field, or to add and remove parameters that are prescribed by the system.

Workaround:

For example: In the above operation, we added a new field in the People table addr, to delete this field, directly with the SQLite statement can not be completed.

We can do this:

A. Rename the People table to temp;

B. Re-creation of the people table;

C. Copy the contents of the corresponding fields from the temp table into the People table.

D. Deleting a temp table

The operation is as follows:

A.alter table people Rename to temp;

B.create table People (id,name,age);

C.insert to people select Id,name,age from temp;

Creation, destruction and modification of sqlite-sql-tables