基本的なSQLコマンド-知っておくべきデータベースクエリとステートメントのリスト

SQLはStructuredQueryLanguageの略です。SQLコマンドは、データベースと通信してタスク、関数、およびデータを使用したクエリを実行するために使用される命令です。

SQLコマンドを使用して、データベースを検索したり、テーブルの作成、テーブルへのデータの追加、データの変更、テーブルの削除などの他の機能を実行したりできます。

これは、SQLを使用する場合に知っておく必要のある基本的なSQLコマンド(句と呼ばれることもあります)のリストです。

SELECTとFROM

SELECTクエリの一部は、結果に表示するデータの列を決定します。テーブル列ではないデータを表示するために適用できるオプションもあります。

ショー以下の例では、3つの列がSELECTFROM「学生」の表と1つの計算列を。データベースには、学生のstudentID、FirstName、およびLastNameが格納されます。姓と名の列を組み合わせて、FullName計算列を作成できます。

SELECT studentID, FirstName, LastName, FirstName + ' ' + LastName AS FullName FROM student;
+-----------+-------------------+------------+------------------------+ | studentID | FirstName | LastName | FullName | +-----------+-------------------+------------+------------------------+ | 1 | Monique | Davis | Monique Davis | | 2 | Teri | Gutierrez | Teri Gutierrez | | 3 | Spencer | Pautier | Spencer Pautier | | 4 | Louis | Ramsey | Louis Ramsey | | 5 | Alvin | Greene | Alvin Greene | | 6 | Sophie | Freeman | Sophie Freeman | | 7 | Edgar Frank "Ted" | Codd | Edgar Frank "Ted" Codd | | 8 | Donald D. | Chamberlin | Donald D. Chamberlin | | 9 | Raymond F. | Boyce | Raymond F. Boyce | +-----------+-------------------+------------+------------------------+ 9 rows in set (0.00 sec)

CREATE TABLE

CREATE TABLEそれがどのように聞こえるかを実行します。データベースにテーブルを作成します。テーブルの名前と、テーブルに含める必要のある列を指定できます。

CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype );

他の机

ALTER TABLEテーブルの構造を変更します。データベースに列を追加する方法は次のとおりです。

ALTER TABLE table_name ADD column_name datatype;

小切手

CHECK制約は、列に配置することができる値の範囲を制限するために使用されます。

CHECK単一の列に制約を定義すると、この列には特定の値のみが許可されます。CHECKテーブルに制約を定義すると、その行の他の列の値に基づいて、特定の列の値を制限できます。

次のSQLはCHECK、「Persons」テーブルの作成時に「Age」列に制約を作成します。CHECKあなたは18歳未満の者を持つことができないという制約を保証します。

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );

CHECK制約の命名を許可しCHECK、複数の列に制約を定義するには、次のSQL構文を使用します。

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City="Sandnes") );

どこ

ANDORINBETWEEN、およびLIKE

このWHERE句は、返される行数を制限するために使用されます。

例として、最初にSELECTステートメントとステートメントなしの結果示しますWHERE。次に、WHERE上記の5つの修飾子すべてを使用するステートメントを追加します。

SELECT studentID, FullName, sat_score, rcd_updated FROM student;
+-----------+------------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+------------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 3 | Spencer Pautier | 1000 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 7 | Edgar Frank "Ted" Codd | 2400 | 2017-08-16 15:35:33 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+------------------------+-----------+---------------------+ 9 rows in set (0.00 sec)

ここで、SELECTクエリを繰り返しますが、WHEREステートメントを使用して返される行を制限します。

STUDENT studentID, FullName, sat_score, recordUpdated FROM student WHERE (studentID BETWEEN 1 AND 5 OR studentID = 8) AND sat_score NOT IN (1000, 1400);
+-----------+----------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+----------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | +-----------+----------------------+-----------+---------------------+ 5 rows in set (0.00 sec)

更新

テーブルのレコードを更新するには、UPDATEステートメントを使用します。

WHERE条件を使用して、更新するレコードを指定します。一度に1つ以上の列を更新することが可能です。構文は次のとおりです。

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

これは、ID4でレコードの名前を更新する例です。

UPDATE Person SET Name = “Elton John” WHERE Id = 4;

他のテーブルの値を使用して、テーブルの列を更新することもできます。このJOIN句を使用して、複数のテーブルからデータを取得します。構文は次のとおりです。

UPDATE table_name1 SET table_name1.column1 = table_name2.columnA table_name1.column2 = table_name2.columnB FROM table_name1 JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key

これは、すべてのレコードのManagerを更新する例です。

UPDATE Person SET Person.Manager = Department.Manager FROM Person JOIN Department ON Person.DepartmentID = Department.ID

GROUP BY

GROUP BY allows you to combine rows and aggregate data.

