【文档说明】数据库原理及应用开发技术_SQL3-课件.ppt，共(95)页，531.512 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92509.html

以下为本文档部分文字说明：

讲义三关系数据库标准语言SQL•1SQL简介•2SQL定义语言•3SQL操作语言•4SQL查询语言•5SQL的基本语言元素•6SQL的操作符•7SQL表达式•8SQL条件•9SQL函数•10SQL查询与子查询•总结3.1SQL简介•Struc

tureQueryLanguage(SQL)的历史–1970年,ProfCodd提出著名的关系数据模型–SQL语言由IBMResearchLab首先开发创建SystemR时使用–1979年,关系软件公司(Oracle)首先开发出

实用的商业SQL版本–SQL是高级的非过程化编程语言。3.1SQL简介•SQL标准–SQL-86是第一个SQL标准–SQL-89、SQL-92(SQL2)、SQL-99(SQL3)–关系型商业数据库遵守的查询语言规范

–各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充3.1SQL简介•SQL的使用–直接使用•交互式查询工具（MSsqlserver:Transact-sql）•直接键入SQL命令对数据库进行操作•由DBMS进行解释–嵌入式SQ

L(EmbeddedSQL)•嵌入到高级编程语言•使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力。•需要将嵌入的SQL语句转化为宿主语言编译器能处理的语句3.1SQL简介•SQL语句分类–数据定义语言（DataDefinition）•create•alt

er•drop–数据操作语言（DataManipulation）•insert•update•delete–数据查询语言•select–数据控制语言(DataControl)•grant•role•revoke3.5.1数据类型•常用的数据类型–数据类型描述–CHAR(s

ize)存储最长为n个字节的定长字符串–VARCHAR(size)存储最长为n个字节的变长字符串–NUMERIC(P,S)存储任何类型的数字–DATETIME存储日期时间–INT长整形–SMALLINT短整形–F

LOAT(n)浮点数，精度至少为n位数字3.1SQL简介•数据定义语句格式–1创建表•CREATETABLE<表名>•（<列名1><数据类型>[列完整性约束条件]，•<列名2><数据类型>[列完整性约束条件]，•…•<列名n><数据类型>[列完整性约束条件],•<表完整性约束条件>）

；3.1SQL简介•数据定义语句格式–2更改表•ALTERTABLE<表名>•[ADD<新列名><数据类型>[列完整性约束条件]，•[DROP<完整性约束名>]，•[MODIFY<列名><数据类型>];–3删除表•DROPTABLE<表名>;3.1SQL简介•数据操作

语句格式–1插入操作•INSERTINTO<表名>•(<列名1>[,<列名1>,…<列名n>])•VALUES(<值1>,[<值2>,…<值n>]);3.1SQL简介•数据操作语句格式–2修改操作•UP

DATE<表名>•SET<列名>=<表达式>[,<列名>=<表达式>]…•[WHERE<条件>];–3删除操作•DELETE•FROM<表名>•[WHERE<条件>];3.1SQL简介•数据查询语句：–数据查询是数据库的核心操作–SELECT语句的格式为

：•SELECT[ALL|DISTINCT]<目标列表达式>[别名][,<目标列表达式>[别名]]„FROM<表名或视图名>[别名][,<表名或视图名>[别名]]„[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|

DESC][,<列名3>[ASC|DESC]„„]；3.1SQL简介•数据控制语句：授权–SQL语言用GRANT语句向用户授予操作权限，GRANT语句的一般格式为：•GRANT<权限>[，<权限>]…•[ON<表名或视图名>]•TO<用户>[，<用户>]…•[

WITHGRANTOPTION];3.1SQL简介•数据控制语句：收回权限–授予的权限用REVOKE语句收回，REVOKE语句的一般格式为：•REVOKE<权限>[,<权限>]…•[ON<表名或视图名>

]•FROM<用户>[,<用户>];3.1SQL简介•数据控制语句：角色–创建角色•CREATEROLE<角色>;–分配角色：•GRANT<角色>TO<用户>;3.2SQL定义语言表、视图是数据库中的主体。SQL提供数据定义语句对这些主体进行管理S

QL的数据定义语句操作对象操作方式创建删除修改表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW3.2.1表定义1表的创建SQL语言使用CREATETABLE语句定义表，其一般格式如下：CREATETABLE<表名>（<列名><数据类型>[列

级完整性约束条件][，<列名><数据类型>[列级完整性约束条件]]…）[，<表级完整性约束条件>]）;3.2.1表定义1表的创建（1）实体完整性[例]建立Student表，Sno作为主码。CREATETABLEStudent(SnoCHA

R(7)PRIMARYKEY,SnameCHAR(8),SsexCHAR(2),SageSMALLINT,SdeptCHAR(20));3.2.1表定义1表的创建（1）实体完整性[例]建立SC表，Sno和Cno是主码。CREATETABLESC(Sno

CHAR(7),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno));3.2.1表定义1表的创建（2）参照完整性[例]建立SC表，Sno和Cno是主码，且Sno和Cno也是外码，分别引用Student表的Sno列和Course表的Cno列。CREA

TETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno)

,FOREIGNKEY(Cno)REFERENCESCourse(Cno));3.2.1表定义1表的创建（3）属性值约束•非空值限制•指定允许的取值范围当往表中插入元组或修改属性的值时，数据库管理系统就检查属性上的限制是否被满足，如果不满足则操

作被拒绝执行。3.2.1表定义1表的创建（3）属性值约束[例]当学生的性别是男时，其名字不能以Ms.打头。CREATETABLEStudent(SnoCHAR(7)PRIMARYKEY,SnameCHAR(8)NOTNULL,Ss

exCHAR(2),SageSMALLINT,SdeptCHAR(20),CHECK(Ssex='女'ORSnameNOTLIKE'Ms.%'));3.2.1表定义2修改表一般格式为：ALTERTABLE<表

名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>];3.2.1表定义2修改表[例]向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeD

ATE;不论表中原来是否已有数据，新增加的列一律为空值。[例]删除学生姓名不能取空值的约束。ALTERTABLEStudentDROPNOTNULL(Sname);3.2.1表定义3、删除表当某个表不再需要时，

可以使用DROPTABLE语句删除它。其一般格式为：DROPTABLE<表名>[例]删除Student表。DROPTABLEStudent;3.2.2试图定义1、视图的作用（1）视图能够简化用户的操作（2）视图可以

减少冗余数据（3）视图对重构数据库提供了一定程度的逻辑独立性（4）视图能够对机密数据提供安全保护3.2.2试图定义2、视图创建SQL语言用CREATEVIEW命令建立视图，其一般格式为：CREATEVIEW<视图名>[(<列名>[，

<列名>]…)]AS<子查询>[WITHCHECKOPTION]；3.2.2试图定义2试图创建[例]建立计算机系学生的视图。CREATEVIEWStudent_CSASSELECT*FROMStudentWHE

RESdept='计算机';3.2.2试图定义2试图创建[例]建立英语课（1156）成绩单的视图。CREATEVIEWEnglish_Grade(Sno,Sname,Grade)ASSELECTStuden

t.Sno,Sname,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoANDSC.Cno='1156';3.2.2试图定义2试图创建[例]建立英语课的成绩在80分以上的学生的视图。C

REATEVIEWEnglish_Grade_80ASSELECTSno,Sname,GradeFROMEnglish_GradeWHEREGrade>=80;3.2.2试图定义3删除视图当不再需要一个视图

