学习SQLite之路(四)

20160621 更新

参考: http://www.runoob.com/sqlite/sqlite-tutorial.html

1. SQLite   alter命令:不通过执行一个完整的转储和数据的重载来修改已有的表。

可以使用 ALTER TABLE 语句重命名表,使用 ALTER TABLE 语句还可以在已有的表中添加额外的列。

在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作(在其他数据库中可以改变表中列的数据类型,删除表中的列)

(1)基本语法:

用来重命名已有的表的 ALTER TABLE 的基本语法如下:

ALTER TABLE database_name.table_name RENAME TO new_table_name;

用来在已有的表中添加一个新的列的 ALTER TABLE 的基本语法如下:

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

(2)实例: alter_company.txt

alter table company rename to old_company; -- 重命名表

alter table old_company add column sex char(1);-- 增加新列,新添加的列是以 NULL 值来填充的

2、SQLite   Truncate Table:在 SQLite 中,并没有 TRUNCATE TABLE 命令。

但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据,但建议使用 DROP TABLE 命令删除整个表,然后再重新创建一遍。

(1):基本语法:

DELETE 命令的基本语法如下:

sqlite> DELETE FROM table_name;

DROP TABLE 的基本语法如下:

sqlite> DROP TABLE table_name;

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

(2)实例:

SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;

现在,COMPANY 表中的记录完全被删除,使用 SELECT 语句将没有任何输出。

3. SQLite 视图(view):是通过相关的名称存储在数据库中的一个 SQLite 语句。

视图(View)实际上是一个以预定义的 SQLite 查询形式存在的表的组合。

视图(View)可以包含一个表的所有行或从一个或多个表选定行。视图(View)可以从一个或多个表创建,这取决于要创建视图的 SQLite 查询。

(1)视图(View)是一种虚表,允许用户实现以下几点:

  • 用户或用户组查找结构数据的方式更自然或直观。
  • 限制数据访问,用户只能看到有限的数据,而不是完整的表。
  • 汇总各种表中的数据,用于生成报告。

SQLite 视图是只读的,因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器,当尝试 DELETE、INSERT 或 UPDATE 视图时触发,需要做的动作在触发器内容中定义。

(2)创建视图:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

(3)删除视图:

DROP VIEW view_name;

(4)实例: view.txt

create view company_view as select NAME, ID, SALARY from company where SALARY>2000;

删除:  drop  view  company_view;

4、SQLite 事务(Transaction):一个对数据库执行工作单元。(就是一堆操作的集合)

是以逻辑顺序完成的工作单位或序列,可以是由用户手动操作完成,也可以是由某种数据库程序自动完成。

您可以把许多的 SQLite 查询联合成一组,把所有这些放在一起作为事务的一部分进行执行。

(1)事务的属性:(ACID)

  • 原子性(Atomicity):确保工作单位内的所有操作都成功完成,否则,事务会在出现故障时终止,之前的操作也会回滚到以前的状态。
  • 一致性(Consistency):确保数据库在成功提交的事务上正确地改变状态。
  • 隔离性(Isolation):使事务操作相互独立和透明。
  • 持久性(Durability):确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

(2)事务控制(语法):

  • BEGIN TRANSACTION:开始事务处理。
  • COMMIT:保存更改,或者可以使用 END TRANSACTION 命令。
  • ROLLBACK:回滚所做的更改。

(3)begin命令: BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去,直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时,事务处理也会回滚。

以下是启动一个事务的简单语法:

BEGIN;
or 
BEGIN TRANSACTION;

commit命令:COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令。

COMMIT 命令把自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库。

基本语法:

COMMIT;
or
END TRANSACTION;

rollback命令:ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令。

ROLLBACK 命令只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务

 语法:   rollback;

(4)实例:commit.txt   这里提交了,已经做了修改

select * from company; begin; update company set SALARY = SALARY + 999;  -- 所有人工资加999 commit; select * from company;

(5)实例2: rollback.txt

 select * from company; begin; update company set SALARY = SALARY + 999; rollback;  -- 这里进行回滚,相当于没操作 select * from company;

5、SQLite 子查询:在另一个 SQLite 查询内嵌入在 WHERE 子句中的查询。

使用子查询返回的数据将被用在主查询中作为条件,以进一步限制要检索的数据。

子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用,可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。

(1)几个原则:

  • 子查询必须用括号括起来。
  • 子查询在 SELECT 子句中只能有一个列,除非在主查询中有多列,与子查询的所选列进行比较。
  • ORDER BY 不能用在子查询中,虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY,功能与 ORDER BY 相同。
  • 子查询返回多于一行,只能与多值运算符一起使用,如 IN 运算符。
  • BETWEEN 运算符不能与子查询一起使用,但是,BETWEEN 可在子查询内使用。

(2)子查询中的select子句: subquery_select.txt

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE]);

-- ID 大于4的人中找工资大于4000的

