MySQL 基础语法总结

王富贵富了吗
127 阅读19分钟

学 MySql 不得先过一遍 SQL 语句

数据库是什么？

MySQL数据库简介 (biancheng.net)对数据库的解释如下：

数据库是计算机应用系统中的一种专门管理数据资源的系统。数据有多种形式，如文字、数码、符号、图形、图像及声音等，数据是所有计算机系统所要处理的对象。我们所熟知的一种处理办法是制作文件，即将处理过程编成程序文件，将所涉及的数据按程序要求组成数据文件，再用程序来调用，数据文件与程序文件保持着一定的关系。

数据库，库，意思是仓库，那么数据库可以理解为，存放数据的仓库。

数据是什么？数据是一个庞大的概念，可以理解为，记录下来的信息，就是数据。

在没有电子产品的时代，我们只有纸笔。在宫廷剧中，古人用卷轴，竹筒记录下进宫的人的信息，包括他们的姓名、性别、年龄、家庭住址、进宫时间。

假设所有的、记录各类信息的卷轴都只能放一个地方，那么，给放宫人信息的卷轴划分一块空间，给 放采购信息的卷轴划分一个空间。

其实，数据库就是在我们电脑的储存空间（一般是硬盘）里开辟出一个 ‘库房’。

卷轴一次性能记录的信息有限，但是，电脑可以把所有信息记录在一块，我们把它叫做 ‘数据表’。

我们可以在数据库中存入很多张表，表里存着我们所需要的信息。

Mysql 是什么？

MySQL数据库简介 (biancheng.net) 解释如下：

其实简单地说，数据库就是一组经过计算机整理后的数据，存储在一个或多个文件中，而管理这个数据库的软件就称为数据库管理系统。一般一个数据库系统（Database System） 可以分为数据库（Database）与数据管理系统（Database Management System，DBMS）两个部分。主流的数据库软件有 Oracle、Informix、Sybase、SQL Server、PostgreSQL、MySQL、Access、FoxPro 和 Teradata 等等。

可以这么理解。管仓库的人见过吧？往仓库存、或者从仓库取，都得通过管仓库的人，不是随随便便每个人都能任意进出。我们要去拿某样东西，一般来说，我们会告诉管理员：”你帮我拿一下书。“然后仓库管理员进去拿出来。

数据库管理系统就充当了电脑中数据库的管理员，不同的是，它的权限更大，它可以决定数据库的位置，即数据的存储位置。

有了数据库管理系统，我们想要拿到数据，或者存数据，我们都需要给这个管理员指令。

这些指令，就是 SQL [Structured Query Language] 命令。

一般来说，SQL 命令在各个数据库管理软件上是通用的，但是由于不同的数据库有功能上的差异（数据存储量、数据读写速度） 等，不同的数据库管理软件在某些命令上依然存在不同，这需要深入学习。

暂时，我们学习通用的 SQL 命令。

SQL 语句关键字

我们可以通过这个网站进行练习：sqlzoo.net/wiki/SQL_Tu…

我是使用我推荐的网站做测试的，我截图的数据有限，实际上有很多数据，所以结果跟现有表的数据无法完全对上。

我们先学习使用单独的语法，具体的 MySql 使用会在后续的实践文章中说明。

假设我们已经有了一张数据表，名字叫做 world。通常来说，我们可以把数据表看作如下结构,表中部分数据如下：

Snipaste_2022-05-31_17-15-25.png

数据库里已经有了这张表，我们想要拿到我们需要的数据需要用什么指令呢？

Select

首先，我们发起一个简单的请求,拿到这张表里面所有人的名字。

查询

检索单列

SELECT name FROM world

这句命令中，大写的是固定指定，小写是我们自定义的内容。这句话的意思是，从 world 表中 选出 所有的 name。 select …… from…… 从…中挑选

我们会得到如下结果：

name
Afghanistan
Albania
Algeria
Andorra
Angola

检索多列

SELECT name,continent FROM world

namecontinent
AfghanistanAsia
AlbaniaEurope
AlgeriaAfrica
AndorraEurope
AngolaAfrica

检索全部列

SELECT * FROM world

*是通配符，就是说，它可以匹配所有内容。

