1.数据库管理系统(DBMS)是一种系统软件,由一个互联关联的数据集合和一组访问数据的程序结构.
2.在数据库系统中,数据重复的现象就是数据冗余.
3.SQL Server中的数据库按照用途可以划分为两种.(系统数据库和用户数据库)
4.SQL Server系统数据库:
(1)Master数据库:记录SQL Server系统的所有系统级别信息.(1.所有的登录账户和系统配置设置.2.所有其他的数据库及数据库文件的位置.3.SQL Server的初始化信息.)
(2)Tempdb数据库:保存所有的临时表和临时存储过程,以及临时生成的工作表.
(3)Model数据库:在系统上创建的所有数据库的模板.
(4)Msdb数据库:供SQL Server代理程序调度警报,作业以及记录操作时使用.
5.访问限制:指定哪些用户可以访问该数据库.可能值有以下三种:
(1)Multiple:数据的正常状态,允许多个用户同事访问该数据库.
(2)Single:用于维护操作的状态,一次只允许一个用户访问该数据库.
(3)Restricted:只有管理员角色或者特定的成员才能使用该数据库.
6.SQL Server启动的时候,数据库文件是不能复制,粘贴和移动的.
7.数据完整性
(1)实体完整性:要求表中的每一行数据都反映不同的实体,不能存在相同的数据.(索引、唯一约束、主键约束或标识列属性)
(2)域完整性:给定列的输入有效性(限制数据类型、检查约束、输入格式、外键约束、默认值、非空约束)
(3)引用完整性:在输入或删除数据行时,引用完整约束来保存表之剑已定义的关系(主键与外键的引用关系来实现)。
(4)自定义完整性:定义特定的规则(数据库的规则、存储过程、触发器对象来进行约束)
8.T-SQL支持逻辑运算符有:And、Or、和Not。
9.使用Insert插入数据行
(1)插入单行数据
语法:insert [into] <表名> [values] <值列表>
例:insert into students (name,age,sex) values('Harry',20,'男')
(2)插入多行数据
语法:insert <表名> [values] Select <值列表> union Select<值列表> 例:insert students(name,age,sex) Select '张三',20,'男' union Select '李四',18,'女'
(3)通过 insert select语句将现有表中的数据添加到新表中
语法:insert [into] <表1><values> Select vaules from <表2> 例:insert into student1(姓名,年龄,性别) Select name,age,sex from student2
10.使用update更新数据
语法:update <表名> set <列名=更新值>[where <更新条件>] 例:update student set sex='男' where studentid=2
11.使用delete删除数据
语法:delete from <表名> [where <删除条件>] 例:delete from student where studentid=2
12.使用turncate table删除表中的所有行数据
语法:turncate table <表名> 例:turncate table student
13.简单查询语句
例1:select * from students 例2:select studentID,name,age,sex from students 例3:select studentID,name,age,sex from students where studentID=10 例4:select name from students where sex is null 例5:select 姓名=name,年龄=age,性别=sex'河北新龙' as 学校名称 例6:select top 5 name,age,sex from students where objectid=3 order by score desc(或者ASC升序排列)
14.使用like进行模糊查询
例:select * from students where name like '张%'
15.使用between在某个范围内进行查询
例:selecr * from student where score between 60 and 80
16.聚合函数
Sum:求和 Avg:平均数 Max:最大值 Min:最小值 Count:求非空行的行数
17.分组查询
(1)使用group by进行分组查询 例:select courseID,avg(score) as 课程平均成绩 from score group by courseID (2)使用having子句进行筛选 例:select courseID,avg(score) as 课程平均成绩 from score group by courseID having
18.where--->group by--->having
19.多表连接查询
(1)内联接:inner join (2)外联接 (1)左外联接:left join 或者 left outer join (2)右外联接:right join 或者 right outer join (3)完整外联接:full join 或者 full outer join
20.设计数据库步骤
(1)收集信息
(2)标识实体
(3)标识每个实体需要存储的详细信息(属性)
(4)标识实体之间的关系
21.数据库3大范式
(1)第一范式(1NF):列不可再分
(2)第二范式(2NF):每个表只描述一件事情
(3)第三范式(3NF):没有传递依赖
T-SQL语句
1.使用T—SQL语句创建和删除数据库
Create database 数据库名 On [primary] ( <数据文件参数>[,......n] [<文件组参数>] ) [log on] ( {<日志文件参数> [,......n]} ) 文件的具体参数如下: Name=逻辑文件名 Filename=物理文件名 Size=文件大小 Maxsize=最大容量 Filegrowth=增长量 例: --调用CMD命令创建文件夹 exec sp_configure 'show advanced options',1 go reconfigure go exec sp_configure 'xp_cmdshell',1 go reconfigure go exec xp_cmdshell 'mkDir E:\Project' go if exists(select * from sysdatabases where name='MySchool')--判断是否存在MySchool数据库 drop database MySchool--删除数据库 Go create database MySchool--创建数据库 --主文件 on ( name='MySchool_data', filename='E:\project\MySchool_data.mdf', size=10, filegrowth=20% ) --日志文件 log on ( name='MySchool_log', filename='E:\project\MySchool_log.ldf', size=3, maxsize=20, filegrowTh=1 ) Go
2.使用T—SQL创建和删除表
Create table 表名 ( 列1 数据类型 列的特征, 列2 数据类型 列的特征 ...... ) 例: use MySchool go if exists (select * from sysobjects where name='Subject')--判断是否存在Subject表 drop table Subject go create table Subject--创建Subject表 ( SubjectId int identity(1,1) not null, --identity自动增长列 subjectName nvarchar(50) not null, ClassHour int not null, GradeId int not null ) go
3.使用T—SQL语句创建和删除约束
主键约束(Primary Key Constraint):要求主键列数据唯一,并且不允许为空,如学号能唯一确定一名学生。
非空约束(Not Null):要求列不能存在空值,如学生的姓名不能为空。
唯一约束(Unique Constraint):要求该列的值必须唯一,允许为空,但只能出现一个空值。
检查约束(Check Constraint):某列取值范围限制,格式限制等,如有关年龄的约束。
默认约束(Default Constraint):某列的默认值,如我们的男性学生较多,性别默认为男。
外键约束(Foreign Key Constraint):用于两表之间建立关系,需要指定引用主表的哪一列。
例:
(1)主键约束(PK_) if exists (select * from sysobjects where name ='pk_SubjectId') alter table Subject drop constraint pk_SubjectId go alter table Subject add constraint pk_SubjectId primary key (SubjectId) go (2)唯一约束(UQ_) if exists (select * from sysobjects where name ='uq_SubjectName') alter table Subject drop constraint uq_SubjectName go alter table Subject add constraint uq_SubjectName unique (SubjectName) go (3)检查约束(CK_) if exists (select * from sysobjects where name ='ck_ClassHour') alter table Subject drop constraint ck_ClassHour go alter table Subject add constraint ck_ClassHour check (ClassHour>0) go (4)默认约束(DF_) if exists (select * from sysobjects where name ='df_Exa') alter table Result drop constraint df_Exa go alter table Result add constraint df_Exa default (getdate()) for ExamDate go (5)外键约束(FK_表1_表2) if exists (select * from sysobjects where name ='fk_Subject_Grade_GradeId') alter table Subject drop constraint fk_Subject_Grade_GradeId go alter table Subject add constraint fk_Subject_Grade_GradeId foreign key (gradeId) references Grade(GradeId) Go
SQL编程
1.局部变量
Declare @variable_name DataType 例:declare @name varchar(20)--声明存放姓名变量name,最多可以存储20个字符
2.给变量赋值
Set @variable_name = value
或
Select @variable_name=value
3.print语句和select语句
Print 局部变量或字符串
select 局部变量 as 自定义列名
例:
Print '服务器的名称:'+@@servername
Select @@servername as '服务器的名称'
4.数据类型转换(Cast()和Convert()函数)
Cast(表达式 as 数据类型)
Convert(数据类型[长度] ,长度[,样式])
例:
Cast(@Result as varchar(10))
Convert(varchar(20),@@error)
5.Begin-End语句
Begin
语句或语句块
End
6.IF-ELSE语句
IF(条件)
语句或语句块1
ELSE
语句或语句块2
7.WHILE语句
WHILE(条件)
Begin
语句或语句块
[break|continue]
End
8.CASE多分支语句
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
[ELSE 其他结果]
END
高级查询
1.子查询
Select ........ From 表1 where 列1>(子查询)
例:
Select * from student where age>(select avg(age) from student)
2.IN 和 NOT IN子查询
3.EXISTS 和 NOT EXISTS子查询
4.使用UNION联合查询
事务、视图、索引、存储过程
1.事务(Transaction)
a) 原子性:不可分割。
b) 一致性:当事务完成时,数据必须处于一致状态。
c) 隔离性:对数据进行修改的所有并发事务是彼此隔离的。
d) 持久性:不管系统是否发生了故障,事务处理的结果都是永久的。
语法:
Begin transaction --显示的标记一个事务的起始点
Commit transaction --这个语句标志一个事务成功结束。
Rollback transaction --将数据状态回滚到事务起始点,并释放由事务控制的资源。
例:
Use myschool
Go
Set nocount no --不显示受影响的行数
Print '查看转账事务前的余额'
Select * from bank
Go
--开始事务
Bengin transaction
Declare @errorNum=0
Update bank set currentMoney=currentMoney-1000 where customerName='张三'
Set @errorNum=@errorNum+@@error
Update bank set currentMoney=currentMoney+1000 where customerName='李四'
Set @errorNum=@errorNum+@@error
Print '查看转账事务过程中的余额'
Select * from bank
Go
--根据是否有错误,确定是提交还是撤销
If @@errorNum>0
Begin
Print '交易失败,回滚事务'
Rollback transaction
End
Else
Begin
Print '交易成功'
Commit transaction
End
Go
Print '查看转账事务后的余额'
Select * from bank
go
2.视图(View)
创建视图语法:
Create view view_name
As
<select 语句>
删除视图语法:
Drop view view_name
例:
If exists(select 1from sysobjects where name='vw_CardCostomer')
Drop view vw_CardCostomer
Go
--创建视图
create view vw_CardCostomer
as
select CardID 卡号, c.CustomerName 客户姓名, PassWord 密码, MoneyType 货币, bs.TypeName 储蓄种类,OpenCardDate 开户日期, OpenCardMoney 开户金额, case
when IsActive ='是' then '挂失'
when IsActive ='否' then '未挂失'
end 是否挂失
from BankCard b
inner join Customer c on c.CustomerID=b.CustomerID
inner join BankSaveGetType bs on bs.TypeID=b.SaveTypeID
Go
3.存储过程(procedure)
优点:
A)模块化程序设计
B)执行速度快,效率高
C)减少网络流量
D)具有良好的安全性
分类:系统存储过程(sp_)、用户自定义的存储过程(up_)
常用系统存储过程
A)Exec sp_databases --列出当前系统中的数据库
B)Exec sp_renamedb 'mybank','bank' --改变数据库名称
C)Exec sp_tables --当前数据库中可查询对象的列表
D)Exec sp_colums student --查看表student中列的信息
E)Exec sp_help student --查看student的所有信息
F)Exec sp_helpconstraint student --查看student表中的约束
G)Exec sp_helptext 'view_student_result' --查看视图的语句文本
H)Exec sp_stored_procedures --返回当前数据库中的存储过程列表
创建和删除存储过程
注:只要存储过程后面有output关键字,表示此参数为输入参数,否则视为输入参数,输入参数还可以设置为默认值。
语法:
Create proc[edure] 存储过程名
[
{@参数1 数据类型}[=默认值][output],
..................................................
{@参数n 数据类型}[=默认值][output]
]
As
SQL语句
例:
--存取钱存储过程
if exists(select 1 from sys.objects where name='up_ChangeMoney')
drop proc up_ChangeMoney
go
create proc up_ChangeMoney
@CardID varchar(32),
@ChangeDate datetime,
@ChangeMoney money,
@ChangeTypeName varchar(10),
@ChangeNote varchar(100)
as
begin tran
declare @ErrorSum int=0
declare @ChangeTypeID int
set @ChangeTypeID=(select TypeID from ChangeType where TypeName=@ChangeTypeName)
if (@ChangeTypeName='存入')
begin
print '交易正在进行中.....'
update BankCard set Banlance=Banlance+@ChangeMoney where CardID=@CardID
set @ErrorSum=@ErrorSum+@@error
insert into ChangeMsg values(@CardID,@ChangeDate,@ChangeMoney,@ChangeTypeID,@ChangeNote)
set @ErrorSum=@ErrorSum+@@error
end
else
begin
update BankCard set Banlance=Banlance-@ChangeMoney where CardID=@CardID
set @ErrorSum=@ErrorSum+@@error
if(@ErrorSum>0)
begin
RAISERROR ('交易失败!余额不足!',16,1)
end
Insert into ChangeMsg values(@CardID,@ChangeDate,@ChangeMoney,@ChangeTypeID,@ChangeNote)
set @ErrorSum=@ErrorSum+@@error
end
if(@errorSum=0)
begin
print @ChangeTypeName+'金额成功'
commit tran
end
else
begin
print '操作失败'
rollback tran
end
Go