# sqlite-net入门_SQLite3入门-基本命令

sqlite-net入门

SQLite is a C library that implements an SQL database engine. It is a Relational Database Management System (or RDBMS). Most of the SQL databases work with the client/server model. Take MySQL for an example. To enter and receive data from a MySQL database you need to send a request to the MySQL server, which on reception will provide you with the appropriate response. Contrary to MySQL, SQLite databases are operated directly from the disk. There is no need to create requests to the server.

SQLite是一个实现SQL数据库引擎的C库。 它是一个关系数据库管理系统 (或RDBMS)。 大多数SQL数据库都使用客户端/服务器模型。 以MySQL为例。 要输入和接收来自MySQL数据库的数据，您需要向MySQL服务器发送请求，该服务器在接收时将为您提供适当的响应。 与MySQL相反，SQLite数据库直接从磁盘操作。 无需创建对服务器的请求。

## 安装 (Installation)

We will operate on our database using the sqlite31 Command Line Interface(CLI). We will work under the Linux Platform. sqlite3 CLI for MAC OS and Windows are just the same, but for the sake of consistency, it’s recommended you install Homestead Improved – a lightweight VM runnable in 5 minutes that will keep your computer clean of extra unneeded software.

To install it on Homestead Improved (or any Debian based OS like Ubuntu), run the following:

sudo apt-get install sqlite3 libsqlite3-dev

This will install sqlite3. To install it on other platforms, follow their official instructions. After it is installed we can start the session. Open the Terminal/Command Line and enter the command sqlite3. You must see something of this kind :

The second line tells you to enter .help for instructions. There is a prompt waiting for you to type a command. So go ahead and enter .help. This will give you a list of Meta Commands and their descriptions.

## 元命令 (Meta Commands)

Meta Commands are used to define output format for tables, examine databases and for other administrative operations. They always start with a dot. Even .help is a meta command. You can go through the list. Here are some that will frequently come in handy:

CommandDescription
.showDisplays current settings for various parameters
.databasesProvides database names and files
.quitQuit sqlite3 program
.tablesShow current tables
.schemaDisplay schema of table
.headerDisplay or hide the output table header
.modeSelect mode for the output table
.dumpDump database in SQL text format

。表演 显示各种参数的当前设置
。数据库 提供数据库名称和文件
。退出 退出sqlite3程序
。表格 显示当前表格
.schema 表的显示模式
。模式 输出表的选择模式
。倾倒 以SQL文本格式转储数据库

## 标准指令 (Standard Commands)

Let us go through the standard commands in sqlite3. Meta commands are issued to examine a database. Standard SQL commands are issued to operate on a database. Standard Commands can be classified into three groups:

• Data Definition Language: It provides the storage structure and methods to access data from the database system.

数据定义语言 ：它提供了用于从数据库系统访问数据的存储结构和方法。

• Data Manipulation Language: It enables users to manipulate (add/modify/delete) data.

数据操作语言 ：它使用户能够操作(添加/修改/删除)数据。

• Data Query Language: It enables users to retrieve required data from the database.

数据查询语言 ：它使用户能够从数据库中检索所需的数据。

Note: SQLite understands many other standard commands, a list of which can be read here. Since the tutorial is a beginner level introduction, we will cover only the above mentioned commands.

SQLite databases are cross-platform portable files. They can be stored on various storage devices and can be transferred across different computers.

SQLite数据库是跨平台的可移植文件 。 它们可以存储在各种存储设备上，并且可以在不同的计算机之间传输。

We will learn to work with sqlite3 with our Comment Section database. Almost every website today has a comment section. To post a comment, the user must enter the following details:

• Name

名称
• Email

电子邮件
• Website

网站
• Comment

评论

Of the four of these, only website URL is optional. We must also define a column that numbers the comments. Let’s call it post_id.

We can define the datatypes of the columns (attributes) as follows:

AttributeDatatype Required
post_idINTEGER
nameTEXT
emailTEXT
website_urlTEXT
commentTEXT

post_id 整数

You can see the documentation for different Datatypes and Storage Classes provided in SQLite3.

SQLite3 uses Manifest Typing2. Most other database engines including MySQL use Static Typing.

SQLite3使用清单输入 2 。 包括MySQL在内的大多数其他数据库引擎都使用静态类型。

Now let us create a database. If you are still in the sqlite3 program, quit it by entering .quit at the sqlite prompt. Then, issue the command:

sqlite3 comment_section.db

This will create a database file comment_section.db in the current working directory.

Note: If no filename follows the command, sqlite3 automatically creates a temporary database. It is what happened when we entered the single command sqlite3 for the first time.

### 创建表(CREATE TABLE)

To store the comments we must define a table. Let us name it comments. To create it, enter the statement:

CREATE TABLE comments (
post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL,
website_url TEXT NULL,
comment TEXT NOT NULL );

NOT NULL makes sure that the particular value is not left empty in a record. PRIMARY KEY3 and AUTOINCREMENT4 elaborate the post_id attribute.

NOT NULL确保特定值在记录中不为空。 PRIMARY KEY 3AUTOINCREMENT 4详细说明了post_id属性。

To check if table has been created, issue the meta command .tables. It will display the table name comments.

Note: To get the schema of the table enter .schema comments

Now that the table has been created, we need to enter data.

### 插入行(INSERT ROWS)

Suppose a user has entered a comment with the details:

