综合案例——图书管理系统（MySQL）

综合案例——图书管理系统

图书管理系统有三大类角色：普通用户、图书管理员和系统管理员。
（1）普通用户：查看个人信息、修改个人信息、查询图书、借阅图书。
（2）图书管理员：图书预约查询、图书借阅查询、借阅图书登记、还书登记、图书遗失登记。
（3）系统管理员：管理用户、分配角色权限、系统设置。

一、数据库设计

1、创建用户表user：包含用户编号、姓名、出生日期、身份证号、登录名称、登录密码、手机号、电子邮箱、部门编号、角色编号信息，如表1所示。

2、创建部门表dept：包含部门编号、部门名称、创建日期信息，如表2所示。

3、创建角色表role：包含角色编号、角色名称、备注信息，如表3所示。

二、创建数据库

有一部分同行因varchar字符串类型时常而报错，网上有各色各类的说法，其实际操作起来较为麻烦，而且还解决不了问题。大家可以试试，将varchar改为nvarchar，其效果完全一样，毫无差别。我们要的是实现效果，其过程官方也确认！
大小写可根据个人情况，在公司尽量大小写分明，这样就一目了然，为防止交接工作时别人看不懂。更要极为重视英汉标点符号。

1、建表语句

mysql> create table user (
id int(11) not null primary key,
user_name nvarchar(255) default null,
birth_date date default null,
id_card nvarchar(255) default null,
login_name nvarchar(255) default null,
password nvarchar(255) default null,
dept_id int(11) default null,
role_id int(11) default null);
Query OK, 0 rows affected (0.05 sec)
------------------------------------------
mysql> create table dept (
id int(11) not null,
dept_name nvarchar(255) default null,
create_date date default null);
Query OK, 0 rows affected (0.06 sec)
------------------------------------------
mysql> create table role (
id int(11) not null,
role_name nvarchar(255) default null,
remark nvarchar(255) default null);
Query OK, 0 rows affected (0.05 sec)

2、初始化数据

（1）插入数据到用户表中，数据如表4所示。

mysql> insert into user values(1,'小红','1985-05-25','10111','xiaohong','123456',1,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(2,'小影','1989-02-19','10112','xiaoying','123456',1,1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into user values(3,'大梅','1989-06-01','30102','damei','123456',2,2);
Query OK, 1 row affected (0.02 sec)

mysql> insert into user values(4,'admin','1988-03-25','30103','admin','123456',3,3);
Query OK, 1 row affected (0.02 sec)

（2）插入数据到部门表dept，数据如表5所示。

mysql> insert into dept values(1,'行政部','2018-06-03'),(2,'后勤部','2018-06-03'),(3,' 运维部','2018-06-03');

Query OK, 1 row affected (0.02 sec)

（3）插入数据到角色表，数据如表6所示。

mysql> insert into role values(1,'普通用户',null),(2,'图书管理员',null),(3,'系统管理员',null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

3、实现效果

mysql> select*from user;
+----+-----------+------------+---------+------------+----------+---------+---------+
| id | user_name | birth_date | id_card | login_name | password | dept_id | role_id |
+----+-----------+------------+---------+------------+----------+---------+---------+
|  1 | 小红      | 1985-05-25 | 10111   | xiaohong   | 123456   |       1 |       1 |
|  2 | 小影      | 1989-02-19 | 10112   | xiaoying   | 123456   |       1 |       1 |
|  3 | 大梅      | 1989-06-01 | 30102   | damei      | 123456   |       2 |       2 |
|  4 | admin     | 1988-03-25 | 30103   | admin      | 123456   |       3 |       3 |
+----+-----------+------------+---------+------------+----------+---------+---------+
4 rows in set (0.02 sec)

mysql> select*from dept;
+----+-----------+-------------+
| id | dept_name | create_date |
+----+-----------+-------------+
|  1 | 行政部    | 2018-06-03  |
|  2 | 后勤部    | 2018-06-03  |
|  3 | 运维部    | 2018-06-03  |
+----+-----------+-------------+
3 rows in set (0.02 sec)

mysql> select*from role;
+----+------------+--------+
| id | role_name  | remark |
+----+------------+--------+
|  1 | 普通用户   | NULL   |
|  2 | 图书管理员 | NULL   |
|  3 | 系统管理员 | NULL   |
+----+------------+--------+
3 rows in set (0.00 sec)

三、用户信息管理

1、修改用户小红的部门，修改为运维部。

mysql> update user set dept_id=3 where login_name='xiaohong';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select*from user;
+----+-----------+------------+---------+------------+----------+---------+---------+
| id | user_name | birth_date | id_card | login_name | password | dept_id | role_id |
+----+-----------+------------+---------+------------+----------+---------+---------+
|  1 | 小红      | 1985-05-25 | 10111   | xiaohong   | 123456   |       3 |       1 |
|  2 | 小影      | 1989-02-19 | 10112   | xiaoying   | 123456   |       1 |       1 |
|  3 | 大梅      | 1989-06-01 | 30112   | damei      | 123456   |       2 |       2 |
|  4 | admin     | 1988-03-25 | 30103   | admin      | 123456   |       3 |       3 |
+----+-----------+------------+---------+------------+----------+---------+---------+
4 rows in set (0.00 sec)

2、查询用户小红的姓名、登录名、部门名称、角色名称。

mysql> select u.user_name,u.login_name,d.dept_name,r.role_name from user u, dept d,role r where u.dept_id=d.id and u.role_id=r.id and u.login_name='xiaohong';
+-----------+------------+-----------+-----------+
| user_name | login_name | dept_name | role_name |
+-----------+------------+-----------+-----------+
| 小红      | xiaohong   | 运维部    | 普通用户  |
+-----------+------------+-----------+-----------+
1 row in set (0.00 sec)

3、删除用户小红。

mysql> delete from user where login_name='xiaohong';
Query OK, 1 row affected (0.00 sec)

四、部门管理

1、查询所有部门信息。

mysql> select*from dept;
+----+-----------+-------------+
| id | dept_name | create_date |
+----+-----------+-------------+
|  1 | 行政部    | 2018-06-03  |
|  2 | 后勤部    | 2018-06-03  |
|  3 | 运维部    | 2018-06-03  |
+----+-----------+-------------+
3 rows in set (0.00 sec)

mysql> insert into dept values('4','办公室','2020-12-02');
Query OK, 1 row affected (0.02 sec)

2、新增一个办公室部门，创建时间为2020-12-02。

mysql> insert into dept values('4','办公室','2020-12-02');
Query OK, 1 row affected (0.02 sec)

五、用户信息查询视图

针对用户表user、部门表dept、角色表role建立一个用户信息查询视图user_info_view,查询用户编号、姓名、登录名称。

mysql> create or replace view user_info_view
    as
    select u.id,u.user_name,u.login_name
    from user u,dept d,role r
    where u.dept_id=d.id and u.role_id=r.id;
Query OK, 0 rows affected (0.01 sec)

mysql> select*from user_info_view;
+----+-----------+------------+
| id | user_name | login_name |
+----+-----------+------------+
|  2 | 小影      | xiaoying   |
|  3 | 大梅      | damei      |
|  4 | admin     | admin      |
+----+-----------+------------+
3 rows in set (0.02 sec)

其为简洁化，需要全面的微信私信我！