常用SQL语句培训.ppt

常用SQL语句,2007-12-4,1,查询语句,select*fromclass;查询表class中的所有列.selectclass,teacherfromclass;查询表class中的列class,teacherSelectcount(*)ascount_allfromclass;返回表class中的总行数给结果集.selectsum(studentnumber)asall_student_numberfromclass:返回class表中studentnumber的总数给结果集selectavg(studentnumber)asavg_student_numberfromclass:返回class中studentnumber的平均值给结果集Selectmax(studentnumber)asmax_studentnumberfromclass:求某个字段的最大值,最小值.min是最小值.select*fromclasswherestudentnumber=(Selectmax(studentnumber)fromclass):求的最大值可以作为条件被引用.Select*fromclasswherestudentnumber=50(50,=50,50):返回studentnumber=50(50,=50,50andteacher=li两个查询条件用and表示与,or表示或.Select*fromclasswherestudentnumberin(44,55):in表示studentnumber为括号中所列的可能值.Select*fromclasswhereclassin(selectclassfromstudent):in中的内容可以是另一个查询语句的结果.Selectdistinctclassfromstudent:查询字段的值不重复select*fromclassorderbystudentnumber(asc,desc):对查询结果排序,可以按升序,也可以按降序.,2,查询语句(续),selectclass,count(*)fromstudentgroupbyclass:查询结果根据groupby分组.selectclass,count(*)fromstudentgroupbyclasshavingcount(*)=5:对分组的结果再用条件过滤select*fromstudentwhereid19:UNION:将两个查询语句的查询结果放在一起,如果有重复的行,就删除,如果是UNIONALL:则重复的行不删除.模糊匹配查询:select*fromstudentwherenamelike%ang%整型,日期类型的字段可以指定范围.用betweenselect*fromstudentwherebornbetween1980-05-04and2983-10-18select*,12fromstudent返回结果中增加一列,且值均为12.selectRTRIM(class)+RTRIM(teacher)ASname1,studentnumberfromclass:将两个字段先删除尾部的空格,再连接起来返回.其中:连接起来的字段名称返回时为name1Selectclass.*,student.*fromclass,studentwhereclass.class=student.class:两个表的内容联合起来查询,字段也可以用JOIN子句实现:select*fromclassJOINstudentonclass.class=student.classJOIN又分为内连接,外连接,左外连接,右外连接等,具体请查看相关的数据库的手册.,3,插入,修改,删除语句,插入:指定字段名称及值的插入Insertintoclass(class,studentnumber,teacher)values(gaoer,55,abc);不指定字段名称的插入Insertintoclassvalues(chuyi,abc,55);一次插入多条记录:只能执行多条insert语句.从另一个表中读出数据插入当前的表先创建一个新表:select*intoclass_bakfromclasswhere1=2insertintoclass_bakselect*fromclasswhereclass=gaoer修改:Updateclasssetclass=gaoererwhereclass=gaoer删除:Deletefromclasswhereclass=gaoerer,4,创建新表,createtabledbo.class_bak(Namenvarchar(25)NOTNULLDEFAULTabcprimarykey,studentnumberintDEFAULT0);insertinto.class_bak(studentnumber)values(23):则插入的值col1的默认值就是abc.复制一个表的结构到一个新表.select*intoclass_bakfromclasswhere1=2:复制一个表为另一个表,如果不导数据,就将条件置为1=2即可.显示某表的建表语句:ORACLE:desctable_nameINFORMIX:dbschmeaSQLSERVER:,5,修改表结构,删除表,Altertableclass_bakaddmonitorvarchar(10);:增加一列Altertableclass_bakalterCOLUMNmontiornvarchar(10):修改一列Altertableclass_bakdropCOLUMNmonitor;:删除一列删除表:Droptableclass_bak,6,索引,用于在查询数据时,加快查询速度,createUNIQUEindexidx1_classonclass(class)在字段class上创建唯一索引,即插入时,该字段不允许重复.名称为idx_classinsertintoclass(class,teacher)values(gaoer,lisi);此插入语句执行第二次将会失败.删除索引:dropindexidx1_classonclass索引的重新生成,重新组织:当一个表经过多次修改,插入,删除后,需要重建索引,以便以后可提高查询效率.但重建的过程是较为消耗资源的.在SCP等业务繁忙的机器上,一定要选择合适的时间段才能重建索引.ALTERINDEXidx_classonclassREBUILD重新生成索引将会删除并重新创建索引。这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。ALTERINDEXidx_classonclassREORGANIZE使用最少系统资源重新组织索引。,7,索引使用技巧与数据库性能调优.,查询语句中,对于字符串型的字段,一定要用单引号括起来,对于整型不要用引号,对于日期型的字段,目前暂无定论,是否会对性能有影响.可进一步跟踪.尽量不要用in子句,那样的话无法使用索引,导致查询效率低下,可以改用UNION来连接多个查询.这样可以提高效率.多个查询条件,应该将过滤多的带索引的字段的条件放在前面.查询条件中尽量用等号,不要用大于,小于等,那样可能会用不上索引.在全部查询出所有结果时,要求带上条件1=1,可以避免使用索引,提高效率.,8,创建存储过程,setANSI_NULLSONsetQUOTED_IDENTIFIERONgoCREATEPROCEDUREdbo.get_avage_age-Addtheparametersforthestoredprocedurehereclass_namenchar(10),CheckDatedatetimeASBEGIN-SETNOCOUNTONaddedtopreventextraresultsetsfrom-interferingwithSELECTstatements.SETNOCOUNTON;selectavg(age)fromstudentwhereclass=class_nameandborn=0);约束是表结构的一部分内容,此例的约束是检查年龄字段不要为负值.Updatestudentsetage=-1;/将会报错.删除一个约束:altertablestudentdropconstraintage_negative;,15,主键,主键:表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键(PK)，用于强制表的实体完整性。在创建或修改表时，您可以通过定义PRIMARYKEY约束来创建主键。一个表只能有一个PRIMARYKEY约束，并且PRIMARYKEY约束中的列不能接受空值。由于PRIMARYKEY约束可保证数据的唯一性，因此经常对标识列定义这种约束。createtabledbo.test6(col1nvarchar(25)NOTNULLDEFAULTabc,col2intDEFAULT23primarykey);首先将原来的主键删除,再建联合主键altertabletest6ADDCONSTRAINTconstraint_1PRIMARYKEY(col1,col2);在两个字段上建联合主键.,16,外键:,外键(FK)是用于建立和加强两个表数据之间的链接的一列或多列。当创建或修改表时可通过定义FOREIGNKEY约束来创建外键。在外键引用中，当一个表的列被引用作为另一个表的主键值的列时，就在两表之间创建了链接。这个列就成为第二个表的外键。ALTERTABLEstudentADDCONSTRAINTFK_classFOREIGNKEY(class)REFERENCESclass(class);删除外键:altertablestudentdropCONSTRAINTFK_CLASS建立外键时,要注意几点:外键在外表中要是主键或索引.两个表的此字段名称最好一样.其他注意事项,项目参考相关数据库的联机帮助文档.例如:外键在外表中重新生成,或重新组织后,或外键的索引被删除后,引起的后果.需要仔细调试.,17,使用游标,setANSI_NULLSONsetQUOTED_IDENTIFIERONgoALTERPROCEDUREdbo.get_avage_age-Addtheparametersforthestoredprocedurehereclass_namenchar(10),CheckDatedatetimeASBEGIN-SETNOCOUNTONaddedtopreventextraresultsetsfrom-interferingwithSELECTstatements.SETNOCOUNTON;selectavg(age)fromstudentwhereclass=class_nameandtimeCheckDate-Insertstatementsforprocedurehere,18,使用游标(续),DECLAREcur_ageasintDECLAREavg_ageasnumeric(8)DECLAREcountasintDECLAREcurObjectCURSORFORselectagefromstudentwhereclass=class_nameandbornCheckDateorderbyagesetavg_age=0setcount=0OPENcurObjectFETCHNEXTFROMcurObjectINTOcur_ageWHILE(FETCH_STATUS=0)BEGINsetavg_age=avg_age+cur_agesetcount=count+1FETCHNEXTFROMcurObjectINTOcur_ageENDsetavg_age=avg_age/countSELECTAVAGEAGE=avg_age,StudentSum=countclosecurObjectdeallocatecurObjectEND在此存储过程中,使用游标来读取结果集中每个记录的字段值,再求平均值,看看与avg函数的功能是否一样的.,19,使用游标逐条更新表的记录,DECLAREcur_ageasintDECLAREavg_ageasnumeric(8)DECLAREcountasintDECLAREcurObjectCURSORFORselectagefromstudentwhereclass=class_nameandbornCheckDateorderbyageFORUPDATEOFage,englishsetavg_age=0setcount=0OPENcurObjectFETCHNEXTFROMcurObjectINTOcur_ageWHILE(FETCH_STATUS=0)BEGINsetavg_age=avg_age+cur_agesetcount=count+1updatestudentsetenglish=countwherecurrentofcurObjectFETCHNEXTFROMcurObjectINTOcur_ageENDsetavg_age=avg_age/countSELECTAVAGEAGE=avg_age,StudentSum=countclosecurObjectdeallocatecurObject根据游标更新表,还有很多选项,涉及到的细节请具体参考相关数据库的联机帮助!,20,常用函数:日期,DAY(date_expression)-函数返回date_expression中的日期值selectDAY(getdate()MONTH(date_expression)-函数返回date_expression中的月份值selectmonth(getdate()YEAR(date_expression)-函数返回date_expression中的年份值selectYEAR(getdate()DATEADD(,)selectDATEADD(yy,1,getdate()-函数返回指定日期date加上指定的额外日期间隔number产生的新日期GETDATE()-函数以DATETIME的缺省格式返回系统当前的日期和时间Datepart的含义及缩写:年份yy、yyyy季度qq、q月份mm、m每年的某一日dy、y日期dd、d星期wk、ww小时hh分钟mi、n秒ss、s毫秒ms,21,函数的创建,CREATEFUNCTIONCheckFnctn()RETURNSintASBEGINDECLAREretvalintSELECTretval=COUNT(*)FROMclassRETURNretvalEND;删除一个函数:dropfunctiondbo.CheckFnctn,22,函数的修改,setANSI_NULLSONsetQUOTED_IDENTIFIERONgoALTERFUNCTIONdbo.CheckFnctn()RETURNSintASBEGINDECLAREretvalintSELECTretval=COUNT(*)FROMclassRETURNretvalEND;,23,函数的使用,可以在增,删,改查,视图,触发器,存储过程,约束,等几乎所有地方使用函数.以下是在查询语句中使用函数.selectdbo.CheckFnctn();,24,事务,一个完整的事务就是其中的每个操作要么都完成,要么都不做.回到事务开始前的状态.beginTRANSACTION;ALTERINDEXidx_