数据库及其应用课件：结构查询语言SQL

结构化查询语言（SQL）本章重点是应用SQL语言创建数据库、定义表结构、输入、修改、查询、删除数据等操作，以及连接查询、嵌套查询等多种查询技术。

一是SQL语言与关系代数的关系，应该从关系代数的理论上来理解SQL语言的工作原理；二是通过上机实验了解和验证SQL的应用实例，熟练掌握SQL的各种功能。本章的实验环境是MySQL数据库系统。

2

第一节SQL语言概述

SQL是一种非过程化的语言，它与通常的高级语言不同，使用SQL时，只要说明做什么，不需要说明怎么做，具体的操作全部由DBMS自动完成。例如，要查询所有女同学的姓名，只要写出SQL语句：

SELECT姓名

FROM学生

WHERE性别=‘女’

3SQL可划分为三个组成部分：

数据定义语言（DDL）：在数据库系统中，数据库、表、视图、索引等都是对象，用于定义这些对象的SQL语句称为DDL。数据处理语言（DML）：用于插入、修改、删除和查询数据的SQL语句称为DML。数据控制语言（DCL）：用于实现数据完整性、安全性、一致性等控制的SQL语句称为DCL。MySQL数据库系统简介主要特点：跨平台Windows、UNIX、Linux和MacOS

多用户、多线程的数据库服务器支持大型数据库多种编程语言接口开放源代码价格优势4第二节Mysql的运行准备在DOS命令行窗口输入命令行执行操作，

简单、高效、快捷地操作数据库；在图形化界面中通过菜单、选项、输入命令的参数，由图形化界面软件生成命令并执行从学习SQL语言的目的考虑选择第一种方式，在Windows平台的命令行窗口输入和执行SQL语句。5第二节Mysql的运行准备使用MySQL数据库需要两个步骤：启动MySQL服务器。连接（登录）MySQL服务器。6一、利用Windows服务管理器

启动MySQL服务器图4.1Windows服务窗口启动MySQL服务器二、连接和断开MySQL服务器方法一在“开始”菜单中搜索到“MySQL8.0CommandLineClient”命令，单击即可打开MySQL客户端命令窗口，输入正确的密码，按回车键，若出现mysql>提示符，则表示已正确连接（登录）MySQL服务器。8方法1：在“开始”菜单中搜索单击“MySQL8.0CommandLineClient”命令，打开MySQL客户端命令窗口，输入正确的密码，按回车键，出现mysql>提示符，连接（登录）MySQL服务器。9方法2在Windows的运行窗口

输入

“mysql-uroot-p”命令”命令1、MySQL安装时在创建的bin目录下存放多个MySQL的应用程序，这些程序运行时，需要给出绝对路径。2、设置Windows的环境变量Path为C:\ProgramFiles\MySQL\MySQLServer8.0\bin

3、在键盘上同时按下【Windows旗帜图标+R】，弹出运行窗口，在【打开(O)】的输入框中键入命令：mysql-uroot-p，如图4.4所示。101、修改Windows的环境变量Path

11图4.3设置环境变量窗口2、打开Windows的运行窗口

输入

“mysql-uroot-p”命令”命令在键盘上同时按下【Windows旗帜图标+R】，弹出运行窗口，在【打开(O)】的输入框中键入命令：mysqluroot-p

12打开MySQL客户端命令窗口13

MySQL命令行客户端窗口输入SQL语句第三节创建数据库SQL语句格式：CREATEDATABASE<数据库名>；例4.1创建一个名称为MYTEST数据库。14二、选择数据库

每个用户每次只能对一个数据库进行操作，该数据库称为当前数据库。

15

SQL语句格式：USE<数据库名>；例4.2选择MYTEST为当前数据库。

三、显示数据库16SQL语句格式：SHOWDATABASES;

例4.3查看创建的

数据库名称。

四、删除数据库17SQL语句格式：DROPDATABASE<数据库名>;

例4.4删除MYTEST数据库。

第四节创建表（案例）

盛达公司数据库的10个关系模式如下：商品（商品编码，品名，颜色，花型，规格，库存数量，最高库存，最低库存，参考价格）供应商（厂商编号，厂商名称，联系人，地址，电话号码）客户（客户编号，客户名称，联系人，地址，电话号码）职工（职工号，姓名，性别，出生年月，职务，工资，身份证号）入库单（入库单号，厂商编号，送货日期，来单编号，职工号）入库明细（入库单号，商品编码，进价，入库数量）订单（订单号，签单日期，客户编号，送货地址，收货人，职工号）订单明细（订单号，商品编码，单价，订购数量）出库单（出库单号，发货日期，订单号，送货地址，收货人，职工号）出库明细（出库单号，商品编码，发货数量）18一、创建表（CREATETABLE）SQL语句基本格式：CREATETABLE<表名>（<字段名1><数据类型>，<字段名2><数据类型>，…，<字段名n><数据类型>，PRIMARYKEY(<主键>)，FOREIGNKEY(<外键>)REFERENCES表名(<外键>));191、常用的数据类型数据类型类型名称说

