当前位置:网站首页 / 数据库 / 正文

ay 写给自己的sqlite笔记【完结】[5]

时间:2016年05月15日 | 作者 : aaronyang | 分类 : 数据库 | 浏览: 1163次 | 评论 0

索引-ayjs.net

索引有助于加快 SELECT 查询和 WHERE 子句,但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除,但不会影响数据。

使用 CREATE INDEX 语句创建索引,它允许命名索引,指定表及要索引的一列或多列,并指示索引是升序排列还是降序排列。

索引也可以是唯一的,与 UNIQUE 约束类似,在列上或列组合上防止重复条目


创建索引,基本

CREATE INDEX index_name ON table_name;


单列索引/唯一索引

CREATE INDEX index_name
ON table_name (column_name);


组合索引

CREATE INDEX index_name
on table_name (column1, column2);

是否要创建一个单列索引还是组合索引,要考虑到您在作为查询过滤条件的 WHERE 子句中使用非常频繁的列。

如果值使用到一个列,则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用,则选择使用组合索引


隐式索引

隐式索引是在创建对象时,由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束


查看索引,等同于.indices 表名

SELECT * FROM sqlite_master WHERE type = 'index'

删除索引

DROP INDEX index_name;


避免场景

索引不应该使用在较小的表上。

索引不应该使用在有频繁的大批量的更新或插入操作的表上。

索引不应该使用在含有大量的 NULL 值的列上。

索引不应该使用在频繁操作的列上


按照索引顺序查询

SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;



修改表名

ALTER TABLE database_name.table_name 
RENAME TO new_table_name;

增加列,新添加的列是以 NULL 值来填充的。

ALTER TABLE OLD_COMPANY 
ADD COLUMN SEX char(1);



删除表

从已有的表中删除全部的数据

DELETE FROM table_name;

DROP TABLE 命令删除整个表,然后再重新创建一遍

DROP TABLE table_name;

如果 DELETE TABLE 命令删除所有记录,建议使用 VACUUM 命令清除未使用的空间

DELETE FROM COMPANY;
VACUUM;





视图 跟sqlserver一样

CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;
SELECT * FROM COMPANY_VIEW;

删除View 

DROP VIEW view_name;





事务

BEGIN TRANSACTION:开始事务处理。可以简写为Begin

COMMIT:保存更改,或者可以使用 END TRANSACTION 命令。

ROLLBACK:回滚所做的更改。

    sqlite> BEGIN TRANSACTION;
    sqlite> INSERT INTO testtable VALUES(1);
    sqlite> INSERT INTO testtable VALUES(2); 
    sqlite> COMMIT TRANSACTION;      --显示事务被提交,数据表中的数据也发生了变化。
    sqlite> SELECT COUNT(*) FROM testtable;
    COUNT(*)
    ----------
    2
    sqlite> BEGIN TRANSACTION;
    sqlite> INSERT INTO testtable VALUES(1);
    sqlite> ROLLBACK TRANSACTION;  --显示事务被回滚,数据表中的数据没有发生变化。
    sqlite> SELECT COUNT(*) FROM testtable;
    COUNT(*)
    ----------
    2



子查询

SELECT * FROM COMPANY 
     WHERE ID IN (SELECT ID 
                  FROM COMPANY 
                  WHERE SALARY > 45000) ;


INSERT INTO COMPANY_BKP
     SELECT * FROM COMPANY 
     WHERE ID IN (SELECT ID 
                  FROM COMPANY) ;


UPDATE COMPANY
     SET SALARY = SALARY * 0.50
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE >= 27 );
 DELETE FROM COMPANY
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE > 27 );


Autoincrement(自动递增)

CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY   AUTOINCREMENT
   )


Explain(解释)

来自 EXPLAIN 和 EXPLAIN QUERY PLAN 的输出只用于交互式分析和排除故障。

输出格式的细节可能会随着 SQLite 版本的不同而有所变化。

应用程序不应该使用 EXPLAIN 或 EXPLAIN QUERY PLAN,因为其确切的行为是可变的且只有部分会被记录

EXPLAIN [SQLite Query]

EXPLAIN  QUERY PLAN [SQLite Query]




VACUUM

VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件,然后清空主数据库,并从副本中重新载入原始的数据库文件。这消除了空闲页,把表中的数据排列为连续的,另外会清理数据库文件结构。

如果表中没有明确的整型主键(INTEGER PRIMARY KEY),VACUUM 命令可能会改变表中条目的行 ID(ROWID)。VACUUM 命令只适用于主数据库,附加的数据库文件是不可能使用 VACUUM 命令。

