較べてわかるT-SQL - MySQL

初投稿です。よろしくお願いします。
*2020/08/04 sp_columnsの仕様が変わっていたので修正

はじめに

いまさら感あふれますが、これから触る初心者向けに簡単な2種類のSQLコマンドを比較してみました。自身のメモを基に書くので情報が古かったら申し訳ないです、ご指摘ください。

主目的となるものはT-SQLコマンドです。それに対してトップメタであるMySQLを比較対象として採用しました。
T-SQL自体はそこまでメジャーなものでもないので利用する人は少ないとは思いますが、実際使うことになったら情報少なかったり、微妙に他と違うものになってるのでその際には、ぜひ参考にしていただけたら嬉しいです。

そもそもT-SQLとは

本記事で詳細に説明することではないと思うので詳しくは 公式のリファレンス 参照

簡単に説明すると、T-SQL（正式にはTransact-SQL）はMicrosoft SQL Serverで利用されているSQL言語です。基本はSQLなので大きくMySQLなどのメジャー言語と差はないのですが上でも書きましたが、微妙に違う部分があります。

MySQL - T-SQL の比較

ざっくりとやっていきます。ベースは前述のとおりトップメタのMySQLとします。
すべてを紹介するわけではない点は承知ください。

データ型について

doubleとenumはT-SQLでは対応していない

もちろん、MySQLならdoubleもfloatもどっちも使える。T-SQLだとdoubleは使えないのでfloat(またはreal)型を使いましょうというだけ。

enumはそもそもないです。

MySQL

-- double
point double
-- enum
color enum('red','blue', 'green')

T-SQL

-- double
point float
point real
-- enum
color varchar(10) NOT NULL CHECK (color IN('red','blue', 'green')) default 'red'

enumの置き換えはどうしても冗長になってしまいますがあきらめましょう。

フィールドの設定について

auto_incrementとkeyは使えない

最重要ポイントかなと自分では考えています。この部分、PostgreSQLとかとも全然違いますし、冗長になってる気がして個人的に好まないんですよね……

MySQL

-- auto_increment
id int not null auto_increment
-- key
key point(point)

T-SQL

-- auto_increment
id int not null IDENTITY(1,1)
-- key
CREATE INDEX score ON users(score);

ポイントとしてはT-SQLの場合、INDEXの設定はそもそも別の句で設定するってところですね。MySQLの場合はテーブル定義の時にセットで書くと思います。

テーブルの作成 - CREATE

上記二点を踏まえたうえで本記事でのベーステーブルを以下CREATE構文で示します。

MySQL

CREATE TABLE accounts(
  id int not null auto_increment,
  name varchar(255),
  mail varchar(255) unique,
  password char(32),
  point double,
  color enum('red', 'blue', 'green'),
  created datetime,
  key point(point)
);

T-SQL

CREATE TABLE accounts(
  id int not null IDENTITY(1,1),
  name varchar(255),
  mail varchar(255) unique,
  password char(32),
  point float,
  color varchar(10) NOT NULL CHECK (color IN('red', 'blue', 'green')) default 'red',
  created datetime
);

CREATE INDEX point ON accounts(point);

こんな感じ。フィールドとかは適当でそれっぽく設定しています。
今回はこのテーブルをベースにしていきます。

テーブルの一覧表示

基本コマンド。そもそもどんなテーブルがあるのかを見る。

MySQL

SHOW TABLES;

T-SQL

SELECT * FROM sys.objects;

-- もしくは
SELECT name, crdate
FROM sysobjects
WHERE xtype = 'U'
ORDER BY NAME

テーブル構造の確認

カラムの定義を確認したくなったら使いましょう。

MySQL

DESC accounts;

T-SQL

sp_columns @table_name = 'accounts';

レコードの挿入 - INSERT

共通

INSERT INTO accounts(
  name,
  mail,
  password ,
  point,
  color,
  created
) VALUES(
  'nayuta',
  '[e-mail]',
  '********'
  5.5,
  'blue',
  '2020-07-19 11:00:00'
), (
  'kiseki',
  '[e-mail]',
  '********'
  7.0,
  'red',
  '2020-07-21 15:00:00'
)

レコードの抽出 - SELECT

共通

-- 全件取得
SELECT * FROM accounts;
-- nameとmailカラムのみ取得
SELECT name, mail FROM accounts;

LENGTH句

そんなに変わらない

MySQL

SELECT mail, LENGTH(mail) FROM accounts;

T-SQL

SELECT mail, LEN(mail) FROM accounts;

NOW句

T-SQLではGETDATEだが、世界標準時間になっているので変換が必要