namecontinentareapopulationgdpcapitaltldflag
AfghanistanAsia6522302550010020364000000Kabul.af//upload.wikimedia.org/wikipedia/commons/9/9a/Flag_of_Afghanistan.svg
AlbaniaEurope28748282197712044000000Tirana.al//upload.wikimedia.org/wikipedia/commons/3/36/Flag_of_Albania.svg
AlgeriaAfrica238174138700000207021000000Algiers.dz//upload.wikimedia.org/wikipedia/commons/7/77/Flag_of_Algeria.svg
AndorraEurope468760983222000000Andorra la Vella.ad//upload.wikimedia.org/wikipedia/commons/1/19/Flag_of_Andorra.svg
AngolaAfrica124670019183590116308000000Luanda.ao//upload.wikimedia.org/wikipedia/commons/9/9d/Flag_of_Angola.svg

限制

结果唯一

查询结果相同的内容只出现一次。

SELECT DISTINCT continent FROM world

continent
Africa
Asia
Europe

只查询前N条

SELECT TOP 3 name FROM world

name
Afghanistan
Albania
Algeria

排序

单列排序

SELECT area FROM world ORDER BY area

area
0
2
21
26
61

多列排序

SELECT name,area,continent FROM world ORDER BY continent,area

这句话的意思是先按 continent 字段排序。如果 continent 字段一样，再按 area 内容排序。

可以看到，area 内容此时是从小到大排序的。

nameareacontinent
Congo, Democratic Republic ofAfrica
Seychelles452Africa
Sao Tomé and Príncipe964Africa

如果我们改成这样：

SELECT name,area,continent FROM world ORDER BY continent,name

结果会变成这样：

nameareacontinent
Algeria2381741Africa
Angola1246700Africa
Benin112622Africa

可以看到，此时 ，continent 相同的情况下，是按照 name 排序的。

指定排序的升序和倒序

可以看到，默认 name、continent 的排序是从 A 开始的，是从小到大排列，如果我们想让它从大到小排列呢？

SELECT name,area,continent FROM world ORDER BY continent DESC,name ASC

nameareacontinent
Argentina2780400South America
Bolivia1098581South America
Brazil8515767South America
Chile756102South America

可以看到我们在 列名 continent 后面添加了 DESC ,它的首字母从 S 开始排 ，我们也可以添加 ASC ，可以看到 name 的字母依然是从 A 开始排。

Where

AND 单个条件筛选

where 用于指定搜索条件进行过滤。

SELECT * FROM world WHERE name='Brazil'

指定输出名字为 Brazil 的那一行，得出结果：

namecontinentareapopulationgdpcapitaltldflag
BrazilSouth America85157672027940002254109000000Brasília.br//upload.wikimedia.org/wikipedia/commons/0/05/Flag_of_Brazil.svg

除了等于号，where 还支持其他运算符号的判断。

Snipaste_2022-06-01_14-02-54.png 我们可以筛选出人口小于 80000 的列。

SELECT * FROM world WHERE population < 80000

namecontinentareapopulationgdpcapitaltldflag
AndorraEurope468760983222000000Andorra la Vella.ad//upload.wikimedia.org/wikipedia/commons/1/19/Flag_of_Andorra.svg
DominicaCaribbean75171293499000000Roseau.dm//upload.wikimedia.org/wikipedia/commons/c/c4/Flag_of_Dominica.svg
LiechtensteinEurope160371325827000000Vaduz.li//upload.wikimedia.org/wikipedia/commons/4/47/Flag_of_Liechtenstein.svg
Marshall IslandsOceania18156086198000000Majuro.mh//upload.wikimedia.org/wikipedia/commons/2/2e/Flag_of_the_Marshall_Islands.svg

可以看到成功地筛选出了我们想要的结果。

条件组合筛选

使用 AND 关键字，组合筛选条件

SELECT * FROM world WHERE population < 80000 AND area < 468

namecontinentareapopulationgdpcapitaltldflag
LiechtensteinEurope160371325827000000Vaduz.li//upload.wikimedia.org/wikipedia/commons/4/47/Flag_of_Liechtenstein.svg
Marshall IslandsOceania18156086198000000Majuro.mh//upload.wikimedia.org/wikipedia/commons/2/2e/Flag_of_the_Marshall_Islands.svg
MonacoEurope2369505707000000Monaco-Ville.mc//upload.wikimedia.org/wikipedia/commons/e/ea/Flag_of_Monaco.svg
NauruOceania219945121000000Yaren District.nr//upload.wikimedia.org/wikipedia/commons/3/30/Flag_of_Nauru.svg

OR

SELECT * FROM world WHERE population < 30000 OR name = 'Monaco'

满足条件之一即可。

