关系数据库标准语言SQL讲义课件

第3章关系数据库标准语言SQL数据库基础第3章关系数据库标准语言SQL数据库基础1应用软件用户级别操作最简单功能受约束-需购买软件企业管理器管理员级别-操作简单-功能较全-直接使用DBMSSQL语言高级管理员、程序员级别-操作较复杂-使用最灵活，效率最高直接使用DBMS可用于开发应用软件第3章关系数据库标准语言SQL应用软件用户级别企业管理员级别SQL语言高级管理员、程序员级2本章主要内容3.1SQL语言概述（了解）3.2数据定义（重点）3.2.1操作数据库3.2.2操作表3.2.3索引的创建和删除3.3数据查询（重点）3.3.1单表查询3.3.2连接查询3.3.3嵌套查询3.3.4联合查询3.4数据更新（重点）3.4.1插入数据3.4.2修改数据3.4.3删除数据3.5视图的创建和删除(了解)第3章关系数据库标准语言SQL本章主要内容3.4数据更新（重点）第3章关系数据库标准33.1.1SQL语言的发展

SQL语言是当前最为成功，应用最为广泛的关系数据库语言，其发展主要经历了以下几个阶段：1972年1974年1987年SQUARE阶段SEQUEL阶段SQL阶段

现在SQL语言应用于Oracle,Sybase,Access和SQLServer等。3.1.1SQL语言的发展SQL语言是当前最为成功，43.1.2T-SQL语言SQL（StructuredQueryLanguage，结构化查询语言）包括数据定义、数据查询、数据更新、数据控制等功能。Access中使用的SQL语言称为J-SQL。SQLServer中使用的SQL语言称为T-SQL。Microsoft针对其自身数据库产品设计开发了遵循SQL标准的结构化查询语言。3.1.2T-SQL语言SQL（StructuredQ53.1.2T-SQL语言

54321

具有极强的数据操作功能

高度非过程化

操作面向集合

语法结构高度统一

语言简洁，易学易用3.1.2T-SQL语言54321具有极强的数据操作6CreateTable…创建表AlterTable…修改表DropTable…删除表Select…from…查询表Table表3.1.2T-SQL语言

CreateTable…创建表AlterTable…7在SQL语言中，默认如下的符号约定：1）<>

中的内容是必须的，是用户自定义语义；2）[]为任选项；3）{}与分隔符|组合使用，即必选其中之一项；4）[,…n]表示前面的项目可以重复多次；5）SQL语言不区分大小写字母；6）SQL语句中的标点均采用西文输入法。3.1.2T-SQL语言在SQL语言中，默认如下的符号约定：3.1.2T-SQL8数据库T-SQL使用数据定义语言(DataDefinedLanguage，DDL)完成对数据库对象的创建、修改和删除。数据库对象包括数据库、表、视图、索引、触发器等。3.2数据定义表视图索引

Create语句：创建新的数据库对象

Alter语句：修改已有对象的结构

