MySQL语法学习笔记

最新推荐文章于 2022-07-26 14:56:53 发布
最新推荐文章于 2022-07-26 14:56:53 发布
阅读量9.4k 收藏 316
点赞数 58
分类专栏： 笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Lalalalazy/article/details/112683595
版权

MySQL语法学习笔记

学习之道，非尽心竭力者不能进也！我是小七黛，欢迎查看我的笔记，有问题欢迎交流探讨。

SQL是一种结构查询语言，用于查询关系数据库的标准语言，包括若干关键字和一致的语法，便于数据库元件（表、索引、字段等）的建立和操纵。全文是学习《MySQL必知必会》做的笔记，如有必要可自行阅读。

目录

1.MySQL建库

1.1建库语句

create database emp   #建库 名为 emp
default character set utf-8   #设置该库的默认编码格式为 utf-8
collate utf8_general_ci;     #设置数据库校对规则。不区分大小写

示例中 utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感。

1.2删除库

drop database emp;

2.建表

2.1建表模板

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

2.2 主键（PRIMARY KEY）

主键值必须唯一：表中的每个行必须具有唯一的主键值。
如果主键使用单个列，则它的值必须唯一。
迄今为止我们看到的CREATE TABLE例子都是用单个列作为主键。

PRIMARY KEY (cust_id)

如果使用多个列，则这些列的组合值必须唯一 。
创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。

PRIMARY KEY (order_num,order_item)

主键中只能使用不允许NULL值的列，允许NULL值的列不能作为唯一标识。

2.3AUTO_INCREMENT

AUTO_INCREMENT定义列为自增的属性，一般用于主键，每次执行一个INSERT操作时，数值会自动加1
使用的最简单的编号是下一个编号，所谓下一个编号是大于当前最大编号的编号。例如，如果cust_id的最大编号为10005，则插入表中的下一个顾客，可以具有等于10006的 cust_id 。

2.4 默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值 用CREATE TABLE语句的列定义中的DEFAULT关键字指定

2.5 存储引擎（ENGINE=InnoDB）

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。
在你使用CREATE TABLE语句时，该引擎具体创建表。
而在你使用SELECT语句 或进行其他数据库处理时，该引擎在内部处理你的请求。
多数时候，此引擎 都隐藏在DBMS内，不需要过多关注它。

以下是几个需要知道的引擎：

  • InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；

  • MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；

  • MyISAM是一个性能极高的引擎，它支持全文本搜索但不支持事务处理 。

2.6 ALTER TABLE 语句

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

-- 表中添加列
ALTER TABLE customers
ADD cust_phone char(10)

-- 删除表中的列（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）
ALTER TABLE customers
DROP COLUMN cust_phone

-- 改变表中列的数据类型
-- 修改数量字段的默认值为80
ALTER TABLE customers
MODIFY COLUMN quantity int(11) null default 80 after prod_id

2.7 删除表

DROP TABLE customers

3.SQL处理数据的基本方法

3.1 数据检索（select 语句）

Select 语句语法
Select 检索所有列
Select 检索单个列
Select 检索不同行
Select 的结果限定

# 描述表
DESC products;
# select 语句 检索所有列
SELECT * FROM products;
# select 语句 检索指定的多个列
SELECT prod_id,prod_name,prod_price FROM products;
# select 语句 检索指定的一个列
SELECT vend_id FROM products;
# 去重，DISTINCT 关键词用于返回唯一不同的值
SELECT distinct vend_id FROM products;
# 第1行开始，取前3行
SELECT * FROM products limit 3;
# 第4行开始，取3行
SELECT * FROM products limit 3,3;

3.2 数据排序

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，可以使用 DESC 关键字。

# 先查看一下这次用的products表
SELECT * from products;
# 单个字段的排序 升序 降序
SELECT prod_id,prod_price from products
ORDER BY prod_price; 
# 默认（ASC）是升序，desc 是降序,默认可以不写
SELECT prod_id,prod_price from products
ORDER BY prod_price desc; 
# 默认排序a-z ,反序就是z-a 
SELECT prod_id,prod_price from products
ORDER BY prod_name desc; 
# 多字段排序
SELECT prod_name,prod_price from products
ORDER BY prod_price,prod_name;
# 找出最便宜的产品
SELECT prod_name,prod_price from products
ORDER BY prod_price LIMIT 1; 
# 找出最贵的产品
SELECT * from products
ORDER BY prod_price DESC LIMIT 1;

3.3 数据过滤

数据库表一般包含大量的数据，很少需要检索表中所有行。
通常只会根据特定操作或报告的需要提取表数据的子集。
只检索所需数据需要指定搜索条件（search criteria）
搜索条件也称为过滤条件（filter condition）。
在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。
WHERE子句在表名 （FROM子句）之后给出。

3.3.1 使用Where子句

检查单个值
不匹配检查

# 筛选价格是2.5的产品
select * from products where prod_price = 2.5;
# 筛选价格是2.5的产品，并只显示特定的字段
select prod_name,prod_price from products 
where prod_price = 2.5;

# WHERE 条件操作符：=、>、<、>=、<=、<>/！=、between * and *（包含首尾）
select prod_name,prod_price from products 
where prod_price >= 2.5 and prod_price <10
order by prod_price;

select prod_name,prod_price from products 
where prod_price BETWEEN 2.5 and 10 
order by prod_price;

练习：
找出价格低于10元的产品
找出价格不是供应商1003制造的产品
找出供应商1001,1003 制造的产品

select prod_name,prod_price from products 
where prod_price < 10
order by prod_price;

select vend_id,prod_name,prod_price from products 
where vend_id != 1003
order by prod_price;

select vend_id,prod_name,prod_price from products 
where vend_id = 1001 or vend_id = 1003;
order by prod_price;

3.3.2 空值检查

select * from products
where prod_desc is null

3.3.3 And、Or、In、Not操作及其计算次序

  • AND & OR 运算符用于基于一个以上的条件对记录进行过滤。
  • 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
  • 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
  • 先and语句后执行or语句
# where 组合子句之 and 操作（交集）
select * from products where vend_id=1003 and prod_price <=10
ORDER BY prod_price;
# where 组合子句之 or 操作（并集）
select * from products where vend_id=1003 or vend_id =1002；
# where 组合子句之 AND 和 OR 的结合使用（使用圆括号来组成复杂的表达式）
SELECT * FROM products
WHERE prod_price <=10
AND (vend_id=1003 or vend_id =1002);
  • IN 操作符允许在 WHERE 子句中规定多个值
  • ‘=’ 规定一个值
# where 组合子句之 in 操作
select * from products where 
vend_id in (1001,1005,1002);
# where 组合子句之 in 与  = 的转换
select * from products where 
vend_id = 1001 or vend_id = 1005 or vend_id = 1002;
# where 组合子句之 not 操作
select * from products where 
vend_id not in (1002);
  • not操作
# where 组合子句之 not 操作
select * from products where 
vend_id not in (1002);

3.3.4 通配符（模糊匹配）

  • 通配符可用于替代字符串中的任何其他字符。
  • % 替代 0 个或多个字符
  • “_” 替代1个字符。
  • LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式
# 找到以 jet开头的产品
select prod_id ,prod_name from products
where prod_name like 'jet%'

在这里插入图片描述

select prod_id ,prod_name from products
where prod_name like '%anvil%'

