SQLクエリー
この文章の例は『データベースシステム概論』第5版高等教育出版社から来ている.
データ定義定義モードCREATE SCHEMA AUTHEORICZATION; 削除モードDROP SCHEMA; 基本テーブルCREATE TABLE([列レベル整合性制約][,[列レベル整合性制約]]...[,])を定義します. 基本表ALTER TABLE[ADD[COLUMN][完全性制約][ADD][DROP[COLUMN][CASCADE|RESTRICT][DROP CONTRAINT[CASCADE|RESTRICT][ALTER COLUMN]; 基本テーブルDROP TABLEを削除する. インデックスCREATE[UNIQUE][CLUSTER]INDEX ON([][,[]]]...); インデックス を変更削除インデックス データ照会フォーム照会SELECT[All|DISTINCT][,]...FROM[,...]|()[WHERE][GROUUPBY[HAVING][ORDER BY[ASC|DESC]] 表の列を選択 クエリー指定列 全カラム を問い合わせる計算された値を問い合わせる 選択テーブルのいくつかのメタグループ 値繰返し行 を消去する.クエリ条件を満たすタプル ORDER BY句ORDER BY[列名[ASC|DESC],...];ASC:昇順(デフォルト)DESC:降順 集約関数COUT()統計メタセット個数COUT([DISTINCT|ALL])統計一列中値の個数SUM([DISTINCT|ALL])一列値の総和(この列は数値型でなければならない)AVG([DISTINCT|ALL])計算一列値の平均値(この列は数値型でなければならない)MAX([DISTINCT|ALL])一列値の最大値MIN([DISTINCT|ALL])を求める列の値の中の最小値の を求めます GROUP BY句WHERE句では条件式として集約関数を用いることができず、集約関数はSELECT句とGROUP BYのHAVING句 にのみ用いることができる.
接続クエリー 等値と非等値の接続クエリー接続演算子が=の場合、等値接続と呼ばれます.他の演算子を非等値接続と呼ぶ[.][. ] [. ] BETWEEN [. ] AND [. ] は、1つのテーブルを自身に接続する、テーブルと呼ばれる自己接続 である.外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン左外部ジョイン マルチテーブル接続
Sname
Cname
Grade
201215121
李勇
データベース#データベース#
92
201215121
李勇
数学
85
201215121
李勇
じょうほうシステム
88
201215122
劉晨
数学
90
201215122
劉晨
じょうほうシステム
80
ネストされたクエリー IN述語を持つサブクエリには、接続演算で置換できるネストされたクエリもあれば、置換できないクエリもあります.サブクエリのクエリ条件が親クエリに依存しない場合は、非相関サブクエリと呼ばれます.サブクエリのクエリ条件が親クエリに依存している場合は、相関サブクエリと呼ばれます.クエリ文全体を相関ネストクエリと呼ぶ相関サブクエリは、非相関サブクエリを解くようにサブクエリを一度に解くことができず、親クエリを解く
Sname
Ssex
Sage
Sdept
201215121
李勇
男
20
CS
201215122
劉晨
女
19
CS
Sno
Sname
201215121
李勇
201215122
劉晨
比較演算子付きサブクエリ
Sname
Ssex
Sage
Sdept
201215121
李勇
男
20
CS
201215122
劉晨
女
19
CS
Sno
Cno
201215121
1
201215122
2
ANY(SOME)またはALL述語を持つサブクエリサブクエリが単一値を返す場合は比較演算子を使用できますが、多値を返す場合はANY(システムによってはSOME)またはALL述語修飾子
Sname
Sage
王敏
18
張立
19
Sname
Sage
王敏
18
EXISTS述語を持つサブクエリEXISTS述語を持つサブクエリはデータを返さず、論理真値「true」または論理偽値「false」
Sname
李勇
Sname
劉晨
張立
王敏
集合クエリー集合操作は主にUNION、交差操作INTERSECT及び差操作EXCEPTを含む および
Sname
Ssex
Sage
Sdept
201215121
李勇
男
20
CS
201215122
劉晨
女
19
CS
201215123
王敏
女
18
MA
201215125
張立
男
19
IS
交差操作 差動作 派生テーブルに基づくクエリー
Cno
‘201215121’
‘1’
‘201215122’
‘2’
データ定義
create schema `S-T`;
drop schema `S-T`;
drop table SC;
drop table Course;
drop table Student;
create table `S-T`.Student
(
Sno char(10) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
);
create table `S-T`.Course
(
Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4),
Ccredit smallint,
/* ,Cpno , Course, Cno */
foreign key(Cpno)references Course(Cno)
);
create table `S-T`.SC
(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno, Cno), /* */
foreign key(Sno)references Student(Sno),
foreign key(Cno)references Course(Cno)
);
alter table Student add S_entrance date;
alter table Student modify column Sage INT; /* SQL Server: modify => alter */
alter table Course add unique(Cname);
alter table Student drop S_entrance;
drop table Student restrict;
create unique index Stusno on `S-T`.Student(Sno);
create unique index Coucno on `S-T`.Course(Cno);
create unique index SCno on `S-T`.SC(Sno asc, Cno desc);
create unique index Stusname on `S-T`.Student(Sname);
/* : alter index SCno rename to SCSno */
alter table `S-T`.SC rename index SCno to SCSno;
/* drop index Stusname */
drop index Stusname on `S-T`.Student;
select Sno, Sname, Sdept
from `S-T`.Student;
select *
from `S-T`.Student;
select Sname Name, 'Year of Birth:' Birth, 2014-Sage Birthday, LOWER(Sdept) Department
from `S-T`.Student;
select Sno /* = select ALL Sno */
from `S-T`.SC;
select distinct Sno /* DISTINCT */
from `S-T`.SC;
/* */
select Sname
from `S-T`.Student
where Sdept='CS';
select distinct Sno
from `S-T`.SC
where Grade<60;
/* */
select Sname, Sdept, Sage
from `S-T`.Student
where Sage between 20 and 23;
select Sname, Sdept, Sage
from `S-T`.Student
where Sage not between 20 and 23;
/* */
select Sname, Ssex
from `S-T`.Student
where Sdept in ('CS', 'MA', 'IS');
select Sname, Ssex
from `S-T`.Student
where Sdept not in ('CS', 'MA', 'IS');
/* [NOT] LIKE '' [ESCAPE '']*/
/* % ( 0) */
/* _ */
select *
from `S-T`.Student
where Sno like '201215121'; /* => Sno='201215121*/
select Sname, Sno, Ssex
from `S-T`.Student
where Sname like ' %';
select Sname, Sno, Ssex
from `S-T`.Student
where Sname not like ' %';
select Cno, Cname, Ccredit
from `S-T`.Course
where Cname like 'DB/_Design' escape '/';
select *
from `S-T`.Course
where Cname like 'DB/_%i__' escape '/'; /* DB_ , i */
/* */
select Sno, Cno
from `S-T`.SC
where Grade is NULL;
select Sno, Cno
from `S-T`.SC
where Grade is not NULL;
/* */
select *
from `S-T`.Student
where Sdept='CS' and Sage < 20;
select Sname, Ssex
from `S-T`.Student
where Sdept='CS' or Sdept='MA' or Sdept='IS'; /* => where Sdept not in ('CS', 'MA', 'IS') */
select Sno, Grade
from `S-T`.SC
where Cno = '3'
order by Grade DESC;
select *
from `S-T`.Student
order by Sdept, Sage DESC; /* , */
/* */
select count(*)
from `S-T`.Student;
/* */
select count(distinct Sno)
from `S-T`.SC;
/* 1 */
select avg(Grade)
from `S-T`.SC
where Cno='1';
/* 1 */
select max(Grade)
from `S-T`.SC
where Cno='1';
/* 201215121 */
select sum(Ccredit)
from `S-T`.SC, `S-T`.Course
where Sno='201215121' and `S-T`.SC.Cno=`S-T`.Course.Cno;
/* */
select Cno, count(Sno)
from `S-T`.SC
group by Cno;
/* */
select Sno
from `S-T`.SC
group by Sno
having count(*)>2;
/* 90 */
select Sno, avg(Grade)
from `S-T`.SC
group by Sno
having avg(Grade)>=80
/* */
select Student.*, SC.Cno, SC.Grade
from `S-T`.Student, `S-T`.SC
where Student.Sno=SC.Sno;
/* 2 60 */
select Student.Sno, Sname
from `S-T`.Student, `S-T`.SC
where Student.Sno=SC.Sno and
SC.Cno=2 && SC.Grade > 60;
/* ( ) */
select First.Cno, First.Cname, Second.Cpno
from `S-T`.Course First, `S-T`.Course Second
where First.Cpno=Second.Cno;
select Student.*, SC.Cno, SC.Grade
from `S-T`.Student left outer join `S-T`.SC
on (Student.Sno=SC.Sno);
Sno Sname
Cname
Grade
201215121
李勇
データベース#データベース#
92
201215121
李勇
数学
85
201215121
李勇
じょうほうシステム
88
201215122
劉晨
数学
90
201215122
劉晨
じょうほうシステム
80
/* 、 、 */
select Student.Sno, Sname, Cname, Grade
from `S-T`.Student, `S-T`.Course, `S-T`.SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;
Sno Sname
Ssex
Sage
Sdept
201215121
李勇
男
20
CS
201215122
劉晨
女
19
CS
/* “ ” */
select * /* : */
from `S-T`.Student
where Sdept in (
select Sdept
from `S-T`.Student
where Sname=' '
);
select S1.* /* : */
from `S-T`.Student S1, `S-T`.Student S2
where S1.Sdept=S2.Sdept and S2.Sname=' ';
Sno
Sname
201215121
李勇
201215122
劉晨
/* ” “ */
select Sno, Sname
from `S-T`.Student
where Sno in
(select Sno
from `S-T`.SC
where Cno in
(select Cno
from `S-T`.Course
where Cname=' ' /* : */
)
);
select Student.Sno, Sname
from `S-T`.Student, `S-T`.Course, `S-T`.SC /* : */
where Student.Sno=SC.Sno and
SC.Cno=Course.Cno and
Course.Cname=' ';
Sno Sname
Ssex
Sage
Sdept
201215121
李勇
男
20
CS
201215122
劉晨
女
19
CS
/* “ ” */
select * /* : , , = => IN */
from `S-T`.Student
where Sdept=
(select Sdept
from `S-T`.Student
where Sname=' '
);
Sno
Cno
201215121
1
201215122
2
/* */
select Sno, Cno
from `S-T`.SC SC1
where Grade>=(select avg(Grade) /* */
from `S-T`.SC SC2
where SC1.Sno=SC2.Sno);
を使用します.Sname
Sage
王敏
18
張立
19
/* */
select Sname, Sage
from `S-T`.Student
where SDept!='CS' and
Sage<any(select Sage
from `S-T`.Student
where SDept='CS');
select Sname, Sage /* */
from `S-T`.Student
where SDept!='CS' and
Sage<(select max(Sage)
from `S-T`.Student
where Sdept='CS')
Sname
Sage
王敏
18
/* */
select Sname, Sage
from `S-T`.Student
where SDept!='CS' and
Sage< ALL(select Sage
from `S-T`.Student
where Sdept='CS');
select Sname, Sage /* */
from `S-T`.Student
where SDept!='CS' and
Sage<(select min(Sage)
from `S-T`.Student
where Sdept='CS');
のみを生成するSname
李勇
/* 1 */
select Sname
from `S-T`.Student
where exists(select *
from `S-T`.SC
where SC.Sno=Student.Sno and Cno='1');
Sname
劉晨
張立
王敏
/* 1 */
select Sname
from `S-T`.Student
where not exists(select *
from `S-T`.SC
where SC.Sno=Student.Sno and Cno='1');
Sno Sname
Ssex
Sage
Sdept
201215121
李勇
男
20
CS
201215122
劉晨
女
19
CS
201215123
王敏
女
18
MA
201215125
張立
男
19
IS
/* 19 */
select *
from `S-T`.Student
where Sdept='CS'
union
select *
from `S-T`.Student
where Sage<=19;
/* 19 。 Mysql */
select *
from `S-T`.SC
where Sdept='CS'
intersect
select *
from `S-T`.Student
where Sage<=19;
/* 19 。 Mysql */
select *
from `S-T`.SC
where Sdept='CS'
except
select *
from `S-T`.Student
where Sage<=19;
Sno Cno
‘201215121’
‘1’
‘201215122’
‘2’