明SMALLINT短整型数有符号数(-32768~32767)，无符号数(0~65535)INT(INTEGER)整型数有符号数(-2147483648~2147483647)，无符号数(0~4294967295)DEC(M,D)(DECIMAL)精准数值M是数值的全部位数（不含小数点），D则是小数的位数FLOAT单精度浮点数科学计数法,例如s=1.5×1011，称1.5为尾数(有效数字),11是指数，记作1.5E11。单精度浮点数的范围：+(-)3.402823466E+38DOUBLE双精度浮点数双精度浮点数的范围：+(-)1.7976931348623157E+308,+(-)2.2250738585072014E-308

CHAR(N)定长字符串0-255字节VARCHAR变长字符串0-65535字节TEXT长文本数据0-65535字节DATE日期YYYY-MM-DDTIME时间HH:MM:SSDATETIME日期时间YYYY-MM-DDHH:MM:SS202、域完整性约束定义

域完整性约束是对属性值有效性的约束，包括在关系模式定义中规定的属性类型、宽度、小数位，属性是否可以取NULL值、缺省值（DEFAULT）、唯一值（UNIQUE），以及基于属性的域检查子句（CHECK）等。213、主键子句（PRIMARYKEY）主键子句PRIMARYKEY定义主键和实体完整性，约束关系的主键不允许为空值（NULL）。例4.5创建职工表，定义实体完整性定义。22例4.6创建客户表23例4.7创建商品表244.外键子句(FOREIGNKEY)外键子句FOREIGNKEY定义外键和参照完整性约束。约束外键的值必须是另一个关系中主键的有效值或空值。25删除约束短语：ONDELETERESTRICT删除约束ONDELETECASCADE级联删除ONDELETESETNULL删除置空ONDELETENOACTION删除约束更新约束短语：ONUPDATERESTRICT更新约束

ONUPDATECASCADE级联更新ONUPDATESETNULL更新置空ONUPDATENOACTION更新约束

例4.8创建订单表26例4.9创建订单明细表27创建表顺序说明

创建表的顺序是先创建被参照表，再创建参照表，即“先父后子”。例如，先创建客户和职工表，再创建订单表，因为客户和职工表是订单的“父表”；创建商品和订单表之后，才能创建订单明细表。

如果要删除已经建立的表时，则删除的顺序与创建表的顺序正相反，即“先子后父”。28二、查看表的结构信息（1）DESCRIBE<表名>;或者DESC<表名>;

DESC订单;29二、查看表的结构信息（2）SHOWCREATETABLE<表名>[\G]

302、MySQL中查看表名称SHOWTABLES;31三、修改表结构表中增加新字段，所有元组在这个新字段上都将赋值NULL。语句格式是：

ALTERTABLE<表名>ADD<列名><数据类型>;

例4.13在职工表中添加“身份证号”字段，设定字段值是唯一的，并显示新增字段。ALTERTABLE职工ADD身份证号CHAR(18)UNIQUE;DESC职工;32332、删除字段ALTERTABLE<表名>DROP<列名>;

例4.14在订单表中增加一个联系电话字段，然后再

删除这个字段。ALTERTABLE订单ADD联系电话CHAR(11);DESC订单;

ALTERTABLE订单DROP联系电话; DESC订单;34例4.14的执行结果353、修改字段的数据类型

ALTERTABLE<表名>MODIFYCOLUMN<列名><数据类型>;例4.15修改客户表“电话号码”字段的长度为CHAR(11)，并显示客户表的结构信息。ALTERTABLE客户MODIFYCOLUMN电话号码CHAR(11);DESC客户;

36例4.15的图37四、删除表DROPTABLE<表名>;例4.16在盛达数据库中，已经创建了客户、职工、商品、订单和订单明细、供应商、入库单、入库明细、出库单和出库明细10个表，并且定义了参照完整性。如果要删除订单表，则必须先删除订单明细表，否则将违反参照完整性约束，系统拒绝执行该删除操作。DROPTABLE订单明细;DROPTABLE订单;DROPTABLE客户;38五、创建索引关键字地址指针10221210462124212234301254021339学

