SQLite Getting Started tutorial two SQLite Create, modify, delete table _sqlite

One, database definition language DDL

In a relational database, table tables, view views, index indices, relational relationship, and trigger Trigger in a database form the schema of the database. In SQL statements, there are specific statements that define the database schema, which is referred to as the database definition language, or DDL.

The SQLite database engine supports the following three types of DDL statements:

Copy Code code as follows:

CREATE
ALTER TABLE
DROP

Where the Create statement creates table tables, view views, index indexes, relational relationship, and trigger Trigger, which are used to delete table tables, view views, index indices, relationships relationship and trigger Trigger, ALTER TABLE statements are used to alter the structure of the table.

Today this article only involves the relevant content of the table, the view, triggers wait until later.

Ii. data types in SQLite

The data in the SQLite database is typically composed of several commonly used data types:

Null-null value
Integer-Signed integer
Real-floating-point numbers
Text-literal string
BLOB-binary data, such as pictures, sounds, and so on
SQLite can also accept other data types.

Third, CREATE table

First, create a test.db database and go into the SQLite command line environment, remember what to do?

Copy Code code as follows:

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 a ";"
Sqlite>. Tables
Sqlite>

In this way, we create a test.db database in the terminal and query the tables in the database with the. Tables command, and the results don't return because the database is empty.

Below we create a Student table that contains fields such as Id, Name, age, and so on.

Copy Code code as follows:

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>

To the above, a Students table is established, which is then run again. The tables command responds, and the system tells us that there is a Students table in the database, which runs the. Schema command, and returns the SQL command that we created this table.

modifying table alter Tables

SQLite only supports part of the ALTER TABLE statement, we can change the name of a table with the ALTER TABLE statement, or add a field (column) to the table, but we cannot delete a field that already exists, or change the name, data type of a field that already exists , qualifiers, and so on.

ALTER TABLE name-ALTER TABLE old table name RENAME to new table name
Add one column-ALTER table name add column name data type qualifier
Let's show you the name of the previous Students table as Teachers

Copy Code code as follows:

Sqlite>
Sqlite>. Tables
Students
sqlite> ALTER TABLE Students RENAME to Teachers;
Sqlite>. Tables
Teachers
Sqlite>

The original database has only one Students table, renamed and then run. Tables command, found that the Students table has gone, and now becomes the Teachers table.

The following changes the structure of the Teachers table, adding a Sex column

Copy Code code as follows:

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>

v. Delete Table drop tables

Deleting a table is simple, just give the table name

Delete table-drop table table name
Below, we remove the Teachers table from the Test.db

Copy Code code as follows:

Sqlite>
Sqlite>. Tables
Teachers
sqlite> DROP TABLE Teachers;
Sqlite>. Tables
Sqlite>

Delete the Teachers table and then run the. Tables command to discover that the database is empty.

Vi. subsequent content

In fact, creating a table is far less simple, each column of the table can have many qualifiers, such as the main column, Non-empty, restrictions, defaults, unique, keys, and so on, and so on, leave it to the next article