MySQL

SELECT NOW();

T-SQL

SELECT GETDATE();
SELECT DATEADD(hour, 9, GETDATE());

DATEDIFF句

T-SQLでは引数が3つになっていおり、第一引数に差分の単位が追加されている。
また、NOWが使えない点も注意。

MySQL

SELECT name, DATEDIFF(NOW(), created) FROM accounts;

T-SQL

SELECT name, DATEDIFF(
  day,
  DATEADD(hour, 9, GETDATE()),
  created
) FROM accounts;

メタコマンドGについて

MySQLには便利なメタコマンドとしてGが存在しています。

MySQL

SELECT * FROM accounts \G

簡単に説明すると取得したデータを縦に表示してくれるものです。超便利……

残念ながらT-SQLには存在しません。解散！

レコードの抽出（条件付き） - WHERE

そもそもセクションとしてSELECT構文と分けるか、内包させるかで悩みましたがSQL言語を学ぶ上で非常に重要な項目だと思ったので分けました。

条件の構文に関しては基本的に共通。

WHERE句

基本のき。共通。

-- accountsテーブルでcolorがred以外のレコードを取得
SELECT * FROM accounts WHERE color != 'red';

BETWEEN句

共通

-- accountsテーブルでpointが5.0から8.0までのレコードを取得
SELECT * FROM accounts WHERE point between 5.0 and 8.0;

IN句

共通

-- accountsテーブルでcolorがredまたはblueであるレコードを取得
SELECT * FROM accounts WHERE color in ('red', 'blue');

AND/OR句

共通

-- accountsテーブルでpointが4以上かつcolorがblueのレコードを取得
SELECT * FROM accounts WHERE point >= 4.0 and color = 'blue';
-- accountsテーブルでpointが4以上かcolorがblueのレコードを取得
SELECT * FROM accounts WHERE point >= 4.0 or color = 'blue';

並び替え、件数の制限

WHERE句同様、分けるか内包するか悩んだ末分けた。

ORDER BY句

共通
デフォルトは昇順。昇順（asc）と降順（desc）は切り替え可能

SELECT * FROM accounts ORDER BY point;
SELECT * FROM accounts ORDER BY point desc;

LIMIT句

T-SQLには存在しません。面倒ですが、OFFSET FETCH句を利用しましょう。
流れとしてはソートしてOFFSET、FETCHになります。

LIMITがどれだけ便利だったかがわかりますね……

先頭から3つレコードを取得する

MySQL

SELECT * FROM accounts LIMIT 3;

T-SQL

SELECT * FROM accounts ORDER BY id OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

先頭2番目から2つレコードを取得する

MySQL

SELECT * FROM accounts LIMIT 2,2;

T-SQL

SELECT * FROM accounts ORDER BY id OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

レコードの更新

共通

UPDATE accounts SET mail = '[e-mail]' WHERE id = 1;

レコードの削除

共通

DELETE FROM accounts WHERE point <= 9.0;

テーブル構造の変更

フィールドの追加

T-SQLの使い勝手が悪い部分です（他もですけど）
T-SQLではカラムの位置を指定しての追加ができません（修正されてたらすみません）

一番後ろに追加することはできます。

MySQL

ALTER TABLE accounts ADD full_name varchar(255) AFTER name;

T-SQL

ALTER TABLE accounts ADD full_name varchar(255);

どっか間に入れたいってなったら……あきらめてSSMSを使うかテーブル自体を生成しなおしましょう。

参考: 【驚愕】SQLServer2008は指定位置にカラムを追加できない【ツール強要】

フィールドの変更

CHENGE句は使えません。

MySQL

ALTER TABLE accounts CHANGE name name varchar(100);

T-SQL

ALTER TABLE accounts ALTER COLUMN name varchar(100);

フィールドの削除

MySQL

ALTER TABLE accounts DROP name;

T-SQL

ALTER TABLE accounts DROP COLUMN name;

テーブル名の変更

ALTER TABLEじゃできないのでsp_renameでやりましょう

MySQL

ALTER TABLE accounts rename new_accounts;

T-SQL

sp_rename accounts, new_accounts;
EXEC sp_rename 'accounts', 'new_accounts';

おわりに

初めての投稿ということもあったんで色々書き方がわからなかったり、どういう風に書けばいいのかわからない点も多かったです。結構ノリと勢いで書いている感じがあるんで指摘していただけると嬉しいです！

当初の目的は私自身の知識の確認という部分が大きかったのですが、もし誰かの手助けになれば幸いです。

以上、閲覧ありがとうございました！