boolquerybuilder查询字段为空或者null的写法_三、p18-28条
件查询、。。。
本部分内容主要包括：MySQL中常⽤的条件查询语句(where)、模糊查询语句(like)、分组语句(group by)、聚合函数(having)、排序语句(order by)。
P18 简单查询语句
1.查询指定的列的数据：
select 列名1，列名2,… From 表名;
注：select * from table student；不对，select不跟table关键字
2.条件查询：在查询时给出where⼦句，在where⼦句中可以使⽤⼀些运算符号及关键字；
SELECT * from student WHERE id = 1001;
SELECT * from student WHERE id =1001 AND stu_age = 3;
P19 灵活运⽤上述判断字符：
• select * from student where stu_gender = '男' and stu_age = 20;
• select * from student where stu_gender = 1001 or stu_name = 'zs';
• select * from student where id =1001 or id=1002 or id=1003;
或者 select * from student where id between 1001 and 1003;【包括头尾】
或者 select * from student where id in(1001,1002,1003);
• select * from student where stu_age is NULL；数值型缺省时是null，字符型缺省时是空字符串'  '和null不⼀样；
• select * from student where stu_age >=18 and stu_age<=20;
或者select * from student where stu_age between 18 and 20;
• select * from student where gender != '男';
• select * from student where stu_name is not null;
查询空字符串的记录：select * from student where stu_name = ' ';s
P20 模糊查询（给⼀个通配符可以代替⼀些字符）
查询姓名由五个字母构成的学⽣记录：
select * from student where stu_name like '_____';
• 查询姓名由5个字符构成且第五个字符为e的学⽣记录
select * from student where stu_name like '____e';
• 查询姓名以“m”开头的学⽣记录
select * from student where stu_name like 'm%';
• 查询姓名中第⼆个字母为u的学⽣记录
select * from student where stu_name like '_u%';
• 查询姓名中包含“s”字母的学⽣记录
select * from student where stu_name like "%s%"；
P21 字段控制查询
去重查询：select distinct stu_name from student;
把年龄和分数相加：
select stu_age,stu_score,stu_age+stu_score from student;
对字段ifnull(XXX，某个值)处理之后：
select stu_age,stu_score,ifnull(stu_age,1)+ifnull(stu_score,0) FROM student;
distinct查询做完处理之后列名太长作为⼀个别名：
select stu_age,stu_score,ifnull(stu_age,1)+ifnull(stu_score,0) as res FROM student;
P22 排序语句
让选出来的数据从⼩到⼤的升序排列：SELECT * from employee ORDER BY salary;
让选出来的数据从⼤到⼩的降序排列：select * from employee order by salary desc;
两层排序：查询所有雇员，按⽉薪降序排序，如果⽉薪相同时，按编号升序排序
select * from employee order by salary desc, id asc;
P23 聚合函数（对查询的结果进⾏统计）-进⾏统计时注意使⽤ifnull：
查询employee表中记录数（返回⼀个数字）：select count(*) from employee;
查询employee表中绩效manage不为空的记录数：
select count(manage) from employee where manage is not null;
统计员⼯表中⽉薪⼤于2500的⼈数：
SELECT COUNT(*) from employee WHERE salary > 2500;
统计⽉薪和绩效之和⼤于5000的⼈数：
select count(*) from employee WHERE salary+ifnull(performance,0)>2500;
最后⼀个：select count(manage), count(performance) from employee;
• select sum(salary) from employee;
• select sum(salary),sum(performance) from employee;
• select sum(ifnull(salary,0)+ifmull(performance,0)) from employee;
• select avg(ifnull(salary,0)+ifnull(performance,0)) as avga from employee;
最⼤⼯资和最⼩⼯资：select max(salary),min(salary) from employee;
P25 分组操作（将查询结果按⼀个或多个字段分组，字段值相同分为⼀组）
原来的表：
• 希望把记录按照男⼥去分组：
select * from employee group by gender；单独使⽤只会出现第⼀条记录；
• 按照性别分组之后，这些⼈的名字分别叫什么：
select group_concat(name) from employee group by gender;
P26 分组与聚合函数【重点关注】
按照男⼥分组出来姓名和薪⽔（此时还是只是简单地堆积在⼀起）再⽤聚合函数进⾏统计（是按组⾥⾯的统计的）：
查询每个部门部门名称及每个部门⼈数：【切记看到“每个”这个字眼后⾯的被修饰词就是要分组】：也可以写成：select department, group_concat(name), count(*) from employee group by department;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。