[原创]数据库操作

2023-6-13 17:58 5849

[原创]数据库操作

sanganlei

2023-6-13 17:58

5849

一、表、触发器、索引等操作

1、直接创建表

		CREATE TABLE COMPANY(
		   ID INT PRIMARY KEY     NOT NULL,
		   NAME           TEXT    NOT NULL,
		   AGE            INT     NOT NULL,
		   ADDRESS        CHAR(50),
		   SALARY         REAL
		);

2、如果表不存在，则创建表

		CREATE TABLE if not exists tb_PEExe (
			   id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
			   isblack INTEGER, 
			   proc_name VARCHAR (260), 
			   proc_path VARCHAR (520), 
			   proc_md5 VARCHAR (64));

3、如果表存在，则删除

DROP TABLE IF EXISTS Person

4、创建带时间戳的表

		CREATE TABLE t_VirusRecord(
			pkt_id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
			flags INTEGER DEFAULT 0,
			src_ip TEXT COLLATE NOCASE,
			src_port INTEGER DEFAULT 0,
			dst_ip TEXT COLLATE NOCASE DEFAULT '',
			dst_port INTEGER DEFAULT 0,
			family INTEGER DEFAULT 0,
			proto INTEGER DEFAULT 0,
			direction_input INTEGER DEFAULT 0,
			data TEXT COLLATE NOCASE DEFAULT '',
			datalen BIGINT DEFAULT 0,
			FDateTimeCreate TEXT COLLATE NOCASE DEFAULT(datetime('now', 'localtime')));

5、创建索引

CREATE INDEX IF NOT EXISTS index_file_md5 ON risk_file_table(file_md5);

6、创建触发器并使用（按照时间删除、按照条数删除记录）

6.1创建一个表

				CREATE TABLE COMPANY(
			   ID INT PRIMARY KEY     NOT NULL,
			   NAME           TEXT    NOT NULL,
			   AGE            INT     NOT NULL,
			   ADDRESS        CHAR(50),
			   SALARY         REAL,
			   FDateTimeCreate DATETIME COLLATE NOCASE DEFAULT(datetime('now', 'localtime'))
			);

6.2创建触发器

6.2.1时间删除（这个触发器验证成功）

				CREATE TRIGGER delete_old_data_trig AFTER INSERT ON COMPANY FOR EACH ROW
				BEGIN
					DELETE FROM COMPANY WHERE FDateTimeCreate <= datetime('now', 'localtime', '-2 minutes');
				END

6.2.2按照条数删除(验证通过当条数大于10条时，按照rowid排序（升序列）删除前3条老数据)

				CREATE TRIGGER delete_old_data_trig2 AFTER INSERT ON COMPANY WHEN (SELECT count(*) FROM COMPANY) > 10
				BEGIN
					DELETE FROM COMPANY WHERE rowid IN  (SELECT rowid FROM COMPANY ORDER BY rowid LIMIT 3);
				END

6.3验证触发器是否生效，插入一条记录

				INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
				VALUES (1, 'Paul', 32, 'California', 20000.00 );

7、创建审计触发器demo

7.1 让我们假设一个情况，我们要为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：

			CREATE TABLE COMPANY(
				   ID INT PRIMARY KEY     NOT NULL,
				   NAME           TEXT    NOT NULL,
				   AGE            INT     NOT NULL,
				   ADDRESS        CHAR(50),
				   SALARY         REAL
				);

7.2 为了保持审计试验，我们将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中：

			 CREATE TABLE AUDIT(
				EMP_ID INT NOT NULL,
				ENTRY_DATE TEXT NOT NULL
			);

7.3 在这里，ID 是 AUDIT 记录的 ID，EMP_ID 是来自 COMPANY 表的 ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。

所以，现在让我们在 COMPANY 表上创建一个触发器，如下所示：

				CREATE TRIGGER audit_log AFTER INSERT 
				ON COMPANY
				BEGIN
				   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
				END;

7.4 现在，我们将开始在 COMPANY 表中插入记录，这将导致在 AUDIT 表中创建一个审计日志记录。

因此，让我们在 COMPANY 表中创建一个记录，如下所示

			 INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
				VALUES (1, 'Paul', 32, 'California', 20000.00 );

7.5 同时，将在 AUDIT 表中创建一个记录。

这个纪录是触发器的结果，这是我们在 COMPANY 表上的 INSERT 操作上创建的触发器（Trigger）。

类似的，可以根据需要在 UPDATE 和 DELETE 操作上创建触发器（Trigger）

8、创建触发器

修改表结构增加一个时间戳字段，插入数据时检测以前的记录超过两分钟删除（触发器1），最大条数限制为5条（触发器2）

