关系数据库设计与应用（工作手册式） 课件 第5章 SQL基础

第5章SQL基础工业和信息化部“十四五”规划教材

浙江省高职院校“十四五”首批重点教材《关系数据库设计与应用》（工作手册式）

配套课件学习目标LEARNINGTARGET认识SQL的标准；认识SQL的数据类型；掌握SQL的定义语句。知识目标能力目标会创建数据库表和数据库；能在数据表中插入数据、修改数据及更新数据。思政目标培养严谨认真的学习态度；了解航天精神，并将航天精神融入学习和工作当中。主要内容MAINCONTENTS5.1SQL基本概念5.2SQL数据类型5.3数据定义语句*5.4数据操作语句SQL基本概念5.1SQL基本概念什么是SQL？SQL(StructureQueryLanguage)是结构化查询语言的简称，是用于关系型数据库通讯的标准语言，也是关系数据库系统中最流行的数据查询和更新语言。SQL可以用两种方法来发音，如P95页。S—Q—L，或者“Sequal”SQL基本概念SQL的标准由来：SQL是20世纪70年代由IBM公司研制开发的关系数据库管理系统的原型系统systemR实现的，经过多年的发展，SQL语言已成为关系数据的标准语言。不同之处：SQL不同于Java、C#程序设计语言，它只是数据库能够识别的指令，但是在程序中，可以组织SQL语句发送给数据库，数据库再执行相应的操作。版本：目前最新版本是SQL2019版本，本课程用到的是SQL2012版本。SQL基本概念SQL的特点SQL之所以能够被用户和业界所接受并成为国际标准，是因为它是一种综合的、功能强大的且简单易学的语言。SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，其主要特点如下：一体化。语言风格统一高度非过程化。面向结果简洁。命令少，核心语句只有9个动词（见表5.1）能以多种方式使用。SQL基本概念SQL的功能概述（4个）SQL功能命令数据定义(DDL)create、alter、drop

数据查询(DQL)select数据操作(DML)insert、update、delete数据控制(DCL)grant、revoke定义、修改、删除选择插入、更新、删除授权、取消授权SQL基本概念SQL的功能概述（4个）数据定义（DDL）：实现定义、删除和修改数据库对象（基本表、视图、索引等）的功能。数据查询（DQL）：实现查询数据的功能。数据操纵（DML）：实现对数据库数据的增加、删除和修改的功能。数据控制（DCL）：实现控制用户对数据库的操作权限的功能。SQL的数据类型5.2SQL的数据类型数据类型（P97-98）数值型字符型日期和时间型货币型SQL的数据类型1.数值型1）准确型数值是指在计算机中能够精确存储的数据（如int，smallint）如123，562）近似型数值用于表示浮点型数据的近似数据类型（如float，real）。浮点型数据为近似值，表示在其数据类型范围内的所有数据在计算机中不一定都能精确地表示。如15.5，2.6SQL的数据类型2.字符型字符型数据是由汉字、字母、数字和各种符号组成的。在SQLServer中，字符数据是用单引号括起来的。如：‘VisualBasic’、‘数据库’、‘2003’等都是合法的字符串。定长字符串：char(n)，nchar(n)

变长字符串：varchar(n)，nvarchar(n)例：varchar(5)“abc”长文本：text变长文本：ntext例：char(5)“abc□□”所谓定长就是长度固定的，当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，使长度达到相应的长度，如char，nchar；有var前缀的，表示是实际存储空间是变长的，比如varchar,nvarchar变长字符数据则不会以空格填充。SQL的数据类型3.日期和时间型SQLServer的日期时间型数据是将日期和时间合并起来存储。提供两种：Datetime：占8个字节，存储时间范围长。Smalldatetime：占4个字节，存储时间范围短。（默认格式是MMDDYYYYhh:mm:ssAM/PM）注：当插入数据或在其它地方使用该类型时，需用单引号把它括起来。问：1）输入2021年11月11日，如何表示？

