软件后台的数据库管理与操作

软件后台的数据库管理与操作

 

 

*********************************************************************

二、3个常用的大数据库系统

1. ORACLE

是一种适用于大型、中型和微型计算机的关系数据库管理系统,

    它使用SQL(Structured guery language)作为它的数据库语言。

    SQL主要包括数据定义、数据操纵（包括查询）和数据控制等三方面功能。

    SQL是一种非过程化程度很高的语言，用户只需说明"干什么"而无需具体说明"怎么干"语言简洁、使用方便功能强大，

    集联机交互与嵌入于一体，能适应广泛的使用环境。ORACLE数据库由三种类型的文件组成：数据库文件、日志文件和控制文件。

    数据字典是由ORACLE自动建立并更新的一组表，这些表中记录用户的姓名、描述表和视图以及有关用户权限等的信息。

    数据字典是只读的，只允许查询。也就是说数据字典是一种数据库资源，每个用户都可以访问数据字典，

    DBA可通过数据字典来监视ORACLE RDBMS的使用，并帮助用户完成其应用。

    ORACLE RDBMS本身也要利用数据库字典来管理和控制整个数据库。

2. SYBASE

是美国SYBASE公司在80年代中推出的客户机/服务器结构的关系数据库系统，也是世界上第一个真正的基于client/server结构的RDBMS产品。

    SYBASE中按权限由高到低，可将用户分为四种不同的类型，

    由高到低分别为：系统管理员、数据库属主、数据库对象属主和其它一般用户。

    当第一次安装SYBASE SQL Server时，自动建立系统管理员帐户。

    系统管理员具有整个系统的最高权利，同时被赋予系统管理角色、系统安全员角色和操作员角色，

    有权执行所有SQL命令，也是系统数据库master的属主，可访问所有数据库和数据库对象。

3.SQL Server

  T－SQL是结构化查询语言SQL的一种，支持ANSI SQL－92标准。

SQL Server 采用二级安全验证、登录验证及数据库用户帐号和角色的许可验证。

SQL Server 支持两种身份验证模式：Windows NT身份验证和SQL Server 身份验证。      SQL Server为公共的管理功能提供了预定义的服务器和数据库角色，可以很容易为某一特定用户授予一组选择好的许可权限。 　　

     SQL Server可以在不同的操作平台上运行，支持多种不同类型的网络协议如TCP/IP、IPX/SPX、Apple Talk等。SQL Server在服务器端的软件运行平台是Windows NT、Windows9x，

*********************************************************************

三、数据库SQL知识

数据库中的数据从整体来看是有结构的，即所谓数据的结构化。

3.1 数据模型三要素是数据结构、数据操作、数据的约束条件

(1)数据结构：是所研究的对象类型的集合，是对系统静态特性的描述。
(2)数据操作：对数据库中各种对象(型)的实例(值)允许执行的操作的集合，操作及操作规则。如操作有检索、插入、删除、修改，操作规则有优先级别等。数据操作对系统动态特性的描述 。
(3)数据的约束条件：是一组完整性规则的集合。也就是说，对于具体的应用娄必须遵循特定的语义约束条件，以保证数据的正确、有效和相容

3.2 数据模型

按不同的应用层次分成三种类型：分别是概念数据模型、逻辑数据模型、物理数据模型.

(1)概念数据模型（Conceptual Data Model）：简称概念模型，是面向数据库用户的实现世界的模型，主要用来描述世界的概念化结构，它使数据库的设计人员在设计的初始阶段，摆脱计算机系统及DBMS的具体技术问题，集中精力分析数据以及数据之间的联系等，与具体的数据管理系统（Database Management System，简称DBMS）无关。概念数据模型必须换成逻辑数据模型，才能在DBMS中实现

(2) 逻辑数据模型（Logical Data Model）:简称数据模型，这是用户从数据库所看到的模型，是具体的DBMS所支持的数据模型，如网状数据模型(Network Data Model)、层次数据模型(Hierarchical Data Model)等等。此模型既要面向用户，又要面向系统，主要用于数据库管理系统（DBMS）的实现。

