---
title: Ch3 - Database Operations with SQL
date: 2024-03-27
excerpt: SQL statement types and data types; DDL for databases, tables, and indexes; DML for insert, update, delete; DQL with sorting, grouping, subqueries, joins; and views.
tags: [DataBase]
cover: https://assets.vluv.space/cover/DataBase/DbSQL.webp
updated: 2026-07-08 21:26:01
lang: en
i18n:
  cn: /Ch3-DbSQL
  translation: 2
---

## SQL Statements

- Data Definition Language (DDL): create, modify, and drop database objects; create/drop/alter database/table/index
- Data Manipulation Language (DML): insert, delete, and update data; insert/update/delete
- Data Query Language (DQL): query data;
- Data Control Language (DCL): access control over database objects; grant/deny/revoke
- Transaction Processing Language (TPL): transaction handling; begin transaction/commit/rollback
- Cursor Control Language (CCL): cursor operations; declare cursor/fetch into/close curso

### DDL

Data Definition Language (DDL) refers to the SQL statements used to create, modify, or drop database objects.

#### Database

```sql
-- database
CREATE DATABASE db_name;
ALTER DATABASE db_name RENAME TO new_db_name;
DROP DATABASE db_name;
```

#### Table

```sql
-- table
CREATE TABLE table_name (
    Sname varchar(10) NOT NULL,
    Age int NOT NULL,
    Sid char(10) PRIMARY KEY,
);

'''
Integrity constraints include:
PRIMARY KEY
NOT NULL
NULL
UNIQUE
CHECK  validity check
DEFAULT
'''

'''Suppose we create a table where the City column may only be Shanghai or Beijing, and age defaults to 10'''
CREATE TABLE TEST(
    City varchar(10) CHECK(City IN('上海','北京')),
    Age int NOT NULL DEFAULT 10
);

ALTER TABLE <table_name> ADD <new_column_name><data_type>|[integrity constraint]
 ALTER TABLE<table_name> DROP  COLUMN <column_name>；
ALTER TABLE<table_name> DROP  CONSTRAINT<constraint_name>；
ALTER TABLE <table_name> RENAME TO <new_table_name>；
ALTER TABLE <table_name> RENAME <old_column_name> TO <new_column_name>；
ALTER TABLE <table_name> ALTER  COLUMN <column_name> TYPE<new_data_type>；
DROP TABLE <table_name>;
```

#### Primary/Foreign Key Constraint

**Primary Key Constraint syntax**

- Define a single-column primary key
  `Age int PRIMARY KEY`
- Define a multi-column primary key
  `CONSTRAINT <constraint_name> PRIMARY KEY <col1,col2...>`
- When defining a surrogate key, note that the primary key should be of type serial

```sql
CREATE TABLE TEST(
    City varchar(10) PRIMARY KEY,
    Age int PRIMARY KEY
);

CREATE TABLE TEST(
    Name varchar(10),
    Age int,
    CONSTRAINT PK_TEST PRIMARY KEY(Name,Age)
);

CREATE TABLE TEST(
    Idx serial PRIMARY KEY,
    Name varchar(10),
    Age int
);
```

**Foreign Key Constraint**
For a foreign key to be created successfully, the following conditions must hold:

- The referenced table (the one named after REFERENCES) must already exist.
- The referenced column must be the parent table's primary key or carry a unique constraint.
- The data types must be compatible, that is, the foreign key column and the referenced column must have the same type or be convertible.

```sql
CREATE TABLE parent_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(10)
)

CREATE TABLE child_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(10),
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);
-- ON DELETE CASCADE
ALTER TABLE
    child_table
ADD CONSTRAINT
    fk_parent_id FOREIGN KEY (parent_id)
REFERENCES parent_table(id)
ON DELETE CASCADE;
```

In SQL, especially in relational database systems such as MySQL and PostgreSQL, foreign keys not only enforce data integrity but can also be configured with different behaviors, such as ON DELETE and ON UPDATE rules, which decide what happens to related rows in the child table when a record in the parent table is deleted or updated. Common ON DELETE and ON UPDATE rules include:

- RESTRICT or NO ACTION
  This is the default behavior. If you try to delete or update a record in the parent table while the child table still has foreign keys depending on it, the operation is blocked. This preserves referential integrity and prevents accidental data loss.
- CASCADE:
  When a record in the parent table is deleted or updated, all related rows in the child table are deleted or updated accordingly, keeping the data in the two tables consistent.
- SET NULL:
  If a record in the parent table is deleted or updated, the corresponding foreign key column in the child table is set to NULL. This requires the foreign key column to allow NULL.
- SET DEFAULT:
  When the referenced primary key is deleted or updated, the foreign key column in the child table is set to its default value.
  Note that SET DEFAULT is not supported in MySQL.

