移动开发入门：SQLite数据库集成与应用 - CSDN文库

5星 · 超过95%的资源需积分: 15 159 浏览量更新于2024-07-21 2 收藏 2.82MB PDF 举报

"《Introducing SQLite for Mobile Developers》是一本专为移动开发者，特别是iOS和Android开发者设计的关于SQLite的入门书籍。书中不仅提供了一个可选的SQL基础介绍，还讨论了何时使用SQLite，以及如何使用最常见的编程语言与SQLite集成。书中通过实例详细介绍了如何在Android或iOS应用中添加简单数据库，并专门有章节讲述如何管理应用程序的生命周期。适合有一定移动开发经验，但对数据库知识需求基础的读者。" 本书内容包括： 1. **数据库和SQLite简介**（Chapter 1）：快速了解数据库的基本概念和SQLite的特点，为后续学习打下基础。 2. **理解SQLite**（Chapter 2）：深入讲解SQLite是什么，它的工作原理以及在移动应用中的适用场景。 3. **SQLite基础：数据的存储与检索**（Chapter 3）：教授如何在SQLite中存取数据，涵盖基本的CRUD（创建、读取、更新、删除）操作。 4. **与关系模型工作**（Chapter 4）：介绍SQLite如何遵循关系数据库模型，帮助理解数据组织和查询的方式。 5. **利用SELECT语句实现SQLite功能**（Chapter 5）：探讨SELECT语句的高级用法，如联接、聚合函数等，增强查询能力。 6. **使用SQLite与PHP**（Chapter 6）：演示如何在PHP开发环境中集成SQLite，实现服务器端的数据处理。 7. **使用SQLite与Android/Java**（Chapter 7）：针对Android平台，讲解如何在Java代码中操作SQLite数据库。 8. **使用SQLite与Core Data（iOS和OS X）**（Chapter 8）：为iOS和OS X开发者提供在Core Data框架中使用SQLite的指导。 9. **使用SQLite/Core Data与Swift（iOS和OS X）**（Chapter 9）：介绍如何在Swift编程语言中利用SQLite和Core Data。 10. **使用SQLite/Core Data与Objective-C（iOS和Mac）**（Chapter 10）：面向Objective-C开发者，展示如何在Objective-C项目中集成SQLite和Core Data。 11. **将简单数据库用于PHP网站**（Chapter 11）：讲解如何在PHP驱动的网站中使用SQLite数据库。 12. **将简单数据库用于Core Data/iOS应用**（Chapter 12）：教程如何在iOS应用中使用SQLite数据库并配合Core Data进行数据管理。通过本书的学习，读者将掌握SQLite的基础知识，SQL语言的使用，以及如何在Android和iOS平台上有效地集成和管理SQLite数据库，从而为移动应用增加数据库功能。

CHAPTER 1 ■ GETTING UP TO SPEED WITH DATABASES AND SQLITE

2

Databases Are Structured and Organized

All those examples can be implemented with a database because all those items need

to be structured and organized. Some of the items can turn into large amounts of

data (particularly a browser history if you don’t choose an option to clear out the old

information occasionally—another option that can be stored in a preference database),

but some of the preferences listed are very small amounts of data: even with a fully

modified file name for your download folder, 128 characters is enough to store that data

element. Furthermore, most browsers store the name of your preferred download folder,

but they don’t store the history of download folders (last week’s preferred download

folder, the folder you used last month, etc.).

In thinking about databases, “big” is often relevant, but very frequently it’s

misleading. It is structure and organization that matter most.

And they matter a great deal to a mobile developer.

Precisely because mobile devices need to function in a world of constrained

resources (e.g., limited storage space and battery power), structuring and organizing

the data that is stored is particularly important. Built into a data management system

are various optimizations that matter to databases large and small. For example, many

databases can store character or string data. Such data is typically quite variable. Even if

a database designer specifies that a text field can contain 50, 500, or 50,000 characters,

behind the scenes trailing blanks are often discarded. The user (and even the developer)

may never know this, but it makes the entire database function more efficiently. (Features

like this function behind the scenes and they can be accessed and turned on or off by the

database designer in many cases.)

Databases Are Smart

Being able to optimize storage to work with the presence or absence of characters in a

text field is a powerful behind-the-scenes tool, but databases have intelligence built

into them.

In most database engines today, the designer can specify many attributes of a field

such as the following:

• Name. A name that is used internally is often quite different from

the name that appears in the user interface.

• Type. You can specify that a certain database field must be an

integer or a string or any other type that the database supports.

• Optionality. Some fields are optional, and you can specify that

when you set up a database. (Many people have a car with a

license number; many other people do not own cars.)

• Default values. A database can be set to provide a default

value—either a simple value or a calculated value based on other

data in the database.

CHAPTER 1 ■ GETTING UP TO SPEED WITH DATABASES AND SQLITE

3

• Cardinality. Sometimes, there are multiple values for part of the

database. (To continue the example, some people have no car,

other people own a car, and others own several cars. Furthermore,

some people own a car and a bike . . . you get the idea.)

• Value ranges. When the database designer specifies the database

structure, a range of values for each field can be set. A driver’s

license identifier that consists of characters other than letters and

numbers (depending on the locality) may not be allowed.

• Value relationships. You can move beyond value ranges in

designing a database. You can create combinations of attributes

and values so that, for example, a database can enforce a rule that

an employee may be married (to one person or no one), but if the

employee is married, the spouse’s employer cannot be the same

as the employee’s employee. These restrictions in the database

