ホーム » 「SQL」タグがついた投稿 (ページ 2)
タグアーカイブ: SQL
予備実験1 データベースの操作
データベースの基本操作
- SQL演習環境(学内からのみアクセス可能)
- SQLの基本(DB授業資料) — (create table,insert,select まで)
- SQLと結合(DB授業資料) — (where節から串刺し検索まで)
データベースをWebから操作
練習問題
- 上記の db_query.php を改良して、ユーザ名を入力してもらい、その名前のデータを表示するプログラムに改造する。
- おなじく、年齢を入力したら、指定した年齢以上の人のデータを表示するプログラムに改良する。
SQLの基本
先週の、関係データベースの導入説明を終えて、実際のSQLの説明。
キー
表形式のテーブルの中の各レコードを一意的に指定できるカラムはキーと呼ばれる。
キーは単独であるとは限らず、成績の評価結果であれば、学生と科目をキーとして成績というカラムが１つに絞られる場合もある。
キーのうち、データを一意に識別するためのキーは、プライマリーキーと呼ばれる。以下の例であれば、uID,sID がプライマリーキーである。一方、成績のテーブルでは、uID, sID は、学生,科目のキーとなっている。このようなキーは外部キーと呼ばれる。点数pointは、uID, sID により一意に決まるが、例えば成績の uID に、学生のテーブルに存在しないものが指定されてはいけない。こういった制約は外部キー制約と呼ばれる。
SQLの命令
SQL で使われる命令は、以下のものに分類される。
- データ定義言語 – CREATE, DROP, ALTER 等
- データ操作言語 – INSERT, UPDATE, DELETE, SELECT 等
- データ制御言語 – GRANT, REVOKE 等 (その他トランザクション制御命令など)
create user
データベースを扱う際の create user 文は、DDL(Data Definition Language)で行う。
CREATE USER ユーザ名
IDENTIFIED BY "パスワード"
CREATE USER ユーザ名
IDENTIFIED BY "パスワード"
CREATE USER ユーザ名
IDENTIFIED BY "パスワード"
grant
テーブルに対する権限を与える命令。
GRANT システム権限 TO ユーザ名
データベースシステム全体に関わる権限をユーザに与える。
(例) GRANT execute ON admin.my_package TO saitoh
GRANT オブジェクト権限 ON オブジェクト名 TO ユーザ名
作られたテーブルなどのオブジェクトに関する権限を与える。
(例) GRANT select,update,delete,insert ON admin.my_table TO saitoh
REVOKE オブジェクト権限 ON オブジェクト名 TO ユーザ名
オブジェクトへの権限を剥奪する。
GRANT システム権限 TO ユーザ名
データベースシステム全体に関わる権限をユーザに与える。
(例) GRANT execute ON admin.my_package TO saitoh
GRANT オブジェクト権限 ON オブジェクト名 TO ユーザ名
作られたテーブルなどのオブジェクトに関する権限を与える。
(例) GRANT select,update,delete,insert ON admin.my_table TO saitoh
REVOKE オブジェクト権限 ON オブジェクト名 TO ユーザ名
オブジェクトへの権限を剥奪する。
GRANT システム権限 TO ユーザ名 データベースシステム全体に関わる権限をユーザに与える。 (例) GRANT execute ON admin.my_package TO saitoh GRANT オブジェクト権限 ON オブジェクト名 TO ユーザ名 作られたテーブルなどのオブジェクトに関する権限を与える。 (例) GRANT select,update,delete,insert ON admin.my_table TO saitoh REVOKE オブジェクト権限 ON オブジェクト名 TO ユーザ名 オブジェクトへの権限を剥奪する。
create table
実際にテーブルを宣言する命令。構造体の宣言みたいなものと捉えると分かりやすい。
CREATE TABLE テーブル名
( 要素名1 型 , 要素名2 型 ... ) ;
PRIMARY KEY 制約
型の後ろに"PRIMARY KEY"をつける、
もしくは、要素列の最後に、PRIMARY KEY(要素名,...)をつける。
これによりKEYに指定した物は、重複した値を格納できない。
型には、以下の様なものがある。(Oracle)
CHAR( size) : 固定長文字列 / NCHAR国際文字
VARCHAR2( size ) : 可変長文字列 / NVARCHAR2...
NUMBER(桁) :指定 桁数を扱える数
BINARY_FLOAT / BINARY_DOUBLE : 浮動小数点(float / double)
DATE : 日付(年月日時分秒)
SQLiteでの型
INTEGER : int型
REAL : float/double型
TEXT : 可変長文字列型
BLOB : 大きいバイナリデータ
DROP TABLE テーブル名
テーブルを削除する命令
CREATE TABLE テーブル名
( 要素名1 型 , 要素名2 型 ... ) ;
PRIMARY KEY 制約
型の後ろに"PRIMARY KEY"をつける、
もしくは、要素列の最後に、PRIMARY KEY(要素名,...)をつける。
これによりKEYに指定した物は、重複した値を格納できない。
型には、以下の様なものがある。(Oracle)
CHAR( size) : 固定長文字列 / NCHAR国際文字
VARCHAR2( size ) : 可変長文字列 / NVARCHAR2...
NUMBER(桁) :指定 桁数を扱える数
BINARY_FLOAT / BINARY_DOUBLE : 浮動小数点(float / double)
DATE : 日付(年月日時分秒)
SQLiteでの型
INTEGER : int型
REAL : float/double型
TEXT : 可変長文字列型
BLOB : 大きいバイナリデータ
DROP TABLE テーブル名
テーブルを削除する命令
CREATE TABLE テーブル名 ( 要素名1 型 , 要素名2 型 ... ) ; PRIMARY KEY 制約 型の後ろに"PRIMARY KEY"をつける、 もしくは、要素列の最後に、PRIMARY KEY(要素名,...)をつける。 これによりKEYに指定した物は、重複した値を格納できない。 型には、以下の様なものがある。(Oracle) CHAR( size) : 固定長文字列 / NCHAR国際文字 VARCHAR2( size ) : 可変長文字列 / NVARCHAR2... NUMBER(桁) :指定 桁数を扱える数 BINARY_FLOAT / BINARY_DOUBLE : 浮動小数点(float / double) DATE : 日付(年月日時分秒) SQLiteでの型 INTEGER : int型 REAL : float/double型 TEXT : 可変長文字列型 BLOB : 大きいバイナリデータ DROP TABLE テーブル名 テーブルを削除する命令
insert,update,delete
指定したテーブルに新しいデータを登録,更新,削除する命令
INSERT INTO テーブル名 ( 要素名,... ) VALUES ( 値,... ) ;
要素に対応する値をそれぞれ代入する。
UPDATE テーブル名 SET 要素名=値 WHERE 条件
指定した条件の列の値を更新する。
DELETE FROM テーブル名 WHERE 条件
指定した条件の列を削除する。
INSERT INTO テーブル名 ( 要素名,... ) VALUES ( 値,... ) ;
要素に対応する値をそれぞれ代入する。
UPDATE テーブル名 SET 要素名=値 WHERE 条件
指定した条件の列の値を更新する。
DELETE FROM テーブル名 WHERE 条件
指定した条件の列を削除する。
INSERT INTO テーブル名 ( 要素名,... ) VALUES ( 値,... ) ; 要素に対応する値をそれぞれ代入する。 UPDATE テーブル名 SET 要素名=値 WHERE 条件 指定した条件の列の値を更新する。 DELETE FROM テーブル名 WHERE 条件 指定した条件の列を削除する。
select
データ問い合わせは、select文を用いる、 select文は、(1)必要なカラムを指定する射影、(2)指定条件にあうレコードを指定する選択、 (3)複数のテーブルの直積を処理する結合から構成される。
SELECT 射影 FROM 結合 WHERE 選択
(例) SELECT S.業者番号 FROM S WHERE S.優良度 > 30 ;
SELECT 射影 FROM 結合 WHERE 選択
(例) SELECT S.業者番号 FROM S WHERE S.優良度 > 30 ;
SELECT 射影 FROM 結合 WHERE 選択 (例) SELECT S.業者番号 FROM S WHERE S.優良度 > 30 ;
理解確認
- キー・プライマリキー・外部キーについて説明せよ。
- 上記説明中の、科目テーブルにふさわしい create table 文を示せ。
- select文における、射影,結合,選択について説明せよ。
創造工学演習・予備実験(SQLとPHP)
先週のPHPの予備実験に引き続き、データベースの説明とそのプログラムで演習を行う。
<?php
// データの保存先
$dbfile = "/home/guests/guest0/public_data/tsaitoh.db" ;
if ( !file_exists( $dbfile ) ) {
// データベースの宣言と初期化
$db = new SQLite3( $dbfile ) ;
$cmd = "create table nametel ( name char( 20 ) , phone char( 20 ) ) ;"
."insert into nametel ( name , phone ) values ( 't-saitoh' , '090-1111-1111' ) ;"
."insert into nametel ( name , phone ) values ( 'tomoko' , '090-2222-2222' ) ;"
."insert into nametel ( name , phone ) values ( 'mitsuki' , '090-3333-3333' ) ;"
."insert into nametel ( name , phone ) values ( 'ayuka' , '090-4444-4444' ) ;"
;
if ( $db->exec( $cmd ) ) {
print "Success.\n" ;
} else {
print "Fail\n" ;
}
$db = null ;
}
?>
<html>
<head>
<title></title>
</head>
<body>
<?php
// 警告表示の抑止
ini_set( 'error_reporting' , E_WARNING ) ;
// データベース処理(検索処理)
$db = new SQLite3( $dbfile ) ;
$id = $_REQUEST[ "name" ] ;
if ( $db !== FALSE && $id != "" ) {
// SQL命令の生成(インジェクションの危険性指摘は別述)
$cmd = "select * from nametel where name='$id' ;" ;
print $cmd ;
// 問い合わせの実行
$result = $db->query( $cmd ) ;
print "<table border='1'>\n" ;
print "<tr><td>name</td><td>phone</td></tr>" ;
while( ($row = $result->fetchArray( SQLITE3_BOTH )) !== FALSE ) {
print "<tr>"
."<td>".$row[0]."</td>"
."<td>".$row[1]."</td>"
."</tr>\n" ;
}
print "</table>\n" ;
}
?>
<form method="post" action="sql.php">
<input type="text" name="name" />
<input type="submit" value="exec" />
</form>
<?php
// データベース登録処理
$newname = $_REQUEST[ "newname" ] ;
$newphone = $_REQUEST[ "newphone" ] ;
if ( $newname != "" && $newphone != "" ) {
// データ登録用SQL
$cmd = "insert into nametel ( name , phone ) values ( '$newname' , '$newphone' ) ;" ;
// データ登録
$db->exec( $cmd ) ;
print "$newname さんの電話番号 $newphone を登録しました" ;
}
?>
<form method="post" action="sql.php">
<input type="text" name="newname" />
<input type="text" name="newphone" />
<input type="submit" value="exec" />
</form>
</body>
</html>