MySQL 常规操作

MySQL 常规操作

1. 查询

1.1 普通查询

select * from students;   -- 获取所有数据
select * from students where age = 15;  -- 获取年龄为15的数据
select * from students where age = 15 and sex = 'male'; 

1. 2 模糊查询

select * from students where name like '%小%';  -- 查询name中含有’小‘的数据

1.3 范围查询

select * from students where id in(1,3,6);   -- 查询id在1，3，6 里的数据
select * from students where id between 3 and 6;   -- 查询id是3~6的数据

1.4 空判断

select * from students where sex is null;   -- 查询sex为null的数据
select * from students where sex is not null;   -- 查询sex为不为null的数据

2. 聚合

select count(*) from students;   -- 查询总数
select max(age) from students;   -- 查询最大年龄
select avg(age) from students;   -- 平均年龄

3. 分类聚合

select age, count(age) from students group by age;  -- 查询不同年龄段人数

4. 排序

select * from students order by id desc;   -- 按id降序排列

5. 日期模糊查询

# 获取包含2019-09-23 所有数据
SELECT * from search_keyword where DATEDIFF(update_time, '2019-09-23') = 0;