mysql查询用什么算法_MySQL查询用法总结

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设置查询行的范围

2. 行级筛选数据–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}--指定匹配数目的范围

3. MySQL中的常用函数

distinct() --去重

聚合函数：

count() --计数

sum() --汇总计算

avg() --平均值

max() --最大值

min() --最小值

std() --标准差

abs() --绝对值

rand() --随机数

sqrt() --平方根

round() --保留几位小数

数据处理函数：

concat() --拼接两个字段列

trim()/ltrim()/rtrim() --去除两边空格/左边空格/右边空格

upper()/lower() --转换字母大小写

left()/right() --返回左右的字符串

locate() --返回中间范围的字符串

length() --返回字符串的长度

substring() --返回字串的字符

日期时间处理函数：

在Mysql中，日期格式必须为'yyyy-mm-dd'

addDate() --增加一个日期

addTime() --增加一个时间

curDate() --返回当前日期

curDate() --返回当前时间

Date() --返回日期部分

Date_format(date,'%Y-%m-%d') --返回一个格式化的日期或时间串

DateDiff() --计算两个日期之差

Date_Add(date,INTERVAL 2 day/month/year) --日期运算函数

Year()/Month()/Day()/DayofWeek()/HOur()/Minute()/Second()

Time() --返回时间部分

Now() --返回当前的日期时间

# 有关聚合函数使用注意：

avg()只能用来确定特定数值列的平均值，并忽略列值为null的行

count(*) 对表中所有行计数，不管是null还是非null，count(col)会忽略null值

min()/max()/sum()都忽略null值，sum()在括号里可以添加合计，例如sum(col1*col2)

函数之间可以嵌套使用，例如count(distinct(col))是计数去重后的结果

4. 分组筛选：

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之前

5. 使用子查询

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

)

6. 表联接：

自联接：

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=''

9. 创建存储过程：

# 存储过程可以简化复杂操作，提高检索性能

--创建存储过程

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