mysql表查询 15 赵清野 +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ | employee_id | name | gender | dept_id | salary | phone | address | description | join_time | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ | 2 | xh | F | CW | 3252.8 | 12345678901 | beijing | good | 2012-12-26 | | 1 | xm | M | CW | 3552.8 | 12345678901 | beijing | good | 2012-12-26 | | 3 | xg | M | CW | 4252.3 | 12345678901 | beijing | good | 2012-12-24 | | 4 | xy | F | CW | 3472.2 | 12345678901 | beijing | good | 2012-12-24 | | 5 | lm | M | KF | 2472.2 | 12345678901 | beijing | good | 2012-12-24 | | 6 | wa | F | KF | 5472.2 | 12345678901 | beijing | good | 2012-12-24 | | 7 | hg | M | KF | 3122.2 | 12345678901 | beijing | good | 2012-12-24 | | 8 | fg | F | KF | 7122.2 | 12345678901 | beijing | good | 2012-12-24 | | 9 | ll | M | SC | 4122.2 | 12345678901 | beijing | good | 2012-12-24 | | 10 | lt | F | SC | 6522.2 | 12345678901 | beijing | good | 2012-12-24 | | 11 | lr | M | SC | 7122.2 | 12345678901 | beijing | good | 2012-12-24 | | 12 | zr | F | SC | 4422.2 | 12345678901 | beijing | good | 2012-12-24 | | 13 | zh | M | RS | 2422.2 | 12345678901 | beijing | good | 2012-12-24 | | 14 | ch | F | RS | 2522.2 | 12345678901 | beijing | good | 2012-12-24 | | 15 | xh | F | RS | 3122.2 | 12345678901 | beijing | good | 2012-12-24 | | 16 | sc | M | RS | 3022.2 | 12345678901 | beijing | good | 2012-12-24 | | 17 | jk | M | RS | 4022.2 | 12345678901 | beijing | NULL | 2012-12-24 | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ 查找为姓名为ch mysql> select * from work.a where name='ch'; +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ | employee_id | name | gender | dept_id | salary | phone | address | description | join_time | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ | 14 | ch | F | RS | 2522.2 | 12345678901 | beijing | good | 2012-12-24 | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ 1 row in set (0.00 sec)
描述为空 mysql> insert into work.a values (17,'jk','M','RS',4022.2,12345678901,'beijing','NULL','2012-12-24'); Query OK, 1 row affected (0.00 sec)
17 | jk | M | RS | 4022.2 | 12345678901 | beijing | NULL | 2012-12-24 | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+
工资大于3000的人员 mysql> select * from work.a where salary>3000; +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ | employee_id | name | gender | dept_id | salary | phone | address | description | join_time | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ | 2 | xh | F | CW | 3252.8 | 12345678901 | beijing | good | 2012-12-26 | | 1 | xm | M | CW | 3552.8 | 12345678901 | beijing | good | 2012-12-26 | | 3 | xg | M | CW | 4252.3 | 12345678901 | beijing | good | 2012-12-24 | | 4 | xy | F | CW | 3472.2 | 12345678901 | beijing | good | 2012-12-24 | | 6 | wa | F | KF | 5472.2 | 12345678901 | beijing | good | 2012-12-24 | | 7 | hg | M | KF | 3122.2 | 12345678901 | beijing | good | 2012-12-24 | | 8 | fg | F | KF | 7122.2 | 12345678901 | beijing | good | 2012-12-24 | | 9 | ll | M | SC | 4122.2 | 12345678901 | beijing | good | 2012-12-24 | | 10 | lt | F | SC | 6522.2 | 12345678901 | beijing | good | 2012-12-24 | | 11 | lr | M | SC | 7122.2 | 12345678901 | beijing | good | 2012-12-24 | | 12 | zr | F | SC | 4422.2 | 12345678901 | beijing | good | 2012-12-24 | | 15 | xh | F | RS | 3122.2 | 12345678901 | beijing | good | 2012-12-24 | | 16 | sc | M | RS | 3022.2 | 12345678901 | beijing | good | 2012-12-24 | | 17 | jk | M | RS | 4022.2 | 12345678901 | beijing | NULL | 2012-12-24 | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+
每个部门人数 mysql> select dept_id,count(*) from work.a group by dept_id; +---------+----------+ | dept_id | count(*) | +---------+----------+ | CW | 4 | | KF | 4 | | SC | 4 | | RS | 5 | +---------+----------+ 4 rows in set (0.00 sec)
每个部门平均工资 mysql> select dept_id,avg(salary) from work.a group by dept_id; +---------+-----------------+ | dept_id | avg(salary) | +---------+-----------------+ | CW | 3632.5249633789 | | KF | 4547.2000732422 | | SC | 5547.2001953125 | | RS | 3022.1999511719 | +---------+-----------------+ 4 rows in set (0.00 sec)
名字已l为开头 mysql> select * from work.a where name like 'l%'; +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ | employee_id | name | gender | dept_id | salary | phone | address | description | join_time | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ | 5 | lm | M | KF | 2472.2 | 12345678901 | beijing | good | 2012-12-24 | | 9 | ll | M | SC | 4122.2 | 12345678901 | beijing | good | 2012-12-24 | | 10 | lt | F | SC | 6522.2 | 12345678901 | beijing | good | 2012-12-24 | | 11 | lr | M | SC | 7122.2 | 12345678901 | beijing | good | 2012-12-24 | +-------------+------+--------+---------+--------+-------------+---------+-------------+------------+ 每个部门女员工工资最高的 mysql> select dept_id, max(salary) from md.a where gender= 'F' group by dept_id; +---------+-------------+ | dept_id | max(salary) | +---------+-------------+ | KF | 2500 | | RS | 6000 | | CW | 4500 | +---------+-------------+