- select语句的基本结构
select col1,col2
from tb_name
where col1='' --where为行级筛选
group by col2 --group by根据某个字段进行分组
having count(col1)>1 --having对分组后的数据进行组级筛选
order by col1 desc --order by根据某个字段进行排序 asc升序，desc降序
limit 3,5 --limit设置查询行的范围
- 行级筛选数据--where
# where字句的操作符：
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between A and B 指定的两个值之间
where col is null --查询空值
where col is not null --查询非空值
where col = '' --查询空字符串
where (条件1 and 条件2) or (条件3 and 条件4)
--任何时候使用具有and 和 or操作符的where字句时，都应该用圆括号明确的分组操作符，
不要过分依赖默认计算次序
where col in (a,b)--in/not in操作符与or是相同的功能，但比or检索速度更快
where col in (select ....) --用in包含其他select语句，实现子查询的嵌套
模糊匹配查询：
1.用like操作符+通配符
where col like '%A%' --'%'可以拼配0个或1个以上的字符，但不能匹配null
'_'只能匹配一个字符
注意不要过度使用通配符，会降低检索性能
2.用正则表达式：
where col REGEXP '.000' --'.'标识任意匹配1个字符
where col REGEXP '100|200|300' --'|'表示or
where col REGEXP '[123]ton' --[]表示另一种形式的or语句，[123]是[1|2|3]的缩写形式
where col REGEXP '[1-9]ton' --[1-9]表示匹配1-9中任意一个数字
where col REGEXP '\.' --\来匹配特殊字符，也用来引用元字符
\f(换页),\n(换行),\r(回车)
where col REGEXP '\([0-9]stick?\)' --'?'匹配0个或1个字符
'*'--0个或多个匹配，'+'--1个或多个匹配
where col REGEXP ’[[:digit:]]{4}’ --表示连在一起的任意4位数字
{n}--指定数目的匹配 {n,}--不少于指定数目的匹配
{n,m}--指定匹配数目的范围
- MySQL中的常用函数
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
<窗口函数>的位置，可以放以下两种函数：
1） 专用窗口函数，包括rank（并列时，占用下个的名次）, dense_rank（并列时只占用一个名次）, row_number（没有并列）等专用窗口函数。
2） 聚合函数，如sum. avg, count, max, min等，对截止到使用这个函数的那一行及以上操作
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
具体见：
一个select语句外面直接再加一个select即可空返回替换为null
distinct() --去重
if(判别式，值1，值2）--为真取1，假取2
ifnull(数据，被替换的值)--去除null
聚合函数：
count() --计数
sum() --汇总计算
avg() --平均值
max() --最大值
min() --最小值
std() --标准差
abs() --绝对值
rand() --随机数
sqrt() --平方根
round() --保留几位小数
数据处理函数：
substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔：
(1).当n大于0时取第n个分隔符(n从1开始)之后的全部内容；
(2).当n小于0时取倒数第n个分隔符(n从-1开始)之前的全部内容；
format（数字，'$999,999'）--格式化数字
concat() --拼接两个字段列group_concat,concat_ws
trim()/ltrim()/rtrim() --去除两边空格/左边空格/右边空格
upper()/lower() --转换字母大小写
left()/right() --返回左右的字符串
locate() --返回中间范围的字符串
length() --返回字符串的长度
substring() --返回字串的字符
substr（截取的字符串，起始下标，长度）
日期时间处理函数：
在Mysql中，日期格式必须为'yyyy-mm-dd'
addDate() --增加一个日期
addTime() --增加一个时间
curDate() --返回当前日期
curDate() --返回当前时间
Date() --返回日期部分
Date_format(date,'%Y-%m-%d') --返回一个格式化的日期或时间串
str_to_date('字符'，'日期格式')--字符转date类型
DateDiff() --计算两个日期之差
Now() --返回当前的日期时间
# 有关聚合函数使用注意：
avg()只能用来确定特定数值列的平均值，并忽略列值为null的行
count(*) 对表中所有行计数，不管是null还是非null，count(col)会忽略null值
min()/max()/sum()都忽略null值，sum()在括号里可以添加合计，例如sum(col1*col2)
函数之间可以嵌套使用，例如count(distinct(col))是计数去重后的结果
日期函数
DATE_ADD()
函数向日期添加指定的时间间隔。
DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。
select DAYOFWEEK('1998-02-03')
-> 3
WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
select WEEKDAY('1997-10-04 22:23:00');
-> 5
DAYOFMONTH(date)
返回date的月份中日期，在1到31范围内。
mysql> select DAYOFMONTH('1998-02-03');
-> 3
DAYOFYEAR(date)
返回date在一年中的日数, 在1到366范围内。
mysql> select DAYOFYEAR('1998-02-03');
-> 34
MONTH(date)
返回date的月份，范围1到12。
mysql> select MONTH('1998-02-03');
-> 2
DAYNAME(date)
返回date的星期名字。
mysql> select DAYNAME("1998-02-05");
-> 'Thursday'
MONTHNAME(date)
返回date的月份名字。
mysql> select MONTHNAME("1998-02-05");
-> 'February'
QUARTER(date)
返回date一年中的季度，范围1到4。
mysql> select QUARTER('98-04-01');
-> 2
WEEK(date)
对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。
mysql> select WEEK('1998-02-20');
-> 7
WEEK(date,first)
2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，
如果第二个参数是1，从星期一开始。
mysql> select WEEK('1998-02-20',0);
-> 7
mysql> select WEEK('1998-02-20',1);
-> 8
YEAR(date)
返回date的年份，范围在1000到9999。
mysql> select YEAR('98-02-03');
-> 1998
HOUR(time)
返回time的小时，范围是0到23。
mysql> select HOUR('10:05:03');
-> 10
MINUTE(time)
返回time的分钟，范围是0到59。
mysql> select MINUTE('98-02-03 10:05:03');
-> 5
SECOND(time)
回来time的秒数，范围是0到59。
mysql> select SECOND('10:05:03');
-> 3
PERIOD_ADD(P,N)
增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。
mysql> select PERIOD_ADD(9801,2);
-> 199803
PERIOD_DIFF(P1,P2)
返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。
mysql> select PERIOD_DIFF(9802,199703);
-> 11
- 分组筛选：
select col1,count(*) as c
from tb_name
group by col1 with rollup --使用with rollup可得到每组的汇总值
having count(*)>1 --having中的条件字段不能使用别名
# 如果在select中使用了表达式，则在group by中要使用相同的表达式，不能使用别名
如果分组中有null值，则将null值单独分为一组
group by必须出现where字句之后，order by之前
- 使用子查询
1.使用子查询进行过滤：
select * from tb_name where col in (1,2,3)
2.作为计算字段的子查询
select a.col1,
a.col2,
(select count(*)
from a
where a.id = b.id) as count
from a
order by a.col1
3.相关子查询：
delete from tb --删除表中的重复记录
where id in(
select id from(
select id from tb a
group by a.id
having count(id)>1) as b
)
- 表联接：
自联接：
select col1,col2
from tb
where col3 = (select col3
from tb
where col1 = 'SA')
生成笛卡尔积表-cross join
select a.col1,b.col2
from a
cross join b
内联接--inner join:
select a.col1,b.col2
from a
inner join b
on a.id = b.id
left join/right join(左联接/右联接）
select a.col1,b.col2
from a
left/right join b
on a.id = b.id
外连接--full outer join
select a.col1,b.col2
from a
full outer join b
on a.id = b.id
延伸用法：left join excluding inner join
返回左表有但右表没有关联数据的记录集。
select a.col1,b.col2
from a
left join b
on a.id = b.id
where b.id is null
right join excluding inner join
返回右表有但左表没有关联数据的记录集。
select a.col1,b.col2
from a
right join b
on a.id = b.id
where a.id is null
full outer join excluding inner join
返回左表和右表里没有相互关联的记录集。
select a.col1,b.col2
from a
full outer join b
on a.id = b.id
where a.id is null or b.id is null
7.组合查询：
select * from a
union
select * from b
# union中的每个查询必须包含相同的列，表达式及聚合函数
union会去除两个查询中的重复值，如果不要去重，用union all
只能使用一条order by字句，且必须放在最后一个查询后面
8.创建视图：
# 视图是一张虚拟的表，不包含数据，可以简化复杂的sql操作
视图必须唯一命名，且需要访问权限
视图可以嵌套，可以和表一起使用，但不能有索引和创建触发器或默认值
create view view_name as
select col1,
col2,
col3*col4 as col5
from tb_name
select * from view_name where col1=''
- 创建存储过程：
# 存储过程可以简化复杂操作，提高检索性能
--创建存储过程
Delimiter $
create procedure ordertotal(
IN number INT, --IN将number传入存储过程
OUT total DECIMIAL(8,2) --OUT将total返回合计
)
Begin
select sum(price*quantity)
from orderitems
where order_num = number
INTO total
END $
--调用存储过程：
CALL ordertotal(200, @total)
select @total
