----用SQL进行单表查询
--查询表中所有记录
select * from db_user;
--查询表中某些字段对应数据
select id,name,age from db_user;
--查询表中某些字段不重复的数据
select distinct city from db_user;
--查询表中字段的所有数据
select all city from db_user; --all可以省
--单条件查询
select * from db_user where city='北京';
--循环 + 判断
select * from db_user where 1=1; --可以执行
select * from db_user where a=1; --不可以执行,因为a不是db_user的字段
select 1 from db_user;
select id,1 from db_user;
select a from db_user;
select 'a' from db_user;
--比较运算符
--等于 ‘=’
select * from db_user where city='北京';
select * from db_user where age=23;
--不等于 ‘!=’
select * from db_user where city!='北京';
select * from db_user where age!=23;
--不等于 ‘^=’
select * from db_user where city^='北京';
select * from db_user where age^=23;
--不等于 ‘<>’
select * from db_user where city<>'北京';
select * from db_user where age<>23;
--小于 ‘<’
select * from db_user where city<'北京';
select * from db_user where age<23;
--大于
select * from db_user where city>'北京';
select * from db_user where age>23;
--小于等于 ‘<=’
select * from db_user where city<='北京';
select * from dn_user where age<=23;
--大于等于 ‘>=’
select * from db_user where city>='北京';
select * from db_user where age>=23;
--在列表 ‘in’
select * from db_user where city in ('北京','上海','深圳');
select * from db_user where age in (22,23,24);
--不在列表 ‘not in’
select * from db_user where city not in ('北京','上海','深圳');
select * from db_user where city not in (22,23,24);
--介于之间 ‘between’
select * from db_user where city between '上海' and '深圳';
select * from db_user where age between 24 and 26;
--不介于之间 ‘not between’
select * from db_user where city not between '上海' and '深圳';
select * from db_user where age not between 24 and 26;
--模式匹配 ‘like’
select * from db_user where name like '李%';
select * from db_user where name like '李_';
--模式不匹配
select * from db_user where name not like '李%';
select * from db_user where name not like '李_';
--是否为空 ‘is null’
select * from db_user where city is null;
select * from db_user where age is null;
--是否不为空 ‘is not null’
select * from db_user where city is not null;
select * from db_user where age is not null;
--组合条件查询
--与 ‘and’
select * from db_user where city='北京' and age>24;
--或 ‘or’
select * from db_user where city='北京' or age>24;
--非 ‘not’
select * from db_user where not city='北京';
--排序查询
--升序
select * from db_user order by age;
--降序
select * from db_user order by age desc;
select * from db_user where sex='男' order by age desc;
select * from db_user order by sex desc ,age;
--分组查询
例 1,我们需要查询用户分布在哪些城市,并且统计每个城市中有多少人。
select city,count(Id)
from db_user
group by city; --count :对每一组记录进行数量统计
例 2,在例 1 的基础上,如果我们只需查询分组后,人数大于等于 2 的城市和人数,那么可
以在分组后,使用 having 检查分组后每组是否满足条件。
select city ,count(Id)
from db_user
group by city
having count(Id)>1; --having 语句只能配合group by语句使用。
--字段运算查询
select name,age,city,age+10
from db_user;
--变换查询显示
select id 编号,name 姓名,age 年龄,city 城市
from db_user;