sqlite数据库的基本操作

1.命名

• 我们数据库命名不要用逗号啊这样的方式来命名，例如： teacher‘s table， 正确：teacher_table
  因为表名称只能是一个单词，如果写成 teacher table 就会报错，所以推荐多个单词之间用下划线来连接

2.我们的命令都带有一定的格式

.*
例如：创建表： CREATE TABLE 表名称 (字段名称 类型, 字段名称 类型, 字段名称 类型);
CREATE TABLE t_classify(
classify_id integer primary key autoincrement,
file_id integer ,
list_id integer
);

3.文本格式的数据在插入的时候前后要加单引号

.*
INSERT INTO TEACHER_TABLE VALUES(1, ‘ZHANGSAN’);

**4.简单的增删改查：

– 插入数据：
– DATE 仅仅只有年月日， DATETIME 不仅有年月日还有时分秒还有毫秒值
CREATE TABLE STU_INF (SNO TEXT, SNAME TEXT, SEX TEXT, TEL TEXT, EMAIL TEXT, AGE INT, CTIME DATETIME);
– 插入时间的话可用文本来进行插入，格式： YYYY-MM-DD HH-MI-SS
INSERT INTO STU_INF VALUES (‘JF180801’, ‘狗蛋’, ‘男’, ‘13846901789’, ‘13846901789@qq.com’, 27, ‘2019-01-15 09:17:30’);
– 插入时间的话，除了可以使用文本还可以使用 datetime(‘now’, ‘localtime’)
INSERT INTO STU_INF VALUES (‘JF180805’, ‘狗腰子’, ‘男’, ‘16809901789’, ‘16809901789@qq.com’, 23, datetime(‘now’, ‘localtime’));

如果我有很多字段在一张表里面，我要插入数据的话，是不是很麻烦，特别是老板这时候只要我插入其中一个字段
– 例如： 老板： 只要插入学号和名字（JF180807, 狗娃），其他不用
– 可以进行字段的选择性插入
INSERT INTO STU_INF (SNO, SNAME) VALUES (‘JF180807’, ‘狗娃’);
– 插入数据不知道需要插入什么，可以用null代替，代表暂无数据
INSERT INTO STU_INF VALUES (‘JF180807’, ‘狗娃’, null, null, null, null, null);

-- 数据删除：
我们其实是需要逐个删除，按照条件删除
– 昨天格式： DELETE FROM 表名称
– 今天格式： DELETE FROM 表名称 WHERE 字段名 = 值 ; 翻译： 找到字段名 = 值的那一行，删除掉
– 删掉狗娃数据
DELETE FROM STU_INF WHERE SNAME = ‘狗娃’; – 找到名字叫狗娃的那行数据，删掉

– 数据修改：
我们其实修改也是需要找到对应的数据，按条件修改，不能将整个表数据都拿来修改
– 昨天格式： UPDATE 表名称 SET 字段名= 值;
– 今天格式： UPDATE 表名称 SET 字段名= 值 WHERE 字段名 = 值 ;

– 修改学号是JF180802的学员的年龄，改成30岁
UPDATE STU_INF SET AGE = 30 WHERE SNO = ‘JF180802’; – 找到学号是JF180802学员，年龄改成30岁

– 将所有学员的年龄+1岁
UPDATE STU_INF SET AGE = AGE + 1;

– 数据查询：
我们的数据查询是整个命令中的重头戏，也是最难的，所有的企业面试几乎都会考到查询数据这块

– 昨天格式： SELECT * FROM 表名称;
– 今天格式： SELECT * FROM 表名称 WHERE …;

– 1. 查询学号是JF180802的学员信息
SELECT * FROM STU_INF WHERE SNO = ‘JF180802’; – 找到学号是JF180802学员，的信息并显示

– 2.查询姓名叫做狗腰子的学员并且性别是男的学员
– 找到姓名是狗腰子并且性别是男的学员，的信息并显示，这里要注意：并且（and）的意思是：条件都要满足
SELECT * FROM STU_INF WHERE SNAME = ‘狗腰子’ AND SEX = ‘男’;

– 3.查询姓名叫做狗腰子的学员或者年龄是31岁的学员
– 找到姓名是狗腰子 或者 年龄31岁的学员，的信息并显示，这里要注意：或者（or）的意思是，只要满足其中一个条件都算满足
SELECT * FROM STU_INF WHERE SNAME = ‘狗腰子’ OR AGE = 31;

– 4.查询所有数据，但是要按照年龄来排序（升序：从小到大）
– 注意： 排序都是使用ORDER BY 字段; 按照字段的数据信息来进行排序
– 注意： 如果一条命令中同时需要排序和where过滤，先写where过滤，再写排序语句
– 注意： ASC这个单词可以不写，默认就是从小到大
SELECT * FROM STU_INF ORDER BY AGE ASC;

– 5.查询所有数据，但是要按照年龄来排序（升序：从大到小）
SELECT * FROM STU_INF ORDER BY AGE DESC;

– 6.查询年龄大于25岁的学员信息, 年龄倒叙排列
SELECT * FROM STU_INF WHERE AGE > 25 ORDER BY AGE DESC;

