MySQL补充笔记

查询数据库的信息的一些命令

use （数据库名） ——切换到某个数据库

show databases ——展示当前可用数据库

show tables ——展示当前所选数据库的表

show columns from （表名） ——从某个表中返回所有列（字段）

展示列的另一种简便的方法 describe （表名）

展示服务器状态：show status

显示建表或建库的语句（逆向操作）：

show create database / show create table

显示授予用户的安全权限 ：show grants

查询（select）

---

查询某个字段的数据：

select 字段名 from 表名

从一个表中拿出某个字段的数据，这些数据是未经过排序未过滤的。

在MySQL命令行中必须以英文分号（；）来结束一个功能语句。在一些可视化ide中可不必使用分号结束。

MySQL对大小写不敏感。在4.1及以前的版本中，标识符是区分大小写的，现在的主流版本中是不区分大小写了

处理sql语句时，会忽略所有的空格。所以sql语句可以分成多行写

查询多个字段的数据：

select 字段名1,字段名2,字段名3 from 表名

使用逗号分隔开，可以查询多个字段的数据

一般来说，很少会直接展示原始数据。会在应用层将原始数据包装一遍。

查询所有字段的数据：

select * from 表名

使用通配符（*）表示所有。

去重（distinct）：

select distinct * from 表名

当不希望查询的数据出现重复时，使用该关键字可以使去除重复的数据。distinct关键字放在select后，字段名前。

当出现多个字段需要去重时，使用distinct会消除所有字段完全相同的行，而不会消除某一个字段相同其余字段不相同的行

分页（limit）：

limit 起始数据,一页显示的数据

一般用在查询语句的最后一行，将查询出来的结果分页，每一页显示多少条数据。

还有一种语法仅作了解limit 4 offset 4从第三行开始，每页取4行数据。

排序（order by）：

可以通过order by 来选中一个列排序，这个列可以是查询的列也可以是非查询的列。

多次排序

在order by后紧跟的多个列使用**逗号，**隔开即可按照顺序进行多次排序。例如

首先按照商品价格排序，当出现价格相同的商品时，按商品名排序。第二次排序仅在价格相同的一组里进行排序（不会排序到价格高或者价格低的前面或后面）

排序如不特别指定顺序，则一般按照顺序（升序）来排序。特别指定顺序ASC（升序）、DESC（降序），当多列排序的时候，ASC或者DESC关键字紧跟着需要特别指定顺序的列名之后

过滤（where和having）：

应用过滤的坏处

• 发送多余数据，浪费带宽
• 循环取数据，性能低

数据过滤（and和or）

在and和or组合中，and优先级高于or，DBMS会优先处理and的两条语句。我们可以使用（）来指定它们的结合顺序

between…and…和in的区别

between and

• 是连续的区间表示一个连续的范围
• 包括边界值

in

• 是一个个孤立的点，括号内可以设多个值。

通配符：

like关键字

like关键字告诉DBMS，后面使用通配符进行搜索。

通配符种类

• %：任意字符出现任意次数（也就是说可以包括0个字符，%通配符不能匹配null）
• _：下划线只匹配一个任意字符

通配符搜索将会导致性能降低，开头统配符是性能最低的

正则表达式在MySQL中的使用：

regexp关键字：

紧跟着regexp关键字后的字符串将会被当作正则表达式。例如 ‘1000’ 就会匹配包含1000文本的行。

正则表达式可以使用关键字binary来使匹配的数据区分大小写。

一些正则表达式的符号：

• |：或者类似于or
• []：匹配中括号内的特定字符，例如[123]匹配的是含有1或者2或者3的行（只会匹配中括号内的一个字符）
• ^（双重作用）：类似于非操作，可以用在中括号内，表示匹配特定字符串以外的字符串[^123]。用作定位符，表示限定在字符串开头进行匹配例如^[123]
• [1-9]：集合，可以用来简化[]中括号匹配多个字符，但作用任然还是匹配一个字符
• .：匹配任意一个字符出现一次
• {}：指定出现的次数，例如{4}a就只匹配包含aaaa的行

