汽车服务企业信息化管理基础关系数据库语言SQL.ppt

2020 3 4 1 四关系数据库语言SQL 4 1查询语句4 2数据更新4 3数据定义 学习目标 能够熟练掌握SQL语言并能够在实际工作中进行灵活的应用 2020 3 4 2 SQL是结构化查询语言 StructuredQueryLanguage 的缩写 美国国家标准局ANSI在1986年将SQL作为关系数据库系统的标准语言 后被国际标准化组织 ISO 采纳为国际标准 现在很多大型数据库都实现了SQL语言 SQL概述 2020 3 4 3 SQL和SQLSERVER的区别 SQL structuredquerylanguage 结构化查询语言 它是一种标准 不是一种软件SQLSERVER是数据库管理系统的一种它是一种软件 这种软件在遵循SQL这种标准 很多数据库管理软件及开发工具都支持SQL这种标准 2020 3 4 4 SQL语言的特点 SQL语言的特点 1 综合统一2 高度非过程化3 面向集合的操作方式4 以同一种语法结构提供两种使用方法5 语言简洁 易学易用 2020 3 4 5 2020 3 4 6 SQL语句的书写准则 对大小写不敏感一条语句可以写成一行或多行关键字不能在行与行之间分开数据项例如属性项 表 视图项等同时列出时 分隔符用逗号 字符或字符串常量要用单引号定界 2020 3 4 7 基本操作对象 基本表 本身独立存在的表 SQL中一个关系就对应一个表 一个表对应一个存储文件 一个表可以带若干索引 视图 从一个或几个基本表中导出的表 本身不独立存放在数据库中 数据库只存储视图的定义 视图相当于一个虚表 用户可以在视图上在定义视图 索引 为快速访问数据 而在包含数据的表中增加的一种组织 分为聚簇索引和非聚簇索引 指索引项的顺序与表中记录的物理顺序一致的索引组织 例如 人事档案 员工姓名 地址 电话号码 社会安全号 工资 其中只有姓名 地址 电话号码是公开信息 可以生成不包含社会安全号和工资的视图 对表和视图设置不同的权限 2020 3 4 8 4 1查询语句 4 1 1基本查询4 1 2使用列表达式4 1 3WHERE从句的进一步使用4 1 4数据汇总4 1 5连接查询4 1 6嵌套查询4 1 7联合查询4 1 8使用系统内置函数的查询 2020 3 4 9 SQL的核心是数据查询 对于数据库的查询操作是通过SELECT查询命令实现的 它的基本形式由SELECT FROM WHERE查询块组成 多个查询块可以嵌套执行 2020 3 4 10 语句格式SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 指定要显示的属性列 指定查询对象 指定查询条件 查询结果按指定列的值分组 筛选出只有满足指定条件的组 对查询结果表按指列值的升序或降序排序 2020 3 4 11 4 1 1基本查询 1 简单的无条件查询 例 Select fromstu info 把stu info表中的所有记录的所有属性查询出来 2020 3 4 12 SELECT FROMSTU INFOWHEREXSH 06 4 1 1基本查询 2 简单的条件查询例4 2 从edu d的数据库表stu info中查找包含所有列的土建学院 学院代号xsh为06 的学生名单 字符或字符串用单引号引起来 相当于选择运算 2020 3 4 13 例4 3 从数据库EDU INFO中查询土建学院 XSH 06 的学生的学号 姓名 性别 学院代号信息 SELECTXH XM XBM XSHFROMSTU INFOWHEREXSH 06 3 查询语句中投影运算的实现 2020 3 4 14 4 1 2使用列表达式 查询指定的列 除了可以使用 或者属性列表以外 还可以使用列表达式 SELECTXH KCH JSH 150成绩 KSCJ 1 50FROMXK 利用列表达式实现不同列的连接 例4 5SELECT 学号 XH XM XSH BHFROMSTU INFOWHEREXBM 男 ORDERBYXHDESC 在SQLServer2000系统的企业管理器中运行后系统自动将语句的写法改成了 SELECT 学号 ASEXPR1 XH XM XSHASEXPR2 BHFROMSTU INFOWHERE XBM 男 ORDERBYXHDESC 2020 3 4 16 注意 更改的是查询结果显示的列标题 这是列的别名 而不是更改了数据库表或视图的列标题 2 修改查询结果的列标题 例如 改写例4 5SELECTXHAS学号 XM XSHAS姓名和学院代码 BHAS班级FROMSTU INFOWHEREXBM 男 ORDERBYXHDESC 2020 3 4 17 另一种形式 SELECT学号 XH 姓名和学院代码 XM XSH 班级 BHFROMSTU INFOWHEREXBM 男 ORDERBYXHDESC 注意 当自定义的列标题中含有空格时 必须用引号将标题括起来 2020 3 4 18 采用别名的目的 数据库表中的列名用英文字母表示更方便 但是为了在查询时 让用户看到的结果更直观 所以可以采用中文别名 2020 3 4 19 查询工作的关键就在通过分析怎样把实际问题中的自然语言描述转化为1 从哪个表中查询即 from后的一项2 要查询哪些列即 select后的一项3 要查询的条件即 where后的一项 完成查询工作的三个关键步骤 第3步最复杂 4 1 3WHERE从句的进一步使用 WHERE从句中 可以使用一个或者多个逻辑表达式限制查询数据的范围 表4 1常用查询条件一览表 2020 3 4 21 1 表达式比较 例如 SELECT FROMXKWHEREKSCJ 60 SELECTXH学号 KCH课程号 KSCJ成绩FROMXKWHEREKSCJBETWEEN60AND70 2020 3 4 22 3 确定集合 例4 7 查询应电0601 机专0601班的学生的信息 SELECT学号 姓名 班级FROMmingdanWHERE班级IN 应电0601 机专0601 当与指定的值表中的任何一个匹配时 返回TRUE 否则返回FALSE 2020 3 4 23 4 字符匹配 用NOTLIKE和LIKE与通配符 和 搭配 其运算对象可以是CHAR VARCHAR TEXT NTEXT DATETIME和SMALLDATETIME类型的数据 表示任意长度的字符串 表示人一单个字符 例如 1 SELECT FROMmingdanWHERE班级LIKE 应 2 SELECT FROMmingdanWHERE姓名LIKE 红 2020 3 4 24 5 涉及空值的查询 例如 SELECT FROMmingdanWHERE班级ISNULL 6 多重条件查询 例如 SELECT FROMmingdanWHERE班级LIKE 应 AND姓名LIKE 红 2020 3 4 25 一般格式 SELECT列名1 列名2 FROM表名WHERE条件表达式ORDERBY列名1 ASC DESC 列名2 ASC DESC 升序 默认 降序 4 1 4 设定排序条件 2020 3 4 26 例4 4 从数据库EDU D的表STU INFO中查询建9809班学生情况并按学号排序 SELECT FROMSTU INFOWHEREBH 建9809 ORDERBYXH SELECT FROMSTU INFOWHEREBH 建9809 ORDERBYZYH XHDESC 按多列排序 先按专业升序排列 然后同一专业的记录按学号进行降序排列 注意 1 ORDERBY子句不改变基本表中行或者列的顺序 只改变查询结果的排列顺序 2 ORDERBY子句制定排序的列必须出现在SELECT子句的列表达式中 3 排序是查询语句的最后一步工作 所以要把ORDERBY子句一般放在查询语句的最后 2020 3 4 28 在要查询的属性名前加DISTINCT 以去掉结果中重复的值 例 SELECTDISTINCT班级FROMmingdan 注意 在一个SELECT语句中 DISTINCT只能出现一次 并且DISTINCT必须写在所有列名之前 5 限定重复记录 2020 3 4 29 4 1 4数据汇总 1 聚合函数 表4 2聚合函数一览表 表示去掉结果中的重复行 指定ALL选项或不指定则表示保留重复行 2020 3 4 30 例4 8 查询学生总数SELECTCOUNT AS学生数FROMmingdan或者SELECTCOUNT XH FROMmingdan 例4 9 已知XK中高等数学的代号是090101 查询学生选修高等数学的平均成绩SELECTAVG KSCJ AS 高等数学平均成绩 FROMXKWHEREKCH 090101 因为XH是该表的主键 不允许有空值 查询每个班的学生人数 2020 3 4 31 例4 10 查询学生选修高等数学的最高分和最低分SELECTMAX KSCJ AS 高等数学最高分 MIN KSCJ AS 高等数学最低分 FROMXKWHEREKCH 090101 注意 在查询中 除COUNT 外 所有的聚合函数都不包括取值为空的行 2020 3 4 32 2 GROUPBY子句 GROUPBY子句用于对表或视图中数据的查询结果按某一列或多列值分组 值相等的分为一组 2 GROUPBY子句 例4 11 在表mingdan中查询各班的学生人数 SELECT班级 COUNT XH AS人数FROMmingdanGROUPBY班级 注意 SELECT子句的列表中只能包含在GROUPBY中指出的列或在聚合函数中指定的列 GROUPBY子句用于对表或视图中数据的查询结果按某一列或多列值分组 值相等的分为一组 2020 3 4 33 2 GROUPBY子句 举例 从表stu中统计xsh 0015 中男生和女生的人数 Selectcount fromstuwherexsh 0015 groupbyxb 还可以按多个字段分组 思考 统计每个学院的男生和女生的人数 2020 3 4 34 例4 12 在表STU INFO上产生一个结果集 包括每个专业的男生 女生人数 SELECTZYH XBM COUNT AS人数FROMSTU INFOGROUPBYZYH XBM 2020 3 4 35 3 HAVING短语 HAVING短语指定组或聚合的条件 只有满足条件的组才被选出来 HAVING通常与GROUPBY子句一起使用 注意与WHERE子句区分 SELECTXH COUNT FROMXK GROUPBYXH HAVINGCOUNT 3 例4 15查询选修了3门以上课程的学生及选课数 2020 3 4 36 例4 16 只查询选修了3门以上课程的学号前四位是20011的学生学号及选课数 SELECTXH COUNT FROMXKWHEREXHLIKE 2001 GROUPBYXHHAVINGCOUNT 3 2020 3 4 37 Having短语指定选择组的条件 只有满足条件的组才被选出来 Where子句从基本表中选择满足条件的元组 having选择满足条件的组 2020 3 4 38 若一个查询同时涉及两个或两个以上的表 则称为连接查询 连接查询中用来连接两个关系的条件称为连接条件或连接谓词 一般格式为 连接谓词中的列名称为连接字段 4 2 5连接查询 例如 xk xh stu info xh 连接查询的from后面跟多个表 2020 3 4 39 连接查询中用来连接两个关系的条件称为连接条件或连接谓词 格式 其中比较运算符主要有 当连接运算符为 时 叫等值连接 使用其它运算符称为非等值连接 连接谓词中的列名称为连接字段 在SELECT中连接条件放在where后面 2020 3 4 40 例 查询每个学生及其选课情况 Selectstu info xk Fromstu info xkWherestu info xh xk xh 2020 3 4 41 例 查询物理012班每个学生及其选修课程的情况 SELECTstu info xk FROMstu info xkWHEREstu info xh xk xhandbh 物理012 复合条件连接 2020 3 4 42 例4 18 查询选修了有机化学这门课程的学生的姓名 SELECTXMFROMSTU INFO XK GCOURSEWHEREGCOURSE KM 有机化学 ANDGCOURSE KCH XK KCHANDXK XH STU INFO XH 说明 1 连接查询涉及的所有表名都放在FROM子句后面 2 连接条件放在WHERE子句中 3 如果属性列名在参加连接的各表中是唯一的 可以省略表名前缀 如果属性列名是两个表共同的属性 则一定要加表名前缀 2020 3 4 43 4 在书写连接查询时 为了简化 可以为表名取别名 别名应该简单 别名只在本次查询有效 例 4 18 SELECTXMFROMSTU INFOS XKX GCOURSECWHEREC KM 有机化学 ANDC KCH X KCHANDX XH S XH 2020 3 4 44 4 1 6嵌套查询 例4 19 在STU info表中查询选修了课程编号为090201的学生姓名 分析 查询所要求的结果出自一个关系 STU info 但相关的条件却涉及多个关系 STU info和XK SELECTXMFROMSTU INFOWHERE XHIN SELECTXHFROMXKWHEREKCH 090201 一个SELECT FROM WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 2020 3 4 45 说明 1 这个查询首先执行小括号里的内部查询 得到一个结果集 再判断外部查询的列是否属于这个集合 2 关键字IN的前面只能有一个列 这个列必须与内部查询结果集里的各个值含义相同 数据类型兼容 SELECTXMFROMSTU INFOWHERE XHIN SELECTXHFROMXKWHEREKCH 090201 2020 3 4 46 例4 20查询与刘玉涛在同一个班学习的学生 SELECTXH XM BHFROMSTU INFOWHERE BHIN 1 带有in谓词的子查询 同一个学校的刘玉涛可能重名 而且可能不同班 SELECTBHFROMSTU INFOWHEREXM 刘玉涛 2020 3 4 47 2 带有比较运算符的子查询 例4 21查询与学号2000029001的学生在同一个班学习的学生 按学号排序 SELECTXH XM BHFROMSTU INFOWHERE BH ORDERBYXH 明确内查询结果是唯一值时可以使用 SELECTBHFROMSTU INFOWHEREXH 2000029001 2020 3 4 48 带有比较运算符的子查询 举例 从mingdan表中查询成绩最高的同学的学号 姓名和成绩 Select学号 姓名 成绩frommingdanWhere成绩 selectmax 成绩 frommingdan Select学号 max 成绩 frommingdan Select学号 max 成绩 frommingdangroupby学号 2020 3 4 49 3 带有ANY SOME ALL的比较子查询 例4 22查询其他班级中比材物2k01班某个学生年龄小的学生的姓名和年龄 SELECTXM NLFROMSTU INFOWHERENL 材物2k01 同义字 SELECTNLFROMSTU INFOWHEREBH 材物2k01 2020 3 4 50 4 带有EXISTS谓词的子查询 EXISTS是测试子查询是否有数据行返回 如果有则返回TRUE 否则返回FALSE NOTEXISTS则相反 当结果表为空时 才返回TRUE 例4 23查询选修了高等数学 KCH 090101 的学生的姓名等信息 SELECTXM XBM BHFROMSTU INFOWHEREEXISTS SELECT FROMXKWHEREXH STU INFO XHANDKCH 090101 不需要明确指定列名 这个子查询不一定会执行完 只要发现匹配条件成立 就退出子查询 2020 3 4 51 例4 24查询没有选修高等数学的学生的姓名等信息 SELECTXM XBM BHFROMSTU INFOWHERENOTEXISTS SELECT FROMK2001WHEREXH STU INFO XHANDKM 高等数学 2020 3 4 52 例 在stu info表中查询选修了090101号课程的学生姓名SELECTxmFROMstu infoWHERE XHIN SELECTXHFROMxkWHERE kch 090101 当查询的信息涉及一个关系时 嵌套查询和连接查询可互换 Selectxmfromstu info xkWherestu info xh xk xhandkch 090101 2020 3 4 53 4 1 7联合查询 多个SELECT语句的结果可进行集合操作 SQLServer的Transact SQL语言只提供UNION 并 运算符实现并操作 语法格式 SELECT 1UNION ALL SELECT 2 UNION ALL SELECT 3 进行并运算的子查询 2020 3 4 54 例4 25 有两个数据库表STUFR和STUIS 其结构完全一样 分别存放外语学院和信息学院的学生基本信息 现在要查询两个学院女生的学号 姓名 SELECTXH XMFROMSTUFRWHEREXB 女 UNIONSELECTXH XMFROMSTUISWHEREXB 女 2020 3 4 55 说明 1 使用UNION运算符进行联合查询时 要保证各个SELECT语句的目标列表达式数量相等 排列顺序相互一一对应 数据类型必须兼容 2 UNION之后如果使用了ALL选项 则显示所有的包括重复的行 如果没有使用ALL选项 则重复行只显示一行 3 UNION操作常用于归档数据 例如归档各个基层部门的数据等 运行时将查询的数据合并到第一个表中 4 也可以对结果进行排序或者分组汇总 这是必须把ORDERBY子句或GROUPBY子句放在最后一个SELECT语句的后面 并且必须是针对第一个SELECT语句的列进行的排序或分组 5 使用UNION连接的所有SELECT语句也可以使用同一张表 此时UNION运算符可以用OR运算符来代替 2020 3 4 56 例4 26 将上个例子中的两个数据库表STUFR和STUIS的数据合并到结构相同的全校的学生数据表STU中去 SELECT FROMSTUUNIONALLSELECT FROMSTUFRUNIONALLSELECT FROMSTUIS 如何将查询的结果保存在当前数据库新建的表NEWSTU中 SELECT INTONEWSTUFROMSTUFRUNIONALLSELECT FROMSTUIS 2020 3 4 57 例4 27 查询01学院的学生及所有的本科生 SELECT FROMSTU INFOWHEREXSH 01 UNIONSELECT FROMSTU INFOWHEREPYCCM 本科 改写SELECT FROMSTU INFOWHEREXSH 01 ORPYCCM 本科 2020 3 4 58 4 1 8使用系统内置函数的查询 T SQL提供了三种系统内置函数 行集函数 聚合函数和标量函数 其中聚合函数在4 1 4节进行了介绍 1 数学函数 对SQLServer提供的数字数据 decimal integer float real money smallmoney smallint和tinyint 进行数学运算并返回运算结果 2020 3 4 59 2 RAND函数语法格式 RAND seed 功能 返回0 1之间的一个随机值 1 ABS函数语法格式 ABS 数值型表达式 功能 返回给定数值表达式的绝对值 参数seed 种子 为整型表达式 2020 3 4 60 1 ASC 函数语法格式 ASC 字符型表达式 功能 返回字符表达式最左端字符的ASC 值 2 CHAR函数语法格式 CHAR 0 255之间的整数 功能 将ASC 转换为字符 2020 3 4 61 3 LEFT函数语法格式 LEFT 字符型表达式 整型表达式 功能 返回从字符串左边开始指定个数的字符 例4 28 查询学号最左边的4个字符 SELECTLEFT XH 4 FROMSTU INFO 2 字符串处理函数 2020 3 4 62 4 LTRIM函数语法格式 LTRIM 字符型表达式 功能 删除字符型表达式字符串最左边的空格 并返回字符串 5 REPLACE函数语法格式 REPLACE 字符表达式1 字符表达式2 字符表达式3 功能 用字符表达式3替换字符表达式1中包含的字符表达式2 并返回替换后的表达式 2020 3 4 63 例4 29已知某学校学号 XH char 的前4位是入学年 第5位是性别代码 后5位是流水号 如某女生的学号 2002030101 在学生基本情况数据库表STU INFO中查询所有学生的学号 姓名 入学年 性别信息 6 SUBSTRING函数语法格式 SUBSTRING expression 整型 整型 功能 返回expression中指定的部分数据 可以是字符串 二进制串 text image字段或表达式 指定从expression的第几个字节开始 length指定要返回的字节数 SELECTXH XM LEFT XH 4 AS入学年 SUBSTRING XH 5 1 AS性别码FROMSTU INFOORDERBYXH 2020 3 4 64 7 STR函数语法格式 STR FLOATL类型的表达式 length decimal 功能 将数字数据转换为字符数据 返回值的总长度 包括小数点 制定小数点右边的位数 2020 3 4 65 3 系统函数 系统函数用于对SQLServer中的值 对象和设置进行操作并返回有关信息 1 CASE函数 简单的CASE函数语法格式 CASE输入表达式WHEN比较表达式THEN结果表达式 ELSE表达式END功能 计算输入表达式的值 与每一个WHEN的比较表达式的值比较 如果相等 则返回对应的结果表达式的值 否则 返回ELSE之后的表达式的值 如果省略了ELSE 则返回NULL值 2020 3 4 66 例4 30 在STU INFO中查询03学院学生的学号 姓名 性别 并将性别分别转换成 男生 女生 SELECTXH XM XBM XBM CASE 男 WHENXBMTHEN 男生 ELSE 女生 ENDFROMSTU INFOWHEREXSH 03 2020 3 4 67 CASE搜索函数语法格式 CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2 ELSE表达式END功能 系统将查询出的结果进行判断 当满足WHEN的某个条件时 则将该结果显示为THEN之后的表达式的值 如果没有满足的条件 则显示ELSE之后的表达式的值 如果没有指定ELSE子句时 返回NULL值 2020 3 4 68 例4 31 查询成绩表XK中选修了 090101 课程的学生的学号 5分制成绩 SELECTXH 5分制成绩 CASEWHENKSCJ 60ANDKSCJ 70ANDKSCJ 80ANDKSCJ 90THEN 优秀 ENDFROMXKWHEREKCH 090101 注意 这个查询改变的仅仅是查询出的结果 而基础表中的数据并没有发生转换 2020 3 4 69 2 CAST函数语法格式 CAST 表达式AS数据类型 功能 将表达式的值转换为数据类型参数所指定的类型 例4 32 查询成绩表K2004 其中考试成绩 KSCJ XH XM等数据类型都是CHAR 现要查询考试成绩在50分到60之间的学生及成绩 并将成绩加10分显示 SELECTXH XM KSCJ CAST KSCJASFLOAT 10AS加分后成绩FROMK2004WHEREKSCJLIKE 5 2020 3 4 70 3 日期时间函数GETDATE语法格式 GETDATE 功能 按照SQLServer标准内部格式返回当前的系统日期和时间 返回值数据类型为datetime型 4 年 月 日函数语法格式 YEAR date MONTH date DAY date 其中参数date是数据类型为datetime或smalldatetime的表达式 返回值的数据类型为int型 2020 3 4 71 4 2数据更新 主要包括对数据库表的数据进行插入 修改 删除操作 INSERTUPDATEDELETE 2020 3 4 72 4 2 1插入数据 插入数据的操作有两种形式 1 使用VALUES子句向数据库的基本表表一次插入一个元组 2 插入SELECT子查询的结果 一次插入一批数据记录 2020 3 4 73 1 插入单个元组 语法格式 INSERTINTO VALUES 说明 1 属性列与常量必须一一对应 数据类型要一致 2 在基本表结构定义中未说明为NOTNULL的属性列 如果没有出现在INTO子句后 这些列将取空值 已经说明为NOTNULL的属性列 则必须出现在INTO子句后面 3 如果INTO子句后面没有指定任何列 则VALUES子句后面的常量个数必须与基本表中列的个数相等 且类型 顺序一致 2020 3 4 74 例 在STU COU数据库中 INSERTINTOSTU XH XM XB XSH NL VALUES 20050902090 王东方 男 0019 22 等价于 INSERTINTOSTUVALUES 20050902090 王东方 男 0019 22 XH XM XB XSH NL 表中所有的列 2020 3 4 75 2 插入子查询结果 语法格式 INSERT INTO 表名 列名1 列名N SELECT兼容列名1 兼容列名NFROM兼容表名WHERE逻辑表达式 2020 3 4 76 例 4 33在STU表中查询出女生的学生信息存放在STUIS表中 INSERTINTOSTUISSELECTXH XM XB XSH NLFROMSTUWHEREXB 女 INSERTintoSTUISSELECT FROMSTUWHEREXB 女 或者 2020 3 4 77 4 2 2修改数据 修改数据主要是对数据库表中一个或多个元组某个或某些属性的值进行更改 语法格式 UPDATESET WHERE 2020 3 4 78 1 修改一个元组的值 例如 UPDATESTUSETXSH 机械 NL NL 1WHEREXH 20000501022 2 修改多个元组的值 例 将所有课程的学分改为2分 UPDATECOURSESETCCREDIT 2 例 将女生年龄增加1岁 UPDATESTUSETNL NL 1WHEREXB 女 2020 3 4 79 3 带子查询的修改语句 4 34 将选修了高等数学的90分以下的学生的成绩加10分 从edu d库中的xk数据表中修改 UPDATEXKSETKSCJ KSCJ 10WHEREKCH SELECTKCHFROMGCOURSEWHEREKM 高等数学 ANDKSCJ 90 建议 使用UPDATE对批量数据进行修改前 先用SELECT语句将要修改的记录查询出来 仔细检查无误后 再进行修改 2020 3 4 80 4 2 3删除数据 语法格式 DELETEFROM WHERE 注意 DELETE命令删除的是一个或多个元组 记录 行 而不是某个行中个别列的值 不需要的某个行的个别列的值 只能用UPDATE命令修改成NULL值或空格符 不能被删除 2020 3 4 81 1 删除一个元组 例如 DELETEFROMSTU INFOWHEREXH 2003050601 2 删除多个元组 例如 DELETEFROMSTU INFOWHEREBH 应0203 2020 3 4 82 3 带子查询的删除语句 例4 35 将信息学院学生的选课信息全部删除 注意 为避免误操作 在日常工作中 通常操作删除语句时 要先查询要删除的数据 确认无误后 再将SELECT 部分改成DELETE进行删除操作 DELETEFROMXK STU INFOWHERESTU INFO XSH 12 ANDSTU INFO XH XK XH SELECTXK FROMXK STU INFOWHERESTU INFO XSH 12 ANDSTU INFO XH XK XH SELECT FROMXKWHERE 12 SELECTXSHFROMSTU INFOWHERESTU INFO XH XK XH DELETEFROMXKWHERE 12 SELECTXSHFROMSTU INFOWHERESTU INFO XH XK XH 2020 3 4 83 4 3数据定义 SQL的数据定义语句是对数据库表 视图 索引等的结构和属性进行定义 常见的操作方式如下表 2020 3 4 84 4 3 1定义基本表 一 语法格式 CREATETABLE 数据库名 默认为当前数据库 要定义的基本表的名字 组成该表的各个属性 列 涉及相应属性列的完整性约束条件 涉及一个或多个属性列的完整性约束条件 2020 3 4 85 常用完整性约束主码约束 PRIMARYKEY唯一性约束 UNIQUE非空值约束 NOTNULL参照完整性约束FOREIGNKEY REFERENCESCHECK约束CHECK默认值DEFAULT标识列IDENTITY seed inctement 公式AS 2020 3 4 86 二 数据类型 2020 3 4 87 浮点类型 2020 3 4 88 字符类型 2020 3 4 89 二进制数据类型 2020 3 4 90 时间类型 2020 3 4 91 货币类型 2020 3 4 92 例1 建立一个 学生 表Student 它由学号Sno 姓名Sname 性别Ssex 年龄Sage 所在系Sdept五个属性组成 其中学号不能为空 值是唯一的 并且姓名取值也唯一 CREATETABLEStudent SnoCHAR 5 NOTNULLUNIQUE SnameCHAR 20 UNIQUE SsexCHAR 1 SageINT SdeptCHAR 15 2020 3 4 93 例2 建立一个 学生选课 表SC 它由学号Sno 课程号Cno 修课成绩Grade组成 其中 Sno Cno 为主码 CREATETABLESC SnoCHAR 5 CnoCHAR 3 Gradeint Primarykey Sno Cno 2020 3 4 94 例4 36 定义一个学生的成绩表k2001 CREATETABLEk2001 表名为K2001 XHvarchar 12 NOTNULL XH 学号 列 不为空 XMvarchar 8 NULL 姓名 KCHvarchar 8 NOTNULL 课程代码 KSCJvarchar 5 NULL 考试成绩8 KKNYvarchar 5 NULL 开课时间 KCXFvarchar 5 NULL 课程学分 KMvarchar 30 NULL 课程名称 KCFZvarchar 1 NULL 课程分组 JSMvarchar 8 NULL 任课教师 BZvarchar 18 NULL 备注 2020 3 4 95 例4 37 创建表n jobs CREATETABLEn jobs job idsmallintIDENTITY 1 1 PRIMARYKEYjob descvarchar 50 NOTNULLDEFAULT 新部门 暂无职位 min lvltinyintNOTNULLCHECK min lvl 12 max lvltinyintNOTNULLCHECK max lvl 250 是表示为PRIMARYKEY约束创建聚集关键字 表示新列是标识列 必须同时指定种子seed和增量increment 或者二者都不指定 如果二者都未指定 则取默认值 1 1 设置默认值 默认值可以是常量 NULL或系统函数 例4 38 创建表new employees CREATETABLEnew employee emp idchar 9 CONSTRAINTPK emp idPRIMARYKEYNONCLUSTEREDCONSTRAINTCK emp idCHECK emp idLIKE A Z A Z A Z 1 9 0 9 0 9 0 9 0 9 FM oremp idLIKE A Z A Z 1 9 0 9 0 9 0 9 0 9 FM 每个员工编号由三个字符打头 然后是10000 99999的某个数字和代表性别的字母F或M fnamevarchar 20 NOTNULL minitchar 1 NULL lnamevarchar 30 NOTNULL job idsmallintNOTNULLDEFAULT1REFERENCESjobs job id job lvltinyintDEFAULT10 pub idchar 4 NOTNULLDEFAULT 9952 REFERENCESpublishers pub id hire datedatetimeNOTNULLDEFAULT getdate 使用了获取当前日期的函数作为默认值 表示PRIMARYKEY NOTNULL UNIQUE FOREIGNKEY或CHECK约束定义的开始 约束的名称 外键约束 2020 3 4 97 4 3 2修改基本表 语法结构 ALTERTABLE ADD 完整性约束 DROP COLUMN ALTERCOLUMN 增加新列和新的完整性约束条件 删除指定的完整性约束条件 用于修改列名和数据类型 2020 3 4 98 例4 43向Stu info表增加 入学时间 列 其数据类型为日期型 ALTERTABLESTU INFOADDRXSJDATETIME 不论基本表中原来是否已有数据 新增加的列一律为空值 1 添加列 2020 3 4 99 例4 44 将教师表的XB列改为SMALLINT数据类型 注意 修改原有的列定义有可能会破坏已有数据 2 修改列 ALTERTABLEGTECHALTERCOLUMNXBSMALLINT 2020 3 4 100 3 删除列 例4 45 删除教师表中的性别一列 ALTERTABLEGTECHDROPCOLUMNXB 下面的列不能除去 被复制列 用在索引中的列 用在CHECK FOREIGNKEY UNIQUE或PRIMARYKEY约束中的列 有相关联的默认值 由DEFAULT关键字定义 的列或绑定到默认对象的列 绑定到规则的列 2020 3 4 101 例 删除数据库表new employee中的emp id的检查约束 ALTERTABLEnew employeeDROPCK emp id 4 删除约束 2020 3 4 102 4 3 3删除基本表 语法格式 DROPTABLE 2020 3 4 103 4 3 4视图 视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制 视图是从一个或多个基本表 或视图 导出的表 是个虚表 数据库只存放视图的定义 不存放视图对应的数据 这些数据仍存放在原来的基本表中 所以 视图的数据跟随基本表的数据而变化 视图一旦被定义 就可以被查询 删除 修改 再定义一个新的视图 2020 3 4 104 补充说明 视图是一个虚拟表 其内容由查询定义 同真实的表一样 视图包含一系列带有名称的列和行数据 但是 视图并不在数据库中以存储的数据值集形式存在 行和列数据来自由定义视图的查询所引用的表 并且在引用视图时动态生成 视图允许我们把表结构的细节封装起来 这些表可能因你的应用的进化而变化 而我们却可以通过视图给用户一个一致的接口 视图几乎可以在一个真正的表可以使用的任何地方使用 在其它视图上面再建造视图也是很常见的 2020 3 4 105 1 定义视图 1 用企业管理器建立视图 自学 2 用SQL数据定义语句createview命令建立视图 语法格式 CREATEVIEW视图名AS WITHCHECKOPTION 对视图进行UPDATE INSERT和DELETE操作时要保证更新 插入或删除的行满足视图定义中的条件表达式 2020 3 4 106 注意问题 1 只能在当前的数据库中创建视图 2 一个视图最多可以引用1024个列 3 视图的命名必须符合SQLServer中的标识符的定义规则 4 可以将视图建立在其他视图或者引用视图的过程之上 SQLServer2000中允许最多32层的视图嵌套 5 不能将规则 默认值定义绑定在视图上 6 定义视图的查询语句中不能包括COMPUTE COMPUTEBY ORDERBY子句或是INTO等关键词 7 在视图中不能定义全文索引 但可以定义索引 8 不能创建临时视图 而且也不能在临时表上创建视图 2020 3 4 107 9 默认状态下 视图中的列继承他们在基表中的名称 对于以下情况 在创建视图时需要明确给出每一列的名称 视图中的某些列来自表达式 函数或常量 视图中两个或多个列在不同表中具有相同的名称 希望在视图中的列使用不同于基表中的列名时 2020 3 4 108 A 选择列定义视图 例 CREATEVIEWMYVIEW1ASSELECTEMP ID LNAME FNAMEFROMEMPLOYEE 定义了视图后 可以和表一样 使用SELECT语句访问它 2020 3 4 109 B 基于列的表达式定义视图 例 CREATEVIEWMYVIEW2ASSELECTTITLE ADVANCE PRICE ROYALTY YTD SALESASNEWPRICEFROMTITLESWHEREPRICE 5 2020 3 4 110 C 选择行定义视图 例 CREATEVIEWMYVIEW3ASSELECT FROMTITLESWHERETITLELIKE SALES 2020 3 4 111 D 选择行和列 例 CREATEVIEWMYVIEW4ASSELECTTITLE ID PUB ID TITLEFROMTITLESWHERETITLELIKE SALES 2020 3 4 112 E 基于多个表定义视图 例 CREATEVIEW查询化学院学生ASSELECTSTU INFO XH STU INFO XM STU INFO BH GDEPT XSM GFIED ZYMFROMSTU INFO GDEPT GFIEDW