文章目录
MySQL
1、什么是MySQL
MySQL实际上就是一个将数据的所有存储。检索。管理和处理的一个数据库软件。
为什么使用MySQL
成本——MySQL是开放源代码的,一般可以免费使用(甚至可以免费修改)。
性能——MySQL执行很快(非常快)。
可信赖——某些非常重要和声望很高的公司、站点使用MySQL,这些公司和站点都用MySQL来处理自己的重要数据。
简单——MySQL很容易安装和使用。
2、数据库基本操作
返回一个可用数据库列表
返回数据库里面的表的列表
SHOW COLUMNS要求给出一个表名(这个例子中的FROM customers),它对每个字段返回一行,行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息(如字段cust_id的auto_increment)。
3、创建和操作表
3.1、创建表
有两种方式
- 使用具有交互式创建和管理表的工具(如第2章讨论的工具
- 表也可以直接用MySQL语句操纵。
3.1.1、利用CREATE TABLE 创建表,必须给出下列信息:
- 新表的名字,在关键字之后
- 表列得名字和定义,用逗号分割
例如
CREATE TABLE <表名>([表定义选项])[表选项][分区选项];
-- 其中表定义选项格式为:
<列名1><类型1>[,...]<列名n><类型n>
#从另一张表复制表结构创建表:
CREATE TABLE tb_name LIKE tb_name_old
#从另一张表的查询结果创建表:
CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options
3.1.2、 主键的介绍
正如所述,主键值必须唯一。即,表中的每个行必须具有唯一的主键值。如果主键使用单个列,则它的值必须唯一。如果使用多个列,则这些列的组合值必须唯一
为创建由多个列组成的主键,应该以逗号分隔的列表给出各列名,例如:
这里用了order_num以及order_item作为主键
综上所述: 主键可以在创建表时定义,也可以在创建表之后定义;
3.1.3、 使用AUTO_INCREMENT
cust_id int NOT NULL AUTO_INCREMENT
这里AUTO_INCREAMENT告诉MySQL,本列每当增加一行时自动增量,每次执行一个INSERT操作时,MySQL自动对该列增量,给该列赋予下一个可用的值,就会给cust_id分配一个唯一的值
每个表只允许有一个AUTO_INCREMENT列,而且它必须被索引
3.1.4、 指定默认值
如果在插入行时没有给值,MySQl允许指定此时使用默认值,在创建表时用DEFAULT表示
但是MySQL不允许使用函数作为默认值,只支持常量
使用默认值,而不是NULL值 许多数据库开发人员使用默认值而不是NULL列,特别是对用于计算或数据分组的列更是如此。
3.1.5、 引擎类型
MySQL有一个具体管理和处理数据内部的引擎。允许你使用create table 语句时,该引擎具体创建表,而在你使用select语句时进行其他数据库时,该引擎在内部处理你的请求。多数的时候,此引擎都影藏在DBMS服务器内。
引擎类型:
- InnoDB是一个可靠的事务处理引擎(参见第26章),它不支持全文本搜索;
- MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);
- MyISAM是一个性能极高的引擎,它支持全文本搜索(参见第18章),但不支持事务处理。
外键不能跨引擎 混用引擎类型有一个大缺陷。外键(用于强制实施引用完整性,如第1章所述)不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
3.2、 更新表
使用ALTER TABLE
- 在ALTER TABLE 之后要给出要更改的表名,
- 所作更改的列
增加一个列
删除刚刚增加的列
3.3、 删除表
3.4、 重命名表
RENAME TABLE tbl_user to tb_user;
ALTER TABLE TABLENAME RENAME TO NEW TABLENAME
3.5、数据库数据类型概述
4、检索数据
4.1 SELECT语句
mysql中检索数据使用select构成的语句
4.2 检索列
检索单个列
SELECT 列名 FROM 表名 ————该语句就是从一个表中检索出一整列的数据
检索多个列
SELECT 列名,列名,列名… FROM 表名 ————从某个表中检索多个列
检索所有列
SELECT * FROM tablename (通常不用,如果数据量大的话造成内存损耗,资源过量)
限制结果
SELECT语句返回所有匹配的行,他们可能是指定表中的每个行,为了返回第一行或者前几行,可用LIMIT字句
SELECT COLUMNname FROM TABLENAME LIMIT 5;
这里表示返回的数据不多于五行
SELECT COLUMNname FROM TABLENAME LIMIT 5,5;
这里表示返回的数据是从第五行开始的五行,第一个5表示开始的位值,第二个5表示要检索的行数
特别注意
- 检索出来的第一行为行0而不是行1。因此,LIMIT 1, 1将检索出第二行而不是第一行
- 在行数不够时 LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行(例如,给出LIMIT 10, 5,但只有13行),MySQL将只返回它能返回的那么多行。
4.3 检索行
有时检索列时会出现重复的行的数据,此时想要去重,就有一个关键字:DISTINCT(指示MySQL只能返回不同的值)
用例mysql SELECT DISTINCT colunmname FROM TABLENAME
4、检索数据的排序
按单个列排序
ORDER BY
:SELECT 列名1 FROM 表名 ORDER BY 列名2(表示排序按 列名2 来排序)
按多个列排序
SELECT 列名1,列名2 FROM 表名 ORDER BY 列名2,列名1
(先按列名2排,如果相同则再按列名1排)
指定排序方向:DESC
按降序排序ASC
按升序排序
使用:SELECT 列名1,列名2 FROM 表名 ORDER BY 列名2 DESC,列名1 ASC
LIMIT 和 ORDER BY 的组合使用
SELECT u_name,u_grade FROM tb_user ORDER BY u_grade DESC LIMIT 1
可以检索出成绩排第一的人
注意事项:
- ORDER BY子句的位置——在给出ORDER BY子句时,应该保证它位于FROM子句之后。如果使用LIMIT,它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。
- 区分大小写和排序顺序——在对文本性的数据进行排序时,A与a相同吗?a位于B之前还是位于Z之后?这些问题不是理论问题,其答案取决于数据库如何设置。
在字典(dictionary)排序顺序中,A被视为与a相同,这是MySQL(和大多数数据库管理系统)的默认行为。但是,许多数据库管理员能够在需要时改变这种行为(如果你的数据库包含大量外语字符,可能必须这样做)。
5、过滤数据
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BENTWEEN | 在指定的两个值之间 |
在SELECT语句中,数据根据WHERE子句中指定的搜索条件进行过滤WHERE子句在表名(FROM子句)之后给出
检查单个值
SELECT u_name FROM tb-user WHERE u_grade = 100;
不匹配检查
SELECT u_name FROM tb_user WHRER u_grade <> 100
(检索出成绩不是一百的学生)
范围检索
SELECT u_name FROM tb_user WHRER u_grade BETWEEN 50 AND 100
(检索成绩在50到一百之间的学生)
空值检索
SELECT u_name FROM tb_user WHRER u_grtade is NULL
(检索返回u_grade列为NULL的学生名字)
5.1 操作符过滤
- AND操作符:
为了通过不止一个列进行过滤,可使用AND操作符给WHERE子句附加
条件,每添加一个条件就要加一个AND
- OR 操作符:
OR操作符与AND操作符不同,它指示MySQL检索匹配任一条件的行。- 计算次序:
先处理AND字符 后处理OR字符- IN 操作符:
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单,全都括在圆括号中。(SELECT u_name FROM tbl_user WHERE u_grade IN (97,99)
)
IN可以包含其他操select语句,动态的建立WHERE语句- NOT 操作符:
额外的否定条件- LIKE操作符与百分号(%)通配符
用来匹配值的一部分的特殊字符。
SELECT u_name FROM tbl_user WHERE u_grade LIKE ‘4%’
(检索任何4开头的数据)区分大小写
SELECT u_name FROM tbl_user WHERE u_grade LIKE ‘%4%’
(检索任何包含文本4的数据,不论前后有什么字符)
- 下划线(_)通配符
用法和%一样,但是_只能匹配单个字符,而%能匹配多个字符
注意事项:
- 在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处,它能消除歧义。
- MySQL 中的NOT MySQL 支持使用NOT对IN、BETWEEN和EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
5.2 通配符过滤
(1)基本字符匹配:
select col from table1 where col regexp 'abc'
匹配包含词abc的行;
select col from table1 where col regexp '.abc'
. 是正则表达式语言中的一个特殊字符,它表示匹配任意一个字符,类似于like关键词搜索中的 _
(2)进行OR匹配:
正则表达式语言中 “|” 作为or操作符,表示匹配其中之一。
select col from table1 where col regexp 'abc|def|ghi';
(3)匹配几个字符之一:
如果要匹配一组特定的字符,可以使用中括号([ ])将字符括起来。
select col from table1 where col regexp ‘[123]abc’;将返回含有 1abc 或 2abc 或 3abc 的行。
(4)范围匹配:
[0-9]相当于[0123456789],可以匹配任意数字;同理[a-z]可以匹配任意字母。
select col from table1 where col regexp '[1-3]abc';
将返回含有 1abc 或 2abc 或 3abc 的行。
(5)匹配特殊字符:
MySQL正则表达式中,匹配特殊字符需要使用"\"为前导,这种处理叫做转义,相当于将具有特殊含义的字符转换为字符本身。在匹配所有特殊字符时,都要进行转义。
select col from table1 where col regexp '\\_';
返回含有_字符的行
元字符 | 说明 |
---|---|
\f | 换页 |
\n | 换行 |
\r | 回车 |
\t | 制表 |
\v | 纵向制表 |
(6)匹配字符类:
如果我们要匹配所有数字、所有字母等内容,可以使用字符类来进行简化操作;
类 | 说明 |
---|---|
[:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |
[:alpha:] | 任意字符(同[a-zA-Z]) |
[:blank:] | 空格和制表(同[\t]) |
[:cntrl:] | ASCII控制字符(ASCII |
[:digit:] | 任意数字(同[0-9]) |
[:graph:] | 与[:print:]相同,但不包括空格 |
[:lower:] | 任意小写字母(同[a-z]) |
[:print:] | 任意可打印字符 |
[:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 |
[:space:] | 包括空格在内的任意空白字符(同[\f\n\r\t\v]) |
[:upper:] | 任意大写字母(同[A-Z]) |
[:xdigit:] | 任意十六进制数字(同[a-fA-F0-9]) |
(7)匹配多个实例:
如果要对匹配的文本的重复次数进行控制,需要使用重复元字符。
元字符 | 说明 |
---|---|
^ | 文本的开始处 |
$ | 文本的结尾处 |
[[:<:]] | 词的开始处 |
[[:>:]] | 词的结尾处 |
6、使用数据处理函数
- 用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数)
- 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)
的数值函数。- 用于处理日期和时间值并从这些值中提取特定成分(例如,返回
两个日期之差,检查日期有效性等)的日期和时间函数。- 返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本
细节)的系统函数。
常见函数: MySQL常见函数
7、分组数据
GROUP BY子句指示MySQL分组数据,然后对每个组而不是整个结果集进行聚集。
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制。
- 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列
中有多行NULL值,它们将分为一组。
关键字WITH ROLLUP的使用
WITH POLLUP 关键字用来在所有记录的最后加上一条记录,这条记录是上面所有记录的总和,即统计记录数量。
下面根据 tb_students_info 表中的 sex 字段进行分组查询,并使用 WITH ROLLUP 显示记录的总和。
mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info
-> GROUP BY sex WITH ROLLUP;
+------+------------------------------------------------------+
| sex | GROUP_CONCAT(name) |
+------+------------------------------------------------------+
| 女 | Henry,Jim,John,Thomas,Tom |
| 男 | Dany,Green,Jane,Lily,Susan |
| NULL | Henry,Jim,John,Thomas,Tom,Dany,Green,Jane,Lily,Susan |
+------+------------------------------------------------------+
3 rows in set (0.00 sec)
查询结果显示,GROUP_CONCAT(name) 显示了每个分组的 name 字段值。同时,最后一条记录的 GROUP_CONCAT(name) 字段的值刚好是上面分组 name 字段值的总和。
7.1 过滤分组
关键字 HAVING
HAVING <查询条件>
HAVING 关键字和 WHERE 关键字都可以用来过滤数据,且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
但是 WHERE 和 HAVING 关键字也存在以下几点差异:
- 一般情况下,WHERE 用于过滤数据行,而 HAVING 用于过滤分组。
- WHERE 查询条件中不可以使用聚合函数,而 HAVING 查询条件中可以使用聚合函数。
- WHERE 在数据分组前进行过滤,而 HAVING 在数据分组后进行过滤 。
- WHERE 针对数据库文件进行过滤,而 HAVING 针对查询结果进行过滤。也就是说,WHERE 根据数据表中的字段直接进行过滤,而 HAVING 是根据前面已经查询出的字段进行过滤。
- WHERE 查询条件中不可以使用字段别名,而 HAVING 查询条件中可以使用字段别名。
7.2 SELECT 子句顺序
子句 | 说明 | 是否必须是使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
8、联结表
8.1 交叉连接(CROSS JOIN)
8.1.1 笛卡尔积
笛卡尔积是交叉连接的产物
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。 会产生许多,多余的、繁冗的、无用的、错误的数据
笛卡尔积(Cartesian product)是指两个集合 X 和 Y 的乘积。
例如,有 A 和 B 两个集合,它们的值如下:
A = {1,2}
B = {3,4,5}
集合 A×B 和 B×A 的结果集分别表示为:
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };
以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。
8.1.2 语法说明
交叉连接的语法格式如下:
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]
语法说明如下:
- 字段名:需要查询的字段名称。
- <表1><表2>:需要交叉连接的表名。
- WHERE 子句:用来设置交叉连接的查询条件。
由于交叉连接会产生巨大的数据量所以一定要加上WHERE语句
8.2 内连接(INNER JOIN)
语法格式如下:
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
语法说明如下:
- 字段名:需要查询的字段名称。
- <表1><表2>:需要内连接的表名。
- INNER JOIN :内连接中可以省略 INNER 关键字,只用关键字 JOIN。
- ON 子句:用来设置内连接的连接条件。
8.3 外连接
8.3.1 左外连接(LEFT OUTER JOIN)
语法格式如下:
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
语法说明如下:
- 字段名:需要查询的字段名称。
- <表1><表2>:需要左连接的表名。
- LEFT OUTER JOIN:左连接中可以省略 OUTER 关键字,只使用关键字 LEFT JOIN。
- ON 子句: 用来设置左连接的连接条件,不能省略。
LEFT OUTER JOIN从FROM子句的左边表中选择所有行。
8.3.1 右外连接(LEFT OUTER JOIN)
语法格式如下:
SELECT <字段名> FROM <表1> RIGHTOUTER JOIN <表2> <ON子句>
语法说明如下:
- 字段名:需要查询的字段名称。
- <表1><表2>:需要右连接的表名。
- LEFT OUTER JOIN:右连接中可以省略 OUTER 关键字,只使用关键字 RIGHT JOIN。
- ON 子句: 用来设置左连接的连接条件,不能省略。
L从右边的表中选择所有行,应该使用RIGHT OUTER JOIN
9、组合查询(UNION)
9.1 如何使用UNION
- UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔
- UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。
- 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型)。
例1:
特别注意:
- UNION会默认去除重复的行,如果不想去重则需要使用 UNION ALL 而不是 UNION
10、全文本搜索
10.1 为什么要用全文本搜索
前面有提过的通配符搜索和正则表达式搜索有极大的弊端:
- 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引),因此,由于搜索行数的不断增加,这些搜索可能非常耗时
- 明确控制——使用通配符和正则表达式匹配,很难(而且并不总是能)明确地控制匹配什么和不匹配什么。
- 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索,但它们都不能提供一种智能化的选择结果的方法。
10.2 如何使用全文搜索
为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引。在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引。
在索引之后,SELECT可与Match()和Against()一起使用以实际执行 搜索。
10.3 启用全文本搜索支持
一般在创建表时启用全文本搜索。CREATE TABLE语句 接受FULLTEXT子句,它给出被索引列的一个逗号分隔的列表。
例1:
分析如下:
这些列中有一个名为note_text的列,为了进行全文本搜索,MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列,如果需要也可以指定多个列。
——在定义之后,MySQL自动维护该索引索引随之自动更新。
10.4 进行全文本搜索
在索引之后,使用两个函数Match()和Against()执行全文本搜索其中Match()指定被搜索的列,Against()指定要使用的搜索表达式
例2:
分析如下: 此SELECT语句检索单个列note_text。由于WHERE子句,一个全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索,Against(‘rabbit’)指定词rabbit作为搜索文本。由于有两行包含词rabbit,这两个行被返回。
使用说明,及注意点:
- 使用完整的Match()说明 传递给 Match() 的值必须与 FULLTEXT() 定义中的相同。如果指定多个列,则必须列出它们(而且次序正确)。
- 搜索不区分大小写 除非使用BINARY方式,否则全文本搜索不区分大小写。
- 如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%的行中)
- 忽略词中的单引号。例如,don’t索引为dont。
- 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。
- 如前所述,仅在MyISAM数据库引擎中支持全文本搜索。
11、插入数据以及更新和删除数据
11.1 插入数据基本语法
- INSERT…VALUES语句
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];
- INSERT…SET语句
NSERT INTO <表名>
SET <列名1> = <值1>,
<列名2> = <值2>,
…
11.2 使用INSERT INTO … FROM 语句复制表数据
mysql> INSERT INTO tb_courses_new
-> (course_id,course_name,course_grade,course_info)
-> SELECT course_id,course_name,course_grade,course_info
-> FROM tb_courses;
分析如下:将查询表中的结果复制到 tb_courses_new 中
11.3 更新和删除的基本语法
#更新
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]
#删除
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
12、视图和索引
12.1 视图
1)视图是什么:
视图就是一张虚拟的表
2)为什么要用视图:
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
3)视图的规则和限制
- 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
- 对于可以创建的视图数目没有限制。
- 为了创建视图,必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
- ORDER BY可以用在视图中,但如果从该视图检索数据SELECT中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖。
- 视图不能索引,也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。
12.1.1 使用视图
- 视图用CREATE VIEW语句来创建。
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
- 用DROP删除视图,其语法为DROP VIEW viewname;。
- 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。
12.1.2 视图的作用
- 利用视图简化复杂的联结
- 利用视图重新格式化检查出的数据
- 利用视图过滤不想要的数据
- 用视图与计算字段
13、存储过程
13.1 存储过程是什么
在数据库的实际操作中,经常会有需要多条 SQL 语句处理多个表才能完成的操作。
而存储过程就是为了完成特定功能的SQL语句,使用存储过程的目的就是:将常用的或者复杂的工作预先用SQL语句写好并用指定名称存储起来,这个过程经编译和优化后存储在数据库中,所以叫做存储过程
而之后想做相同的操作时只需要调用“CALL存储过程的名字”即可自动完成
存储过程的优点:
- 封装性
- 可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。 - 可减少网络流量
由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。 - 高性能
当存储过程被成功编译后,就存储在数据库服务器里了,以后客户端可以直接调用,这样所有的 SQL 语句将从服务器执行,从而提高性能。但需要说明的是,存储过程不是越多越好,过多的使用存储过程反而影响系统性能。 - 提高数据库的安全性和数据的完整性
存储过程提高安全性的一个方案就是把它作为中间组件,存储过程里可以对某些表做相关操作,然后存储过程作为接口提供给外部程序。这样,外部程序无法直接操作数据库表,只能通过存储过程来操作对应的表,因此在一定程度上,安全性是可以得到提高的。 - 使数据独立
数据的独立可以达到解耦的效果,也就是说,程序可以调用存储过程,来替代执行多条的 SQL 语句。这种情况下,存储过程把数据同用户隔离开来,优点就是当数据表的结构改变时,调用表不用修改程序,只需要数据库管理者重新编写存储过程即可。
13.2 创建存储过程
创建语法:
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>
删除语法:
DROP PROCEDERE productpricing
检查存储过程:
SHOW CREATE PROCEDURE ordertotal
在创建存储过程时可能语句包含 ; 而mysql默认结束语句也是;所以需要 DELIMITER // 告诉命令行实用程序使用//作为新的语句结束分隔符, 而不是 ;
例 1
创建名称为 ShowStuScore 的存储过程,存储过程的作用是从学生成绩信息表中查询学生的成绩信息,输入的 SQL 语句和执行过程如下所示。
mysql> DELIMITER //
mysql> CREATE PROCEDURE ShowStuScore()
-> BEGIN
-> SELECT * FROM tb_students_score;
-> END //
Query OK, 0 rows affected (0.09 sec)
例 2 无参数的存储过程
--创建名为 GetStuCou 的无参数存储过程
create procedure GetStuCou
as
begin
select *
from Student s
left join Course c on s.C_S_Id=c.C_Id
end
--执行名为 GetStuCou 的无参数存储过程
execute GetStuCou
例 3 有返回值的存储过程:
--创建名为 GetStuCou_Re 的有返回值的存储过程
create procedure GetStuCou_Re
as
begin
insert into Course(C_Name) values('HTML5')
return SCOPE_IDENTITY(); -- 返回为当前表插入数据最后生成的标识值。
end
--执行名为 GetStuCou 的有返回值的存储过程
execute GetStuCou_Re
例 4 有输入参数的存储过程:
--创建名为 GetStuCou_In 的有输入参数的存储过程
create procedure GetStuCou_In
@StuNo nvarchar(64)='001' --设置默认值
as
begin
select * from Student where S_StuNo=@StuNo
end
--执行名为 GetStuCou_In 的有输入参数的存储过程(不传参数,即使用默认值)
execute GetStuCou_In
--执行名为 GetStuCou_In 的有输入参数的存储过程(传入参数)
execute GetStuCou_In '005'
例 5 有输入、输出参数的存储过程:
--创建名为 GetStuCou_Out 的有输入参数和输出参数的存储过程
create procedure GetStuCou_Out
@StuNo nvarchar(64),
@Height nvarchar(32) output
as
begin
if(@StuNo is not null and @StuNo <> '')
begin
select @Height=S_Height
from Student
where S_StuNo=@StuNo
end
else
begin
set @Height='185'
end
end
--执行名为 GetStuCou_Out 的有输入参数和输出参数的存储过程
execute GetStuCou_Out '005',null
14、触发器
想要某条语句(或某些语句)在事件发生时自动执行的东西就是触发器
14.1 创建触发器
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该响应的活动(DELETE、INSERT或UPDATE);
- 触发器何时执行(处理之前或之后)。
CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>
语法说明如下。
- 触发器名
触发器的名称,触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建,名称前面应该加上数据库的名称。 - INSERT | UPDATE | DELETE
触发事件,用于指定激活触发器的语句的种类。
注意:三种触发器的执行时间如下。
INSERT:将新行插入表时激活触发器。例如,INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活,也能被 LOAD DATA 语句激活。
DELETE: 从表中删除某一行数据时激活触发器,例如 DELETE 和 REPLACE 语句。
UPDATE:更改表中某一行数据时激活触发器,例如 UPDATE 语句。 - BEFORE | AFTER
BEFORE 和 AFTER,触发器被触发的时刻,表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件,则使用 BEFORE 选项;若希望在激活触发器的语句执行之后完成几个或更多的改变,则通常使用 AFTER 选项。 - 表名
与触发器相关联的表名,此表必须是永久性表,不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如,对于一张数据表,不能同时有两个 BEFORE UPDATE 触发器,但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器,或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。 - 触发器主体
触发器动作主体,包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句,可使用 BEGIN…END 复合语句结构。 - FOR EACH ROW
一般是指行级触发,对于受触发事件影响的每一行都要激活触发器的动作。例如,使用 INSERT 语句向某个表中插入多行数据时,触发器会对每一行数据的插入都执行相应的触发器动作。
例1、
创建BEFORE类型触发器
mysql> CREATE TRIGGER SumOfSalary
-> BEFORE INSERT ON tb_emp8
-> FOR EACH ROW
-> SET @sum=@sum+NEW.salary;
Query OK, 0 rows affected (0.35 sec)
例2、
创建AFTER触发器
mysql> CREATE TRIGGER double_salary
-> AFTER INSERT ON tb_emp6
-> FOR EACH ROW
-> INSERT INTO tb_emp7
-> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
Query OK, 0 rows affected (0.25 sec)
14.2 查看触发器
SHOW TRIGGERS;
在表中查看、
SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';
SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME= '触发器名字'\G
14.3 修改和删除触发器
DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>
语法说明如下:
- 触发器名
要删除的触发器名称。 - 数据库名
可选项。指定触发器所在的数据库的名称。若没有指定,则为当前默认的数据库。 - 权限
执行 DROP TRIGGER 语句需要 SUPER 权限。 - IF EXISTS
可选项。避免在没有触发器的情况下删除触发器。