数据库小作业

郭高旭 ggx21@mails.tsinghua.edu.cn 2021010803

作业1

创建测试样例如下表

sql> select * from lab1.person LIMIT 0, 1001;
+------+-----+
| NAME | AGE |
+------+-----+
| A    |  10 |
| B    |  20 |
| C    |  30 |
| D    |  40 |
| E    |  50 |
| F    |  60 |
| G    |  70 |
| H    |  80 |
| I    |  90 |
| J    | 100 |
| K    | 110 |
| L    | 110 |
| M    | 110 |
| L    | 110 |
| M    | 110 |
| L    | 110 |
| M    | 110 |
+------+-----+

sql> -- 方法一,直接使用DISTINCT关键字
SELECT COUNT(DISTINCT NAME, AGE) FROM PERSON LIMIT 0, 1001;
+---------------------------+
| COUNT(DISTINCT NAME, AGE) |
+---------------------------+
|                        13 |
+---------------------------+

sql> -- 方法二,使用子查询和GROUP BY
SELECT COUNT(*)
FROM (
    SELECT NAME, AGE
    FROM PERSON
    GROUP BY NAME, AGE
) AS UniqueTuples LIMIT 0, 1001;
+----------+
| COUNT(*) |
+----------+
|       13 |
+----------+

sql> -- 方法三,使用GROUP BY和HAVING
SELECT COUNT(*) FROM (SELECT DISTINCT NAME, AGE FROM PERSON) AS UniqueTuples LIMIT 0, 1001;
+----------+
| COUNT(*) |
+----------+
|       13 |
+----------+

作业2

实现一trigger，使得在向department添加行时复制内容到student3表中，代码如下

DROP TRIGGER IF EXISTS copy_to_std3;
DELIMITER $$

CREATE TRIGGER copy_to_std3 
AFTER INSERT 
ON department
FOR EACH ROW
BEGIN
    DECLARE DL varchar(10);
    DECLARE DN varchar(12);

    SET DL = NEW.department_leader;
    SET DN = NEW.department_name;

    INSERT INTO student3
    VALUES ('名誉生', DL, DN);
END$$
DELIMITER ;

结果展示

sql> insert into department values('雅典大学','中央主楼','吴院士');
sql> SELECT * FROM student3 LIMIT 0, 1001;
+----------------+--------------+-----------------+
| student_number | student_name | department_name |
+----------------+--------------+-----------------+
| 861317         | 孙大帅          | 网络学院            |
| 名誉生            | 吴院士          | 雅典大学            |
+----------------+--------------+-----------------+