02--SQLite操作一步到位

SQLite数据库（一）：基本操作

SQLite 是一个开源的嵌入式关系数据库，实现自包容、零配置、支持事务的SQL数据库引擎。 其特点是高度便携、使用方便、结构紧凑、高效、可靠。 与其他数据库管理系统不同，SQLite 的安装和运行非常简单，在大多数情况下 - 只要确保SQLite的二进制文件存在即可开始创建、连接和使用数据库。如果您正在寻找一个嵌入式数据库项目或解决方案，SQLite是绝对值得考虑。

目录

SQLite数据库（一）：基本操作... 1

目录... 1

一、SQLite介绍... 2

1.    什么是SQLite. 2

2.    什么是数据库... 2

3.    数据库是如何存储数据的... 2

4.    数据库存储数据的步骤... 2

5.    Navicat 2

6.    如何在程序运行过程中操作数据库中的数据... 3

7.    SQL语句的种类... 3

二、表—创，查，显示，删除... 3

1.    安装... 3

2.    创建创建首个 SQLite 数据库... 4

3.    创建表：... 4

4.    修改表结构（列，视图，索引）... 5

5.    查看创建的表... 6

6.    删除创建的表... 6

7.   设置格式化查询结果：... 6

三、其他 SQLite 输出命令... 6

四、字段类型... 7

五、数据：插入、更新、删除... 8

1.    数据—插入（一行叫记录）... 8

2.    更新数据... 8

3.    删除数据... 8

六、条件语句... 9

七、DQL语句：数据查询显示... 9

1.    精确查找... 9

2.    模糊查询... 9

八、起别名... 10

九、计算记录的数量... 10

十、排序... 10

十一、limit 11

十二、简单约束... 11

十三、主键... 12

十四、外键约束... 12

 

 

一、SQLite介绍

1.      什么是SQLite

SQLite是一款轻型的嵌入式数据库。

它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。

它的处理速度比Mysql、PostgreSQL这两款著名的数据库都还快。

2.      什么是数据库

1)        数据库（Database）是按照数据结构来组织、存储和管理数据的仓库

2)        数据库可以分为2大种类。

关系型数据库（主流）和   对象型数据库

3)        常用关系型数据库

PC端：Oracle、MySQL、SQL Server、Access、DB2、Sybase

嵌入式\移动客户端：SQLite

3.   数据库是如何存储数据的

数据库的存储结构和excel很像，以表（table）为单位。

4.   数据库存储数据的步骤

1)        新建一张表（table）

2)        添加多个字段（column，列，属性）

3)        添加多行记录（row.record,每行存放多个字段对应的值）

 

5.      Navicat

Navicat是一款著名的数据库管理软件，支持大部分主流数据库（包括SQLite）

6.      如何在程序运行过程中操作数据库中的数据

1)        那得先学会使用SQL语句。

2)        SQL（structured query language）：结构化查询语言

3)        SQL是一种对关系型数据库中的数据进行定义和操作的语言。SQL语言简洁，语法简单，好学好用

4)        SQL中的常用关键字：有select、insert、update、delete、from、create、where、                    desc、 order、by、group、table、alter、view、index等等

5)        数据库中不可以使用关键字来命名表、字段

 

7.      SQL语句的种类

1. 数据定义语句（DDL：Data Definition Language）

包括create和drop等操作

在数据库中创建新表或删除表（create table或 drop table）

1. 数据操作语句（DML：Data Manipulation Language）

包括insert、update、delete等操作

上面的3种操作分别用于添加、修改、删除表中的数据

1. 数据查询语句（DQL：Data Query Language）

可以用于查询获得表中的数据

关键字select是DQL（也是所有SQL）用得最多的操作

其他DQL常用的关键字有where，order by，group by和having

二、表—创，查，显示，删除

1.      安装

1）进入 SQL 下载页面：http://www.sqlite.org/download.html

2）下载 Windows 下的预编译二进制文件包：

