假设我们有一个名为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)