02 数据库二.docx

1、02 数据库二数据库(二)上节回顾：SQL Server 特点、版本、Management Studio使用管理数据库(创建、删除、修改、导入导出、备份还原)管理数据表(创建表,表关系,数据类型,完整性规则等)没有操作成功的：1.导入/导出：在不同格式数据库导入导出时,数据类型映射,会出现错误提示,忽略这些错误就行了。2.备份/还原数据库：因为是数据库维护的一大项,可以找时间详细讲。上次作业：根据书上创建BlogDb数据库。BlogDB数据库的三个表的字段设置比较合理,没有多余的项,也没有缺少的项,怎么样做到这样呢。需要根据数据库设计的范式来设计。6.8 数据完整性规则完整性规则是为了保证表中

2、的数据正确、一致、有效。1)数据完整性规则主键不能为空,要唯一,因为一个主键标识一条记录(实体),如果为空或有重复就不能正确识别。2)参照完整性规则外键的值必须参照主键的取值,也就是说,外键的值必须有对应的主键值。3)用户定义完整性规则主要是取值范围,例如年龄不能小于0.6.9 关系范式(数据表)为了建立结构合理,冗余小的数据库,设计、构造数据表时要遵循一定的规则,就是范式。共有6种范式从简单到复杂为：第一范式(1NF),第二范式,第三范式,BC范式(BCNF),第四范式,第五范式。一般满足前三个范式就足够了。第一范式表中的所有属性都不能再分割。(每一列都是一个分量),是关系模式最起码的要求,

3、不满足第一范式的数据库模式不能称为关系数据库。工号 姓名 固定电话和手机应当将该列分为两列工号 姓名 固定电话 手机第二范式除了满足第一范式,还要做到表中的非主键值都依赖于主键。如果不符合的要分成两个表。学号 姓名 性别 课程号 成绩姓名、性别是依赖学号的,成绩是依赖课程号的,要分为两个表。根据学号能推导出姓名,性别,但是不能推出成绩。学号 姓名 性别课程号 学号 成绩第三范式除了满足第一范式和第二范式,还要满足任何两个非主键字段不存在函数依赖关系。商品编号 商品名称 单价 数量 金额 不能有 金额=单价*数量 这样的依赖关系遵循第三范式就能设计出比较好的数据库了。了解：BC范式任何字段都不能

4、被非主属性决定姓名 国家 地区洲决定有哪些国家思考：根据这些范式设计数据库表字段。6.10 使用SSMS 操作数据插入记录：设置自动增长的列不写数据,非空的列一定要有数据要点一下！运行sql语句才能提交。修改记录(编辑前200条)修改外键时要注意,在关联表中一定要有这个主键。删除记录删除记录时要注意,表中的主键是否有关联的外键,要是有关联外键,需要先删除关联的记录,再删除本记录。否则会违反完整性规则(外键必须有对应的主键)。查询记录(前1000条)少量的操作数据可以使用SSMS,一条记录一条记录的添加、修改、删除,如果大批量的数据操作,或者有一些限定条件,就会很麻烦,容易出现错误。再一种情况,

5、操作远程数据库服务器上的数据,网络却不给力,不能使用远程桌面、图形客户端等,就只能在sqlcmd命令行下操作了。要解决这种问题,我们的SQL语言就要闪亮登场了。先介绍SQL语言中的 select 语句：Select 以记录的形式返回数据,From 子句,是表示从表或视图中查找数据Where 子句设置查询条件例如：select * from users - *号表示所有列select UserName,Password from Users - 列之间逗号隔开,效率高select * from Users where UserName= 张三 - 查询条件不过在正式讲 SQL语言登场前,按照惯例

6、,先补充一些理论依据。7 补充知识 关系和逻辑7.1 集合运算集合运算的理论,是SQL语言的理论基础。有两个集合,R和S,里面有若干元素。如果要与数据库联系起来,两个集合就是两个数据表,其中的元素就是数据表中一条一条记录。并 - 属于R或属于S的元素组成的集合, 记作 RS交 - 既属于R同时也属于S的元素的集合,记作 RS差 - R-S 表示属于R但是不属于S的元素集合 S-R 表示属于S但是不属于R的元素集合广义笛卡尔积 - RSR和S元组分别为n和m,那么,R和S的笛卡尔积是一个 n+m 元的元组集合,新集合中,每个元组的前n个分量来自R的一个元组,后m个分量来自S的一个元组,总元组数为

