代码实现数据库的创建和查询（随便写点，只为记录自己的成长过程）

第一本书快要学完了，时间过得飞快！

  这是这前三章学到的，自己来总结下！

一.设计数据库的步骤

  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:projectLibrary.mdf',
size=5,
filegrowth=15%
)
log on
(
name='Library_log',
filename='D:projectLibrary_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:projectMySchool.mdf',
size=10,
filegrowth=20%
)
log on
(
name='MySchool_log',
filename='D:projectMySchool_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