sql Servers数据库基础
1. 数据库约束包含：
·非空约束
·主键约束(PK) primary key constraint 唯一且不为空
·唯一约束(UQ) unique constraint 唯一，允许为空，但只能出现一次
·默认约束 (DF)default constraint 默认值
·检查约束 (CK) check constraint 范围以及格式限制
·外键约束 (FK) foreign key constraint 表关系
·增加外键约束时，设置级联更新、级联删除：
[ON DELETE {NO ACTION|CASCADE |SET NULL | SET DEFAULT}]
[ON UPDATE {NO ACTION|CASCADE |SET NULL | SET DEFAULT}]
2.文本类型：区分var 前缀、n前缀
·var 前缀：
对于不带var 的情况，表示不可变长度，如果赋的值不够指定的位数，则末尾使用空格补齐。如果长处指定的范围，系统就会报错。
对于带var 的情况，表示可变长度，如果赋的值不够指定的位数，以实际赋的值的长度为准。如果赋的值超出了指定的位数，系统就会报错，插入的值无效!
·对于采用unicode这种编码格式：
如果是带n的，则每一个字符占2个字节
如果不带n的，则英文字符占1个字节，中文字节占两个字节
这里注意：编码格式是在我们装SQl Server 数据库的时候进行设置的，一般我们会设置为：Chinese_PRC_CI_AS 【中文简体格式】的编码。
·对于采用Chinese_PRC_CI_AS编码格式：
如果是带n的，指定的长度n,可以代表n个英文字符，即【n个字节】；也可以是n个中文字符，即【n个字】
如果是不带n的，指定的长度n,代表n个字节，其中英文字符占1个字节，中文字节占两个字节。
·查看编码格式方法：
·右击某个数据库，然后查看属性，常规选项里面就会显示对应的编码格式，如下图：
3. 外键：[外键只是一种约束，保证数据的有效性]
·对应关系：1对1，1对多，多对多
·1对1 的关系，这种数据存储到双方任一表里就可以
·1对多的关系，将关系的数据存储到多的一方的表里
·多对多的关系，单独建立一个表，用于存储关系
根据关系创建出来的列所存储的信息，需要满足3NF
4. 关于数据库的名词有哪些？
-》数据库管理系统DBMS
-》表table
-》列column
-》字符按field
-》行row
-》范式：
第一范式：列的原子性
第二范式：不要重复
第三范式：引用其它表的主键信息
5. SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG（平均值）、SUM（和）、COUNT（数量：记录的条数）
6. 字符串函数：
·LEN()：计算字符串长度（字符的个数）
·datelength()：计算字符串所占用的字节数，不属于字符串函数。
·LOWER()、UPPER()：转小写、转大写
·LTRIM()：字符串左侧的空格去掉
·RTRIM()：字符串右侧的空格去掉
·LTRIM(RTRIM(" bb ")) ：去掉左右侧两边的空格
·LEFT()、RIGHT：截取字符串 例如：SELECT LEFT('abcdefg',2)
·SUBSTRING(string, start_position,length)：索引从1开始。从指定位置，截取字符数
参数string为主字符串，start_position为子字符串在主字符串中起始位置，length为子字符串的最大长度。
例如：SUBSTRING('abcdef111',2,3)
7. 日期函数：
GETDATE():取得当前日期时间
DATEADD(datepart ,number ,date),计算增加以后的日期。参数date为带计算的日期；参数number为增量；参数datepart为计量单位，可选值见备注。
DATEADD(DAY,3,date)为计算日期date的3天后日期，而DATEADD(MONTH,-8,,date)位计算日期date的8个月之前的日期
DATEDIFF(datepart,startdate,enddate):计算两个日期之间的差额。
datepart 为计量单位，可取值参考DateAdd.
统计不同入学年数的学生个数：
select DateDiff(year,slnDate,getdate()),count(*) from student Group by DateDiff(year,slnDate,getdate())
8. 联合查询：
-》 将多个结果集合并成一个结果集
-》 前提：列数相同，并且相应的列的类型相同
-》 判断标准以第一个表的结果集为准
-》执行结果集的特点：
· 结果集的列名以第一个为准；
·自动排序；
·消除重复项；
例如：使用关键字：union
select * from (
select cid,cName from TblClass
union
select Did,DName from Department
union
…
) as t1
order by cname
使用关键字union all 【特点：不排序，不消除重复行】
9. 连接查询：当结果中的数据分布于多个表中时，需要将多个表连接到一起进行查询，这种查询就是连接查询。
-》做多个表的数据查询
-》有时，结果集中的数据被包含在多个表中，则需要使用连接查询
-》关键字：join …… in……
主要分为两种：内连接，外连接。
· 内连接：普通内连接，自连接 inner join
· 外连接：左外连接，右外连接，完全外连接
-》第一种：内连接 inner join … on
匹配两个表中都有的数据
10. 完整的select 语句及执行顺序（必须记住）
5 … select 5.2 -> distinct 7…top n [percent] 5.1 -> 列名 聚合函数(1.2 -> 列名或表达式)
1 … 1.1 -> from 表名
2 … where 条件
3 … group by .. 4… having…
6 … order by …
实际练习：
select distinct top 1 tsclassid , COUNT(*) AS 人数, avg(tsage) as 平均年龄
from tblstudent
where tsGender ='男'
group by tsclassid having tsclassid > 3
order by 平均年龄 desc
11. 系统内置函数：
类型转换函数：cast(值 as 类型) convert(目标类型，值[，格式])
字符串函数：len();
日期时间函数：gerdate() datediff(关键字，日期1，日期2) datepart(关键字，日期)
12. 联合查询：将多个结果集合并成一个结果集
要求：列相同，列类型相同
union ：会排序，会去重
union all ：所有数据都取出来，并且不排序
13. 自连接：
特点：关系列的值引用的本表中主键列信息。
14. 外连接：
-》left join 左外连接：左表中的信息全部出现，右表中的信息必须匹配的才能出现
匹配项出现在前面
未匹配的项使用null填充
-》right join 右外连接：右表中的信息全部出现，左表中的信息必须能够匹配的才出现
未匹配的项使用null填充
-》full outer join ：所有数据都会出现，不匹配的项使用null填充。
15. 在查询语句的select后面，可以进行选择判断的逻辑【case】
语法1：判等
Case 列名
When ….. then…..
When ….. then……
Else….
End as 列别名
例如：将性别的0、1显示为男、女
select * ,
case egender when 0 then '男' when 1 then '女' end
from Employee
语法2：判不等
Case
When 包含列名的逻辑表达式then…
When …. then ….
else
End as 列别名
例1：查询低于60分的学生提示不及格
select tsname,isnull(
case when tEnglish<60 then '不及格' else CAST(tEnglish as varchar(10)) end,'缺考')
from TblStudent
left join TblScore on TblStudent.tSId=TblScore.tSId
例2：显示成绩为'优、 良、 中、 差'
select sName,
case when sEnglish between 0 and 60 then '差'
when sEnglish between 60 and 70 then '中'
when sEnglish between 70 and 80 then '良'
when sEnglish between 80 and 100 then '优'
end as '成绩等级'
from Student
注意：不可以作为独立的语句，只能在select的子句中使用。
16. 声明变量：
自定义变量：【一般以@符号开头，表示自定义的变量】
declare @UserName nvarchar(50) 可以直接附初值：declare @uname nvarchar(10)='sk'
赋值1：set @UserName=N'杨'：修改
赋值2：select @ UserName=N'牛'：修改
输出1：print @UserName
输出2：select @UserName
select @UserName +N '中'，这时@UserName仍然是'杨'，这句代码执行后显示'杨中'
区别：赋值时，set 比 select 更严谨些
全局变量：
@@***
这些变量由系统维护，不需要我们管理，用于查看信息。
如下常见的全局变量：
@@version : 查看版本信息 select @@VERSION
@@identity : 查看当前的标识，跟在insert 语句后
例如：insert into Employee (eName,eCode,eDid) values('zjy',13,4)
select @@IDENTITY --查询最新插入的数据的ID
@@servername : 查看服务器名称 select @@servername --结果是：MSSQLSERVER
@@error : 返回最后执行的一句代码的错误编号，如果没有出错返回0；如果被go 中断，就不能用。它可以结合if替代try---catch的使用
例如: insert into Employee (eName,eCode,eDid) values('zjy',13,4)
select @@error
注意：@@error 紧挨着上一条语句，显示的是上一条的出错编号。
@@rowcount : 查看最后执行的一句代码的影响行数
17. if … else 语句的用法：
例如：判断一个值是奇数还是偶数
declare @i int=10
if(@i%2=0)
begin
print '偶数'
end
else
begin
print '奇数'
end
18. while循环语句【在sql中只有这么一种循环语句】
例如：求1~100之间的和
declare @j int=1
declare @sum int=0
while(@j<=100)
begin
set @sum+=@j
set @j+=1
end
print @sum
19. over 子句【被称为一个开窗函数】
开窗函数：几个行作为一个区，就被成为一个窗，能够进行按行划区的函数就是开窗函数。
排名函数：rank()
例如：通过英语成绩进行排名：
select *,rank() over(order by tEnglish desc) from TblScores
这块注意：使用了over子句，就不需要在最后面使用order by语句了。
排号函数：row_number()
例如:价格对订单进行排号
select *,ROW_NUMBER()over(order by oprice) as '排号' from TblOrders
划区：
例如：结合聚合函数通过名称，对订单的数量进行统计
select distinct oname,SUM(ocount) over(partition by oname)
from TblOrders
20. 视图 View：
就是一个select语句，写起来比较复杂，多次被使用，则可以将这个select 语句存放到一个视图中。
创建语法：
create view 名称
as
查询语句
好处：使用方便，安全（看不到表的名称，结构等信息）
使用：主要用于查询，与查询表的语法一样。
查看视图中的代码：ordersWithNum 为视图的名称
exec sp_helptext ordersWithNum
注意：视图并不是一个存储数据的容器，而只是存储了一个查询。
21. 事务：【transaction】
定义：保证多个操作全部成功，否则全部失败，这处机制就是事务
例如:
declare @errornum int=0
begin tran --开始事务
insert into TblOrders values('五粮液',2,300)
set @errornum+=@@error
insert into TblOrders values('包子',3,1)
set @errornum+=@@error
if(@errornum>0)
begin
rollback tran --回滚事务
end
else
begin
commit tran --提交事务
end
阻止自动提交事务：【手动提交事务】即【显式事务】
set implicit_transactions on
开启自动提交事务:【自定提交事务】即【隐式事务】
set implicit_transactions off
22. 存储过程;
定义：完成一段代码的封装
查看存储过程代码：exec sql_helptext 存储过程名称
语法：
--创建存储过程：proc[edure]
create proc 存储过程名称
--参数列表，多个间用逗号分隔
as
--自定义代码段
例子1：
create proc trim
@str varchar(10)
as
declare @str1 varchar(10)
set @str1=LTRIM(RTRIM(@str))
print @str1
--使用存储过程
exec trim ' vabc '
例子2：
--求两个数的和
create proc sum1
@num1 int,
@num2 int,
@result int output --表示这个参数可以将结果带出存储过程
as
set @result=@num1+@num2
--使用存储过程
declare @r1 int
exec sum1 1,2,@r1 output --一定要写output 关键字，否则调用出错
print @r1
--参数带默认值的存储过程，注意：带默认值必须是最后一个参数
create proc multi
@num1 int,
@num2 int=10 output --output 的功能类似于C#中的ref修饰参数
as
set @num2=@num2*@num1
--测试带出值的效果 结果是：6
declare @num int=2
exec multi 3,@num output
select @num
--提交学生成绩，以使平均分提高
create proc AddScore
@growth int, --分数的增量
@avg float=60
as
declare @avg1 float
select @avg1=AVG(tenglish) from tblScore
while(@avg1<@avg)
begin
update TblScore set tEnglish+=@growth
update tblScore set tEnglish=100 where tEnglish>100
select @avg1=AVG(tenglish) from tblScore
end
print 'OK'+cast(@avg as varchar(10))
23. 异常处理：和C#不同的是，C#中的try后面可以跟0~多个catch，而数据库里面，只能跟1个catch
begin try
…
end try
begin catch
…
end catch
注意：尽量少使用，会影响结果，可以通过@@error来查看上一条语句是否发生错误
24. 索引：
作用：提高查询速度
注意：不是越多越好，会损耗存储空间
应用：对于出现在where字句中的列建立索引
创建索引步骤：
1. 在指定的表中右击，点击设计，然后在对应的列名称上面右击，选择索引/键
2. 点击添加，如下图所示:
3. 选择索引：
代码创建:
create index 索引名称
on 表名(列名)
25. 触发器：【当有关联操作发生的时候】
比如：下订单时，创建中的商品数量需要减少
比如：退票时，总的票量要增加
作用：对表进行增、删、改操作时，自动进行一个操作
根据触发器机制不同，分为：after触发器，instead of 替换触发器
创建触发器：
create trigger 名称
on 表名
[After|instead of][insert|delete|update]
AS
Begin
…
end
关键：确定要将触发器建立在哪个表上on
执行时间：after:在触发执行完成后执行触发器中的代码
instead of:在触发原执行前执行触发器中的代码，同时触发源的操作被废弃
例如：
案例一：
--创建触发事件，对刚刚插入的数据进行备份
create trigger bak_order
on tblorders
after insert --在插入语句之后，执行下面的语句
as
begin
insert into TblOrders_bak(oname,ocount,oprice)
select oname,ocount,oprice from inserted
end
测试方式:
insert into TblOrders values('ff',10,20)
案例二：
create trigger update_order
on tblorders
instead of update --在执行update的时候，会对该更新进行替换，替换成下面的语句
as
begin
update TblOrders_bak set ocount=(select ocount from inserted)
where oname in(select oname from inserted)
end
测试方式：
update TblOrders set ocount=20 where oname='ff'
26. 游标：
· 可以逐条操作表中的数据
- 缺点：性能低，慎重使用
游标的使用方式：
--游标的定义
declare s1 cursor for select oname,ocount,oprice from TblOrders
--在访问游标中某行的数据时，需要声明变量
declare @name varchar(10)
declare @count int
declare @price money
--打开游标
open s1
--通过游标从结果集中取数据 注意：每次取一行的值
fetch s1 into @name,@count,@price
--如果取值成功，对应的装态@@FETCH_STATUS的值就会为0
while(@@FETCH_STATUS=0)
begin
set @price=@price+RAND(100)
update TblOrders set oprice=@price where oname=@name and ocount=@count
fetch s1 into @name,@count,@price
end
--关闭游标
close s1
--释放资源
deallocate s1
