SQL SERVER 、SQLList、以及Android之sqlite总结

来源：互联网发布：上古卷轴5mod捏脸数据编辑：程序博客网时间：2024/06/16 02:05

1，数据库

1）概念

数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。

2）特点

①数据库数据特点

永久存储、有组织、可共享。

（数据的最小存取单位是数据项）

②数据库系统的特点

数据结构化

数据的共享性，冗余度，易扩充

数据独立性高

逻辑数据独立性（logical data independence)是指概念模式改变，外模式和应用程序不变。

在逻辑数据独立性里，数据的逻辑结构发生改变或存储关系的选择发生改变时用户不会受到影响。改变概念模式，例如增加和删除实体、增加和删除属性、增加和删除联系，不需要改变现有的外模式或重写应用程序。在DBMS中只需要修改视图的定义和映像来支持逻辑数据独立性。对用户来说，不再关心所做的修改是非常重要的。换句话说，模式经过逻辑重构之后，根据外模式构建的应用程序还是和从前一样工作。

3）数据库连接

java应用程序可以通过JDBC或Hibernate对数据库系统进行访问。JDBC或Hibernate提供了事务控制的接口，这些接口把事务控制相关的命令发送给数据库系统，由数据库系统来控制事务的隔离级别。

①Hibernate

Hibernate是一个开放源代码的对象关系映射框架，它对JDBC进行了非常轻量级的对象封装，使java程序员可以随心所欲地使用对象编程思想来操纵数据库。

②JDBC（Java Data Base Connectivity,java数据库连接）

是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。

config.properties配置文件

mysql连接

jdbc.driverClassName=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/hadoop?useUnicode=true&characterEncoding=utf8

jdbc.username=root

jdbc.password=root

//hadoop为数据库名。

driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

url=jdbc\:sqlserver\://202.200.102.1\:6666;databaseName\=IMAGE_VIDEO_DEMO;

username=sa

password=123456

③事务隔离级别

事务隔离级别由数据库系统实现。

在标准SQL规范中，定义了4个事务隔离级别。

①未授权读取（读未提交read uncommitted）

②授权读取（读提交read committed）

③可重复读取（repeatable read）

④序列化（serializable）

4）数据库安全–SQL注入

SQL执行时，2种方式：

①字符串处理（拼接），然后执行SQL

用户输入的时候，可以通过输入sql语句来进行SQL注入。

②传参，执行SQL –>交给SQL引擎（推荐）

5）范式

各个范式联系：

5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF

①1NF（满足最低要求的范式）

如果一个关系模式R的所以属性都是不可分的基本数据项，则R∈1NF

②2NF

若R∈1NF，且每一个非主属性完全函数依赖于码，则R∈2NF

③3NF

若R∈3NF，则每一个非主属性既不部分依赖于码，也不传递依赖于码。

