【試題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”的操作員的基本資訊；
?查詢出姓名為“劉德華”的操作員具有哪些功能權限；
?查詢出“投標責任人”角色所擁有的功能；
?創建視圖查詢操作員的姓名，密碼和所屬部門；
創建存盤程序，查詢指定操作員所具有的功能權限
uj5u.com熱心網友回復：
請求大家講解，我有點蠢，可能不難，但是剛入門，不太會。謝謝大家了uj5u.com熱心網友回復：
# 創建資料庫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 $$
不客氣
uj5u.com熱心網友回復：
E-R圖都能做出來，后面就擼起袖子，干就是了撒
uj5u.com熱心網友回復：
重新改了下格式，讓你看起來方便些創建資料庫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;
uj5u.com熱心網友回復：
我以后能經常問你問題嗎？如果方便的話
uj5u.com熱心網友回復：
可以，因為我也在學習，不知道你學的是哪個專業，可能有些問題我解決不了
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/43733.html
標籤：數據庫
上一篇：VC中文全集