table scheme

最新推荐文章于 2024-07-09 22:26:42 发布

weixin_30735745

最新推荐文章于 2024-07-09 22:26:42 发布

阅读量226

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jinrize/archive/2009/09/02/1559045.html

本文详细展示了数据库中各类表的创建过程，包括客户、账户、分行、贷款等，并通过插入、删除、更新和选择查询操作，演示了如何在这些表中进行数据管理。同时，对比了MobileLite和SQLite两种数据库在不同操作下的资源使用和性能表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

테이블 스키마:
create table customer(customer_name nvarchar(15) not null,customer_street nvarchar(12),customer_city nvarchar(15),primary key(customer_name));

create table account(account_number varchar(15) not null,branch_name varchar(15), balance int, primary key(account_number));

create table branch (branch_name varchar(15) not null,branch_city varchar(15),assets int, primary key(branch_name));

create table loan(loan_number varchar(15) not null,branch_name varchar(15),amount int,primary key(loan_number));

create table depositor(customer_name varchar(15) not null,account_number varchar(15) not null,primary key(customer_name, account_number));

create table borrower(customer_name varchar(15) not null,loan_number varchar(15) not null,primary key(customer_name, loan_number));

create table test_insert(customer_name nvarchar(15) not null,customer_street nvarchar(12),customer_city nvarchar(15), account_number varchar(15) not null,branch_name varchar(15) not null, balance int,branch_city varchar(15),assets int,loan_number varchar(15) not null,amount int);

insert into test_insert values (N'홍길동', N'수원', N'원천동', '632-20-XXX', 'SC-First',500, 'Suwon',90000,'L-23',100);
insert into test_insert values ('홍길동', '수원', '원천동', '632-20-XXX', 'SC-First',500, 'Suwon',90000, 'L-23',100);

리소스 사용량: 1. 디비 생성마자: MobileLite: 7개 파일 생성됬고, 총 1.3M (_DATA: 336K, _INDEX: 176K).
                              SQLite: 1개 파일 3K.
              2. 만/5만/10만 개 레코드 삽입하고: MobileLite: 5.7M, 348ms/ 8.7M, 1779ms /15.9M, 3562ms
                                      SQLite: 796K. 280ms./ 4.1M, 1399ms / 8.2M, 2997ms

Insert 질의어(sqlite):
insert into test_insert values ('홍%d', '수원', '원천동', '632-20--%d', 'SC-First',%d, 'Suwon',900%d, 'L-%d',10%d);";

Insert 질의어(mobilelite):
insert into test_insert values (N'홍%d', N'수원', N'원천동', '632-20--%d', 'SC-First',%d, 'Suwon',900%d, 'L-%d',10%d);";

delete 질의어(sqlite):
delete from test_insert where customer_name='홍%d'
delete 질의어(mobilelite):
delete from test_insert where customer_name= N'홍%d'

select 질의어(sqlite):
    select * from test_insert where customer_name='홍%d';
like:
     select * from test_insert where customer_name like '%23'
     select * from test_insert where customer_name like '홍%'
     select * from test_insert where customer_name like '%22%'
[db@localhost basic_test]$ ./select_sq 10000
select_time : 5ms.
select_left : 4ms.
select_right : 4ms.
select_left_right : 5ms.
[db@localhost basic_test]$ ./insert_rnd_sq 50000[db@localhost basic_test]$ ./select_sq 50000
select_time : 24ms.
select_left : 31ms.
select_right : 32ms.
select_left_right : 33ms.
[db@localhost basic_test]$ ./insert_rnd_sq 100000
[db@localhost basic_test]$ ./select_sq 100000
select_time : 46ms.
select_left : 57ms.
select_right : 63ms.
select_left_right : 66ms.

select 질의어(mobilelite):
    select * from test_insert where customer_name=N'홍%d';
like:
     select * from test_insert where customer_name like N'%23'
     select * from test_insert where customer_name like N'홍%'
     select * from test_insert where customer_name like N'%22%'

Update:질의어
sqlite: update test_insert set balance=900000 where customer_name='홍%d';
mobilelite: update test_insert set balance=900000 where customer_name= N'홍%d';
조건절 사용:
sqlite: update test_insert set balance=200000 where balance between 100 and 3000; fix: 3/22/43 v: 40/235/511
mobilelite: update test_insert set balance=200000 where balance between 100 and 3000;

转载于:https://www.cnblogs.com/jinrize/archive/2009/09/02/1559045.html