【数据库编程-SQLite3（四）】基本常用操作

1、sql数据类型

Sqlite3主要数据类型如下：

同时sqlite3也接受如下的数据类型：

1.1、约束

表的每一列都有一些限制属性，比如有的列的数据不能重复，有的则限制数据范围等，约束就是用来进一步描述每一列数据属性的。SQLite 数据库常用约束如下：

2、数据定义语言DDL(Data Definition Language)

常用的有CREATE和DROP，用于在数据库中创建新表或删除表，以及为表加入索引等。主要是对数据库中的某些对象(例如，database,table)进行管理

2.1、创建表

表创建时，默认创建了rowid列，作为自增长ID
sqlite的是动态的数据类型，而非一般数据库的静态数据类型

create table 表名(字段名 字段类型 [约束],...);

示例：

-- 用户表
DROP TABLE IF EXISTS tbl_user;											-- 表存在，则删除
CREATE TABLE IF NOT EXISTS tbl_user									-- 表不存在，就创建
(
	user_id integer primary key AUTOINCREMENT,				--主键自增
	user_name varchar(20) not null unique,						-- 唯一   不为空
	user_pwd varchar(20) default '123456' not null,		-- 默认值123456  不为空
	user_status int(11) check(user_status=0 or user_status=1) default 0,	--登录状态类型  默认0
	reg_time datetime not null												-- 系统时间
);

2.2、修改表

修改表的名称：

alter table 表名 rename to 新名字;

增加字段：

alter table 表名 add column 新增加字段名 字段类型 [约束];

2.3、删除表

表删除后，表结构和记录都被删除；在默认模式下，数据库文件不会减少，该空间留给后续操作使用
表删除后，表的索引以及触发器也相应被删除

drop table 表名;

3、 数据操纵语言DML(Data Manipulation Language)

主要有几种形式，是对数据库中的数据进行一些简单操作，如insert,delete,update,select等

3.1、INSERT语句

设置了not null那一列 必须要赋值，而且表名字不区分大小写。

insert into 表名 values(字段值,...);
insert into 表名(字段名,...) values(字段值,...);

示例

insert into t_student values(1001,"xxx",18,"CN");
insert into t_student(sno,sname,sage) values(1001,"xxx",18);

3.2、DELETE语句

and/or 可在where子句中连接多个条件

delete from 表名 where 条件;

示例

delete from t_student where sno=1001;

3.3、UPDATE语句

UPDATE <表名> SET <列名 = 更新值>  [WHERE <更新条件>]

示例

UPDATE student SET sage=29 WHERE sname='xxx';

3.4、SELECT语句

SELECT [*]|[列名] 
   FROM [表名]
   WHERE [查询条件]

示例

select * from tab_student where stu_no=‘1001’；

4、高级查询

4.1、SQL通配符

4.2、模糊查询–like

查询班级编号以jn开头所有学生
select * from t_student where sclass like 'jn%'
查询2004入学的所有学生
select * from tab_t_student where sclass like ‘__2004%’
查询2001~2005年间入学的所有学生
select * from tab_t_student where sclass like ‘%200[1-5]%'

4.3、between…and

查询jn205班02课程成绩在60~80分的成员记录

select * from t_score where sno like 'jn205%' and Cno='02' and  score between 60 and 80

说明：选取该列数据属于between… and区间,包含边界值的

4.4、order by 子句

将查询后结果集重新排序
排序：order by (升序 asc,降序 desc)

select * from t_stu order by sno desc/asc;

4.5、示例

–播放量前三的视频

select video_id video_name
from tbl_video
order by play_counts desc
limit 3

–分页 limit num offset start
当前页（pagenow）=1 start =（pagenow-1）*3

select *from  tbl_user limit 3 offset 0;  --第一页
select *from  tbl_user limit 3 offset 3;  --第二页
select *from  tbl_user limit 3 offset 6;  --第三页

5、视图

视图是查看数据库表中数据的一种方式。视图提供了存储预定义的查询语句作为数据库中的对象以备以后使用的能力。视图是一种逻辑对象，是一种虚拟表。

5.1、创建SQLite视图

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

其中，view_name是要创建的视图名称，column1, column2, …是要查询的列名，table_name是要查询的表名，condition是可选的查询条件。

5.2、修改和删除SQLite视图

要修改已存在的SQLite视图，可以使用CREATE OR REPLACE VIEW语句来替换原有的视图定义。例如：

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

删除一个SQLite视图，可以使用DROP VIEW语句。例如：

DROP VIEW view_name;

6、索引

索引是对数据库表中一个或多个列（例如，employee 表的姓氏 (lname) 列）的值进行排序的结构。使用索引可以利用索引快速访问数据库表中的特定信息。

6.1、创建索引的优缺点

优点：
快速访问数据；
加强数据行的唯一性；
缺点：
占用硬盘存储空间；
在插入、修改、删除数据时将产生额外开销；

6.2、创建和查看索引

index_name 是你为索引指定的名称，table_name 是你的表名，column_name 是你想为其创建索引的列。

CREATE INDEX index_name ON table_name (column_name);

查看索引:

PRAGMA index_list(table_name) 

7、连表查询

7.1、内连接-inner join

select *from tbl_struct
inner join
tbl_result
on tbl_struct.strcut_id = tbl_result_id

表取别名----as可写可不写

select a.name,b.age  from tbl_struct as a
inner join
tbl_result b
on a.strcut_id = b.result_id

字段取别名

select a.name ‘学生姓名’,b.age ‘学生年龄’  from tbl_struct as a
inner join
tbl_result b
on a.strcut_id = b.result_id

7.2、外连接-left join

外连接分为左外连接和右外连接，但是在sqlite3中只有左外连接

• 查询到的数据是以左表的主键为主，一 一对应的。会出现空数据
• 不想查询到空数据，需让成绩表为（左表）主表。【一般尝试多次确定主表】

select *from tbl_struct
left join
tbl_result
on tbl_struct.strcut_id = tbl_result_id

7.3、将查询到的重复行进行过滤

Distinct子句

select distinct ssex from t_student

示例

select distinct *from tbl_struct
left join
tbl_result
on tbl_struct.strcut_id = tbl_result_id

8、子查询

子查询：一个 SELECT 语句嵌套在另一个 SELECT 语句中。

8.1、in子查询

查询有成绩的学生信息

select * from tab_student 
where stu_id in (select stu_id from tab_score)

查询1课程有成绩的学生信息

select * from t_student 
where sno in (select sno from t_score  where cno='1')

8.2、exists子查询

判断是否有行存在
如果有行存在，语句返回true；否则false

查询有成绩的学生列表

 select * from t_student a
   where exists (select sno from t_score b where a.sno=b.sno)

查询没有成绩的学生列表

select * from t_student a
   where not exists (select sno from t_score b where a.sno=b.sno)

9、集合操作-并union

并的前提-------表或虚拟表必须拥有相同数量的列，列也必须拥有相似的数据类型

select * from tab_student where ssex=‘女’
union
select * from tab_student where ssex=‘男’