sqlite c语言编程_用C教程2编程SQLite

sqlite c语言编程

This tutorial is the second in a series on programming SQLite in C.

本教程是用C语言 编程 SQLite系列文章的第二篇。

SQLite stores a collection of tables in a single file database, usually ending in .db. Each table is like a spreadsheet, it consists of a number of columns and each row has values.

SQLite将表的集合存储在单个文件数据库中 ，通常以.db结尾。 每个表就像一个电子表格，它由许多列组成，每一行都有值。

If it helps, think of each row as being a struct, with the columns in the table corresponding to the fields in the struct.

如果有帮助，可以将每一行都视为一个struct ， 表中的列与该结构中的字段相对应。

A table can have as many rows as will fit on a disk. There is an upper limit but its huge 18,446,744,073,709,551,616 to be precise.

一个表可以具有磁盘上可以容纳的尽可能多的行。 有一个上限，但是确切地说，它是庞大的18,446,744,073,709,551,616。

A table can have up to 2,000 columns or if you recompile the source, you can max it to an awesome 32,767 columns.

一个表最多可以包含2,000列，或者如果您重新编译源，则最多可以将其扩展为32,767列。

SQLite API ( The SQLite API )

To use SQLite, we need to make calls to the API. You can find an introduction to this API on the official Introduction to SQLite C/C++ Interface web page. It's a collection of functions and easy to use.

要使用SQLite，我们需要调用API。 您可以在官方的SQLite C / C ++接口简介网页上找到此API的简介 。 它是功能的集合，易于使用。

First, we need a handle to the database. This is of type sqlite3 and is returned by a call to sqlite3_open( filename, **ppDB). After that, we execute the SQL.

首先，我们需要一个数据库句柄。 这是sqlite3类型，通过调用sqlite3_open(文件名，** ppDB)返回。 之后，我们执行SQL 。

Let's have a slight digression first though and create a usable database and some tables using SQLiteSpy. (See the previous tutorial for links to that and the SQLite Database Browser).

不过，让我们先进行一点题外话，并使用SQLiteSpy创建可用的数据库和一些表。 (有关该链接和SQLite数据库浏览器的链接，请参见前面的教程)。

活动和场地 ( Events and Venues )

The database about.DB will hold three tables to manage events at several venues. These events will be parties, discos, and concerts and will take place at five venues (alpha, beta, charlie, delta, and echo). When you are modeling something like this, it often helps to start with a spreadsheet. For simplicities sake, I'll just store a date not a time.

about.DB数据库将包含三个表，以管理多个场所的事件。 这些活动将是聚会，迪斯科舞会和音乐会，并将在五个地点(alpha，beta，charlie，delta和echo)举行。 当您在进行此类建模时，通常有助于从电子表格开始。 为简单起见，我只存储日期而不是时间。

The spreadsheet has three columns: Dates, Venue, Event Type and about ten events like this. Dates run from 21st to 30th of June 2013.

电子表格包含三列：日期，地点，事件类型以及大约十个此类事件。 日期为2013年6月21日至30日。

Now SQLite has no explicit date type, so it's easier and faster to store it as an int and the same way that Excel uses dates (days since Jan 1, 1900) have int values 41446 to 41455. If you put the dates in a spreadsheet then format the date column as a number with 0 decimal places, it looks something like this:

现在，SQLite没有显式的日期类型，因此将其存储为int更加容易和快捷，并且Excel使用日期的方式(自1900年1月1日以来的天数)具有int值41446至41455。如果将日期放入电子表格中然后将日期列的格式设置为带0小数位的数字，看起来像这样：

Now we could store this data in one table and for such a simple example, it would probably be acceptable. However good database design practice requires some normalization.

现在我们可以将此数据存储在一个表中，对于这样一个简单的示例，它可能是可以接受的。 但是，好的数据库设计实践需要一些规范化 。

Unique data items like venue type should be in its own table and the event types (party etc) should also be in one. Finally, as we can have multiple event types at multiple venues, ( a many to many relationship) we need a third table to hold these.

唯一的数据项(例如会场类型)应在其自己的表中，而事件类型(聚会等)也应在一个表中。 最后，由于我们可以在多个场所拥有多种事件类型(多对多关系)，因此我们需要第三个表来保存这些事件。

The three tables are:

这三个表是：

• venues - holds all five venues
  场地-容纳所有五个场地
• eventtypes - holds all three event types
  eventtypes-包含所有三种事件类型
• events - holds the date plus venue id plus event type id. I also added a description field for this event eg "Jim's Birthday".
  events-保存日期，地点ID和事件类型ID。 我还为此事件添加了一个描述字段，例如“吉姆的生日”。

The first two tables hold the data types so venues have names alpha to echo. I've added an integer id as well and created an index for that. With the small numbers of venues (5) and event types (3), it could be done without an index, but with larger tables, it will get very slow. So any column that is likely to be searched on, add an index, preferably integer

前两个表保存数据类型，因此场所具有要回显的名称alpha。 我还添加了一个整数id并为此创建了一个索引。 在场所(5)和事件类型(3)较少的情况下，可以不用索引来完成，但是在较大的表中，它会变得很慢。 因此，可能要搜索的任何列都添加索引，最好是整数

