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中使用+或||来表示.

语句:

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相同，只是前者针对分组后，后者针对分组前

子查询

使用子查询时，应要保子查询中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查询基本相同

语句:

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 TRANSACTIONDELETE FROM table1 WHERE id = 1; COMMIT;SELECT * FROM table1;

tips:

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

保存点

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

语句： SAVEPOINT pointOne;

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