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

四汽车服务企业信息化管理基础四关系数据库语言SQL2023/3/22SQL是结构化查询语言（StructuredQueryLanguage）的缩写，ANSI在1986SQL作为关系数据库系统的标准语言，后被国际标准化组织（ISO）采纳为国际标准。现在很多大型数据库都实现了SQL语言。SQL概述2023/3/23SQL和SQLSERVER的区别SQL（structuredquerylanguage)结构化查询语言。它是一种标准，不是一种软件SQLSERVER是数据库管理系统的一种它是一种软件，这种软件在遵循SQL这种标准，很多数据库管理软件及开发工具都支持SQL这种标准。2023/3/24SQL语言的特点SQL语言的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用2023/3/252023/3/26SQL语句的书写准则对大小写不敏感一条语句可以写成一行或多行关键字不能在行与行之间分开数据项例如属性项、表、视图项等同时列出时，分隔符用逗号；字符或字符串常量要用单引号定界。2023/3/27基本操作对象基本表：本身独立存在的表，SQL中一个关系就对应一个表；一个表对应一个存储文件；一个表可以带若干索引。视图：从一个或几个基本表中导出的表，本身不独立存放在数据库中，数据库只存储视图的定义，视图相当于一个虚表，用户可以在视图上在定义视图。索引：为快速访问数据，而在包含数据的表中增加的一种组织，分为聚簇索引和非聚簇索引。

指索引项的顺序与表中记录的物理顺序一致的索引组织例如：人事档案（员工姓名、地址、号码、社会安全号、工资）其中只有姓名、地址、号码是公开信息。可以生成不包含社会安全号和工资的视图，对表和视图设置不同的权限2023/3/284.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使用系统内置函数的查询2023/3/29SQL的核心是数据查询。对于数据库的查询操作是通过SELECT查询命令实现的，它的基本形式由SELECT－FROM－WHERE查询块组成，多个查询块可以嵌套执行。2023/3/210语句格式SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM

<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

指定要显示的属性列指定查询对象指定查询条件查询结果按指定列的值分组筛选出只有满足指定条件的组对查询结果表按指列值的升序或降序排序2023/3/2114.1.1基本查询1、简单的无条件查询例：Select*fromstu_info

把stu_info表中的所有记录的所有属性查询出来2023/3/212SELECT*FROMSTU_INFOWHEREXSH=’06’4.1.1基本查询2、简单的条件查询例4.2：从edu_d的数据库表stu_info中查找包含所有列的土建学院（学院代号xsh为06）的学生名单。“*”表示查询所有列字符或字符串用单引号引起来相当于选择运算2023/3/213例4.3：从数据库EDU_INFO中查询土建学院（XSH=’06’）的学生的学号、姓名、性别、学院代号信息。SELECTXH,XM,XBM,XSHFROMSTU_INFOWHEREXSH=’06’属性名用逗号隔开

3、查询语句中投影运算的实现2023/3/2144.1.2使用列表达式查询指定的列，除了可以使用*或者属性列表以外，还可以使用列表达式。1、计算列值例4.4：在成绩表XK中按满分150分计算学生成绩并显示学号、课程号、教师号

SELECTXH,KCH,JSH,’150成绩’=KSCJ*1.50FROMXK利用列表达式实现不同列的连接例4.5SELECT'学号',XH,XM+XSH,BHFROMSTU_INFOWHEREXBM=‘男’ORDERBYXHDESC在SQLServer2000系统的企业管理器中运行后系统自动将语句的写法改成了：SELECT'学号'ASEXPR1,XH,XM+XSHASEXPR2,BHFROMSTU_INFOWHERE(XBM=‘男’)ORDERBYXHDESC2023/3/216注意：更改的是查询结果显示的列标题，这是列的别名，而不是更改了数据库表或视图的列标题。2、修改查询结果的列标题例如：改写例4.5SELECTXHAS学号,XM+XSHAS姓名和学院代码,BHAS班级FROMSTU_INFOWHEREXBM=‘男’ORDERBYXHDESC2023/3/217另一种形式：SELECT学号=XH,姓名和学院代码=XM+XSH,班级=BHFROMSTU_INFOWHEREXBM=‘男’