The SQL to create this is:

创建它SQL是：

The index on the events table has date, id-event, the event type, and venue. That means we can query the event table for "all events on a date", "all events at a venue","all parties" etc and combinations of those such as "all parties at a venue" etc.

事件表上的索引包含日期，ID事件，事件类型和地点。 这意味着我们可以在事件表中查询“日期上的所有事件”，“场地上的所有事件”，“所有参与者”等以及“场地上的所有参与者”等的组合。

After running the SQL create table queries, the three tables are created. Note I've put all that sql in the text file create.sql and it includes data for populating some of the three tables.

运行SQL创建表查询后，将创建三个表。 注意，我将所有sql都放在了文本文件create.sql中，它包含用于填充三个表中的某些表的数据。

If you put ; on the end of the lines as I've done in create.sql then you can batch and execute all the commands in one go. Without the ; you have to run each one by itself. In SQLiteSpy, just click F9 to run everything.

如果你放; 在行的最后，就像我在create.sql中所做的那样，然后您可以一次性批处理并执行所有命令。 没有的; 您必须自己运行每个。 在SQLiteSpy中，只需单击F9即可运行所有内容。

I've also included sql to drop all three tables inside multi-line comments using /* .. */ same as in C. Just select the three lines and do ctrl + F9 to execute the selected text.

我还包括sql，使用/ * .. * /将所有三个表放在多行注释中，与C中相同。只需选择三行并执行ctrl + F9即可执行所选文本。

These commands insert the five venues:

这些命令将插入五个场所：

Again I've included commented out text to empty tables, with the delete from lines. There's no undo so be careful with these!

再次，我将注释掉到空表中的文本以及从行中删除 。 不可撤消，因此请小心使用！

Amazingly, with all the data loaded (admittedly not much) the entire database file on disk is only 7KB.

令人惊讶的是，加载了所有数据(公认的不是很多)后，磁盘上的整个数据库文件只有7KB。

活动数据 ( Event Data )

Rather than build up a bunch of ten insert statements, I used Excel to create a .csv file for the event data and then used the SQLite3 command line utility (that comes with SQLite) and the following commands to import it.

我没有建立十个插入语句，而是使用Excel为事件数据创建.csv文件，然后使用SQLite3命令行实用程序(SQLite附带)和以下命令将其导入。

Note: Any line with a period (.) prefix is a command. Use .help to view all commands. To run SQL just type it in with no period prefix.

注意：任何带有句点(。)前缀的行都是命令。 使用.help查看所有命令。 要运行SQL，只需输入不带句点前缀SQL即可。

You have to use double blackslashes \\ in the import path for each folder. Only do the last line after the .import has succeeded. When SQLite3 runs the default separator is a : so it has to be changed to a comma before the import.

您必须在每个文件夹的导入路径中使用双黑斜杠\\。 .import成功后仅执行最后一行。 当SQLite3运行时，默认的分隔符是：，因此在导入之前必须将其更改为逗号。

返回代码 ( Back to the Code )

Now we have a fully populated database, let's write the C code to run this SQL query which returns a list of parties, with description, dates and venues.

现在我们有了一个完全填充的数据库，让我们编写C代码来运行此SQL查询，该查询返回包含说明，日期和地点的参与方列表。

• New to SQL? Read What is SQL?
  SQL新手？ 阅读什么是SQL？

This does a join using the idvenue column between the events and venues table so we get the name of the venue not its int idvenue value.

这将使用事件和场所表之间的idvenue列进行联接，因此我们将获得场所的名称，而不是其int idvenue值。

SQLite C API函数 ( SQLite C API Functions )

There are many functions but we only need a handful. The order of processing is:

有很多功能，但我们只需要少数几个。 处理顺序为：

1. Open database with sqlite3_open(), exit if have error opening it.
   使用sqlite3_open()打开数据库，如果在打开数据库时出错则退出。
2. Prepare the SQL with sqlite3_prepare()
   使用sqlite3_prepare()准备SQL
3. Loop using slqite3_step() until no more records
   使用slqite3_step()循环直到没有更多记录
4. (In the loop) process each column with sqlite3_column...
   (在循环中)使用sqlite3_column处理每一列...
5. Finally call sqlite3_close(db)
   最后调用sqlite3_close(db)

There's an optional step after calling sqlite3_prepare where any passed in parameters are bound but we'll save that for a future tutorial.

调用sqlite3_prepare之后，有一个可选步骤，其中绑定了任何传入的参数，但我们将其保存以备将来使用。

So in the program listed below the pseudo code for the major steps are:

因此，在下面列出的程序中，主要步骤的伪代码为：

The sql returns three values so if sqlite3.step() == SQLITE_ROW then the values are copied from the appropriate column types. I've used int and text. I display the date as a number but feel free to convert it to a date.​

sql返回三个值，因此，如果sqlite3.step()== SQLITE_ROW，则会从相应的列类型中复制这些值。 我用过int和text。 我将日期显示为数字，但可以随时将其转换为日期。

Listing of Example Code

示例代码清单

翻译自: https://www.thoughtco.com/creating-populating-running-database-sql-query-958233

sqlite c语言编程