内连接(INNER JOIN)是最常见的连接类型,是默认的连接类型。INNER 关键字是可选的。
语法:
SELECT … FROM table1 [INNER] JOIN table2 ON conditional_expression …;
为了避免冗余,并保持较短的措辞,可以使用USING表达式声明内连接(INNER JOIN)条件。这个表达式指定一个或多个列的列表:
SELECT … FROM table1 JOIN table2 USING ( column1 ,… ) …;
自然连接(NATURAL JOIN)类似于JOIN…USING,只是它会自动测试存在两个表中的每一列的值之间相等值:
SELECT … FROM table1 NATURAL JOIN table2…;
如下展示示例:
外连接OUTER JOIN
外连接(OUTER JOIN)是内连接(INNER JOIN)的扩展。虽然 SQL标准定义了三种类型的外连接:LEFT、RIGHT、FULL,但SQLite只支持左外连接(LEFT OUTER JOIN)。外连接(OUTER JOIN)声明条件的方法与内连接(INNER JOIN)是相同的,使用 ON、USING或NATURAL关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成,外连接(OUTER JOIN)将从一个或两个表中任何未连接的行合并进来,外连接的列使用NULL值,将它们附加到结果表中。
左外连接(LEFT OUTER JOIN)的语法:
SELECT … FROM table1 LEFT OUTER JOIN table2 ON conditional_expression …;
为了避免冗余,并保持较短的措辞,可以使用USING表达式声明外连接(OUTER JOIN)条件。这个表达式指定一个或多个列的列表:
SELECT … FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,… ) …;
如下展示示例:
SQLite Unions子句
SQLite的UNION子句/运算符用于合并两个或多个SELECT语句的结果,不返回任何重复的行。为了使用UNION,每个SELECT被选择的列数必须是相同的,相同数目的列表达式,相同的数据类型,并确保它们有相同的顺序,但它们不必具有相同的长度。
语法:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
如下展示示例:
UNION ALL子句
UNION ALL运算符用于结合两个SELECT语句的结果,包括重复行。适用于UNION的规则同样适用于UNION ALL运算符。
语法:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition];
这里给定的条件根据需要可以是任何表达式。
如下展示示例:
SQLite NULL值
SQLite的NULL是用来表示一个缺失值的项。表中的一个NULL值是在字段中显示为空白的一个值。带有NULL值的字段是一个不带有值的字段。NULL值与零值或包含空格的字段是不同的,理解这点是非常重要的。
语法示例:
CREATE TABLE AndroidTeam (
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address CHAR(50),
money REAL
);
这里NOT NULL表示列总是接受给定数据类型的显式值。这里有两个列我们没有使用NOT NULL,这意味着这两个列可能为NULL。带有NULL值的字段在记录创建的时候可以保留为空。NULL值在选择数据时会引起问题,因为当把一个未知的值与另一个值进行比较时,结果总是未知的,且不会包含在最后的结果中。
如下展示示例:
SQLite as别名
您可以暂时把表或列重命名为另一个名字,这被称为别名。使用表别名是指在一个特定的SQLite语句中重命名表。重命名是临时的改变,在数据库中实际的表的名称不会改变。列别名用来为某个特定的SQLite语句重命名表中的列。
表别名语法:
SELECT column1, column2…
FROM table_name AS alias_name
WHERE [condition];
列别名语法:
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];
如下实例语句:
SELECT a.id, a.name, a.age, b.position FROM teamTable AS a, monkeyTable AS b WHERE a.id = b.tid;
SELECT a.id AS aid, a.name AS aname, a.age, b.position FROM teamTable AS a, monkeyTable AS b WHERE a.id = b.tid;
SQLite触发器(Trigger)
SQLite的触发器是数据库的回调函数,它在自动执行/指定的数据库事件发生时调用。以下是关于SQLite的触发器的要点:
-
SQLite的触发器(Trigger)可以指定在特定的数据库表发生DELETE、INSERT或UPDATE时触发,或在一个或多个指定表的列发生更新时触发。
-
SQLite只支持FOR EACH ROW触发器(Trigger),没有FOR EACH STATEMENT触发器(Trigger)。因此,明确指定FOR EACH ROW是可选的。
-
WHEN子句和触发器(Trigger)动作可能访问使用表单NEW.column-name和OLD.column-name的引用插入、删除或更新的行元素,其中column-name是从与触发器关联的表的列的名称。
-
如果提供WHEN子句,则只针对WHEN子句为真的指定行执行SQL语句。如果没有提供WHEN子句,则针对所有行执行SQL语句。
-
BEFORE或AFTER关键字决定何时执行触发器动作,决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
-
当触发器相关联的表删除时,自动删除触发器(Trigger)。
-
要修改的表必须存在于同一数据库中,作为触发器被附加的表或视图,且必须只使用tablename,而不是database.tablename。
-
一个特殊的SQL函数RAISE()可用于触发器程序内抛出异常。
创建触发器(Trigger)语法:
CREATE
TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
– Trigger logic goes here…
END;
在这里,event_name 可以是在所提到的表 table_name上的INSERT、DELETE和UPDATE数据库操作。您可以在表名后选择指定FOR EACH ROW。
以下是在UPDATE操作上在表的一个或多个指定列上创建触发器(Trigger)的语法:
CREATE TRIGGER trigger_name [BEFORE|AFTER]
UPDATE OF column_name
ON table_name
BEGIN
– Trigger logic goes here…
END;
如下实例语句:
列出触发器(TRIGGERS)
您可以从sqlite_master表中列出所有触发器,如下所示:
SELECT name FROM sqlite_master WHERE type = ‘trigger’;
上面的 SQLite 语句只会列出一个条目,如下:
name
audit_log
如果您想要列出特定表上的触发器,则使用AND子句连接表名,如下所示:
SELECT name FROM sqlite_master WHERE type = ‘trigger’ AND tbl_name = ‘teamTable’;
上面的 SQLite 语句只会列出一个条目,如下:
name
audit_log
删除触发器(TRIGGERS)
下面是DROP命令,可用于删除已有的触发器:
DROP TRIGGER trigger_name;
SQLite索引(Index)
索引(Index)是一种特殊的查找表,数据库搜索引擎用来加快数据检索。简单地说,索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。例如,如果您想在一本讨论某个话题的书中引用所有页面,您首先需要指向索引,索引按字母顺序列出了所有主题,然后指向一个或多个特定的页码。
索引有助于加快SELECT查询和WHERE子句,但它会减慢使用UPDATE和INSERT语句时的数据输入。索引可以创建或删除,但不会影响数据。使用CREATE INDEX语句创建索引,它允许命名索引,指定表及要索引的一列或多列,并指示索引是升序排列还是降序排列。索引也可以是唯一的,与UNIQUE约束类似,在列上或列组合上防止重复条目。
CREATE INDEX命令:
CREATE INDEX基本语法:
CREATE INDEX index_name ON table_name;
单列索引
单列索引是一个只基于表的一个列上创建的索引。基本语法:
CREATE INDEX index_name ON table_name (column_name);
唯一索引
使用唯一索引不仅是为了性能,同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法:
CREATE UNIQUE INDEX index_name on table_name (column_name);
组合索引
组合索引是基于一个表的两个或多个列上创建的索引。基本语法:
CREATE INDEX index_name on table_name (column1, column2);
是否要创建一个单列索引还是组合索引,要考虑到您在作为查询过滤条件的WHERE子句中使用频繁的列。如果值使用到一个列,则选择使用单列索引。如果在作为过滤的WHERE子句中有两个或多个列经常使用,则选择使用组合索引。
隐式索引
隐式索引是在创建对象时,由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。
如下实例演示:
CREATE INDEX money_index ON teamTable (money);
查看索引:
.indices teamTable
结果:
salary_index
sqlite_autoindex_COMPANY_1 //创建表时创建的隐式索引
列出数据库范围的所有索引:
SELECT * FROM sqlite_master WHERE type = ‘index’;
DROP INDEX命令:
一个索引可以使用SQLite的DROP命令删除。当删除索引时应特别注意,因为性能可能会下降或提高。
基本语法:
DROP INDEX index_name;
什么情况下避免使用索引
索引的目的在于提高数据库的性能,但这里有几个情况需要避免使用索引。使用索引时,应重新考虑下列准则:
-
索引不应该使用在较小的表上。
-
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
-
索引不应该使用在含有大量的NULL值的列上。
-
索引不应该使用在频繁操作的列上。
SQLite Indexed By关键字
“INDEXED BY index-name”子句规定必须需要命名的索引来查找前面表中值。如果索引名index-name不存在或不能用于查询,然后SQLite语句的准备失败。”NOT INDEXED” 子句规定当访问前面的表(包括由UNIQUE和PRIMARY KEY约束创建的隐式索引)时,没有使用索引。然而,即使指定了 “NOT INDEXED”,INTEGER PRIMARY KEY 仍然可以被用于查找条目。
下面是INDEXED BY子句的语法,它可以与DELETE、UPDATE 或 SELECT 语句一起使用:
SELECT|DELETE|UPDATE column1, column2…
INDEXED BY (index_name)
table_name
WHERE (CONDITION);
如下实例:
假设有表teamTable表,我们将创建一个索引,并用它进行INDEXED BY操作:
CREATE INDEX money_index ON teamTable(money);
现在使用INDEXED BY子句从表teamTable中选择数据:
SELECT * FROM teamTable INDEXED BY money_index WHERE money > 5000;
SQLite Alter命令
SQLite的ALTER TABLE命令不通过执行一个完整的转储和数据的重载来修改已有的表。您可以使用ALTER TABLE语句重命名表,使用ALTER TABLE语句还可以在已有的表中添加额外的列。在SQLite中,除了重命名表和在已有的表中添加列,ALTER TABLE命令不支持其他操作。
用来重命名已有的表的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…;
如下展示示例:
ALTER TABLE teamTable RENAME TO androidTeamTable;
ALTER TABLE androidTeamTable ADD COLUMN sex char(1);
新添加的列是以NULL值来填充的
SQLite Truncate Table关键字
在SQLite中,并没有TRUNCATE TABLE命令,但可以使用SQLite的DELETE命令从已有的表中删除全部的数据,但建议使用DROP TABLE命令删除整个表,然后再重新创建一遍。
DELETE命令的基本语法:
DELETE FROM table_name;
DROP TABLE的基本语法:
DROP TABLE table_name;
如果您使用DELETE TABLE命令删除所有记录,建议使用VACUUM命令清除未使用的空间。
如下实例:
DELETE FROM androidTeamTable;
VACUUM;
SQLite视图(View)
视图(View)只不过是通过相关的名称存储在数据库中的一个SQLite语句。视图(View)实际上是一个以预定义的SQLite查询形式存在的表的组合。视图(View)可以包含一个表的所有行或从一个或多个表选定行。视图(View)可以从一个或多个表创建,这取决于要创建视图的SQLite查询。视图(View)是一种虚表,允许用户实现以下几点:
-
用户或用户组查找结构数据的方式更自然或直观。
-
限制数据访问,用户只能看到有限的数据,而不是完整的表。
-
汇总各种表中的数据,用于生成报告。
SQLite视图是只读的,因此可能无法在视图上执行DELETE、INSERT或UPDATE语句。但是可以在视图上创建一个触发器,当尝试DELETE、INSERT或UPDATE视图时触发,需要做的动作在触发器内容中定义。
创建视图
SQLite的视图是使用CREATE VIEW语句创建的。SQLite视图可以从一个单一的表、多个表或其他视图创建。
CREATE VIEW基本语法:
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2…
FROM table_name
WHERE [condition];
您可以在SELECT语句中包含多个表,这与在正常的SQL SELECT查询中的方式非常相似。如果使用了可选的TEMP或TEMPORARY关键字,则将在临时数据库中创建视图。
如下实例展示:
CREATE VIEW test_view AS SELECT id, name, age FROM androidTeamTable;
SELECT * FROM test_view;
删除视图
要删除视图,只需使用带有view_name的DROP VIEW语句。DROP VIEW的基本语法如下:
DROP VIEW view_name;
SQLite事务(Transaction)
事务(Transaction)是一个对数据库执行工作单元。事务(Transaction)是以逻辑顺序完成的工作单位或序列,可以是由用户手动操作完成,也可以是由某种数据库程序自动完成。事务(Transaction)是指一个或多个更改数据库的扩展。例如,如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录,那么您正在该表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误。实际上,您可以把许多的SQLite查询联合成一组,把所有这些放在一起作为事务的一部分进行执行。
事务的属性
事务(Transaction)具有以下四个标准属性,通常根据首字母缩写为ACID:
-
原子性(Atomicity):确保工作单位内的所有操作都成功完成,否则,事务会在出现故障时终止,之前的操作也会回滚到以前的状态。
-
一致性(Consistency):确保数据库在成功提交的事务上正确地改变状态。
-
隔离性(Isolation):使事务操作相互独立和透明。
-
持久性(Durability):确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。
事务控制
使用下面的命令来控制事务:
BEGIN TRANSACTION;
开始事务处理。
COMMIT:保存更改,或者可以使用END TRANSACTION命令。
ROLLBACK:回滚所做的更改。
事务控制命令只与DML命令 INSERT、UPDATE和DELETE一起使用。他们不能在创建表或删除表时使用,因为这些操作在数据库中是自动提交的。
BEGIN TRANSACTION命令
事务(Transaction)可以使用BEGIN TRANSACTION命令或简单的BEGIN命令来启动。此类事务通常会持续执行下去,直到遇到下一个COMMIT或ROLLBACK命令。不过在数据库关闭或发生错误时,事务处理也会回滚。以下是启动一个事务的简单语法:
BEGIN; 或者 BEGIN TRANSACTION;
COMMIT命令
COMMIT命令是用于把事务调用的更改保存到数据库中的事务命令。COMMIT命令把自上次COMMIT或ROLLBACK命令以来的所有事务保存到数据库。
语法:
COMMIT; 或者 END TRANSACTION;
ROLLBACK命令
ROLLBACK命令是用于撤消尚未保存到数据库的事务的事务命令。ROLLBACK命令只能用于撤销自上次发出COMMIT或ROLLBACK命令以来的事务。
语法:
ROLLBACK;
示例就不演示了,比较easy!!
SQLite子查询
子查询或内部查询或嵌套查询是在另一个SQLite查询内嵌入在WHERE子句中的查询。使用子查询返回的数据将被用在主查询中作为条件,以进一步限制要检索的数据。子查询可以与SELECT、INSERT、UPDATE和DELETE语句一起使用,可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN等。
以下是子查询必须遵循的几个规则:
-
子查询必须用括号括起来。
-
子查询在SELECT子句中只能有一个列,除非在主查询中有多列与子查询的所选列进行比较。
-
ORDER BY不能用在子查询中,虽然主查询可以使用ORDER BY。
-
子查询返回多于一行,只能与多值运算符一起使用,如IN运算符。
-
BETWEEN运算符不能与子查询一起使用,但是BETWEEN可在子查询内使用。
* SELECT语句中的子查询使用*
子查询通常与SELECT语句一起使用。基本语法如下:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE]);
如下示例:
SELECT * FROM teamTable WHERE id IN (SELECT id FROM teamTable WHERE money > 45000);
INSERT语句中的子查询使用
子查询也可以与INSERT语句一起使用。INSERT语句使用子查询返回的数据插入到另一个表中。在子查询中所选择的数据可以用任何字符、日期或数字函数修改。
基本语法如下:
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ];
如下示例:
把一个表的数据全部插入到另一个bck表中
INSERT INTO teamTable_bak SELECT * FROM teamTable WHERE id IN (SELECT id FROM teamTable);
UPDATE语句中的子查询使用
子查询可以与UPDATE语句结合使用。当通过UPDATE语句使用子查询时,表中单个或多个列被更新。
基本语法如下:
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ];
如下示例:
UPDATE teamTable SET money = money * 0.50 WHERE age IN (SELECT age FROM teamTable_bck WHERE age >= 24);
DELETE语句中的子查询使用
子查询可以与DELETE语句结合使用,就像上面提到的其他语句一样。
基本语法如下:
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ];
如下示例:
DELETE FROM teamTable WHERE age IN (SELECT age FROM teamTable_bck WHERE age > 24);
SQLite Autoincrement(自动递增)
SQLite的AUTOINCREMENT是一个关键字,用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用AUTOINCREMENT关键字实现该字段值的自动增加。关键字AUTOINCREMENT只能用于整型(INTEGER)字段。
设置了AUTOINCREMENT后insert或者update时不需要指定值,自动的,而且是从1开始的。
语法:
CREATE TABLE table_name(
column1 INTEGER AUTOINCREMENT,
column2 datatype,
column3 datatype,
…
columnN datatype,
);
示例就算了,太easy!!
SQLite注入
如果您的站点允许用户通过网页输入,并将输入内容插入到SQLite数据库中,这个时候您就面临着一个被称为SQL注入的安全问题。
注入通常在请求用户输入时发生,比如需要用户输入姓名,但用户却输入了一个SQLite语句,而这语句就会在不知不觉中在数据库上运行。
永远不要相信用户提供的数据,所以只处理通过验证的数据,这项规则是通过模式匹配来完成的。
SQLite日期 & 时间
SQLite支持以下五个日期和时间函数:
| 函数 | 实例 |
| :-- | :-- |
| date(timestring, modifiers…) | 以YYYY-MM-DD格式返回日期。 |
| time(timestring, modifiers…) | 以 HH:MM:SS 格式返回时间。 |
| datetime(timestring, modifiers…) | 以 YYYY-MM-DD HH:MM:SS 格式返回。 |
| julianday(timestring, modifiers…) | 这将返回从格林尼治时间的公元前4714年11月24日正午算起的天数。 |
| strftime(timestring, modifiers…) | 这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。 |
上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个modifiers修饰符。strftime() 函数也可以把格式字符串作为其第一个参数。下面将为您详细讲解不同类型的时间字符串和修饰符。
时间字符串
一个时间字符串可以采用下面任何一种格式:
| 时间字符串 | 实例 |
| :-- | :-- |
| YYYY-MM-DD | 2010-12-30 |
| YYYY-MM-DD HH:MM | 2010-12-30 12:10 |
| YYYY-MM-DD HH:MM:SS.SSS | 2010-12-30 12:10:04.100 |
| MM-DD-YYYY HH:MM | 30-12-2010 12:10 |
| HH:MM | 12:10 |
| YYYY-MM-DDTHH:MM | 2010-12-30 12:10 |
| HH:MM:SS | 12:10:01 |
| YYYYMMDD HHMMSS | 20101230 121001 |
尾声
评论里面有些同学有疑问关于如何学习material design控件,我的建议是去GitHub搜,有很多同行给的例子,这些栗子足够入门。
有朋友说要是动真格的话,需要NDK以及JVM等的知识,首现**NDK并不是神秘的东西,**你跟着官方的步骤走一遍就知道什么回事了,无非就是一些代码格式以及原生/JAVA内存交互,进阶一点的有原生/JAVA线程交互,线程交互确实有点蛋疼,但平常避免用就好了,再说对于初学者来说关心NDK干嘛,据鄙人以前的经历,只在音视频通信和一个嵌入式信号处理(离线)的两个项目中用过,嵌入式信号处理是JAVA->NDK->.SO->MATLAB这样调用的我原来MATLAB的代码,其他的大多就用在游戏上了吧,一般的互联网公司会有人给你公司的SO包的。
至于JVM,该掌握的那部分,相信我,你会掌握的,不该你掌握的,有那些专门研究JVM的人来做,不如省省心有空看看计算机系统,编译原理。
一句话,平常多写多练,这是最基本的程序员的素质,尽量挤时间,读理论基础书籍,JVM不是未来30年唯一的虚拟机,JAVA也不一定再风靡未来30年工业界,其他的系统和语言也会雨后春笋冒出来,但你理论扎实会让你很快理解学会一个语言或者框架,你平常写的多会让你很快熟练的将新学的东西应用到实际中。
初学者,一句话,多练。
《Android学习笔记总结+移动架构视频+大厂面试真题+项目实战源码》,点击传送门,即可获取!