第一本书快要学完了，时间过得飞快！
这是这前三章学到的，自己来总结下！
一.设计数据库的步骤
1.收集信息 2.标识实体 3.标识每个实体需要存储的详细信息 4.标识实体之间的关系
三大范式;
1.第一范式：确保每列的原子性
2.第二范式：在第一范式的基础上，确保表中的每列都和主键相关
3.第三范式：在满足第二范式的基础上，确保除主键列直接相关，而不是间接相关
E-R图：
矩形代表实体 菱形代表关系 椭圆代表属性
Entity:实体 normal:正常的 model:模型 selationship:关系 state:状态 promation:提升
formate:格式化 diagram:图表 guest:客人 hotel:旅馆
二.
1.创建数据库 Use master If exists(select * from sysdatabases where name=’MySchool’) Drop database MySchool Create database MySchool go ( Name=’MySchool_data’ , ---逻辑文件名 Filename=’D:\新建文件夹\MySchool_data.mdf ’ , ---物理文件名 Size=5, Filegrowth=10, Maxsize=50 ) Log on ( Name=’MySchool_log’, Filename=’D:\新建文件夹\MySchool_log.ldf’, Size:5, Filegrowth=15%, Maxsize=50 ) Go 2.创建表 Use MySchool If exists(select * from sysubjects where name=’student’) Drop table student Create table student ( Stuid int indentity(1,1) not null, Stuname varcchar(20) not null, Sex char(1) not null, Stuage int not null, Grade int not null, Address varchar(255) null, Phone varchar(50) null, IdentityCrad varchar(18) not null, BornDate datetime not null ) Go 3.创建约束 （1）主键约束 Alter table student Add constraint pk_stuid primary key (stuid) （2）检查约束 Alter table student Add constraint ck_BornDate check(BornDate>=’1980-01-01’) （3）默认约束 Alter table studnet Add constraint df_address default(‘地址不详’) for Address （4）唯一约束 Alter table student Add constraint uq_IdentutyCrad unique(IdentityCrad) （5）外键约束 Alter table result Add constraint fk_stuid Foregin key(stuid) references student(stuid) 4.删除表，删除约束，删除数据库 Drop table 表名 Drop database 数据库名 Alter table 表名 Drop constraint 约束名 Create:创造 drop:删除 primary:主要的 exists:存在 alter:改变 growth:增长 constrint:约束 Unique:唯一 foregin:外键 references:引用 use master go if exists(select * from sysdatabases where name='Library') drop database Library create database Library on primary ( name='Library', filename='D:\project\Library.mdf', size=5, filegrowth=15% ) log on ( name='Library_log', filename='D:\project\Library_log.ldf', size=1, filegrowth=15% ) go use Library --先将数据库定位到Library go if exists(select * from sysobjects where name='Book') drop table Book create table Book /*--创建图书信息表*/ ( BID varchar(10) primary key not null, BName varchar(20) not null, Author varchar(20) null, PubComp varchar(20) null, PubDate datetime not null, BCount int not null, Price money not null ) go use Library --先将数据库定位到Library go alter table Book --必须以ISBN开头 add constraint ck_BID check(BID like 'ISBN%') go use Library --先将数据库定位到Library go alter table Book --检查出版日期是否小于当前日期 add constraint ck_PubDate check(PubDate<=getdate()) go use Library --先将数据库定位到Library go alter table Book --现存数量必须大于等于1 add constraint ck_BCount check (BCount>=1) go use Library --先将数据库定位到Library go alter table Book add constraint ck_Price check (Price>0) go use Library --先将数据库定位到Library go --检查是否有读者信息表 if exists(select * from sysobjects where name='Reader') drop table Reader create table Reader /*--读者信息表*/ ( RID varchar(10) primary key not null, RName varchar(10) not null, LendNum int not null, RAddress varchar(50) null ) go use Library --先将数据库定位到Library go alter table Reader add constraint ck_LendNum check (LendNum>=0) go use Library --先将数据库定位到Library go if exists (select * from sysobjects where name='Borrow') drop table Borrow create table Borrow /*创建图书借阅表*/ ( RID varchar(10) not null, BID varchar(10) not null, LendDate datetime not null,primary key(RID,BID,LendDate), WillDate datetime null, ReturnDate datetime null ) go /*--添加外键约束--*/ alter table Borrow add constraint fk_RID foreign key (RID) references Reader(RID) go /*--添加外键约束--*/ alter table Borrow add constraint fk_BID foreign key (BID) references Book(BID) go /*--添加默认约束--*/ alter table Borrow add constraint df_LendDate default('getdate()') for LendDate go /*--添加检查约束--*/ alter table Borrow add constraint ck_WillDate check (WillDate>=LendDate) go /*--添加默认约束--*/ alter table Borrow add constraint df_WillDate default (dateadd(mm,1,getdate())) for WillDate go use Library --先将数据库定位到Library go if exists (select * from sysobjects where name='Penalty') drop table Penalty create table Penalty /*创建罚款记录表*/ ( RID varchar(10) not null, BID varchar(10) not null, PDate datetime not null,primary key(RID,BID,PDate), PType int not null, Amount money not null ) go /*--添加外键约束--*/ alter table Penalty add constraint fk1_RID foreign key(RID) references Reader(RID) go /*--添加外键约束--*/ alter table Penalty add constraint fk1_BID foreign key(BID) references Book(BID) go /*--添加默认约束--*/ alter table Penalty add constraint df_PDate default('getdate()') for PDate go /*--添加检查约束--*/ alter table Penalty add constraint ck_PType check (Ptype=1 or Ptype=2 or Ptype=3 ) go /*--添加检查约束--*/ alter table Penalty add constraint ck_Amount check (Amount>0) go /*--添加一列--*/ alter table Book add BTotal int 三．局部变量和全局变量 1.局部变量 局部变量的名称必须以@作为前缀 局部变量的声明语法： Declare @variable_name DataType variable_name:局部变量的名称 DateType:局部变量的数据类型 例： Declare @name varchar(32) --声明一个存放姓名的变量 name Declare @number int --声明一个存放学号的变量 number 给局部变量赋值有两种方法： Set @variable_name=value Select @variable_name=value Set 和 select 的区别： Set只能支持一个变量的赋值 Seelct 能同时支持多个变量的赋值 2.全局变量 全局变量是以@@前缀命名的 全局变量都是系统定义的，不能自己命名 @@servicename :本地服务器的名称 @@error : 最后一个T-SQL错误的错误号 @@rowcount: 上一个SQL语句受影响的行数 @@version: SQL Server的版本信息 3.数字转换为字符串 Cast(@variable_name as 数据类型) Convert(数据类型,@variable) Cast()函数和convert()函数用于将某种数据类型的表达式转换为另一种数据类型的表 达式。与cast()函数不同的是，在将日期时间类型/浮点类型转换为字符串数据时，示 conver()函数可以通过第三个参数指定转换后的字符样式，不同的样式使转换后的数 据的显示格式不同。 4. Begin-end语句 Begin 语句或者语句块 End If-else条件语句 If 语句或者语句块1 Else 语句或者语句块2 当有多条语句时，则需要与begin-end结合使用 If Begin 语句1 语句2 end Else Begin 语句 end While(条件) Begin 语句或者语句块 [Break][continue] End Case多分支语句 语法： Case When 条件1 then 结果1 When 条件2 then 结果2 Else[ 其他结果 ] End Go指令：它是一条或多条SQL语句的集合 use master go if exists(select * from sysdatabases where name='MySchool') drop table MySchool create database MySchool on ( name='MySchool', filename='D:\project\MySchool.mdf', size=10, filegrowth=20% ) log on ( name='MySchool_log', filename='D:\project\MySchool_log.ldf', size=3, filegrowth=1, maxsize=20 ) go use MySchool go if exists(select * from sysobjects where name='Subject') drop table Subject create table Subject /*创建科目表*/ ( SubjectNo int identity(1,1) not null, SubjectName nvarchar(50) , ClassHour int , GradeID int not null ) go use MySchool go if exists(select * from sysobjects where name='Result') drop table Result create table Result /*创建成绩表*/ ( StudentNo int not null, SubjectNo int not null, ExamDate datetime not null, StudentResult int not null ) go use MySchool go if exists(select * from sysobjects where name='Student') drop table Student create table Student /*创建学生表*/ ( StudentNo int not null, LoginPwd nvarchar(50) not null, StudentName nvarchar(50) not null, Sex bit not null, GradeID int not null, Phone varchar(50), Address nvarchar(255) , BornDate datetime not null, Email varchar(50), IdentityCard varchar(18) ) go use MySchool go if exists(select * from sysobjects where name='Grade') drop table Grade create table Grade /*创建年级表*/ ( GradeID int identity(1,1) not null, GradeName nvarchar(50) not null ) go ---年级表添加主键约束 alter table Grade add constraint pk_GradeID primary key (GradeID) go ---学生表添加主键约束 alter table Student add constraint pk_StudentNo primary key (StudentNo) go --学生表添加唯一约束 alter table Student add constraint uq_IdentityCard unique (IdentityCard) go --学生表添加默认约束 alter table Student add constraint df_Address default ('地址不详') for Address go --学生表添加检查约束 alter table Student add constraint ck_BornDate check(BornDate>='1980-01-01') go --学生表添加外键约束 alter table Student add constraint fk_GradeID foreign key(GradeID) references Grade(GradeID) go insert into Subject( SubjectName, ClassHour, GradeID)values ('s1',150,1), ('s2',180,2), ('y2',200,3) --科目表添加主键约束 alter table Subject add constraint pk_SubjectNo primary key(SubjectNo) go --科目表添加检查约束 alter table Subject add constraint ck_ClassHour check(ClassHour>=0) go --科目表添加外键约束 alter table Subject add constraint fk1_GradeID foreign key(GradeID) references Grade(GradeID) go --成绩表添加主键约束 alter table Result add constraint pk1_StudentNo primary key (StudentNo,SubjectNo,ExamDate) go --成绩表添加默认约束 alter table Result add constraint df_ExamDate default(getdate()) for ExamDate go --成绩表添加检查约束 alter table Result add constraint ck_StudentResult check (StudentResult<=100 or StudentResult>=0) go --成绩表添加外键约束 alter table Result add constraint fk_StudentNo foreign key(StudentNo) references Student(StudentNo) go --添加外键约束 alter table Result add constraint fk2_SubjectNo foreign key(SubjectNo) references Subject(SubjectNo) go -- alter table Result drop constraint ck_StudentResult alter table Result alter column StudentResult decimal (5,2) alter table Result add constraint ck_StudentResult check (StudentResult<=100 or StudentResult>=0) go 第三章. ----打印直角三角形 declare @num varchar(20),@count int select @num='',@count=0 while(@count<5) begin select @num+='*',@count+=1 print @num end ----面试表 use myschool create table biao ( riqi varchar(20) not null, jieguo varchar(10) not null ) insert into biao values('2010-05-06','胜') insert into biao values('2010-05-06','胜') insert into biao values('2010-05-06','负') insert into biao values('2010-05-06','负') insert into biao values('2012-05-06','胜') insert into biao values('2012-05-06','负') insert into biao values('2012-05-06','胜') select * from biao select riqi as 日期,sum ( case when jieguo='胜' then 1 else 0 end ) as 胜,sum ( case when jieguo='负' then 1 else 0 end ) as 负 from biao group by riqi select * from student declare @name varchar(20) select @name=studentname from student where studentno=23270 print @name declare @age int select @age=DATEDIFF(YY,birthday,GETDATE()) from student where studentno=23270 print @age ----九九乘法表 declare @n int ,@m int ,@u varchar(255) select @n=1 while(@n<10) begin select @u='',@m=1 while(@m<=@n) begin select @u=@u+cast(@m as nvarchar)+'*'+cast(@n as nvarchar)+'='+CAST(@m*@n as nvarchar)+' ', @m=@m+1 0end print @u set @n=@n+1 end --上机练习3 select * from result declare @date datetime select @date=MAX(examdate) from result where studentno=23231 declare @score int select @score=studentResult from result where studentno=23231 and examdate=@date print @score if(@score>85) begin print '优秀' end else if(@score>70) begin print '良好' end else if(@score>60) begin print '中等' end else begin print '差' end --上机练习4 --查询科目编号 declare @subid int select @subid=subjectid from subject where subjectname='oop' --查询最近的考试时间 declare @date datetime select @date =MAX(examdate) from result where subjectid=@subid order by examdate --查询有没有考试不及格的同学 declare @count int select @count=COUNT(1) from result where studentresult<60 and subjectid=@subid and examdate=@date while(@count>0) begin update result set studentresult=studentresult+2 where studentresult<95 and subjectid=@subid and examdate=@date --加分后,再判定有没有及格的 select @count=COUNT(1) from result where studentresult<70 and subjectid=@subid and examdate=@date end print @count select * from result --上机练习5 create table Admins ( admin varchar(20) primary key not null, pwd varchar(10) not null ) go insert into Admins values('张三','1'), ('李四','11') declare @count int select @count=COUNT(1) from Admins where admin='张三' if(@count>0) begin update Admins set pwd='111' where admin='张三' end print @count
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
