MySQL 数据库（三）：查

查询特定字段，语法
select <字段1，字段2，...> from <表名>;

查询会员表里所有用户的手机号码和可用余额
select MobilePhone, LeaveAmount from member:

select * from <表名>:

查询会员表的所有用户信息。
select * from member;

语法：
select ... from ... order by 字段A asc(desc);
select ... from ... order by 字段A asc(desc), 字段B asc(desc）;
asc: 升序
desc: 降序

SELECT regname, leaveamount from member ORDER BY LeaveAmount desc;

-- 查询会员 id 为 1,2,3,7,15 的会员信息;
SELECT * from member where id in (1,2,3,7,15);

-- 查询会员 id 不为 1,2,3,7,15 的会员信息;
SELECT * from member where id not in (1,2,3,7,15);

条件字段的值以任意字符串开头，以xx结尾的值---（大熊猫）
select <字段1，字段2，...> from <表名> where <条件字段> like ‘%XX‘;
select * from member WHERE RegName like "%猫";

条件字段的值以xx开头，以任意字符串结尾---（大熊猫）
select <字段1，字段2，...> from <表名> where <条件字段> like ‘XX%‘
SELECT * from member where RegName like "大%";

条件字段的值包含了xx ---（大熊猫）
select <字段1，字段2，...> from <表名> where <条件字段> like ‘%XX%‘
SELECT * from member where RegName like "%熊%";

语法
select 查询字段，聚合函数 from 查询涉及到的表 group by 分组字段 having 过滤条件;

1、统计每个项目的平均投资额;
SELECT * ,AVG(amount) from invest GROUP BY LoanId;


2、扩展:统讦每个投资用户的累计投资额，并且显示出每个用户的昵称、id:
分析:
几张表? invest, member 关联
要分组吗?
有条件过滤吗? 

SELECT 
    member.RegName,
    member.id,
    SUM(invest.Amount)
FROM 
    invest, member
WHERE
    invest.MemberID = member.id
GROUP BY
    invest.MemberID
HAVING
    member.id < 10;

使用场景:去除查询结果中的重复数据
语法：
select distinct字段名1 from 表名;
示例：
查询所有有投资的用户Id
select distinct MemberID from invest;

使用场景:条件字段的取值处于两个数据范围内的情况
语法：
select .... from 表名 where 条件字段 between 数值A and 数值B;
SELECT * from member where id BETWEEN 10 and 20;

SELECT * from membe rLIMIT 10,10;

求字段A的最小值: min(字段A)
SELECT min (Amount) from invest;

求字段A的最大值: max(字段A)
SELECT max (Amount) from invest;

求字段A的平均值: avg(字段A)
SELECT avg (Amount) FROM invest;

求字段A的和: sum(字段A)
SELECT sum (Amount) from invest;

SELECT
       ....
FROM
       ....
WHERE
       ....
GROUP BY
       ....
HAVING
       ....
ORDER BY
       ....
LIMIT
       ....

内连接
语法: select 字段名 from 表名 inner JOIN 表名 on 关联关系;
SELECT * from user INNER JOIN user_lover on user.id = user_lover.id