1.復(fù)習(xí)SQL語句(結(jié)構(gòu)化查詢語言)

1.SQL語句分類

DDL數(shù)據(jù)定義語言
DML數(shù)據(jù)操作語言
DCL數(shù)據(jù)控制語言
DQL數(shù)據(jù)查詢語言

2.SQL語句

1)庫
create database aaa; //創(chuàng)建數(shù)據(jù)庫
drop database aaa; //刪除數(shù)據(jù)庫
use aaa; //切換使用數(shù)據(jù)庫
show databases; //顯示數(shù)據(jù)庫

2)表
create table abc( //創(chuàng)建表結(jié)構(gòu)
name text,
sex varchar(100),
age int(10)
);
drop table abc; //刪除表
desc abc; //顯示表結(jié)構(gòu)

3)數(shù)據(jù)
insert into abc(name,sex,age) values('abc','nan',20); //插入數(shù)據(jù)
update abc set age = 101 【where name = 'abc'】 //更改數(shù)據(jù)
delete from abc 【where name = 'abc'】 //刪除數(shù)據(jù)
select * from abc 【where age > 18】 //查詢數(shù)據(jù)

4）約束
主鍵： primary key
自增： auto_increment
非空： not null
唯一： unique

    create table abc(
        id  int(100) primary key auto_increment,    主鍵自增
        name varchar(100) not null unique,          非空唯一
        sex varchar(100),
        age int(10)
    );

5）查詢

一、基本查詢
select * from emp;
select empno,ename,sal from emp;
select distinct deptno from emp;
select sal*1.5 from emp;
select concat('$',sal) from emp;
select concat(sal,'RMB') from emp;
select ifnull(comm,0)+1000 from emp;
select sal as 獎金 from emp;

二、條件查詢
select * from emp where deptno = 20;
select * from emp where deptno != 20;
select * from emp where sal >=20000;
select * from emp where sal >=10000 and sal <=20000;
select * from emp where sal<=10000 or sal >=40000;
select * from emp where comm is null;
select * from emp where comm is not null;
select * from emp where sal between 20000 and 40000;
select * from emp where deptno in(10,30);

三、模糊查詢 某一個字符 %多個字符
select * from emp where ename like '張';
select * from emp where ename like '張%';
select * from emp where ename like '一';

四、排序
select * from emp order by sal asc;
select * from emp order by sal desc;

五、聚合函數(shù)
select max(sal) from emp;
select min(sal) from emp;
select count(ename) from emp;
select sum(sal) from emp;
select avg(sal) from emp;

六、分組
select deptno,count(ename) from emp group by deptno;

總結(jié)：
    select deptno,count(ename)
    from emp
    where sal >= 10000
    group by deptno
    order by deptno asc;

2.SQLliteDatabase

SQLiteDatabase的創(chuàng)建和實現(xiàn)的方法
SQLiteOpenHelper的使用
onCreate的調(diào)用機制，onUpgrade的調(diào)用機制；建庫、建表
增刪改查（使用sql語句方式）
增刪改查（使用系統(tǒng)方法）