SQLite3学习笔记(2)
SQLite 创建表
SQLite 的CREATE TABLE 语句用于在任何指定的数据库创建一个新表。 创建新表，涉及到命名表、定义列及每一行的数据类型。
CREATE TABLE 的基本语法如下：
CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype, );
CREATE TABLE 是告诉数据库系统创建一个新表的关键字。CREATE TABLE 语句后跟着表的唯一的名称或标识。您也可以选择指定带有 table_name 的 database_name。
下面是一个实例，它创建了一个COMPANY表，ID作为主键，NOT NULL 的约束表示在表中创建记录时这些字段不能为NULL：
sqlite> CREATE TABLE COMPANY( ...> ID INT PRIMARY KEY NOT NULL, ...> NAME TEXT NOT NULL, ...> ADDRESS CHAR(50), ...> SALARY REAL ...> );
再创建一个表：
sqlite> CREATE TABLE DEPARTMENT( ...> ID INT PRIMARY KEY NOT NULL, ...> DEPT CHAR(50) NOT NULL, ...> EMP_ID INT NOT NULL ...> );
可以使用SQLite 的.table 命令来验证表是否已经成功创建，该命令用于列出附加数据库中的所有表。
sqlite> .table COMPANY DEPARTMENT
可以使用SQLite.schema 命令得到表的完整信息：
sqlite> .schema COMPANY CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
SQLite 删除表
SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
使用此命令时要特别注意，一旦一个表被删除，表中所有信息也将被永久丢失。
DROP TABLE 的用法如下。您可以选择带有指定带有表名的数据库名称：
DROP TABLE database_name.table_name;
实例：
sqlite> .table TEST XING sqlite> DROP TABLE XING; sqlite> .table TEST sqlite>
SQLite Insert 语句
SQLite INSERT INTO语句用于向数据库的某个表中添加新的数据行。
Insert 有两种基本语法，如下：
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
其中 column1、column2.....columnN 是要插入数据的表中的列的名称.
如果要为表中的所有列添加值，也可以不需要正在SQLite查询中指定列名称。但要确保值的顺序与列在表中的顺序一致：
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
实例：
sqlite> CREATE TABLE COM( ...> ID INT PRIMARY KEY NOT NULL, ...> NAME TEXT NOT NULL, ...> AGE INT NOT NULL); sqlite> .table COM TEST sqlite> .schema COM CREATE TABLE COM( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL); sqlite> INSERT INTO COM (ID,NAME,AGE) ...> VALUES(1,'JXX',18); sqlite> INSERT INTO COM VALUES(2,'CLJ',19); sqlite>
先创建一个表，再使用两种语法向表中插入数据。
SQLite Select 语句
SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果集也被称为结果集。
SQLite 的SELECT 语句的基本语法如下：
SELECT column1, column2, columnN FROM table_name;
column1, column2...是表的字段，他们的值即是您要获取的。如果您想获取所有可用的字段，那么可以使用下面的语法：
SELECT * FROM table_name;
实例，使用两种语法从刚才插入的数据库中获取数据：
sqlite> .open test.db sqlite> .table COM TEST sqlite> SELECT *FROM COM ...> ; 1|JXX|18 2|CLJ|19 sqlite> .header on sqlite> .mode column sqlite> SELECT *FROM COM; ID NAME AGE ---------- ---------- ---------- 1 JXX 18 2 CLJ 19 sqlite> SELECT ID,NAME FROM COM; ID NAME ---------- ---------- 1 JXX 2 CLJ sqlite>
设置输出列的宽度
有时由于要显示的列太短，这种情况下输出被截断。此时可以使用 .width num,num...... 命令设置显示列的宽度，如下：
sqlite> .width 5,5,5 sqlite> SELECT *FROM COM; ID NAME AGE ----- ---------- ---------- 1 JXX 18 2 CLJ 19 sqlite>
Schema 信息
因为所有的点命令只在 SQLite 提示符中使用，所以在进行带有SQLite 的编程时，要使用下面的带有 sqlite_master 表的SELECT 语句来列出所有在数据库中创建的表，在test.db 中已经存在TEST，test 两个表，所有产生以下结果：
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table'; tbl_name ---------- TEST COM
SQLite 算术运算符
运算符是一个保留字或字符，主要用于 SQLite 语句的 WHERE 子句中执行操作，如比较和算术运算。
运算符用于指定 SQLite 语句中的条件，并在语句中连接多个条件。
sqlite> .mode line sqlite> select 20 + 10; 20 + 10 = 30 sqlite> select 20 - 10; 20 - 10 = 10 sqlite> select 20 / 10; 20 / 10 = 2 sqlite> select 20 * 10; 20 * 10 = 200 sqlite> select 20 % 10; 20 % 10 = 0
WHERE 子句是用来设置SELECT 语句的条件语句。
sqlite> SELECT * FROM COM WHERE AGE > 20; ID NAME AGE ---------- ---------- ---------- 4 HY 21 sqlite> SELECT * FROM COM WHERE AGE == 19; ID NAME AGE ---------- ---------- ---------- 2 CLJ 19 3 WX 19 sqlite> SELECT * FROM COM WHERE AGE >= 19; ID NAME AGE ---------- ---------- ---------- 2 CLJ 19 3 WX 19 4 HY 21 sqlite> SELECT * FROM COM WHERE AGE <> 19; ID NAME AGE ---------- ---------- ---------- 1 JXX 18 4 HY 21 sqlite> SELECT * FROM COM WHERE AGE != 19; ID NAME AGE ---------- ---------- ---------- 1 JXX 18 4 HY 21
其中 运算符 '<>19' 表示不等于 19。
SQLite 逻辑运算符
| AND | AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。 |
| BETWEEN | BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。 |
| EXISTS | EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。 |
| IN | IN 运算符用于把某个值与一系列指定列表的值进行比较。 |
| NOT IN | IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。 |
| LIKE | LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。 |
| GLOB | GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。 |
| NOT | NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。 |
| OR | OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。 |
| IS NULL | NULL 运算符用于把某个值与 NULL 值进行比较。 |
| IS | IS 运算符与 = 相似。 |
| IS NOT | IS NOT 运算符与 != 相似。 |
| || | 连接两个不同的字符串，得到一个新的字符串。 |
| UNIQUE | UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。 |
实例：
sqlite> SELECT * FROM COM; ID NAME AGE ---------- ---------- ---------- 1 JXX 18 2 CLJ 19 3 WX 19 4 HY 21 5 CCC 19 sqlite> SELECT * FROM COM WHERE AGE >=19 AND AGE <=20; --列出了AGE在19~20之间的数据 ID NAME AGE ---------- ---------- ---------- 2 CLJ 19 3 WX 19 5 CCC 19 sqlite> SELECT * FROM COM WHERE AGE <19 OR AGE >20; ID NAME AGE ---------- ---------- ---------- 1 JXX 18 4 HY 21 sqlite> SELECT * FROM COM WHERE AGE IS NOT NULL; ID NAME AGE ---------- ---------- ---------- 1 JXX 18 2 CLJ 19 3 WX 19 4 HY 21 5 CCC 19 sqlite> SELECT * FROM COM WHERE NAME LIKE 'J%'; -- 列出以 "J" 开头的 NAME 名单 ID NAME AGE ---------- ---------- ---------- 1 JXX 18 sqlite> SELECT * FROM COM WHERE NAME GLOB 'J*'; -- 列出 以 "J" 开头的 NAME 名单 ID NAME AGE ---------- ---------- ---------- 1 JXX 18 sqlite> SELECT * FROM COM WHERE AGE IN (18,21); -- 列出以AGE 的值为 18或 21 的所有记录 ID NAME AGE ---------- ---------- ---------- 1 JXX 18 4 HY 21 sqlite> SELECT * FROM COM WHERE AGE NOT IN (18,21); -- 列出AGE值不是 18或 21 的所有记录 ID NAME AGE ---------- ---------- ---------- 2 CLJ 19 3 WX 19 5 CCC 19 sqlite> SELECT * FROM COM WHERE AGE BETWEEN 19 AND 21; -- 列出 AGE 值在19 和21 的所有记录 ID NAME AGE ---------- ---------- ---------- 2 CLJ 19 3 WX 19 4 HY 21 5 CCC 19 sqlite>
