SQLServer2008数据库-创建、建表、查询语句

1、SQLServer2008数据库创建、建表、查询语句一、创建数据库1、利用对象资源管理器创建用户数据库：选择“开始”“程序”MicrosoftSQLServer2008SQLServerManagementStudio命令，打开SQLServerManagementStudio。使用Windows身份验证连接到SQLServer2008数据库实例。(3)展开SQLServer实例，右击“数据库”，然后人弹出的快捷菜单中选择“新建数据库存”命令，打开“新建数据库”对话框。(4)在“新建数据库”对话框中，可以定义数据库的名称、数据库的所有者、是否使用全文索引、数据文件和日志文件的逻辑名称和路径、文

2、件组、初始大小和增长方式等。输入数据库名称student。2、利用T-SQL语句创建用户数据库：在SQLServerManagementStudio中，单击标准工具栏的“新建查询”按钮，启动SQL编辑器窗口，在光标处输入T-SQL语句，单击“执行”按钮。SQL编辑器就提交用户输入的T-SQL语句，然后发送到服务器执行，并返回执行结果。创建数据库student的T-SQL语句如下：CreatedatabasestudentOnprimary(name=student_data,filename=E:SQLServer2008SQLFULL_CHSMicrosoftSQLServerMSSQL10

3、.MSSQLSERVERMSSQLDATAstudent_data.mdf,size=3,maxsize=unlimited,filegrowth=1)Logon(name=student_log,filename=E:SQLServer2008SQLFULL_CHSMicrosoftSQLServerMSSQL10.MSSQLSERVERMSSQLDATAstudent_log.ldf,size=1,maxsize=20,filegrowth=10%)二、创建数据表1、利用表设计器创建数据表：启动SQLServerManagementStudio,连接到SQLServer2008数据库实例。

4、展开SQLServer实例，选择数据库”student“表”，单击鼠标右键，然后从弹出的快捷菜单中选择“新建表”命令，打开“表设计器”。(3)在“表设计器”中，可以定义各列的名称、数据类型、长度、是否允许为空等属性。(4)当完成新建表的各个列的属性设置后，单击工具栏上的“保存”按钮，弹出“选择名称”对话框，输入新建表名stu_info，SQLServer数据库引擎会依据用户的设置完成新表的创建。2、利用T-SQL语句创建数据表：Createtablestu_info(stu_idchar(10)notnull,namenvarchar(20)notnull,birthdaydatenull,s

5、exnchar(2)null,addressnvarchar(20)null,markintnull,majornvarchar(20)null,sdeptnvarchar(20)null);3、样本数据库student表数据：学生信息表（stu_info）:或uclnamebirthdaysexaddressmarkm司or112007070101K-张元1985-10-09男河南郑州560计算机科学与信2LILI7LI7LI1LI2张红1985-01-14河南开封565计算机科学与信2LILI7LI7LI1LI3王明1986-07-08男河南洛阳570计算机科学与信2i：iLi7Li7i：

6、ilLi4李伟1986-03-11男河南郑州564计算机科学与信2007070201郑澜1985-12-01河南平顶山567电子商务信2LILI7LI7LI2LI2赵恒1986-02-03男河南周口566电子商务信2LILI7LI7LI2LI3张兰1986-04-06河南许昌571电子商务信2007080101李伟1985-09-12男河南郑州578会计学会2LILI7LI8LI1LI2锚丽1985-11-23河南安阳573会计学会i2007080201孙楠1986-11-19男河南南阳577财务管理会课程信息表（courseinfo)：陋L也匚oursejd匚ourse_ridme匚ours

7、e_type匚ourse_rridrk匚ourse_timepre_course_idBai计算机基础考试350702操作丢统考试斗50701703计算机网络考试斗50701704数据库原理考查350701706Java考查3斗0704801宏观经济学考试斗50802初级会计考试斗50803财政学考试350804会计电算化考查3来.wzA/LS.L学生成绩表(stu_grade):三、完整性与约束数据库中的数据是现实世界的反映，数据库的设计必须能够满足现实情况的实现，即满足现实商业规则的要求，这也是数据完整性的要求。在数据库的管理系统中，约束是保证数据库中数据完整性的重要方法。1、完整性：数据