匹配特殊字符：

例如需要匹配.或者|这样的字符时，需要使用转义，不然会当作是正则表达式的符号进行处理，使用\\来对这样的特殊字符进行转义

注意的是：正常的正则表达式使用一个\来转义特殊符号，而MySQL使用两个\

使用//引用元字符：

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

字符类：

存在一些预定义好的类，用来匹配常用字符

类	说明
[:alnum:]	任意字母和数字
[:alpha:]	任意字母
[:blank:]	空格和制表符
[:cntrl:]	ASCⅡ控制字符（0到31和127）
[:digit:]	任意数字
[:graph:]	除空格外的任意可打印字符
[:lower:]	任意小写字母
[:print:]	任意可打印字符
[:punct:]	非正常字母和数字，也不在ASCII控制内的任意字符
[:space:]	包括空格在内的任意空白字符
[:upper:]	任意大写字母
[:xdigit:]	任意十六进制数字

匹配多次：

元字符	说明
*	匹配0个或者多个
+	匹配1个或者多个
?	匹配0个或者1个
{n}	匹配n个
{n,}	不少于n个（大于等于n）
{n,m}	匹配数目范围（m不大于255）

这些元字符必须紧跟着它限定出现次数的那个字符

定位符：

元字符	说明
^	文本开始位置
$	文本结尾位置
[[:<:]]	词的开始位置
[[:>:]]	词的结尾位置

总结：正则表达式的作用类似于like，但比like要更灵活

计算字段：

一般来说，可能我们应用需要的数据在数据库中并不存在，需要从数据库中的多个列中计算得出，这就有两种方式进行计算。

1. 在应用程序内进行计算
   • 查询语句比较简单
   • 性能低于在DBMS中计算
2. 在DBMS中计算好数据，再返回到应用程序
   • 查询语句比较复杂
   • 性能比较高

例如需要拼接两个字符串当成一个值返回时，使用字符串拼接函数concat（）select concat(列1,'(',列2,')') from 表

删除空格的函数：RTrim（）删除右空格，LTrim（）删除左空格， Trim（）删除左右两端空格

日期：

Date（）：

如果使用datetime存日期时间，那么使用where去匹配值往往匹配不上，因为还有一个具体的时间没有匹配上。

使用一般会使用Date（）函数来提取日期再进行怕匹配。where Date(datetime格式的列) = 'yyyy-mm-dd'

查找某个时间范围：

where datetime格式的列 between '2005-9-1' and '2005-9-30'

还有一种通用的方法(它可以不用考虑闰年和一个月一共有几天)：

where Year(datetime格式的列) = 2005 and Month(datetime格式的列) = 9

汇总数据（报表）：

AVG（）函数忽略null值

COUNT（）：

• count（*），统计所有行包括null 它的另一种写法是count(1)
• count（列），忽略null

MAX（）忽略null

MIN（）忽略null

联结表

---

select 列1,列2,列3
from 表1,表2
where 表1.列=表2.列

实际上这是一种等值连接，也是内连接。它是实际意义上和inner join是一样的。只是在语法形式上有所不同。where其实就是语法过滤的一种。不使用where进行过滤的话，这种等值连接出来的是将两张表的每一行都匹配另一张表的每一行（也就是笛卡尔积）。

自联结：

当我们需要在同一张表上进行多次查询的时候，一般情况下自联结比子查询的性能要好

外部连接：

在我们实际查表的时候，可能会要查询另一个没有关联的表的数据，这个时候就需要使用到外连接。外部连接分为左外连接和右外连接，具体语法是from 表1 left|right outer join 表2 on

组合查询

---

