Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the operations of Sqlite database tables". In the daily operation, I believe that many people have doubts about the operation of Sqlite database tables. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what is the operation of Sqlite database tables?" Next, please follow the editor to study!

1)。 Create data table CREATE TABLE student (ID INTEGER DEFAULT '1406061' PRIMARY KEY AUTOINCREMENT NOT NULL, NAME NVARCHAR (100) UNIQUE NOT NULL, score INTEGER NOT NULL, time TIMESTAMP NOT NULL)

We can use SqliteAdmin to generate tables quickly, as follows:

In this way, our watch has been successfully established. Here the editor uses the data file DEMO in the software and the database file TEST in the command window, because a database file is not allowed to be opened in two locations.

2)。 Delete table DROP TABLE people;3). Show table name .tables

4)。 Addition, deletion, modification and query of data sheet

1). Increase

Here we can add a piece of data to the table we just created, as follows:

Insert into student (ID,NAME,score,time) values (1406063 boy',90,'2020 'wayward post-90s boy',90,'2020-07-06 12)

Here I inserted three rows of data, one of which reported an error because I used the same ID number, and the ID number of my database is not allowed to repeat, so it will report an error and change it to something else. If you set ID to self-increase, you don't need to write it. Here I use self-increment, so you don't have to write Sqlite that will be filled in automatically. You can also omit the field name and write the value directly, as follows:

We can also add new columns to the table, as follows

Alter table people add column aa char (10); # add a column and the column name is aa

Note: Sqlite3 does not support columns with UNIQUE constraints

2). Delete

Delete from people;# deletes table data delete from people where score10 group by age having count (NAME) > 1 TX # name count greater than 15). Rename alter table people rename to man;# of the table change the table name people to man

6)。 Connection of data table

The connection here is divided into three kinds of internal and external cross connections, which can greatly improve our work efficiency. Here we create a table again, as follows:

1). Cross connection

Select * from people cross join woman

2). Internal connection

Select * from people inner join woman

3). External connection

Select * from people outer join woman

Note: Sqlite3 only supports left outer connection.

7)。 Create table man as select * from people where 1 replication # replication table structure create table woman as select * from people;# replication table structure and data 8). Temporary watch

Sometimes we don't need to store all the table data, so it's necessary to use temporary tables. As follows:

# create a temporary table create temporary table temp_table (id int primary key, name varchar (50) unique not null, age int not null); # View the temporary table temp.temp_table # delete the temporary table drop table temp.temp_table;5. Index operation 1). Create the index create index user on people (score); # set the index create unique name on people (NAME) on the score field of the people table; # set the unique index create index pa on people (score,age) on the score field of the people table # set index 2 on the score and age fields of the people table. Check the index SELECT * FROM sqlite_master WHERE type = 'index'

Here we just created an index before, that is "user", why there are two indexes, and the top index does not seem to have anything to do with us, nor does it seem to be created by ourselves, in fact, this is the implicit index, which was created with it when we created the table, just to make the query faster, with little impact.

3)。 Use index

The two keywords involved here should be used together to indicate where the index comes from.

Select * from people indexed by user where score > 10 × 4). Delete index drop index user

Note: the index should not be used when the amount of data is small, so as not to pressurize the system.

6. View operation 1). Create view create view name as select NAME from people;# create view 2 of the NAME field. Use View select * from name

This allows you to directly output all the values of the view field.

3)。 Delete the view drop view name;7. Trigger

Trigger is to enhance the interaction of multiple tables, call each other, and invoke which table if the conditions are met. The syntax format is as follows:

Create trigger trigger after (before) insert (delete update) on Table 1 begin insert into Table 2 (Table 2 fields, Table 2 fields, n) VALUES (Table 1 values, Table 1 values, n); END;1). Create trigger # create a trigger create trigger cf after insert on people begin insert into woman for the people table values (new.ID,' insert forward', 100 journal datetime ('now'); end;2). View trigger select * from sqlite_master where type = 'trigger'AND tbl_name='people';# View trigger 3 of the people table. Delete trigger drop trigger cf;8. Business

With transactions, we can make our Sqlite statements work in a more orderly manner, which is generally divided into the beginning of the transaction, commit and rollback. Let's take a look at:

Begin;# starts insert into people ('gf',65,datetime (' now')); rollback; # rollback is to undo COMMIT; submission to save data end;# is over, and the study on "what are the operations of Sqlite database tables" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.