1.主键

主键的作用：保证表中每条数据的唯一性特点：主键不能重复，不能为空主键，建议选择那些一般不会被修改的列选择单列，不选择多列（不用组合主键）选择那些简单列（整数列（自动编号））

 

2.char(),nchar(),varchar()之间的区别

char(10)与varchar(10)的区别char(10) 固定长度，表示在数据库中存储的时候占用10个字节的空间，如果超出10个则报错，如果不够10个则用空格补全。 varchar(10) 可变长度，表示该列最多可以存储10个字节，如果实际存储不够10个字节，则会在存储的时候自动计算一下实际的存储个数，而动态的改变长度。【节省空间】char(10)与nchar(10)的区别char(10) 可以存储10个字母或者5个汉字。 用来存储数据的时候，英文站1个字节，中文站2个字节。nchar(10) 表示可以存储10个字母或10个汉字，因为每个字符都是按照unicode方法来存储的。当使用nchar（10），来存储数据的时候无论存储的是中文还是英文都是每个字符占2个。

 

3. 创建数据库

创建一个数据库create database School 删除数据库drop database School 创建数据库的时候，指定一些数据库的相关参数。create database School on primary 主数据文件(name='School',size=10mb,filename='c:school.mdf',filegrowth=10%,maxsize=100mb)切换数据库use schoolgo

 

4.创建表

创建表create table Class(ClassId int identity(1,1) primary key,ClassName varchar(50) not null,ClassDesc varchar(50) not null)

 

5.update 数据

将所有年龄小于20岁的人的年龄都改成19（tage是Class表后加属性）update Class set tage = 19 where tage < 20将年龄为19岁的并且性别为0的人的姓名两边★改为☆update Class set ClassName =replace (tname,'★','☆') where tage=19 and tgender=0

 

6.删除数据

delete from Class --删除所有数据 自动编号没有恢复到默认值 可以根据条件来删除truncate table Class --重新设置了自动编号 删除只能一次性都清空，不能根据条件来删除 清除速度（性能）比delete语句快的多delete form Class where tage = 19 or tage is null --删除19岁或者空值删除重复数据只保留一条（id最小的一条），删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录delete from people where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1) and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)

 

7.条件查询，模糊查询

查询数学没有及格的学生的学号select fid as 学号,fmath as 分数from MyStudent where fmath<60查询年龄在20-30岁之间的男学生selectfname as 姓名 from MyStudent where fage between 20 and 30 and fgender='男'查询班级id 1 2 3 的所有学生select * from MyStudent where classid in (1,2,3)查询所有姓赵的同学 （通配符%表示：任意多个任意字符）select * from MyStudent where fname like '赵%'查询出姓名中只要包含一个‘民’字即可。select * from MyStudent where fname like '%民%'查询所有姓赵的同学，并且姓名字数是3个通配符 _ ：表示任意的单个字符。select * from MyStudent where fname like '赵__'select * from MyStudent where fname like '赵%' and len(fname)=3查询出姓名中包含‘民’或‘用’的同学通配符[]：表示中括号中的任意个字符，只选一个匹配通配符 ^a ：表示除了a这个字符都行。select * from MyStudent where fname like '%[民用]%'

 

8.聚合函数

查询数学成绩最高低分select max(fMath) as 数学成绩最高分 from MyStudentselect min(fMath) as 数学成绩最低分 from MyStudent平均分（计算平均分的时候对空值不处理）select avg(fMath) as 平均分 form MyStudent求数据记录中的总条数（总人数）select count(*) as 班级总人数 from MyStudent分数评级90以上 优秀80以上 良好70以上 中70以下 差select chengji,评级=casewhen shuxue >= 90 then '优秀'when shuxue >= 80 then '良好'when shuxue >= 70 then '中'else '差'endfrom Student

 

9.null 问题

请查询出学生表中所有数学成绩为null的人的信息select * from MyStudent where fMath is null查询所有fmath为非null的值select * from MyStudent where fMath is not nullnull值与任何数据运算后得到的还是null值。update MyStudent set fage=fage+1 where fid=1

 

10.分组group by

统计出mystudent表中，男女同学的个数select fgender as 性别, --这时，count（*）统计的是每一组的记录条数， 不是总条数count(*) as 人数from MyStudent group by fgender --先执行group by语句分组，分完组在统计每 组个数。 分出来几个组，那么count（*）就统计几次查询班级的男同学的人数大于2的信息having是group by的条件对分组后的数据进行筛选（与where类似，都是筛选，只不过having是用来筛选分组后的组的）select classid as 班级号,count(*) as 班级人数from TblStudentwhere fgender='男'group by classidhaving count(*)>2语句执行顺序selectdistinct / top 之类的关键字fgender as 性别, --5》选择列count(*) as 人数from MyStudent --1》先从表中拿到数据where fage>30 --2》从MyStudent的数据中筛选出所有年龄大于30岁的任的信息group by fgender --3》按照性别分组，分完组得到一个新的结果集having count(*)>500 --4》基于分组以后的结果集，然后再筛选，筛选出那些组中记录大于500的组order by 人数 asc --6》最后把显示出来的结果排序语句执行顺序from > where > group by > having > select > order by

 

