MySQL数据库项目式教程

命令

博客网址

大林是个Der的博客

大林是个Der→一个不简单的博主

命令以及博客网址

1.MySQL概述：Mysql是一个关系型数据库管理系统，将数据保存在不同的表中，增加了速度并提高了灵活性

①速度：Mysql运行速度很快；

②容易使用：Mysql是一个高性能且相对简单的数据库系统；

③价格：Mysql对多数个人用户来说是免费的；

④支持语言查询：Mysql不仅可以利用SQL[SQL（结构化查询语言）是一种所有现代数据库系统都选用的语言]；还可以利用支持ODBC（开放式数据库连接）的应用程序（ODBC是Microsoft开发的一种数据库通信协议）；

⑤性能：许多客户机可同时连接到服务器，并同时使用多个数据库；

⑥：连接性和安全性：Mysql是完全网络化的，其数据库可在因特网上的任何地方访问；

⑦可移植性：Mysql可运行在多种系统。

2.MySQL的主要特色

3.数据库的技术构成：数据库系统由硬件部分和软件部分共同构成。其中，硬件部分主要用于存储数据库中的数据，包括计算机、存储设备等；软件部分则主要包括DBMS、支持DBMS运行的操作系统，以及支持多种语言进行应用开发的访问技术等。

①.数据库；②.数据库管理系统；③.数据库应用程序。

4.数据库系统的3个主要组成部分

5.数据库是什么：存储数据的仓库。

6.用户可以对数据库执行什么操作：新增、删除、修改、查询。

7.什么是数据库管理系统：专门用于创建和管理数据库的一种软件。

8.什么时候会用到数据库应用程序：在数据库管理系统无法满足用户对数据库的管理时。

9.一个数据库服务器可以管理多少个数据库？答：无数个。一个数据库有多少张表？答：无数张。一个表有多少条数据？答：无数条。

答：树型结构。

10.数据库存储结构整体呈现什么结构？

接触数据库

12.SQL语言的作用？答：主要用于管理数据库中的数据，如存取数据、查询数据、更新数据等。

13.有哪几种SQL语言？答：①DDL 数据定义语言：主要用于定义数据库，数据表等；②DML 数据操作语言：主要用于对数据库中的数据进行添加、修改和删除操作；③DQL 数据查询语言：主要用于查询数据；④DCL 数据控制语言：主要用于控制用户的访问权限。

14.定义、操作语言有什么区别？DDL 数据定义语言：定义数据库、数据表。DML 数据操作语言：操作数据库中的数据。

15.Oracle、SQLServer、MySQL区别？Oracle：良好的兼容性、可移植性、可连接性。SQLServer：界面友好、易于操作。MySQL：免费的、运营成本低。

MySQL的命令行工具

项目1.MySQL印象

1.登录Mysql（登录MySQL时可以省略-h localhost。因为是本地连接，所以-h localhost可以省略）

2.退出Mysql（quit或者\\q）

3.查看数据库状态信息（status;）

4.查看所有数据库（show databases;)

5.创建数据库（create database 数据库名；）

6.删除数据库（drop database 数据库名）

7.查看数据库创建信息（show create database 数据库名;）

8. 修改数据库默认字符集（alter database 数据库名 default character set 新字符集）

9.使用数据库（use 数据库名）*在给数据库进行命令之前都要先使用相关的数据库

10.mysql -h localhost -u root -p中-h含义？-u含义？-p含义？命令缩写 英文含义 中文含义-h host 主机地址-u user 用户名-p password 密码

项目2.创建与维护MySQL数据库

1.创建表（创建表前得先使用数据库）create table 表名(字段名1 字段数据类型1，字段名2 字段数据类型2，。。。。。。。。。。字段名n 字段数据类型n                                *此时应注意最后一个数据类型后不接分号);

2 删除数据表drop table 表名;

3.查看数据库中所有表show tables;

4.查看数据表字段信息describe 表名;desc       表名;             *desc为简写

5.查看数据表创建信息show create table 表名\\G;

6.修改表名alter table 表名 rename 新表名;

7.修改字段名和数据类型alter table 表名 change 原字段名 新字段名 新数据类型;

8.添加字段alter table 表名 add 字段名 数据类型;

9.在表中第一个位置添加字段alter table 表名 add 字段名 数据类型 first;

10.在指定字段之后添加字段alter table 表名 add 字段名 数据类型 after 已存在字段名;

11.删除字段alter table 表名  drop 字段名;