8.1 让我们假设一个情况，我们要为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：

			CREATE TABLE COMPANY(
				   ID INT PRIMARY KEY     NOT NULL,
				   NAME           TEXT    NOT NULL,
				   AGE            INT     NOT NULL,
				   ADDRESS        CHAR(50),
				   SALARY         REAL
				);

8.2 修改COMPANY表结构

alter table COMPANY add column FDateTimeCreate DATETIME;

8.3 创建触发器，插入一条数据的时候填充记录创建时间戳，同时删除老的数据比如三天前的

				CREATE TRIGGER delete_old_data_trig AFTER INSERT ON COMPANY FOR EACH ROW
				BEGIN
					DELETE FROM COMPANY WHERE FDateTimeCreate <= datetime('now', 'localtime', '-2 minutes');
					UPDATE COMPANY SET FDateTimeCreate=datetime('now', 'localtime') WHERE rowid=new.rowid;
				END

8.4 创建触发器限制某个表中的数据总数为5条

				CREATE TRIGGER delete_till_5 INSERT ON COMPANY WHEN (select count(*) from COMPANY)>=5 
				BEGIN
					DELETE FROM COMPANY WHERE rowid IN  (SELECT rowid FROM COMPANY ORDER BY rowid limit (select count(*) -5 + 1 from COMPANY ));
				END

8.5 向COMPANY表插入记录

				INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
				VALUES (1, 'Paul', 32, 'California', 20000.00 );

9、删除触发器

9.1 删除一个名为“audit_log”的触发器。

DROP TRIGGER audit_log;

二、增删改查

1、插入

1.1插入记录

INSERT INTO tb_PEExe(isblack, proc_name, proc_path,proc_md5) VALUES(1, 'test.exe', 'c:\test.exe', 'sanganleitest');

2、删除

2.1、where子句为整形，删除

DELETE FROM tb_PEExe WHERE id=2;

2.2、where子句为字符串类型删除

DELETE FROM tb_PEExe WHERE proc_name='tests.exe';

2.3 按照某个字段排序后，删除前n条数据

DELETE from TableName  WHERE rowid IN (SELECT rowid FROM TableName ORDER BY id LIMIT 5);

备注：rowid为sqlite中每行默认的字段

id为根据那个字段进行拍讯

LIMIT为限制的函数，跟n行，代表前n条记录

2.4 清空数据空的某个表数据（不删除表本身），执行完vacuum这一句会使数据库占用的空间真正的减小，

不执行vacuum的话，即使DELETE了表中记录，db占用的硬盘空间大小也不会减小。

			      DELETE FROM file_info_table;
				  vacuum;

2.5 删除2分钟以前的数据

DELETE FROM risk_file_table WHERE record_create_time <= datetime('now', 'localtime', '-2 minutes');

2.6 删除3小时以前的数据demo

2.6.1 创建temps表（包含日期字段）

			        CREATE TABLE temps(
						name TEXT DEFAULT 'RPi.CPU',
						tdatetime DATETIME DEFAULT (datetime('now', 'localtime')),
						temperature NUMERIC NOT NULL
					);

2.6.2 插入3条记录，插入的时间分别是3小时以前、2小时以前、1小时以前的时间点

				INSERT INTO temps
				VALUES('RPi.CPU', datetime('now', 'localtime', '-3 hours'), 40.1);
						
				INSERT INTO temps(name, tdatetime, temperature)
				VALUES('RPi.CPU', datetime('now', 'localtime', '-2 hours'), 40.2);

				INSERT INTO temps(tdatetime, temperature)
				VALUES(datetime('now', 'localtime', '-1 hours'), 40.3);

2.6.3 清除3小时以前的数据

DELETE FROM temps WHERE tdatetime <= datetime('now', 'localtime', '-3 hours')

2.7 删除3天以前的数据demo

2.7.1 创建temps表（包含日期字段）

		        CREATE TABLE temps(
					name TEXT DEFAULT 'RPi.CPU',
					tdatetime DATETIME DEFAULT (datetime('now', 'localtime')),
					temperature NUMERIC NOT NULL
				);

2.7.2 插入3条记录，插入的时间分别是3天以前、2天以前、1天以前的时间点

				INSERT INTO temps
				VALUES('RPi.CPU', datetime('now', 'localtime', '-3 days'), 40.1);
						
				INSERT INTO temps(name, tdatetime, temperature)
				VALUES('RPi.CPU', datetime('now', 'localtime', '-2 days'), 40.2);

				INSERT INTO temps(tdatetime, temperature)
				VALUES(datetime('now', 'localtime', '-1 days'), 40.3);

