SQL学习总结.doc

《SQL学习总结.doc》由会员分享，可在线阅读，更多相关《SQL学习总结.doc（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL学习总结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语言中的函数，是一组为了完成特定功能的S

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

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

6、如SELECTCHARINDEX(lucia,Iamlucia)返回：6Selectlen（sql）Selectlower（SQL）返回sqlSelectltrim(你好)返回你好Selectreplace(连玉龙,龙,凤)返回：连玉凤Selectstudiff(ABCDEFG,2,3,我的音玛丽莲.梦露，2）梦露从字符串右边返回指定数目字符Selectright（长度的字符，并在该位置插入一个新的字符串乐我的世界)返回：A我的音乐我的世界EFG日期函数GetdateDateaddDatediffDatenameDatepart数学函数ConvertCurrent_userDatalength

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

8、ectgetdate()Selectdateadd（mm,4,01/01/1999）返回05/01/19999Selectdatediff(mm,01/01/1999,05/01/1999)Selectdatename（dw，01/01/2022）Selectdatepart（day，01/15/2022）5like用法*通配符*%包含零个或更多字符串*_(下划线)任何单个字符*指定范围（a-f）或集合abcdf中任何的一个单个字符*不属于指定范围a-f或集合abcdef的任何单个字符例SELECT*FROM数据表WHERE编号LIKE008%AC%可能会查询出的编号值为(a)。A、0090A

9、CDB、007_AFFC、008&DCGD、008C6.聚合函数*Count(),*Max(),*Min(),*Avg()，sum（）6.1分组查询groupbyhavingSelect任职部门，count（*）Fromlucia工作室Where目前的薪资=2022Groupby任职部门Havingcount（*）47.多表查询*innerjoin内连接:两张表的顺序颠倒对结果没有影响*leftjoin左连接：左边的表是主表，表的顺序不能颠倒*rightjoin右连接：右边的表是主表，表的顺序不能颠倒例SELECTS.姓名,C.课程编号,C.笔试成绩FROMStudentInfoASSINNE

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

11、主键(primarykey)，外键(foreignkey)创建/添加语法ConstraintPK_主键名称primarykey(主键名称)ConstraintFK_外键的名称foreignkey(外键名称)References父表（父表字段）修改Altertable子表AddConstraintFK_外键的名称foreignkey(外键名称)References父表（父表字段）(b)其他约束：唯一约束AlterconstraintUQ_stuIDunique(stuID)默认约束AlterconstraintDF_addressdefault(地址不详)foraddress检查约束Alterc

12、onstraintCK_stuAgecheck(stuAgebetween15and40)10变量（局部变量，全局变量）局部变量*声明局部变量DECLARE变量名数据类型*赋值*SET变量名=值*SELECT变量名=值全局变量是系统的，是不能改变的较常用的是：变量ERRORIDENTITYLANGUAGEMAX_CONNECTIONSROWCOUNTSERVERNAMETRANCOUNTVERSION变量含义最后一个T-SQL错误的错误号最后一次插入的标识值当前使用的语言的名称可以创建的同时连接的最大数目受上一个SQL语句影响的行数本地服务器的名称当前连接打开的事务数SQLServer的版本信

13、息11.ifelse,while,caseend,whenthen用法例题=ifelse=ifexists(select*fromsysobjectswherename=NewTable)droptableNewTableselect姓名,S.学号,笔试成绩,机试成绩,是否通过=casewhen笔试成绩60and机试成绩60then1else0endintoNewTablefromstudentinfoassleftjoinscoreinfoascons.学号=c.学号go=while=declarewrittenAvgdecimaldeclarelabavgdecimalselectwrit

14、tenAvg=avg(笔试成绩)fromstudentscoreselectlabavg=avg(机试成绩)fromstudentscoreif(writtenAvglabavg)beginprint(笔试成绩大于机试成绩)while(1=1)beginupdatestudentscoreset机试成绩=机试成绩+1if(selectmax(机试成绩)fromstudentscore)=97breakendendelsebeginprint(机试成绩大于笔试成绩)while(1=1)beginupdatestudentscoreset笔试成绩=笔试成绩+1if(selectmax(笔试成绩)f

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

16、ename=NewTable)droptableNewTableselect姓名,S.学号,笔试成绩,机试成绩,是否通过=casewhen笔试成绩60and机试成绩60then1else0endintoNewTablefromstudentinfoassleftjoinscoreinfoascons.学号=c.学号goselect*fromnewTable12子查询（in，exists）SELECT姓名FROMStudentInfoWHERE学号IN（SELECT学号FROMScoreInfo）GO注意：基本上in可以等价于“=”可是“=”只能是子查询返回的是单个结果，如果是多个查询结果只能用

