MYSQL中select语句详解

-- 查询 MQL:数据查询语言
select NOW();
select 1;
-- 指定列名查询
select * from staff;//查询所有的数据

-- 使用as指定别名 列 方法结果 表 视图
select name as 姓名 from staff;

-- where 指定条件语句
select * from staff where id=6;

-- 不等于
select * from staff where id!=6;
select * from staff where id<>1;

-- 大于  小于
select * from staff where id>2;
select * from staff where id>=2;

-- 对null值的判断
select * from satff where salary=null;-- 错误写法
select * from staff where salary is null;
select * from staff where salary is not null;
-- 等于null
select * from staff where salary <=> null;

-- 多条件
select * from staff where id<100 or salary>=10000;

*-- in() 
select * from staff where id in(1,3);-- 判断是否存在in中的任意一个，输出id为1和3的数据
select * from staff where id not in(1,3);-- 输出id不为1和3的数据

*-- 是否存在 exists 只要存在(select * from staff where id=2)
-- 就显示前面的(select * from staff)所有结果  不存在就不显示结果
-- exists中的内容不显示
select * from staff where exists(select * from staff where id=2)
select * from staff where not exists(select * from staff where id=1)

-- between and
select * from staff where salary between 10000 and 13000;

-- 模糊查询  %任意个数任意字符    _有且只有一个字符
select * from staff where name like '%李%';
select * from staff where name like '李--';-- 李某某

-- any all
-- 输出为 code中最大的数大于staff的列
select * from staff where salary>all(select code from staff)
-- 输出为 code中最小的数大于staff的列
select * from staff where salary>any(select code from staff)

-- 排序 order by
select * from staff ORDER BY salary -- 默认正序
-- 正序  asc;  倒序  desc
SELECT * from staff order by salary asc;
select * from staff ORDER BY salary desc;
-- 为int类型code 当第一个比较的属性值相同时，按照第二个的比较排序
select * from staff ORDER BY salary asc,code desc;

-- 拼接(合并)查询结果  UNION  两个语句查询的列相同
select name,salary from staff UNION 
select name,code from staff;
-- 去重效果
select name,salary from staff UNION 
select name,salary from staff;
-- union All 不会去重
select name,salary from staff UNION All
select name,salary from staff;

-- 去重 对整个查询结果去重
SELECT DISTINCT salary,name from staff;

-- 部分查询 limit
SELECT * from staff ORDER BY salary desc limit 3;
-- 查询第三名到第五名 limit start,count;
select * from staff ORDER BY salary desc LIMIT 2,3;

-- case when then
select *,
case
when salary<=10000 then '薪资较低'
when salary>=12000 and salary<=20000 then '薪资正常'
else '薪资较高'
end 
from staff;

select *,case salary when 9000 then '还行'
when 10000 then '还可以'
when 12000 then '很高'
when 16000 then '好厉害'
end as '等级'
from staff;

-- case when then
select *,
case
when salary<=10000 then '薪资较低'
when salary>=12000 and salary<=20000 then '薪资正常'
else '薪资较高'
end 
from staff;
select *,case salary when 9000 then '还行'
when 10000 then '还可以'
when 12000 then '很高'
when 16000 then '好厉害'
end as '等级'
from staff;

-- 分组查询  可以结合聚合函数，
SELECT department,avg(salary) as 平均薪资,
sum(salary) as 薪资总和
from staff GROUP BY department;
-- 分组筛选 having  对分组之后的数据在进行筛选
select department from staff group by department

1. HAVING avg(salary)<10000;