④BCNF(修正第三范式、扩充第三范式）

所有非主属性对每一个码都是完全函数依赖；

所有主属性对每一个不包含它的码，也是完全函数依赖；

没有任何属性完全函数依赖于非码的任何一组属性。

⑤4NF

关系模式R

6）数据类型

①uniqueidentifier

可存储16字节的二进制值，其作用与全局唯一标记符（GUID）一样。GUID是唯一的二进制数：世界上的任何两台计算机都不会生成重复的GUID值。GUID主要用于在用于多个节点，多台计算机的网络中，分配必须具有唯一性的标识符。

SQL Server

7）函数

①OBJECT_ID

A. 返回指定对象的对象 ID

USE master;

SELECT OBJECT_ID(N'AdventureWorks.Production.WorkOrder') AS 'Object ID';

B. 验证对象是否存在

USE AdventureWorks;

IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL

DROP TABLE dbo.AWBuildVersion;

N是显式的将非unicode字符转成unicode字符，它来自 SQL-92 标准中的 National（Unicode）数据类型，用于扩展和标准化，在这里可以不用，写作object_id(PerPersonData)。

8）数据库命名

①加中括号

列名、表名、存储过程名、函数名等都可以按需要加中括号。防止某些关键字在应用中引起歧义。

select [select] from 表名;

9）锁

①共享锁（S锁、读锁）

（读取）操作创建的锁。其他用户可以并发读取数据，但任何事物都不能获取数据上的排它锁，直到已释放所有共享锁。

若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

②排它锁（X锁、写锁，eXclusive lock）

若事物T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。

2，SQL

1）基本对象

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有表、视图和索引。

①模式（schema）

A.概念

用于区分一个大项目中的各个小项目，这样若有相同名字的表的话, 不同模式不会发生冲突。相当于编程时的命名空间。

如：

一个公司的系统，分2个子系统，分别为财务系统和人力资源系统.

这2个子系统, 共用一个数据库。

那么财务系统的表, 可以放在财务的模式（schema）.

人力资源系统的表，放在人力资源系统的模式里面。

这2个子系统，能够互相访问对方的表。

但是又不因为表重名的问题，影响对方。

B.访问

访问具体的一个表，可以由 4个部分组成

分别为服务器名，数据库名，模式名，表名。

对于访问本地的数据库：

不指定模式名的话，数据库默认使用dbo模式。

（DBO是每个数据库的默认用户，具有所有者权限，即DbOwner ）

②表

表分为临时表和永久表。

临时表

临时表存储在tempdb中（如下），当不再使用时会自动删除。

IF OBJECT_ID('tempdb..#ownerAnnouce') IS NOT NULL

根据进程独立，只有进程的拥有者有表的访问权限，其它用户不能访问该表；

不同的用户进程，创建的临时表虽然“名字”相同，但是这些表之间相互并不存在任何关系；在SQLSERVER中，通过特别的命名机制保证临时表的进程独立性。

临时表有两种类型：本地和全局。

A.本地临时表

名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。

B.全局临时表

名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

临时表优点

真正的临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的I/O次数，因此也提高了系统效率。

临时表的创建

A. create table #临时表名

B.select * into #临时表名 from 表名（永久表或临时表）

③视图

A.概念

视图是一张虚拟表，视图的字段是自定义的，视图只支持查询，查询数据来源于实体表。

B.优点

视图可以将多个复杂关联表提取信息，优化查询速度。

④索引

建立索引是加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。

缺点：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

优点：

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

2）数据查询

①查询条件

等于：=

不等于：<>或!=

不大于：!>

空值：is null，is not null

确定集合：in，not in

确定范围：between and,not between and

字符匹配：like,not like

%代表任意长度（可为0）的字符串；

_（下划线）：代表任意单个字符。（汉字代表2个字符，所以一个汉字用两个下划线）

\为转义字符

②多重排序查询：

先按字段5排序，再按字段6排序

默认为asc：升序排列。desc：降序排序。

order by 字段5,字段6 asc

③联表查询

select a.title,b.type_name

from news_table as a,name_table as b

where a.type_id=b.type_id ;

select 字段1,字段2,字段3,字段4 from 表

④case when then查询

--简单case函数

case sex

when '1' then '男'

when '2' then '女’

else '其他' end

--case搜索函数

case when sex = '1' then '男'

when sex = '2' then '女'

else '其他' end

应用：

select (case sex

when '1' then '男'

when '2' then '女’

else '其他' end)sex from student where class = 11;

⑤其它

use test

select * from student

use test1 /*表示使用什么数据库*/

set ansi_nulls on

set quoted_identifier on

select top 100 * from student where no=11;/*显示前100行*/

select name,age from student order by age desc;/*desc倒序 asc表示正序*/

select isnull(name,'无') as name,age,class from student;/*isnull之后就无列名了用as给列重命名*/

select name,age,class,'the name is' + name as introduce from student;/*用加号形成一个自定义列*/

select * from student where name like '%丽%';/*like模糊查询*/

select * from student where age in(21,23);/*找出所有年龄为21、23的人*/

select * from student where age not in(21,23);

select * from student where class is not null;

select * from student where class is not null or age in(21,23);

select * from student where class is not null and age in(21,23);

select count(class)from student;/*数量因为使用了92标准，所以null不计入count*/

select distinct(class)from student;/*去重复，出现所有不同的内容*/

select count(distinct(class)) from student;

select avg(age) from student;/*平均数*/

select min(age) from student;

select max(age) from student;

select sum(age) from student;/*和*/

select class,avg(age) as age from student group by class order by age desc;/*对每个班级求年龄平均值*/

select class,avg(age) as age from student

group by class

having avg(age)>23 /*要求平均年龄大于23*/

LEFT（“123456789”，LEN（“数据库”））/*分两步运算，第一步是运算LEN函数，结果是3。第二步针对123456789这个字符从左边开始连续取三个数

###⑤SQL-92 规则

是数据库的一个标准。以下代码写在存储过程前面，表示遵从SQL-92 规则。

SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<) 比较时取值为 FALSE。

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。

即使 column_name 中包含非空值，使用 WHERE column_name < NULL 的 SELECT 语句仍会返回零行。

SET QUOTED_IDENTIFIER ON

为ON：标识符可以由双引号分隔，而文字必须由单引号分隔。

为OFF：标识符不可加引号。

3）数据更新

数据插入

insert into tableName(no,name) values（'1','kate');

数据修改

update tableName set name = 'Tom' where name='kate';

update tableName set age = age + 1;

数据删除：

删除表中几行：

DELETE FROM Person WHERE LastName = 'Wilson'

删除表中所有行：

DELETE FROM table_name

3，数据库完整性

1）实体完整性

主键唯一且不为空。

2）参照完整性

不允许修改外码

级连操作：当删除或修改被参照表时，同时删除或修改参照表中的不一致元祖。

3）用户定义的完整性

4）触发器（Trigger）

是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对标的增删改操作均由服务器自动激活相应触发器，在DBMS核心层进行集中的完整性控制。

4，存储过程（Stored Procedure）

1）概念

存储过程是一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

2）优点

①执行效率高

存储过程不像解释执行的SQL语句那样在提出操作请求时才进行语法分析和优化工作，它提供了服务器端快速执行SQL语句的有效途径。

②降低了客户机和服务器之间的通信录

客户机上的应用程序只要通过网络向服务器发出存储过程的名字和参数，就可以让RDBMS(关系数据库管理系统 Relational Database Management System)执行许多条的SQL语句，并执行数据处理，只有最终处理结果才返回客户端。

③方便实施企业规则

可以把企业规则的运算程序写成存储过程放入数据库服务器中，由RDBMS管理，既有利于集中控制，又能够方便地进行维护。

当用户规则发生变化时，只要修改存储过程，无须修改其他应用程序。

④安全性高

可设定只有某些用户才具有对指定存储过程的使用权。

3）好的习惯

sql尽量放在存储过程中。

面对大量数据，用orcle比sql server稳定。

4）场景

当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；

当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。

5）代码

①创建

use test1

set ansi_nulls on

set quoted_identifier on

create procedure procedure_student

-- add the parameters for the stored procedure here

@gradeid int,

@gradename varchar(10) --传入的参数

begin

--计算内容

end

②执行

exec dbo.procedure_student 1,'g'

5，mysql命令行：

##1)查看数据库：

show databases;

2)建库：

create database children;

3)调用数据库

use children;

4)建表

主键、非空。

create table product

(

no char(10) primary key,

code char(10) not null unique,

online char(1),

bind char(1)

);

外键：

foreign key(no) references product(no)

5)更新

update near_log set lnt=99 where no=001;

6)插入

insert into product values('001','001','N','N');

7)删除

delete from all_log where lnt=34.342342;

delete from all_log;

drop table user;

6，android之sqlite

##1）概念

sqlite是一款轻量级数据库，是遵守ACID（指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability））的关系型数据库管理系统。在嵌入式设备中，可能只需要几百K的内存就够了。

SQLite默认是打开事务的。

2）安全性

android手机root之后，就可以在/data/data/包名/database/目录中查看sqlite数据库。所以，对于敏感信息，应该考虑加密。

SQLite不支持加密，所以我们针对数据库文件加密。现有两种解决方案：

i>对数据库中的数据进行加密

①优点：

加密速度快。

程序无须变动，只针对数据进行加密。

②缺点：

来回加密，造成处理数据缓慢。

仅对数据加密，依然可以看到数据表的sql语句，可能猜测到表的作用。

③实现：

一是：对明文数据进行加密返回密文数据

二是：对密文数据进行解密返回明文数据

ii>对数据库文件进行加密

①优点

对整个文件进行了加密，用户通过编辑器看不到任何有用的数据。

进行数据库打开时，使用程序即可解密文件。

②缺点

需要修改sqlite源码，这个工作难度比较大。

③实现（太难，本人并没有进行实验）

a.修改sqlite源代码，追加对数据库文件进行加密的功能。

b.编译含有加密功能的程序源代码，生成各自平台需要使用的库文件。

c.将加密sqlite库文件引入各自平台中，修改数据库访问层代码。

d.进行程序的部署，测试。

3）demo

i>在AndroidManifest.xml中添加权限：

<uses-permission android:name= />

<uses-permission android:name= />

ii>添加继承SQLiteOpenHelper的MyHeper类，用于创建使用数据库。

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

public class MyHelper extends SQLiteOpenHelper {

private String Tag = ;

private static String DB_NAME = ; //数据库名称

public static String TABLE_NAME_TASK = ; //联班任务_fra_task表名

/**super(参数1，参数2，参数3，参数4)，其中参数4是代表数据库的版本，

* 是一个大于等于1的整数，如果要修改（添加字段）表中的字段，则设置

* 一个比当前的参数4大的整数，把更新的语句写在onUpgrade(),下一次

* 调用

public MyHelper(Context context) {

super(context, DB_NAME, null, 1);

}

@Override

public void onCreate(SQLiteDatabase db) {

//Create table

try{

String sql = +TABLE_NAME_TASK +

+ ;

db.execSQL(sql); //创建表

}catch(Exception e){

LogUtil.i(Tag, + e.getMessage());

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

iii>创建DatabaseUtil进行数据库操作。

import java.util.ArrayList;

import java.util.List;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.text.style.TtsSpan.DateBuilder;

/**

* task数据操作。 ①存入数据库：每次getList的时候调用。每次删除所有数据，插入所有内容到数据库。

* ②删除内容：每次存入数据库的时候调用。删除所有数据。private。 ③更新：没有此功能。 ④取数据：两种情况下调用：

* -----i>切换任务、保障等Tab且未收到服务器让重新getList的命令。（所以只有第一次登录才需要第一次进入taskFragment时获取数据）

* -----ii>切换已降落（有实落）、未降落Tab. 另外，取到的数据为列表返回。

* @author luo

public class DatabaseUtil {

private String Tag = ;

private MyHelper helper;

public DatabaseUtil(Context context) {

super();

try {

context.deleteDatabase();

helper = new MyHelper(context);

} catch (Exception e) {

LogUtil.e(Tag, + e.getMessage());

}

/**

* 插入数据插入数据之前，删除所有数据

* @param taskList

* @return

public boolean Insert(List<Task> taskList) {

SQLiteDatabase db = helper.getWritableDatabase();

try {

DeleteALL(db);

for (Task task : taskList) {

String sql = + MyHelper.TABLE_NAME_TASK

+ + + task.TaskCode

+ + + task.TaskName + + + task.TaskState + + ;

db.execSQL(sql);

}

return true;

} catch (SQLException e) {

LogUtil.i(Tag, + e.getMessage());

return false;

} finally {

db.close();

}

/**

* 删除所有数据

private void DeleteALL(SQLiteDatabase db) {

try {

db.execSQL( + MyHelper.TABLE_NAME_TASK);

} catch (Exception e) {

LogUtil.i(Tag, + e.getMessage());

}

/**

* 查询所有数据

public List<Task> queryAll() {

List<Task> list = new ArrayList<Task>();

try {

SQLiteDatabase db = helper.getReadableDatabase();

Cursor cursor = db.query(MyHelper.TABLE_NAME_TASK, null, null, null, null, null, null);

while (cursor.moveToNext()) {

Task task = new Task();

task.TaskCode = cursor.getString(cursor.getColumnIndex());

task.TaskName = cursor.getString(cursor.getColumnIndex());

task.TaskState = cursor.getString(cursor.getColumnIndex());

list.add(task);

}

db.close();

} catch (Exception e) {

LogUtil.i(Tag, + e.getMessage());

}

return list;

}

public List<Task> queryByThisActualLanding(Boolean actual) {

SQLiteDatabase db = helper.getReadableDatabase();

List<Task> list = new ArrayList<Task>();

Cursor cursor = null;

/**

* public Cursor query (String table, String[] columns, String

* selection, String[] selectionArgs,String groupBy, String

* having,String orderBy,String limit)参数如下：

* table：表名。相当于select语句from关键字后面的部分。如果是多表联合查询，可以用逗号将两个表名分开。

* columns：要查询出来的列名。相当于select语句select关键字后面的部分。

* selection：查询条件子句，相当于select语句where关键字后面的部分，在条件子句允许使用占位符“?”

* selectionArgs：对应于selection语句中占位符的值，值在数组中的位置与占位符在语句中的位置必须一致，否则就会有异常。

* |------在 selection 中需要嵌入字符串的地方用 ? 代替，然后在 selectionArgs

* 中依次提供各个用于替换的值就可以了. groupBy：相当于select语句group by关键字后面的部分

* having：相当于select语句having关键字后面的部分 orderBy：相当于select语句order

* by关键字后面的部分，如：personid desc, age asc升序;

* limit：指定偏移量和获取的记录数，相当于select语句limit关键字后面的部分。

cursor = db.query(MyHelper.TABLE_NAME_TASK, new String[] { , , },

, null, null, null, );

// Cursor cursor = db.query(table, columns, selection, selectionArgs,

// groupBy, having, orderBy)

while (cursor.moveToNext()) {

Task task = new Task();

task.TaskCode = cursor.getString(cursor.getColumnIndex());

task.TaskName = cursor.getString(cursor.getColumnIndex());

task.TaskState = cursor.getString(cursor.getColumnIndex());

list.add(task);

}

db.close();

return list;

}

4）其它

①多条件查询：

and连接。

②模糊查询：

i>使用这种query方法%号前不能加’

cursor = db.query(MyHelper.TABLE_NAME_TASK,

new String[] { ,}, ,

new String[]{ + TaskName + }, null, null, null);

ii>使用这种query方法%号前必须加’

Cursor c_test = mDatabase.query(tab_name,

new String[]{tab_field02},

tab_field02+ + str[0] + , null, null, null, null);

iii>使用这种方式必须在%号前加’

String current_sql_sel = "SELECT * FROM where like '%"+str[0]+"%'

多重排序，字符串排序

cursor = db.query(MyHelper.TABLE_NAME_TASK,

new String[] { , ,}, ,

new String[]{ + TaskName+ }, null, null,

+ + TaskState.STATE2 +

+ + TaskState.STATE1 +

+ );

③SQLiteOpenHelper

通过getWriteableDatabase和getReadableDatebase()方法来对数据库版本进行管理。

阅读全文

0 0