选择表A中id等于B表中列名为 bid,并且满足bbid等于127306,127309和typeid等于1,和categoryid等于6
SELECT * FROM A (Id IN (SELECT bid
FROM B WHERE bbid in('127306','127309')
AND TypeId = '1')) AND (CategoryId = '6')
删除(两种方法)
for (int i = 0; i < count; i++) {
/*int id = ListToDel.get(i).getID();
mDatabase.delete("MyFavorites", "ContentID=? and theme=?",
new String[] { ListToDel.get(i).getID() + "" , String.valueOf(ThemeId)});*/
sb=new StringBuilder();
int id = ListToDel.get(i).getID();
sb.append("DELETE FROM MyFavorites WHERE ContentID=");
sb.append("'").append(id).append("'").append("and theme=")
.append("'").append(ThemeId).append("'");
String string = sb.toString();
Log.i("TAG", "sb="+sb);
mDatabase.execSQL(string);
在已有的表中插入一列
mDatabase.execSQL("ALTER TABLE MyFavorites ADD COLUMN theme text");
查看某个表是否存在
String isTableExist = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='MyFavoritesTheme'";
Cursor cursor = mDatabase.rawQuery(isTableExist, null);
cursor.moveToFirst();
if (cursor.getString(0).equals("0")) {}
SQL全称是:结构化查询语句 (StructuredQuery)
一:数据定义语言(DDL) 对表本身
primary key 主键 auto_increment 自动增加
varchar 可变的字符 float(10,2) 总共10位,小数点后是两位
not null 是不能为空
Create table name(id int(11) primary key auto_increment,
name varchar(100) not null,gender char(1),hiredate date,salary float(10,2),
job varchar,comm float(7,2));
删除表: drop table name;
二:数据操作语言(DML) 是针对表内的数据
//插入数据 前面是列名,value后面对应相应的值,逗号与引号必须是英文状态下的,列的属性为text时需要加单引号,如果为integer时不需要加引号
intsert into name(name,gender,hiredate) value(‘小郭’,‘男’,‘2015-01-02’)
//在已有的表中插入一列
在name表中插入tel列 列最多是11个是数字
alter table name add column tel int(11)
//查询 (从name表中查询所有的值)
select * from name
//更改 update 表名 set 列值=’值’ where 条件
update name set gender=‘nv’ where id=1;
//删除 delect from 表名 条件
delect from name where id=1
三:数据库查询语句(DRL)
查询tel列为空的
select tel from Guo WHERE tel is NULL
取别名 as可以不写
SELECT name as 姓名, age as 年龄 FROM Guo
select g.name as 姓名, g.age as 年龄 FROM Guo g;
select age=40 FROM Guo WHERE tel=16 and name='小郭'
//age列大于30的
SELECT age>30 from Guo
select * from Guo where age > 10
已郭结尾,开头的 用like 和 %
SELECT * from Guo where name LIKE '%郭'
SELECT * from Guo where name LIKE '%郭%'
SELECT * from Guo where name LIKE '郭%'
排序
按tel降序
select * from Guo ORDER BY tel DESC
升序
select *from Guo ORDER BY age ASC
多排序查询 当第一个相同时 按照第二个排
SELECT * FROM Guo ORDER BY tel DESC, age ASC
//按列分组
SELECT * FROM Guo GROUP BY age
分组完查总和
SELECT count(age), age FROM Guo GROUP BY age
SELECT count(age) 个数, age FROM Guo GROUP BY age HAVING age=23
//查总数
SELECT count(*) from Guo
//查指定的第几条
SELECT * from Guo LIMIT 3,4
//去除id列重复的值(重复的值只会得到一个)
SELECT DISTINCT id FROM Guo
数据库中的一些关键词用法(IN / ON / INNER JOIN / LEFT JOIN / RIGHT JOIN / DISTINCT)
IN常用在同一个表中的同一列 (filed是a,b,c的)
select * from tab where field in ('A', 'B', 'C')
等价于select * from tab where field = 'A' or field = 'B' or field = 'C'
ON常用在表连接
select * from jun LEFT JOIN ju ON jun.name =ju.name
INNER JOIN 是取出相同的字段(会以字段多的表为准,如ju表中出现name为张三的有2次,jun表中只有1次,查询出的结果jun表中会出现两次张三(虽然它只有一个,但是ju表中有两个))
select * from jun INNER JOIN ju ON jun.name =ju.name
LEFT JOIN 是左连接(已jun表为基准,显示出所有jun表中的数据,ju表中只会显示name列与jun表中相同的值,其余不显示)
select * from jun LEFT JOIN ju ON jun.name =ju.name
LEFT JOIN 是右连接
DISTINCT 去除想、列里面相同的值(取出ju表中name相同的列值)
SELECT DISTINCT name from ju