文章详细展示了在数据库中创建和调用存储过程的示例，包括查询、插入、删除和修改数据。同时，通过Python和ODBC在前端调用存储过程进行了演示。此外，文章还设计并实现了数据插入、更新和删除的触发器，并讨论了索引对查询效率的影响，通过实验数据对比了有索引和无索引情况下查询时间随数据量增加的变化情况。
作业内容—存储过程和触发器、索引实验
一、 存储过程和触发器实验
1. 请在你选用的数据库平台上，针对你的应用场景，对如下操作至少各实现一个存储过程：
1）单表或多表查询 2）数据插入 3）数据删除 4）数据修改
2．通过ODBC、OLEDB、JDBC或任意其他的途径，在前端程序（C/S或B/S模式）中调用所实现的后台存储过程。
3．在你的案例场景中，分别设计并实现一个由数据插入、数据更新、数据删除所引发的触发器（前触发或后触发都可以），测试触发器执行效果。
二、索引实验
1) 结合作业#3，针对你的数据库中的一个表，编写简单的数据查询（查询语句应包括单个涉及非主属性等值比较的查询条件，设该非主属性为A，具体属性结合业务背景）和数据插入语句，程序应能在终端或服务器以文件形式记录每次数据读写操作的耗时。
2) 无索引测试：执行查询（查询条件不包含主码，且不存在针对属性A建立的索引），记录不同数据规模下的查询时间，
3) 有索引测试：针对属性A建立索引，采用与2）中相同的查询，记录不同数据规模下的查询时间。
4) 分析实验数据，制作图表，比较有索引和无索引的情况下，查询时间随数据量增加的变化情况，分析导致实验结果的原因。
-
存储过程和触发器实验
1. 请在你选用的数据库平台上，针对你的应用场景，对如下操作至少各实现一个存储过程：
1）单表或多表查询
查询client表中的client_id为 [init, termient] 的数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `select_pro`(in init int, in termient int)
BEGIN
SELECT * FROM client WHERE client_id>=init AND client_id<=termient;
END - 数据插入
在bank表中插入数据，插入前检测主键约束。若要插入的数据的bank_id已存在，则返回已存在的表中的这条数据，并返回 'The ID has exsited alreadly' 的消息。若要插入的数据的bank_id不存在，则插入新的数据，并返回 'Successfully insert' 的消息。
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_pro`(IN id INT, IN bank_name VARCHAR(255), IN bank_balance DOUBLE, OUT result VARCHAR(64))
BEGIN
IF EXISTS(SELECT * FROM bank WHERE bank_id=id) THEN
SET result='The ID has exsited alreadly';
SELECT * FROM bank WHERE bank_id=id;
ELSE
INSERT INTO bank VALUES(id, bank_name, bank_balance);
SET result='Successfully insert';
END IF;
END - 数据删除
删除bank表中的数据，若该数据存在，则删除，并返回'Successfully delete'消息。若该消息不存在，则返回'No this data'消息。
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_pro`(IN id INT, OUT result VARCHAR(64))
BEGIN
IF EXISTS(SELECT * FROM bank WHERE bank_id=id) THEN
DELETE FROM administrator WHERE adm_id=id;
SET result='Successfully delete';
ELSE
SET result='No this data';
END IF;
END - 数据修改
指定bank_id，对bank表中相对应的bank_balance数据进行修改。若该数据存在，则更新数据，并返回'Successfully update'消息。若该数据不存在，则返回'No this data'消息。
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_pro`(IN id INT, IN balance DOUBLE, OUT result VARCHAR(64))
BEGIN
IF EXISTS(SELECT * from bank where bank_id=id) THEN
UPDATE bank SET bank_balance=balance WHERE bank_id=id;
SET result='Successfully update';
ELSE
SET result='No this data';
END IF;
END 2．通过ODBC、OLEDB、JDBC或任意其他的途径，在前端程序（C/S或B/S模式）中调用所实现的后台存储过程。
使用ODBC，通过python语言对上述数据修改的`update_pro`存储过程进行前端的实现。
import web
import pyodbc
class update_web:
'''''
open the update web
'''
def GET(self):
return render.update()
class result_web:
'''''
open the result web
'''
def GET(self):
return render.result(result)
class returnback:
'''''
recieve message from web and return to homepage
'''
def POST(self):
raise web.seeother('/')
class update():
def POST(self):
i = web.input() # 接收web发送的信息
try:
cnxn =pyodbc.connect('DRIVER={'+driver+'};'+
f'SERVER={server};DATABASE={database};UID={username};PWD={password}') # 连接数据库ODBC
cursor =cnxn.cursor()
global result
result = ''
params = (i.bank_id, i.bank_balance, result)
cursor.execute("{CALL update_pro(?,?,?)}", params)
rows = cursor.fetchall()
result = rows[0][0].decode()
print(result)
cnxn.close()
raise web.seeother('/result')
except:
pass
if __name__ == "__main__":
urls = (
'/', 'update_web',
'/update', 'update',
'/result', 'result_web',
'/returnback', 'returnback'
)
# 数据库ODBC连接信息
driver = 'MySQL ODBC 8.0 Unicode Driver' # pyodbc.drivers()查看可用driver，选择之前已经配置过数据库的driver
server = 'localhost'
database = 'bank'
username = 'root'
password = '********'
render = web.template.render('./template')
app = web.application(urls, globals())
app.run() 
3．在你的案例场景中，分别设计并实现一个由数据插入、数据更新、数据删除所引发的触发器（前触发或后触发都可以），测试触发器执行效果。
数据插入触发器
-- 新插入的职工积分始终为0
CREATE DEFINER=`root`@`localhost` TRIGGER insert_adm
BEFORE INSERT
on administrator
FOR EACH ROW
IF new.score<>0 THEN
SET new.score=0;
END IF -- 当新用户第一次存款时，bank表中的balance也随之增加，也就是银行总资金会随之增加
CREATE TRIGGER insert_basic
AFTER INSERT
on basic_service
FOR EACH ROW
UPDATE bank SET bank_balance=bank_balance+new.balance where bank_id=new.bank_id; 数据更新触发器
-- 当用户存取款时，bank表中的余额也随之变化
CREATE TRIGGER update_basic
AFTER UPDATE
on basic_service
FOR EACH ROW
UPDATE bank SET bank_balance=bank_balance-old.balance+new.balance where bank_id=old.bank_id; 数据删除触发器
-- 当用户将存款全部取出时，bank表中的balance也减少
CREATE TRIGGER delete_basic
AFTER DELETE
on basic_service
FOR EACH ROW
UPDATE bank SET bank_balance=bank_balance-old.balance where bank_id=old.bank_id; -
索引实验
1) 结合作业#3，针对你的数据库中的一个表，编写简单的数据查询（查询语句应包括单个涉及非主属性等值比较的查询条件，设该非主属性为A，具体属性结合业务背景）和数据插入语句，程序应能在终端或服务器以文件形式记录每次数据读写操作的耗时。
2) 无索引测试：执行查询（查询条件不包含主码，且不存在针对属性A建立的索引），记录不同数据规模下的查询时间，
3) 有索引测试：针对属性A建立索引，采用与2）中相同的查询，记录不同数据规模下的查询时间。
4) 分析实验数据，制作图表，比较有索引和无索引的情况下，查询时间随数据量增加的变化情况，分析导致实验结果的原因。
import numpy as np
from random import randint
import pickle
import pymysql
import time
from tqdm import tqdm, trange
def insert_data(job_num_i, data_num_i,f): # 数据插入，设置索引列中的类别数量和数据总数
start_time = time.perf_counter()
sql = "INSERT INTO administrator VALUES(%s,'L','hycyzufs','2023-5-23',13022896926,990,%s)"
sql_list = []
for i in trange(data_num_i, desc=f'inserting-{job_num_i}-{data_num_i}', leave=False):
a = randint(1, job_num_i)
sql_list.append((i+1, a))
cursor.executemany(sql, sql_list)
conn.commit()
insert_time = time.perf_counter() - start_time
f.write(f'Insert_time: {insert_time}\n')
def delete_all(): # 删除所有数据
sql = 'DELETE FROM administrator where adm_id>0'
cursor.execute(sql)
conn.commit()
def index_on(): # 开索引
sql = 'CREATE INDEX index_job ON administrator (job_id)'
cursor.execute(sql)
conn.commit()
def index_out(): # 关索引
sql = 'DROP INDEX index_job ON administrator'
cursor.execute(sql)
conn.commit()
def test(test_num_i, data_num_i, sql, f): # 测试查找时间，设置测试的次数，求平均查找时间
all_time = []
for _ in trange(test_num_i, desc=f'testing-{test_num_i}', leave=False):
start_time = time.perf_counter()
cursor.execute(sql)
results = cursor.fetchall()
pickle.dump(list(results), open('outcome', 'wb'))
end_time = time.perf_counter()
use_time = end_time - start_time
all_time.append(use_time)
avg_time = np.mean(all_time)
f.write(f'{str(data_num_i)}: {str(avg_time)}\n')
if __name__ == '__main__':
job_num = [3, 5, 10] # 索引列类别数量列表
data_num = [100, 1000, 10000, 100000, 1000000] # 生成数据数量列表
test_num = 10 # 测试次数列表，测试几次后求平均
conn = pymysql.connect(user='root', password='********', database='bank')
cursor = conn.cursor(pymysql.cursors.DictCursor)
f = open('results.txt', 'w+')
sql = 'select * from administrator where job_id=1'
job_num_i = job_num[0]
data_num_i = data_num[0]
for job_num_i in tqdm(job_num, desc=f'job_num-{job_num_i}'):
f.write(f'--------job_num{job_num_i}---------\n')
for data_num_i in tqdm(data_num, desc=f'data_num-{data_num_i}'):
delete_all()
insert_data(job_num_i, data_num_i, f)
test(test_num, data_num_i, sql, f)
try:
index_on()
time.sleep(3)
except:
pass
test(test_num, data_num_i, sql, f)
try:
index_out()
time.sleep(3)
except:
pass
f.write('\n')
delete_all()
f.close()
cursor.close()
conn.close() 测试结果：
| data_num job_num | 100 | 1,000 | 10,000 | 100,000 | 1,000,000 |
| 无index 3 | 0.002488 | 0.008003 | 0.065155 | 0.792094 | 8.438734 |
| 有index 3 | 0.001907 | 0.007637 | 0.058433 | 0.077227 | 0.094179 |
| 无index 5 | 0.153701 | 0.029841 | 0.049829 | 0.934420 | 5.202662 |
| 有index 5 | 0.001961 | 0.004566 | 0.027477 | 0.0697084 | 0.563507 |
| 无index 10 | 0.329588 | 0.031040 | 0.039202 | 0.291128 | 2.984093 |
| 有index 10 | 0.001789 | 0.005060 | 0.003347 | 0.068878 | 0.086979 |
从数据量上看，在数据较少时，有无索引查询时间都很快，有索引比无索引稍快，但二者之间差距并不大。随着数据量的增加，有索引的查询时间会逐渐显现出优势，在本次实验中相差最大的可以达到有索引所花费时间是无索引花费时间的1/180。
从索引列的类别量来看，在本实验不同类别数量下，有索引都比无索引要快。但是随着类别数量的增多，有索引的查询耗时逐渐增加，优势减小。
扫一扫
1352
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