11.日期函数

请查询出所有入职一年以上的员工信息select * from TblStudentwhere dateadd(year,1,tsday)

 

12.子查询

把一个查询结果作为另外一个查询的查询源 select * from (select * from Student where tbage between 3 and 5)as ct where tbname=5 --ct是新创的表名把另外一个查询的结果作为当前查询的条件来使用。子查询中=、!= 、< 、> 、<= 、>=之后只能返回单个值，如果多个值就会报错解决办法 可以用in 代替select * from Studentwhere tbage in(select tbage from Student where tbname=3)select * from Studentwhere tbage=(select tbage from Student where tbname=3)子查询分页显示第一页的数据分页查询的时候首先是将数据排序select * from Student order by id desc第一页 显示5条数据select Top 5 * from Student order by id desc第二页select top 5 * from Student where id not in (select top 5 * from Student order by id desc)order by id desc第三页select top 5 * from Student where id not in (select top (2*5) * from Student order by id desc)order by id desc开窗函数分页第七页数据 每页5条数据over属于开窗函数select * from(select * ,row_number() over( order by id desc) as paixu from Student) as tblwhere tbl.paixu between 6*5+1 and 7*5

 

13.连表查询

查询所有学生的姓名、年龄及所在班级 （班级在另一个表中）当多个列在不同的表中时，要跨表查询，所以一般可以使用inner jointc ts是对表名起的别名selectts.tsname,ts.tsage,tc.tclassnamefrom TblStudent as tsinner join TblClass as tc on ts.tsclassid=tc.tclassid（只查询两个表中都有的数据）full join 是查询所有的数据（没有的为空）左外联接（左联接）查询没有参加考试的学生的姓名与编号把左表（left join 关键字左边的表）中的全部记录都显示出来，对于那些在右表中能找到匹配的记录，显示对应匹配数据，对于那些右表中找不到匹配的记录显示为nullselectts.tsid,ts.tsname,TblScore.*from TblStudent as tsleft join TblSore.tsid=ts.tsid 右外联接表示要将右表（right join 右边的表）中的所有数据都显示，左表中只显示那些匹配的数据。selectts.tsid,ts.tsname,TblScore.*from TblStudent as tsright join TblSore.tsid=ts.tsid右外联与左外联都是先将匹配的数据找到，然后再将那些没有匹配的数据添加进来，（注意：不是一起查询出来的，有先后顺序）练习：查询所有学生（参加和未参加的考试）的学生姓名、年龄、成绩，如果没有参加考试显示缺考，如果小于english或者math 小于60分显示不及格selectts.tsname,ts.tsage,tscore.tsid,casewhen tscore.tenglish is null then '缺考'else convert(varchar(10),tscore.tenglish)end as 英语成绩,casewhen tscore.tmath id null then '缺考'else convert (varchar(10),tscore.tmath)end as 数学成绩,是否报考=casewhen tscore.tscoreid is null then '是'else '否'endfrom TblStudent as tsleft join TblScore as tscore on ts.tsid=tscore.tsid

 

14.视图

视图本身并不存储数据，只是存储的查询语句，如果把真实表中的数据修改后，则通过视图查询到的结果也变了。视图的目的是方便查询，所以一般情况下不能对视图进行增删改查在视图中的查询语句，必须为每一列创建一个列名create view vw2asselecttsname,casewhen tsage>13 and tsage<=16 then '少年'when tsage>50 then '老年'else '青壮年'end as 称呼from TblStudent在视图中不能使用order by语句。除非：另外还指定了top 或for xml错误create view vw3as select * from TblStudent order by tsage desc正确create view vw3as select top 3 * from TblStudent order by tsage desc

 

15.声明变量与使用

局部变量声明变量declare @name varchar(10)declare @age int赋值set @name='yhz'set @age=17输出值print @nameprint @age使用set与select为变量赋值的区别declare @rcount int set @rcount=(select count(*) from TblStudent)print @rcountdeclare @rcount int select @rcount=count(*) from TblStudentprint @rcount全局变量print @@languageprint @@versionprint 'aaa'+100通过判断@@error变量中是否不为0，就可以判断上一条sql语句执行是否出错了如果@@error为0，表示上一条sql语句执行没出错，如果@@error不为0，则表示上一条sql语句出错了。print@@error通过while计算1-100之间所有奇数的和声明变量并初始化declare @sum int=0declare @i int =1while @i<=100beginif @i%2<>0beginset @sum=@sum+@iendendprint @sum

 

16.事务

