一、单表查询
素材: 表名:worker-- 表中字段均为中文,比如 部门号 工资 职工号 参加工作 等
CREATE TABLE `worker` (
`部门号` int(11) NOT NULL,
`职工号` int(11) NOT NULL auto_increment,
`工作时间` date NOT NULL,
`工资` float(8,2) NOT NULL,
`政治面貌` varchar(10) NOT NULL DEFAULT '群众',
`姓名` varchar(20) NOT NULL,
`出生日期` date NOT NULL,
`年龄` int not null,
`性别` char(1) not null,
PRIMARY KEY (`职工号`)
)auto_increment=1001 ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
insert into worker values(101, 1001,'2015-5-4',3500.00,'群众','张三','1990-7-1',33,'M');
INSERT INTO worker VALUES (102, 1003,'2011-1-4',8500.00,'党员','刘晓芳','1983-6-8',40, 'F');
INSERT INTO worker VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5', 29, 'M');
INSERT INTO worker VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8', 26, 'M');
INSERT INTO worker VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '张巧巧', '1996-9-2', 27, 'F');
INSERT INTO worker VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30', 31, 'M');
INSERT INTO worker VALUES (102,null ,'2013-7-23',7500.00,'党员','李平','1996-2-8',27,'M');
INSERT INTO worker VALUES (103,null, '2017-2-4',15000.00,'团员','华轩','1992-3-4',31,'F');
INSERT INTO worker VALUES (104,null ,'2016-5-17',3200.00,'群众','胡光','1999-2-8',24,'M');
INSERT INTO worker VALUES (103,null ,'2021-9-27',12500.00,'群众','孙芳芳','2000-4-27',23,'F');
INSERT INTO worker VALUES (103,null ,'2015-8-24',13500.00,'党员','刘海','1987-4-9',36,'M');
INSERT INTO worker VALUES (104,null,'2020-7-8', 4000.00,'团员','孙甜','1999-5-30',24,'F');
INSERT INTO worker VALUES (103,null ,'2010-4-4',14000.00,'团员','赵一辰','1995-9-27',28,'M');
INSERT INTO worker VALUES (102,null ,'2010-11-1',6500.00,'党员','马小宁','1989-9-8',34,'F');
INSERT INTO worker VALUES (101,NULL, '2012-5-14',3300.00,'群众','王亮','1997-6-8',26,'M');
INSERT INTO worker VALUES (101,null ,'2012-5-28',3400.00,'群众','丁四海','1997-10-18',26,'M');
1、显示所有职工的基本信息。
mysql> select *from worker;
+-----------+-----------+--------------+----------+--------------+-----------+--------------+--------+--------+
| 部门号 | 职工号 | 工作时间 | 工资 | 政治面貌 | 姓名 | 出生日期 | 年龄 | 性别 |
+-----------+-----------+--------------+----------+--------------+-----------+--------------+--------+--------+
| 101 | 1001 | 2015-05-04 | 3500.00 | 群众 | 张三 | 1990-07-01 | 33 | M |
| 101 | 1002 | 2017-02-06 | 3200.00 | 团员 | 李四 | 1997-02-08 | 26 | M |
| 102 | 1003 | 2011-01-04 | 8500.00 | 党员 | 刘晓芳 | 1983-06-08 | 40 | F |
| 102 | 1004 | 2016-10-10 | 5500.00 | 群众 | 赵六 | 1994-09-05 | 29 | M |
| 102 | 1005 | 2014-04-01 | 4800.00 | 党员 | 钱七 | 1992-12-30 | 31 | M |
| 102 | 1006 | 2017-05-05 | 4500.00 | 党员 | 张巧巧 | 1996-09-02 | 27 | F |
| 102 | 1007 | 2013-07-23 | 7500.00 | 党员 | 李平 | 1996-02-08 | 27 | M |
| 103 | 1008 | 2017-02-04 | 15000.00 | 团员 | 华轩 | 1992-03-04 | 31 | F |
| 104 | 1009 | 2016-05-17 | 3200.00 | 群众 | 胡光 | 1999-02-08 | 24 | M |
| 103 | 1010 | 2021-09-27 | 12500.00 | 群众 | 孙芳芳 | 2000-04-27 | 23 | F |
| 103 | 1011 | 2015-08-24 | 13500.00 | 党员 | 刘海 | 1987-04-09 | 36 | M |
| 104 | 1012 | 2020-07-08 | 4000.00 | 团员 | 孙甜 | 1999-05-30 | 24 | F |
| 103 | 1013 | 2010-04-04 | 14000.00 | 团员 | 赵一辰 | 1995-09-27 | 28 | M |
| 102 | 1014 | 2010-11-01 | 6500.00 | 党员 | 马小宁 | 1989-09-08 | 34 | F |
| 101 | 1015 | 2012-05-14 | 3300.00 | 群众 | 王亮 | 1997-06-08 | 26 | M |
| 101 | 1016 | 2012-05-28 | 3400.00 | 群众 | 丁四海 | 1997-10-18 | 26 | M |
+-----------+-----------+--------------+----------+--------------+-----------+--------------+--------+--------+
16 rows in set (0.00 sec)
2、查询所有职工所属部门的部门号,不显示重复的部门号。
mysql> select distinct 部门号 from worker;
+-----------+
| 部门号 |
+-----------+
| 101 |
| 102 |
| 103 |
| 104 |
+-----------+
4 rows in set (0.00 sec)
3、求出所有职工的人数。
mysql> select count(*) from worker;
+----------+
| count(*) |
+----------+
| 16 |
+----------+
1 row in set (0.01 sec)
4、列出最高工和最低工资。
mysql> select min(工资),max(工资) from worker ;
+-------------+-------------+
| min(工资) | max(工资) |
+-------------+-------------+
| 3200.00 | 15000.00 |
+-------------+-------------+
1 row in set (0.00 sec)
5、列出职工的平均工资和总工资。
mysql> select avg(工资),sum(工资) from worker ;
+-------------+-------------+
| avg(工资) | sum(工资) |
+-------------+-------------+
| 7056.250000 | 112900.00 |
+-------------+-------------+
1 row in set (0.00 sec)
6、创建一个只有职工号、姓名和参加工作的新表,名为工作日期表。
mysql> create table workertime( 职工号 int not null, 姓名 varchar(30) not null, 工作时间 date not null );
Query OK, 0 rows affected (0.03 sec)
mysql> desc workertime;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 职工号 | int | NO | | NULL | |
| 姓名 | varchar(30) | NO | | NULL | |
| 工作时间 | date | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
7、显示所有女职工的年龄。
mysql> select 姓名,年龄 from worker where 性别='F';
+-----------+--------+
| 姓名 | 年龄 |
+-----------+--------+
| 刘晓芳 | 40 |
| 张巧巧 | 27 |
| 华轩 | 31 |
| 孙芳芳 | 23 |
| 孙甜 | 24 |
| 马小宁 | 34 |
+-----------+--------+
6 rows in set (0.00 sec)
8、列出所有姓刘的职工的职工号、姓名和出生日期。
mysql> select 职工号,姓名,出生日期 from worker where 姓名 like '刘%';
mysql> select 职工号,姓名,出生日期 from worker where 姓名 regexp '^刘';
+-----------+-----------+--------------+
| 职工号 | 姓名 | 出生日期 |
+-----------+-----------+--------------+
| 1003 | 刘晓芳 | 1983-06-08 |
| 1011 | 刘海 | 1987-04-09 |
+-----------+-----------+--------------+
2 rows in set (0.00 sec)
9、列出1990年以前出生的职工的姓名、参加工作日期。
mysql> select 姓名,工作时间,出生日期 from worker where 年龄>33;
+-----------+--------------+--------------+
| 姓名 | 工作时间 | 出生日期 |
+-----------+--------------+--------------+
| 刘晓芳 | 2011-01-04 | 1983-06-08 |
| 刘海 | 2015-08-24 | 1987-04-09 |
| 马小宁 | 2010-11-01 | 1989-09-08 |
+-----------+--------------+--------------+
3 rows in set (0.00 sec)
10、列出工资在3000-4000之间的所有职工姓名。
mysql> select 姓名 from worker where 工资>3000 and 工资<4000;
+-----------+
| 姓名 |
+-----------+
| 张三 |
| 李四 |
| 胡光 |
| 王亮 |
| 丁四海 |
+-----------+
5 rows in set (0.00 sec)
11、列出所有陈姓和李姓的职工姓名。
mysql> select 姓名 from worker where 姓名 like '陈%' or 姓名 like '李%';
+--------+
| 姓名 |
+--------+
| 李四 |
| 李平 |
+--------+
2 rows in set (0.00 sec)
12、列出所有部门号为102和103的职工号、姓名、党员否。
mysql> select 职工号,姓名,政治面貌 from worker where 职工号=1002 or 职工号=1003 ;
+-----------+-----------+--------------+
| 职工号 | 姓名 | 政治面貌 |
+-----------+-----------+--------------+
| 1002 | 李四 | 团员 |
| 1003 | 刘晓芳 | 党员 |
+-----------+-----------+--------------+
2 rows in set (0.00 sec)
13、将职工表worker中的职工按出生的先后顺序排序。
mysql> select * from worker order by 年龄 desc;
mysql> select * from worker order by 出生日期 ;
+-----------+-----------+--------------+----------+--------------+-----------+--------------+--------+--------+
| 部门号 | 职工号 | 工作时间 | 工资 | 政治面貌 | 姓名 | 出生日期 | 年龄 | 性别 |
+-----------+-----------+--------------+----------+--------------+-----------+--------------+--------+--------+
| 102 | 1003 | 2011-01-04 | 8500.00 | 党员 | 刘晓芳 | 1983-06-08 | 40 | F |
| 103 | 1011 | 2015-08-24 | 13500.00 | 党员 | 刘海 | 1987-04-09 | 36 | M |
| 102 | 1014 | 2010-11-01 | 6500.00 | 党员 | 马小宁 | 1989-09-08 | 34 | F |
| 101 | 1001 | 2015-05-04 | 3500.00 | 群众 | 张三 | 1990-07-01 | 33 | M |
| 103 | 1008 | 2017-02-04 | 15000.00 | 团员 | 华轩 | 1992-03-04 | 31 | F |
| 102 | 1005 | 2014-04-01 | 4800.00 | 党员 | 钱七 | 1992-12-30 | 31 | M |
| 102 | 1004 | 2016-10-10 | 5500.00 | 群众 | 赵六 | 1994-09-05 | 29 | M |
| 103 | 1013 | 2010-04-04 | 14000.00 | 团员 | 赵一辰 | 1995-09-27 | 28 | M |
| 102 | 1007 | 2013-07-23 | 7500.00 | 党员 | 李平 | 1996-02-08 | 27 | M |
| 102 | 1006 | 2017-05-05 | 4500.00 | 党员 | 张巧巧 | 1996-09-02 | 27 | F |
| 101 | 1002 | 2017-02-06 | 3200.00 | 团员 | 李四 | 1997-02-08 | 26 | M |
| 101 | 1015 | 2012-05-14 | 3300.00 | 群众 | 王亮 | 1997-06-08 | 26 | M |
| 101 | 1016 | 2012-05-28 | 3400.00 | 群众 | 丁四海 | 1997-10-18 | 26 | M |
| 104 | 1009 | 2016-05-17 | 3200.00 | 群众 | 胡光 | 1999-02-08 | 24 | M |
| 104 | 1012 | 2020-07-08 | 4000.00 | 团员 | 孙甜 | 1999-05-30 | 24 | F |
| 103 | 1010 | 2021-09-27 | 12500.00 | 群众 | 孙芳芳 | 2000-04-27 | 23 | F |
+-----------+-----------+--------------+----------+--------------+-----------+--------------+--------+--------+
16 rows in set (0.00 sec)
14、显示工资最高的前3名职工的职工号和姓名。
mysql> select 职工号,姓名 from worker order by 工资 desc limit 0,3;
+-----------+-----------+
| 职工号 | 姓名 |
+-----------+-----------+
| 1008 | 华轩 |
| 1013 | 赵一辰 |
| 1011 | 刘海 |
+-----------+-----------+
3 rows in set (0.00 sec)
15、求出各部门党员的人数。
mysql> select 部门号,count(部门号) from worker group by 部门号;
+-----------+------------------+
| 部门号 | count(部门号) |
+-----------+------------------+
| 101 | 4 |
| 102 | 6 |
| 103 | 4 |
| 104 | 2 |
+-----------+------------------+
4 rows in set (0.00 sec)
16、统计各部门的工资和平均工资
ysql> select 部门号,sum(工资),avg(工资) from worker group by 部门号;
+-----------+-------------+--------------+
| 部门号 | sum(工资) | avg(工资) |
+-----------+-------------+--------------+
| 101 | 13400.00 | 3350.000000 |
| 102 | 37300.00 | 6216.666667 |
| 103 | 55000.00 | 13750.000000 |
| 104 | 7200.00 | 3600.000000 |
+-----------+-------------+--------------+
4 rows in set (0.00 sec)
17、列出总人数大于4的部门号和总人数。
mysql> select 部门号,count(部门号) from worker group by 部门号 having count(部门号)>4;
+-----------+------------------+
| 部门号 | count(部门号) |
+-----------+------------------+
| 102 | 6 |
+-----------+------------------+
1 row in set (0.00 sec)