【応用情報】午後試験データベース分野の頻出SQLまとめ。

create table テーブル名 (id int, name varchar(10)); -- テーブル作成
insert into テーブル名 ... -- データ追加
select * from テーブル名 ... -- データ検索
update テーブル名 set = ... -- データ更新
delete テーブル名 where ... -- データ削除

... where id = 1 -- where句 条件で結果を絞る
... order by id -- order by句 条件でソート

クラス  点数
a      10
a      20
b      30
a      30
b      30

select クラス,avg(点数) from 上のテーブル group by クラス
------
a 20
b 30
------

クラス  点数
a      10
a      20
b      30
a      30
b      30

select クラス,avg(点数) from 上のテーブル group by クラス having クラス = a
------
a 20
------

クラス  点数  先生
a      10   aaaさん
a      20   aaaさん
b      30   bbbさん
a      30   aaaさん
b      30   bbbさん

select クラス,avg(点数),先生 from 上のテーブル group by クラス,先生
------
a 20 aaaさん
b 30 bbbさん
------

select count(*) from ... -- データの個数
select max(項目) from ... -- 点数などの項目の一番上のデータを取得
select sum(*) from ... -- 点数などの項目合計を取得
select avg(*) from ... -- 点数などの項目の平均点を取得

select count(*) from テーブル where 点数 in (0,1,2,3) -- 0,1,2,3の中に含まれている点数のデータが抽出される

select count(*) from テーブル where exists 点数 < 30 -- 30以下の点数のデータ抽出される

-- 通蔵のselect文
select id, name from テーブル where id in (0,1,2) ;
-- サブクエリを含むselect文（上記とやっていることは同じだがin句の中身をselect文で取得している）
select id, name from テーブル where id in (select id from テーブル where id < 3); 

-- 通常のinsert文
insert into テーブル (id, name) values (1, 'aaa');
-- サブクエリを使用したinsert
insert into テーブル (id, name) values (select id, name from 取得元テーブル where id < 100);

CREATE TABLE SAMPLETABLE
(
  no int auto_increment
  id int PRIMARY KEY,
  name varchar(50) UNIQUE NOT NULL,
  tel varchar(12) default '000-0000-0000'
  FOREIGN KEY(refNo) REFERENCES anotherTable(refNo)
);

select count(*) from テーブル where between 点数 40 and 50 -- 40点以上50点以下の点数のデータ

GRANT {権限3,権限2} ON テーブル TO ユーザ;
GRANT SELECT, UPDATE ON table1 TO user1;

CREATE ASSERTION totalCount
CHECK (SELECT COUNT(*) FROM TableA) = (SELECT COUNT(*) FROM TableB) + (SELECT COUNT(*) FROM TableB);

-- 単一カラム追加
ALTER TABLE sample ADD COLUMN addCol VARCHAR(8);
-- 複数の場合
ALTER TABLE sample ADD (addCol1 VARCHAR(8),addCol2 VARCHAR(8));

-- カラムの定義変更（複数は上記と同じ要領）
ALTER TABLE sample MODIFY COLUMN addCol VARCHAR(8);

-- カラム削除（複数は上記と同じ要領）
ALTER TABLE sample DROP COLUMN address;