(3)物理数据模型（Physical Data Model）:简称物理模型，是面向计算机物理表示的模型，描述了数据在储存介质上的组织结构，它不但与具体的DBMS有关，而且还与操作系统和硬件有关。每一种逻辑数据模型在实现时都有起对应的物理数据模型。DBMS为了保证其独立性与可移植性，大部分物理数据模型的实现工作又系统自动完成，而设计者只设计索引、聚集等特殊结构。

 

3.3查询数据、更新、删除

 3.3.1简单查询

    SELECT  子句列出了要被SELECT语句检索的数据项。

    FROM    子句列出了包含要被查询检索的数据表。

    WHERE   子句告诉SQL仅包含查询结果中的某些数据记录。

    GROUP BY 指定一个汇总查询

    HAVING   子句告诉SQL把由GROUP BY子句生成的某些组包含在查询结果中

    ORDER BY 子句基于一个字段或更多的字段的数据排序查询结果。

    重复的记录用DISTINCT

例如：SELECT  NAME, GZ

FROM TB_HR_BM ,TB_HR_GZ

WHERE TB_HR_GZ_NAME=TB_HR_BM_BM

GROUP BY

3.3.2搜索条件

     比较测试  把一个表达式的值和另一个表大式的值进行比较。

     （=，<>,<,<=,>=,>）

     对空值（NULL）的考虑

     如果查询的条件里存在NULL，则要从新选择查询条件。

     范围测试（BETWEEN）

     提供一种不同形式的搜索条件，可以查询结果在你想要的范围内。

     组成员测试（IN）

     另一种常用的搜索条件是组成员测试，很直观的查询相应结果。

     模式匹配测试（LIKE）

     可以使用一些简单的比较测试来检索文本字段的内容匹配一些特定文本的记录。

     NULL 值测试（IS NULL）

     复合搜索条件（AND，OR和NOT）

     组合查询结果（UNION）

  

3.3.3 多表查询

二个或多个表表的查询，通过匹配相关字段的内容来形成一对一对的记录的过程连接表

3.3.4 汇总查询

字段函数

       SUM（）计算字段的总和

        AVG（）计算字段的平均值

        MIN（）查找字段中的最小值

        COUNT（）计算字段中值的数目

        COUNT（*）计算查询结果的记录数

 

3.3.5子查询

什么是子查询，子查询是一个查询中的查询，出现在WHERE、HAVING 子句里的一个小子查询搜索条件内

嵌套的子查询

主查询与子查询是二级查询，如果出现更多的子查询就会是三级查询

关联子查询

对多子查询来说主查询的结果，子查询也会生成对应的一个或是一组结果。

所以在关联的子查询的过程中是用来主查询与子查询做对比。

3.3.6更新数据

  UPDATE <表名>

   SET <表名>_<列名>=取值

   WHERE 条件

 

   插入数据:向表里添加一组数据

  INSERT INTO <表名>（值1值，值3）

VALUSE(对应值1，对应值2，对应值3)

保护数据:设置数据访问权限

GRANT UPDATE,INSERT,DELETE

  ON <表名>

    TO 某个人

修改列

alter table <表名> add

<列名> <数据类型> [长度]  <,>

<列名...>

例如：tb_basic_dept表添加多列remark1

alter table tb_basic_dept  add

remark1 varchar(50),

remark2 varchar(50),

remark3 varchar(50)

3.3.7标准SQL字符串函数

ASCII() --函数返回字符表达式最左端字符的ASCII 码值

CHAR() --函数用于将ASCII 码转换为字符--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL

LOWER() --函数把字符串全部转换为小写

UPPER() --函数把字符串全部转换为大写

STR() --函数把数值型数据转换为字符型数据

LTRIM() --函数把字符串头部的空格去掉

RTRIM() --函数把字符串尾部的空格去掉

LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串