号姓

名性别出生年月系编号1022田平平女08/05/19801012124郭黎明男03/04/19811024021何明慧女04/12/19821041223姜明明男12/05/19801013012何漓江男10/05/19791032104康纪平女03/04/1981102索引表基本表创建索引的SQL语句SQL语句格式：CREATE[UNIQUE]<索引名称>ON<表名>(<字段名1>,<字段名2>,……);例4.18

在职工表的身份证号字段上创建唯一索引，索引名称为SFZNO。在职工表的身份证号字段上创建唯一索引，索引名称为SFZNO。语句格式如下：在职工表的身份证号字段上创建唯一索引，索引名称为SFZNO。语句格式如下：式如下：CREATEUNIQUEINDEXSFZNOON职工(身份证号);40六、删除索引1、ALTERTABLE<表名>DROPINDEX<索引名>;2、DROPINDEX<索引名>ON<表名>;例4.19删除职工表上创建的索引SFZNO。ALTERTABLE职工DROPINDEXSFZNO;或者DROPINDEXSFZNOON职工;41第五节数据操作

一、插入数据第一种格式是指定字段名和要插入的值INSERTINTO<表名>（字段名列表）VALUES(<值1>,<值2>,.....,<值N>);INSERTINTO职工(职工号,姓名,性别,出生年月,职务,工资,身份证号)VALUES('E1','李树生','男','1981-3-12','经理',8800.00,);SELECT*FROM职工;42第二种插入数据的格式将VALUES子句换成一个查询语句INSERTINTO<表名>（<列名表>）SELECT<列名表>FROM<表名>WHERE<条件表达式>（1）创建一个名单表，SQL语句如下：

CREATETABLE名单(姓名CHAR(8),性别CHAR(2));（2）将职工表中姓名和性别两列数据拷贝到名单表中。在命令行窗口输入下列语句：INSERTINTO名单(姓名,性别)SELECT姓名,性别FROM职工;（3）输入下列语句查看操作的结果，如图4.23所示。

SELECT*FROM名单;43一个查询语句第三种插入数据的格式INSERTINTO<表名>（<列名表>）VALUES(<值1>,<值2>,.....,<值N>)，(<值1>,<值2>,.....,<值N>)，……，(<值1>,<值2>,.....,<值N>);INSERT

INTO职工(职工号,姓名,性别)

VALUES('E3','韩康健','男'),('E4','何乃云','女'),('E5','王建南','男');SELECT*FROM职工;MySQL的扩展功能，在表中一次插入多行数据44一个查询语句45第二种格式是将VALUES子句换成一个查询语句

SQL的语法格式：

INSERTINTO<表名>（<列名表>）例4.23执行的结果如图4.24所示二、更新数据MySQL数据更新语句的格式是：UPDATE<表名>SET<列名1>=<新值1>，<列名2>=<新值2>，……，<列名n>=<新值n>WHERE<条件表达式>；UPDATE职工SET出生年月='1980-10-18',职务='销售员’,工资=4500,身份证号=WHERE职工号='E2';SELECT*FROM职工WHERE职工号='E2';46例4.25将订单明细表中订单号为“D2”、商品编码为“P2”的订购数量增加10%.UPDATE订单明细SET订购数量=订购数量*1.1WHERE订单号='D2'

AND商品编码='P2';47三、删除数据DELETEFROM<表名>WHERE<条件表达式>DELETEFROM职工WHERE职工号='E3';

SELECT*FROM职工;48第六节SQL查询语句、SQL查询语句的基本格式SELECT<列名表>包括字段名、函数、表达式等FROM表名

数据来源（表名）、单表或多表WHERE<条件表达式>;选择行的条件表达式491、SELECT子句的参数1）列名表SELECT姓名,工资

FROM职工WHERE工资>=5000;2）所有字段SELECT*FROM职工WHERE职务='经理’;3）表达式SELECT订单号,商品编码,单价,订购数量,单价*订购数量AS金额

FROM订单明细WHERE订单号='D2’;4）函数SELECT姓名,YEAR(NOW())-YEAR(出生年月)AS年龄

FROM职工WHERE性别='男';2、WHERE子句中条件表达式的运算符算术运算符

：加减乘除乘方，先乘除后加减。比较运算符

：<、<=、>、>=、=、<>，用于字符串表达式、算术表达式，以及特殊的类型（如日期类型）的比较。运算的结果是逻辑值真（T）或假（F），表达式成立为真，否则为假。逻辑运算符：AND（逻辑与）、OR（逻辑或）和NOT（逻辑非），可将多个比较表达式连接起来，构成复杂的逻辑表达式，表示复杂的条件，运算的结果是逻辑值真(T)或假(F)。51三类运算符的级别括号最优先。算术运算符

