mysql进阶语法
Search
mysql进阶语法

mysql进阶语法

Date
Oct 21, 2014
Tags
Mysql
Database

导语

在上一篇《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中使用+或||来表示.
 
语句:
notion image
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()：返回秒部分
  • FROM_UNIXTIME()时间戳转日期
  • FROM_TIMESTAMP()日期转时间戳

聚合函数

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之前
  • 分组后过滤只能用HAVINGHAVING用法与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之后)
  • 回退用来处理INSERTUPDATE和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之后保存点会自动释放掉
 

Loading Comments...