MySQL数据库项目式教程

视图中包含了SELECT查询的结果，因此视图的创建基于SELECT语句和已存在的数据表。视图可以建立在一张表上，也可以建立在多张表上。在 MySQL中，创建视图使用 CREATEVIEW 语句，其基本语法格式为：CREATE[ORREPLACE][ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view name[(column list)]AS select statement[WITH[CASCADED]LOCAL] CHECKOPTION ]在上述语法格式中，每个部分的含义如下。CREATE；表示创建视图的关键字，上述语句能创建新的视图。ORREPLACE：如果给定了此子句，表示该语句可以替换已有视图。ALGORITHM：可选项，表示视图选择的算法。UNDEFINED：表示MySQL将自动选择所要使用的算法。MERGE：表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。TEMPTABLE：表示将视图的结果存入临时表，然后使用临时表执行语句。view name：表示要创建的视图名称。column list：可选项，表示字段名清单。指定了视图中各个字段名，默认情况下，与SELECT语句中查询的字段名相同。AS：表示指定视图要执行的操作。select statement：一个完整的查询语句，表示从某个表或视图中查出某些满足条件的记录，将这些记录导入视图中。W1THCHECKOPTION ：可选项，表示创建视图时要保证在该视图的权限范围之内。CASCADED：可选项，表示创建视图时，需要满足与该视图有关的所有相关视图和表的条件，该参数为默认值。LOCAL：可选项，表示创建视图时，只要满足该视图本身定义的条件即可。该语句要求具有针对视图的 CREATEVIEW 权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有ORREPLACE子句，必须在视图上具有DROP权限。视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时应将名称指定为db name，view name。

1.创建视图

创建视图前，应该知道它的一些限制。视图创建和使用最常见的规则和限制如下。①与表一样，视图必须唯一命名(不能给视图取与别的视图或表相同的名称)。②对于可以创建的视图数目没有限制。③为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。④视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。⑤ORDERBY可以用在视图中，但如果在该视图检索数据SELECT中也含有ORDERBY，那么该视图中的ORDERBY将被覆盖。⑥视图不能索引，也不能有关联的触发器或默认值。⑦视图可以和表一起使用。例如，编写一条连接表和视图的SELECT语句。

2.视图的规则和限制

【任务5.1】   使用语句创建视图

用户在视图创建工具中新建视图，首先必须添加表到视图，从左侧数据库对象窗口拖动数据表到图表设计窗口或双击相应的数据表。

1.添加数据表

用户在视图创建工具界面的图表设计窗口中，选中相应数据表对象的字段名复选框；也可以选中对象标题的复选框，即可包含所有字段。

2.选择字段名

从视图创建工具界面的图表设计窗口中移除对象，单击对象标题上的关闭按钮。

3.删除对象

在视图创建工具中添加表别名，用户只需在图表设计窗口双击表名并输入别名。

4.添加表别名

通过两个字段连接数据库对象，从一个对象列表拖动一个字段到另一个对象列表，一条线将出现在连接的字段之间。删除某些对象的全部连接，单击对象别名旁的“-”按钮。转到语法窗口改变连接的关系，单击运算符并在弹出的菜单中选择属性项目，还可以通过列表(=、<>、<、<=、>、>=)改变关系条件，最后单击“确定”按钮进行保存。除此之外，还可以改变连接的类型。

5.设置表间关系

在图表设计窗口选择的字段将显示在语法窗口，设置它们的显示顺序，以及使用和修改视图输出字段。<Distinct>：相同的记录不会包含在查询结果中。<func>：为每个栏位设置聚集函数(SUM、MAN、MIX、AVG、COUNT)。<别名>：改变输出查询字段名。

6.设置输出字段

添加一个查询条件，在语法窗口的WHERE子句单击“<按这里添加条件>”单击“<-->，<-->”中的“<-->”从当前所建查询中添加的所有数据表的字段列表中选择一个字段。定义条件，在“编辑”选项卡中输入值，单击“=”设置条件运算符。

7.设置条件

可以从语法窗口的GROUPBY子句为组查询记录设置条件，它们以设置条件相同的方法设置，条件将会包含在当前的HAVING语句中。

8.设置组条件

可视化创建工具可以从语法窗口ORDERBY子句设置排序的方法，单击ASC或DESC可改变排序方向。

9.设置排序准则

LIMIT子句用于在指定的范围限制查询记录，可以用它来显示前X条记录，格式为LimitX，X参数指的是返回从0条开始的X条记录行；也可以显示由X~Y之间的记录，格式为Limit X，Y，X参数指定第一个返回记录行的偏移量(第一条记录是0)，Y参数指定返回记录行的最大数目(显示多少条记录)。

10.设置限制准则

【任务5.2】   使用图形管理工具创建视图

在MySQL中，使用 CREATEORREPLACEVIEW 语句修改视图，其语法格式为：CREATE[OR REPLACE][ALGORITHM = {UNDEFINED |MERGE|TEMPTABLE}]VIEW view name[(column list)]AS select statement[WITH[CASCADED | LOCAL] CHECKOPTION ]可以看到，修改视图的语句和创建视图的语句是完全一样的。如果视图已经存在，那么使用修改语句对视图进行修改；如果视图不存在，那么将创建一个新视图。

1.使用 CREATEORREPLACEVIEW 语句修改视图

ALTER语句是MySQL提供的另外一种修改视图的方法，其修改视图的语法格式为：ALTER[ALGORITHM= { UNDEFINED | MERGETE | MPTABLE }]VIEW view name[(column list)]ASSELECT statement[WITH[ CASCADED | LOCAL ] CHECKOPTION ]这个语法中的关键字与前面创建视图的关键字是一样的，这里不再介绍。

2.使用ALTER语句修改视图

【任务5.3】   修改视图

在MySQL中，可以使用UPDATE语句对视图中原有的数据进行更新。

1.使用UPDATE语句更新视图

在MySQL中，可以使用INSERT语句对视图中的基本表插入一条记录。

2.使用INSERT语句更新视图

在MySQL中，可以使用DELETE语句对视图中的基本表删除部分记录。需要注意的是，尽管更新视图有多种方式，但并非所有情况下都能执行视图的更新操作。当视图中包含如下内容时，视图的更新操作将不能被执行。(1)视图中不包含基本表中被定义为非空的列。(2)在定义视图的SELECT语句后的字段列表中使用了数学表达式。(3)在定义视图的SELECT语句后的字段列表中使用了聚合函数。(4)在定义视图的SELECT语句中使用了DISTINCT、UNION、LIMIT、GROUPBY或HAVING子句。

3.使用DELETE语句更新视图

【任务5.4】   利用视图更新数据表

当视图不再需要时，可以将其删除，删除视图时，只会删除视图的定义，不会删除数据、删除一个或多个视图可以使用DROPVIEW语句，其基本语法格式为：font color=\"#ff0000\

【任务5.5】   删除视图

项目五 创建与使用视图

变量是指在程序运行过程中其值可以改变的量，包括用户变量、系统变量和局部变量。(1)用户变量。用户可以在PL/SQL中使用自己定义的变量，这样的变量称为用户变量。可以先在用户变量中保存一个数据，然后在以后的语句中引用该变量：这样就可以将数据从一条语句传递到另一条语句。用户变量在使用前必须定义和初始化，如果使用没有初始化的变量，其值为NULL。定义和初始化一个用户变量可以使用SET语句，其语法格式为：font color=\"#ff0000\

(3)局部变量。在语句块(BEGIN到End之间)中定义的变量为局部变量，局部变量可以保存特定类型数据，其有效作用范围在存储过程和自定义函数的语句块中，在语句块结束以后，局部变量就失效了。MySQL的局部变量必须先声明后使用。使用DECLARE语句声明局部变量，其声明语法格式为：font color=\"#ff0000\

1.PL/SQL的变量

(1)运算符。运算符用于执行程序代码运算，会针对一个以上操作数项目进行运算。MySQL中的运算符主要有以下类型。①算术运算符。算术运算符用于对表达式执行数学运算，操作数可以是任何数值类型。MySQL中的算术运算符有+(加)、-(减)、*(乘)、/(除)、%(取模)。②赋值运算符。“=”是MySQL中的赋值运算符，可以将表达式的值赋给一个变量。③比较运算符。比较运算符用于对两个表达式进行比较，数字以浮点值进行比较，字符串以不区分大小写的方式进行比较，若表达式成立则返回1，否则返回0。MySQL中的比较运算符有= (等于)、>(大于)、<(小于)、>= (大于等于)、<=(小于等于)、<>(不等于)、!=(不等于)、<=>(相等或都等于空).④逻辑运算符。逻辑运算符用于对某些条件进行测试，以返回其真假。MySQL中的逻辑运算符有And(与)、Or(或)、Not(非)。⑤位运算符。位运算符用于对两个表达式执行二进制位操作。MySQL中的位运算符有&(位与)、Ⅰ(位或)、-(位异或)、～(位取反)、>>(位右移)、<<(位左移)。⑥一元运算符。一元运算符对一个操作数执行运算，该操作数可以是任何一种数据类型。MySQL中的一元运算符有+(正)、- (负)和~(位取反)。

(2)表达式。表达式是由操作数、运算符、分组符号(括号)和函数构成的组合，MySQL可以对表达式进行运算以获取结果，一个表达式通常可以得到一个值。表达式的值同样具有字符类型、数值类型、日期时间类型等，根据表达式的值类型可分为字符型表达式、数值表达式和日期表达式。