ORDERBYXHDESC

注意：当自定义的列标题中含有空格时，必须用引号将标题括起来。

2023/3/218采用别名的目的：

数据库表中的列名用英文字母表示更方便，但是为了在查询时，让用户看到的结果更直观，所以可以采用中文别名。2023/3/219查询工作的关键就在通过分析怎样把实际问题中的自然语言描述转化为1）从哪个表中查询即：from后的一项2）要查询哪些列即：select后的一项3)要查询的条件即：where后的一项完成查询工作的三个关键步骤

第3步最复杂4.1.3WHERE从句的进一步使用WHERE从句中，可以使用一个或者多个逻辑表达式限制查询数据的范围。表4-1常用查询条件一览表查询条件谓词比较=,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符确定范围Betweenand,notbetweenand确定集合In,notin字符匹配Like,notlike空值Isnull,isnotnull多重条件And,or2023/3/2211、表达式比较例如：SELECT*FROMXKWHEREKSCJ>602、确定范围例4.6：查询数据库表XK中考试成绩在60-70之间的学号、课程号、成绩

SELECTXH学号,KCH课程号，KSCJ成绩FROMXKWHEREKSCJBETWEEN60AND70

2023/3/2223、确定集合例4.7：查询应电0601、机专0601班的学生的信息

SELECT学号，姓名，班级FROMmingdanWHERE班级IN(’应电0601’,’机专0601’)当与指定的值表中的任何一个匹配时，返回TRUE，否则返回FALSE

2023/3/2234、字符匹配用NOTLIKE和LIKE与通配符%和_搭配，其运算对象可以是CHAR,VARCHAR,TEXT,NTEXT,DATETIME和SMALLDATETIME类型的数据。

表示任意长度的字符串表示人一单个字符例如：1、SELECT*FROMmingdanWHERE班级LIKE‘应%’2、SELECT*FROMmingdanWHERE姓名LIKE‘__红’

2023/3/2245、涉及空值的查询例如：SELECT*FROMmingdanWHERE班级ISNULL6、多重条件查询例如：SELECT*FROMmingdanWHERE班级LIKE‘应%’AND姓名LIKE‘%红’

2023/3/225一般格式：SELECT列名1，列名2，…FROM表名WHERE条件表达式ORDERBY列名1[ASC|DESC][，列名2[ASC|DESC]…]升序（默认）降序4.1.4、设定排序条件2023/3/226例4.4:从数据库EDU_D的表STU_INFO中查询建9809班学生情况并按学号排序。SELECT*FROMSTU_INFOWHEREBH=‘建9809’ORDERBYXHSELECT*FROMSTU_INFOWHEREBH=‘建9809’ORDERBYZYH,XHDESC按多列排序：先按专业升序排列，然后同一专业的记录按学号进行降序排列

注意：（1）ORDERBY子句不改变基本表中行或者列的顺序，只改变查询结果的排列顺序。（2）ORDERBY子句制定排序的列必须出现在SELECT子句的列表达式中。（3）排序是查询语句的最后一步工作，所以要把ORDERBY子句一般放在查询语句的最后。2023/3/228在要查询的属性名前加DISTINCT，以去掉结果中重复的值。例：SELECTDISTINCT班级

FROMmingdan注意：在一个SELECT语句中，DISTINCT只能出现一次，并且DISTINCT必须写在所有列名之前。5、限定重复记录2023/3/2294.1.4数据汇总1、聚合函数表4-2聚合函数一览表

聚合函数含义Count([distinct|all]*)统计元组（记录）个数Count([distinct|all]<列名>)统计一列中不为NULL值的个数Sum([distinct|all]<列名>)求一列值的总合（必须为数值型）Avg([distinct|all]<列名>)求一列值的平均数（必须为数值型）Max([distinct|all]<列名>)求一列值中的最大值Min([distinct|all]<列名>)求一列值中的最小值表示去掉结果中的重复行

指定ALL选项或不指定则表示保留重复行

2023/3/230例4.8：查询学生总数SELECTCOUNT(*)AS学生数FROMmingdan或者SELECTCOUNT(XH)FROMmingdan例4.9：已知XK中高等数学的代号是090101,查询学生选修高等数学的平均成绩

