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 | +--------------+----------+---------+---------+---------+-----------+