Name    : Shivam Mamgain
Email   : xyz@gmail.com
Website : shivammg.blogspot.com
Comment : Great tutorial for beginners.

To insert it, we use the INSERT command.

INSERT INTO comments ( name, email, website_url, comment )
VALUES ( 'Shivam Mamgain', 'xyz@gmail.com',
'shivammg.blogspot.com', 'Great tutorial for beginners.' );

I have not provided the value for post_id, even though it is defined as NOT NULL. The thing is, it is not needed because it is an AUTOINCREMENT field.

Enter some more rows in the table to practice.

### 选择(SELECT)

We have learned to insert data into the table. To retrieve data we use the SELECT command.

SELECT post_id, name, email, website_url, comment
FROM comments;

The above statement can also be written as:

SELECT *
FROM comments;

This will retrieve all rows from the table. The resultant table might look obfuscated without headers and proper separation between columns. To overcome this, we must alter some parameters.

Enter .show

To display column attributes, enter .headers ON. To display rows in column style, enter .mode column. Enter the SELECT statement again.

Note: Select a mode of display that suits you. Enter .help and search for .mode. You will be shown the various values .mode can have.

### 更新(UPDATE)

Suppose email for ‘Shivam Mamgain’ was changed to ‘zyx@email.com’ . We need to update the row. Issue the following statement:

UPDATE comments
SET email = 'zyx@email.com'
WHERE name = 'Shivam Mamgain';

This will change the email attribute for the name ‘Shivam Mamgain’ to ‘zyx@email.com’.

Note: The name attribute is not unique, so the above statement may affect more than one row. For every name = ‘Shivam Mamgain’ the email will be set to ‘zyx@email.com’. To update a particular row use the post_id attribute. It is defined as a PRIMARY KEY and will always be unique.

### 删除(DELETE)

To DELETE row(s) from the table we can use the WHERE condition, just like in UPDATE.

Suppose we need to delete a row with post_id 9. We can enter the command:

DELETE FROM comments
WHERE post_id = 9;

We are in a need to delete all comments that are posted by ‘Bart Simpson’ and ‘Homer Simpson’. We can run the following query:

DELETE FROM comments
WHERE name = 'Bart Simpson' OR name = 'Homer Simpson';

### 改变(ALTER)

New columns can be added to a table using ALTER. On the web, most people don’t necessarily use their real name. They generally work under an alias, a username. We need to add a column username to our table. The query below accomplishes the task:

ALTER TABLE comments
ADD COLUMN username TEXT;

This will create a column username in comments and will set its datatype to TEXT. The value of username for already inserted rows will be set to NULL.

The ALTER command is also used to rename tables. Let us change the comments table to Coms.

ALTER命令还用于重命名表。 让我们将comments表更改为Coms

ALTER TABLE comments
RENAME TO Coms;

### 下降(DROP)

Dropping a table means deleting the entire table. The Coms table can be deleted with the following query:

DROP TABLE Coms;

## 结论 (Conclusion)

SQLite3 offers many advantages over other relational databases. Some of its distinctive features can be read here. Most PHP Frameworks and many other web frameworks including Django, Ruby on Rails and web2py have SQLite3 as their default database. Being lightweight makes it preferable for local storage in web browsers. It is also employed as client storage for many Operating Systems, including Android and Windows Phone 8. It is one of the most widely deployed database engines.

In the tutorial we interacted with an SQLite database system. There are GUI applications to do the same, without having to learn commands. Two such applications are the DB Browser for SQLite and SQLiteStudio. You can also practice SQL online at SQL Fiddle.

This guide has covered the basics to get you started with SQLite3. I hope it will tempt you to go further. You can easily integrate it with PHP using the common PDO class.

1. Manifest Typing releases many restrictions on the type of value that can be entered for a particular field. This allows you to enter any value of any datatype into a column, irrespective of the declared type of the column (except for INTEGER PRIMARY KEY). You are not even required to provide the maximum size for datatypes. After the user inputs data, SQLite determines the size it would require on the disk. To get a deeper insight refer to Datatypes in SQLite3.

清单键入对可以为特定字段输入的值的类型释放了许多限制。 这使您可以在列中输入任何数据类型的任何值，而不管该列的声明类型如何(除了INTEGER PRIMARY KEY )。 您甚至不需要为数据类型提供最大大小。 用户输入数据后，SQLite确定磁盘上所需的大小。 要获得更深入的了解，请参阅SQLite3中的数据类型

2. A PRIMARY KEY can uniquely define a particular row. Such an attribute must always be defined as NOT NULL.

PRIMARY KEY可以唯一地定义特定行。 这样的属性必须始终定义为NOT NULL

3. AUTOINCREMENT automatically sets the corresponding attribute of the currently inserted record to a number that is one plus the value of AUTOINCREMENT (unless specified). AUTOINCREMENT defined attributes must necessarily be INTEGERS.

AUTOINCREMENT自动将当前插入的记录的相应属性设置为一个数字，再加上AUTOINCREMENT的值(除非指定)。 AUTOINCREMENT定义的属性必须一定是INTEGERS

sqlite-net入门

• 0
点赞
• 0
收藏
• 0
评论
11-27 4360
01-06 8876
09-27 3646
05-04 230
07-02 8547
03-04 6030
07-16 872
04-20 316
09-18
01-15
09-29 4042
06-20 8386
03-11 4191

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

©️2022 CSDN 皮肤主题：编程工作室 设计师：CSDN官方博客

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。