namecontinentareapopulationgdpcapitaltldflag
MonacoEurope2369505707000000Monaco-Ville.mc//upload.wikimedia.org/wikipedia/commons/e/ea/Flag_of_Monaco.svg
NauruOceania219945121000000Yaren District.nr//upload.wikimedia.org/wikipedia/commons/3/30/Flag_of_Nauru.svg
PalauOceania45920901213000000Ngerulmud.pw//upload.wikimedia.org/wikipedia/commons/4/48/Flag_of_Palau.svg
TuvaluOceania261132340000000Funafuti.tv//upload.wikimedia.org/wikipedia/commons/3/38/Flag_of_Tuvalu.svg
Vatican CityEurope0839.va//upload.wikimedia.org/wikipedia/commons/0/00/Flag_of_the_Vatican_City.svg

AND 和 OR 的顺序

当两个关键字同同时出现时，先执行 AND ，然后执行 OR。

我们先执行这个语句：

SELECT * FROM world WHERE population < 30000 OR continent = 'Asia'

namecontinentareapopulationgdpcapitaltldflag
AfghanistanAsia6522302550010020364000000Kabul.af//upload.wikimedia.org/wikipedia/commons/9/9a/Flag_of_Afghanistan.svg
AzerbaijanAsia86600947710068727000000Baku.az//upload.wikimedia.org/wikipedia/commons/d/dd/Flag_of_Azerbaijan.svg
BahrainAsia765123457130362000000Manama.bh//upload.wikimedia.org/wikipedia/commons/f/f9/Flag_of_Bahrain_1972.svg
BangladeshAsia147570156557000127195000000Dhaka.bd//upload.wikimedia.org/wikipedia/commons/f/f9/Flag_of_Bangladesh.svg
BhutanAsia383947490901861000000Thimphu.bt//upload.wikimedia.org/wikipedia/commons/9/91/Flag_of_Bhutan.svg
BruneiAsia576539316216954000000Bandar Seri Begawan.bn//upload.wikimedia.org/wikipedia/commons/9/9c/Flag_of_Brunei.svg

然后写下如下语句：

SELECT * FROM world WHERE population < 30000 OR continent = 'Asia' AND area < 400

如果它是先执行 OR,那么，它的执行顺序是这样：（population < 30000 OR continent = 'Asia'），这部分的执行结果再加一个条件： area < 400。但实际的运行结果呢？

namecontinentareapopulationgdpcapitaltldflag
MaldivesAsia3003172802606000000Malé.mv//upload.wikimedia.org/wikipedia/commons/0/0f/Flag_of_Maldives.svg
NauruOceania219945121000000Yaren District.nr//upload.wikimedia.org/wikipedia/commons/3/30/Flag_of_Nauru.svg
PalauOceania45920901213000000Ngerulmud.pw//upload.wikimedia.org/wikipedia/commons/4/48/Flag_of_Palau.svg
TuvaluOceania261132340000000Funafuti.tv//upload.wikimedia.org/wikipedia/commons/3/38/Flag_of_Tuvalu.svg
Vatican CityEurope0839.va//upload.wikimedia.org/wikipedia/commons/0/00/Flag_of_the_Vatican_City.svg

