sql基础之select语句（二）

上一篇文章介绍了select语句的简单用法→https://blog.csdn.net/autobot_area/article/details/88798641

本篇文章进行一些拓展

1.通过函数处理数据

    ①处理文本字符类：

        1>进行大小写处理：select UPPER(user_name) from users where user_age = 18;LOWER(user_name)

        2>进行去空格处理：select trim(user_name) from users where user_age = 18; LTRIM(),RTRIM();去除左/右空格

        3>返回该列数据长度：select length(user_name) from users where user_age = 18;   

    ② 处理日期时间类：

        1>Mysql：select user_name from users where YEAR(create_date) = 2018;

        2>Oracle：select user_name from users where to_char('yyyy-mm-dd',create_time) = '2018-03-01';

2.汇总数据

    通常我们会通过聚合函数来进行某列的数据计算

    ①计算总和：select sum(user_score) from users where user_name = '张三';

    ②计算平均数：select avg(user_score) from users where user_age = 18;

    ③计算最大值：select max(user_score) from users where user_age = 18;

    ④计数：select count(*) from users where user_score > 90;

    ⑤计算不同的值：select avg(distinct user_score) as avg_score from users where user_age = 18;

    ⑥组合聚合函数：select count(*) as user_nums,max(user_score) as max_score,min(user_score) as max_score from users;

 3.分组数据

    ①创建分组：select user_sex,count(*) from users group by user_sex;

    ②过滤分组：

        1>select user_sex ,count(*) from users group by user_sex having count(*) > 2;

        2>select user_sex ,count(*) from users where user_score > 90 group by user_sex having count(*) > 2;

        3>select user_score,count(*) from users where user_score > 90 group by user_score having count(*) > 2 order by user_score;

4.子查询

    ①  select user_name,user_score from users where user_id in (select user_id from roles where subject in ('math','english'))

5.关联表查询

    ①select user_name,user_score,subject from users,roles where users.user_id = roles.user_id;

6.组合查询(union)

    union 适用于两条及两条以上的select语句，且查询的字段需相同。union默认返回去重后的字段，而union all返回所有结果

    ①select user_name,user_sex from users where user_age = 18 union select user_name,user_sex from users where user_score > 80;