SQLite getting started tutorial (2) creating, modifying, and deleting tables

I. Database 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", that isDDL. 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, Drop statements are used to delete tables, views, index indexes, relational relationship, and trigger triggers, 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/. sqlitercsqlite version 3.7.4enter ". Help" for instructionsenter SQL statements terminated with ";"SQLite>. TablesSQLite>
 

In this way, we created a test. DB database in the terminal,
The. Tables command is used to query tables in the database. No results are returned,
Because the database is 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>. TablesStudentsSQLite>. Schema studentsCreate Table students (ID integer, Name text, age integer );SQLite>
 

As shown in the preceding figure, a students table is created, and the response is returned when you run the. Tables command again,
The system tells us that there is a students table in the database,
Run the. schema command and return 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 TABLEOld table nameRenameNew table name
• Add a column-ALTER TABLETable NameAdd ColumnColumn Name Data Type qualifier

Next, we will demonstrate how to change the name of the Students table to teachers.

 
SQLite>SQLite>. TablesStudentsSQLite>Alter table students Rename to teachers;SQLite>. TablesTeachersSQLite>
 

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 teachersCreate Table "Teachers" (ID integer, Name text, age integer );SQLite>Alter table teachers add column sex text;SQLite>. Schema teachersCreate 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 tableTable Name

Next, we will delete the teachers table in test. DB.

SQLite>SQLite>. TablesTeachersSQLite>Drop table teachers;SQLite>. TablesSQLite>
 

Delete the teachers table and run the. Tables command again. The database is empty.

Vi. Subsequent content

In fact, creating a table is far from that simple. Each column of the table can have many delimiters, such as primary columns, non-empty columns, restrictions, default values, unique columns, and keys. Keep the content in the next article.

// ================================================ ========================================================== ==================================