(3)运算符的优先级。当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的次序有时会影响所得到的运算结果。MySQL运算符优先级如表6-1所示，当一个表达式中的两个运算符有相同的优先级时，根据它们在表达式的中的位置，一般而言，一元运算符按从右到左(即右结合性)的顺序运算，二元运算符按从左到右(即左结合性)的顺序运算。                                                 表6-1 MySQL运算符优先级优先级                              运算符最高                                   ！2                                      －（负号）、～（按位取反）3                                      ＾（按位异或）4                                       *、/(DIV)、%(MOD)5                                       十、一6                                       >>、<<7                                       &8                                         |9                                       ＝（比较运算）、＜＝＞、＜、＜＝、＞、＞＝、！＝、＜＞、IN、IS、NULL、LIKE、REGEXP10                                     BETWEENAND 、CASE、WHEN、THEN、ELSE11                                     NOT12                                     &&、AND13                                     | | 、OR、XOR最低                                  ：=

2.PL/SQL的运算符及表达式

(1)BEGIN…END语句。MySQL中BEGIN…END语句用于将多个SQL语句组合成一个语句块，相当于一个整体，达到一起执行的目的。BEGIN…END语句的语法格式为：BEGIN<语句1>；<语句2>；......ENDMySQL中允许嵌套使用BEGIN…END语句。

(2)IF…THEN…ELSE语句。IF…THEN…ELSE语句用于进行条件判断，实现程序的选择结构。根据是否满足条件，将执行不同的语句，其语法格式为：IF <条件> THEN<语句块1>[ELSE<语句块 3>]ENDIF ;

(3)CASE语句。CASE语句用于计算列表并返回多个可能结果表达式中的一个，可用于实现程序的多分支结构，虽然使用IF…THEN…ELSE语句也能够实现多分支结构，但是使用CASE语句的程序可读性更强。在MySQL中，CASE语句有以下两种形式。①简单CASE语句。简单CASE语句用于将某个表达式与一组简单表达式进行比较，以确定其返回值，其语法格式为：CASE <测试表达式>WHEN <表达式１> THEN <SQL语句１>WHEN <表达式2> THEN <SQL语句2>......[ELSE<SQL语句n+1> ]END CASE;简单CASE语句的执行过程是将“测试表达式”的值与各个WHEN子句后面的“表达式n”进行比较，若相等，则执行对应的“SQL语句”，然后跳出CASE语句，不再执行后面的WHEN子句；WHEN子句中没有与“测试表达式”相等的“表达式n”，若指定了ELSE子句，则执行ELSE子句后面的“SQL语句n+1”。若没有指定ELSE子句，则不执行CASE语句内任何一条SQL语句。②搜索CASE语句。搜索CASE语句用于计算一组逻辑表达式以确定返回结果，其语法格式为：CASEWHEN< 逻辑表达式 1>THEN<SQL语句 1> WHEN<逻辑表达式 2>THEN<SQL语句 2>......[ ELSE< SQL语句 n+1>]ENDCASE;搜索CASE语句的执行过程是先计算第一个WHEN子句后面的“逻辑表达式1”的值，若值为True，则CASE语句执行对应的“SQL语句1”；若值为False，则继续判断下面的WHEN子句中的“逻辑表达式n”的值，若值为True，则执行对应的“SQL语句n”。在所有“逻辑表达式”的值都为False的情况下，若指定了ELSE子句，则执行ELSE子句后面的“SQL语句n+1”。若没有指定ELSE子句，则不执行CASE语句内任何一条SQL语句。

(4)WHILE循环语句。WHILE循环语句用于实现循环结构，是有条件的执行循环语句，当满足指定条件时执行循环体内的语句，其语法格式为：[begin label:]WHILE <条件> DO<语句块>ENDWHILE[end label];说明：先判断“条件”是否为True，若为True，则执行“语句块”，然后进行判断，若为True则继续循环，若为False则结束循环。“begin[label：”和“end label”是WHILE语句的标注，“begin label：”与“end label”同时存在，并且标注的名称必须相同。“begin label：”和“end label”通常都可以省略。

(5)LOOP循环语句。LOOP语句用于实现循环结构。但是LOOP语句本身没有停止循环的机制，必须遇到LEAVE语句才能停止循环。LOOP语句的语法格式为：[begin label:]LOOP<语句块>ENDLOOP[end label];说明：LOOP语句允许语句块重复执行，实现一些简单的循环。在循环体内的语句一直重复执行直到循环被强迫终止，终止时通常使用LEAVE语句。

(6)REPEAT循环语句。REPEAT循环语句是有条件控制的循环语句，当满足指定条件时，就会跳出循环语句，其语法格式为：[begin]label:]REPEAT<语句块>UNTIL< 条件>ENDREPEAT[end label];说明：先执行语句块，然后判断逻辑表达式的值是否为True，若为True则停止循环，若为False则继续循环。REPEAT语句也可以被标注。REPEAT语句与WHILE语句的区别在于：REPEAT语句先执行语句，再进行条件判断；而WHILE语句先进行条件判断，只有条件为True时才执行语句。

(7)LEAVE语句。LEAVE语句主要用于跳出循环控制，经常与循环一起使用，其语法格式为：LEAVE<标签>；使用LEAVE语句可以退出被标注的循环语句，标签是自定义的。

(8)ITERATE语句。ITERATE语句用于跳出本次循环，然后直接进入下一次循环，其语法格式为：ITERATE< 标签>；ITERATE语句与LEAVE语句都是用来跳出循环语句的，但两者的功能不一样。其中，LEAVE语句用来跳出整个循环，然后执行循环语句后面的语句；而ITERATE语句是跳出本次循环，然后进行下一次循环。

3.PL/SQL的控制语句

(1)存储过程是一组为了完成特定功能的SQL语句块，经编译后存储在数据库。中，用户通过指定存储过程的名称并给定参数(如果该存储过程带有参数)来调用并执行它，存储过程能重复使用，这样可以大大减少数据库开发人员的工作量。存储过程主要有以下优点。①执行效率高：存储过程编译后存储在数据库服务器端，可以直接调用从而提高了SQL语句的执行效率。②灵活：存储过程可以用结构化语句编写，可以完成较复杂的判断和运算。③数据独立：用户在程序中调用存储过程，存储过程能把数据同用户程序隔离开、其优点是当数据表结构变化时，可以随时修改存储过程，不用修改程序源代码。④安全：存储过程可被作为一种安全机制来充分利用，系统管理员通过设置存储过程的访问权限，从而实现相应数据的访问权限限制，避免了用户对数据表的直接访问，保证了数据的安全。⑤降低网络流量：当在客户机上调用该存储过程时，网络中传送的只是该调用语句，而不是这一功能的全部代码，从而大大降低了网络负载。

(2)DELIMITER命令。DELIMITER命令用于更改MySQL语句的结束符，如将默认结束符“；”更改为“$$”，避免与SQL语句默认结束符相冲突，其语法格式为：DELIMITER< 自定义结束符>DELIMITER$S在存储过程编写结束后恢复使用MySQL的默认结束符“；”，例如：DELIMITER;

(3)创建简单存储过程。创建存储过程的语法格式为：CREATEPROCEDURE 存储过程名()BEGIN<存储过程体>END;说明：存储过程名应符合MySQL的命名规则，避免使用与MySQL的内置函数相同的名称。

(4)查看存储过程。查看存储过程的语法格式为：SHOWPROCEDURESTATUS [LIKE<存储过程模糊名>]；SHOWPROCEDURESTATUS  proc 601;MySQL中存储过程的信息存储在 information  schema数据库下的Routines表中，也可以通过查询该数据表的记录来查询存储过程的信息，例如：SELECT*FROM  information  schema. Routines WHERE Routine name=\"proc60l\";

(5)调用简单存储过程。存储过程创建完成后，可以在程序、触发器或其他存储过程中被调用，其语法格式为：CALL存储过程名()；

(6)删除存储过程。在命令行中删除存储过程的语法格式为：DROPPROCEDURE <存储过程名>；

4.MySQL的存储过程

【任务6.1】   创建简单存储过程

创建带输入参数的存储过程的语法格式为：CREATE PROCEDURE 存储过程名（［形参列表］）BEGIN<存储过程体>END;说明：存储过程可以不使用参数，也可以带一个或多个参数。如果有多个参数，各个参数之间使用半角逗号分隔。参数的定义格式为：[In]<参数名><参数类型>

1.MySQL带输入参数的存储过程的创建

存储过程创建完成后，可以在程序、触发器或其他存储过程中被调用，其语法格式为：CALL存储过程名(L<实参列表>]；说明：如果定义存储过程时使用了参数，那么调用该存储过程时，也要使用参数，并且参数个数和顺序必须一一对应。

2.调用存储过程

【任务6.2】   创建带输入参数的存储过程

MySQL带输入和输出参数的存储过程创建的语法格式如下：CREATEPROCEDURE 存储过程名（［形参列表］）BEGIN<存储过程体>END;说明：存储过程可以使用输入、输出和输入/输出参数。参数的定义格式为：[In]Out]InOut]<参数名><参数类型>MySQL的存储过程支持3种类型的参数：输入类型、输出类型和输入/输出类型，关键字分别使用In、Out、InOut，省略参数传递类型默认为In。

【任务6.3】   创建带输入和输出参数的存储过程