时，可以删除它，语句格式为：DROPVIEW<视图名>[例]删除视图Student_CSDROPVIEWStudent_CS;3.3SQL操作语言1插入操作2修改操作3删除操作3.3.1插入操作1、插入操作插入语句的格式是：INSERTINTO<表名>[(<属性列1>[,<属

性列2>…])VALUES(<常量1>[,<常量2>]…);3.3.1插入操作[例]将学生王林的信息插入到Student表中。INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('2

000012','王林','男','计算机',19);INTO子句指定Student表和要赋值的列，VALUES子句对元组的各列赋值。3.3.1插入操作[例]在表Course中增加课程离散数学的信息。INSERTINTOCourse(Cno,Cna

me,Cpno,Ccredit)VALUES('1136','离散数学',NULL,4);符号NULL的含义是赋予该列的值是空值。3.3.2修改操作2、修改操作修改操作又称为更新操作，语句的一般格式是：UPDATE<表名>SET<列名>=<表达式>[,<列名>=<表达式>]…[WHERE

<条件>];3.3.2修改操作[例]将学号为2000012的学生的年龄改为18岁。UPDATEStudentSETSage=18WHERESno='2000012';[例6]将所有学生的年龄增加1岁。UPD

ATEStudentSETSage=Sage+1;3.3.3删除操作3、删除操作语句的一般格式为：DELETEFROM<表名>[WHERE<条件>];3.3.3删除操作[例]删除学号为2000012的学生记录。DELETEFROMStudentWHERESno='200

0012';[例]删除所有的学生选课记录。DELETEFROMSC;3.4SQL查询语音•1.查询基本结构概述•2.查询子句概述•3.SELECT列表•4.FROM子句•5.WHERE子句3.4.1查询基本结构概述•查询基本结构概述：–SELECT列表：指定列[FROM指定表

][WHERE指定行]•语义：–Select子句中的属性列表，对上述结果作投影（π）操作。–From子句：对指定关系，作笛卡儿积（×）–Where子句：进行选择（σ）运算，找出符合条件的元组。–3.4.2查询子句概述•SELECT子句指定您要检索的列。•FROM子句指定从中请求列的表。在所有

从表中检索数据的查询中都需要该子句。•ON子句指定FROM子句中的表将如何进行连接。该子句只用于多表查询。•WHERE子句指定表中您要查看的行。•GROUPBY子句用于集合数据。•HAVING子句指定要在其上收集集合数据的行。•OR

DERBY子句对结果集中的行进行排序。3.4.3SELECT列表•1从表中选择所有列–SELECTdistinct*FROMtable-expression•2从表中选择特定的列–SELECTdept_id,dept_nameFROMdepartment•3重命名

查询结果中的列–SELECTcolumn-nameASalias–SELECTcolumn-namealias•4查询结果中显示字符串–select'thefirstnameis'as“”,first_namefromemployees;•5计算SELECT列

表中的值–SELECTfirst_name,salary-10AS"salaryaftertax"FROMemployees;3.4.4FROM子句•直接使用表名–FROMtable_name•使用相关名–您可以赋予表名一个相关名以减少需要键入的字符。您可以在FROM子句中指

派相关名，方法是在表名之后键入该相关名，如下所示：–SELECTd.dept_id,d.dept_nameFROMDepartmentd•FROM子句包括链接两个或多个表的表名3.4.5WHERE子句•比较–WHEREsalar

y>50000•范围–WHEREsalaryBETWEEN40000AND60000•列表–WHEREstationIN(‘beijing',‘shanghai',‘jinan')•字符匹配–WHEREphoneLIKE'415%'•未知值–WHEREfirst_nameisnotNULL•组合

–WHEREsalary>50000ANDfirst_namelike'A%'3.5SQL的基本语言元素•数据类型•数值常量•数据格式化•空值3.5.1数据类型•数据类型自动转换规则–INSERT\UPDATE语句中,数据值按照字段的类

型进行转换–SELECT语句中,字段值按照定义的变量类型转换–比较字符数值与数字数值时,将字符数值转换为数字–比较字符数值与日期数值时,将字符数值转换为日期–赋值语句中,一般将等号右边的变量转换为左边的数据类型–……3.5.1数据类型•字符自动转换–

SELECTsalary+’10’FROMemployees;–SELECTlast_nameFROMemployeesWHEREemployee_id=‘200’;–SELECTlast_nameFROMemployeesWHEREhiredate

=‘1982-01-23’;3.5.2数值常量•字符常量–‘Hello’–’ORACLE’–’09-MAR-98’•数字常量–25–+6.34–0.5–25e-03–-13.5.3数据格式化•字符串格式化–CAST(expressionASdata_t

ype)–CONVERT(data_type[(length)],expression[,style])•日期格式化–字符串转日期Selectcast('2009-01-01'asdatetime)–日期格式转换Se

lectCONVERT(varchar(100),GETDATE(),0)3.5.4NULL(空)•Null:表明记录的字段中不包含任何值(空)•目前将‘’等同于Null（ORACLE)•条件中的NULL–IfA