Here is the syntax of GROUP BY:

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

HAVING

HAVING allows you to filter the data aggregated by the GROUP BY clause so that the user gets a limited set of records to view.

Here is the syntax of HAVING:

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;

AVG()

“Average” is used to calculate the average of a numeric column from the set of rows returned by a SQL statement.

Here is the syntax for using the function:

SELECT groupingField, AVG(num_field) FROM table1 GROUP BY groupingField

Here’s an example using the student table:

SELECT studentID, FullName, AVG(sat_score) FROM student GROUP BY studentID, FullName;

AS

AS allows you to rename a column or table using an alias.

SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table

This results in output as below.

+-------------+------------------------+ | AgeOfServer | NonWarrantyPeriod | +-------------+------------------------+ | 36 | 24 | | 24 | 12 | | 61 | 49 | | 12 | 0 | | 6 | -6 | | 0 | -12 | | 36 | 24 | | 36 | 24 | | 24 | 12 | +-------------+------------------------+

You can also use AS to assign a name to a table to make it easier to reference in joins.

SELECT ord.product, ord.ord_number, ord.price, cust.cust_name, cust.cust_number FROM customer_table AS cust JOIN order_table AS ord ON cust.cust_number = ord.cust_number

This results in output as below.

+-------------+------------+-----------+-----------------+--------------+ | product | ord_number | price | cust_name | cust_number | +-------------+------------+-----------+-----------------+--------------+ | RAM | 12345 | 124 | John Smith | 20 | | CPU | 12346 | 212 | Mia X | 22 | | USB | 12347 | 49 | Elise Beth | 21 | | Cable | 12348 | 0 | Paul Fort | 19 | | Mouse | 12349 | 66 | Nats Back | 15 | | Laptop | 12350 | 612 | Mel S | 36 | | Keyboard| 12351 | 24 | George Z | 95 | | Keyboard| 12352 | 24 | Ally B | 55 | | Air | 12353 | 12 | Maria Trust | 11 | +-------------+------------+-----------+-----------------+--------------+

ORDER BY

ORDER BY gives us a way to sort the result set by one or more of the items in the SELECT section. Here is an SQL sorting the students by FullName in descending order. The default sort order is ascending (ASC) but to sort in the opposite order (descending) you use DESC.

SELECT studentID, FullName, sat_score FROM student ORDER BY FullName DESC;

COUNT

COUNT will count the number of rows and return that count as a column in the result set.

Here are examples of what you would use COUNT for:

  • Counting all rows in a table (no group by required)
  • Counting the totals of subsets of data (requires a Group By section of the statement)

This SQL statement provides a count of all rows. Note that you can give the resulting COUNT column a name using “AS”.

SELECT count(*) AS studentCount FROM student; 

DELETE

DELETE is used to delete a record in a table.

Be careful. You can delete all records of the table or just a few. Use the WHERE condition to specify which records you want to delete. The syntax is:

DELETE FROM table_name WHERE condition;

Here is an example deleting from the table Person the record with Id 3:

DELETE FROM Person WHERE Id = 3;

INNER JOIN

JOIN, also called Inner Join, selects records that have matching values in two tables.

SELECT * FROM A x JOIN B y ON y.aId = x.Id

LEFT JOIN

A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. Rows in the left table will be returned even if there was no match in the right table. The rows from the left table with no match in the right table will have null for right table values.

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table, and the matched rows from the left table. Opposite of a left join, this will return all rows from the right table even where there is no match in the left table. Rows in the right table that have no match in the left table will have null values for left table columns.

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id 

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows for which there is a match in either of the tables. So if there are rows in the left table that do not have matches in the right table, those will be included. Also, if there are rows in the right table that do not have matches in the left table, those will be included.

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName

INSERT

INSERT is a way to insert data into a table.

INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);

LIKE

LIKE  is used in a WHERE or HAVING (as part of the GROUP BY) to limit the selected rows to the items when a column has a certain pattern of characters contained in it.

This SQL will select students that have FullName starting with “Monique” or ending with “Greene”.

SELECT studentID, FullName, sat_score, rcd_updated FROM student WHERE FullName LIKE 'Monique%' OR FullName LIKE '%Greene'; 
+-----------+---------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+---------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | +-----------+---------------+-----------+---------------------+ 2 rows in set (0.00 sec)

You can place NOT before LIKE to exclude the rows with the string pattern instead of selecting them. This SQL excludes records that contain “cer Pau” and “Ted” in the FullName column.

SELECT studentID, FullName, sat_score, rcd_updated FROM student WHERE FullName NOT LIKE '%cer Pau%' AND FullName NOT LIKE '%"Ted"%';
+-----------+----------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+----------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+----------------------+-----------+---------------------+ 7 rows in set (0.00 sec)