reflect policies, but there well may be exceptions (a supervisor’s

approval), and the database can be designed to support a case

like that.

These are just a few of the ways in which a database can be set up to enforce

logical rules.

Perhaps at this point you’re tempted to say, “But I can do each of those things

with a line or two of code.” That’s absolutely true. But there are a couple of points to bear

in mind.

Writing Code Is Just the Beginning

You can write the code to implement any of those conditions, but that’s just the start.

Over time, conditions change, users have suggestions, and the Power That Be (whoever

that (or “they”) may be in your life) decides to change the rules for employment, cars,

the conditions that apply to license plate numbers, or any other things that strike his or

her fancy.

The code that took only a few lines of code and a couple of minutes to write needs to

be rewritten. And, although there is no clear research on the matter, it seems that these

changes take place at the last minute before a product (or version) launch and they must

be done immediately.

Parlez-vous Python? Sprechen Sie Scala?

The line or two that implement the rules for one of the data conditions may have been

written by someone who’s no longer around. Even if you wrote them, time may have passed

and you may not remember exactly what you were trying to implement (yes, commenting

your code is a desirable feature, but life happens . . .)

Those few lines of code may have been modeled on some code from a friend that

does exactly (or almost) what you want. Maintaining even the best-written code is a

challenge for everyone involved from the first author to the updater to the manager

and—eventually the user or manager who asks for “just a little tweak.”

CHAPTER 1 ■ GETTING UP TO SPEED WITH DATABASES AND SQLITE

4

Relational Databases and SQL to the Rescue

Today, databases are remarkably standardized in their structure—perhaps more so

than almost any other concept in the world of information technology. The idea of

a relational database was first proposed in 1970 by Edgar Codd, of IBM’s San Jose

Research Laboratory, and his proposal has become the basis for modern database

implementations (see article titled “A Relational Model of Data for Large Shared Data

Banks,” Communications of the ACM, 13(6), 377–387 (June 1970)).

In 1974, Donald D. Chamberlin and Raymond F. Boyce (also from IBM) wrote a paper

titled “SEQUEL: A Structured English Query Language” for the ACM (Association for

Computing Machinery) SIGFIDET Workshop on Data Description, Access and Control.

The relational model (proposed by Codd), together with SQL, has become the

basis for modern database implementations. Books by C. J. Date and others together

with Codd’s original work on the relational model remain the cornerstones of relational

database theory today.

■ Note The name SQL is derived from SEQUEL which turned out to be a trademark

of the Hawker Siddeley aircraft company. Today, many people suggest that SQL is not

an abbreviation or acronym but is a name in and of itself. Others suggest that SQL is an

acronym for structured query language.

The basic concepts of SQL are few. You can visualize them by thinking of a

spreadsheet with its rows and columns. (You can also conceptualize them using

mathematical set theory and other concepts, but spreadsheets are easier for many people

to think about today.)

Following are the basic SQL concepts:

• Table. A table is much like a simple spreadsheet with rows and

columns. (It’s not like a complex spreadsheet that may include

several tables. For this discussion, think simple—just rows and

columns in a single table.) A table is sometimes referred to as a

relation, but more often table is used.

• Column. A column represents a single data element such as

“address” or “name.” In the database world, a column may be

called a field. In the world of programming, a column may be

called an attribute or property.

• Row. A row represents single observations or set of values with

one for each column. Thus, a row in this imaginary simple

spreadsheet might represent a person’s data. In the database

world, a row may be called a record or tuple.

CHAPTER 1 ■ GETTING UP TO SPEED WITH DATABASES AND SQLITE

5

To retrieve data from a relational database table, you run a query. A query is a set

of logical instructions that manipulate a given table in such a way as to retrieve the data

that you want. The result of a query is another table. The resulting table may be empty

(if no data satisfies the query), it may be some or all of the data in the table, and, in

some complex queries, it may be larger than the basic table. The results of a query are

sometimes called a view or a result set.

These concepts and terms apply to most databases today regardless of the language

in which they are implemented and, perhaps more important, regardless of the database

engine or database management system (DBMS) in which they are implemented.

Furthermore, databases are sometimes implemented as part of frameworks and

languages. Thus, in PHP, you can use PDO (PHP Data Objects) starting with PHP 5.1 to

access SQLite. For iOS, you can use the Core Data framework to work with SQLite and

other data managers. In these cases as well as others, the idea has been to abstract as

much as possible into a framework or other wrapper so that switching databases does not

require massive rewrites of code.

SQLite implements most of the SQL standard. The exceptions are listed here

https://sqlite.org/omitted.html.

Looking Inside a Relational Table and Query

For the remainder of this chapter and several that follow, we are going to use a simple

example to learn how to create SQLtables and queries. Table1-1 shows a simple table that

can be used to demonstrate the basic concepts of SQL, queries, and relational tables. It

shows some data for a few people and their country of origin. The names of the columns

(fields) are PK, Name, and Origin. The values of Name are Cecelia, Leif, and Charlotte; the

values for Origin are Australia, Iceland, and United States.

Table 1-1. SimpleTable

PK Name Origin

1 Cecelia Australia

2 Leif Iceland

3 Charlotte United States

■ Note In a relational database, the columns have names, but rows are not named. In the

simple table shown in Table 1-1, the first column (named PK) has three values: 1, 2, and 3.

These values happen to correspond to the row numbers, but the sample would work just as

well if they were named Chair, Tree, and 15.

剩余155页未读，继续阅读

ramissue

粉丝: 354
资源: 1487

最新资源