SQLite常用语句

SQLite常用语句

---

数据类型

类型	描述
NULL	值是一个空值
INTEGER	带符号的整数，根据值得大小存储在1、2、3、4、6或8字节中。兼容(等同于)其他数据库的INT,INTEGER,TINYINT,SMALLINT,MEDIUMINT,BIGINT,UNSIGNED BIG INT,INT2,IN8
REAL	浮点值，存储8字节的浮点数字。兼容其他数据库的REAL,DOUBLE,DOUBLE PRECISION,FLOAT
TEXT	一个文本字符串。兼容其他数据库的CHAR,VARCHAR,NCHAR等等
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 开始算秒数

---

创建表

语法

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

实例

创建一个STUDENT表，ID为主键，NAME为TEXT类型非空，AGE为INT类型非空，ADDRESS为CHAR类型50字符以内可以为空：
CREATE TABLE STUDENT(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
);

---

删除表

语法

DROP TABLE database_name.table_name;

实例

删除表STUDENT
DROP TABLE STUDENT;

---

插入语句insert

语法

INSERT INTO TABLE_NAME [(column1, column2, column3,…columnN)]
VALUES (value1, value2, value3,…valueN);

实例

在STUDENT表中添加一条记录，ID为1，NAME为ZS，AGE为21，ADDRESS为成都:
INSERT INTO STUDENT
(ID,NAME,AGE,ADDRESS)
VALUES
(1, ‘ZS’, 21, ‘成都’);

---

查询语句select

语法

SELECT column1, column2, columnN FROM table_name;

实例

• 查询STUDENT表的所有记录
  SELECT * FROM STUDENT
• 查询STUDENT表中ID为1的记录(WHERE)
  SELECT * FROM STUDENT WHERE ID=1
• 查询STUDENT表中ID为1的学生的姓名
  SELECT NAME FROM STUDENT WHERE ID=1
• 查询STUDENT表中NAME为ZS的学生的AGE和ADDRESS
  SELECT AGE,ADDRESS FROM STUDENT FROM STUDENT WHERE NAME = 'ZS’
• 查询AGE 大于等于 20 且ID大于1的所有记录(AND)
  SELECT * FROM STUDENT WHERE AGE >= 20 AND ID > 1
• 查询AGE 大于等于 20 或ID大于1 的所有记录(OR)
  SELECT * FROM STUDENT WHERE AGE >= 20 OR ID > 1
• 查询 ADDRESS为 NULL 的所有记录(IS)
  SELECT * FROM STUDENT WHERE ADDRESS IS NULL
• 查询 ADDRESS不为 NULL 的所有记录(NOT IS)
  SELECT * FROM STUDENT WHERE ADDRESS IS NOT NULL
• 查询 AGE 的值为 20 或 21 的所有记录(IN)
  SELECT * FROM STUDENT WHERE AGE IN ( 20, 21 );
• 查询 AGE 的值既不为 20 又不为 21 的所有记录(NOT IN)
  SELECT * FROM STUDENT WHERE AGE NOT IN ( 20, 21 );
• 查询 AGE 的值在 20 和 22 之间的所有记录(BETWEEN…AND)
  SELECT * FROM STUDENT WHERE AGE BETWEEN 20 AND 22;
• 查询在ADDRESS为成都的学生中，AGE大于20的记录
  SELECT * FROM STUDENT WHERE AGE > (SELECT AGE FROM STUDENT WHERE ADDRESS = ‘成都’)
• 查询在ADDRESS为成都的学生的AGE
  SELECT AGE FROM STUDENT WHERE EXISTS (SELECT AGE FROM STUDENT WHERE ADDRESS = ‘成都’)
• 查询学生信息，只显示前3个(LIMIT)
  SELECT * FROM STUTENT LIMIT 3
• 查询学生信息，只显示前3个,从第三个(偏移2位)开始数(LIMIT…OFFSET)
  SELECT * FROM STUTENT LIMIT 3 OFFSET 2
• 查询学生信息，按AGE升序显示(ORDER BY)
  SELECT * FROM STUTENT ORDER BY AGE ASC
• 查询学生信息，按AGE降序显示(ORDER BY)
  SELECT * FROM STUTENT ORDER BY AGE DESC
• 查询学生的NAME并消除重复记录(DISTINCT)
  SELECT DISTINCT NAME FROM STUDENT

---

模糊查询

语法

• SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用：
  百分号 （%）、下划线 （_）
  百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用:
  SELECT column_list
  FROM table_name
  WHERE column LIKE ‘XXXX%’
  or
  SELECT column_list
  FROM table_name
  WHERE column LIKE ‘%XXXX%’
  or
  SELECT column_list
  FROM table_name
  WHERE column LIKE ‘XXXX_’
  or
  SELECT column_list
  FROM table_name
  WHERE column LIKE ‘_XXXX’
  or
  SELECT column_list
  FROM table_name
  WHERE column LIKE 'XXXX’
• SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。
  星号 （*）
  问号 （?）
  星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。

实例

• 查询 NAME 以 ‘Z’ 开始的所有记录
  SELECT * FROM STUDENT WHERE NAME LIKE 'Z%'
• 查询 NAME 以 ‘Z’ 结尾的所有记录
  SELECT * FROM STUDENT WHERE NAME LIKE '%Z’
• 查询 NAME 包含 ‘Z’ 的所有记录
  SELECT * FROM STUDENT WHERE NAME LIKE '%Z%'
• 查询 NAME 的 'Z’开头两位字符 的所有记录
  SELECT * FROM STUDENT WHERE NAME LIKE 'Z_'

---

内置函数

实例

• 求AGE平均数AVG()
  SELECT AVG(AGE) AS “平均数” FROM STUDENT;
• 求AGE总数SUM()
  SELECT SUM(AGE) AS “总年龄” FROM STUDENT;
• 求学生总数COUNT()
  SELECT COUNT(*) AS “学生总数” FROM STUDENT;
• 求年龄最大值MAX()
  SELECT MAX(AGE) FROM STUTENT
• 求年龄最小值MIN()
  SELECT MIN(AGE) FROM STUTENT
• 把所有NAME的值改成大写UPPER()
  SELECT * UPPER(NAME) FROM STUTENT
• 把所有NAME的值改成小写LOWER()
  SELECT * LOWER(NAME) FROM STUTENT
• 获取每条记录NAME的长度LENGTH()
  SELECT NAME,LENGTH(NAME) FROM STUDENT

---

修改语句UPDATE

语法

UPDATE table_name
SET column1 = value1, column2 = value2…, columnN = valueN
WHERE [condition];

实例

• 把ID为1的记录的AGE修改为22
  UPDATE STUDENT SET AGE = 22 WHERE ID = 1;
• 把所有记录的AGE修改为22
  UPDATE STUDENT SET AGE = 22;

---

删除语句DELETE

语法

DELETE FROM table_name
WHERE [condition];

实例

• 把ID为1的记录从表中删除
  DELETE FROM STUDENT WHERE ID = 1;
• 删除STUDENT表中的所有记录
  DELETE FROM STUDENT;

---

修改表 ALTER TABLE

语法

• 用来重命名已有的表的 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…;

实例

• 把表STUDENT的表名改为PERSON
  ALTER TABLE STUDENT RENAME TO PERSON;
• 为表STUDENT添加一个新的列
  ALTER TABLE STUDENT ADD COLUMN SEX CHAR(1);