当需要将多条select语句查询结果合并输出至一个查询结果的时候，可以使用union关键字。它类似于我们数学上的并集，只是简单的将两个select语句的结果合并到一起。union需要放在两个select语句整体之间，union组合的查询语句的查询结果必须包含相同的列、表达式或聚集函数。union默认去重，如果想返回全部数据，可以使用union all。在组合查询中的排序，只需要在最后一条查询语句中使用order by即可，不允许在前面的查询语句中使用order by

全文搜索

---

使用函数match（）指定搜索列（该函数必须和指定的搜索列数据类型相等），against（）指定要搜索的表达式。全文搜索不区分大小写

where match(列名) against('表达式')
select where match(列名) against('表达式') as 别名

全文搜索我们可以通过使用全文搜索索引fulltext来实现，同时也可以使用like来对搜索内容进行匹配。但是使用like进行匹配的时候，结果的顺序可能是无序的，全文索引输出的结果是mysql已经排过一遍顺序的。这个顺序是按照搜索内容的等级来进行排序的。搜索内容的等级是通过内容在全文中的位置确定的，搜索内容在全文中的顺序越靠前，mysql对该行的等级赋值越大，最后输出的顺序该行也就越靠前

全文搜索中的查询扩展

在使用全文搜索中，我们可能还需要对我们搜索的内容进行一个相关性的查找。例如你想搜索番茄的做法，mysql会首先把番茄的做法搜索出来然后，可能还有番茄的营养价值也会搜索出来，这就是查询扩展。

查询扩展的内部实现是，通过全文搜索索引查找出来的行，用这些行包含的内容作为搜索内容在进行全文搜索。包含这些行内容越多的其他行就越靠前，但总是再第一次全文搜索的行以下。

where match(列名) against('表达式' with query expansion)

布尔文本搜索

where match(列名) against('表达式' in boolean mode) 布尔搜索其实就是另一种形式的全文搜索，它在搜索上和全文搜索基本一致，但是布尔全文搜索不需要全文索引也可以使用，但是布尔全文搜索随着数据量的增加，消耗的性能也随着增加。在布尔方式中，输出结果排列而不排序

在没用使用布尔操作符的情况下默认和全文搜索的内容一致。使用了布尔操作符的布尔文本搜索可以更加灵活的保留和去除不需要的内容。

布尔操作符	说明
+	包含某个词，该词必须存在
-	排除某个词，该词必须不出现
>	包含，且增加该词的等级值
<	包含，减少该词的等级值
()	组合一个新的表达式
~	取消一个值的排序值
*	词尾通配符，表示以什么开头
“”	定义一个短语，它弥补了全文搜索只搜索一个词的弊端

对表重命名

---

rename table 旧表名 to 新表名

视图

---

首先视图是一种虚拟的表，它的本质是将sql语句存储起来，使用到这个视图的时候就会去执行这个视图存储的sql语句。因此，如果sql语句连接的表比较多的话，可能造成调用视图的过程性能比较低。

使用视图需要注意的地方：

• 视图名字唯一，不能重复
• 创建视图要有足够的访问权限（要本身就能够访问得到数据）
• 视图可以嵌套，也就是可以使用已经创建好的视图来创建一个新的视图（如果旧视图被删除，不会影响新的视图）
• order by也可以用在视图中，但如果创建视图的sql语句中存在有order by 的话，视图中使用的order by 会被覆盖

-- 创建视图
create view as 
select XXX
show create view 视图名 #查看建立视图的语句（可以看到视图中的数据来源）
drop view 视图名  #删除视图
-- 更新视图有两种方式
-- 1.先删除原来的视图，再创建一个新的视图
drop view 视图名
create view 视图名
-- 2.使用create or replace view
create or replace view 视图名 #如果视图不存在则创建视图，否则更新视图

一个普通的视图内的数据是可以被更新的。更新视图的行，MySQL会去找对应的基表更新但是如果创建视图的时候存在

• group by和having
• 联结
• 子查询
• 并
• 聚集函数
• 去重
• 计算字段

一般来说，我们极少更新视图。视图一般用来做数据检索。

存储过程

---

-- 通过关键字call来调用存储过程
call 存储过程名(@变量1,@变量2...)

