SQL の基本
1導入
この
SQL は、データへ
2SELECT、FROM、WHERE
SELECT、FROM、WHERE の
FROMは、問 い合 わせの対象 となる表 を指定 する。WHEREは、各行 を条件式 によって選択 する。この操作 を選択 SELECTは、結果 に含 める列 または式 を指定 し、概 ね射影 対応 する。ただし、SQLは既定 で重複行 を保持 し、SELECT DISTINCTを指定 した場合 に重複 を除去 する。これは関係代数 の集合 としての射影 との相違 である。
FROM でWHERE でSELECT で
SELECT student_id, name
FROM students
WHERE department = 'Computer Science';
このstudents Computer Science であるstudent_id と name をDISTINCTを
3結果 の順序
SQL のORDER BY を
SELECT student_id, name
FROM students
ORDER BY student_id ASC;
ORDER BY に
4NULL と三値論理
NULL は、NULL をTRUE または FALSE ではなく UNKNOWN となる。SQL の
WHERE がTRUE となるFALSE と UNKNOWN のNULL の= NULL ではなく IS NULL または IS NOT NULL を
SELECT student_id, name
FROM students
WHERE email IS NULL;
5JOIN とキー
JOIN によってstudents.student_id をenrollments.student_id をそれを
SELECT s.student_id, s.name, e.course_id
FROM students AS s
JOIN enrollments AS e
ON e.student_id = s.student_id
WHERE e.status = 'enrolled';
ON はJOIN
6更新 、制約 、トランザクション
SQL は、INSERT でUPDATE でDELETE で
INSERT INTO enrollments (student_id, course_id, status)
VALUES (1001, 'CS101', 'enrolled');
UPDATE enrollments
SET status = 'completed'
WHERE student_id = 1001 AND course_id = 'CS101';
DELETE FROM enrollments
WHERE student_id = 1001 AND course_id = 'CS101';
PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、CHECK などの
COMMIT はトランザクションのROLLBACK は
7パラメータ化 と安全性
SELECT student_id, name
FROM students
WHERE email = ?;
? はパラメータの
8要点
- SQL は、
必要 な結果 を宣言 し、その物理的 な実行方法 をデータベース管理 システムへ委 ねる。 WHEREは行 を選択 し、SELECTは結果 の列 や式 を指定 する。SQLは既定 で重複行 を保持 する。NULLを含 む条件 には三値論理 が適用 される。JOINは複数 の表 を関連付 け、キーと制約 はデータの整合性 を支 える。更新 はトランザクションによって一体 として管理 でき、外部入力 はパラメータ化 して SQL構文 から分離 する。ORDER BYのない結果 に順序 の保証 はない。
9次 に進 む講義
data/lecture/information/software-engineering/software-engineering-portal.lecture.n.md
SQL Fundamentals
data/lecture/information/database/database-basics.lecture.n.md1Introduction
This lecture explains the fundamentals of
SQL describes conditions on a required result rather than a procedure for reaching the data. A database management system normally chooses the execution plan, including index use and join order. This declarative approach separates a logical query from its physical execution method.
2SELECT, FROM, and WHERE
A basic query consists of three elements: SELECT, FROM, and WHERE.
FROMspecifies the tables that form the query's input.WHEREtests each row against a condition. This operation is calledselection , or filtering.SELECTspecifies the columns or expressions included in the result and broadly corresponds toprojection . SQL retains duplicate rows by default;SELECT DISTINCTremoves duplicates. This differs from projection under the set semantics of relational algebra.
Conceptually, FROM establishes the input, WHERE selects rows, and SELECT projects columns. These are logical roles; a database management system need not perform the physical operations in this order.
SELECT student_id, name
FROM students
WHERE department = 'Computer Science';
This query selects rows whose department is Computer Science from students and specifies student_id and name as result columns. Duplicate rows remain unless DISTINCT is specified.
3Result Ordering
A SQL query result has no guaranteed order unless the query specifies ORDER BY. The displayed order of the same query can change with the execution plan or physical data layout.
SELECT student_id, name
FROM students
ORDER BY student_id ASC;
When reproducible ordering is required, ORDER BY must include enough columns to resolve ties.
4NULL and Three-Valued Logic
NULL is a marker for a value that is absent or unknown; it is neither an empty string nor the number zero. An ordinary comparison involving NULL yields UNKNOWN rather than TRUE or FALSE. SQL conditions therefore follow
WHERE retains only rows for which its condition is TRUE; it excludes rows for which the condition is FALSE or UNKNOWN. A query must consequently test nullity with IS NULL or IS NOT NULL, not with = NULL.
SELECT student_id, name
FROM students
WHERE email IS NULL;
5JOIN and Keys
JOIN combines related data stored in separate tables. Suppose that students.student_id is a primary key and enrollments.student_id is a foreign key that references it.
SELECT s.student_id, s.name, e.course_id
FROM students AS s
JOIN enrollments AS e
ON e.student_id = s.student_id
WHERE e.status = 'enrolled';
ON specifies the join condition that matches rows from the two tables. The uniqueness of the primary key and the referential integrity of the foreign key allow the database to validate this relationship. A JOIN can nevertheless execute even when no foreign-key constraint has been declared.
6Updates, Constraints, and Transactions
SQL uses INSERT to add rows, UPDATE to modify existing rows, and DELETE to remove rows.
INSERT INTO enrollments (student_id, course_id, status)
VALUES (1001, 'CS101', 'enrolled');
UPDATE enrollments
SET status = 'completed'
WHERE student_id = 1001 AND course_id = 'CS101';
DELETE FROM enrollments
WHERE student_id = 1001 AND course_id = 'CS101';
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK record conditions on admissible data in the schema and reject invalid updates. Specific constraint features and syntax vary among database products.
A COMMIT makes a transaction's changes permanent, whereas ROLLBACK cancels uncommitted changes. Transactions can thereby prevent the database from retaining only an intermediate portion of an operation. Isolation levels and default autocommit behavior vary among implementations.
7Parameterization and Security
Directly concatenating user input into a SQL string can cause SQL injection, in which input is interpreted as SQL syntax. Values must instead be supplied separately through the parameterized-query facility of the relevant database driver.
SELECT student_id, name
FROM students
WHERE email = ?;
Here, ? is a conceptual parameter marker; actual placeholder syntax varies among drivers and database products. Parameterization separates values from SQL syntax, but it does not automatically make arbitrary identifiers such as table or column names safe. Dynamically selected identifiers require validation such as an allowlist. Authorization, least privilege, and protection of confidential data remain separate requirements.
8Key Points
- SQL declares a required result and delegates its physical execution method to the database management system.
WHEREselects rows, andSELECTspecifies result columns or expressions. SQL retains duplicate rows by default. Three-valued logic applies to conditions involvingNULL.JOINrelates multiple tables, while keys and constraints support data integrity.- Transactions can manage updates as one unit, and external values must be parameterized to separate them from SQL syntax.
- A result has no guaranteed order without
ORDER BY.