数据库的设计与应用

数据库设计

数据库设计就是将数据库中的数据对象以及这些数据库对象之间的关系进行规划和结构化的过程

良好的数据库设计表现为：

1、 效率高

2、 便于进一步的扩展

3、 使得应用程序开发变的个容易

设计数据库的步骤：

1、 需求分析阶段

2、 概要设计阶段

3、 详细设计阶段

映射基数

映射基数表示可以通过关系与该实体关联的其他实体个数

规范设计

在数据库设计时有一些专门的规则成为数据库的规范设计

第一范式

第一范式是指确保每列的原子性，即列要分的不可再分的地步

第二范式

第二范式是指在第一范式的基础上更近一层确保表中的每列都和主键相关，即

只能有一个老大

第三范式

第三范式是指在第二范式的基础上再进一层，确保每列都和主键有直接关系而不是间接关系即每个表只等有一个老大，不能再有老二

使用三范式的好处是为了是数据库规范化使用规范化是从关系型数据库表中除去沉余数据的过程

创建数据库

use master

go

if exists(select * from sysdatabases where name='stuDB')

begin

drop database stuDB

end

create database stuDB

on primary

(

name='stuDB',

filename='e:/DBData/stuDB.mdf',

size=3mb,

maxsize=10mb,

filegrowth=20%

)

log on

(

name='stuDB_log',

filename='e:/DBData/stuDB_log.ldf',

size=2mb,

maxsize=10mb,

filegrowth=1mb

)

go

use stuDB

go

建表

if exists(select * from sysobjects where name='stuInfo')

begin

drop table stuInfo

end

create table stuInfo

(

Sname varchar(50)not null,

SNO int identity(10001,1) not null,

Sage int not null,

SCardID numeric(18,0),

Sseat smallint not null,

Saddress text

)

go

if exists (select * from sysobjects where name='stuMarks')

begin

drop table stuMarks

end

create table stuMarks

(

ExamNo varchar(7)not null,

SNO int not null,

WrittenExam int not null,

LabExam int not null

)

主数据文件：*.mdf

次要数据文件：*.ndf

日志文件：*.ldf

所有的数据文件都放在master系统数据库的sysdatabases表中

所有的表都放在系统表sysobjects表中

主键约束： PK primary key constraint

唯一约束： UQ unique constraint

检查约束： CK check constraint

默认约束： DF default constraint

外键约束： FK foreign key constraint

添加主键约束：

alter table stuInfo add constraint pk_SNO primary key(SNO)

添加唯一约束:

alter table stuInfo add constraint uq_SCardID unique(SCardID)

添加默认约束：

alter table stuInfo add constraint df_Saddress default('地址不详')for Saddress

添加检查约束：

alter table stuInfo add constraint ck_Sage check(Sage>15 and Sage<40)

添加外间约束：

alter table stuMarks add constraint fk_SNO foreign key(SNO)references stuInfo(SNO)

创建windows登录用户

Exec sp_grantlogin 'windows域名/域账户'

创建SQL登录用户

Exec sp_addlogin '账户名', '密码'

给登录用户创建数据库用户

Exec sp_grantdbaccess '登录账户', '数据库用户'

给数据库用户授权

Grant 权限on 表名 to 数据库账户

全县有 select/insert/update/delete

变量

变量是可以存储数据的对象

T-sql中的变量分为局部变量和全局变量

局部变量使用时是先声明，再赋值

全局变量是由系统定义和维护，我们可以直接使用，但一般不再定义全局变量

声明变量的语法

Declare @变量名 数据类型

给局部变量赋值的两种语法

1、 set @变量名=value

2、 select @变量名=列名 from表名

全局变量

select '最后一个T-sql错误的错误号'+@@error

select '最后一个插入的标示值'+identity

select '当前使用的语言名称'+@@language

select '可以创建的同时连接的最大数目'+convert(varchar(10),@@max_connections)

select '受上一行SQL语句影响的行数'+convert(varchar(10),@@rowcount)

select '本地服务器的名称'+@@servername

select '代计算机上的SQL服务的名称'+@@servicename

select '当前计算机上没可读的微秒数'+convert(varchar(10),@@timeTicks)

select 'SQL Server的版本信息'+@@version

输出语句：

