Sqlite数据库知识必知必会(下篇)

数据库 其他数据库
这篇文章主要介绍了Sqlite数据库的数据表、索引、视图、触发器和事务操作知识,干货满满。

[[393279]]

大家好,我是IT共享者,人称皮皮。

前面几天给大家分享了MySQL数据库知识,没来得及看的小伙伴可以前往:Mysql查询语句进阶知识集锦,一篇文章教会你进行Mysql数据库和数据表的基本操作,关于数据库的安装可以参考:手把手教你进行Mysql5.x版本的安装及解决安装过程中的bug。

上篇文章主要给大家介绍了Sqlite数据库的安装、图形化管理系统、基本数据类型、基本关键字和数据库的相关操作,干货|Sqlite数据库知识必知必会(上篇),今天皮皮紧接上篇文章,继续给大家分享数据库知识,一起来看看吧。

前言

前面我们学习了很多数据库,虽然它们功能非常丰富,但是占用空间比较大,使得我们的系统负荷变大,这对于我们新手小白来说不是一个理想的选择,于是乎,Sqlite闪亮登场,它受人喜爱的真正原因正是因为它足够小,无需安装和管理配置,简单容易上手,支持数据库的大小也有2TB,足够我们个人开发爱好者使用了。

一、数据表相关操作

1).创建数据表

  1. CREATE TABLE student ( 
  2. ID INTEGER DEFAULT '1406061' PRIMARY KEY AUTOINCREMENT NOT NULL
  3. NAME NVARCHAR(100)  UNIQUE NOT NULL
  4. score INTEGER  NOT NULL
  5. time TIMESTAMP  NOT NULL 
  6. ); 

我们可以使用SqliteAdmin快速生成表,如下:

 

这样我们的表就建立成功了。这里小编在软件中用的是DEMO这个数据文件,而在命令窗口中用的是TEST这个数据库文件,因为一个数据库文件不允许在两个位置打开的缘故。

2).删除表

  1. DROP TABLE people; 

3).显示表名

  1. .tables 

 

4).数据表的增删改查

1)).增加

这里我们可以给刚创建的表添加一个数据,如下:

  1. insert into student(ID,NAME,score,timevalues(1406063,'任性的90后boy',90,'2020-07-06 12:23:32'); 

 

这里我插入了三行数据,其中有一行报错是因为我使用了相同的ID号,而我的数据库的ID号是不允许重复的,故而会报错,改成其它的就好了,如果你把ID设为自增的话,就不需要你去写了,这里我用到了自增,所以大家不必写Sqlite会自动填充进去的。这里也可以省掉字段名,直接写值,如下:

我们还可以给表添加新列,如下;

  1. alter table people add  column aa char(10);#添加列并且列名为aa 

注:Sqlite3并不支持带有UNIQUE约束的列

2)).删除

  1. delete from people;#删除表数据 
  2. delete from people where score<20#删除people数据表score数值小于20的记录 

3)).修改

其实就是更新操作,如下;

  1. update people set score=100 where  age=46;#当age=46时score设置为100 

4)).查找

查找不管是在哪个数据库中方法都是相当多的,因为查询操作是最常见也是最频繁的,下面我们就来详细说说:

1))).查询所有的结果

  1. select * from student; 

 

我们可以看到虽然结果打印出了,但是并不是那么美观,我们将它的打印格式化一下,如下;

这下就完美多了。虽然这种方法很不错,但是如果数据列很多的话就无法完全显示了,这个时候我们就需要设置列的宽度,如下:

  1. .width 10 20 15 

2))).查询指定结果

  1. select NAME,score from student; #打印出NAME和score列的结果 
  2. select * from people where score<20 and age<40;#当score小于20并且age小于40的时候 
  3. select *from people where score between 10 and 30;#当score为10~30之间时 
  4. select *from people where NAME LIKE '放大%';#当name的开始处含有放大 
  5. select *from people where NAME glob '放大*';#与上同 
  6. select *from where score not in(12,23);#当score不属于12~23 

3))).查找排序

  1. select * from people order by score desc; #对score降序排序 

4))).查找指定数量的数据

  1. select * from people  limit 3 offset 2#偏移2个单位然后输出三行数据,其实就是输出后三行数据 

5))).查找重复数据并消除

  1. select distinct * from people where score>10;#当score大于10的时候消除重复数据 

6))).查找数据并进行分组

  1. select NAME ,max(age) from people where score>10 group by age#返回score大于10的时候age的最大值 

7))).查找数据进行分组并过滤分组

  1. select NAME ,max(age) from people where score>10 group by age having count(NAME)>1;#名称计数大于1 

5).表的重命名

  1. alter table people rename to man;#将表名people改为man 

 

6).数据表的连接

