Mysql-进阶语法

导语

在上一篇《MySQL的基本语法》中我们了解了mysql基本的增删改查的语句。本篇我们将进一步对“查”做深入的了解。

REGEXP

mysql的where子句也支持正则匹配

[](OR)

[]用来表示OR匹配

用法:

SELECT * FROM table WHERE name REGEXP '[34]tmp;'检索name中含有3tmp或者4tmp的列。

tips:

要是连续的字符[012345679]或[abcdefg]可以用[0-9]或[a-g]来表示

可以用.来表示任意字符

由于正则匹配都是特殊字符,所以匹配特殊字符时需要转义,而Mysql的转义必须用“\\”,如“\\.”表示匹配.这个字符。

元字符

重复元字符:

用于匹配元字符前面的任何字符的次数

*：0个或多个匹配
+：1个或多个匹配(同{1,})
?：0个或1个匹配(同{0,1})
{n}：指定数目匹配
{n,}：不少于指定数目的匹配
{n,m}：匹配数目的范围(MAX(m)==255)

语句：
SELECT * FROM table WHERE name REGEXP 'hello?'匹配含有字符hello或helloh这类的行。

定位元字符:

限定匹配字符串的位置

^：文本的开始
$：文本的结尾
[[:<:]]：词的开始
[[:>:]]：词的开始

Contact

Mysql的Contact用来表示拼接字符串,在其他SQL中使用+或||来表示.

语句:
[图片上传失败...(image-340ce3-1525193389477)]

tips:

用AS来取别名

算术操作符

Mysql也支持字段间用算术操作符来计算

+：加
-：减
*：乘
\：除

基本函数

文本处理函数

Left()：返回串左边的字符
Length()：返回字符串长度
Locate()：找出一个子串
Lower()：字符串转换为小写
LTrim()：去掉左边的空格
Right()：返回字符串右边的字符
RTrim()：去掉字符串右边的空格
SubString()：返回子串
Upper()：字符串转换为大写

日期和时间处理函数

CurDate()：返回当前日期
CurTime()：返回当前时间
Date()：返回日期部分
DateDiff()：计算两个日期之差
Date_Format()：返回一个格式化的日期或时间串
DateOfWeek()：返回对应的星期
Now()：返回当前日期和时间
Year()：返回年份部分
Month()：返回月份部分
Day()：返回天数部分
Hour()：返回小时部分
Minute()：返回分钟部分
Second()：返回秒部分

聚合函数

AVG()函数

对表中选中的行数计算其平均值

语句:
SELECT AVG(price) FROM table;

tips: AVG()会忽略NULL值得行

COUNT函数

计算行数

语句:
SELECT COUNT(*) FROM table;

MAX()函数

返回指定列中的最大值

语句:
SELECT MAX(price) FROM table;

MIN()函数

返回指定列中的最小值

语句:
SELECT MIN(price) FROM table;

SUM()函数

返回指定列的和

语句:
SELECT SUM(price) FROM table;

tips:

这些聚合函数要想计算不同值可加上DISTINCT :AVG(DISTINCT price)

GROUP BY

通常分组是和聚合函数搭配使用

语句：
SELECT id,COUNT(*) FROM table GROUP BY id以id来分组计算每个id的个数

tips:

GROUP BY 子句中不能是聚合函数

SELECT语句中每个列(除聚合函数外)必须在GROUP BY子句中出现

NULL值将作为一个分组返回

WHERE之后，ORDER BY之前

分组后过滤只能用HAVING,HAVING用法与WHERE相同，只是having针对组级过滤，而where是行级过滤,where过滤掉的数据不会出现分组中

子查询

使用子查询时，应要确保子查询中SELECT返回的列与WHERE子句的列数相同

语句:

SELECT id FROM table WHERE name IN (
 SELECT name FROM table WHERE pid = 5);

联结表(JOIN)

通常我们会把数据有效分散在多个表中，要把我们统一起来查询就要用到联结(JOIN)
用联结表示，要注意列名必须使用完全限定列名(列名前跟上表面),以避免歧义

INNER JOIN

内部联结只会返回关联的行

语句:
SELECT table1.id,table2.name FROM table1 INNER JOIN table2 ON table1.id = table2.id;

等同于

SELECT table1.id,table2.name FROM table1,table2 WHERE table1.id = table2.id

OUTER JOIN

外部联结必须要用RIGHT OUTER JOIN或LEFT OUTER JOIN来指定包括的所有行的表(RIGHT指定OUTER JOIN右边的表,LEFT反之)

UNION

与多WHERE查询基本相同

语句:
[图片上传失败...(image-a0b9e6-1525193389477)]

tips:

UNION中的每个查询必须包含相同的列、表达式

UNION会自动去除重复的行,若想返回全部的行,可以用UNION ALL

事务

事务主要用于保护数据表的完整性

事务开始

START TRANSACTION;

事务回退

ROLLBACK

例子:

SELECT * FROM table1;
START TRANSACTION;
DELECT FROM table1;
SELECT * FROM table1;
ROLLBACK;
SELECT * FROM table1;

tips:

ROLLBACK只能在一个事务处理内使用(一个START TRANSACTION之后)

回退用来处理INSERT,UPDATE和DELETE

事务提交

在事务中数据库是不自动提交保存
事务外的更新可以设置SET autocommit = 0来关掉自动提交

例子:

SELECT * FROM table1;
START TRANSACTION
DELETE FROM table1 WHERE id = 1; 
COMMIT;
SELECT * FROM table1;

tips:

假设事务提交是多表时,只有多表语句均正确才会提交,有一个语句错误都会自动撤销,不会提交。

保存点

在复杂的事务流程中,事务是被分成多步骤,进行部分保存。方便回退到某个节点

语句： SAVEPOINT pointOne;

回退保存点:ROLLBACK TO pointOne
在执行COMMIT或者ROLLBACK之后保存点会自动释放掉

索引

加快查询速度

创建索引

用法:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON `table`(col1,···)

tips:

UNIQUE -- 唯一索引，FULLTEXT -- 全文索引, SPATIAL -- 空间索引，不指定的话为普通索引
index_type 为索引方式，有BTREE和HASH两种。但是InnoDB只使用BTREE
复合索引时，column用逗号，隔开

删除索引

用法：

ALTER TABLE `table`
DROP INDEX index_name;

tips:

修改索引的话为先delete再create

查看索引

用法：

SHOW INDEX FROM [db_name.]`table_name`

Mysql——进阶语法