Drop语句：删除已有的数据库对象数据库T-SQL使用数据定义语言(DataDefined91.创建数据库CreateDatabase<数据库名称>[on[Primary][<数据文件说明>[,…n]][,<文件组>[,…n]]][Logon[<日志文件说明>[,…n]]]3.2.1操作数据库CreateDatabase：创建数据库的关键字数据库名称：可以是英文或中文，用户自定义1.创建数据库CreateDatabase<数据库10【例3-1】创建“salesystem”数据库，参数取系统默认值CreateDatabasesalesystem必须有空格3.2.1操作数据库必须有空格【例3-1】创建“salesystem”数据库，参数取系统默113.2.1操作数据库CreateDatabasesalesystemOnPrimary

(Name='salesystem

',Filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\salesystem.Mdf',Size=10mb,Maxsize=30mb,Filegrowth=1mb)LogOn(Name='salesystem

_Log',Filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\salesystem

_Log.Ldf',Size=2mb,Maxsize=6mb,Filegrowth=10%)主数据文件日志文件补充内容3.2.1操作数据库CreateDatabasesa122.数据库的修改AlterDatabase<数据库名称>{addfile<增加的文件名>[,…n][TOFILEGROUP文件组名称]|addlogfile<日志文件名称>[,…n]|removefile<删除的逻辑文件名>[withdelete]|modifyfile<修改的文件名>|modifyname=新的数据库名称|addfilegroup文件组名称|removefilegroup文件组名称|modifyfilegroup文件组名称{filegroup_property|name=新文件组名称}}3.2.1操作数据库2.数据库的修改AlterDatabase<数据13【例3-2】将“学生管理”数据库的初始容量修改为100MBAlterDatabase学生管理modifyfile(Name=学生管理,Size=100MB)3.2.1操作数据库初始容量默认为1MB【例3-2】将“学生管理”数据库的初始容量修改为100MB143.数据库的删除DropDatabase<数据库名称>3.2.1操作数据库该命令将删除数据库及其包含的所有数据库对象，因此要慎用。该命令不能删除系统数据库和正在使用的数据库。3.数据库的删除DropDatabase<数据库名15【例3-3】删除“salesystem”和“学生管理”数据库。DropDatabasesalesystemDropDatabase学生管理或者

DropDatabasesalesystem,学生管理

3.2.1操作数据库必须是西文状态下的逗号【例3-3】删除“salesystem”和“学生管理”数据库16

表中的内容2

表的结构13.2.2操作表表表是数据库最重要的对象之一列名称数据类型uidvarchar(20)upasswordvarchar(6)utypevarchar(20)unamevarchar(20)uaddrvarchar(50)utelvarchar(20)uemailvarchar(30)uaccountfloatuid毛毛熊upassword1234utype顾客uname闫弘uaddr北京市西城区鼓楼大街26号uteemailyanhong@uaccount9760表中的内容2表的结构13.2.172.表结构的创建CreateTable<表名>（<列名><数据类型>[完整性约束],<列名><数据类型>[完整性约束]

[,…n]）3.2.2操作表表名，在同一个数据库中，表的名称不允许重复。列名，同一表中不许有重复的列名。[,…n]表示表中可设计n个列，每列定义用逗号隔开。2.表结构的创建CreateTable<表名>3.183.2.2操作表【例3-4】在salesystem数据库中建立一个users(用户信息)表，表结构如下CreateTableusers(uidvarchar(20),upassword varchar(6),utype varchar(20),uname varchar(20),uaddr varchar(50),utel varchar(20),uemail varchar(30),uaccount float

)；列名称数据类型说明字段大小uid字符用户ID最大20upassword字符用户密码最长6utype字符用户类型最长20uname字符收货人姓名最长20uaddr字符收货人地址最长50utel字符收货人电话最长20uemail字符收货人电子邮箱最长30uaccount数字用户帐户余额3.2.2操作表【例3-4】在salesystem数据库中19注意：1）单个列定义语句的关键字之间用空格分隔，不使用标点。2）定义表时每列之间用逗号分隔，最后一条语句不用逗号。3）表名之后的所有列要用圆括弧括起来。4）SQL语句以分号结束或者无任何符号。5）SQL语句不区分大小写，标点均使用西文输入法录入。3.2.2操作表CreateTableusers(uidvarchar(20),upassword varchar(6),……uaccount float

)逗号分隔最后一列没有逗号注意：3.2.2操作表CreateTableusers20数据类型名称取值范围Char定长字符型0~8000个字符Varchar变长字符型0~8000个字符Int整型-231~231-1Float浮点型-1.79×10308~1.79×10308Text文本型0~231-1个字符Datetime日期时间型1753/1/1~9999/12/31，时间精度是1/300秒3.2.2操作表T-SQL中常用的数据类型（P42详见表3-4）数据类型名称取值范围Char定长字符型0~8000个字符Va213.2.2操作表定义完整性约束，可以防止非法的数据更新操作，保护数据。例如：要求必须填写、要求输入的数据在某个范围内等。完整性约束的基本语法格式为：列名数据类型字段大小uidvarchar(20)3.2.2操作表定义完整性约束，可以防止非法的数据更新操作223.2.2操作表PrimaryKey

约束（主键约束）1Unique

约束（唯一键约束）2ForeignKey

约束（外键约束）3Null/NotNull

约束（空/非空约束）4Check约束（检查约束）53.2.2操作表PrimaryKey约束（主键约束）123【例3-4】在salesystem数据库中建立一个users(用户信息)表，注意主键约束和非空约束的设置。

CreateTableusers( uid varchar(20)primarykey, upassword varchar(6) notnull, …… uaccount float notnull )3.2.2操作表PrimaryKey定义表的主键，起唯一标识作用，其值不能为空，也不能出现重复，以此来保证实体的完整性。一旦将某列设置为主键，系统会自动为其增加非空约束。一个表中只能定义一个PrimaryKey约束。【例3-4】在salesystem数据库中建立一个users24【例3-5】在salesystem数据库中建立一个product(商品信息)表。CreateTableproduct( pid char(10) primarykey, pname varchar(30) notnull,…… profile text, picture varchar(100))3.2.2操作表没有约束，允许为空若为表中的一列同时设定两种约束，格式为：pname varchar(30) notnullunique,使用空格分开【例3-5】在salesystem数据库中建立一个produ25PrimaryKey与Unique区别：在一个表中只能定义一个PrimaryKey约束，但可定义多个Unique约束；对于指定为PrimaryKey的列不能出现空值，而对于Unique所约束的唯一键，允许有一个数据项为空值。不允许为一列既定义Unique约束，又定义PrimaryKey约束。3.2.2操作表PrimaryKey与Unique区别：3.2.2操作表26【例3-6】在salesystem数据库中建立一个orders(订单)表，注意外键约束的设置。

CreateTableorders(oid char(10) primarykey,uid varchar(20) referencesusers(uid)notnull,pid char(10) referencesproduct(pid)notnull,……)3.2.2操作表外键约束【例3-6】在salesystem数据库中建立一个order2710000000017樱桃000000000910000000027小鱼菁菁000000000710000000006毛毛熊00000000040000000001test0000000001PamountPidUidOid1李洋顾客test樱桃北京市朝阳区黄贺顾客test小鱼菁菁北京市西城区闫弘顾客test毛毛熊北京市昌平区王燕顾客testtest管理员adminadminUaddrUnameUtypeUpasswordUid3.2.2操作表外键约束：包含外键的表称为从表，将外键作为主键的表称为主表。users表(主表)orsers表(从表)外键主键10000000017樱桃00000000091000000283.2.2操作表users表的主键product表的主键orders表的外键pid必须是product表中已有的pidorders表的外键uid必须是users表中已有的uid主表主表从表orders表的外键3.2.2操作表users表的主键product表的主键o293.表结构的修改AlterTable表名{Add<新列名><数据类型>[完整性约束]|DropColumn<列名>[,…n] |Drop[Constraint]<约束名>[,…n] }3.2.2操作表

<表名>指定需要修改的表；

Add子句用于增加新列和新的完整性约束条件；

Drop子句用于删除指定的列和完整性约束条件。3.表结构的修改AlterTable表名3.2.230【例3-7】向users(用户信息)表中增加一个表示性别的sex列。

AlterTableusersAddsexchar(2)Null3.2.2操作表

注意：使用此方式增加的新列一律为空值，所以不能为增加的新列指定NotNull约束。

【例3-7】向users(用户信息)表中增加一个表示性别的31【例3-8】删除users(用户信息)表中存储用户电子邮箱的uemail列。

AlterTableusersDrop

Columnuemail3.2.2操作表请不要漏掉【例3-8】删除users(用户信息)表中存储用户电子邮箱323.2.2操作表4.表结构的删除一旦删除了表，表的结构、数据、约束、索引都将被删除，而建立在该表上的视图不会随之删除，系统将继续保留其定义，但已无法使用。删除表的命令格式为：3.2.2操作表4.表结构的删除333.2.2操作表【例3-9】删除orders(订单)表。

DropTableorders主表主表从表【提示】对于“网上购物系统”所使用的三张表，如果都想删除，存在删除顺序的问题，应该先删除从表orders表，解除外键约束后，再删除两张主表。3.2.2操作表【例3-9】删除orders(订单)表。343.2.3索引的创建与删除索引

（Index）①索引是数据库随机检索的常用手段，类似书的目录。②索引的主要用途是提供一种无须扫描每一页而快速访问数据库表中特定信息的手段。③在实际的数据库中，所有的查询在有索引和无索引的情况下都是可以工作的，索引仅仅与查询的速度有关，故建立索引的作用就是加快查询速度。3.2.3索引的创建与删除索引（Index）353.2.3索引的创建与删除创建索引的格式为：

索引的顺序可以是Asc(升序)或Desc(降序)，默认升序。当索引建立在多列上时，首先按第一列排序，第一列相同再考虑第二列，以此类推。Unique表示每一个索引值对应唯一的数据记录。3.2.3索引的创建与删除创建索引的格式为：索引的顺序可363.2.3索引的创建与删除【例3-10】在users(用户信息)表的uaccount(用户账户余额)上建立降序索引，名为users_uaccountindex。

CreateIndexusers_uaccountindexOnusers(uaccountDesc)

索引名表名列名降序3.2.3索引的创建与删除【例3-10】在users(用户373.2.3索引的创建与删除删除索引的格式为：【例3-11】删除上一例题创建的索引users_uaccountindex

DropIndexusers_uaccountindexOnusers索引名表名3.2.3索引的创建与删除删除索引的格式为：【例3-11】383.3数据查询SQL语言提供了Select语句进行数据库的查询，其基本命令格式为：Select[All|Distinct]<目标列表达式>[,<目标列表达式>]From<表名或查询名>[,<表名或查询名>][Where<条件表达式>][GroupBy<列名1>[Having<条件表达式>]][OrderBy<列名2>[Asc|Desc]]3.3数据查询SQL语言提供了Select语句进行数据393.3数据查询Select ——选择From ——从…Where ——当满足…条件时GroupBy ——按…分组OrderBy ——按…排序Distinct ——互不相同的Having ——组当种满足…条件时Asc/Desc ——升序/降序关键字3.3数据查询Select ——选择关403.3.1单表查询

单表查询是指仅使用一个表的查询，一般只用到Select、From和Where，单表查询也称简单查询。1．Select子句

【例3-12】查询users表中所有列的数据

Select*Fromusers查询结果:全表查询3.3.1单表查询单表查询是指仅使用一个表的查询413.3.1单表查询【例3-13】查询

users表中用户ID、用户类型、收货人姓名和收货人地址。Selectuid,utype,uname,uaddrFromusers查询结果:顺序决定查询结果显示方式3.3.1单表查询【例3-13】查询users表中用423.3.1单表查询【例3-14】返回orders表前5条记录的订单ID、用户ID和送货方式。SelectTop5oid,uid,deliverFromorders查询结果:Top53.3.1单表查询【例3-14】返回orders表前5条433.3.1单表查询【例3-15】返回product表前10%的商品信息。SelectTop10percent*Fromproduct查询结果:3条记录30条记录3.3.1单表查询【例3-15】返回product表前1443.3.1单表查询【例3-16】查询product表中具体包含哪些类别的商品。SelectDistinctptypeFromproduct查询结果:4类商品30条记录3.3.1单表查询【例3-16】查询product表453.3.1单表查询【例3-17】完成同【例3-16】一样查询，并将返回的列命名为“商品分类”。SelectDistinctptypeAs商品分类Fromproduct查询结果:查询结果:3.3.1单表查询【例3-17】完成同【例3-16】一样46

2．From子句

From子句指定Select语句查询及与查询相关的表或视图。在From子句中最多可指定256个表或视图，它们之间用逗号分隔，在单表查询中From子句后只有一个表名。3．Where子句

Where子句设置查询条件，过滤掉不需要的数据行，只有满足条件的行才能出现在查询结果中。3.3.1单表查询2．From子句3.3.1单表查询473.3.1单表查询【例3-18】查询users表中用户ID为“樱桃”的顾客的收货人姓名及收货人地址。SelectuidAs用户ID,unameAs收货人姓名,uaddrAs收货人地址FromusersWhereuid='樱桃'查询结果:文本型数据加单引号3.3.1单表查询【例3-18】查询users表中用户I483.3.1单表查询【例3-19】查询users表中用户账户余额超过9000的顾客信息。Select*FromusersWhere

uaccount>9000

浮点型数据不加单引号查询结果:3.3.1单表查询【例3-19】查询users表中用户账49查询结果:3.3.1单表查询【例3-20】查询orders表中在2009-7-23后产生的订单信息。Select*FromordersWhere

otime>'2009-7-23'日期时间数据加单引号>表示在某时间之后查询结果:3.3.1单表查询【例3-20】查询order503.3.1单表查询查询条件运算符含义比较大小＝、>、<、>=、<=、!=、!>、!<进行数值和字符串的比较确定范围Between…And…数据范围在And连接的两个数值之间确定集合In、NotIn检查一个属性值是否属于集合中的值字符匹配Like、NotLike用于构造条件表达中的字符匹配空集IsNull、IsNotNull用于判断属性值是否为空多重条件And、Or用于构造复合表达式比较运算符及其含义3.3.1单表查询查询条件运算符含义比较大小＝、>、<513.3.1单表查询【例3-21】查询product表中商品价格高于200的数码产品类商品，返回商品ID、商品名称、商品分类及商品价格信息。Selectpid,pname,ptype,priceFromproductWhereprice>200and

ptype='数码产品'查询结果:使用逻辑运算符的查询（And，Or，Not）3.3.1单表查询【例3-21】查询product表中商523.3.1单表查询【例3-22】查询product表中商品价格低于25的日用百货类或者图书类商品，返回商品ID、商品名称、商品分类及商品价格信息。Selectpid,pname,ptype,priceFromproductWhereprice<25and(ptype='日用百货'orptype='图书')增加程序的可读性3.3.1单表查询【例3-22】查询product表中商533.3.1单表查询查询结果:【思考】如果去掉Where子句中的小括号，查询结果是否会发生改变呢？Selectpid,pname,ptype,priceFromproductWhereprice<25and(ptype='日用百货'orptype='图书')10条记录3.3.1单表查询查询结果:【思考】如果去掉Where子543.3.1单表查询查询结果:Selectpid,pname,ptype,priceFromproductWhereprice<25andptype='日用百货'orptype='图书'12条记录3.3.1单表查询查询结果:Selectpid,p553.3.1单表查询限定范围的查询（Between…And…）【例3-23】查询product表中库存量在0~20范围内的商品，返回商品ID、商品名称、商品分类及库存量信息。Selectpid,pname,ptype,stockFromproductWherestockbetween0and20stock>=0andstock<=203.3.1单表查询限定范围的查询（Between…563.3.1单表查询确定集合的查询（In）【例3-24】查询product表中商品类型为日用百货或图书类的商品，返回商品ID、商品名称、商品分类及商品价格。Selectpid,pname,ptype,priceFromproductWhereptypein('日用百货','图书')ptype='日用百货'orptype='图书'3.3.1单表查询确定集合的查询（In）ptype573.3.1单表查询

模糊查询（Like）【例3-25】查询product表中商品名称中含有“设计”字样的图书类商品，返回商品ID、商品名称、商品分类及商品价格。Selectpid,pname,ptype,priceFromproductWhereptype='图书'andpnamelike'%设计%'查询结果:3.3.1单表查询模糊查询（Like）查询结果:583.3.1单表查询模糊查询（Like）当不清楚要查找信息的精确值时，通常采用Like关键字进行模糊查找。在SQLServer中常用的通配符是%——百分号，代表任意多个字符_——下划线，代表单个字符【思考】以下条件有什么差别Wherepnamelike'%设计%'Wherepnamelike'设计%'Wherepnamelike'%设计'3.3.1单表查询模糊查询（Like）【思考】以下条件有593.3.1单表查询

【例3-26】查询product表中商品名称是以“服装”字样开头的图书类商品，返回商品ID、商品名称、商品分类及商品价格。Selectpid,pname,ptype,priceFromproductWhereptype='图书'andpnamelike'服装%'查询结果:3.3.1单表查询【例3-26】查询product表中603.3.1单表查询与空值相关的查询（Null）数据库表中，如果某一列中没有输入数据，则它的值就为空，空值用一个特殊的数据Null来表示。如果要判断某一列是否为空，不能用“=Null”或者“<>Null”，只能用“isNull”或“isnotNull”来表达。3.3.1单表查询与空值相关的查询（Null）613.3.1单表查询

【例3-27】查询product表中哪些商品填写了商品简介信息，返回商品ID、商品名称、商品分类及商品简介。Selectpid,pname,ptype,profileFromproductWhereprofileisnotNull查询结果:3.3.1单表查询【例3-27】查询product表中623.3.1单表查询使用聚合函数的查询（Count,Sum,Avg,Max,Min）聚合函数可以对一组数据执行某种计算并返回结果，常用的聚合函数包括：Count：返回一组值中项目的数量

Sum：返回一组值的和

Avg：返回一组值的平均值

Max：返回一组值中的最大值

Min：返回一组值中的最小值3.3.1单表查询使用聚合函数的查询（Count,Sum633.3.1单表查询

【例3-28】查询product表中共有多少款商品。Selectcount(*)As商品数量Fromproduct查询结果:统计结果3.3.1单表查询【例3-28】查询product表中643.3.1单表查询

【例3-29】查询product表中图书类商品的平均价格是多少。SelectAvg(price)As图书类平均价格FromproductWhereptype='图书'查询结果:3.3.1单表查询【例3-29】查询product表中653.3.1单表查询4.GroupBy子句

GroupBy子句可以将查询结果按一列或多列的数据值进行分组。分组的目的是为了细化聚合函数的作用对象，起到分类汇总的作用。

如果在分组后还对查询结果进行筛选，则需要使用Having子句。3.3.1单表查询4.GroupBy子句663.3.1单表查询【例3-30】查询product表中服装服饰类、日用百货类、数码产品类及图书类各有几款商品。Selectcount(*)As商品数量FromproductWhere

ptype='服装服饰'查询结果:SelectptypeAs商品类型,count(*)As商品数量FromproductGroupbyptype4类商品同时统计3.3.1单表查询【例3-30】查询product表中服673.3.1单表查询【例3-31】查询product表中商品数量低于7种的商品类型。SelectptypeAs商品类型,count(*)As商品数量FromproductGroupbyptypeHavingcount(*)<7查询结果:不能再使用Where3.3.1单表查询【例3-31】查询product表中商683.3.1单表查询5.OrderBy子句如果没有指定查询结果的显示顺序，系统将按记录在表中的先后顺序输出查询结果。Select*fromproduct

用户可以用OrderBy子句指定按照一个或多列的升序（Asc）或降序（Desc）重新排列查询结果，默认为升序。Select*fromproductorderbyprice默认升序3.3.1单表查询5.OrderBy子句默认升序693.3.1单表查询【例3-32】查询product表中图书类商品的商品名称、商品分类、商品价格及库存量，并将查询结果按照商品价格降序排列。Selectpname,ptype,price,stockFromproductWhereptype='图书'OrderBypriceDesc查询结果:OrderBy3Desc3.3.1单表查询【例3-32】查询product表中图703.3.1单表查询【例3-33】查询product表中图书类商品的商品名称、商品分类、库存量及商品价格，并将查询结果按照库存量降序排列，库存量相同的再按价格降序排列。Selectpname,ptype,stock,priceFromproductWhereptype='图书'OrderBystockDesc,priceDesc查询结果:3.3.1单表查询【例3-33】查询product表中图7110000000017樱桃000000000910000000027小鱼菁菁000000000710000000006毛毛熊00000000040000000001test0000000001PamountPidUidOid1orders表3.3.2连接查询

涉及两个以上表的查询称之为联结查询。【例3-34】查询orders表中订单ID为“0000000004”的订单，订单的收货人姓名、收货人地址及收货人电话。李洋顾客test樱桃北京市朝阳区黄贺顾客test小鱼菁菁北京市西城区闫弘顾客test毛毛熊北京市昌平区王燕顾客testtest管理员adminadminUaddrUnameUtypeUpasswordUidusers表10000000017樱桃0000000009100000072Selectorders.oid,users.uid,users.uname,

users.uaddr,users.utelFromorders,usersWhere(orders.uid=users.uid)And(orders.oid='0000000004')3.3.2连接查询

注意：uid同时属于两个不同的表，所以在引用时前面必须加上各自的表名，如orders.uid。2张表查询结果:Selectorders.oid,users.uid,73

×=60条Selectorders.oid,orders.uid,users.uid,users.uname,users.uaddrFromorders,users3.3.2连接查询

12条5条×=60条Se74Selectorders.oid,orders.uid,users.uid,users.uname,users.uaddrFromorders,usersWhere(orders.uid=users.uid)3.3.2连接查询

60条12条Selectorders.oid,orders.uid75Selectorders.oid,orders.uid,users.uid,users.uname,users.uaddrFromorders,usersWhere(orders.uid=users.uid)And(orders.oid='0000000004')3.3.2连接查询

12条1条Selectorders.oid,orders.uid76product表20图书长袜子皮皮00000000305020图书藏地密码00000000291945图书时装设计元素00000000281940图书服装设计视觉词典0000000027图书杜拉拉升职记0000000026StockPricePtypePnamePid18504810000000017樱桃000000000910000000027小鱼菁菁000000000710000000006毛毛熊00000000040000000001test0000000001PamountPidUidOid1orders表3.3.2连接查询

【例3-35】查询orders表中订单ID为“0000000007”的订单，订单所购商品名称、商品价格及库存量。product表20图书长袜子皮皮000000003050277Selectorders.oid,product.pname,product.price,

product.stockFromorders,productWhere(orders.pid=product.pid)And(orders.oid='0000000007')3.3.2连接查询

连接条件不要漏掉查询结果:Selectorders.oid,product.pn783.3.2连接查询

【例3-36】查询orders表中每张订单的订单ID，商品名称，商品价格，收货人姓名，收货人地址信息。(orders.pid=product.pid)And(orders.uid=users.uid)3.3.2连接查询【例3-36】查询orders表中79Selectorders.oid,product.pname,product.price,users.uname,users.uaddrFromorders,product,usersWhere(orders.pid=product.pid)And(orders.uid=users.uid)3.3.2连接查询

连接条件不要漏掉查询结果:Selectorders.oid,3.3.2连接803.3.3嵌套查询

在一个Select语句中嵌入另一个完整的Select语句称为嵌套查询。

嵌入的Select语句称为子查询，而包含子查询的Select语句称为外部查询。

子查询还可以再嵌套子查询，T-SQL对于嵌套的层数没有限制。3.3.3嵌套查询在一个Select语句中嵌入另一个完8110000000017樱桃000000000910000000027小鱼菁菁000000000710000000006毛毛熊00000000040000000001test0000000001PamountPidUidOid1orders表3.3.3嵌套查询

【例3-37】使用嵌套查询方式获得orders表中订单ID为“0000000004”的订单，订单的收货人姓名、收货人地址及收货人电话。李洋顾客test樱桃北京市朝阳区黄贺顾客test小鱼菁菁北京市西城区闫弘顾客test毛毛熊北京市昌平区王燕顾客testtest管理员adminadminUaddrUnameUtypeUpasswordUidusers表10000000017樱桃0000000009100000082SelectuidFromordersWhereoid='0000000004'【例3-37】嵌套查询方式Selectuname,uaddr,utelFromusersWhereuidIn(

)3.3.3嵌套查询【例3-34】连接查询方式Selectorders.oid,users.uid,users.uname,users.uaddr,users.utelFromorders,usersWhere(orders.uid=users.uid)And(orders.oid='0000000004')先做子查询Selectuid【例3-37】嵌套查询方式3.3.83【例3-38】查询网上购物系统中一直未被订购过的商品ID，商品名称及库存量。Select

pidAs商品ID,pnameAs商品名称,stockAs库存量FromproductWherepidnotin(Selectpidfromorders)3.3.3嵌套查询先做子查询30–12=18条【例3-38】查询网上购物系统中一直未被订购过的商品ID，商84【例3-39】查询图书类商品中价格高于同类商品平均价格的商品名称，商品价格及库存量。Select

pnameAs商品名称,priceAs商品价格,stockAs库存量FromproductWhereptype='图书'andprice>3.3.3嵌套查询先做子查询(Select Avg(price)From productWhereptype='图书')子查询结果：【例3-39】查询图书类商品中价格高于同类商品平均价格的商品853.3.4联合查询使用Union子句的查询称为联合查询。连接查询是匹配两个表中的列，而联合查询是组合两个表中的行。联合查询中，系统会自动屏蔽重复的行，同时还要求参加Union操作的结果集具有相同的列数，对应列的数据类型也相互兼容。3.3.4联合查询使用Union子句的查询称为联合查询86【例3-40】将users表中的用户ID、收货人姓名、用户账户余额和product表中的商品ID、商品名称、商品价格组成一组数据。Selectuid,uname,uaccountfromusersUnionSelectpid,pname,pricefromproduct3.3.4联合查询查询结果:结果集列数相同，数据类型兼容users表product表【例3-40】将users表中的用户ID、收货人姓名、用户账87联合查询输出的结果集遵循什么样的显示顺序。下面的T-SQL语句执行结果是什么？Selectuname,uaccountfromusersUnionSelectpname,pricefromproduct3.3.4联合查询

联合查询输出的结果集遵循什么样的显示顺序。下面的T-SQL语883.4更新数据插入InsertInto…Values修改Update…Set删除Delete…From3.4更新数据插入InsertInto…Value893.4.1插入数据【例3-41】向product表中插入下面两条新的商品信息。InsertintoproductValues('0000000031','佳能IXUS95','数码产品',1500,30,0,'本周销量冠军','数码产品\佳能IXUS95.jpg')InsertintoproductValues('0000000032','数据库原理与应用','图书',21,20,0,Null,Null)内容为空不加单引号3.4.1插入数据【例3-41】向product表中插入903.4.1插入数据批量插入数据【例3-42】创建新表product_1，其列来product表的商品ID、商品名称、商品价格3列。

Select pid,pname,price

Into product_1

From product

IntoProduct_1表3.4.1插入数据批量插入数据IntoProduct_913.4.2修改数据T-SQL语言提供了Update语句，用于修改表中数据。

Update<表名>

Set<列名>=<表达式>[,<列名>=<表达式>]...[Where<条件>]表名指要修改的表的名称。Set子句指出要修改的列及其修改后的值。Where子句指定待修改的记录应当满足的条件，Where子句省略时，则修改表中的所有记录。3.4.2修改数据T-SQL语言提供了Update语句，用923.4.2修改数据1．修改某一个元组的值【例3-43】修改product表中商品ID为“0000000001”的商品价格为170，库存量为10。Update productSet price=170,stock=10Where pid='0000000001'逗号3.4.2修改数据1．修改某一个元组的值逗号934.4.2修改数据2．修改多个元组的值【例3-44】修改product表中图书类商品的价格改为原来的90%。Update productSet price=price*0.9Where ptype='图书'赋值表达式操作前后4.4.2修改数据2．修改多个元组的值赋值表达式操作前后943.4.2修改数据3．带子查询的修改语句【例3-45】修改product表中商品价格比同类商品均价高的图书类商品，价格改为原来的80%。Update productSet price=price*0.8Where ptype='图书'and

price>(SelectAvg(price)fromproductwhereptype='图书')3.4.2修改数据3．带子查询的修改语句953.4.3删除数据

当表中的某条记录或多条记录不再需要时，就要删除这些多余的记录，以免影响查询速度。

Delete

From<表名> [Where<条件>];表名指要删除数据的表。where子句指定待删除记录应当满足的条件，Where子句省略时，则删除表中的所有记录。Delete语句只是删除记录，但对表的结构没有任何影响，注意和DropTable命令加以区分。3.4.3删除数据当表中的某条记录或多条记录不再需要时963.4.3删除数据1．删除某一个元组的值【例3-46】删除orders表中订单ID为“0000000003”的订单。Delete From ordersWhere oid='0000000003'2．删除多个元组的值【例3-47】删除orders表中所有的订单记录。

DeleteFromorders;

执行此语句后，orders表即为一个空表，但其定义仍然存在数据库中，与Droptableorders不同。3.4.3删除数据1．删除某一个元组的值973.5视图的创建和删除视图是从一个或几个基本表中导出的表。视图是一种虚表，不是物理存在的表。基本表中的数据发生变化，从视图查询出的数据也随之改变。视图可以屏蔽数据来源表中的某些信息，有利于数据库的安全性。视图在数据库里是作为查询来保存的，因此执行视图就是执行查询。视图拥有表的几乎所有操作，通过对视图数据的修改可转换为对基本表数据的修改。删除视图后，表和视图所基于的数据并不受到影响。视图表3.5视图的创建和删除视图是从一个或几个基本表中导出的表983.5.2创建视图视图实际上是根据对基本表的查询需求来定义的。使用T-SQL创建视图的命令格式为：CreateView视图名

[(<列名1>[,…n])][WithEncryption]AsSelect语句[WithCheckOption]3.5.2创建视图视图实际上是根据对基本表的查询需求来定993.5.2创建视图【例3-48】建立包含顾客类用户的用户ID、收货人姓名、收货人地址的视图。CreateViewusers_viewAsSelectuid,uname,uaddrFromusersWhereutype='顾客'查询视图users_view屏蔽了需要保密的信息3.5.2创建视图【例3-48】建立包含顾客类用户的用户1003.5.2创建视图【例3-49】创建一个视图，查询每张订单的订单ID，商品名称，商品价格，收货人姓名，收货人地址信息。CreateVieworders_detailAsSelectorders.oid,product.pname,product.price,users.uname,users.uaddrFromorders,product,usersWhere(orders.pid=product.pid)And(orders.uid=users.uid)3.5.2创建视图【例3-49】创建一个视图，查询每张订1013.5.3查询视图【例3-50】查询【例3-49】中创建的视图orders_detail。Select*fromorders_detail3.5.3查询视图【例3-50】查询【例3-49】中创建1023.5.4更新视图更新视图是指通过视图来更新基本表中的数据。【例3-51】修改【例3-49】创建的视图orders_detail，将订单ID为“0000000001”的商品价格调整为120。Updateorders_detailSetprice=120Whereoid='0000000001'视图名操作前后3.5.4更新视图更新视图是指通过视图来更新基本表中的数1033.5.5删除视图当不再需要某个视图时，可以将其删除，删除视图后，创建视图使用的基本表及数据并不受到影响。DropView视图名[,…n]使用该语句可以一次删除多个视图

DropViewusers_view,orders_detail3.5.5删除视图当不再需要某个视图时，可以将其删除，删104第3章关系数据库标准语言SQL数据库基础第3章关系数据库标准语言SQL数据库基础105应用软件用户级别操作最简单功能受约束-需购买软件企业管理器管理员级别-操作简单-功能较全-直接使用DBMSSQL语言高级管理员、程序员级别-操作较复杂-使用最灵活，效率最高直接使用DBMS可用于开发应用软件第3章关系数据库标准语言SQL应用软件用户级别企业管理员级别SQL语言高级管理员、程序员级106本章主要内容3.1SQL语言概述（了解）3.2数据定义（重点）3.2.1操作数据库3.2.2操作表3.2.3索引的创建和删除3.3数据查询（重点）3.3.1单表查询3.3.2连接查询3.3.3嵌套查询3.3.4联合查询3.4数据更新（重点）3.4.1插入数据3.4.2修改数据3.4.3删除数据3.5视图的创建和删除(了解)第3章关系数据库标准语言SQL本章主要内容3.4数据更新（重点）第3章关系数据库标准1073.1.1SQL语言的发展

SQL语言是当前最为成功，应用最为广泛的关系数据库语言，其发展主要经历了以下几个阶段：1972年1974年1987年SQUARE阶段SEQUEL阶段SQL阶段

现在SQL语言应用于Oracle,Sybase,Access和SQLServer等。3.1.1SQL语言的发展SQL语言是当前最为成功，1083.1.2T-SQL语言SQL（StructuredQueryLanguage，结构化查询语言）包括数据定义、数据查询、数据更新、数据控制等功能。Access中使用的SQL语言称为J-SQL。SQLServer中使用的SQL语言称为T-SQL。Microsoft针对其自身数据库产品设计开发了遵循SQL标准的结构化查询语言。3.1.2T-SQL语言SQL（StructuredQ1093.1.2T-SQL语言

54321

具有极强的数据操作功能

高度非过程化

操作面向集合

语法结构高度统一

语言简洁，易学易用3.1.2T-SQL语言54321具有极强的数据操作110CreateTable…创建表AlterTable…修改表DropTable…删除表Select…from…查询表Table表3.1.2T-SQL语言

CreateTable…创建表AlterTable…111在SQL语言中，默认如下的符号约定：1）<>

中的内容是必须的，是用户自定义语义；2）[]为任选项；3）{}与分隔符|组合使用，即必选其中之一项；4）[,…n]表示前面的项目可以重复多次；5）SQL语言不区分大小写字母；6）SQL语句中的标点均采用西文输入法。3.1.2T-SQL语言在SQL语言中，默认如下的符号约定：3.1.2T-SQL112数据库T-SQL使用数据定义语言(DataDefinedLanguage，DDL)完成对数据库对象的创建、修改和删除。数据库对象包括数据库、表、视图、索引、触发器等。3.2数据定义表视图索引

Create语句：创建新的数据库对象

Alter语句：修改已有对象的结构

Drop语句：删除已有的数据库对象数据库T-SQL使用数据定义语言(DataDefined1131.创建数据库CreateDatabase<数据库名称>[on[Primary][<数据文件说明>[,…n]][,<文件组>[,…n]]][Logon[<日志文件说明>[,…n]]]3.2.1操作数据库CreateDatabase：创建数据库的关键字数据库名称：可以是英文或中文，用户自定义1.创建数据库CreateDatabase<数据库114【例3-1】创建“salesystem”数据库，参数取系统默认值CreateDatabasesalesystem必须有空格3.2.1操作数据库必须有空格【例3-1】创建“salesystem”数据库，参数取系统默1153.2.1操作数据库CreateDatabasesalesystemOnPrimary

(Name='salesystem

',Filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\salesystem.Mdf',Size=10mb,Maxsize=30mb,Filegrowth=1mb)LogOn(Name='salesystem

_Log',Filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\salesystem

_Log.Ldf',Size=2mb,Maxsize=6mb,Filegrowth=10%)主数据文件日志文件补充内容3.2.1操作数据库CreateDatabasesa1162.数据库的修改AlterDatabase<数据库名称>{addfile<增加的文件名>[,…n][TOFILEGROUP文件组名称]|addlogfile<日志文件名称>[,…n]|removefile<删除的逻辑文件名>[withdelete]|modifyfile<修改的文件名>|modifyname=新的数据库名称|addfilegroup文件组名称|removefilegroup文件组名称|modifyfilegroup文件组名称{filegroup_property|name=新文件组名称}}3.2.1操作数据库2.数据库的修改AlterDatabase<数据117【