事务有四个属性：原子性 一致性 隔离性 持久性原子性：对于数据修改，要么全都执行，要么全都不执行一致性：当数据完成时，数据必须处于一致状态隔离性：对数据进行修改的所有并发事务时彼此隔离的。这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务永久性：事务完成后，他对数据库的修改被永久保持，事务日志能够保持事务的永久性打开事务begin transaction提交事务commit transaction回滚事务rollback transaction账户A给账户B转账 当一方出问题时，两个语句都不执行begin trandeclare @sum int=0update bank set balance =balance-1000 where cid='0001'set @sum=@sum+@@errorupdate banl set balance =balance+1000 where cid='0002'set @sum=@sum+@@errorif @sum<>0beginrollback tranprint '回滚'endelse begincommit tranprint '提交了'end

 

17.存储过程

创建一个自定义的存储过程create proc usp_HelloWorldas beginprint 'hello world'end输出存储过程exec usp_HelloWorld创建一个存储过程计算两个数的和create procedure usp_Add@num1 int,@num2 intas beginprint @num1+@num2end输出值exec usp_Add 100,230存储过程中的参数的问题存储过程如果有参数，则调用的时候必须为参数赋值exec usp_Add --不传参数则报错第二个参数如果用户不传，则有一个默认值create procedure usp_Add@num1 int,@num2 int 1000 --为存储过程的参数设置默认值as beginprint @num1+@num2end创建分页存储过程create proc usp_PageBum@pageSize int, --每页显示的数量@pageIndex int --第几页asbeginselect * from (select *,row_number()over (order by CityID asc)as num from S_City )as s where s.num between (@pageIndex -1)*@pageSize +1 and @pageIndex *@pageSize end查询第5页内容每页显示10条数据exec usp_PageBum 10,5删除一个存储过程drop proc usp_Add

 

18.触发器

尽量避免在触发器中执行耗时操作，因为触发器会与sql语句认为在同一个事务中（事务不结束，就无法释放锁）创建插入数据触发器create trigger tri_Teacher_insert_afteron Teacher after insertasbegindeclare @id intdeclare @name varchar(10)declare @phone intdeclare @mail varchar(50)select @id=tcid,@name=tcname,@phone=tcphone,@mail=tcmail from insertedprint @idprint @nameprint @phoneprint @mailend插入数据insert into Teacher values('网名好','12352536','Wjifdfji@qq.com')创建删除数据触发器不能有主键create trigger tri_Teacher_afteron Teacher after deleteasbegininsert into TeacherBakselect * from deletedend删除数据sql server中的触发器是表级触发器，无论删除多少行或者插入多少行，只触发一次是按语句来触发的，每次执行一次语句，触发一次触发器delete from Teacher where tcid>18

 

19.游标

定义游标declare cur_Student cursor fast_forward for select * from Student打开游标open cur_Student对游标的操作将每条数据读取并输出将游标向后移动一条fetch next from cur_Student将游标循环向后移动，直到末尾while @@fetch_status=0beginfetch next from cur_Studentend关闭游标close cur_Student释放资源deallocate cur_Student

 

20.（补充）全局临时表，局部临时表

局部临时表：表名以#为开头。只在当前会话中有效，不能跨连接访问。如果直接在连接会话中创建，则当前连接断开后删除，如果是在存储过程中创建的，则存储过程执行完毕后删除全局临时表：表名以##为开头。多个会话可共享全局临时表。当创建全局临时表的会话断开，并且没有用户正在访问全局临时表时删除

 

21.（补充）约束

删除一列（EmpAddress列）alter table Class drop column EmpAddress增加一列（增加一列EmpAddr varchar（1000））alter table Class Add EmpAddr varchar(1000)修改一下Emp 的数据类型（varchar（200））alter table Class alter column Emp varchar(200)为EmpId增加一个主键约束alter table Class add constraint PK_Class_EmpId primary key(EmpId)为EmpName增加一个唯一约束alter table Class add constraint UQ_Class_EmpName unique(EmpName)为性别增加一个默认约束，默认为男alter table Class add constraint DF_Class_EmpGender default('男') for EmpGender为年龄增加一个检查约束，年龄必须在1—120岁之间（包含）alter table Class add constraint CK_Class_EmpAge check(EmpAge>=0 and EmpAge<=120)增加外键约束，表Class中有一列EmpDeptId引用Student表中的DeptIdalter table Class add EmpDeptId int not nullalter table Student add constraint PK_Student_DeptId primary key(DeptId)alter table Class add constraint FK_Class_Student foreign key(EmpDeptId)references Student(DeptId)一条语句删除多个约束，约束名用 逗号 隔开alter table Class drop constraintPK_Student_DeptId,FK_Class_Student,CK_Class_EmpAge用一条语句为表增加多个约束alter table Class add constraint PK_Student_DeptId primary key(DeptId),constraint CK_Class_EmpAge check(EmpAge>=0 and EmpAge<=120),add constraint DF_Class_EmpGender default('男') for EmpGender