数据库三范式：
第一范式：
任何给定行的列必须是只包含一个值；
表中的每一行必须有相同数量的列；
表中的每一行必须是唯一的即是不相同的；
第二范式：
必须满足第一范式；
表中的所有非主键必须依赖整个主键；
第三范式：
必须满足第二范式；
表中的所有非主键必须相互独立；
数据表字段之间的关系：
主键和外键
一对多，一对一，多对多
数据建模
1 工具：Sysbase PowerDesigner , Borland Together , Rose, MS Visio
2 概念模型：概念数据模型也称信息模型，它以实体－联系(Entity-RelationShip,简称E-R)理论为基础，并对这一理论进行了扩充。它从用户的观点出发对信息进行建模
3 物理模型：数据库物理结构模型
4 正向工程：从物理模型产生一个数据库, 或产生数据库脚本，这是正向工程
5 逆向工程：从数据库映射为物理模型
1 创建数据库
create database AA
on
(
name='aa_data',filename='C:\aa.mdf',size=10,filegrowth=5
)
log on
(
name='aa_log', filename='c:\aa.ldf', size=10, filegrowth=5
)
2 备份数据库: backup database AA to disk ='E:\aa.dat'
还原数据库: restore database AA from disk =’E:\aa.dat’
3 创建表: create table BB ( a1 int primary key, a2 nvarchar(20) )
约束：主键 primary key
自增 identity(1,1)
不能为空字段 not null
默认值 default(1)
外键 id2 int, foreign key (id2) references table1
唯一值 unique
检查 id int check(id>100 and id<200)
4 数据库增删改查
添加：insert into 表(字段1，...) values(值1, ...)
修改：update 表 set 字段1 = 值1 , 字段2 = 值2 where 主键字段 = 值
删除：delete from 表 where 主键字段 = 值
查询：select * from 表
create table Student
(
sid int primary key,
sname nvarchar(20),
sex nvarchar(2),
birthday datetime,
class nvarchar(10),
pwd nvarchar(10)
)
go
create table Course
(
cid int identity(1,1) primary key,
cname nvarchar(20)
)
go
create table Score
(
sid int,
cid int,
score int,
primary key(sid,cid)
)
go
insert into Course values('C#')
insert into Course values('English')
insert into Student values(1001,'张三','男', '1990-1-12','一班','123')
insert into Student values(1002,'李四','女', '1990-4-20','一班','456')
insert into Student values(1003,'王五','男', '1991-10-11','二班','789')
insert into Student values(1004,'赵六','男', '1992-8-5','二班','101')
insert into Score values(1001,1,65)
insert into Score values(1001,2,60)
insert into Score values(1002,1,50)
insert into Score values(1002,2,40)
insert into Score values(1003,1,75)
insert into Score values(1003,2,60)
insert into Score values(1004,1,72)
insert into Score values(1004,2,45)
5 查询的多种形式:
查询指定字段
--查询学生姓名和学号
select sname,sid from student
带多条件的查询
--查询学号是1001的学生
select * from student where sid=1001
--查询出生在1991-1-1之后所有男生信息
select * from student where sex='男' and birthday>'1991-1-1'
查询排序结果 order by desc 或 asc
--按学号降序查询学生信息
select * from student order by sid desc
--按性别和班级升序查询学生信息
select * from student order by sex,class
范围查询 between and
--查询出生在1990-1-1和1991-1-1的学生
select * from student where birthday between '1990-1-1' and '1991-1-1'
模糊 like '%a%'
--查询名字中带“五”字的学生
select * from student where sname like '%五%'
消除查询的重复项 distinct
--查询总共有哪些班级
select distinct(class) from student
统计查询 count() max() sum() avg()
--统计学生总人数
select count(sid) from student
--统计学生登录信息是否合法
select count(sid) from student where sid=1001 and pwd='123'
--统计总分和平均分
select sum(score)，avg(score) from score
--统计学号是1001的平均分
select avg(score) from score where sid=1001
分组统计查询 group by
--统计每个班的人数
select count(sid),class from student group by class
--统计男女生人数
select count(sid),sex from student group by sex
--统计每个班的男女生人数
select count(sid),sex,class from student group by sex,class
--统计每个班男生的人数
select count(sid),class from student where sex='男' group by class
--统计每门课程的总分和平均分
select sum(score), avg(score),cid from score group by cid
--统计每门课最高分
select max(score),cid from score group by cid
--统计平均分及格的学生
select avg(score),sid from score group by sid having avg(score)>59
内连接: 列出与连接条件匹配的数据行
select * from 表1 inner join 表2 on 条件
自然连接查询
--查询每个学生的学号，姓名，班级，成绩
select student.sid,student.sname,student.class,score.score from student inner join score
on student.sid = score.sid
--查询每人每门课程的考试成绩
select student.sid,student.sname , course.cname, score.score
from student inner join score on student.sid=score.sid
inner join course on score.cid = course.cid
--查询张三的各科考试成绩
select student.sid,sname,score,cname from student inner join score on
student.sid = score.sid
inner join course on
score.cid = course.cid
where student.sname='张三'
外连接: 查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行
select * from 表1 left outer join 表2 on 条件
--用左外连接 查询每个学生的学号，姓名，班级，成绩
select student.sid,student.sname , course.cname, score.score
from student left join score on student.sid=score.sid
left join course on score.cid = course.cid
--查询没有参加考试的学生信息
select student.sid, score.score from student left outer join score
on student.sid = score.sid
where score.score is NULL
交叉连接: 交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积
select a1, a2 from 表1 CROSS JOIN 表2 ORDER BY a1
嵌套查询: select * from talbe1 where id = (select .... )
--查询最高分的学生
select sname from student where sid in (select sid from score where score = (select max(score) from score))
--查询总分最高的学生学号
select sid from (select top 1 sum(score) as s,sid from score group by sid order by s desc) a
--统计每门课程最高分的学生学号
select a.sid, a.cid,a.score from score a,(select max(score) as m,cid from score group by cid) b
where a.score = b.m
and a.cid = b.cid
临时表变量:
--查询1001的总分排名
declare @temp table(pid int identity(1,1),s int, sid int)
insert into @temp select sum(score) s,sid from score group by sid order by s desc
select pid from @temp where sid=1001
6 流程控制：
赋值语句：declare @i int set @i=0
条件语句：if 条件为真
begin ... end
else
begin ... end
循环语句：while 条件为真
begin ... end
case when 语句：
case 字段名 when 字段值 then 显示内容 end
case when 条件 then 输出1 else 输出2 end
--查询判断每人每门课程是否及格
select student.sid, cname, score ,case when score<60 then '不及格' else convert(nvarchar(4),score) end
from course,score,student where course.cid = score.cid and student.sid=score.sid
7 视图
创建单表视图
create view 视图名
as
select * from employees
select * from 视图名 where sid =1001
update 视图名 set
8 存储过程
create proc 存储过程名
@i int, --输入参数
@j int output --输出参数，可选
as
begin
...
return 1 --返回值，可选
select * from ...
end
执行存储过程:
exec sp --输入参数
exec sp @输入参数, @输出参数 output
declare @返回值 int
exec @返回值 = sp 参数列表 -- 返回值
--使用存储过程判断用户登录信息
create proc sp_login
@sid int,
@pwd nvarchar(10),
@r nvarchar(20) output
as
declare @i int
set @i = (select count(1) from student where sid=@sid and pwd=@pwd)
--return @i --0 , 1
if @i>0
--print '登录成功'
set @r = 'Y'
else
--print 'no'
set @r = 'N'
declare @i int
exec @i = sp_login 1001,'123'
print @i
--使用存储过程实现主从表及联删除
create proc sp_delete
@sid int
as
delete from student where sid=@sid
delete from score where sid=@sid
--使用存储过程添加学生信息
create proc sp_insert
@sid int,
@sname nvarchar(10),
@sex nvarchar(10),
@birthday datetime,
@class nvarchar(10),
@pwd nvarchar(10),
@r nvarchar(10) output
as
if not exists (select sid from student where sid=@sid)
begin
insert into student values(@sid,@sname,@sex,@birthday,@class,@pwd)
set @r = '添加成功'
end
else
set @r = '重复添加'
--使用存储过程添加新课程 ，并输出新课程的ID
create proc sp_insertCourse
@cname nvarchar(10)
as
insert into course values(@cname)
return @@identity
--实现数据表分页查询
create proc sp_page
@page int
as
declare @count int
set @count = (select count(orderid) from orders)
select top 5 * from (select top (@count-5*(@page-1)) * from orders order by orderid desc) a order by a.orderid
create proc sp_page
@page int,
@table varchar(10),
@orderby varchar(10),
@size int
as
declare @sql varchar(500)
set @sql = 'declare @i int'
set @sql = @sql + ' set @i = (select count('+ @orderby +') from ' + @table + ')'
set @sql =@sql + ' select top '+ str(@size) +' * from (select top (@i-'+ ltrim(str((@page-1)*@size)) +') * from ' + @table + ' order by '+ @orderby +' desc) a order by a.' + @orderby
print @sql
exec(@sql)
9 函数
字符串：
字符串长度 len('...')
获取字符串 str('...') ,返回的是char
截取字符串 substring(开始位置,长度)
随机数 Rand()
时间：
添加时间间隔 dateadd(day, 2, getdate())
时间差 datediff(day, 时间1, 时间2)
获取年,月,日 datepart(yy,getdate())
--按年份统计人数
select count(sid),datepart(yy,birthday) from student group by datepart(yy,birthday)
类型转换：
convert(nvarchar(10), 123), cast(124 as nvarchar(10))
自定函数：
create function 函数名称(@i int, @j int) returns int
as
begin
...
return
end
--定义函数 输入姓名后返回学号
create function fun(@sname nvarchar(10)) returns int
as
begin
declare @i int
set @i = (select sid from student where sname=@sname)
return @i
end
--查询学号，课程号，分数 同时在分数栏将<60的成绩直接输出为"不及格"
create function fun3(@score int) returns nvarchar(10)
as
begin
declare @str nvarchar(10)
if @score<59
set @str = '不及格'
else
set @str = convert(nvarchar(10), @score)
return @str
end
--在同一列，输出 Employees 表的 lastname 和 firstname 字段
create function fun2(@firstname varchar(10),@lastname varchar(10)) returns varchar(40)
as
begin
return @firstname + '-' + @lastname
end
select employeeid, dbo.fun2(firstname,lastname) as n,title from employees
--计算 orders details 表 每条订单的总价
create function fun3(@unitprice money, @quantity smallint, @discount real) returns money
as
begin
if @discount = 0
set @discount = 1
return @unitprice * @quantity * @discount
end
select *, dbo.fun3(unitprice,quantity, discount) from [order details]
9 游标
declare 游标名称 cursor for select * from 表
open 游标名称
-- declare @i int
fetch 游标名称 --或 fetch from 游标 into @i
while @@fetch_status = 0
begin
fetch next from 游标名称
end
close 游标名称
deallocate 游标名称
@@fetch_status 的 意义
0 FETCH 语句成功。
-1 FETCH 语句失败或行不在结果集中。
-2 提取的行不存在
--输出以下效果
--1.1001,张三,一班
--2.1002,李四,一班
--3.1003,王五,二班
--4.1004,赵六,二班
declare cur cursor for select sid,sname,class from student
open cur
declare @i int
set @i = 1
declare @sname nvarchar(10),@class nvarchar(10),@sid int
fetch from cur into @sid,@sname,@class
while @@fetch_status =0
begin
print convert(varchar(10),@i) + '.' + convert(varchar(10),@sid) + ',' + @sname + ',' + @class
set @i = @i + 1
fetch from cur into @sid,@sname,@class
end
close cur
deallocate cur
--给所有分数<60的人加送10分
declare cur2 cursor for select * from score
open cur2
declare @sid int, @score int,@cid int
fetch from cur2 into @sid,@cid,@score
while @@fetch_status = 0
begin
if @score < 60
update score set score = @score + 10 where sid=@sid and cid=@cid
fetch from cur2 into @sid,@cid,@score
end
close cur2
deallocate cur2
select * from score
delete from score
--找出重名的人，并使用合适的方式返回结果数据
create proc sp_find
as
create table #table1
(
sid int,
sname nvarchar(10)
)
declare @s nvarchar(100)
declare cur4 cursor for select sid,sname from student
open cur4
declare @sid int , @sname nvarchar(10),@count int
fetch from cur4 into @sid,@sname
while @@fetch_status = 0
begin
set @count = (select count(1) from student where sname = @sname)
if @count > 1
insert into #table1 values(@sid, @sname)
fetch from cur4 into @sid,@sname
end
close cur4
deallocate cur4
select * from #table1
exec sp_find
10 触发器
create trigger 触发器名 on 表名 for insert --或delete
as
begin
--select * from inserted
insert into 相关表
end
--级联删除学生信息
create trigger t_delete on student for delete
as
begin
declare @sid int
set @sid = (select sid from deleted)
delete from score where sid = @sid
end
--添加学生信息，同时设置每人的考试成绩默认为0
create trigger t_insert on student for insert
as
begin
declare @sid int
set @sid = (select sid from inserted)
declare cur1 cursor for select cid from course
open cur1
declare @cid int
fetch from cur1 into @cid
while @@fetch_status = 0
begin
insert into score values(@sid,@cid,0)
fetch from cur1 into @cid
end
close cur1
deallocate cur1
end
--修改学生学号信息
create trigger t_update on student for update
as
begin
declare @sid1 int,@sid2 int
set @sid2 = (select sid from inserted)
set @sid1 = (select sid from deleted)
print @sid2
print @sid1
update score set sid=@sid2 where sid = @sid1
end
11 事务
事务是指一个单元的工作，这些工作要么全做，要么全部不做。作为一个逻辑单元，必须具备四个属性：自动性、一致性、独立性和持久性
自动提交事务
每条单独的语句都是一个事务。
显式事务
每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。
原子性
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
一致性
事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。
隔离
由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。
持久性
事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。
begin tran
declare @i int
insert into Student values(1001,'张三')
set @i = @@error
delete from student where sid = 1004
set @i = @i + @@error
if @i>0
rollback tran
else
commit tran
用try … catch 写
begin tran
begin try
insert into Student values(1001,'张三','男', '1990-1-12','一班','123')
delete from student where sid= 1004
end try
begin catch
rollback tran
end catch
数据库复习笔记
世上没有绝望的处境，只有对处境绝望的人...