可以看到 area 大于 400 了，但是他的 population < 300 .所以它的执行结果是 （population < 30000 OR （continent = 'Asia' AND area < 400`））

IN

SELECT * FROM world WHERE area IN (300,21)

IN , 就是字段值，只要存在于 IN 后面的内容中，就把它查询出来。

namecontinentareapopulationgdpcapitaltldflag
MaldivesAsia3003172802606000000Malé.mv//upload.wikimedia.org/wikipedia/commons/0/0f/Flag_of_Maldives.svg
NauruOceania219945121000000Yaren District.nr//upload.wikimedia.org/wikipedia/commons/3/30/Flag_of_Nauru.svg

<>

SELECT * FROM world WHERE area <> 300

条件是：area 不等于 300

namecontinentareapopulationgdpcapitaltldflag
AfghanistanAsia6522302550010020364000000Kabul.af//upload.wikimedia.org/wikipedia/commons/9/9a/Flag_of_Afghanistan.svg
AlbaniaEurope28748282197712044000000Tirana.al//upload.wikimedia.org/wikipedia/commons/3/36/Flag_of_Albania.svg
AlgeriaAfrica238174138700000207021000000Algiers.dz//upload.wikimedia.org/wikipedia/commons/7/77/Flag_of_Algeria.svg
AndorraEurope468760983222000000Andorra la Vella.ad//upload.wikimedia.org/wikipedia/commons/1/19/Flag_of_Andorra.svg

LIKE

% 通配

SELECT * FROM world WHERE area LIKE '%68'

查找出 area 中 以 68 结尾的内容。

通配的意思是，%可以代表任意内容，%68 意思就是 ‘任意内容+68’。

只要符合这个条件，就会被查询出来。

所以我们可以查询 ’%68‘，’68%‘，’%68%‘

namecontinentareapopulationgdpcapitaltldflag
AndorraEurope468760983222000000Andorra la Vella.ad//upload.wikimedia.org/wikipedia/commons/1/19/Flag_of_Andorra.svg
GabonAfrica267668171100024076000000Libreville.ga//upload.wikimedia.org/wikipedia/commons/0/04/Flag_of_Gabon.svg
NigeriaAfrica923768178517000286470000000Abuja.ng//upload.wikimedia.org/wikipedia/commons/7/79/Flag_of_Nigeria.svg
SudanAfrica18860683728940651453000000Khartoum.sd//upload.wikimedia.org/wikipedia/commons/0/01/Flag_of_Sudan.svg

_ 单个匹配

不同于% 可以匹配任意（一个，多个，零个） 字符。_ 限定 只能匹配一个。

比如以下查询内容就要求符合的条件是： 一个字符 + 68.

SELECT * FROM world WHERE area LIKE '_68%'

namecontinentareapopulationgdpcapitaltldflag
AndorraEurope468760983222000000Andorra la Vella.ad//upload.wikimedia.org/wikipedia/commons/1/19/Flag_of_Andorra.svg

正则(词条数据在网页中验证失败)

SELECT name FROM world WHERE name regexp 'a'

创建计算字段

拼接字段(CONCAT)

SELECT concat(name,'-',area) FROM world

Afghanistan-652230
Albania-28748
Algeria-2381741
Andorra-468
Angola-1246700
Antigua and Barbuda-442

concat() 拼接串，即把多个串连接起来形成一个较长的串。concat() 需要一个或多个指定的串，各个串之间用逗号分隔。这么做只能返回值，没有表头。如果想返回一个有表头的数据该怎么办呢？

SELECT concat(name,'-',area) as new_colums FROM world

new_colums
Afghanistan-652230
Albania-28748
Algeria-2381741
Andorra-468

执行算数计算(*)

SELECT population*area as new_colums FROM world

new_colums
16631930223000
81126194796
92173376700000
35613864
23916181653000

现在可以使用这个计算字段，就跟使用其他的计算字段一样。

使用数据处理函数

文本处理函数 (UPPER )

upper: 能够将字母转化为大写

SELECT Upper(name) as name_upper FROM world

name_upper
AFGHANISTAN
ALBANIA
ALGERIA
ANDORRA
ANGOLA

文本常用处理函数：

函数说明
LEFT返回字符串左边的字符
LENGHT()(也使用DATALENGTH() 或者 LEN())返回字符串的长度
LOWER()（Access 使用 LCASE())将字符串转换为小写
LTRIM()去掉字符串左边的空格
RIGHT()返回字符串右边的自负
RTRIM()去掉字符串右边的空格
SOUNDEX()返回字符串的 soundex 值
UPPER()将字符串转换为大写

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数组模式的算法。

日期和时间处理函数

SELECT order_num FROM Orders WHERE DATEPART('yyyy', order_date) = 2012

DATAPART() 函数，顾名思义， 此函数返回日期的某一部分。DATEPART() 函数有两个参数，它们分别是返回的成分和从中返回成分的日期。

数值处理函数(SQRT,ABS)

SELECT sqrt(area) as newArea FROM world WHERE continent='Asia'

newArea
807.607577973362
294.27877939124323
27.65863337187866
384.1484088213825
195.94386951369518

常用的数值处理函数如下：

函数说明
ABS()返回一个数的绝对值
COS()返回一个角度的余弦
EXP()返回一个数的指数值
PI()返回圆周率
SIN()返回一个角度的正弦
SQRT()返回一个数的平方根
TAN()返回一个角度的正切

汇总数据

聚集函数(AVG,COUNT,MAX,MIN,SUM)

SELECT MAX(area) as maxArea FROM world WHERE continent='Asia'

maxArea
9596961

其他函数如下表：

函数说明
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值之和

分组数据

数据分组（COUNT）

SELECT COUNT(area) as countArea FROM world WHERE continent='Asia'

countArea
47

创建分组（GROUP BY)

SELECT continent,COUNT(*) as count FROM world GROUP BY continent

continentcount
Africa53
Asia47
Caribbean11
Eurasia2

过滤分组(HAVING)

SELECT continent,COUNT(*) as count FROM world GROUP BY continent HAVING COUNT(*) >= 44

continentcount
Africa53
Asia47
Europe44

注意，WHERE 过滤行，而 HAVING 过滤分组。

分组和排序(ORDER BY)

SELECT continent,COUNT(*) as count FROM world GROUP BY continent HAVING COUNT(*) >= 8 ORDER BY count,continent DESC

先分组，然后按照 count 排序。当 count 排序完成后，如果 count 有相同的数值，按照 continent 降序排序。升序排序是指定为 ASC.

continentcount
North America11
Caribbean11
South America13
Oceania14
Europe44
Asia47
Africa53

注意

实践网址如下：The JOIN operation - SQLZOO](sqlzoo.net/wiki/The_JO…)

由于接下来内容需要用到两张以上的表。

使用的表如下：

game

idmdatestadiumteam1team2
10018 June 2012National Stadium, WarsawPOLGRE
10028 June 2012Stadion Miejski (Wroclaw)RUSCZE
100312 June 2012Stadion Miejski (Wroclaw)GRECZE
100412 June 2012National Stadium, WarsawPOLRUS

goal

matchidteamidplayergtime
1001POLRobert Lewandowski17
1001GREDimitris Salpingidis51
1002RUSAlan Dzagoev15
1002RUSRoman Pavlyuchenko82

子查询

子查询，即嵌套在其他查询中的查询。

核心要点：一个查询的返回结果可以被另一个查询直接使用

SELECT * FROM game WHERE id IN (SELECT matchid from goal where matchid < 1003)

idmdatestadiumteam1team2
10012012-06-08T00:00:00National Stadium, WarsawPOLGRE
10022012-06-08T00:00:00Stadion Miejski (Wroclaw)RUSCZE

联结

创建联结(JOIN……ON)

SELECT * FROM game JOIN goal ON (id=matchid)

核心要点，使用特定条件将两张表组合。

即，goal.matchid = game.id 时，将对应的内容组合成新的一行。

idmdatestadiumteam1team2matchidteamidplayergtime
10012012-06-08T00:00:00National Stadium, WarsawPOLGRE1001GREDimitris Salpingidis51
10012012-06-08T00:00:00National Stadium, WarsawPOLGRE1001POLRobert Lewandowski17
10022012-06-08T00:00:00Stadion Miejski (Wroclaw)RUSCZE1002CZEVáclav Pilar52
10022012-06-08T00:00:00Stadion Miejski (Wroclaw)RUSCZE1002RUSAlan Dzagoev15

联结多个表

select game.id as gameId,teamid,teamname from game,goal,eteam where game.id = goal.matchid and goal.teamid = eteam.id

gameIdteamidteamname
1001GREGreece
1001POLPoland
1002CZECzech Republic
1002RUSRussia
1002RUSRussia

具体联结可以参看一下博客：(59条消息) Mysql中外连接,内连接,左连接,右连接的区别_萌萌哒的瓤瓤的博客-CSDN博客_左连接,右连接,内连接,外连接的区别

组合查询（UNION)

SELECT player, teamid, gtime FROM goal WHERE gtime<=10 UNION SELECT player, teamid, gtime FROM goal WHERE teamid = 'RUS' ORDER BY gtime

playerteamidgtime
Mario MandžukicCRO3
Petr JirácekCZE3
Fernando TorresESP4
Václav PilarCZE6
Alan DzagoevRUS15
Roman ShirokovRUS24
Alan DzagoevRUS37

插入数据(INSERT)

使用表如下：

games

yrcity
2000Sydney
2004Athens
2008Beijing

INSERT INTO GAMES(yr,city) VALUES (2012,'London')

在表明后的括号里明确给出列名，即使数据表顺序改变，依然能够准确插入。

更新和删除数据

更新数据(UPDATE)

需求：客户 10005 需要更新他的电子邮件地址。

UPDATE Cutomers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '10005'

更新多个列语法如下：

UPDATE Customers SET cust_contact = 'Sam Robers', cust_email = 'sam@toyland.com' WHERE cust_id = '10006'

删除数据（DELETE)

DELETE FROM Customers WHERE cust_id = '100006'

创建和操纵表

创建表（CREATE TABLE)

CREATE TABLE Products
{
	prod_id CHAR(10) NOT NULL,
	vend_id CHAR(10) NOT NULL,
	prod_name CHAR(254) NOT NULL,
	prod_price DECIMAL(8,2) NOT NULL,
	prod_desc VARCHAR(1000) NOT NULL
}

null 值就是没有值或缺值。允许 null 值的列也允许在插入行时不给出该列的值。

不允许 null 值的列不接受没有列值的行，换句话说， 在插入或更新行时，改列必须有值。

如果插入没有值的列，将返回错误，且插入失败。

混合 null 和 not null 示例：

CREATE TABLE Vendors
{
 vend_id CHAR(10) NOT NULL,
 vend_name CHAR(50) NOT NULL,
 vend_address CHAR(50),
 vend_city CHAR(50)
}

指定默认值：

CREATE TABLE OrderItems
{
 order_num INTEGER NOT NULL,
 order_item INTEGER NOT NULL,
 prod_id CHAR(10) NOT NULL,
 quantity INTEGER NOT NULL DEFAULT 1,
 item_price DECIMAL(8,2) NOT NULL
}

更新表（ALTER TABLE）

新增列

ALTER TABLE Vendors ADD vend_phone CHAR(20)

删除列

ALTER TABLE Vendors DROP COLUMN vend_phone

删除表(`DROP TABLE)