7、R和S元组数的乘积。R SA B C D ERA1 RB1 RC1 SD1 SE1RA2 RB2 RC2 SD2 SE2R X SR.A R.B R.C S.D S.ERA1 RB1 RC1 SD1 SE1RA1 RB1 RC1 SD2 SE2RA2 RB2 RC2 SD1 SE1RA2 RB2 RC2 SD2 SE27.2 专门的关系运算这是数据库中使用的运算方式。选择 - 选出符合某种条件的记录组成新的集合。(水平分割,整行),这也是集合的交集运算。比如： 选择Users表中性别为男的记录用SQL语句来表示选择Select * from Users where Sex=男投影 - 从关系中

8、选择若干属性(列)组成新的关系。比如：选择所有记录的UserName列和Password列组成一个新表,就是对原来的表做投影运算。Select UserName,Password from Users选择并投影Select UserName,Password from Users where Sex=男连接 - 从两个关系R和S的笛卡尔积中选择满足条件的元组组成新的关系。先做笛卡尔积,再选择。在连接运算中,如果要求两个关系中进行比较的列必须是相同的属性组,且在结果集中去掉重复性列,这种叫自然连接。 记作 RSSelect Users.UserName, Users.Password, Arti

9、cle.subject,Atricle.shijianFrom Users, ArticleWhere Users.UserName=Article.UserName用 Users表和Arctic表来做自然连接,新表会很大。回头再看第二范式 其实就是不要做连接有了这些理论依据,可以学习sql语言了。8 SQL 语言基础8.1 SQL语言介绍SQL是StructureQueryLanguage的缩写,意思是结构化查询语言。SQL语言是关系型数据库管理系统的标准语言,用来对数据库进行各种操作。SQL语言是面向集合的、描述性、非过程化语言。它功能强,效率高,简单易学易维护。面向集合：SQL的操作对象

10、是一个或多个关系,操作的结果也是是一个关系;非过程化：用户只需要提出干什么,DBMS即可把结果返回给用户,至于怎么干由DBMS解决;能以多种方式使用：可以直接以命令方式交互使用,也可嵌入到宿主语言中使用;一体化特点：具有查询、操作、定义、控制等四种语言一体化的特点,每种操作只需一种操作符;然而SQL语言同时也出现了这样一个问题：它是非过程性语言,即大多数语句都是独立执行的,与上下文无关,而绝大部分应用都是一个完整的过程,显然用SQL完全实现这些功能是很困难的。所以大多数数据库公司为了解决此问题,作了如下两方面的工作：(1)扩充SQL,在SQL中引入过程性结构;(2)SQL嵌入到高级语言中,以便

11、一起完成一个完整的应用。8.2 SQL语言功能分类SQL语言按功能可分为四部分：数据控制功能、数据查询功能、数据操纵功能。读音：drop 的如啊坡 alter 奥特儿 grant 个如昂特 revoke 瑞外欧克SQL功能命令动词描述数据定义功能 CREATEDROPALTER定义、删除、修改数据库中的对象数据查询功能SELECT实现查询数据的功能数据操纵功能INSERTUPDATEDELETE实现对数据的增加、删除和修改数据控制功能GRANTREVOKE控制用户对数据库的操作权限对应的,SQL语言也分为：数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。既然是语

12、言就需要有语法,就是要按一定的格式来写8.3 T-SQL语法约定8.3.1 常量常量,也称为文字值或标量值,是给定的不变的值。常用常量类型：字符串常量用单引号括起来的字符或字符串例如：a, hello与数据表Users结合, UserName=张三 Sex=男整型常量不带小数点的十进制数据123, -1年龄 Age=20日期时间常量 单引号括起来的表示日期时间的字符串2012-01-01, 00:00:00, 2012-01-01 00:00:00RegTime=2012-01-01 00:00:00实型常量有两种：定点表示和浮点(科学计数法)表示1234.56, 1.23E5货币常量以$符号

