2021年SQL学习总结

1、精选word文档 下载可编辑sql学习总结上个星期，自主学习了sqlserver，基本了解了sqlserver的语法知识和结构，对sql有了一定的认识，现在我就对我这一星期对sql的学习做一个总结为了永久性的集中存放数据,并且还能体现数据与数据之间联系.此时需要用到数据库来保存数据.由于之前也接触过一两种数据库，向oracle，mysql,再就是sqlserver，这三种都是关系型数据库，都是用于存储大量的数据信息，是一种存储数据的仓库。不过，不管是哪一种数据库，他们之间的联系我觉得都是相通的，对于数据库的常见操作，无非就是“三建四句“。所谓的“三建四句“就是说，三建建库，建表，建约束；四句增

2、，删，改，查。sql语言，也称为结构化查询语言，是用于访问关系型数据库的专用语言，同样也是数据库的核心语言，总体上可以分为三类数据操纵语言（dml）,数据定义语言（ddl）、数据控制语言（dcl）。数据操纵语言（dml）主要有四种形式插入insert更新update删除delete查询select，涉及到数据的完整性简单查询、子查询、多表连接查询数据定义语言（ddl）主要用于创建数据库中的各种对象数据库数据表视图是一种观察数据的途径索引用于查询数据，提高效率同义词create，drop，alert，truncate（截断）数据控制语言（dcl）是用于授予和回收访问数据库的某种权限，并对数据库进

3、行监控。grant（授予），revoke（回收），denny（拒绝），rollbacksql中的运算符算术运算符、位运算符、比较运算符、逻辑运算符、通配运算符、字符串连接符、赋值运算符上面这些只是sqlserver数据的基本操作，而它真正强大的地方并不止这些，还有程序设计，sqlserver中的编程语言就是t-sql语言，是一种非过程化的语言。触发器触发器是一种特殊类型的存储过程，主要是通过实践进行触发而被执行。它的主要作用就是能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。存储过程（storedprocedure）类似c语言中的函数，是一组为了完成特定功能的sql语句集，经编

4、译后存储在数据库中。用户通过指定存储过程的名字并给出参数来执行它。游标游标是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。将批操作变成行操作，对结果集中得某行进行操作。扩展阅读sql学习总结sql总结1插入，删除，修改（语法）（插入）insertinto列名values(更新)updateset=where（删除）deletefromwhere注意插入语句如果没有能很好的记住字段在表中的顺序最好是写上列名条件查询selectfromwhereorder3给字段取别名as用法使用as来为列其别名select学号asstudentno，姓名asname,家庭地址asaddressfro

5、mstudentinfowhere家庭地址黑龙江哈尔滨使用=来为列另起别名select姓名=firstname+.+lastnamefromemployees注意+连接字符数据，结果为字符串数据的连接2如果+连接数值型，结果为数值的和4函数字符串函数函数名charindexlenlowerupperltrimrtrimrightreplacestudiff描述用来寻找一个指定的字符在另一个字符串中的起始位置返回传递给它的字符串长度将传递给他的字符串变成小写将传递给他的字符串变成大写清空字符左边的空格清空字符右边的空格替换一个字符串中的字符在一个字符串中，删除指定例如selectcharinde

6、x(lucia,iamlucia)返回6selectlen（sql）selectlower（sql）返回sqlselectltrim(你好)返回你好selectreplace(连玉龙,龙,凤)返回连玉凤selectstudiff(abcdefg,2,3,我的音玛丽莲.梦露，2）梦露从字符串右边返回指定数目字符selectright（长度的字符，并在该位置插入一个新的字符串乐我的世界)返回a我的音乐我的世界efg日期函数getdatedateadddatediffdatenamedatepart数学函数convertcurrent_userdatalengthhost_name备注日期函数中da

7、tediff可以用来计算时间差（例如，年龄）getdate（）用来设置默认这些函数容易记也容易忘，尤其是书写的格式规范。用来转变数据类型返回当前用户的名字给指定表达式的字节数返回当前用户所登录的计算机名字selectconvert（varchar（5），1234）selectcurrent_user，返回你登录的用户名selectdatalegth（中国）selectuser_name(1)返回从任何数据库中返回abo取得当前的系统日期将指定的数值添加到指定的日期后面的日期两个日期之间的指定日期日期中指定日期部分的字符串日期中指定日期部分的整数形式selectgetdate()selectda

8、teadd（mm,4,01/01/1999）返回05/01/19999selectdatediff(mm,01/01/1999,05/01/1999)selectdatename（dw，01/01/201*）selectdatepart（day，01/15/201*）5like用法*通配符*%包含零个或更多字符串*_(下划线)任何单个字符*指定范围（a-f）或集合abcdf中任何的一个单个字符*不属于指定范围a-f或集合abcdef的任何单个字符例select*from数据表where编号like008%ac%可能会查询出的编号值为(a)。a、0090acdb、007_affc、008&dcg

