Sqlite Study Notes 4: Create and Delete tables and sqlite Study Notes

Sqlite Study Notes 4: Create and Delete tables and sqlite Study Notes

If you have done so much, isn't it just to get a few tables for data? Let's take a look at how to create a new table.

1. Create a table

The basic syntax is as follows:

CREATE TABLE database_name.table_name(   column1 datatype  PRIMARY KEY(one or more columns),   column2 datatype,   column3 datatype,   .....   columnN datatype,);

Specifically, database_name is your database name. table_name is of course the table name, columnN is the name, PRIMARY_KEY is the primary key, and datatype is the data type of this column.

For example, to create a user table:

CREATE TABLE USER(   ID INT PRIMARY KEY     NOT NULL,   NAME           TEXT    NOT NULL,   AGE            INT     NOT NULL,   ADDRESS        CHAR(50));

# "Not null" indicates that the current Column cannot be blank

After the new table is created, run the following command to view the new table:

.tables

To view the detailed structure of a table, run the following command:

.schema

Ii. delete a table

Run the following command:

DROP TABLE database_name.table_name;

How can I delete table fields in sqlite3? I don't want to delete the table fields in the visual interface. I want to delete the table fields directly by using the command below, but the syntax is incorrect.

Sqlite does not support column deletion. You can do this:
1. Create a publisher_temp table based on the Publisher table (excluding the state_province field)

2. copy the data to publisher_temp.
3. Delete the Publisher table

4. Rename the publisher_temp table

How does sqlite3 delete a specified row of data? (If you want to delete the fifth row of data in a data table)

Delete the fifth row of data in the tmp table
Delete from tmp where rowid in (select rowid from tmp limit 1 offset 4 );