2）输入2021年11月11日下午2点25分35秒，如何表示？1）‘2021-11-11’或‘20211111’2）‘2021-11-112:25:35PM’或‘2021-11-1114:25:35’SQL的数据类型4.货币类型货币数据表示正的或负的货币值用money和smallmoney表示货币类型，存储精度为4位小数。货币数据前要加一个货币符号，如$。输入负值时应在货币数据的前面加一个负号。如$5.5-$600.55SQL的数据类型5.SQL标识符与关键字用户或系统为数据库对象设定的名字，如数据库、数据表、表约束及表内的字段、视图等。是字符串，可以具有多达128个字符的长度，只能由字母、数字以及下划线组成，并且只能以字母开始。可分为规则标识符和定界标识符。规则标识符大小写不敏感。定界标识符大小写敏感，由双引号或方括号界定。select*fromabcselect*fromABCselect*from[st]select*from[ST]表名中含有空格，则应加上方括号或双引号例：select*from[ab]SQL的数据类型5.

SQL标识符与关键字对于SQL有特殊意义的单词，称为关键字。如SELECT、OR、AND等。关键字一般都不用于规则标识符。关键字大小写不敏感。SQL的数据类型5.

SQL标识符与关键字NULL代表丢失或未知的数据。NULL属于一个域：不能说NULL“等于”、“大于”、“小于”某个值。SQL的数据类型5.

SQL标识符与关键字大部分的逻辑系统都是建立在二值基础之上的：是/否（True/False）；但在SQL中，逻辑系统是建立在三值基础之上的：是/否/未知（True/False/Unknown）；一般地，对于两个表达式E1和E2，若要比较E1＝E2，如果E1为NULL或者E2为NULL或者两者都为NULL，那么其结果为Unknown（未知）。SQL的数据类型5.

SQL标识符与关键字Not：见真得假，见假得真，见未知得未知And：①见假为假，全真为真②见未知得未知Or：①见真为真，全假为假②见未知得未知数据定义语句5.3数据定义语句SQL的数据定义语句能创建和修改数据库的逻辑结构。数据定义语句包括：定义对象（CREATE语句）修改对象（ALTER语句）删除对象（DROP语句）注：这里所说的对象包括数据库、表、视图等各种对象。在SQL中，数据库被定义为对象（即基本表、视图和索引等）的集合，并包含数据库中每一个对象的定义。数据库在磁盘上是以文件为单位存储的，由数据文件和日志文件组成：数据文件：实际存放数据库中的所有数据和对象。日志文件：存放（记录）用户对数据库所进⾏的所有操作，它是维护数据库完整性的重要工具。1.定义数据库5.3.1定义数据库与数据表

5.3.1定义数据库与数据表（1）用简单语句定义数据库用简单语句定义数据库语法如下：CREATEDATABASE<数据库名称>说明：1）使用该语句的用户必须具有DBA（DatabaseAdministrator）的权限。2）数据库的创建者被默认为该数据库的所有者。3）简单语句定义数据库，数据文件、日志文件初始大小、最大容量、增量，以及存放文件的位置等都会选择系统默认值。1.定义数据库5.3.1定义数据库与数据表（1）用简单语句定义数据库【例5.1】用简单语句创建test数据库。CREATEDATABASEtest1.定义数据库5.3.1定义数据库与数据表（2）用完整语句定义数据库使用完整SQL语句定义数据库，在创建时可以指定数据库的名称、数据库文件存放的位置、数据文件和日志文件的初始容量、最大容量和文件的增量等。其完整语法格式如下：CREATEDATABASE<数据库名称>ON{[PRIMARY](NAME=<逻辑文件名称>,FILENAME='<物理文件名称>',[SIZE=<文件初始大小>],[MAXSIZE={<文件最大容量>|UNLIMITED}],[FILEGROWTH=<文件增量>])}[,..n]

