ViewとTableで同じデータを持ったオブジェクトを作成する事が出来るが、
それぞれメリットデメリットがある。Viewは常に最新のデータが反映されるという
メリットがあるが、毎回計算が入るので速度が遅い。Tableに関しては、速度は速いが
最新のデータが常に反映されない。用途にもよるので、それぞれ最適な方法を
選択する必要がある。最適なのは、テーブルのデータが常に最新のデータで反映
される事だが、Viewを必要とする時は対外テーブルが分散されている場合が多い…

以下のテーブルとビューは同じ結果を抽出することが出来る。

CREATE VIEW V_Surface
(Name, ContinentSurface, CountryAvgSurface)
AS SELECT Continent, SUM(SurfaceArea),
AVG(SurfaceArea)
FROM Country GROUP BY Continent;


CREATE TABLE T_Surface
AS SELECT Continent AS Name,
SUM(SurfaceArea) AS ContinentSurface,
AVG(SurfaceArea) AS CountryAvgSurface
FROM Country GROUP BY Continent;

table_vs_view


大陸の名前、面積が入っているテーブルをベースに大陸毎の面積や各大陸に存在する
国の平均面積を算出するViewを作成。

下記2つの内容は同じViewになります。


Create View V_Surface
(NAME, SUM_Surface, AVG_Suface) AS
select continent, sum(SurfaceArea),avg(SurfaceArea)
from Country Group by Continent;


Create View V_Surface2 AS
select continent as NAME, sum(SurfaceArea) as SUM_Surface,
avg(SurfaceArea) as AVG_Suface from Country Group by Continent;

create_view_surface

上記のViewはSUMなどのAggregate funcation(集合関数)を利用していることGroup byを
利用しているので、更新することは出来ない。

view_update_fail

——————————————————————-
・データベース構造を意識させない。
・正規化によって分解された表を、Viewにて組み合わせて一つのテーブルのように操作可能。
・Tableへ直接の権限を付与しないでViewにて権限を制限する事でセキュリティ強化
・複数の表からなるビューは更新できない。
・集合関数や演算を使用して作成したViewはUpdateできない。


ビュー(更新可能なビューを含む) はMySQL Server 5.1から入手可能。
旧バージョンMySQLを5.1にアップグレードした場合、ビューの使用はビュー関連の
権限を含むようにグラントテーブルもアップグレードが必要です。

———————————
メモ
———————————
mysql_upgrade コマンドは、古い方、つまり mysql_fix_privilege_tables より優先です。
MySQL 5.1.7 では、シェル スクリプト として mysql_upgrade が加えられ、Unix システムだけで機能します。
MySQL 5.1.10 以降は、mysql_upgrade は実行可能なバイナリとして、すべてのシステムで使用できます。
mysql_upgrade をサポートしているものより古いシステムでは、手動で mysqlcheck コマンドを実行し、
システム テーブルのアップグレードを行ないます

ビューの現在の実装には欠点があります。もしユーザがビューの作成に必要な基本権限
( CREATE VIEW と SELECT 権限 ) を取得した場合、SHOW VIEW 権限も取得しない限り、
そのユーザはオブジェクトの SHOW CREATE VIEW を呼び出すことはできない。

PREPARE によって準備されたステートメントがビューを参照する場合、後でステートメントが実行される度
に参照されるビューの内容が、ステートメントが準備できた時のビューの内容になります。
これは、ステートメントが準備された後、実行される前にビュー定義が変更されても同じ。


CREATE VIEW v AS SELECT 1;
PREPARE s FROM 'SELECT * FROM v';
ALTER VIEW v AS SELECT 2;
EXECUTE s;

view11

幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、
UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。
ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在しなければなりません。
ビューを更新不能にするその他の生成子もあります。もっと具体的に言うと、それが以下のいずれかを含んでいると
ビューは更新可能となりません。

* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
* DISTINCT
* GROUP BY
* HAVING
* UNION もしくはUNION ALL
* 選択リスト中のサブ・クエリ
* 特定結合(このセクション中の後の部分に追加した結合の説明参照)
* FROM節中の更新不能ビュー
* FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ
* 文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)
* ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)

(INSERTステートメントで更新不能となる)挿入性に関して、それがビューカラムに対する
これらの追加条件も満たすと、更新不能ビューが挿入可能になります。

