一，数据库设计
设计数据库的步骤：
1.需求分析:分析客户的业务和数据库处理需求。
2.概要设计：绘制数据库的E-R图，设计人员和客户之间进行沟通，确认需求的正确性和完整性。
3.详细设计阶段：将E-R图转换为多张表进行设计。
映射基数
一对一 1：1
一对多 1：N
多对一 N:1
多对多 M:N
实体关系
矩形表示实体集
椭圆表示属性
菱形表示联系集
第一范式：
第一范式的目标是确保每列的原子性，每列都不可再分的最小数据单元。
第二范式：
第二范式在第一范式的基础上更进一层，其目标是确保表中的每列都和主键相关。
第三范式：
第三范式在第二范式的基础上更进一层，第三范式的目标是确保每列都和主键列直接相关联，而不是间接相关。
二,数据库的实现
创建数据库：
use master
go
if exists(select * from sysdatabases where name='Myschool')---首先检查是否有同样的数据库
drop database Myschool
create database myschool
on
(
name=''--文件逻辑名
filename=''--文件物理名
size=5mb---文件的初始大小
maxsize=100mb---文件增长的最大值
filegrowth=15%----文件的增长率
)
建立数据库表
use myschool
go
if exists(select * from sysobjects where name='student')-----首先检查是否有同样的表
drop table student
create table student
(
......
)
添加约束
alter table student ---添加主键约束
add constraint pk_studentNo primary key(student)
alter table student ----添加唯一约束
add constraint UQ_identityCard UNIOE(identityCard)
alter table student -----添加默认约束
add constraint DF_address default('地址不详')
alter table student -----添加检查约束
add costraint CK_BornDate Check(borndate>=1980-01-01)
alter table Result ---外键约束
add constraint FK_studentNo
foreign key(studentno)References student(studentNo)
SQL编程
局部变量
声明变量
declare @name varchar(20)
declare @age int
set @name='张三'---给变量赋值
set @age=18
查看于张三相邻的学号
declare @studentno int
declare @name nvarchar(20)
select @studentno=studentno from student
where studentname=@name
select * from student
where(studenno=@studentno+1)or(studentno=@studentno-1)
全局变量
@@error --最后一个T-sql错误号
其他也有写
数据库类型转换
cast() 和 convare()函数
'姓名:'+@name+',性别:'+@sex+',年龄:'
+cast(@age as varchar)
--+convert(varchar,@age)
--两个转型函数:
1.convert -- 语法: convert(类型,数据)
2.cast -- 语法: case(数据 as 类型)
区别
convert（narchar(20),'2016-6-9',120）--日期
case
when 条件1 then 结果1
when 条件2 then 结果2
end
insert into shuying(Datet,sy)values
('2016-1-2','赢'),
('2016-1-3','赢'),
('2016-1-2','输'),
('2016-1-3','输')
select * from shuying
select Datet as 日期,
sum(case when sy='赢'then 1 else 0 end)as 胜,
sum(case when sy='输' then 1 else 0 end)as 负
from shuying
group by Datet
高级查询
简答子查询
查询张三年龄大的人
select studentno,studentName,sex,bordate,address from student
where Borndate>
(
select Borndate from studet where studentname='张三'
)
go
用表连接来用子查询
select studentname from student where studentno=
(
select studentno from result
inner join subject on result.subjectno=subject.subjectno
where studentResult=60 and subjectname='java logic'
)
go
in 和not in
in子查询使用in关键字可以使父查询匹配子查询返回的多个单列值。
ont in
和子查询相反的结果，返回列值。
exists和not exists
if exists 如果子查询的结果非空，则返回exists（子查询）将返回true否则返回false
not exists和in一样可以在前加not表示不存在。
子查询注意事项：
子查询语句可以嵌套在SQL语句中任何表达式出现的位置
在select语句中，子查询可以被嵌套在select语句的列，表和查询条件中，即select子句，from子句，where子句，group by子句，having子句。
在子查询的select子句中不能出现TEXT,NTEXT或IMAGE数据类型的列。
事物：
事物是一种机制，一个操作序列，它包含了一组数据库指令。
事物有四个属性，原子性，一致性，隔离性，持久性。
事物分类：
事物的使用示例：
视图
使用视图的注意事项：
视图是一个虚拟表，通常是作为执行查询的结果而创建的。
1.每个视图可以使用多个表
2.与查询相似，一个视图可以嵌套另一个视图，但最好不要超过三层
3.视图定义中的select语句不能包括：
>order by子句除非在select语句的选择列表中也有一个TOP子句
>into 关键字
>引用临时表或变量表
索引
索引分类有：
，唯一索引：唯一索引不允许两行具有相同的索引值
，主键索引：在数据库关系图中为表定义一个主键索引，主键索引是唯一索引的特殊类型。
，聚集索引:在一张表中聚集索引只能有一个，默认为主键。
，非聚集索引：非聚集索引建立在索引页上，查询数据时可以从索引中找到记录存放的位置。非聚集索引最多有254个
，复合索引：在创建索引时并不是对其中一列创建索引，于主键一样可以将多个列组合作为索引。
，全文索引：全文索引是一种特殊类型的基于标记的功能性索引。
create unique index index_name--创建索引
on table_name
with fillfactoe=30--填充因子
储存过程
1.模式化程序设计
只需要创建一次储存过程并将其存储在数据库中。
2.执行速度快效率高
3.减少网络流量
4.具有良好的安全性
用户自定义存储过程
创建不带参数的存储过程
Create proc usp_selectstu As
SelectStudentName,Gender,GradeId,Phone
fromdbo.Student
创建带入参数的存储过程
Create proc usp_stuInfo
@gradeid int=2
As
Select * from student where gradeId=@gradeid
调用存储过程：exec usp_stuInfo 2
创建带出参数
createprocusp_selectGrade@namenvarchar(10),@gradeidintoutput
As
Select@gradeid=gradeidfromstudentwherestudentname=@name
print@gradeid
调用存储过程
declare@idint
execusp_selectGrade'李小龙',@id output
数据库分页：
假设现在有这样的一张表：
CREATE TABLE test
(
id int primary key not null identity,
names varchar(20)
)
然后向里面插入大约1000条数据，进行分页测试
假设页数是10，现在要拿出第5页的内容，查询语句如下：
--10代表分页的大小
select top 10 *
from test
where id not in
(
--40是这么计算出来的：10*(5-1)
select top 40 id from test order by id
)
order by id
原理：需要拿出数据库的第5页，就是40-50条记录。首先拿出数据库中的前40条记录的id值，然后再拿出剩余部分的前10条元素
第二种方法：
还是以上面的结果为例，采用另外的一种方法
--数据的意思和上面提及的一样
select top 10 *
from test
where id >
(
select is null(max(id),0)
from
(
select top 40 id from test order by id
) A
)
order by id
原理：先查询前40条记录，然后获得其最id值，如果id值为null的，那么就返回0
然后查询id值大于前40条记录的最大id值的记录。
这个查询有一个条件，就是id必须是int类型的。
第三种方法：
select top 10 *
from
(
select row_number() over(order by id) as rownumber,* from test
) A
where rownumber > 40
原理：先把表中的所有数据都按照一个rowNumber进行排序，然后查询rownuber大于40的前十条记录
这种方法和oracle中的一种分页方式类似，不过只支持2005版本以上的
第四种：
存储过程查询
创建存储过程
alter procedure pageDemo
@pageSize int,
@page int
AS
declare @temp int
set @temp=@pageSize*(@page - 1)
begin
select top (select @pageSize) * from test where id not in (select top (select @temp) id from test) order by id
end
执行存储过程
exec 10,5