(1)声明游标。其语法格式为：DECLARE<游标名>CURSORFOR<select语句>；说明：游标名称必须符合MySQL标识符的命名规则，select语句返回一行或多行记录数据，但不能使用into子句。

(2)打开游标。其语法格式为：OPEN<游标名>；说明：打开一个已经声明过的游标。

(3)读取游标。其语法格式为：FETCH<游标名>INTO变量名1[，变量名2]…说明：在指定打开的游标读取一行数据并赋给对应的变量，并且游标指针下移，指向结果集的下一行。

(4)关闭游标。其语法格式为：CLOSE<游标名>；说明：关闭一个之前打开的游标。

MySQL带游标的存储过程

【任务6.4】   创建应用游标的存储过程

(1)原子性。一个事务( transaction )中的所有操作，要么全部执行，要么全部不执行。

(2)一致性。在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

(3)隔离性。MySQL数据库允许多个并发事务，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

(4)持久性。事务处理结束后，对数据的修改就是永久的。

I.事务的ACID特性

(1)开始事务。其语法格式为：STARTTRANSACTION ;说明：用于显式地启动一个事务。

(2)提交事务。其语法格式为：COMMIT;说明：用于提交事务，将事务对数据所做的修改进行保存。

(3)设置保存点。其语法格式为：SAVEPOINT<保存点名称>；说明：用于在事务内设置保存点。