如果有一个活动的事务,VACUUM 命令就会失败。VACUUM 命令是一个用于内存数据库的任何操作。由于 VACUUM 命令从头开始重新创建数据库文件,所以 VACUUM 也可以用于修改许多数据库特定的配置参数

手动 VACUUM

下面是在命令提示符中对整个数据库发出 VACUUM 命令的语法:

$sqlite3 database_name "VACUUM;"

您也可以在 SQLite 提示符中运行 VACUUM,如下所示:

sqlite> VACUUM;

您也可以在特定的表上运行 VACUUM,如下所示:

sqlite> VACUUM table_name;


自动 VACCUM(Auto-VACUUM)

SQLite 的 Auto-VACUUM 与 VACUUM 不大一样,它只是把空闲页移到数据库末尾,从而减小数据库大小。通过这样做,它可以明显地把数据库碎片化,而 VACUUM 则是反碎片化。所以 Auto-VACUUM 只会让数据库更小。

在 SQLite 提示符中,您可以通过下面的编译运行,启用/禁用 SQLite 的 Auto-VACUUM:

sqlite> PRAGMA auto_vacuum = NONE;  -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL;  -- 1 means enable incremental vacuum
sqlite> PRAGMA auto_vacuum = FULL;  -- 2 means enable full auto vacuum

您可以从命令提示符中运行下面的命令来检查 auto-vacuum 设置:

$sqlite3 database_name "PRAGMA auto_vacuum;"


www.ayjs.net拥有,未经许可,不许转载,违者追究法律责任



日期操作

blob.png

blob.png


这里参数我使用了now

blob.png



下面是计算当年 10 月的第一个星期二的日期:

 SELECT date('now','start of year','+9 months','weekday 2');
2015-10-01

下面是计算给定 UNIX 时间戳 1092941466 的日期和时间:

SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06


下面是计算给定 UNIX 时间戳 1092941466 相对本地时区的日期和时间

SELECT datetime(1092941466, 'unixepoch', 'localtime');

下面是计算美国"独立宣言"签署以来的天数:

SELECT julianday('now') - julianday('1776-07-04');

blob.png

下面是计算从 2004 年某一特定时刻以来的秒数

blob.png


在 UTC 与本地时间值之间进行转换,当格式化日期时,使用 utc 或 localtime 修饰符,如下所示

SELECT time('12:00', 'localtime');

blob.png

blob.png



blob.png


SELECT strftime('%Y-%m-%d %H:%M:%S','now');     这里的小时显示有点疑问。

blob.png

我们可以使用localtime,转换为本地时间

select strftime('%Y.%m.%d %H:%M:%S','now','localtime');

blob.png


select datetime('now','localtime');

blob.png






常用函数

查看sqlite版本

SELECT sqlite_version() AS 'SQLite Version';

blob.png

其他的MIN,MAX,COUNT,AVG,SUM,ABS(绝对值),UPPER,LOWER,LENGTH(字符串长度)

用法都是MIN(被操作的列)

RANDOM(函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数)

select Random() from Table1





www.ayjs.net拥有,未经许可,不许转载,违者追究法律责任



推荐您阅读更多有关于“数据库sqlite3,”的文章

猜你喜欢

额 本文暂时没人评论 来添加一个吧

发表评论

必填

选填

选填

必填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

  查看权限

合肥科大智能常年招聘.NET,Java,Web前端,有想找想换工作的私聊我AY唯一QQ:875556003和AY交流

抖音号:wpfui,可以看到我的很多作品效果

AYUI8社区版Github地址:前往获取

作者:杨洋(AaronYang简称AY,安徽六安人)目前是个人,还没公司AY唯一QQ:875556003和AY交流

高中学历,2015年1月17日开始,兴趣学习研究WPF,目前工作繁忙,不接任何活

声明:AYUI7个人与商用免费,源码可购买。部分DEMO不免费.AY主要靠卖技术服务挣钱

不是从我处购买的ayui7源码,我不提供任何技术服务,如果你举报从哪里买的,我可以帮你转正为我的客户,并送demo

查看捐赠

AYUI7.X MVC教程 更新如下:

第一课 第二课 程序加密教程

vs2015 企业版密钥HM6NR-QXX7C-DFW2Y-8B82K-WTYJV

vs2017 企业版密钥NJVYC-BMHX2-G77MM-4XJMR-6Q8QF

标签列表