SQL自習資料
ホームページを見てmysqlをインストールしてください
mysqlを起動できることを確認してください
(説明ではユーザ名mush, パスワードpotato)
% mysql -u mush -p mydb Enter password:****** メッセージ mysql>show tables; usertable mysql> (出力の枠線は省略、以下同様)
SQLの文法
問合せ文
(Query)とアクション文
問合せ文: select <対象> from <テーブル> where <条件>; アクション文: create, drop, insert, delete, ...
; を入力するとサーバに送られる
複数の文(コマンド)をファイルから読み込むこともできる mysql> select user_name from usertable;
実習
1
ex01.sqlを読み込んで動作を確認せよ
ex01.sqlを以下のように変更した場合の
エラーメッセージを確認せよ
; を忘れる
user_id のアンダースコアを忘れる
from 以下を省略する (select user_id ;)
user_id を * に置き換える
select文では計算もできる。以下を実行せよ
select (3+4)*7, SIN(PI()/2) ;問合せ文
(1)
テーブルの列を指定して抽出
select <列名リスト> from <テーブル名> ; 列名リスト : 列名を, で区切る。 * は全てuser_id user_name user_pass 1001 Suzuki foo
1002 Sato baa 1003 Tanaka grr 列名
問合せ文
(2)
条件つき抽出
select <列名リスト> from <テーブル名> where <条件>; 条件は任意の式 user_id > 1001 user_name = “Suzuki” (user_id = 1001 or user_id = 1002) 文字列は“”で囲む AND,ORは()で囲む
ソートする場合は以下を付加する
order by <列名> : 昇順ソート order by <列名> desc : 降順ソート実習
2
ex02.sqlを実行せよ
条件やソート順序を変更し、結果を確認せよ
同一の要素を含む場合に、ソートした結果を別の
キーを利用してソートする方法を調査せよ
(内容の追加を学習した後で行うこと)
テーブルの作成
(1)
作成前に消去すること(二重作成はエラー)
drop table if exists roomtable; create table roomtable (
room_id int unique, room_type char(2), room_capacity int(2), room_name varchar(20), last_used date, primary_key(room_id) ); ex03.sql(抜粋) 整数, 重複を許さない 文字列(2文字) 短整数(2ワード) 可変文字列(最大20文字) 列の型 日付
テーブルの作成
(2)
テーブルの詳細表示
describe <テーブル名>Field Type Null Key Default Extra room_id int(11) PRI 0 room_capacity int(2) YES NULL room_name varchar(20) YES NULL last_used date YES NULL room_type char(2) YES NULL
フィールド名 型 ヌルを認めるか デフォルト値
データの追加と削除
insert/deleteコマンド
insert into <テーブル> values <値> mysql> insert into roomtable values
-> (101, “SG”, 1, “Room #101”, “04-01-31”); OK ...
mysql> select * from roomtable; 同idで別の項目を追加するとエラー
型が合わないと強制変換, 出来ない場合はデフォルト値 delete from <テーブル> where <条件>
条件に一致するデータをテーブルから削除 条件を省略すると全項目を削除(要注意!)
実習
3
ex3.sqlを実行してテーブルを作成せよ
以下のデータを追加して結果を確認せよ
(101, 1, “SG”, “Room #101”, “04-01-31”) (102, “SGL”, 1, “Room #102”, “04-01-29”) (103, “TW”, 2, “Room #103, 1001)(104, “TW”, 2, “Very Very Long Room Name”, “04-02-01”) (105, “TW”, 2, “Room #103, “04-01-40”)
データの読み込み
1.
insert文をファイルに書いておいて読み込み
2.
テキストファイルを直接読み込み
delete from roomtable ; insert into roomtable values
(101, “SG”, 1, “Room #101”, “04-01-31”), (102, “TW”, 2, “Room #102”, “04-02-02”), ... ;
ex04.sql
作成が面倒 列をtab(‘¥u0009’)で区切って作成すること
mysql> load data local inflie “ex04.txt” into table roomtable; (tab区切りテキストはtrコマンドで作成可能)
実習
4
ex04.jspを読み込み、結果を確認せよ
一旦roomtableを消去し、今度はex04.txtから
データを読み込み、結果を確認せよ
読み込んだ後で誤りに気づいた場合、
表の一部のみ修正するにはupdateを用いる。
下記の例を試してみよ
update roomtable set last_used=“04-02-02” where room_id = 101 ;
表の演算
列名の部分には式も記述できる
範囲指定にはgroup byを用いる
selectは入れ子にできる
select <式> from <テーブル名> [where <条件式>]
COUNT(項目) : 項目の個数 SUM(項目) : 項目の合計 MAX(項目) : 最大値 MIN(項目) : 最小値 平均と分散は 組み込み関数なし
select * from roomtable where room_capcity = (select MAX(room_capacity) from roomtable) ; select last_used, COUNT(room_id) from roomtable
group by last_used;
表の演算
(つづき)
問合せ結果を別の表に格納できる
create table <表> select <フィールド列> from ... ; insert into <表> select <式> from ... ;
例: room_idと未使用日数のみの表を作成 create table tmp
select room_id, (CURDATE()-last_used) as unused from roomtable ;
表の演算
(つづき)
その他の演算
演算結果に基づいた抽出: whereの代わりにhavingを用いる select room_id, CURDATE()-last_used as unused
from roomtable having unused = 4; 例1: 4日間未使用の部屋のみ抽出
パターンマッチ: like, regexpを用いる 例2: 10X号室のみ抽出
select * from roomtable where room_id like “10_”; 例3: 名前に(A)を含む部屋のみ抽出
select * from roomtable where room_name like “%(A)”; _ : 任意の1文字にマッチ, %: 任意の文字列(0個以上)にマッチ
実習
5
ex05.sqlを実行せよ
select文と結果の対応に注意せよ
以下の表を作成せよ
room_type total_capacity SG ??? TW ??? SU ??? シングル/ツイン/スイート 各部屋タイプ毎の合計収容人数 ヒント: group byを使うと 1行で書ける表の結合
(1)
複数の表の項目をまとめて一つの表にする
⇒JOIN操作
Field0 Field1 Field0 Field2 v00 v01 v00 v02
Field0 Field1 Field2 v00 v01 v02
+
=
t1 t2
select f0, f1, f2 from t1, t2 where t1.f0=t2.f0; Joinの実行
表の結合
(2)
Joinの種類
標準: t1 inner join t2 (省略形: t1,t2)
左固定: t1 left join t2 on <条件>
右固定: t1 right join t2 on <条件>
f0 f1 f0 f2実習
6
ex06.sqlを実行せよ
joinには他にも以下のようなものがある
実行して違いを確認せよ
tmp1とtmp3, tmp2とtmp3だとどうなるか?
自然結合 : t1 natural join t2 外結合 : t1 outer join t2自然外結合: t1 natural outer join t2
正規形
DBに格納する情報は正規形にする必要あり
第1正規形: 列の項目が原子的(これ以上分解できない) 第2正規形: 列の項目間に従属関係*がない 第3正規形: 任意の列の項目を2個取ってきたとき AはBに従属だがBはAが従属でない 組み合わせが存在しない *従属関係: Aを決めればBが一意に決まる より厳密にはキー候補かそれ以外かで異なるid type capacity name 105 SG 1 105 205 SG 1 205 typeがSGならcapacityは必ず1 ⇒従属関係 nameに複合情報あり ⇒列を分割 201 TW 2 201(B)
正規形への変換
複合項目の分割
従属関係の解消⇒複数の表への分割
正規化された表 type capacity SG 1 TW 2id type name grade 104 TW #104 A 105 SG #105 NULL 201 TW #201 B inner joinすれば元の表が得られる
JDBC
JavaプログラムからDBを利用する方法
java.sql.* 名前空間をインポート
アクセス用オブジェクト
(インターフェース)
Connection : DBとの接続を抽象化 Stetement: DBへの問合せ ResultSet : DBからの応答データの読み込み
基本的な問合せの形式
try {
Class.forName(<jdbcドライバ名>)
Connection con = DriverManager.getConnection(...); Statement st = con.createStatement(); ResultSet rs = st.executeQuery(“select ...”); // 前処理 white (rs.next()) { // rs.getInt(), rs.getString() など } // 後処理
rs.close(); st.close(); con.close(); } catch (Exception e) { ... } Ex01.java(抜粋)
実習
7
Ex01.javaをコンパイルし実行せよ
ドライバ名、ユーザ名などが正しくないコードを作
成/実行し、どのようなエラーが出るか確認せよ
roomtableの内容を出力するように修正せよ
(日付の取り出しにはgetDateを用いる)
データの更新
(1)
ResultSetの内容を更新するとDBも連動して
更新されるようにする
Class.forName(<jdbcドライバ名>)
Connection con = DriverManager.getConnection(...); Statement st = con.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = st.executeQuery(“select ...”); rs.first(); rs.updateString(“user_pass”, “newpassword”); rs.updateRow(); Ex02.java(抜粋)
データの更新
(2)
executeQueryの代わりにexecuteUpdateを使用する
要素の追加/削除(insert into/delete from文) 表の追加/削除(create/drop table文)
Statement st = con.createStatement(); ResultSet rs = st.executeUpdate
(“drop table categorytable if exists”);
rs.executeUpdate(“inser into categorytable values ...); Ex03.java
実習
8
Ex03.javaを実行せよ
表の形式やデータの種類を変更せよ
(上級者向け) コマンド行を読んで
select文なら問合せを
それ以外なら更新を行うフロントエンドを作成せよ
詳細情報の取得
ResultSetMetadata: 結果の詳細
getColumnCount() : 列数 getColumnName(int) : 列名 getColumnClassName(int) : 列の型(javaのクラス名) getColumnType(int) : 列の型(java.sql.Typesで定義) getColumnDisplaySize(int) : 列の表示幅(桁数) 列は1からgetColumnCount()までなので注意実習
9
Ex04.javaを実行せよ
様々な型を要素に持つ表を作成することで、
Ex04.javaでUndefとなっている型の種類を
調査し、正しく表示できるように修正せよ
(java.sql.Types参照)
JSPからの利用(1)
sqlタグライブラリを使用
setDataSource : DB接続
query: 問合せ
update: 更新
<sql:setDataSource var=“ds” driver=“com.mysql.jdbc.Driver” url=“jdbc:mysql://localhost/mydb” user=“mush” password=“potato” /> sql-ex01.jspJSPからの利用(2)
値は
rsより簡単に取り出すことが可能
int rs.rowCount : 行数 String[] rs.columnNames : 列名の配列 SortedMap[] rs.rows : 各行の内容を要素とするマップ Object[][] rs.rowsByIndex : 2次元配列 <c:forEach>と組み合わせて表示<table><c:forEach var=“row” items=“${rs.rowByIndex}”> <tr><c:forEach var=“col” items=“${row}”>
<td><c:out value=“${col}” /></td> </c:forEach></tr> </c:forEach></table> sql-ex01.jsp
JSPからの利用(3)
表の更新
<sql:update>を用いるが、複数クライアントからの 同時アクセスの可能性があるため排他制御が必要 <sql:transaction isolation=“serializable”> で囲むのが安全 <sql:transaction isolation=“serializable” dataSource=“${ds}”><sql:update>drop table if exists categorytable</sql:update> <sql:update>create table ... </sql:update>
<sql:update>insert into ... </sql:update> </sql:transaction>