select prod_id ,prod_name from products
where prod_name like 's%e'

select prod_id ,prod_name from products
where prod_name like '_ ton anvil'

select prod_id ,prod_name from products
where prod_name like '__ ton anvil'

注意：

  • 不要过度使用通配符，如果其他操作符能达到相同的目的，应优先使用其他操作符。
  • 非必要情况下，不要把通配符用在搜索模式的开始处，因为这样搜索起来是最慢的。
  • 仔细注意通配符的位置，如果放错地方可能不会返回想要的数据。

4.SQL处理数据高级方法

4.1正则表达式

正则表达式 用特殊的字符集合与一个文本串进行比较，过滤检索出想要的数据

  • 正则表达式是用来匹配文本的特殊的串（字符集合）
  • 如果你想从一个文本文件中提取电话号码，可以使用正则表达式。
  • 如果你需要查找名字中间有数字的所有文件，可以使用一个 正则表达式。
  • 如果你想在一个文本块中找到所有重复的单词，可以使用一 个正则表达式。
  • 如果你想替换一个页面中的所有URL为这些URL的实际 HTML链接，也可以使用一个正则表达式（对于最后这个例子，或者是两个正则表达式）。

4.1.1基本字符串匹配

# 使用 like 关键字和通配符 % 
select prod_name from products
where prod_name like '%1000';
# 使用正则表达式 REGEXP
select prod_name from products
where prod_name REGEXP '1000';
# 可以检索出prod_name 中所有含有‘1000’的行

在这里插入图片描述

4.1.2 特殊字符“ .” 的使用

“ .” 在正则表达式中表示匹配任意一个字符

# 使用 like 关键字和通配符 _
select prod_name from products
where prod_name like 'JetPack _000';
# 使用正则表达式 REGEXP 和特殊字符“.”
select prod_name from products
where prod_name REGEXP '.000';

在这里插入图片描述

  • 在这里会不会有人觉得奇怪，“.” 表示的是匹配任意一个字符，但结果显示的 JetPack 1000、JetPack
    2000，000的前面可不止一个字符，为什么能这样匹配呢？
  • 好好理解这句话 “正则表达式是用来匹配文本的特殊的串”
    你会发现利用正则表达式我们可以匹配出相应字段中所有含有需要匹配的文本的行。例子中我们需要匹配的是‘.000’，.
    可以代表任何一个字符，所以我们把 JetPack 1000、JetPack 2000
    匹配出来了，1000，2000就是我们想要匹配的文本。不管他们前后还有没有别的字符都会被查询的到。
  • 对比 关键字 like 与通配符的联合使用就无法达到这种效果了。

4.1.3 使用 or 进行匹配（条件匹配）

此 or 不是真的 or ，而是使用竖线 “|” 表示搜索 两个匹配文本串的其中一个

# 检索prod_name 中所有含有‘1000’或者‘2000’的行
select prod_name from products
where prod_name REGEXP '1000|2000';

在这里插入图片描述
正则表达式中可使用多个 or 条件

select prod_name from products
where prod_name REGEXP '1000|2000|anvil';

在这里插入图片描述

4.1.4 []匹配

”[]“代表需要匹配[]中所包含的任意一个字符

4.1.4.1 匹配几个字符之一
select prod_name from products
where prod_name REGEXP '[12] ton';

这里使用正则表达式 [12] ton 。 [12] 定义了一组字符1，2；可以匹配 1 ton 或者 2 ton.
其实，[] 是另一种形式的 or 语句

4.1.4.2 匹配范围

[]匹配[]中所包含的任意一个字符
[0-9] 匹配0到9的任意数字字符
[1-3] 匹配1到3的任意数字字符
[6-9] 匹配6到9的任意数字字符
[a-z] 匹配a到z 的任意字母字符

select prod_name from products
where prod_name REGEXP '[1-5] ton';
# 匹配1到5任意一个数字，所以返回了1 ton、2 ton、5 ton

在这里插入图片描述

4.1.4.3 排他符[^]
  • [^] 匹配未包含在[]中的任意字符。即，将匹配除指定字符外的任何东西。
  • 在集合开始处放置一个 ^
  • 例如，[^12]，会匹配除1，2外的任何东西
select prod_name from products
where prod_name REGEXP '[^345] ton'
# 不会出现 3 ton、4 ton、5 ton

在这里插入图片描述

4.1.5 特殊字符的匹配（. [] | -）

匹配特殊字符（. [] | -）时，需要使用 转义符(两个反斜杠\\)
\\- 表示查找 -，\\.表示查找 .

select prod_name from products
where prod_name REGEXP '\\.';
#查找 .

在这里插入图片描述
\\也用来引用元字符（具有特殊含义的字符）

元字符说明
\\f换页
\\n换行
\\r回车
\\t制表
\\v纵向制表

4.1.6 匹配字符类

[:digit:] 任意数字 （同[0-9]）
[:alnum:] 任意字母和数字 （同[a-zA-Z0-9]）
[:alpha:] 任意字母 （同[a-zA-Z]）
[:lower:] 任意小写字母 （同[a-z]）
[:upper:] 任意大写字母 （同[A-Z]）

select prod_name from products
where prod_name REGEXP '[[:digit:]]{4}'

4.1.7 匹配多个实例，重复元字符

元字符说明
*0个或多个匹配
+1个或多个匹配 （同{1,}）
?0个或1个匹配（同{0,1}）
{n}指定数目的匹配
{n,}不少于指定数目的匹配
{n,m}匹配数目的范围（m不超过255）
#匹配连在一起的任意4位数字的行
select prod_name from products
here prod_name REGEXP '[[:digit:]]{4}';

select prod_name from products
where prod_name REGEXP '[0-9][0-9][0-9][0-9]';

在这里插入图片描述

select prod_name from products
where prod_name REGEXP '\\([0-9] sticks?\\)';
# \\( 、\\) 使用转义符，[0-9]匹配1到9任意数字
#s? 中 使用 ？表示 s 可以出现0次或1次，所以出现了结果stick、sticks

在这里插入图片描述

  • ？ 匹配它前面的任何字符的0次或1次出现，换句话说，？前面的字符有（1个）或者没有（0个）都能被匹配

4.1.8 定位符，元字符

元字符说明
^文本的开始
$文本的结尾
[[:<:]]词的开始
[[:>:]]词的结尾
select prod_name from products
where prod_name REGEXP '^[\\.]'

在这里插入图片描述
在这里，我们可以回顾一下排他符[^ ]
总结一下^ 的用途：
1.在集合中 [^],表示不匹配集合所包含的字符
2.匹配输入字符串的开始位置。

4.2 计算字段

4.2.1 什么是计算字段

  • 有时候，我们需要的是直接从数据库中检索出转换、计算或格式化过的数据；比如物品订单表存储的是物品的价格和数量，而有时候我们需要的是总价格，这时我们就可以通过计算字段创建我们需要的数据了。
  • 计算字段不实际存在于数据库表中，而是运行时在select语句内创建的字段（列），可以使用别名。

4.2.2 拼接字段

  • 将值联结到一起构成单个值
  • concat() 函数，可以拼接字符串，可以拼接两个列。拼接的串之间用逗号分隔
SELECT * FROM vendors;
SELECT CONCAT(vend_city,' (',vend_country,')') from vendors;