* ビューカラム名に重複があってはなりません。
* ビューには、デフォルト値を持っていないベーステーブル内にある全てのカラムを含んでいなくてはなりません。
* ビューカラムは派生カラムではなく、単純なカラムリファレンスでなければなりません。
派生カラムは単純なカラムリファレンスでなく、表現から派生したものです。これらは派生カラムの例です。

3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

UPDATE,INSERTが可能か確認検証


CREATE VIEW V_Region (CityName, CountryName)
AS SELECT City.Name, Country.Name FROM City, Country
WHERE City.CountryCode = Country.Code
AND City.CountryCode = 'DEU';

VIEW
create_view

    UPDATE確認


update V_Region set CityName = 'HAMBURG'
where CityName = 'Hamburg';

1つのテーブルしか更新されないのでエラーにはならない。
view_update

複数テーブルを更新しようとするとUPDATEもエラーになる

update V_Region set
CityName = 'Hamburg',
CountryName = 'Japan'
where CityName = 'HAMBURG';

複数テーブルを更新しようとしているのでエラーになる
view_update_multi

    INSERT確認

insert into V_Region(CityName,CountryName) values('NewCity','Japan');

※ 複数のテーブルを更新することになるのでエラーになる。
view_insert1

参考サイト
20.2. CREATE VIEW 構文


D.4. ビューの規制


4.5.4. mysql_upgrade — MySQL アップグレードのテーブル チェック


GROUP_CONCAT、サブクエリーを利用してデータの抽出を行い
CONCATを用いて文字列を連結する。

CONCAT(str1,str2,…)

引数を連結した結果であるストリングを戻します。ひとつ以上の引数を持つ場合があります。
すべての引数が非バイナリ ストリングである場合、結果は非バイナリ ストリングになります。
引数がひとつでもバイナリ ストリングを含む場合は、結果はバイナリ ストリングになります。
数値の引数はそれに等しいバイナリ ストリング形態に変換されます。それを避けたい場合は、
次の例のように、明示的なタイプ キャストを使用することができます :

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

サンプルテーブル
sample

サンプルデータ
sample_data

上記のテーブルからオフィシャル言語が多い国を抽出してみる。


select B.NAME,Count(*) SUM from
CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
Order by SUM desc
limit 1;

select_official

select B.NAME,Count(*) SUM,
GROUP_CONCAT(Language) LANG
from CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
ORDER BY SUM DESC
limit 1;

gc1


SELECT TMP.NAME,TMP.SUM,TMP.LANG FROM
(
select B.NAME,Count(*) SUM,
GROUP_CONCAT(Language) LANG
from CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
ORDER BY SUM DESC
limit 1
) AS TMP;

gc2

上記結果をCONCATで結合して文言にする。

SELECT
CONCAT(
'The country ',
(SELECT Name FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp
),
' has ',
(SELECT nlanguages FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp1
),
' official languages: ',
(SELECT languages FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp2
)
) AS '文字の結合';

concat_sentenct

参考サイト
11.11.1. GROUP BY (Aggregate) Functions


11.3. 文字列関数


「サブクエリー+IN」、「LEFT JOIN」、「RIGHT JOIN」によりテーブルから同じ結果を抽出
出来ますが、INNER JOINでも同じ結果を抽出する事が出来ます。


SELECT PID,ID,NAME,START,END
FROM project WHERE project.id IN
(SELECT client.id FROM client);


select B.PID,B.ID,B.NAME,B.START,B.END from client A
LEFT JOIN project B using(ID) /* or: ON A.ID = B.ID */
WHERE B.NAME IS NOT NULL;


select B.PID,B.ID,B.NAME,B.START,B.END from client A
LEFT JOIN project B ON A.ID = B.ID /* or: using(ID) */
WHERE B.NAME IS NOT NULL;


select B.PID,B.ID,B.NAME,B.START,B.END from project B
RIGHT JOIN client A ON A.ID = B.ID /* or: using(ID) */
WHERE B.NAME IS NOT NULL;

INNER JOIN


select B.PID,B.ID,B.NAME,B.START,B.END from client A
INNER JOIN project B using(ID) /* or: ON A.ID = B.ID */
WHERE B.NAME IS NOT NULL;


select B.PID,B.ID,B.NAME,B.START,B.END from client A
INNER JOIN project B ON A.ID = B.ID /* or: using(ID) */
WHERE B.NAME IS NOT NULL;

inner_join_on

参考サイト
12.2.7.1. JOIN 構文


「サブクエリー+IN」、「LEFT JOIN」、「RIGHT JOIN」により
テーブルから同じ結果を抽出。