1、 print 局部变量或字符串

2、 select 局部变量 as 自定义列名

逻辑控制语句中的beginn...end相当于Java中的'{}'符号

Case多分支语句的用法如：

Select stuNO,case

When exam<60 then 'E'

When exam between 60 and 69 then 'D'

When exam between 70 and 79 then 'C'

When exam between 80 and 89 then 'A'

Else 'A' as '等级'

From stuMarks

批处理语句

Go 是批处理的标志

批处理是以一条命令的方式来处理一组命令的过程

批处理的好处是能够简化数据库的管理提高语句的效率

事务

事务是作为单个逻辑工作单元执行一系列的操作

事务的四个属性：

1、 原子性(Atomicity) 事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行

2、 一致性(Consistency) 当事务完成时，数据必须处于一致状态

3、 隔离性(Isolation) 对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

4、 持久性(Durability) 事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

这些特性通常称为ACID

开始事务：begin transaction

提交事务：commit transaction

回滚事务：rollback transaction

事务分为：

1、 显示事务：用begin transaction明确指明事务的开始

2、 隐式事务：通过设置setimplicit_transaction on语句，将隐式事务模式设置为打开

3、 自动提交事务：这是SQL server默认模式，他将每条单独的T-sql语句视为一个事物，如果执行成功，则自动提交，否则自动回滚

索引

索引提供指针指向存储在表中的指定的数据值，然后根据指定的排列次序排列这些指针，它是SQL server编排数据库的内存方法为SQL server一中方法来编排查询数据的路线

索引页是数据库存储索引的数据的数据页

索引分为以下四种：

1、 唯一索引：唯一索引不允许两行具有相同的索引值

2、 主键索引：在数据库关系图中定义一个主键将自动创建索引，主键索引是索引的特殊类型

3、 聚集索引：在聚集索引中表中各行的物理顺序与逻辑（索引）顺序相同，但打乱了表结构

4、 非聚集索引：在非聚集索引中表中各行的物理顺序与逻辑（索引）顺序不相同而是在旁边新建一列不可见且与索引列相同的列，用指针指向对应的值，他虽不打乱表结构但他要比聚集索引慢还占空间

注意：一个表只能建一个聚集索引，但可以建(249)多个非聚集索引,设置某列为主键，该列就默认为聚集索引

创建索引的T-sql语句：

if exists(select name from sysindexes where name='ix_SON')

drop index stuMarks.ix_SON

go

create unique[*1] /clustered[*2] /nonclustered [*3] index ix_SON on stuMarks(SNO) with fillfactor[*4] =40

go

视图

视图时另一种查看数据库中一个或多个表的数据方法，它是一种虚拟表，他可以基于一个表或多个表，也可以基于其他视图、当前的额数据库或其他数据库

创建视图的T-sql语句，如

if exists(select * from sysobjects where name='newStudent')

drop view newStudent

go

create view newStudent

as select A.Sname,A.SNO,B.writtenExam,B.LabExam

from stuInfo as A right join stuMarks as B on A.SNO=B.SNO

go

触发器

触发器是当数据库服务器发生数据操作语言事件是要执行的操作，即增删改时的操作

创建触发器的T-sql语句

if exists(select * from sysobjects where name='newStudent_update')

drop trigger newStudent_update

go

create trigger newStudent_update

on newStudent instead of[*5] /for[*6] insert/delete/update

as

[*7] declare @oldSname varchar(50)

declare @newSname varchar(50)

declare @oldWrittenExam int

declare @newWrittenExam int

declare @SNO int

select @oldSname=Sname,@oldWrittenExam=WrittenExam,@SNO=SNO from deleted

select @newSname=Sname,@newWrittenExam=WrittenExam from inserted

print'您将'+@oldSname+'修改成了'+@newSname+'并将他的笔试成绩由'+convert(varchar,@oldWrittenExam)+'改为'+convert(varchar,@newWrittenExam)

update stuInfo set Sname=@newSname where SNO=@SNO

update stuMarks set WrittenExam=@newWrittenExam where SNO=@SNO

go

print'修改之前：'

select * from newStudent

update newStudent set Sname='张三',WrittenExam=56 where SNO=10002

print '修改之后：'

select * from newStudent

存储过程