在这里插入图片描述

  • 使用别名：使用 as 关键字
  • as 后接着你想要赋予字段的名字
SELECT CONCAT(vend_city,' (',vend_country,')') as '位置' 
from vendors;

在这里插入图片描述

4.2.3 执行算术计算

  • 对检索出来的数据进行算术计算
select prod_id,quantity * item_price as total 
from orderitems
ORDER BY total;

在这里插入图片描述

  • MySQL算术操作符
操作符说明
+加
-减
*乘
/除

4.3 MySQL数据处理函数

函数类型说明
文本函数处理文本串（如删除或填充值，转换值得大小写）
数值函数用于数值数据上的算术操作（如返回绝对值，进行代数计算 ）
日期和时间函数处理日期和时间值并从这些值中提取特定的成分（如返回两个日期之差，检查日期有效性等）
系统函数返回DBSM正使用的特殊信息（如返回用户登录信息，检查版本细节）

4.3.1 文本处理函数

函数作用
Rtrim(s)去除字符串右边的空格
Ltrim(s)去除字符串左边的空格
TRIM(s)去除字符串开头结尾的空格
upper(s)定义字符串全部大写
UCASE(s)将字符串转成大写
lower(s)定义字符串全部小写
LCASE(s)将字符串全部转成小写
ASCII(s)返回字符串 s 的第一个字符的 ASCII 码
CHAR_LENGTH(s)返回字符串s的字符数
CHARACTER_LENGTH(s)返回字符串s的字符数
CONCAT(s1,s2…sn)合并字符串
MID(s,n,len)从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)
substring(s,n,len)从字符串 s 的 n 位置截取长度为 len 的子字符串
SPACE(n)返回n个空格
RIGHT(s,n)返回字符串 s 的后 n 个字符
LPAD(s1,len,s2)在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
RPAD(s1,len,s2)在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len
INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。
SELECT UPPER(RTRIM(vend_name))as vend_name FROM vendors;
SELECT vend_name,ASCII(vend_name) FROM vendors;

4.3.2 数值函数

