logo
Xenon
banner
NEWS LETTER

数据管理技术-Lab-6

Scroll down

数据管理技术-Lab6

Task1：

  1. 新建数据库 Library
  2. 新建如下表：（键与约束自行合理设定即可，字段可自由增加，比如 ID）
    账户(用户名, 密码, …)
    书库(ISBN, 书名, 数量, …)
    借阅记录(用户名, ISBN, 借书时间, 到期时间, 还书时间, …)

Ps：到期时间约定为借书时间+30天。

新建表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create table account(
    user_name varchar(20) primary key ,
    u_password varchar(20) not null 
);

create table library(
    ISBN int primary key ,
    b_name varchar(20) not null ,
    num int not null 
);

create table record(
    user_name varchar(20) not null ,
    ISBN int not null ,
    borrow_date date not null ,
    due_date date not null ,
    return_date date not null ,
    constraint fk_1 foreign key (user_name) references account (user_name) , 
    constraint fk_2 foreign key (ISBN) references library (ISBN) ,
    constraint check_dueDate check ( datediff(borrow_date, due_date) =-30 )
);

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
INSERT INTO account (user_name, u_password) VALUES
    ('Alice', 'qwerty'),
    ('Bob', '123456'),
    ('Charlie', 'abcdef'),
    ('David', 'password'),
    ('Emily', '987654'),
    ('Frank', 'iloveyou'),
    ('George', 'football'),
    ('Hannah', 'sunshine');
INSERT INTO library (ISBN, b_name, num) VALUES
    (10001, 'The Great Gatsby', 10),
    (10002, 'Mockingbird', 5),
    (10003, '1984', 7),
    (10004, 'Prejudice', 8),
    (10005, 'Animal Farm', 3),
    (10006, 'The Catcher', 6),
    (10007, 'Brave New World', 4),
    (10008, 'Wuthering', 2);