is:ConditionEvaluatesto:–10aISNULLFALSE–10aISNOTNULLTRUE–NULLaISNULLTRUE–NULLaISNOTNULLFALSE–10a=NULLUNKNOWN–10a!=NULLUNKNOWN3.6SQL的操作符•操作符

介绍–一元操作符–二元操作符–多元操作符•算术运算符•连接运算符•组合操作符3.6.1算数运算符•运算符举例•+-SELECT*FROM•WHEREquantity=-1;••SELECThire_dateFROMemployeesW

HEREconvert(int,GETDATE()-hire_date,1)>365;•*/UPDATEemployeesSETsalary=salary*1.1;3.6.2连接运算符•连接运算符:+•Example:–CREATETABLEtab1(col1

VARCHAR(6),col2CHAR(6),col3VARCHAR(6),col4CHAR(6));–INSERTINTOtab1(col1,col2,col3,col4)VALUES('abc','def','ghi','jkl');–SELECTcol1+col2+col3+col

4"Concatenation"FROMtab1;–------------------------–Abcdefghijkl3.6.3组合操作符•将两个或多个查询的结果组合到一个结果集中–操作符返回结果–UNION组合查询的结果（不重复）–UNIONALL组合查询

的全部结果–INTERSECT组合查询的共有结果–MINUS对第一个查询取唯一记录，对共有记录不返回3.6.3组合操作符•举例：–createtablea1(t1char(1),t2char(2));–createtablea2(

t01char(1),t02char(2));–insertintoa1(t1,t2)values('1','11');–insertintoa1(t1,t2)values('2','22');–insertintoa2(t01,t02)values('2','22');–insertinto

a2(t01,t02)values('3','33');–selectt01,t02froma2–Union–selectt1,t2froma1;3.7SQL表达式•SQL表达式的应用范围–Select[expr]、

where[expr]、having[expr]–Orderby[expr]–insert的values()、update的set[expr]•SQL表达式的形式–简单表达式、复合表达式、CASE表达式–D

atetime表达式、间隔表达式–变量表达式、表达式列表3.7.1SQL简单表达式•举例–Select[Employees.]first_namefromEmployees–Select1fromEmployeeswherefirst_name=‘w’–Select‘anystring’fr

omEmployeeswhere…3.7.2SQL复合表达式•举例–(’CLARK’+’SMITH’)–LEN(’MOOSE’)*57–SQRT(144)+723.7.3CASE表达式•CASE表达式让你可以在SQL

中使用IF…ELSE…ENDIF的逻辑•Example–SELECTsno,CASEWHENgrade>85THEN'high'–WHENgrade<70THEN'low'–ELSE'medium'END–FROMSC;3.7.4间

隔表达式•举例–SELECTdatediff(year,getdate(),scome)fromstudent;–SELECTyear(getdate())–year(scome)fromstudent;3.7.5表达式列表•

Examples–1.Insertintoexployees(first_name,last_name)values(‘Michael’,’Jordan’);–2.SELECT*FROMemployeesWHERE(first_name,last_name)IN((’Gu

y’,’Himuro’),(’Karen’,’Colmenares’));3.8SQL条件•SQL条件的应用范围–Select的WHERE和HAVING子句–Delete和update的WHERE子句•SQL条件的形式–比较条件、逻辑条件、成员条件–

范围条件、NULL条件、LIKE条件–复合条件3.8.1比较条件•条件类型Example–>,<,>=,<=SELECT*FROMemployeesWHEREsalary>=2500;–ANY,SOMESELECT*FROMemployeesWHEREsal

ary=ANY(SELECTsalaryFROMemployeesWHEREdepartment_id=30);–ALLSELECT*FROMemployeesWHEREsalary>=ALL(1400,3000);3.8.2逻辑条件

