《mysql》必知必会读书笔记

作者: 自度君 | 来源:发表于2018-08-17 21:38 被阅读0次

排序

逆序 DESC

通配符进行过滤

like操作符

百分号（%）通配符

%表示任何字符出现的任何次数

SELECT title FROM fk_post WHERE markdown_content LIKE '%java%';

除了一个或多个字符外，%还能匹配0个字符，%代表给定位置0个，1个或多个字符，因此在搜索时要注意尾部空格的干扰，如果尾部有空格，%java将匹配不到它，解决方法就是在搜索模式中附加一个%，更好的方式就是使用函数去除空格。此外就是%无法匹配NULL。

下划线（_）通配符

下划线（）的用途和%一样，但是下划线只能匹配单个字符。与‘%’不同的是，‘’只能匹配单个字符，不能匹配0个字符。

通配符的总结

不要过多的使用通配符，因为通配符搜索的处理要比其他的搜索花的时间更长
不要把通配符用在搜索模式的开始处，因为会影响性能。

正则表达式进行搜索

REGEXP与like的区别

like匹配的是整个行而regexp可以在列值内进行匹配，如果被匹配的文本在列值内被找到，将返回整个行。
匹配不区分大小写，如果需要区分大小写可以使用BINARY关键字。

SELECT title FROM fk_post WHERE markdown_content REGEXP BINARY 'java';

正则表达式资料

正则表达式30分钟入门教程

计算字段

拼接字段=>Concat()函数

Concat()函数把多个串连接起来形成一个新的串，参数间以,分离。

SELECT Concat(title,'(',id,')') FROM   fk_post ORDER BY title;

上一节曾提到删除数据右侧多余的空格来处理数据，可以使用mysql中的Rtrim()函数处理。

SELECT Concat(Rtrim(title), '(', id, ')') FROM fk_post ORDER BY title;

同样的，MYSQL还支持trim()，Ltrim()分别去掉所有空格和去掉左侧空格。

使用别名=>AS关键字（alias）

前文中的新字段没有名字，需要给他取一个新名字，别名（alias）是一个字段或者值得替换值，别名用AS关键字赋予。

SELECT Concat(Rtrim(title), '(', id, ')') AS new_title FROM fk_post ORDER BY title;

别名还有其他用途，常见的有在实际的表列包含不符合规定的字符如空格时重命名他，在原来名字含混或容易误解时扩充他。

执行算数计算

mysql支持简单的算数计算，如'+,-,*,/'等。

使用函数处理数据

Mysql内置了部分函数来处理数据，但相比SQL语句，函数的可移植性较差，不同的DBMS函数的含义也不尽相同，因此，采用建树的部分应当做好注释

函数类型

处理文本串
用在数值上进行数字操作
处理日期和时间
返回此时DBMS正使用的特殊信息（如用户登录信息，版本信息）、

文本处理函数

上文提到的Trim(),RTrim(),LTrim(),
Upper()=>将文本都转为大写
Lower()=>将文本转为小写
Left()返回左边的字符串
Right()返回右侧字符串
Length()返回字符长度
SubSTring()返回子串的字符

日期和时间处理函数

常用于WHERE语句过滤数据，标准化日期
Date()=>返回时间与日期部分
Year()=>返回一个日期的年数部分
Mouth()=>返回一个日期的月份部分
Time()返回一个日期的时间部分
DayOfWeek()=>返回一个日期对应星期几
Day()=>返回返回一个日期的天数部分
Hour()=>返回日期的小时部分
Minute()=>返回日期的分钟部分
Second()=>返回一个日期的秒部分

CurDate()=>返回当前日期
CurTime()=>返回当前时间
DateDiff()=>计算两个日期之差
Date_add()
Date_format()=>返回一个格式化的时间与日期
AddDate()=>增加一个日期(天，周)
AddTime()=>增加一个日期（时分）

数值处理函数

Abs()=>返回绝对值
Exp()=>返回一个数的指数值
Mod()=>返回一个数的余数
Ramd()=>生成随机数

汇总数据

聚集函数

我们需要把数据汇总出来而不必实际检索出来，具体例子有:

确定表的行数（或满足特定条件的行数）
获得表中行数的和
找出行（列）的特定值（最小，最大，平均）
AVG()=>获取某列的平均值

