02 数据库二.docx

02 数据库二.docx

《02 数据库二.docx》由会员分享，可在线阅读，更多相关《02 数据库二.docx（27页珍藏版）》请在冰豆网上搜索。

02数据库二

数据库

（二）

上节回顾：

SQLServer特点、版本、ManagementStudio使用

管理数据库（创建、删除、修改、导入导出、备份还原）

管理数据表（创建表,表关系,数据类型,完整性规则等）

没有操作成功的：

1.导入/导出：

在不同格式数据库导入导出时,数据类型映射,会出现错误提示,忽略这些错误就行了。

2.备份/还原数据库：

因为是数据库维护的一大项,可以找时间详细讲。

上次作业：

根据书上创建BlogDb数据库。

BlogDB数据库的三个表的字段设置比较合理,没有多余的项,也没有缺少的项,怎么样做到这样呢。

需要根据数据库设计的范式来设计。

6.8数据完整性规则

完整性规则是为了保证表中的数据正确、一致、有效。

1）数据完整性规则

主键不能为空,要唯一,因为一个主键标识一条记录（实体）,如果为空或有重复就不能正确识别。

2）参照完整性规则

外键的值必须参照主键的取值,也就是说,外键的值必须有对应的主键值。

3）用户定义完整性规则

主要是取值范围,例如年龄不能小于0.

6.9关系范式（数据表）

为了建立结构合理,冗余小的数据库,设计、构造数据表时要遵循一定的规则,就是范式。

共有6种范式

从简单到复杂为：

第一范式（1NF）,第二范式,第三范式,BC范式（BCNF）,第四范式,第五范式。

一般满足前三个范式就足够了。

第一范式

表中的所有属性都不能再分割。

（每一列都是一个分量）,是关系模式最起码的要求,不满足第一范式的数据库模式不能称为关系数据库。

工号姓名固定电话和手机

应当将该列分为两列

工号姓名固定电话手机

第二范式

除了满足第一范式,还要做到表中的非主键值都依赖于主键。

如果不符合的要分成两个表。

学号姓名性别课程号成绩

姓名、性别是依赖学号的,成绩是依赖课程号的,要分为两个表。

根据学号能推导出姓名,性别,但是不能推出成绩。

学号姓名性别

课程号学号成绩

第三范式

除了满足第一范式和第二范式,还要满足任何两个非主键字段不存在函数依赖关系。

商品编号商品名称单价数量金额

不能有金额=单价*数量这样的依赖关系

遵循第三范式就能设计出比较好的数据库了。

了解：

BC范式

任何字段都不能被非主属性决定

姓名国家地区

洲决定有哪些国家

思考：

根据这些范式设计数据库表字段。

6.10使用SSMS操作数据

插入记录：

设置自动增长的列不写数据,非空的列一定要有数据

要点一下！

运行sql语句才能提交。

修改记录（编辑前200条）

修改外键时要注意,在关联表中一定要有这个主键。

删除记录

删除记录时要注意,表中的主键是否有关联的外键,要是有关联外键,需要先删除关联的记录,再删除本记录。

否则会违反完整性规则（外键必须有对应的主键）。

查询记录（前1000条）

少量的操作数据可以使用SSMS,一条记录一条记录的添加、修改、删除,如果大批量的数据操作,或者有一些限定条件,就会很麻烦,容易出现错误。

再一种情况,操作远程数据库服务器上的数据,网络却不给力,不能使用远程桌面、图形客户端等,就只能在sqlcmd命令行下操作了。

要解决这种问题,我们的SQL语言就要闪亮登场了。

先介绍SQL语言中的select语句：

Select以记录的形式返回数据,

From子句,是表示从表或视图中查找数据

Where子句设置查询条件

例如：

select*fromusers--*号表示所有列

selectUserName,PasswordfromUsers--列之间逗号隔开,效率高

select*fromUserswhereUserName='张三'--查询条件

不过在正式讲SQL语言登场前,按照惯例,先补充一些理论依据。

7补充知识关系和逻辑

7.1集合运算

集合运算的理论,是SQL语言的理论基础。

有两个集合,R和S,里面有若干元素。

如果要与数据库联系起来,两个集合就是两个数据表,其中的元素就是数据表中一条一条记录。

并----属于R或属于S的元素组成的集合,记作R∪S

交----既属于R同时也属于S的元素的集合,记作R∩S

差----R-S表示属于R但是不属于S的元素集合

S-R表示属于S但是不属于R的元素集合

广义笛卡尔积----R×S

R和S元组分别为n和m,那么,R和S的笛卡尔积是一个n+m元的元组集合,新集合中,每个元组的前n个分量来自R的一个元组,后m个分量来自S的一个元组,总元组数为R和S元组数的乘积。

RS

ABCDE

RA1RB1RC1SD1SE1