17、inExists用法用例：IFEXISTS(SELECT*FROMsysdatabasesWHEREname=LuciaBank)DROPDATABASELuciaBank14存储过程优点：执行的速度更快允许模块化程序设计提高系统安全性减少网络流通量14.1分类系统存储过程“sp_”或者是“XP_”常用系统存储过程Sp_database：列出服务器上的所有数据库Sp_helpdb：报告有关指定数据库或所有数据库的信息Sp_rename：更改数据库的名称Sp_tables：返回当前环境下可查询的对象的列表sp_columns：返回某个表列的信息sp_help：查看某个表的所有信息sp_helpc

18、onstraint：查看某个表的约束sp_helpindex:查看某个表的索引sp_password:添加或修改登录帐户的密码sp_helptext:显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本14.2自定义的存储过程14.2.1定义存储过程的语法和调用的语法CREATEPROCEDURE存储过程名参数1数据类型=默认值OUTPUT,参数n数据类型=默认值OUTPUTASSQL语句调用的语法EXEC过程名参数14.2.2不带参数的存储过程/*-存储过程（不带参数）题目：查看本次考试的平均分数，并查看没有通过的学生的名单-*/createprocedurep_scor

19、eInfoasdeclarewrittenAvgdecimaldeclarelabAvgdecimalselectwrittenAvg=avg(笔试成绩),labAvg=avg(机试成绩)fromstudentscoreprint笔试成绩的平均分+convert(nvarchar(5),writtenAvg)print机试成绩的平均分+convert(nvarchar(5),labAvg)if(writtenAvg60andlabAvg60)print本班的成绩优秀elseprint本班的成绩很差print-print本班成绩不及格的人名单selects.学号,姓名,笔试成绩,机试成绩from

20、studentinfos,studentscorecwheres.学号=c.学号and笔试成绩可以规定及格的分数-*/createprocedureproc_scoreinfoHaveParams(writePassdecimal,-默认值是60labPassdecimal)asdeclarewrittenAvgdecimaldeclarelabAvgdecimalselectwrittenAvg=avg(笔试成绩),labAvg=avg(机试成绩)fromstudentscoreprint笔试成绩的平均分+convert(nvarchar(5),writtenAvg)print机试成绩的平均

21、分+convert(nvarchar(5),labAvg)if(writtenAvgwritePassandlabAvglabPass)print本班的成绩优秀elseprint本班的成绩很差print-print本班成绩不及格的人名单selects.学号,姓名,笔试成绩,机试成绩fromstudentinfos,studentscorecwheres.学号=c.学号and笔试成绩labPass)print本班的成绩优秀elseprint本班的成绩很差print-selectsumNotPass=count(*)fromstudentscorewhere笔试成绩=3print本班成绩不及格的人

22、数是+convert(varchar(5),sum)+及格人太少，及格分数要在调低elseprint及格人数适中，及格分数线可以dropprocproc_outputscoreInfogo14.2.4RAISERROR用法语法：RAISERROR(msg_id|msg_str,severity,stateWITHoption,.n)msg_id：在sysmessages系统表中指定用户定义错误信息msg_str：用户定义的特定信息，最长255个字符severity：定义严重性级别。用户可使用的级别为018级state：表示错误的状态，1至127之间的值option：指示是否将错误记录到服务器错

23、误日志中/*-带参数（输出）的存储过程题目：查看本次考试的平均分数，并查看没有通过的学生的人数当输入的及格分数不再1到100之间则报错-*/declaresumint,tintexecproc_outputscoreInfosumoutput,80sett=errorprint错误号：+convert(nvarchar(10),t)ift0raiserror(及格线错误，请重新输入（1100之间）,16,1)returnprint*ifsum=3print本班成绩不及格的人数是+convert(varchar(5),sum)+及格人太少，及格分数要在调低elseprint及格人数适中，及格分数