存储过程是一组预编译的SQL语句，存储过程可以包含数据操作语句、逻辑控制语句和调用函数等，他可以接受参数，输出参数，返回单个或多个结果集以及返回值

存储过程可分为两大类：

1、 系统存储过程

2、 用户自定义存储过程

常用的系统存储过程

系统存储过程
说明

sp_databases
列出服务器上的所有数据库。

sp_helpdb
报告有关指定数据库或所有数据库的信息

sp_renamedb
更改数据库的名称

sp_tables
返回当前环境下可查询的对象的列表

sp_columns
回某个表列的信息

sp_help
查看某个表的所有信息

sp_helpconstraint
查看某个表的约束

sp_helpindex
查看某个表的索引

sp_stored_procedures
列出当前环境中的所有存储过程。

sp_password
添加或修改登录帐户的密码。

sp_helptext
显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。

调用存储过程用exec

在E盘下建一个文件夹bank的语句

Exec xp_cmdshell 'mkdir D:/bank',no_output

创建用户自定义的存储过程

Create proc[edure] 存储过程名

@参数1 数据类型[=默认值][output]

...

@参数n 数据类型[=默认值][output]

As sql语句

调用存储过程

Exec 存储过程名 [参数1=] 值 [output]

...

存储过程名 [参数n=] 值 [output]

[]代表可选

如果参数后面有output关键字表示参数为输出参数，即返回值，否则为输入参数,调用时如果是输出参数也要加上output关键字

处理错误信息

Raiserror(msg_id | msg_str,severity,state with option[,...n]])

msg_id：在sysmessages系统表中指定用户定义错误信息

msg_str：用户定义的特定信息，最长255个字符

severity：定义严重性级别。用户可使用的级别为0–18级

state：表示错误的状态，1至127之间的值

option：指示是否将错误记录到服务器错误日志中

一般用法的T-sql语句，如

Raiserror('及格线错误：',16,1)

--------------------------------------------------------------------------------

[*1]唯一索引

[*2]聚集索引

[*3]非聚集索引

[*4]填充因子

[*5]给视图创建触发器

[*6]给表创建触发器

[*7]主要的

 

回答人的补充   2009-08-25 15:09

----添加windows登陆帐户 exec sp_grantlogin 'windows 域名/域帐户'
--exec sp_grantlogin 'jbtraining/s26301'

--添加 sql登陆帐户 exec sp_addlogin '用户名','密码'
exec sp_addlogin 'zhangsan','123456'
--
----创建数据库用户exec sp_grantdbaccess '登陆帐户','数据库用户'
----创建2个
--use ownHomeDB
--go
--exec sp_grantdbaccess 'sa','zhangsan'
--exec sp_grantdbaccess 'lisi','zhangsanDBuser'
-- 授权 grant 权限 [on 表名] to 数据库用户
use ownHomeDB
go
-- 为zhangsanDBuser分配表users的select insert update 权限
grant select ,insert ,update on users to zhangsanDBuser
go
--给lisi分配 了创建表的权限
grant create table to lisi

 

 

 

 

 

select * from users

此表还的 添加 用户帐户 用户身份证信息 用户

select newsID,title,userName,writerDate from
users,news where users.userID = news.userID
order by newsId desc

 

--select * from result where srim in (select stuno from stuInfo where acanes>60)

select type,sum(price) as 总和 from titles
where type in('sport','trad_cook')
group by type

select type,sum(price) as 总和,avg(price) as 平均价格
from titles
group by type,pub_id
select * from titles

 

select type,avg(price) as 平均价格
from titles
where price>10
group by type
having avg(price)>80

---------------compute and compute by------
select type,price from titles
order by type
compute avg(price),sum(price),min(price)
by [type]

select type,price from titles
compute avg(price)

--------------------------------------------------------------------------------------------------------------

select sid as 考号,sNO as 学号,sname as 姓名,bir as 生日,
smath as 数学成绩 from student order by smath desc,
senglish as '英语成绩' from student order by senglish asc
--smath 降序 senglish 升序？

select sid as 考号,sNO as 学号,sname as 姓名,bir as 生日,
smath as 数学成绩 ,senglish as 英语成绩,