这里的连接分为内外交叉三种连接,使用好了可以极大提高我们的工作效率。这里我们再次创建一张表,如下:

1)).交叉连接

  1. select * from people cross join woman; 

 

2)).内连接

  1. select * from people inner join woman; 

3)).外连接

  1. select * from people outer join woman; 

注:Sqlite3只支持左外连接。

7).表的复制

  1. create table man as select * from people where 1=0;#复制表结构 
  2. create table woman as select * from people;#复制表结构和数据 

8).临时表

有时候我们不需要存储所有表的数据,这个时候临时表就很有使用的必要了。如下:

  1. #创建一个临时表 
  2. create  temporary table temp_table( 
  3.     id int primary key
  4.     name varchar(50) unique not null
  5.     age int not null 
  6. ); 
  7.  
  8. #查看临时表 
  9. temp.temp_table 
  10.  
  11. #删除临时表 
  12. drop table temp.temp_table; 

5.索引操作

1).创建索引

  1. create index user on people(score);#在people表的score字段上设置索引 
  2. create unique name on people(NAME);#在people表的score字段上设置唯一索引 
  3. create index pa on people(score,age)#在people表的score和age字段上设置索引 

2).查看索引

  1. SELECT * FROM sqlite_master WHERE type = 'index'

 

这里我们之前仅仅只是创建了一个索引,那就是”user“,为何会有两个索引了,而且最上面的索引好像跟我们并没有什么关系,也不像是自己创建的,其实,这个就是隐式索引,这个隐式索引在我们创建表的时候就连带着一起创建了的,只是为了让查询更加快速而已,影响不大。

3).使用索引

这里涉及到两个关键字请配套使用,表示索引来自于哪里的意思。

  1. select * from people indexed by user where score>10; 

4).删除索引

  1. drop index user

注:数据量较小不应使用索引,以免给系统增压。

6.视图操作

1).创建视图

  1. create view name as select NAME from people;#创建NAME字段的视图 

2).使用视图

  1. select *from name

这样就可以直接输出视图字段的所有值了。

3).删除视图

  1. drop view name

7.触发器

触发器就是使得多个表的交互增强,互相调用,满足条件就调用哪个表,语法格式如下:

  1. create trigger 触发器 after(before) insert(delete update
  2. on 表1 
  3. begin 
  4. insert into 表2(表2字段,表2字段,n) VALUES(表1值, 表1值,,,,n); 
  5. END

1).创建触发器

  1. #给people表创建一个触发器 
  2. create trigger cf 
  3. after insert on people 
  4. begin 
  5. insert into woman(w_ID,NAME,SCORE,datevalues(new.ID,'向前插入',100,datetime('now')); 
  6. end

2).查看触发器

  1. select * from sqlite_master where type = 'trigger'AND tbl_name='people';#查看people表的触发器 

3).删除触发器

  1. drop trigger cf; 

8.事务

有了事务可以让我们的Sqlite语句工作的更加有条不紊,一般大致分为事务开始 提交 回滚 结束,下面来看看:

  1. begin;#开始 
  2. insert into people('gf',65,datetime('now')); 
  3. rollback; #回滚就是撤销 
  4. COMMIT;提交保存数据 
  5. end;#结束 

这里的数据没有插入到“people”表中哦,是不是超级简单了?

二、总结

这篇文章主要介绍了Sqlite数据库的数据表、索引、视图、触发器和事务操作知识,干货满满。

皮皮自从学过Sqlite之后,就果断卸载了Mysql和Mongo了,主要是目前还用不着Mysql数据库和Mongo那么强大的数据,够用就好。

 

责任编辑:姜华 来源: IT共享之家
相关推荐

2021-04-12 10:00:47

Sqlite数据库CMD

2022-08-19 10:31:32

Kafka大数据

2020-07-10 07:58:14

Linux

2024-01-09 13:58:22

PandasPython数据分析

2024-01-03 07:56:50

2022-05-18 09:01:19

JSONJavaScript

2019-11-06 10:56:59

Python数据分析TGI

2021-06-09 11:06:00

数据分析Excel

2018-12-28 09:11:28

运维监控开源

2017-07-12 15:32:12

大数据大数据技术Python

2022-04-25 21:40:54

数据建模

2015-10-20 09:46:33

HTTP网络协议

2019-01-30 14:14:16

LinuxUNIX操作系统

2018-10-26 14:10:21

2023-05-08 15:25:19

Python编程语言编码技巧

2015-08-17 16:05:35

javascript对象编程

2023-04-20 14:31:20

Python开发教程

2023-12-26 12:10:13

2023-09-12 11:25:15

2022-08-26 14:46:31

机器学习算法线性回归
点赞
收藏

51CTO技术栈公众号