Mysql的单表查询语法

1.完整语法:(语法级别关键字排列顺序如下)

select distinct 字段1，字段2，字段3，...form 库名.表名
where 约束条件
group by 分组依据
having 过滤条件
order by 排序的字段
limit 限制显示的条数
;
关键字执行的优先级

from>where>group by>having>distinct(去重)>order by>limit...

2.单表查询:

create table emp(
        id int primary key auto_increment,
        name varchar(20) not null,
        sex enum('male','female')not null default 'male',
        age int not null,
        h_date date not null,
        post varchar(50),
        port_comment varchar(100),
        salary double(15,2),
        office int,#一个部门一个屋子
        depart_id int #部门id
                 );

插入数据：

insert into emp(name,sex,age,h_date,post,salary,office,depart_id) values
('西施','male',18,'20170301','tuling',7300.33,401,1), #以下是教学部
('大海','male',78,'20150302','teacher',1000000.31,401,1),
('夏洛','male',81,'20130305','teacher',8300,401,1),
('顾安','male',73,'20140701','teacher',3500,401,1),
('诸葛亮','male',28,'20121101','teacher',2100,401,1),
('周瑜','female',18,'20110211','teacher',9000,401,1),
('刘邦','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('美美','female',48,'20150311','sale',3000,402,2),#以下是销售部门
('九九','female',38,'20101101','sale',4000,402,2),
('攒攒','female',18,'20110312','sale',5000,402,2),
('西西','female',18,'20160513','sale',6000,402,2),
('东东','female',28,'20170127','sale',7000,402,2),

('段誉','male',28,'20160311','operation',3000,403,3), #以下是运营部门
('乔峰','male',18,'19970312','operation',4000,403,3),
('东邪','female',18,'20130311','operation',5000,403,3),
('西毒','male',18,'20150411','operation',6000,403,3),
('北丐','female',18,'20140512','operation',7000,403,3);

简单查询：

select * from emp;

1.查询单个字段：

select post from emp;

2.去除某个字段的重复：

select distinct post from emp;

3.查看员工的月薪：

select name,salary from emp;

4.查看员工的年薪并取名为year_salary:

select name,salary*12 as year_salary form emp;

5.字符串的拼接查询:

select concat('名字：',name) as new_name,concat('年龄:',age)as new_age from emp;

6.合并一列，拼接：

select concat(name,':',age) as name_and_age for emp;

7.一次性合并多个：

select concat_ws(':',name,age,sex) as info from emp;

二.where:
1.id 大于10 小于15的员工：

select * from emp where id>10 and id<15;

2.id大于或等于10，小于或等于15的员工 等价between

select * from emp where id>=10 and id <= 15;
select * from emp where id between 10 and 15;

3.or 等价 in

select * from emp where id =6 or id=9 or id=12;
select * from emp where id in(6,9,12);

4.not

select * from emp where id not in(6,9,12)

5.like _代表单个字符 %代表任意无穷个字符

select * from emp where name like '__';

select * from emp where name like '西%';

三.group by 分组
什么是分组：按照所有记录相同的部分进行归类，一定要是区分度低的字段。
为何要分组：当我们要以组为单位进行统计时就必须分组，分组的目的是为了以组为单位进行统计的，再去考虑单条记录毫无意义
group by 常和聚合函数连用
max 最大
min 最小
avg 平均
sum 和
count 个数
1.分组语法：
错误：

select * from emp group by post;
select name,age from emp group by post;

正确：

select post from emp group by post;

2.group_concat 可以展示分组后字段的记录（一般不这样用）就是分组后的组员信息

select group_concat(name),group_count(age) from emp group by post;

3.按照部门分组，每个部门多少人

select post,count(id) from emp group by post;

4.每个部门最高薪资人

select post,max(salary) from emp group by post;

5.每个部门平均薪资

select post,avg(salary) from emp group by post;

6.每个部门当月总共多少薪资

select post,sum(salary) from emp by post;

7.性别分组，男人有多少个，女人有多少个

select sex,count(id) from emp group by sex;

8.统计出每个部门年龄30以上的员工的平均薪资
第一步先把30岁以上的员工过滤出来

select * from emp where age >=30;

第二部分组部门，配合聚合函数

select post,avg(salary) from emp where age >=30 group by post;#where 必须在group by 前面 where不能与聚合函数连用

四：having过滤条件
where 是在分组之前的过滤，即在分组之前做了一次整体性的筛选
having 是在分组之后的 过滤 即在分组之后专门对聚合的结果进行的进一步筛选
例：
如每个部门的平均薪资为：

select post avg(salary) from emp group by post;

则：
1.筛选平均薪资大于一万的部门的平均薪资

select post avg(salary) from emp group by post having avg(salary)>10000;

2.筛选平均薪资大于10000且小于200000的部门的平均薪资

select post avg(salary) from emp group by post having avg(salary)>10000 and 
avg(salary) < 200000;

3.查询各个部门包含的员工小于2的岗位名和员工
group_concat 可以展示字段名

select post group_concat(name),count(id) from emp group by post having              
count (id)<2;

五.distinct 去重
1.查询平均薪资5000的部门并值展示一条
我们来分步写
1.1 先查询每个部门的平均薪资：

select post avg(salary) from emp group by post;

1.2 然后查询平均薪资为5000的部门

select post avg(salary) from emp group by post having avg(salary) =5000;

1.3最后去重

select distinct avg(salary) from emp group by post having avg(salary)=5000;

六.order by (排序)
1.年龄排序小到大

select * from emp order by age asc;

2.大到小

select * from emp order by age desc

3.先按照age 升序排列，如果age相同 则按照salary 降序排

select * from emp order by age asc,salary desc;

4.部门平均薪资的排序，因为是分组之后的排序

select post,avg(salary) from emp group by post order by avg(salary);

七. limit限制显示条数
1.整体显示3条

select * from limit 3;

2.薪资最高那个人的详细信息利用排序

select * from emp order by salary desc limit 1;

3.#分页显示

select * from emp limit 0,5;#从0开始查5个
select * from emp limit 5,5;
select * from emp limit 5,10;