SQL第4章数据操纵语句.ppt

1 第四章数据操纵语句 4 1在SQLServerManagementStudio中查看记录4 2INSERT语句4 3UPDATE语句4 4DELETE语句4 5简单查询4 6数据统计查询 2 4 1在SQLServerManagementStudio中查看记录 4 1 1打开表4 1 2快速定位记录4 1 3返回前几条记录 3 4 1 1打开表 例 在SQLserverManagementStudio中打开 Northwind 数据库中的 products 表 1 启动 SQLserverManagementStudio 在 对象资源管理器 中展开树形目录 定位到 products 表 2 右击 products 表 在弹出的快捷菜单中选择 打开表 选项 3 此时已经将 products 表打开了 右边对话框显示的是 products 表里的记录内容 该表格对话框名为 结果窗格 4 4 1 2快速定位记录 在 结果窗格 的最下方 可以快速定位到上一条 下一条记录 也可以直接跳到第一条 最后一条记录 如果要想跳到某一条记录的话 在文本框内输入数字 再按回车键 5 4 1 3返回前几条记录 1 在打开表之后 调出 属性 对话框 调出方法为 单击菜单栏中的 视图 属性窗口 选项 在 属性 对话框里 展开 TOP规范 选项 在 最前面 下拉列表框里选择 是 选项 在 percent 下拉列表框里选择 否 选项 在 表达式 文本框里输入数字 10 2 选择 结果窗格 然后再单击 运行SQL 按钮 在 结果窗格 里只显示前十条记录 6 第四章数据操纵语句 4 1在SQLServerManagementStudio中查看记录4 2INSERT语句4 3UPDATE语句4 4DELETE语句4 5简单查询4 6数据统计查询 7 4 2INSERT语句 4 2 1在SSMS中插入记录4 2 2用INSERT语句记录 8 4 2 1在SSMS中插入记录 注意事项 1 标识列 计算列的字段不能输入字段内容 2 不能为NULL值的字段必须输入字段内容 3 输入字段内容的数据类型要和字段定义的数据类型一致 4 如果字段上有约束 一定要符合约束要求 5 如果要让字段输入默认值 则不用在字段内输入任何数据 在保存记录时 SQLserver会自动填入默认值 但前提是该字段设置有默认值 9 4 2 2用INSERT语句记录 语法格式如下 INSERT INTO table name column list VALUES expression n 10 4 2 2用INSERT语句记录 语法注释 INTO 一个可选的关键字 可以将它用在INSERT和目标表之间 table name 将要接收数据的表或table变量的名称 column list 要在其中插入数据的一列或多列的列表 必须用圆括号将column list括起来 并且用逗号进行分隔 VALUES 引入要插入的数据值的列表 对于column list 如果已指定 中或者表中的每个列 都必须有一个数据值 必须用圆括号将值列表括起来 如果VALUES列表中的值与表中列的顺序不相同 或者未包含表中所有列的值 那么必须使用column list明确地指定存储每个传入值的列 11 4 2 2用INSERT语句记录 1 最简单的INSERT语句 示范案例1 在 Northwind 数据库的 OrderDetails 表里插入一条记录 其中 OrderID 字段的值为10248 ProductID 字段的值为1 UnitPrice 字段的值为10 Quantity 字段的值为2 Discount 字段的值为0 8 程序清单如下 INSERT orderdetails orderid productid unitprice quantity discount VALUES 10248 1 10 2 0 8 12 4 2 2用INSERT语句记录 2 省略清单的INSERT语句 示范案例2 在上例中省略字段清单 程序清单如下 INSERT orderdetails VALUES 10249 2 10 2 0 9 13 4 2 2用INSERT语句记录 3 标识列 允许为空字段的处理 示范案例3 在 Northwind 数据库的 Categories 表里插入一条记录 其中 CategoryName 字段的值为 图书 Description 字段的值为 所有类型的图书 程序清单如下 INSERTINTOcategories categoryname description VALUES 图书 所有类型的图书 14 4 2 2用INSERT语句记录 4 有默认值字段的处理 示范案例4 在 学生 数据库中创建 教师 表 性别为有默认值列 在该表中录入数据 程序清单如下 INSERTINTOteachers teach id teach name tsex tdegree VALUES rj001 李波 DEFAULT 本科 15 4 2 2用INSERT语句记录 5 省略VALUES清单的INSERT语句在T SQL中 有一种简单的插入多行的方法 这种方法是使用SELECT语句查询出的结果代替VALUES子句 这种方法的语法结构如下 INSERT INTO table name column name1 column name2 column namen SELECTcolumn name n FROMtable nameWHEREsearch conditions 16 4 2 2用INSERT语句记录 示范案例5 在 Northwind 数据库中 从 Employees 表里查出 EmployeeID LastName FirstName PostalCode City Address HomePhone 7个字段 组成一个 雇员通信录 表 17 4 2 2用INSERT语句记录 程序清单如下 CREATETABLE雇员通信录 雇员IDintprimarykey 姓氏nvarchar 20 notnull 名字nvarchar 10 notnull 邮政编码nvarchar 10 null 城市nvarchar 15 null 地址nvarchar 60 null 家庭电话nvarchar 24 null GOINSERTINTO雇员通信录SELECTemployeeid lastname firstname postalcode city address homephoneFROMemployeesGO 18 第四章数据操纵语句 4 1在SQLServerManagementStudio中查看记录4 2INSERT语句4 3UPDATE语句4 4DELETE语句4 5简单查询4 6数据统计查询 19 4 3UPDATE语句 4 3 1在SSMS中更新记录4 3 2用UPDATE语句更新记录 20 4 3 1在SSMS中更新记录 注意事项 1 在修改char和nchar这类长度固定的字段时 要注意其后的空格 2 在可以为空值NULL的字段中 如果要设为NULL 可以按Ctrl 0键 3 如果将记录字段内容修改过后 又想回到修改前的值 可用将光标聚焦到该字段 然后按Esc键 4 如果想放弃整条记录的修改 可以连按两次Esc键 21 4 3 2用UPDATE语句更新记录 语法格式如下 UPDATEtable nameSET column name expression DEFAULT NULL n FROM n WHERE table name AS table alias WITH n 22 4 3 2用UPDATE语句更新记录 语法注释 table name 需要更新的表的名称 SET 指定要更新的列或变量名称的列表 column name 含有要更改数据的列的名称 expression DEFAULT NULL 列值表达式 修改数据来源表 23 4 3 2用UPDATE语句更新记录 示范案例6 使用T SQL语句 将 Northwind 数据库 Employees 表中 EmployeeID 字段值为1的记录的 LastName 字段值修改为 bb 程序清单如下 UPDATEemployeesSETlastname bb WHEREemployeeid 1 24 4 3 2用UPDATE语句更新记录 示范案例7 用计算值来更新记录 修改 Northwind 数据库 Products 表中 ProductID 字段值为1的记录的 UnitPrice 字段的值 使其值在当前的基础上减少5 程序清单如下 UPDATEproductsSETunitprice unitprice 5WHEREproductid 1 25 第四章数据操纵语句 4 1在SQLServerManagementStudio中查看记录4 2INSERT语句4 3UPDATE语句4 4DELETE语句4 5简单查询4 6数据统计查询 26 4 4DELETE语句 4 4 1在SSMS中删除记录4 4 2用DELETE语句删除记录4 4 3用Truncatetable语句删除记录 27 4 4 1在SSMS中删除记录 注意事项 1 记录删除之后不能再撤消删除 2 一次可以删除多条记录 3 在选择记录后 按Delete键也可以进行删除操作 4 如果要删除的记录是其他表的外键字段 删除操作可能会影响外键表 28 4 4 2用DELETE语句删除记录 语法格式如下 DELETEtable name FROM n WHERE table name AS table alias n 29 4 4 2用DELETE语句删除记录 语法注释 table name 是要从其中删除行的表的名称 FROM 指定附加的FROM子句 table name AS table alias 是为删除操作提供标准的表名 WHERE 指定用于限制删除行数的条件 如果没有提供WHERE子句 则DELETE删除表中的所有行 指定删除行的限定条件 对搜索条件中可以包含的谓词数量没有限制 30 4 4 2用DELETE语句删除记录 示范案例8 将 Northwind 数据库 OrderDetails 表中 OrderID 字段值为10251的记录删除 程序清单如下 DELETE orderdetails WHEREorderid 10251 31 4 4 2用DELETE语句删除记录 示范案例9 在 Northwind 数据库中删除雇员 Nancy 的所有订单 在该例中删除的数据引用的是雇员表中的数据 程序清单如下 DELETEordersFROMemployeesWHEREemployees employeeid orders employeeidANDemployees firstname Nancy 32 4 4 3用TRUNCATETABLE语句删除记录 基本语法格式 TRUNCATETABLEtable name功能 删除表中的所有记录语法注释 TRUNCATETABLE 为关键字table name 为要删除所用记录的表名 33 4 4 3用TRUNCATETABLE语句删除记录 TRUNCATE与DELETE比较 比DELETE快 不记录日志的操作 它将释放表的数据和索引所占据的所有空间及所有为全部索引分配的页 删除的数据是不可恢复的 34 4 4 3用TRUNCATETABLE语句删除记录 示范案例10 删除学生信息管理数据库student中教师表teachers中的所有记录 程序清单如下 DELETEteachers或TRUNCATETABLEteachers 35 第四章数据操纵语句 4 1在SQLServerManagementStudio中查看记录4 2INSERT语句4 3UPDATE语句4 4DELETE语句4 5简单查询4 6数据统计查询 36 4 5简单查询 4 5 1选择表中的若干列4 5 2选择表中的若干记录4 5 3对查询的结果排序 37 4 5简单查询 基本语法格式 SELECT DISTINCT TOPn PERCENT WITHTIES select listFROMtable list WHEREsearch conditions ORDERBYorder list ASC DESC 38 4 5简单查询 注释说明 SELECT子句用于指定所选择的要查询的特定表中的列 它可以是星号 表达式 列表 变量等 FROM子句用于指定要查询的表或者视图 最多可以指定16个表或者视图 用逗号相互隔开 WHERE子句用来限定查询的范围和条件 ORDERBY用于指定排序方式 DISTINCT用于消除结果集中的重复值 TOPn PERCENT 用于显示前n条或n 条的记录信息 WITHTIES用于在结果集中包含附加记录 在使用ORDERBY子句时 当出现两个或多个记录和最后一条记录的值相等时 这些附加记录也将出现在结果集中 39 4 5 1选择表中的若干列 1 查询表中的所有列 示范案例11 查询 Northwind 数据库中Categories表中的所有记录 程序清单如下 USEnorthwindGOSELECT FROMcategoriesGO 40 4 5 1选择表中的若干列 2 查询表中部分列 示范案例12 查询 Northwind 数据库中 Categories 表中的 categoryname 和 description 字段 程序清单如下 USEnorthwindGOSELECTcategoryname DescriptionFROMcategoriesGO 41 4 5 1选择表中的若干列 3 为结果集内的列指定别名格式 表达式AS别名由于计算列不是数据表中的列 所以用到了 AS 关键字来指定列的别名 即使要查询的列是数据表中真实存在的列 也可以用 AS 来指定别名 关键字AS可以省略 42 4 5 1选择表中的若干列 示范案例13 查询 Northwind 数据库中 OrderDetails 表中每个订单的总价 程序清单如下 USEnorthwindGOSELECTorderid productid unitprice quantity discount unitprice 1 discount quantityASzongjiaFROM orderdetails GO 43 4 5 2选择表中的若干记录 1 查看不重复记录 DISTINCT例如 在 Northwind 数据库中要查看货物在哪些城市里有过销售记录 在订购表 Orders 中有一个字段是 ShipCity 通过查询这个字段 就可以了解货物曾经销往过哪些城市 如果程序代码如下 SELECTshipcityFROMorders或SELECTALLshipcityFROMorders 44 4 5 2选择表中的若干记录 如果要显示不重复的记录的话 可以使用如下代码 SELECTDISTINCTshipcityFROMordersDistinct关键字可以同时指定多个字段 例如同时指定两个字段的话 那么查询的结果是两个字段同时不重复的记录 如以下代码 SELECTDISTINCTshipname shipcityFROMorders 45 4 5 2选择表中的若干记录 示范案例14 在 northwind 数据库中 suppliers 表中检索所有行 但每个国家 Country 只显示一次 程序清单如下 USEnorthwindGOSELECTDISTINCTcountryFROMsuppliersGO 46 4 5 2选择表中的若干记录 2 查看满足条件记录 WHERE如果只希望得到表中满足特定条件的一些记录 用户可以在查询语句中使用WHERE子句 47 4 5 2选择表中的若干记录 常用的查询条件 48 1 比较大小 示范案例15 查询 Northwind 数据库中 Products 表中库存量unitinstock不为零的产品 程序清单如下 USEnorthwindGOSELECT FROMproductsWHEREunitsinstock0GO 4 5 2选择表中的若干记录 49 2 确定范围 示范案例16 在 Northwind 数据库中 products 表中检索 unitprice 在 10 00到 20 00之间的所有产品的产品名称和单价 程序清单如下 SELECT FROMproductsWHEREunitpriceBETWEEN10AND20GO 4 5 2选择表中的若干记录 50 3 确定集合IN 示范案例17 在 northwind 数据库中 suppliers 表中检索坐落在日本或意大利的公司列表 程序清单如下 SELECT FROMsuppliersWHEREcountryIN Japan Italy 4 5 2选择表中的若干记录 51 4 5 2选择表中的若干记录 4 模糊查询 通配符 语法格式 not like ESCAPE 代表任意长度的字符串 长度可为0 的字符串 代表任意单个字符 表示和方括号中列出的任意一个字符相匹配 表示不在方括号中列出的任意一个字符 52 4 5 2选择表中的若干记录 例 a cf h tion action aftion agtion ahtion 0 5 ch 0ch 1ch 2ch 3ch 4ch 5chAb pl c Abcc Abfc 53 4 5 2选择表中的若干记录 字符匹配 示范案例18 在 northwind 数据库 customers 表中检索公司名称中包含字符串 restaurant 公司名称 程序清单如下 SELECT FROMcustomersWHEREcompanynameLIKE restaurant 54 4 5 2选择表中的若干记录 转义字符的使用 示范案例19 在 northwind 数据库的类别 categories 表查询类别名称categoryName中包含下划线 的所有的类别记录信息 程序清单如下 SELECT FROMcategoriesWHEREcategorynameLIKE 55 4 5 2选择表中的若干记录 程序清单如下 SELECT FROMcategoriesWHEREcategorynameLIKE ESCAPE 这里的 号是程序员自行定义的转义字符 并在语句中使用ESCAPE关键字进行说明 可将其替换为其他的符号 如 等等 Select FROMfinancesWheredescriptionLIKE gs ESCAPE S WhereColumnALIKE 5 ESCAPE 56 4 5 2选择表中的若干记录 5 涉及空值 非空值 查询列表达式is not null 示范案例20 在 northwind 数据库 Customers 表中查看区域为空值的顾客信息 程序清单如下 SELECT FROMcustomersWHEREregionISNULL 57 4 5 2选择表中的若干记录 6 多重条件查询 示范案例21 在 northwind 数据库 products 表中检索产品名称 productname 以字母T开头或产品标识为46 并且单价大于 16 00 程序清单如下 SELECT FROMproductsWHERE productnameLIKE T ORproductid 46 ANDunitprice 16 58 4 5 2选择表中的若干记录 3 限制返回行数 TOPTOPn PERCENT WITHTIES 示范案例22 在 Northwind 数据库 orderdetails 订单明细表中查看订购数量最多的五条订单 程序清单如下 区别 SELECTTOP5 withties FROM orderdetails ORDERBYquantityDESCSELECTTOP5 withties FROM orderdetails ORDERBYquantityDESC 59 4 5 3对查询的结果排序 用户可以使用ORDERBY子句对查询结果按照一个或多个属性列的升序 ASC 或降序 DESC 排列 默认为升序 如果不使用ORDERBY子句 则结果集按照记录在表中的顺序排列 基本语法格式 ORDERBY列名 列名 DESC ASC 60 4 5 3对查询的结果排序 示范案例23 在 Northwind 数据库 products 产品表中查看所有产品记录 并以产品名排序 程序清单如下 SELECT FROMproductsORDERBYproductname 61 4 5 3对查询的结果排序 当按多列排序时 先按前面的列排序 如果值相同再按后面的列排序 示范案例24 在 Northwind 数据库 products 产品表中查看所有产品记录 并以供应商编号和产品名排序 程序清单如下 SELECT FROMproductsORDERBYsupplierid productname 62 第四章数据操纵语句 4 1在SQLServerManagementStudio中查看记录4 2INSERT语句4 3UPDATE语句4 4DELETE语句4 5简单查询4 6数据统计查询 63 4 6数据统计查询 4 6 1使用聚合函数4 6 2对结果进行分组4 6 3用COMPUTE子句来归类 64 4 6 1使用聚合函数 SQLServer提供了许多聚合函数 主要有 65 4 6 1使用聚合函数 字段的数据类型决定了可以用在该字段上的聚合函数类型 如 SUM和AVG只能用在数据类型代表数字的字段上 66 4 6 1使用聚合函数 示范案例25 在 Northwind 数据库中查询 employees 表中雇员的数目 程序清单如下 SELECTcount AS雇员数目FROMemployees 67 4 6 1使用聚合函数 实验并思考 1 如果本例中将count 更改为count employeeid 其显示的个数为多少 2 如果改为count city 呢 结果又如何呢 68 4 6 1使用聚合函数 对包含空值的字段使用聚合函数时 应注意SQLServer的聚合函数 COUNT 除外 将忽略字段中的空值 COUNT 将计算所有的行 即使每个字段都含有空值 69 4 6 2对结果进行分组 GROUPBY子句将查询结果集按某一列或多列值分组 分组列的值相等的为一组 并对每一组进行统计计算 对查询结果集分组的目的是为了细化聚合函数的作用对象 GROUPBY子句的语法格式为 GROUPBY ALL 列名 列名 WITH CUBE ROLLUP HAVING筛选条件表达式 70 4 6 2对结果进行分组 语法注释 ALL 用于指定包含所有组和结果集 BY列名 是按列名指定的字段进行分组 使用WITHCUBE会对GROUPBY所列出的所有分组字段进行汇总运算 使用WITHROLLUP会对GROUPBY所列出的第一个分组字段进行汇总运算 HAVING筛选条件表达式 表示对生成的组筛选后再对满足条件的组进行统计 71 4 6 2对结果进行分组 72 4 6 2对结果进行分组 示范案例26 统计 northwind 数据库 orders 表中每个城市的订单总数 程序清单如下 SELECTshipcity count orderid AS订单总数FROMordersGROUPBYshipcity 73 4 6 2对结果进行分组 使用GROUPBY子句时 应注意SQLServer将为每一组计算一个汇总值 并把汇总值保存在一个字段中 对于指定的一组 SQLServer只生成一条记录 不返回详细信息 SQLServer只对满足WHERE子句的记录进行分组和汇总 不要对可能包含空值的字段使用GROUPBY子句 因为空值也将被当作一组 而不管记录是否满足WHERE子句的条件 74 USEnorthwindSELECTproductid orderid quantityFROM orderdetails GO 4 6 2对结果进行分组 75 4 6 2对结果进行分组 示范案例27 统计 northwind 数据库订购 orders 表中订单总数超过20的城市 以及这些城市的订单总数和总运费 程序清单如下 SELECTshipcity count orderid 订单总数 sum freight 运货费总数FROMordersGROUPBYshipcityHAVINGcount orderid 20 76 USEnorthwindSELECTproductid orderid quantityFROM orderdetails GO 4 6 2对结果进行分组 77 4 6 2对结果进行分组 示范案例28 在 northwind 数据库订购 orders 表中按年份统计每个城市的订单总数 程序清单如下 SELECTshipcity count orderid 订单总数 year orderdate AS订购年份FROMordersGROUPBYshipcity year orderdate ORDERBYshipcity year orderdate 78 4 6 2对结果进行分组 描述 79 4 6 2对结果进行分组 示范案例29 统计 Northwind 数据库 orders 表中每个城市的订单总数 并进行汇总 程序清单如下 SELECTshipcity count orderid AS订单总数FROMordersGROUPBYshipcityWITHCUBE 80 4 6 2对结果进行分组 联合使用GROUPBY子句和CUBE操作符 能生成基于GROUPBY子句指定的所有字段的可能组合使用GROUPBY子句和CUBE操作符时 应注意如果在GROUPBY子句中有n个字段或表达式 SQLServer将在结果集中返回2n种可能的组合 结果集中含有NULL的记录代表该记录由CUBE操作符生成 81 4 6 2对结果进行分组 82 4 6 2对结果进行分组 示范案例30 在 Northwind 数据库 orders 表中按年份统计每个城市的订单总数 并按城市进行汇总 程序清单如下 SELECTshipcity year orderdate AS订购年份 count orderid AS订单总数FROMordersGROUPBYshipcity year orderdate WITHROLLUP 83 4 6 2对结果进行分组 联合使用GROUPBY子句和ROLLUP操作符 计算组中的汇总值使用GROUPBY子句和ROLLUP操作符时 应注意SQLServer处理GROUPBY中字段列表的顺序是从右到左 然后对每个组使用聚合函数 SQLServer将在结果集中增加一行 这行将显示总和或平均值之类的汇总值 新增的行以NULL标识 84 4 6 3用COMPUTE子句来归类 COMPUTE子句对查询结果集中的所有记录进行汇总统计 并显示所有参加汇总记录的详细信息 当与by一起使用时 COMPUTE子句在结果集内生成控制中断和小计 可在同一查询内指定COMPUTEBY和COMPUTE 使用语法格式为 COMPUTE聚合函数 列名 BY列名 85 4 6 3用COMPUTE子句来