sqllite和mysql语法,Android SQLite（一.简介和常用语法）

一.简介

SQLite是一个轻量级的数据库，实现了自给自足的，无服务器的，零配置的，事务性的SQL数据库引擎。

二.基本SQL语法

SQLite 不区分大小写，但有一些命令是大小写敏感的，例如：GLOB和glob

1.数据类型

每个存储在SQLite的值都属于以下存储类之一

存储类

说明

NULL

值是一个NULL值

INTEGER

值是一个带符号的整数，根据值的大小存储在1、2、3、4、6或8个字节中

REAL

值是一个浮点值，存储为8字节的IEEF浮点数

TEXT

值是一个文本字符串，使用数据库编码存储

BLOB

值是一个blob数据，完全根据它的输入存储

关于SQLite存储类型和数据类型可以参考这篇文章:SQLite剖析之数据类型

2.创建表和删除表

创建表

CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建新表，涉及到命名表，定义列及每一列的数据类型和名称

约束： 限定数据列的规则

NOT NULL ：确保列不能有NULL值

DEFAULT ：当某列没有指定值时，为该列提供默认值

UNIQUE ：确保某列中所有的值是不同的

PRIMARY KEY ：主键：唯一标识数据库表中的每一条记录

CHECK ：CHECK约束确保某列中的所有值满足一定条件

PRIMARY KEY (index_name1,index_name2...))：混合主键

(设计表的时候，约束条件一定要设计好，不支持删除约束和修改约束)

CRATE TABLE (

column1 datatype,

column2 datatype,

.....

columnN datatype);

实例，创建一个Student表，ID作为主键，自增

CREATE TABLE student (

id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL,

age INTEGER,

gender TEXT,

score INTEGER

);

如下:成功在当前数据库下创建一个名为student的表

删除表

DROP TABLE

索引

索引(Index)是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的

语法：

单例索引：CREATE INDEX index_name ON table_name (column_name);

唯一索引 : CREATE UNIQUE INDEX index_name on table_name (column_name);

混合索引 : CREATE INDEX index_name on table_name (column1, column2); (混合唯一索引可以达到和混合主键同样的效果，表创建后无法修改如果想使用混合主键可以使用混合唯一索引)

删除索引 : DROP INDEX index_name;

3.增删改查

增

INSERT INTO语句用于向数据库某个表中添加新的数据行

语法如下：

INSERT INTO (column1,column2,……columnN)

VALUES (value1,value2,……valueN);

//或者为表中所有列插入数据(顺序和表保持一致)

INSERT INTO

VALUES(value1,value2,……valueN);

实例：

INSERT INTO student (name, score, age, gender)

VALUES

("张三", 100, 20, "男");

结果如下：

删

DELETE 语句用于删除表中已有记录。可以使用WHRER 字句指定删除行，不然删除所有的。

DELETE

FROM

WHERE ;

实例：

有student表数据如下，现在删除所有年龄大于20的数据

DELETE

FROM

student

WHERE

age >20;

结果如下：删除了age大于20的数据

改

UPDATE 语句用于修改表中已有记录。可以使用WHRER 字句指定修改行，不然修改所有行

UPDATE

SET column1 = value1,column2 = value2,……columnN = valueN

WHERE ;

实例：

有student表数据如下，现在将所有学生分数小于90的男人变为女人(O(∩_∩)O)

UPDATE student

SET gender = "女"

WHERE score < 90;

结果如下：所有小于90分的都变成女人

查

SELECT 语句用于从表中获取数据，以结果表的形式返回数据。

SELECT column1,column2,……columnN

FROM

WHERE ;

//或者查询所有列的数据

SELECT * FROM

实例：

有student表数据如下，查询年龄在20到22之间且分数大于60的数据，并按照成绩升序排列

SELECT

name,

age,

score

FROM

student

WHERE

age BETWEEN 20

AND 22

AND score > 60

ORDER BY

score ASC

结果如下：

4 高级功能

高级功能很多了，篇幅有限，这里只说三个Android中经常会用到的修改表，多表查询和子查询

修改表

ALTER TABLE命令用来修改表。可以使用ALTER TABLE 命令来修改表名称和在已存在的表中添加额外的列，除此之外ALTER TABLE 命令不支持其他操作。

语法如下：

//修改表名

ALTER TABLE RENAME TO ;

//新增字段

ALTER TABLE ADD COLUMN culumanName dataType;

实列(改名很简单不举例了)：

已用表student，字段如下，先新增一个classId字段

ALTER TABLE student ADD COLUMN classId INTEGER;

结果如下：在student表中新增了classId字段列

多表查询

JOIN子句用于结合两个或多个数据库中表的记录。

交叉连接：CROSS JOIN

SELECT ... FROM table1 CROSS JOIN table2 ...;

内连接：INNER JOIN