3.外键constraint 字段别名一般为fk_本表字段名 foreign key (本表字段名) references 来源表名(来源表中字段名)

4.其它约束 （在语句后面加上即可）非空  not null唯一  unique默认  default  “默认值”自增  auto_increment

12.数据表的约束

项目3.创建与维护MySQL数据表

1.查询时选择列

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

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

2、检索所有列

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

（2）检索多个字段。要想从数据表中检索多个字段的数据，仍然使用相同的select语句，只需在关键字select后面指定要查找的多个字段的名称，不同字段名称之间用逗号（，）隔开，最后一个字段后面不需要加逗号，语法格式为：SELECT 字段1，字段2，...，字段n FROM 表名;有些情况，显示的字段名会很长，不能直观，可以指定字段别名替换字段或表达式，为字段定义别名的基本语法格式为：字段名 [AS] 字段别名其中字段名为表中字段定义的名称，字段别名为字段新的名称，AS关键字为可选参数。注意，MySQL中的SQL语句时不区分大小写的，因此SELECT和select的作用是相同。

3、检索指定列

2、带IN关键字的查询IN操作符用来指定条件范围，范围每一个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号的合法值，语法格式为：SELECT 字段名1，字段名2，...，字段名n FROM 表名 WHERE 字段名 IN（值1，值2，...）;

（1)百分号通配符（％）。通配符“％”匹配任意长度的字符，甚至包括零字符，可以在搜索模式中的任意位置使用，并且可以使用多个通配符。

2)下划线通配符（＿）。通配符“＿”的用法与“％”相同，区别是“％”可以匹配多个字符，而“＿”只能匹配任意单个字符。若需要匹配多个字符，则使用相同个数的“＿”。

4.查询时选择行

5.查询结果排序

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

①COUNT(*):计算表中总的行数，不管某列是否有数值或为空值。

②COUNT(字段名）：计算指定列下总的行数，计算时将忽略空值的行。注意，指定列的值为空的行被COUNT()函数忽略，但是如果不指定列，而在COUNT()函数中使用星号（＊），则所有记录都不忽略。

（2)COUNT()函数。COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数，其使用方法有以下两种。

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

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

1、聚集函数

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

（1)创建分组。分组是使用SELECT语句的 GROUP BY子句建立的。GROUPBY关键字通常和聚集函数一起使用，如MAX()、MINO、COUNT()、SUM()、AVG().例如，要返回员工表中每个部门的员工人数，这时就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。

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

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

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

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

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

2.分组查询

6.查询分组与汇总

数据库管理系统的一个最重要的功能就是数据查询，数据查询不仅是简单查询数据库中存储的数据，还更要对数据进行筛选，以及确定数据以怎样的格式显示。MySQL提供了功能强大、灵活的语句来实现这样操作，本项目将介绍如何使用SELECT语句查询数据表中的一列或多列数据、连接查询，子查询，以及使用Navicat生成查询等。

项目4.MySQL数据表的检索

视图中包含了SELECT查询的结果，因此视图的创建基于SELECT语句和已存在的数据表。视图可以建立在一张表上，也可以建立在多张表上。在MySQL中，创建视图使用CREATE VIEW语句，其基本语法格式为：

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

1.创建视图：

2.视图的规则和限制：

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

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

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

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

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

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

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

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

9.设置排序准则：可视化创建工具可以从语法窗口 ORDER BY子句设置排序的方法，单击ASC或 DESC可改变排序方向。

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

3.使用图形管理工具创建视图

1.使用 CREATE OR REPLACE VIEW 语句修改视图：在MySQL中，使用CREATE OR REPLACE VIEW语句修改视图，其语法格式为：CREATE [OR REPLACE] [ALGORITHM={UNDEFINEDTEMPTABLE} ]VIEW view_name [ (column_list) ]AS select_statement[WITH [CASCADED|LOCAL] CHECK OPTION]可以看到，修改视图的语句和创建视图的语句是完全一样的。如果视图已经存在，那么使用修改语句对视图进行修改；如果视图不存在，那么将创建一个新视图。

2.使用ALTER 语句修改视图：ALTER语句是MySQL提供的另外一种修改视图的方法，其修改视图的语法格式为：ALTER [ALGORITHM= {UNDEFINED|MERGE|TEMPTABLE} ]VIEW view_name [ (column_list) ]AS SELECT_statement[WITH [CASCADED|LOCAL]CHECK OPTION]这个语法中的关键字与前面创建视图的关键字是一样的，这里不再介绍。