比较运算符逻辑运算符高低逻辑运算符NOT、AND、OR高低例如，下列表达式的计算顺序：WHERE((YEAR(NOW())-YEAR(出生年月))>40AND性别='男')OR((Year(NOW())-YEAR(出生年月))>35AND性别='女');52WHERE子句的示例SELECT姓名,工资FROM职工WHERE工资>=6000;SELECT姓名FROM职工WHERE出生年月>="1980-1-1";SELECT姓名FROM职工WHERE出生年月>='1980-1-1'AND性别='男’;SELECT姓名,性别,YEAR(NOW())-YEAR(出生年月)AS年龄FROM职工WHERE((YEAR(NOW())-YEAR(出生年月))>40AND性别='男')OR((Year(NOW())-YEAR(出生年月))>35AND性别='女');533、FROM子句指定查询语句的数据来源(表名)。如果是多个表，则该查询语句是连接，连接条件可以用两种格式表示。（1）在FROM子句中给出表与表之间的连接条件(§4.7节)（2）在WHERE子句中给出连接条件。如下例：SELECT订单号,客户名称,姓名,签单日期FROM客户,订单,职工

WHERE客户.客户编号=订单.客户编号AND职工.职工号=订单.职工号

AND签单日期>='2022-03-01';543、FROM子句SQL允许在FROM子句中使用表的别名（简名），可用别名代替表名，上述SQL语句可写成：SELECT订单号,客户名称,姓名,签单日期FROM客户C,订单D,职工EWHEREC.客户编号=D.客户编号ANDD.职工号=E.职工号AND签单日期>='2022-3-1';55

二、字符串操作字符串匹配符有：

百分号%表示任意字符串。

下划线_表示任何一个字符字符串操作符有：LIKE

NOTLIKE内置函数trim示例：SELECT姓名FROM职工WHERE姓名LIKE'李%’;SELECT姓名

FROM职工

WHERE职务NOTLIKE'经%’;SELECT姓名FROM职工WHEREtrim(姓名)LIKE'李_生';56三、几个特殊的运算符BETWEENAND在某个范围之间。NOTBETWEENAND不在某个范围之间。IN指定字段的值属于指定集合中的一个元素。NOTIN指定字段的值不属于指定集合的元素。57特殊运算符应用示例SELECT*FROM职工

WHERE工资BETWEEN3000AND5000;SELECT*FROM职工WHERE工资>=3000AND工资<=5000;SELECT*FROM职工

WHERE工资NOTBETWEEN3000AND5000;SELECT*FROM职工

WHERE工资<3000OR工资>5000;SELECT客户名称,签单日期