函数说明
ABS(x)返回 x 的绝对值
ACOS(x)求反余弦值(参数是弧度)
ASIN(x)求反正弦值(参数是弧度)
ATAN(x)求反正切值(参数是弧度)
AVG(expression)返回一个表达式的平均值，expression 是一个字段
CEIL(x)返回大于或等于 x 的最小整数
CEILING(x)返回大于或等于 x 的最小整数
COS(x)求余弦值(参数是弧度)
COT(x)求余切值(参数是弧度)
COUNT(expression)返回查询的记录总数，expression 参数是一个字段或者 * 号
DEGREES(x)将弧度转换为角度
n DIV m整除，n 为被除数，m 为除数
EXP(x)返回 e 的 x 次方
FLOOR(x)返回小于或等于 x 的最大整数
GREATEST(expr1, expr2, expr3, …)返回列表中的最大值
LEAST(expr1, expr2, expr3, …)返回列表中的最小值
LN()返回数字的自然对数，以 e 为底。
LOG(x) 或 LOG(base, x)返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。
LOG10(x)返回以 10 为底的对数
LOG2(x)返回以 2 为底的对数
MAX(expression)返回字段 expression 中的最大值
MIN(expression)返回字段 expression 中的最小值
MOD(x,y)返回 x 除以 y 以后的余数
PI()返回圆周率(3.141593）
POW(x,y)返回 x 的 y 次方
POWER(x,y)返回 x 的 y 次方
RADIANS(x)将角度转换为弧度
RAND()返回 0 到 1 的随机数
ROUND(x)返回离 x 最近的整数
SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
SIN(x)求正弦值(参数是弧度)
SQRT(x)返回x的平方根
SUM(expression)返回指定字段的总和
TAN(x)求正切值(参数是弧度)
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

4.3.3 日期函数

函数说明
ADDDATE(d,n)计算起始日期 d 加上 n 天的日期
ADDTIME(t,n)n 是一个时间表达式，时间 t 加上时间表达式 n
CURDATE()返回当前日期
CURRENT_DATE()返回当前日期
CURRENT_TIME返回当前时间
CURRENT_TIMESTAMP()返回当前日期和时间
CURTIME()返回当前时间
DATE()从日期或日期时间表达式中提取日期值
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
DAY(d)返回日期值 d 的日期部分
DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday
DAYOFMONTH(d)计算日期 d 是本月的第几天
DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推
DAYOFYEAR(d)计算日期 d 是本年的第几天
NOW()返回当前日期和时间
WEEK(d)计算日期 d 是本年的第几个星期，范围是 0 到 53
YEAR(d)返回年份

4.3.4 系统函数

函数说明
VERSION()返回数据库的版本号
USER()返回当前用户
NULLIF(expr1, expr2)比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1
ISNULL(expression)判断表达式是否为 NULL
IFNULL(v1,v2)如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

4.4 数据汇聚

  • 确定表中行数（或者满足某个条件或包含某个特定值的行数）。
  • 获得表中行组的和。
  • 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值。
  • SQL汇聚函数 AVG()、COUNT()、MAX()、MIN()、SUN()
  • 汇聚函数：运行在行组上，计算和返回单个值的函数
汇聚函数说明
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUN()返回某列值之和

4.4.1 AVG() 函数

#计算产品表中所有产品的平均价格
SELECT AVG(prod_price) as avg_price from products;

#计算特定供应商所提供产品的平均价格
SELECT AVG(prod_price) as avg_price 
from products
WHERE vend_id = 1003；

注意：AVG()函数忽略列值为 null 的行

4.4.2 count() 函数

  • COUNT() 确定表中行的数目或符合特定条件的行的数目
  • 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
  • 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
SELECT * from customers;
#对所有行进行计数
SELECT COUNT(*) as num_cust from customers;
# cust_email 为null的行不被计数
SELECT COUNT(cust_email) as num_cust from customers;

4.4.3 MAX() 函数

MAX() 返回指定列中最大值，要求指定列名

SELECT MAX(prod_price) as max_price from products;

4.4.4 MIN() 函数

MIN() 返回指定列中最小值，要求指定列名

SELECT MIN(prod_price) as max_price from products;

4.4.5 SUM() 函数

返回指定列值得和

select sum(quantity) from orderitems where order_num = 20005

4.4.6 聚集不同值（distinct）

对所有行计算，检索出包含不同的值

SELECT distinct vend_id FROM products;
SELECT AVG(DISTINCT prod_price) as avg_price from products WHERE vend_id = 1003;

4.4.7 组合聚集函数

select count(*) as num_items, MIN(prod_price) as min_price, 
max(prod_price) as max_price, avg(prod_price) as avg_price from products；

在这里插入图片描述

单个select 语句执行了4个聚集函数

4.5 数据分组

  • group by 子句
  • having 子句

4.5.1 创建分组

  • 利用group by 子句创建分组轻松知道哪个供应商提供了多少个产品。
SELECT vend_id,COUNT(*) as num_prods from products
GROUP BY vend_id;

在这里插入图片描述

  • 使用关键字 with rollup 得到汇总数
SELECT vend_id,COUNT(*) as num_prods from products
GROUP BY vend_id with rollup;

在这里插入图片描述

4.5.2 过滤分组

  • 使用 having 子句，having 必须跟在 group by 子句之后。
  • 对比where 子句，where 子句只是过滤指定的行，而 having 子句过滤的是分组，把不符合条件的组过滤掉。
  • where 分组前过滤，having 分组后过滤。
  • having 与 where 句法相同
SELECT vend_id,COUNT(*) as num_prods from products
GROUP BY vend_id
having num_prods >2;
#把供应产品数目小于2的供应商过滤掉
SELECT vend_id,COUNT(*) as num_prods from products
WHERE prod_price >=10
GROUP BY vend_id
having num_prods >=2
ORDER BY num_prods;
#使用where 把产品单价小于10的行过滤掉，然后按照供应商id分组，再把供应产品数目小于2的供应商过滤掉，最后排序。

4.5.3 分组与排序

ORDER BYGROUP BY
排序产生的输出分组行。输出可能不是分组的顺序
任何列都可以使用（非选择的列也可以使用）只能使用选择列或表达式列，而且必须使用每个选择列表达式
SELECT order_num,SUM(quantity*item_price) as order_total
from orderitems
GROUP BY order_num
HAVING order_total>=50;

4.5.4 SELECT 子句顺序

子句说明是否必须使用
SELECT要返回的列或表达式是
FROM从中检索数据的表仅在从表选择数据时使用
WHERE行级过滤否
GROUP BY分组说明仅在按组计算聚集时使用
HAVING组级过滤否
ORDER BY输出排序顺序否
LIMIT要检索的行数否
SELECT order_num,SUM(quantity*item_price) as order_total
from orderitems
GROUP BY order_num
HAVING order_total>=50
ORDER BY order_total
LIMIT 3;

在这里插入图片描述

4.6 子查询

  • 子查询：嵌套在其他查询中的查询

4.6.1 利用子查询进行过滤

  • 以订单录入系统表为例，假如需要列出订购物品TNT2的所有客户，应该怎样检索？
#(1)检索包含物品TNT2的所有订单的编号。
SELECT order_num,prod_id from orderitems
where prod_id = 'tnt2';
#(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
SELECT cust_id,order_num FROM orders
WHERE order_num in (20005,20007);
#(3) 检索前一步骤返回的所有客户ID的客户信息。
SELECT * FROM customers
WHERE cust_id in (10001,10004);
  • 上述每个步骤都可以单独作为一个查询来执行。
  • 可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。
SELECT cust_id FROM orders
WHERE order_num in (SELECT order_num from orderitems
where prod_id = 'tnt2');
SELECT * FROM customers
WHERE cust_id in (SELECT cust_id FROM orders
WHERE order_num in (SELECT order_num from orderitems
where prod_id = 'tnt2'));
  • 在select 语句中，子查询总是从内向外处理。
  • SQL对于能嵌套的子查询的数目没有限制，不过实际时有性能的限制，不能嵌套太多的子查询。
  • where 子句中使用子查询时需要注意列必须匹配

4.6.2 作为计算字段使用子查询

  • 假如现在需要显示customers表中每个客户的订单总数，你会怎么做呢？
#(1) 查看客户表有哪些客户
SELECT cust_id,cust_name FROM customers c;

在这里插入图片描述

#(2)根据第一步结果相应的去查找哪个客户下了多少订单
SELECT COUNT(*) as '10001的订单数' from orders
WHERE cust_id = 10001;

在这里插入图片描述

  • 是不是感觉很费力！那就看看怎么使用子查询作为计算字段吧！
SELECT cust_id,cust_name,(SELECT COUNT(*) FROM orders s# s 是orders表的别名，代表的是orders表
WHERE c.cust_id = s.cust_id) as '订单总数' FROM customers c;# o 是customers表的别名，代表的是customers表

在这里插入图片描述

  • 这个例子中，“订单总数”就是我们利用圆括号中的子查询创建的计算字段。
(SELECT COUNT(*) FROM orders s WHERE c.cust_id = s.cust_id) as '订单总数'
  • 跟子查询过滤不太一样的是，我们这里使用了限定列名 WHERE c.cust_id = s.cust_id 这个where语句告诉SQL
    需要比较orders表中的cust_id 与customers表中的cust_id，当他们相同时把订单的数目返回来。
  • 在这个例子中，该子查询其实执行了5次，检索了5个客户。 当c.cust_id=10001时，在s 中查找s.cust_id
    ,所以查找到了2个订单编号，当c.cust_id=10002时，在s
    中没找到s.cust_id=10002，所以返回了0，说明10001这个客户没有订单。

4.7 联结表

4.7.1 创建联结表

SELECT * FROM vendors v,products p
WHERE v.vend_id = p.vend_id;

在这里插入图片描述
当然你也可以只创建你想要的字段

SELECT v.vend_id,p.prod_name,p.prod_price FROM vendors v,products p
WHERE v.vend_id = p.vend_id;

在这里插入图片描述
再看一下多表联结

SELECT * FROM customers c,orders o,orderitems oi
WHERE c.cust_id=o.cust_id and o.order_num =oi.order_num
#通过外键主键关系联结了3个表

SELECT c.cust_name,o.order_num,oi.prod_id FROM customers c,orders o,orderitems oi
WHERE c.cust_id=o.cust_id and o.order_num =oi.order_num
# 创建我想要的数据字段

SELECT c.cust_name,o.order_num,oi.prod_id FROM customers c,orders o,orderitems oi
WHERE c.cust_id=o.cust_id and o.order_num =oi.order_num
and c.cust_name = 'Coyote Inc.';
#找到某个客户所购买的产品id

4.7.2 自联结

  • 引入例子：假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。
  • 此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。
SELECT prod_id,prod_name,vend_id FROM products
WHERE vend_id in (SELECT vend_id FROM products WHERE prod_id='dtntr');
#使用子查询，返回生产了DTNTR物品的供应商id。
#该id用于外部查询的where子句中，检索出这个供应商生产的所有物品。
  • 接着，使用自联结看看效果
SELECT p1.prod_id,p1.prod_name,p1.vend_id 
FROM products p1,products p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'dtntr';
# where 通过vend_id 匹配表p1、p2，
#按照p2.prod_id='dtntr'过滤数据，把需要的vend_id号传达给p1表，
#从而找出对应vend_id号的相关产品信息。

在这里插入图片描述

4.7.3 自然联结

  • 无论何时，我们对表进行联结时，都至少有一个列不止一次出现在一个表中。
  • 自然联结关注的是一一对应，通过主键与外键的关联，把相关联的信息显示出来，而关联不上的数据会被过滤掉。比如客户表id为1009，如果在订单表找不到cust_id=1009 ，则这条数据不会显示出来。
  • 事实上，我们建立的每个内部联结都是自然联结
select c.* ,o.order_num,o.order_date from customers c , orders o ,orderitems oi
where c.cust_id = o.cust_id and oi.order_num = o.order_num
and oi.prod_id ='FB'

4.7.4 外部联结

  • 对比内部联结，外部联结有时候可以包含没有关联的行

以下例子，联结包含了哪些在相关表中没有关联行的行，这种类型的联结称为外部联结

  • 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；
  • 列出所有产品以及订购数量，包括没有人订购的产品；
  • 计算平均销售规模，包括那些至今尚未下订单的客户。
#内部联结，检索所有客户及其订单
SELECT c.cust_id,o.order_num FROM customers c
INNER JOIN orders o
ON c.cust_id = o.cust_id;
# INNER JOIN 内部联结，INNER表示是内部联结，省略inner也可以
# 另一种写法
SELECT c.cust_id,o.order_num FROM customers c,orders o
WHERE c.cust_id = o.cust_id;

在这里插入图片描述

SELECT c.cust_id,o.order_num FROM customers c
LEFT JOIN orders o
ON c.cust_id = o.cust_id;
# LEFT JOIN 左边联结，实际上是LEFT OUTER JOIN，表示这是外部联结，此处省略了OUTER

在这里插入图片描述

SELECT c.cust_id,o.order_num FROM customers c
RIGHT JOIN orders o
ON c.cust_id = o.cust_id;
# RIGHT JOIN 右边联结

在这里插入图片描述

  • 外部联结必须使用 RIGHT JOIN、LEFT JOIN 关键字
  • 左外部联结与右外部联结唯一的差别是所关联的表的顺序不一样。
  • 顾名思义，LEFT指在from子句之后的左边表（customers表）中选择所有的行，RIGHT是指在from子句之后的右边表（orders）中选择所有的行。
  • 换种说法，from之后有两个表，customers、orders，相对来说customers是左边的表，而orders是右边的表。
  • 为什么右边联结与左边联结的结果不一样，不必明说也可意会了吧！

4.7.5 带聚集函数的联结

  • 引入例子：如果要检索所有客户及每个客户所下的订单数，你会怎么做？
#(1)内部联结
SELECT c.cust_id,c.cust_name,COUNT(o.order_num) AS '订单数' FROM customers c,orders o
WHERE c.cust_id = o.cust_id
GROUP BY o.cust_id;

在这里插入图片描述

#(2)外部联结
SELECT c.cust_id,c.cust_name,COUNT(o.order_num) AS '订单数' FROM customers c LEFT JOIN orders o
ON c.cust_id = o.cust_id
GROUP BY c.cust_id;

在这里插入图片描述

5.SQL高级数据查询

5.1 组合查询

  • 利用关键字union 将多条select语句组合成一个结果集
  • 在单个查询中从不同的表返回类似结构的数据；
  • 对单个表执行多个查询，按单个查询返回数据。

5.1.1 创建组合查询

  • 引入例子：假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）。
  • 可以利用WHERE子句完成，不过这次主要是使用UNION。
#使用where子句
SELECT prod_id,vend_id,prod_price FROM products
WHERE prod_price <=5 OR vend_id IN (1001,1002)
ORDER BY vend_id,prod_price;
# 使用组合语句 union
SELECT prod_id,vend_id,prod_price FROM products
WHERE prod_price <=5 
UNION
SELECT prod_id,vend_id,prod_price FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id,prod_price;
  • 创建组合查询即在两条select语句之间加入关键字 UNION

5.1.2 UNION的使用规则

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
  • 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
  • UNION的查询结果会自动去除重复的行
  • 如果需要返回所有匹配的行，可以使用 UNION ALL
SELECT prod_id,vend_id,prod_price FROM products
WHERE prod_price <=5 
UNION ALL
SELECT prod_id,vend_id,prod_price FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id,prod_price;
  • 对组合查询的排序，order by 必须只能使用一次且只能放在最后一条select 语句之后，以上例子都可观察到。

5.2 全文本搜索

  • MySQL最常用的数据库引擎为 myisam 和 inodb
  • MyISAM 支持全文本搜索
  • INODB 不支持全文本搜索
  • 在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。
  • 为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引
  • 在索引之后，select 可与match() 和 against() 一起使用以实际执行搜索。
  • 一般会在创建表时启用全文本搜索
CREATE TABLE `productnotes`  (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `note_date` datetime NOT NULL,
  `note_text` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`note_id`) USING BTREE,
  FULLTEXT INDEX `note_text`(`note_text`)
) ENGINE = MyISAM AUTO_INCREMENT = 115 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
# 以上的代码中根据以下这条子句启用了全文索引
  FULLTEXT INDEX `note_text`(`note_text`)
  • 在定义之后，MySQL自动维护该索引，在增加、更新或删除行时，索引随之自动更新。

