8.Mysql 常用函数和基础查询

Mysql 函数参考和扩展: Mysql 官网

Mysql 语法执行顺序如下，一定要清楚！！！运算符相关，可前往 Mysql 基础语法和执行顺序扩展。

(8) select (9) distinct (11)<columns_name list>
(1) from <left_table>
(3) <join_type> join <right_table>
(2) on <join_condition>
(4) where <where_condition>
(5) group by <group_by columns_name list>
(6) with <rollup>
(7) having <having_condition>
(10) order by <order_by columns_name list>
(12) limit <[offset，] rows>
;

Mysql 常用函数

数学函数

字符串函数

日期函数

json 函数

聚合函数

简单查询

数据准备

create database sql_test1;
use sql_test1;
create table if not exists `user_info`
(
    `id`                    int         not null,
    `device_id`             int         not null comment '用户id',
    `gender`                varchar(14) not null comment '性别',
    `age`                   int comment '年龄',
    `university`            varchar(32) not null comment '大学',
    `gpa`                   float comment '得分',
    `active_days_within_30` int comment '30天内活跃天数'
);
truncate table user_info;
insert into user_info values(1,2138,'male',21,'北京大学',3.4,7);
insert into user_info values(2,3214,'male',null,'复旦大学',4.0,15);
insert into user_info values(3,6543,'female',20,'北京大学',3.2,12);
insert into user_info values(4,2315,'female',23,'浙江大学',3.6,5);
insert into user_info values(5,5432,'male',25,'山东大学',3.8,20);
insert into user_info values(6,2131,'male',28,'山东大学',3.3,15);
insert into user_info values(7,4321,'male',28,'复旦大学',3.6,9);

直接查询

查询所有列、指定字段和字段起别名

select * from user_info; -- 查询所有列
+----+-----------+--------+------+--------------+------+-----------------------+
| id | device_id | gender | age  | university   | gpa  | active_days_within_30 |
+----+-----------+--------+------+--------------+------+-----------------------+
|  1 |      2138 | male   |   21 | 北京大学     |  3.4 |                     7 |
|  2 |      3214 | male   | NULL | 复旦大学     |    4 |                    15 |
|  3 |      6543 | female |   20 | 北京大学     |  3.2 |                    12 |
|  4 |      2315 | female |   23 | 浙江大学     |  3.6 |                     5 |
|  5 |      5432 | male   |   25 | 山东大学     |  3.8 |                    20 |
|  6 |      2131 | male   |   28 | 山东大学     |  3.3 |                    15 |
|  7 |      4321 | male   |   28 | 复旦大学     |  3.6 |                     9 |
+----+-----------+--------+------+--------------+------+-----------------------+
select device_id,gender,age,university 
from user_info;-- 查询指定字段
+-----------+--------+------+--------------+
| device_id | gender | age  | university   |
+-----------+--------+------+--------------+
|      2138 | male   |   21 | 北京大学     |
|      3214 | male   | NULL | 复旦大学     |
|      6543 | female |   20 | 北京大学     |
|      2315 | female |   23 | 浙江大学     |
|      5432 | male   |   25 | 山东大学     |
|      2131 | male   |   28 | 山东大学     |
|      4321 | male   |   28 | 复旦大学     |
+-----------+--------+------+--------------+
select device_id,gpa,
       round(gpa*rand(),1) as gpa_adj
from user_info; -- 生成新计算列并起别名
+-----------+------+---------+
| device_id | gpa  | gpa_adj |
+-----------+------+---------+
|      2138 |  3.4 |     2.3 |
|      3214 |    4 |     2.3 |
|      6543 |  3.2 |     2.8 |
|      2315 |  3.6 |     2.4 |
|      5432 |  3.8 |     2.4 |
|      2131 |  3.3 |     0.9 |
|      4321 |  3.6 |     1.3 |
+-----------+------+---------+

去重查询

select distinct university 
from user_info;-- 查看去重后有哪些大学
+--------------+
| university   |
+--------------+
| 北京大学     |
| 复旦大学     |
| 浙江大学     |
| 山东大学     |
+--------------+

条件查询

select * 
from user_info 
where age is null;-- 查询年龄不为null的数据
+----+-----------+--------+------+--------------+------+-----------------------+
| id | device_id | gender | age  | university   | gpa  | active_days_within_30 |
+----+-----------+--------+------+--------------+------+-----------------------+
|  2 |      3214 | male   | NULL | 复旦大学     |    4 |                    15 |
+----+-----------+--------+------+--------------+------+-----------------------+
select device_id
from user_info
where university = '北京大学' 
  and gender = 'male';-- 查询北京大学男性有哪些用户
+-----------+
| device_id |
+-----------+
|      2138 |
+-----------+

排序查询

select *
from user_info
order by gender,age desc;-- 查询性别升序，年龄降序的记录
+----+-----------+--------+------+--------------+------+-----------------------+
| id | device_id | gender | age  | university   | gpa  | active_days_within_30 |
+----+-----------+--------+------+--------------+------+-----------------------+
|  4 |      2315 | female |   23 | 浙江大学     |  3.6 |                     5 |
|  3 |      6543 | female |   20 | 北京大学     |  3.2 |                    12 |
|  6 |      2131 | male   |   28 | 山东大学     |  3.3 |                    15 |
|  7 |      4321 | male   |   28 | 复旦大学     |  3.6 |                     9 |
|  5 |      5432 | male   |   25 | 山东大学     |  3.8 |                    20 |
|  1 |      2138 | male   |   21 | 北京大学     |  3.4 |                     7 |
|  2 |      3214 | male   | NULL | 复旦大学     |    4 |                    15 |
+----+-----------+--------+------+--------------+------+-----------------------+

分页查询

select *
from user_info
limit 1;-- 查询出一条数据
+----+-----------+--------+------+--------------+------+-----------------------+
| id | device_id | gender | age  | university   | gpa  | active_days_within_30 |
+----+-----------+--------+------+--------------+------+-----------------------+
|  1 |      2138 | male   |   21 | 北京大学     |  3.4 |                     7 |
+----+-----------+--------+------+--------------+------+-----------------------+
select *
from user_info
order by gpa desc
limit 1;-- 查询得分最高的一个用户
+----+-----------+--------+------+--------------+------+-----------------------+
| id | device_id | gender | age  | university   | gpa  | active_days_within_30 |
+----+-----------+--------+------+--------------+------+-----------------------+
|  2 |      3214 | male   | NULL | 复旦大学     |    4 |                    15 |
+----+-----------+--------+------+--------------+------+-----------------------+

分组查询

select university,
	     count(distinct device_id) as user_cnt,
       round(avg(gpa),2) as avg_gpa,
       max(gpa) as max_gpa,
       min(gpa) as min_gpa,
	     group_concat(distinct age order by age separator '、') as age_distr
from user_info
group by university;-- 查询每个大学的用户数、平均得分、最大的分、最小得分和年龄分布情况
+--------------+----------+---------+---------+---------+-----------+
| university   | user_cnt | avg_gpa | max_gpa | min_gpa | age_distr |
+--------------+----------+---------+---------+---------+-----------+
| 北京大学     |        2 |     3.3 |     3.4 |     3.2 | 20、21    |
| 复旦大学     |        2 |     3.8 |       4 |     3.6 | 28        |
| 山东大学     |        2 |    3.55 |     3.8 |     3.3 | 25、28    |
| 浙江大学     |        1 |     3.6 |     3.6 |     3.6 | 23        |
+--------------+----------+---------+---------+---------+-----------+