LOGON{(NAME=<逻辑文件名称>,FILENAME='<物理文件名称>',[SIZE=<文件初始大小>],[MAXSIZE={<文件最大容量>|UNLIMITED}],FILEGROWTH=<文件增量>])}[,...n]该命令中的参数含义如下：<数据库名称>：新建数据库的名称。ON：指定显式定义用来存储数据库数据部分的磁盘文件(数据文件)。PRIMARY：在主文件组中指定文件。LOGON：指定用来存储数据库日志的磁盘文件(日志文件)。NAME：指定文件的逻辑名称。FILENAME：指定物理文件名称，其为由操作系统使用的路径和文件名。SIZE：指定文件初始大小。MAXSIZE：指定文件可增长到的最大大小。UNLIMITED：指定文件将增长到整个磁盘。FILEGROWTH：指定文件的自动增量。1.定义数据库5.3.1定义数据库与数据表（2）用完整语句定义数据库【例5.2】用完整SQL语句创建test数据库。要求：数据库test中包含一个数据文件，逻辑文件名为test_data，物理文件名为test_data.mdf，文件初始容量为10MB，最大容量为400MB，文件容量递增值为5MB；事务日志文件的逻辑文件名为test_log，物理文件名为test_log.ldf，文件初始容量为5MB，最大容量为100MB，文件容量递增值为10%。CREATEDATABASEtest/*数据库名*/ONPRIMARY/*主数据文件*/(NAME=test_data,/*数据文件逻辑名*/FILENAME='D:\test_data.mdf',/*数据文件磁盘(物理)名*/SIZE=10MB,/*数据文件初始大小*/MAXSIZE=400MB,/*数据文件增长的上限*/FILEGROWTH=5MB/*文件增量*/)

LOGON(NAME=test_log,/*事务日志文件逻辑名*/FILENAME='D:\test_log.ldf',/*事务日志文件磁盘(物理)名*/SIZE=5MB,/*事务日志文件初始大小*/MAXSIZE=unlimited,/*事务日志文件增长的上限*/FILEGROWTH=10%/*文件增量*/)1.定义数据库5.3.1定义数据库与数据表用SQL语句创建一个“学生”数据库，包含一个数据文件，逻辑文件名为student_data，物理文件名为student_data.mdf，初始容量为5MB，最大容量为1000MB，递增值为10MB；事务日志文件逻辑文件名为student_log，物理文件名为student_log.ldf，初始容量为5MB，最大不限，递增值为10MB。课堂练习18分钟1.定义数据库5.3.1定义数据库与数据表创建表即是定义表所包含的列的结构，包括列的名称、数据类型、约束等。基本语法格式：

CREATETABLE<表名>（<列名1><数据类型1>，<列名2><数据类型2>，<列名3><数据类型3>，……<列名n><数据类型n>）

<表名>是所定义的基本表的名字<列名>是表中所包含的列的名字2.定义数据表

5.3.1定义数据库与数据表创建学生表、课程表、选课表，其结构见P107。CREATETABLE学生表（ 学号INT，姓名CHAR（8）， 性别CHAR（1）， 班级CHAR（10）， 年龄INT）课堂练习2：新建一个“通讯录”表，包括“编号”、“姓名”和“联系电话”三列。CREATETABLE课程表（ 课程号INT，课程名CHAR（20）， 教师CHAR（8）， 周课时数float， 备注varchar（50）

）5分钟2.定义数据表

