Mysql常用功能

MYSQL常用及存储过程
一、常用查询语句
1）LIKE的灵活运用

% ：匹配一个或多个字符串 
_ ：匹配单个任意字符串 
【】：中括号内的单个字符匹配 
【^ 】：非括号内的单个字符匹配

2）分组查询

GROUP BY +HAVING 
GROUP BY：用于分组，通常会与聚合函数（如max、sum、min、count等）搭配起来使用 
HAVING：用于筛选，与where类似，但是一般会与GROUP BY搭配起来使用 

实例：
查询骑手商城的商品中定价大于1元的商品，并以商品品类进行分组

SELECT sale_price,COUNT( sale_price) AS heji FROM malldb.mall_order_sku GROUP BY sale_price HAVING sale_price>1

3）子查询

一般用in 、not in、exsit、not exsit将主查询表与子表中的关联字段做连接

4）取两个表结果集中的差集
在oracle里面比较简单，可以用minus 来直接取出两个表的差集
在mysql里面没有此类关键字可以用，相对来说查询效率较高的，要用左连接的方式来查询
举例：

SELECT a.* 
FROM (SELECT * from malldb.mall_user_express_addr 
WHERE user_id=’53’ 
) a 
LEFT JOIN (SELECT * 
FROM malldb.mall_user_express_addr 
WHERE address LIKE ‘%今天%’ 
) b 
on a.id=b.id 
where b.id is null

5）分页查询

limit N,M 从N条开始到N+M条被取出 
比如要取出第5页的数据，每页显示20条 
select * from 表A limit 81，20

二、常用函数
1）获取总数量的方式，有2种方式，但是方式1会效率上快很多

方式一、 
select SQL_CALC_FOUND_ROWS * FROM malldb.mall_order where user_id=’53’; 
SELECT FOUND_ROWS(); 
方式二、 
select COUNT(*) FROM malldb.mall_order

2）日期时间相关函数

select CURDATE(), CURTIME(), NOW(); 
比如有时候插入数据时，某个字段会记录插入时间 
INSERT INTO mall_user_express_addr VALUES(NULL,’52’,”,”,”,”,addr,”,”,NOW(),sysdate())

3）INSERT(str,x,y,instr) 函数

将字符串str从第x位置开始，y个字符长的子串替换为字符串instr

4）去从后再统计

count (distinc ‘字段A’,’字段B’) 
去掉A 字段和B字段的重复记录之后，再算除总值

4）格式转换

CAST函数 
CAST(‘12.5’ AS decimal(9,2)) —–结果12.50 
CAST(123.4 as int) —–123

5）日期转化成字符串

date_format(now(),’%Y-%c-%d %h:%i:%s’) 
实例 
SELECT * FROM malldb.mall_user_express_addr WHERE address LIKE ‘%今天%’ AND DATE_FORMAT(create_time,’%Y-%c-%d’)=’2017-12-05’

三、存储过程基本使用
1）语法

创建存储过程 
create procedure 存过名称(in 参数 参数类型,inout 参数 参数类型，out 参数 参数类型) 
begin 
存过主体内容 
end

删除存储过程 
drop procedure 存过名称

调用存储过程 
call 存过名称（参数1，参数2，…）

2）实例

DROP PROCEDURE PROTEST 
CREATE PROCEDURE PROTEST(in times INT) 
BEGIN 
DECLARE i INT DEFAULT 0; 
DECLARE addr VARCHAR(1000) ; 
SET addr=’人力资源产业园’; 
WHILE i<cishu DO 
INSERT INTO mall_user_express_addr VALUES(NULL,’52’,”,”,”,”,addr,”,”,NOW(),sysdate()); 
SET i=i+1; 
SET addr=CONCAT(addr,CAST(i AS CHAR(8))); 
END WHILE; 
END

二、left join 和inner join 多表的 复杂查询实例

SELECT DISTINCT 
(a.package_id) 
FROM 
mall_itemdb.mall_item_package_item a 
LEFT JOIN mall_stockdb.mall_sku_warehouse_stock b ON a.item_id = b.item_id 
WHERE 
b.id IS NOT NULL 
AND a.package_id NOT IN ( 
SELECT DISTINCT 
( 
mall_itemdb.mall_item_package_item.package_id 
) 
FROM 
( 
mall_itemdb.mall_item 
INNER JOIN mall_itemdb.mall_item_package_item ON mall_itemdb.mall_item.id = mall_itemdb.mall_item_package_item.item_id 
) 
INNER JOIN mall_stockdb.mall_sku_warehouse_stock ON mall_itemdb.mall_item_package_item.item_id = mall_stockdb.mall_sku_warehouse_stock.item_id 
WHERE 
mall_stockdb.mall_sku_warehouse_stock.good_stock = 0 
) 
AND a.package_id IN ( 
SELECT 
mall_itemdb.mall_item_package_item.package_id 
FROM 
mall_itemdb.mall_item 
INNER JOIN mall_itemdb.mall_item_package_item ON mall_itemdb.mall_item.id = mall_itemdb.mall_item_package_item.item_id 
WHERE 
mall_item.shelf_status = 1 
AND mall_item.sort_code IS NOT NULL 
) 
ORDER BY 
b.item_id DESC 
LIMIT 1