学习SQLite之路（四）

不通过执行一个完整的转储和数据的重载来修改已有的表。

可以使用 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

< 上一篇 winForm连接数据库（sqlserver2005） 下一篇 > postgresql----根据现有表创建新表 人气教程排行 229次 1 oracle 用户解锁和修改用户密码 229次 2 解决：安装SQL Server 2008 Native Client遇到错误（在Navicat premium新建sqlserver连接时 需要）：An error occurred during ...HRESULT: 0x80070422（注意尾部的错误号） 229次 3 rocksdb 编译步骤 229次 4 adb命令开关蓝牙及NFC 229次 5 解决数据库连接错误 您在wp-config.php文件中提供的数据库用户名和密码可能不正确，或者无法连接到localhost上的数据库服务器，这意味着您的主机数据库服务器已停止工作。 229次 6 使用java自动填充，实现mysql的创建修改时间的自动填充 229次 7 Linux mount挂载磁盘报错 mount: wrong fs type, bad option, bad superblock on /dev/vdb 228次 8 关于mysql创建数据库中字符集和排序规则的选择 228次 9 Linux设置Mysql开机自启动服务 227次 10 SQL2008：WITH MOVE 子句可用于重新定位一个或多个文件 227次 11 图数据库Neo4j在GIS系统的应用 227次 12 SpringBoot配置 druid 数据源配置 慢SQL记录 227次 13 SQL state [72000]; error code [1013]; ORA-03111: 通信通道收到中断; java.sql.SQLException: ORA-01745: 无效的主机/绑定变量名;java.sql.SQLException: ORA-01013: 用户请求取消当前的操作 227次 14 docker-compose启动MySQL并配置远程登录 225次 15 Mysql安装（for mac） 225次 16 kettle将图片转换至二进制存储至数据库 225次 17 adb的安装及配置 224次 18 使用PHP控制MODBUS-RTU设备 224次 19 MySQL 查询时间差值大于某一个值的 记录 224次 20 MongoDB内存配置 --wiredTigerCacheSizeGB 本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！ 登录   记住我的登录 忘记密码？ 登录 我已阅读用户协议及版权声明 注册