import sqlite3

# 創(chuàng)建數據庫連接

conn = sqlite3.connect('intern_management.db')

科研成果管理系統

cursor = conn.cursor()

# 創(chuàng)建用戶表

cursor.execute('''

CREATE TABLE IF NOT EXISTS users (

id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL,

department TEXT,

phone TEXT

);

''')

# 創(chuàng)建考勤表

cursor.execute('''

CREATE TABLE IF NOT EXISTS attendance (

id INTEGER PRIMARY KEY AUTOINCREMENT,

user_id INTEGER,

date DATE,

time TIME,

FOREIGN KEY(user_id) REFERENCES users(id)

);

''')

# 創(chuàng)建任務表

cursor.execute('''

CREATE TABLE IF NOT EXISTS tasks (

id INTEGER PRIMARY KEY AUTOINCREMENT,

description TEXT,

assignee_id INTEGER,

due_date DATE,

FOREIGN KEY(assignee_id) REFERENCES users(id)

);

''')

# 關閉連接

conn.commit()

conn.close()

]]>

SELECT u.name, t.description, t.due_date, (julianday(t.due_date) - julianday('now')) AS days_left

FROM users u

JOIN tasks t ON u.id = t.assignee_id

WHERE u.id = ?;

]]>