13、作为前缀的整型或实型数据$10, $10.00二进制常量以0x开始的十六进制数据0x12AF8.3.2 变量概述在数学中，求两个数的和，比如求1+2的和，可以写 1+2= ，这里的1 和 2 就是常数，是直接给出的。如果，先不告诉你几加几，只告诉你两个数求和，那么我们可以先列一个方程式 z=x+y, 当给出x和y的值时，就可以写了x=1y=2z=x+y=1+2=3再算其他值时同样道理编程语言中，包括T-SQL也一样，要能有个像x，y这样能临时替代实际数据的东东，就是变量。变量用于临时存放数据, 必须提供一个合法标示符(名字，像x，y，z) 。标识符：长度1-128位,以英文字母或下划线开头,字

14、符可以是英文字母、下划线或数字,不能是关键词。全局变量：由系统定义和维护 ,不是用户定义的,用户只能使用系统预先定义好的全局变量,全局变量名称前两个 ,局部变量：用户自己定义和赋值的变量,在一定范围内有效。 名称前一个 ,名字不能与全局变量相同;全局变量名称 数据类型局部变量名称 数据类型var_1变量定义 （声明） Declare 语句 (读音 迪克莱尔)先要定义变量，包括变量标示符和数据类型。Declare x int, y int, z varchar(100) -初始化为NULL (读音 闹) 变量赋值 Set语句Declare x int, y int, z varchar(100)

15、Set x=20Set y=100Set z=hello赋值时值可以是一个常量，也可以是经过计算得出的结果。显示输出打印变量值 print语句Declare x int, y int, z varchar(100)Set x=20Set y=100Set z=helloPrint xPrint yPrint z以数据记录形式输出变量 SelectSelect x as X, y as Y -做为X字段和Y字段,否则没有字段名作为一条记录输出,记录有两个列X和Y8.3.3运算符算术运算符 (返回运算后的数值)+ 加 减 * 乘 / 除 % 求余数(模)Declare x int, y int,

16、z varchar(100)Set x=20Set y=100Set z=helloPrint x + y赋值运算符 给变量赋值 =比较运算符 比较两个表达式是否相等 = = = ! !2Select * from Users where UserID between 1 and 3Select * from Users where UserID in(1,2,3)Select * from Users where UserID=1 or UserID=2 or UserID=3Select * from Article where Content is NullSelect * from U

17、sers where UserName like %yang%8.3.4 语句Begin end 将多个语句组成一个语句块,一并处理。在条件语句和循环语句中,要执行两个或两个以上语句就需要beginend语句条件判断语句 If else Declare x int, y int, z varchar(100)Set x=20Set y=100If x ybeginPrint xyendElseBeginPrint xyEndCASE语句计算多个条件式,将其中符合条件的一个结果表达式返回。Declare x int, y int, z varchar(100)Set x=20Set y=100x

18、 = CASE yWhen 100 then OneWhen 200 then TwoElse otherEnd当y 等于100时 x 等于 One,当y等于200时x等于 Two循环语句 while continue break (读音 肯替妞)Declare i intSet i=1while i 10beginif i=5continueprint ii = i + 1End批处理多条语句作为一个批处理执行时,用go语句作为分隔。Go表示执行Use BlogDbGo -因为下面的语句是针对BlogDb数据库的Select * from UsersGo上节回顾：关系数据库范式在manage

19、ment studio 中插入记录，修改记录，删除记录，查询记录等集合的运算：并，交，差，笛卡尔积关系运算：选择，投影，连接T-SQL语言理论较多，要多实践。SQL语言内容太多，不能都记住，只要知道基本的语法规则，和常用的一些内容，就行，遇到不知道怎么用的内容，查一下工具书，或上网查资料，一般都能解决。暂停语句 waitfor暂时停止执行SQL语句,直到设定的时间已过或到达设定时间。-例 等待(延迟)1 小时2 分零3 秒后才执行后面的语句waitfor delay 01:02:03; -读音(低累)select * from Users;-例 等到晚上11 点零8 分后才执行后面的语句wai

20、tfor time 23:08:00Return 语句 无条件中止,位于return后面的语句将不被执行。Return数据操作语句SELECT -从数据库表中检索数据行和列INSERT -向数据库表添加新数据行DELETE -从数据库表中删除数据行UPDATE -更新数据库表中的数据-数据定义CREATE TABLE -创建一个数据库表DROP TABLE -从数据库中删除表ALTER TABLE -修改数据库表结构CREATE VIEW -创建一个视图DROP VIEW -从数据库中删除视图CREATE INDEX -为数据库表创建一个索引DROP INDEX -从数据库中删除索引CREAT

