MySQL操作（查询练习）

一。创建表：

mysql> create table t_worker(
    -> department_id int(11) not null,
    -> worker_id int(11) primary key not null,
    -> worker_date date not null,
    -> wages float(8,2) not null,
    -> politics varchar(10) not null default '群众',
    -> name varchar(20) not null,
    -> borth_date date not null);

二。插入数据：

insert into t_worker values(101,1001,'2015-5-4',7500.00,'群众','张春燕','1990-7-1');
insert into t_worker values(101,1002,'2019-2-6',5200.00,'团员','李名博','1997-2-8');
insert into t_worker values(102,1003,'2008-1-4',10500.00,'党员','王博涵','1983-6-8');
insert into t_worker values(102,1004,'2016-10-10',5500.00,'群众','赵小军','1994-9-5');
insert into t_worker values(102,1005,'2014-4-1',8800.00,'党员','钱有财','1992-12-30');
insert into t_worker values(103,1006,'2019-5-5',5500.00,'党员','孙菲菲','1996-9-2');

三。查找数据

1.查询所有职工所属部门的部门号，不显示重复的部门号

mysql> select distinct department_id from t_worker;

2.求出所有职工的人数

mysql> select count(name) from t_worker;

3.列出最高工资和最低工资

mysql> select max(wages),min(wages) from t_worker;

4.列出职工的平均成绩和总工资

mysql> select round(avg(wages),2) as "平均工资",sum(wages) as "总工资" from t_worker;

5.创建一个只有职工号，姓名和参加工作的新表，名字为工作日期表

mysql> create table wokr_date(
    -> worker_id int(11) primary key not null,
    -> name varchar(20) not null,
    -> department_id int(11) not null);

6.显示所有党员的年龄

mysql> select name,year(now())- year(worker_date) as "年龄" from t_worker;

7.列出工资在4000-8000之间的所有职工姓名

mysql> select name from t_worker where wages between 4000 and 8000;

8.列出所有孙姓和李姓的职工信息

mysql> select name from t_worker where name like "李%" or name like "孙%";

9.列出所有部门号为102和103且不是党员的职工号和姓名

mysql> select worker_id,name from t_worker where department_id in(102,103) and politics in("群众","团员");

10.将职工表t_worker中的职工按照出生的先后顺序排序

mysql> select * from t_worker order by worker_date asc;

11.显示工资最高的前3名职工的职工号和姓名

mysql> select worker_id,name from t_worker order by wages desc limit 0,3;

12.求出各部门的党员人数

mysql> select  department_id as "部门", COUNT(*) as "党员人数" from t_worker where politics = '党员' group by department_id;

13.统计各部门的工资和平均工资并且保留两位小数

select department_id, sum(wages) as "总工资", round(avg(wages), 2) as "平均工资" from t_worker group by department_id;

14.列出总人数大于等于3的部门号和总人数

mysql> select department_id,count(*) from t_worker group by department_id having count(*) >= 3;