MySQL 数据库表与查询操作实战案例_Mysql

mysql 数据库表操作与查询实战案例

在数据库学习过程中，熟练掌握表的创建、数据插入及各类查询操作是基础且重要的技能。本文将通过实际案例，详细介绍 mysql 中数据库表的设计、数据插入以及常用的查询操作，帮助初学者快速上手。

项目一：产品相关数据库设计与创建

一、数据库及表结构设计

本项目主要创建产品相关的数据库及员工表、订单表、发票表，用于存储产品业务相关数据。

二、数据库与表的创建

新建产品库 mydb6_product

mysql> create database mydb6_product;
query ok, 1 row affected (0.00 sec)
mysql> use mydb6_product;
database changed

在该库中新建 4 张表，表结构要求如下：
- employees 表：
  - 列 1：id，整型，主键
  - 列 2：name，字符串（最大长度 50 ），不能为空
  - 列 3：age，整型
  - 列 4：gender，字符串（最大长度 10 ），不能为空，默认值 unknown
  - 列 5：salary，浮点型
```
mysql> create table employees(id int primary key,name varchar(50) not null,age int,gender varchar(10) not null default 'unknown',salary float);
query ok, 0 rows affected (0.02 sec)
```
- orders 表：
  - 列 1：id，整型，主键
  - 列 2：name，字符串（最大长度 100 ），不能为空
  - 列 3：price，浮点型
  - 列 4：quantity，整型
  - 列 5：category，字符串（最大长度 50 ）
```
mysql> create table orders(id int primary key,name varchar(100) not null,price float,quantity int,category varchar(50));
query ok, 0 rows affected (0.01 sec)
```
- invoices 表：
  - 列 1：number，整型，主键自增长
  - 列 2：order_id，整型，外键关联到 orders 表的 id 列
  - 列 3：in_date，日期型
  - 列 4：total_amount，浮点型，要求数据大于 0

mysql> create table invoices(number int primary key auto_increment, order_id int ,in_date date,itotal_amount float check(total_amount>0),foreign key (order_id) references orders(id));
query ok, 0 rows affected (0.02 sec)

项目二：员工信息数据库操作实战

一、数据库及表结构创建

本项目创建员工信息数据库，实现员工信息的插入与各类查询操作。

-- 创建员工数据库
create database mydb8_worker;
-- 使用员工数据库
use mydb8_worker;
-- 创建员工表（t_worker）
create table t_worker(
    department_id int(11) not null comment '部门号',
    worker_id int(11) primary key not null comment '职工号',
    worker_date date not null comment '工作时间',
    wages float(8,2) not null comment '工资',
    politics varchar(10) not null default '群众' comment '政治面貌',
    name varchar(20) not null comment '姓名',
    borth_date date not null comment '出生日期' 
);

二、数据插入操作

insert into t_worker values (101,1001,'2015-5-4',7500.00,'群众','张春燕','1990-7-1');
insert into t_worker values (101,1002,'2019-2-6',5200.00,'团员','李名博','1997-2-8');
insert into t_worker values (102,1003,'2008-1-4',10500.00,'党员','王博涵','1983-6-8');
insert into t_worker values (102,1004,'2016-10-10',5500.00,'群众','赵小军','1994-9-5');
insert into t_worker values (102,1005,'2014-4-1',8800.00,'党员','钱有财','1992-12-30');
insert into t_worker values (103,1006,'2019-5-5',5500.00,'党员','孙菲菲','1996-9-2');

三、查询操作练习

查询所有员工信息

mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+-----------+------------+
| department_id | worker_id | worker_date | wages    | politics | name      | borth_date |
+---------------+-----------+-------------+----------+----------+-----------+------------+
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕    | 1990-07-01 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博    | 1997-02-08 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵    | 1983-06-08 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军    | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财    | 1992-12-30 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲    | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+-----------+------------+
6 rows in set (0.00 sec)

2. 查询去重后的部门 id

mysql> select distinct department_id from t_worker;
+---------------+
| department_id |
+---------------+
|           101 |
|           102 |
|           103 |
+---------------+
3 rows in set (0.01 sec)

3. 统计员工总数

mysql> select count(name) from t_worker;
+-------------+
| count(name) |
+-------------+
|           6 |
+-------------+
1 row in set (0.01 sec)

4. 查询最高工资和最低工资

mysql> select max(wages),min(wages) from t_worker;
+------------+------------+
| max(wages) | min(wages) |
+------------+------------+
|   10500.00 |    5200.00 |
+------------+------------+
1 row in set (0.00 sec)