INSERT INTO record (user_name, ISBN, borrow_date, due_date, return_date)
VALUES
    ('Alice', 10001, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Alice', 10002, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Alice', 10004, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Bob', 10003, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Bob', 10006, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Charlie', 10005, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Charlie', 10007, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('David', 10008, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('David', 10001, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Emily', 10002, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Emily', 10005, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Emily', 10006, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Frank', 10001, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Frank', 10004, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Frank', 10006, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('George', 10003, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('George', 10004, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('George', 10005, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Hannah', 10002, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Hannah', 10003, '2023-01-01', '2023-01-31', '2023-01-28'),
    ('Hannah', 10005, '2023-01-01', '2023-01-31', '2023-01-28');

1-1

用户密码检查/修改： 接收四个参数（用户名，密码，新密码，动作），若动作为1，则检查用户名和密码是否和密码表中存的相符，相符则返回 true，不相符返回false； 若动作为2， 则首先检查用户名、密码是否相符，若不相符返回false，相符则将密码表中的密码改成新密码，返回true。密码要求只包含数字和字母，长度大于等于4、小于等于10。

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
delimiter $$
create procedure check_or_modify_password(
    in name varchar(20) ,
    in password varchar(20) ,
    in new_password varchar(20) ,
    in action int ,
    out res boolean
)begin
    declare match_flag int default 0;
    declare wrong_password int default 0;

    if action = 1 then
        select count(*) into match_flag from account where user_name = name and u_password = password;
        if match_flag = 1 then
            set res = true;
        else
            set res = false;
        end if;
    elseif action = 2 then
        select count(*) into match_flag from account where user_name = name and u_password = password;
        if match_flag = 1 then
            if new_password regexp '^[a-zA-Z0-9]+$' then
                set wrong_password = 0;
            else
                set wrong_password = 1;
            end if;
            if length(new_password) >= 4 and length(new_password) <= 10 and wrong_password = 0 then
                update account set u_password = new_password where user_name = name;
                set res = true;
            else
                set res = false;
            end if;
        else
            set res = false;
        end if;
    end if;
end; $$

测试：

1
2
3
4
5
6
7
8
9
10
call check_or_modify_password('George', '212234', '212.234', 2, @res);
select @res;
call check_or_modify_password('Hannah', 'sunshine', 'sunshine3', 1, @res);
select @res;
call check_or_modify_password('Hannah', 'sunshine2', 'sunshine3', 1, @res);
select @res;
call check_or_modify_password('Frank', 'iloveyou', 'youloveme', 2, @res);
select @res;
call check_or_modify_password('Emily', '987654', '123456', 2, @res);
select @res;

测试前account表：

测试后res输出0，1， 0， 1， 1，测试后account表：

1-2

借书：接收两个参数（用户名，ISBN），没有足够的书、用户不存在或一个人借阅两本同样的书时返回false，合法执行后，借阅记录表会新增一条记录，书库对应书的数量也需要减1，并返回true；

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
delimiter $$
create procedure borrow_book(
    in name varchar(20) ,
    in b_isbn int ,
    out res boolean
)begin
    declare user_exist boolean default false;
    declare book_num int default 0;
    declare borrow_record int default 0;
    select count(*) into user_exist from account where user_name = name;
    if user_exist != 1 then
        set res = false;
    else
        select library.num into book_num from library where library.ISBN = b_isbn;
        if book_num < 1 then
            set res = false;
        else
            select count(*) into borrow_record from record where record.user_name = name and record.ISBN = b_isbn;
            if borrow_record > 0 then
                set res = false;
            else
                set res = true;
                insert into record(user_name, ISBN, borrow_date, due_date, return_date)
                values (name, b_isbn, curdate(), date_add(curdate(), interval 30 day), null);
                update library set num = num - 1 where ISBN = b_isbn;
            end if;
        end if;
    end if;
end; $$

测试：

1
2
3
4
5
6
7
8
9
10
call borrow_book('avs', '10008', @res);
select @res;
call borrow_book('Hannah', '10008', @res);
select @res;
call borrow_book('Hannah', '10008', @res);
select @res;
call borrow_book('George', '10008', @res);
select @res;
call borrow_book('Hannah', '10008', @res);
select @res;

测试前library表：

测试前record表：

测试后res结果为0， 1， 0， 1， 0

测试后library表：

测试后record表：

1-3

还书：接收两个参数（用户名，ISBN），未查询到借阅记录时返回false，合法执行后，借阅记录表对应记录会修改还书时间，书库对应书的数量需要加1，并返回true；

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
delimiter $$
create procedure return_book(
    in name varchar(20) ,
    in b_isbn int ,
    out res boolean
)begin
    declare user_exist boolean default false;
    declare book_record int default 0;
    select count(*) into user_exist from account where user_name = name;
    if user_exist != 1 then
        set res = false;
    else
        select count(*) into book_record from record where user_name = name and ISBN = b_isbn;
        if book_record = 0 then
            set res = false;
        else
            update record set return_date = curdate() where user_name = name and ISBN = b_isbn;
            update library set num = num + 1 where ISBN = b_isbn;
        end if;
    end if;
end; $$

测试：

1
2
3
4
5
6
call return_book('Hannah', '10008', @res);
select @res;
call return_book('George', '10008', @res);
select @res;
call return_book('阿萨德', '10008', @res);
select @res;

测试前library表：

测试前record表：

测试后res结果：1, 1，0

测试后library表：

测试后record表：

1-4

查看当前借阅记录：接受一个参数（用户名），返回该用户名的当前借阅中的记录(用户名, ISBN, 到期时间)

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
delimiter $$
create procedure check_record(
    in name varchar(20)
)begin
    declare done boolean default false;
    declare current_userName varchar(20);
    declare current_ISBN int;
    declare current_dueDate date;
    declare cursor1 cursor for select user_name, ISBN, due_date from record;
    declare continue handler for not found set done = true;

    create temporary table if not exists temp_res(
        u_name varchar(20) ,
        ISBN int ,
        due_date date
    );

    open cursor1;
    cursor_loop: loop
        fetch cursor1 into current_userName, current_ISBN, current_dueDate;
        if done then
            leave cursor_loop;
        end if;

        if current_userName = name then
            insert into temp_res values (current_userName, current_ISBN, current_dueDate);
        end if;
    end loop cursor_loop;
    close cursor1;
    select * from temp_res;
    drop temporary table if exists temp_res;
end; $$

测试：

1
2
3
call check_record('Hannah');
call check_record('George');
call check_record('David');

结果如下：

Task2:

创建表：

1
2
3
4
5
create table tableA (
    id int primary key auto_increment,
    sparse int check ( sparse>=0 and sparse <= 5000000 ),
    dense int check ( dense>=0 and dense <= 9 )
);

2-1

加载（一次性插入40万条记录）.

代码如下：

1
2
3
4
5
6
7
8
9
10
delimiter $$
create procedure load_data(
    in num_rows int
)begin
    declare i int default 0;
    while i < num_rows do
        insert into tableA (sparse, dense) values (round(rand() * 5000000), floor(rand() * 10)) ;
        set i = i + 1;
    end while;
end; $$

测试：

1
call load_data(400000);

结果：

2-2

使用定义好的加载存储过程或自定义函数，完成400万条记录的插入.

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
delimiter $$
create procedure insert_data(
    in num_rows int
)begin
    declare max_id int default 0;
    declare table_a_num int default 0;
    select max(id) into max_id from tableA;
    select count(*) into table_a_num from tableA;
    drop temporary table if exists temp_table;
    create temporary table temp_table like tableA;
    insert into temp_table select * from tableA;
    while table_a_num < num_rows do
        update temp_table set sparse = (sparse + 1) mod 5000000 where id <= num_rows;
        update temp_table set dense = (dense + 1) mod 10 where id <= num_rows;
        update temp_table set id = id + max_id where id <= num_rows;
        insert into tableA select * from temp_table;
        select count(*) into table_a_num from tableA;
    end while;
    drop temporary table if exists temp_table;
end; $$

测试：

1
call insert_data(4000000);

结果如图：

其他文章
cover
OS
OS-Lab5
  • 23/05/08
  • 10:27
  • 1.3k
  • 5
cover
DataBase
数据管理技术-大作业
  • 23/04/27
  • 10:11
  • 911
  • 4
© 2023- 2024 Xenon的小破站
由 Hexo 驱动 v6.3.0 主题 - Async v2.0.3