DROP TABLE CustCopy

使用视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

假设有 select 语句如下：

SELECT cust_name,cust_contact FROM Customers,Orders,OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01'

现在，加入可以把整个查询包装成一个名为 ProductCustomers 的虚拟表，则可以如下轻松地检索出相同的数据：

SELECT cust_name,cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01'

这就是视图的作用。ProductCustomers 是一个视图，它不包含任何列或数据，包含的是一个查询。

为什么使用视图

  • 重用 SQL 语句
  • 简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其他基本查询细节。
  • 使用表的一部分而不是整个表。
  • 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

注意，每次使用视图时，都必须处理查询执行时需要的所有检索。如果使用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。

创建视图（CREATE VIEW)

CREATE VIEW ProductCustomer AS SELECT cust_name,cust_contact,prod_id FROM Customers,Orders,OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num

删除视图（DROP VIEW)

DROP VIEW viewname

覆盖（或更新）视图，必须先删除它，然后再重新创建。

使用存储过程

简单来说，存储过程就是为以后使用而保存的一条或多条 SQL 语句。

为什么要使用存储过程

优点

  • 通过把处理封装在一个医用单元中，可以简化复杂的操作。

  • 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。

  • 简化对变动的管理。如果表名、列名或者业务逻辑有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

缺点

  • 不懂 DBMS 中的存储过程语法有所不同。
  • 一般来说，编写存储过程比编写基本 SQL 语句复杂，需要更高的技能，更丰富的经验。