```sql
'''
Managing constraints
constraint_type specifies the kind of constraint, e.g. FOREIGN KEY, UNIQUE, PRIMARY KEY.
'''

ALTER TABLE
    table_name
ADD CONSTRAINT
    constraint_name constraint_type(column_name, ...);

ALTER TABLE
    child_table
ADD CONSTRAINT IF NOT EXISTS
    fk_parent_id FOREIGN KEY (parent_id)
REFERENCES
    parent_table(id)
    ON DELETE CASCADE;

ALTER TABLE child_table DROP CONSTRAINT fk_parent_id;
```

#### Index

Index: a data structure that organizes a table's tuples by the values of specified columns. It speeds up queries, but takes extra storage and carries maintenance overhead.

```sql
CREATE INDEX Birthday_Idx ON STUDENT(Birthday);
ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;
DROP INDEX bday_idx;
```

### DML

DML: Data Manipulation Language, used for CRUD (Create, Retrieve, Update, Delete) operations on data

Ref: [[DataBase01#DML]]

#### INSERT, UPDATE, DELETE

```sql
-- insert
INSERT INTO  <table|view>[<column list>]  VALUES （value list）;
INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程',
'liuyin@163.com')
-- update
UPDATE
    <table|view>
SET
    <column1>=<expression1> [，<column2>=<expression2>...]
[WHERE <condition>]；

UPDATE
    Student
SET
    Email='zhaodong@163.com'
WHERE
    StudentName='赵东';
-- delete
DELETE FROM
    <table|view>
[WHERE <condition>]

DELETE FROM
    Student
WHERE
    StudentName='赵东';
```

### DQL

```sql
SELECT  [ALL|DISTINCT]  <target column>[，<target column>…]
[ INTO <new table> ]
FROM  <table|view>[，<table|view>…]
[ WHERE  <condition> ]
[ GROUP BY  <column> [HAVING <condition> ]]
[ ORDER BY  <column> [ ASC | DESC ] ];


-- BETWEEN AND restricts the range of column values
SELECT  *
FROM  STUDENT
WHERE BirthDay BETWEEN ‘2000-01-01’ AND ‘2000-12-30’;

-- LIKE wildcards: '_' matches one character, '%' matches one or more characters
SELECT  *
FROM  STUDENT
WHERE  Email  LIKE  '%@163.com';

-- AND\OR\NOT logical operators
SELECT  StudentID, StudentName, StudentGender, Major
FROM  STUDENT
WHERE  Major='软件工程'  AND  StudentGender='男';

-- IN limits the value set
SELECT  StudentID, StudentName, StudentGender, Major
FROM  STUDENT
WHERE  Major IN  ('CS','SE');

-- ORDER BY <> ASC/DESC, ascending (ASC) by default
-- With multiple sort columns, the next column is compared only when the current one ties
SELECT  *
FROM  STUDENT
ORDER  BY  Birthday DESC ,  StudentName  ASC;

-- Built-in functions
SELECT  COUNT（*） AS  学生人数
FROM  Student;

SELECT  Min（Birthday） AS 最大年龄，Max（Birthday） AS 最小年龄
FROM  Student;

-- GROUP BY <> HAVING
-- Count male students in STUDENT by major, but only show majors with more than 2
SELECT  Major  AS 专业,  COUNT（StudentID） AS 学生人数
FROM  Student
WHERE  StudentGender=’男’
GROUP  BY  Major
HAVING  COUNT(*)>2;
```

#### Multi-table Queries

##### Subquery

```sql
SELECT  TeacherID, TeacherName, TeacherTitle
FROM  Teacher
WHERE  CollegeID  IN
        (SELECT  CollegeID
         FROM  College
         WHERE  CollegeName='计算机学院');
```

##### Join Query

```sql
SELECT
    B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称
FROM
    Teacher  AS  A，College  AS  B
WHERE
    A.CollegeID=B.CollegeID
ORDER BY
    B.CollegeName, A.TeacherID;

-- JOIN ON inner join
SELECT
    B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称
FROM
    TEACHER  AS  A  JOIN  COLLEGE  AS  B
ON
    A.CollegeID=B.CollegeID
ORDER BY
    B.CollegeName, A.TeacherID;

-- LEFT JOIN/RIGHT JOIN/FULL JOIN outer joins
SELECT
    C.CourseName AS 课程名称, T.TeacherName AS 教师,COUNT (R.CoursePlanID)  AS 选课人数
FROM
    COURSE  AS  C
JOIN
    PLAN  AS  P
    ON C.CourseID=P.CourseID
JOIN
    TEACHER  AS  T
    ON P.TeacherID=T.TeacherID
LEFT  JOIN
    REGISTER  AS  R
    ON  P.CoursePlanID=R.CoursePlanID
GROUP BY
    C.CourseName, T.TeacherName;
```