24、线可以dropprocproc_outputscoreInfogo15事物15.1事物概念事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元15.2事物四个属性原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行一致性（Consistency）：当事务完成时，数据必须处于一致状态隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务永久性（Durab

25、ility）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性15.3管理事务T_SQL语句开始事务：beigintransaction提交事务：committransaction回滚事物：rollbacktransaction15.4判断出错的语句ERROR15.5事务的分类：显性事务：用BEGINTRANSACTION明确指定事务的开始，这是最常用的事务类型隐性事务：通过设置SETIMPLICIT_TRANSACTIONSON语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。当该事务完成时，再下一个T-SQL语句又将启动一个新事务自动提交事务：这是SQLSe

26、rver的默认模式，它将每条单独的T-SQL语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚15.6例题-转账事物处理-begintransactiondeclareerrorcountintseterrorcount=0updateclientAccountset余额=余额-1000where账号=6226900707220987seterrorcount=errorcount+errorupdateclientAccountset余额=余额+1000WHERE账号=6226900707220654seterrorcount=errorcount+erroriferrorco

27、unt0beginprint交易失败，回滚事物rollbacktransactionendelsebeginprint交易成功committransactionendgoprint查看转账事物后余额select*fromBankAccountgo16索引（提高查询速度，但是占空间）是SQLServer编排数据的内部方法16.1索引的类型唯一索引：唯一索引不允许两行具有相同的索引值主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一

28、个非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个16.2语法CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXindex_nameONtable_name(column_name)WITHFILLFACTOR=xUNIQUE表示唯一索引，可选CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选FILLFACTOR表示填充因子，指定一个0到100之间的值，值指示索引页填满的空间所占的百分比16.3索引创建的指定原则请按照下

29、列标准选择建立索引的列该列用于频繁搜索该列用于对数据进行排序请不要使用下面的列创建索引：列中仅包含几个不同的值表中仅包含几行。为小型表创建索引可能不太划算，因为SQLServer在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长17视图概念：视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上17.1视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库17.2语法CREATEVIEWview_nameAS17.3例题-创建CS_KC视图，包括雇员名字，部门名字、其选修的课程号及成绩收入在之

30、间的雇员号码-createviewCS_KCasselecte.nameas顾员姓名,d.departmentnameas部门名称,e.EmployeeIDas雇员编号fromEmployeesase,Departmentsasd,Salaryasswheres.Incomebetween2022and3000ande.departmentID=d.departmentIDande.employeeID=s.employeeID18触发器触发器是一种特殊的存储过程，类似于事件函数，SQLServer允许为INSERT、UPDATE、DELETE创建触发器，即当在表中插入、更新、删除记录时，触发

31、一个或一系列T-SQL语句。18.1语法创建触发器用：CREATETRIGGERCREATETRIGGER触发器名称ON表名FORINSERT、UPDATE或DELETEAST-SQL语句18.2触发器中用到两个临时表：Deleted和Inserted一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：Deleted，Inserted虚拟表Inserted表记录新增时，用来存放新增的数据记录表记录更新时，用来存放更新的新记录表记录删除时，不存储任何信息虚拟表Deleted表记录新增时，不存放任何记录表记录更新时，用来存放更新前的记录表记录删除时，用来存放删除前的数据记录18.4例

32、题-修改Departments表departmentID字段值时，该字段在Employees表中的对应值也应修改；-createtriggerdeparmentsudateondepartmentsforupdateasbeginif(columns_updated()&01)0updateemployeessetdepartmentID=(selectins.departmentIDfrominsertedins)wheredepartmentID=(selectdepartmentIDfromdeleted)end-1向Employees表添加一记录时，该记录的departmentID值在

33、-Departments表中应存在createtriggeremployeesinsertonemployees-createtriggeremployeesinsertonemployeesforinsertupdateasbeginif(selectins.departmentidfrominsertedins)notin(selectdepartmentidfromdepartments)rollbackend-1.3.删除Departments表中一记录时，该记录departmentID字段值在Employees表中对应的记录也应删除-createtriggerdepartmentsdeleteondepartmentsfordeleteasbegindeletefromemployeeswheredepartmentID=(selectdepartmentIDfromdeleted)endgo第 15 页 共 15 页