创建数据库表格

创建数据库表格createdatabasezhangweionprimary(name=scoredata3filename='d:\zhangwei\zhangwei.mdf',size=10mb,maxsize=50mbfilegrowth=5mb)logon(name=scorelog1filename='d:\zhangwei\zhangwei.ldf',size=3mb,maxsize=unlimitedfilegrowth=2)go更改文件属性ALTERDATABASEstudentscoreMODIFYFILE(NAME=sss_data,SIZE=20MB,MAXSIZE=60)GO更改逻辑文件名ALTERDATABASEstudentscore主键约束MODIFYFILE(NAME=sss_data,NEWNAME=scoredata1)删除数据库文件alterDATABASEStudentScoreremovefileScoreData4格式：［CONSTRAINT约束名］PRIMARYKEY［（列名）］。如：CONSTRAINTPk_课程表PRIMARYKEY（课程号）惟一键约束：格式：［CONSTRAINT约束名］UNIQUEKEY［（列名）］如：CONSTRAINTUk_专业表UNIQUE（专业名）外键（FOREIGNKEY）约束:格式：［CONSTRAINT约束名］FOREIGENKEY［（外键列）］REFERENCES引用表名（引用列）如:CONSTRAINTFk_CourseIdFOREIGNKEY（课程号）REFERENTCES成绩表（课程号），检查（CHECK）约束:CHECK约束通过限制可输入或修改的一列或多列的值来强制实现域完整性，它作用于插入（INSERT）和修改（UPDATE）语句。格式：［CONSTRAINT约束名］CHECK（检查表达式）如：CONSTRAINTCk_成绩表CHECK（成绩>=0）默认值（DEFAULT）约束：格式：［CONSTRAINT约束名］DEFAULT默认值如：CONSTRAINTDe_bScoreDEFAULT（0）创建规则创建规则的语法是：CREATERULErule_nameAScondition_expression例：createruleeeeas@kk>=60and@kk<=100规则捆绑格式：sp_bindrulerule_name,‘object_name’例：sp_bindruleeee,'成绩表.成绩’解除由sp_bindrule建立的规则与列或用户定义数据类型的绑定sp_unbindruleobject_name［,futureonly］例：sp_unbindrule'成绩表.成绩’删除规则例:dropruleeee用户自定义函数分为三种：（1）创建标量函数例：创建名为“Func_自己的姓名”（如“Func_张亮”）的标量函数，计算某个（如学号为：200009001）学生各科成绩之和，函数的参数变量名为@sn，类型为char（12），函数返回类型为float。函数最后要求能显示调用结果信息（如：该生的各科成绩总和为：189分）createfunctiondboFunc_张巍@snchar（12returnsfloatasbegindeclare@sum1floatselect@sum1=sum（成绩）from成绩表where学号二@snreturn@sum1endselectdbo.Func_张巍（200009001）例：在StudentScore数据库中创建一个自定义函数，按出生日期计算年龄，然后从“学生表”中检索出含有年龄的学生信息(包括学号、姓名、性别和年龄)。CREATEFUNCTIONdbo.年龄(@出生日期Datetime,@当前日期Datetime)RETURNSIntASBEGINRETURNYear(@当前日期)-Year(@出生日期)ENDgoSELECT学号，姓名，dbo.年龄(出生日期，getdate())AS年龄FROM学生表(2)内嵌(单语句)的返回表的自定义函数例：创建名为func_stu的函数，参数名为@stuno，参数类型为int。要求输入某个学号后，可以查看与该学号对应的学生信息，输出结果包括学号、姓名、性别和出生日期createfunctiondbofunc_stu(@stunoint)returnstableasreturn(select学号，姓名，性别，出生日期from学生表where学号=@stuno)select*fromfunc_stu(200206008)例：定义内嵌表值函数coursegrade,要求能够查询某一课程所有学生成绩列表。CREATEFUNCTIONcoursegrade(@coursevarchar(30))ReturnstableAsreturn(select学生表.学号,学生表.姓名,成绩表.成绩 From学生表,成绩表where学生表.学号二成绩表.学号and成绩表.课程号=@course)函数执行：select*fromcoursegrade('aa')(3)多语句的返回表的自定义函数例：定义多语句表值查询函数course_grade,要求能够查询某一(aa)课程所有学生成绩列表，并将其成绩加100。createfunctioncourse_grade9(@coursevarchar(30))returns@scoretable(s_nochar(10),s_namechar(30),gradesmallint)asbegininsert@scoreselect学生表.学号，学生表.姓名，成绩表.成绩from学生表，成绩表where学生表.学号二成绩表.学号and成绩表.课程号=@courseupdate@scoresetgrade=grade+100returnendgoselect*fromcourse_grade9（'aa'）创建索引格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameONtable_nameUNIQUE:指定创建的索引是唯一索引CLUSTERED|NONCLUSTERED:指定被创建索引的类型。使用CLUSTERED来创建聚簇索引（一个表只可创建一个聚簇索引）；使用NONCLUSTERED来创建非聚簇索引。这两个关键字中只能选用一个。.index_name：为新创建的索引指定的名字。.table_name：创建索引的表的名字。例：学生表中创建一个以“学号”索引。createclusteredindexIX—学号on学生表（学号）例：createclusteredindexIX—学号on学生表（学号）withdrop_existing例：创建复合索引createclusteredindexIX_学号on学生表（学号，姓名）删除索引的语法：例： 删除“学生表”上的“IX—学生表”索引dropindex学生表.IX_学号，学生表.ix—姓名创建存储过程格式：Createprocedureprocedue_name[©parameterdata_type][output][with]{recompile|encryption}assql_statement解释：output:表示此参数是可传回的with{recompile|encryption}recompile:表示每次执行此存储过程时都重新编译一次encryption:所创建的存储过程的内容会被加密例：在StudentScore数据库中创建一个存储过程Major_Class，要求从专业信息表和班级信息表的连接中返回所有专业的班级信息，其中包括班级代号、班级名称、专业名称和学制。USEStudentScoreGOCREATEPROCMajor_ClassASSELECTc.班级代号，c.班级名称,m.专业名称，学制FROM专业表mJOIN班级表cONm.专业代号=c.专业代号Major_Class的调用语句为：EXECMajor_Class例:在StudentScore数据库中创建一个存储过程Class_Num_Sum，要求根据专业代号输出该专业的学生人数。USEStudentScoreGOCREATEPROCEDUREClass_Num_Sum@MajorIdChar(2)='%',@SumIntOUTPUTASSELECT@Sum=SUM(班级人数)FROM班级表WHERE专业代号二@MajorId调用：DECLARE@nIntEXECClass_Num_Sum'11',@nOUTPUTPRINT该专业的学生人数为：'+convert(Char(4),@n)SELECT语句的语法格式SELECT语句的基本语法格式如下：SELECT[all|distinct]列名的列表[INTO新表名][FROM表名与视图名列表][WHERE条件表达式][GROUPBY列名的列表][HAVING条件表达式][ORDERBY列名1[ASC|DESC],列名2[ASC|DESC],...列名n[ASC|DESC]]注：select语句中出现的字段列要不以聚合函数形式出现，要不该字段在分组中体现。基本的SELECT语句使用位置：SELECT选取的列FROM表的列表WHERE查询条件使用INTO子句语法格式：SELECT选取的列INTO新表名FROM表的列表WHERE查询条件例：创建一个名为“新学生表”的表，表中的字段为姓名、籍贯、年龄。SELECT姓名，籍贯，YEAR(GETDATE())-YEAR(出生日期)AS年龄INTO新学生表FROM学生表例：查询性别为’男’并且是2002年6月份入学的学生。SELECT姓名，学号FROM学生表WHERE(性别='男’)AND(学号LIKE'200206%')ORDERBY姓名DESC例：查询籍贯不是“上海”和“扬州”的学生select*from学生表where籍贯notin('上海'，'扬州')/*在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数*/例：查询年龄在20-21之间的学生SELECT姓名，学号，籍贯，年龄FROM新学生表WHERE(年龄BETWEEN20AND21)或SELECT姓名，学号，籍贯，YEAR(GETDATE())-YEAR(出生日期)AS年龄FROM学生表WHERE（YEAR（GETDATE（））-YEAR（出生日期）BETWEEN20AND21）例：查找姓“刘”或“肖”的学生。select*from学生表where姓名like'[刘肖]%'例：查找班级代号为001-003的学生。如：select*from学生表where班级代号like'00[1-3]'例：查找姓名中含有“河”字的学生。SELECT姓名FROM学生表WHERE（姓名LIKE'%河%'）例：查询学生“性别”和“籍贯”的信息SELECTDISTINCT性别，籍贯FROM学生表例：求平均数SELECTAVG（成绩）AS平均成绩FROM成绩表例：统计班级中性别数SELECTDISTINCT班级代号，性别，COUNT（性别）AS性别个数FROM学生表GROUPBY班级代号，性别HAVING（NOT（班级代号ISNULL））ORDERBY班级代号例：从学生表中返回第3学期成绩总分超过200分的学生学号和成绩总分。SELECT学号，SUM（成绩）AS总分FROM成绩表WHERE（学期二3）GROUPBY学号HAVING（SUM（成绩）>200）例：查找学生成绩大于学分为4的最低成绩的学生的学号、姓名、课程名称、成绩和学分。SELECTdbo.学生表.学号，dbo.学生表.姓名，dbo.课程表.课程名称，dbo.成绩表.成绩，dbo.成绩表.学分FROMdbo.成绩表INNERJOINdbo.学生表ONdbo.成绩表.学号二dbo.学生表.学号INNERJOINdbo.课程表ONdbo.成绩表.课程号二dbo.课程表.课程号WHERE（dbo.成绩表.成绩>=ANY（SELECT成绩FROM成绩表WHERE成绩表.学分二4））例：钱河的成绩加100分。UPDATE成绩表SET成绩=成绩+100WHERE（学号IN（SELECT学号FROM学生表WHERE姓名二’钱河'））例：查询平均（每一门课）成绩大于80分学生的情况SELECT学生表.学号，学生表.姓名，学生表.性别intostudentFROM学生表INNERJOIN成绩表ON学生表.学号二成绩表.学号WHERE（成绩表.课程号IN（SELECT成绩表.课程号FROM成绩表INNERJOIN学生表ON成绩表.学号二学生表.学号GROUPBY成绩表.课程号HAVING（AVG（成绩表.成绩）>80）））视图例：在“计算机系学生”视图上，列出年龄小于22的学生。SELECT姓名，系部名称，年龄FROMdbo.计算机系学生WHERE（年龄<22）例：修改计算机系学生视图：要求去掉原视图中的系部名称，增加学生性别。alterviewdbo.计算机系学生（姓名，性别，年龄）asSELECTdbo.学生表.姓名，dbo.学生表.性别，YEAR（GETDATE（））-YEAR（dbo.学生表.出生日期）AS年龄FROMdbo.学生表INNERJOINdbo.班级表ONdbo.学生表.班级代号二dbo.班级表.班级代号INNERJOINdbo.专业表ONdbo.班级表.专业代号二dbo.专业表.专业代号WHERE（dbo.专业表.系部名称二’计算机'）例：以“学生表”和“成绩表”为数据源新建一个视图，命名为VIEW2，查询出参加补考的学生的姓名、学分和补考成绩。CREATEVIEWdbo.VIEW2ASSELECTdbo.学生表.学号，dbo.学生表.姓名，dbo.成绩表.学分，dbo.成绩表.补考成绩FROMdbo.成绩表INNERJOINdbo.学生表ONdbo.成绩表.学号=dbo.学生表.学号WHERE(NOT(dbo.成绩表.补考成绩ISNULL))例：通过修改视图VIEW2来更新表的数据。当学生补考成绩大于等于60分时，其对应的学分增加2分。UPDATEVIEW2SET学分二学分+2WHERE(补考成绩＞=60)删除视图：dropviewview_name局部变量例：声明局部变量Studno、Studname，并用SET语句为其赋值。DECLARE@StudnoChar(10)，@StudnameChar(8)SET@Studno='200009001'SET@Studname='葛文’例：从成绩表中查询学号为“200009001”学生的成绩总分，并将其赋给变量SumscoreoDECLARE@SumscoreFloatSELECT@sumscore=SUM(成绩)FROM成绩表WHERE学号='200009001'全局变量例：演示全局变量@@rowcount和@@error的使用。select*from学生表where性别二'女’select@@rowcount处理记录数select@@errorerror的值为：CAST功能：将某种数据类型的表达式显式转换为另一种数据类型。CAST和CONVERT提供相似的功能。例：将学生表中的姓名属性的类型转换为char(50)selectcast(姓名aschar(50)),学号from学生表或：selectconvert(char(50),姓名)，学号from学生表BEGIN...END功能： 当需要将一个以上的SQL语句作为一组对待时，可以使用BEGIN和END将它们括起来形成一个SQL语句块。例：如果课程aa的平均成绩低于60，那么显示“不及格”，如果高于80，显示“优秀”，其他为“合格”。declare@scorenumericif(selectavg(成绩)from成绩表where课程号='aa')<60beginselect@score=avg(成绩)from成绩表where课程号='aa'print'平均成绩为：'+convert(varchar(30),@score)+space(5)+'此科目不及格’endelseif(selectavg(成绩)from成绩表where课程号='aa')>=80beginselect@score=avg(成绩)from成绩表where课程号='aa'print'平均成绩为：'+convert(varchar(10),@score)+space(5)+'此科目优秀'endelsebeginselect@score=avg(成绩)from成绩表where课程号='aa'print'平均成绩为:'+convert(varchar(10),@score)+space(5)+'此科目合格’end用户自定义数据类型用户定义数据类型使用系统存储过程sp_addtype来定义(增加一个用户定义数据类型)。语法如下：sp_addtypetype_name,phystypetype_name是用户定义数据类型的名字例如：execsp_addtypetyp_ch_pid,〃char(4)〃查看用户定义数据类型例如：execsp_hel