《创建表和修改表.ppt》由会员分享，可在线阅读，更多相关《创建表和修改表.ppt（44页珍藏版）》请在三一办公上搜索。
1、SQL Server2005高级程序设计,主讲：黄俊南手机：,第四章 创建表和修改表,4.1 SQL Server中的对象名完全限定名称：服务器名.数据库名.模式名.对象名4.1.1 模式名（所有权）老式数据库没有模式，但模式似乎越来越重要Select s.name,s.sex,cj.subject,cj.successFrom student as s join success as cjOn s.s_id=cj.s_id针对我们已经处理的三表考虑上面sql语句哪些地方用到模式名？数据库名和服务器命名做为模式很流行。,默认模式：dbo,数据库所有者(dbo)dbo 是具有在数据库中执行所有活
2、动的暗示性权限的用户。将固定服务器角色 sysadmin 的任何成员都映射到每个数据库内称为 dbo 的一个特殊用户上。另外，由固定服务器角色 sysadmin 的任何成员创建的任何对象都自动属于 dbo。例如，如果用户 Andrew 是固定服务器角色 sysadmin 的成员，并创建表 T1，则表 T1 属于 dbo，并以 dbo.T1 而不是 Andrew.T1 进行限定。相反，如果 Andrew 不是固定服务器角色 sysadmin 的成员，而只是固定数据库角色 db_owner 的成员，并创建表 T1，则 T1 属于 Andrew，并限定为 Andrew.T1。该表属于 Andrew，
3、因为该成员没有将表限定为 dbo.T1。无法删除 dbo 用户，且此用户始终出现在每个数据库中。只有由 sysadmin 固定服务器角色成员（或 dbo 用户）创建的对象才属于 dbo。由任何其他也不是 syadmin 固定服务器角色成员的用户（包括 db_owner 固定数据库角色成员）创建的对象：属于创建该对象的用户，而不是 dbo。用创建该对象的用户名限定。,create、alter和drop语句,Create database Create table Alter database Alter table Drop database Drop table,2.使用DISTINCT消除重
4、复值例：从学生表中检索班级编码，要求消除值相同的那些行。USE XKGOSELECT DISTINCT CLASSNO FROM STUDENTGO,SELECT语句的各种使用方法,3.使用TOP nPERCENT仅返回前n行例：从学生表中检索所有的信息，要求只显示前6行数据。USE XKGO SELECT TOP 6*FROM STUDENTGO,SELECT语句的各种使用方法,4.修改检索结果中列的标题例1：检索课程表中课程编码、课程名称、教师。方法1：USE XKGO SELECT 课程编码=COUNO,课程名称=COUNAME,教师=TEACHERFROM COURSEGO方法2：US
5、E XKGOSELECT COUNO课程编码,COUNAME 课程名称,TEACHER 教师FROM COURSEGO方法3：USE XKGOSELECT COUNO AS课程编码,COUNAME AS课程名称,TEACHER AS 教师FROM COURSEGO,SELECT语句的各种使用方法,5.在检索结果中显示字符串在SELECT子句中，将要增加的字符串用单引号括起来，然后和列的名字写在一起，中间用逗号隔开。例：检索课程表的信息。USE XKGOSELECT COUNAME课程名称,课程编码为：，COUNO 课程编码FROM COURSEGO,SELECT语句的各种使用方法,6.在WHE
6、RE子句给出检索的条件在WHERE子句中，可以包含比较运算符（大于等）、逻辑运算符（与AND等）。例：在课程表中检索LINUX操作系统课程的主讲教师。USE XKGOSELECT TEACHER 主讲教师 FROM COURSEWHERE COUNAME=LINUX操作系统GO,SELECT语句的各种使用方法,7.列为表达式例1：检索XK数据库的COURSE表的课程信息，要求显示报名人数与限选人数之比。USE XKGOSELECT*,WILLNUM/LIMITNUM FROM COURSEGO例2：检索课程表中最小的报名人数、最大的报名人数以及平均报名人数。USE XKGOSELECT MIN
7、(WINLLNUM)最小的报名人数,MAX(WILLNUM)最大的报名人数,AVG(WILLNUM)平均报名人数FROM COURSEGO,SELECT语句的各种使用方法,8.使用ORDER BY子句重新排序检索结果例1：检索课程信息（包括报名人数与限选人数之比）。要求检索结果按报名人数与限选人数之比升序排序。USE XKGOSELECT*,WILLNUM/LIMITNUM 报名人数与限选人数之比 FROM COURSEORDER BY 报名人数与限选人数之比GO例2：检索课程表的教师名、课程名，要求检索结果按教师名称降序排序，教师名相同时，按课程名升序排序。USE XKGOSELECT TE
8、ACHER 教师名,COUNO 课程号 FROM COURSEORDER BY TEACHER DESC,COUNOGO,SELECT语句的各种使用方法,9.使用IN关键字例：检索课程号为004、007、013的课程名称。USE XKGOSELECT COUNAME 课程名称FROM COURSEWHERE COUNO=004 OR COUNO=007 OR COUNO=013/*COUNO IN(004,007,013)*/GO,SELECT语句的各种使用方法,10.使用LIKE关键字进行检索LIKE关键字是一个匹配运算符，它与字符串表达式相匹配，字符串表达式被定义为字符串和通配符。SQL的
9、四个通配符为：百分号，匹配包含0个或多个字符的字符串；_下划线，匹配任何单个的字符；排列通配符，匹配任何在范围或集合中的单个字符，例如m-p匹配的是m、n、o、p单个字符；不在范围之内的字符，匹配任何不在范围或集合之内的单个字符，例如mnop或m-p匹配的是除了m、n、o、p之外的任何字符。注意：通配符和字符串必须括在单引号内。,SELECT语句的各种使用方法,例1：检索第二个字为“宝”的学生姓名。USE XKGOSELECT*FROM STUDENTWHERE STUNAME LIKE _宝%GO例2：检索不姓刘的学生信息。USE XKGO SELECT*FROM STUDENTWHERE
10、STUNAME LIKE 刘%/*NOT LIKE 刘%*/GO,SELECT语句的各种使用方法,11.使用IS NULL关键字检索列中没有赋值的行。例：检索课程表中教师未定的课程名称和教师。USE XKGOSELECT COUNAME 课程名称,TEACHER 教师 FROM COURSEWHERE TEACHER IS NULLGO,SELECT语句的各种使用方法,12.检索某一范围内的信息。在WHERE子句限制检索条件，这个条件通常是一个逻辑表达式，在表达式中可以使用比较运算符（=等于、15 AND WILLNUM25GO,SELECT语句的各种使用方法,13.使用BETWEEN关键字例
11、：检索报名人数少于25并且多于15人的课程信息，要求检索结果按报名人数降序排序。USE XKGOSELECT COUNAME 课程名，WILLNUM 报名人数 FROM COURSEWHERE WILLNUM BETWEEN 15 AND 25ORDER BY 报名人数DESCGO,SELECT语句的各种使用方法,14.使用COMPUTE子句COMPUTE用来计算总计和分组小计，总计值或小计值作为附加新值出现在检索结果中。该子句用在WHERE子句之后。例：检索课程表中课程分类为“信息技术”的课程编码、课程名称、课程分类、报名人数，并计算平均报名人数。USE XKGOSELECT COUNO 课
12、程编码,COUNAME 课程名称,KIND 课程分类，WILLNUM报名人数 FROM COURSEWHERE KIND=“信息技术”COMPUTE AVG(WILLNUM)GO,SELECT语句的各种使用方法,15.使用COMPUTE BY子句使用COMPUTE BY子句对BY后面给出的列进行分组显示，并计算该列的分组小计。使用COMPUTE BY子句时必须使用ORDER BY 对COMPUTE BY中BY指定的列进行排序。例:按类显示课程表的课程编码、课程名称、课程分类、报名人数，并计算每类课程的平均报名人数。USE XKGOSELECT COUNO 课程编码,COUNAME 课程名称,K
13、IND 课程分类，WILLNUM报名人数 FROM COURSEORDER BY KINDCOMPUTE AVG(WILLNUM)BY KINDGO,SELECT语句的各种使用方法,16.使用GROUP BY子句当在SELECT子句中包含有聚合函数时，最适合使用GROUP BY子句。例：按课程分类统计平均报名人数。USE XKGOSELECT KIND 课程分类,AVG(WILLNUM)每类平均报名人数FROM COURSEGROUP BY KINDGO,SELECT语句的各种使用方法,17.HAVING子句HAVING子句用于限定组或聚合函数的检索条件。该子句通常用在GROUP BY子句之后
14、，在结果集分组之后再进行判断。例：检索“信息技术”类课程的平均报名人数。USE XKGOSELECT KIND 课程分类,AVG(WILLNUM)每类平均报名人数FROM COURSEGROUP BY KINDHAVING KIND=“信息技术”GO等价：SELECT KIND 课程分类,AVG(WILLNUM)每类平均报名人数FROM COURSEWHERE KIND=“信息技术”GROUP BY KIND例2：检索平均报名人数大于25人的课程分类和每类平均报名人数。USE XKGOSELECT KIND 课程分类,AVG(WILLNUM)每类平均报名人数FROM COURSEGROUP B
15、Y KINDHAVING AVG(WILLNUM)25GO错误方法：SELECT KIND 课程分类,AVG(WILLNUM)每类平均报名人数FROM COURSEWHERE AVG(WILLNUM)25GROUP BY KIND注意：1.可以在SELECT子句和HAVING子句中使用聚合函数，但是不能在WHERE子句中使用它们。2.WHERE子句对原始记录进行过滤，HAVING子句对结果进行过滤。,SELECT语句的各种使用方法,18.使用子查询子查询是在查询中包含另一个查询的查询，可以使用子查询代替表达式。子查询只返回一列数据，有时只能返回一行数据。例：检索报名人数大于平均报名人数的课程编
16、码、课程名称、报名人数。USE XKGOSELECT COUNO 课程编码,COUNAME 课程名称,WILLNUM 报名人数FROM COURSEWHERE WILLNUM(SELECT AVG(WILLNUM)FROM COURSE)GO,SELECT语句的各种使用方法,19.使用UNION运算符UNION运算符用于将两个或多个检索结果合并成一个结果，需遵循两个规则：1.所有查询中的列数和列的顺序必须相同；2.所有查询中按顺序对应列的数据类型必须兼容。例1.从班级表中检索班级名，从系部表中检索系部名称，使用UNION运算符合并这两个检索结果。USE XKGOSELECT CLASSNAME
17、 FROM CLASSUNIONSELECT DEPARTNAME FROM DEPARTMENTGO注意：1.UNION结果集的列标题取自第一个SELECT语句，如果要改变列标题，则在第一个SELECT中改变。2.如果希望重新排序多个检索结果的合并结果，则在最后的SELECT语句中使用ORDER BY子句。例2:从班级表中检索班级名，从系部表中检索系部名称，使用UNION运算符合并这两个检索结果，改变列标题并按降序排序。USE XKGOSELECT CLASSNAME 所有班名和所有系名FROM CLASSUNIONSELECT DEPARTNAME FROM DEPARTMENTORDER
18、 BY 所有班名和所有系名GO,SELECT语句的各种使用方法,20.对多个表或视图进行检索（1）笛卡儿积可对多个表或多个视图进行检索，检索时需要使用WHERE子句将表（或视图）与表（或视图）进行连接，否则就会出现笛卡儿积。例:CLASS表中有18行数据，STUDENT表中有180行数据，得到的结果集有3240行。SELECT语句如下：USE XKGOSELECT*FROM CLASS,STUDENTGO（2）连接条件为避免产生笛卡儿积，并且得到所需要的检索结果，必须使用WHERE子句给出连接条件。对N个表（或视图）的检索有N-1个连接条件。多个表检索中的表或视图可以在同一个数据库中，也可以来
19、自不同的数据库。在FROM子句中最多可使用16个表或视图。,SELECT语句的各种使用方法,例：检索“01电子商务”班的学生选修课程的情况，要求显示班级、学号、姓名、课程号、课程分类、学分、教师、上课时间、报名人数。USE XKGOSELECT CLASSNAME 班级,STUDENT.STUNO 学号，STUNAME 姓名，COUNAME 课程名，KIND 课程分类，CREDIT 学分，TEACHER 教师，SCHOOLTIME 上课时间，WILLNUM 报名人数FROM CLASS,STUDENT，COURSE,STUCOUWHERE CLASS.CLASSNO=STUDENT.CLASS
20、NO AND COURSE.COUNO=STUCOU.COUNO AND STUDENT.STUNO=STUCOU.STUNO AND CLASSNAME=01电子商务GO连接的类型分为内连接（值包含满足连接条件的数据行，主要有相等连接和自然连接）外连接（分为左外连接、右外连接和全连接）交叉连接,SELECT语句的各种使用方法,（3）相等连接相等连接是将要连接的列作相等比较后所作的连接，相等连接总会产生冗余，因为连接的列要显示两次。例:检索学生信息和学生相应的班级信息。USE XKGOSELECT*FROM CLASS,STUDENTWHERE CLASS.CLASSNO=STUDENT.CL
21、ASSNOGO等价SELECT*FROM CLASS INNER JOIN STUDENTON CLASS.CLASSNO=STUDENT.CLASSNO,SELECT语句的各种使用方法,(4)自然连接自然连接是将要连接的列作相等比较的连接，但是连接的列只显示一次，消除了相等连接产生的冗余。例：检索学生信息和学生对应的班级信息，要求连接的列只显示一列。USE XKGOSELECT CLASS.*,STUNO,STUNAME,PWD FROM CLASS,STUDENTWHERE CLASS.CLASSNO=STUDENT.CLASSNOGO等价SELECT CLASS.*,STUNO,STUN
22、AME,PWD FROM CLASS INNER JOIN STUDENTON CLASS.CLASSNO=STUDENT.CLASSNO,SELECT语句的各种使用方法,(5)比较连接例：检索各班可以选择的非本系开设的选修课程的信息。USE XKGOSELECT CLASSNAME 班级,COUNAME 课程名,KIND 课程分类，CREDIT 学分,TEACHER 教师,SCHOOLTIME 上课时间，WILLNUM 报名人数FROM CLASS,COURSEWHERE COURSE.DEPARTNOCLASS.DEPARTNOGO,SELECT语句的各种使用方法,(6)自连接自连接是一个
23、表和它自身进行连接。例：检索课程类相同但系部编码不同的课程信息（意味着同一种类的课有多个系部开设）。USE XKGOSELECT DISTINCT T1.COUNO 课程号,T1.COUNAME 课程名称,TI.KIND 课程分类，T1.DEPARTNO 系部编码FROM COURSE T1,COURSE T2WHERE T1.KIND=T2.KIND AND T1.DEPARTNOT2.DEPARTNOGO,SELECT语句的各种使用方法,(7)左外、右外、全外连接左外、右外、全外连接需在FROM子句中指明：FROM 左表名 LEFT/RIGHT/FULL JOIN 右表名 ON 连接条件例
24、：使用左外连接检索学生报名信息（课程号、课程名称、学号）。USE XKGOSELECT COURSE.COUNO,COUNAME,STUNO FROM COURSE LEFT JOIN STUCOUON COURSE.COUNO=STUCOU.COUNOGO,SELECT语句的各种使用方法,(8)交叉连接交叉连接就是将连接的两个表的所有行进行组合，形成一个结果集，该结果集的列数等于两个表的列数和，行数等于两个表的行数和。例：计算COURSE和CLASS的交叉连接。（即笛卡儿积）USE XKGOSELECT*FROM CALSS JOIN COURSEGO等价SELECT*FROM CALSS，
25、COURSE,SELECT语句的各种使用方法,21.使用EXISTS关键字EXISTS子句用于测试跟随的子查询中的行是否存在。例：检索已经报了选修课程的学生的学号和姓名。USE XKGOSELECT STUNO,STUNAMEFROM STUDENTWHERE EXISTS(SELECT STUNO FROM STUCOU)GO,SELECT语句的各种使用方法,2.2.3 向表中输入数据,1.使用INSERT语句INSERT语句用来向表中或视图中输入新的数据行。INSERT语句的语法：INSERT INTO TABLE_NAME(COLUMN_LIST)VALUES(DEFAULT|NULL|
26、EXPRESSION,N)|EXECUTE_STATEMENT,例:向DEPARTMENT表输入3行数据。USE XKGOINSERT DEPARTMENT VALUES(11,数学系)INSERT DEPARTMENT(DEPARTNAME,DEPARTNO)VALUES(物理系，12)INSERT DEPARTMENT(DEPARTNO,DEPARTNAME)VALUES(13,物理系)GOSELECT*FROM DEPARTMENTGO,2.使用企业管理器插入数据3.使用SELECT子句插入数据INSERT语句的特点是每一次只能输入一行数据。在INSERT语句中使用SELECT子句可以将
27、表中的数据行输入到表中，并且一次可以输入多行数据。语法格式为：INSERT TABLE_NAME SELECT COLUMN_LIST FROM TABLE_LIST WHERE SEARCH_CONDITIONS例：使用SELECT子句将NEWDEPARTMENT表中的数据插入到DEPARTMENT表中。USE XKGOINSERT DEPARTMENTSELECT NO,NAME FROM NEWDEPARTMENTGO,2.2.3 向表中输入数据,使用SELECT子句插入数据时，应注意：1.所使用的表必须是已存在的。2.INSERT中指定的表和SELECT子句得到的结果集一定要兼容，即列
28、的数量和顺序必须相同，列的数据类型和长度要相同，或者进行转换。例：使用SELECT子句将CLASS列和CLASSNAME列插入到DEPARTMENT表中。USE XKGOINSERT DEPARTMENTSELECT SUBSTRING(CLASSNO,7,2),CLASSNAMEFROM CLASSGO,2.2.3 向表中输入数据,4.使用SELECT INTO插入数据使用SELECT INTO语句允许用户定义一个新表，并且把SELECT的数据插入到新表中。语法形式：SELECT SELECT_LIST INTO NEW_TABLE_NAME FROM TABLE_LISTWHERE SEA
29、RCH_CONDITIONS,2.2.3 向表中输入数据,例：将COURSE表中报名人数超过20人的数据行插入到NEWCOURSE中。USE XKGOSELECT COUNO,COUNAME,TEACHER,SCHOOLTIMEINTO NEWCOURSEFROM COURSEWHERE WILLNUM20GO使用SELECT INTO插入数据时，应注意：1.新表不能存在；2.新表中的列和行是基于查询结果集的。,2.2.4 修改表中的数据,修改表中的数据有两种方法：使用UPDATE语句和使用企业管理器。UPDATE语句既可以一次修改一行数据，也可以一次修改多行数据，甚至可以一次修改表中的全部数
30、据行。UPDATE语句使用WHERE子句指定要修改的行，使用SET子句给出新的数据。语法：UPDATE TABLE_NAME SETCOLUMN_NAME=EXPRESSION|DEFAULT|NULL,NFROM TABLE_NAME,NWHERE 如果使用UPDATE语句修改数据时与数据完整性有冲突，修改就不会发生。,例1：将01数据库班学号为01000025的学生的名字修改为“陈晓梅”。USE XKGOUPDATE STUDENTSET STUNAME=“陈晓梅”FROM STUDENT,CLASSWHERE STUDENT.STUNO=01000025 ANDCLASSNAME=01数
31、据库 ANDCLASS.CLASSNO=STUDENT.CLASSNOGO,例2：将已选修了课程且学号为“00000005”的同学的密码改为“7F5E5D3D”。USE XKGOUPDATE STUDENTSET PWD=“7F5E5D3D”WHERE STUNO=“00000005”AND STUNO IN(SELECT STUNO FROM STUCOU)GO,2.2.5 使用DELETE从表中删除数据,DELETE语句用来从表中删除数据。可以一次从一个表删除一行或者多行数据。语法：DELETE FROM TABLE_NAME WHERE,例1：学号为“00000005”的同学因故取消课程号为017的选修课，在STUCOU表中删除此行。USE XKGODELETE STUCOUWHERE STUNO=0000005AND COUNO=017例2：学号为“00000005”的同学因故取消“中餐菜肴制作”选修课，在表中进行删除处理。USE XKGODELETE STUCOUFROM STUCOU,COURSEWHERE STUCOU.STUNO=00000005 AND COUNAME LIKE 中餐菜肴制作 ANDSTUCOU.COUNO=COURSE.COUNO,谢谢,第三章结束！,
