開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-87311-281-8) の4章(フィルタリング), 4.5(練習問題)4-1.を解いてみる。
4-1.
返されるトランザクションIDは、1、2、3、5、6、7。
t欄ザクションデータのテーブルを作成、確認。
入出力結果(Terminal)
$ mysql -u lrngsql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tabases;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tabases' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bank |
| mysql |
| performance_schema |
| sample_store |
| store |
| test |
+--------------------+
7 rows in set (0.03 sec)
mysql> use bank;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_bank |
+----------------+
| account |
| branch |
| business |
| customer |
| department |
| employee |
| favorite_food |
| individual |
| officer |
| person |
| product |
| product_type |
| transaction |
+----------------+
13 rows in set (0.00 sec)
mysql> desc transaction;
+---------------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+----------------------+------+-----+---------+----------------+
| txn_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| txn_date | datetime | NO | | NULL | |
| account_id | int(10) unsigned | NO | MUL | NULL | |
| txn_type_cd | enum('DBT','CDT') | YES | | NULL | |
| amount | double(10,2) | NO | | NULL | |
| teller_emp_id | smallint(5) unsigned | YES | MUL | NULL | |
| execution_branch_id | smallint(5) unsigned | YES | MUL | NULL | |
| funds_avail_date | datetime | YES | | NULL | |
+---------------------+----------------------+------+-----+---------+----------------+
8 rows in set (0.04 sec)
mysql> desc transaction;
+---------------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+----------------------+------+-----+---------+----------------+
| txn_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| txn_date | datetime | NO | | NULL | |
| account_id | int(10) unsigned | NO | MUL | NULL | |
| txn_type_cd | enum('DBT','CDT') | YES | | NULL | |
| amount | double(10,2) | NO | | NULL | |
| teller_emp_id | smallint(5) unsigned | YES | MUL | NULL | |
| execution_branch_id | smallint(5) unsigned | YES | MUL | NULL | |
| funds_avail_date | datetime | YES | | NULL | |
+---------------------+----------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)
mysql> select * from transaction;
+--------+---------------------+------------+-------------+--------+---------------+---------------------+---------------------+
| txn_id | txn_date | account_id | txn_type_cd | amount | teller_emp_id | execution_branch_id | funds_avail_date |
+--------+---------------------+------------+-------------+--------+---------------+---------------------+---------------------+
| 1 | 2000-01-15 00:00:00 | 1 | CDT | 100.00 | NULL | NULL | 2000-01-15 00:00:00 |
| 2 | 2000-01-15 00:00:00 | 2 | CDT | 100.00 | NULL | NULL | 2000-01-15 00:00:00 |
| 3 | 2004-06-30 00:00:00 | 3 | CDT | 100.00 | NULL | NULL | 2004-06-30 00:00:00 |
| 4 | 2001-03-12 00:00:00 | 4 | CDT | 100.00 | NULL | NULL | 2001-03-12 00:00:00 |
| 5 | 2001-03-12 00:00:00 | 5 | CDT | 100.00 | NULL | NULL | 2001-03-12 00:00:00 |
| 6 | 2002-11-23 00:00:00 | 7 | CDT | 100.00 | NULL | NULL | 2002-11-23 00:00:00 |
| 7 | 2002-12-15 00:00:00 | 8 | CDT | 100.00 | NULL | NULL | 2002-12-15 00:00:00 |
| 8 | 2003-09-12 00:00:00 | 10 | CDT | 100.00 | NULL | NULL | 2003-09-12 00:00:00 |
| 9 | 2000-01-15 00:00:00 | 11 | CDT | 100.00 | NULL | NULL | 2000-01-15 00:00:00 |
| 10 | 2004-09-30 00:00:00 | 12 | CDT | 100.00 | NULL | NULL | 2004-09-30 00:00:00 |
| 11 | 2004-01-27 00:00:00 | 13 | CDT | 100.00 | NULL | NULL | 2004-01-27 00:00:00 |
| 12 | 2002-08-24 00:00:00 | 14 | CDT | 100.00 | NULL | NULL | 2002-08-24 00:00:00 |
| 13 | 2004-12-28 00:00:00 | 15 | CDT | 100.00 | NULL | NULL | 2004-12-28 00:00:00 |
| 14 | 2004-01-12 00:00:00 | 17 | CDT | 100.00 | NULL | NULL | 2004-01-12 00:00:00 |
| 15 | 2001-05-23 00:00:00 | 18 | CDT | 100.00 | NULL | NULL | 2001-05-23 00:00:00 |
| 16 | 2001-05-23 00:00:00 | 19 | CDT | 100.00 | NULL | NULL | 2001-05-23 00:00:00 |
| 17 | 2003-07-30 00:00:00 | 21 | CDT | 100.00 | NULL | NULL | 2003-07-30 00:00:00 |
| 18 | 2004-10-28 00:00:00 | 22 | CDT | 100.00 | NULL | NULL | 2004-10-28 00:00:00 |
| 19 | 2004-06-30 00:00:00 | 23 | CDT | 100.00 | NULL | NULL | 2004-06-30 00:00:00 |
| 20 | 2002-09-30 00:00:00 | 24 | CDT | 100.00 | NULL | NULL | 2002-09-30 00:00:00 |
| 21 | 2003-07-30 00:00:00 | 28 | CDT | 100.00 | NULL | NULL | 2003-07-30 00:00:00 |
+--------+---------------------+------------+-------------+--------+---------------+---------------------+---------------------+
21 rows in set (0.02 sec)
mysql> create table tmp
-> (txn_id smallint unsigned,
-> txn_date DATE,
-> account_id int,
-> txn_type_cd enum('CDT','DBT'),
-> amount int,
-> constraint pk_txn primary key(txn_id)
-> );
Query OK, 0 rows affected (1.35 sec)
mysql> desc tmp
-> ;
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| txn_id | smallint(5) unsigned | NO | PRI | 0 | |
| txn_date | date | YES | | NULL | |
| account_id | int(11) | YES | | NULL | |
| txn_type_cd | enum('CDT','DBT') | YES | | NULL | |
| amount | int(11) | YES | | NULL | |
+-------------+----------------------+------+-----+---------+-------+
5 rows in set (0.41 sec)
mysql> alter table tmp modify txn_id smallint unsigned auto_increment;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tmp;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| txn_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| txn_date | date | YES | | NULL | |
| account_id | int(11) | YES | | NULL | |
| txn_type_cd | enum('CDT','DBT') | YES | | NULL | |
| amount | int(11) | YES | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> insert into tmp
-> (txn_id,txn_date,account_id,txn_type_cd,amount)
-> values(1,'2005-02-22',101,'CDT',1000.00);
Query OK, 1 row affected (0.07 sec)
mysql> select * from tmp;
+--------+------------+------------+-------------+--------+
| txn_id | txn_date | account_id | txn_type_cd | amount |
+--------+------------+------------+-------------+--------+
| 1 | 2005-02-22 | 101 | CDT | 1000 |
+--------+------------+------------+-------------+--------+
1 row in set (0.00 sec)
mysql> alter table tmp modify account_id int autoincrement;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'autoincrement' at line 1
mysql> alter table tmp modify account_id int auto_increment;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> alter table tmp modify amount double;
Query OK, 1 row affected (0.56 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from tmp;
+--------+------------+------------+-------------+--------+
| txn_id | txn_date | account_id | txn_type_cd | amount |
+--------+------------+------------+-------------+--------+
| 1 | 2005-02-22 | 101 | CDT | 1000 |
+--------+------------+------------+-------------+--------+
1 row in set (0.00 sec)
mysql> insert into tmp
-> (txn_date,account_id,txn_type_cd,amount)
-> values('2005-02-23',102,'CDT',525.75);
Query OK, 1 row affected (0.03 sec)
mysql> insert into tmp
-> (txn_date,account_id,txn_type_cd,amount) -> values('2005-02-24',101,'DBT',100.00);Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp (txn_date,account_id,txn_type_cd,amount) values('2005-02-24',103,'CDT',55);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp (txn_date,account_id,txn_type_cd,amount) values('2005-02-25',101,'DBT',50);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp (txn_date,account_id,txn_type_cd,amount) values('2005-02-25',103,'DBT',25);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp (txn_date,account_id,txn_type_cd,amount) values('2005-02-25',102,'CDT',125.37);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp (txn_date,account_id,txn_type_cd,amount) values('2005-02-26',103,'DBT',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp (txn_date,account_id,txn_type_cd,amount) values('2005-02-27',101,'CDT',75);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tmp;
+--------+------------+------------+-------------+--------+
| txn_id | txn_date | account_id | txn_type_cd | amount |
+--------+------------+------------+-------------+--------+
| 1 | 2005-02-22 | 101 | CDT | 1000 |
| 2 | 2005-02-23 | 102 | CDT | 525.75 |
| 3 | 2005-02-24 | 101 | DBT | 100 |
| 4 | 2005-02-24 | 103 | CDT | 55 |
| 5 | 2005-02-25 | 101 | DBT | 50 |
| 6 | 2005-02-25 | 103 | DBT | 25 |
| 7 | 2005-02-25 | 102 | CDT | 125.37 |
| 8 | 2005-02-26 | 103 | DBT | 10 |
| 9 | 2005-02-27 | 101 | CDT | 75 |
+--------+------------+------------+-------------+--------+
9 rows in set (0.00 sec)
mysql> select txn_id from tmp
-> where txn_date < '2005-02-26'
-> and (txn_type_cd = 'DBT' or amount > 100);
+--------+
| txn_id |
+--------+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 7 |
+--------+
6 rows in set (0.00 sec)
mysql> quit;
Bye
$
0 コメント:
コメントを投稿