RA2RB2RC2SD2SE2

RXS

R.AR.BR.CS.DS.E

RA1RB1RC1SD1SE1

RA1RB1RC1SD2SE2

RA2RB2RC2SD1SE1

RA2RB2RC2SD2SE2

7.2专门的关系运算

这是数据库中使用的运算方式。

选择----选出符合某种条件的记录组成新的集合。

（水平分割,整行）,这也是集合的交集运算。

比如：

选择Users表中性别为’男’的记录

用SQL语句来表示选择

Select*fromUserswhereSex='男'

投影----从关系中选择若干属性（列）组成新的关系。

比如：

选择所有记录的UserName列和Password列组成一个新表,就是对原来的表做投影运算。

SelectUserName,PasswordfromUsers

选择并投影

SelectUserName,PasswordfromUserswhereSex='男'

连接----从两个关系R和S的笛卡尔积中选择满足条件的元组组成新的关系。

先做笛卡尔积,再选择。

在连接运算中,如果要求两个关系中进行比较的列必须是相同的属性组,且在结果集中去掉重复性列,这种叫自然连接。

记作R∞S

SelectUsers.UserName,Users.Password,Article.subject,Atricle.shijian

FromUsers,Article

WhereUsers.UserName=Article.UserName

用Users表和Arctic表来做自然连接,新表会很大。

回头再看第二范式其实就是不要做连接

有了这些理论依据,可以学习sql语言了。

8SQL语言基础

8.1SQL语言介绍

    SQL是Structure Query Language的缩写,意思是结构化查询语言。

    SQL语言是关系型数据库管理系统的标准语言,用来对数据库进行各种操作。

    SQL语言是面向集合的、描述性、非过程化语言。

它功能强,效率高,简单易学易维护。

    面向集合：

SQL的操作对象是一个或多个关系,操作的结果也是是一个关系;

    非过程化：

用户只需要提出干什么,DBMS即可把结果返回给用户,至于怎么干由DBMS解决;

    能以多种方式使用：

可以直接以命令方式交互使用,也可嵌入到宿主语言中使用;

    一体化特点：

具有查询、操作、定义、控制等四种语言一体化的特点,每种操作只需一种操作符;

    然而SQL语言同时也出现了这样一个问题：

它是非过程性语言,即大多数语句都是独立执行的,与上下文无关,而绝大部分应用都是一个完整的过程,显然用SQL完全实现这些功能是很困难的。

所以大多数数据库公司为了解决此问题,作了如下两方面的工作：

（1） 扩充SQL,在SQL中引入过程性结构;

（2） SQL嵌入到高级语言中,以便一起完成一个完整的应用。

8.2SQL语言功能分类

SQL语言按功能可分为四部分：

数据控制功能、数据查询功能、数据操纵功能。

 读音：

drop的如啊坡alter奥特儿grant个如昂特revoke瑞外欧克

SQL功能        命令动词                描述

数据定义功能   CREATE  DROP  ALTER     定义、删除、修改数据库中的对象

数据查询功能    SELECT                  实现查询数据的功能

数据操纵功能    INSERT  UPDATE DELETE   实现对数据的增加、删除和修改

数据控制功能    GRANT  REVOKE           控制用户对数据库的操作权限

对应的,SQL语言也分为：

数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。

既然是语言就需要有语法,就是要按一定的格式来写

8.3T-SQL语法约定

8.3.1常量

常量,也称为文字值或标量值,是给定的不变的值。

常用常量类型：

字符串常量

用单引号括起来的字符或字符串

例如：

'a','hello'

与数据表Users结合,UserName='张三'Sex='男'

整型常量

不带小数点的十进制数据

123,-1

年龄Age=20

日期时间常量

单引号括起来的表示日期时间的字符串

'2012-01-01','00:

00:

00','2012-01-0100:

00:

00'

RegTime='2012-01-0100:

00:

00'

实型常量

有两种：

定点表示和浮点（科学计数法）表示

1234.56,1.23E5

货币常量

以$符号作为前缀的整型或实型数据

$10,$10.00

二进制常量

以0x开始的十六进制数据

0x12AF

8.3.2变量

概述

在数学中，求两个数的和，比如求1+2的和，可以写1+2=，这里的1和2就是常数，是直接给出的。

如果，先不告诉你几加几，只告诉你两个数求和，那么我们可以先列一个方程式z=x+y,当给出x和y的值时，就可以写了

x=1

y=2

z=x+y=1+2=3

再算其他值时同样道理

编程语言中，包括T-SQL也一样，要能有个像x，y这样能临时替代实际数据的东东，就是变量。

变量用于临时存放数据,必须提供一个合法标示符（名字，像x，y，z）。

标识符：

长度1-128位,以英文字母或下划线开头,字符可以是英文字母、下划线或数字,不能是关键词。

全局变量：

