mysql中怎样去除 值 值不为 null的 空行_MySql学习笔记——DQL

*查询数据

查询所有

…select * from students;

查询指定列

… select name,age from students;

… select * from students

… where sex=‘nan’ and age =18;

//在navicat中可以保存查询语句方便下次使用

… select * from students

…where sno=1 or sname=‘zs’;

//navicat中注释 ctrl+/

IN查询同一属性的多值,固定范围值

… select * from students where sno in(1,2,3);

==select * from students where sno=1 or sno=2 or sno=3;

查询值为null的记录

… select * from students where sname is null;

查询值不为null的记录

…select * from students where sname is not null;

查询非的数据

… select * from students where sex!=‘nan’;

查询两者之间的数据

…select * from students where age>=18 and age<=20;

==select * from students where age between 18 and 20;

*模糊查询

查询姓名由5个字母构成的学生记录

…select * from students where name like ‘_____’;

…select * from students where name like ‘____s’;

以m开头的学生

…select * from students where name like ‘m%’;

第二个字母是u

…select * from students where name like ‘_u%’;

包含信息s

…select * from students where name like ‘%s%’;

*字段控制查询

去除重复操作distinct

…select distinct name from students;

对字段结果进行运算来生成新的字段

…select *,age+score from students;

null变为0

…select *,ifnull(age,0)+ifnull(score,0) from students;

改变列名

…select *,ifnull(age,0)+ifnull(score,0) as total from students;

…select sname as resname from students;

创建表

create table employee(

id int(11) not null,

name varchar(50) default null,

gender varchar(1) default null,

hire_date date default null,

salary decimal(10,0) default null,

performance double(255,0) default null,

manage double(255,0) default null,

department varchar(255) default null

);

//navicat中

CREATE TABLE employee (

id int(11) NOT NULL,

name varchar(50) DEFAULT NULL,

gender varchar(1) DEFAULT NULL,

hire_date date DEFAULT NULL,

salary decimal(10,0) DEFAULT NULL,

performance double(255,0) DEFAULT NULL,

manage double(255,0) DEFAULT NULL,

department varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// 在navicat中可以把数据导出

// 还可以通过搜索语句建表

*排序

升序(默认升序)

… select * from employee order by salary;

== select * from employee order by salary ASC;

降序

… select * from employee order by salary DESC;

多重排序

… select * from employee order by salary DESC,id DESC;

*聚合函数

计数

…select conut() from employee;

条件计数

…select count() from employee where salary>2500

…select count(*) from employee where ifnull(salary,0) + ifnull(performance,0)>5000;

多条计数

…select count(performance),count(manage) from employee;

求和

…select sum(salary) from employee;

多条求和

…select sum(salary),sun(performance) from emplyoee;

求多条和

…select sum(salary+ifnull(performance,0)) from emplyoee;

求平均

…select avg(salary) from employee;

求最大

…select max(salary) from employee;

求最小

…select min(salary) from employee;

还有就是这我总结出了一些架构视频资料和互联网公司java程序员面试涉及到的绝大部分面试题和答案做成了文档和架构视频资料还有完整高清的java进阶架构学习思维导图免费分享给大家(包括Dubbo、Redis、Netty、zookeeper、Spring cloud、分布式、高并发等架构技术资料)，希望能帮助到您面试前的复习且找到一个好的工作，也节省大家在网上搜索资料的时间来学习。需要的可以私信我

如果您喜欢本文章，可以点击关注，每天将有更多精彩文章与您分享！