5. 计算平均工资和工资总和

mysql> select avg(wages),sum(wages) from t_worker;
+-------------+------------+
| avg(wages)  | sum(wages) |
+-------------+------------+
| 7166.666667 |   43000.00 |
+-------------+------------+
1 row in set (0.00 sec)

6. 创建工作日期表

mysql> create table 工作日期 as select worker_id,name,worker_date from t_worker;
query ok, 6 rows affected (0.02 sec)
records: 6  duplicates: 0  warnings: 0
mysql> select * from 工作日期;
+-----------+-----------+-------------+
| worker_id | name      | worker_date |
+-----------+-----------+-------------+
|      1001 | 张春燕    | 2015-05-04  |
|      1002 | 李名博    | 2019-02-06  |
|      1003 | 王博涵    | 2008-01-04  |
|      1004 | 赵小军    | 2016-10-10  |
|      1005 | 钱有财    | 2014-04-01  |
|      1006 | 孙菲菲    | 2019-05-05  |
+-----------+-----------+-------------+
6 rows in set (0.00 sec)

7. 查询党员的出生日期和姓名

mysql> select borth_date,name from t_worker where politics = '党员';
+------------+-----------+
| borth_date | name      |
+------------+-----------+
| 1983-06-08 | 王博涵    |
| 1992-12-30 | 钱有财    |
| 1996-09-02 | 孙菲菲    |
+------------+-----------+
3 rows in set (0.00 sec)

8. 查询工资在 4000 到 8000 之间的员工

mysql> select name,wages from t_worker where wages between 4000 and 8000;
+-----------+---------+
| name      | wages   |
+-----------+---------+
| 张春燕    | 7500.00 |
| 李名博    | 5200.00 |
| 赵小军    | 5500.00 |
| 孙菲菲    | 5500.00 |
+-----------+---------+
4 rows in set (0.01 sec)

9. 查询姓名以 “孙” 或 “李” 开头的员工

mysql> select name from t_worker where name like '孙%' or name like '李%';
+-----------+
| name      |
+-----------+
| 李名博    |
| 孙菲菲    |
+-----------+
2 rows in set (0.00 sec)

10. 查询非党员且部门为 102 或 103 的员工姓名和工号

mysql> select name,worker_id from t_worker where politics != '党员' and (department_id =102 or department_id = 103);
+-----------+-----------+
| name      | worker_id |
+-----------+-----------+
| 赵小军    |      1004 |
+-----------+-----------+
1 row in set (0.00 sec)

11. 按出生日期排序查询所有员工

mysql> select * from t_worker order by borth_date;
+---------------+-----------+-------------+----------+----------+-----------+------------+
| department_id | worker_id | worker_date | wages    | politics | name      | borth_date |
+---------------+-----------+-------------+----------+----------+-----------+------------+
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵    | 1983-06-08 |
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕    | 1990-07-01 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财    | 1992-12-30 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军    | 1994-09-05 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲    | 1996-09-02 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博    | 1997-02-08 |
+---------------+-----------+-------------+----------+----------+-----------+------------+
6 rows in set (0.00 sec)

12. 按工资排序取前 3 名员工的工号和姓名

mysql> select worker_id,name from t_worker order by wages limit 3;
+-----------+-----------+
| worker_id | name      |
+-----------+-----------+
|      1002 | 李名博    |
|      1004 | 赵小军    |
|      1006 | 孙菲菲    |
+-----------+-----------+
3 rows in set (0.00 sec)

13. 按部门统计党员人数

mysql> select department_id,count(*) from t_worker where politics = '党员' group by department_id;
+---------------+----------+
| department_id | count(*) |
+---------------+----------+
|           102 |        2 |
|           103 |        1 |
+---------------+----------+
2 rows in set (0.00 sec)

14. 按部门统计工资总和及平均工资（保留 2 位小数）

mysql> select department_id,sum(wages),round(avg(wages),2) from t_worker group by department_id;
+---------------+------------+---------------------+
| department_id | sum(wages) | round(avg(wages),2) |
+---------------+------------+---------------------+
|           101 |   12700.00 |                6350 |
|           102 |   24800.00 |             8266.67 |
|           103 |    5500.00 |                5500 |
+---------------+------------+---------------------+
3 rows in set (0.00 sec)

15. 查询员工人数不少于 3 人的部门

mysql> select department_id,count(*) from t_worker group by department_id having count(*)>=3;
+---------------+----------+
| department_id | count(*) |
+---------------+----------+
|           102 |        3 |
+---------------+----------+
1 row in set (0.00 sec)