T-SQL

第一章 数据库的设计


理论知识：

1.1
数据库设计就是将数据库中的数据对象以及这些数据对象之间的关系，进行规划和结构化过程。
1.2
项目开发需要经过需求分析、概要设计、详细设计、代码编写、运行测试和打包发行。
1.3
1.3.1
E-R图：实体（矩形）、属性（圆形）、关系（线）、映射基数（1:n,1:1,n:n,n:1）、实体关系图（整体）
1.3.2
E-R图转化为表

1.4数据规范化：
范式设计：1NF（原子性），2NF（每一列都与主键有关），3NF（每一列都和主键直接相关）



第二章 数据库的实现
2.1回顾
语法：
insert into table (col1,col2,col3) values（val1,val2,val3）
update table set col1=val1,col2=val2……where
select col1,col2……from table where (order by (desc)) (group by )
delete from table where

2.2.1建库
ex1:
create database DataBaseName
on PRIMARY
(
Name='stuDB_data',
FILENAME='D:/project/stuDB_data.mdf',
SIZE=5mb,
MAXSIZE=100mb
FILEGROWTH=15%
)
LOG ON
(
NAME='stuDB_log',
FILENAME='D:/project/stuDB_log.ldf',
SIZE=2mb,
FILEGROWTH=1mb
)
go



ex2:
create database employees
on primary
(
NAME='employee1',
FILENAME='D:/project/empoyee1.mdf',
SIZE=10,
FILEGROWTH=10%
),
(
NAME='employee2',
FILENAME='D:/project/empoyee2.ndf',
SIZE=20,
MAXSIZE=100,
FILEGROWTH=1
)
LOG ON
(
NAME='employeelog1',
FILENAME='D:/project/employeelog1_Log.ldf',
SIZE=10,
MAXSIZE=50,
FILEGROWTH=1
)
(
NAME='employeelog2',
FILENAME='D:/project/employee2_Log.ldf',
SIZE=10,
MAXSIZE=50,
FILEGROWTH=1
)
go


2.2.2
ex1:
DROP DATABASE stuDB
ex2:
use master
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name='stuDB')
DROP DATABASE stuDB
create DATABASE stuDB
on
(
……
)
LOG ON
(
……
)
GO

2.3.1创建表
CREATE TABLE table
(
……
)

ex1:
use stuDB
GO
create table stuinfo
(
stuNAME varchar(20) not NULL,
stuNO CHAR(6) NOT NULL,
stuAge INT NOT NULL,
stuID NUMERIC(18,0)
stuSEAT SMALLINT IDENTITY(1,1),
stuaddress text
)
go

ex2:
create table stumarks
(
ExamNo char(7) not NULL,
stuNo char(6) not NULL,
writtenEXAM INT NOT NULL,
Labexam int not null
)
go

2.3.2删除表
语法：DROP table table
ex1:
DROP table stuInfo
ex2:
use stuDB
go
if exists(select * from sysobjects where nme='stuinfo')
DROP table stuinfo
create table stuinfo
(
……
)
go

2.4使用sql语句创建和删除约束
添加约束语法：
1.常见类型约束：
主键约束：primary key constraint
唯一约束：unique constraint
检查约束：check constraint
默认约束：Default Constraint
外键约束：Foreign Key Constraint



2.语法
alter table 表名
add constraint 约束名 约束类型 具体约束说明
ex:
--主键约束
alter table stuinfo
add constraint PK_stuNO PRIMARY KEY (stuNO)
--唯一约束
alter table stuinfo
add constraint uq_stuID unique(stuid)
--添加默认约束
alter table stuinfo
add constraint DF_stuAddress DEFAULT('地址不详') for stuAddress
--添加检查约束，要求年龄只能在15~40岁之间
alter table stuINFO
add constraint ck_stuage check(stuAge between 15 and 40)
--添加外键约束
Alter table stuMarks
add constraint FK_stuNo
FOREIGN KEY(stuNo) REFERENCES stuINFO(stuNo)
go

2.5
三个安全等级：
1.sqlsever系统登陆账户
2.成为数据库的用户
3.成为数据库表的管理员

2.5.1创建登陆账户
windows验证：EXEC sp_grantLogin 'windows 域名/域账户'

sql登陆账户：EXEC sp_addlogin '账户名','密码'

个人注释：用应该是系统的存储过程



2.5.2创建数据库用户

EXEC sp_grantdbaccess '登陆账户','数据库用户'


2.5.3给数据库用户授权
语法：
GRANT 权限 （on 表名） to  数据库用户
ex：
use stuDB
GO
GRANT select,insert,update on stuinfo to zhangsanDBuser
GRANT create table to s26301DBUser






第三章 T-sql 编程


3.1.1局部变量

1.定义
语法：declare @名称 类型
ex:declare @name varchar(8)

2.赋值
set @名称 =值
select @名称=值


