SQL - SQLite - Python - 制約、ビュー、トランザクション - 料理人が多するぎると、データベースがダメになる - トランザクション, STARTではなくBEGIN
Head First SQL ―頭とからだで覚えるSQLの基本、 Lynn Beighley(著)、 佐藤 直生(監訳)、 松永 多苗子(翻訳)、 オライリージャパンの 11章(制約、ビュー、トランザクション - 料理人が多するぎると、データベースがダメになる)、p.488(自分で考えてみよう)の解答を求めてみる。
| id | coin | coin_year |
|---|---|---|
| 1 | Q | 1950 |
| 2 | P | 1972 |
| 3 | N | 2005 |
| 4 | Q | 1999 |
| id | coin | coin_year |
|---|---|---|
| 1 | Q | 1950 |
| 2 | P | 1972 |
| 3 | N | 2005 |
| 4 | Q | 1999 |
| id | coin | coin_year |
|---|---|---|
| 1 | Q | 1950 |
| 2 | P | 1972 |
| 3 | N | 2005 |
| 4 | Q | 1999 |
| id | coin | coin_year |
|---|---|---|
| 1 | Q | 1950 |
| 2 | P | 1972 |
| 3 | N | 2005 |
| 4 | Q | 1999 |
| id | coin | coin_year |
|---|---|---|
| 1 | Q | 1950 |
| 2 | P | 1972 |
| 3 | N | 2005 |
| 4 | D | 1999 |
| id | coin | coin_year |
|---|---|---|
| 1 | Q | 1950 |
| 2 | P | 1972 |
| 3 | P | 2005 |
| 4 | Q | 1999 |
schema4.sql
drop table if exists piggy_bank;
create table piggy_bank(
id integer primary key autoincrement,
coin text,
coin_year integer text
);
insert into piggy_bank values
(null, 'Q', 1950),
(null, 'P', 1972),
(null, 'N', 2005),
(null, 'Q', 1999);
schema4_1.sql
begin transaction;
update piggy_bank
set coin = 'Q'
where coin = 'P' and coin_year < 1970;
commit;
schema4_1_1.sql
```sql
begin transaction;
update piggy_bank set coin = ‘Q’ where coin = ‘P’ and coin_year < 1970; commit;
```
schema4_2.sql
begin transaction;
update piggy_bank
set coin = 'N'
where coin = 'Q';
rollback;
schema4_2_1.sql
```sql
begin transaction;
update piggy_bank set coin = ‘N’ where coin = ‘Q’; rollback;
```
schema4_3.sql
begin transaction;
update piggy_bank
set coin= 'Q'
where coin = 'N' and coin_year > 1950;
rollback;
schema4_3_1.sql
```sql
begin transaction;
update piggy_bank set coin= ‘Q’ where coin = ‘N’ and coin_year > 1950; rollback;
```
schema4_4.sql
begin transaction;
update piggy_bank
set coin = 'D'
where coin = 'Q';
commit;
schema4_4_1.sql
```sql
begin transaction;
update piggy_bank set coin = ‘D’ where coin = ‘Q’; commit;
```
schema4_5.sql
begin transaction;
update piggy_bank
set coin = 'P'
where coin = 'N' and coin_year > 1970;
commit;
schema4_5_1.sql
```sql
begin transaction;
update piggy_bank set coin = ‘P’ where coin = ‘N’ and coin_year > 1970; commit;
```
入出力結果(Terminal, Zsh)
% ./sample4.py
1.
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'Q', 1999)
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'Q', 1999)
2.
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'Q', 1999)
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'Q', 1999)
3.
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'Q', 1999)
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'Q', 1999)
4.
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'Q', 1999)
['id', 'coin', 'coin_year']
(1, 'D', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'D', 1999)
5.
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'N', 2005)
(4, 'Q', 1999)
['id', 'coin', 'coin_year']
(1, 'Q', 1950)
(2, 'P', 1972)
(3, 'P', 2005)
(4, 'Q', 1999)
%