执行存储过程

CALL productpricing(@pricelow,@pricehigh,@priceaverage)

创建存储过程

CREATE PROCEDURE productpricing()
BEGIN 
	SELECT Avg(prod_price) AS priceaverage FROM products;
END;

如何使用这个存储过程呢？

CALL productpricing()

priceaverage
16.133571

删除存储过程

DROP PROCEDURE productpricing

注意：如果指定的过程不存在，则 DROP PROCEDURE 将产生一个错误。当过程存在想删除它时，可使用：

DROP PROCEDURE IF EXISTS

使用参数

out

CREATE PROCEDURE productpricing(OUT p1 DECIMAL(8,2),OUT ph DECIMAL(8,2),OUT pa DECIMAL(8,2))
BEGIN 
	SELECT Min(prod_price) INTO p1 FROM products;
	SELECT Max(prod_price) INTO ph FROM products;
	SELECT Avg(prod_price) INTO pa FROM products;
END;

MySQL 支持 IN（传递给存储过程） / OUT（从存储过程传出） / INOUT（对存储过程传入和传出） 类型的参数。

调用如下：

CALL productpricing(@pricelow,@pricehigh,@priceaverage)

SELECT @pricehigh,@pricelow,@priceaverage;

@pricehigh@pricelow@priceaverage
55.02.5016.133571428

IN 和 OUT

CREATE PROCEDURE ordertotal(IN onumber INT,OUT ototal DECIMAL(8,2))
BEGIN
	SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber
	INTO ototal;
END;

调用如下：

CALL ordertotal(20005,@total)

SELECT @total

@total
149.87

使用游标（CURSOR)

游标（cursor) 是一个存储在 MYSQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

创建游标

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

使用游标

  • 在使用游标前，必须声明（定义） 它。
  • 一旦声明后，必须打开游标以供食用。
  • 对于填有数据的游标，根据需要取出（检索）各行。
  • 在结束游标使用时，必须关闭游标。
  • 一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用 OPEN 语句打开它就可以了。

