sqlite3 关系型数据库语言 SQL 语言

        SQL(Structured Query Language)语言是一种结构化查询语言,是一个通用的,功能强大的关系型数据库操作语言.

包含 6 个部分:

1.数据查询语言(DQL:Data Query Language)

        从数据库的二维表格中查询数据,保留字 SELECT 是 DQL 中用的最多的语句

2.数据操作语言(DML)

        最主要的关键字是 INSERT,UPDATE,和 DELETE,分别用于向二维表格中插入行,修改和删除行

3.事务处理语言(TPL)

        它的语句能确保被 DML 语句影响的表的所有行及时得以更新。TPL 语句包括 BEGIN TRANSACTION,COMMIT 和 ROLLBACK。

4.数据控制语言(DCL)

        确定单个用户或者用户组对数据库操作的权限

5.数据定义语言(DDL)

        主要的动作包括 CREATE 和 DROP,用来在数据库中新建表格或者删除表格

6.指针控制语言(CCL)

        用于对一个或者多个表格单独行的操作

        SQL 语句最好写成大写.SQL 语句在执行的时候,如果给出的是小写就会转换成大写再去执行。 所有 SQL 语句都以分号结尾。如果没有加分号，将会出现 数据库的命令输入将变为…>就是在等待你输入分号结束。

创建一个 sqlite3 数据库

方式一：sqlite3 命令方式

【命令语法】

sqlite3 + sqlite3 数据库名

【命令示例】

sqlite3 test.db

【图示】

方式二：点命令方式

【命令语法】

.open + sqlite3 数据库名

【命令示例】

.open test.db

【图示】

【备注】

        打开已存在数据库也是用 .open 命令，以上命令如果 test.db 存在则直接会打开，不存在就创建它。

往数据库中新建二维表格

【命令语法】

CREATE TABLE 表名

(

        [字段 1] [数据类型] ["约束"],

        [字段 2] [数据类型] ["约束"],

        [字段 3] [数据类型] ["约束"],

        ....

 );

【数据类型】

【数据类型】

1) 数值类型

        TINYINT（微小整型）、SMALLINT（小整型）、MEDIUMINT（中等整型）、INT（标准整 型）、BIGINT（大整型）

备注： INT(size):数值类型后面括号里的 size 可以规定这个字段的的最大值

2) 日期/时间类型

        包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

3) 字符串类型

        包括 CHAR、VARCHAR（变长字符串）、BINARY（二进制）、VARBINARY（变长二进 制）、BLOB、TEXT（文本）、ENUM（枚举） 和 SET 等。

        CHAR(M) 固定长度非二进制字符串 M 字节，1<=M<=255 长度固定为 M

        VARCHAR(M) 变长非二进制字符串 L+1 字节，L< = M 和 1<=M<=255，长度不超过 M

        TEXT小的非二进制字符串  L+2 字节，在此，L<2^16

4) 二进制类型

        包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。 【约束】

        NOT NULL 不为空,

        UNIQUE  唯一,此列数据不能重复

        PRIMARY KEY 主键,主键必须是 NOT NULL 和 UNIQUE

        FOREIGN KEY 外键,这一列在外部的表中是主键（保持数据的一致性,维护数据的完整性）

备注： sqlite3 中外键默认是关闭的，需要使用 PRAGMA foreign_keys = ON; 命令打开外键

CHECK 用于限制列中值的范围 例：CHECK(ID>0)

DEFAULT 设置列的默认值 例：DEFAULT 10086

【命令示例】

        新建一个名字为 STUINFO 的二维表，ID 为主键

CREATE TABLE STUINFO (

ID INT PRIMARY KEY,

NAME VARCHAR(255) NOT NULL,

ADDR TEXT,

TEL TEXT DEFAULT 10086,

CHECK(ID>0)

);

【图示】

【外键创建示例】

        CREATE TABLE CJB(ID INT,KCH INT,SCORE FLOAT,FOREIGN KEY(ID) REFERENCES STU(ID));

        上面语句就是新建一个 CJB 表格,其中 ID 是外键,这个外键是 STU 表格中的主键，其中这个 REFERENCES 是外键约束，表示这个外键是哪个表里的主键。上图就是创建了一个 CJB，有一个外键 ID，这个 ID 还是 STU 表中的主键。

删除一个表格

【命令语法】

DROP TABLE 表名;

【命令示例】

DROP TABLE STUINFO;

【图示】

修改表名

【命令语法】

ALTER TABLE 表名 RENAME TO 新表名;

【命令示例】

ALTER TABLE STUINFO RENAME TO STU;

【图示】

增加表中字段

【命令语法】

ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 约束;

【命令示例】

ALTER TABLE STU ADD COLUMN HEIGHT INT(300) DEFAULT 100;

【备注】

        1 新增列时约束不能为 NOT NULL，因为当前表中的已有的数据将默认新增列为 NULL

        2. 在 sqlite3 中,修改列名,删除列都是不支持的