(4)撤销事务。其语法格式为：ROLLBACK；[ ROLLBACKTOSAVEPOINT <保存点名称>；说明：撤销事务又称为事务回滚，即事务被执行后，如果执行的SQL语句导致业务逻辑不符或数据库操作错误，ROLLBACK语句撤销事务中所有的执行语句。 ROLLBACKTOSAVEPOINT 语句撤销事务中保存点之后的执行语句。

2.事务控制语句

【任务6.5】   创建与使用事务

项目六 创建与使用存储过程

触发器是特殊的存储过程，是一个被指定关联到数据表上的数据库对象，可以看作数据表定义的一部分，可用于实现数据库中数据的完整性。普通的存储过程通过Call命令调用，而触发器的调用不同，只能由数据库的特定事件来触发，且不能接收参数，它在插入、删除或修改指定数据表中数据时触发执行，从而实现数据的自动维护。

(1)安全性。可以使用户具有操作数据库的特定权利。例如，在“销售表”中插入一条销售记录，可以通过触发器实现对“商品一览表”中库存的更新，而不用直接将“商品一览表”展现在特定用户面前。

(2)实现复杂的数据完整性。实现数据完整性约束或规则，触发器可实现比约束更复杂的限制。

(3)实现复杂的非标准数据相关完整性。触发器可以对数据库中相关的表进行级联更新。例如，在“销售表”中插入一条销售记录，可以通过触发器实现对“商品一览表”中库存的级联更新。

1.触发器概述

创建触发器使用 CREATETRIGGER 语句，其语法格式为：Ｃ EATETRIGGER 触发器名触发时刻触发事件表名FOREACHROW触发器动作；说明：①触发器名。触发器名在当前数据库中必须具有唯一性，如果是在某个特定数据库中创建，在触发器名前加上数据库的名称。②触发时刻。触发时刻有两个选择：BEFORE或AFTER，以表示触发器在激活它的语句之前触发或之后触发。③触发事件。触发事件是指激活触发器执行的语句类型，可以是INSERT(插入记录时激活触发器)、DELETE(删除记录时激活触发器)、UPDATE(更新记录时激活触发器)。④表名。与触发器相关的数据表名称，在该数据表上发生触发事件时激活触发器。⑤ FOREACHROW 。行级触发器，指受触发事件每影响一行都会执行一次触发程序。⑥触发器动作。触发器激活时将要执行的语句，如果要执行多条语句可使用BEGIN…END复合语句。触发器动作中可以使用NEW与OLD关键字： 当插入记录时，在触发动作中可以使用NEW关键字表示新记录，当需要访问新记录的某个字段值时，可以使用“NEW字段名”的方式访问；当删除记录时，在触发动作中可以使用OLD关键字表示旧记录，当需要访问旧记录的某个字段值时，可以使用“OLD.字段名”的方式访问；当更新某条记录时，在触发程序中可以使用OLD关键字表示更新前的旧记录，使用NEW关键字表示更新后的新记录。注意，在MySQL触发器中不能直接在客户端界面返回结果，所以在触发器动作中不要使用SELECT语句，也不能在一个表上同时创建两个相同类型的触发器。因此，在一个表上最多创建6个触发器。

2.创建触发器

【任务7.1】   创建触发器

查看触发器使用 SHOWTRIGGERS 语句和SELECT语句。(1) SHOWTRIGGERS 的语法格式为：SHOWTRIGGERS [FROM数据库名];

(2)SELECT的语法格式为：SELECT*.FROM  Information  Schema. TriggersWHERE Trigger Name=<触发器名>；

1.查看触发器

删除触发器使用 DROPTRIGGER 语句，其语法格式为：DROPTRIGGER [IFEXISTS][数据库名：]触发器名；说明：“IFEXISTS”用于判断此触发器如果存在，那么执行此删除语句。

2.删除触发器

【任务7.2】   查看及删除触发器

项目七 创建与使用触发器

MySQL通过权限表来控制用户对数据库的访问，MySQL数据库在安装时会自动安装多个数据库。MySQL权限表存放在名称为MySQL的数据库中。常用的权限表有user、db、host、table priv、columns priv和procs priv。(1) user权限表。user是MySQL中最重要的一个权限表，user列主要分为4个部分：用户列、权限列、安全列和资源控制列。①用户列：用户登录时通过表中的Host、User和Password列判断连接的IP、用户名称和密码是否存在于表中来通过身份验证或拒绝连接。②权限列：user表中包含多个以“ priv”结尾的字段，这些字段决定了该用户的权限，既包括查询权限、插入权限、更新权限、删除权限等普通权限，也包括关闭服务器和加载用户等高级管理权限。③安全列：ssl(加密)、x509(标识用户)开头的字段，以及plugin和 authoritication  string字段(验证用户身份、授权的插件)。④资源控制列：max(最大允许次数，0表示无限制)开头的字段。max questions：表示每小时允许执行查询数据库的次数。max updates：表示每小时允许执行更新数据库的次数。max  connections ：表示每小时允许执行连接数据库的次数。max user conntions：表示单个用户同时连接数据库的次数。

(2) db、host权限表。db权限表存储用户在各个数据库上的操作权限，决定哪些用户可以从哪些主机访问哪些数据库。host权限表是db权限表的扩展，配合db权限表对给定主机上数据库级操作权限做更细致的控制。host权限表很少使用，只有在db表的范围内扩展一个条目时才会用到。

(3) table priv权限表。记录数据表级别的操作权限。table priv权限表与db权限表相似，不同之处是它用于数据表而不是数据库。

(4) columns priv权限表。记录数据字段级别的操作权限。columns priv权限表的作用与table priv权限表类似，不同之处是它针对某些表的特定字段的权限。

(5) procs priv权限表。该权限表存储用户在存储过程和函数上的操作权限。

1.MySQL的权限表

新安装的MySQL中只有一个名称为root的用户。这个用户是安装服务器时由系统创建并赋予了MySQL的所有权限。在对MySQL的实际操作中，通常需要创建不同层次要求的用户来确保数据的安全访问。添加用户可以通过 CREATEUSER 、INSERT和GRANT语句来实现。(1) CREATEUSER 语句的语法格式为：CREATEUSER <'用户名'>@<'主机'>[ 1DENTIFIEDBY [PASSWORD][<'密码>]]；说明：①使用 CREATEUSER 语句可以创建一个或多个用户，用户之间用逗号分隔。②“主机”可以是主机名或IP地址，本地主机名可以使用local host，“%”表示一组主机。③“ IDENTIFIEDBY ”关键字用于设置用户的密码，若指定用户登录不需要密码，则可以省略该选项。④“PASSWORD”关键字指定使用哈希值设置密码。密码的哈希值可以使用PASSWORD()函数获取。

(2)INSERT语句的语法格式为：font color=\"#ff0000\

2.添加用户

【任务8.1】   添加数据库用户

GRANT语句不仅是授权语句，还可以达到添加新用户或修改用户密码的作用。GRANT语句的语法格式为：GRANT<权限名称>[(字段列表)]ON<对象名>TO<'用户名'>@<'主机'>[ IDENTIFIEDBY [PASSWORD]<'新密码'>][ WITHGRANTOPTION ];说明：①“权限名称”中常用的权限如下。ALL[ PRIVILEGES ]：除 GRANTOPTION 之外的所有简单权限。CREATE：允许创建数据表。ALTER：允许修改数据表。DROP：允许删除数据表。SELECT：允许检索数据表。INSERT：允许在数据表中插入数据。DELETE：允许在数据表中删除数据。UPDATE：允许在数据表中更新数据。INDEX：允许在数据表中定义索引。CREATEVIEW ：允许创建视图。EXECUTE：允许运行指定的存储过程。②“对象名”有以下权限级别。全局权限：适用于一个给定服务器中的所有数据库，可以用“*.*”来表示。数据库权限：适用于一个给定数据库中的所有数据库对象，可以用“数据库名.*”来表示。表权限：适用于一个给定表中的所有列，可以用“数据库名.表名”来表示。列权限：适用于一个给定表中的单一列，可以先用“数据库名.表名”来表示，再在权限名称后加上“[(字段列表)]”可选项，如SELECT(员工ID，姓名)。子程序权限：适用于给定存储过程或函数，可以用“PROCEDURE=FUNCTION数据库名.过程名”来表示。③“<'用户名'>@<'主机'>”中若“用户名”不存在则添加用户。“[IDENTIF]ED3Y[PASSWORD]<'新密码'>]”可选项可以设置新用户的密码，若“用户名”已经存在，则此选项可以修改用户的密码。④“[ WITHGRANTOPTION ]”可选项表示允许用户将获得的权限授予其他用户。

1.授予用户权限

(1)使用 SHOWGRANTS 语句查看授权信息，其语法格式为：SHOWGRANTSFOR <用户名'>@<'主机'>

(2)使用SELECT语句查看mysql. user表中用户的全局权限，其语法格式为：SELECT<权限字段>FROM mysql. user［WHERE User=<'用户名'>AND Host=<'主机'>];说明：“mysql. user”表可以查询到用户的全局权限，“<权限字段>”中常用的权限字段有Select priv、Insert priv、Create priv等，mysql. db中可以查询到用户的数据库权限。

2.查看用户权限

使用REVOKE语句回收用户权限，其语法格式为：REVOKE<权限名称>[(字段列表)]ON<对象名>FROM<「用户名'>@<'主机'>；说明：REVOKE语句用来取消指定用户的某些指定权限，与GRANT语句类似。

3.回收用户权限

【任务8.2】   授予、回收数据库用户权限

使用 RENAMEUSER 语句可以修改用户的名称，其语法格式为：RENAMEUSER<'旧的用户名'>@<'主机'>TO<'新的用户名'>@<'主机′>;说明： RENAMEUSER 语句可以对用户进行重命名，该语句可以同时对多个已存的用户进行重命名，各个用户之间使用逗号分隔，重命名时“旧的用户名”必须已存在，并且“新的用户名”还不存在，使用者必须拥有“ RENAMEUSER ”权限。

1.修改用户的名称

(1)使用 mysqladmin 命令修改用户密码的语法格式为：mysqladmin -u<用户名>［-h<主机>］-p password[<新密码>]说明：“ mysqladmin ”是一条外部命令，必须在服务器端的“命令提示符”下执行。

(2)使用 SETPASSWORD 语句修改用户密码的语法格式为：font color=\"#ff0000\

(3)使用UPDATE语句修改用户密码的语法格式为：UPDATE mysql. user SET Password=PASSWORD(<'新密码'>)WHERE User=<'用户名'>AND Host=<'主机'>；说明：“新密码”需要用“PASSWORD()”函数来加密。

2.修改用户的密码

(1)使用DROPUSER语句删除用户的语法格式为：DROPUSER<'用户名'>@<'主机'>;说明：DROPUSER语句可以删除一个或多个普通用户，各用户之间用逗号分隔。如果删除用户已经创建的数据库对象，那么该用户将继续保留。使用者必须拥有“DROPUSER”权限。

(2)使用DELETE语句删除用户的语法格式为：DELETEFROMmysql . userWHEREUser =<用户名'>AND Host=<'主机'>;说明：使用DELETE语句删除用户时，使用者必须拥有“mysql. user”的“Delete”权限。

3.删除用户

【任务8.3】   数据库用户管理

项目八 数据库的安全性维护

MySQL是一个关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的SQL语言是访问数据库的常用标准化语言。MySQL软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。MySQL的主要特色有以下几点。(1)速度。MySQL运行速度很快。开发者声称MySQL可能是目前运行速度最快的数据库。(2)容易使用。MySQL是一个高性能且相对简单的数据库系统，与一些更大系统的设置和管理相比，其复杂程度较低。(3)价格。MySQL对多数个人用户来说是免费的，对于一些商业用途，需要购买相应的商业许可，但价格相对低廉。(4)支持查询语言。MySQL不仅可以利用SQL[SQL(结构化查询语言)是一种所有现代数据库系统都选用的语言]；还可以利用支持ODBC(开放式数据库连接)的应用程序(ODBC是Microsoft开发的一种数据库通信协议)。(5)性能。许多客户机可同时连接到服务器，并同时使用多个数据库。可利用几个输入查询并查看结果的界面来交互式地访问MySQL。这些界面为命令行客户机程序、Web浏览器或X-Window System客户机程序。此外，还有由各种语言(如C、Perl、Java、PHP和Python)编写的界面。因此，可以选择使用已编好的客户机程序或编写自己的客户机应用程序。(6)连接性和安全性。MySQL是完全网络化的，其数据库可在因特网上的任何地方访问，因此可以与任何地方的任何人共享数据库。而且MySQL能进行访问控制，可以控制哪些人不能看到数据。(7)可移植性。MySQL既可运行在各种版本的UNIX及其他非UNIX的系统(如Windows和OS/2)上，也可运行在从家用PC到高级的服务器上。

1.MySQL概述

数据库系统由硬件部分和软件部分共同构成。其中，硬件部分主要用于存储数据库中的数据，包括计算机、存储设备等；软件部分则主要包括DBMS、支持DBMS运行的操作系统，以及支持多种语言进行应用开发的访问技术等。数据库系统有以下3个主要的组成部分。(1)数据库。用于存储数据的地方。(2)数据库管理系统。用于管理数据库的软件。(3)数据库应用程序。为了提高数据库系统的处理能力所使用的管理数据库的软件补充。

2.数据库的技术构成

主从式架构(Client-Server Model)或客户端/服务器(Client/Server)结构(简称C/S结构)是一种网络架构，通常在该网络架构下软件分为客户端(Client)和服务器(Server)。服务器是整个应用系统资源的存储与管理中心，多个客户端则各自处理相应的功能，共同实现完整的应用。

3.客户端-服务器结构

【任务1.1】   尝试接触数据库

结构化查询语言(&nbspStructured&nbsp Query Language，SQL)是一种应用最为广泛的关系数据库语言，该语言定义了操作关系数据库的标准语法，几乎所有的关系数据库管理系统都支持SQL.SQL包含以下几个部分。①u style=\

1.结构化查询语言

my. ini是MySQL数据库中使用的配置文件，修改这个文件可以达到更新配置的目的。

2. my. ini

【任务1.2】   MySQL的命令行工具

Navicat是一套快速、可靠并价格便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而开发，其设计符合数据库管理员、开发人员及中小企业的需要。它拥有直观化的图形用户界面，让用户可以以安全且简单的方式创建、组织、访问和共享MySQL数据库中的数据。Navicat可以用来对本机或远程的MySQL、SQL Server、SQLite、Oracle及Post-greSQL数据库进行管理及开发，其功能不仅符合专业开发人员的所有需求，而且对数据库服务器的新手学习也相当容易。Navicat适用于Microsoft Windows、Mac OS及Linux 3种平台，它可以让用户连接到本机或任意远程服务器，并提供一些实用的数据库工具(如数据模型、数据传输、数据同步、结构同步、导入、导出、备份、还原、报表创建工具)及计划以协助管理数据。Navicat包括多个产品，其中的一个产品Navicat for MySQL是一套专为MySQL设计的高性能数据库管理及开发工具。它可以用于版本3.21及其以上的MySQL数据库服务器，并支持大部分MySQL最新版本的功能，包括触发器、存储过程、函数、事件、视图、管理用户等。另一个产品Navicat Premium是一个可多重连接的数据库管理工具，它可以让用户以单一程序同时连接到MySQL、Oracle、 PostgreSQL 、SQLite及SQL Server数据库，让管理不同类型的数据库更加方便。Navicat Premium使用户能简单并快速地在各种数据库系统之间传输数据，或者传输一份指定SQL格式及编码的纯文本文件。这可以简化数据从一台服务器迁移到另一台服务器的进程，不同数据库的批处理作业也可以按计划并在指定的时间运行。

【任务1.3】   图形管理工具Navicat

项目一   MySQL印象

MySQL由瑞典MySQLAB公司开发，默认情况下MySQL使用的是latin 1字符集。由此可能导致MySQL数据库不够支持中文字符串查询或发生中文字符串乱码等问题。字符(Character)是人类语言最小的表意符号，如“A”“B”等。给定一系列字符，对每个字符赋予一个数值，用数值代表对应的字符，这个数值就是字符的编码(Character Encoding)。给定一系列字符并赋予对应的编码后，所有这些“字符和编码对”组成的集合就是字符集(Character Set)。字符序(Collation)是指在同一字符集内字符之间的比较规则。一个字符集包含多种字符序，每个字符序唯一对应一种字符集。MySQL字符序的命名规则：以字符序对应的字符集名称开头，以国家名居中(或以general居中)，以ci、cs或bin结尾。其中，ci表示大小写不敏感，cs表示大小写敏感，bin表示按二进制编码值比较。使用MySQL命令“ SHOWCHARACTERSET ；”即可查看当前MySQL服务实例支持的字符集、字符集默认的字符序，以及字符集占用的最大字节长度等信息。

1.字符集及字符序

(1)通过修改my. ini配置文件，可修改MySQL默认的字符集。(2)MySQL提供一些MySQL命令，可以临时修改MySQL当前会话的字符集及字符序。(3)使用MySQL命令“ SETNAMESGBK ；”。可以临时一次性地设置CHARAC TER SET CLIENT、CHARACTER SET  CONNECTION 及CHARACTER SET RESULTS的字符集为GBK。(4)连接MySQL服务器时指定字符集。在命令行输入连接MySQL命令“MySQL-DEFAULT _CHARACTER_SET=GBK−H 服务器IP地址-U用户名 -P\"。连接成功后可以临时一次性地设置CHARACTER SET CLIENT、CHARACTER SET  CONNECTION 及CHARACTER SET RESULTS的字符集为GBK。

2.MySQL字符集的设置

数据库存储引擎是数据库底层软件组件，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎可以获得特定的功能。现在的数据库管理系统都支持多种不同的数据引擎，MySQL的核心就是存储引擎。

3.MySQL存储引擎

【任务2.1】   使用图形化工具创建数据库

在命令行中使用命令语句创建MySQL数据库命令的语法格式为：create database <数据库名>;

1.创建数据库命令语句

在命令行中使用命令语句修改MySQL数据库命令的语法格式为：alter database <数据库名> default character set 新字符集;

2.修改数据库命令语句

在命令行中使用命令语句删除MySQL数据库命令的语法格式为：drop database <数据库名>;

3.删除数据库命令语句

【任务2.2】   使用命令语句创建、修改和删除数据库

在命令行中使用命令语句使用MySQL数据库命令的语法格式为：use <数据库名>;

1.使用数据库

在命令行中使用命令语句查看MySQL数据库创建信息命令的语法格式为：show create database <数据库名>;

2.查看数据库的创建信息

【任务2.3】   使用语句方式选择与查看数据库

任何时候，数据都是一个网站或应用的重中之重，尽管在系统中会有多种措施来保证数据库的安全和完整，但不可预知的伤害还是会发生，如硬件故障、软件错误、病毒入侵等情况发生时会导致运行的中断，影响数据的正确性，还有可能会破坏数据库，导致数据的丢失。所以，在数据库的操作过程中一定要养成经常备份数据库的良好习惯。而用图形化工具Navicat来备份、还原MySQL的数据库是一种简单易用，且效率较高的方式。

【任务2.4】   使用图形化工具备份、还原MySQL的数据库

项目二 创建与维护MySQL数据库

(1)关系型数据库是一种建立在关系模型(数学模型)上的数据库。关系模型是一种建立在关系上的模型，包括以下3个方面。①数据结构：二维表，解决数据如何存储。②操作指令集合：所有的SQL语句，解决如何处理数据。③完整性约束：表内数据约束(字段与字段)，表与表之间的约束(外键)，关系是一个描述两个集合的元素如何相互联系或一一对应的数学概念。因此，关系模型是建立在数学基础上的。然而，关系只是一个带有一些特殊属性的表，一个关系模型把数据组织到表中，而且仅在表中。客户、数据库设计者、数据库系统管理员和用户都以同样的方式从表中查看数据。那么，表就是关系模型的近义词。一个关系型表有一组命名的属性或列，以及一组元组或行。有时列被称为域，行被称为记录，列和行的交集通常被称为单元。列标示的位置有作用域或数据类型，如字符或整数；行本身就是数据。一个关系表必须符合以下特定条件，才能成为关系模型的一部分。①储存在单元中的数据必须是原子的。每个单元只能存储一条数据，这也称信息原则。违反这一条将不能运用良好的设计原则。当一个单元包含多于一条的信息时，称为信息编码。在这样的情况下，是否采用违背理论的方案是一个设计的选择问题，尽管在多数情况下，结果证明这对数据的完整性是不利的。②储存在列下的数据必须具有相同的数据类型。③每行都是唯一的(没有完全相同的行)。④列没有顺序。⑤行没有顺序。⑥列有一个唯一性的名称。

(2)关系型数据库的设计。关系型数据库：从需要存储的数据需求中分析，如果是一类数据(实体)，就应该设计成一张二维表，且表由表头(字段名，用来规定数据的名称)和数据部分(实际存储的数据单元)组成。关系型数据库的特点之一：有的表中对应的某个字段没有值(数据)，但是系统依然要分配相应的空间，那么在没有数据时空间也是已经分配好的，所以关系型数据库比较浪费空间。

1.关系型数据库

数据要分类型的原因有两个：一是存储数据时可以更好地与显示相对应，不同字段存储的数据类型不一样；二是可以节省空间，针对某种类型的数据，就分配对应的内存空间用于存储。MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL中的数据类型分为三大类：数值型、字符串型和时间日期型。三大类又细分为多种类型。（1）数值型：整数类型的数，默认情况下既可以表示正整数又可以表示负整数(此时称为有符号数)。若只希望表示零和正整数，则可以使用无符号关键字“unsigned”对整数类型进行修饰(此时称为无符号整数)。若不需要小数部分，则使用整数；若需要表示小数部分，则使用浮点型数据类型。浮点型数据类型精度有限，当插入数据超出精度范围时，会自动进行四舍五入，造成精度丢失。float：单精度，占用4个字节存储数据，精度范围在7位左右。double：双精度，占用8个字节存储数据，精度范围在15位左右。因此，如果要求存储精度较高时，应使用double类型，若是精度较低的小数，则使用float类型。浮点类型(float和double)相对于定点类型decimal的优势是，在长度一定的情况下，浮点类型比定点类型能表示更大的数据范围，其缺点是容易产生计算误差。decimal在MySQL中是以字符串形式存储的，用于存储精度相对要求较高的数据(如货币、科学数据等)。两个浮点数据进行减法或比较运算时容易出现问题，如果进行数值比较，那么最好使用decimal类型。

（2）日期时间型：MySQL对于不同种类的日期和时间有很多数据类型，如year和time。如果只需存储年份，使用year类型即可；如果只需记录时间，使用time类型即可；如果需要同时存储日期和时间，就可以使用datetime或timestamp类型。存储范围较大的日期最好使用datetime类型。timestamp类型也有datetime类型不具备的属性，默认情况下，当插入一条记录但没有给timestamp类型字段指定具体的值时，MySQL会把timestamp字段设置为当前时间。因此，当需要在插入记录的同时插入当前时间时，使用timestamp类型更方便。

(3)font color=\"#ff00ff\

2.数据类型

MySQL关键字                含义null                                数据列可包含null值not null                          数据列不允许包含null值default                           默认值primary key                   主键auto _ increment           自动递增，适用于整数类型unsigned                       无符号character set<字符集名> 指定一个字符集zerofill                           数值类型前自动用0补足位数comment                      列描述

3.数据类型的属性

MySQL的约束是指对数据表中数据的一种约束行为，约束主要完成对数据的检验，如果有相互依赖数据，保证该数据不被删除。它能够帮助数据库管理员更好地管理数据库，并且能够确保数据库表中数据的正确性和一致性。MySQL的约束主要包括主键约束、外键约束、唯一约束、非空约束和默认值约束。(1)主键约束(primary key)。关系数据库依赖于主键，它是数据库物理模式的基石。主键在物理层面上只有两个用途：唯一地标识一行和作为一个可以被外键有效引用的对象。通常在数据表中将一个字段或多个字段组合设置为各种不同的值，以便能唯一地标识数据表中的每一条记录，这样的一个字段或多个字段称为数据表的主键，通过它可实现实体完整性，消除数据表冗余数据。一个数据表只能有一个主键约束(可以是复合主键)，并且主键约束中的字段不能接受空值。由于主键约束可保证数据的唯一性，因此经常对标识字段定义这种约束。可以在创建数据表时定义主键约束，也可以修改现有数据表的主键约束。

(2)外键约束( foreignkey )。外键约束保证了数据库中各个数据表中数据的一致性和正确性。将一个数据表的一个字段或字段组合定义为引用其他数据表的主键字段，则引用该数据中的这个字段或字段组合就称为外键。被引用的数据表称为主键约束表，简称为主表，引用表称为外键约束表，简称为从表。可以在定义数据表时直接创建外键约束，也可以对现有数据表中的某一个字段或字段组合添加外键约束。

(3)唯一约束(unique)。一个数据表只能有一个主键，如果有多个字段或多个字段组合需要实施数据唯一性，可以采用唯一约束。可以对一个数据表定义多个唯一约束，唯一约束允许为null值，但每个唯一约束字段只允许存在一个null值。

(4)非空约束(not null)。指定为not null的字段则不能输入null值，数据表中出现nuH值通常表示值未知或未定义，null值不同于零、空格或长度为零的字符串。在创建数据表时，默认情况下，如果在数据表中不指定非空约束，那么数据表中所有字段都可以为空。由于主键约束字段必须保证字段是不为空的。因此，要设置主键约束的字段一定要设置非空约束。

(5)默认值约束(default)。默认值约束用来约束当数据表中的某个字段不输入值时，自动为其添加一个已经设置好的值。可以在创建数据表时为字段指定默认值，也可以在修改数据表时为字段指定默认值。default约束定义的默认值仅在执行Insert操作(插入数据)时生效，一列最多有一个默认值，其中包括null值。默认值约束通常用在已经设置了非空约束的字段上，这样能够防止数据表在输入数据时出现错误。

4.MySQL的约束

关系数据库中的关系必须满足一定的要求，满足不同程度要求的为不同范式，数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式，才能设计出高效率的数据库，否则可能会设计出错误的数据库。三大范式介绍如下。第一范式(1NF)：每一列属性都是不可再分的属性值，确保每一列的原子性。两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。满足第一范式是关系模式规范化的最低要求，否则将有很多基本操作在这样的关系模式中实现不了。第二范式(2NF)：满足1NF后，要求表中的所有列都必须依赖于主键，而不能有任何一列与主键没有关系，即一个表只描述一件事情。第三范式(3NF)：必须先满足第二范式(2NF)，要求表中的每一列只与主键直接相关，而不是间接相关(表中的每一列只能依赖于主键)。总结：第一范式和第二范式的区别在于有没有分出两张表，第二范式是在一张表中包含了几种不同的实体属性，那么必须分成多张表，第三范式要求已经分成了多张表，那么一张表中只能有另一张表中的id(主键)，而不能有其他的任何信息(其他的信息一律用主键在另一张表中查询)。

5.数据库设计的三打范式

【任务3.1】   数据库的结构及数据完整性设计

数据表属于数据库，在创建数据表之前，应该使用语句“USE一数据库名>”指定操作在哪个数据库中进行，如果没有选择数据库，就会抛出“ NODATABASE  SELECTED”的错误提示。表的创建命令需要表的名称、字段名称、定义每个字段(类型、长度等)。创建数据表需要用到 CREATETABLE 语句，其语法格式为：CREATE TABLE <表名称>(<列名1><数据类型> [列级别约束条件] [默认值]，<列名2> <数据类型> [列级别约束条件] [默认值]，......<列名N> <数据类型> [列级别约束条件] [默认值]，) [表级别约束条件]；要创建表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。如果创建多个列，列之间要用逗号隔开。

(1)定义主键约束。主键约束( PRIMARYKEYCONSTRAINT )要求主键列的数据唯一，并且不允许为空。主键分为两种类型：单字段主键和多字段组合主键。在定义字段的同时指定一个字段为主键的语法格式为：<字段名> <数据类型>  PRIMARYKEY  [默认值]在定义完所有字段之后指定一个字段为主键的语法格式为：[ CONSTRAINT ＜主键约束名＞] PRIMARYKEY  ＜字段名＞在定义完所有字段之后指定多个字段组合主键的语法为：[ CONSTRAINT ＜主键约束名＞] PRIMARYKEY (＜字段名1＞，＜字段名2＞，…，<字段名N>)当主键为多字段组合主键时，不能直接在相应字段名后面声明主键约束。

(2)定义唯一约束。唯一约束与主键约束的主要区别如下。①一个数据表可以有多个唯一约束，可主键约束只能有一个。②主键的字段不允许为空值(null)，唯一键的字段允许空值(null)，但只能有一个空值。③唯一约束通常设置在主键以外的字段上，一旦创建系统就会默认保存其在索引中。在定义完字段后直接指定唯一约束的语法格式为：<字段名><数据类型> UNIQUE在定义完所有字段后再指定唯一约束的语法格式为：[ CONSTRAINT <唯一约束名>] UNIQUE(<字段名1>，<字段名2>，…)唯一约束可以在一个数据表中设置多个字段，也可以类似组合主键设置组合唯一约束。

(3)定义非空约束。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。定义非空约束的语法格式为：<字段名><数据类型> NOTNULL

(4)定义外键约束。外键用于在两个表的数据之间建立链接，它可以是一列或多列。一个表可以有一个或多个外键。外键对应参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。主表(父表)：对于两个具有关联关系的表而言，相关联字段中主键所在的表即为主表。从表(子表)：对于两个具有关联关系的表而言，相关联字段中外键所在的表即为从表。创建外键的要求如下。①外键字段的数据类型必须与父表中的主键类型一致。②添加外键的数据表的存储引擎必须是InnoDB，否则即使外键添加成功，也不具备外键约束作用。③一张表中不能出现同名外键。④当外键字段中有值后，再添加外键时，已有的值必须在父表主键中，否则无法创建成功。定义外键约束的语法格式为：[ CONSTRAINT <外键约束名>] FOREIGNKEY （字段名1［，字段名2，…]）REFERENCES 〈主表名＞（主键字段名１［，主键字段名２，…］）