3.1.2全局变量
@@ERROR         最后一个sql错误的错误号
@@IDENTITY          最后一次插入的标识值
@@LANGUAGE        当前使用的语言名称
@@MAX_CONNEXTIONS    可以创建的同时连接的最大数目
@@ROWCOUNT        受上一个sql语句影响的行数
@@SERVERNAME        本地服务器的名称
@@TIMETICKS        当前计算机每刻度的毫秒数
@@TRANSCOUNT        当前连接打开的事务数
@@VERSION        sql sever 的版本信息


3.2输出语句
print 局部变量或字符串
SELECT 局部变量 AS 自定义列名

3.3.1 if-else
语法：
if(条件)
begin
……
end
else
begin
……

end

3.3.2while循环语句
while(条件)
begin
……
end


3.3.3case多分支语句
语法：
case
    when 条件 then 结果
    when 条件 then 结果
    else 其他结果
end

3.4 go
go是批处理语句，用于同时执行go以上的命令语句。



第四章 高级查询


4.1简单介绍子查询


不使用子查询一般我们用这种方法查询
delclare @age int
select @age=stuage from stuinfo where stuName='李斯文'
select * FROM stuINFO WHERE stuAge>@age
go

子查询：
select stuAge>(select stuage FROM stuInfo where suName='李斯文')
go

insert,delete,select同样可以这样查询。

多表连接的子查询：
select stuNAME FROM stuINfo INNER JOIN stuMarks
on stuInfo.stuNo=stuMarks.stuNo where wittenExam=60
go

Select stuName FROM stuInfo
where stuNo=(select stuNo FROM stuMarks WHERE writtenExam =60)
go


由上面的例子可以看出，子查询比较灵活，子查询可以代替几乎所有的多表连接，而反之不能。


4.2 in 和not in
in 和 not in 可以完成select返回多条数据的情况要求，比=更加灵活
ex：
select stuName FROM stuInfo
where stuNo in(select stuNo from stuMarks where writtenExam=60)
go

4.3 EXISTS和not EXISTS子查询
其实这个很简单，就是 Boolean <--EXISITS（select语句）
一般用法：
ex:
if EXISITS（select * from sysDATAbases where name='stuDB'）
        DROP DATABASE stuDB
        create DATABASE stuDB

not exisits 一样，就是返回值取反O(∩_∩)O









第五章 事务、索引和视图
5.1.2事务拥有：1原子性、2一致性、3隔离性、4持久性
一般使用格式：
begin transaction
decleare @errorSum int
set @errorSum=0
--语句一：(update bank set currentMoney=currentMoney-1000 where customerName='张三')
set @errorSum=@errorSum+@@error
--语句二：(update bank set currentMoney=currentMoney+1000 where customerName='李四')
set @errorSum=@errorSum+@@error
print'查看转账事务过程中的余额'
select * FROM bank
IF @errorSum<>0
begin
print'交易失败，回滚事务'
rollback transaction
end
else
begin
print'交易成功，提交事务，写入磁盘，永久的保存'
commit transaction
end
go


5.2.2如何创建索引

create[unique] [clustered|nonclustered]index index_name
on table_name(column_name)
[
with
fillfactor=x
]

ex:
create NONCLUSTERED INDEX IX_writtenExam
ON stuMarks(writtenExam)
with FiLLFACTOR=30
Go






5.3.2创建视图
create view view_name
AS
sql select语句











第六章 存储过程
调用系统存储过程：
sp_dataBases        列出服务器上的所有数据库
sp_helpdb        报告有关指定数据库或所有数据库信息
sp_renamedb        更改数据库的名称
sp_tables        返回当前环境下可查询的对象的列表
sp_columns        返回某个表列的信息
sp_help            查看某个表的所有信息
sp_helpindex        查看某个表的索引
sp_stored_procedures    列出当前环境中的所有春初过程
sp_password        添加或修改登录账户的密码
sp_helptext        现实默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本






定义存储过程以及调用：
ex:
use stuDB
GO
IF EXISTS (select * FROM sysobjects WHERE name='proc_stu')
    DROP PROCEDURE proc_stu
GO
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT,----OUTPUT关键字，视为输出参数
@writtenPass int=60,
@labPass int =60
AS
print '笔试及格线：'+convert(varchar(5),@writtenPass)
+'机试成绩线：'+convert(varchar(5),@labPass)
print'-----------------------------------------------'
print'参加本次考试没有通过的学员：'
SELECT stuName,stuInfo.stuNo,WrittenExam,labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenEXAM<@writtenPass OR labExam<@labPass
SELECT @notpassSum=COUNT(stuNo) FROM stuMarks
where writtenExam<@writtenPass OR lab LabExam<@labPass
Go



---调用存储过程
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64                --调用时带OUTPUT关键字，
print'--------------------------------------'
IF @sum>=3
print'未通过人数：'+conver（varchar(5),@sum）+'人，超过60%，及及格分数线还应下调'
else
print'未通过人数：'+conver(varchar(5),@sum)+'人，已控制在60%以下，及格分数线适中'
go