MySQL操作(五)查询实例

1、mysql查询区分大小写

方法1、在不改变表任何结构的情况下,最简单的做法就是在条件后面的字段名或者字段值作为binary()函数的参数即可,如下:
select * from `user`  where binary `email`='zhang_hao@163.com';
select * from `user`  where binary(`email`)='zhang_hao@163.com';

方法2、建表的时候在字段后面加上binary,或者用alter语句来改变字段类型,只需要加上binary就行
ALTER table `gl_user` modify column `name` varchar(255) binary NOT NULL DEFAULT '' COMMENT '姓名';

 

2、mysql统计一个数据库中每张表的行数、表所占空间大小(数据长度+索引长度)

use information_schema;
select table_name,table_rows,data_length+index_length as data_size from tables where TABLE_SCHEMA = '数据库名' order by data_size desc,table_rows desc;

 

3、mysql多表联合查询加分页

SELECT `un`.crid,cr.crname,sum(total) successtotalfee,sum(count) taketimes from ((SELECT crid,sum(total) total,count(1) count from ebh_takes where state=1 AND del=0 group by crid) UNION ALL (SELECT crid,sum(moneyaftertax) total,count(1) count from ebh_jsapplys where paystatus=1 group by crid)) as un left JOIN `ebh_classrooms` cr ON cr.crid=un.crid  WHERE  `un`.crid in(14339,14338,14337,10606)  GROUP BY `un`.crid ORDER BY successtotalfee DESC  LIMIT 0, 50

 

4、返回子串substr在字符串str中出现的位置

FIND_IN_SET(str,list)  分析逗号分隔的list列表，如果发现str，返回str在list中的位置
POSITION(substr IN str) 返回子串substr在字符串str中第一次出现的位置 等价于LOCATE
LOCATE(substr,str)  返回子串substr在字符串str中第一次出现的位置 

//participants : 'zhansan@163.com,lisi@qq.com,mazi@126.com'
//tidstr: '85,36,25'

SELECT * from cls_course where find_in_set('gu_yun@qq.com',participants); 

SELECT * from ebh_activitys where LOCATE('85',tidstr)>0;

SELECT * from ebh_activitys where POSITION('85' IN tidstr)>0;

 

5、字符串查找替换(字段值替换)

replace(field,search,new); 字符串查找替换

UPDATE webinars_course set course_url=replace(course_url,'training/','www/');  //将course_url字段中的training/ 字符串替换为www/
UPDATE user_menu SET order_by=REPLACE(order_by,'0',id); //将order_by字段的0值 全部替换为id字段值

 

6、mysql控制流函数

-- 1、根据数字返回判断性别
SELECT u.uid,u.username,CASE u.sex WHEN 0 THEN '男' WHEN 1 THEN '女' ELSE '其他' END sex FROM eb_users u LIMIT 100000;

-- 2、按性别和余额区间统计数量和平均值
SELECT count(0) count,if(sex=0,'男','女') sex,avg(balance),(case when balance<1000 then '1000以下' when balance BETWEEN 1000 and 5000 then '1000-5000' else '5000以上' end) as `level` from ebh_users where sex<2  GROUP BY `level`,sex;

-- 3、根据条件取不同字段值，并新增字段(新增字段res_type,赋值为1)，多字段组合模糊查询
SELECT id,IF(user_name<>'',user_name,account) as `name`,1 as `res_type` FROM `users` WHERE CONCAT(account,user_email) LIKE '%V_gu_liang%' 

 

7、变量设置

-- 1、变量设置(适用多条语句操作，有相同值得时候)
SET @userId='123466665';
-- 将sql语句查询的结果赋值到变量
SET @userId=(SELECT USER_ID FROM `user` WHERE EMAIL='gu_liang@dahuatech.com');
DELETE FROM `user` WHERE  USER_ID=@userId；
DELETE FROM `customer` WHERE  CUSTOMER_ID=@userId；
DELETE FROM `t_customer` WHERE  CUSTOMER_ID=@userId；

 

8、指定值排序

SELECT id,parent_id,parent_str from test_category where id in(7976,3918,61138,61139,100972) order by field(id,7976,3918,61138,61139,100972);

 

9、多字段去重查询

SELECT DISTINCT on (project_id,product_id) project_id,product_id,product_name,project_name FROM product_project;

 

未完、持续更新中