ホーム » 「SQL」タグがついた投稿

タグアーカイブ: SQL

2018年12月
« 11月    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

最近の投稿(電子情報)

アーカイブ

カテゴリー

SQLの基本

先週の、関係データベースの導入説明を終えて、実際のSQLの説明。

キー

表形式のテーブルの中の各レコードを一意的に指定できるカラムはキーと呼ばれる。

キーは単独であるとは限らず、成績の評価結果であれば、学生科目をキーとして成績というカラムが1つに絞られる場合もある。

キーのうち、データを一意に識別するためのキーは、プライマリーキーと呼ばれる。以下の例であれば、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 "パスワード"

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 ユーザ名
   オブジェクトへの権限を剥奪する。

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 テーブル名
   テーブルを削除する命令

insert,update,delete

指定したテーブルに新しいデータを登録,更新,削除する命令

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 ;

理解確認

  • キー・プライマリキー・外部キーについて説明せよ。
  • 上記説明中の、科目テーブルにふさわしい 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>