等级=case
when smath <60 then '不及格'
else
'及格'
end from student
--加分
declare @n int
while(1=1)
begin
select @n=count(*) from student
where smath <1
if (@n>0)
update student set smath= smath+1
else
break;
end
print '加分后的成绩'
select * from student

select sid as 考号,sNO as 学号,sname as 姓名,bir as 生日,
smath as 数学成绩,senglish as 英语成绩,
总分= (smath+senglish),
平均分=(smath+senglish)/2.0
from student
select * from student
where sname like '张%'

select * from teacher
select * from student

--内连接
select * from teacher
inner join student
on teacher.tNo = student.sNo
--左连接
select * from teacher
left join student
on teacher.tNo = student.sNo
--右连接
select * from teacher
right join student
on teacher.tNo = student.sNo
--交叉连接
select p.tname,q.sname from teacher as p
cross join student as q

--详细信息
select tname from teacher
order by tname--姓名排列
select sname from student
order by sname--学生姓名排列
select sname,sum(smath) from sdudent
order by sname
compute sum(smath) by sname

 

 

 

--聚合函数 6

create table On13
(
id int primary key,
sname varchar(20),
math int
)
insert into On13 values(1004,'孙权',5900)
select *from On13
--(1)
select max(math) as '数学成绩' from On13
--(2)
select max(math) '数学成绩' from On13
--(3)
select '数学成绩' = max(math) from On13
--(二 聚合函数)
select min(math) as '最低分数' from On13

-- (三 聚合函数)

select avg(math) as '平均成绩' from On13
--(四)
select sum(math) as ' 总分数' from On13
--(五 聚合函数)
int
select count(*) as '人数' from On13
select count(*) from On13 where math>=100
--(六 聚合函数)
bigint
select count_big(*) from On13
select max(math) as '最大值',min(math) as '最小值',sum(math) as ' 总分',avg(math) as '平均分',count(*) as '统计',count_big(*)as '统计' from On13

----------------==========================================================

 

回答人的补充   2009-08-25 15:10

use master
go
if exists(select * from sysdatabases where name='stu') -- 判断数据库 stu 是否存在
drop database stu -- 如果存在就删除
create database stu --联存在 创建
go

use stu
go
if exists (select * from sysobjects where name ='stuinfo')
drop table stuinfo
go

create table stuinfo(
stuId int identity(1,1) primary key,
stuName varchar(50) not null,
stuNo varchar(20) null,
stuSex varchar(2) null,
-- stuSeat varchar(100) null, --职务
stuAge int null,
stuAddress varchar(1000) null, --地址
)
go
insert into stuInfo
values('张秋丽','s25301','男',18,'北京')
insert into stuInfo
values('李文采','s25302','男',31,'地址不详细')
insert into stuInfo
values('李斯文','s25303','女',22,'河南新乡')
insert into stuInfo
values('欧阳俊雄','s25304','男',28,'新疆')
insert into stuInfo
values('梅超风','s25308','女',23,'南京')

use stu
go
if exists (select * from sysobjects where name ='result')
drop table result
go
create table result(
ExamNo varchar(20) null,
stuNo varchar(20) null,
weitenExam varchar(20) null,
LabExam varchar(20) null,
)
go
insert into result
values('S271811','s25303',90,58)
insert into result
values('S271813','s25302',60,90)
insert into result
values('S271816','s25301',87,82)
insert into result
values('S271817','s25304',87,45)
insert into result
values('S271818','s25308',47,87)

select * from stuInfo left join result result on stuInfo.stuno=result.stuno

use stu
go
if exists (select * from sysobjects where name ='titles')
drop table titles
go
create table titles
(
type varchar(20),
pub_id int,
price int
)
go
use stu
go
if exists (select * from sysobjects where name ='authors')
drop table authors
go
create table authors
(
pub_id int,
book_name varchar(20),
type varchar(20)
)

go
insert into authors values(5003,'击剑','sport')
insert into authors values(5004,'滑冰','sport')
insert into authors values(5005,'乒乓球','sport')
insert into authors values(5006,'羽毛球','sport')
insert into authors values(5007,'足球','sport')

insert into titles values('business',2001,56)
insert into titles values('business',2002,98)
insert into titles values('business',2003,102)

insert into titles values('mod_cook',4007,98)

insert into titles values('sport',5004,48)