9、d、008c聚合函数*count(),*max(),*min(),*avg()，sum（）1分组查询groupbyhavingselect任职部门，count（*）fromlucia工作室where目前的薪资=201*groupby任职部门havingcount（*）4多表查询*innerjoin内连接:两张表的顺序颠倒对结果没有影响*leftjoin左连接左边的表是主表，表的顺序不能颠倒*rightjoin右连接右边的表是主表，表的顺序不能颠倒例selects.姓名,c.课程编号,c.笔试成绩fromstudentinfoassinnerjoinscoreinfoasconc.学号=s.学号

10、建表的三大范式第一范式的目标是确保每列的原子性如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1nf）如果一个表中个字段关系满足1nf，并且除了主键以外的其它列，都依赖于该主键，则满足第二范式（2nf）第二范式要求每个表只描述一件事情(去除不依赖主键或部分依赖主键的列)由第二范式可知产品价格不是依赖订单的编号如果一个关系满足2nf，并且除了主键以外的其它列都不传递依赖于主键列，则满足第三范式（3nf）第三范式的目标是确保每列都直接依赖主键由第三范式可知产品的结果是依赖产品编号而不是直接依赖订单编号的主键(primarykey)，外键(foreignkey)创建/添加

11、语法constraintpk_主键名称primarykey(主键名称)constraintfk_外键的名称foreignkey(外键名称)references父表（父表字段）修改altertable子表addconstraintfk_外键的名称foreignkey(外键名称)references父表（父表字段）(b)其他约束唯一约束alterconstraintuq_stuidunique(stuid)默认约束alterconstraintdf_addressdefault(地址不详)foraddress检查约束alterconstraintck_stuagecheck(stuagebetwe

12、en15and40)10变量（局部变量，全局变量）局部变量*声明局部变量declare变量名数据类型*赋值*set变量名=值*select变量名=值全局变量是系统的，是不能改变的较常用的是变量erroridentitylanguagemax_connectionsrowcountservernametrancountversion变量含义最后一个t-sql错误的错误号最后一次插入的标识值当前使用的语言的名称可以创建的同时连接的最大数目受上一个sql语句影响的行数本地服务器的名称当前连接打开的事务数sqlserver的版本信息1ifelse,while,caseend,whenthen用法例题=

13、ifelse=ifexists(select*fromsysobjectswherename=newtable)droptablenewtableselect姓名,s.学号,笔试成绩,机试成绩,是否通过=casewhen笔试成绩60and机试成绩60then1else0endintonewtablefromstudentinfoassleftjoinscoreinfoascons.学号=c.学号go=while=declarewrittenavgdecimaldeclarelabavgdecimalselectwrittenavg=avg(笔试成绩)fromstudentscoreselect

14、labavg=avg(机试成绩)fromstudentscoreif(writtenavglabavg)beginprint(笔试成绩大于机试成绩)while(1=1)beginupdatestudentscoreset机试成绩=机试成绩+1if(selectmax(机试成绩)fromstudentscore)=97breakendendelsebeginprint(机试成绩大于笔试成绩)while(1=1)beginupdatestudentscoreset笔试成绩=笔试成绩+1if(selectmax(笔试成绩)fromstudentscore)=97breakendendgoselect

15、*fromstudentscorego=caseend=select姓名,学号,笔试成绩=casewhen笔试成绩isnullthen缺考elseconvert(varchar(5),笔试成绩)end,机试成绩=casewhen机试成绩isnullthen缺考elseconvert(varchar(5),机试成绩)end,是否通过=casewhen是否通过=1then是when是否通过=0then否endfromnewtablego=whenthen=ifexists(select*fromsysobjectswherename=newtable)droptablenewtableselect

16、姓名,s.学号,笔试成绩,机试成绩,是否通过=casewhen笔试成绩60and机试成绩60then1else0endintonewtablefromstudentinfoassleftjoinscoreinfoascons.学号=c.学号goselect*fromnewtable12子查询（in，exists）select姓名fromstudentinfowhere学号in（select学号fromscoreinfo）go注意基本上in可以等价于“=”可是“=”只能是子查询返回的是单个结果，如果是多个查询结果只能用inexists用法用例ifexists(select*fromsysdata

17、baseswherename=luciabank)dropdatabaseluciabank14存储过程优点执行的速度更快允许模块化程序设计提高系统安全性减少网络流通量11分类系统存储过程“sp_”或者是“xp_”常用系统存储过程sp_database列出服务器上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息sp_rename更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex:查看某个表的索引sp_password

18、:添加或修改登录帐户的密码sp_helptext:显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本12自定义的存储过程11定义存储过程的语法和调用的语法createprocedure存储过程名参数1数据类型=默认值output,参数n数据类型=默认值outputassql语句调用的语法exec过程名参数12不带参数的存储过程createprocedurep_scoreinfoasdeclarewrittenavgdecimaldeclarelabavgdecimalselectwrittenavg=avg(笔试成绩),labavg=avg(机试成绩)fromstuden