CHARINDEX()--函数返回字符串中某个指定的子串出现的开始位置

REPLICATE() --函数返回一个重复指定次数的字符串

REVERSE() --函数将指定的字符串的字符排列顺序颠倒

REPLACE() --函数返回被替换了指定子串的字符串

select replace('abc123g', '123', 'def')运行结果如下abcdefg

SPACE() --函数返回一个有指定长度的空白字符串

STUFF() --函数用另一子串替换字符串指定位置长度的子串

3.3.8.建立函数
语法:
CREATE FUNCTION <函数名>(参数变量列表)
[返回值RETURNS 数据类型] [WITH ENCRYPTION]
AS
BEGIN
    {函数代码体....}    
END

局部/全局变量定义
局部变量 (以@开头)
格式:declare @变量名 数据类型
代码:declare @x int

全局变量 (必须以@@开头)
格式:declare @@变量名 类型
代码:select @@id = '10010001'

赋值语句set/select

set @id = '10010001'
select @id = '10010001'

8.条件语句(if/case)

语句CASE

循环语句(while)

定时执行(waitfor)

*********************************************************************

 

 

 

1.建立数据库test

 是我学习创建的数据库---是一个记录保存的系统

1.1所谓数据库是指:以同一组织方式将相关数据组织在一起,并存放在计算机存储器上的,能够为多个用户所共享,与应用程序彼此相互独立的一组数据的组合.
数据库可以有效的组织数据,并将数据方便的输入到计算机中,还可以根据用户的要求将数据从计算机中取出来.

1.2  创建数据库语句：Create  database  test   这是在SQL       SERVER数据库中用来创建的表。 如果删除数据库用 drop database test。

1.3  现在对SQL SERVER数据库这方面的认识深入很多，数据库原理的基本知识与管理与应该，都以基本熟悉。     

2.建立数据库的表

2.1 数据库表：是一个虚拟的临时的表。数据库里的信息都存在数据库的柱子里面，但你查询的时候他就会根据你的信息临时组建一个表，这个就是数据库表。

2.2 创建数据库表语句：

tb_hr_bm      创建部门表 ；  tb_hr_gz           创建员工表

create table tb_hr_gz

(id int not null,

name varchar(30)

not null,hrid char(18) null,    

workage int null ,

bm varchar(20) null,

gz real null,remark varchar(100) null)

hrid=身份证号码，workage=工作年数，gz=工资金额，bm=部门

id      (int,not null)               是表tb_hr_gz 的例，在数据库语言中称为字段

name    (varchar(30),null)          小括号是是设置字段的数据类型

hrid    (char(18),null)              在建表的过程中还要注意字段的关系

workage (int, null)                  还有每个字段要不要设置什么限制或是默认值

bm      (varchar(20),null)

gz      (real,null)

remark  (varchar(100),null)

删除数据库表:    drop table tb_hr_gz

 

2.2.1 索引，是建立在表中的一个属性。

1> 索引是在数据库大量数据中，为迅速查找你需要的数据，或排序等需要，在原数据表为基础建立的，辅助文件。通过它可对数据表进行排序，迅速查找.

     2> 建立索引语句： create index tb_hr_gz_ix on tb_hr_gz (id)

     3>删除索引的语句：drop index tb_hr_gz. tb_hr_gz_ix

2.2.2 约束，是建立在表中的属性

约束有完整性约束和事务

2.2.2.1>检查约束(建立/删除) 

 在建表或是以后有需要的时候，会根据USER提供的资料做一些输入数据的约束，当有新数据输入时系统会检查是否符合这约束。

  例如：方法1:
create table tb_hr_gz

(   …

gz real default 0.0 check(gz >=0 and gz <=10000000000),

...)

方法2:

alter table tb_hr_gz
add constraint tb_hr_gz_ck check(gz >=0 and gz <=10000000000)             

2.2.2.2>唯一约束(建立/删除)   数据库表里如果出现一个字段唯一就会给他设置一个唯一约束，就如：身份证号码 例如：

            alter table tb_hr_gz
