一.数据库基本操作语句
1.1表格列的添加，删除，域的修改，复制
--修改表格
alter table 课程表 add 所属专业 nchar(5)--添加一列
alter table 课程表 add 开课学院 nchar(10),任课教师 nchar(6)--添加多列
alter table 课程表 drop column 任课教师--删除一列
alter table 课程表 alter column 所属专业 nchar(10)--修改列的域
alter table 课程表 alter column 开课学院 char(40)--nachar(20)==char(40)
--对表进行复制
select *into a2 from 课程表
select *from a2
--对表进行删除
drop table a2
--对已有表的列更名
1.2完整性的约束
-
代码添加约束
(1)添加主键约束
alter table 表名 add constraint 约束名 primary key (主键)(2)添加唯一约束
alter table 表名 add constraint 约束名 unique (字段)(3)添加默认约束
alter table 表名
add constraint 约束名 default (‘默认内容’) for 字段
(4)添加检查check约束,要求字段只能在1到100之间
alter table 表名
add constraint 约束名 check (字段 between 1 and 100 )
(5)添加外键约束(主表stuInfo和从表stuMarks建立关系,关联字段为stuNo)
alter table 从表 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)(6)sql server中删除约束的语句是：
alter table 表名 drop constraint 约束名 sp_helpconstraint 表名 找到数据表中的所有列的约束 下图为为一个表添加两个外键约束，vs2013好像没有直接可视化的创建表关系的地方，创建只可以写语句,在server explorer添加外键。不过一般都是在sql server中创建表结构直接添加。 语法为 alter table 从表 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)(7)连级删除
--创建表时同时建立连级删除
Create table 成绩表(学号 char(7) ,课程号 char(3), 成绩 tinyint,
Foreign key(学号) references 学生表(学号) on delete cascade )
--创建表后的添加
alter table t_student add foreign key (cname) references t_class(cname) on delete cascade
1.3函数的使用(convert()取整,as取别名,datediff(),复制表头,distinct()去掉重复)
- 数据的取整
select convert( numeric(3,1),(34.95))--numeric四舍五入，向上取整
select convert( numeric(2,0),(30.15+0.5))--超过就要统计进位
- as 列的别名取法，主表取出相应的数据变为附表
select 学号,姓名,身高+1 from 学生表--
select 学号,姓名 as '学号姓名',身高+1 from 学生表--
select 学号 as xh,姓名 as xm ,身高+1 as sg from 学生表 as xsb--列名表名都可以用别名，方便后程变成用
--表的拆分与合并，把主表选取性别为男的，弄到另一张表
select *into 学生男 from 学生表 where 性别='男'
select *from 学生男
-- 只选取一定的列，以及满足要求的，弄到另一张表
select 学号,姓名,性别,身高 into 学生女 from 学生表 where 性别='女'
select *from 学生女
- 把一张表的数据投影到另一张表，注意“年龄”的计算方式（datediff()函数的运用）
select 学号,姓名,性别,身高,
datediff(year ,出生日期,getdate()) as 年龄--计算出生日期
into 学生女 from 学生表 where 性别='女'
select *from 学生女
- ①只复制一个表的表头到另一个表，②把两个不同的表的相同数据部分，按一定的选择条件，选择投影到另一个表
select 学号,姓名,性别 into temp from 学生表 where 1>2--复制有相同表头的列
insert into temp select 学号,姓名,性别 from 学生表 where left(姓名,1)='李'
insert into temp select 学号,姓名,性别 from 学生女 where 年龄>=20
select *from temp
- distinct的使用方法，去掉重复值
--①
select distinct 性别 from 学生表--去掉重复值，涉及到数据的问题
--比如成绩表中的学号有你重复值，使相同的学号保存一个，查看选课的人数
--②
select *from 学生表 where 学号 in--查看选了课的人数
(
select distinct 性别 from 学生表
)
--③
select *from 学生表 where 学号 not in--查看没有选课的人数
(
select distinct 性别 from 学生表
)
--④
select distinct left(姓名,1) from 学生表--查询学生表中的姓的类别
1.4表的，创建，曾，删，投影，选择，更新（条件更新）
--插入
insert into 成绩表(学号,课程号,成绩)--验证学号外键的冲突
values('111','D001',88)
--删除
delete from 学生表 where 学号='001'
--创建表
create table 成绩表(
学号 nchar(3),
课程号 nchar(4) check(课程号 like 'D[0-9][0-9][0-9]'),
成绩 numeric(4,1) check(成绩>=0 and 成绩<=100),
foreign key(学号) references 学生表(学号),
foreign key(课程号) references 课程表(课程号),
);
update 课程表 set 课程名称='高等数学上' where 课程号='D002'
基本语法：UPDATE 表名称 SET 列名称 = 新值，列名称2 = 新值 WHERE 列名称 = 某值--多字段
基本语法：UPDATE 表名称1 SET 表1.列名称 = 表2.列名称 from 表名称1 表1，表名称2 表2 where 表1.Id=表2.Id--多表
--多表查询
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
根据条件动态跟新数据
--方法1 case用法1
update 班级表
set 班级 = (case 班级
when 1 then 2
when 2 then 1
else 3
end);
--其中when后面为一个确定的值
--方法2 case用法2
update 教课表
set 工作量=(case
when 人数<50 then 1
else 人数/50.0*1
end )
--when后面接判断语句
--方法3 where条件语句
#pm<24 pm设为6
UPDATE tb_weekly_plan SET pm = 6 WHERE pm + 0 < 24;
#24<pm<36 pm设为24
UPDATE tb_weekly_plan SET pm = 24 WHERE pm + 0 > 24 AND pm + 0 < 36;
#36<pm pm设为36
UPDATE tb_weekly_plan SET pm = 36 WHERE pm + 0 > 36;
1.5变量的声明与使用(declare(),print())
对变量的理解
declare @xh char(10)--变量的声明
set @xh='123'--变量的赋值
print @xh--变量的输出，注意三个语句是一起的
declare @xm char(10)--变量的声明
select @xm='hhh'--变量的另一种赋值方式
print @xm--变量的输出
-
变量的运算
print，select输出的区别
--变量计算 declare @lsf float declare @ldf float declare @reu float select @lsf=(select YEAR(出生日期)from 学生表 where 姓名='李四方') select @ldf=(select YEAR(出生日期)from 学生表 where 姓名='李大方') select @reu=@lsf-@ldf print @reu ---------------- declare @nl1 int ,@nl2 int, @nlc int declare @re nchar(20) select @nl1=YEAR(GETDATE())-YEAR(出生日期) from 学生表 where 姓名='李四方' select @nl2=YEAR(GETDATE())-YEAR(出生日期) from 学生表 where 姓名='李大方' print @nl2 set @nlc=@nl1-@nl2 print @nlc if(@nlc>0) --if判断语句的使用 begin set @re='李大方比李四方大'+CONVERT( nchar(2),@nlc)+'岁'--把年龄 end else begin set @re='李大方比李四方小'+CONVERT( nchar(2),0-@nlc)+'岁'--0-@nlc把复数变为正数 end print @re
1.6集合的应用(in,比较运算)
- in的使用
--集合in 与not in
select*from 学生表 where 学号=(select 学号 from 学生表 where 姓名='李华')
--这句执行正确是因为()这个集合就只有一个元素，是可以赋值的
select*from 学生表 where 学号=(select 学号 from 学生表 where 姓名='李华'or 姓名='李四')
--导致出错的原因为后面()是一个集合，学号是不能等于集合的
select*from 学生表 where 学号 in(select 学号 from 学生表 where 姓名='李华'or 姓名='李四')
--使用了in在集合里使用，所以输出正确
--集合的综合应用
--例子，查询所有男生党员的数据结构，计算机网络的成绩
select *from 成绩表 where 学号 in(select 学号 from 学生表 where 性别='男' and 是否党员='是')
and
课程号 in(select 课程号 from 课程表 where 课程名称 in('数据结构','计算机网络'))
--注意只能能使用in的两套循环，超过三套就不行了
-
关系运算,集合运算，集合差运算
--子查询是集合不是单一值时 select *from 学生表 where 性别='男'--关系运算 and 身高 > (select 身高 from 学生表 where 姓名='李大方') select *from 学生表 where 性别='男'--集合运算，比其中一个身高大，比最小的大 and 身高 >some (select 身高 from 学生表 where 姓名='李大方' or 姓名='李四方') select *from 学生表 where 性别='男'--集合运算，比其中一个身高大，比最小的大 and 身高 >any (select 身高 from 学生表 where 姓名='李大方' or 姓名='李四方') ---some与any是一个意思 select *from 学生表 where 性别='男'--集合运算，比其中一个身高大，比最小的大 and 身高 > (select min(身高) from 学生表 where 姓名='李大方' or 姓名='李四方') select *from 学生表 where 性别='男'--集合运算，与子查询（最大的）的都大 and 身高 >all (select 身高 from 学生表 where 姓名='李大方' or 姓名='李四方') select *from 学生表 where 性别='男'--关系运算，，比子查询最大的都要大 and 身高 > (select max(身高) from 学生表 where 姓名='李大方' or 姓名='李四方') --集合差值 select(select 身高 from 学生表 where 姓名='李四方')-(select 身高 from 学生表 where 姓名='李大方') select 5-4 -
查出与李大方的大学英语成绩相同的其它学生的情况:学号,姓名,课程号,名称,成绩
#方法一 select 学生表.学号,姓名,成绩表.课程号,课程名称,成绩 from 学生表 inner join 成绩表 on 学生表.学号=成绩表.学号 inner join 课程表 on 课程表.课程号=成绩表.课程号 --先取出成绩表与李大方的英语成绩相同的人 where 成绩表.成绩=(select 成绩 from 成绩表 where 学号=(select 学号 from 学生表 where 姓名='李大方') and 课程号=(select 课程号 from 课程表 where 课程名称='大学英语')) and 学生表.姓名 !='李大方' #方法二 declare @lafcj numeric(4,1) select @lafcj=成绩 from 成绩表 where 学号=(select 学号 from 学生表 where 姓名='李大方') and 课程号=(select 课程号 from 课程表 where 课程名称='大学英语') select 学生表.学号,姓名,成绩表.课程号,课程名称,成绩 from 学生表,课程表,成绩表 where 成绩表.成绩=@lafcj and 学生表.学号=成绩表.学号 and 课程表.课程号=成绩表.课程号
1.7排序的使用(升降序，随机NEWID()的使用，whit ties的保留，自增row_number())
--把身高排名前三的学号和姓名降序输出
select top 3 学号,姓名,身高 from 学生表 order by 身高 desc
--把身高排名后百分之四十的学号和姓名升序输出输出
select top 40 percent 学号,姓名,身高 from 学生表 order by 身高 asc--默认为asc
--如果最几两名的值相同，则需要保留最后几明的值
select top 4 with ties 学号,姓名,性别,身高,出生日期 from 学生表 order by 身高 desc
--随机挑选四个记录
select top 4 学号,姓名,性别,身高 from 学生表 order by NEWID()
--多关键字排序第一个主关键字，后面第二，三关键字. 从左向右，先满足左边的关键字排序，
--在值重复情况下才再按右边关键字排序
--性别，身高降序，出生日期升序
select 学号,姓名,性别,身高,出生日期 from 学生表 order by 性别,身高 desc,出生日期 asc
--order by 依据可以是列名，也可是列序号（如3表示按第3列排序）
select 学号,姓名,YEAR(getdate())-YEAR(出生日期) as 年龄 from 学生表 where 性别='男' order by 3 desc
--(注意：本处的年龄不是表中的原属性年龄，是查询结构别名年龄
select 学号,姓名,YEAR(getdate())-YEAR(出生日期) as 年龄 from 学生表 where 性别='男' order by 年龄
select结果自动添加序号
select row_number() over(order by a.Tea_id), a.Tea_name,a.Tea_sex,a.Tea_degree,b.Coll_name from T_Teacher as a inner join T_College as b on a.Tea_college=b.Coll_id
waitfor delay的使用
update 学生1 set 年龄=年龄+1 where 姓名='李大方'
waitfor delay '00:00:05'--每隔五秒年龄+1
1.8模式匹配(以特定的字符串进行查询)
--①符串匹配 like
--②%代表任意多个字符（包括0-n个），张% 可代表：张 张三 张三四(以要以张字开头即可)
--③_ 必须只能代表一个任意字符 如：张_ 可代表张三，张六（两个字）
--④[ ] 在其中只能选一个字符去匹配
--⑤[^字符] 不在其中选一个字符
--⑥left()函数
--例子查询姓李，刘，张的人
select *from 学生表 where left(姓名,1)='李' or left(姓名,1)='刘' or left(姓名,1)='张'
select *from 学生表 where left(姓名,1) in ('李','刘','张')
select *from 学生表 where left(姓名,1) like '[李刘张]'
select *from 学生表 where 姓名 like '[李刘张]%'
select *from 学生表 where substring(姓名,1,1) like '[李刘张]'
--上述反操作
select *from 学生表 where 姓名 like '[^李刘张]%'
--本身查询内容就包含_和%，就需要使用转义
--例：要从表中查出某说明列中含有“50%”字样的串
Select * from xx where 列名 like ’50%’ (不对)
Select * from xx where 列名 like ’%50!%%’ escape ’!’
说明：第1个%和第3个%是匹配任意字符串，第2个!%表示此%不是匹配意义，escape ’!’ 说明!后面这个字符不是匹配意义（类似于早期C语言中的\转意
例：有产品表，有列产品编号，现要查出编号中含有 no_2字样的所有产品
Select * from 产品表 where 编号 like ’%no!_2%’ escape ’!’
1.9关系的运算，集合的运算
--子查询是集合不是单一值时
select *from 学生表 where 性别='男'--关系运算
and 身高 > (select 身高 from 学生表 where 姓名='李大方')
select *from 学生表 where 性别='男'--集合运算，比其中一个身高大，比最小的大
and 身高 >some (select 身高 from 学生表 where 姓名='李大方' or 姓名='李四方')
select *from 学生表 where 性别='男'--集合运算，比其中一个身高大，比最小的大
and 身高 >any (select 身高 from 学生表 where 姓名='李大方' or 姓名='李四方')
---some与any是一个意思
select *from 学生表 where 性别='男'--集合运算，比其中一个身高大，比最小的大
and 身高 > (select min(身高) from 学生表 where 姓名='李大方' or 姓名='李四方')
select *from 学生表 where 性别='男'--集合运算，与子查询（最大的）的都大
and 身高 >all (select 身高 from 学生表 where 姓名='李大方' or 姓名='李四方')
select *from 学生表 where 性别='男'--关系运算，，比子查询最大的都要大
and 身高 > (select max(身高) from 学生表 where 姓名='李大方' or 姓名='李四方')
--集合差值
select(select 身高 from 学生表 where 姓名='李四方')-(select 身高 from 学生表 where 姓名='李大方')
select 5-4
1.10聚合函数与group by的使用(count(),sum(),avg(),max(),min(),)
- count统计元组的个数
select COUNT(性别) from 学生表
select COUNT(姓名) from 学生表
select COUNT(*) from 学生表 //常用的
可见，空值当空气，啥也不参与。空值不是空格！
例：统计表中的女生比男生多几个人？
select (select COUNT(*) from 学生表 where 性别='女')
-(select COUNT(*) from 学生表 where 性别='男')
例： 根据成绩表统计出有几门课有学生选
select COUNT(distinct 课程号) from 成绩表
补：哪些课程有人选？ 列出课程名
select 名称 from 课程表 where 课程号 in (select distinct 课程号 from 成绩表)
--求选课人数超过4门的人的姓名和性别
select 姓名,性别 from 学生表 where 学号 in
(select 学号 from 成绩表 group by 学号 having count(学号)>=4)
- sum(),avg()纵向对数值型列求和，平均
select SUM(成绩) from 成绩表 where 课程号='101'
select SUM(学号) from 成绩表 where 课程号='101' 错，学号不是数值型无法求和与平均值
select AVG(成绩) from 成绩表 where 课程号='102'
select AVG(成绩) from 成绩表 where 课程号='109'
--没有109课程,结果为null,实际上是总和/数据个数，0/0 严格说要溢出，sqlserver做了溢出容错处理的.
- group by 的使用
一次性分组统计出两门课的平均成绩：
select 课程号, AVG(成绩) as 科平均 from 成绩表
where 课程号 in('102','105')
group by 课程号
等效写法：
select AVG(成绩) as 科平均 from 成绩表
where 课程号 in('102' )
select sum (成绩)/COUNT(*) as 科平均 from 成绩表
where 课程号 in('102' )
- max(),min()常用于对数值，日期列求大值，小值
select MAX(成绩)-MIN(成绩) from 成绩表 where 课程号='101'
select MAX(生日) from 学生表 求年龄最小值(生日最大值)
select MAX(姓名) from 学生表
也可以，可见姓名汉字按拼音排序的.
例：一次性求出101课程的最高，最低，平均，总分，选课人数
select MAX(成绩) as 最高分,MIN(成绩) 最低分,
avg(成绩) as 平均分 ,sum(成绩) as 科总分,
count(*) as 选课人数 from 成绩表 where 课程号='101'
- group by分组一般结合统计聚会函数一起使用
select 性别, COUNT(*) from 学生表 where 性别='男'
--错：多了性别。因为这种统计结果是一个单一的数值，与哪一个人无关，所以不能引用查询非统计外的其它列.
--再如：统计全班的人数，这个人数值与哪一个具体的学生是无关
--统计各性别人数正确做法，是按性别先分组，再统计每组的人数.此时的统计结果与性别值有关
select 性别,count(*) as 人数 from 学生表 group by 性别
select 性别,count(*) as 人数,avg(身高) as 平均身高,max(身高) as 最高 from 学生表 group by 性别
select 性别,count(*) as 人数,avg(身高) as 平均身高,max(身高) as 最高 from 学生表 where 性别 is not null group by 性别
--where在前group在后，因为where是查询过程中使用的命令，而group是对结果进行处理
--那些课程被选
1.11 index索引与视图
1 概念理解：
如学生花名册，默认是学生有序的，今天的新申请的QQ 号数字比以前申请的要大。如此，这些数据有序的，主要目的是方便以后查询
数据表事先按要查询的关键字列索引后，以后要查询时可以使用二分查找**—折半法。
如我们经常按用户ID号或用户名登陆等操作，可以对数据原表建立一个附属的文件，保存查询依据的记录顺序。但这样做要增加额外的存储空间，增加数据的复杂度，但最大好处是提高查询效率。从顺序查找的n/2 降到log2(n)
(1)聚集索引 clustered index )
把数据按索引项的顺序进行物理排序。要生成一个新表，完全打乱原表中原数据的物理位置。
create clustered index index_xm on 学生表(姓名)--clustered为聚集的意思
#对学生表的姓名建立聚集索引
（2）非聚集索引(non clustered index)
现实开发相对说用得较多。一般是一个索引针对一表的一列（也可以是多列，少），针对一列建立一个索引，一个表可以建立多个索引的。如果使用聚集索引不现实。非聚集索引就是上面的理论的实际应用，针对一个物理表，可以建立多个附属的索引表，并不打乱原物理的记录顺序，只是重新记录原表记录的位置。
例：以非聚集索引为例说明实际索引结构。索引使用数据页管理，可以把大量的数据分段管理，大大提高查询效率
create nonclustered index index_kch on 课程表(课程号)--nonclustered为非聚集的意思
#对课程表的课程 号建立聚集索引
--经常增删改的表不适合左索引
create index index_xm on 学生表(姓名)--在使用姓名来进行查询的时候是用二分查找
create unique index index_xm on 学生表(姓名)--去掉重复值
视图
--创建视图，简化sql命令
--提高数据的安全性
create view myvv1 as (select a.*,课程号,成绩 from 学生表 a inner join 成绩表 b on a.学号=b.学号)
--as a和as b可以省去as
select *from myvv1--视图不是表，但是可以当作表来用，本身不保存数据，只保存sql命令
--与物理表的区别
--一个视图可以成为另一个视图的来源
select myvv1.学号,myvv1.姓名,myvv1.出生日期,myvv1.课程号,myvv1.成绩,课程表.课程名称,课程表.学分 from myvv1 inner join 课程表 on myvv1.课程号=课程表.课程号
--建立另一个视图
create view myvv3 as
(
select 学生表.学号,姓名,课程号,成绩,
case when 成绩>=60 then '及格'
else '不及格'
end as 及格否
from 学生表 inner join 成绩表 on 学生表.学号=成绩表.学号
)
select *from myvv3
1.12时间的计算(dateadd())
计算时间的相距距离
select DATEADD(YEAR,-102,getdate())
--dateadd(类型,累加值,日期时间的基数)
--类型:可以是 y/年,m/月,d/天 , 累加值表示按类型前推或后推指定数值
计算一个人活了多少天，生日过了多少天或者差多少天生日
declare @y smalldatetime ,@d int,@m smalldatetime,@n int
--y获取生日，d计算活了的天数，m计算在当年的生日，n距离生日的天数
select @y= 出生日期 from 学生表 where 姓名='赵明'
set @d=convert(int,getdate()-@y)--当天日期减去出生日期，在转换为天数
select '此人已经活了'+convert(char,@d)+'天'
set @m=dateadd(year,year(getdate())-year(@y),@y)--出生日期的年+相距的年=本年的生日
set @n=convert(int,getdate()-@m)--当天的日期-本年的生日在转化为天数，
if(@n>0)
select '此人的生日已经过了'+convert(nchar(3),@n)+'天'
else
select '此人的生日没过，还差'+convert(nchar(3),-@n)+''
1.13 case when 的用法
用法1，变量的使用
update 教课表
set 工作量=(case
when 人数<50 then 1
else 人数/50.0*1
end )
用法2，常量的使用
update 教课表
set 工作量=(case 人数
when 50 then 1
when 60 then 2
end )
1.14 while循环
declare @n int ,@s int
set @n=100
set @s=0
while(@n>0)
begin
set @s=@s+@n
set @n=@n-1
end
print @s
1.15存储过程（函数）create procedure
无参数的函数传递
create procedure p1 as
declare @xm nchar(6)
set @xm='阳春'
select 学号,姓名,出生日期 from 学生表 where 姓名=@xm
有参的函数传递
create procedure p2 @xm nchar(6),@kch nchar(3)
as
select 成绩 from 成绩表 where 课程号=@kch and
学号 in(select 学号 from 学生表 where 姓名=@xm)
execute p2 '李大方','101'
有参数有返回值
create procedure p3 @xm nchar(6),@kch nchar(3),
@cj int output
as
declare @cj0 int
select @cj0=成绩 from 成绩表 where 课程号=@kch and
学号 in(select 学号 from 学生表 where 姓名=@xm)
set @cj=@cj0
调用
declare @cj int
execute p3 '李大方','101',@cj output
print @cj
说明：需要返回值的实参和形参后面都要跟上output.****掌握此概念后，调用一个过程随便返回什么数据，随便返回多少个数据。
可见：过程就是一个小功能模板，程序段，一般是参数的输入，输出，与外界进行接口。**
二.实际应用
2.1数据类型转换
/*1>convert(目标类型及宽度，被转换前原值[,其它附加参数]
Convert(nchar(5), n)
Convert(nchar(10),生日,21) 其中21是针对日期的格式参数
2> cast(表达式 as 新类型[长度])*/
select CAST(身高 as numeric(4,1)) from 学生表
select 姓名+'同学的身高是:'+convert(nchar(4),身高) from 学生表
select 姓名+'同学的身高是:'+cast(身高 as NCHAR(4)) from 学生表
2.2字符串操作
/*1> left(串，n) 取串左n个字符
2> right(串，n) 取串右n个字符
3>substring(串,m,n) 从串的第m个字符开始共取n个字符出来(如果要取的字符串超过的实际，取完即可，容错处理)
4>len(串) 求串长
5>Ltrim(串) 去掉串左边多余的空格 Rtrim(串) 去掉串的右边多余空格*/
select RIGHT(学号,3) from 学生表
select SUBSTRING(学号,2,3) from 学生表
select REPLACE('很多坏人信法轮功，在中国法轮法是不允许','法轮功',REPLICATE('*',len('法轮功')))
select substring('sdasfaj',1,100)--抓取字符串
select ltrim('sss ssseee eef')--去掉空格
select replace('sdwefwe','w','xx')--字符串替换
select replace('s上升的空间法轮功','法轮功','**')--字符串替换
select replace('s上升的空间法轮功','法轮功',REPLICATE('*',len('法轮功')))--字符串替换
--取扩展名
declare @fn nchar(50)
set @fn='d:\12\abc\照片\123.jpeg'
print reverse(@fn)
declare @fn2 nchar(50)
set @fn2='d:\12\abc\照片\123.ab..jpeg'
print reverse(@fn2)
declare @fn3 nchar(50)
set @fn3='d:\12\abc\照片\123.ab..jpeg'
print reverse(rtrim(@fn3))
2.3时间操作
/*1> Getdate( )
2> Year(日期) 返回日期的年份 month(日期) day(日期)
3> Dateadd(日期时间的部分量，偏差值, 日期) 返回指定日期的指定分量的偏移后的日期结果
例： dateadd(year,95,getdate()) 求今天后的95天后是哪一天？
其中的日期部分量：year, month,quarter，day,week,hour,minute,second,millisecond*/
select GETDATE()+105
select DATEADD(day,105,getdate())
select DATEADD(hour,200,getdate())
select DATEADD(hour,200,'1990-2-1 11:05:30')
select 姓名,出生日期,DATEADD(day,100,出生日期)
from 学生表
2.4函数的应用create function
2.4.1标值函数：返回单个变量
函数的创建 与调用
--案例①
--创建函数
create function countrs(@x nchar(1)) returns int
begin
declare @rs int
select @rs= count(*) from 学生表 where left(姓名,1)=@x
return @rs
end
--函数的使用
select '姓李的人数为' + convert(char(3),dbo.countrs('李') )--注意名词空间的引用
--案列②
--创建函数
create function chengjiRs() returns int
begin
declare @a int
select @a=count(*) from 成绩表 where 成绩>90
return @a
end
select '成绩大于90的人数为：'+convert(char(3),dbo.chengjiRs())--函数调用
获取实际获得学分，对成绩进行评定，模块化实现
create function sdxf(@cj float,@kch char(4)) returns int--根据成绩和课程号，计算出实际获得学分
begin
declare @xf int
select @xf=学分 from 课程表 where 课程号=@kch--把该课程的学分算出来
if(@cj<60)
set @xf=0
return @xf
end
select 学号,课程号,成绩, dbo.sdxf(成绩,'D004')as 获得学分 from 成绩表 where 课程号='D004'--查看选D004课程学生获得的学分情况
select 学号,课程号,成绩, dbo.sdxf(成绩,课程号)as 获得学分 from 成绩表--查看所有学生的所有课程获得学分的情况
select *from 成绩表
--成绩评定
create function djpd(@cj float) returns nchar(2)
begin
declare @dj nchar(2)
if(@cj>=90) set @dj='优秀'
else if(@cj>=70) set @dj='良好'
else if(@cj>=60) set @dj='及格'
else set @dj='重修'
return @dj
end
select 学号,课程号,成绩, dbo.sdxf(成绩,课程号)as 获得学分,dbo.djpd(成绩)as 评定 from 成绩表--成绩评定出来
求n的阶乘
create function jiechen(@n bigint) returns bigint
begin
declare @i bigint,@res bigint
set @i=1;
set @res=1
while(@i<=@n)
begin
set @res=@res*@i
set @i=@i+1
end
return @res
end
select dbo.jiechen(20)
输入学号，获得总学分（成绩大于60）
--输入学号，求得获得的总学分并返回
create function sumxf(@xh char(3)) returns int
begin
declare @xf int
select @xf=sum(学分) from 课程表 as a inner join 成绩表 as b on a.课程号=b.课程号 where b.成绩>=60 and b.学号=@xh
return @xf
end
--函数的使用
select '学生的总学分为：'+convert(nchar(2),dbo.sumxf('011'))
编写一个标量函数,func3(@xm nchar(5) )返回指定姓名的学生应得总学分数(所选课程的成绩及格才能得到学分,否则该课程学分为0). 最后使用本函数查询数据:学号 姓名 生日 总学分
--方法一
create function func3(@xm nchar(5))returns int
begin
declare @sumxf int
select @sumxf=sum(学分) from 课程表
where 课程号 in(select 课程号 from 成绩表
where 学号=(select 学号 from 学生表 where 姓名=@xm) and 成绩>=60)
return @sumxf
end
--方法二
create function func3(@xm nchar(5))returns int
begin
declare @sumxf int
select @sumxf=sum (学分) from 课程表,成绩表
where 学号=(select 学号 from 学生表 where 姓名='李大方')
and 课程表.课程号=成绩表.课程号 and 成绩>=60
return @sumxf
end
2.4.2表值函数：内敛表值函数
可以返回大量数据，把需要的数据放在一个表内,数据计算过程毫无相干，目的是减少数据访问的次数，在return之前，把所有的数据都放在一个表内，进行传输。数据一次性传输完，减少多次传输的次数，目的是节约时间，提高程序的运行效率
案例1：输入一个姓氏，返回，学号，姓名，生日，有几人
create function myll(@xs nchar(1))returns table--返回一个表
as
return
(
select 学号,姓名,出生日期 from 学生表 where left(姓名,1)=@xs
)
select *from dbo.myll('王')
案例1：查询姓王的学号，姓名，生日，以及姓王的人数，和多余姓杨的人数
create function myll(@xs nchar(1))returns --返回一个表 ，数据计算过程可能毫无相干，在不同行显示不相干的数据
@table1 table(xh char(10),xm nchar(6),生日 smalldatetime,rs int,多于杨人数 int)--定义要返回的表
as begin--函数的开始
insert into @table1(xh,xm,生日) select 学号,姓名,出生日期 from 学生表 where left(姓名,1)=@xs
declare @rs int,@rsy int
select @rs=count(*) from 学生表 where left(姓名,1)=@xs
select @rsy=count(*) from 学生表 where left(姓名,1)='杨'
insert into @table1(rs,多于杨人数)values(@rs,@rs-@rsy)--从最后一行在重新插入
return --直接return
end
select *from dbo.myll('王')--查询姓王的学号，姓名，生日，以及姓王的人数，和多余姓杨的人数
案列2建立一个表值函数fzfun(xm)传一个学生姓名，返回该学生的姓名，学号，选课门数，选课总分，实得学分，选出哪些课程（列出各科课程名字—用”|”间隔开）
--需要先用一个辅助函数来把所选的课程以及对应获得的学分存在一张表当中，以便计算
--辅助函数，输入姓名，返回课程名称，获得学分字段的表
create function funtemp(@xm nchar(10)) returns
@tabel table(课程名称 nchar(10),获得学分 float)
as begin
insert into @tabel
select 课程名称,(case
when 成绩>=60 then (select 学分 from 课程表
where 课程表.课程号=成绩表.课程号)
else 0
end)as 获得学分
from 学生表
inner join 成绩表 on 学生表.学号=成绩表.学号
inner join 课程表 on 课程表.课程号=成绩表.课程号
where 学生表.姓名=@xm
return
end
--主函数
create function fzfun(@xm nchar(10)) returns
@table1 table(姓名 nchar(10),学号 nchar(4),选课门数 int,选课总分 float,实得学分 float,所有课程 nvarchar(100))
as begin
declare @sumxf float--总学分
declare @sykcm nvarchar(100)--所有课程名称
select @sumxf= sum(获得学分) from dbo.funtemp(@xm)--把所有的学分相加获得总学分
set @sykcm=( select RTrim(课程名称) +'|' from dbo.funtemp(@xm) for xml path(''))--通过xml格式，拼接字符串
insert into @table1--把数据插入表
select 姓名,学号,
(select count(*) from 成绩表
where 学号=(select 学号 from 学生表 where 姓名=@xm)),
(select sum(成绩)from 成绩表
where 学号=(select 学号 from 学生表 where 姓名=@xm)),
@sumxf,
@sykcm
from 学生表 where 学生表.姓名=@xm
return
end
--测试
select *from dbo.fzfun('李大方')
select *from dbo.fzfun('刘欢')
for xml path(‘’)
for xml path有的人可能知道有的人可能不知道，其实它就是将查询结果集以XML形式展现
path()函数中的字符串代表xml中的父节点的名称，默认为row,表现形式为 <字段1>字段1内容<字段1>… ，
如果是未命名的字段，比如字段1+‘’这种临时变量，就不会有该标签名，表现为实际值。。。
path(‘’)参数为父节点的名称，为空时xml不会生成父节点
SqlServer的for xml path函数_寡丶人的博客-CSDN博客_sqlserver xmlpath
2.5数据库设计
产品表：产品号 pk，性能参数
零件表：零件号 pk，规格，价格
材料表：材料号 pk，材料类型，库存量
产品零件组成表：产品号 fk，零件号 fk，零件个数
材料零件使用表：材料号 fk，零件号 fk，使用量
2.5.1物理结构设计
（1）确定物理结构（线性），一个文件小于等于实际占用空间，在磁盘上是块状储存的。
2.5.2数据库设计
- 功能分析：查询，视图，索引，操作约束，操作效率
- 功能设计：为模块化做准备，功能流程/结构图
- 事物设计：一块完成一个功能的程序段，每一句都要全部执行，要么一句也不执行，如果中途发生错误，退回到操作数据的原点。SQL的每一语句的操作都是事物
- 程序设计：数据库接口：ADO.NET, JDOC
2.5.3数据库的备份和恢复
- 安全性
2.5.4 范式
第一范式（1NF）：每列（属性）的值域的值都是不可再分的。
第二范式（2NF）：满足每列（属性）对主键为完全依赖，不能是部分依赖
第三范式（3NF）：属性与属性之间不能有传递依赖
2.6游标
- 处理一些有实时性要求的例子，
- 相当于指针，目的是减少对服务器的访问次数，提高效率。把数据都修改完过后在进行数据库的更新操作。
create function getbirth1() returns varchar(50)
as
begin
declare @birth smalldatetime,@sex nchar(2),@result nvarchar(50)
declare mycur1 cursor dynamic read_only for--定义一个动态的游标
select 性别,出生日期 from 学生表 where 姓名='李大方'
open mycur1--打开游标
fetch first from mycur1 into @sex,@birth--把查询的数据填充到变量当中
set @result=@sex+convert(char(10),@birth,21)--
close mycur1--关闭游标
deallocate mycur1--释放空间，删除游标
return @result
end
select dbo.getbirth1()--调用函数
游标分类
- 静态游标
游标中对应的数据不会改变，也不改变原物理表
- 动态游标
用户A对结果集的修改要反应给其他用户，不同网点售票数据的实时改变，适合并发现，实时性
- 只进游标
不能回看，只能从头到尾提取数据。
游标声明
declare 游标名 cursor[local|global]#定义游标的作用类型（全局，本地）
[forword_only|scorll...]#指针游标类型，只进，滚动
[static|dynamic...]#呈现结果的类型
[read_only|...]#操作权限
for select
[for update[of column]]#定义游标可跟新的列
#数据的提取
fetch[next|prior|first|last|absolute n|relative n]from cursor name
#下一行，上一行，第一行，最后一行，绝对行（定位到第n行），相对行（向前后跳n行）
#更新删除数据
update 表 set 列名=值[....]where current of 游标名
2.7触发器
概念
对关键数据，重要数据的控制反馈的信息。是一个存储过程，发生在用户对数据进行增删改时自动运行的一个过程。
应用场景：1.假设学生表删除一个人，成绩表也要删除。2.变化的条件的判断，约束不能搞定，要用触发器。3.在数据生成过程中动态的判断。
临时表：inserted 表，先插入到临时表中，在插入到物理数据库表当中（数据有效性的验证）。在临时表到物理表的过程中添加触发器trigger进行判断，判断正确过后在插入到物理数据库表当中
deleted 表，先把要删除的记录放入到deleted的临时表中（在临时表当中判断业务逻辑是否正确），确认无误后在插入物理表当中（例如删除一个题库的题目，判断题目的解答内容有没有被删除）
更新 ——删除记录，更新记录都要用deleted临时表，更新记录时要同时使用deleted与inserted临时表。
触发器建立格式
create trigger 触发器名 on 表名/视图名
for after/instead of #after后置，先插入后在判断。instead of前置，先判断在插入
insert/delete/update
as
sql语句序列（多多句单句即可）
一个表可建立多个触发器，但增删改同一种操作，一般只有一个。
一般触发器是建立在对数据（表，视图）增删改时发生，不对数据库及非表的其他对象操作
2.7.1 后触发器for after(先操作在触发)
遇到insert、update，delete时，
案列，
检查插入学生信息时，检查性别是否为男，女。（前提是在建表时没有对性别进行check约束）
create trigger trigsex1 on 学生表 after insert,update
as
begin
declare @sex char(2)
select @sex=性别 from inserted--从临时表中获取性别
if(@sex<>'男' and @sex<>'女' )--不为男，女
begin
rollback--回滚操作
end
end
插入学生成绩，判断学生表中是否有此人
create trigger trigmy1 on 学生表 after insert,update
as
begin
declare @sno char(9)
select @sno=学号 from inserted
if not exists(select *from 学生表 where 学@sno)
begin
rollback
end
end
sql
动态数据的约束（不同工种的数据约束是不一样的）
create trigger trill on 职工表 after insert,update
as
if exists(select *from 职工表 a inner join 工作表 b on a.工种号=b.工种号
where 基本工资 not between 最低工作 and 最高工资)--判断查出的数据是否合法
begin
rollback
end
删除学生表的的信息，自动删除成绩表的信息
create trigger trigdelete on 学生表 after delete
as
begin
declare @xh nchar(3)
select @xh=手机号 from deleted--从临时表中获取学号
if exists(select *from 成绩表 where 学号=@xh)
begin
delete 成绩表 where 学号=@xh
end
end
注册信息时使电话号码不重复
create trigger trigphone on 学生表 after insert,update
as
begin
declare @tell char(11)
select @tell=手机号 from inserted--从临时表中获取学号
if exists(select *from 学生表 where 手机号=@tell)
begin
rollback--回滚操作
end
end
--删除题目自动删除选题记录
create trigger deleteque on T_Question after delete
as
begin
declare @queid char(10)
select @queid=Que_id from deleted
if exists(select *from T_Result where Res_queid=@queid)
begin
delete T_Result where Res_queid=@queid
end
end
delete T_Question where Que_id='1803240101'
2.7.2 前置触发器
A对数据表C进行操作，B先判断A的语句是否满足业务逻辑，如果满足，B代替A执行语句
create trigger trimy3 on 职工表 instead of insert as
begin
if not exitst
(select *from inserted a join 工种表 b on )
end
2.8 事务与并发控制
2.8.1 概念
事务：是一次完完整的操作过程，由多条命令组成，所有命令被当做一个整体全部执行，要么全部不执行（前面执行的命令都撤销），后触发器的操作就是事务。：例子：银行之间的转账。。
事务一般定义在过程中，有可能为整个过程的代码，也可以是部分代码为事务。明尾暗头：开始位置可以是隐藏的，尾部是明确的
begin transaction：事务的开始
commit transaction 事务到此已经成功执行，后续可不当事务
rollback transaction：如果出错，返回到事务点状态
save transaction：事务保存点，如果事务出错的时候返回到该点
2.8.2 简单事务
begin tran
update 学生表 set 年龄=20 where 姓名='阳春'
update 学生表 set 年龄=-1 where 姓名='李大方'
commit tran
2.8.3 过程中的事务
--事务写在过程中
create proc mypro13 as
begin
begin tran--事务的开始，显示事务
begin try
insert into 学生表(学号,姓名,年龄)
values('030','aaa',17)
insert into 学生表(学号,姓名,年龄)
values('031','bbb','xx')
insert into 学生表(学号,姓名,年龄)
values('032','ccc',19)
end try
begin catch
--捕获错误
select error_number() as errnum,error_severity() as err2,error_line() as err3,
error_procedure() as err4,error_message() as err4
if(@@trancount>0)
rollback tran--事务的结束
end catch
end
execute dbo.mypro13
2.8.4 设置保存点，退回至指定保存点
create proc mypro16 as
begin
begin tran
begin try
insert into 学生表(学号,姓名,年龄)
values('030','aaa',17)
save tran mysave1--设置保存点，如果后面出错则可滚回到此处为
insert into 学生表(学号,姓名,年龄)
values('031','bbb','xx')
insert into 学生表(学号,姓名,年龄)
values('032','ccc',19)
end try
begin catch
--捕获错误
select error_number() as errnum,error_severity() as err2,error_line() as err3,
error_procedure() as err4,error_message() as err4
if(@@trancount>0)
rollback tran mysave1
end catch
if(@@trancount>0)--事务的结束点
commit tran
end
execute dbo.mypro16
2.8.5 并发： 多用户，多程序访问同一个数据
锁：完全依靠数据库来管理锁的工作，程序员自己管理的锁
多用户都用事务同时访问同一个数据资源的情况
- 更新丢失
- 不可重复读：
- 脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半没更新过的数据
- 幽灵数据：第一个事务读取
锁定，解决上述问题：
2.9 系统安全管理
2.9.1 身份验证模式
window验证登录：类似于超级用户
SQL server认证模式：用户登录
2.9.2 动态给数据库账户添加权限
选择某个登录名–右击属性–服务器角色。（数据库管理员的任务）
用户映射：默认操作那个数据库
状态：是否可以连接，是否禁用。使用命令行进行动态开放，控制时间权限。
- 对象权限：对数据库表的操作权限
- 语句权限：对数据库定义语句权限，建表
权限的操作使用，给任意用户授予任意权限
- 授予权限：操作表的对象权限，
grant 权限名 on 表名/视图/存储过程 to 数据库用户名/角色名
grant select,insert on 学生表 to user1
- 回收权限：
revoke 权限名 on 表名/视图/存储过程 from 数据库用户名/角色名
revoke insert on 学生表 from user1--回收插入权限
- 拒绝权限：临时的拒绝
deny on 表名/视图/存储过程 to 数据库用户名/角色名
deny update on 成绩表 tu user1--拒绝user1的更新权限
- 对多个用户，设置多个权限
三.应用开发
1.MVC
MVC：模型—视图—控制器 ，优点：前后端分开。视图程序给用户
**①“V” 视图： **前端：H5+JS（解决前台一些简单问题，减少服务器负担。jQuery框架：js的模块化），给用户最终呈现
②“M”模型 Java：，c++，C#，实现各个环节交换数据的类class
**③“C” 控制器：**C，Java，C++，后台程序，如aspx.cs Java的servlet
④数据库：：SQL server
动态网页：下面三种，缺点：前台后台代码写在一起
ASP：动态网页
JSP：Java服务器动态网页
PHP
静态网页与服务器没有交互，
2.前后端
html+jquery+ajax,当前最流行，效率最高
- ajax 局部刷新技术，有接管前后台交互的数据，起中间人作用
- jquery 前端的简单判断
execute dbo.mypro16
### 2.8.5 并发： 多用户，多程序访问同一个数据
**锁：完全依靠数据库来管理锁的工作，程序员自己管理的锁**
多用户都用事务同时访问同一个数据资源的情况
* 更新丢失
* 不可重复读：
* 脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半没更新过的数据
* 幽灵数据：第一个事务读取
**锁定，解决上述问题：**
## 2.9 系统安全管理
### 2.9.1 身份验证模式
**window验证登录：类似于超级用户**
**SQL server认证模式：用户登录**
### 2.9.2 动态给数据库账户添加权限
选择某个登录名--右击属性--服务器角色。（数据库管理员的任务）
用户映射：默认操作那个数据库
状态：是否可以连接，是否禁用。使用命令行进行动态开放，控制时间权限。
* 对象权限：对数据库表的操作权限
* 语句权限：对数据库定义语句权限，建表
**权限的操作使用，给任意用户授予任意权限**
* 授予权限：操作表的对象权限，
```sql
grant 权限名 on 表名/视图/存储过程 to 数据库用户名/角色名
grant select,insert on 学生表 to user1
- 回收权限：
revoke 权限名 on 表名/视图/存储过程 from 数据库用户名/角色名
revoke insert on 学生表 from user1--回收插入权限
- 拒绝权限：临时的拒绝
deny on 表名/视图/存储过程 to 数据库用户名/角色名
deny update on 成绩表 tu user1--拒绝user1的更新权限
扫一扫
4854
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
