数据库设计
数据库设计就是将数据库中的数据对象以及这些数据库对象之间的关系进行规划和结构化的过程
良好的数据库设计表现为：
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]主要的
----添加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
----------------==========================================================
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)
扫一扫
1773
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
