sql作业

CREATE DATABASE workers;

use workers;

 

CREATE table worker(

`部门号` int (11) not NULL,

`职工号` int(11) not NULL,

`工作时间` date not NULL,

`工资` float(8,2)not null,

`政治面貌` varchar(10) not null default '群众',

`姓名` varchar(20) not null,

`出生日期` date not null,

`性别` char(1) not NULL,

PRIMARY KEY (`职工号`)

)engine = INNODB DEFAULT charset=utf8 row_format=dynamic;

 

insert into worker (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`性别`)VALUES(101,1001,'2015-5-4',3500.00,'群众','张三','1997-7-1','男');

insert into worker (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`性别`)VALUES(101,1002,'2017-2-6',3200.00,'团员','李四','1997-2-8','男');

insert into worker (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`性别`)VALUES(102,1003,'2011-1-4',8500.00,'党员','王亮','1983-6-8','男');

insert into worker (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`性别`)VALUES(102,1004,'2016-10-10',5500.00,'群众','赵六','1994-9-5','女');

insert into worker (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`性别`)VALUES(102,1005,'2014-4-1',4800.00,'党员','钱七','1992-12-30','女');

insert into worker (`部门号`,`职工号`,`工作时间`,`工资`,`政治面貌`,`姓名`,`出生日期`,`性别`)VALUES(102,1006,'2017-5-5',4500.00,'党员','孙八','1996-9-2','女');

-- 1.显示所有职工的基本信息。

select * from worker;

 

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

select DISTINCT `部门号` from worker ;

 

-- 3.求出所有职工的人数。

select count(*) as 总人数 from worker;

 

-- 4.列出最高工和最低工资。

select MAX(`工资`) as 最高工资 , MIN(`工资`) as 最低工资 from worker;

 

-- 5.列出职工的平均工资和总工资。

select AVG(`工资`) 平均工资 , SUM(`工资`) 总工资 FROM worker;

 

-- 6.创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

create table `工作日期表` SELECT `职工号`,`姓名`,`工作时间` FROM worker;

select * FROM `工作日期表`;

 

-- 7.显示所有女职工的年龄。

select * from worker where `性别` = '女';

-- SELECT `姓名`, `出生日期`, TIMESTAMPDIFF(YEAR, `出生日期`, CURDATE()) AS age FROM worker;

 

-- 8.列出所有姓刘的职工的职工号、姓名和出生日期。

select `职工号`,`姓名`,`出生日期` from worker where `姓名` LIKE '刘%';

-- 9.列出1960年以前出生的职工的姓名、参加工作日期和出生日期。

select `姓名`,`工作时间` ,`出生日期`FROM worker where `出生日期` < '1960-01-01';

 

 

-- 10.列出工资在1000一2000之间的所有职工姓名以及工资。

select `姓名`,`工资` from `worker` where `工资` between 1000 and 4000;

 

-- 11.列出所有张姓和李姓的职工姓名。

select `姓名` FROM worker WHERE `姓名`  LIKE '张%' or `姓名` like '李%';

-- 12.列出所有部门号为2和3的职工号、姓名、党员否

select `职工号`,`姓名`,`政治面貌`from worker where (`部门号` = 102 or `部门号` = 103 )and `政治面貌` = '党员';

select `职工号`,`姓名`,`政治面貌`from worker where `部门号` = 102 or `部门号` = 103;

select `职工号`,`姓名`,`政治面貌`from worker where `部门号` IN(102,103) AND `政治面貌` = '党员';

 

-- 13、将职工表worker中的职工按出生的先后顺序排序。

select * from worker ORDER BY `出生日期`;

 

-- 14、显示工资最高的前3名职工的职工号和姓名。

select `职工号`,`姓名` ,`工资`FROM worker ORDER BY `工资`DESC LIMIT 3;

 

-- 15.求出各部门党员的人数。

SELECT `部门号`,count(*)  as 党员人数 from worker where `政治面貌` = '党员'  GROUP BY `部门号` ;

-- 16.统计各部门的工资和平均工资

SELECT `部门号`, SUM(`工资`) AS `工资总和`, AVG(`工资`) AS `平均工资` FROM worker GROUP BY `部门号`;

 

 

-- 17.列出总人数大于4的部门号和总人数。

SELECT `部门号`, COUNT(*) AS `总人数` FROM worker GROUP BY `部门号` HAVING COUNT(*)  > 1;