数据库完整性控制、关系代数的理论与应用、数据操作第五次课
1数据完整性控制及实例(继续)
1)PK主码约束
2)unique约束
3)default 默认值
4)check 列级约束
5)check（条件）约束，可作为列级，也可元组级约束
6)check元组级约束
说明：check（条件），在编辑该列时/完成时/离开该列数据单元时，发生的条件检查。发生在列的编辑完成时的条件约束检查；
如果约束针对元组（行记录）时，约束检查发生在该元组数据编辑时。比如：鼠标离开编辑行状态，或insert命令操作时。
例题：
create table 学生表 (学号 char(7) not null primary key,姓名 char(10) ,性别 char(2) ,年龄 tinyint ,所在系 char(20), Set check ((性别 =’男’ or 性别=’女’ ) and (年龄>=15 and 年龄<=30)) )
如果是列级约束，check直接不加逗号跟在列说明后；如果check前面加了逗号，它是独立于所有列的，此时当元组约束。
元组约束可以同时限制多列，可以把以前的列组约束的多个条件合并与操作。
注意：列级约束发生数据错误时，每列单独提示；而元组约束统一提示，往往区分不出具体哪一列数据出错
例:列级和元组约束同时存在
create table 学生表21(学号 char(7) not null primary key ,
姓名 char(10) ,性别 char(2) default '男' ,
年龄 tinyint check (年龄>=15 and 年龄<=30),
所在系 char(20) ,
check((性别='男' or 性别='女') and len(姓名)<=8) )
说明： len(串) 测试串的长度,如测试注册时的账号和密码长度，要求用户名不低于6个字符，密码不低于8个字符.
条件： len(用户名)>=6 and len(密码)>=8
(注：如果约束搞不定的条件，可以考虑触发器)
7) 关系/表间完整性约束
比方：网上订购的8斤水果，而我们收到了5.5斤，发和收方的数据不一致，被破坏了完整性。在数据库，一表的数据在数据对应依赖于另一个表，如果缺失和破坏了这个依赖，就发生完整性错误。如：在图书销售表中出现事先不存在的书店编号. 在成绩表中出现根本不存在于学生表的人。
A表中的key1列属于主码PK,在b表中也有一个同名的key1列，但不是主码. 如果要让b表的key1的值参考a表中的key1的值事先存在。此时在b表中key1叫参照于a表的外键/外码FK。
如：成绩表在编辑插入数据时，学号不是主码，应该设置为指向学生表的主码学号。此时，成绩表的学号是参考学生表的外码FK
说明：在e-r图中，主属性、主要其它非主属性、表间约束都要表示清楚.
实例一：
建立教材上三表:学生,成绩,课程(要求使用命令实现) 注意:要求以学生为父表,完成三表之关的约束关系.
立足于成绩表时，把成绩表的学号参考学生表的学号，把成绩表中的课程号参考课程表的课程号.
完成约束后，不存在于学生表的人，不存在于课程表的课程，就不能在成绩表中出现.
create table 学生表(学号 char(7) not null primary key,姓名 char(10) not null ,性别 char(2) ,年龄 tinyint,所在系 char(20))
create table 课程表(课程号 char(6) not null primary key,课程名 char(20) not null,学分 tinyint,学期 tinyint)
create table 成绩表(
学号 char(7) not null,课程名 char(20) not null,成绩 tinyint,
primary key(学号,课程号),
foreign key(学号) references 学生表(学号),
foreign key(课程号) references 课程表(课程号)
)
说明：一般表主属性是一列，有时有多列组成PK（PK可是属性，也可以是属性组）. PK是属性组时，要独立于列来实现主码约束。
比如： primary key(学号,课程号)
完成后，在成绩表不允许同一个学生选两次同一门课
实例二：作业上的图书管理三个表
create table 图书表(
书号 nchar(6) primary key,书名 nvarchar(30) not null,
第一作者 nchar(10) not null,出版日期 smalldatetime,
价格 numeric(4,1) )
create table 书店表(书店编号 nchar(6) primary key,
店名 nvarchar(30) not null,
电话 nchar(8) check(电话 like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),地址 varchar(40),邮政编码 nchar(6) )
create table 图书销售表(
书号 nchar(6) not null,书店编号 nchar(6) not null,
销售日期 smalldatetime not null,
销售数量 tinyint ,
primary key(书号,书店编号,销售日期),
foreign key(书号) references 图书表(书号),
foreign key(书店编号) references 书店表(书店编号) )
说明：primary key(书号,书店编号,销售日期) 三个属性合起来才能唯一确定某一笔销售
再如：定义连锁超市的销售记录
Primary key(商品编号,超市号,销售员号,销售时间)
2 关系代数的理论与应用
1）概念
对关系进行集合代数的运算，三要素是：运算符，运算对象，运算结果;
关系操作是集合操作方式，一次一集合
（关系操作如查询，结果是元组集，把这些结果以view视图方式呈现给用户）
2）常用的4运算
(1)集合运算-并、交、差：
union 并 两个表查询结果相并时列要相同，一一对应
例:从学生表中把姓李和杨的人查出来
select 学号,姓名,年龄,家庭住址
from 学生表 where LEFT(姓名,1)='李' or LEFT(姓名,1)='杨'
例：从学生表查出姓李，再从学生表2中查出姓杨的，合成一个视图
(select 学号,姓名,年龄,家庭住址
from 学生表 where LEFT(姓名,1)='李')
union
(select 学号,姓名,年龄,家庭住址
from 学生表2 where LEFT(姓名,1)='杨')
例：从学生表查出所有女生，合并学生表2中的姓杨的人
(select 学号,姓名,性别,年龄,家庭住址
from 学生表 where 性别='女' )
union
(select 学号,姓名,年龄,家庭住址
from 学生表 where LEFT(姓名,1)='杨')
不对，两个子集数据类型不同（上面那个多一个性别）
改为：
(select 学号,姓名,性别,年龄,家庭住址
from 学生表 where 性别='女' )
union
(select 学号,姓名,性别,年龄,家庭住址
from 学生表 where LEFT(姓名,1)='杨')
except 差
从学生表中查出数据减去在学生表2中存放的数据
select 学号,姓名,性别 from 学生表
except
select 学号,姓名,性别 from 学生表
intersect 交
例：把学生表中年龄19以上人与学生表2中的李姓人求交集
select 学号,姓名,性别,年龄 from 学生表 where 年龄>=19
intersect
select 学号,姓名,性别,年龄 from 学生表2 where LEFT(姓名,1)='李'
（2）专门的关系运算
选择：
投影：
连接：
关系乘（广义的笛卡尔积）：
关系除
(3)比较运算：select 5>3
(4) 逻辑运算：与，或，非
C: 数据库中
与 a && b a and b (a,b同时成立，结果才成立)
或 a|| b a or b (a,b中至少一个成立，结果就成立)
非 ! a not a (求反)
说明：以上四大类，如果仅从关系数据库专业角度看，只分两大类：传统的集合运算，专业的关系运算
3 数据操作
1） 在SQLSERVER与其它计算机语言结合时，常用的关系操作分：
查询数据操作，非查询数据操作两大类
数据操作功能：（1）查询 query ： select
（2）非查询 noquery ：insert ，update ，delete 等
3） 讲查询操作之前补讲几个常用函数相关问题
（1）时间相关的函数
日期美式: mm/dd/yy
中国人常用:Yyyy-mm-dd 2012-09-26 20:04:07.345
getdate( ) 返回系统时间，服务器的时间
year(日期时间 ) 返回一个日期的年份数
month（日期 ） 返回月份数
day(日期 ) 返回几号
例： select GETDATE(),YEAR(getdate()),MONTH(getdate()),
DAY(getdate())
例：查询出学生表中今天有哪些人过生日
select 姓名,生日 from 学生表
where MONTH(生日)=month(GETDATE()) and
DAY(生日)=day(GETDATE())
例：提前3天预警哪些人过生日，以便公司安排慰问
where MONTH(生日-3)=month(GETDATE()) and
DAY(生日-3)=day(GETDATE())
（课后思考：例：把最近7天要过生日的所有人列出来
dateadd( ) 函数在日期中添加或减去指定的时间间隔。
格式：DATEADD(datepart,number,date)，
其中：date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。
datepart 参数可以是下列的值：
DATEDIFF( ) 两个日期之间的差异。它适用于任何可以解析为time、date、smalldatetime、datetime、datetime2或datetimeoffset值的表达式。
格式：DATEDIFF ( datepart , startdate , enddate )
上例：可用于于根据商品的生产日期和保质提前提示打折销售。
(2)字符串常用操作函数
注意：sqlserver是允许字符串直接相加的
Left( )
Right( )
Substring(s,m,n ) 从S串第m个字符开始取出后面n个字符
Trim( )：LTRIM(S)，RTRIM（s）去左右多余的空格
REPLACE() replace(original-string，search-string，replace-string)
例子：
select 姓名+'的身高是'+CONVERT(char(4), 身高)+'米' from 学生表 where 学号='2009010108'
select LEFT(家庭住址,2),RIGHT(家庭住址,2),SUBSTRING(家庭住址,3,4) from 学生表
select LEFT(家庭住址,2),RIGHT(Rtrim(家庭住址),2),SUBSTRING(家庭住址,3,4) from 学生表
select REPLACE(' ab 中国 ',' ','')
替换的办法实现删除所有空格
（3）类型转换函数
Convert(目标类型，源值[,n] )
例题：把日期标准格式转换中国格式
select 出生日期,CONVERT(char(10),出生日期,21) from 学生表
(课后：例：返回杨春的数据：
杨春的生日是1987-12-02，身高是1.77米
例：查出男生占全班人数的百分比，如45.6%
select CONVERT(char(4), convert(numeric(4,1),((select COUNT(性别) from 学生表 where 性别='男')*1.0/
(select COUNT(性别) from 学生表))*100)) +'%'