-- 创建存储过程
delimiter $$
create procedure 存储过程名([out 参数1 类型,in 参数2 类型...])
begin
	select XXX into 参数1 from XXX;
end $$
delimiter ;

-- 删除存储过程
drop procedure if exists 存储过程名;

-- 输出变量值
select @变量1,传入值...;

-- 查看存储过程
show create procedure ;
show procedure status ;

创建存储过程的时候，在存储过程体中使用的sql语句要使用分号;结尾，但是由于创建存储过程这一整体的sql语句也需要使用;结尾，所以mysql可能会搞不清楚什么时候该是查询语句结束，什么时候是创建存储过程结束。因此，我们使用delimiter关键字来短暂的修改结束符号，将结束符号修改成$$来区分查询和存储过程的结束。最后要将我们修改的结束符号改回;，否则之后使用；结尾的sql语句都不能正常执行

删除存储过程的时候如果存储过程不存在会报错，报错是我们在mysql中不希望看见的，加上if exists当删的除存储过程不存在的时候也不会保存，只是会警告

变量：mysql中的变量是在内存中开辟的一个位置，用来临时存放数据

关键字declare 变量名 变量类型定义局部变量

参数：创建存储过程的参数需要指定是输出参数还是输出参数，关键字out指定输出，in指定输入，inout指定输入输出

游标

---

游标只存在于存储过程中

-- 在存储过程中创建游标
DELIMITER $$
CREATE PROCEDURE test1()
BEGIN
-- 声明局部变量，用来接收游标传递的数据
DECLARE o VARCHAR(15);
-- 声明局部布尔变量，用来后面循环判断
DECLARE done BOOLEAN DEFAULT 0;
-- 声明游标
DECLARE youbiao CURSOR FOR SELECT `contact` FROM `smbms_address`;
-- 声明一个句柄，后面用来循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;	#这里的sqlstate代表状态码
-- 打开游标
OPEN youbiao;
-- 循环开始部分
REPEAT
-- 使用fetch检索游标中的数据，传递给变量。
FETCH youbiao INTO o;	#游标指向列，游标内部有行指针，当不做任何处理的时候，只检索一行数据
-- 处理数据，这里会检查时候和句柄内容相同，相同退出循环，不同则回到repeat继续循环
UNTIL done END REPEAT;
-- 关闭游标
CLOSE youbiao;	#打开和关闭游标都是在存储过程中进行的
-- 当打开游标之后没有明确关闭游标，执行end之后MySQL都会默认关闭游标
END $$
DELIMITER ;

-- 句柄必须定义在游标之后，否则会报错
-- 除了repeat之外，MySQL还允许使用循环语句来循环特定部分

整个使用游标的存储过程中，可以搭配建表语句和调用其他存储过程，插入数据。

触发器

---

触发器是在更新、插入或者删除表数据的时候自动执行的一段代码。、

触发器在同一数据库内，名字唯一

创建触发器：

create trigger 触发器名 after|before insert|update|delete on 表名 for each row
begin
XXX
end
-- after和before表示的是在插入、更新、删除之前触发还是在之后触发
-- after和before表示的是在插入、更新、删除哪一个操作进行的时候触发这个触发器
-- for each row表示任意一行在进行插入、更新、删除操作的时候都会触发这个触发器
-- begin 和 end之间写触发器代码，也就是你希望自动执行的代码，如果这个代码只有一条语句的话，就可以不用写begin和end

如果before触发器触发失败的话，MySQL不会执行我们写的sql语句。如果before触发器或者语句本身执行失败的话，那么after触发器也不会执行

如果需要修改一个触发器，要先删除这个触发器再创建一个新的触发器

drop trigger 触发器名

在触发器中可以使用new.列名来取出将要插入或者更细的值。使用old.列名来读取插入、删除更新的原始数据。要注意是的是new可以在触发器中人为的进行修改，但是old只能读取不能赋值。并且触发器中不支持调用存储过程