2.7.3 清除3小时以前的数据

DELETE FROM temps WHERE tdatetime <= datetime('now', 'localtime', '-3 days')

3、修改

3.1 修改记录

UPDATE tb_PEExe SET proc_path='sanganlei' WHERE proc_name='unins000.exe';

3.2 修改表结构，新增一列。

命令：ALTER TABLE table-name ADD COLUMN column-namecolumn-type

例如：在student表中添加一列名为name，类型为varchar:

	alter table student add column name varchar;
        alter table catalog add column xxx1 char(20) default '';

4、查找

4.1、where子句为字符串类型查找

SELECT * FROM tb_PEExe WHERE proc_name='unins000.exe';

4.2、where子句为整形查找

SELECT * FROM tb_PEExe WHERE isblack=1;

4.3、查找仅显示指定的列

SELECT proc_name, proc_path FROM tb_PEExe WHERE isblack=1;

4.4、LIMIT 子句返回前3行记录

SELECT * FROM tb_PEExe LIMIT 3;

4.5、LIMIT 子句返回第3行到第5行之间的记录

SELECT * FROM tb_PEExe LIMIT 3 OFFSET 2;

4.6、返回的结果按照指定升序排列

SELECT * FROM tb_PEExe ORDER BY proc_name ASC;

4.7、返回的结果按照指定降序排列

SELECT * FROM tb_PEExe ORDER BY proc_name DESC;

4.8、统计满足条件的函数

SELECT count(*) FROM tb_PEExe WHERE isblack = 1;

4.9、limit和like子句合起来用(模糊查询分页查找)

SELECT * FROM tb_PEExe WHERE proc_name LIKE '%stand%' limit 1,3

4.10、不带条件的分页查询1(例如下面语句是查询第5页，每页15个条) limit子句的范围就是：

(nPage-1)* , 每页条数即：（5-1）*15,15 也就是limit 60,15

SELECT * FROM tb_PEExe order by id desc limit 60,15

4.11、带条件的分页查询2

SELECT * FROM tb_PEExe WHERE proc_name LIKE '%stand%' AND isblack=0 order by id desc limit 0 , 15

4.12、查询某一列的重复次数

select lib_md5,count(0) as 重复次数 from tb_PEDll group by lib_md5 having count(lib_md5) > 0 order by 重复次数

4.13、查询某一列的重复次数大于1的所有记录

select * from (select lib_md5,count(0) as 重复次数 from tb_PEDll group by lib_md5 having count(lib_md5) > 0 order by 重复次数) where 重复次数 > 1

4.14、查找有重复值的记录（只显示一个重复值，比如MD5值为1111的一共有10，只显示其中的一个）

SELECT * FROM test_table GROUP BY MD5 HAVING COUNT(MD5)>1;

4.15 显示某一字段MD5重复的次数>1的所有记录

SELECT * FROM file_info_table WHERE MD5 IN(
				SELECT MD5 FROM file_info_table GROUP BY MD5 HAVING COUNT(MD5)>1
				);

4.16 计算某一列的平均值

SELECT avg(calc_md5_cost_time) FROM file_info_table;

4.17、查看数据库中所有表名字

select name from sqlite_master where type='table' order by name;

4.18、查看数据库表内容

select * from sqlite_master;

4.19、判断指定的表是否存在，可以用如下语句：

select count(*) from sqlite_master where type='table' and name = 'file_info_table'

4.20 查看某个表的所有字段信息

PRAGMA table_info([file_info_table])

4.21 查看所有的触发器

SELECT name FROM sqlite_master WHERE type = 'trigger';

4.22、按照时间筛选数据

select * from COMPANY where FDateTimeCreate >= Datetime('2009-11-13 00:00:00');

4.23、显示一个时间

select datetime('now','localtime', '-2 minutes');

[培训]二进制漏洞攻防（第3期）；满10人开班；模糊测试与工具使用二次开发；网络协议漏洞挖掘；Linux内核漏洞挖掘与利用；AOSP漏洞挖掘与利用；代码审计。

最后于 2023-6-13 18:00 被sanganlei编辑，原因：

#基础知识 #开发技巧 #其他

收藏・3

点赞・0

打赏

最新回复 (0)
	游客登录 \| 注册方可回帖　回帖　表情雪币赚取及消费高级回复

sanganlei

发帖

回帖

RANK

关注

私信

他的文章

[原创]数据库操作

[原创]最新版360安全卫士极速版蓝屏分析

windbg查看STL类型数据

[原创]cmake使用

[原创]windows API汇总

关于我们

联系我们

企业服务

看雪公众号