--- title: Database Basics — Functions & Constraints date: 2024-01-27 excerpt: Database study notes. This post covers SQL functions and constraints. tags: - DataBase - SQL cover: https://assets.vluv.space/cover/DataBase/Database_Image.webp updated: 2026-07-08 21:17:39 lang: en i18n: cn: /DataBase02 translation: 2 --- ## 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_](https://www.sqltutorial.org/sql-aggregate-functions/) 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: ```sql 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** ```sql -- randomly generate 6 digits SELECT SUBSTRING(ROUND(RAND(),6),3,6); +--------------------------------+ | SUBSTRING(ROUND(RAND(),6),3,6) | +--------------------------------+ | 578207 | +--------------------------------+ ``` ### Date Functions ```sql SELECT CURDATE(); -- YYYY-MM-DD SELECT CURTIME(); -- HH:MM:SS SELECT NOW(); -- YYYY-MM-DD HH:MM:SS YEAR(date); MONTH(date); DAY(date); SELECT YEAR("2023-1-2"); +------------------+ | YEAR("2023-1-2") | +------------------+ | 2023 | +------------------+ DATEADD(date,INTERVAL expr type); DATEDIFF(date1,date2); -- date1 - date2 SELECT 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** ```sql -- For each employee, compute the number of days since they joined, sorted in descending order SELECT Name,DATEDIFF(now(),entrydate) AS days FROM emp ORDER BY days DESC; ``` ### Control Flow Functions ```sql -- if value is true, return if_true_expr, else return if_false_expr IF(value,if_true_expr,if_false_expr) -- if expr1 IS NULL, return expr2, else return expr1 IFNULL(expr1,expr2) -- if condition1 is true, return result1; if condition2 is true, return result2; otherwise return result CASE WHEN THEN WHEN THEN ELSE END -- expr = value1, return result1, expr = value2, return result2, else return result CASE WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE result END ``` **Exercises** ```sql -- 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 addrTag FROM 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; ``` ## Constraints ### Overview **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** ```sql 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 () REFERENCES ()` **Example** ```sql -- 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 table CREATE 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 constraint ALTER TABLE Department ADD CONSTRAINT fk_depa_id FOREIGN KEY (depa_id) REFERENCES Company(Id); -- Drop a foreign key constraint ALTER 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 ` For example `ALTER TABLE Department ADD CONSTRAINT fk_depa_id FOREIGN KEY (depa_id) REFERENCES Company(Id) ON DELETE CASCADE ON UPDATE CASCADE;`