SQLServer2008数据库—创建建表查询语句

SQL Server 2008数据库创建、建表、查询语句一、创建数据库1、利用对象资源管理器创建用户数据库：( 1)选择“开始” “程序” Microsoft SQL Server 2008SQL Server Management Studio命令，打开 SQL Server Management Studio。(2) 使用“Windows身份验证”连接到SQL Server 2008 数据库实例。(3) 展开SQL Server实例，右击“数据库”，然后人 弹出的快捷菜单中选择“新建数据库存”命令，打开“新建数据库”对话框。( 4)在“新建数据库”对话框中，可以定义数据库的 名称、数据库的所有者、是否使用全文索引、数据文 件和日志文件的逻辑名称和路径、文件组、初始大小 和增长方式等。输入数据库名称 student。2、利用T-SQL语句创建用户数据库：在SQL Server Management Studic中，单击标准工具栏 的“新建查询按钮，启动 SQL编辑器窗口，在光标 处输入T-SQL吾句，单击“执行”按钮。SQL编辑器就 提交用户输入的T-SQL吾句，然后发送到服务器执行， 并返回执行结果。创建数据库student的T-SQL语句如下：Create data base studentOn primary(name=student_data ,filename =E:SQL Server2008SQLFULL_CHSMicrosoft SQLstudent_data.mdf ,size =3,maxsize = u nlimited,filegrowth =1)Log on( name=student_log ,filename =E:SQL Server2008SQLFULL_CHSMicrosoft SQLstudent_log.ldf,size =1,maxsize =20, filegrowth =10% )二、创建数据表1、利用表设计器创建数据表：( 1)启动 SQL Server Management Studi，o 连接到 SQL Server 2008数据库实例。(2)展开SQL Serve实例，选择“数据库” 一student “表”，单击鼠标右键， 然后从弹出的快捷菜单中选择 “新建表”命令，打开“表设计器” 。( 3)在“表设计器”中，可以定义各列的名称、数据 类型、长度、是否允许为空等属性。( 4)当完成新建表的各个列的属性设置后，单击工具栏上的“保存”按钮，弹出“选择名称”对话框，输 入新建表名stunfo, SQL Serve数据库引擎会依据用 户的设置完成新表的创建。2、利用T-SQL语句创建数据表：Create table stu_info (stu_id char (10)not null,name nvarchar ( 20)not null,birthday daten ull,sex nchar ( 2)nuII,address nvarchar( 20 )null,mark int n ull,major nvarchar ( 20 )null, sdept nvarchar ( 20 )null );3、样本数据库student表数据:学生信息表(stunfo)stuijdnameibirthdaysexaddressmarkmaiwsdept1995-ID-W男阿南郢州560计苴机科学与,”偿息翔2007070021905-01-14皱计算机科学与信且学傑20070TCM03王明1966-07-08男河南洛阳S70计矍机科学与信BL学隔2007070104李伟16-03-11男阿南钢HS64计算机科学与2O07n7tE0L1905-I2-D1河南平顶山5S7电子躍信且学俭20070702021%6-0-03河南問口S66电手35曙3007070203味兰16-04-06阿南许吕5712007080011905-09-12男河南郑抽STS盘计举釧十学碌2007080J021%5-11-23河南5?阳S73会计孚aoroffieoi押嵋男河 Kiwa57?会计学1院亦1他1ffULlNULLNULINULL课程信息表(course_info):coiuseidcoursenamecoursetypeCQursemtarkcourse _timepre_courseJd计算机基础考试350NULL702操作系统考试450701703计算机嗨考试4SO701704数据库原理考査3so701706Java考查340TO斗801宏观鏈济学考试斗soULL80Z初级会计考试430603财政学3SOfVULL604会计电算化考查3fl/ULfULLNULLtJLLMLMLNULLNULL学生成绩表(stu_grade)coursedgradeLoCi70701C1H701392007C70101702ai2007070101703962007C7U102701S52007070102702742007070102703112007070104701912007070104702冊2007C7U104801792007C8010LSOI7920070801018029120070801028013720070801028028320070802019Q375200708020180432/ozMJSLL三、完整性与约束数据库中的数据是现实世界的反映，数据库的设计必 须能够满足现实情况的实现，即满足现实商业规则的 要求，这也是数据完整性的要求。在数据库的管理系统中，约束是保证数据库中数据完 整性的重要方法。1、 完整性：数据完整性是数据库设计方面一个非常重要的问题， 数据完整性代表数据的正确性、一致性和可靠性。实 施数据完整性的目的在于确保数据的质量。在SQL Server中，根据数据完整性措施所作用的数据库对象和范围不同，可以将数据完整性分类为实体完 整性、域完整性和参照完整性。实体完整性把数据表中的每行看作一个实体，它要求 所有的行都具有唯一的标识；域完整性要求数据表中 指定列的数据具有正确的数据类型、格式和有效的数 据范围；参照完整性维持被参照表和参照表之间的数 据一致性。2、 约束：约束是数据库中的数据完整性实现的具体方法。在SQL Serve中，包括5种约束类型：primary key约束、 foreign key 约束、unique 约束、check 约束禾廿 default 约束。四、数据查询1、查询语句：查询就是根据客户端的要求，数据库服务器搜寻出用 户所需要的信息资料，并按用户规定的格式进行整理 后返回给客户端。查询语句select在SQLServer中是 使用频率最高的语句，可以说 select语句是SQL语言 的灵魂。select语句的语法结构： select select_listinto new_table From table_source where search_condition group by group_by_expression having search_conditionOrder by order_expressionasc|desc 参数说明如下：Select 子句 ： 指定由查询结果返回的列 。 Into 子句 ：将查询结果存储到新表或视图中 。From子句：用于指定数据源，即使用的列所在的表或 视图 。如果对象不止一个 ，那么它们之间必用逗号分 开。Where 子句 ： 指定用于限制返回的行的搜索条件 。如 果select 语句没有where子句，dbms假设目标表中 的所有行都满足搜索条件 。Group by 子句 ： 指定用来放置输出行的组 ， 并且如 果select 子句select_list中包含聚合函数，则计算每组的汇总值Having 子句 ：指定组或聚合函数的搜索条件 。Having 通常与 groupby 子句一起使用 。Order by 子句：指定结果集的排序方式。ASC关键 字表示升序排列结果，DESC关键字表示降序排列结 果。如果没有指定任何一个关键字，那么ASC就是默 认的关键字。如果没有orderby子句，DBMS各根据 输入表中的数据的存放位置来显示数据 。在这一系列的子句中， select 子句和 from 子句是必 需的，其他的子句根据需要都是可选的。2、简单查询：21、查询列：（1）查询指定列： 数据表中有很多列，通常情况下并不需要查看全部的 列，因为不同的用户所关注的内容不同。在指定列的查询中， 列的显示顺序由 select 子句指定， 与数据在表中的存储顺序无关； 同时，在查询多列时， 用“ ,”将各字段隔开。例 7-1、查询所有同学学号、姓名和成绩信息。Select stu_id , name, markfrom stu_info查询结果如下:3结果-J消息stu_idnamemark11 2007070101 i张元56022007070102张红5S532007070103王明57042007070104李伟56452007070201郑澜567E2007070202赵恒56672007070203张兰571E2007080101李伟57B32007080102钱丽5731020070B0201孙楠577(2) 查询所有列：使用“ * ”通配符，查询结果将列出表中所有列的值, 而不必指明各列的列名，这在用户不清楚表中各列的 列名时非常有用。服务器会按用户创建表格时声明列 的顺序来显示所有的列。例7-2、查询所有同学的所有信息。select * from stunfo查询结果如下：P结藁I自消息Inamebirthdaysexaddressmarkmajorcdept1t 2007070101 1张元1985-10-09男河南郑列560计算机科学与技术信息学院220070701021985 01-14河商开封565计算机科学与技术信息学院32007070103王明1988 07-08男河南洛阳570计算机科学与技术信息学院42007070104季伟198S0311男河南郑州564计算机科学与技术信息学院52007070201郑澜1985-12-01玄河南平顶山567电子商务信息学院E2007070202赵恒1986-02-03男河南周口5GE电子商务信息学院12007070203张兰1966 04-06河南许昌571电孑商务信息学院82007080101李伟1905 09-12男河南郑州578会计学会计学院S2007080102贱册198511 *23女河南安阳573会计学会计学院102007060201孙楠1966-11-19男河南南阳577财务管理会计学院(3) 使用运算列：YEAR为系统函数，获取指定日期的年份；GEDDATE()为系统函数，获取当前日期和时间。例7-3、查询所有同学的年龄信息。Selectstud , name, YEAR( getdate ()- YEAR( birthday )from stunfo查询结果如下：r结真1由消息stu_idname兀列名)1 2007070101 ：张元2522007070102张红2532007070103王明2442007070104李伟2452007070201郑澜2562007070202赵恒2472007070233张兰24820070E0101李伟25920070E0102锚丽25102007060201孙楠24(4) 改变列标题显示：通常在查询结果显示的列标题就是创建表时所使用的 列名，但是，这在实际使用中往往会带来一些不便， 因此，可以利用列标题列名 或as列标题来根 据需要修改列标题的显示。例74、查询所有同学的年龄信息。Select name as姓名,YEAR getdate ()- YEAR( birthday ) as年龄fromstunfo查询结果如下:巳站果 1山消息年龄1张元j 252“ 253王明244245郑澜25赵恒247张兰248李伟253犠丽2510孙楠24(5) 除去结果的重复信息：使用distinct关键字能够从返回的结果数据集合中删 除重复的行，使返回的结果更简洁。例7-5、查询所有的院系信息。sdeptSelect disti net from stunfo查询结果如下:卍第顚消息adept1堆计学院i2 信息学院(6) 返回查询的部分数据：在SQL Server 200中，提供了 top关键字让用户指定返回一定数量的数据。Top n表示返回最前面的n行，n表示返回的行数;top n percent表示返回前面的 n%行。例7-6、查询前5位同学的学号、姓名和成绩信息。Select top 5 stud , name, mark from stunfo查询结果如下：隅结果的消息丨$tu_idnamemark11 2007070101 j张元56022007070102张红56532007070103王明57042007070104李伟56452007070201郑澜567例7-7、查询60%同学的学号、姓名和成绩信息。Select top 60 percentstu_id , name, mark from stunfo查询结果如下：ps结哥由消息$tu_idnamemark1| 2007070101 张元56022007070102张红56532007070103王明57042007070104李伟5S452007070201郑澜5S7S2007070202划亘5SB2. 2、选择行：Where子句用于指定查询条件，使得select语句的结 果表中只包含那些满足查询条件的记录。在使用时，where子句必须紧跟在from子句后面。Where子句中的条件表达式包括算术表达式和逻辑 表达式两种，SQL Serve对 Where子句中的查询条件 的数目没有限制。（1）使用比较表达式：例7-8、查询所有的男同学学号、姓名、生日和性别 信息。Select stu_id , name, birthday , sexfrom stunfowhere sex =男查询结果如下：IS结果1 b消息1stu_idnamebirthdaysex1| 2007070101张元1985-10-09男22007070103王明19860708男32007070104李伟196603-11男4200707020219B6 02433男520070801 QI李伟1985 09-12男62007080201孙楠1986-11-19男例7-9、查询所有的总分大于 550分的同学学号、姓 名、生日和性别信息。Select stu_id , name, birthday , sex from stunfowhere mark 550查询结果如下:面结果%消息idnamebrthda1i 2007070101Ls. s .J张元1985-10-09男22007070102张红1935-01-14立32007070103王明1986-07-08男42007070104李伟1986*031男52007070201郑澜1985-12-012007070202赵恒1996-02-03男72007070203张兰1386-04-0682007080101李伟1985-09-12男92007080102钱丽1985-11-23女102007080201孙楠1996-11-19男(2)使用逻辑比较表达式:例7-10.查询所有总分大于550的男同学信息Select stu_id , name, birthday , sex from stunfo where mark 550 and sex =男查询结果如下：From stunfoWhere mark 550 or sex =男查询结果如下：宙錯果自消息stujdnamebirthdayse1j 2007070101 j张元1965-10-09男22007070102张红19B&D1-14女32007070103王明19B&07-06男42DO7O7O104李伟19B&09-11男52007070201郑澜1S8&12-01玄&2007070202超恒1966-02-03男72007070203张兰19E&DM6玄82007080101李伟19E5-09-12男920070B0102钱丽1985-11231020070B0201孙帏19BE-11-19男为了增强程序可读性，一般采用括号()来实现需要的执行顺序，而不考虑其默认的优先级顺序。例7-12、查询所有信息学院和会计学院并且总分大于 550分的同学信息。select *from stunfowhere ( sdept =信息学院or sdept =会计学院 )and mark 550查询结果如下：I ca结果匾消息stu_idnamebithdai5CXaddrewmarkmajorsdept1i 2007070101 j张元1995-10-09男河南郑州560计算机科学与技术信息学院2张红1995-01 -14女计算机科学号技术信息学院2007070102河南开封585n2007070103王明1986-07-08男河南潜阳571计算机科学弓技术信息学院42007070104李伟1986-03-11男河南郑州5G4计算机科学与技术信息学院52007070201郑澜19SM2T1立祠南平顶山567电子商务信息学院620070702021936-02-03男河南周口565电子商务信息学院72007070203张兰13304-06立河南许昌571电子商务信息学院a2007090101李伟138&4B-12男祠南郑州573会计学会计学睜920070301021985-11-23河南安阳573会计学会计学障W2007080201孙楠1386-11-19男河南商阳577!10务管理会计学瞬（3）空值（null ）的判断：如果在创建数据表时没有指定 not null约束，那么数 据表中某些列的值就可以为 null。所谓null就是空， 在数据库中，其长度为0。例7-13、查询所有籍贯为空的同学信息。select *from stunfowhere address is null查询结果如下：p结果n畐消息slu_d name birthday sen ddiess mark maior sdepl（4）限定数据范围：使用between限制查询数据范围时同时包括了边界值，效果完全可以用含有“ =”和“ ”和“=80Group by stu_id查询结果如下：$tu_id氏列名)(S?阁6E列名)氏列勾13 20070701011 96B1S743922007070102B7E385255320D7C7010439179(3)分组筛选:如果使用group by子句分组，则还可用having子句对 分组后的结果进行过滤筛选。Having子句通常与groupby子句一起使用，用于指定组或合计的搜索条件，其 作用与where子句相似，二者的区别如下： 作用对象不同：where子句作用于表和视图中的行， 而having子句作用于形成的组。Where子句限制查找 的行，having子句限制查找的组。执行顺序不同。若查询句中同时有where子句和 having子句,执行时，先去掉不满足 where条件的行， 然后分组，分组后再去掉不满足 having条件的组。Where子句中不能直接使用聚合函数，但 having子句 的条件中可以包含聚合函数。例7-27、统计学生成绩表中每个同学的最高分、最低 分、平均分和总分，并输出平均分大于 87分的信息。Selectstud , MAX( grade ), MIN( grade ), AVG( grade),SUM grade )From stu_gradeGroup by studHaving AVG( grade ) 87查询结果如下：ro结哥命消息tu_ld氏列名)兀列名)氏列名)氏列名)1 I 2007070104 jA 匸 r k etr-! r iW船的179(4)明细汇总：使用group by子句对查询数据进行分组汇总，为每一 组产生一个汇总结果，每个组只返回一行，无法看到 详细信息。使用compute和compute by子句既能够看 到统计经营部的结果又能够浏览详细数据。例7-28、使用compute子句对所有学生的人数进行明 细汇总。select *from stunfocompute count (stu_id )查询结果如下：sty_idrihsnnebirthday5CXaddfmarkmajoradept1| 2007070101 |张元1986-10 09男河南郑州576计算机科学与技术信息学院22007070102张红1995-01 14玄河南开封565计算机科学与技术信息学院32007070103王明1996-07-08男河南洛阳570计算机科学与技术信息学院420C7070104李伟1936-03-11男河南郑州564计算机科学与技术信息学院52009070105李澜199G-1241女河南平顶山567电子商务信息学院S200907010G赵恒1936-02-03男河南周口5E6电子商务信息学院72009070107张兰1936-04 W女河南许昌571电子商务信息学院32009070100李伟1935-0912男河南郑州578会计学会计学院32009070109钱丽193M123立河南安阳57b会计学会计学院102009070110孙楠198&11 ”19男祠南安阳573会计学会计学院在使用compute和compute by时，需要注意以下几占：八、Computeby子句不能与select into子句一起使用。Compute子句中的列必须在select子句的字段列表中 出现。Compute by表示按指定的列进行明细汇总，使用by关键字时必须同时使用 order by子句，并且compute by中出现的列必须具有与order by后出现的列相同的 顺序，且不能跳过其中的列。例7-29、使用compute by子句按照院系对所有学生的 人数进行明细汇总。select *from stunfoorder by sdeptcompute count (stu_id )by sdept查询结果如下:也结果Sh莎stu_idnamebirthdaysexaddrmarkmajorsdept12009070108李伟19E5-O9-12男河南郑州578会计学会计学降22009070109拔丽196511-23河南安阳576会计学会计学院32009070110孙蓿1966-11-19男河南安阳573会计学会计学瞬ent1LIIJISIMIBIHIL3 ih stu_idriamebirthdaysenaddrmarkmajorsdept12007070101张元1986-10-09男河南郑州576计豊机科学与技术信息学院22007070102张红19B5-01-14女河南弄封5G5计算机科学T技术信息子陆32007070103王明1986 07 03男河南洛阳570计宦机科学与技术信息学院42007070104李伟1966 0311男河南郑州5&4计算机科学与技术信息学院52009070105李澜19S6-1201女河南平567电子商务信息学院62009070106赵恒1986-02 03男河南周口566电子商勞信息学院12009070107张兰19E604 05河南许昌571电子商务信息学院ent1 13、连接查询：前面介绍的查询都是针对单一的表，而在数据通库管 理系统中，考虑到数据的冗余度低、数据一致性等问 题，通常对数据表的设计要满足范式的要求，因此也 会造成一个实体的所有信息保存在多个表中。当检索 数据时，往往在一个表中不能够得到想要的信息，通 过连接操作，可以查询出存放在多个表中同一实体的 不同信息，给用户带来很大的灵活性。多表连接实际上就是实现如何使用一个表中的数据来 选择另一个表中的行。而连接条件则主要通过以下方 法定义两个表在查询中的关联方式：指定每个表中要用于连接的列。典型的连接条件在一 个表中的指定外键，在另一个表中指定与其关联的键。 指定比较各列的值时要使用的比较运算符 (=、 等)表的连接的实现可以通过两种方法：利用 select语句 的 where 子句；在 from 子句中使用 join (i nn er jo in ,cross join ,outer joi n,left outer jo in ,full outer jo in 等)关键字。例7-30、查询所有选修课程编号701的同学学号、姓 名和成绩。Select stunfo . stud , name, markFrom stunfo,stu_gradeWhere stunfo.stud=stu_grade . stu_idand course id=701查询结果如下:田结果A消息$tu_idnamemark1$ 2007070101 !张元57E22007070102张红56532007C70104李伟564例7-31、查询所有选修课程的同学选修课程的成绩Selectstunfo. stud , name, course_name , gradeFrom stunfo,stu_grade , course_infoWhere stunfo.stud=stu_grade . studandcoursenfoid.course_id=stu_grade.course查询结果如下:|扇结果I 消息stu_idnamecouisenamegrade1| 20070701011张元计算机基础E922007070101张元操作系毓E132C07070101张元计篡机网络9E42007070102张红计篡机基础G552007070102张红操作系斷74E2C07070102张红计算机网络7772007070104李伟计篡机基础9182007070101李伟操作系统G892007070101张元宏观经挤学91102007070102张红宏观经济学E3112007070101张元初级会计75122007070101张元财政学E2132007070101张无会计电算化G2在SQL中，也可以通过 AS关键字为表定义有时表名比较烦琐，使用起来很麻烦，为了程序的简 洁明了， 别名。例 7-32、Select查询所有同学所有课程的成绩。A. stu idname, course_name , markFrom stunfoas A, stu_gradeB, course_infoas CWhereA. stu_id =B. stu_idB. course_id=C. course_id查询结果如下：asand| C3结果|匕消息stu_idnamecouise_rieinnemark12007070101 张元计算机基础57622007070101张元操作系统57632007070101张无计算机网第57642007070102张红计豐机基砒55552007070102张红操作系统55562007070102张红计算机网第55572007070104李伟计直机基础554a2007070104李伟操作系蜒56492007070101张元宜观经挤学576102007070102张红密观经济学5S5112007070101张无初级樂计576122007070101张元财政学576132007070101张无会计电算化576在select语句的from子句中，通过指定不同类型的 joi