Add   constraint  tb_hr_gz_uk  unique(hrid) 

2.2.2.3>主键约束(建立/删除)  主键就是你一张数据表中，保证你每行数据都是唯一的那个列，这个列不能为空。

        例如：alter table tb_hr_bm

add constraint tb_hr_bm_pk  Primary Key (bm)

    

2.2.2.4>外键约束(建立/删除)  外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。

         所以会在更新或删除的过程出现级联更新、级联删除。

      例如：alter table tb_hr_personl_info

add constraint tb_hr_personl_info__bm_fkforeign

key(bm)references tb_hr_bm (bm)

on update cascade

on delete cascade

    

2.2.2.5>事务(COMMIT/ROLLBACK)

是一条或多条SQL语句序列，它们组合在一起形成一个逻辑工作单元。

   按先后顺序完成任务。

   COMMIT(提交)：事务成功结束

   ROLLBACK：（回滚） 事务没有成功结束

2.2.3 触发器，

2.2.3.1触发器是一种特殊类型的存储过程，

当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：UPDATE、INSERT 或 DELETE。触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求

2.2.3.2 触发器语句：

reate  trigger tg_tb_hr_bm

on tb_hr_bm

for insert,update,delete

as

  declare @bm_d varchar(20)

  declare @bm_i varchar(20)

begin

  set @bm_d=(select bm from deleted) 

  set @bm_i=(select bm from inserted) 

  if  exists(select *  from tb_hr_gz ,deleted where(tb_hr_gz.bm =deleted.bm  ))      

  begin

     update tb_hr_gz   set bm=''  where  bm =@bm_d

  end

  if update(bm)

    begin   

        update tb_hr_gz         set bm=@bm_i      where  bm =@bm_i

    end

end

2.2.3.3禁用或开启触发器

           语法：alter table <表名> enable/disable trigger 触发器名

           例如：alter table tb_hr_gz enable/disable trigger tg_tb_hr_gz

2.3表的连接

   2.3.1 、内连接  

内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接

   2.3.2 、外连接

       外联接可以是左向外联接、右向外联接或完整外部联接。

   2.3.3 、交叉联接

           交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积  

3.视图

   3.1在查询数据的时候，你可以把视图看做是一个表

但这个表所查询的内容却有可能是和多个表有联系的

如果没有视图，众多的复合查询会让你的语句很难看，用好视图可让你的语句变得简洁

同时查询的时候也可以让USER真接来查看视图得到结果的新表.

   3.2视图语句：

     3.2.1建立 create view v_tb_hr_bm 

as  select *

from tb_hr_bm

3.2.1删除  drop view v _b_hr_bm

 

4.存储过程

4.1存储过程（Stored Procedure）

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

系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。通过系统存储过程，MS SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。

4.2 创建存储过程的语句：

    create procedure p_tb_hr_gz

(@id int,

@newname varchar(30))

as

begin

 if (exists(select * from tb_hr_gz where id=@id))

 begin

    update tb_hr_gz  set name=@newname  where id=@id

 end

end

4.2.2 带有游标的存储过程

     create procedure p_fill_remark_tb_hr_gz

as

 declare @id1 int

 declare @name1 varchar(30)

 declare @bm1 varchar(20)

 

begin

  declare cursor1 cursor for select id,name,bm from tb_hr_gz

  open cursor1

  fetch next from cursor1 into @id1,@name1,@bm1

  while @@fetch_status = 0

  begin

  update tb_hr_gz

  set remark=@name1+'-'+@bm1

  where id=@id1

  fetch next from cursor1 into @id1,@name1,@bm1

  end 

  close cursor1

end

5、函数

    5.1数据库函数是指当需要分析数据清单中的数值是否符合特定条件时，使用数据库工作表函数

5.2 create function f_tb_hr_bm(@num numeric(15,2))

returns numeric

begin

return @num+1

end

*********************************************************************