修改视图是指修改数据库中存在的视图，例如，当基本表的某些字段发生变化时，可以通过修改视图的方式来保持视图与基本表的一致性。在MySQL中，可以通过CREATE OR REPLACE VIEW 语句和ALTER 语句来修改视图。

4.修改视图

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

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

（1)视图中不包含基本表中被定义为非空的列。

（2)在定义视图的SELECT语句后的字段列表中使用了数学表达式。

（3)在定义视图的SELECT语句后的字段列表中使用了聚合函数。

（4)在定义视图的SELECT 语句中使用了 DISTINCT、UNION、LIMIT、GROUP BY或 HAVING子句。

需要注意的是，尽管更新视图有多种方式，但并非所有情况下都能执行视图的更新操作。当视图中包含如下内容时，视图的更新操作将不能被执行。

3.使用DELETE 语句更新视图：在MySQL中，可以使用DELETE语句对视图中的基本表删除部分记录。

更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。当通过视图更新数据时，其实是在更新基本表中的数据，如果对视图增加或删除记录，实际上是对其基本表增加或删除记录。视图更新主要有3种方法：UPDATE、INSERT和DELETE。

5.利用视图更新数据表

6.删除视图

视图是一个从或多个表中导出来的表，它是一种虚拟存在的表，并且表的结构和数据都依赖于基本表，通过视图不仅可以看到存放在基本表中的数据，还可以像操作基本表一样，对视图中存放的数据进行查询、修改和删除。与直接操作基本表相比，视图具有以下特点：①简单化；②安全性；③逻辑数据独立性。

项目5.创建与使用视图

（1）用户变量。用户可以在PL/SQL中使用自己定义的变量，这样的变量称为用户变量。可以先在用户变量中保存一个数据，然后在以后的语句中引用该变量，这样就可以将数据从一条语句传递到另一条语句。用户变量在使用前必须定义和初始化，如果使用没有初始化的变量，其值NULL。定义和初始化一个用户变量可以使用SET语句，其语法格式为:SET @<变量名1>=<表达式1>[，@<变量名2>=<表达式2>，…];说明:       ① 用户变量以“@”开始，形式为“@变量名”，以便将用户变量和字段名予以区别。变量名必须符合 MySQL标识符的命名规则，即变量可以由当前字符集的字母、数字、“.”“_”和“$”组成，默认字符集是cp1252(Latin 1)。       ②<表达式>可以为整数、实数、字符串或NULL值，例如:        SET @name\"OPPO\

（2）系统变量。MySQL可以访问许多系统变量和连接变量，当服务器运行时许多变量可以动态更改。这样通常允许修改服务器操作而不需要停止并重启服务器。服务器维护两种变量，全局变量影响服务器整体操作，会话变量影响具体客户端连接的操作。       系统变量一般都以“@@”为前缀，如@@Version 返回 MySQL的版本。但某些特定的系统变量可以省略“@@”符号，如Current_Date、Current_ Time 和Current_User。

3）局部变量，在语句块（BEGIN到Nnd间)中定义的变量为局部变量，局部变量可以保存特定类型数据，其有效作用范围在存储过程和自定义函数的语句块中，在语句块结束以后，局部变量就失效了。       MySQL的局部变量必须先声明后使用。使用DECLARE语句声明局部变量，其声明语法格式为:        DECLARE<变量名称><数据类型>[DEFAULT<默认值>];        说明:        ①“DEFAULT”子句为变量指定默认值，若不指定则默认为 NULL。        ②变量名称必须符合MySQL标识符的命名规则，在局部变量前面不使用“@”符号。        例如:        DECLARE unit char(2);

1.PL/SQL的变量：      变量是指在程序运行过程中其值可以改变的量，包括用户变量、系统变量和局部变量。

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

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

(3)运算符的优先级。当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的次序有时会影响所得到的运算结果。MySQL 运算符优先级如表 6-1所示，当一个表达式中的两个运算符有相同的优先级时，根据它们在表达式中的位置，一般而言，一元运算符按从右到左、（即右结合性）的顺序运算，二元运算符按从左到右（即左结合性）的顺序运算。

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

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

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

其语法格式为：CALL 存储过程名(［<实参列表>]）；说明：如果定义存储过程时使用了参数，那么调用该存储过程时，也要使用参数，并且参数个数和顺序必须一一对应

