SQL数据库操作

数据库操作主讲：宁茗数据库作用：SQL是用于访问和处理数据库的标准的计算机语言。SQL是什么？SQL，指结构化查询语言，全称是StructuredQueryLanguage。SQL让您可以访问和处理数据库。SQL是一种ANSI（AmericanNationalStandardsInstitute美国国家标准化组织）标准的计算机语言。SQL能做什么？SQL面向数据库执行查询SQL可从数据库取回数据SQL可在数据库中插入新的记录SQL可更新数据库中的数据SQL可从数据库删除记录SQL可创建新数据库SQL可在数据库中创建新表SQL可在数据库中创建存储过程SQL可在数据库中创建视图SQL可以设置表、存储过程和视图的权限一些最重要的SQL命令SELECT-从数据库中提取数据UPDATE-更新数据库中的数据DELETE-从数据库中删除数据INSERTINTO-向数据库中插入新数据CREATEDATABASE-创建新数据库ALTERDATABASE-修改数据库CREATETABLE-创建新表ALTERTABLE-变更（改变）数据库表DROPTABLE-删除表CREATEINDEX-创建索引（搜索键）DROPINDEX-删除索引SELECT语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。选取所有列：Select*from表名选取某些列：Select列名,列名from表名多个条件时的查询：1：Select*/列名from表名where（条件1and条件2）or条件3//条件不唯一，可多列取值为条件2：Select*/列名from表名where列名in（’值1’,’值2’）//同一列中，包含某些不同的值为条件SELECT语句单个条件时的查询：Select*/列名from表名where列名=‘值1’选取某一列中所有不同的值(去重):Selectdistinct列名from表名选取前N行数据：SelecttopN列名/*from表名选取第N行数据:Selecttop1列名from表名where列名notin(selecttopN-1列名from表名)SELECT语句选取重新排序后的第N行：select*from(select*,line=ROW_NUMBER()over(orderby列名asc)from表名)mwhereline=N(也可以大于N)选取一定百分比的数据：SelecttopNpercent*from表名模糊查询：Select*from表名where列名like‘%N%’SELECT语句通配符示例：‘N%’:以N开头‘%N%’：包含N‘%N’：以N结尾‘_N’：第一个字符之后是N，一共两个字符‘_N_M’：第一和第四个字符是N和M，一共四个字符‘[ALN]%’：以A或L或N开头‘[^ALN]%’：不以A或L或N开头，过滤掉以A或L或N开头的数据‘%[ALN]’：以A或L或N结尾‘%[^ALN]’：不以A或L或N结尾‘%[ALN]%’：包含A或L或N‘%[^ALN]%’：不包含A或L或NSELECT语句选取介于两个值之间的数据，比如首字母介于N和W之间：Select*from表名where列名between‘值1’and‘值2’为临时的数据或者列插入别名：1:列名后加空格指定别名2:(列名)别名3:列名as别名选取两个表中的数据：Select表1.列1，表2.列2from表1，表2where表1.列1=表2.列2Select表1.列1，表2.列2from表1innerjoin表2on表1.列1=表2.列2SELECT语句Join/innerjoin：内连接-取交叉-左（匹配值）+右（匹配值）Leftjoin：左连接-左表为主表-左（所有值）+右（匹配值）Rightjoin：右连接-右表为主表-左（匹配值）+右（所有值）Fulljoin：全连接-取两个表所有值-左（所有值）+右（所有值）选取某列中的重复项，并计算重复数量：Select列名1，count（列名1）asRepeatNumfrom表名where列名1=‘值1’Groupby列名1Havingcount(列名1)1SELECT语句Select语句结果集的合并1：取不同表中的不同列的值放在同一列中Select列名1from表1Union/unionallSelect列名2from表2注意：1：列名1与列名2必须有相同的数据类型2：上下两个表中搜索的列名数量必须相同复制表：从一个表中选择数据，然后插入到另一个新表中（常用于数据库中某一张表的备份，或数据库的备份）1：表间互传数据：Select*into新表名from表2where列名=‘值1’2：数据库间传数据：Select*into数据库2.dbo.新表名from数据库1.dbo.表1//后者为原数据库的表SQLUPDATE语句UPDATE语句用于更新表中已存在的记录。1：更新某一行中的某一列：Update表名set列名1=‘值1’，列名2=‘值2’where列名=‘值’2：将某一列中的值换成同一个值：Update表名set列名=‘值’3：将某一列中的某一字段（有多个）批量转换成另一字段：Update表名set列名=replace（列名，’原值’,’新值’）4：将某一列中的时间批量修改（比如天数加两天）：Update表名set列名=dateadd(day,2,列名)5：将某一列中的字母批量转换成大写或小写：Update表名set列名1=upper（列名1）Update表名set列名1=lower（列名1）6:修改自增不从1开始：dbcccheckident(‘表名’,reseed,0)//从1开始dbcccheckident(‘表名’,reseed,N-1)//从N开始SQLDELETE语句DELETE语句用于删除表中的行。1：删除表中的某一行：Deletefrom表名where列名=‘值1’2：删除所有行，清空所有数据1：不清空自增量，下次插入仍然从最后一次的自增数字开始Deletefrom表名Delete表名2：清空自增量，下次插入从1开始Truncatetable表名3：删除一列（包括列名）Altertable表名dropcolumn列名4:清空某一列中的所有数据Update表名set列名=null5:删除表Droptable表名6：删除数据库Dropdatabase数据库名SQLINSERTINTO语句INSERTINTO语句用于向表中插入新记录。1：插入一行数据：Insertinto表名values（’值1’,’值2’,’值3’,’值4’,’值5’,·······）2：插入值（在指定列）Insertinto表名（列名1，列名2，·····）values（’值1’,’值2’,······）3：数据多行插入，一列一次插入多个值Insertinto表名（列名）values（’值1’），（’值2’），（’’），·······4：数据多行插入，所有行都插入数据Insertinto表名values（’值1’,’值2’,···）（’值3’,’值4’,···）ALTERTABLE语句ALTERTABLE语句用于在已有的表中添加、删除或修改列。1：增加一列Altertable表名add列名数据类型2：修改列名Exec.sp_rename‘表名.原列名’，’新列名’3：修改列的数据类型Altertable表名altercolumn列名新的数据类型4：删除一列：Altertable表名dropcolumn列名SQLCREATEDATABASE语句CREATEDATABASE语句用于创建数据库1：创建数据库：Createdatabase数据库名2：创建表：Createtable表名（列名1数据类型1primarykeynotnull，列名2数据类型2identity(开始数，增幅数)notnull,Constraint约束名unique(列名1，列名2)//添加唯一性验证）CREATETABLE-创建新表3：在关联两张表的时候就创建表：Select表1.列名1，表2.列名2into新表1from表1innerjoin表2on表1.Guid=表2.RYGuid4:修改表的时候增加/删除约束：Altertable表名addconstraint约束名unique（列名1，列名2）Altertable表名dropconstraint约束名5：外键约束（foreignkey)用于预防破坏表之间的连接动作预防非法数据插入外键，因为它必须是它指向的那个表中的值之一Createtable表名1（列名1数据类型notnullprimarykey,列名2数值类型notnull,Constraint约束名1foreignkey(列名2）references表2（列1））CREATETABLE-创建新表6：修改表时创建/删除外键Altertable表名1addconstraint约束名1foreignkey(列名1)references表2（列名2）Altertable表名1dropconstraint约束名//删除外键7：check约束-用于约束限制列中值的范围1：如果对单个列定义check约束，那么该列只允许特定的值2：如果对一个表定义check约束，那么此约束会在特定的列中对值进行限制Createtable表名（列1数值类型1notnullcheck（约束条件）,列2数值类型2notnull,Constraint约束名check（约束条件1and约束条件2））8：修改表时创建/删除约束Altertable表名addconstraint约束名check（约束条件1and约束条件2）Altertable表名dropconstraint约束名创建表的时候默认值的插入1：创建表的时候插入默认值：Createtable表名（列名1数据类型1notnull,列名2数据类型2default‘中国’/getdate()）2:修改表的时候添加默认值Altertable表名addconstraint约束名default‘中国’for列名Altertable表名dropconstraint约束名创建索引在不读取整个表的时候，索引是数据库应用程序可以更快的查找数据更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是因为索引本身也需要更新，因此，理想的做法是仅仅在经常被使用列上面穿件索引在表上创建一个唯一索引，唯一意味着两个行不能拥有相同的索引值Createindex索引名on表名（列名）Createindex索引名on表名（列名1desc,列名2desc）Createuniqueindex索引名on表名（列名1，列名2）Dropindex表名.索引名函数AVG（column）//求取平均值例：找到RowID的值比RowID平均值高的名字SelectDisplayName,RowID,(selectAVG(RowID)fromdbo.SYS_User)asPJFromdbo.SYS_UserwhereRowID(selectAVG(RowID)fromdbo.SYS_User)//不用写Having语句Count（列名）//统计行数Right（列名，截取位数）//截取字符，仅限字母，从左边截取Left（列名，截取位数）//截取字符，仅限字母，从右边截取Substring(列名，截取开始位置，截取字符个数)//截取字符，仅限字母，从左边截取函数Max(列名)//求最大值Min（列名）//求最小值Sum（列名）//求和Year（列名）//取日期的年份month(列名)//取日期的月份day（列名）//取日期的天数Upper（列名）//转换字符为大写Lower（列名）//转换字符为小写Round（列名，必须要返回