– 7. 查询年龄大于25岁小于30岁的学员信息
SELECT * FROM STU_INF WHERE AGE > 25 AND AGE < 30;

– 注意： BETWEEN AND 就是意味着在xxx和xxx之间
– 注意： BETWEEN AND 是有包含指定的数值的， 25和31是有包含的
SELECT * FROM STU_INF WHERE AGE BETWEEN 25 AND 31;

– 8.查询名字中带有 狗 的学员信息
– LIKE : 像
SELECT * FROM STU_INF WHERE SNAME LIKE ‘%狗%’;

– 9.查询名字中，第一个字是 狗 的学员信息
SELECT * FROM STU_INF WHERE SNAME LIKE ‘狗%’;

– 10.查询名字中，最后一个字是 狗 的学员信息
SELECT * FROM STU_INF WHERE SNAME LIKE ‘%狗’;

– 11.查询名字中，不带 狗 的学员信息
– NOT LIKE 不带，不像
SELECT * FROM STU_INF WHERE SNAME NOT LIKE ‘%狗%’;

– 12.排序，我们按照学号来排序
– 注意： 排序不仅仅可以排数字还可以排文本
– 注意： 不支持中文排序，仅仅支持英文的文本排序
SELECT * FROM STU_INF ORDER BY SNO DESC;
SELECT * FROM STU_INF ORDER BY SNAME DESC;

– 13.区间判断
– 区间判断不仅仅可以判断数字还能判断时间和文本（不支持中文）
SELECT * FROM STU_INF WHERE CTIME > ‘2019-01-14 10:20:30’;
SELECT * FROM STU_INF WHERE SNO > ‘JF180803’;

– 14.别名
– 查找所有学员的姓名
– 格式： 字段名 别名， 或者 字段名 as 别名
SELECT SNAME as 姓名 FROM STU_INF;
SELECT SNAME 姓名 FROM STU_INF;

– 15.查询 所有学员的年龄之和
– 注意：SUM() 将找到的对应字段，数据列的值进行叠加
SELECT SUM(AGE) FROM STU_INF;

– 16.查询 学员的平均年龄
SELECT AVG(AGE) FROM STU_INF;

– 17.查询 最大年龄
SELECT MAX(AGE) FROM STU_INF;

– 18.查询 最小年龄
SELECT MIN(AGE) FROM STU_INF;

– 19.查询学员人数 (一共多少行)
SELECT COUNT(*) FROM STU_INF;

– 20.分组
– 按照数值进行分组，相同的数值就分到一个组中，例如，按照性别来分组，那么只要是男的就分到一组，女的分到另一组
– 这里的COUNT 所得到的结果是 每个组的人数
SELECT SEX, COUNT(SEX), AVG(AGE) FROM STU_INF GROUP BY SEX;

21.联表查询
当查询数据需要几张表联系在一起的时候，不同表相同的字段，要在字段前加上表名，否则在进行dao的封装时，会出错。
例如：
select file_address from t_classify,t_file,t_list where (t_classify.[file_id]=t_file.[file_id]) and (t_classify.[list_id]=t_list.[list_id]) and list_name=?

数据的完整性：

当我们做数据增删改的时候，需要判断数据的合理性
– 如果招一个专门来检测合理性的人员不划算，最好是我在做数据插入的时候，计算机能够自动帮我审核，只要有问题就不能做增删改操作
– 而这样的审核就叫做 数据约束
– 常用约束种类：
– 1.非空 – NOT NULL
– 2.唯一 – UNIQUE
– 3.限制（年龄） – CHECK
– 4.默认 – DEFAULT

– 在什么时候写呢？ 我们在创建表的时候就把对应的字段加上对应的约束就好了
– 格式： 字段名 类型 约束

CREATE TABLE STU_INF (SNO TEXT PRIMARY KEY, SNAME TEXT NOT NULL, SEX TEXT DEFAULT 男, TEL TEXT UNIQUE, EMAIL TEXT, AGE INT CHECK( AGE < 100 ), CTIME DATETIME);

– 优势： 数据的合理性更加完善，劣势：每次做增删改计算机都需要判断，开销大，所以这种约束，需要慎重
INSERT INTO STU_INF (SNO, SNAME, TEL) VALUES (‘JF180801’, ‘HEHEDA’, ‘123’);

– 5.主键约束，一定要记得每张表必须要有一个字段作为主键（非空且唯一） – PRIMARY KEY
– 注意：如果是数字的主键，还可以自增长, 但是类型改成 INTEGER

CREATE TABLE TEACHER_INF (TID INTEGER PRIMARY KEY, TNAME TEXT);
INSERT INTO TEACHER_INF VALUES(1, ‘林小丹’);
INSERT INTO TEACHER_INF(TNAME) VALUES(‘雷燕’);

– 小结： 主键约束每张表都需要做，并且最好主键设置成数字类型，这样就可以自增长了，就像：CREATE TABLE TEACHER_INF (TID INTEGER PRIMARY KEY, TNAME TEXT);