Mysql练习：单表查询

练习题

1.1 首先需要创建数据库：

 CREATE DATABASE IF NOT EXISTS Employees_db;

1.2  创建一个名为worker的表：

mysql> CREATE TABLE worker(
    -> part_id int(11) not null comment '部门号',
    -> work_id int(11) not null comment '职工号',
    -> work_time date not null comment '工作时间',
    -> salary float(8,2) not null comment '工资',
    -> politics_status varchar(20) not null comment '政治面貌',
    -> name varchar(20) not null comment '姓名',
    -> birth_date date not null comment '出生时间',
    -> primary key(work_id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 4 warnings (0.02 sec)

1.3 插入数据

INSERT INTO worker(part_id,work_id,work_time,salary,politics_status,name,birth_date) values(101,1001,'2015-5-4','3500.00','群众','张三','1990-7-1');
INSERT INTO worker(part_id,work_id,work_time,salary,politics_status,name,birth_date) values(101,1002,'2017-2-6','3200.00','团员','李四','1997-2-8');
 INSERT INTO worker(part_id,work_id,work_time,salary,politics_status,name,birth_date) values(102,1003,'2011-1-4','8500.00','党员','王亮','1983-6-8');
INSERT INTO worker(part_id,work_id,work_time,salary,politics_status,name,birth_date) values(102,1004,'2016-10-10','5500.00','群众','赵六','1994-9-5');
INSERT INTO worker(part_id,work_id,work_time,salary,politics_status,name,birth_date) values(102,1005,'2014-4-1','4800.00','党员','钱七','1992-12-30');
INSERT INTO worker(part_id,work_id,work_time,salary,politics_status,name,birth_date) values(102,1006,'2017-5-5','4500.00','党员','孙八','1996-9-2');

2.1查询

2.2.1 显示所有员工的基本信息。

SELECT *FROM worker;

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

SELECT DISTINCT part_id FROM worker;

2.2.3求出所有职工的人数。

SELECT count(work_id) as '职工人数' FROM worker;

2.2.4列出最高工和最低工资。 

SELECT min(salary) as '最低工资',max(salary) as '最高工资' FROM worker;

2.2.5列出职工的平均工资和总工资。 

SELECT avg(salary) as '平均工资',sum(salary) as '职工工资总数' FROM worker;

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

CREATE TABLE work_time SELECT work_id,name,work_time FROM worker;

2.2.7显示所有女职工的年龄。(添加字段：gender,age)

mysql> ALTER TABLE worker add gender char(1);
mysql> ALTER TABLE worker add age int;

然后给表中这两列输入一些数据：男='B',女='G';

mysql> UPDATE worker set gender='B';
mysql> UPDATE worker set gender='G';
mysql> UPDATE worker set age=20;
mysql> UPDATE worker set age=19 WHERE part_id=101;
mysql> UPDATE worker set gender='B' WHERE work_id=1001;
mysql> UPDATE worker set gender='B' WHERE work_id=1003;
mysql> UPDATE worker set gender='B' WHERE work_id=1005;

显示所有女职工的年龄

mysql> SELECT age FROM worker WHERE gender='G';

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

SELECT work_id,name,birth_date FROM worker where name like '刘%';

2.2.9列出1960年以前出生的职工的姓名、参加工作日期。

SELECT name,work_time FROM worker WHERE birth_date < '1960-1-1';

2.2.10列出工资在1000－2000之间的所有职工姓名。

SELECT name FROM worker WHERE salary>=1000 and salary<=2000;

 
2.2.11列出所有陈姓和李姓的职工姓名。

SELECT name FROM worker WHERE name like '陈%' or name like '李%';

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

SELECT work_id,name,politics_status FROM worker WHERE part_id regexp '10[23]'
and politics_status!='党员';

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

SELECT * FROM worker order by birth_date;

2.2.14显示工资最高的前3名职工的职工号和姓名。 

 SELECT work_id,name,salary FROM worker order by salary desc limit 3;

2.2.15求出各部门党员的人数。

SELECT part_id as '部门',count(politics_status) as '党员人数' FROM worker group by part_id;

 
2.2.16统计各部门的工资和平均工资

SELECT part_id as '部门',avg(salary) as '平均工资',sum(salary) as '总工资' FROM worker group by part_id;

2.2.17列出总人数大于4的部门号和总人数。

SELECT part_id as '部门',count(work_id) as '人数' FROM worker group by part_id having count(work_id)>=4;