5.2.1 全文本搜索基本应用

  • 在索引之后，使用两个函数 Match()、Against() 执行全文本搜索。
  • Match() 指定被搜索的列
  • Against() 指定要使用的搜索表达式
SELECT note_text FROM productnotes
WHERE MATCH(note_text) against('rabbit');
  • 注意：传递给 Match() 的值必须与FULLTEXT() 定义中的相同，如果指定多个列，则必须列出它们（而且次序必须正确）。
# 使用正则表达式
SELECT note_text FROM productnotes
WHERE note_text REGEXP 'rabbit';
# 使用关键字 like 
SELECT note_text FROM productnotes
WHERE note_text LIKE '%rabbit%';
  • 通过对比三个结果，你会发现全文本搜索的一个重要部分就是对结果排序（匹配rabbit时，全文本搜索中先返回rabbit 作为第3个词的行，因为包含词rabbit作为第3个词的行的等级比作为第20个词的行高）。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。而like语句及正则表达式不具有这种特别的排序功能。
  • 如果还不太理解，那再继续看以下例子。
SELECT note_text,MATCH(note_text) against('rabbit') AS rank_notetext FROM productnotes;

在这里插入图片描述

  • 在这里，Match()和Against() 作为一个计算字段被返回，别名是rank_notetext。此列包含全文本搜索计算出来的等级值，也就是匹配率。这里每个行以及每个行与rabbit这个词的等级值都显示出来，不包含词rabbit的行，等级值为0，文本中词越靠前的行等级值就越高。
  • 这里体现了全文本搜索提供了简单的LIKE搜素不能提供的功能，而且由于数据是索引的，全文本搜索还相当快。

5.2.2 查询扩展

  • 查询扩展用来设法放宽所返回的全文本搜索结果的范围。
  • 使用查询扩展时，MySQL对数据和索引进行两遍臊面来完成搜索：
  • 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
  • 其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。
  • 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
  • 利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
# 这是简单的全文本查询，没有查询扩展，结果返回了一行包含词anvils的行
SELECT note_text FROM productnotes
WHERE MATCH(note_text) against('anvils');
# 使用了查询扩展
SELECT note_text FROM productnotes
WHERE MATCH(note_text) against('anvils' WITH QUERY expansion);

在这里插入图片描述

  • 这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（customer和recommend)，所以也被检索出来了。
  • 查询扩展的行越多越好

5.2.3 布尔文本查询

  • 要匹配的词；
  • 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
  • 排列提示（指定某些词比其他词更重要，更重要的词等级更高）;
  • 表达式分组；
  • 另外一些内容。
布尔操作符说明
+必须包含
-必须不包含
>增加优先等级
<降低优先等级
*词尾通配符
“”定义短句
  • 为了匹配包含heavy但不包含任意以rope开始的词的行，可使用以下查询：
SELECT note_text FROM productnotes
WHERE MATCH(note_text) against('heavy -rope*' in boolean mode);

5.2.4 全文本搜索说明

  • 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
  • MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
  • 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于 IN BOOLEAN MODE。
  • 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
  • 忽略词中的单引号。例如，don’t索引为dont。
  • 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
  • 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。

5.3 插入数据

  • INSERT INTO……VALUES(……)

5.3.1 插入完整的行

INSERT语法，要求指定表名和被插入新行中的值