8、完整性是数据库设计方面一个非常重要的问题，数据完整性代表数据的正确性、一致性和可靠性。实施数据完整性的目的在于确保数据的质量。在SQLServer中，根据数据完整性措施所作用的数据库对象和范围不同，可以将数据完整性分类为实体完整性、域完整性和参照完整性。实体完整性把数据表中的每行看作一个实体，它要求所有的行都具有唯一的标识；域完整性要求数据表中指定列的数据具有正确的数据类型、格式和有效的数据范围；参照完整性维持被参照表和参照表之间的数据一致性。2、约束：约束是数据库中的数据完整性实现的具体方法。在SQLServe中，包括5种约束类型：primarykey约束、foreignkey约束、uniq

9、ue约束、check约束和default约束。四、数据查询1、查询语句：查询就是根据客户端的要求，数据库服务器搜寻出用户所需要的信息资料，并按用户规定的格式进行整理后返回给客户端。查询语句select在SQLServer中是使用频率最高的语句，可以说select语句是SQL语言的灵魂。select语句的语法结构：selectselect_listintonew_tableFromtable_sourcewheresearch_conditiongroupbygroup_by_expressionhavingsearch_conditionOrderbyorder_expressionasc|d

10、esc参数说明如下：Select子句：指定由查询结果返回的列。Into子句：将查询结果存储到新表或视图中。From子句：用于指定数据源，即使用的列所在的表或视图。如果对象不止一个，那么它们之间必用逗号分开。Where子句：指定用于限制返回的行的搜索条件。如果select语句没有where子句，dbms假设目标表中的所有行都满足搜索条件。Groupby子句：指定用来放置输出行的组，并且如果select子句select_list中包含聚合函数，贝U计算每组的汇总值。Having子句：指定组或聚合函数的搜索条件。Having通常与groupby子句一起使用。Orderby子句：指定结果集的排序方式。

11、ASC关键字表示升序排列结果，DESC关键字表示降序排列结果。如果没有指定任何一个关键字，那么ASC就是默认的关键字。如果没有orderby子句，DBMS将根据输入表中的数据的存放位置来显示数据。在这一系列的子句中，select子句和from子句是必需的，其他的子句根据需要都是可选的。2、简单查询：21、查询列：（1）查询指定列：数据表中有很多列，通常情况下并不需要查看全部的列，因为不同的用户所关注的内容不同。在指定列的查询中，列的显示顺序由select子句指定与数据在表中的存储顺序无关；同时，在查询多列时用“,”将各字段隔开。例7-1、查询所有同学学号、姓名和成绩信息。Selectstu_i

12、d,name,markfromstu_info查询结果如下：stu_idnamemark1|2007070101I张元56022007070102张红56532007070103王明57042007070104李伟56452007070201郑澜56762007070202赵恒56672007070203张兰57102007000101李伟57892007030102钱丽573102007000201孙楠577（2）查询所有列：使用“*”通配符，查询结果将列出表中所有列的值，而不必指明各列的列名，这在用户不清楚表中各列的列名时非常有用。服务器会按用户创建表格时声明列的顺序来显示所有的列。例7-

13、2、查询所有同学的所有信息。select*fromstu_info查询结果如下：stu_idnamebirthdaysexaddressmarkmajorsdept1|2007070101I张元1985-10-09男河南郑州560计算机科学与技术信息学院22007070102张红1935-01-14玄河南开封565计算机科学与技术信息学院32007070103王明1936-07-00男河南洛阳570计算机科学与技术信息学院42007070104李伟1986-03-11男河南郑州564计算机科学与技术信息学院52007070201郑澜1905-12-01河南平顶山567电子商务信息学院62007