FROM客户C,订单DWHEREC.客户编号=D.客户编号AND签单日期IN('2022-2-12','2022-5-13’);SELECT客户名称,签单日期FROM客户C,订单DWHEREC.客户编号=D.客户编号AND(签单日期='2022-2-12'OR签单日期='2022-5-13');58四、排序（ORDERBY子句）SELECT<列名表>FROM表名WHERE<条件表达式>ORDERBY<列名表>[DESC][ASC]其中：DESC表示降序，ASC表示升序，缺省为升序。例4.22按照签单日期升序显示订单号、签单日期、客户名称、业务员姓名。SELECT订单号,签单日期,客户名称,姓名FROM客户C,订单D,职工EWHEREC.客户编号=D.客户编号ANDE.职工号=D.职工号ORDERBY签单日期;59按时间排序的查询结果60五、并操作（UNION语句）MySQL实现并运算的语句UNION。例4.43列出入库明细和订单明细表中，商品编码为“P1”的入库单号（或订单号）、商品编码和单价。(分析差价和利润）SELECT入库单号AS单号,商品编码,进价AS价格FROM入库明细WHERE商品编码='P1'UNIONSELECT订单号,商品编码,单价

FROM订单明细WHERE商品编码='P1';6162六、聚合函数631、求平均值函数

SELECTAVG(工资)AS平均工资WHERE职务=‘销售员’;

2、求总和函数

SELECTFORMAT(SUM(订购数量*单价),2)AS总金额

FROM订单D,订单明细M

WHERED.订单号=M.订单号AND客户编号=‘C2’;

3、求最大值和最小值函数

SELECTMAX(工资)AS最高工资,MIN(工资)AS最低工资

FROM职工;

SELECTMIN(出生年月)AS年龄最大,

MAX(出生年月)AS年龄最小

FROM职工;

64计数函数有3种格式：1、COUNT(*)统计所有行数，包括含有空值的行。2、COUNT(字段名)计算表中字段值非空值的行数。3、COUNT(DISTINCT(字段名))计算表中字段值

唯一且非空值的行数。65示例：SELECTCOUNT(*)AS职工人数FROM职工;SELECTCOUNT(身份证号)AS有身份证人数FROM职工;SELECTCOUNT(DISTINCT职务)AS种类数目FROM职工;七、分组GROUPBY、HAVING子句SELECT性别,COUNT(性别)AS人数FROM职工GROUPBY性别;SELECT商品编码,MAX(订购数量)AS最高量,AVG(订购数量)AS平均订购量,MIN(订购数量)AS最低量FROM订单明细GROUPBY商品编码;66SELECT<字段列表>FROM表名

WHERE<条件表达式>GROUPBY<分组表达式>HAVING<筛选条件表达式>SELECT客户编号,SUM(单价*订购数量)AS总金额FROM订单D,订单明细MWHERED.订单号=M.订单号AND签单日期BETWEEN'2022-3-1'AND'2022-12-31'GROUPBY客户编号HAVINGSUM(单价*订购数量)>18000;例4.51检索在2022年1月1日-2022年12月31期间，客户订购商品的总金额超过18000元的客户编号和总金额。八、空值（NULL）1.测试空值运算符（ISNULL和ISNOTNULL）SELECT职工号,姓名FROM职工WHERE身份证号ISNULL;SELECT职工号,姓名FROM职工WHERE身份证号ISNOTNULL2.空值的处理原则(1)算术表达式中含有NULL，运算结果是NULL。(2)比较运算中含有NULL，比较的结果视为假（false）。(3)SQL规定，比较运算中含有NULL,结果是“未知(unknown)”(4)几乎其他所有情况下，将unknown作为false。683、聚合函数对NULL的处理原则(1)SUM、AVG、MIN、MAX函数对参数中的空值（NULL）忽略不计。若所有参数值都是空值（NULL），则函数的返回值是NULL。(2)COUNT函数的参数是“*”，返回表的所有行数，包括含有空值（NULL）的行；若参数是一个字段名，则该字段为空值（NULL）行忽略不计；若所有参数值都是空值（NULL），则函数的返回值是0。69第七节SQL的连接查询

一个查询同时涉及2个以上表，称为连接查询。连接查询是关系数据库最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自连接查询、外连接查询和复合条件查询等。SQL语句中定义连接条件的方法有两种方法：WHERE子句中定义连接条件FROM子句中定义连接条件

70定义连接条件的两种方法SELECT订单号,客户名称,姓名FROM客户,订单,职工WHERE客户.客户编号=订单.客户编号AND职工.职工号=订单.职工号AND签单日期>='2022-1-1’;SELECT订单号,客户名称,姓名FROM(客户CINNERJOIN订单DON(C.客户编号=D.客户编号))INNERJOIN职工EON(E.职工号=D.职工号)WHERE签单日期>='2022-1-1';71连接操作的类型72一、内连接

内连接（INNERJOIN）也称为等值连接。它是以连接属性值相等为条件的连接，在连接的结果中，仅包含两个关系笛卡儿积中连接属性值相等的元组，且不消除重复的属性。

FROMRINNERJOINS[ON(<连接条件>)]例4.55内连接示例SELECT供应商.*,客户.*FROM供应商INNERJOIN客户ON厂商名称=客户名称;73二、自然连接自然连接以公共属性值相等为条件的连接，且消除重复列。例4.56自然连接示例SELECT客户名称,订购数量

FROM客户AINNERJOIN订单BON(A.客户编号=B.客户编号)INNERJOIN订单明细CON(B.订单号=C.订单号)INNERJOIN商品DON(C.商品编码=D.商品编码)

WHERE规格='245’;三、左外连接

左外连接（LEFTOUTERJOIN）的结果中，除了内连接结果之外还包括左表不相匹配的元组，且其对应的右表值为空值。FROM左表LEFTOUTERJOIN右表ON(<连接条件>)

例4.57左外连接示例SELECTA.客户编号,客户名称,订单号FROM客户ALEFTOUTERJOIN订单BON(A.客户编号=B.客户编号);75左外连接示例76四、右外连接

右外连接（RIGHTOUTERJOIN）的结果中，除了内连接结果之外还包括右表不相匹配的元组，且其对应的左表值为空值。FROM左表RIGHTOUTERJOIN右表ON(<连接条件>)

例4.58右外连接示例SELECT