insert  into customers values (  null,'ABC','100 Main street','Los angeles','CA','90046','USA',null,null )
  • 上例插入一行数据到customers表中，存储在表列中的每个数据在values子句中，对每个列必须提供一个值，没有值时应使用NULL（前提是该表允许该列是空值），各个列必须以它们在表中出现的次序填充。
  • 这种语法特点是简单但不安全，应尽量避免使用。
  • 编写insert语句更安全（但更繁琐）的方法如下：
  • 表名后括号里明确给出列名，values后相对应的给出值。
insert into customers(
  cust_id,
  cust_name,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country,
  cust_contact,
  cust_email)
values (null,'john','CDEF','99878','ug',null,null,null,null);

5.3.2 插入行的一部分

insert into customers (cust_name,cust_zip) values ('CDEF','99878');

5.3.3 插入多行

insert into customers (cust_name,cust_zip) values ('2CDEF','199878');
insert into customers (cust_name,cust_zip) values ('3CDEF','399878');
insert into customers (cust_name,cust_zip) values ('4CDEF','599878');
insert into customers (cust_name,cust_zip) values ('5CDEF','799878');

5.3.4 插入某些查询的结果

insert into customers2 select * from customers;

5.4 更新和删除数据

  • UPDATE语句
  • DELETE语句

5.4.1 更新数据

  • UPDATE……SET ……
# 复制表customers
CREATE TABLE customers2 as SELECT * FROM customers;
update customers2 set cust_email = 'elmer@fudd.com'
where cust_id =10005;

5.4.2 删除数据

  • DELETE FROM ……
delete from customers2 where cust_id = 10006;

5.5 视图

  • 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
  • 视图不包含表中应有的任何列或数据，它包含的是一个SQL查询

视图常见的应用：

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

视图的功能：

  • 可执行select操作，过滤和排序数据
  • 可将视图联结到其他视图或表
  • 能添加和更改数据（添加和更新数据有一定的限制）

但视图仅仅是用来查看存储在别处的数据的一种设施，视图本身不包含数据，它们返回的数据是从其他表中检索出来的，在添加或更改这些表中的数据是，视图将返回改变过的数据。

视图的性能问题：

  • 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

  • 视图必须唯一命名
  • 对于可以创建的视图数目没有限制
  • 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
  • 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
  • ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
  • 视图不能索引，也不能有关联的触发器或默认值。
  • 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

创建视图

  • 视图用CREATE VIEW语句来创建。
  • 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
  • 用DROP删除视图，其语法为DROP VIEW viewname;。
  • 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE
    VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

还记得在学习联结表时，我们为了检索购买了TNT2产品的客户是怎么做的吗？

SELECT cust_name,cust_contact,prod_id
FROM customers c,orders o,orderitems oi
WHERE c.cust_id = o.cust_id
AND o.order_num =oi.order_num
AND prod_id = 'TNT2';

在这里插入图片描述
现在，我如果想检查购买了别的产品的客户呢？再创建一个这样的语句吗？也不是不行，只是我们可以通过创建视图的方法，一次性编写基础的SQL，然后可以根据需要多次使用，极大地简化了复杂的SQL语句。

#讲了这么多，先创建个视图
CREATE VIEW productscustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers c,orders o,orderitems oi
WHERE c.cust_id = o.cust_id
AND o.order_num =oi.order_num;

在这里插入图片描述

#查看视图
SELECT * FROM productscustomers

在这里插入图片描述

#查看购买了TNT2产品的客户
SELECT cust_name,cust_contact,prod_id
FROM productscustomers
WHERE prod_id = 'tnt2';

在这里插入图片描述

#还想再看看购买了产品FB的客户
SELECT cust_name,cust_contact,prod_id
FROM productscustomers
WHERE prod_id = 'fb';

在这里插入图片描述
视图内容格式化

  • 视图还可以重新格式化检索出来的数据
#学习拼接字段时，我们利用了select语句在单个组合计算列中返回供应商名和位置。
SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;

在这里插入图片描述

#假如我们需要经常需要这个格式的结果，可创建个视图，不必每次都进行拼接
CREATE VIEW vendorlocation AS
SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;

在这里插入图片描述

# 检索相关数据
SELECT * FROM vendorlocation;

在这里插入图片描述
视图过滤

#创建视图，过滤掉信息为空值的数据
CREATE view customernotnull as
select * from customers
where cust_contact is not NULL

视图与计算字段

CREATE view orderprice as
select prod_id,quantity,item_price, 
(quantity*item_price) as total from orderitems;

视图可更新（可使用insert、update、delete，但有条件）

  • 不可包含group by和having的使用
  • 不可包含子查询
  • 不可包含计算字段
  • 不可包含distinct

5.6 存储过程

  • 简单来说，存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，但它们的作用不仅限于批处理。
  • 存储过程的使用有3个好处：简单、安全、高性能。
  • 存储过程的缺点：编写较基本的SQL语句复杂，需要更高的技能更丰富的经验；许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

5.6.1 创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
 SELECT AVG(prod_price) AS avg_price
 FROM products;
END;

在这里插入图片描述

  • 此例中，存储过程名为：productpricing
  • 用CREATE PROCEDURE productpricing() 来定义
  • BEGIN 和 END 语句用来限定存储过程体，此过程体仅用了一个简单的 select 语句。
  • 如果存储过程接受参数，可将其列举进productpricing() 的括号中
delimiter //
create PROCEDURE productpricing2()
begin
select AVG(prod_price) from products;
end //
# delimiter;——>保护程序

5.6.2 调用存储过程

CALL productpricing();
#执行存储过程并显示返回的结果

在这里插入图片描述

  • 存储过程实际上是一种函数，所以存储过程名后需要有（）括号，即使不传递参数也是需要的。

5.6.3 删除存储过程

DROP PROCEDURE productpricing;

在这里插入图片描述

  • 注意：删除存储过程时，存储过程名后没有使用括号（）。

5.6.4 使用参数

CREATE PROCEDURE productpricing(
 OUT pl DECIMAL(8,2),
 OUT ph DECIMAL(8,2),
 OUT pa DECIMAL(8,2)
 )
BEGIN
 SELECT MIN(prod_price)
 INTO pl
 FROM products;
 SELECT MAX(prod_price)
 INTO ph
 FROM products;
 SELECT AVG(prod_price)
 INTO pa
 FROM products;
END;

在这里插入图片描述

  • 此存储过程接受了3个参数，pl、ph、pa，每个参数必须具有指定的类型，这里使用了十进制值（DECIMAL）
  • 关键字 OUT 表示相应的参数是从存储过程中传出的一个值（返回给call 调用）
关键字作用
IN传递给存储过程
OUT从存储过过程传出
INOUT对存储过程传入和传出
INTO保存存储体的值
CALL productpricing(@pl,@ph,@pa);
# 调用存储过程，必须指出3个变量名
# 所有的MySQL变量都必须以@开始

在这里插入图片描述

SELECT @pl,@pa;

在这里插入图片描述

# 下面使用 in 和 out 创建存储过程
CREATE PROCEDURE ordertotal(
 IN o_number INT,
 OUT o_total DECIMAL(8,2))
BEGIN
 SELECT SUM(item_price*quantity)
 INTO o_total
 FROM orderitems
 WHERE order_num =o_number;