打开和关闭游标

打开游标

OPEN ordernumbers;

在处理 OPEN 语句时执行查询，存储检索出的数据以供浏览和滚动。

关闭游标

CLOSE ordernumbers

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	OPEN ordernumbers;
	CLOSE ordernumbers;
END;

这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没做。

使用游标数据

在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索什么数据（所需的列），检索出来的数据存储在什么地方，它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（不重复读取同一行）。

CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE O INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	OPEN ordernumbers;
	
	REPEAT 
		FETCH ordernumbers INTO O;
    UNTIL done END REPEAT;
    
    CLOASE ordernumbers;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

这里定义了一个条件出现被执行的代码。当 SQLSTATE '02000' 出现时， SET done = 1.

SQLSTATE '02000' 是一个未找到条件，当 REPEAT 由于没有更多的行供循环而不能继续时，出现这个条件。

我们可以在循环内放入任意我们需要的处理。

比如创建一个表，在循环中插入数据：

CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE O INT;
	DECLARE t DECIMAL(8,2)
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	CREATE TABLE IF NOT EXISTS ordertotals
	
	OPEN ordernumbers;
	
	REPEAT 
		FETCH ordernumbers INTO O;
		
		CALL ordertotal(o,1,t);
		-- 此处调用了上文写好的存储过程，对 t 进行赋值
		
		INSERT INTO ordertotals(order_num,total) values(o,t)
		
    UNTIL done END REPEAT;
    
    CLOASE ordernumbers;

触发器

如果我们有以下需求：

  • 每当增加一个客户到某个数据库表时，都检查其电话好嘛格式是否正确，州的缩写是否为大写。
  • 每当订购一个产品时，都从库存数量中减去订购的数量。

所有这些例子的共同之处是他们都需要在某个表发生更改时自动处理，这就是触发器。

触发器是 MySQL 响应以下任意语句而自动执行的一条 MySQL 语句（或位于 BEGIN 和 END 语句之间的一组语句）：

DELETE INSERT UPDATE

创建触发器

创建触发器需要给出 4 条信息：

  • 唯一的触发器名
  • 触发器关联的表
  • 触发器应该响应的活动
  • 触发器何时执行
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'

这个触发器的功能是，每使用 INSERT 语句添加一行或者多行到 products 中，你将看到对每个成功的插入，显示 Product added 消息。

每个表最多支持 6 个触发器（每条 INSERT UPDATE DELETE 的之前和之后）。

单一触发器不能与多个事件或者多个表关联，所以，如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器。

如果 BEFORE 触发器失败，则 MySQL 将不执行请求的操作。此外，如果 BEFORE 触发器或语句本身失败， MySQL 将不执行 AFTER 触发器（如果有的话）

删除触发器

DROP TRIGGER newproduct

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

使用触发器

INSERT 触发器
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order num;

在插入一个新订单到orders表时，MySQL 生成一个新订单号病保存到 order_num中。触发器从 NEW.order_num 取的这个值并返回它。

DELETE 触发器
  • 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；
  • OLD 中的值全都是只读的，不能更新。

演示使用 OLD 保存将要被删除的行到一个存档表中：

CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR ECAH ROW
BEGIN
	INSERT INTO archive_orders(order_num,order_date,cust_id) 
    VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;
UPDATE 触发器
  • 在 UPDATE 触发器的代码中，你可以引用一个名为 OLD 的虚拟表访问以前（UPDATE 语句前） 的值，引用一个名为 NEW 的虚拟表访问新更新的值。
  • 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）
  • OLD 中的值全都是只读的，不能更新
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

管理事务处理

事务处理（transaction processing) 可以用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。

假设有现在这样一个给系统添加订单的过程：

1.检查数据库中是否存在相应的客户（从 customers 表查询），如果不存在，添加 他/她。

2.检索客户的 ID。

3.添加一行到 orders 表，把它与客户 ID 关联。

4.检索 orders 表中赋予的新订单 ID。

5.对于订购的每个物品在 orderitems 表中添加一行，通过检索出来的 ID 把它与 orders 表关联（以及通过产品 ID 与 products 表关联）

如果在这个过程中，在 orders 行添加之后、orderitems 行添加之前发生了故障，数据库中就会存在一个空订单。

更糟糕的是，如果系统在添加 orderitems 行之中出现故障，结果是 数据库中存在不完整的订单，而且你还不知道。

