sql 语句练习 In MySQL

本人使用MySQL数据库，

<1>  练习group By

 

首先创建2个表

 

 

 

create table department(department_id int,department_name varchar(10)) ;

create table staff(id int,name varchar(20),sex varchar(20),department_id int) 

插入数据

 

insert into department select 1,'d1'; 
insert into department select 2,'d2' ;
 insert into department select 3,'d3' ;
 insert into department select 4,'d4' ; 
insert into staff select 1,'qjy1','m',1; 
insert into staff select 2,'qjy2' ,'m',1 ; 
insert into staff select 3,'qjy3','m' ,1; 
insert into staff select 4,'qjy4' ,'m' ,1; 
insert into staff select 5,'qjy1','m',1; 
insert into staff select 6,'qjy2' ,'m',1 ; 
insert into staff select 7,'qjy3','m' ,1;
 insert into staff select 8,'qjy4' ,'m' ,1;
 insert into staff select 9,'qjy1','m',2; 
insert into staff select 10,'qjy2' ,'m',2 ; 
insert into staff select 11,'qjy3','m' ,2; 
insert into staff select 12,'qjy4' ,'m' ,4; 
insert into staff select 13,'qjy1','m',4; 
insert into staff select 14,'qjy2' ,'m',4 ;
 insert into staff select 15,'qjy3','m' ,4; 
insert into staff select 16,'qjy4' ,'m' ,4; 

  

需求：查出 部门信息 ，要求查询每个部门的员工数，并按照统计的员工数倒序排列，不用select 子查询

 

  

select d.*,count(*) as staffCount from staff as s join department as d on s.department_id = d.department_id group by d.department_id having count(*)>1 order by staffCount DESC

 

查询结果：

query result(3 records)

 

department_id	department_name	staffCount
1	d1	8
4	d4	5
2	d2	3

 

联系目的 ：

1.熟悉group by ..having

2.table,列 取别名 ，以及别名的用处 ：order by staffCount DESC

3.升序 ASC ，倒序是 DESC

 

<2> avg函数 ，及

 

 

id	sname	smoney	sprovince
1	zhangsan	2098	A
2	lisi	3000	B
3	wangwu	6789	C
4	liumazi	4587	C
5	dongjiu	3298	B
6	shiga	4567	A

  

id:合同id  sname:姓名     smoney :业绩     sprovince:地区

 第一道：显示出  业绩 大于同一地区平均值的 合同id  姓名 地区 业绩

 第二道：把同一地区的  平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）

 

 

1. select t1.* ,avgTemp from mytable as t1 join ( select avg(m.smoney) as avgTemp, m.sprovince from mytable as m group by m.sprovince) as t2 where t1.smoney > avgTemp and t1.sprovince = t2.sprovince; 2. create table newTable (avgMoney int ,sprovince varchar(20)); insert into newTable select avg(m.smoney) , m.sprovince from mytable as m group by m.sprovince