select * from (select * from company where ID > 4) where SALARY > 4000;

-- ID 在 工资大于4000的范围内

select * from company  where ID IN (select ID from company where SALARY > 4000);

(3)子查询中的insert子句:subquery_insert.txt

INSERT INTO table_name [ (column1 [, column2 ]) ]
           SELECT [ *|column1 [, column2 ]
           FROM table1 [, table2 ]
           [ WHERE VALUE OPERATOR ]

create table company_bak( -- 建一个和company一样的表

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR(50),

SALARY REAL );

-- 利用查询查来的ID,再往一张一样的表中插入数据

insert into company_bak select * from company where ID in(select ID from company);

select * from company_bak;

(4)子查询中的update子句:subquery_update.txt

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

-- 大于30岁的人涨1.5倍工资, update company set SALARY = 1.5*SALARY where AGE IN (select AGE from company where AGE > 30);

(5)子查询中的delete子句:subquery_delete.txt

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

-- 删除年龄大于30的客户记录 delete from company where ID IN (select ID from company where AGE > 30);

 (6)运行效果图: 省略

6、SQLite Autoincrement(自动递增): AUTOINCREMENT 是一个关键字,用于表中的字段值自动递增。

在创建表时在特定的列名称上使用 AUTOINCREMENT 关键字实现该字段值的自动增加。

关键字 AUTOINCREMENT 只能用于整型(INTEGER)字段。

(1)语法:

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

(2)实例: autoincrement.txt

create table autoincrement_test (

ID INTEGER PRIMARY KEY AUTOINCREMENT,  -ID 是自动递增的

NAME TEXT NOT NULL,

AGE INT NOT NULL );

insert into autoincrement_test(NAME, AGE) values('Paul', 3);   -- 插入几条数据

insert into autoincrement_test(NAME, AGE) values('Kobe', 24);

insert into autoincrement_test(NAME, AGE) values('KD', 35);

insert into autoincrement_test(NAME, AGE) values('MJ', 23);

select * from autoincrement_test;

7、SQLite  Explain(解释):在 SQLite 语句之前,可以使用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语,用于描述表的细节。

如果省略了 EXPLAIN 关键字或短语,任何的修改都会引起 SQLite 语句的查询行为,并返回有关 SQLite 语句如何操作的信息。

  • 来自 EXPLAIN 和 EXPLAIN QUERY PLAN 的输出只用于交互式分析和排除故障。
  • 输出格式的细节可能会随着 SQLite 版本的不同而有所变化。

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

(1)语法:

EXPLAIN语法:

EXPLAIN [SQLite Query]

EXPLAIN QUERY PLAN语法:

EXPLAIN  QUERY PLAN [SQLite Query]

(2)实例:explain.txt

explain select * from company where SALARY > 2000;

结果是一大堆什么鬼东西,我看不懂,有兴趣可以试试。

(3)说明:这个东西不太理解,先放一下,写完这些再说。

8、SQLite 时间日期:

(1)支持下面5个时间和日期函数:

       函数                                                             说明

1

date(timestring, modifier, modifier, ...)

以 YYYY-MM-DD 格式返回日期。

2

time(timestring, modifier, modifier, ...)

以 HH:MM:SS 格式返回时间。

3

datetime(timestring, modifier, modifier, ...)

以 YYYY-MM-DD HH:MM:SS 格式返回。

4

julianday(timestring, modifier, modifier, ...)

这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。

5

strftime(format, timestring, modifier, modifier, ...)

这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。

上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifier 修饰符。

strftime() 函数也可以把格式字符串 format 作为其第一个参数。

(2)时间字符串:可以用下面任何一种形式

可以使用 "T" 作为分隔日期和时间的文字字符。

             时间字符串                        实例

1

YYYY-MM-DD

2010-12-30

2

YYYY-MM-DD HH:MM

2010-12-30 12:10

3

YYYY-MM-DD HH:MM:SS.SSS

2010-12-30 12:10:04.100

4

MM-DD-YYYY HH:MM

30-12-2010 12:10

5

HH:MM

12:10

6

YYYY-MM-DDTHH:MM

2010-12-30 12:10

7

HH:MM:SS

12:10:01

8

YYYYMMDD HHMMSS

20101230 121001

9

now

2013-05-07

(3)修饰符(Modifier):时间字符串后边可跟着零个或多个的修饰符,这将改变有上述五个函数返回的日期和/或时间。

任何上述五大功能返回时间。修饰符应从左到右使用。

下面是可以在SQLite中使用的修饰符:

  • NNN days
  • NNN hours
  • NNN minutes
  • NNN.NNNN seconds
  • NNN months
  • NNN years
  • start of month
  • start of year
  • start of day
  • weekday N
  • unixepoch
  • localtime
  • utc

(4)格式化:SQLite 提供了非常方便的函数 strftime() 来格式化任何日期和时间。

可以使用以下的替换来格式化日期和时间:

替换       描述

%d

一月中的第几天,01-31

%f

带小数部分的秒,SS.SSS

%H

小时,00-23

%j

一年中的第几天,001-366

%J

儒略日数,DDDD.DDDD

%m

月,00-12

%M

分,00-59

%s

从 1970-01-01 算起的秒数

%S

秒,00-59

%w

一周中的第几天,0-6 (0 is Sunday)

%W

一年中的第几周,01-53

%Y

年,YYYY

%%

% symbol

(5)实例:date.txt

-- 计算当前日期

select date('now');

-- 计算当前月份的最后一天 -- now 后面接的是几个修饰符,

-- 'start of month'  当前月份开始

-- '+1 month', 当前月份加一个月

-- '-1 day'  减一天

select date('now','start of month','+1 month','-1 day');

-- 所以这个是上个月的第2天

select date('now','start of month','-1 month','+1 day');

-- 所以这个是今年的最后一天, 当前年加一年再减一天,就是今年的最后一天

select date('now','start of year','+1 year','-1 day');

-- 计算给定时间戳1992241466的日期和时间

select datetime('1992241466', 'unixepoch');

-- 计算新中国成立到现再的天数

select julianday('now') - julianday('1949-10-01');

-- 计算当年9月的第一个星期2的日期

select date('now', 'start of year','+8 months','weekday 2');

-- 在UTC与本地时间之间进行转化,当格式化日期时,用utc修饰符或localtime修饰符

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

select time('12:00', 'utc');

-- 计算当前时间

select datetime('now');

select datetime('now', '+8 hour');  -- 北京在东八区  这个才是北京时间

-- 当天的最后一秒

select datetime('now', 'start of day', '+1 day', '-1 seconds');

9、常用函数:

下面列出了一些有用的 SQLite 内置函数,且所有函数都是大小写不敏感,这意味着您可以使用这些函数的小写形式或大写形式或混合形式。

(1)count() : 计算表中的行数

(2)MAX() :  求某列的最大值

(3)MIN():求某列的最小值

(4)sqlite_version():返回SQLite的版本

(5)AVG():求某列的平均值

(6)SUM():求某列的和

(7)RNDOM():产生随机数

(8)ABS():求绝对值

(9)upper():字符串转成大写

(10)lower():字符串转成小写

(11)length():求字符串的长度

(12)实例:fun.txt   结果不显示了

select count(*) from company;

select MAX(SALARY), NAME from company;

select MIN(SALARY), NAME from company;

select MIN(SALARY), NAME from company;

select SUM(SALARY) from company;

select random() as Random;

select ABS(5), ABS(-5), ABS(NULL), ABS('aa');

select upper(NAME) as Big_Name from company;

select lower(NAME) as Small_Name from company;

select length(NAME) as Name_len , name from company;

select sqlite_version() as SQL_VER;

10、之前所有的例子  全部放在这里:

https://github.com/xcywt/MySQLite

这样大家可以看到吗,Github用的不是很熟,能看到的话给个回复吧,好人一生平安

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

SQL Server 深入解析索引存储(下)

概述 非聚集索引与聚集索引具有相同的 B 树结构,它们之间的显著差别在于以下两点: 基础表的数据行不按非聚集键的顺序排序和存储。 非聚集索引的叶层是由索引...

2817
来自专栏漆洪凯的专栏

MySQL 数据库设计总结

MySQL支持很多种不同的数据类型,并且选择正确的数据类型对于获得高性能至关重要。本文将对MySQL 数据库设计总结,希望与大家共同探讨。

25.7K12
来自专栏Kevin-ZhangCG

数据库索引

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库...

1090
来自专栏恰童鞋骚年

《MSSQL2008技术内幕:T-SQL语言基础》读书笔记(下)

  所谓透视(Pivoting)就是把数据从行的状态旋转为列的状态的处理。其处理步骤为:

1332
来自专栏乐沙弥的世界

SQL基础--> 序列(SEQUENCE)、同义词(SYNONYM)

--=============================================

842
来自专栏张善友的专栏

Entity Framework Core 实现MySQL 的TimeStamp/RowVersion 并发控制

将通用的序列号生成器库 从SQL Server迁移到Mysql 遇到的一个问题,就是TimeStamp/RowVersion并发控制类型在非Microsoft ...

3368
来自专栏Java3y

Oracle总结【SQL细节、多表查询、分组查询、分页】

前言 在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了…印象中就只有基本的SQL语句和相关一些概念…...

38710
来自专栏java小白

explain 分析sql语句字段的解释

3575
来自专栏Kevin-ZhangCG

SQL优化总结之二

1852
来自专栏JAVA同学会

mysql如何执行关联查询与优化

在数据库中执行查询(select)在我们工作中是非常常见的,工作中离不开CRUD,在执行查询(select)时,多表关联也非常常见,我们用的也比较多,那么my...

1433

扫码关注云+社区

领取腾讯云代金券