5.3.2修改数据库与数据表在创建数据库之后，在实际操作中，常常涉及修改数据库的操作，如修改数据库的名称、修改数据文件的大小、添加数据文件、清理数据文件等。用SQL语句修改数据库的完整语法格式如下：1.修改数据库USEMASTERGOALTERDATABASE<数据库名称>{MODIFYNAME=<新数据库名称>MODIFYFILE=<数据文件名称>ADDFILE<文件规范>[,…n][TOFILEGROUP{<文件组名称>}]ADDLOGFILE<文件规范>[,..n]REMOVEFILE<文件规范>ADDFILEGROUP<文件组名称>MODIFYFILEGROUP<文件组名称>{<文件组属性>}}GO5.3.2修改数据库与数据表语法说明如下:MODIFYNAME：重命名数据库。MODIFYFILE：修改数据文件大小。ADDFILE：向数据库文件组添加新的数据文件。ADDLOGFILE：向数据库添加事务日志文件。REMOVEFILE：从SQLServer实例中删除逻辑文件说明并删除物理文件。ADDFILEGROUP：向数据库添加文件组。MODIFYFILEGROUP：修改某一个文件组的属性。注意：其中USEMASTER语句即选择MASTER数据库进行创建新的数据库，不过默认情况下都是在MASTER数据库下创建的，如果不是的情况，用USE<数据库名称>，即可定位到想要的数据库中，而GO语句为执行命令，一般与USE配合使用。1.修改数据库5.3.2修改数据库与数据表（1）重命名数据库语法格式如下：ALTERDATABASE<原数据库名称>MODIFYNAME=<新数据库名称>【例5.4】在SQLServer的数据库目录下将test数据库重命名为test_new。USEMASTERGOALTERDATABASEtest/*原数据库名称*/MODIFYNAME=test_new/*新数据库名称*/GO1.修改数据库5.3.2修改数据库与数据表（2）修改数据库的文件容量语法格式如下：ALTERDATABASE<数据库名称>MODIFYFILE(NAME=<数据库文件名>,MAXSIZE=<文件最大容量>)【例5.5】在SQLServer的数据库目录下修改test_new数据库数据文件“test_data”的最大容量为500MB。USEMASTERGOALTERDATABASEtest_newMODIFYFILE/*修改用MODIFY*/(NAME='test_data',/*要修改的数据文件*/MAXSIZE=500MB)/*必须大于当前容量*/GO1.修改数据库5.3.2修改数据库与数据表（3）添加数据文件语法格式如下：ALTERDATABASE<数据库名称>ADDFILE(NAME=<逻辑文件名称>,FILENAME=<物理文件名>,SIZE=<初始大小>,MAXSIZE=<最大容量>,FILEGROWTH=<文件自动增量>)TOFILEGROUP=<新文件组>【例5.6】在SQLServer的数据库目录下修改test_new数据库，添加两个辅助数据文件,分别为“test_data1.ndf”和“test_data2.nfd”，大小均为5MB，容量上限均为100MB，并将这两个辅助文件添加到newgroup1文件组中。USEMASTERGOALTERDATABASEtest_newADDFILE/*新增辅助数据文件*/(NAME='test_data1',/*新增第一个辅助数据文件*/FILENAME='D:\test_data1.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB),(NAME='test_data2',/*新增第二个辅助数据文件*/FILENAME='D:\test_data2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)TOFILEGROUPnewgroup1/*将上述两个辅助文件添加到newgroup1文件组中*/GO1.修改数据库5.3.2修改数据库与数据表（4）清理数据文件语法格式如下：ALTERDATABASE<数据库名称>REMOVEFILE<需清理的数据文件名称>【例5.7】在SQLServer的数据库目录下修test_new数据库，删除数据库文件test_data2。USEMASTERGOALTERDATABASEtest_newREMOVEFILEtest_data2/*删除用REMOVE*/GO1.修改数据库5.3.2修改数据库与数据表为“学生”数据库添加两个数据文件，其中一个逻辑文件名为student_data1，磁盘文件名为student_data1.ndf，文件初始容量为5MB，最大容量为50MB，文件容量递增值为1MB，另一个逻辑文件名为student_data2，磁盘文件名为student_data2.ndf，文件初始容量为5MB，最大容量为无限，文件容量递增值为1MB。课堂练习310分钟1.修改数据库5.3.2修改数据库与数据表2.修改数据表（1）修改列类型的语法格式为：ALTERTABLE<表名>ALTERCOLUMN<列名>