END;
# 调用
CALL ordertotal(20005,@o_total);
SELECT @o_total;

在这里插入图片描述

  • 此例中，传递了两个参数，o_number (定义为IN ，作为传入参数传给了存储过程)、o_total (定义为OUT ，作为传出参数用于返回值)

5.7 游标

5.7.1 创建游标

# 用 DECLARE 语句创建游标
CREATE PROCEDURE processorders()
BEGIN
 DECLARE ordernumbers CURSOR
 FOR
 SELECT order_num FROM orders;
END;
# 存储过程处理完成后，游标就消失，因为游标局限于存储过程

5.7.2 打开和关闭游标

# 打开游标
OPEN ordernumbers;
# 在处理open语句执行查询时，存储检索出的数据以供浏览和滚动
# 游标处理完成后，当使用CLOSE语句关闭游标
CLOSE ordernumbers;
  • CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。
  • 游标关闭后可用OPEN 语句打开，否则不能使用
  • 隐含关闭：如果不明确关闭游标，语句到达END语句时会自动关闭游标
CREATE PROCEDURE processorders()
BEGIN
 -- 声明定义一个游标
 DECLARE ordernumbers CURSOR
 FOR
 SELECT order_num FROM orders;
 -- 打开游标
 OPEN ordernumbers;
 -- 关闭游标
 CLOSE ordernumbers;
END;
# 此存储过程声明、打开和关闭了游标。但对检索出来的数什么也没有做

5.7.3 使用游标数据

CREATE PROCEDURE processorders2()
BEGIN
 
 -- DECLARE local variables 声明局部变量
 DECLARE o INT;
 
 DECLARE ordernumbers CURSOR
 FOR
 SELECT order_num FROM orders;
 
 OPEN ordernumbers;
 
 -- Get order number 获取当前行
 FETCH ordernumbers INTO o;
 
 CLOSE ordernumbers;
 
END;
  • 其中，fetch 用来检索当前行的order_num 列（将自动从第1行开始），到一个名为 o 的局部变量中。对检索出的数据不做任何处理。
# 循环检索数据，从第1行到最后一行
CREATE PROCEDURE processorders0()
BEGIN
 -- DECLARE local variables 声明定义局部变量done（用作循环标记），开始值默认为0
 DECLARE done boolean DEFAULT 0;
 -- 声明局部变量o 
 DECLARE o INT;
 DECLARE ordernumbers CURSOR
 FOR
 SELECT order_num FROM orders;
 -- DECLARE continue handler
 -- 当出现‘02000’错误（游标取不到数据）时把局部变量done的值设置为1
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 OPEN ordernumbers;
 REPEAT
 -- Get order number 把游标获得的数据取到变量o中
 FETCH ordernumbers INTO o;
 -- 查询变量的值
 select o;
 -- end of loop 结束循环的条件
 UNTIL done 
END REPEAT;
-- 关闭游标，释放游标使用的所有内部内存和资源
CLOSE ordernumbers;
END;
call processorders0;

在这里插入图片描述

  • 通过结果可以看到，游标就是一个结果集，把select语句的结果一行一行的返回。
# 以下是把游标得到的结果集放在创建的表中
CREATE PROCEDURE processorders3()
BEGIN
 -- DECLARE local variables
 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
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 -- CREATE a table to store the results
 CREATE TABLE IF NOT EXISTS ordertotals(order_num INT,total DECIMAL(8,2));
 OPEN ordernumbers;
 REPEAT
 -- Get order number
 FETCH ordernumbers INTO o;
 -- INSERT order and total into ordertotals
 INSERT INTO ordertotals(order_num,total)
 VALUES(o,t);
 select * from ordertotals;
 -- end of loop
 UNTIL done END REPEAT;
 CLOSE ordernumbers;
END;

5.8 触发器

  • 触发器是在我们执行delete update insert语句时自动执行另外一组sql语句的功能。
  • 触发器只能用于表，不能用于视图中

5.8.1 创建触发器

触发器的创建需要包含以下4点信息：

  • 唯一的触发器名；
  • 触发器关联的表；
  • 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
  • 触发器何时执行（处理之前或之后）
# 创建触发器
create trigger newproduct after insert on products
for each row select 'Product add' into @ee;

  • 以上例子创建了一个名为 newproduct 的触发器，在表 products 每次完成插入语句时把’Product add’存进变量@ee中

  • 接着，为了验证效果，我们可试着插入一行数据

insert into products(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES(666,1006,'随便','999','好产品')；
# 查看结果
SELECT @ee;

5.8.2 触发器的删除

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

5.8.3 INSERT 触发器

create trigger neworder after insert on orders
for each row select NEW.order_num into @ee;
  • 这里创建了一个名为neworder的触发器，在插入一个新的订单后（after insert on orders），MySQL会生成一个新的订单号并保存在order_num 中，触发器会获得这个值并返回它。
  • 以下测试触发器：
insert into orders(order_date,cust_id)
values (now(),10001);

在这里插入图片描述

  • 以上例子，在insert触发器的代码内，引用了‘new‘虚拟表，用来访问被插入的行
  • 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
  • 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

5.8.4 DELETE 触发器

create trigger deleteorder  before  delete on orders
for each row 
BEGIN
   insert into archive_orders(order_num,order_date,cust_id)
   values (old.order_num,old.order_date,old.cust_id);
end;
  • 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行
  • OLD中的值全都是只读的，不能更新。

5.8.4 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);

6.MySQL数据安全

6.1 事务管理

  • 用来维护数据库的完整性，它保证成批的SQL操作要么全部成功，要么全部失败。
  • 事务处理主要是用于管理INSERT、UPDATE、DELETE语句
  • 并非所有引擎都适合事务管理：
  • myisam不支持事务管理
  • inodb 支持事务管理

事务处理术语

  • 事务（transaction）一组SQL语句
  • 回退（rollback）撤销指定SQL语句的过程
  • 提交（commit ）将未存储的SQL语句结果写入数据库表中
  • 保留点（savepoint）事务处理中设置的临时占位符，可对它发布回退

事务处理开始语句

START TRANSACTION;

使用ROLLBACK

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

在这里插入图片描述

  • 以上例子中，选择了表ordertotals进行操作，第一个select语句说明了这表不为空（开始的状态），开始事务管理，执行了delete语句，删除ordertotals表中的所有行，第二个select语句验证了表ordertotals确实为空了，然而，当执行ROLLBACK语句时，回退事务处理（START TRANSACTION）之后的所有语句，即撤退了事务处理中的SQL语句。

使用COMMIT

START TRANSACTION;
DELETE FROM ordertotals WHERE total is NULL;
DELETE FROM ordertotals WHERE order_num = 20005;
COMMIT;
SELECT * FROM ordertotals;

在这里插入图片描述
使用保留点（SAVEPOINT）

  • 为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals WHERE order_num = 20007;
SAVEPOINT d1;
DELETE FROM ordertotals WHERE order_num = 20009;
SELECT * FROM ordertotals;
ROLLBACK TO d1;
SELECT * FROM ordertotals;

在这里插入图片描述

6.2 数据备份和性能管理

6.2.1 数据备份

  • 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
  • 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
  • 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。
小七黛
关注
  • 58
    点赞
  • 踩
  • 316
    收藏
    觉得还不错? 一键收藏
  • 26
    评论
