SQLを理解する

データベース

授業で必要になりそうなので、Codecademyで学んだことをぱぱっとまとめておきます。
Learn SQL | Codecademy
SQLリファレンス
Oracle Database SQLリファレンス -- 目次
逆引きSQL構文集
SQL Quick Reference

SQLって？

'Structured Query Language', is a programming language designed to manage data stored in relational databases. SQL operates through simple, declarative statements. This keeps data accurate and secure, and helps maintain the integrity of databases, regardless of size.
SQLとは、リレーショナルデータベースのデータを管理する目的で作られたプログラミング言語です。
The SQL language is widely used today across web frameworks and database applications. Knowing SQL gives you the freedom to explore your data, and the power to make better decisions. By learning SQL, you will also learn concepts that apply to nearly every data storage system.


CREATE statement

CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);

テーブル（表）を作ります。パラメーターは、格納するデータタイプを定義します。
1. CREATE TABLE is a clause that tells SQL you want to create a new table.
2. celebs is the name of the table.
3.(id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each column in the table and its data type.

INSERT statement

INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);

表に新たにロウ（行）を加えます。VALUESで挿入するデータを示します。
1. INSERT INTO is a clause that adds the specified row or rows.
2. celebs is the name of the table the row is added to.
3. (id, name, age) is a parameter identifying the columns that data will be inserted into.
4. VALUES is a clause that indicates the data being inserted.
(1, 'Justin Bieber', 21) is a parameter identifying the values being inserted.

SELECT statement

SELECT name FROM celebs;

データベースからデータをフェッチ（取得）します。この場合、SELECTはcelebsテーブルからnameカラム（列）を返します。
複数のカラムをコンマで区切ることで、複数のカラムに属するデータをフェッチすることができます。
1. SELECT is a clause that indicates that the statement is a query. You will use SELECT every time you query data from a database.
2. name specifies the column to query data from.
3. FROM celebs specifies the name of the table to query data from. In this statement, data is queried from the celebs table.

SELECT * FROM celebs;

* は特別なワイルドカード文字です。この場合、SELECTはcelebs表から各カラム（行）を返します。
* is a special wildcard character that we have been using. It allows you to select every column in a table without having to name each one individually. Here, the result set contains every column in the celebs table.

SELECT filter（フィルター）
SELECT DISTINCT genre FROM movies;

result setから特定の値を返します。重複した結果は取り除かれます。この場合、moviesテーブルの中に格納されているそれぞれのgenreがちょうど一度だけ、返されます。
1. SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s)
2. genre is the name of the column to display in the result set.
3. FROM movies indicates the table name to query from.

SELECT * FROM movies  WHERE imdb_rating > 8;

この場合、result setにフィルターをかけ、imdb_ratingが８より大きいmoviesのみを返します。
1. WHERE is a clause that indicates you want to filter the result set to include only rows where the following condition is true.
2. imdb_rating > 8 is a condition that filters the result set. Here, only rows with a value greater than 8 in the imdb_rating column will be returned in the result set.
3. > is an operator. Operators create a condition that can be evaluated as either true or false. Common operators used with the WHERE clause are:

  • = equals != not equals > greater than < less than >= greater than or equal to <= less than or equal to
SELECT * FROM movies WHERE name LIKE 'Se_en';

似たような値を比較するときに使われます。この場合、nameカラムの値がSe_enのmoviesデータを一つ以上返します。
_は一種のワイルドカード文字です。文字数はresult setに引き継がれます。大文字小文字は区別されません。
1. LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.
2. name LIKE Se_en is a condition evaluating the name column for a specific pattern.
3. Se_en represents a pattern with a wildcard character. The _ means you can substitute any individual character here without breaking the pattern. The names Seven and Se7en both match this pattern.

SELECT * FROM movies WHERE name LIKE '%man%';

％もワイルドカード文字です。文字数は引き継がれません。大文字小文字は区別されません。例えば、この場合、'Man Of Steel','The Batman'が返されます。
% is a wildcard character that matches zero or more missing letters in the pattern.

SELECT * FROM movies
WHERE name BETWEEN 'A' AND 'J';

"A""から"J"までの文字から始まるnameを持つmoviesのみをresult setは含みます。
This statement filters the result set to only include movies with names that begin with letters "A" up to but not including "J".

SELECT * FROM movies
  WHERE year BETWEEN 1990 and 2000
  AND genre = 'comedy';

AND,OR operaterを使って、条件文(condition)を結ぶことができます。
Sometimes you want to combine multiple conditions in a WHERE clause to make the result set more specific and useful. One way of doing this is to use the AND operator.
The OR operator can also be used to combine more than one condition in a WHERE clause. The OR operator evaluates each condition separately and if any of the conditions are true then the row is added to the result set.

ORDER BY
SELECT * FROM movies
ORDER BY imdb_rating DESC;
LIMIT 3;

resultを降順にソートします。
LIMITによってresult setに与えられるrow(行)の最大数を制限します。

You can sort the results of your query using ORDER BY. Sorting the results often makes the data more useful and easier to analyze.
1. ORDER BY is a clause that indicates you want to sort the result set by a particular column either alphabetically or numerically.
2. imdb_rating is the name of the column that will be sorted.
3. DESC is a keyword in SQL that is used with ORDER BY to sort the results in descending order (high to low or Z-A). Here, it sorts all of the movies from highest to lowest by their IMDb rating.

Sometimes even filtered results can return thousands of rows in large databases. In these situations it becomes important to cap the number of rows in a result set.
LIMIT is a clause that lets you specify the maximum number of rows the result set will have. Here, we specify that the result set can not have more than three rows.

UPDATE statement

UPDATE celebs SET age = 22 WHERE id = 1;

ロウ（行）を書き換えます。SETで編集するロウ（行）とその内容を指定し、WHEREでどのロウ（行）を編集するのかを指定します。この場合、idカラム（列）が１のデータの、ageカラム（列）を22に書き換えます。

1. UPDATE is a clause that edits a row in the table.
2. celebs is the name of the table.
3. SET is a clause that indicates the column to edit.

  • age is the name of the column that is going to be updated 22 is the new value that is going to be inserted into the age column.

4. WHERE is a clause that indicates which row(s) to update with the new column value. Here the row with a 1 in the id column is the row that will have the age updated to 22.

ALERT statement

ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

カラム（列）を加えます。ALERTは特定の変更を行うときにつけます。
NULLは失われた、あるいは未知のデータを表す特別なvalueです。ここでは、カラム（列）が追加された前に存在していたロウ（行）はtwitter_handleに対して、NULLの値を持ちます。
1. ALTER TABLE is a clause that lets you make the specified changes.
2. celebs is the name of the table that is being changed.
3. ADD COLUMN is a clause that lets you add a new column to a table.

  • twitter_handle is the name of the new column being added TEXT is the data type for the new column

4. NULL is a special value in SQL that represents missing or unknown data. Here, the rows that existed before the column was added have NULL values for twitter_handle.

DELETE statement

DELETE FROM celebs WHERE twitter_handle IS NULL;

テーブルから一つ以上のロウ（行）を削除します。この場合、twitter_handleがNULLの値を持つロウ（行）全てを削除します。
IS NULL は条件文で、値がNULLなら真を、それ以外は偽を返します。

  • DELETE FROM is a clause that lets you delete rows from a table.
  • celebs is the name of the table we want to delete rows from.
  • WHERE is a clause that lets you select which rows you want to delete. Here we want to delete all of the rows where the twitter_handle column IS NULL.
  • IS NULL is a condition in SQL that returns true when the value is NULL and false otherwise.