(5)定义默认值约束。其语法格式为：<字段名> <数据类型> DEFAULT <默认值>在定义默认值约束时，如果默认值为字符类型，就要用半角引号将字符引起来。

(6)定义字段值自增长。在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值，可以通过为表主键添加AUTO INCREMENT关键字来实现。默认情况下，在MySQL中AUTO INCREMENT的初始值是1，每新增一条记录，字段值自动加1。一个表只能有一个字段使用AUTO INCREMENT，且该字段必须为主键的一部分、AUTO-INCREMENT约束的字段可以是任何整数类型 (TI−NYTNT、SMALLIN、INT、BIGINT等)。定义字段值自增长的语法格式为：<字段名><数据类型>AUTO _INCREMENT

1.使用 CREATETABLE 语句创建数据表及其约束

使用语句创建好数据表之后，可以查看表结构的定义，以确认表的定义是否正确。在MySQL中，查看表结构可以使用DESCRIBE和 SHOWCREATETABLE 语句。(1)查看表基本结构语句DESCRIBE。DESCRIBE/DESC语句可以查看表的字段信息，其中包括字段名、字段数据类型、是否为主键、是否有默认值等，其语法格式为：DESCRIBE <表名>；或者简写为：DESC <表名>；

(2)查看表详细结构语句 SHOWCREATETABLE 。 SHOWCREATETABLE 语句可以用来显示创建表时的 CREATETABLE 语句，其语法格式为：SHOWCREATETABLE  <表名>；使用 SHOWCREATETABLE 语句，不仅可以查看表创建时的详细语句，而且可以查看存储引擎和字符编码。如果显示的结果非常混乱，就在语句命令后加上参数“\\G'，可使显示结果更加直观并易于查看。

