大一数据库题（希望大家帮忙解答，不要运用太难的知识）

[April_625]

【试题1】
1任 务
你作为《建设工程监管信息系统》项目开发组的程序员，请按要求完成：
数据库的创建；
数据表的创建；
数据的操作。
2要 求
《系统权限管理》子模块的E-R图如图2.1 所示，逻辑数据模型如图2.2 所示，物理数据模型如图2.3所示，数据表字段名定义见表2.3。请按以下设计完成数据库创建、数据表创建和数据操作任务：

图2.1 E-R图

图2.2 逻辑数据模型

图2.3 物理数据原型
表2.3 字段名定义表
字段名 字段说明 字段名 字段说明
Func_id 功能id User_passwd 用户密码
Func_name 功能名称 Dept_id 所属部门
Func_role_id 功能角色id Telephone 联系电话
Func_role_name 角色名称 Address 联系地址
User_id 用户id Handphone 手机号
User_name 用户姓名 Usb_no 密码锁号
Reserve 备注
3 数据库操作
3.1 创建数据库
创建数据库ConstructionDB。
3.2创建数据表
根据图2.2和表2.3，创建数据表T_user、T_func_item、T_func_role_def。
3.3创建数据表间的关系
根据物理数据原型，创建数据关系表。
3.4数据操作
用SQL语句完成如下操作：
在T_user表插入数据：“id01，刘德华，123，KBB，5678900，湖南长沙，13899005678，ldh123，admin”；
查询出所属部门为“KBB”的操作员的基本信息；
查询出姓名为“刘德华”的操作员具有哪些功能权限；
查询出“投标责任人”角色所拥有的功能；
创建视图查询操作员的姓名，密码和所属部门；
创建存储过程，查询指定操作员所具有的功能权限

[April_625]

请求大家讲解，我有点蠢，可能不难，但是刚入门，不太会。谢谢大家了

[Super_Robot]

# 创建数据库ConstructionDB
CREATE DATABASE ConstructionDB;
# 使用ConstructionDB
use ConstructionDB;
# 创建数据表操作员基本信息表
CREATE TABLE T_user(
	User_id CHAR ( 4 ) PRIMARY KEY COMMENT '用户ID',
	User_name CHAR ( 16 ) COMMENT '用户姓名',
	User_password CHAR ( 16 ) COMMENT '用户密码',
	Dept_id CHAR ( 3 ) COMMENT '所属部门',
	Telephone VARCHAR ( 16 ) COMMENT '联系电话',
	Address VARCHAR ( 32 ) COMMENT '联系地址',
	Handphone VARCHAR ( 16 ) COMMENT '手机号',
	Usb_no VARCHAR ( 64 ) COMMENT '密码锁号',
	Reserv VARCHAR ( 64 ) COMMENT '备注' 
);

# 创建一级功能权限定义表
CREATE TABLE T_func_item(
	Func_id CHAR ( 3 ) PRIMARY KEY COMMENT '功能ID',
	Func_name VARCHAR ( 32 ) COMMENT '功能名称',
	Reserv VARCHAR ( 64 ) COMMENT '备注'
);

# 创建功能角色定义表
CREATE TABLE T_func_role_def(
	Func_role_id CHAR ( 3 ) PRIMARY KEY COMMENT '功能ID',
	Func_role_name VARCHAR ( 32 ) COMMENT '功能名称',
	Reserv VARCHAR ( 64 ) COMMENT '备注'
);
# 创建关系表1

CREATE TABLE Realationship_1(
	Func_id CHAR ( 3 ) COMMENT '功能ID',
	Func_role_id CHAR ( 3 ) COMMENT '功能角色ID',
	PRIMARY KEY ( Func_id, Func_role_id ),
	CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_I FOREIGN KEY ( Func_id ) REFERENCES T_func_item ( Func_id ),
	CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_R FOREIGN KEY ( Func_id ) REFERENCES T_func_role_def ( Func_role_id )
);

