// //

一、简介：

    SQLite是目前最流行的开源嵌入式数据库，和很多其他嵌入式存储引擎相比(NoSQL)，如BerkeleyDB、MemBASE等，SQLite可以很好的支持关系型数据库所具备的一些基本特征，如标准SQL语法、事务、数据表和索引等。事实上，尽管SQLite拥有诸多关系型数据库的基本特征，然而由于应用场景的不同，它们之间并没有更多的可比性。下面我们将列举一下SQLite的主要特征：
    1). 管理简单，甚至可以认为无需管理。
    2). 操作方便，SQLite生成的数据库文件可以在各个平台无缝移植。
    3). 可以非常方便的以多种形式嵌入到其他应用程序中，如静态库、动态库等。
    4). 易于维护。
    综上所述，SQLite的主要优势在于灵巧、快速和可靠性高。SQLite的设计者们为了达到这一目标，在功能上作出了很多关键性的取舍，与此同时，也失去了一些对RDBMS关键性功能的支持，如高并发、细粒度访问控制(如行级锁)、丰富的内置函数、存储过程和复杂的SQL语句等。正是因为这些功能的牺牲才换来了简单，而简单又换来了高效性和高可靠性。
    
二、SQLite的主要优点：

    1. 一致性的文件格式：
    在SQLite的官方文档中是这样解释的，我们不要将SQLite与Oracle或PostgreSQL去比较，而是应该将它看做fopen和fwrite。与我们自定义格式的数据文件相比，SQLite不仅提供了很好的移植性，如大端小端、32/64位等平台相关问题，而且还提供了数据访问的高效性，如基于某些信息建立索引，从而提高访问或排序该类数据的性能，SQLite提供的事务功能，也是在操作普通文件时无法有效保证的。
    
    2. 在嵌入式或移动设备上的应用：
    由于SQLite在运行时占用的资源较少，而且无需任何管理开销，因此对于PDA、智能手机等移动设备来说，SQLite的优势毋庸置疑。
    
    3. 内部数据库：
    在有些应用场景中，我们需要为插入到数据库服务器中的数据进行数据过滤或数据清理，以保证最终插入到数据库服务器中的数据有效性。有的时候，数据是否有效，不能通过单一一条记录来进行判断，而是需要和之前一小段时间的历史数据进行特殊的计算，再通过计算的结果判断当前的数据是否合法。在这种应用中，我们可以用SQLite缓冲这部分历史数据。还有一种简单的场景也适用于SQLite，即统计数据的预计算。比如我们正在运行数据实时采集的服务程序，我们可能需要将每10秒的数据汇总后，形成每小时的统计数据，该统计数据可以极大的减少用户查询时的数据量，从而大幅提高前端程序的查询效率。在这种应用中，我们可以将1小时内的采集数据均缓存在SQLite中，在达到整点时，计算缓存数据后清空该数据。
    
    4. 数据分析：
    可以充分利用SQLite提供SQL特征，完成简单的数据统计分析的功能。这一点是CSV文件无法比拟的。
    
    5. 产品Demo和测试：
    在需要给客户进行Demo时，可以使用SQLite作为我们的后台数据库，和其他关系型数据库相比，使用SQLite减少了大量的系统部署时间。对于产品的功能性测试而言，SQLite也可以起到相同的作用。
       
三、和RDBMS相比SQLite的一些劣势：

    1. C/S应用：
    如果你有多个客户端需要同时访问数据库中的数据，特别是他们之间的数据操作是需要通过网络传输来完成的。在这种情况下，不应该选择SQLite。由于SQLite的数据管理机制更多的依赖于OS的文件系统，因此在这种操作下其效率较低。
    
    2. 数据量较大：
    受限于操作系统的文件系统，在处理大数据量时，其效率较低。对于超大数据量的存储，甚至不能提供支持。
    
    3. 高并发：
    由于SQLite仅仅提供了粒度很粗的数据锁，如读写锁，因此在每次加锁操作中都会有大量的数据被锁住，即使仅有极小部分的数据会被访问。换句话说，我们可以认为SQLite只是提供了表级锁，没有提供行级锁。在这种同步机制下，并发性能很难高效。
   
四、个性化特征：

    1. 零配置：
    SQLite本身并不需要任何初始化配置文件，也没有安装和卸载的过程。当然也不存在服务器实例的启动和停止。在使用的过程中，也无需创建用户和划分权限。在系统出现灾难时，如电源问题、主机问题等，对于SQLite而言，不需要做任何操作。
    
    2. 没有独立的服务器：
    和其他关系型数据库不同的是，SQLite没有单独的服务器进程，以供客户端程序访问并提供相关的服务。SQLite作为一种嵌入式数据库，其运行环境与主程序位于同一进程空间，因此它们之间的通信完全是进程内通信，而相比于进程间通信，其效率更高。然而需要特别指出的是，该种结构在实际运行时确实存在保护性较差的问题，比如此时，应用程序出现问题导致进程崩溃，由于SQLite与其所依赖的进程位于同一进程空间，那么此时SQLite也将随之退出。但是对于独立的服务器进程，则不会有此问题，它们将在密闭性更好的环境下完成它们的工作。
    
    3. 单一磁盘文件：
    SQLite的数据库被存放在文件系统的单一磁盘文件内，只要有权限便可随意访问和拷贝，这样带来的主要好处是便于携带和共享。其他的数据库引擎，基本都会将数据库存放在一个磁盘目录下，然后由该目录下的一组文件构成该数据库的数据文件。尽管我们可以直接访问这些文件，但是我们的程序却无法操作它们，只有数据库实例进程才可以做到。这样的好处是带来了更高的安全性和更好的性能，但是也付出了安装和维护复杂的代价。
    
    4. 平台无关性：
    这一点在前面已经解释过了。和SQLite相比，很多数据库引擎在备份数据时不能通过该方式直接备份，只能通过数据库系统提供的各种dump和restore工具，将数据库中的数据先导出到本地文件中，之后在load到目标数据库中。这种方式存在显而易见的效率问题，首先需要导出到另外一个文件，如果数据量较大，导出的过程将会比较耗时。然而这只是该操作的一小部分，因为数据导入往往需要更多的时间。数据在导入时需要很多的验证过程，在存储时，也并非简简单单的顺序存储，而是需要按照一定的数据结构、算法和策略存放在不同的文件位置。因此和直接拷贝数据库文件相比，其性能是非常拙劣的。
    
    5. 弱类型：
    和大多数支持静态类型的数据库不同的是，SQLite中的数据类型被视为数值的一个属性。因此对于一个数据表列而言，即便在声明该表时给出了该列的类型，我们在插入数据时仍然可以插入任意类型，比如Integer的列被存入字符串'hello'。针对该特征唯一的例外是整型的主键列，对于此种情况，我们只能在该列中存储整型数据。
    
    6. SQL语句编译成虚拟机代码：
    很多数据库产品会将SQL语句解析成复杂的，

