Sqlite3总结

数据类型

存储类	描述
NULL	空值（只能存）
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

用来存储时间的数据

SQLite没有单独用于存储时间的数据，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

存储类	日期格式
TEXT	格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期。
REAL	从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
INTEGER	从 1970-01-01 00:00:00 UTC 算起的秒数。

基本命令

所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号（;）结束。

数据库

新建数据库

sqlite3 temp.db

检查数据库列表

sqlite> .databases
main: /home/namewei/temp.db

导出数据库到一个文本文件中

[email protected]:~$ sqlite3 temp.db .dump > tempDB.sql

表操作

创建表

CREATE TABLE table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

创建表

DROP TABLE database_name.table_name;

数据操作（增删改查）

插入

--普通插入（不推荐用，数据库可能改动，之后代码得大改，工程量大）
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
--按值插入
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

--高级插入(在一个有一组字段的表上使用 select 语句，填充数据到另一个表中)
INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

查询

--查询所有信息
SELECT * FROM table_name;
--查询某一些列的信息
SELECT column1, column2, columnN FROM table_name;

运算符

a=10， b=20

运算符	描述	实例
+	加法 - 把运算符两边的值相加	a + b 将得到 30
-	减法 - 左操作数减去右操作数	a - b 将得到 -10
*	乘法 - 把运算符两边的值相乘	a * b 将得到 200
/	除法 - 左操作数除以右操作数	b / a 将得到 2
%	取模 - 左操作数除以右操作数后得到的余数	b % a will give 0

表达式

--布尔表达式
SELECT column1, column2, columnN 
FROM table_name 
WHERE SINGLE VALUE MATCHING EXPRESSION;
--数值表达式
sqlite> select (15+6) as num;
num = 21
--日期表达式
sqlite> SELECT CURRENT_TIMESTAMP;
2020-03-04 14:34:37

Where子句

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

and/or运算符

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

update

--当[condition]时，置column1为value1。。。。。。。。。
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

delete

DELETE FROM table_name
WHERE [condition];

like

--百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。可以使用 AND 或 OR 运算符来结合 N 个数量的条件
SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'

limit

--带有 LIMIT 子句的 SELECT 语句的基本语法(显示前几行)
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]
-- LIMIT 子句与 OFFSET 子句一起使用时（显示几行，从第几行开始）
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

Order By

--根据column1排，ASC正序，DESC倒叙
SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

group by

GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

having

用来过滤最终出现的结果

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

distinct

用于消除重复记录,只显示某一列不重复的值。

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

SQLite约束

• NOT NULL 约束：确保某列不能有 NULL 值。
• DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
• UNIQUE 约束：确保某列中的所有值是不同的。
• PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
• CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

NOT NULL 约束

--不接受空值
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

DEFAULT约束

--默认约束（没有赋值的时候，默认赋初值）
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

UNIQUE约束

--UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
    --不能存在age一样的数据
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

PRIMARY KEY约束

--主键约束，唯一，且一个表只能有一个主键。
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

CHECK 约束

--对输入的数据进行检查判断是否符合条件
CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

SQLite Join

SQL的连接主要分为三种类型

• 交叉连接 - CROSS JOIN
• 内连接 - INNER JOIN
• 外连接 - OUTER JOIN

交叉连接

内连接

外连接

补充

范式

---

第一范式

需要满足每一列都不可再分割

姓名	电话	性别
火警	110	男
急救	120	男

姓名	电话	电话	性别
	座机	电话
李白	0595-*****	130*	男

---

第二范式

在第一范式的基础上，所有属性完全依赖**于主键，完全依赖就是不能只是取决于主键的一部分。

---

第三范式

在第二范式的基础上，消除传递依赖，比如学生表里有学生属于的班级编号，但不能有班级的名称，班级人数等班级信息，因为班级信息可有由班级编号通过班级表推出来，有传递依赖。

---

数据库的设计过程

**需求分析**

**概念数据库设计**

**逻辑数据库设计**

**物理数据库设计**

索引