Mar 26·6 min· PV

Ch3 - Database Operations With SQL

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.

DataBase

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

-- databaseCREATE DATABASE db_name;ALTER DATABASE db_name RENAME TO new_db_name;DROP DATABASE db_name;

Table

-- tableCREATE TABLE table_name (    Sname varchar(10) NOT NULL,    Age int NOT NULL,    Sid char(10) PRIMARY KEY,);'''Integrity constraints include:PRIMARY KEYNOT NULLNULLUNIQUECHECK  validity checkDEFAULT''''''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

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.

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 CASCADEALTER TABLE    child_tableADD 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.

'''Managing constraintsconstraint_type specifies the kind of constraint, e.g. FOREIGN KEY, UNIQUE, PRIMARY KEY.'''ALTER TABLE    table_nameADD CONSTRAINT    constraint_name constraint_type(column_name, ...);ALTER TABLE    child_tableADD 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.

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

INSERT, UPDATE, DELETE

-- insertINSERT INTO  <table|view>[<column list>]  VALUES （value list）;INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程','liuyin@163.com')-- updateUPDATE    <table|view>SET    <column1>=<expression1> [，<column2>=<expression2>...][WHERE <condition>]；UPDATE    StudentSET    Email='zhaodong@163.com'WHERE    StudentName='赵东';-- deleteDELETE FROM    <table|view>[WHERE <condition>]DELETE FROM    StudentWHERE    StudentName='赵东';

DQL

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 valuesSELECT  *FROM  STUDENTWHERE BirthDay BETWEEN ‘2000-01-01’ AND ‘2000-12-30’;-- LIKE wildcards: '_' matches one character, '%' matches one or more charactersSELECT  *FROM  STUDENTWHERE  Email  LIKE  '%@163.com';-- AND\OR\NOT logical operatorsSELECT  StudentID, StudentName, StudentGender, MajorFROM  STUDENTWHERE  Major='软件工程'  AND  StudentGender='男';-- IN limits the value setSELECT  StudentID, StudentName, StudentGender, MajorFROM  STUDENTWHERE  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 tiesSELECT  *FROM  STUDENTORDER  BY  Birthday DESC ,  StudentName  ASC;-- Built-in functionsSELECT  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 2SELECT  Major  AS 专业,  COUNT（StudentID） AS 学生人数FROM  StudentWHERE  StudentGender=’男’GROUP  BY  MajorHAVING  COUNT(*)>2;

Multi-table Queries

Subquery

SELECT  TeacherID, TeacherName, TeacherTitleFROM  TeacherWHERE  CollegeID  IN        (SELECT  CollegeID         FROM  College         WHERE  CollegeName='计算机学院');

Join Query

SELECT    B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称FROM    Teacher  AS  A，College  AS  BWHERE    A.CollegeID=B.CollegeIDORDER BY    B.CollegeName, A.TeacherID;-- JOIN ON inner joinSELECT    B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称FROM    TEACHER  AS  A  JOIN  COLLEGE  AS  BON    A.CollegeID=B.CollegeIDORDER BY    B.CollegeName, A.TeacherID;-- LEFT JOIN/RIGHT JOIN/FULL JOIN outer joinsSELECT    C.CourseName AS 课程名称, T.TeacherName AS 教师,COUNT (R.CoursePlanID)  AS 选课人数FROM    COURSE  AS  CJOIN    PLAN  AS  P    ON C.CourseID=P.CourseIDJOIN    TEACHER  AS  T    ON P.TeacherID=T.TeacherIDLEFT  JOIN    REGISTER  AS  R    ON  P.CoursePlanID=R.CoursePlanIDGROUP BY    C.CourseName, T.TeacherName;