Android进阶Android StudioAndroid开发

SQL总结

2017-08-31  本文已影响86人  慕涵盛华

目录

简介

在Android中存储数据有时会用到数据库，Android给我们提供了 一系列的API来操作数据库，非常简单，我们只需要输入对应的SQL语句，甚至不懂SQL语句，只传入对应的参数即可使用。还有一些第三方库，如GreenDao，OrmLite等都极大的简化了对数据库的一些操作。这样虽然我们不需要对数据库有多了解一样能实现功能，但是在面对复杂操作时，对SQL语句的熟练使用就显得尤为重要了。因为在Android我们只要是利用SQL语句对表操作，所以本文主要介绍SQL的使用，只是简单介绍了数据库一些相关的概念，这样能有更加清晰的认识。

数据模型

按照计算机系统的观点对数据进行建模。

概念模型

也称为信息模型，即按照用户的观点来对数据和信息建模

现实世界的实体进入数据库

键的概念

例子说明： 员工（身份证号、姓名、年龄、性别）假设姓名也是唯一的超键：身份证号、姓名、姓名+年龄、姓名+性别、身份证号+年龄、身份证号+性别候选键：身份证号、姓名

数据库设计范式

数据库设计范式简单的说就是关系数据库在设计时需要遵循的一种规范，数据库范式按照要求从低到高分为6大范式，即第一范式（1NF）~第六范式（6NF）。在第一范式的基础上进一步满足更多要求的称为第二范式，一次类推。越高的范式数据库冗余越小，一般来说，数据库只需要满足第三范式（3NF）就行了。

第一范式

是数据库设计的最基本的要求，不满足1NF的数据库不是关系型数据库
所谓第一范式就是指数据库表的每一列都是不可分割的基本数据项，同一列中只能有一个属性（也就是说一个属性下不能再分出其他的属性来）

第二范式

第二范式是在第一范式的基础上建立起来的，即满足2NF必须先满足1NF
只出现在复合主键的数据库表中
第二范式要求数据库表中的每个实例或者行必须可以被唯一地区分，所以实体必须设置主键，并且实体的属性必须完全依赖于主键，不得出现非主键属性部分依赖于主键的情况。

主键为学号+课程名称，而学分依赖于学号，这就是所谓的非主键属性依赖于主键的情况，这是不符合2NF的，出现了数据的冗余（存储多余的数据，浪费空间），解决办法就是拆成3张表。

第三范式

要求一个数据库表中不包含已在其他表中已包含的非主关键字信息（非主键属性不能出现在第二张表中）

非主键属性重复了，完全可以根据编号（主键）去查询部门简介和名称

数据库设计的完整性约束

实体完整性约束

每个实例或者行的主键都不能为空

参照完整性约束

外键可以为空值;当外键不为空时，其取值只能等于参照的主键的某个值

数据库的约束

约束是表级强制执行的规则，当表中数据有相互依赖性时，可以保护数据不被删除

约束的类型：

oracle有如下类型的约束：

not null：非空
primary key：主键约束
foreign key：外键约束
check：检查约束
unique key：唯一性约束

not null(非空约束)

create table student(
     name varchar2(10), 
     sex varchar(5) not null, 
     age integer)

primary key(主键约束)

表级方式定义主键

create table student（ 
    name varchar2（19），
    sex varchar2（10），
    age integer，
    constraint student_age_pk primary key（age）
    ）；

把age定义为主键（student_age_pk:约束的名字有一定的规范：表名主键名约束类型）

列级方式定义主键

create table student（ 
   name varchar2（19） primary key，
   sex varchar2（10），
   age integer）;

foreign key（外键约束）

create table emp（
   empno number(4), 
   ename varchar2(10) not null, 
   job varchar2(9),
   deptno number(7,2) not null, 
   constraint emp_deptno_fk foreign key(deptno) references dept (deptn)
   );

