交付：整套 schema 一次验完

👁️ 0 人浏览 💬 0 人评论 ❤️ 添加收藏

这条路线的交付题：把一份 schema 的四项验收一次做完——三张表都有主键、外键指得对、拼得回原样、约束能挡住错误数据。输出四样（用 / 隔开）。

enroll_flat（一张宽表，什么都塞在里面）
student  phone  course  teacher  room
阿岚     111    数学    王老师   A101
阿岚     111    英语    李老师   B202
小满     222    数学    王老师   A101
阿泰     333    英语    李老师   B202
小满     222    物理    张老师   C303
南风     444    数学    王老师   A101

拆开之后的三张
student(id, name, phone)          4 行
course (id, name, teacher, room)  3 行
enroll (student_id, course_id)    6 行

提交你的答案

请登录后提交答案。

去登录

← 第四步：拼回去验收没有索引时，数据库怎么找那一行 →

更多题目

代码编辑器

语言： Ctrl + Enter 运行

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE enroll_flat (student TEXT, phone TEXT, course TEXT, teacher TEXT, room TEXT);
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, phone TEXT NOT NULL);
CREATE TABLE course  (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,
                      teacher TEXT NOT NULL, room TEXT NOT NULL);
CREATE TABLE enroll  (student_id INTEGER, course_id INTEGER,
                      PRIMARY KEY (student_id, course_id));
""")
db.executemany("INSERT INTO enroll_flat VALUES (?,?,?,?,?)", [
    ("阿岚", "111", "数学", "王老师", "A101"),
    ("阿岚", "111", "英语", "李老师", "B202"),
    ("小满", "222", "数学", "王老师", "A101"),
    ("阿泰", "333", "英语", "李老师", "B202"),
    ("小满", "222", "物理", "张老师", "C303"),
    ("南风", "444", "数学", "王老师", "A101")])
db.executemany("INSERT INTO student VALUES (?,?,?)", [
    (1, "阿岚", "111"), (2, "小满", "222"), (3, "阿泰", "333"), (4, "南风", "444")])
db.executemany("INSERT INTO course VALUES (?,?,?,?)", [
    (1, "数学", "王老师", "A101"), (2, "英语", "李老师", "B202"),
    (3, "物理", "张老师", "C303")])
db.executemany("INSERT INTO enroll VALUES (?,?)", [
    (1, 1), (1, 2), (2, 1), (3, 2), (2, 3), (4, 1)])
import sqlite3

def norm(rows):
    return sorted("|".join(str(x) for x in r) for r in rows)

# TODO ①：数出 student / course / enroll 三张表里**有主键**的有几张
#       提示：PRAGMA table_info(表名) 的第 6 列（下标 5）是 pk 标记
has_pk = 0
for t in ("student", "course", "enroll"):
    cols = db.execute("PRAGMA table_info(" + t + ")").fetchall()
    pass

# TODO ②：三张表连回去，取五列
j = db.execute("SELECT  FROM enroll e "
               "JOIN student s ON s.id = e.student_id "
               "JOIN course c ON c.id = e.course_id").fetchall()
f = db.execute("SELECT student, phone, course, teacher, room "
               "FROM enroll_flat").fetchall()
same = "一致" if norm(j) == norm(f) else "不一致"

# TODO ③：往 enroll 里插一条已经存在的选课，看被哪一条约束拦下
try:
    db.execute("INSERT INTO enroll VALUES ")
    got = "没报错"
except sqlite3.IntegrityError as e:
    got = str(e).split(" constraint")[0].replace(" ", "")

n = db.execute("SELECT COUNT(*) FROM enroll").fetchone()[0]
print(str(has_pk) + "/" + same + "/" + got + "/" + str(n))

本次输入：

输出：

👩‍🏫

AI

💬 题目评论

全部评论