# 创建关系表2
CREATE TABLE Realationship_2(
	Func_role_id CHAR ( 3 ) COMMENT '功能角色ID',
	User_id CHAR ( 4 ) COMMENT '用户ID',
	PRIMARY KEY ( Func_role_id, User_id ),
	CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_R1 FOREIGN KEY ( Func_role_id ) REFERENCES T_func_role_def ( Func_role_id ),
	CONSTRAINT FK_RELATION_RELATIONS_T_USER FOREIGN KEY ( User_id ) REFERENCES T_user ( User_id )
);

# 插入数据
INSERT into T_user values
('01','刘德华','123','KBB','5678900','湖南长沙','13899005678','ldh123','admin');

# 查询出所属部门为“KBB”的操作员的基本信息；
SELECT
	*
FROM
	T_user 
WHERE
	Dept_id = 'KBB';
	
# 查询出姓名为“刘德华”的操作员具有哪些功能权限；
SELECT
	f.Func_name '功能权限'
FROM
	T_user u,Realationship_2 r2,Realationship_1 r1,T_func_item f
WHERE
	Dept_id = '刘德华'
	AND
	u.User_id = r2.User_id
	AND
	r2.Func_role_id = r1.Func_role_id
	AND
	r1.Func_id = f.Func_id;
	
# 查询出“投标责任人”角色所拥有的功能；
SELECT
	fi.Func_name '功能权限'
FROM
	T_user u,
	Realationship_2 r2,
	Realationship_1 r1,
	T_func_item fi,
	T_func_role_def fr
WHERE
	u.User_id = r2.User_id
	AND
	r2.Func_role_id = r1.Func_role_id
	AND
	r1.Func_id = fi.Func_id
	AND
	r2.Func_role_id = fr.Func_role_id
	AND
	fr.Func_role_name = '投标责任人';
	
# 创建视图查询操作员的姓名，密码和所属部门；
CREATE VIEW view_T_user AS
SELECT
	User_name '姓名',User_password '密码',Dept_id '所属部门'
FROM
	T_user;
# 创建存储过程，查询指定操作员所具有的功能权限
delimiter $$
CREATE PROCEDURE check_func(IN user_name CHAR(16))
BEGIN
	SELECT
	f.Func_name '功能权限'
	FROM
	T_user u,Realationship_2 r2,Realationship_1 r1,T_func_item f
	WHERE
		Dept_id = input_name
		AND
		u.User_id = r2.User_id
		AND
		r2.Func_role_id = r1.Func_role_id
		AND
		r1.Func_id = f.Func_id;
	END $$

不客气

[sichuanwww]

E-R图都能做出来，后面就撸起袖子，干就是了撒

[Super_Robot]

重新改了下格式，让你看起来方便些

创建数据库ConstructionDB

CREATE DATABASE ConstructionDB;

使用ConstructionDB
如果不使用无法创建下面的表

use ConstructionDB;

创建数据表操作员基本信息表

CREATE TABLE T_user(
	User_id CHAR ( 4 ) PRIMARY KEY COMMENT '用户ID',
	User_name CHAR ( 16 ) COMMENT '用户姓名',
	User_password CHAR ( 16 ) COMMENT '用户密码',
	Dept_id CHAR ( 3 ) COMMENT '所属部门',
	Telephone VARCHAR ( 16 ) COMMENT '联系电话',
	Address VARCHAR ( 32 ) COMMENT '联系地址',
	Handphone VARCHAR ( 16 ) COMMENT '手机号',
	Usb_no VARCHAR ( 64 ) COMMENT '密码锁号',
	Reserv VARCHAR ( 64 ) COMMENT '备注' 
);

创建一级功能权限定义表

CREATE TABLE T_func_item(
	Func_id CHAR ( 3 ) PRIMARY KEY COMMENT '功能ID',
	Func_name VARCHAR ( 32 ) COMMENT '功能名称',
	Reserv VARCHAR ( 64 ) COMMENT '备注'
);

创建功能角色定义表

CREATE TABLE T_func_role_def(
	Func_role_id CHAR ( 3 ) PRIMARY KEY COMMENT '角色ID',
	Func_role_name VARCHAR ( 32 ) COMMENT '角色名称',
	Reserv VARCHAR ( 64 ) COMMENT '备注'
);