【图示】

向表中增加用户数据

【命令语法】

语法 1：

INSERT INTO 表名 VALUES(值 1,值 2,值 3.....);

// 每一列都必须提供一个值,和创建表格的时候要一一对应

语法 2：

INSERT INTO 表名(列名 1,列名 2,...)VALUES(值 1,值 2....);

//提供的列名必须和值个数相同

【命令示例】

示例 1：

        INSERT INTO STU VALUES(2,”jiuyue”,3,10010,5,168);

示例 2：

        INSERT INTO STU(ID,NAME,ADDR,TEL,SEX,HEIGHT)VALUES(2,”jiuyue”,3,10010,5,168);

【图示】

表中数据查询

基本查找

【命令语法】

SELECT 列名 FROM 表名 WHERE 条件;

【条件运算符】

        基本语法如下：

                SELECT 列名 1,列名 2.... FROM 表名 WHERE 列 运算符 值

                运算符可以是下面一些:

                                        = 等于

                                        <> 不等于

                                         > 大于

                                        < 小于

                                        BETWEEN AND 在某一个范围之间

                                        //例：BETWEEN 2 AND 3 2~3 之间

                                        LIKE 模糊查找

【备注】

                有多个条件可以使用 AND 和 OR 来连接条件

                                AND 并且

                                OR 或者

【命令示例】

        SELECT * FROM STU; //查看 STU 表中所有数据

        SELECT ID FROM STU WHERE ID=1; //查看 STU 表中 ID 这一列 ID 号为 1 的数据

        SELECT * FROM STU WHERE TEL="10086" OR NAME="zhangsan"; // 查看 STU 表中列 TEL 值等于 10086 或 NAME 列值等于 zhangsan 的数据

        SELECT ID,NAME,TEL FROM STU WHERE NAME LIKE 'zhang%'; //查看所有姓 zhang 的同学的信息

【图示】

其他的一些查看语法

【命令示例】

SELECT MAX(ID) FROM STU; //查找列中的最大值

SELECT MIN(ID) FROM STU; //查找列中的最小值

SELECT AVG(ID) FROM STU; //平均值

SELECT SUM(ID) FROM STU; //总和

SELECT COUNT(ID) FROM STU; //行数

排序和限行查询

【命令语法】

排序查询：

SELECT * FROM 表名 ORDER BY 字段名 ASC/DESC;

                                //ASC 为升序，DESC 为降序

限行查询：

SELECT * FROM STU LIMIT 行数

【命令示例】

        SELECT * FROM STU ORDER BY ID ASC; //在 STU 表中以 ID 号升序查看

        SELECT * FROM STU LIMIT 3; //只查看前 3 行

        SELECT * FROM STU ORDER BY ID DESC LIMIT 3; //降序排序并只查看前 3 行

多表查询

【命令语法】

SELECT 表名 N.列名 N,表名 M.列名 M FROM WHERE 条件;

【命令示例】

  1.查看表 STU 和 CJB 中相关列满足 STU.ID = CJB.ID 条件的数据

        SELECT STU.ID,STU.NAME,CJB.ID,CJB.KCH,CJB.SCORE FROM STU,CJB WHERE STU.ID = CJB.ID;

2.查看表 STU,CJB 和 KCB 中相关列满足 STU.ID = CJB.ID 且 KCB.KCH = CJB.KCH 条件的数据

        SELECT STU.ID,KCB.KCH,STU.NAME,CJB.SCORE,KCB.KCM,KCB.TEACHER FROM STU,CJB,KCB WHERE STU.ID = CJB.ID AND KCB.KCH = CJB.KCH;

3.加上查看 CJB.SCORE 列中降序排序的前三列

        SELECT STU.ID,KCB.KCH,STU.NAME,CJB.SCORE,KCB.KCM,KCB.TEACHER FROM STU,CJB,KCB WHERE STU.ID = CJB.ID AND KCB.KCH = CJB.KCH AND KCB.KCH = 1 ORDER BY CJB.SCORE DESC LIMIT 3;

更新数据(数据修改)

【命令语法】

UPDATE 表名 SET 列名 1 = 新值,列名 2 = 新值,... WHERE 条件;

【命令示例】

 1. 将 STU 表 ID 为 6 的一行数据的 NAME 和 TEL 改变

        UPDATE STU SET NAME="wanglang",TEL="88888888" WHERE ID = 6;

2. 将 CJB 表中 SCORE 中 0 到 60 的数据全部改为 60

        UPDATE CJB SET SCORE = 60 WHERE SCORE > 0 AND SCORE < 60;

删除用户数据

【命令语法】

DELETE FROM 表名 WHERE 条件;

【命令示例】

1. 删除 STU 表中 TEL 为“88888888“的一行数据

        DELETE FROM STU WHERE TEL = "88888888";