How to create, modify, and delete a table in SQLite (2)

How to create, modify, and delete a table in SQLite (2)

I. Database tutorial Definition Language DDL
In relational databases, tables, views, Index indexes, relational Relationship, and Trigger triggers in databases constitute the Schema of databases. In SQL statements, some specific statements are used to define the database architecture. These statements are called "database definition language" (DDL.

The SQLite Database Engine supports the following three DDL statements:

CREATE
ALTER TABLE
DROP
The CREATE statement is used to CREATE a Table, View, Index, relational Relationship, and Trigger. The DROP statement is used to delete a Table, View, Index, relational Relationship, and Trigger, the alter table statement is used to change the TABLE structure.

Today, this article only involves table-related content. Views and triggers will be discussed later.

Ii. Data Types in SQLite
Data in the SQLite database is generally composed of the following common data types:

NULL-NULL
INTEGER-signed INTEGER
REAL-floating point number
TEXT-TEXT string
BLOB-binary data, slices, sounds, and so on
SQLite can also accept other data types.

3. CREATE TABLE
First, create a test. db database and enter the SQLite command line environment. Do you still remember how to do this?

Myqiao @ ubuntu :~ $ Sqlite3 test. db
-- Loading resources from/home/myqiao/. sqliterc
SQLite version 3.7.4
Enter ". help" for instructions
Enter SQL statements terminated with ";"
Sqlite>. tables
Sqlite>

In this way, we created a test. db database in the terminal and used the. tables command to query the tables in the database. The results were not returned because the database was empty.

Next we create a Student table, which contains fields such as Id, Name, and Age.

Sqlite>
Sqlite> create table Students (Id integer, Name text, age integer );
Sqlite>. tables
Students
Sqlite>. schema Students
Create table Students (Id integer, Name text, age integer );
Sqlite>

In this way, a Students table is created and runs again. the tables command returns a response. The system tells us that there is a Students table in the database and runs it. the schema command returns the SQL command for creating the table.

4. ALTER TABLE
SQLite only supports part of the alter table statement. We can use the alter table statement to change the name of a TABLE or add a field (column) to the TABLE ), however, you cannot delete an existing field, or change the name, data type, and qualifier of an existing field.

Change TABLE name-alter table old TABLE name rename to new TABLE name
ADD a COLUMN-alter table name add column data type qualifier
Next, we will demonstrate how to change the name of the Students table to Teachers.

Sqlite>
Sqlite>. tables
Students
Sqlite> alter table Students rename to Teachers;
Sqlite>. tables
Teachers
Sqlite>

In the original database, there was only one Students table. After the name was changed, run the. tables command again. It was found that the Students table was gone and now it is changed to the Teachers table.

Next we will change the structure of the Teachers table and add a Sex column.

Sqlite>
Sqlite>. schema Teachers
Create table "Teachers" (Id integer, Name text, age integer );
Sqlite> alter table Teachers add column Sex text;
Sqlite>. schema Teachers
Create table "Teachers" (Id integer, Name text, age integer, Sex text );
Sqlite>

5. DROP TABLE
It is easy to delete a table, as long as the table name is given.

Delete TABLE-drop table Name
Next, we will delete the Teachers table in test. db.

Sqlite>
Sqlite>. tables
Teachers
Sqlite> drop table Teachers;
Sqlite>. tables
Sqlite>

Delete the Teachers table and run the. tables command again. The database is empty.