--references dept (dept）：外键所对应的主键所在的表 关键字 主表 主键

SQL语句

常用数据类型

  • varchar2（size）：可变长度的字符串，最大长度为size个字节，size最大值为4000，最小值为1
  • char（size）：固定长度的字符数据，其长度为size个字节，最大值为2000，最小值和默认值为1，不管实际的长度为多少都会分配指定的size个字节
  • number(p,s)：有效位数为p且精度为s的数值（小数点后有s位）p的取值范围为1到38
  • date：有效日期范围从公元前4712年1月到公元后4712年12月31日
  • long：可变长度的字符数据，其长度可达2G个字节

查询

子查询

应用场景：例如那些雇员的工资比林志玲的高？

单行子查询

相关子查询

多行子查询

使用IN：

select empno，ename，job from emp where deptno in（select deptno from emp where ename = 'SMITH' or ename ='MITLER'）;

使用ANY

使用ALL

select empno，ename，job，sal from emp where sal > ALL(select avg(sal) from emp GROUP BY depton)

SQL语句

select 列名 form 表名select name from student
select * from student ：查询所有的列
select name，sex from student

order by

select name，sex，age from student order by age asc：按照age进行降序排列
select name，sex，age，sal from student order by age asc，sal desc：按照age降序排列，如果相等则在按照sal升序排列

单行函数

字符函数

数值处理函数

若x<0，返回-1;若x=0，返回0；若x>0，返回1

日期处理函数

Month 1-15日算上一个月，15日后算下一个月
Year 1-6月算上一年，7-12月算下一年

select round(date'2014-7-16','month') from dual;--返回2014-8-1 select round(date'2014-7-16','year') from dual;--返回结果为2015-1-1

类型转换函数

数据类型转换包含隐士类型转换和显示类型转换
隐士类型转换：系统自动转换
显示类型转换：调用相应的函数转换

TO_CHAR：字符串转换（其他类型转换为字符串）
TO_NUMBER：数值转换
TO_DATE：日期转换

将日期转换为字符串格式
必须用单引号括起来，大小写不敏感
有一个fm元素，用于填补空格或者禁止前面的零
使用逗号分离日期
TO_CHAR（date，'fmt'）
常用的日期格式：
YYYY：四位数表示的年
YEAR：拼写出的年
MM：两位数字的月
MONTH：全月明（例如：sepember）
DD：两位数字表示的月
DAY：全天名

select name,TO_CHAR(hiredate,'fmDD Month YYYY') HIREDATE from emp

通用函数

case表达式：（选择表达式）

Case国际sql通用支持的，使用case可移植更好。相当于在SQL中执行if语句 CASE 可用于允许使用有效表达式的任意语句或子句。 例如，可以在 SELECT、UPDATE、DELETE 和 SET 等语句以及 select_list、IN、WHERE、ORDER BY 和 HAVING 等子句中使用 CASE。

select e.salary,
case e.type_id
   when 1 then e.salary*2 
   when 2 then e.salary*3
   when 3 then e.salary*4 
    eles e.salary*5
end 
as new_salary from employee e;

说明：当typeid为1时，薪水2，当typeid为2时，薪水3，当type_id为3时，薪水4，其他情况下薪水5；

组（聚合）函数

语法的执行顺序

1.select 2.from 3.where 4.group by 5.组函数 6.having 7.order by

说明：having与where功能一样，就是执行的顺序不一样eg：select e.division_id,avg(e.salary) from employees2 e group by e.division_id where avg(e.salary)>150000;--会报错的，因为where比组函数（avg）先执行，在执行where的时候，avg（e.salary）还没有算出来，所以会出错，换成having就没事了。

插入的名字为'name insert into student(name,sex) values(&Name,'nan');
&:变量，在执行该语句时，会让你输入name要插入的值（Name变量名自己起的） 一次插入多条语句，要查入得值从另一个表中取数据 insert into student（name，sex，age） select name，sex，age from student2 where id>10;

事务

一组sql语句一块执行，要么一起成功，要么一起失败 以commit开始

锁：保证数据库的一致性（同步）：自添加锁

T1执行 update students set name='gfd' where id=3; 如果T2想修改id=3的数据，必须等T1提交后才能修改（也就是说提交后自动释放锁）

死锁

T1 update students set name='gfd' where id=3;
update students set name='gfd' where id=4; T2 update students set name='gfd' where id=4; 
update students set name='gfd' where id=3;

说明：在T1，T2执行完第一条语句时，再执行下一条语句时，都被各彼此锁着，所以都不能执行下去了。 就出现了死锁的状态，oracle数据库会自动解锁一条语句

DDL语句（数据定义语言）

对表的操作可以通过DDL语句进行，包括：

alert table 表名 add (增加的列名 类型，。。。。。)；--增加列
alert table 表名 modify （修改的列名 类型）；--修改列
alert table 表名 rename column 要修改的列名 to 新的列名；--修改列名
alter table 表名 rename to 新的表名；--修改表名
alert table 表名 drop column 要删除的列名；--删除列
alert table dept30 add （job varchar2（9））；
alert table dept30 modify （job varchar2（15））；
alert table dept30 raname column deptname to dname；alert table dept30 drop column job；

表中的所有数据将被删除
事物被自动提交
所有的索引被删除
不能回退
彻底删除表
drop table 表名；--删除指定的表

直接删除全部的记录，无法指定删除条件
无法回退
只会删除数据，会保留表的结构（字段），可以再次插入数据
速度快于delect
truncate table 表名；--删除指定的表

约束条件

索引

使用索引大大加快查询的速度
对数据量大的，经常使用的表才去创建索引(需要维护的)
查询的时候与正常的一样

create index 索引名 on 表名(字段名。。。。);--创建索引delete index 索引名；--删除索引

视图

可以隐藏一些信息
虚拟的表（不真实存在，基表的数据删除时，视图中的数据也会删除）
可以进行增删改查（对视图表中的数据改变时，基表的数据也会改变）
只要视图中能看到的，基表中肯定能看到

create or replace view ch_view as select * from products p where p.price<15;--创建视图
select * from ch_view;--查询视图insert into ch_view values(14,3,'fff','ggg',12);--插入数据

添加注释

comment on table 表名 is ‘注释’；--表添加注释
comment on column 表名.字段名 is ‘注释’；--字段添加注释

多表联合查询

没有指定连接条件的多表查询将造成笛卡尔积的结果
笛卡尔积结果：两个表中所有数据的集合都会查询出来

笛卡尔积

多表连接查询中的连接类型

内连接：返回符合特定连接条件的查询记录
select * from products p natural inner join purchases pt;--自动按着相等的去连接，不用加条件了 在emp中每一个员工都有自己的mgr（经理），并且每一个经理自身也是公司的员工，自身也有自己的mgr。我们需要将每一个员工自己的名字和经理的名字都找出来。
select worker.ename,'work for',manager.ename from emp worker,emp managetr where worker.mgr = manager.empne work for:连接字符串
外连接：不仅返回符合连接条件的记录，也返回指定表中的不符合连接条件的记录

在emp中每一个员工都有自己的mgr（经理），并且每一个经理自身也是公司的员工，自身也有自己的mgr。我们需要将每一个员工自己的名字和经理的名字都找出来。

  select worker.ename,'work for',manager.ename from emp worker,emp managetr 
  where worker.mgr = manager.empnework for:连接字符串

用SQL语句列出EMP表中所有部门的详细信息以及对应的部门人数

select a.* , b.* from dept a,(select deptno,count(*) from emp group by deptno) b where a.deptno = b.deptno(+)

用SQL语句列出NEWEMP表和NEWDEPT表中所有部门和员工的详细信息:

select * from newdept full outer join newemp on newdept.deptid = newemp.deptid

集合

集合中的元素可以把一条记录或者一个字段当做一个元素
用了集合再排序的时候，order by 字段的位置（例如 1）：只能用数字了

经典例子

根据时间统计胜负数
执行sql语句变成为：
select
 t_date, 
 (select count(*) from t where t_status='胜' and t_date=e.t_date) 胜, 
 (select count(*) from t where t_status='负' and t_date=e.t_date) 负
from t e group by t_date order by e.t_date;
两张表中的数据运算
select kc.mc "种类",kc.s1-(select sum(s1) from ck where ck.mc = kc.mc ) "剩余量" from KC;
一次修改多条记录
update employees e set e.salary = 
case e.employee_id
  when 1 then 5555
  when 2 then 6666
end
where e.employee_id<3;

多表联查

购买数量超过一个的产品名，顾客名以及购买的数量（3张表）
select * from products;
select * from customers;
select * from purchases;

四表联查

有如下几张表:

部门表：organization（orgid ，orgName）
商品表：Goods（goodsid，goodsName）
销售单（单头）：sale（billid，billdt，orgid，status）
销售单（单体）：sale_item（billid，goodsid，qty）

orgid：组织编码；
orgName：组织名称；
billid：单据编号
billdt：订单日期

查询“销售一部”本月的商品销售明细（商品编码。商品名称，销售日期，销售数量）

select * from organization o,goods g,sale s,sale_item si 
where 
o.orgid=s.orgid and g.goodsid=si.goodsid ands.billid=si.billid and orgName="销售一部" and billdt>trunc（sysdate） and billdt < last_day(sysdate);

关注微信公众号获取更多相关资源

Android小先生
上一篇 下一篇

猜你喜欢

热点阅读