ビューの機能 free62

Wednesday,July,10,2013


○データベースを表示させる。
MySQL> show databases;
○現在接続されているデーターベース
MySQL> select database();
database()
NULL どこのデータベースにも接続していない状況
mysql> USE sadachika_shop;        *データベースを指定する
○テーブルを表示させる。
SHOW TABLES;
○テーブルを作る
mysql> create table sadachika_shop.sadachi_products(sada_id int, sada_name varchar(20), sada_category varchar(20));  *カラム名sada_id INT型, カラム名sada_name VARCHAR型, カラム名sada_category VARCHAR型
○データをカラムに入力
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(1, '牛乳', '飲み物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(2, 'たこ焼き', '食べ物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(3, 'りんご', '果物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(4, 'ワイン', '飲み物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(5, 'ピザ', '食べ物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(6, 'ブドウ', '果物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(7, 'にんじん', '野菜');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(8, 'コーラ', '飲み物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(9, '牛丼', '食べ物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(10, 'キューイフルーツ', '果物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(11, 'ほうれん草', '野菜');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(12, 'ビール', '飲み物');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(13, '天丼', '食べ物');

○データをカラムに入力
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(1, '牛乳', 'Drink');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(2, 'たこ焼き', 'Food');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(3, 'りんご', 'Fruit');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(4, 'ワイン', 'Drink');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(5, 'ピザ', 'Food');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(6, 'ブドウ', 'Fruit');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(7, 'にんじん', 'Vegetables');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(8, 'コーラ', 'Drink');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(9, '牛丼', 'Food');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(10, 'キューイフルーツ', 'Fruit');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(11, 'ほうれん草', 'Vegetables');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(12, 'ビール', 'Drink');
mysql> insert into sadachika_shop.sadachi_products(sada_id, sada_name, sada_category) values(13, '天丼', 'Food');




○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_products;
sada_id    sada_name        sada_category
1          牛乳             飲み物
2          たこ焼き         食べ物
3          りんご           果物
4          ワイン           飲み物
5          ピザ             食べ物
6          ブドウ           果物 
7          にんじん         野菜
8          コーラ           飲み物
9          牛丼             食べ物
10         キューイフルーツ 果物
11         ほうれん草       野菜
12         ビール           飲み物
13         天丼             食べ物
1          牛乳             Drink
2          たこ焼き         Food
3          りんご           Fruit
4          ワイン           Drink
5          ピザ             Food
6          ブドウ           Fruit 
7          にんじん         Vegetables
8          コーラ           Drink
9          牛丼             Food
10         キューイフルーツ Fruit
11         ほうれん草       Vegetables
12         ビール           Drink
13         天丼             Food

失敗○○「sadachi_products」テーブルの中の「sada_category」カラムの値が'食べ物'のビューを作成する。
                      ↓作ったビューをおさめるデータベース名
mysql> create view sadachika_shop.sadachi_viewfoodproducts         *新しく作るビューの名前
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = '食べ物';           *漢字だとなぜか失敗します。アルファベットだとビューの作成が出来ます。
                 ↑元のカラム名 ↑元のカラム名  ↑元のdb名   ↑元のテーブル名    ↑元のカラム名    ↑元のカラムデータ

失敗○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_viewfoodproducts;      *漢字だと失敗する  なんで?


失敗○○「sadachi_products」テーブルの中の「sada_category」カラムの値が'食べ物'のビューを作成する。
mysql> create view sadachika_shop.sadachi_viewdrink
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = '飲み物';

失敗○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_viewdrink;             *漢字だと失敗する  なんで?


失敗○○「sadachi_products」テーブルの中の「sada_category」カラムの値が'果物'のビューを作成する。
mysql> create view sadachika_shop.sadachi_viewfruit5
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = '果物';

失敗○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_viewfruit5;             *漢字だと失敗する  なんで?


失敗○○「sadachi_products」テーブルの中の「sada_category」カラムの値が'Vegetables'のビューを作成する。
mysql> create view sadachika_shop.sadachi_viewvegetables5
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = '野菜';

失敗○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_viewvegetables5;        *漢字だと失敗する  なんで?

○○「sadachi_products」テーブルの中の「sada_category」カラムの値が'Drink'のビューを作成する。                                        ↓カラム名が抜けている
お手本mysql> create view drink as select * from products where category = 'Drink';

mysql> create view sadachika_shop.sadachi_viewdrink2
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = 'drink';

○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_viewdrink2;
sada_id   sada_name
      1   牛乳
      4   ワイン
      8   コーラ
     12   ビール


○○「sadachi_products」テーブルの中の「sada_category」カラムの値が'Food'のビューを作成する。
mysql> create view sadachika_shop.sadachi_viewfood
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = 'food';

○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_viewfood;
sada_id   sada_name
      2   たこ焼き
      5   ピザ
      9   牛丼
     13   天丼


○○「sadachi_products」テーブルの中の「sada_category」カラムの値が'Fruit'のビューを作成する。
mysql> create view sadachika_shop.sadachi_viewfruit
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = 'Fruit';

○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_viewfruit;
sada_id   sada_name
      3   りんご
      6   ブドウ
     10   キューイフルーツ
     

○○「sadachi_products」テーブルの中の「sada_category」カラムの値が'Vegetables'のビューを作成する。
mysql> create view sadachika_shop.sadachi_viewvegetables
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = 'Vegetables';

○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_viewvegetables;
sada_id   sada_name
      7   にんじん
     11   ほうれん草
     


○○もう一つ試してみます。「products」テーブルの中の「category」カラムの値を重複せずに取得するビューを作成します。
作ったビューを収めるdbの場所↓        ↓ビュー名
mysql> create view sadachika_shop.sadachi_categorylist                  *新しく作るビューの場所であるデータベース名,ビュー名
    -> as select distinct sada_category from sadachi_products;
                               ↑元のカラム名  ↑元のテーブル名
○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_categorylist;
飲み物
食べ物果物
野菜
Drink
Food
Fruit
Vegetables

未だ○テーブルの内を検索
mysql> SELECT * FROM sadachika_shop.sadachi_products WHERE sada_category = '食べ物';     *日本語で検索結果をしぼれるのに、ビューの作成はできない
sada_id sada_name sada_category
      2 たこ焼き   食べ物
      5 ピザ       食べ物     
      9 牛丼       食べ物
     13 天丼       食べ物


○○ビューのカラム名を指定し変更する
CREATE VIEW db_name.view_name(column_list) AS select_statement;
create view user(userid, username) as select id, name from personal;

   作ったビューを収めるdbの場所↓      ↓ビュー名       ↓ビューのカラム名 ↓ビューのカラム名
mysql> create view sadachika_shop.hiraya_fruitproducts(hira_id, hira_fruitname)
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = 'Fruit';
                  ↑元カラム名 ↑元カラム名 ↑元db名     ↑元テーブル名          ↑元カラム名    ↑データ

○テーブルの中身の表示 
mysql> select * from sadachika_shop.hiraya_fruitproducts;
hira_id   hira_fruitname
      3   りんご
      6   ブドウ
     10   キューイフルーツ

○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_products;
SHOW TABLES FROM sadachika_shop;

○○指定した名前のビューを作成するためのCREATE VIEW文を取得
SHOW CREATE VIEW view_name;
mysql> show create view sadachika_shop.hiraya_fruitproducts\G  
****************** 1. row *******************
View: hiraya_fruitproducts
Create View: CREATE ALGORITHM=UNDEFINED DEFINER= 'root'@'localhost'SQL
SECURITY DEFINER VIEW 'hiraya_fruitproducts' AS select 'sadachi_products'.'sada_id' AS 'hira_id',
'sadachi_products'.'sada_name' AS 'hira_fruitname' from 'sadachi_products' where('sadachi_products'.'sada_category'='Fruit')
character_set_client: cp932
collation_connection: cp932_japanese_ci
1 row in set (0.02 sec)

○○ビューではないテーブルをCREATE VIEW文で使ってみる。
SHOW CREATE VIEW view_name;
mysql> show create view sadachika_shop.sadachi_products\G 
ERROR 1347 (HY000): 'sadachika_shop.sadachi_products' is not VIEW

○○元のカラムをそのままに、新しいビューを作る
                                                            ↓元のカラム名を特定していない(全てを選択している)
mysql> create view sadachika_shop.sadachi_drink15 as select * from sadachika_shop.sadachi_products where sada_category = 'Drink';
                                           ↑新しく作るビューの名前

○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_drink15;
sada_id    sada_name   sada_category
      1    牛乳        Drink
      4    ワイン      Drink
      8    コーラ      Drink
     12    ビール      Drink

○○指定した名前のビューを作成するためのCREATE VIEW文を取得
mysql> show create view sadachika_shop.sadachi_drink15\G  
****************** 1. row *******************
View: sadachi_drink15
Create View: CREATE ALGORITHM=UNDEFINED DEFINER= 'root'@'localhost'SQL
SECURITY DEFINER VIEW 'sadachi_drink15' AS select 'sadachi_products'.'sada_id' AS 'sada_id',
'sadachi_products'.'sada_name' AS 'sada_name', 
'sadachi_products'.'sada_category' AS 'sada_category'
 from 'sadachi_products' where('sadachi_products'.'sada_category'='Drink')
character_set_client: cp932
collation_connection: cp932_japanese_ci
1 row in set (0.03 sec)

○テーブルの削除場合はこんな感じ
             ↓テーブル
mysql> drop table sadachika_shop.sadachi_drink15;                       *ビューをテーブルを削除する式では、削除できない
ERROR 1051 (42S02): Unknown table 'sadachika_shop.sadachi_drink15'

○○ビューの削除
             ↓ビュー
mysql> drop view sadachika_shop.sadachi_drink15;  *削除できました
Query OK, 0 rows affected (0.03 set)

○テーブルの中身の表示 
SHOW TABLES FROM sadachika_shop;

○○新規、又は上書きでビューを作成する。
replace 取って代わる.
                 ↓既に同名のビュー名があれば上書きする     ↓新規のビュー名 
mysql> create or replace view sadachika_shop.sadachi_vegetables         *既存のテーブル名にsadachi_vegetablesは無いので新規でビューを作成する
    -> as select sada_id, sada_name from sadachika_shop.sadachi_products where sada_category = 'Vegetables';
                 ↑元カラム名 ↑元カラム名 ↑db名            ↑元のテーブル名       元カラム名     元カラムデータ
○テーブルの中身の表示 
SHOW TABLES FROM sadachika_shop;
Tables_in_sadachika_shop
sadachi_vegetables  *増えた

○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_vegetables;
sada_id    sada_name
      7    にんじん
     11    ほうれん草

○○新規、又は上書きでビューを作成する。
mysql> create or replace view sadachika_shop.sadachi_vegetables         *既存のテーブル名にsadachi_vegetablesがあるので上書きする  
    -> as select * from sadachika_shop.sadachi_products where sada_category = 'Vegetables';   
                 ↑元カラム全選択

○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_vegetables;
sada_id    sada_name    sada_category   *カテゴリが増えたね、上書きされた。
      7    にんじん     Vegetables
     11    ほうれん草   Vegetables

○○ビューとカラムの名前を変える         ↓新テーブル上書きされる  ↓カラム名を変更するsada_name→hira_vegetablename
mysql> alter view sadachika_shop.sadachi_vegetables(hira_vegetablename)
    -> as select sada_name from sadachika_shop.sadachi_products where sada_category = 'vegetables';
                 ↑元カラム名          ↑db名          ↑元テーブル名      ↑元カラム名  ↑元カラムデータ
           
○○テーブルの中身の表示 
mysql> select * from sadachika_shop.sadachi_vegetables;
hira_vegetablename      *カラム名が1つになり名前も変わった。
にんじん
ほうれん草


* 参考にしたサイトは→ こちら です。

これは赤色の文字例です。

これは青色の文字例です。