如何解决这种问题呢？这里就需要使用事务处理了。事务处理是一种机制，用来管理必须成批执行的 MySQL 操作，以保证数据库不包含不完整的操作结果。

利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行。如果没有错误发生，整租数据提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

在使用事务和事务处理时，需要知道如下术语

  • 事务（transaction) ：指一组 SQL 语句
  • 回退（rollback): 指撤销指定 SQL 语句的过程
  • 提交（commit): 指将未存储的 SQL 语句结果写入数据库表
  • 保留点（savepoint): 指事务处理中设置的临时占位符（place-holder), 你可以对它发布回退（与回退整个事务处理不同）

控制事务处理

使用如下语句来标识事务的开始：

START TRANSACTION

使用 ROLLBACK

该命令可用来回退（撤销） MySQL 语句。

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

以上过程可以看出，使用 ROLLBACK 能够撤销对于 ordertotals 表的删除。

使用 COMMIT

一般的 MySQL 语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit)， 即提交（写或保存）操作是自动进行的。

但是在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用 COMMIT 语句，如下所示：

START TRANSACTION
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

如果第一条 DELETE 起作用，但第二条失败，则 DELETE 不会提交（实际上，它是被自动撤销地）。

使用保留点

简单地 ROLLBACK 和 COMMIT 语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。

为了支持回退部分事务处理，必须能在事务处理块中合适地位置放置占位符。

创建占位符地语句如下：

SAVEPOINT delete1

回退到 delete1 地代码如下：

ROLLBACK TO delete1

释放保留点

保留点在事务处理完成（执行一条 ROLLBACK 或 COMMIT) 后自动释放。自 MySQL 5 以来，也可以用 RELEASE SAVEPOINT 明确地释放保留点。

更改默认的提交行为

任何时候，执行一条 MySQL 语句，实际上都是针对表执行的，所作更改立即生效。

如果想要 MySQL 不自动提交更改，可使用以下语句：

SET autocommit=0

autocommit 标志决定是否自动提交更改，不管有没有 commit 语句，直到 autocommit 被设置为真。

安全管理

MySQL 服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多，也不能少。

一般来说，我们需要创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用，等等。

mysql 数据库有一个名为 user 的表，它包含所有用户账号。user表有一个名为 user 的列，它存储用户登录名。

USE mysql;
SELECT user  FROM user;
user
root

创建用户账号

CREATE USER ben IDENTIFIED BY '123@'

重新命名账户：

RENAME USER ben TO bforta

删除用户账号

DROP USER bforta

设置访问权限（GRANT)

GRANT SELECT ON crashcourse.* TO bforta

此 GRANT 允许用户在 crashcourse.*(crashcourse 数据库的所有表) 上使用 SELECT. 通过只授予 SELECT 访问权限，用户 bforta 对 crashcourse 数据库中的所有数据具有只读访问权限。

展示用户权限：

SHOW GRANTS FOR bforta

Grants for bforta@%
GRANT SELECT ON 'crashcourse'.* TO 'bforta'@'%'

收回用户权限：

REVOKE SELECT ON crashcourse.* FROM bforta

数据库维护

备份数据

像所有数据一样， MySQL 的数据也必须经常备份。

  • 使用命令行使用程序 mysqldump 转出所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
  • 可用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据。
  • 使用 MySQL 的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所有数据到某个外部文件。

为了保证所有数据被写入到磁盘（包括索引数据）， 可能需要在进行备份前使用 FLUSH TABLES 语句。

进行数据库维护

  • ANALYZE TABLE , 用来检查表健是否正确。
    • ANALYZE TABLE orders;
  • CHECK TABLE , 对表进行检查
    • CHECK TABLE ordrs,orderitems

诊断启动问题

在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL 服务器自身通过在命令上执行 mysqld 启动。下面是几个重要的 mysqld 命令行选项：

  • --help 显示帮助 ，一个选项列表
  • --safe-mode 装载减去某些最佳配置的服务器
  • --verbose 显示全文本消息
  • --version 显示版本信息然后退出

查看日志文件

MySQL 维护管理员依赖的一系列日志文件。主要的日志文件有以下几种。

  • 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为 hostname.err, 位于 data 目录中。
  • 查询日志。记录所有 MySQL 活动。此日志通常名为 hostname.log, 位于 data 目录中。
  • 二进制日志。它记录更新过数据的所有语句。此日志通常名为 hostname-bin, 位于 data 目录中。
  • 缓慢查询日志。此日志记录执行缓慢的任何查询。此日志通常名为 hostname-show.log, 位于 data 目录中。
avatar
文章
阅读
粉丝