SELECTAVG(KSCJ)AS‘高等数学平均成绩’FROMXKWHEREKCH=‘090101’因为XH是该表的主键，不允许有空值

查询每个班的学生人数？2023/3/231例4.10：查询学生选修高等数学的最高分和最低分SELECTMAX(KSCJ)AS‘高等数学最高分’,MIN(KSCJ)AS‘高等数学最低分’

FROMXKWHEREKCH=‘090101’

注意：在查询中，除COUNT（*）外，所有的聚合函数都不包括取值为空的行。2023/3/2322、GROUPBY子句GROUPBY子句用于对表或视图中数据的查询结果按某一列或多列值分组，值相等的分为一组

2、GROUPBY子句例4.11：在表mingdan中查询各班的学生人数

SELECT班级,COUNT(XH)AS人数FROMmingdanGROUPBY班级

注意：SELECT子句的列表中只能包含在GROUPBY中指出的列或在聚合函数中指定的列。GROUPBY子句用于对表或视图中数据的查询结果按某一列或多列值分组，值相等的分为一组

GROUPBY子句常与聚合函数联合使用，用于针对分组的统计汇总

2023/3/2332、GROUPBY子句举例：从表stu中统计xsh=‘0015’中男生和女生的人数Selectcount(*)fromstuwherexsh=‘0015’groupbyxb还可以按多个字段分组思考:统计每个学院的男生和女生的人数?2023/3/234例4.12：在表STU_INFO上产生一个结果集，包括每个专业的男生、女生人数。SELECTZYH,XBM,COUNT(*)AS人数FROMSTU_INFOGROUPBYZYH,XBM

2023/3/2353、HAVING短语HAVING短语指定组或聚合的条件。只有满足条件的组才被选出来，HAVING通常与GROUPBY子句一起使用。（注意与WHERE子句区分）SELECTXH,COUNT(*)；FROMXK；GROUPBYXH；HAVINGCOUNT(*)>3例4.15查询选修了3门以上课程的学生及选课数：2023/3/236例4.16：只查询选修了3门以上课程的学号前四位是20011的学生学号及选课数：

SELECTXH,COUNT(*)FROMXKWHEREXHLIKE‘2001%’GROUPBYXHHAVINGCOUNT(*)>32023/3/237Having短语指定选择组的条件，只有满足条件的组才被选出来。Where子句从基本表中选择满足条件的元组，having选择满足条件的组2023/3/238若一个查询同时涉及两个或两个以上的表，则称为连接查询。连接查询中用来连接两个关系的条件称为连接条件或连接谓词，一般格式为：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>连接谓词中的列名称为连接字段

4.2.5连接查询例如：xk.xh=stu_info.xh连接查询的from后面跟多个表2023/3/239

连接查询中用来连接两个关系的条件称为连接条件或连接谓词，格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>其中比较运算符主要有：=、>、<、>=、<=、!=

当连接运算符为=时，叫等值连接。使用其它运算符称为非等值连接。连接谓词中的列名称为连接字段。

在SELECT中连接条件放在where后面2023/3/240例：查询每个学生及其选课情况Selectstu_info.*,xk.*Fromstu_info,xkWherestu_info.xh=xk.xh2023/3/241例：查询物理012班每个学生及其选修课程的情况SELECTstu_info.*,xk.*FROMstu_info,xkWHEREstu_info.xh=xk.xhandbh=‘物理012’/*复合条件连接*/2023/3/242例4.18：查询选修了有机化学这门课程的学生的姓名

SELECTXMFROMSTU_INFO,XK,GCOURSEWHEREGCOURSE.KM=‘有机化学’

