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

This article mainly introduces "how to use the basic command operation of the embedded database Sqlite3". In the daily operation, I believe that many people have doubts about how to use the basic command operation of the embedded database Sqlite3. The editor consulted all kinds of materials and sorted out a simple and easy-to-use operation method. I hope it will be helpful to answer the doubt of "how to use the basic command operation of the embedded database Sqlite3". Next, please follow the editor to study!

SQLite

SQLite, a lightweight database, is an ACID-compliant relational database management system, which is contained in a relatively small C library.

Author D.RichardHipp

In January 2000, Hipp began discussing with a colleague the idea of creating a simple embedded SQL database that would use the GNU DBM hash library (gdbm) as a background and would not require installation and administrative support. Later, as soon as there was free time, Hipp began to implement this work, and in August 2000, SQLite version 1.0 was released. I hereby offer my knee to the great god.

Its design goal is embedded, and it has been used in many embedded products, it occupies very low resources, in embedded devices, it may only need a few hundred kilogrammes of memory. It can support mainstream operating systems such as Windows/Linux/Unix, and can be combined with many programming languages, such as Tcl, C#, PHP, Java, etc., as well as ODBC interface. It is also faster than the two open source world-famous database management systems, Mysql and PostgreSQL.

SQLite characteristics

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Zero configuration one eliminates the need to install and manage configurations

A complete database stored in a single disk file

Database files can be freely shared among machines with different byte order

Supports database size up to 2TB

Small enough, the total source code is about 30,000 lines of c code, 250KB

It is faster than most popular databases to manipulate data.

Installation

Now it is mainly the sqlite3 version, and it is also very convenient to install under ubuntu.

Sudo apt-get install sqlite sqlite3 installation application sudo apt-get install libsqlite3-dev installation library + header files, operating the database with code must be installed

Check the version number: enter the command sqlite3 to enter the operation interface, enter the command .version, and you can view the detailed version number.

You can also install graphical tools using the following command:

Establishment of database by sudo apt-get install sqlitebrowser graphical tool

In addition, it can also be deployed under windows. This article only discusses how to operate the database on the command line under ubtuntu.

Data type

Operating the database is mainly to manipulate the table, each column of the table has a certain data type, such as integer value, string, Boolean and so on.

The main data types of Sqlite3 are as follows:

The data type definition data type NULL indicates that the value is null. INTEGER unsigned integer value. REAL floating point value. TEXT text string, the encoding used for storage is UTF-8, UTF-16BE, UTF-16LE. BLOB stores Blob data, which is exactly the same as input data, and 1 means true,0 represents false.

Sqlite3 also accepts the following data types:

The data type defines a 16-bit integer. An integer for the interger32 bit. The exact value p of decimal refers to all decimal numbers, and s means that there can be several decimal places after the decimal point. If it is not specifically specified, the system defaults to proom5 swarms 0. The real number of float32 bits. The real number of double64 bits. A string of char (n) n length, n cannot exceed 254. A string whose varchar (n) length is not fixed and its maximum length is n cannot exceed 4000. Graphic (n) is the same as char (n), except that the units are two bytes and n cannot exceed 127s. This form is designed to support two-byte fonts, such as Chinese characters. Vargraphic (n) A double-character string of variable length with a maximum length of n, n cannot exceed 2000date containing year, month, and date. Time contains hours, minutes, and seconds. Timestamp includes year, month, day, hour, minute, second, and 1/1000 seconds.

Constraint

Each column of the table has some restricted properties, such as the data of some columns can not be repeated, some limit the scope of data, and so on. Constraints are used to further describe the data attributes of each column. Common constraints for SQLite databases are as follows:

Name definition NOT NULL- non-empty UNIQUE unique PRIMARY KEY primary key FOREIGN KEY foreign key CHECK conditional check DEFAULT default

Non-empty NOT NULL

There are some fields we may not know what to fill, and it does not set the default value. When adding data, we leave this field blank, and the system thinks it is the NULL value. But there is another type of field that must be filled in with data, and if not, the system will report an error. Such fields are called NOT NULL non-empty fields and need to be declared in advance when the table is defined.

Unique UNIQUE

In addition to the main column, there are some columns that cannot have duplicate values.

Primary key PRIMARY KEY

It is usually an integer or string, as long as it is guaranteed to be unique. In SQLite, if the primary key is of integer type, the value of the column can automatically grow.

Foreign key FOREIGN KEY

We already have a Teachers table in our database. If we create another Students table, every student in the Students table is required to correspond to a teacher in the Teachers table. Quite simply, you only need to set up a TeacherId field in the Students table and save the corresponding teacher's Id number, so that a relationship is established between the student and the teacher. The problem is that we may save a TeacherId value that is not in the Teachers table for the student, and we will not be able to find this error. In this case, you can declare the TeacherId field in the Students table as a foreign key so that its value corresponds to the Id field in the Teachers table. In this way, once a teacher Id that does not exist is stored in the Students table, the system will report an error.

Default value DEFAULT

There are some special field columns whose values are basically the same in each record. It is only changed to a different value in a few cases, and we can give him a default value for such a field column.

Conditional check CHECK

Some values must meet certain conditions before they are allowed to be stored, which requires the use of this CHECK constraint.

Common command

The following describes the use of common commands in Shell mode.