COUNT()=>获取某列的行数

如果是Count(*)择包含值为Null的值，如果Count(某列)，则不包含NULL的值

MAX()=>返回某列的最大值

一般用来找出最大的日期或者数字，但如果是文本，则返回最后一行。

MIN()=>返回某列的最小值

与Max类似，但如果是文本时，与Max相反，返回第一行

SUM()=>返回某列的和

聚集不同值（distinct参数）

聚集函数的默认值 => All，不指定默认为all
若要包含不同的值，需指定distinct参数

SELECT AVG(DISTINCT post_id) FROM fk_post_tag WHERE `tag_id` =4 ;

组合聚合函数

SELECT 可以包含多个聚集函数

分组数据

现有的汇总函数只能匹配特定的WHERE子句上进行的，如果想要返回所有汇总数据，怎么办？这时就要使用数据分组，分组允许把数据分为多个逻辑组以便能对每个组进行逻辑计算。

创建分组

GROUP BY子句可以包含多个列，这样做使得分组可以嵌套
除聚集函数之外，所有SELECT的列都要在GROUP BY中给出
GROUP BY在WHERE之后，ORDER BY之前

SELECT id, COUNT(*) AS num_post FROM fk_post WHERE type =0 GROUP BY id;

使用WITH ROLLUP每个分组的汇总级别以及值，但在使用了WITH ROOLUP关键字，便不能使用ORDER BY进行排序

SELECT id, COUNT(*) AS num_post FROM fk_post WHERE type =0 GROUP BY id WITH ROLLUP;

过滤分组

当我们需要针对分组进行过滤时，使用WHERE就无法满足，因为WHERE过滤的是行而不是分组，因此我们需要用到HAVING进行分组。
HAVING支持所有WHERE子句的条件，区别就是WHERE在分组前进行过滤，HAVING在分组后进行过滤

SELECT id, COUNT(*) AS num_post FROM fk_post HAVING num_pos >1 GROUP BY id WITH ROLLUP;

SQL子句顺序

SELECT=>FROM=>WHERE=>GROUP BY=>HAVING=>ORDER BY=>LIMIT

子查询

使用子查询

子查询就是嵌套在查询里的查询，子查询一般和IN操作符结合使用。

SELECT id,title FROM fk_post WHERE id IN
(SELECT `post_id` FROM fk_post_cate WHERE `cate_id` IN 
(SELECT id FROM fk_cate WHERE name='PHP'));

子查询也可用于测试（-，<>）

计算字段的子查询

SELECT id,name,
(SELECT COUNT(*) FROM `fk_post_cate` WHERE `fk_post_cate`.cate_id = `fk_cate`.id) AS cate_num 
FROM `fk_cate` ORDER BY id;

注意字段前要加表名，子查询也并不是最高效的检索方法，下面就要介绍另一种高效方法->联结（join）

联结表（join）

联结是一种机制，用来在一条SELECT中关联表，因此如果数据在多个表中，又要用一条语句查出，就只能用联结。
在SELECT联结表时，相应关系是在运行时进行构造的，在联结两个表时，是将第一个表的每一行与第二个表中的每一行进行配对。

SELECT cate_id,post_id,name FROM `fk_cate`, `fk_post_cate` WHERE `fk_cate`.id = `fk_post_cate`.cate_id;

内部联结

上文中用到的联结都是等值联结，就是基于了两个表相等的联结。这种连接也被称为内部联结。这种连接可以利用INNER JOIN关键字来表名联结类型。

SELECT cate_id,post_id,name FROM `fk_cate`INNER JOIN`fk_post_cate` ON `fk_cate`.id = `fk_post_cate`.cate_id;

联结多个表

SELECT cate_id,post_id,title,name FROM `fk_cate`
INNER JOIN`fk_post_cate` 
INNER JOIN `fk_post` 
ON `fk_cate`.id = `fk_post_cate`.cate_id 
AND `fk_post_cate`.`post_id`= `fk_post`.id

上文中的子查询也可转化为联结查询

自联结

使用子查询查同表

SELECT title FROM `fk_post` WHERE user_id =(SELECT user_id FROM fk_post WHERE type =1);

