(adsbygoogle = window.adsbygoogle || []).push({ google_ad_client: "ca-pub-6940460185323525", enable_page_level_ads: true });
由于很多时候表名比较长而且难记,所以就用表名的别名来代替表名:select m.regname,m.leaveamount,i.amount from member as m, invest as i where m.id=i.memberid;(as 可以去掉,多表查询字段可能会重复,字段名前面加上表名.字段名)
用法:select * from 表名 where 字段名1 in(数据1,数据2);
用法等同于select * from 表名 where 表达式1 or 表达式2;
数据集:可以是具体的某几个值:值a,值b,…..值n,也可以是通过一个子查询得到的数据集
比如: select * from test where id in (1,2,3);
select * from 表名 where 字段名 like %关键字/关键字%/%关键字%/占位符_;
用法:select 字段名,聚合函数 from 表名 where group by 分组字段名;
多字段进行分组:select t1.id ,t1.regname ,sum(amount) from member t1,invest t2 where t1.id=t2.memberid group by t1.id ,t1.regname ;
注意:多表查询一定要先写关联条件在分组
比如:
1、统计每个标的投资总额、投标人数、总投资金额、最大与最小投资金额
select LoanId,count(id),sum(amount),min(amount),max(amount) from invest group by loanid;
2、从loan表分组查询,按照memberid进行分组,计算用户个数,且投资金额大于100000
select *,count(*) from loan group by membered having amount>100000;
3、统计每个用户的投资额
select t1.id ,sum(amount) from member t1,invest t2 where t1.id=t2.memberid group by t1.id ;
去除查询结果中的重复数据
用法:select distinct 字段名 from 表名;
比如:查询所有投资的用户id (重复的memberid就去除了)
Select distinct memberid from invest;
5、两者之间 between:
使用场景:条件字段的取值处于两个数据范围内的情况
用法:select 字段名 from 表名 where 字段名 between … and …..;
比如:找出用户表可用余额在100000到400000的用户信息(包含边界值)
select * from member where leaveamount between 100000 and 400000;
或select * from member where leaveamount>=100000 and leaveamount<=400000;
使用场景:去查询结果的前n条
用法:select 字段名 from 表名 limit m,n;(m为偏移量=要显示的第一位数-1 ,n=要显示的数据个数)
比如:
1、选取member表的前10条记录
select * from member limit 0,10;
2、选取member表的后十条记录
select * from member order by id desc limit 0,10;
3、选取member表第21-30条记录
select * from member limit 20,10;
4、查询id 20-30的记录
select * from member limit 19,11;
top语法
select *from member where leaveamount>30000 limit 5;
偏移量 offset
select * from member limit 11 offset 19;
=select * from member limit 19,11;
1、求字段A的最小值min(字段A)
eg: select min(leaveamount) from member;
2、求字段A的最大值max(字段A)
eg: select max(leaveamount) from member;
3、求字段A的平均值
eg: select avg(leaveamount) from member;
1、获取系统当前时间syndate();。select syndate() ;
2、获取系统当前日期curdate()。select curdate();
3、获取系统当前时间curtime()。select curtime();
4、获取给定日期的年份:year(date)。select year(2016);
5、获取给定日期的月份:month(date)。select month(2016);
6、为指定日期增加一个时间间隔的函数:DATE_ADD(date,interval,expr unit)
select DATE_ADD(sysdate(),interval 7 day);
1、字符串拼接函数:concat(字段A,字段B)
eg:select concat(regname,’-‘,mobilephone) from member;
2、字符串截取函数substr(字段A,截取最开始的位置position,截取字符个数)
eg:select substr(mobilephone,1,3) from member;
3、获取字符串长度函数:length(字段A)
select length(regname) from member ;