mysql练习题

假设我们有一个名为employees的表，存储了员工的信息。表结构如下：

列名	数据类型	描述
id	INT	员工的唯一ID
name	VARCHAR(100)	员工的姓名
age	INT	员工的年龄
salary	DECIMAL(10,2)	员工的薪水（单位为元）
department_id	INT	员工所在部门的ID

此外，还有一个名为departments的表，存储了部门的信息：

列名	数据类型	描述
id	INT	部门的唯一ID
name	VARCHAR(100)	部门的名称

这两个表通过department_id列关联。

创建表

mysql> create table departments(id int primary key,name varchar(100)not null);
Query OK, 0 rows affected (0.02 sec)

mysql> create table employees(id int primary key unique,name varchar(100),age int,salary decimal(10,2),department_id int,foreign key(department_id) references departments(id));
Query OK, 0 rows affected (0.02 sec)

插入数据

mysql> insert into departments(id,name)values(1,"人力资源"),(2,"技术部",(3,"销售部"),(4,"财
务部");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from departments;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 人力资源     |
|  2 | 技术部       |
|  3 | 销售部       |
|  4 | 财务部       |
+----+--------------+
4 rows in set (0.00 sec)
mysql> desc departments;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> insert into employees(id,name,age,salary,department_id)values(1,"张三",28,8000.00,1),(2,"李四",32,9500.00,2),(3,"王五",25,6000.00,3),(4,"赵六",35,12000.00,2),(5,"孙七",27,7500.00,1),(6,"周八",30,5500.00,4),(7,"吴九",40,10500.00,3),(8,"陈十",29,6500.00,2);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from employees;
+----+--------+------+----------+---------------+
| id | name   | age  | salary   | department_id |
+----+--------+------+----------+---------------+
|  1 | 张三   |   28 |  8000.00 |             1 |
|  2 | 李四   |   32 |  9500.00 |             2 |
|  3 | 王五   |   25 |  6000.00 |             3 |
|  4 | 赵六   |   35 | 12000.00 |             2 |
|  5 | 孙七   |   27 |  7500.00 |             1 |
|  6 | 周八   |   30 |  5500.00 |             4 |
|  7 | 吴九   |   40 | 10500.00 |             3 |
|  8 | 陈十   |   29 |  6500.00 |             2 |
+----+--------+------+----------+---------------+
8 rows in set (0.00 sec)

mysql> desc employees;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| id            | int           | NO   | PRI | NULL    |       |
| name          | varchar(100)  | YES  |     | NULL    |       |
| age           | int           | YES  |     | NULL    |       |
| salary        | decimal(10,2) | YES  |     | NULL    |       |
| department_id | int           | YES  | MUL | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> 

查询年龄大于30岁且薪水高于5000元的员工姓名。

mysql> select * from employees where age>30 and salary>5000.00;
+----+--------+------+----------+---------------+
| id | name   | age  | salary   | department_id |
+----+--------+------+----------+---------------+
|  2 | 李四   |   32 |  9500.00 |             2 |
|  4 | 赵六   |   35 | 12000.00 |             2 |
|  7 | 吴九   |   40 | 10500.00 |             3 |
+----+--------+------+----------+---------------+
3 rows in set (0.00 sec)

mysql> 

查询每个部门的员工数量。

mysql> select departments.name AS name ,count(employees.id) AS em_count from departments left
 join employees on departments.id = employees.department_id group by departments.id;
+--------------+----------+
| name         | em_count |
+--------------+----------+
| 人力资源     |        2 |
| 技术部       |        3 |
| 销售部       |        2 |
| 财务部       |        1 |
+--------------+----------+
4 rows in set (0.00 sec)

mysql> 

 查询薪水最高的员工的姓名和薪水。

mysql> select name,salary from employees order by salary desc limit 1;
+--------+----------+
| name   | salary   |
+--------+----------+
| 赵六   | 12000.00 |
+--------+----------+
1 row in set (0.01 sec)

mysql> 

 查询薪水在3000到8000元之间的员工，并按薪水从高到低排序。

mysql> select *  from employees where salary>3000.00 and salary<8000.00 order by salary desc
;
+----+--------+------+---------+---------------+
| id | name   | age  | salary  | department_id |
+----+--------+------+---------+---------------+
|  5 | 孙七   |   27 | 7500.00 |             1 |
|  8 | 陈十   |   29 | 6500.00 |             2 |
|  3 | 王五   |   25 | 6000.00 |             3 |
|  6 | 周八   |   30 | 5500.00 |             4 |
+----+--------+------+---------+---------------+
4 rows in set (0.00 sec)

mysql> 

查询年龄最小和年龄最大的员工的姓名和年龄。

mysql> (select name,age from employees order by age asc limit 1) union (select name,age from employees order by age desc limit 1);
+--------+------+
| name   | age  |
+--------+------+
| 王五   |   25 |
| 吴九   |   40 |
+--------+------+
2 rows in set (0.00 sec)