《创建表和修改表》PPT课件.ppt

SQLServer2005高级程序设计 主讲 黄俊南手机 13959906116E mail baronhjn 第四章创建表和修改表 4 1SQLServer中的对象名完全限定名称 服务器名 数据库名 模式名 对象名4 1 1模式名 所有权 老式数据库没有模式 但模式似乎越来越重要Selects name s sex cj subject cj successFromstudentassjoinsuccessascjOns s id cj s id针对我们已经处理的三表考虑上面sql语句哪些地方用到模式名 数据库名和服务器命名做为模式很流行 默认模式 dbo 数据库所有者 dbo dbo是具有在数据库中执行所有活动的暗示性权限的用户 将固定服务器角色sysadmin的任何成员都映射到每个数据库内称为dbo的一个特殊用户上 另外 由固定服务器角色sysadmin的任何成员创建的任何对象都自动属于dbo 例如 如果用户Andrew是固定服务器角色sysadmin的成员 并创建表T1 则表T1属于dbo 并以dbo T1而不是Andrew T1进行限定 相反 如果Andrew不是固定服务器角色sysadmin的成员 而只是固定数据库角色db owner的成员 并创建表T1 则T1属于Andrew 并限定为Andrew T1 该表属于Andrew 因为该成员没有将表限定为dbo T1 无法删除dbo用户 且此用户始终出现在每个数据库中 只有由sysadmin固定服务器角色成员 或dbo用户 创建的对象才属于dbo 由任何其他也不是syadmin固定服务器角色成员的用户 包括db owner固定数据库角色成员 创建的对象 属于创建该对象的用户 而不是dbo 用创建该对象的用户名限定 create alter和drop语句 CreatedatabaseCreatetableAlterdatabaseAltertableDropdatabaseDroptable 2 使用DISTINCT消除重复值例 从学生表中检索班级编码 要求消除值相同的那些行 USEXKGOSELECTDISTINCTCLASSNOFROMSTUDENTGO SELECT语句的各种使用方法 3 使用TOPn PERCENT 仅返回前n行例 从学生表中检索所有的信息 要求只显示前6行数据 USEXKGOSELECTTOP6 FROMSTUDENTGO SELECT语句的各种使用方法 4 修改检索结果中列的标题例1 检索课程表中课程编码 课程名称 教师 方法1 USEXKGOSELECT 课程编码 COUNO 课程名称 COUNAME 教师 TEACHERFROMCOURSEGO方法2 USEXKGOSELECTCOUNO 课程编码 COUNAME 课程名称 TEACHER 教师 FROMCOURSEGO方法3 USEXKGOSELECTCOUNOAS 课程编码 COUNAMEAS 课程名称 TEACHERAS 教师 FROMCOURSEGO SELECT语句的各种使用方法 5 在检索结果中显示字符串在SELECT子句中 将要增加的字符串用单引号括起来 然后和列的名字写在一起 中间用逗号隔开 例 检索课程表的信息 USEXKGOSELECTCOUNAME 课程名称 课程编码为 COUNO 课程编码 FROMCOURSEGO SELECT语句的各种使用方法 6 在WHERE子句给出检索的条件在WHERE子句中 可以包含比较运算符 大于 等 逻辑运算符 与AND等 例 在课程表中检索 LINUX操作系统 课程的主讲教师 USEXKGOSELECTTEACHER 主讲教师 FROMCOURSEWHERECOUNAME LINUX操作系统 GO SELECT语句的各种使用方法 7 列为表达式例1 检索XK数据库的COURSE表的课程信息 要求显示报名人数与限选人数之比 USEXKGOSELECT WILLNUM LIMITNUMFROMCOURSEGO例2 检索课程表中最小的报名人数 最大的报名人数以及平均报名人数 USEXKGOSELECTMIN WINLLNUM 最小的报名人数 MAX WILLNUM 最大的报名人数 AVG WILLNUM 平均报名人数 FROMCOURSEGO SELECT语句的各种使用方法 8 使用ORDERBY子句重新排序检索结果例1 检索课程信息 包括报名人数与限选人数之比 要求检索结果按报名人数与限选人数之比升序排序 USEXKGOSELECT WILLNUM LIMITNUM 报名人数与限选人数之比 FROMCOURSEORDERBY报名人数与限选人数之比GO例2 检索课程表的教师名 课程名 要求检索结果按教师名称降序排序 教师名相同时 按课程名升序排序 USEXKGOSELECTTEACHER 教师名 COUNO 课程号 FROMCOURSEORDERBYTEACHERDESC COUNOGO SELECT语句的各种使用方法 9 使用IN关键字例 检索课程号为004 007 013的课程名称 USEXKGOSELECTCOUNAME 课程名称 FROMCOURSEWHERECOUNO 004 ORCOUNO 007 ORCOUNO 013 COUNOIN 004 007 013 GO SELECT语句的各种使用方法 10 使用LIKE关键字进行检索LIKE关键字是一个匹配运算符 它与字符串表达式相匹配 字符串表达式被定义为字符串和通配符 SQL的四个通配符为 百分号 匹配包含0个或多个字符的字符串 下划线 匹配任何单个的字符 排列通配符 匹配任何在范围或集合中的单个字符 例如 m p 匹配的是m n o p单个字符 不在范围之内的字符 匹配任何不在范围或集合之内的单个字符 例如 mnop 或 m p 匹配的是除了m n o p之外的任何字符 注意 通配符和字符串必须括在单引号内 SELECT语句的各种使用方法 例1 检索第二个字为 宝 的学生姓名 USEXKGOSELECT FROMSTUDENTWHERESTUNAMELIKE 宝 GO例2 检索不姓 刘 的学生信息 USEXKGOSELECT FROMSTUDENTWHERESTUNAMELIKE 刘 NOTLIKE 刘 GO SELECT语句的各种使用方法 11 使用ISNULL关键字检索列中没有赋值的行 例 检索课程表中教师未定的课程名称和教师 USEXKGOSELECTCOUNAME 课程名称 TEACHER 教师 FROMCOURSEWHERETEACHERISNULLGO SELECT语句的各种使用方法 12 检索某一范围内的信息 在WHERE子句限制检索条件 这个条件通常是一个逻辑表达式 在表达式中可以使用比较运算符 等于 15ANDWILLNUM 25GO SELECT语句的各种使用方法 13 使用BETWEEN关键字例 检索报名人数少于25并且多于15人的课程信息 要求检索结果按报名人数降序排序 USEXKGOSELECTCOUNAME 课程名 WILLNUM 报名人数 FROMCOURSEWHEREWILLNUMBETWEEN15AND25ORDERBY报名人数DESCGO SELECT语句的各种使用方法 14 使用COMPUTE子句COMPUTE用来计算总计和分组小计 总计值或小计值作为附加新值出现在检索结果中 该子句用在WHERE子句之后 例 检索课程表中课程分类为 信息技术 的课程编码 课程名称 课程分类 报名人数 并计算平均报名人数 USEXKGOSELECTCOUNO 课程编码 COUNAME 课程名称 KIND 课程分类 WILLNUM 报名人数 FROMCOURSEWHEREKIND 信息技术 COMPUTEAVG WILLNUM GO SELECT语句的各种使用方法 15 使用COMPUTEBY子句使用COMPUTEBY子句对BY后面给出的列进行分组显示 并计算该列的分组小计 使用COMPUTEBY子句时必须使用ORDERBY对COMPUTEBY中BY指定的列进行排序 例 按类显示课程表的课程编码 课程名称 课程分类 报名人数 并计算每类课程的平均报名人数 USEXKGOSELECTCOUNO 课程编码 COUNAME 课程名称 KIND 课程分类 WILLNUM 报名人数 FROMCOURSEORDERBYKINDCOMPUTEAVG WILLNUM BYKINDGO SELECT语句的各种使用方法 16 使用GROUPBY子句当在SELECT子句中包含有聚合函数时 最适合使用GROUPBY子句 例 按课程分类统计平均报名人数 USEXKGOSELECTKIND 课程分类 AVG WILLNUM 每类平均报名人数 FROMCOURSEGROUPBYKINDGO SELECT语句的各种使用方法 17 HAVING子句HAVING子句用于限定组或聚合函数的检索条件 该子句通常用在GROUPBY子句之后 在结果集分组之后再进行判断 例 检索 信息技术 类课程的平均报名人数 USEXKGOSELECTKIND 课程分类 AVG WILLNUM 每类平均报名人数 FROMCOURSEGROUPBYKINDHAVINGKIND 信息技术 GO等价 SELECTKIND 课程分类 AVG WILLNUM 每类平均报名人数 FROMCOURSEWHEREKIND 信息技术 GROUPBYKIND例2 检索平均报名人数大于25人的课程分类和每类平均报名人数 USEXKGOSELECTKIND 课程分类 AVG WILLNUM 每类平均报名人数 FROMCOURSEGROUPBYKINDHAVINGAVG WILLNUM 25GO错误方法 SELECTKIND 课程分类 AVG WILLNUM 每类平均报名人数 FROMCOURSEWHEREAVG WILLNUM 25GROUPBYKIND注意 1 可以在SELECT子句和HAVING子句中使用聚合函数 但是不能在WHERE子句中使用它们 2 WHERE子句对原始记录进行过滤 HAVING子句对结果进行过滤 SELECT语句的各种使用方法 18 使用子查询子查询是在查询中包含另一个查询的查询 可以使用子查询代替表达式 子查询只返回一列数据 有时只能返回一行数据 例 检索报名人数大于平均报名人数的课程编码 课程名称 报名人数 USEXKGOSELECTCOUNO 课程编码 COUNAME 课程名称 WILLNUM 报名人数 FROMCOURSEWHEREWILLNUM SELECTAVG WILLNUM FROMCOURSE GO SELECT语句的各种使用方法 19 使用UNION运算符UNION运算符用于将两个或多个检索结果合并成一个结果 需遵循两个规则 1 所有查询中的列数和列的顺序必须相同 2 所有查询中按顺序对应列的数据类型必须兼容 例1 从班级表中检索班级名 从系部表中检索系部名称 使用UNION运算符合并这两个检索结果 USEXKGOSELECTCLASSNAMEFROMCLASSUNIONSELECTDEPARTNAMEFROMDEPARTMENTGO注意 1 UNION结果集的列标题取自第一个SELECT语句 如果要改变列标题 则在第一个SELECT中改变 2 如果希望重新排序多个检索结果的合并结果 则在最后的SELECT语句中使用ORDERBY子句 例2 从班级表中检索班级名 从系部表中检索系部名称 使用UNION运算符合并这两个检索结果 改变列标题并按降序排序 USEXKGOSELECTCLASSNAME 所有班名和所有系名 FROMCLASSUNIONSELECTDEPARTNAMEFROMDEPARTMENTORDERBY所有班名和所有系名GO SELECT语句的各种使用方法 20 对多个表或视图进行检索 1 笛卡儿积可对多个表或多个视图进行检索 检索时需要使用WHERE子句将表 或视图 与表 或视图 进行连接 否则就会出现笛卡儿积 例 CLASS表中有18行数据 STUDENT表中有180行数据 得到的结果集有3240行 SELECT语句如下 USEXKGOSELECT FROMCLASS STUDENTGO 2 连接条件为避免产生笛卡儿积 并且得到所需要的检索结果 必须使用WHERE子句给出连接条件 对N个表 或视图 的检索有N 1个连接条件 多个表检索中的表或视图可以在同一个数据库中 也可以来自不同的数据库 在FROM子句中最多可使用16个表或视图 SELECT语句的各种使用方法 例 检索 01电子商务 班的学生选修课程的情况 要求显示班级 学号 姓名 课程号 课程分类 学分 教师 上课时间 报名人数 USEXKGOSELECTCLASSNAME 班级 STUDENT STUNO 学号 STUNAME 姓名 COUNAME 课程名 KIND 课程分类 CREDIT 学分 TEACHER 教师 SCHOOLTIME 上课时间 WILLNUM 报名人数 FROMCLASS STUDENT COURSE STUCOUWHERECLASS CLASSNO STUDENT CLASSNOANDCOURSE COUNO STUCOU COUNOANDSTUDENT STUNO STUCOU STUNOANDCLASSNAME 01电子商务 GO连接的类型分为内连接 值包含满足连接条件的数据行 主要有相等连接和自然连接 外连接 分为左外连接 右外连接和全连接 交叉连接 SELECT语句的各种使用方法 3 相等连接相等连接是将要连接的列作相等比较后所作的连接 相等连接总会产生冗余 因为连接的列要显示两次 例 检索学生信息和学生相应的班级信息 USEXKGOSELECT FROMCLASS STUDENTWHERECLASS CLASSNO STUDENT CLASSNOGO等价SELECT FROMCLASSINNERJOINSTUDENTONCLASS CLASSNO STUDENT CLASSNO SELECT语句的各种使用方法 4 自然连接自然连接是将要连接的列作相等比较的连接 但是连接的列只显示一次 消除了相等连接产生的冗余 例 检索学生信息和学生对应的班级信息 要求连接的列只显示一列 USEXKGOSELECTCLASS STUNO STUNAME PWDFROMCLASS STUDENTWHERECLASS CLASSNO STUDENT CLASSNOGO等价SELECTCLASS STUNO STUNAME PWDFROMCLASSINNERJOINSTUDENTONCLASS CLASSNO STUDENT CLASSNO SELECT语句的各种使用方法 5 比较连接例 检索各班可以选择的非本系开设的选修课程的信息 USEXKGOSELECTCLASSNAME 班级 COUNAME 课程名 KIND 课程分类 CREDIT 学分 TEACHER 教师 SCHOOLTIME 上课时间 WILLNUM 报名人数 FROMCLASS COURSEWHERECOURSE DEPARTNOCLASS DEPARTNOGO SELECT语句的各种使用方法 6 自连接自连接是一个表和它自身进行连接 例 检索课程类相同但系部编码不同的课程信息 意味着同一种类的课有多个系部开设 USEXKGOSELECTDISTINCTT1 COUNO 课程号 T1 COUNAME 课程名称 TI KIND 课程分类 T1 DEPARTNO 系部编码 FROMCOURSET1 COURSET2WHERET1 KIND T2 KINDANDT1 DEPARTNOT2 DEPARTNOGO SELECT语句的各种使用方法 7 左外 右外 全外连接左外 右外 全外连接需在FROM子句中指明 FROM左表名LEFT RIGHT FULLJOIN右表名ON连接条件例 使用左外连接检索学生报名信息 课程号 课程名称 学号 USEXKGOSELECTCOURSE COUNO COUNAME STUNOFROMCOURSELEFTJOINSTUCOUONCOURSE COUNO STUCOU COUNOGO SELECT语句的各种使用方法 8 交叉连接交叉连接就是将连接的两个表的所有行进行组合 形成一个结果集 该结果集的列数等于两个表的列数和 行数等于两个表的行数和 例 计算COURSE和CLASS的交叉连接 即笛卡儿积 USEXKGOSELECT FROMCALSSJOINCOURSEGO等价SELECT FROMCALSS COURSE SELECT语句的各种使用方法 21 使用EXISTS关键字EXISTS子句用于测试跟随的子查询中的行是否存在 例 检索已经报了选修课程的学生的学号和姓名 USEXKGOSELECTSTUNO STUNAMEFROMSTUDENTWHEREEXISTS SELECTSTUNOFROMSTUCOU GO SELECT语句的各种使用方法 2 2 3向表中输入数据 1 使用INSERT语句INSERT语句用来向表中或视图中输入新的数据行 INSERT语句的语法 INSERT INTO TABLE NAME COLUMN LIST VALUES DEFAULT NULL EXPRESSION N EXECUTE STATEMENT 例 向DEPARTMENT表输入3行数据 USEXKGOINSERTDEPARTMENTVALUES 11 数学系 INSERTDEPARTMENT DEPARTNAME DEPARTNO VALUES 物理系 12 INSERTDEPARTMENT DEPARTNO DEPARTNAME VALUES 13 物理系 GOSELECT FROMDEPARTMENTGO 2 使用企业管理器插入数据3 使用SELECT子句插入数据INSERT语句的特点是每一次只能输入一行数据 在INSERT语句中使用SELECT子句可以将表中的数据行输入到表中 并且一次可以输入多行数据 语法格式为 INSERTTABLE NAMESELECTCOLUMN LISTFROMTABLE LISTWHERESEARCH CONDITIONS例 使用SELECT子句将NEWDEPARTMENT表中的数据插入到DEPARTMENT表中 USEXKGOINSERTDEPARTMENTSELECTNO NAMEFROMNEWDEPARTMENTGO 2 2 3向表中输入数据 使用SELECT子句插入数据时 应注意 1 所使用的表必须是已存在的 2 INSERT中指定的表和SELECT子句得到的结果集一定要兼容 即列的数量和顺序必须相同 列的数据类型和长度要相同 或者进行转换 例 使用SELECT子句将CLASS列和CLASSNAME列插入到DEPARTMENT表中 USEXKGOINSERTDEPARTMENTSELECTSUBSTRING CLASSNO 7 2 CLASSNAMEFROMCLASSGO 2 2 3向表中输入数据 4 使用SELECTINTO插入数据使用SELECTINTO语句允许用户定义一个新表 并且把SELECT的数据插入到新表中 语法形式 SELECTSELECT LISTINTONEW TABLE NAMEFROMTABLE LISTWHERESEARCH CONDITIONS 2 2 3向表中输入数据 例 将COURSE表中报名人数超过20人的数据行插入到NEWCOURSE中 USEXKGOSELECTCOUNO COUNAME TEACHER SCHOOLTIMEINTONEWCOURSEFROMCOURSEWHEREWILLNUM 20GO使用SELECTINTO插入数据时 应注意 1 新表不能存在 2 新表中的列和行是基于查询结果集的 2 2 4修改表中的数据 修改表中的数据有两种方法 使用UPDATE语句和使用企业管理器 UPDATE语句既可以一次修改一行数据 也可以一次修改多行数据 甚至可以一次修改表中的全部数据行 UPDATE语句使用WHERE子句指定要修改的行 使用SET子句给出新的数据 语法 UPDATETABLE NAMESET COLUMN NAME EXPRESSION DEFAULT NULL N FROMTABLE NAME N WHERE 如果使用UPDATE语句修改数据时与数据完整性有冲突 修改就不会发生 例1 将 01数据库 班学号为 01000025 的学生的名字修改为 陈晓梅 USEXKGOUPDATESTUDENTSETSTUNAME 陈晓梅 FROMSTUDENT CLASSWHERESTUDENT STUNO 01000025 ANDCLASSNAME 01数据库 ANDCLASS CLASSNO STUDENT CLASSNOGO 例2 将已选修了课程且学号为 00000005 的同学的密码改为 7F5E5D3D USEXKGOUPDATESTUDENTSETPWD 7F5E5D3D WHERESTUNO 00000005 ANDSTUNOIN SELECTSTUNOFROMSTUCOU GO 2 2 5使用DELETE从表中删除数据 DELETE语句用来从表中删除数据 可以一次从一个表删除一行或者多行数据 语法 DELETE FROM TABLE NAME WHERE 例1 学号为 00000005 的同学因故取消课程号为 017 的选修课 在STUCOU表中删除此行 USEXKGODELETESTUCOUWHERESTUNO 0000005 ANDCOUNO 017 例2 学号为 00000005 的同学因故取消 中餐菜肴制作 选修课 在表中进行删除处理 USEXKGODELETESTUCOUFROMSTUCOU COURSEWHERESTUCOU STUNO 00000005 ANDCOUNAMELIKE 中餐菜肴制作 ANDSTUCOU COUNO COURSE COUNO 谢谢 第三章结束