同样的转为自联结查询

SELECT p1.title,p1.id FROM fk_post AS p1, fk_post AS p2 WHERE p1.type = p2.type AND p2.title='前后端分离';

自联结的处理要快过子查询

自然联结

外部联结

有时需要包含没有进行关联的行，这种联结叫做外部联结
在使用Outer join语句时，必须用left和right关键字来指定包含所有行的表。

组合查询

union关键字，连接两条查询语句
union all结果显示重复行，where语句无法完成，必须用union all关键字
union查询智能有一条order by，且必须在最后一行

全文本搜索

myISAM引擎支持，innodb引擎不支持

使用全文本搜索，必须要索引被搜索的列，而且要随着数据的改变不断重新索引。在对表列进行适当的设计后，mysql会自动进行所有索引和重新索引。

索引后SELECT可以与Match与against一起用使用执行搜索
全文搜索一般在创建表时候启用。Create Table语句接受full text字句

CREATE TABLE `fl_post` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `type` tinyint(11) NOT NULL DEFAULT '0' COMMENT '0 为文章，1 为页面',
  `status` tinyint(11) NOT NULL DEFAULT '0' COMMENT '0 为草稿，1 为待审核，2 为已拒绝，3 为已经发布',
  `title` varchar(255) NOT NULL,
  `pathname` varchar(255) NOT NULL DEFAULT '' COMMENT 'URL 的 pathname',
  `summary` longtext NOT NULL COMMENT '摘要',
  `markdown_content` longtext NOT NULL,
  `content` longtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `allow_comment` tinyint(11) NOT NULL DEFAULT '1' COMMENT '1 为允许， 0 为不允许',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime NOT NULL,
  `is_public` tinyint(11) NOT NULL DEFAULT '1' COMMENT '1 为公开，0 为不公开',
  `comment_num` int(11) NOT NULL DEFAULT '0',
  `options` text COMMENT '一些选项，JSON 结构',
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  FULLTEXT(title)
)

导入数据时不要加全文索引，应该先导入数据，再添加索引

全文索引的函数

Match()
Against()

SELECT  * FROM `fk_post` WHERE Match(title) Against('java')

使用扩展查询

进行两次搜索，第一次返回查询结果，第二次返回Mysql认为有用的结果

SELECT  * FROM `fk_post` WHERE Match(title) Against('java' WITH QUERY EXPRESSION)

布尔文本搜索

布尔搜索在没有定义FullText时也能使用，但是会影响性能。
可以提供

要查询的词
要排斥的词（不返回的词）
排泄提示（优先级）
表达式分组

SELECT  * FROM `fk_post` WHERE Match(title) Against('java' In Boolean Mode)

- 包含必须存在
- 排除
包含且增加等级
<包含且减少等级
~取消排序
*通配符

插入数据

插入完整的一行
插入行的一部分
插入多行
插入查询结果

INSERT INTO tableName VALUES ();

必须给出每列，如想留空需指定一个NULL值。
如果想省略某些列需满足：

该列允许为NULL
该列有默认值

插入多行

INSERT INTO tableName VALUES (),();

Update

UPDATE tableName SET column='valuee' WHERE statement;

delete

可以删除行，但不能删除表

TRUNCATE

删除表并重建表

创建表

CREATE TABLE oreders IF NO EXSIT(
order_id int NOT NULL AUTO_INCREMENT,
price decimal(8,2) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (order_id)
)ENGINE=InnoDB;

数据引擎

innoDB
事务处理引擎，不支持全文搜索
MEMORY
储存在内存，非常快
MyISAM
性能高，全文搜索，但不支持事务

更新表

ALTER TABLE

ALTER TABLE fk_post
ADD auther char(20);

常用于定义外键

ALTER TABLE products
ADD CONSTRAINT fk_post_vendor
FOREIGN KEY (vend_id) REFERENCE venders (vender_id)

视图

视图只包含动态使用检索数据的查询，并不是真的包含数据
主要用于

简化复杂的联结
重新格式化检索出的数据
过滤率不想要的数据

存储过程

CREATE PROCEDUCE productpricing()
BEGIN
        SELECT Avg(comment_num) AS priceavg FROM  products;
END