ANDGCOURSE.KCH=XK.KCHANDXK.XH=STU_INFO.XH说明：（1）连接查询涉及的所有表名都放在FROM子句后面。（2）连接条件放在WHERE子句中。（3）如果属性列名在参加连接的各表中是唯一的，可以省略表名前缀；如果属性列名是两个表共同的属性，则一定要加表名前缀。2023/3/243（4）在书写连接查询时，为了简化，可以为表名取别名，别名应该简单。别名只在本次查询有效。例：（4.18）SELECTXMFROMSTU_INFOS,XKX,GCOURSECWHEREC.KM=‘有机化学’ANDC.KCH=X.KCHANDX.XH=S.XH2023/3/2444.1.6嵌套查询例4.19：在STU_info表中查询选修了课程编号为090201的学生姓名分析：查询所要求的结果出自一个关系（STU_info），但相关的条件却涉及多个关系（STU_info和XK）。SELECTXMFROMSTU_INFOWHERE(XHIN(SELECTXHFROMXKWHEREKCH=‘090201’))一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

子查询2023/3/245说明：（1）这个查询首先执行小括号里的内部查询，得到一个结果集，再判断外部查询的列是否属于这个集合；（2）关键字IN的前面只能有一个列，这个列必须与内部查询结果集里的各个值含义相同，数据类型兼容。SELECTXMFROMSTU_INFOWHERE(XHIN(SELECTXHFROMXKWHEREKCH=‘090201’))结果集2023/3/246例4.20查询与刘玉涛在同一个班学习的学生SELECTXH,XM,BHFROMSTU_INFOWHERE(BHIN())1、带有in谓词的子查询同一个学校的刘玉涛可能重名，而且可能不同班SELECTBHFROMSTU_INFOWHEREXM=‘刘玉涛’2023/3/2472、带有比较运算符的子查询例4.21查询与学号2000029001的学生在同一个班学习的学生，按学号排序。SELECTXH,XM,BHFROMSTU_INFOWHERE(BH=())ORDERBYXH明确内查询结果是唯一值时可以使用=SELECTBHFROMSTU_INFOWHEREXH=‘2000029001’2023/3/248带有比较运算符的子查询举例：从mingdan表中查询成绩最高的同学的学号，姓名和成绩Select学号,姓名,成绩frommingdanWhere成绩=(selectmax(成绩)frommingdan)Select学号,max(成绩)frommingdanSelect学号,max(成绩)frommingdangroupby学号2023/3/2493、带有ANY、SOME、ALL的比较子查询例4.22查询其他班级中比材物2k01班某个学生年龄小的学生的姓名和年龄。SELECTXM,NLFROMSTU_INFOWHERENL<ANY(

)ANDBH<>’材物2k01’同义字SELECTNLFROMSTU_INFOWHEREBH=‘材物2k01’2023/3/2504、带有EXISTS谓词的子查询EXISTS是测试子查询是否有数据行返回，如果有则返回TRUE，否则返回FALSE。NOTEXISTS则相反，当结果表为空时，才返回TRUE。

例4.23查询选修了高等数学（KCH=090101）的学生的姓名等信息

