Mysql高阶SQL语句
文章目录
演示表
mysql> select * from location;
+--------+-------------+
| Region | store_Name |
+--------+-------------+
| East | Boston |
| East | New York |
| west | Los Angeles |
| west | Houston |
+--------+-------------+
4 rows in set (0.00 sec)
mysql> select * from store_info;
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Boston | 700 | 2020-12-08 |
| Los Angeles | 300 | 2020-12-08 |
+-------------+-------+------------+
4 rows in set (0.01 sec)
语句
SELECT
显示表格中一个或数个字段的所有数据记录
SELECT “字段“ FROM “表名" ;
mysql> select store_Name from store_info;
+-------------+
| store_Name |
+-------------+
| Los Angeles |
| Houston |
| Boston |
| Los Angeles |
+-------------+
4 rows in set (0.00 sec)
DISTINCT
不显示重复的数据记录
SELECT DISTINCT “字段"FROM“表名”;
mysql> select distinct store_Name from store_info;
+-------------+
| store_Name |
+-------------+
| Los Angeles |
| Houston |
| Boston |
+-------------+
3 rows in set (0.01 sec)
WHERE
有条件查询(where后常正则表达式等条件)
SELECT"字段”FROM"表名"WHERE"条件";
mysql> select store_Name from store_info where Sales > 1000;
+-------------+
| store_Name |
+-------------+
| Los Angeles |
+-------------+
1 row in set (0.00 sec)
AND/OR
与/或
SELECT"字段"FROM"表名”WHERE"条件1" AND/OR “条件2”}…;
mysql> select store_Name from store_info where Sales > 1000 or Sales > 500;
+-------------+
| store_Name |
+-------------+
| Los Angeles |
| Boston |
+-------------+
2 rows in set (0.00 sec)
mysql> select store_Name from store_info where Sales > 1000 or (Sales < 500 and Sales > 200);
+-------------+
| store_Name |
+-------------+
| Los Angeles |
| Houston |
| Los Angeles |
+-------------+
3 rows in set (0.00 sec)
IN
显示己知的值的数据记录
SELECT "字段"FROM "表名”WHERE"字段”IN(‘值1’,‘值2’,…);
mysql> select * from store_info where store_Name in ('Los Angeles','Houston');
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Los Angeles | 300 | 2020-12-08 |
+-------------+-------+------------+
3 rows in set (0.00 sec)
BETWEEN
显示两个值范围内的数据记录
SELECT"字段”FROM "表名”WHERE"字段”BETWEEN’值1’ AND’值2’;
mysql> select * from store_info where Date between '2020-12-05' and '2020-12-10';
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Boston | 700 | 2020-12-08 |
| Los Angeles | 300 | 2020-12-08 |
+-------------+-------+------------+
4 rows in set (0.00 sec)
通配符
通常通配符都是跟LIKE一起使用的
:百分号表示零个、一个或多个字符
:下划线表示单个字符
‘A_Z’:所有以’A′起头,另一个任何值的字符,且以’z’为结尾的字符串。例如,'AB2’和‘R2′都符合这一个模式,而‘ Axz·并不符合(国为在A和Z之间有两个字符,而不是一个字符)。
‘ABC%’:所有以 'ABC’起头的字符串。例如,'ABCD’和‘ABCABC’都符合这个模式。
‘%XYZ’:所有以’XYz’结尾的字符串。例如,'WXYZ’和’ZZXYZ’都符合这个模式。
'%AN% ':所有含有 'AN’这个模式的字符串。例如,'LOS ANGELES’和’SANFRANCISCO’都符合这个模式。
‘_AN%’:所有第二个字母为 ‘A’和第三个字母为’’的字符串。例如,'SAMN FRANCISCo’符合这个模式,而‘os AMGELES’则不符合这个模式。
mysql> select * from store_info where store_Name like '%ton';
+------------+-------+------------+
| store_Name | Sales | Date |
+------------+-------+------------+
| Houston | 250 | 2020-12-07 |
| Boston | 700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)
LIKE
匹配一个模式来找出我们要的数据记录
SELECT"字段”FROM "表名"WHERE“字段"LIKE{模式};
mysql> select * from store_info where store_Name like '%os%';
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Boston | 700 | 2020-12-08 |
| Los Angeles | 300 | 2020-12-08 |
+-------------+-------+------------+
3 rows in set (0.00 sec)
ORDER BY
按关键字排序
SELECT"字段"FROM “表名”[WHERE"条件”]ORDER BY"字段"[ASC,DESC] ;
ASC是按照升序进行排序的,是默认的排序方式。
DESC是按降序方式进行排序。
mysql> select store_Name,Sales,Date from store_info order by Sales desc;
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Boston | 700 | 2020-12-08 |
| Los Angeles | 300 | 2020-12-08 |
| Houston | 250 | 2020-12-07 |
+-------------+-------+------------+
4 rows in set (0.00 sec)
mysql> select store_Name,Sales,Date from store_info order by Sales asc;
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Houston | 250 | 2020-12-07 |
| Los Angeles | 300 | 2020-12-08 |
| Boston | 700 | 2020-12-08 |
| Los Angeles | 1500 | 2020-12-05 |
+-------------+-------+------------+
4 rows in set (0.00 sec)
函数
数学函数 | 说明 |
---|---|
abs(z) | 返回x的绝对值 |
rand() | 返回o到1的随机数 |
mod(x,y) | 返回x除以y以后的余数 |
power(x, y) | 返回x的y 次方 |
round(z) | 返回离x最近的整数 |
round(x,y) | 保留x的y位小数四舍五入后的值 |
sqrt(z) | 返回x的平方根 |
truncate(x,y) | 返回数字x截断为y位小数的值 |
ceil(x) | 返回大于或等于x的最小整数 |
floor(z) | 返回小于或等于x的最大整数 |
greatest(zl,x2…) | 返回集合中最大的值 |
least (zl,x2…) | 返回集合中最小的值 |
mysql> select abs(-1),rand(),mod(5,3),power(2,3),round(1.89);
+---------+--------------------+----------+------------+-------------+
| abs(-1) | rand() | mod(5,3) | power(2,3) | round(1.89) |
+---------+--------------------+----------+------------+-------------+
| 1 | 0.8611330425936106 | 2 | 8 | 2 |
+---------+--------------------+----------+------------+-------------+
1 row in set (0.00 sec)
mysql> select round(1.89,3),truncate(1.235,2),ceil(5.2),floor(2.1),least(1.89,3,6.1,2.1);
+---------------+-------------------+-----------+------------+-----------------------+
| round(1.89,3) | truncate(1.235,2) | ceil(5.2) | floor(2.1) | least(1.89,3,6.1,2.1) |
+---------------+-------------------+-----------+------------+-----------------------+
| 1.890 | 1.23 | 6 | 2 | 1.89 |
+---------------+-------------------+-----------+------------+-----------------------+
1 row in set (0.00 sec)
聚合函数 | 说明 |
---|---|
avg() | 返回指定列的平均值 |
count() | 返回指定列中非NULL值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum(z) | 返回指定列的所有值之和 |
mysql> select avg(sales) from store_info;
+------------+
| avg(sales) |
+------------+
| 687.5000 |
+------------+
1 row in set (0.00 sec)
mysql> select count(store_Name) from store_info;
+-------------------+
| count(store_Name) |
+-------------------+
| 4 |
+-------------------+
1 row in set (0.00 sec)
mysql> select count(DISTINCT store_Name) from store_info;
+----------------------------+
| count(DISTINCT store_Name) |
+----------------------------+
| 3 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select max(Sales) from store_info;
+------------+
| max(Sales) |
+------------+
| 1500 |
+------------+
1 row in set (0.04 sec)
mysql> select min(Sales) from store_info;
+------------+
| min(Sales) |
+------------+
| 250 |
+------------+
1 row in set (0.00 sec)
mysql> select sum(Sales) from store_info;
+------------+
| sum(Sales) |
+------------+
| 2750 |
+------------+
1 row in set (0.00 sec)
字符串函数 | 说明 |
---|---|
trim() | 返回去除指定格式的值 |
concat(x,y) | 将提供的参数x和y拼接成一个字符串 |
substr(x,y) | 获取从字符串:中的第y个位置开始的字符串,跟substring()函数作用相同 |
substr(x,y,z) | 获取从字符串x中的第y个位置开始长度为z的字符串 |
length(z) | 返回字符串z的长度 |
replace(x,y,z) | 将字符串z替代字符串x中的字符串y |
upper(x) | 将字符串x的所有字母变成大写字母 |
lower(x) | 将字符串x的所有字母变成小写字母 |
left(x,y) | 返回字符串x的前y个字符 |
right (x,y) | 返回字符串x的后y个字符 |
repeat(x,y) | 将字符串x重复y次 |
space(x) | 返回x个空格 |
strcmp(x, y) | 比较x和y,返回的值可以为-1,0,1 |
reverse(x) | 将字符串x反转 |
mysql> select concat(Region,store_Name) from location where store_Name = 'Boston ';
+---------------------------+
| concat(Region,store_Name) |
+---------------------------+
| EastBoston |
+---------------------------+
1 row in set (0.00 sec)
如sql_mode开启了PIPES_AS_CONCAT,"|"视为字符串的连接操作符而非或运算符,和字符串的拼接函数concat相类似,这和oracle数据库使用方法一样的
mysql> select Region ||''|| store_Name from location where store_Name = 'Boston';
+--------------------------+
| Region ||''|| store_Name |
+--------------------------+
| EastBoston |
+--------------------------+
1 row in set (0.00 sec)
mysql> select substr(store_Name,2,4) from location where store_Name = 'New York';
+------------------------+
| substr(store_Name,2,4) |
+------------------------+
| ew Y |
+------------------------+
1 row in set (0.00 sec)
SELECT TRIM ([[位置][要移除的字符串] FROM ]字符串);
[位置]:的值可以为LEADING (起头),TRAILING(结尾),BOTH(起头及结尾)。
[要移除的字符串]:从字串的起头、结尾,或起头及结尾移除的字符串。缺省时为空格。
mysql> select trim(leading 'Bo' from 'Boston');
+----------------------------------+
| trim(leading 'Bo' from 'Boston') |
+----------------------------------+
| ston |
+----------------------------------+
1 row in set (0.01 sec)
mysql> select Region,length(store_Name) from location;
+--------+--------------------+
| Region | length(store_Name) |
+--------+--------------------+
| East | 6 |
| East | 8 |
| west | 11 |
| west | 7 |
+--------+--------------------+
4 rows in set (0.00 sec)
mysql> select replace (Region,'ast','astern') from location;
+---------------------------------+
| replace (Region,'ast','astern') |
+---------------------------------+
| Eastern |
| Eastern |
| west |
| west |
+---------------------------------+
4 rows in set (0.00 sec)
GROUP BY
对GROUP BY后面的字段的查询结果进行汇.总分组,通常是结合聚合函数一起使用的GROUP BY有一个原则,凡是在 GROUP BY后面出现的字段,必须在SELECT后面出现;凡是在SELECT后面出现的、且未在聚合函数中出现的字段,必须出现在 GROUP BY后面
SELECT"字段1",SUM (“字段2”) FROM"表名”GROUP BY “字段1”;
mysql> select store_name,sum(Sales) from store_info group by store_Name order by sales desc;
+-------------+------------+
| store_name | sum(Sales) |
+-------------+------------+
| Los Angeles | 1800 |
| Boston | 700 |
| Houston | 250 |
+-------------+------------+
3 rows in set (0.00 sec)
HAVING
用来过滤由GROUP BY 语句返回的记录集,通常与GROUP BY语句联合使用HAVING 语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
SELECT “字段1”,SUM(“字段2”)FROM"表格名”GROUP BY "字段1"HAVING(函数条件);
mysql> select store_Name,sum(Sales) from store_info group by store_Name having sum(Sales) > 1500;
+-------------+------------+
| store_Name | sum(Sales) |
+-------------+------------+
| Los Angeles | 1800 |
+-------------+------------+
1 row in set (0.00 sec)
别名
字段别名,表格别名
SELECT"表格别名".“字段1”[AS]"字段别名”FROM “表格名”[AS]“表格别名”;
mysql> select A.store_Name store,sum(A.Sales) "Total Sales" from store_info A group by A.store_Name;
+-------------+-------------+
| store | Total Sales |
+-------------+-------------+
| Boston | 700 |
| Houston | 250 |
| Los Angeles | 1800 |
+-------------+-------------+
3 rows in set (0.00 sec)
子查询
连接表格在WHERE子句或HAVING子句中插入另一个sql语句
SELECT "字段1"FROM"表格1"WHERE "字段2”[比较运算符]; 外查询
(SELECT"字段1"” FROM"表格2"WHERE"条件"); 内查询
可以是符号的运算符,例如=、>、<、>=、<= ;也可以是文字的运算符,例如LIKE、IN、BETWEEN
mysql> select sum(Sales) from store_info where Store_Name IN (select store_Name from location where Region = 'west');
+------------+
| sum(Sales) |
+------------+
| 2050 |
+------------+
1 row in set (0.00 sec)
EXISTS
用来测试内查询有没有产生任何结果,类似布尔值是否为真
如果有的话,系统就会执行外查询中的sgL语句。若是没有的话,那整个sQI语句就不会产生任何结果。
SELECT "字段1"FROM"表格1"WHERE EXISTS (SELECT* FROM “表格2” WHERE “条件”);
mysql> select sum(Sales) from store_info where exists (select * from location where Region = 'west' );
+------------+
| sum(Sales) |
+------------+
| 2750 |
+------------+
1 row in set (0.00 sec)
连接查询
mysql> select * from location;
+--------+-------------+
| Region | store_Name |
+--------+-------------+
| East | Boston |
| East | New York |
| west | Los Angeles |
| west | Houston |
+--------+-------------+
4 rows in set (0.00 sec)
mysql> select * from store_info;
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Boston | 700 | 2020-12-08 |
| Los Angeles | 300 | 2020-12-08 |
+-------------+-------+------------+
4 rows in set (0.00 sec)
mysql> update store_info set store_Name='washington' where Sales=300;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from store_info;
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Boston | 700 | 2020-12-08 |
| washington | 300 | 2020-12-08 |
+-------------+-------+------------+
4 rows in set (0.00 sec)
inner join(内连接):只返回两个表中联结字段相等的行。
left join(左连接):返回包括左表中的所有记录和右表中联结字段相等的记录。
right join(右连接):返回包括右表中的所有记录和左表中联结字段相等的记录。
左右连接:
mysql> select * from location A right join store_info B on A.store_Name = B.store_Name;
+--------+-------------+-------------+-------+------------+
| Region | store_Name | store_Name | Sales | Date |
+--------+-------------+-------------+-------+------------+
| East | Boston | Boston | 700 | 2020-12-08 |
| west | Los Angeles | Los Angeles | 1500 | 2020-12-05 |
| west | Houston | Houston | 250 | 2020-12-07 |
| NULL | NULL | washington | 300 | 2020-12-08 |
+--------+-------------+-------------+-------+------------+
4 rows in set (0.00 sec)
mysql> select * from location A left join store_info B on A.store_Name = B.store_Name;
+--------+-------------+-------------+-------+------------+
| Region | store_Name | store_Name | Sales | Date |
+--------+-------------+-------------+-------+------------+
| west | Los Angeles | Los Angeles | 1500 | 2020-12-05 |
| west | Houston | Houston | 250 | 2020-12-07 |
| East | Boston | Boston | 700 | 2020-12-08 |
| East | New York | NULL | NULL | NULL |
+--------+-------------+-------------+-------+------------+
4 rows in set (0.00 sec)
内连接1:
mysql> select * from location A inner join store_info B on A.store_Name = B.store_Name;
+--------+-------------+-------------+-------+------------+
| Region | store_Name | store_Name | Sales | Date |
+--------+-------------+-------------+-------+------------+
| west | Los Angeles | Los Angeles | 1500 | 2020-12-05 |
| west | Houston | Houston | 250 | 2020-12-07 |
| East | Boston | Boston | 700 | 2020-12-08 |
+--------+-------------+-------------+-------+------------+
3 rows in set (0.00 sec)
内连接2:
mysql> select * from location A, store_info B where A.store_Name = B.store_Name;
+--------+-------------+-------------+-------+------------+
| Region | store_Name | store_Name | Sales | Date |
+--------+-------------+-------------+-------+------------+
| west | Los Angeles | Los Angeles | 1500 | 2020-12-05 |
| west | Houston | Houston | 250 | 2020-12-07 |
| East | Boston | Boston | 700 | 2020-12-08 |
+--------+-------------+-------------+-------+------------+
3 rows in set (0.00 sec)
mysql> select A.Region Region,sum(B.Sales)Sales from location A,store_info B where A.store_Name = B.store_Name group by Region;
+--------+-------+
| Region | Sales |
+--------+-------+
| East | 700 |
| west | 1750 |
+--------+-------+
2 rows in set (0.00 sec)
CREATE VIEW视图
可以被当作是虚拟表或存储查询。视图跟表格的不同是,表格中有实际储存数据记录,而视图是建立在表格之上的一个架构,它本身并不实际储存数据记录。临时表在用户退出或同数据库的连接断开后就自动消失了,而视图不会消失。
视图不含有数据,只存储它的定义,它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询,而且还要进行统计排序等操作,写sp语句会很麻烦的,用视图将几个表联结起来,然后对这个视图进行查询操作,就和对一个表查询一样,很方便。
CREATE VIEW “视图表名” AS “SELECT 语句” ;
mysql> create view V_region_Sales as select A.Region Region,sum(B.Sales) Sales from location A inner join store_info B on A.store_Name = B.store_Name group by Region;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from V_region_Sales;
+--------+-------+
| Region | Sales |
+--------+-------+
| East | 700 |
| west | 1750 |
+--------+-------+
2 rows in set (0.02 sec)
mysql> drop view V_region_Sales;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from V_region_Sales;
ERROR 1146 (42S02): Table 'test.V_region_Sales' doesn't exist
UNION联集
将两个sql语句的结果合并起来,两个sql语句所产生的字段需要是同样的数据记录种类
UNION:生成结果的数据记录值将没有重复,且按照字段的顺序进行排序
[SELECT 语句1] UNION [SELECT 语句2];
UNION ALL:将生成结果的数据记录值都列出来,无论有无重复
[SELECT 语句1]UNION ALL[SELECT 语句2];
mysql> select store_Name from location UNION select store_Name from store_info;
+-------------+
| store_Name |
+-------------+
| Boston |
| New York |
| Los Angeles |
| Houston |
| washington |
+-------------+
5 rows in set (0.00 sec)
mysql> select store_Name from location UNION ALL select store_Name from store_info;
+-------------+
| store_Name |
+-------------+
| Boston |
| New York |
| Los Angeles |
| Houston |
| Los Angeles |
| Houston |
| Boston |
| washington |
+-------------+
8 rows in set (0.00 sec)
无交集值
显示第一个sql语句的结果,且与第二个sql语句没有交集的结果,且没有重复
mysql> select DISTINCT store_Name from location where (store_Name) not in (select store_Name from store_info);
+------------+
| store_Name |
+------------+
| New York |
+------------+
1 row in set (0.00 sec)
mysql> select DISTINCT A.store_Name from location A left join store_info B using(store_Name) where B.store_Name is null;
+------------+
| store_Name |
+------------+
| New York |
+------------+
1 row in set (0.00 sec)
mysql> select A.store_Name from (select DISTINCT store_Name from location UNION all select DISTINCT store_Name from store_info) A group by A.store_Name HAVING COUNT(*)=1;
+------------+
| store_Name |
+------------+
| New York |
| washington |
+------------+
2 rows in set (0.00 sec)
CASE
是sql用来做为IF-THEN-ELSE之类逻辑的关键字
SELECT CASE ("字段名")
WHEN"条件1"THEN"结果1"
WHEN"条件2"THEN"结果2"...
[ELSE"结果N"]
END
FROM"表名";
"条件"可以是一个数值或是公式。ELSE子句则并不是必须的。
mysql> select store_Name,case store_Name
-> when 'Los Angeles' then Sales * 2
-> when 'Boston' then 2000
-> else Sales
-> end
-> "New Sales",Date
-> from store_info;
+-------------+-----------+------------+
| store_Name | New Sales | Date |
+-------------+-----------+------------+
| Los Angeles | 3000 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Boston | 2000 | 2020-12-08 |
| washington | 300 | 2020-12-08 |
+-------------+-----------+------------+
4 rows in set (0.00 sec)
空值(NULL)和无值(‘’)的区别
无值的长度为0,不占用空间的;而NULL值的长度是NULL,是占用空间的。
IS NULL或者 IS NOT NULL,是用来判断字段是不是为NULL或者不是NULL,不能查出是不是无值的。
无值的判断使用='‘或者<>’'来处理。<>代表不等于。
在通过 count()指定字段统计有多少行数时,如果遇到NULL值会自动忽略掉,遇到无值会加入到记录中进行计算。
正则表达式
匹配模式 | 说明 | 实例 |
---|---|---|
^ | 匹配文本的开始字符 | '^bd’匹配以 bd 开头的字符串 |
$ | 匹配文本的结束字符 | ‘qn$’ 匹配以 qn 结尾的字符串 |
. | 匹配任何单个字符 | 's.t’匹配任何 s 和 t 之间有一个字符的字符串 |
* | 匹配零个或多个在它前面的字符 | 'fo*t’匹配 t 前面有任意个 o |
+ | 匹配前面的字符 1 次或多次 | 'hom+'匹配以ho开头,后面至少一个m的字符串 |
字符串 | 匹配包含指定的字符串 | 'clo’匹配含有clo的字符串。 |
p1|p2 | 匹配p1或p2 | 'bg|fg’匹配bg或者fg |
[…] | 匹配字符集合中的任意一个字符 | ‘[abc]’ 匹配a或者b或者c |
[^…] | 匹配不在括号中的任何字符 | ‘^ ab’ 匹配不包含a或者b的字符串 |
{n} | 匹配前面的字符串n次 | 'g{2}'匹配含有2个g的字符串 |
{n,m} | 匹配前面的字符串至少n次,至多m次 | ‘f{1,3}’ 匹配 f 最少1次,最多3次 |
SELECT "字段” FROM "表名” WHERE “字段” REGEXP {模式};
mysql> select * from store_info where store_Name regexp 'os';
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Boston | 700 | 2020-12-08 |
+-------------+-------+------------+
2 rows in set (0.00 sec)
mysql> select * from store_info where store_Name regexp '^[A-G]';
+------------+-------+------------+
| store_Name | Sales | Date |
+------------+-------+------------+
| Boston | 700 | 2020-12-08 |
+------------+-------+------------+
1 row in set (0.00 sec)
mysql> select * from store_info where store_Name regexp 'Ho|Bo';
+------------+-------+------------+
| store_Name | Sales | Date |
+------------+-------+------------+
| Houston | 250 | 2020-12-07 |
| Boston | 700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)
存储过程
存储过程是一组为了完成特定功能的sql语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用sql语句写好并用一个指定的名称存储起来,这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时,只需要调用它即可。存储过程在执行上比传统sql速度更快、执行效率更高。
存储过程的优点:
1、执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率2、sQL语句加上控制语句的集合,灵活性高
3、在服务器端存储,客户端调用时,降低网络负载
4、可多次重复被调用,可随时修改,不影响客户端调用
5、可完成所有的数据库操作,也可控制数据库的信息访问权限
mysql> delimiter $$ #将语句的结束符号从分号; 临时改为两个$$(可以是自定义)
mysql> create procedure proc() #创建存储过程,过程名为Proc,不带参数
-> begin #过程体以关键字BEGIN开始
-> select * from store_info; #过程体语句
-> end $$ #过程体以关键字END 结束
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; #将语句的结束符号恢复为分号
mysql> call proc; #调用存储过程
+-------------+-------+------------+
| store_Name | Sales | Date |
+-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 |
| Houston | 250 | 2020-12-07 |
| Boston | 700 | 2020-12-08 |
| washington | 300 | 2020-12-08 |
+-------------+-------+------------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show create procedure proc; #查看某个存储过程的具体信息
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| proc | PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER="root"@"localhost" PROCEDURE "proc"()
begin
select * from store_info;
end | utf8 | utf8_general_ci | utf8_general_ci |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
mysql> show procedure status LIKE '%proc%' \G;
*************************** 1. row ***************************
Db: test
Name: proc
Type: PROCEDURE
Definer: root@localhost
Modified: 2022-06-04 23:42:44
Created: 2022-06-04 23:42:44
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
删除存储过程
存储过程内容的修改方法是通过删除原有存储过程,之后再以相同的名称创建新的存储过程。
mysql> drop procedure if exists proc; #仅当存在时删除,不添加IF EXISTs时,如果指定的过程不存在,则产生一个错误
Query OK, 0 rows affected (0.00 sec)
mysql> show create procedure proc;
ERROR 1305 (42000): PROCEDURE proc does not exist
存储过程的控制语句
create table t (id int(10));
insert into t values(10);
条件语句if-then-else…end if
mysql> delimiter $$
mysql> create procedure proc2 (in pro int)
-> begin
-> declare var int;
-> set var=pro*2;
-> if var>=10 then
-> update t set id=id+1;
-> else
-> update t set id=id-1;
-> end if;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> call proc2(6);
Query OK, 1 row affected (0.00 sec)