14、070202赵恒1936-02-03男河南周口566电子商勢信息学院72007070203张兰1986-04-06玄河南许昌571电子商务信息学院02007080101李伟1935-09-12男河南郑州578会计学会计学院92007030102钱丽1935-11-23吉、河南安阳573会计学会计学院102007080201孙楠1986-11-19男河南南阳577财务管理会计学院使用运算列：YEAR为系统函数，获取指定日期的年份；GEDDATE()为系统函数，获取当前日期和时间。例7-3、查询所有同学的年龄信息。Selectstu_id,name,YEAR(getdate()-YEAR(birt

15、hday)fromstu_info查询结果如下：结哥雷消息stu_idname氏列名1|2007070101I张元2522007070102张红2532007070103王明2442007070104李伟2452007070201郑澜2562007070202赵恒2472007070203张兰24S2007080101李伟2592007080102贱丽25102007080201孙楠24改变列标题显示：通常在查询结果显示的列标题就是创建表时所使用的列名，但是，这在实际使用中往往会带来一些不便，因此，可以利用列标题列名或列标题来根据需要修改列标题的显示。例7-4、查询所有同学的年龄信息。Sele

16、ctnameas姓名,YEAR(getdate()-YEAR(birthday)as年龄fromstu_info查询结果如下：姓名年龄1i张元1252张红253王明244李伟245郑澜256赵恒247张兰240李伟259钱丽2510孙楠24（5）除去结果的重复信息：使用distinct关键字能够从返回的结果数据集合中删除重复的行，使返回的结果更简洁。例7-5、查询所有的院系信息。Selectdistinctsdeptfromstu_info查询结果如下：严结亘T面消息sdept番谆窿“洁点爭磽”（6）返回查询的部分数据：在SQLServer2008中，提供了top关键字让用户指定返回一定数量的

17、数据。Topn表示返回最前面的n行，n表示返回的行数;topnpercent表示返回前面的n%行。例7-6、查询前5位同学的学号、姓名和成绩信息。Selecttop5stu_id,name,markfromstu_info查询结果如下：stuidnamemark2007070101张元560TOC o 1-5 h z2007070102张红5652007070103王明5702007070104李伟5642007070201郑澜567例7-7、查询60%同学的学号、姓名和成绩信息。Selecttop60percentstu_id,name,markfromstu_info查询结果如下：為结果占

18、消息stu_idnamemark1|2007070101|张元56022007070102张红56532007070103王明57042007070104李伟56452007070201郑澜56762007070202赵恒56622、选择行：Where子句用于指定查询条件，使得select语句的结果表中只包含那些满足查询条件的记录。在使用时，where子句必须紧跟在from子句后面。Where子句中的条件表达式包括算术表达式和逻辑表达式两种，SQLServe对Where子句中的查询条件的数目没有限制。（1）使用比较表达式：例7-8、查询所有的男同学学号、姓名、生日和性别信息。Selectstu

19、_id,name,birthday,sexfromstu_infowheresex=男查询结果如下：匡结果區消息stu_idnamebirthdaysex1j2007070101j张元1985-10-09男22007070103王明1906-07-00男32007070104李伟1986-03-11男42007070202赵恒1906-02-03男52007030101李伟1985-09-12男62007080201孙楠1986-11-19男例7-9、查询所有的总分大于550分的同学学号、姓名、生日和性别信息。Selectstu_id,name,birthday,sexfromstu_info

20、wheremark550查询结果如下：stu_idnamebirthdaysex1|2007070101i张元1985-10-09男22007070102张红1985-01-1432007070103王明1986-07-08男42007070104李伟1986-03-11男52007070201郑澜1985-12-01玄62007070202赵恒1986-02-03男72007070203张兰1986-04-0602007000101李伟1985-09-12男92007000102钱丽1985-11-23玄102007030201孙楠1986-11-19男（2）使用逻辑比较表达式：例7-10、

21、查询所有总分大于550的男同学信息Selectstu_id,name,birthday,sexfromstu_infowheremark550andsex=男查询结果如下：日结果血消息stu_idnamebirthdaysex1|2007070101I张元1905-10-09男22007070103王明1936-07-00男32007070104李伟1986-03-11男42007070202赵恒1936-02-03男52007030101李伟1935-09-12男62007080201孙楠1986-11-19男例7-11、查询所有总分大于550分或男同学信息。Selectstu_id,nam

22、e,birthday,sexFromstu_infoWheremark550orsex=男查询结果如下：stu_idnam已birthdaysex1|2007070101I张元1935-10-09男22007070102张红1905-01-1432007070103王明1906-07-00男42007070104李伟1906-03-11男52007070201郑澜1905-12-0162007070202赵恒1906-02-03男72007070203张兰1936-04-06玄82007030101李伟1905-09-12男92007030102钱丽1905-11-23102007080201

23、孙楠1906-11-19男为了增强程序可读性，一般采用括号()来实现需要的执行顺序，而不考虑其默认的优先级顺序。例7-12、查询所有信息学院和会计学院并且总分大于550分的同学信息。select*fromstu_infowhere(sdept=信息学院orsdept=会计学院)andmark550查询结果如下：M结果的消息stu_idnamebirthdaysexaddr已定markmajorsdept1|2007070101|张元1905-10-09男河南郑州560计算机科学与技术信息学院22007070102张红1905-01-14河南开封565计算机科学与技术信息学院3200707010

24、3王明1906-07-00男河南洛阳570计算机科学与技术信息学院42007070104李伟1906-03-11男河南郑州564计算机科学与技术信息学院52007070201郑澜1935-12-01玄河南平顶山567电子商务信息学院62007070202赵恒1906-02-03男河南周口566电子商务信息学院72007070203张兰1906-04-06河南许昌571电子商务信息学院82007080101李伟1905-09-12男河南郑州578会计学会计学院92007000102钱丽1905-11-23河南安阳573会计学会计学院102007030201孙楠1906-11-19男河南南阳577

25、财务管理会计学院空值(null)的判断：如果在创建数据表时没有指定notnull约束，那么数据表中某些列的值就可以为null。所谓null就是空，在数据库中，其长度为0。例7-13、查询所有籍贯为空的同学信息。select*fromstu_infowhereaddressisnull查询结果如下：弹结禹辰-消息id|门已m已|bithd刖砂addressmarkmajor|sdept(4)限定数据范围：使用between限制查询数据范围时同时包括了边界值，效果完全可以用含有“=”和“”和“=80Groupbystu_id查询结果如下：旨结果也消息stu_id无列名氏列名庞列名氏列名1j2007

26、070101j9681074392200707010207S3052553200707010491S3091793）分组筛选：如果使用groupby子句分组，则还可用having子句对分组后的结果进行过滤筛选。Having子句通常与groupby子句一起使用，用于指定组或合计的搜索条件，其作用与where子句相似，二者的区别如下：作用对象不同：where子句作用于表和视图中的行，而having子句作用于形成的组。Where子句限制查找的行，having子句限制查找的组。执行顺序不同。若查询句中同时有where子句和having子句,执行时，先去掉不满足where条件的行，然后分组，分组后再去掉

27、不满足having条件的组。Where子句中不能直接使用聚合函数，但having子句的条件中可以包含聚合函数。例7-27、统计学生成绩表中每个同学的最高分、最低分、平均分和总分，并输出平均分大于87分的信息。Selectstu_id,MAX(grade),MIN(grade),AVG(grade),SUM(grade)Fromstu_gradeGroupbystu_idHavingAVG(grade)87查询结果如下：皓哥由消息stu_id氏列名比列名氏列名比列名1|20070701049188S3179明细汇总：使用groupby子句对查询数据进行分组汇总，为每一组产生一个汇总结果，每个组只

28、返回一行，无法看到详细信息。使用compute和computeby子句既能够看到统计经营部的结果又能够浏览详细数据。例7-28、使用compute子句对所有学生的人数进行明细汇总。select*fromstu_infocomputecount(stu_id)查询结果如下：&结哥S消息stujdnamebirthdaysexaddrmarkmajorsdept1j2007070101i张元1936-10-09男河南郑州576计算机科学与技术信息学院22007070102张红1935-01-14河南开封565计算机科学与技术信息学院32007070103王明1986-07-08男河南洛阳570计算

29、机科学与技术信息学院42007070104李伟1906-03-11男河南郑州564计算机科学与技术信息学院52009070105李澜1936-12-01吉.河南平顶山567电子商勞信息学院62009070106赵恒1986-02-03男河南周口566电子商务信息学院72009070107张兰1986-04-06玄河南许昌571电子商务信息学院02009070103李伟1935-09-12男河南郑州573会计学会计学院92009070109钱丽1935-11-23河南安阳576会计学会计学院102009070110孙楠1986-11-19男河南安阳573会计学会计学院在使用compute和com

30、puteby时，需要注意以下几点：八、Computeby子句不能与selectinto子句一起使用。Compute子句中的列必须在select子句的字段列表中出现。Computeby表示按指定的列进行明细汇总，使用by关键字时必须同时使用orderby子句，并且computeby中出现的列必须具有与orderby后出现的列相同的顺序，且不能跳过其中的列。例7-29、使用computeby子句按照院系对所有学生的人数进行明细汇总。select*fromstu_infoorderbysdeptcomputecount(stu_id)bysdept查询结果如下方结果消息stu_idn已门已birth

31、daysexaddrmarkmajorsdept1|2009070108|李伟1935-09-12男河南郑州578会计学会计学院22009070109贱丽1985-11-23河南安阳576会计学会计学院32009070110孙楠1936-11-19男河南安阳573会计学会计学院1ent|3stu_id门31已birthdaysexaddrmarkmajorsdept1j2007070101j张元1936-10-09男河南郑州576计算机科学与技术信息学院22007070102张红1935-01-14玄河南开封565计算机科学与技术信息学院32007070103王明1986-07-08男河南豁阳

32、570计算机科学与技术信息学院42007070104李伟1906-03-11男河南郑州564计算机科学与技术信息学院52009070105李澜1936-12-01玄河南平567电子商务信息学院62009070106赵恒1986-02-03男河南周口566电子商务信息学院72009070107张兰1936-04-06玄河南许昌571电子商务信息学院1ent|73、连接查询：前面介绍的查询都是针对单一的表，而在数据通库管理系统中，考虑到数据的冗余度低、数据一致性等问题，通常对数据表的设计要满足范式的要求，因此也会造成一个实体的所有信息保存在多个表中。当检索数据时，往往在一个表中不能够得到想要的信息

33、，通过连接操作，可以查询出存放在多个表中同一实体的不同信息，给用户带来很大的灵活性。多表连接实际上就是实现如何使用一个表中的数据来选择另一个表中的行。而连接条件则主要通过以下方法定义两个表在查询中的关联方式：指定每个表中要用于连接的列。典型的连接条件在一个表中的指定外键，在另一个表中指定与其关联的键指定比较各列的值时要使用的比较运算符（=、等）。表的连接的实现可以通过两种方法：利用select语句的where子句；在from子句中使用join(innerjoin,crossjoin,outerjoin,leftouterjoin,fullouterjoin等)关键字。例7-30、查询所有选修课

34、程编号701的同学学号、姓名和成绩。Selectstu_info.stu_id,name,markFromstu_info,stu_gradeWherestu_info.stu_id=stu_grade.stu_idandcourse_id=701查询结果如下：N结果占消息stu_idnamemark1j2007070101i张元57622007070102张红56532007070104李伟564例7-31、查询所有选修课程的同学选修课程的成绩。Selectstu_info.stu_id,name,course_name,gradeFromstu_info,stu_grade,course_

35、infoWherestu_info.stu_id=stu_grade.stu_idandcourse_info.course_id=stu_grade.course_id查询结果如下：二结果區消息stu_idnamecourse_ridrn已grade1|2007070101I张元计算机基础S322007070101张元操作系统8132007070101张元计算机网络9642007070102张红计算机基础0552007070102张红操作系统7462007070102张红计算机网络7772007070104李伟计算机基础9182007070104李伟操作系统8892007070101张元宏观经济学91102007070102张红宏观经济学S3112007070101