11月27日(金)2コマ目

今日、やったこと

  • オラクルのデータディクショナリ
  • SQLのDDLの基本パターン
  • ビュー

今日のホワイトボード

オラクルのデータディクショナリ

オラクルはどんなテーブルがある、とかどんなビューがあるといった情報を管理するためのテーブル（のようなモノ）を持っています。これをデータディクショナリと呼びます。

データディクショナリは結構たくさんありますが、名前にパターンがあります。

図　オラクルのデータディクショナリ


このパターンをおさえておけば、とっさのときに対応しやすいです。


SQLのDDLの基本パターン

今まで扱ったSQLは以下の2種類に分類できます。

DML

Data Manipulation Languageの略。

データ操作を行うSELECT、INSERT、UPDATE、DELETEです。
DDL

Data Definition Languageの略。

テーブルやビューなどデータベースオブジェクトの生成、変更、削除を行うCREATE、ALTER、DROPです。


DDLも基本パターンがあります。

図　SQLのDDL基本パターン


ビュー

ビューの作成、変更

ビューの変更を行うALTER VIEWはありません。

かわりにCREATE OR REPLACE VIEWで既存ビューの置き換えができます。

図　CREATE VIEWとCREATE OR REPLACE VIEW


ビューの列に別名を設定

ビューの列名は無指定時はテーブルの列名がそのまま引き継がれます。

別名をつけたい場合は下図のようにビュー名のあとに（）で指定します。

図　ビューの列に別名をつける


ビュー作成

先週作った商品一覧ビューを変更しました。

〇商品一覧ビューに「税込み価格」列を追加

create or replace view 商品一覧

as

select 商品名, ジャンル名, 価格, 価格*1.1 as 税込価格

from 商品マスタ, ジャンルマスタ

where 商品マスタ.ジャンルid=ジャンルマスタ.id;


〇商品一覧ビューの「価格」列を「税抜価格」列に別名を設定

create or replace view 商品一覧(商品名, ジャンル名, 税抜価格, 税込価格)

as

select 商品名, ジャンル名, 価格, 価格*1.1

from 商品マスタ, ジャンルマスタ

where 商品マスタ.ジャンルid=ジャンルマスタ.id;


先週作ったジャンルマスタテーブル、商品マスタテーブルを利用してビューを作成しました。

〇ジャンル名が「めん類」だけの「めん一覧」ビューを作成

列は

  • 商品マスタ.ID => 別名「No」に
  • 商品名
  • 価格

create or replace view めん一覧(No, 商品名, 価格)

as

select 商品マスタ.ID, 商品名, 価格

from 商品マスタ, ジャンルマスタ

where 商品マスタ.ジャンルid=ジャンルマスタ.id and 

ジャンル名='めん類';


〇税込み価格が600以下の商品だけの「六百円以下商品一覧」ビューを作成

列は

  • 商品名
  • ジャンル名
  • 価格 => 別名「税抜価格」に
  • 税込価格

create or replace view 六百円以下商品一覧(商品名, ジャンル名, 税抜価格, 税込価格)

as

select 商品名, ジャンル名, 価格, 価格*1.1

from 商品マスタ, ジャンルマスタ

where 商品マスタ.ジャンルid=ジャンルマスタ.id and 

価格*1.1<=600;


〇ジャンルごとの商品点数、最高値、最安値が取得できる「ジャンル別商品データ」ビューを作成

列は

  • ジャンル名
  • 商品点数
  • 最高値
  • 最安値

create or replace view ジャンル別商品データ(ジャンル名, 商品点数, 最高値, 最安値)

as

select ジャンル名, count(*), max(価格), min(価格)

from 商品マスタ, ジャンルマスタ

where 商品マスタ.ジャンルid=ジャンルマスタ.id 

group by ジャンル名;



コメント

新しいコメントは書き込めません。

このブログの人気の投稿

1月15日(金)2コマ目

今日、やったこと ビューのおさらい課題　その１、その２ 今日の課題 解答例をあげておきます。 ビューのおさらい課題　その１ テーブル作成 商品マスタ create table 商品マスタ( ID number(2), 商品名  nvarchar2(64) not null, 定価 number(5), constraint pk_商品 primary key(ID), constraint chk_商品定価 check(定価>=0) ); 顧客マスタ create table 顧客マスタ( ID number(2), 氏名 nvarchar2(64) not null, constraint pk_顧客 primary key(ID) ); 売上データ create table 売上データ( ID number(4), 商品ID number(2), 顧客ID number(2), 数量 number(4), 売上日 date, constraint pk_売上データ primary key(ID, 商品ID), constraint fk_売上_商品 foreign key(商品ID) references 商品マスタ(ID), constraint fk_売上_顧客 foreign key(顧客ID) references 顧客マスタ(ID), constraint chk_売上数量 check(数量 >= 0) ); ビュー作成 商品別販売回数 create or replace view 商品別販売回数(商品名, 販売回数) as  select 商品名, count(*)  from 商品マスタ, 売上データ where 商品マスタ.ID=売上データ.商品ID group by 商品名; 顧客別商品購入回数 create or replace view 顧客別商品購入回数(顧客名, 商品名, 購入回数) as  select 氏名, 商品名, count(*) from 商品マスタ, 顧客マスタ, 売上データ where 商品マスタ.ID=売上データ.商品ID and 顧客マスタ.ID=売上データ.顧客ID group by 氏名, 商品名 order by 氏名, 商品名; 商品別販売...
続きを読む

1月29日(金)2コマ目

イメージ
今日、やったこと シーケンス 今日のホワイトボード シーケンスとは ID列のように重複してはいけない列の値を生成する仕組み。 図　シーケンス シーケンスから採番する シーケンス名.nextval で採番できる。 直近に採番した値は シーケンス名.currval で取得できる。 シーケンスを作る 図　シーケンスを作る SQLは以下の構文。 create sequence シーケンス名 start with 初期値 increment by 増分 maxvalue 最大値 minvalue 最小値 cycle または nocylce cache または nocache ; 各オプションは以下のとおり。 〇start with 初期値 一番最初に採番される値。 〇increment by 増分 採番するたびに増える値。 負の値にすると、デクリメントするシーケンスになる。 たとえば、start with 1 increment by -1にすると、 1 => 0 => -1 => -2　と採番される。 〇maxvalue 最大値 採番される最大値。最大値は初期値以上、かつ最小値を超える値。 未指定時は最大値は昇順の場合、10の28乗-1、降順の場合は-1。 〇minvalue 最小値 採番される最小値。最小値は初期値以下、かつ最大値未満の値。 未指定時は最大値は昇順の場合、1、降順の場合は-(10の27乗 - 1)。 〇cycle 採番値が最小値と最大値で循環する。 シーケンス採番値が昇順の場合、最大値に達すると次は最小値が採番される。 降順の場合は、最小値に達すると、次は最大値が採番される。 〇nocycle デフォルトはこちら。 循環しない。 〇cache 高速に採番するためにあらかじめメモリ上に採番しておく数を指定。 〇nocache メモリ上に事前採番をしない。デフォルトはこちら。 作成済みシーケンスの変更 変更できるのは、増分(increment by)、最大値(maxvalue)、最小値(minvalue)、キャッシュ(cache)。 SQLは以下のとおり。 alter sequence シーケンス名 　変更対象項目名　設定値 ; 作成済みシーケンスの削除 SQLは以下のとおり。 drop sequence シーケンス名;  
続きを読む