排序
逆序 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';
正则表达式资料
计算字段
拼接字段=>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
网友评论