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');

三、查詢操作練習

1. 查詢所有員工信息

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: 0mysql> 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)

四、總結

本文通過實際案例介紹了 MySQL 數據庫的基本操作，包括數據庫和表的創建、數據插入以及各種常用查詢。這些操作是數據庫開發的基礎，掌握這些技能可以幫助我們更好地處理和分析數據。在實際應用中，還需要根據具體業務場景靈活運用這些查詢語句，結合索引優化等技術，提高查詢效率。

希望本文對初學者有所幫助，后續可以繼續深入學習多表連接查詢、子查詢、存儲過程等更高級的數據庫操作。