–逻辑类型Example–NOTSELECT*FROMemployeesWHERENOT(job_idISNULL);SELECT*FROMemployeesWHERENOT(salaryBETWEEN1000AND2000);AND\ORSELECT*FROMemployeesWHEREjob_

id=’PU_CLERK’AND[OR]department_id=30;3.8.3成员条件–类型Example–INSELECT*FROMemployeesWHEREjob_idIN(’PU_CLERK’,’SH_CLERK’);S

ELECT*FROMemployeesWHEREsalaryIN(SELECTsalaryFROMemployeesWHEREdepartment_id=30);NOTINSELECT*FROMemployeesWHEREjob_idNOTIN(’PU_CLERK’,’SH_CLERK’);

3.8.4范围条件•类型Example[not]betweenXandySELECT*FROMemployeesWHEREsalaryBETWEEN2000AND3000;3.8.5NULL条件•类型ExampleIS[NOT]SELECTlast_n

ameNULLFROMemployeesWHEREsalaryISNULL;3.8.6LIKE条件•举例：•SELECTename•FROMemp•WHEREenameLIKE'A\_L%'•ESCAPE'\';类型X[not]LIKEy[escape‘

z’]3.8.7复合条件•通过（）、not、and、or复合各类简单的条件•Example–Selectfirst_namefromemployees–Wherefirst_namelike‘m%’an

d–(salary>=2000ordepartment_idin(10,20));3.9功能函数•内部函数–单行函数–聚集函数•用户自定义函数3.9功能函数•常用的单行函数–数值函数•abs、sqrt、round、mod–字符函数•lower、upper、len、tri

m、substr–日期函数•Day,month,year,getdate,weekday,today–转换函数•Convert、cast3.9功能函数•常用的聚集函数–求平均：AVG–总计：COUNT–最大值：MAX–最小值：MIN–求总和：SUM3.10SQL查询和子查询•1.SQL查询举例•

2.SQL子查询–子查询简介–子查询的作用–WHERE子句中的子查询–FROM子句中的子查询–HAVING子句中的子查询3.10.1SQL汇总、分组与排序•SQL汇总：–应用了集合函数的每一组行生成一个值•举例：–SELECTavg(salary+1)FROM…–S

ELECTcount(distinctid)FROM…–SELECTsum(salary)FROM…–SELECTmax(salary)FROM…3.10.2SQL汇总、分组与排序•SQL分组：–GROUPBY字句，将表的输出划分为若干个组–举

例：•SELECTemployee_id,avg(salary)FROMemployeesGROUPBYemployee_id;3.10.2SQL汇总、分组与排序•WHERE子句与HAVING子句：–Where决定哪些元组被选择参加运算，作用于关系中的元组。–Ha

ving决定哪些分组符合要求，作用于分组。–聚合函数的条件关系必须用Having，Where中不应出现聚合函数。•举例：•SELECTdepartment_id,MIN(salary),MAX(salary)FROMemployeesGROUPBYdepartment_idHAVINGMI

N(salary)<5000;3.10.3SQL汇总、分组与排序•SQL排序：–对查询结果集的记录安装一个多个表达式排序–升序：ASC;降序：DESC;默认：升序•举例：–SELECT*FROMemployeesORDERBYfirst_name–SELECT

*FROMemployeesORDERBYdepartment_id,first_namedesc;3.10.4连接：从多个表查询数据•1连接:–通过使用来自两个或多个表（或多个视图）的信息，重新创建更大的表。–若要使连接有效，您必须组合每个表

的正确的列。–连接操作通过比较指定的列中的值来组合多个表中的行。3.10.4连接：从多个表查询数据•2连接方式:–内连接：INNERJOIN–外连接：OUTERJOIN•LEFT[OUTER]JOIN•R

IGHT[OUTER]JOIN•FULL[OUTER]JOIN–交叉连接:CROSSJOIN–自然连接：NATURALJOIN3.10.4连接：从多个表查询数据•3连接举例:–(1)内连接：•SELECTe.first_n

