SQL基本

sqlの歴史

1970年代の初め、IBM社のE.F.Coddによってリレーショナル型DBの概念が提唱された。
その後、世界初のリレーショナルDB管理システム「SystemR」が開発された。
SystemRを操作するに当たって「SEQUEL(Structured English Query Language)」という言語が採用されていた。
SEQUELが改名されたものがSQL。SQLは言語のこと。

1986年にANSI(American National Standard Institute)によって
1987年にISO(International Organization for Standardization)とJIS(Japanese Industrial Standard)によって相次いで標準化された。

標準化とは
複数のものの仕組みや規格などを統一すること。
これを行うことで生産者はいろんな仕様のものを作る手間が省けるし、消費者は様々な製品を同じような方法で使用できる。


SQLのようにDBを操作する働きを持つ言語のことをデータベース言語という。
さらに細かく分類することができる。
・データ定義言語(DDL:Data Definition Language)
 create文など
・データ操作言語(DML:Data Manipulation Language)
 select文など
・データ制御言語(DCL:Data Control Language)
 commitやcallbackなど

静的SQL、動的SQL
ユーザーに渡された値によって実行時に作成されるsql文を動的SQL、
固定のsql文を静的SQLという。

DBMS

データベース管理システム(DataBase Management System)の略。
DBから実際にデータを取り出したり格納したりするのがDBMSの仕事。
ユーザーがソフトウェアの操作、ソフトウェアがDBMSに要求を出す、DBMSがDBを操作しソフトウェアに結果を返す、
ソフトウェアがユーザーに結果を表示する。

リレーショナルデータベースを管理するシステムをRDBMSという。
代表的なRDBMS:SQL Server,Oracle,MySQL,PostgreSQL。
AccessもRDBMSの一つらしい。

クエリ

RDBMSに出す要求のこと。
クエリの中身の命令文をSQL文という。

連結キー

複数の列を組み合わせて1つの主キーにすることができる。これを連結キーという。

CREATE

CREATE DATABASE データベース名
データベースを作る。

CREATE TABLE テーブル名 (カラム名 型,カラム名 型,…)
CREATE TABLE 従業員 (ID int , 名前 varchar(10) , 所属コード varchar(10))
テーブルを作る。

CREATE USER ユーザー名
CREATE USER ユーザー名 IDENTIFIED BY ‘パスワード’
ユーザーを作る。
IDENTIFIED以降を書くと、パスワードの設定ができる。
ユーザー名は、「ユーザー名@ホスト名」で指定する。
ホスト名とは、「localhost」だったりIPアドレスだったりする。

制約
単一の列に設定する列制約と、複数の列にまとめて設定するテーブル制約がある。
create table samle(ID int unique , name varchar(30),age int, primary key(name,age));
IDについているuniqueが列制約。データ型の後に半角スペースを開けて記述する。
最後のprimary keyがテーブル制約。連結キーを定義する場合は、上のようにprimary key(列名,列名)と書く。
1つの列に対して複数の制約を設定できる。その場合、半角スペースを開けて続けて記述すればいい。
ID int unique not null
ユニークキーと、not nullを設定している。
制約の種類
・primary key
・unique
・check(条件式) 条件式に合わないデータを禁止する
・not null
・default=値 指定したデータを初期値として登録する

要素の型

char(n) 長さが固定の文字列
varchar(10) 文字。()内の数で最大文字数を設定。
datetime 時間。年、日付、時間まで保存する。
text 文字。65535バイトまで文字を格納する。
int 整数
smallint intより範囲が狭い整数値
desimal(m,n) mに全桁数、nに小数点以下の桁数を指定する。desimal(10,3)としたら、整数7桁、小数3桁。
float 浮動小数点
date 日付(YYYY-MM-DD)
time 時刻(HH:MM:SS)

desc

desc テーブル名
これで、テーブルの構成を確認できる。

INSERT