SELECTXM,XBM,BHFROMSTU_INFOWHEREEXISTS(SELECT*FROMXKWHEREXH=STU_INFO.XHANDKCH=‘090101’不需要明确指定列名，这个子查询不一定会执行完，只要发现匹配条件成立，就退出子查询2023/3/251例4.24查询没有选修高等数学的学生的姓名等信息

SELECTXM,XBM,BHFROMSTU_INFOWHERENOTEXISTS(SELECT*FROMK2001WHEREXH=STU_INFO.XHANDKM='高等数学')2023/3/252例：在stu_info表中查询选修了090101号课程的学生姓名SELECTxmFROMstu_infoWHERE(XHIN(SELECTXHFROMxkWHERE(kch=‘090101')))当查询的信息涉及一个关系时，嵌套查询和连接查询可互换Selectxmfromstu_info,xkWherestu_info.xh=xk.xhandkch=‘090101’2023/3/2534.1.7联合查询

多个SELECT语句的结果可进行集合操作，SQLServer的Transact-SQL语言只提供UNION（并）运算符实现并操作。语法格式：SELECT_1UNION[ALL]SELECT_2{[UNION[ALL]SELECT_3]}…进行并运算的子查询2023/3/254例4.25：有两个数据库表STUFR和STUIS，其结构完全一样，分别存放外语学院和信息学院的学生基本信息，现在要查询两个学院女生的学号、姓名。

SELECTXH,XMFROMSTUFRWHEREXB=＇女＇UNIONSELECTXH,XMFROMSTUISWHEREXB=＇女＇

2023/3/255说明：

（1）使用UNION运算符进行联合查询时，要保证各个SELECT语句的目标列表达式数量相等、排列顺序相互一一对应、数据类型必须兼容。（2）UNION之后如果使用了ALL选项，则显示所有的包括重复的行，如果没有使用ALL选项，则重复行只显示一行。（3）UNION操作常用于归档数据，例如归档各个基层部门的数据等，运行时将查询的数据合并到第一个表中。（4）也可以对结果进行排序或者分组汇总，这是必须把ORDERBY子句或GROUPBY子句放在最后一个SELECT语句的后面，并且必须是针对第一个SELECT语句的列进行的排序或分组。

（5）使用UNION连接的所有SELECT语句也可以使用同一张表，此时UNION运算符可以用OR运算符来代替。

2023/3/256例4.26：将上个例子中的两个数据库表STUFR和STUIS的数据合并到结构相同的全校的学生数据表STU中去。SELECT*FROMSTUUNIONALLSELECT*FROMSTUFRUNIONALLSELECT*FROMSTUIS如何将查询的结果保存在当前数据库新建的表NEWSTU中？SELECT*INTONEWSTUFROMSTUFRUNIONALLSELECT*FROMSTUIS2023/3/257例4.27：查询01学院的学生及所有的本科生

SELECT*FROMSTU_INFOWHEREXSH=‘01’

UNIONSELECT*FROMSTU_INFOWHEREPYCCM=‘本科’

改写

SELECT*FROMSTU_INFOWHEREXSH=’01’ORPYCCM=‘本科’

2023/3/2584.1.8使用系统内置函数的查询T-SQL提供了三种系统内置函数：行集函数、聚合函数和标量函数。其中聚合函数在节进行了介绍。

1、数学函数：对SQLServer提供的数字数据（decimal,integer,float,real,money,smallmoney,smallint和tinyint）进行数学运算并返回运算结果。2023/3/259（2）RAND函数语法格式：RAND([seed])功能：返回0～1之间的一个随机值。

（1）ABS函数语法格式：ABS(数值型表达式)功能：返回给定数值表达式的绝对值参数seed（种子）为整型表达式

2023/3/260（1）ASCⅡ函数语法格式：ASCⅡ(字符型表达式)功能：返回字符表达式最左端字符的ASCⅡ值。（2）CHAR函数语法格式：CHAR(0～255之间的整数)

功能：将ASCⅡ转换为字符。2023/3/261（3）LEFT函数语法格式：LEFT(字符型表达式,整型表达式)

功能：返回从字符串左边开始指定个数的字符

例4.28：查询学号最左边的4个字符。SELECTLEFT(XH,4)FROMSTU_INFO2、字符串处理函数2023/3/262（4）LTRIM函数语法格式：LTRIM(字符型表达式)功能：删除字符型表达式字符串最左边的空格，并返回字符串。

（5）REPLACE函数语法格式：REPLACE(‘字符表达式

1’,’字符表达式

2’,’字符表达式

3’)功能：用字符表达式

3替换字符表达式1中包含的字符表达式2，并返回替换后的表达式。

2023/3/263例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_INFOORDERBYXH2023/3/264（7）STR函数语法格式：STR（FLOATL类型的表达式[，length[,decimal]]）功能：将数字数据转换为字符数据。返回值的总长度（包括小数点）制定小数点右边的位数整数2023/3/2653、系统函数系统函数用于对SQLServer中的值、对象和设置进行操作并返回有关信息。（1）CASE函数①简单的CASE函数语法格式：CASE输入表达式

WHEN比较表达式THEN结果表达式

……ELSE表达式

END功能：计算输入表达式的值，与每一个WHEN的比较表达式的值比较，如果相等，则返回对应的结果表达式的值；否则，返回ELSE之后的表达式的值，如果省略了ELSE，则返回NULL值。2023/3/266例4.30：在STU_INFO中查询03学院学生的学号、姓名、性别，并将性别分别转换成“男生”、“女生”。SELECTXH,XM,XBM,XBM=CASE＇男＇

WHENXBMTHEN＇男生＇

ELSE＇女生＇

ENDFROMSTU_INFOWHEREXSH=＇03＇2023/3/267②CASE搜索函数语法格式：CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2

……ELSE表达式

END功能：系统将查询出的结果进行判断，当满足WHEN的某个条件时，则将该结果显示为THEN之后的表达式的值，如果没有满足的条件，则显示ELSE之后的表达式的值，如果没有指定ELSE子句时，返回NULL值。2023/3/268例4.31：查询成绩表XK中选修了“090101”课程的学生的学号、5分制成绩

SELECTXH,＇5分制成绩＇=CASEWHENKSCJ<60THEN＇不及格＇

WHENKSCJ>=60ANDKSCJ<70THEN＇及格＇

WHENKSCJ>=70ANDKSCJ<80THEN＇中等＇

WHENKSCJ>=80ANDKSCJ<90THEN＇良好＇

WHENKSCJ>=90THEN＇优秀＇

ENDFROMXKWHEREKCH=＇090101＇注意：这个查询改变的仅仅是查询出的结果，而基础表中的数据并没有发生转换。2023/3/269（2）CAST函数语法格式：CAST(表达式AS数据类型)

功能：将表达式的值转换为数据类型参数所指定的类型。

例4.32：查询成绩表K2004,其中考试成绩（KSCJ）、XH、XM等数据类型都是CHAR，现要查询考试成绩在50分到60之间的学生及成绩，并将成绩加10分显示。

SELECTXH,XM,KSCJ,CAST(KSCJASFLOAT)+10AS加分后成绩FROMK2004WHEREKSCJLIKE＇5_＇

2023/3/270（3）日期时间函数GETDATE语法格式：GETDATE（）

功能：按照SQLServer标准内部格式返回当前的系统日期和时间。返回值数据类型为datetime型。（4）年、月、日函数语法格式：YEAR(date)MONTH(date)DAY(date)其中参数date是数据类型为datetime或smalldatetime的表达式。返回值的数据类型为int型。

2023/3/2714.2数据更新主要包括对数据库表的数据进行插入、修改、删除操作。INSERTUPDATEDELETE2023/3/2724.2.1插入数据

插入数据的操作有两种形式：（1）使用VALUES子句向数据库的基本表表一次插入一个元组；（2）插入SELECT子查询的结果，一次插入一批数据记录。2023/3/2731、插入单个元组语法格式：INSERTINTO<表名>[(<属性列1>[,<属性列2>..)]VALUES(<常量1>[，<常量2>]…)

说明：（1）属性列与常量必须一一对应，数据类型要一致；（2）在基本表结构定义中未说明为NOTNULL的属性列，如果没有出现在INTO子句后，这些列将取空值。已经说明为NOTNULL的属性列，则必须出现在INTO子句后面；（3）如果INTO子句后面没有指定任何列，则VALUES子句后面的常量个数必须与基本表中列的个数相等，且类型、顺序一致。2023/3/274例（在STU_COU数据库中）INSERTINTOSTU(XH,XM,XB,XSH,NL)VALUES(‘20050902090’,’王东方’，‘男’，

‘0019’，22)等价于：INSERTINTOSTUVALUES(‘20050902090’,’王东方’，‘男’，

‘0019’，22)(XH,XM,XB,XSH,NL)表中所有的列2023/3/2752、插入子查询结果语法格式：INSERT[INTO]表名[(列名1，……，列名N)]SELECT兼容列名1，……，兼容列名NFROM兼容表名WHERE逻辑表达式一一对应结构一致2023/3/276例：4.33在STU表中查询出女生的学生信息存放在STUIS表中。INSERTINTOSTUISSELECTXH,XM,XB,XSH,NLFROMSTUWHEREXB=‘女’INSERTintoSTUISSELECT*FROMSTUWHEREXB=‘女’或者2023/3/2774.2.2修改数据修改数据主要是对数据库表中一个或多个元组某个或某些属性的值进行更改。语法格式：UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>,…][WHERE<条件>]2023/3/2781、修改一个元组的值

例如：UPDATESTUSETXSH=‘机械’，NL=NL+1WHEREXH=＇20000501022＇

2、修改多个元组的值例：将所有课程的学分改为2分

UPDATECOURSESETCCREDIT=2例：将女生年龄增加1岁UPDATESTUSETNL=NL+1WHEREXB=‘女’2023/3/2793、带子查询的修改语句4.34：将选修了高等数学的90分以下的学生的成绩加10分（从edu_d库中的xk数据表中修改）UPDATEXKSETKSCJ=KSCJ+10WHEREKCH=(SELECTKCHFROMGCOURSEWHEREKM=＇高等数学＇)ANDKSCJ<=90建议：使用UPDATE对批量数据进行修改前，先用SELECT语句将要修改的记录查询出来，仔细检查无误后，再进行修改。2023/3/2804.2.3删除数据语法格式：DELETEFROM<表名>[WHERE<条件>]注意：

DELETE命令删除的是一个或多个元组（记录、行），而不是某个行中个别列的值。不需要的某个行的个别列的值，只能用UPDATE命令修改成NULL值或空格符，不能被删除。2023/3/2811、删除一个元组例如：DELETEFROMSTU_INFOWHEREXH=＇2003050601＇2、删除多个元组例如：DELETEFROMSTU_INFOWHEREBH=＇应0203＇

2023/3/2823、带子查询的删除语句

例4.35：将信息学院学生的选课信息全部删除

注意：为避免误操作，在日常工作中，通常操作删除语句时，要先查询要删除的数据，确认无误后，再将SELECT*部分改成DELETE进行删除操作。DELETEFROMXK,STU_INFOWHERESTU_INFO.XSH=’12’ANDSTU_INFO.XH=XK.XHSELECTXK.*FROMXK,STU_INFOWHERESTU_INFO.XSH=’12’ANDSTU_INFO.XH=XK.XHSELECT*FROMXKWHERE‘12＇=(SELECTXSHFROMSTU_INFOWHERESTU_INFO.XH=XK.XH)DELETEFROMXKWHERE‘12＇=(SELECTXSHFROMSTU_INFOWHERESTU_INFO.XH=XK.XH)2023/3/2834.3数据定义SQL的数据定义语句是对数据库表、视图、索引等的结构和属性进行定义。常见的操作方式如下表：表4_3数据操作方式一览表DROPINDEXCREATEINDEX索引DROPVIEWCREATEVIEW视图ALTERTABLEDROPTABLECREATETABLE表修改删除创建操作方法操作对象2023/3/2844.3.1定义基本表一、语法格式：CREATETABLE[数据库名]<表名>（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）默认为当前数据库

要定义的基本表的名字组成该表的各个属性（列）涉及相应属性列的完整性约束条件涉及一个或多个属性列的完整性约束条件2023/3/285常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束FOREIGNKEY……REFERENCESCHECK约束CHECK默认值DEFAULT标识列IDENTITY(seed,inctement)公式AS2023/3/286二、数据类型类型标识符取值范围空间注意逻辑类型BIT0,11位不允许定义为NULL整数类型INT-231~231-14Smallint-215~215-12TINYINT0~28-11正整数2023/3/287浮点类型标识符类别取值空间REAL-3.4×1038~3.4×10384FLOAT(n)n省略或[8，15]-1.7×10308~1.7×103088n∈[1,7]-3.4×1038~3.4×10384Decimal(p,s)或Numeric(p,s)P数据总位数S小数位数-1038~1038-1Numeric可用于带identify关键字的列2-172023/3/288字符类型定长CHAR(n)最多为8000个ANSI字符NCHAR(n)最多为4000个Unicode字符变长VARCHAR(n)最多为8000个ANSI字符NVARCHAR(n)最多为4000个Unicode字符超长TEXT1~231-1NTEXT230-1个Unicode字符2023/3/289二进制数据类型类型标识符空间说明定长BINARY(n)8000个字节如允许为NULL，成变长变长VARBINARY(n)8000个字节超长IMAGE231-1个字节2023/3/290时间类型DATETIME8字节1753-1-1~9999-12-31的时间和日期精确到1/300秒SMALLDATETIME4字节1900-1-1~2079-6-6的时间和日期精确到1秒2023/3/291货币类型MONEY用2个4字节整数存储SMALLMONEY用4字节存储2023/3/292[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))2023/3/293[例2]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno))2023/3/294例4.36：定义一个学生的成绩表k2001CREATETABLEk2001/*表名为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) /*备注*/2023/3/295例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约束定义的开始

约束的名称

外键约束

2023/3/2974.3.2修改基本表

语法结构：ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>|COLUMN<列名>][ALTERCOLUMN<列名><数据类型>]增加新列和新的完整性约束条件删除指定的完整性约束条件用于修改列名和数据类型2023/3/298例4.43向Stu_info表增加“入学时间”列，其数据类型为日期型