SELECT ... FROM table1 INNER JOIN table2 ON condition;

如果多个表存在相同的字段，可以简化成如下语句

SELECT ... FROM table1 JOIN table2 USING(column1, ...);

NATURAL JOIN自动连接多表中的相同字段相等值合并成一行

SELECT ... FROM table1 NATURAL JOIN table2 ... ;

外连接：OUTER JOIN

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON condition;

如果多个表的条件字段相同，可以简化为如下语句

SELECT ... FROm table1 LEFT OUTER JOIN table2 USING(column1, ...);

实例：有如下两张表student和school，数据如下：

student表

school表

交叉连接：CROSS JOIN

交叉连接将所有连接的表内符合条件的行相乘，即如果是两个表，表1符合条件的右X行，表2符合条件的有Y行，最后的查询结果是X*Y行

如下：student表中classId=10001的有三行，school表没有条件返回所有3行，最后结果会是9行数据：

SELECT student.classId as class ,name,monitor FROM student CROSS JOIN school WHERE class=10001;

结果如下：

内部连接：INNER JOIN

内部连接根据匹配条件的连接词，筛选出匹配条件的行，然后将多表的内容合并为一行

如下：筛选出每个班的班长及其信息，从上面表数据可知一共只有三个班每个班只能有一共monitor，所以最终筛选出三行数据

SELECT

name,

age,

gender,

score,

stu.classId AS class,

schoolNum,

avgScore AS classAvgScore

FROM

student AS stu

INNER JOIN school AS sch ON stu.classId = sch.classId

AND stu.name = sch.monitor;

结果如下：

外部连接

外连接是内连接的扩展。SQLite只支持左外连接，可以简写成LEFT JOIN。

外连接的连接的方式和内连接相同，连接合并的方式也和内一样，区别是不能符合连接条件的行的也会合并一起，之所以叫左为连接就是以左边的表行为基准，无法连接的列会展示NULL。

举个栗子：

例1：上面的内连接的查询改成左外连接，已知完全符合连接条件的有三行数据，但是左表共有10行数据都会合并，但是剩下的7条数，左表没有的数据会展示成NULL

SELECT

name,

age,

gender,

score,

stu.classId AS class,

schoolNum,

avgScore AS classAvgScore

FROM

student AS stu

LEFT JOIN school AS sch ON stu.classId = sch.classId

AND stu.name = sch.monitor;

结果如下：

例2：school表插入一行，如下

然后将例1的查询语句左表换成school表，如下：

SELECT

name,

age,

gender,

score,

stu.classId AS class,

schoolNum,

avgScore AS classAvgScore

FROM

school AS sch

LEFT JOIN student AS stu ON stu.classId = sch.classId

AND stu.name = sch.monitor;

按照之前的结论，会根据左表产生4条数据，且第4条数据因为不匹配连接，坐标没有的字段都一个是NULL值。看下结果如下，和之前总结的结论一致：

子查询

子查询又叫嵌套查询，是将另一个SQLite查询内嵌入WHERE子句中的查询。

子查询返回的数据将被用在主查询中作为条件，以进一步限制要检索的数据。

规则：

子查询必须用括号括起来。

一般情况下子查询在SELECT字句中只能用有一个列，除非在主查询中有多列，与子查询所选的列进行比较。

OEDER BY 不能用在子查询中，但可以使用GROUP BY。

子查询返回多余一行，只能以多值运算符一起使用，如IN运算符。

BETWEEN运算符不能喝子查询一起使用，但可以在子查询中使用。

SELECT 语句中子查询所有

语法：

SELECT column1, ... columnN

FROM tableName

WHERE column1 condition

(SELECT column1, ... columnN

FROM tableName

WHERE ...)

示例：

已存在Student表，数据如下：

student表

查询大于平均分数的学生，SQL如下

SELECT * FROM student WHERE score > (SELECT avg(score) FROM student);

结果如下：

INSERT中使用子查询

语法：

INSERT INTO (column1, ... columnN)

SELECT *|column1, ... columnN FROM

WHERE ...;

示例：

现在有个name表，将上面的student表中name都插入到新表中，SQL如下

INSERT INTO name SELECT

name

FROM

student;

结果：

UPDATE中使用子查询

语法

UPDATE SET columnName = columnValue

WHERE

(SELECT

FROM

WHERE ...);

示例：将Student表中小于平均分数学生的classId都变为10004

SQL如下：

UPDATE student

SET classId = 10004

WHERE

score < (

SELECT

avg(score)

FROM

student

);

结果：

原表数据

改变后表数据

DELETE中使用

语法：

DELETE FROM

WHERE

(SELECT

FROM

WHERE ...);

示例：删除小于平均分数的数据

SQL

DELETE

FROM

student

WHERE

score < (

SELECT

avg(score)

FROM

student

);

结果：