General Query (IN)

SELECT * FROM project WHERE project.id IN
(SELECT client.id FROM client)

LEFT JOIN

select B.PID,B.ID,B.NAME,B.START,B.END from client A
LEFT JOIN project B using(ID) /* or: ON A.ID = B.ID */
WHERE B.NAME IS NOT NULL;

RIGHT JOIN

select B.PID,B.ID,B.NAME,B.START,B.END from project B
RIGHT JOIN client A using(ID) /* or: ON A.ID = B.ID */
WHERE B.NAME IS NOT NULL;

right_join


    INで書いたサブクエリーをLEFT JOINにて書き換える


CREATE TABLE `client` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=UTF8;


CREATE TABLE `project` (
`PID` int(11) NOT NULL,
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` char(35) NOT NULL DEFAULT '',
`START` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`END` timestamp NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8;

sub_a


mysql> insert into client(Name) values('company-a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into client(Name) values('company-b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into client(Name) values('company-c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10000,'Project1','2009-12-15');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10001,'Project2','2009-12-15');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10002,'Project3','2009-12-16');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10003,'Project4','2009-12-19');
Query OK, 1 row affected (0.00 sec)
mysql> insert into project(pid,name,end) values(10004,'Project5','2009-12-30');
Query OK, 1 row affected (0.00 sec)

table_select

    Clientがいるプロジェクトのみ抽出


mysql> select * from project where project.ID IN
-> (select client.ID from client);

client

    上記QueryをLEFT JOINで書き直してみる


select B.PID,B.ID,B.NAME,B.START,B.END from client A
LEFT JOIN project B using(ID) /* or: ON A.ID = B.ID */
WHERE B.NAME IS NOT NULL;


select B.PID,B.ID,B.NAME,B.START,B.END from client A
LEFT JOIN project B ON A.ID = B.ID /* or: using(ID) */
WHERE B.NAME IS NOT NULL;

left_join


CItyテーブルから首都のみを選んでCapitalテーブルを作成。

city

1) テーブル作成
CREATE TABLE Capitals LIKE City;

create_table_like

2) 首都データの投入

INSERT INTO Capitals SELECT * FROM City
where ID IN(select Capital from Country where Capital is not null);

insert_into

select * from Capitals where name like 'to%' order by name;

tokyo

=================
その他の方法(全てのデータをいれてから削除
=================


mysql> CREATE TABLE Capitals LIKE City;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Capitals SELECT * FROM City;
Query OK, 4079 rows affected (0.17 sec)
Records: 4079 Duplicates: 0 Warnings: 0

mysql> delete from Capitals where ID NOT IN
-> (select Capital from Country where Capital is not null);
Query OK, 3847 rows affected (4.86 sec)

mysql> select count(*) from Capitals;
+----------+
| count(*) |
+----------+
| 232 |
+----------+
1 row in set (0.00 sec)

mysql> select * from Capitals where name like 'to%' order by name;
+------+---------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------+-------------+----------------+------------+
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 3503 | Toskent | UZB | Toskent Shahri | 2117500 |
+------+---------+-------------+----------------+------------+
2 rows in set (0.01 sec)

mysql>

delete


サブクエリーのSELECTステートメントを利用して次のテーブルから,Western Europeの中で
ドイツ語を母国語とする人口を算出するクエリーサンプル。

テーブルサンプル
subquery_select

Country には人口
CountryLanguageには言語毎の割合が%で入っている


select sum(tmp.German_Speakers) from
(
select (A.Population * B.Percentage) / 100 as 'German_Speakers'
from Country A, CountryLanguage B
where A.Region = 'Western Europe'
and A.Code = B.CountryCode
and B.Language = 'German'
) as tmp;

population

参考サイト

12.2.9.1. The Subquery as Scalar Operand


MS SQL2000, 2005, 2008にてデータベースに作成されたインデックスの調査する為のクエリー


SELECT "table" = object_name(i.id), i.name,
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
"column" = col_name(i.id, ik.colid), ik.keyno
FROM sysindexes i
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(i.id, name, 'IsHypothetical') = 0
AND indexproperty(i.id, name, 'IsStatistics') = 0
AND indexproperty(i.id, name, 'IsAutoStatistics') = 0
AND objectproperty(i.id, 'IsMsShipped') = 0
ORDER BY "table", "isclustered" DESC, i.name, ik.keyno

インデックス調査
db_index_confirm1