First known SQLITE3 database, first known sqlite3

First known SQLITE3 database, first known sqlite3

DataBase, we have never touched the DataBase. What is the DataBase?

It is a structured, integrated, and shareable unified data collection!

Structured means that data is organized according to a certain model.

Simply put, take a box and separate it with a partition, so the box is divided into many grids, so we put the data in like a pitfall! OK, so we can assemble the data. Some people say it is better to use a struct? But don't forget, boxes can also be classified and stored. This is something the struct cannot do!

The so-called integration refers to the database that centrally stores a variety of enterprise data.

As I said above, the box aggregates scattered data!

And you can clear all your extra items in this box, right?

The benefits of centralized storage are obvious:

(1) only one backup is required for one data, and there is little duplicate storage, which eliminates data redundancy.

(2) Data Consistency can be ensured without data redundancy.

Sharing means that data in the database can be used by different users.That is to say, each user can access the same database as required.

The so-called unified management refers to the unified management of databases by DBMS, and any data access is done through DBMS.

I will not talk about sharing and unified management. A box can certainly be used by others, as long as you don't lock it!

Just now, we saw another new word: DBMS.

What is this?

In fact, his full name is DataBase Management System

Is a commercial software used to manage databases.
All requests to access the database are completed by the DBMS.
DBMS provides many commands (Languages) for operating databases, that is, the SQL language.
The official explanation is very complicated. What application layer, language translation processing, and data access layer! Well, I hate this very much, so I am here to explain it to everyone!

Can you see what is going on through the above figure? How straightforward! But someone asked, What about management? That's our SQL statement! Of course, we will never use a very large database in linux, so we only need to master some basic statements!

I will summarize the SQL statements in the blog below!

I just talked about database management, right? But we also need to know what is wrong with the database?

I don't need to talk about Oracle. You have heard of this database! Features: multi-language support, excellent parallel processing capabilities, mostly used in finance, insurance, and other industries
SQL Server Microsoft products, a good database management system, the best should be now Microsoft to promote cloud services, don't tell everyone, I have built a Server on azure, it is used! Excellent integration, although I am not very good at using it!
DB2, which is also a database that I seldom access! I only know that it is very portable!
There is no more ACCESS, and there is office. The graphical interface is easy to operate.
Sqlite3 is a very small database, so it will be my main character!
Now, we have introduced these five database types!

The database also has a very important concept: Data Model

In fact, it is a combination:

(1) Data Structure // type value, etc.

(2) Data Operations // traversal and Deletion

(3) data integrity constraints // link

There are four main categories

Hierarchical Data Model
Mesh Data Model
Relational Data Model
Object-Oriented Database Model
I will talk about relational data, because this is the focus. As for others, you may want to know Baidu. The object-oriented model seems to have just started recently, but not a lot of it is used!

In the general office, when you enter the school, what will the School define for you? Student ID! Yes, so the student ID is related to your name, score, and other types of information. Finding the student ID is to find all your information. This is the relational model!

So how does it ensure data independence? It depends on how the database is constructed!

The three-level structure of the database system refers to that the database system is composed of three levels: external mode, mode, and internal mode. But I am not going to talk about this, because I am also afraid of this abstract thing. In fact, in the development process, we don't need to know him at all, even if I go to the interview, if the examiner asks me, I will also tell him that I don't know, Nima, I am not the person who is here to create a database! You don't have to worry about it. I mention it, that is, you just need to know it!

There is also a candidate key that exists in the Database. What is the primary key? Don't worry about it. Let's use it when we use SQL server! Remember that the student ID is your primary key! Unique Identifier!

Why should I choose sqlite3 as an embedded database?

Because it is small! Suitable size! Embedded things space is limited, and it is just right to use it for data!
High portability. I will not mention embedded systems. It must be portable!
The programming interface is simple. I can use it for simple programming! What does it use?
Code is open-source and cost saving is required! Otherwise, what is embedded!
 
What are the specific advantages? Baidu!

Advantages of SQLite
Small memory usage
Transaction operations are atomic, consistent, isolated, and persistent (ACID), even after system crashes and power failures.
ACID compatibility (atomicity, consistency, independence, and durability), support for views, subqueries, and triggers
A single library file contains database engines and interfaces, and its operation does not depend on other libraries.
Data can be put into a single file
Provides interfaces for C/C ++, PHP, and other applications.
Free
Allows you to dynamically Add User-Defined Functions (simple functions and Aggregate functions) to the SQL command set, without the need to re-compile the SQLite Library
Improved B-tree. The B + tree is used for tables, which greatly improves the query efficiency.
Well, I wrote this article today. So hungry, I'm going to eat! I will write sqlite3 installation and SQL syntax later!

How does sqlite3 select the database to operate?

Sqlite3 can only operate on database files. It does not have the concept of a database like mysql. A file is a database, and a database is a file.
So after you open the database file, the next step is to operate the "table", no "Database"

Sqlite3 database time comparison

This is my own test. You can randomly Add the record time to 12 or more hours.
DECLARE @ time DATETIME, @ rand INT
SET @ rand = RAND () * 20
SELECT @ rand
-- SET @ rand = RAND () * 20

IF @ rand <12 SET @ rand = 12
-- Drop table
-- Create table
--(
-- F_id int identity (1, 1 ),
-- F_time datetime
--)
-- Insert into a (f_time) VALUES ('13:35:40 ')

UPDATE a SET f_time = DATEADD (HOUR, @ rand, f_time) WHERE f_id = 1
SELECT * FROM