sqlite-shell-win32-x86-<build#>.zip
sqlite-dll-win32-x86-<build#>.zip

注意: <build#> 是 sqlite 的编译版本号

将 zip 文件解压到你的磁盘，并将解压后的目录添加到系统的 PATH 变量中，以方便在命令行中执行 sqlite 命令。

可选: 如果你计划发布基于 sqlite 数据库的应用程序，你还需要下载源码以便编译和利用其 API

sqlite-amalgamation-<build#>.zip

SQLite on Linux

在 多个 Linux 发行版提供了方便的命令来获取 SQLite：

1)        /* For Debian or Ubuntu /*  

2)        $ sudo apt-get install sqlite3 sqlite3-dev  

 

3)        /* For RedHat, CentOS, or Fedora/*  

4)        $ yum install SQLite3 sqlite3-dev 

 

2.      创建创建首个 SQLite 数据库

现在你已经安装了 SQLite 数据库，接下来我们创建首个数据库。在命令行窗口中输入如下命令来创建一个名为 test.db 的数据库

~$aqlite3

sqlite3 test.db 

3.      创建表：

l  create table 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;

//可以不指定字段类型

 我们往往都是指定类型。 
如： 
CREATE TABLE test1(a,b,c);  //语句结束用“;” 
CREATE TABLE test2(id int primary key autoincrement,name varchar(20));

primary key：这个字段有唯一的值不能重复；

l  create table if not exists 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;

l  CREATE [[TEMP|TEMPORARY]  TABLE  table_name  (column_definitions ，[constraints]);
中括号表示可选项，竖线表示在多个中选一，{}包含一个选项列表，表示必须从中选择一个
TEMP:声明创建的表为临时表，只存活于当前会话，一旦连接断开，就会被自动删除。
table_name：表示表名.
column_definitions:表示一个用逗号分隔的列定义(也称字段列表),每个字段包括一个字段名、字段类型和字段约束 如有多个约束，约束间使用空格分隔.
[, constraints]：表级约束.例如，你可以用UNIQUE约束来规定所有记录中某个字段的值要各不相同

l  示例:

create table t_student (id integer, name text, age inetger, score real) ;

如下所示为一个创建表的语句:
CREATE TABLE contacts ( id INTEGER PRIMARY KEY, 

name TEXT NOT NULL COLLATE NOCASE,
phone TEXT NOT NULL DEFAULT 'UNKNOWN',

UNIQUE (name,phone) );

创建了一个名为contacts的表，该表包含三个字段，分别为：
id字段 integer类型 字段约束为PRIMARY KEY.  （表示该字段为关键Key）
name字段 text类型 字段约束为not null，COLLATE 和NOCASE.  (表示该字段值不允许空， 不区分大小写)
phone字段 text类型 字段约束为NOT NULL， DEFAULT 'UNKNOWN'(表示该字段值不允许空 默认值为 unknow)
表级约束为UNIQUE (name,phone)表示(name和phone的组合是唯一的，即允许同名的联系人，但不允许同名又同电话号码的联系人).

 

4.      修改表结构（列，视图，索引）

1)  增加列：

sqlite> alter table mytable add column email text not null '' collate nocase;; 

2)  创建视图：

sqlite> create view nameview as select * from mytable; 

3)  创建索引：

sqlite> create index test_idx on mytable(value);  

4)        创建视图：
   我们这里只是给出简单的SQL命令示例，具体的含义和技术细节可以参照上面的创建数据表部分，如临时视图、"IF NOT EXISTS"从句等。
    1). 最简单的视图：
    sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col > 100;    
     2). 创建临时视图：
    sqlite> CREATE TEMP VIEW tempview AS SELECT * FROM testtable WHERE first_col > 100;
    3). "IF NOT EXISTS"从句：
    sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col > 100;
    Error: table testview already exists
    sqlite> CREATE VIEW IF NOT EXISTS testview AS SELECT * FROM testtable WHERE first_col > 100;

5)        删除视图：
    该操作的语法和删除表基本相同，因此这里只是给出示例：
    sqlite> DROP VIEW testview;
    sqlite> DROP VIEW testview;
    Error: no such view: testview
    sqlite> DROP VIEW IF EXISTS testview;    

5.   查看创建的表 

.table 

 .schema 表名

6.      删除创建的表

drop table 表名 ；

drop table if exists 表名；

7.  设置格式化查询结果：

1)  sqlite> .mode column;  

2)  sqlite> .header on;  

3)  sqlite> select * from test;  

4)  id          value  

5)  ----------- -------------  

6)  1           Micheal  

7)  2           Jenny  

8)  3           Francis  

9)  4           Kerk 

.mode column 将设置为列显示模式，.header 将显示列名。

 

三、其他 SQLite 输出命令

1. 1.        显示表结构：

sqlite> .schema [table] 

1. 2.        获取所有表和视图：

sqlite > .tables 

1. 3.        获取指定表的索引列表：

sqlite > .indices [table ] 

1. 4.        导出数据库到 SQL 文件：

sqlite > .output [filename ]  

sqlite > .dump  

sqlite > .output stdout 

1. 5.        从 SQL 文件导入数据库：

sqlite > .read [filename ] 

1. 6.        格式化输出数据到 CSV 格式：

sqlite >.output [filename.csv ]  

sqlite >.separator ,  

sqlite > select * from test;  

sqlite >.output stdout                将输出恢复到标准输出

1. 7.        从 CSV 文件导入数据到表中：

sqlite >create table newtable ( id integer primary key, value text );  

sqlite >.import [filename.csv ] newtable 

1. 8.        备份数据库：

/* usage: sqlite3 [database] .dump > [filename] */  