ame,d.department_name•FROMemployeeseJOINdepartmentsd•ONe.department_id=d.department_id;–等价于：•SELECTe.first_name,

d.department_name•FROMemployeese,departmentsd•wheree.department_id=d.department_id;3.10.4连接：从多个表查询数据•3连接举

例:–(2)外连接：–SELECTe.employee_id,d.department_NAMEFROMemployeeseLEFTJOINdepartmentdONe.department_id=d.department_id3.10.4连接：从多个表查询数据•3连接举例:–(3)

交叉连接：•SELECTe.first_name,d.department_name•FROMemployeeseCROSSJOINdepartmentsd;•SELECTe.first_name,d.de

partment_name•FROMemployeese,departmentsd;3.10.4连接：从多个表查询数据•3连接举例:–(4)自然连接：•SELECTe.first_name,d.departm

ent_name•FROMemployeeseNATURALJOINdepartmentsd;3.10.5子查询•简介–1.子查询的结构类似于常规查询，并且出现在主查询的SELECT、FROM、WHERE或HAVING子句中。–2.由于单一的表查询无

法满足现实系统业务的需要。子查询是满足该查询需求的一种解决方式。3.10.5子查询•使用–1插入INSERT或CREATETABLE语句的记录集–2确定UPDATESET子句的查询值–3确定SELECT,UPDATE,

DELETE语句中的Where、Having子句的值–4定义一个表，并在查询中使用3.10.5子查询•INSERTINTO语句子查询–复制表employees的数据到表employees1中–INSERTINTOemployees1•SELECTemployee_id,first_

name,last_name,salary*1.1–FROMemployees3.10.5子查询•复制表语句子查询–select*–intoemployees1–fromemployees3.10.5子查

询•UPDATE语句的SET子查询–UPDATEemployeesd•SETsalary=salary+(SELECTavg(e.salary)–FROMemployeese–WHEREe.department_id=d.department_id);3.10.5子查询•

Where子句中的子查询–SELECTfirst_name,salary•FROMemployees•WHEREsalary>(SELECTavg(e.salary)–fromemployeese);3.10.5子查询•定义一个查询表–SELECTa.depart

ment_id"Department",–a.num_emp/b.total_count"%_Employees",–a.sal_sum/b.total_sal"%_Salary"•FROM•(SELECTdepartment

_id,COUNT(*)num_emp,SUM(salary)sal_sum–FROMemployees–GROUPBYdepartment_id)a,•(SELECTCOUNT(*)total_count,SUM(salary)total_sal–FRO

Memployees)b;总结1.SQL是关系数据库的标准语言。从功能上可以划分为DDL（CREATE和DROP）、DML（INSERT、UPDATE、DELETE、SELECT）、DCL（GRANT和REV

OKE）。2.查询语句SELECT是SQL中常使用的语句。由SELECT、FROM、WHERE、GROUPBY、HAVING和ORDERBY子句构成。SELECT和FROM子句在每个SQL语句中都必须出现，其它子句可以根据实际情况选用。

总结3.SELECT语句的基本功能是经过横向和纵向操作，从一个表构造出另外一个新表，这个新表是查询的结果，是一个临时表。4.聚集函数的自变量的值不是单值，而是一个集合。SQL提供的聚集函数有COUNT、MAX、MIN、SUM、AVG。特别要注意的是，除了COUNT(*)函数以外，其它的聚集函数对

空值忽略不计。总结5.分组是将在分组列上有相同值的元组分配到同一组。分组是聚集函数的作用对象，可以把同一组的所有元组，或者每个元组在某一列上的值在作为聚集函数自变量的值。6.连接操作是一个二元操作符，它将两个表中的元组首尾相连，形成新表的一个元组。连接操作有交叉连接、条件连接和外连接三类。总结7

.SELECT语句的子句中出现了SELECT语句叫做嵌套查询或子查询。8.子查询可以出现在CREATE、INSERT、UPDATE和DELETE语句中。9.SQL语句可以直接使用也可嵌入到高级编程语言中使用课堂练习•找出所在部门在“CHIKAGO”,各个不同职位的平

均工资，且每个职位的人数在2人以上。