2.使用语句查看数据表结构

【任务3.2】   使用语句创建数据表及其约束

在图形管理工具Navicat中创建表，由于是图形化的数据库管理工具，所有的操作都是在图形化界面中完成，因此相对于用命令语句创建表要直观得多。但是，其效率没有用命令语句创建表那么高。对于初学者来说，学会使用Navicat建表，对帮助理解MySQL数据库中的数据表还是非常明显的。

【任务3.3】   使用图形管理工具创建数据表

修改表是指修改数据库中已经存在的数据表的结构。MySQL使用 ALTERTABLE 语句修改表，常用的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

(1)修改表名。其语法格式为：ALTER TABLE<旧表名> RENAME[TO] <新表名>；其中，“TO”为可选参数，使用与否均不影响结果。

(2)修改字段的数据类型。修改字段的数据类型，就是把字段的数据类型转换为另一种数据类型，具体的语法格式为：ALTER TABLE(表名)MODIFY〈字段名〉〈数据类型〉其中，“表名”是指要修改数据类型的字段所在表的名称，“字段名”是指需要修改的字段，“数据类型”是指修改后字段的新数据类型。

(3)修改字段名。其语法格式为：ALTER TABLE（表名）Ｃ（旧字段名）〈新字段名〉 〈新数据类型〉；其中，“旧字段名”是指修改前的字段名；“新字段名”是指修改后的字段名；“新数据类型”是指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置为与原来一样，但数据类型不能为空。CHANGE也可以只修改数据类型，实现与MODIFY一样的效果，方法是将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。由于不同类型的数据在机器中存储的方式及长度并不相同，而且修改数据类型可能会影响到数据表中已有的数据记录。因此，当数据库表中已经有数据时，不要轻易修改数据类型。

(4)添加字段。其语法格式为：ALTER TABLE<表名>ADD〈新字段名〉〈数据类型〉 [约束条件][FIRST]AFTER已存在字段名];新字段名为需要添加字段的名称；“FIRST”为可选参数，其作用是将新添加的字段设置为表的第一个字段： “AFTER”为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”后面。“FIRST”或“AFTER已存在字段名”用于指定新增字段在表中的位置，若语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

(5)删除字段。删除字段是指将数据表中的某个字段从表中移除，其语法格式为：ALTERT ABLE <表名> DROP <字段名>；“字段名”是指需要从表中删除的字段名称。

(6)修改字段的排列位置。对于一个数据表来说，在创建时，字段在表中的排列顺序就已经确定了。但表的结构并不是完全不能改变的，可以通过 ALTERTABLE 语句来改变表中字段的相对位置。其语法格式为：ALTER TABLE<表名>MODIFY <字段1><数据类型>FIRST|AF-TER<字段2>；其中，“字段1”是指要修改位置的字段，“数据类型”是指“字段1”的数据类型，FIRST为可选参数，指将“字段1”修改为表的第一个字段，AFTER“字段2”是指将“字段1”插入“字段2”后面。

(7)修改表的存储引擎。其语法格式为：ALTER TABLE <表名>ENGINE=<更改后的存储引擎名>；引擎的选择请参考项目2的前导知识。

(8)修改表的各类约束。添加主键约束的语法格式为：font color=\"#ff0000\

1.使用 ALTERTABLE 语句修改、删除表结构

删除数据表就是将数据库中已经存在的表删除。注意，在删除表的同时，表的定义和表中所有的数据均会被删除。因此，在进行删除操作前，最好对表中的数据进行备份，以免造成无法挽回的后果。使用DROPTABLE语句可以一次删除一个或多个没有被其他表关联的数据表，其语法格式为：DROP TABLE[IFEXISTS]表1，表2，…，表n；可选参数“IFEXISTS”用于在删除前判断删除的表是否存在，加上该参数后，再删除表时，如果表不存在，SQL语句虽然可以顺利执行，但会发出警告（WARNING）。注意，在数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败，因为直接删除将破坏表的参照完整性。如果必须删除，那么可以先删除与它关联，的子表，再删除父表，但这样就会同时删除两个表中的数据。但某些情况下可能要保留子表，这时若要单独删除父表，只需将关联的表的外键约束条件取消，然后删除父表即可。