The command function .help displays a list of all commands available in shell mode .database displays database information Contains the location of the current database. Mode column causes the results of SQL statement processing to be displayed in a column-aligned manner. Mode listcolumn.headers on/off turns the column header display switch on and off To make the query results have column headings when displayed on the screen. Tables lists the total number of tables in the currently open database. Exit exits the SQLite environment. Schema foods displays the SQL statement when the table foods is created. Schema shows the statements when all tables are created. Nullvalue STRING query with the specified string instead of the output NULL string defaults to. Nullvalue'. Show shows some output-related settings defined in shell mode. Output file.csv sets the output file format to CSV The file name is file.csv.separator, and the column data output by the select statement is separated by ",". Output stdout restores the output to the standard output device (screen)

[note] the commands of sqlite are all. At the beginning, there is nothing in front of the operation statement. Of.

Use an example

The operation statement of the database is mainly to add, delete, modify and check, below we let you understand these basic operations of the database through some examples.

Table type

Suppose we are going to create a database of teaching management, jxgl.db, in which the student table STUDENT is saved.

Snosnamessexsagesdept95001yikoum21cs95002pengm21cs

According to our common sense, we know that each column of data has the following characteristics:

Sno student number: integer value, each student number is unique, the school usually uses the student number to distinguish all students, and generally the student number is incremental, so we set sno to primary key.

Sname name: usually a string, which can be repeated, but cannot be empty

Ssex gender: string, can be empty

Sage age: integer value, assumed to be older than 14

Sdept Professional: string, which can be empty, here we default to 'CS'.

Let's implement all the operations of this database step by step.

Create "jxgl" database of teaching management

To open and exit the database, use the following command.

Create a table:

CREATE TABLE IF NOT EXISTS STUDENT (Sno integer primary key, Sname text not null, Ssex text,Sage integer check (Sage > 14), Sdept text default 'CS')

The properties of the table execute the results according to the properties of the table in the previous section:

View the table:

When you see STUDENT, it means that the table has been created. [note]

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The operation statement is not a command, do not add it in front of it.

Be sure to follow the operation statement; at the end, if you miss it, be sure to fill in the semicolon

The operation statement is very sensitive to the full-width half-width of letters, and all symbols should use half-width.

Insert data

Inserting data is implemented using an insert into statement, as follows:

INSERT INTO STUDENT VALUES ('95001 Zhi Yong', 'Manyong 20 penciled CS'); INSERT INTO STUDENT VALUES (' 95002 Yuji 'Liu Chen', 'Flying pencilled 19pies'); INSERT INTO STUDENT VALUES ('95003pr' Wang Min', 'Fleming 18pr' Ma'); INSERT INTO STUDENT VALUES ('95004pr' Zhang Li', 'MFG' 18pr')

The implementation results are as follows:

The inserted data initializes only the partial value

The column with not null set must be assigned, and the table name is not case sensitive.

Insert into student (sname,sage) values (one bite, 19)

View tabl

Use the select statement to view the contents of the table:

SELECT * FROM STUDENT

The * in it means to view all the data information.

Did you see that the result looks uncomfortable? let's adjust the display format below:

Sqlite > .headers on shows column names sqlite > .mode column column alignment

Delete a line of information

Delete from student where sname=' takes a bite.

As can be seen from the picture above, the record named "one bite" has been deleted.

Modify something in a record

UPDATE student SET sage=29 WHERE sname=' Zhang Li'

Modify the data table structure.

The statement ALTER TABLE is needed to modify the table structure. Let's add a "spwd" column to the STUDENT table, whose data type is text, and use the SELECT command to view the contents of the table.

ALTER TABLE STUDENT ADD spwd TEXT default '123456'

Modify the table name

Alter table student rename to stu

Delete the data table.

DROP TABLE STUDENT

Delete column

Sqlite3 does not implement the command to delete a column. To do this, you need to copy the table to a new table, but only integrate the required columns, and the columns to be deleted are not inherited. You can delete a column in the following ways:

Sqlite > create table test as select sno, sname,ssex,sage,sdept from stu; sqlite > drop table stu; sqlite > alter table test rename to stu

As you can see from the figure above, the column spwd we just added has been deleted.

Sqlite advance

Where clause

What if I don't want to look at all the data and point to someone's information? We are going to use the where clause to achieve this. Where clause can be combined with operation statements to add, delete, modify and query the process, is the most commonly used clause.

Find the record by name:

Select * from student where sname=''

Look up the record according to the student number:

Select * from student where sno=95001

Look up records based on name and age at the same time:

Select * from student where sname=' one bite of 'and sage=19

Show two columns of data

Select sno,sname from student

Backup and recovery of database

Now let's assume that we want to back up the table foods as foodsdb.sql and reply to the database with a command. Just follow these steps.

Sqlite > .dump-- all SQL statements that create the table and insert records into the table are displayed on the screen sqlite > .output foodsdb.sql-- specify the file name of the dump command output to the file sqlite > .dump-- output the SQL statement that creates and inserts data into the basic table to the file specified by output > .output stdout-- restore the output to the standard output device (screen) sqlite > .dump-- at this time the output SQL statement goes back to the screen sqlite > Drop table foods -- Delete foods statement description:

The above Drop is a SQL statement that deletes the specified table. Because it is a SQL statement, it ends with ";"

Sqlite > .dump-- all SQL statements that create a table and insert records into the table are displayed on the screen sqlite > .output foodsdb.sql-- specify the filename of the dump command output to the file sqlite > .dump-- output the SQL statement that creates and inserts data into the basic table to the file specified by output > .output stdout-- restore the output to the standard output device (screen) sqlite > .dump- -at this point, the output SQL statement goes back to screen sqlite > Drop table foods Delete foods statement statement: at this point, the study on "how to use basic command operations in embedded database Sqlite3" is over. I hope to be able to solve your 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.