由系统定义和维护,不是用户定义的,用户只能使用系统预先定义好的全局变量,全局变量名称前两个@@,

局部变量：

用户自己定义和赋值的变量,在一定范围内有效。

名称前一个@,名字不能与全局变量相同;

@@全局变量名称数据类型

@局部变量名称数据类型

@var_1

变量定义（声明）Declare语句（读音迪克’莱尔）

先要定义变量，包括变量标示符和数据类型。

Declare@xint,@yint,@zvarchar（100）--初始化为NULL（读音闹~）

变量赋值Set语句

Declare@xint,@yint,@zvarchar（100）

Set@x=20

Set@y=100

Set@z='hello'

赋值时值可以是一个常量，也可以是经过计算得出的结果。

显示输出打印变量值print语句

Declare@xint,@yint,@zvarchar（100）

Set@x=20

Set@y=100

Set@z='hello'

Print@x

Print@y

Print@z

以数据记录形式输出变量Select

Select@xasX,@yasY--做为X字段和Y字段,否则没有字段名

作为一条记录输出,记录有两个列X和Y

8.3.3运算符

算术运算符（返回运算后的数值）

+加–减*乘/除%求余数（模）

Declare@xint,@yint,@zvarchar（100）

Set@x=20

Set@y=100

Set@z='hello'

Print@x+@y

赋值运算符给变量赋值=

比较运算符比较两个表达式是否相等>>=<<=<>=!

>!

<

范围运算是否在范围内between…andnotbetween…and

列表运算是否是给定的值的一项IN（item1,item2,…）NOTIN（,,,…）

逻辑运算符测试条件是否为真NOT,AND,OR

判断是否空（与''的区别）ISNULL,ISNOTNULL

模糊查询LIKE（用于char,varchar,datetime）

_表示匹配任意一个单字符

%表示匹配任意多个字符（含零个）

[]表示范围内,

[^]表示范围外补字符号读音凯瑞特

例：

'AB%'以BR开始的字符串

'%XYZ'以XYZ结束

'_BC_'四个字符,前后两个任意字符

'[A-Z]'表示A到Z之间的一个字符

'[^A]'一个非A的字符

举例：

Select*fromUserswhereUserID>2

Select*fromUserswhereUserIDbetween1and3

Select*fromUserswhereUserIDin（1,2,3）

Select*fromUserswhereUserID=1orUserID=2orUserID=3

Select*fromArticlewhereContentisNull

Select*fromUserswhereUserNamelike'%yang%'

8.3.4语句

Begin…end…

将多个语句组成一个语句块,一并处理。

在条件语句和循环语句中,要执行两个或两个以上语句就需要begin…end语句

条件判断语句If…else…

Declare@xint,@yint,@zvarchar（100）

Set@x=20

Set@y=100

If@x>@y

begin

Print'x>y'

end

Else

Begin

Print'x

End

CASE语句

计算多个条件式,将其中符合条件的一个结果表达式返回。

Declare@xint,@yint,@zvarchar（100）

Set@x=20

Set@y=100

@x=CASE@y

When100then'One'

When200then'Two'

Else'other'

End

当@y等于100时@x等于'One',当@y等于200时@x等于'Two'

循环语句while…continue…break（读音肯替妞）

Declare@iint

Set@i=1

while@i<10

begin

ifi=5

continue

print@i

@i=@i+1

End

批处理

多条语句作为一个批处理执行时,用go语句作为分隔。

Go表示执行

UseBlogDb

Go--因为下面的语句是针对BlogDb数据库的

Select*fromUsers

Go

上节回顾：

关系数据库范式

在managementstudio中插入记录，修改记录，删除记录，查询记录等

集合的运算：

并，交，差，笛卡尔积

关系运算：

选择，投影，连接

T-SQL语言

理论较多，要多实践。

SQL语言内容太多，不能都记住，只要知道基本的语法规则，和常用的一些内容，就行，遇到不知道怎么用的内容，查一下工具书，或上网查资料，一般都能解决。

暂停语句waitfor

暂时停止执行SQL语句,直到设定的时间已过或到达设定时间。

--例等待（延迟）1小时2分零3秒后才执行后面的语句

waitfordelay'01:

02:

03';--读音（低累~）

select*fromUsers;

……

--例等到晚上11点零8分后才执行后面的语句

waitfortime'23:

08:

00'

……

Return语句

无条件中止,位于return后面的语句将不被执行。

……

Return

……

数据操作语句

SELECT--从数据库表中检索数据行和列

INSERT--向数据库表添加新数据行

DELETE--从数据库表中删除数据行

UPDATE--更新数据库表中的数据

--数据定义

CREATETABLE--创建一个数据库表

DROPTABLE--从数据库中删除表

ALTERTABLE--修改数据库表结构

CREATEVIEW--创建一个视图

DROPVIEW--从数据库中删除视图