INSERT INTO テーブル名 (要素名1,要素名2,要素名3) VALUE(要素1,要素2,要素3)
文字列を入れるときはシングルクォーテーションで囲む。

insert from

insert文にfromを使う。

insert into テーブル名(要素名1,要素名2,要素名3)
select テーブル名2.要素名1 , テーブル名2.要素名2 from テーブル名2
where テーブル名2.要素名1 = “aaaa”
とすると、テーブル名2の要素名1が「aaaa」の要素を、テーブルにinsertできる。

SELECT

SELECT * FROM テーブル名
*はそのテーブルの中のすべての要素を表示するという意味。
SELECT 要素名1,要素名2 FROM テーブル名
こう書けば要素名1と要素名2の要素だけを表示できる。

表示項目名を自分で設定する

select age As 年齢 from USER_MST
と書けば、「USER_MST」というテーブルの「age」の値を
年齢という項目にして出せる。
複数のテーブルから同じ項目名の値を出すときに区別できる。

delete

delete from テーブル名 where 要素名=”
で、指定した項目を削除できる。
null値のデータを指定する場合は、要素名 is NULL と書く。

WHERE

条件を指定できる。
SELECT * FROM テーブル名 WHERE 要素1=’aaaa’
こう書くと要素1が「aaaa」のレコードだけを表示できる。
等しくないの指定方法は「!=」。

要素1 IS NULLとすると、要素1がNULLのものを指す。
要素1 IS NOT NULLはNULLではないものを指す。

要素1 BETWEEN a AND b はa以上b以下。

要素1 IN (‘aaaa’,’bbbb’,’cccc’)は、「aaaa」か「bbbb」か「cccc」を指す。

要素1 LIKE ‘%a%’は、aを含むものを指定するという意味。
要素1 LIKE ‘%a’とするのと違いがわからない。
%aとしたら、aの前に何文字か文字が入っててもOKとする。
a%としたら、aの後ろに文字が入っててもOKとする。
%a%としたら、aの前後に文字が入っててもOKとする。
要素1 LIKE ‘_a%’とすると、aの前に何か1文字入ったものを指定するという意味。

時間を指定する場合は、TO_TIMESTAMP(‘2020/03/10’)やTO_DATE(‘2020/03/10’)の記述をしなきゃいけない(Oracleだけ?)。

サブクエリー

SELECT文の中にSELECT文を入れて参照する。
SELECT文の抽出条件に、別のSELECT文を使用する感じ。