sqlite3 mytable.db .dump > backup.sql 

1. 9.        恢复数据库：

/* usage: sqlite3 [database ] < [filename ] */  

sqlite3 mytable.db < backup.sql 

四、字段类型

null	空值。
integer	带符号的整型，具体取决有存入数字的范围大小。
real	浮点数字。
text	字符串文本。
blob	二进制对象。
smallint	16 位元的整数。
interger	32 位元的整数。
decimal(p,s)	p 精确值和 s 大小的十进位整数，精确值p是指全部  有几个数(digits)大小值，  s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
float	32位元的实数。
double	64位元的实数。
char(n)	n 长度的字串，n不能超过 254。
varchar(n)	长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n)	和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n)	可变长度且其最大长度为 n 的双字元字串，n不能超过 2000
date	包含了 年份、月份、日期。
time	包含了 小时、分钟、秒。
timestamp	包含了 年、月、日、时、分、秒、千分之一秒。

 

五、数据：插入、更新、删除

1.      数据—插入（一行叫记录）

l  insert into <表名>(字段列表) values(对应的值);

 //字符串加双引号或单引号

l  insert into 表名 (字段1, 字段2, …) values (字段1的值, 字段2的值, …) ;

insert into test2(name,age) values("zhangsan",20);

l  insert into t_student (name, age) values (‘wg’, 10) ;

l sqlite> insert into mytable(id, value) values(1, 'Micheal');  

l sqlite> insert into mytable(id, value) values(2, 'Jenny');  

l sqlite> insert into mytable(value) values('Francis');  

l sqlite> insert into mytable(value) values('Kerk'); 

2.      更新数据

l  update 表名 set 字段1 = 字段1的值, 字段2 = 字段2的值, … ;

 update t_student set name = ‘jack’, age = 20 ;

 上面的示例会将t_student表中所有记录的name都改为jack，age都改为20

l  update <表名> set field1='newValue',field2='newValue2' where id=2;

sqlite> update student1 set name='wo' where id=01;

3.      删除数据

• delete from <表名> where 条件； 
  如果没有where部分，那么会清空整个表