19、tscoreprint笔试成绩的平均分+convert(nvarchar(5),writtenavg)print机试成绩的平均分+convert(nvarchar(5),labavg)if(writtenavg60andlabavg60)print本班的成绩优秀elseprint本班的成绩很差print-print本班成绩不及格的人名单selects.学号,姓名,笔试成绩,机试成绩fromstudentinfos,studentscorecwheres.学号=c.学号and笔试成绩可以规定及格的分数-*/createprocedureproc_scoreinfohaveparams(write

20、passdecimal,-默认值是60labpassdecimal)asdeclarewrittenavgdecimaldeclarelabavgdecimalselectwrittenavg=avg(笔试成绩),labavg=avg(机试成绩)fromstudentscoreprint笔试成绩的平均分+convert(nvarchar(5),writtenavg)print机试成绩的平均分+convert(nvarchar(5),labavg)if(writtenavgwritepassandlabavglabpass)print本班的成绩优秀elseprint本班的成绩很差print-pr

21、int本班成绩不及格的人名单selects.学号,姓名,笔试成绩,机试成绩fromstudentinfos,studentscorecwheres.学号=c.学号and笔试成绩labpass)print本班的成绩优秀elseprint本班的成绩很差print-selectsumnotpass=count(*)fromstudentscorewhere笔试成绩=3print本班成绩不及格的人数是+convert(varchar(5),sum)+及格人太少，及格分数要在调低elseprint及格人数适中，及格分数线可以dropprocproc_outputscoreinfogo14raiserro

22、r用法语法raiserror(msg_id|msg_str,severity,statewithoption,.n)msg_id在sysmessages系统表中指定用户定义错误信息msg_str用户定义的特定信息，最长255个字符severity定义严重性级别。用户可使用的级别为018级state表示错误的状态，1至127之间的值option指示是否将错误记录到服务器错误日志中declaresumint,tintexecproc_outputscoreinfosumoutput,80sett=errorprint错误号+convert(nvarchar(10),t)ift0raiserror(

23、及格线错误，请重新输入（1100之间）,16,1)returnprint*ifsum=3print本班成绩不及格的人数是+convert(varchar(5),sum)+及格人太少，及格分数要在调低elseprint及格人数适中，及格分数线可以dropprocproc_outputscoreinfogo15事物11事物概念事务(transaction)是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元12事物四个属性原子性（atomicity）事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要

24、么都不执行一致性（consistency）当事务完成时，数据必须处于一致状态隔离性（isolation）对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务永久性（durability）事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性13管理事务t_sql语句开始事务beigintransaction提交事务committransaction回滚事物rollbacktransaction14判断出错的语句error15事务的分类显性事务用begintransaction明确指定事务的开始，这是最常用的事务类型隐性事务通过设置

25、setimplicit_transactionson语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。当该事务完成时，再下一个t-sql语句又将启动一个新事务自动提交事务这是sqlserver的默认模式，它将每条单独的t-sql语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚16例题-转账事物处理-begintransactiondeclareerrorcountintseterrorcount=0updateclientaccountset余额=余额-1000where账号=6226900707220987seterrorcount=errorcount+erro

26、rupdateclientaccountset余额=余额+1000where账号=6226900707220654seterrorcount=errorcount+erroriferrorcount0beginprint交易失败，回滚事物rollbacktransactionendelsebeginprint交易成功committransactionendgoprint查看转账事物后余额select*frombankaccountgo16索引（提高查询速度，但是占空间）是sqlserver编排数据的内部方法11索引的类型唯一索引唯一索引不允许两行具有相同的索引值主键索引为表定义一个主键将自动创

27、建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空聚集索引(clustered)表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个非聚集索引(non-clustered)非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个12语法createuniqueclustered|nonclusteredindexindex_nameontable_name(column_name)withfillfactor=xunique表示唯一索引，可选clustered、noncl

28、ustered表示聚集索引还是非聚集索引，可选fillfactor表示填充因子，指定一个0到100之间的值，值指示索引页填满的空间所占的百分比13索引创建的指定原则请按照下列标准选择建立索引的列该列用于频繁搜索该列用于对数据进行排序请不要使用下面的列创建索引列中仅包含几个不同的值表中仅包含几行。为小型表创建索引可能不太划算，因为sqlserver在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长17视图概念视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上11视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数

29、据库抽象为一个逻辑数据库12语法createviewview_nameas13例题-创建cs_kc视图，包括雇员名字，部门名字、其选修的课程号及成绩收入在之间的雇员号码-createviewcs_kcas顾员姓名,d.departmentnameas部门名称,e.employeeidas雇员编号fromemployeesase,departmentsasd,salaryasswheres.incomebetween201*and3000ande.departmentid=d.departmentidande.employeeid=s.employeeid18触发器触发器是一种特殊的存储过程，类似于事件函数，sqlserver允许为insert、update、delete创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列t-sql语句。11语法创建触发器用createtriggercreatetrigger触发器名称on表名forinse