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`
