MYSQL-DQL操作

基本查询:

查询特定列:
select name,entrydate from tb_emp;
查询所有:
select * from tb_emp;
查询并起别名:
select name as bbb,entrydate as aaa from tb_emp;

(as可省略)
去除重复记录:
select distinct job from tb_emp;

条件查询:

点击查看代码

select * from tb_emp where name='杨逍';

select * from tb_emp where id<=5;

select * from tb_emp where job is null;

select * from tb_emp where job is not null;

select * from tb_emp where password!='123456';

select * from tb_emp where entrydate >='2000-01-01' and entrydate <='2010-01-01';

select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';

select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender=2;

select * from tb_emp where job =2 or job=3 or job=4;

select * from tb_emp where job in (2,3,4);

select * from tb_emp where name like '__';

select * from tb_emp where name like '张%';

分组查询:
聚合函数:

点击查看代码

select count(id) from tb_emp;

select count(job) from tb_emp;

select count(*) from tb_emp;

select min(entrydate) from tb_emp;

select max(entrydate) from tb_emp;

select avg(id) from tb_emp;

select sum(id) from tb_emp;

select gender,count(*) from tb_emp group by gender;
select job,count(*) from tb_emp where entrydate <='2015-01-01' group by job having count(*)>=2;

排序查询:

select * from tb_emp order by entrydate ;

select * from tb_emp order by entrydate desc ;

select * from tb_emp order by entrydate ,update_time desc ;

分页查询:

select * from tb_emp limit 0,5;

select * from tb_emp limit 10,5;

起始索引=(页码-1)*每页条数