<新数据类型>（2）添加列的语法格式为：ALTERTABLE<表名>ADD<新列名>

<数据类型>（3）删除列的语法格式为：ALTERTABLE<表名>DROPCOLUMN<列名>修改表使用ALTERTABLE语句实现，可以增加列、删除列、修改列的定义、定义主码和外码，也可以添加和删除约束。5.3.2修改数据库与数据表1）为选课表添加“课程类别”，类型为字符串，长度为4ALTERTABLE选课表ADD课程类别char(4)修改表示例2）将“课程类别”列的类型改为char（6）ALTERTABLE选课表ALTERCOLUMN课程类别char(6)2.修改数据表5.3.2修改数据库与数据表3）删除课程表的备注列ALTERTABLE选课表DROPCOLUMN备注修改表示例2.修改数据表5.3.2修改数据库与数据表课堂练习4：①修改“编号”列类型为Varchar(10)②给通讯录表增加一列，列名为“性别”，类型为Char(1)③删除通讯录表“联系电话”列10分钟2.修改数据表5.3.2修改数据库与数据表1）修改“编号”列类型为Varchar(10)ALTERTABLE通讯录ALTERCOLUMN编号varchar(10)课堂练习4

参考答案2）给通讯录表增加一列，列名为“性别”，类型为Char(1)ALTERTABLE通讯录

ADD性别char(1)3）删除通讯录表“联系电话”列ALTERTABLE通讯录DROPCOLUMN联系电话2.修改数据表5.3.3删除数据库与数据表对于不再使用数据库可以将其删除，删除数据库之后，如果没有备份，那么这种删除就不能再恢复了。删除数据库可以使用DROPDATABASE的语句实现，该语句的语法格式如下：

DROPDATABASE

<数据库名>1.删除数据库【例5.9】要删除test_new数据库DROPDATABASEtest_new课堂练习5：删除“学生”数据库5.3.3删除数据库与数据表2.删除数据表删除表就是将指定的表中的数据和表的结构从数据库中永久性地除去。表删除之后，如果没有备份，那么这种删除就不能再恢复了。删除表可以使用DROPTABLE语句实现，该语句的语法格式如下：

DROPTABLE<表名>5.3.3删除数据库与数据表【例5.10】要删除课程表DROPTABLE课程表课堂练习6：删除通讯录表注意：在删除一张表时，如果有其它对象依赖于这张表（比如存在外键引用时），那么这张表就无法删除。2.删除数据表数据操作语句5.4数据操作语句5.4.1插入数据5.4.2更新数据5.4.3删除数据数据操作语句（1）插入单个元组（insert）（2）插入子查询的结果集（带select的insert）5.4.1插入数据数据操作语句（1）插入单个元组（insert）INSERT语句用来新增一个符合表结构的数据行。INSERT语句的语法格式：INSERT[INTO]<表名>[（<列名列表>）]VALUES（值列表）注a：在into子句中若没有指明任何列名，则在values子句中必须在每个列上均有值，并且要与表中属性的逻辑顺序对应。例1：在选课表中插入一行，以下哪个是对的？insertinto选课表values(‘010’,‘C3’,70)insertinto选课表values(‘010’,‘C3’)insertinto选课表values('C3'，'010'，70)逻辑顺序对应5.4.1插入数据数据操作语句注b：如果某些列在into子句中没有出现，则新插入的记录在这些列上取空值，但如果这些列在表定义是定义为NOTNULL，则不能在into子句中省略。例2：在选课表中插入以下数据，哪个语句是正确的？insertinto选课表(学号,课程号)values('011','C4')insertinto选课表(学号,成绩)values(‘011’,70)主码不为空（1）插入单个元组（insert）INSERT语句用来新增一个符合表结构的数据行。INSERT语句的语法格式：INSERT[INTO]<表名>[（<列名列表>）]VALUES（值列表）5.4.1插入数据数据操作语句注c：INTO子句中列名与VALUES子句中的常量要求逻辑顺序一致。例3：在选课表中插入以下数据，哪个语句是正确的？insertinto选课表(学号,课程号,成绩)values('011','D1',87)insertinto选课表(学号,课程号,成绩)values(87,'D1','011')逻辑顺序一致5.4.1插入数据（1）插入单个元组（insert）INSERT语句用来新增一个符合表结构的数据行。INSERT语句的语法格式：INSERT[INTO]<表名>[（<列名列表>）]VALUES（值列表）数据操作语句（2）插入子查询的结果集（带select的insert语句）可以先用SELECT检索出要插入的数据值，再插入到指定的表中。语法格式：insertinto