2.使用语句删除数据表

【任务3.4】   修改、删除数据表结构

使用基本的INSERT语句插入数据，要求指定表名称和插入新记录中的值，其基本语法格式为：INSERT INTO <表名>[（字段1，字段2，…，字段N）]VALUES （数据1，数据2，数据N)；注意，使用该语句时字段列和数据值的数量必须相同。如果数据是字符型，必须使用单引号或双引号将其引起来。INSERT语句还可以将SELECT语句查询的结果插入表中，其基本语法格式为：INSERTINTO 插入数据表名（输入字段列表）SELECT（查询字段列表）FROM查询数据表名WHERE(条件)输入字段列表必须和查询字段列表中字段个数相同，且数据类型相同。WHERE的条件是指查询的条件。

1.使用命令语句插入数据

表中有数据之后，便可以对数据进行更新操作，在MySQL中使用UPDATE语句更新表中的记录，可以更新特定的行或同时新所有行，基本语法结构为：UPDATE表名SET字段l=值1，字段2=值2，…，字段N=值N  [WHERE条件]可以同时更新一个或多个字段，可以在WHERE子句中指定任何条件。要保证UPDATE以WHERE子句结束，通过WHERE子句指定被更新的记录所需要满足的条件，如果忽略WHERE子句，MySQL将更新表中所有的行。

2.使用命令语句更新数据

从数据表中删除数据使用DELETE语句，DELETE语句允许WHERE子句指定删除条件。DELETE语句的基本语法格式为：DELETEFROM  表名[WHERE条件]如果没有指定WHERE子句，MySQL表中的所有记录将被删除；可以在WHERE子句中指定任何条件；也可以在单个表中一次性删除记录。

3.使用命令语句删除数据

【任务3.5】   数据插入、更新与删除

MySQLDUMP命令位于MySQL程序安装的“BIN\\”目录中，它可以把整个数据库装载到一个单独的文本文件中。这个文件包含所有重建数据库所需的SQL命令。这个命令取得所有的模式，并且将其转换为DDL语法，取得所有的数据，并从这些数据中创建INSERT语句。这个命令将数据库中所有的设计倒转，因为所有的东西都被包含到一个文本文件中。这个文本文件可以用一个简单的批处理和一个合适的SQL语句导回MySQL中。使用MySQLDUMP命令必须有足够的权限，其基本语法格式为：MySQLDUMP [−H<IP地址>] -U<用户名> -P[数据库密码]  −DATA −BASE 数据库1 数据库2… > [存储路径]文件名.SQL

使用命令语句转储数据

【任务3.6】   数据转储

项目三 创建与维护MySQL数据表

MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：font color=\"#ff0000\

1.基本查询语句

(1)在SELECT语句中使用星号(*)通配符查询所有字段。SELECT查询记录最简单的形式是从一个表中检索所有记录，实现的方法是使用星号(*)通配符指定查询。找所有列的名称，其语法格式为：SELECT*FROM表名；

(2)在SELECT语句中指定所有字段。根据前面SELECT语句的格式，SELECT关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在SELECT子句后面。有时候，由于表中的字段比较多，不一定能记得所有字段的名称，因此该方法会很不方便，不建议使用，其语法格式为：SELECT字段1，字段2，…，字段nFROM表名；

2.检索所有列

(1)检索单个字段。查询表中的某一个字段，语法格式为：SELECT字段名FROM表名；

(2)检索多个字段。要想从数据表中检索多个字段的数据，仍然使用相同的SELECT语句，只需在关键字SELECT后面指定要查找的多个字段的名称，不同字段名称之间用逗号(，)分隔开，最后一个字段后面不需要加逗号，语法格式为：SELECT字段1，字段2，…，字段nFROM表名；有些情况下，显示的字段名会很长或名称不够直观，MySQL可以指定字段别名替。换字段或表达式。为字段定义别名的基本语法形式为：字段名[AS]字段别名其中，“字段名”为表中字段定义的名称，“字段别名”为字段新的名称，AS关键字为可选参数。

3.检索指定列

【任务4.1】   查询时选择列

数据库中包含大量的数据，根据用户需求，可能只需要查询表中的指定数据，即对数据进行过滤。在SELECT语句中，通过WHERE子句可以对数据进行过滤，语法格式为：font color=\"#ff0000\

1.查询指定记录

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号的合法值，语法格式为：font color=\"#ff0000\

2.带IN关键字的查询

BETWEENAND 用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，若字段值满足指定的范围查询条件，则这些记录被返回，BETWEEN匹配范围中的所有值，包括开始值和结束值，语法格式为：SELECT字段名1，字段名2，…，字段名nFROM表名WHERE字段名BE-TWEEN值1AND值2；BETWEENAND 操作符前可以加关键字NOT，表示指定范围之外的值，若字段值不满足指定范围内的值，则这些记录被返回。

3.带 BETWEENAND 的范围查询

前面介绍的所有操作符都是针对已知值进行过滤。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤使用的值都是已知的，如果要查找所有的姓“刘”的员工信息，该如何查找呢?简单的比较操作在这里已经行不通了，需要使用通配符进行匹配查找，通过创建查找模式对表中的数据进行比较。执行这个任务的关键字是LIKE，语法格式为：SELECT字段名1，字段名2，…，字段名nFROM表名WHERE字段名LIKE条件通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符，SQL语句中支持多种通配符，可以和LIKE一起使用的通配符有“%”和“ ”。(1)百分号通配符(%)。通配符“%”匹配任意长度的字符，甚至包括零字符，可以在搜索模式中的任意位置使用，并且可以使用多个通配符。(2)下划线通配符( )。通配符“ ”的用法与“%”相同，区别是“%”可以匹配多个字符，而“ ”只能匹配任意单个字符。若需要匹配多个字符，则使用相同个数的“ ”。

4.带LIKE的字符匹配查询

数据表创建时，设计者可以指定某列中是否可以包含空值(NULL)。空值不同于0，也不同于空字符串。空值一般表示数据未知、不适用或在以后添加数据。在SELECT语句中使用ISNULL子句，可以查询某字段内容为空的记录，语法格式为：SELECT字段名1，字段名2，…，字段名nFROM表名WHERE字段名1SNULL；

5.查询空值

使用SELECT语句查询时，可以增加查询的限制条件，这样会使查询的结果更加精确。MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开，语法格式为：SELECT字段名］，字段名2，字段名nFROM表名WHERE条件1AND条件2…；

6.带AND的多条件查询

与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开，语法格式为：SELECT字段名1，字段名2，…，字段名nFROM表名WHERE条件1OR条件2....;OR可以和AND一起使用，但在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

7.带OR的多条件查询

SELECT语句返回所有匹配的行，如果不希望每个值每次都出现，该怎么办呢?例如，如果想检索“员工表”所有部门的名称，按以往的方法，部门字段中所有行的数据都会显示出来，有些部门名称会有雷同。因此，可以使用DISTINCT关键字，返回数据的不同值，语法格式为：font color=\"#ff0000\

8.查询不同的值

SELECT返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或前几行，可以使用LIMIT关键字，语法格式为：SELECTDISTINCT 字段名1，字段名2，…，字段名nFROM表名LIMIT[位置偏移量，]行数；注意，第一个被检索的行是第0行，而不是第1行。

9.显示前N行

【任务4.2】   查询时选择行

从表中查询出来的数据是无序的，或者其排列顺序不是用户所期望的。为了使查询结果满足用户的要求，可以使用ORDERBY语句对查询结果进行排序，其语法格式为：SELECT字段名1，字段名2，…，字段名nFROM表名ORDERBY字段名1[ASC|DESC]，字段名2[ASC∣DESC]…;在上面的语法格式中，指定的字段名1、字段名2等是对查询结果排序的依据。参数ASC表示按升序进行排序，DESC表示按降序进行排序。默认情况下，按照ASC方式进行排序。

【任务4.3】   查询结果排序

有时候并不需要返回实际表中的数据，而只是对数据进行总结。MySQL提供了一些查询功能，可以对获取的数据进行分析和报告。这些函数的功能包括：计算数据表中记录行数的总数、计算某个字段列下数据的总和，以及计算表中某个字段下的最大值、最小值或平均值。这些聚合函数的名称和作用如表4-2所示。表4-2 SQL聚集函数span style=\

(1)AVG()函数。AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。AVG()函数可以用来返回所有列的平均值，也可以用来返回特定列或行的平均值。注意，AVG()函数只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。AVG()函数忽略列值为NULL的行。

(2)COUNT()函数。COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数，其使用方法有以下两种。①COUNT(*)：计算表中总的行数，不管某列是否有数值或为空值。②COUNT(字段名)：计算指定列下总的行数，计算时将忽略空值的行。注意，指定列的值为空的行被COUNT()函数忽略，但是如果不指定列，而在COUNT()函数中使用星号(*)，则所有记录都不忽略。

(3)MAX()函数。MAX()函数返回指定列中的最大值，而且要求指定列名。注意，MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，包括返回字符类型的最大值。在对字符类型数据进行比较时，按照字符的ASCH码值大小进行比较，从a到z，a的最小，z的最大。在比较时，先比较第一个字符，如果相等，继续比较下一个字符，一直到两个字符不相等或字符结束为止。例如，“bef”与“bcg”比较时，“bef”为最大值。

(4)MIN()函数。MIN()函数返回指定列中的最小值，且要求指定列名。MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也应用于字符类型。