STR()
        把数值型数据转换为字符型数据。
STR (<float_expression>[，length[， <decimal>]])
        length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。
        当length 或者decimal 为负值时，返回NULL;
        当length 小于小数点左边（包括符号位）的位数时，返回length 个*;
        先服从length ，再取decimal ;
        当返回的字符串位数小于length ，左边补足空格。
一、去空格函数
1、LTRIM() 把字符串头部的空格去掉。

2、RTRIM() 把字符串尾部的空格去掉。

二、取子串函数
1、left()
LEFT (<character_expression>， <integer_expression>)
返回character_expression 左起 integer_expression 个字符。

2、RIGHT()
RIGHT (<character_expression>， <integer_expression>)
返回character_expression 右起 integer_expression 个字符。

3、SUBSTRING()
SUBSTRING (<expression>， <starting_ position>， length)
返回从字符串左边第starting_ position 个字符起length个字符的部分。

三、字符串比较函数
1、CHARINDEX()
        返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<’substring_expression’>， <expression>)
        其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。
        此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
        返回字符串中某个指定的子串出现的开始位置。
        PATINDEX (<’%substring _expression%’>， <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。
        与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。

四、字符串操作函数
1、QUOTENAME()
        返回被特定字符括起来的字符串。
QUOTENAME (<’character_expression’>[， quote_ character]) 其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。
2、REPLICATE()
        返回一个重复character_expression 指定次数的字符串。
REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值，则返回NULL 。

3、REVERSE()
        将指定的字符串的字符排列顺序颠倒。
REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。

4、REPLACE()
        返回被替换了指定子串的字符串。
REPLACE (<string_expression1>， <string_expression2>， <string_expression3>) 用string_expression3 替换在string_expression1 中的子串string_expression2。

4、SPACE()
返回一个有指定长度的空白字符串。
SPACE (<integer_expression>) 如果integer_expression 值为负值，则返回NULL 。

5、STUFF()
        用另一子串替换字符串指定位置、长度的子串。
STUFF (<character_expression1>， <start_ position>， <length>，<character_expression2>)
        如果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则返回NULL 值。
        如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。

五、数据类型转换函数
1、CAST()
CAST (<expression> AS <data_ type>[ length ])

2、CONVERT()
CONVERT (<data_ type>[ length ]， <expression> [， style])

        1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。
        2）length用于指定数据的长度，缺省值为30。
        3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
        4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。
        5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。
        6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
        7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。
        8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。
        9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。
六、日期函数
1、day(date_expression) 
        返回date_expression中的日期值

2、month(date_expression)
        返回date_expression中的月份值

3、year(date_expression)
        返回date_expression中的年份值

4、DATEADD()
DATEADD (<datepart>， <number>， <date>)
        返回指定日期date 加上指定的额外日期间隔number 产生的新日期。
5、DATEDIFF()
DATEDIFF (<datepart>， <date1>， <date2>)
        返回两个指定日期在datepart

回滚事务对临时表有效

SET NOCOUNT ON;

DECLARE @TestTable TABLE (
  RowID   INT IDENTITY PRIMARY KEY CLUSTERED,
  Name     VARCHAR(9) NOT NULL UNIQUE,
  Age     TINYINT NULL,
  rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid()));

begin tran

INSERT INTO @TestTable (Name, Age) VALUES ('Roy', 25),('中国风', 21);
rollback tran

SELECT 'Insert rows' as Action, * FROM @TestTable;
/*
Action	RowID	Name	Age	rowguid
Insert rows	1	Roy	25	84C4F6CC-3AB3-4D7D-8779-0DCF3414AB61
Insert rows	2	中国风	21	1DB02A77-F20E-4326-A4E4-023CE9F5DEA7
*/
go
if OBJECT_ID('Tempdb..#TestTable') is not null
	drop table #TestTable
CREATE TABLE #TestTable (
  RowID   INT IDENTITY PRIMARY KEY CLUSTERED,
  Name     VARCHAR(9) NOT NULL UNIQUE,
  Age     TINYINT NULL,
  rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid()));
 
 begin tran
 
INSERT INTO #TestTable (Name,Age) VALUES ('Roy', 25),('中国风', 21);
rollback tran
SELECT 'Insert rows' as Action,* FROM #TestTable
/*
无记录
*/