数据库作业3

数据库系统作业3

1. 编写一个函数，函数名为get_student_phone，无接收参数，返回一个随机的手机号，长度11位，手机号以’159’或 '137’开头，要求任意满足该要求的手机号能等概率生成。

CREATE OR REPLACE FUNCTION get_student_phone() RETURNS varchar AS $$
DECLARE 
	num1 integer;
	num2 integer;
	phone_num varchar;
BEGIN
	num1 := floor(random()*(2-1+1))+1;
	IF num1 = 1 THEN
		phone_num := CONCAT(phone_num, '159');
	ELSEIF num1 = 2 THEN
		phone_num := CONCAT(phone_num, '137');
	END IF;
	
	FOR i IN 1..8 LOOP
		num2 := floor(random()*(9+1));
		phone_num := CONCAT(phone_num, num2);
	END LOOP;
	RETURN phone_num;
END;
$$ LANGUAGE plpgsql;

语义解释：

  因为手机号必须以‘159’或‘137’开头，并要求等概率生成手机号，所以先使用random()函数搭配floor()函数随机等概率生成1或2，当随机值为1时生成的手机号码以‘159’开头，否则以‘137’开头，确定好开头之后用concat()函数将开头与空的字符串连接起来；剩下的八位手机号则使用跟上述产生随机数的方法，生成0-9之间的数字，再使用concat()函数将后八位数字与开头连接起来即可。

2. 编写一个函数，函数名为get_student_date，无接收参数，返回一个随机的日期，日期格式为’YYYY-MM-DD’。要求返回的日期区间为[2020-01-01, 2021-12-31]，其中，要求生成2020年份概率为60%，生成2021年份概率为40%，此外，月和日则是等概率返回。

CREATE OR REPLACE FUNCTION get_student_date() RETURNS date AS $$
DECLARE
	date1 date := '2020-01-01';
	date2 date := '2021-12-31';
	rand_date date;
	days integer;
	halfdays integer;
	inter_days integer;
	num integer;
BEGIN
	days := date2 - date1;
	halfdays := days / 2;
	num = random();
	IF num < 0.6 THEN
		inter_days := floor(random()*(halfdays + 1));
	ELSE
		inter_days := floor(random()*(days - halfdays + 1)) + halfdays;
	END IF;

	rand_date := date1 + inter_days;
	RETURN rand_date;
END;
$$ LANGUAGE plpgsql;

语义解释：

  首先说明如何是生成2020年份概率为60%，2021年份的概率为40%：使用随机数生成函数，已知所生成的数字在0到1之间，所以如果数字小于0.6则生成2020年份的日期，否则生成2021年份的日期。

  接下来是日期的生成，要生成处于区间内的日期可以在开始日期的基础上加上一定的天数，但最后所得到的日期不能超出区间最大日期。要生成2020年份的日期，需满足加的天数是区间间隔天数的一半，要生成2021年份的日期，需满足所加的天数应大于区间间隔天数的一般并小于等于区间间隔天数，在此基础上使用问题一的随机数产生函数即可。

3. 编写一个函数，函数名为create_student_table，无接收参数。在该函数中，新建一个数据表 student，该数据表拥有3个字段，分别是 student_id, phone_num, enrollment_date，其中 student_id 为自增的序列，从1开始自增，且为主键；然后，往该数据表新增 15条记录，这 15条记录中，phone_num和 enrollment_date分别使用上述自己编写的第一个和第二个函数生成。最后返回该表。该函数理应可以连续调用多次，每次生成并返回的表都不一样。

CREATE OR REPLACE FUNCTION create_student_table() 
	RETURNS TABLE(
	student_id integer,
	phone_num varchar,
	enrollment_date date
	)
AS $$
BEGIN 
	IF(to_regclass('student') is not null) THEN
		EXECUTE 'truncate table student';
	ELSE
		EXECUTE 'create table student(student_id integer, phone_num varchar, enrollment_date date,primary key(student_id))';
	END IF;

	FOR i IN 1..15 LOOP
		INSERT INTO student values(i, get_student_phone(), get_student_date());
	END LOOP;
	RETURN query SELECT * FROM student;
END;
$$ LANGUAGE plpgsql; 

语义解释：

  因为函数所返回的参数为一个表，所以需要先声明返回的表的属性。又因为要求函数可以多次调用且每次生成并返回的表都不一样，所以我们需要检查该表是否已经存在，若存在我们需要先使用truncate table命令删除表中的数据，若不存在我们需要使用create table命令来创建一个新的表，然后因为表中要有15条记录，所以我们使用循环，循环15次，每次循环使用INSERT INTO命令向表中插入数据，需要使用我们已创建好的函数只需直接调用即可。

  函数返回的是表的数据，所以我们需要使用查询语句得到表的数据，然后将查询结果返回。

4. 查询：使用 student表，找出所有enrollment_date在2020年7月1日（包括这一天）之后的学生，并输出其phone_num。

SELECT s.phone_num
FROM student s
WHERE s.enrollment_date >= timestamp '2020-07-01';

语义解释：

  查询条件需要对日期进行筛选，我们只需要把筛选日期声明为一个timestamp类型数据，然后直接使用比较操作符比较即可。