ALTERTABLESTU_INFOADDRXSJDATETIME不论基本表中原来是否已有数据，新增加的列一律为空值。1、添加列2023/3/299例4.44:将教师表的XB列改为SMALLINT数据类型

注意：修改原有的列定义有可能会破坏已有数据2、修改列ALTERTABLEGTECHALTERCOLUMNXBSMALLINT2023/3/21003、删除列例4.45:删除教师表中的性别一列。ALTERTABLEGTECHDROPCOLUMNXB下面的列不能除去：被复制列；用在索引中的列；用在CHECK、FOREIGNKEY、UNIQUE或PRIMARYKEY约束中的列；有相关联的默认值（由DEFAULT关键字定义）的列或绑定到默认对象的列；绑定到规则的列。2023/3/2101[例]删除数据库表new_employee中的emp_id的检查约束。ALTERTABLEnew_employeeDROPCK_emp_id4、删除约束2023/3/21024.3.3删除基本表语法格式：DROPTABLE<表名>

2023/3/21034.3.4视图

视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。视图是从一个或多个基本表（或视图）导出的表，是个虚表。数据库只存放视图的定义，不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以，视图的数据跟随基本表的数据而变化。视图一旦被定义，就可以被查询、删除，修改。再定义一个新的视图。2023/3/2104补充说明：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。视图允许我们把表结构的细节封装起来，这些表可能因你的应用的进化而变化，而我们却可以通过视图给用户一个一致的接口．视图几乎可以在一个真正的表可以使用的任何地方使用．在其它视图上面再建造视图也是很常见的．2023/3/21051、定义视图（1）用企业管理器建立视图（自学）（2）用SQL数据定义语句createview命令建立视图

