MySQL学习笔记
数据库作为数据的存储，在软件系统中占据着不可替代的作用，而数据存储的速度在某种程度上决定了数据交互的速率。本人多次使用MySQL数据库却一直没有去对它进行比较深入的了解，这段时间趁着有时间，把MySQL的知识学习了一下，希望以后能够写些高效一点的SQL语句。
一、基本操作
检索不同的列
select distinct vend_id from products;
限制结果
select distinct vend_id from products limit 5;
limit 3,5 返回从行3开始的5行，第一个数为开始位置，第二个数为要检索的行数。默认一个数检索出来的第一行为行0而不是行1，因此limit 1,1将要检索出第二行而不是第一行。 limit 4 offset 3，从行3开始的4行＝＝limit 3,4。
排序(order by)
select prod_name from products order by prod_name;
降序：desc 升序：asc 关于位置：order by子句应该保证出现在from子句之后，如果使用where的时候，order by需要位于where 之后，如果使用limit，limit必须位于order by之后，否则将产生错误信息.
where子句过滤信息
空值检查：is null/is not null
IN操作符：制定条件范围
select prod_name,prod_price
from products
where vend_id IN (1002,1003);
NOT操作符：否定之后所跟的任何条件
select prod_name,prod_price
from products
where vend_id NOT IN (1002,1003)
order by prod_name;
计算次序
select prod_name,prod_price
from products
where vend_id = 1002 OR vend_id = 1003 And prod_price >= 10;
结果发现返回的结果中🈶出现小于10美元的数据。原因是SQL(像多数语言一样)在处理OR操作符前，优先处理AND操作符。上述SQL语句应该理解为供应商1003制造的任何价格为10美元或以上的产品，或者是由供应商1002制造的任何产品，而不管价格如何。
二、匹配搜索
Like操作符####
由字面值、通配符或两者组合构成搜索条件。
select prod_id,prod_name
from products
where prod_name Like '%anvil%';
注意：
- 搜索区分大小写
- 注意去掉为空格，否则会出现匹配不正确
-
%可以匹配任何东西，但NULL除外
where prod_name like ‘%’; //不能匹配值为NULL的长产品行。
下划线(_)操作符####
下划线的用途和%类似，但下划线只能匹配单个字符，不能多也不能少，而不是任意个字符。
使用通配符的技巧####
- 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
- 在确定需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
正则表达式REGEXP匹配
以下例子检索列包含prod_name包含文本1000的所有行。
select prod_name
from products
where prod_name REGEXP '1000'
order by prod_name;
注意：
- ’.’匹配任意一个字符
-
匹配不区分大小写,为了区分大小写，可以使用BINARY关键字
where prod_name REGEXP BINARY 'JetPack .000';
OR匹配
‘|’相当于OR条件，例如：‘1000｜2000｜3000’
匹配几个字符之一
使用’[ ]’匹配几个字符之一，［123］为［1｜2｜3］的缩写。
匹配范围
使用［0-9］代替［0123456789］，［a-z］任意字母字符
匹配特殊字符
需要匹配’.’等特殊字符的时候需要使用’\.’进行匹配。包括’.’、’|’、’[‘、’]’、’'都需要同样处理。
| 元字符 | 说明 |
|---|---|
| \\f | 换页 |
| \\n | 换行 |
| \\r | 回车 |
| \\t | 制表 |
| \\v | 纵向制表 |
匹配字符类
| 类 | 说明 |
|---|---|
| [:alnum:] | 任意字母和数字（同[a-zA-Z0-9]） |
| [:alpha:] | 任意字符（同[a-zA-Z]） |
| [:blank:] | 空格和制表符同（[\\t]） |
| [:cntrl:] | ASCII控制字符（ASCII0到31和127） |
| [:digit:] | 任意数字（同[0-9]） |
| [:graph:] | 与[:print:]相同，但不包括空格） |
| [:lower:] | 任意消小写字母（同[a-z]） |
| [:print:] | 任意可打印字符 |
| [:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 |
| [:space:] | 包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]） |
| [:upper:] | 任意大写字母（同[A-Z]） |
| [:xdigit:] | 任意十六进制数字（同[a-fA－F0-9]） |
定位符
| 元字符 | 说明 |
|---|---|
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| [ [:<:] ] | 词的开始 |
| [ [:>:] ] | 词的结尾 |
注意：
- ^的双重用途，在集合中[ ] ,用来否定该集合，否则，用来指串的开始。
- REGEXP总是返回0（没有匹配）或者1（匹配）
匹配多个实例
| 元字符 | 说明 |
|---|---|
| * | 0个或多个匹配 |
| + | 1个或多个匹配（等于{1,}） |
| ? | 0个或1个匹配（等于{0,1}） |
| {n} | 指定数目的匹配 |
| {n,} | 不少于指定数目的匹配 |
| {n,m} | 匹配数目的范围（m不超过255） |
三、数据处理函数
函数没有SQL的可移植性强，能使用其他方式实现最好不使用函数，使用函数的使用，应该保证做好代码注释。
文本处理函数
| 函数 | 说明 |
|---|---|
| Left(str,length) | 返回串左边的字符 |
| Length() | 返回串的长度 |
| Locate(substr,str,pos) | 找出串的一个子串a在b中的位置，不存在返回0，第一个位置为1 |
| Lower() | 将串转换为小写 |
| LTrim() | 去掉串左边的空格 |
| Right(str,length) | 返回串右边的字符 |
| RTrim() | 去掉串右边的空格 |
| Soundex() | 返回串的SOUNDEX值 |
| SubString(str,pos,length) | 返回子串的字符,length可以省略 |
| Upper() | 将串转换为大写 |
soundex()的使用例子： 可以搜到鱼Y Lie同音的Y Lee
select cust_name,cust_contact
from customers
where Soundex(cust_contact) = Soundex('Y Lie');
日期和时间处理函数
| 函数 | 说明 |
|---|---|
| AddDate() | 增加一个日期(天,周等) |
| AddTime() | 增加一个时间(时,分等) |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date_Add(date,INTERVAL expr type) | 高度灵活的日期运算函数 |
| Date_Format(date,format) | 返回一个格式化的日期或时间串 |
| Day() | 返回一个日期的天数部分 |
| DayOfWeek() | 对于一个日期，返回对应的星期几,1=星期天 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Month() | 返回一个日期的月份部分 |
| Now() | 返回当前的时间日期 |
| Second() | 返回时间的秒部分 |
| Time() | 返回一个日期的时间部分 |
| Year() | 返回一个日期的年份部分 |
注意：
日期date的数据类型如果为datetime，这时候如果存储的值为2016-03－15 16:30:05则where order_date ='2016-03-15'会出现检索失败。可以使用日期函数，或者使用范围替代。
数值处理函数
| 函数 | 说明 |
|---|---|
| Abs() | 返回一个数的绝对值 |
| Floor(x) | 返回小于或等于x的最大整数 |
| Ceil(x) | 返回大雨或等于x的最小整数 |
| Round(x) | 返回离x最近的整数 |
| Round(x,y) | 保留x小数点后y位的值，但截断时要进行四舍五入 |
| Cos() | 返回一个角度的余弦 |
| Exp(x) | 返回e的x次方 |
| Pow(x,y) | 返回x的y次方,y可以为负数 |
| Mod(x,y) | 返回x除以y后的余数 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sin() | 返回一个角度的正弦 |
| Sqrt() | 返回一个数的平方根 |
| Tan() | 返回一个角度的正切 |
四、分组与汇总
聚集函数
Avg()、Count()、Max()、Min()、Sum()
Avg()只能用来确定特定数值列的平均值，直接忽略NULL值的行
其他几个函数也忽略NULL值
注意:
ALL为默认的行为，如果只包含不同的值使用DISTINCT参数Avg(DISTINCT price)，不能使用Count(DISTINCT).
分组
select vend_id,count(*) as num_prods
from products
group by vend_id;
注意
- 如果分组中有NULL值，则NULL将作为一个分组返回。
- Group By子句必须出现在Where子句之后，Order By子句之前。
- Group By只能使用选择列或表达式列，而且必须使用每个选择列表达式。
-
过滤子句使用Having，而不用where，where过滤基于特定行，having基于分组。
select vend_id,Count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;
五、子查询
待续。。。