preface
Data is widely used in practical work, database products are also more, Oracle, DB2, SQL2000, mySQL; Database based on embedded Linux mainly includes SQLite, Firebird, Berkeley DB, eXtremeDB.
This article mainly explains the database SQLite, through this open source small embedded database to lead you to master some basic database operations, these operations are common in many systems, can be said to learn a hundred.
SQLite
The author D.R ichardHipp
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 backend and would require no installation or administrative support. As soon as Hipp had free time, it was implemented, and SQLite version 1.0 was released in August 2000. Here I offer my knee to the great god!
It is designed to be embedded, and has been used in many embedded products, it is very low resource footprint, in embedded devices, may need only a few hundred K of memory is enough. It can support Windows/Linux/Unix and other mainstream operating systems, and can be combined with many programming languages, such as Tcl, C#, PHP, Java, etc., as well as ODBC interface. Compared with Mysql and PostgreSQL, two open source database management systems in the world, It can process it faster than any of them.
SQLite features
- Zero configuration no installation and management configuration is required.
- A complete database stored in a single disk file;
- Database files can be freely shared between machines in different byte order;
- Supports database size up to 2TB;
- Small enough, the total source code is approximately 30,000 lines of c code, 250KB;
- Faster than most popular databases that operate on data.
The installation
Sqlite3 is the main version running now, and it’s easy to install in Ubuntu.
Sudo apt-get install libsqlite3 sudo apt-get install libsqlite3 sudo apt-get install libsqlite3 sudo apt-get install libsqlite3 sudo apt-get install libsqlite3Copy the codeTo view the version number, run the sqlite3 command to go to the operation window and run the. Version command.
Sudo apt-get install sqliteBrowserCopy the codeIn addition, it can also be deployed under Windows, this article only discusses ubtuntu, using the command line to operate the database.
The data type
Operation database, mainly operation table, table each column content 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 | define |
|---|---|
| Data type NULL | Indicates that the value is NULL. |
| INTEGER | Unsigned integer value. |
| REAL | Floating point value. |
| TEXT | It is a character string. The encoding is UTF-8, UTF-16BE, or UTF-16LE. |
| BLOB | Stores Blob data, which is exactly the same as input data, with 1 representing true and 0 representing false. |
Sqlite3 also accepts the following data types:
| The data type | define |
|---|---|
| smallint | A 16-bit integer. |
| interger | A 32-bit integer. |
| decimal(p,s) | The exact value p means how many decimal numbers there are, and s means how many decimal places there can be after the decimal point. If this parameter is not specified, the system defaults to p=5 and s=0. |
| float | 32-bit real numbers. |
| double | A 64-bit real number. |
| char(n) | The length of the string cannot exceed 254. |
| varchar(n) | A string of variable length with a maximum length of n, n cannot exceed 4000. |
| graphic(n) | It is the same as char(n), except that the unit is two bytes and n cannot exceed 127. This pattern is designed to support two-byte fonts such as Chinese characters. |
| vargraphic(n) | A two-character string of variable length with a maximum length of n, n cannot exceed 2000 |
| date | Contains the year, month, and date. |
| time | It includes hours, minutes, and seconds. |
| timestamp | It contains years, months, days, hours, minutes, seconds, and thousandths of a second. |
The constraint
Each column of a table has some restricted attributes, for example, the data of some columns cannot be repeated, and the data range of others is restricted. Constraints are used to further describe the data attributes of each column. Common constraints for SQLite databases are as follows:
| The name of the | define |
|---|---|
| NOT NULL | – is not empty |
| UNIQUE | The only |
| PRIMARY KEY | A primary key |
| FOREIGN KEY | A foreign key |
| CHECK | Condition check |
| DEFAULT | The default |
NOT empty the NOT NULL
There are some fields that we don’t know what to fill in, and it doesn’t have a default value. When adding data, we leave such fields blank and the system considers them NULL. But there is another type of field that must be filled in with data, and if it is not filled in, the system will report an error. Such fields are called NOT NULL non-empty fields and need to be declared when defining the table.
The only UNIQUE
In addition to the primary column, there are also some columns that cannot have duplicate values.
The PRIMARY KEY PRIMARY KEY
It’s usually an integer or a string, as long as it’s unique. In SQLite, the value of this column can grow automatically if the primary key is an integer type.
FOREIGN KEY FOREIGN KEY
Select * from Teachers; select * from Students; select * from Teachers; select * from Students; select * from Teachers; Simply create a TeacherId field in the Students table, save the Id number of the corresponding teacher, and then establish a relationship between the student and the teacher. The problem is: it is possible to store a TeacherId value to a student that is not in the Teachers table and not find the error. In this case, you could declare the TeacherId field in the Students table as a foreign key and have its value correspond to the Id field in the Teachers table. As a result, once a nonexistent teacher Id is stored in the Students table, the system will report an error.
The DEFAULT value is the DEFAULT
There are special columns of fields whose values are essentially the same in each record. Only in rare cases to change the value of the other column, we can give him a default value.
Condition CHECK
Some values must meet certain conditions to be allowed to be stored, and this is where the CHECK constraint is used.
Common commands
The following describes the common commands in Shell mode.
| The command | function |
|---|---|
| .help | Displays a list of all commands available in shell mode |
| .database | Display database information; Contains the location of the current database |
| .mode column | Causes the results of SQL statement processing to be displayed in column alignment |
| .mode list | column |
| .headers on/off | Turn on or off the column header display switch so that the query results have column headers when displayed on the screen |
| .tables | Lists the total number of tables in the currently open database |
| .exit | Exit the SQLite environment |
| .schema foods | Shows the SQL statement when the table Foods was created |
| .schema | Displays statements when all tables are created |
| .nullvalue STRING | Nullvalue = NULL; nullValue = NULL; |
| .show | Shows some of the output-related Settings defined in shell mode |
| .output file.csv | Set the output file format to CSV and file name to file.csv |
| .separator , | Set column data output by the SELECT statement to be separated by commas (,) |
| .output stdout | Restore output to standard output device (screen) |
[note] sqLite commands are There is nothing in front of the operation statement. .
Using the instance
The operation statement of the database is mainly to add, delete, change and check, let us understand the basic operation of the database through some examples.
Table type
Suppose we want to create a teaching management database, jxgl.db, and store the STUDENT table in the database.
| sno | sname | ssex | sage | sdept |
|---|---|---|---|---|
| 95001 | yikou | m | 21 | cs |
| 95002 | peng | m | 21 | cs |
| According to our common sense, the data in each column has the following characteristics: |
- Sno student id: integer value, each student id is unique, schools usually use the student ID to distinguish all students, and generally increasing, so we set sNO as the primary key.
- Sname Name: It is a string that can be repeated but cannot be empty.
- Ssex Sex: a string that can be empty.
- Sage age: Integer value, assumed to be older than 14;
- Sdept professional: a string that can be empty, where we default to ‘CS’.
Let’s implement all the operations of the database step by step.
Create the teaching management “JXGL” database
To open and exit the database, run the following commands.
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');
Copy the codeThe attributes of this table are the result of executing the attributes of the table in the previous section:
See table:
- Operation statements are not commands and do not start with.
- Action statements must be followed by; At the end, be sure to add a semicolon if you miss it;
- Operation statements are sensitive to full and half corners of letters, and all symbols use half corners.
Insert data
Insert data using insert into statement, as shown below:
INSERT INTO STUDENT VALUES('95001'.'li yong'.'M', 20,'CS');
INSERT INTO STUDENT VALUES('95002'.'Mr Liu'.'F', 19,'IS');
INSERT INTO STUDENT VALUES('95003'.'min'.'F', 18.'MA');
INSERT INTO STUDENT VALUES('95004'.'Zhang Li'.'M', 18.'IS');
Copy the codeThe result is as follows:
The inserted data initializes only partial values
Not NULL columns must be assigned, and the table name is case insensitive.
insert into student(sname,sage) values ('a'19);Copy the code
See the table
Use the SELECT statement to view the contents of the table:
SELECT * FROM STUDENT;
Copy the code* indicates that all data information is viewed.
Sqlite >. Mode column Column alignmentCopy the code
Delete a Line of information
delete from student where sname='a';
Copy the code
Modifies the contents of a record
UPDATE student SET sage=29 WHERE sname='Zhang Li';
Copy the code
Modify the data table structure.
ALTER TABLE ALTER TABLE SELECT SPWD from STUDENT; SELECT SPWD from STUDENT;
ALTER TABLE STUDENT ADD spwd TEXT default '123456';
Copy the code
Alter table name
alter table student rename to stu;
Copy the code
Delete the data table.
DROP TABLE STUDENT;
Copy the codeDelete the column
Sqlite3 does not implement the command to delete a column. To do this, the table must be copied to a new table, but only the required columns are integrated, and the columns to be deleted are not inherited. You can delete a column as follows:
sqlite> create table test as select sno, sname,ssex,sage,sdept from stu;
sqlite> drop table stu;
sqlite> alter table test rename to stu;
Copy the code
Sqlite advanced
The where clause
What if I don’t want to see all of my data and point to a particular person? We use the WHERE clause to do this. The WHERE clause, which can be combined with action statements, is the most commonly used clause.
Find records by name:
Select * from student where sname='a';
Copy the code
Select * from student where sno=95001;
Copy the code
select *from student where sname='a' and sage=19;
Copy the code
select sno,sname from student ;
Copy the code
Database backup and recovery
Now let’s say we want to back up the table Foods as foodsdb.sql and reply to that database with a command. Follow the steps below.
Sqlite >.dump -- displays all SQL statements that create tables and insert records into tables on the screen sqlite>.output foodsdb. SQL -- specifies the name of the dump command output to the file sqlite>.dump Output stdout -- restore the output to the standard output device (screen) SQlite >.dump -- at this point the output SQL statement goes back to the screen SQlite >Drop table foods; -- Delete foodsCopy the codeThe above Drop is an SQL statement used to Drop the specified table. Because it is an SQL statement, it is preceded by “; “. At the end
sqlite>.readFoodsdb.sql -- Execute all SQL statements contained in foodsdb. SQL to rebuild the 4 tables and related data that were just droppedCopy the codeSqlite has some other clauses and functions to use, which will continue to be introduced in the next article.
For more information about Linux, please follow the public account “Linux in one Bite”.