专栏目录
只此一篇，让你掌握Mysql所有语法及用法
LYX_WIN
06-02 1062
- 查询emp表中各个职位的薪水总和，只查询薪水总和大于5000的职位，且职位不是SALES开头的，并按照薪水总和降序排列。-- 查询emp表中各个职位的薪水总和，只查询薪水总和大于5000的职位，且销售部（dept表）除外，并按照薪水总和降序排列。-- 索引虽然提高了查询速度，同时却会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。-- 在编程语言中，函数是为了实现特定功能的小程序，在很多高级语言中，都有一些给定的函数，也可以自己编写函数。
MySQL学习之基础语法详解
Java学习博客
08-19 1008
MySQL基础语法，对表的新增，修改，删除。对于数据的新增，修改删除，查询，包括多表联合查询，嵌套子查询，左外连接，右外连接，内连接，分组，排序。约束的建立，包括非空，主键，默认，外键，...
SQL语句简单增删改查
Infinityfeng的博客
03-30 1279
什么是 SQL？ SQL 是 结构化查询语言，专门用来访问和处理数据库的编程语言。能够让我们以编程的形式，操作数据库里面的数据 三个关键点： SQL 是一门数据库编程语言 使用 SQL 语言编写出来的代码，叫做 SQL 语句 SQL 语言只能在关系型数据库中使用（例如 MySQL、Oracle、SQL Server）。非关系型数据库（例如 Mongodb）不支持 SQL 语言 SQL 能做什么？ 从数据库中查询数据 向数据库中插入新的数据 更新数据库中的数据 从数据库删除数据
笔记：MySQL语法
Guido Kuo's Blog
01-03 190
1.SQL不区分大小写，ABC和abc是一样的 2.SQL可以分为两部分，数据操作语言（DML）和数据定义语言（DDL） DML: select --从数据表中获取数据 update --更新数据表中的数据 delete --从数据表中删除数据 insert into --向数据表中插入数据 DDL： create database --创建新的数据库 alter database --修改数据库...
Mysql 基本语法（学习笔记）
Mental-Flow的博客
07-20 358
一、数据库的一些基本概念 1) 什么是数据库 ​ 数据库是用于存储和管理数据的仓库 2) 数据库用什么特点 数据库实际上就是一个文件管理系统，是用来持久化存储数据的，就有点类似于我们常见的文件夹。 它的作用是方便我们存储和管理数据。 我们可以通过一个统一的方式操作数据库(也就是我们所说的sql语句)。 3) 数据库的一些配置问题 MySQL启动 手动 在电脑左下角的搜索框中输入计算机管理 然后在服务和应用的服务中找到服务 然后找到mysql，手动启动mysql 使用cm
mysql语法学习笔记
10-11
记的视频来自于:...我在视频的基础上做了一些修改:比如说视频中可能有些不是太严谨但是被我发现了的,或者在弹幕中我看到有人提出疑问的,再或者一些我自己的见解,我都会有说明
MySQL学习笔记 MySQL学习笔记
最新发布
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MYSQL的select 学习笔记
09-11
1. **换行书写**：在编写SQL语句时，虽然换行不会影响语法，但在长查询中使用换行可以提高可读性。例如： ```sql $sql="select * from article where id=1"; ``` 2. **IN操作符**：用于批量查询，一次可以检查...
MySQL基本语法笔记
09-06
最近在学习MySQL，一边学习，一边做个日记帮助同样的小伙伴了解一点数据知识。
MySQL架构与内部模块学习笔记
08-14
本笔记主要探讨了MySQL 5.7版本的架构和内部组件，重点关注了SQL查询的执行流程、通信协议、连接管理以及查询缓存等方面。 首先，了解一条SQL查询的执行过程至关重要。当客户端（例如一个程序或数据库工具）想要...
mysql语法笔记
谷雨2004的博客
12-08 873
一.简单搜索 1.模糊条件查询 用于搜索功能 示例：查询出姓名中含有字母e的有哪些 select *from emp where ename like '%e%'; 练习：查询姓名中以e结尾的员工有哪些 select *from emp where ename like '%e'; 查询姓名中倒数第二个字符是e的员工 select *from emp where ename like '%e_'; % 匹配任意个字符 >=0 _ 匹配任意一个字符 =1 以上必须结合like使用 2.分页查询
Mysql 语法笔记
weixin_49579979的博客
07-26 124
Mysql 语法笔记
MySQL数据库基础语法总结
Zincy星辰
03-06 2559
目录 精华总结 1. 数据库的操作 1.1 显示当前的数据库 1.2 创建数据库 2. 常用数据类型 2.1 数值类型 2.2 字符串类型 2.3 日期类型 3. 表的操作 3.1 查看表 3.2查看表结构 3.3创建表 3.4删除表 4. 练习 精华总结 操作数据库： show databases ; --显示 create database xxx ; --创建 use xxx ; --使用 dr...
MySql语法学习
qq_42384206的博客
11-11 116
1.检索数据 检索不同的行 select distinct 行 from 表; 限制结果 select 行 from 表 limit 3;(3表示，限制行数) select 行 from 表 limit 3,3;(从行3开始的3行) 使用完全限制的表名 select 表.行 from 表; 2.排序检索数据 排序数据 select 行 from 表 order by 行;（以字母顺序排...
数据库系统教程(第二版何玉洁)课后数据库上机实验答案
chenyuchao的博客
11-08 7万+
数据库系统教程(第二版何玉洁)课后数据库上机实验答案 数据操作语句，索引和视图，触发器和存储过程
建立数据库，建立一个“学生”表student。
热门推荐
lushanghuafei的博客
10-28 7万+
1.建立数据库，建立一个“学生”表student。 2.设计思想：首先利用create database语句建立一个数据库，再用create table语句按要求建立基本表，再按照规则添加数据。 3.实验代码及注释： 创建数据库 mysql> create database ymz; Query OK, 1 row affected (0.03 sec) 使用数据库 mysql> us...
【Week 10 作业C】拿数问题II
应是天仙狂醉，乱把白云揉碎
04-25 364
题目描述 YJQ 上完第10周的程序设计思维与实践后，想到一个绝妙的主意，他对拿数问题做了一点小修改，使得这道题变成了 拿数问题 II。 给一个序列，里边有 n 个数，每一步能拿走一个数，比如拿第 i 个数， Ai = x，得到相应的分数 x，但拿掉这个 Ai 后，x+1 和 x-1 (如果有 Aj = x+1 或 Aj = x-1 存在) 就会变得不可拿（但是有 Aj = x 的话可以继续拿这个...
创建一个数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）
汉子一枚
04-18 4万+
创建一个数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表… 表（一）Student (学生表) 属性名 数据类型 可否为空 含 义 Sno varchar (20) 否 学号 Sname varchar (20) 否 学生姓名 Ssex varchar ...
Mysql语法学习大全（初学者）
weixin_42105345的博客
05-20 3607
用户管理新建用户及密码：temp为名，123为密码，locahost为固定地址登陆# 以下为两种创建方式 CREATE USER temp@localhost IDENTIFIED BY ‘123’ insert into mysql.user(Host,User,Password) values("localhost","test",password("1234")); #只要更改了用户及密...
mysql初学者笔记
08-26
- *2* [MySQL学习笔记2-高级查询与存储.md](https://download.csdn.net/download/weixin_52057528/88240999)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 26
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值