21、E PROCEDURE -创建一个存储过程DROP PROCEDURE -从数据库中删除存储过程CREATE TRIGGER -创建一个触发器DROP TRIGGER -从数据库中删除触发器-数据控制GRANT -授予用户访问权限DENY -拒绝用户访问REVOKE -解除用户访问权限-事务控制COMMIT -结束当前事务ROLLBACK -中止当前事务-程序化SQLDECLARE -为查询设定游标EXPLAN -为查询描述数据访问计划OPEN -检索查询结果打开一个游标FETCH -检索一行查询结果CLOSE -关闭游标PREPARE -为动态执行准备SQL 语句EXECUTE -动态地执行

22、SQL 语句DESCRIBE -描述准备好的查询8.3.5 注释注释就是程序代码中不执行的文本字符串-注释单行，两个横杠，后面的内容被省略。/* */ 注释多行8.3.6 T-SQL常用函数T-SQL语言为了方便使用,将一些常用的功能封装为函数,用户可以直接调用。函数会返回一个值或完成某项功能。函数返回值可以给变量赋值。函数名(参数列表)数学函数Abs(n) 绝对值Power(数,幂) 幂函数 Rand() 从0到1之间的随机float数。 读音 入按的Round(数,精度) 精确到指定的精度 读音 入昂的Select ABS(-1) as 绝对值, rand() as 随机数, round(

23、1.234,2) as 两位精度字符串函数Len(str) 字符串中的字符个数Substring(str,开始,长度) 从s中指定位置开始,指定长度的字符串Str() 将数字类型转换为字符类型Char(数字) 返回ASCII码字符 比如：A = 65, Ascii(字符) 返回对应的十进制数字附：常用ascii码表：48 057 965 A90 Z97 a122 zLeft(s,len) 返回从字符串左边开始指定个数的字符Right(s, len) 返回从字符串左边开始指定个数的字符Upper(字符串) 大写Lower(字符串) 小写Select len(abcdef) as 字符个数 -中文

24、每个字长度是1Select substring(ABCDEF,1, 3) as 前三个字符Select char(65) as ASCII码 -A日期时间函数Getdate() 得到当前系统日期时间Year(datetime) 得到指定日期时间中的年份Day(datetime) 得到指定日期时间中的日Month(datetime) 得到月份Dateadd (年或月或日, 数量, 日期)在指定日期时间基础上加上一段时间的日期时间Select getdate() as 日期时间Select year(getdate() as 年份Select dateadd(day, 10, getdate()

25、as 十天后的时间聚合函数Count(列名) 返回指定列的个数,* 表示所有列,则是记录数例：Select count(*) as 总数 from UsersSum(列名) 对应列的数值总和Select sum(UserID) as 和 from UsersMax(列名) 对应列的数值的最大值Min(列名) 对应列的数值的最小值Avg(列名) 对应列的数值平均值数据类型转换函数Cast(变量 as 数据类型) 读音 卡斯特显示转换为另一种类型,是sql标准语句Declare i int, s varchar(10)Set i=123456set s = substring(Cast( i as

26、 varchar(6),1,3)select sConvert(数据类型,值,style) 读音 肯外特显示转换为另一种数据类型。是T-SQL特有的Style是数字,表示日期时间的样式,转换日期时间时用。Declare s varchar(100)Set s=Convert(varchar(100),GETDATE(), 20)Select s转换日期需要格式 style常用的格式CONVERT(varchar(100), GETDATE(), 0) 05 16 2006 10:57AMCONVERT(varchar(100), GETDATE(), 1) 05/16/06 CONVERT(v

27、archar(100), GETDATE(), 5) 16-05-06 CONVERT(varchar(100), GETDATE(), 20) 2006-05-16 10:57:47 CONVERT(varchar(100), GETDATE(), 100) 05 16 2006 10: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 CON

28、VERT(varchar(100), GETDATE(), 112) 20060516 CONVERT(varchar(100), GETDATE(), 113) 16 05 2006 10:57:49:513 CONVERT(varchar(100), GETDATE(), 120) 2006-05-16 10:57:49 用户自定义函数除了系统给的这些函数外，用户可以根据自己的需要，定义自己的函数。Create Function 函数名称（形式参数名称 AS 数据类型）Returns 返回数据类型asBegin函数内容Return 表达式End例1： 返回Hello World字符串create function