MySQL查询

以下是每个查询的MySQL代码

(1)显示所有职工的基本信息

SELECT * FROM t_worker;

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

SELECT DISTINCT department_id FROM t_worker;

(3）求出所有职工的人数

SELECT COUNT *)AS total_workersFROM t_worker;

(4）列出最高工资和最低工资

最高工资

SELECT MAX (wages)AS highest_wageFROM t_worker;

最低工资

SELECT MIN(wages) AS lowest_wageFROM t_worker;

(5）列出职工的平均工资和总工资

平均工资

SELECT AVG(wages) AS average_wageFROM t_worker;

总工资

SELECT SUM(wages)AS total_wageFROM t_worker;

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

CREATE TABLE work_date_table AS

SELECT worker_id, name,worker_dateFROM t_worker;

(7）显示所有党员的年龄

SELECT TIMESTAMPDIFF(YEAR,borth_date,CURDATE))AS ageFROM t_worker

WHERE politics = '党员;

(8）列出工资在4000 - 8000之间的所有职工姓名

SELECT name

FROM t_worker

WHERE wages BETWEEN 4000 AND 8000;

(9)列出所有孙姓和李姓的职工姓名

SELECT name

FROM t_worker

WHERE name LIKE '孙%’ OR name LIKE'李%;

(10）列出所有部门号为102和103且不是党员的职工号、姓名

SELECT worker_id, name

FROM t_worker

WHERE ( department_id = 102 OR department_id =103）AND politics !='党员;

(11）将职工表t_worker中的职工按出生的先后顺序排序

SELECT * FROM t_worker ORDER BY borth_date;

(12)显示工资最高的前3名职工的职工号和姓名

SELECT worker_id, name

FROM t_worker

ORDER BY wages DESCLIMIT 3;

(13)求出各部门党员的人数

SELECT department_id,COUNT(*）ASnum_of_party_members 

FROM t_worker

WHERE politics = '党员' GROUP BY department_id;

(14）统计各部门的工资和平均工资并保留2位小数

SELECT department_id,

        SUM(wages)AS total_wage,ROUND（ AVG(wages),2)AS

average__wage

FROM t_worker

GROUP BY department_id;

(15)列出总人数大于等于3的部门号和总人数

SELECT department_id,COUNT( *)AStotal_workers FROM t__worker GROUP BY department_idHAVING COUNT(*)>= 3;