SQL — DDL and DML
| English | Chinese | Pinyin |
|---|---|---|
| SQL | 结构化查询语言 | jié gòu huà chá xún yǔ yán |
| DDL | 数据定义语言 | shù jù dìng yì yǔ yán |
| DML | 数据操作语言 | shù jù cāo zuò yǔ yán |
| join | 连接 | lián jiē |
| aggregate functions | 聚合函数 | jù hé hán shù |
Talking to a database with SQL 结构化查询语言
- SQL (Structured Query Language) is how we build and use a database.
- It has two halves: DDL 数据定义语言 changes the structure; DML 数据操作语言 works with the data.
- Let's see the core statements of each.
Which is part of the Data Definition Language (DDL)?
DDL changes the structure (CREATE, ALTER, DROP). SELECT/INSERT/UPDATE/DELETE are DML (working with data).
DDL defines the structure (e.g. CREATE TABLE), while DML works with the data inside it (SELECT, INSERT, UPDATE, DELETE).
Definition vs Manipulation: DDL shapes the tables; DML reads and changes the rows.
DDL — defining the structure
CREATE TABLE Customer (
CustomerID INTEGER PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Phone VARCHAR(20)
);
ALTER TABLE Customer ADD Email VARCHAR(100);
DROP TABLE Customer;
CREATE TABLEbuilds a table with field types and keys;ALTERchanges it;DROPdeletes it.- Add a relationship with
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID). - Common types:
INTEGER,VARCHAR(n),CHAR(n),DATE,BOOLEAN,DECIMAL(p, s).
A SELECT query returns only the rows that match its condition
DML — querying with SELECT
SELECT Name, Phone
FROM Customer
WHERE City = 'London'
ORDER BY Name ASC;
SELECTlists fields ·FROMnames the table ·WHEREfilters rows ·ORDER BYsorts.- Strings go in single quotes; comparisons:
= < > <= >= <>; patterns:LIKE 'A%'; sets:IN (1,2,3); ranges:BETWEEN 10 AND 20.
DDL builds the database structure; DML works with the data
Which SQL keyword retrieves data from a table? (one word)
SELECT lists the fields to retrieve; FROM names the table.
The WHERE clause in a SELECT statement:
WHERE filters rows by a condition; ORDER BY sorts; the SELECT list chooses columns.
Joins 连接 and aggregates
SELECT C.Name, O.OrderDate
FROM Customer C INNER JOIN Orders O
ON C.CustomerID = O.CustomerID;
SELECT CustomerID, COUNT(*) AS NumOrders
FROM Orders
GROUP BY CustomerID;
- A JOIN combines two tables using a foreign-key relationship.
- Aggregate functions 聚合函数 (
COUNT,SUM,AVG,MIN,MAX) summarise rows, often withGROUP BY.
Stitch two tables with INNER JOIN
A join matches rows where the foreign key equals the primary key — here Orders.CustomerID = Customer.CustomerID — and combines each matching pair into one wider row.
An INNER JOIN is used to:
A JOIN combines two tables on a relationship (usually a foreign key matching a primary key).
What does COUNT(*) return?
COUNT(*) counts rows; SUM/AVG/MIN/MAX are the other aggregate functions.
Insert, update, delete
INSERT INTO Customer (CustomerID, Name) VALUES (101, 'Ada Lovelace');
UPDATE Customer SET Phone = '020-9999' WHERE CustomerID = 101;
DELETE FROM Customer WHERE CustomerID = 101;
- Always put a
WHEREclause onUPDATEandDELETE, or the change hits every row. - SQL data types include REAL, CHARACTER and TIME; the Data Definition Language (DDL) builds structure and the DBMS runs it.
What happens if you run UPDATE or DELETE without a WHERE clause?
With no WHERE, the operation affects all rows — a common and dangerous mistake.
Match each DML statement to what it does.
SELECT reads; INSERT adds; UPDATE changes; DELETE removes — the four core DML verbs.
You've got it
- DDL (
CREATE,ALTER,DROP) defines structure; DML works with data - query with
SELECT … FROM … WHERE … ORDER BY - JOIN combines tables via a foreign key; aggregates (
COUNT/SUM/AVG) often pair withGROUP BY INSERT/UPDATE/DELETEchange data — alwaysWHEREon update/delete!