Jan 26·7 min· PV

Database Basics — Functions & Constraints

Database study notes. This post covers SQL functions and constraints.

Functions

AggregateFunction

Overview

An SQL aggregate function calculates on a set of values and returns a single value
Because an aggregate function operates on a set of values, it is often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause divides the result set into groups of values and the aggregate function returns a single value for each group.
SQL Tutorial

The following are the commonly used SQL aggregate functions:

AVG() – returns the average of a set
COUNT() – returns the number of items in a set
COUNT(*) – returns the number of items in a set (including NULL values)
MAX() – returns the maximum value in a set
MIN() – returns the minimum value in a set
SUM() – returns the sum of all or distinct values in a set

Except for the COUNT() function, SQL aggregate functions ignore null.

You can use aggregate functions as expressions only in the following:

The select list of a SELECT statement, either a subquery or an outer query.
A HAVING clause

StringFunction

Functions that allow you to manipulate string data more effectively.

CONCAT(s1,s2,...,sn) – return the result of concatenation two strings together.
LOWER(str),UPPER(str) – converts a string to lower case or upper case.
LPAD(str,n,padstr),RPAD(str,n,padstr) – left/right pads a string with another string to a certain length.
TRIM(str),LTRIM(str),RTRIM(str) – removes all spaces from a string or from the left or right side of a string.
REPLACE(str,source,replace) – replaces all occurrences of a substring within a string with another substring.
SUBSTRING(str,pos,len) – returns a substring from a string starting at a specified position with a specified length.
Reference:

SELECT CONCAT('SQL',' is',' fun!');+-------------------------+| CONCAT("HELLO","WORLD") |+-------------------------+| HELLOWORLD              |+-------------------------+SELECT LOWER("Hello,World");+----------------------+| LOWER("Hello,World") |+----------------------+| hello,world          |+----------------------+SELECT RPAD("1",5,"Z");+-----------------+| RPAD("1",5,"Z") |+-----------------+| 1ZZZZ           |+-----------------+SELECT RTRIM("  HELLO   ");+---------------------+| RTRIM("  HELLO   ") |+---------------------+|   HELLO             |+---------------------+SELECT REPLACE("Hello,World","World","SQL");+--------------------------------------+| REPLACE("Hello,World","World","SQL") |+--------------------------------------+| Hello,SQL                            |+--------------------------------------+SELECT SUBSTRING("Hello,World",1,5);+------------------------------+| SUBSTRING("Hello,World",1,5) |+------------------------------+| Hello                        |+------------------------------+

Math Functions

SQL has many mathematical functions that allow you to perform business and engineering calculations.

ABS(x) – returns the absolute value of x.
CEIL(x) – returns the smallest integer that is greater than or equal to x.
FLOOR(x) – returns the largest integer that is less than or equal to x.
RAND() – returns a random floating-point value.
MOD(x,y) – returns the remainder(modulo) of x divided by y, MOD(10,7) returns 3. e.g.
ROUND(x,d) – returns a number rounded to d decimal places. ROUND(2.6666,3) returns 2.667 e.g.

Exercise

-- randomly generate 6 digitsSELECT SUBSTRING(ROUND(RAND(),6),3,6);+--------------------------------+| SUBSTRING(ROUND(RAND(),6),3,6) |+--------------------------------+| 578207                         |+--------------------------------+

Date Functions

SELECT CURDATE(); -- YYYY-MM-DDSELECT CURTIME(); -- HH:MM:SSSELECT NOW(); -- YYYY-MM-DD HH:MM:SSYEAR(date); MONTH(date); DAY(date);SELECT YEAR("2023-1-2");+------------------+| YEAR("2023-1-2") |+------------------+|             2023 |+------------------+DATEADD(date,INTERVAL expr type);DATEDIFF(date1,date2); -- date1 - date2SELECT DATE_ADD(now(),INTERVAL 70 DAY);+---------------------------------+| DATE_ADD(now(),INTERVAL 70 DAY) |+---------------------------------+| 2024-04-06 20:09:20             |+---------------------------------+SELECT DATEDIFF(now(),"2023-1-20");+-----------------------------+| DATEDIFF(now(),"2023-1-20") |+-----------------------------+|                         372 |+-----------------------------+

Exercises

-- For each employee, compute the number of days since they joined, sorted in descending orderSELECT Name,DATEDIFF(now(),entrydate) AS days FROM emp ORDER BY days DESC;