语法格式：CREATEVIEW视图名AS<子查询>[WITHCHECKOPTION]

对视图进行UPDATE,INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的条件表达式

2023/3/2106注意问题：（1）只能在当前的数据库中创建视图；

（2）一个视图最多可以引用1024个列；（3）视图的命名必须符合SQLServer中的标识符的定义规则；

（4）可以将视图建立在其他视图或者引用视图的过程之上，SQLServer2000中允许最多32层的视图嵌套；

（5）不能将规则、默认值定义绑定在视图上；（6）定义视图的查询语句中不能包括COMPUTE、COMPUTEBY、ORDERBY子句或是INTO等关键词；

（7）在视图中不能定义全文索引，但可以定义索引；（8）不能创建临时视图，而且也不能在临时表上创建视图；2023/3/2107（9）默认状态下，视图中的列继承他们在基表中的名称。对于以下情况，在创建视图时需要明确给出每一列的名称。①视图中的某些列来自表达式、函数或常量；②视图中两个或多个列在不同表中具有相同的名称；③希望在视图中的列使用不同于基表中的列名时。

2023/3/2108A．选择列定义视图例：

CREATEVIEWMYVIEW1ASSELECTEMP_ID,LNAME,FNAMEFROMEMPLOYEE定义了视图后，可以和表一样，使用SELECT语句访问它。

2023/3/2109B．基于列的表达式定义视图

例：CREATEVIEWMYVIEW2ASSELECTTITLE，ADVANCE，

PRICE*ROYALTY*YTD_SALESASNEWPRICEFROMTITLESWHEREPRICE>$52023/3/2110C．选择行定义视图例：CREATEVIEWMYVIEW3ASSELECT*FROMTITLESWHERETITLELIKE＇%SALES%＇

2023/3/2111D．选择行和列例：CREATEVIEWMY