delete from 表名 ; 上面的示例会将t_student表中所有记录都删掉

delete from test2 where id=2;

delete from test2 where socre<60;注意

六、条件语句

如果只想更新或者删除某些固定的记录，那就必须在DML语句后加上一些条件

条件语句的常见格式

• where 字段 = 某个值 ;   // 不能用两个 =
• where 字段 is 某个值 ;   // is 相当于 = 
• where 字段 != 某个值 ; 
• where 字段 is not 某个值 ;   // is not 相当于 != 
• where 字段 > 某个值 ; 
• where 字段1 = 某个值 and 字段2 > 某个值 ;  // and相当于C语言中的 &&
• where 字段1 = 某个值 or 字段2 = 某个值 ;  //  or 相当于C语言中的 ||

七、DQL语句：数据查询显示

1.      精确查找

select <列的列表中间用逗号隔开，查询所有列用*>  from <表名>;

select * from test2 where id=1;

sprintf(show, "select word from history where username='%s';", climsg.name);

2.      模糊查询 

select * from <表格名字>；//所有数据；
select * from test2 where name like "w%"; //%表示任意多个字符 
select * from test2 where name like "w_"; //_表示任意一个字符

select * from test2 limit 0,2;//0表示从第一行开始，2表示查询两条记录      select * from t_student where age > 10 ;  //  条件查询

select * from test2 where age is null; //查询age为空的记录

select * from test2 where age not null;//查询age不为空的记录

 

select语句是sql中最最复杂的语句,是sql精华所在.从语法上说,select语句由一系列字句组成，每个字句完成一种特定的操作.select语句中除select字句外，其余所有的字句都是可选的.

八、起别名

  格式(字段和表都可以起别名)

• select 字段1 别名 , 字段2 别名 , … from 表名 别名 ; 
• select 字段1 别名, 字段2 as 别名, … from 表名 as 别名 ;
• select 别名.字段1, 别名.字段2, … from 表名 别名 ;

示例：

n  select name myname, age myage from t_student ;

n  给name起个叫做myname的别名，给age起个叫做myage的别名

九、计算记录的数量

    格式

• select count (字段) from 表名 ;
• select count ( * ) from 表名 ;

    示例

• select count (age) from t_student ;
• select count ( * ) from t_student where score >= 60;
• 查询出来的结果可以用order by进行排序
• select * from t_student order by 字段 ;
• select * from t_student order by age ;
• 默认是按照升序排序（由小到大），也可以变为降序（由大到小）
• select * from t_student order by age desc ;  //降序
• select * from t_student order by age asc ;   // 升序（默认）
• 也可以用多个字段进行排序
• select * from t_student order by age asc, height desc ;
• 先按照年龄排序（升序），年龄相等就按照身高排序（降序）
• 使用limit可以精确地控制查询结果的数量，比如每次只查询10条数据

十、排序

十一、limit

格式

• select * from 表名 limit 数值1, 数值2 ;

示例：select * from t_student limit 4, 8 ;

可以理解为：跳过最前面4条语句，然后取8条记录

十二、简单约束

建表时可以给特定的字段设置一些约束条件，常见的约束有：

not null：规定字段的值不能为null

unique:  规定字段的值必须唯一

default: 指定字段的默认值

建议：尽量给字段设定严格的约束，以保证数据的规范性）

• 示例
• create table t_student (id integer, name text not null unique, age integer not null default 1) ;
• name字段不能为null，并且唯一
• age字段不能为null，并且默认为1

十三、主键

主键（Primary Key，简称PK）用来唯一地标识某一条记录

主键的设计原则：

1)        主键应当是对用户没有意义的

2)        永远也不要更新主键

3)        主键不应包含动态变化的数据

4)        主键应当由计算机自动生成

十四、外键约束

• 利用外键约束可以用来建立表与表之间的联系
• 外键的一般情况是：一张表的某个字段，引用着另一张表的主键字段