SELECT * FROM テーブル1 WHERE
テーブル1.値段=(
SELECT 値段 FROM テーブル2 WHERE テーブル2.名前=’商品1′
こう書けば、テーブル2の商品1と同じ値段のレコードを、テーブル1から参照して表示できる。

変数

DECLARE 変数名 型;
以上のように変数は宣言できる。

DECLARE 変数名 型:=値;
以上のように書くと初期値を入れられる。
DECLARE num int:=10;みたいな。

関数

単一行関数 各行に対して処理を行、行単位で結果を返す。
グループ関数 複数の行を1グループとして処理を行い、1つの結果を返す。

ceiling関数
引数の値以上の最も低い整数を返す。
select atai , ceiling(atai) As kekka from sample;
値が10.1だった場合、11が表示される。

floor関数
引数の値以下の最も高い整数を返す。
select atai , ceiling(atai) As kekka from sample;
値が10.1だった場合、10が表示される。

rand関数
0以上1未満の範囲の乱数を取得する。

GRANT

GRANT CREATE on *.* to ユーザー名
指定したユーザーに、CREATE文を使う権限を与える。
*.*と書くと、すべてのDB、テーブルに対して権限を与えられる。
データベース名.テーブル名と書けば、特定のテーブルに対して権限を与えられる。

SHOW GRANTS FOR ユーザー名
指定したユーザーの権限を確認する。

文字コードの確認

MySQLの文字コードを確認する方法。
SHOW variables LIKE ‘character_set%’;
utf8mb4だと文字化けしないらしい。

コンソールの文字コードを変更する方法。
CHARSET utf8mb4

COUNT

SELECT COUNT(*) FROM テーブル名
これでテーブル内のレコードの数を取得できる。
WHEREで条件を指定して数えることもできる。

LOOP文

LOOP
 EXIT WHEN 終了条件
 処理内容
END LOOP
何故かBEGINとENDで挟まないとエラーになって処理できなかった。

CONCAT 文字連結演算子

文字列と数値をくっつけるにはCONCATが使えた。
変数名 int:=0;
CONCAT(‘a’,変数名)
これで「a0」という形にできる。
INSERTで値を入れるときにはこの書き方しかうまくいかなかった。

複数の項目の値をくっつけるには文字連結演算子を使えばいいらしい。
select name || age from sample;
とかけば、名前と年齢をつなげて表示できる。

結合

交差結合、クロス結合
単純に全てのレコード同士を結合させる方法。
select * from sample1 cross join sample2;

内部結合
交差結合の中から指定した値に一致するデータのみを取り出す方法。
select * from sample inner join sample2 on sample1.id = sample2.id;

外部結合

片方のテーブルの情報をすべて残すテーブルの結合方法。
SELECT * FROM テーブル1,テーブル2 WHERE テーブル1.要素=テーブル2.要素(+)
以上のように書くことでテーブル1の中身をすべて残したレコードを表示できる。
(普通の結合だとテーブル1.要素=テーブル2.要素の条件を満たすレコード以外は除外される)

SELECT * FROM テーブル1,テーブル2 WHERE テーブル1.要素=テーブル2.要素(+) AND
テーブル1.要素=’aaaa’
という風にWHEREの条件を増やすこともできる。

LEFT OUTER JOIN

SELECT * FROM テーブル1 LEFT OUTER JOIN テーブル2 ON テーブル1.要素=テーブル2.要素
という書き方をすることでも外部結合は可能。
後ろにWHEREをつけて抽出条件を入れることもできる。

3つのテーブルを結合したい場合は以下のように書く。
SELECT * FROM テーブル1 LEFT OUTER JOIN テーブル2 ON
テーブル1.要素=テーブル2.要素 LEFT OUTER JOIN テーブル3 ON
テーブル1.要素=テーブル3.要素

ORDER BY

テーブルを表示する際の順番を変更できる。
SELECT * FROM テーブル名 ORDER BY 要素名
以上のように書くことで、要素名が昇順になるようにレコードが表示される。

SELECT * FROM テーブル名 ORDER BY 要素名 DESC
以上のように書くと降順で表示される。昇順はASC。何も書かないと昇順になる。

SELECT * FROM テーブル名 ORDER BY 要素名1 DESC,要素名2,ASC
以上のように書くことで複数の値で表示順を決めることができる。

TOP

表示する行数を決めるのに使える。Oracleでは使えない。
SELECT TOP 5 * FROM テーブル名
とすれば、最初の5行だけ表示できる。

SELECT TOP * 5 FROM テーブル名 ORDER BY 要素名 ASC
と書けば、指定した要素名で昇順で表示できる。


Oracleの場合、ROWNUMを使う。
select * from sample where ROMNUM <=10;
としたら、最初の10行だけ表示できる。
select * from sample where ROMNUM between 2 and 4;
と書くこともできる。2行目から4行目の範囲を表示する。

MySQLの場合、LIMITを使う。
select * from sample order by key LIMIT 0, 10;
0行目から10行目までを表示する。

PostgreSQLの場合、LIMITとOFFSETを使う。
select * from sample order by key OFFSET 0 LIMIT 10;

DUAL

DUALはダミーのテーブルを指す。
とりあえず関数とかを表示したいときとかに使える。
SELECT sysdate FROM DUAL
以上のように書くと、sysdateでシステムの時刻が表示されることを確認できたりする。
動作確認などをするとき、わざわざそれ用のテーブルを作らなくてもいいように存在するもの。

UNION

複数のSQL文の結果を合わせて表示する。
SELECT * FROM テーブル1 WHERE テーブル1.要素=”xx”
UNION
SELECT * FROM テーブル2 WHERE テーブル2.要素=”yy”

DECODE

条件分岐のSQL文。
SELECT
DECODE(カラム名,’aaaa’,’出したい値1′,’bbbb’,’出したい値2′,’出したい値3′)
FROM テーブル名
こう書くことで、指定したカラムの値が「aaaa」だったら「出したい値1」を表示し、「bbbb」だったら「出したい値2」を出し、「aaaa」でも「bbbb」でもなかったら「出したい値3」を表示する。
SELECT
DECODE(name,NOT NULL,name,’名前書き忘れ’) FROM テーブル名
こうすると、nameの値がNULLだったら「名前書き忘れ」と表示し、NULLじゃなかったらnameの値をそのまま出す。

CASE

条件指定のSQL文。
SELECT
CASE
  WHEN 条件式 THEN ‘表示したい値’
  WHEN 条件式 THEN ‘表示したい値’
  ELSE ‘表示したい値’
END カラム名
FROM テーブル名
条件式は、WHEN score>=10 AND name=’aaaa’ THEN などANDで複数指定もできる。

ALTER

テーブルの設定を変える文。
テーブルに項目を追加したり、削除したりできる。

新しい項目を追加するには
alter table テーブル名 add 項目名 型 not null
nullと書けば、null制約をなくせる。

alter table テーブル名 add 項目名 型 Not NULL
と書けば、指定したテーブル名に指定した型の項目をNot NULLで追加できる。
NULLと書けばNULL制約なしで追加できる。

alter table テーブル名 change 項目名 新しい項目名 型
と書いたら、項目名を新しい項目名、型に変えることができる。

alter table テーブル名 modify column 項目名 型 Not NULL
と書くと、既にある項目にNot NULL属性を追加できる。

既存の項目に、一意制約を加えるには
alter table テーブル名 add constraint 制約名 unique(項目名)
制約名は、かぶらなければなんでもいいらしい。
unique(項目名,項目名,)と書けば、複数の項目にキーを追加できる。ただ、それは項目ごとに一意制約を加えるのでなく、指定した項目すべてに対して一意制約が加わる。
unique(ID,password)とした場合、IDとpassword両方がかぶっているものを追加できないということになる。IDが同じでもpasswordが違っていれば登録できてしまう。

一意制約を解除するには
alter table テーブル名 drop index 制約名
一意制約を確認するには
show indexes from テーブル名

profileを変更するには
alter profile プロファイル名 limit パラメータ名 設定値

enum型の項目に値を追加するには
alter table テーブル名 modify enum型の項目 enum(列挙する値1,列挙する値2,列挙する値3);

ストアド

SQL99から、SQLにプログラミング機能が加わった。
SQL文をあらかじめRDB管理システム上に置いておき、それをプログラムとして呼び出すことができる。
この仕組みをストアドプロシージャという。

ビュー

既存のテーブルから必要な部分だけを取り出し、テーブルの形にとらわれない仮想的な表として表示する仕組み。

ビューの作り方
create view ビュー名 as select 項目1,項目2 from テーブル名1,テーブル名2 where 項目1 = 値;

ビューにinsertなどを行うと、元のテーブルにもその結果が反映される。ただし以下のルールがある。
・データの変更は1つのテーブルから作成されたビューに対してのみ行える
・group by、having、distinct句を使って作られたビューはデータの変更ができない(グループ化されたことで行の特定ができないから)

group by

select price , COUNT(title) from sample group by price;
と書くと、priceが同じ値は1つにまとめられて表示される。

distinct

指定した列にある重複したデータを取り除いて表示できる。
select distinct title from sample;

AS

既存の列を、別の名前で表示できる。
select ID as userID from sample;
IDという列の項目を、userIDという項目名で表示する。

IN

指定した列から、指定したいずれかの値と一致するデータを参照できる。
select player from sample where age in (16,20,26);
と書くと、ageが16,20,26のレコードが参照される。
select player from sample where age not in (16,20,26);
と書くと、ageが16,20,26以外のレコードが参照される。

相関副問合せ

副問い合わせの条件に主問い合わせのテーブルを使うこと?
select * from テーブル1 where select * from テーブル2 where テーブル1.id = テーブル2.id
相関副問い合わせの記述を行うと、主問い合わせで取得したレコード1件1件に、副問い合わせの処理を行うらしい。
existsと組み合わせて使うなどができる。

EXISTS

指定したレコードが存在するかtrue,falseを返す関数。
select * from テーブル where exists (select * from テーブル where id = ’01’)
上のように書くと、idが01のレコードだけが表示されるように感じるけどそうではない。
idが01のレコードが存在すればexistsの結果はtrueになるので上の記述は以下と同じ意味になり、全てのレコードが返される。
select * from テーブル where true

相関副問い合わせを使って使用することで、条件で指定したテーブルだけを取得することなどができる。
select * from テーブル1 where exists(select * from テーブル2 where テーブル1.id = テーブル2.id and テーブル1.id=’01’)

PROFILE

プロファイルは、ユーザーに対して制限をかけるための設定。
パスワードに関するものと、リソースに関するものの2種類がある。

パスワード管理のprofileのパラメータ
FAILED_LOGIN_ATTEMPTS 指定した回数ログインに失敗するとアカウントをロックする。
PASSWORD_LIFE_TIME パスワードを設定してからの有効期間
PASSWORD_REUSE_TIME 同一パスワードが使用可能になるまでの期間
PASSWORD_REUSE_MAX同一パスワードが使用可能になるまでのパスワード変更回数
PASSWORD_LOCK_TIME FAILED_LOGIN_ATTEMPTSでロックされたアカウントが使用可能になるまでの期間
PASSWORD_GRACE_TIME パスワード期限ぎれ後のパスワード変更ができる猶予期間。
PASSWORD_VERIFY_FUNCTION 簡単なパスワードを設定させたくないなど、パスワードをチェックするファンクションの指定。

profileの情報を表示するsql文
select * from dba_profiles;
dba_profileの主要カラム
PROFILE プロファイル名
RESOURCE_NAME リソース名
RESOURCE_TYPE リソースタイプ
LIMIT リソースの制限値

alterでprofileを変更するには
alter profile プロファイル名 limit パラメータ名 設定値;
例)alter profile dafault limit password_life_time unlimited;
例2) alter profile default limit session_per_user 5;

SubStr,SubStrB

文字列から指定した部分を切り抜いて取得する。
SubStr(文字列,開始桁数,何文字切り取るか)
SubStr(‘aiueo’,2,2)
と書くと、iuが取得できる。
後ろの文字から取り出したい場合は、第2引数にマイナスの値を入れる。
SubStr(‘aiueo’,-3,2)
と書くと、ueが取得できる。

SubStrBは文字数で切り取るのでなくバイト数で切り取る。

for update 排他ロック検索

for update , for update nowait
検索した行にロックをかける。
select * from テーブル名 for update
と書くと、検索で出たレコードにロックをかけることができる。他のユーザーからそのレコードを更新できないようにする。
for update nowaitとすると、他のユーザーがそのレコードにアクセスするとき待ち状態にならず即時排他エラーを返す。
排他ロックを解除するにはコミットかロールバックを行えばいい。

match against

部分一致の検索ができる?
select * from テーブル名 where match(項目名) against(‘aaaa’)
と書くと、指定した項目に「aaaa」の文字が入るレコードを検索できる。

編集画面

タイトルとURLをコピーしました