MySQL进阶

[TOC]

前言

• 正则表达式过滤，计算字段，MySQL中的常用函数。
• REGEX
  • 与like的区别，^$实现like的功能
  • BINARY区分大小写
  • 使用|进行OR匹配
  • 匹配几个字符之一使用[]—>匹配范围[1-5]
  • 转义\\，匹配特殊字符

• 计算字段

  • MySQL使用Concat函数实现拼接，大多数DBMS使用+或||进行拼接
  • AS 进行别名更改
  • 执行算术运算
• MySQL中函数
  • 文本处理函数
  • 时间处理函数：Date()处理时间类型
  • 数值处理函数
• 组合聚集函数
  • COUNT()，AVG()，MAX()，MIN()，DISTINCT关键字
• 联结
• 组合查询

  正则表达式过滤

REGEX关键字

regex关键字的使用跟like一样，但却更强大，通用性更高

SELECT prod_name FORM prod_table WHERE prod_name REGEX '1000' OREDER BY prod_name;

输出结果：

注意事项：这语句看起来跟like一模一样，但其实意义全然不同，对于like关键字，它是匹配全文为‘1000’的商品，而regex则表达的是正文为‘1000’的具体的正则表达式。此外将上面SQL语句中的regex改成like，并没有任何返回值，因为like语句需要加上通配符，才会有返回值。

regex想实现like的全文匹配，需要用上^和$两个字符。

MySQL的正则表达式匹配不区分大小，若想要区分大小写，需要加上BINARY关键字

MySQL中匹配符

• ，进行OR匹配

  SELECT prod_name from prod_table WHERE prod_name REGEX '1000|2000';
  

  输出结果：

• […]，匹配中括号内任意字符，还可以写成[0-9]匹配0到9之间任意数字，[a-z]匹配a到z的任意字母

SELECT prod_name from prod_table WHERE prod_name REGEX '[123] ton';

• . ， 匹配任意字符

  SELECT vend_name From vendors Where vend_name REGEX '.';
  

  输出结果：

• \\，MySQL中的转义字符，\\f换页，\\n换行，\\r回车，\\t制表，\\v纵向制表

• 匹配字符类

  

• 匹配多个字符

  

SELECT prod_name FROM prod_table WHERE prod_name REGEX '[[:digit]]{4}';

输出结果：

• 定位元字符

  

计算字段

Concat()函数

Concat函数是MySQL用来实现拼接的函数，使用AS关键字可以给拼接的字段另起名字

SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name;

输出结果：

SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title FROM vendors ORDER BY vend_name;

输出结果：

算术计算

MySQL可以执行简单的算术计算，如加减乘除

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

输出结果：

MySQL中数据处理函数

文本处理函数

时间日期处理函数

数值处理函数

数据库索引

详细参考：https://segmentfault.com/a/1190000015738121

联结

在正常情况下，分解数据为多个表能更有效地存储和更方便的处理，提供更好的伸缩性。但这样会带来这样一种情况，当需要检索的分布在多张表时，该怎么通过一条检索语句从多个表中获取。MySQL中可通过联结机制关联多张表，来获取数据。

内部联结

内部联结，又叫做等值联结，是最常见的一种方式。

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON verdors.vend_id = products.vend_id;

INNER JOIN这种内部联结方式，和使用WHERE语句等价，如下面：

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE verdors.vend_id = products.vend_id;

自联结

表自身与自身相关联。

SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

自然联结

自然联结的作用是排除多次出现，是每个列只返回一次。与内联结的区别，自然联结自动连接所有同名列，内联结提供连接的列。

SELECT A.column, B.column FROM tableA as A NATURAL JOIN tableB as B;

外联结

外连接保留了相关表中没有关联行的行。外连接有左联结LEFT OUTER JOIN，右联结RIGHT OUTER JOIN和全联结OUTER JOIN。

SELECT customers.cust_id, orders.order_name FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

以上是左联结，会保留cust_id存在但是order_name为NULL的行。

组合查询

UNION关键字可以将多个SELECT查询语句的结果集合并到一起。UNION ALL会保留重复的行。

UNION的使用条件：

• UNION必须由两条或两条以上的SELECT语句构成，语句之间使用UNION关键字进行组合。
• UNION中每个查询必须包含相同的列、表达式或聚集函数（次序可以不一致）
• 列数据类型必须兼容

SELECT vend_id, prod_id, prod_price FROM products 
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001, 1002);

• Previous
  Git使用记录