SQLite Quick Start 2-create, modify, and delete tables and views

Create, modify, and delete tables, views, and indexes

I. Table Creation

1. Create a table

Create if not exists table student (stuid integer );

2. Create a data table with default values:

Create Table if not exists schooltable (schid integer default 0, schname varchar default 'hz ');

3. Use if not exists

If the table name, view name, and index name already exist, this creation operation will fail. If not exists clause is added, this creation operation will not be affected.

Create Table if not exists studenttest (stuid integer );

4. Primary Key

Create Table if not exists studenttable (stuid integer primary key ASC); Create a primary key

Create Table if not exists studenttable2 (stuid integer, stuname varchar, primary key (stuid, stuname); create a joint primary key

4 unique constraints

Create Table if not exists suttest (stuid integer unique); Create a uniqueness Constraint

5 check Constraints

Create Table if not exists suttest2 (stuid integer, Id integer, check (stuid> 0 and ID <0 ));

 

Ii. Table Modification

1. Modify the table name

Alter table susutest Rename to stutest;

2. Add a new column to the table

Alter table stutest add column stuname varchar;

 

Iii. Table Deletion

Drop table if exists stutest if a table is deleted, the related indexes and triggers are also deleted.

 

4. Create a view

1. Create a simple view

Create view if not exists view_effecate as select * From effecate where corid> 1

2. Create a temporary View

Create temp view tempview_cmdate as select * From cmdate where corid> 1

 

5. delete a view

Drop view if exists view_gateate;

 

6. Create an index

1. The index is based on the corid field of the replicate table.

Create index cor_index on duplicate ate (corid );

2. The index is based on the corid and corname fields of the replicate table and specifies the sorting rules for each field.

Create index cor_index2 on marshate (corid ASC, corname DESC );

3. Create a unique index

Create unique index cor_index3 on duplicate ate (corid ASC, corname DESC );

 

7. delete an index

Drop index if exists cor_index3;

 

8. reindex reconstruction;

Re-indexing is used to delete an existing index and re-indexing the index based on its original rules.

9. Data Analysis analyze;

10. Vacuum;