【无标题】

1111111111111
mysql> insert into emp values
-> (1789,‘张三’,35,‘1980/1/1’,4000,101),
-> (1674,‘李四’,32,‘1983/4/1’,3500,101),
-> (1776,‘王五’,24,‘1990/7/1’,2000,101),
-> (1568,‘赵六’,57,‘1970/10/11’,7500,102),
-> (1564,‘荣七’,64,‘1963/10/11’,8500,102),
-> (1879,‘牛八’,55,‘1971/10/20’,7300,103),
-> (1668,‘钱九’,64,‘1963/5/4’,8000,102),
-> (1724,‘武十’,22,‘2023/5/8’,1500,103),
-> (1770,‘孙二’,65,‘1986/8/12’,9500,101),
-> (18400,‘苟一’,65,‘1986/8/12’,1500,101);
Query OK, 10 rows affected, 10 warnings (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 10
mysql> select * from emp
-> ;
±------±-----±-----±---------------±---------±------+
| sid | name | age | worktime_start | incoming | dept2 |
±------±-----±-----±---------------±---------±------+
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
| 1668 | 钱九 | 64 | 1963-05-04 | 8000 | 102 |
| 1724 | 武十 | 22 | 2023-05-08 | 1500 | 103 |
| 1770 | 孙二 | 65 | 1986-08-12 | 9500 | 101 |
| 18400 | 苟一 | 65 | 1986-08-12 | 1500 | 101 |
±------±-----±-----±---------------±---------±------+
10 rows in set (0.00 sec)

mysql> select name from emp where dept2=102 order by age desc limit 1;
±-----+
| name |
±-----+
| 钱九 |
±-----+
1 row in set (0.00 sec)
222222222222222
mysql> select name from emp where dept2=101 order by incoming asc limit 1;
±-----+
| name |
±-----+
| 苟一 |
±-----+
1 row in set (0.00 sec)
333333333333333
mysql> select dept_name from (select dept2,sum(incoming) as total_income from emp group by dept2) as dept_income join dept on dept.dept1 = dept_income.dept2 where total_income >9000;
±----------+
| dept_name |
±----------+
| 财务 |
| 销售 |
±----------+
2 rows in set (0.00 sec)
44444444444444444
mysql> select name,dept_name from emp join dept on emp.dept2 = dept.dept1 where incoming between 7500 and 8500 order by
age desc limit 1;
±-----±----------+
| name | dept_name |
±-----±----------+
| 钱九 | 销售 |
±-----±----------+
1 row in set (0.00 sec)
55555555555555
mysql> select worktime_start from emp where dept2 = 102 order by ‘incominng’ asc limit 1;
±---------------+
| worktime_start |
±---------------+
| 1970-10-11 |
±---------------+
1 row in set (0.00 sec)
666666666666666
mysql> select name from emp where dept2 = 101 and incoming >2000;
±-----+
| name |
±-----+
| 张三 |
| 李四 |
| 孙二 |
±-----+
3 rows in set (0.00 sec)
777777777777777
mysql> select dept_name,avg(incoming) from emp join dept on emp.dept2 =dept.dept1 group by dept_name;
±----------±--------------+
| dept_name | avg(incoming) |
±----------±--------------+
| 财务 | 4100.0000 |
| 销售 | 8000.0000 |
| IT技术 | 4400.0000 |
±----------±--------------+
3 rows in set (0.00 sec)
88888888888888
mysql> select sid from emp where dept2 = 103;
±-----+
| sid |
±-----+
| 1879 |
| 1724 |
±-----+
2 rows in set (0.00 sec)
99999999999999
mysql> select sum(incoming)
-> from emp where dept2=101;
±--------------+
| sum(incoming) |
±--------------+
| 20500 |
±--------------+
1 row in set (0.00 sec)
1010101010101010010
mysql> select dept_name from dept where dept1 not in (select dept2 from emp)
-> ;
±----------+
| dept_name |
±----------+
| 行政 |
±----------+
1 row in set (0.00 sec)
**11 11 11 11 1111 11
mysql> select dept1,dept_name from(select dept2,max(incoming) as max_income from emp group by dept2)
-> as dept_max_income join dept on dept.dept1 = dept_max_income.dept2 where max_income >7000
-> ;
±------±----------+
| dept1 | dept_name |
±------±----------+
| 101 | 财务 |
| 102 | 销售 |
| 103 | IT技术 |
±------±----------+
3 rows in set (0.00 sec)
12 12 12 12 12 12
mysql> select dept_name,sum(incoming) as total_income from emp join dept on emp.dept2 = dept.dept1 group by dept_name;
±----------±-------------+
| dept_name | total_income |
±----------±-------------+
| 财务 | 20500 |
| 销售 | 24000 |
| IT技术 | 8800 |
±----------±-------------+
3 rows in set (0.00 sec)
13 13 13 13 13
mysql> select e.name,d.dept_name from( select max(age) as max_age,dept2 from emp group by dept2) as max_age_emp join emp as e on max_age_emp.max_age = e.age and max_age_emp.dept2 = e.dept2 join dept as d on e.dept2 = d.dept1;
±-----±----------+
| name | dept_name |
±-----±----------+
| 荣七 | 销售 |
| 牛八 | IT技术 |
| 钱九 | 销售 |
| 孙二 | 财务 |
| 苟一 | 财务 |
±-----±----------+
5 rows in set (0.00 sec)
141414141414
mysql> select name,dept_name, incoming from emp join dept on emp.dept2 = dept.dept1 where name = ‘李四’;
±-----±----------±---------+
| name | dept_name | incoming |
±-----±----------±---------+
| 李四 | 财务 | 3500 |
±-----±----------±---------+
1 row in set (0.00 sec)
15 15 15 15 15 15
mysql> select e.name , d.dept_name,e.incoming
-> from ( select max(incoming) as max_income,dept2 from emp group by dept2 )
-> as max_income_emp join emp as e on max_income_emp.max_income = e.incoming and max_income_emp.dept2=e.dept2
-> join dept as d on e.dept2 = d.dept1
-> order by e.incoming desc;
±-----±----------±---------+
| name | dept_name | incoming |
±-----±----------±---------+
| 孙二 | 财务 | 9500 |
| 荣七 | 销售 | 8500 |
| 牛八 | IT技术 | 7300 |
±-----±----------±---------+
3 rows in set (0.00 sec)