第三次单表查询作业

mysql> create databases db_lianxi;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases db_lianxi' at line 1
mysql> create database db_lianxi;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_classics        |
| db_han             |
| db_lianxi          |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
9 rows in set (0.00 sec)

mysql> use db_lianxi
Database changed

mysql> 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,
    ->  PRIMARY KEY (`职工号`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 4 warnings (0.03 sec)

mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 部门号   | int         | NO   |     | NULL    |       |
| 职工号   | int         | NO   | PRI | NULL    |       |
| 工作时间 | date        | NO   |     | NULL    |       |
| 工资     | float(8,2)  | NO   |     | NULL    |       |
| 政治面貌 | varchar(10) | NO   |     | 群众    |       |
| 姓名     | varchar(20) | NO   |     | NULL    |       |
| 出生日期 | date        | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, ` 出生日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, ` 出生日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, ` 出生日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, ` 出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, ` 出生日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, ` 出生日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');
Query OK, 1 row affected (0.00 sec)

1.mysql> select * from worker;
+--------+--------+------------+---------+----------+------+------------+
| 部门号 | 职工号 | 工作时间   | 工资    | 政治面貌 | 姓名 | 出生日期   |
+--------+--------+------------+---------+----------+------+------------+
|    101 |   1001 | 2015-05-04 | 3500.00 | 群众     | 张三 | 1990-07-01 |
|    101 |   1002 | 2017-02-06 | 3200.00 | 团员     | 李四 | 1997-02-08 |
|    102 |   1003 | 2011-01-04 | 8500.00 | 党员     | 王亮 | 1983-06-08 |
|    102 |   1004 | 2016-10-10 | 5500.00 | 群众     | 赵六 | 1994-09-05 |
|    102 |   1005 | 2014-04-01 | 4800.00 | 党员     | 钱七 | 1992-12-30 |
|    102 |   1006 | 2017-05-05 | 4500.00 | 党员     | 孙八 | 1996-09-02 |
+--------+--------+------------+---------+----------+------+------------+
6 rows in set (0.00 sec)

mysql> select 部门号 from worker;
+--------+
| 部门号 |
+--------+
|    101 |
|    101 |
|    102 |
|    102 |
|    102 |
|    102 |
+--------+
6 rows in set (0.00 sec)

2.mysql> select distinct(部门号) from worker;
+--------+
| 部门号 |
+--------+
|    101 |
|    102 |
+--------+
2 rows in set (0.00 sec)

3.mysql> select count(*) from  worker;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

4.mysql> select max(工资),min(工资) from worker;
+-----------+-----------+
| max(工资) | min(工资) |
+-----------+-----------+
|   8500.00 |   3200.00 |
+-----------+-----------+
1 row in set (0.00 sec)

5.mysql> select avg(工资),sum(工资) from worker;
+-------------+-----------+
| avg(工资)   | sum(工资) |
+-------------+-----------+
| 5000.000000 |  30000.00 |
+-------------+-----------+
1 row in set (0.00 sec)
 

6.mysql> create table `工作日期`(
    -> `职工号` int(11) not null,
    -> `姓名` varchar(20) not null,
    -> `工作时间` date not null,
    -> primary key (`职工号`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

7.mysql>  select 2023-year(出生日期) from worker;
+---------------------+
| 2023-year(出生日期) |
+---------------------+
|                  33 |
|                  26 |
|                  40 |
|                  29 |
|                  31 |
|                  27 |
+---------------------+
6 rows in set (0.00 sec)

8.mysql> select 职工号,姓名,出生日期 from worker where 姓名 like '张%';
+--------+------+------------+
| 职工号 | 姓名 | 出生日期   |
+--------+------+------------+
|   1001 | 张三 | 1990-07-01 |
+--------+------+------------+
1 row in set (0.00 sec)

9.mysql>  select 姓名,工作时间 from worker where year(出生日期)<1996;
+------+------------+
| 姓名 | 工作时间   |
+------+------------+
| 张三 | 2015-05-04 |
| 王亮 | 2011-01-04 |
| 赵六 | 2016-10-10 |
| 钱七 | 2014-04-01 |
+------+------------+
4 rows in set (0.00 sec)

10.mysql> select 姓名 from worker where 工资 between 1000 and 5000;
+------+
| 姓名 |
+------+
| 张三 |
| 李四 |
| 钱七 |
| 孙八 |
+------+
4 rows in set (0.00 sec)

11.mysql>  select 姓名 from worker where 姓名 regexp '(^张|^李)';
+------+
| 姓名 |
+------+
| 张三 |
| 李四 |
+------+
2 rows in set (0.02 sec)

12.mysql> select 职工号,姓名,政治面貌 from worker where 部门号 like '%2' and 政治面貌 like '党员';
+--------+------+----------+
| 职工号 | 姓名 | 政治面貌 |
+--------+------+----------+
|   1003 | 王亮 | 党员     |
|   1005 | 钱七 | 党员     |
|   1006 | 孙八 | 党员     |
+--------+------+----------+
3 rows in set (0.00 sec)

13.mysql>  select * from worker order by 出生日期;
+--------+--------+------------+---------+----------+------+------------+
| 部门号 | 职工号 | 工作时间   | 工资    | 政治面貌 | 姓名 | 出生日期   |
+--------+--------+------------+---------+----------+------+------------+
|    102 |   1003 | 2011-01-04 | 8500.00 | 党员     | 王亮 | 1983-06-08 |
|    101 |   1001 | 2015-05-04 | 3500.00 | 群众     | 张三 | 1990-07-01 |
|    102 |   1005 | 2014-04-01 | 4800.00 | 党员     | 钱七 | 1992-12-30 |
|    102 |   1004 | 2016-10-10 | 5500.00 | 群众     | 赵六 | 1994-09-05 |
|    102 |   1006 | 2017-05-05 | 4500.00 | 党员     | 孙八 | 1996-09-02 |
|    101 |   1002 | 2017-02-06 | 3200.00 | 团员     | 李四 | 1997-02-08 |
+--------+--------+------------+---------+----------+------+------------+
6 rows in set (0.00 sec)

14.mysql> select * from worker order by 工资 desc limit 3;
+--------+--------+------------+---------+----------+------+------------+
| 部门号 | 职工号 | 工作时间   | 工资    | 政治面貌 | 姓名 | 出生日期   |
+--------+--------+------------+---------+----------+------+------------+
|    102 |   1003 | 2011-01-04 | 8500.00 | 党员     | 王亮 | 1983-06-08 |
|    102 |   1004 | 2016-10-10 | 5500.00 | 群众     | 赵六 | 1994-09-05 |
|    102 |   1005 | 2014-04-01 | 4800.00 | 党员     | 钱七 | 1992-12-30 |
+--------+--------+------------+---------+----------+------+------------+
3 rows in set (0.00 sec)

15.mysql> select 部门号,count(*) from worker where 政治面貌='党员' group by 部门号;
+--------+----------+
| 部门号 | count(*) |
+--------+----------+
|    102 |        3 |
+--------+----------+
1 row in set (0.00 sec)

16.mysql> select 部门号,sum(工资),avg(工资) from worker group by 部门号;
+--------+-----------+-------------+
| 部门号 | sum(工资) | avg(工资)   |
+--------+-----------+-------------+
|    101 |   6700.00 | 3350.000000 |
|    102 |  23300.00 | 5825.000000 |
+--------+-----------+-------------+
2 rows in set (0.00 sec)

17.mysql>  select 部门号,count(*) from worker group by 部门号 having count(*)>3;
+--------+----------+
| 部门号 | count(*) |
+--------+----------+
|    102 |        4 |
+--------+----------+
1 row in set (0.00 sec)