Control Flow Functions

--  if value is true, return if_true_expr, else return if_false_exprIF(value,if_true_expr,if_false_expr)-- if expr1 IS NULL, return expr2, else return expr1IFNULL(expr1,expr2)-- if condition1 is true, return result1; if condition2 is true, return result2; otherwise return resultCASE WHEN <condition1> THEN <result1> WHEN <condition2> THEN <result2> ELSE <result> END-- expr = value1, return result1, expr = value2, return result2, else return resultCASE <expr> WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE result END

Exercises

-- Query name and addr from the emp table; if addr is Beijing/Shanghai, show "first-tier city", otherwise "second-tier city"SELECT name, (CASE addr WHEN 'Beijing' THEN '一线城市'  WHEN "Shanghai" THEN "一线城市" ELSE '二线城市' END) AS addrTagFROM emp;-- Query the score table; if a score is 60 or above, show "pass", otherwise "fail"SELECT name. (CASE WHEN score >= 60 THEN '及格' ELSE '不及格' END AS) math, (CASE WHEN english >= 60 THEN '及格' ELSE '不及格' END AS) english,FROM score;

Concept: a constraint is a rule applied to a column in a table, used to restrict the data stored in the table (its type, format, range, and so on).
Purpose: to guarantee that the data is correct, valid, and complete.
Categories:

Constraint	Description	Keyword
Not-null constraint	The column’s data cannot be null	NOT NULL
Unique constraint	Guarantees that all values in the column are unique, with no duplicates	UNIQUE
Primary key constraint	The primary key is the unique identifier of a row; it must be non-null and unique	PRIMARY KEY
Default constraint	If no value is given for the column when saving data, the default value is used	DEFAULT
Check constraint (since 8.0.1)	Guarantees that the column value satisfies a given condition	CHECK
Foreign key constraint	Links data between two tables, guaranteeing consistency and integrity	FOREIGN KEY

Example

CREATE TABLE USER(  id INT PRIMARY KEY AUTO_INCREMENT,  name VARCHAR(10) NOT NULL UNIQUE,  age TINYINT UNSIGNED CHECK(age>0 AND age<120),  gender ENUM('男','女','保密') DEFAULT '保密');

Foreign Key Constraint

Concept: a foreign key is a column in a table that points to the primary key of another table. It establishes a link between the two tables and guarantees consistency and integrity of the data.

Syntax: FOREIGN KEY (<foreignKey>) REFERENCES <tableName>(<primaryKey>)

Example

-- Two tables are created. The Id column of the Company table (parent table) is the primary key; the depa_id column of the Department table (child table) is a foreign key pointing to the Id column of the Company tableCREATE TABLE Company (    Id int NOT NULL,    name varchar(20) NOT NULL,    PRIMARY KEY (Id));CREATE TABLE Department (    id int NOT NULL,    name varchar(20) NOT NULL,    salary decimal(10,2),    depa_id int,    PRIMARY KEY (id),    FOREIGN KEY (depa_id) REFERENCES Company(Id));-- Add a foreign key constraintALTER TABLE Department ADD CONSTRAINT fk_depa_id FOREIGN KEY (depa_id) REFERENCES Company(Id);-- Drop a foreign key constraintALTER TABLE Department DROP FOREIGN KEY fk_depa_id;

Delete/Update Behavior

Behavior	Description
NO ACTION	When deleting/updating a record in the parent table, first check whether the record has a matching foreign key; if so, the delete/update is rejected (same as RESTRICT)
RESTRICT	When deleting/updating a record in the parent table, first check whether the record has a matching foreign key; if so, the delete/update is rejected (same as NO ACTION)
CASCADE	When deleting/updating a record in the parent table, first check whether the record has a matching foreign key; if so, the matching rows in the child table are also deleted/updated
SET NULL	When deleting/updating a record in the parent table, first check whether the record has a matching foreign key; if so, set the foreign key value in the child table to null (the foreign key must allow null)
SET DEFAULT	When the parent table changes, the child table sets the foreign key to a default value (not supported by InnoDB)

Syntax for setting the delete/update behavior: ON DELETE/UPDATE <action>
For example ALTER TABLE Department ADD CONSTRAINT fk_depa_id FOREIGN KEY (depa_id) REFERENCES Company(Id) ON DELETE CASCADE ON UPDATE CASCADE;