(5)SUM()函数。SUM()函数用于求总和，返回指定列值的总和(总计)。注意：SUM()函数在计算时，忽略列值为NULL的行。

1.聚集函数

分组查询是对数据按照某个或多个字段进行分组，在MySQL中使用GROUPBY关键字对数据进行分组，其基本语法形式为：[GROUPBY字段名][HAVING<条件表达式>]其中，“字段名”为进行分组时所依据的列名称：“HAVING<条件表达式>”指定满足表达式限定条件的结果将被显示。

(1)创建分组。分组是使用SELECT语句的GROUPBY子句建立的。GROUP BY关键字通常和聚集函数一起使用，如MAX()、MIN()、COUNT()、SUM()、AVG()。例如，要返回员工表中每个部门的员工人数，这时就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。在使用GROUPBY子句时，需要知道以下重要的规定。①GROUPBY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。②如果在GROUPBY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算(但不能从个别的列取回数据)。③GROUPBY子句中列出的每一列都必须是检索列或有效的表达式(不能是聚集函数)。如果在SELECT中使用表达式，就必须在GROUP子句中指定相同的表达式，不能使用别名。④大多数SQL实现不允许GROUPBY列带有长度可变的数据类型(如文本或备注型字段)。⑤除聚集函数外，SELECT语句中的每一列都必须在GROUPBY子句中给出。⑥如果分组列中包含具有NULL值的行，那么NULL将作为一个分组返回，如果列中有多行NULL值，它们将分为一组。⑦GROUPBY子句必须出现在WHERE子句之后，ORDERBY子句之前。

(2)过滤分组。GROUPBY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。HAVING关键字与WHERE关键字都是用来过滤数据的，HAVING支持所有WHERE操作符。两者的区别在于，HAVING在数据分组之后进行过滤来选择分组，而WHERE在分组之前选择记录。另外，WHERE排除的记录不包括在分组中。

(3)分组中使用 WITHROLLUP 。使用 WITHROLLUP 关键字之后，在所有查询出的分组记录后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

(4)多字段分组。使用GROUPBY可以对多个字段进行分组，GROUPBY关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组，依次类推。

(5)GROUPBY和ORDERBY一起使用。某些情况下需要对分组进行排序，在前面的介绍中，ORDERBY用来对查询的记录进行排序，如果和GROUPBY一起使用就可以完成对分组的排序。注意，当使用ROLLUP时，不能同时使用ORDERBY子句进行结果排序，即ROLLUP和ORDERBY是互相排斥的。

2.分组查询

下面回顾一下SELECT语句中子句的顺序。表4-3列出了在SELECT语句中的子及其必须遵循的次序。                           表4-3 SELECT子句及其顺序子句                   说明                                  是否必须使用SELECT              要返回的列或表达式           是FROM               从中检索数据的表               仅在从表选择数据时使用WHERE             行级过滤                             否GROUPBY        分组说明                             仅在按组计算聚集时使用HAVING           组级过滤                             否ORDERBY         输出排序顺序                      否

3.SELECT子句顺序

【任务4.4】   查询的分组与汇总

交叉连接查询返回的结果是被连接的两个表中所有数据行的笛卡儿积，也就是返回第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例如，员工表中有13个员工，销售表中有11条销售记录，那么交叉连接的结果就有13×11=143条数据，其语法格式为：SELECT*FROM表名 1CROSSJOIN 表名2;

1.交叉连接查询

内连接(INNERJOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新记录。也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中，其语法格式为：SELECT查询字段FROM表名1[INNER]JOIN表名2ON表名1.关系字段=表名2.关系字段；在上述语法格式中，“[INNER]JOIN”用于连接两个表，“ON”来指定连接条件，其中“INNER”可以省略。用户也可以使用WHERE子句来实现多表连接查询，其语法格式为：SELECT查询字段FROM表名1，表名2，… WHERE表名1.关系字段=表名2.关系字段[AND连接条件…]；使用WHERE子句定义连接条件比较简单明了，而INNERJOIN语法是ANSI SQL的标准规范，使用INNERJOIN连接语法能够确保不会忘记连接条件，而且，WHERE子句在某些时候会影响查询的性能。

2.内连接查询

（1）LEFTJOIN（左连接）。LEFTJOIN（左连接）返回包括左表中的所有记录和右表中连接字段相等的记录。左连接的结果包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，那么在相关联的结果行中，右表的所有选择列表列均为空值，其语法格式为：SELECT查询字段FROM表名 1LEFTOUTERJOIN 表名2ON表名1.关系字段=表名2.关系字段；

(2)RIGHTJOIN（右连接）。RIGHTJOIN（右连接）返回包括右表中的所有记录和左表中连接字段相等的记录。右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值，其语法格式为：SELECT查询字段FROM表名 lRIGHTOUTERJOIN 表名2ON表名1.关系字段=表名2.关系字段；

3.外连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件来限制查询的结果，使查询结果更加准确。

4.复合条件连接查询

在查询操作时，如果表名很长使用起来就不太方便，这时可以为表取一个别名，用别名来代替表的名称。在MySQL中为表起别名的格式为：SELECT∗FROM 表名[AS]别名；在上面的语法格式中，“AS”关键字用于指定表名的别名，它可以省略不写。

5.为表取别名

【任务4.5】   创建多表连接查询

使用1N关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列中的值将提供给外层查询语句进行比较操作。SELECT语句中可以使用NOTIN关键字，其作用与IN相反、语法格式为：SELECT查询字段FROM表名WHERE字段名[NOT]1N(SELECT语句);

1.带IN关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EX1STS的结果为TRUE，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EX1STS返回的结果是FALSE，此时外层语句将不进行查询。NOTEXISTS与EXISTS的使用方法相同，返回的结果相反。子查询如果至少返回一行，那么NOTEXISTS的结果为FALSE，此时外层查询语句将不进行查询；如果子查询没有返回任何行，那么NOTEXISTS返回的结果是TRUE，此时外层语句将进行查询。其语法格式为：SELECT查询字段FROM表名WHERE[NOT]EXISTS（SELECT语句）；

2.带EXISTS关键字的子查询

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。其语法格式为：SELECT查询字段FROM表名WHERE字段名比较运算符ANY|SOME(SELECT语句)；

3.带ANY、SOME关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。ALL关键字必须接在一个比较操作符后面，表示与子查询返回的所有值比较都为TRUE，则返回TRUE。其语法格式为：SELECT查询字段FROM表名WHERE字段名比较运算符ALL(SELECT语句);

4.带ALL关键字的子查询

【任务4.6】   创建子查询

使用UNION很简单，所要做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION，其语法格式为：SELECT查询字段 FROM表名UNION[ALL]SELECT查询字段 FROM表名；

1.使用UNION

UNION非常容易使用，但在应用过程中需要注意以下几条规则。①UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔(因此，如果组合4条SELECT语句，将要使用3个UNION关键字)。③UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过，各个列不需要以相同的次序列出)。③列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐式转换的类型(如不同的数值类型或不同的日期类型)。如果遵守了这些基本规则或限制，那么可以将UNION用于任何数据检索操作。

2.UNION规则

UNION从查询结果集中自动去除重复的行，换句话说，它的行为与一条SELECT语句中使用多个WHERE子句条件一样。如果想返回所有的匹配行，要使用UNIONALL而不是UNION。

3.包含或取消重复的行

SELECT语句可以使用ORDERBY子句排序。在使用UNION联合查询时，只能使用后条ORDERBY子句，它必须位于最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDERBY子句。

4.对联合查询结果排序

【任务4.7】   创建多表联合查询

用户在查询创建工具中新建查询，首先必须添加表到查询，从左侧数据库对象窗口拖动数据表到图表设计窗口或双击相应的数据表。

用户在查询创建工具中的图表设计窗口，选中相应数据表对象的字段名复选框；也可以选中对象标题的复选框，包含所有字段。

从查询创建工具中的图表设计窗口移除对象，单击对象标题上的关闭按钮。

在查询创建工具中添加表别名，用户只需在图表设计窗口中双击表名并输入别名。

在图表设计窗口选择的字段将会显示在语法窗口，设置它们的显示顺序，以及使用和修改查询输出字段。<Distinct>：相同的记录不会包含在查询结果中。<func>：为每个栏位设置聚集函数(SUM、MAN、MIX、AVG、COUNT)，<别名>：改变输出查询字段名。

添加一个查询条件，在语法窗口的WHERE子句中单击“<按这里添加条件>”，单击“<-->，<->”中的“<->”从当前所建查询中添加的所有数据表的字段列表中选择一个字段。定义条件，在“编辑”选项卡中输入值，单击“=”设置条件运算符。

7.设置查询条件

可以从语法窗口的GROUPBY子句为组查询记录设置条件，它们以设置查询条件相同的方法设置，条件将会包含在当前查询的HAVING语句中。

8.设置查询组条件

可视化创建工具可以从语法窗口ORDERBY子句设置排序查询记录的方法，单击ASC或DESC可改变排序方向。

LIMIT子句用于在指定的范围限制查询记录，可以用它来显示前X条记录，格式为LimitX，X参数指的是返回从0条开始的X条记录行；也可以显示由X~Y之间的记录，格式为Limit X，Y·X参数指定第一个返回记录行的偏移量(第一条记录是0)，Y参数指定返回记录行的最大数目(显示多少条记录)。

【任务4.8】   使用Navicat生成查询

项目四 MySQL数据表的检索

MySQL数据库项目式教程