CREATEINDEX--为数据库表创建一个索引

DROPINDEX--从数据库中删除索引

CREATEPROCEDURE--创建一个存储过程

DROPPROCEDURE--从数据库中删除存储过程

CREATETRIGGER--创建一个触发器

DROPTRIGGER--从数据库中删除触发器

--数据控制

GRANT--授予用户访问权限

DENY--拒绝用户访问

REVOKE--解除用户访问权限

--事务控制

COMMIT--结束当前事务

ROLLBACK--中止当前事务

--程序化SQL

DECLARE--为查询设定游标

EXPLAN--为查询描述数据访问计划

OPEN--检索查询结果打开一个游标

FETCH--检索一行查询结果

CLOSE--关闭游标

PREPARE--为动态执行准备SQL语句

EXECUTE--动态地执行SQL语句

DESCRIBE--描述准备好的查询

8.3.5注释

注释就是程序代码中不执行的文本字符串

--注释单行，两个横杠，后面的内容被省略。

/**/注释多行

8.3.6T-SQL常用函数

T-SQL语言为了方便使用,将一些常用的功能封装为函数,用户可以直接调用。

函数会返回一个值或完成某项功能。

函数返回值可以给变量赋值。

函数名（参数列表）

数学函数

Abs（n）绝对值

Power（数,幂）幂函数

Rand（）从0到1之间的随机float数。

读音入按的

Round（数,精度）精确到指定的精度读音入昂~的

SelectABS（-1）as绝对值,rand（）as随机数,round（1.234,2）as两位精度

字符串函数

Len（str）字符串中的字符个数

Substring（str,开始,长度）从s中指定位置开始,指定长度的字符串

Str（）将数字类型转换为字符类型

Char（数字）返回ASCII码字符比如：

A=65,

Ascii（字符）返回对应的十进制数字

附：

常用ascii码表：

480

579

65A

90Z

97a

122z

Left（s,len）返回从字符串左边开始指定个数的字符

Right（s,len）返回从字符串左边开始指定个数的字符

Upper（字符串）大写

Lower（字符串）小写

Selectlen（'abcdef'）as字符个数--中文每个字长度是1

Selectsubstring（'ABCDEF',1,3）as前三个字符

Selectchar（65）asASCII码--A

日期时间函数

Getdate（）得到当前系统日期时间

Year（datetime）得到指定日期时间中的年份

Day（datetime）得到指定日期时间中的日

Month（datetime）得到月份

Dateadd（年或月或日,数量,日期）在指定日期时间基础上加上一段时间的日期时间

Selectgetdate（）as日期时间

Selectyear（getdate（））as年份

Selectdateadd（day,10,getdate（））as十天后的时间

聚合函数

Count（列名）返回指定列的个数,*表示所有列,则是记录数

例：

Selectcount（*）as总数fromUsers

Sum（列名）对应列的数值总和

Selectsum（UserID）as和fromUsers

Max（列名）对应列的数值的最大值

Min（列名）对应列的数值的最小值

Avg（列名）对应列的数值平均值

数据类型转换函数

Cast（变量as数据类型）读音卡斯特

显示转换为另一种类型,是sql标准语句

Declare@iint,@svarchar（10）

Set@i=123456

set@s=substring（Cast（@iasvarchar（6））,1,3）

select@s

Convert（数据类型,值,[style]）读音肯外特

显示转换为另一种数据类型。

是T-SQL特有的

Style是数字,表示日期时间的样式,转换日期时间时用。

Declare@svarchar（100）

Set@s=Convert（varchar（100）,GETDATE（）,20）

Select@s

转换日期需要格式style

常用的格式

CONVERT（varchar（100）,GETDATE（）,0）0516200610:

57AM

CONVERT（varchar（100）,GETDATE（）,1）05/16/06

CONVERT（varchar（100）,GETDATE（）,5）16-05-06

CONVERT（varchar（100）,GETDATE（）,20）2006-05-1610:

57:

47

CONVERT（varchar（100）,GETDATE（）,100）0516200610:

57AM

CONVERT（varchar（100）,GETDATE（）,101）05/16/2006

CONVERT（varchar（100）,GETDATE（）,102）2006.05.16

CONVERT（varchar（100）,GETDATE（）,111）2006/05/16

CONVERT（varchar（100）,GETDATE（）,112）20060516

CONVERT（varchar（100）,GETDATE（）,113）1605200610:

57:

49:

513

CONVERT（varchar（100）,GETDATE（）,120）2006-05-1610:

57:

49

用户自定义函数

除了系统给的这些函数外，用户可以根据自己的需要，定义自己的函数。

CreateFunction函数名称

（形式参数名称AS数据类型）

Returns返回数据类型

as

Begin

函数内容

Return表达式

End

例1：

返回HelloWorld字符串

createfunction