<表名>[(<属性列1>[,<属性列2>…)](子查询)select[(<属性列1>[,<属性列2>…)列名1，列名2…from

表名where

条件例4：求每个系的系名和平均年龄，并要求将结果存入系平均年龄表中。（注：已存在系平均年龄表（所在系，平均年龄））insertinto系平均年龄表

select

所在系,avg(年龄)from学生表

groupby所在系5.4.1插入数据数据操作语句例5：假设有一张和学生表定义完全一样的表，表名为“学生表1”，要求在其中插入学生表中所有的男生信息。（注：假设学生表1已存在。）insertinto学生表1

select

*from学生表

where

性别=‘M’5.4.1插入数据（2）插入子查询的结果集（带select的insert语句）可以先用SELECT检索出要插入的数据值，再插入到指定的表中。语法格式：insertinto

<表名>[(<属性列1>[,<属性列2>…)](子查询)select[(<属性列1>[,<属性列2>…)列名1，列名2…from

表名where

条件数据操作语句课堂练习7学生表（学号，姓名，性别，班级，年龄）课程表（课程号，课程名，教师，周课时数，备注）选课表（学号，课程号，成绩）1.要求往学生表中插入一行数据：学号为999999，姓名为张三，性别为男，其它情况未知。2.假设有一张和学生表定义完全一样的表，表名为“学生表1”，要求在其中插入学生表中所有的女生且年龄大于20岁的学生信息。（请先定义学生表1）3.为GZ02计6班未选4号课程的学生补选4号课程。（提高）问题：数据操作语句语句格式：update<表名>set

<列名1>=<表达式1>[,<列名2>=<表达式2>][,…n][where<条件>]注a：如果无where子句，则表示修改表中全部元组例5：将学生表中每个学生年龄加1update学生表set年龄=年龄+15.4.2更新数据数据操作语句语句格式：update<表名>set

<列名1>=<表达式1>[,<列名2>=<表达式2>][,…n][where<条件>]例6：将“高等数学”的成绩加5分注b：where子句中可以嵌入子查询update选课表set成绩=成绩+5where课程号in(select课程号from课程表where课程名='高等数学')5.4.2更新数据数据操作语句语句格式：update<表名>set

<列名1>=<表达式1>[,<列名2>=<表达式2>][,…n][where<条件>]例7：要求将“软件工程”课程的周课时数改为8课时，并且把备注改为“核心课程”。update课程表set周课时数=8，备注=‘核心课程’where课程名=‘软件工程’5.4.2更新数据数据操作语句课堂练习8学生表（学号，姓名，性别，班级，年龄）课程表（课程号，课程名，教师，周课时数，备注）选课表（学号，课程号，成绩）将名字为“王玲俐”的学生的年龄修改为18岁，班级改为“GZ02计8”。要求将学号为“1031231”的学生的年龄改为与学号为“1031233”的学生的年龄一样。要求将“GZ02计5”班全班同学的成绩都加上10分。将“软件工程”这门课成绩在60分以下的学生的成绩统一修改为59分。问题：数据操作语句例8：删除所有学生的所有选课记录。deletefrom选课表语句格式：Deletefrom<表名>[where<条