4.调用存储过程：存储过程创建完成后，可以在程序、触发器或其它存储过程中被调用

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

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

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

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

5.事务的ACID特性

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

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

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

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

2.事务控制语句

SQL 语句没有流程控制，无法实现复杂的应用，PL/SQL 语言 （Procedural Language/SQL）是将结构化查询与数据库过程控制结合为一体的强大语言，PL/SQL 不旦支持更多的数据类型，拥有变量声明、赋值语句，而且有选择、循环等流程控制语句。       系统开发过程中，经常会有同一个功能模块多次调用的情况，如果每次都编写代码会浪费大量的时间，为了解决这类问题，MySQL 5.0开始引入存储过程。存储过程是一组为了完成特定功能的 PL/SQL 语句集，经编译后存储在数据库中，用户可以重复使用该存储过程，这样可以降低数据库开发人员的工作量。       游标是处理数据的一种方法，为了查看或处理结果集中的数据，游标提供了在结果集中一次一行浏览数据的能力。       事务是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单元。

项目6.创建与使用存储过程

触发器有以下3个作用。       (1)安全性。可以使用户具有操作数据库的特定权利。例如，在“销售表”中插入一条销售记录，可以通过触发器实现对“商品一览表”中库存的更新，而不用直接将“商品一览表”展现在特定用户面前。       (2)实现复杂的数据完整性。实现数据完整性约束或规则，触发器可实现比约束更复杂的限制。       (3)实现复杂的非标准数据相关完整性。触发器可以对数据库中相关的表进行级联更新。例如，在“销售表”中插入一条销售记录，可以通过触发器实现对“商品一览表”中库存的级联更新。

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

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

2.创建触发器创建触发器使用 CREATE TRIGGER 语句，其语法格式为：CREATE TRIGGER 触发器名 触发时刻 触发事件 ON 表名       FOR EACH ROW      触发器动作；

1）SHOW TRIGGERS 的语法格式为：SHOW TRIGGERS  [FROM 数据库名]；

2）SELECT 的语法格式为：SELECT * FROM Information_Schema.TriggersWHERE Trigger_Name=<触发器名>;

3.查看触发器查看触发器使用SHOW TRIGGERS 语句和 SELECT 语句。

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

数据表中为了保证数据的完整性或执行其它特殊规则，MySQL除了提供约束之外，还提供了另外一种机制：触发器（trigger）。

项目7.创建与使用触发器

(1)user权限表。user是MySQL中最重要的一个权限表，user列主要分为4个部分:用户列、权限列、安全列和资源控制列。        ①用户列:用户登录时通过表中的Host、User和Password列判断连接的IP、用户名称和密码是否存在于表中来通过身份验证或拒绝连接。        ②权限列:user表中包含多个以“_priv”结尾的字段，这些字段决定了该用户的权限，既包括查询权限、插入权限、更新权限、删除权限等普通权限，也包括关闭服务器和加载用户等高级管理权限。        ③ 安全列:ssl(加密)、x509(标识用户)开头的字段，以及 plugin 和 authentication 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通过权限表来控制用户对数据库的访问，MySQL数据库在安装时会自动安装多个数据库。MySQL权限表存放在名称为MySQL的数据库中。常用的权限表有 user、db、host、table_priv、columns_priv和 procs_ priv。

2.添加用户：新安装的MySQL 中只有一个名称为root的用户。这个用户是安装服务器时由系统创建并赋予了 MySQL 的所有权限。在对 MySQL 的实际操作中，通常需要创建不同层次要求的用户来确保数据的安全访问。添加用户可以通过 CREATE USER INSERT和 GRANT语句来实现。

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

3.授予用户权限：GRANT语句仅是语句，可以达到添加新用户或修改用户密码的作用。

(1)使用SHOWGRANTS语句查看授权信息，其语法格式为:SHOW GRANTS FOR<‘用户名’>@<‘主机’>

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

4.查看用户权限

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

5.回收用户权限

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

1.修改用户的名称

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

(2)使用SET PASSWORD语句修改用户密码的语法格式为:SET PASSWORD[FOR<'用户名>@<主机'>]=PASSWORD<‘新密码’>);说明:SET PASSWORD语句可以修改用户的密码，语句中若不加“[FOR<'用户名'>@<'主机'>]”可选项，则修改当前用户密码。

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

2.修改用户的密码

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

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

3.删除用户

项目8.数据库的安全性维护

MySQL数据库项目式教程