创建关系表1
因为上面已经创建了对应表，可以在创建关系表时直接加上外键，也可以创建表后用alter加上

CREATE TABLE Realationship_1(
	Func_id CHAR ( 3 ) COMMENT '功能ID',
	Func_role_id CHAR ( 3 ) COMMENT '功能角色ID',
	PRIMARY KEY ( Func_id, Func_role_id ),
	CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_I FOREIGN KEY ( Func_id ) REFERENCES T_func_item ( Func_id ),
	CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_R FOREIGN KEY ( Func_id ) REFERENCES T_func_role_def ( Func_role_id )
);

创建关系表2
同上

CREATE TABLE Realationship_2(
	Func_role_id CHAR ( 3 ) COMMENT '功能角色ID',
	User_id CHAR ( 4 ) COMMENT '用户ID',
	PRIMARY KEY ( Func_role_id, User_id ),
	CONSTRAINT FK_RELATION_RELATIONS_T_FUNC_R1 FOREIGN KEY ( Func_role_id ) REFERENCES T_func_role_def ( Func_role_id ),
	CONSTRAINT FK_RELATION_RELATIONS_T_USER FOREIGN KEY ( User_id ) REFERENCES T_user ( User_id )
);

插入数据
这个简单

INSERT into T_user values
('01','刘德华','123','KBB','5678900','湖南长沙','13899005678','ldh123','admin');

查询出所属部门为“KBB”的操作员的基本信息；
如果要查角色名称，或权限要用多表连查

SELECT
	*
FROM
	T_user 
WHERE
	Dept_id = 'KBB';

查询出姓名为“刘德华”的操作员具有哪些功能权限；
也是用多表连查，根据对应的id关联查出权限

SELECT
	f.Func_name '功能权限'
FROM
	T_user u,Realationship_2 r2,Realationship_1 r1,T_func_item f
WHERE
	Dept_id = '刘德华'
	AND
	u.User_id = r2.User_id
	AND
	r2.Func_role_id = r1.Func_role_id
	AND
	r1.Func_id = f.Func_id;

查询出“投标责任人”角色所拥有的功能；
也是多表连查

SELECT
	fi.Func_name '功能权限'
FROM
	T_user u,
	Realationship_2 r2,
	Realationship_1 r1,
	T_func_item fi,
	T_func_role_def fr
WHERE
	u.User_id = r2.User_id
	AND
	r2.Func_role_id = r1.Func_role_id
	AND
	r1.Func_id = fi.Func_id
	AND
	r2.Func_role_id = fr.Func_role_id
	AND
	fr.Func_role_name = '投标责任人';

创建视图查询操作员的姓名，密码和所属部门；
创建视图的基本格式：CREATE VIEW 视图名称 AS 查询条件

CREATE VIEW view_T_user AS
SELECT
	User_name '姓名',User_password '密码',Dept_id '所属部门'
FROM
	T_user;

创建存储过程，查询指定操作员所具有的功能权限
CREATE PROCEDURE 名称(IN|OUT|INOUT 变量名 数据类型)
BEGIN 开始
查询条件
END 结束
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
delimiter $$ 语句结束符 是一种定义;的语句,如果不想让数据库立马执行 可以这么定义,$$可以自定义，默认是;
临时改为两个 $$
写完代码运行时输入delimiter; 改回分号就可以让数据库执行了
这个可以不写

delimiter $$
CREATE PROCEDURE check_func(IN user_name CHAR(16))
BEGIN
	SELECT
	f.Func_name '功能权限'
	FROM
	T_user u,Realationship_2 r2,Realationship_1 r1,T_func_item f
	WHERE
		Dept_id = input_name
		AND
		u.User_id = r2.User_id
		AND
		r2.Func_role_id = r1.Func_role_id
		AND
		r1.Func_id = f.Func_id;
	END $$
delimiter;

[Super_Robot]

引用 5 楼 yueyetu_yy 的回复:

我以后能经常问你问题吗？如果方便的话

可以，因为我也在学习，不知道你学的是哪个专业，可能有些问题我解决不了