import pymysql

# 创建名为SCT的数据库
def create_database():
    cursor = conn.cursor()    # 创建游标
    cursor.execute("DROP DATABASE IF EXISTS SCT")
    # 创建数据库
    cursor.execute("CREATE DATABASE SCT CHARACTER SET  utf8 COLLATE utf8_general_ci")
    cursor.close()  # 关闭游标

# 创建数据库表
def create_table():
    cursor = conn.cursor()  # 创建游标
    cursor.execute("USE SCT")    # 指定数据库
    # 创建student数据表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS student 
    (
        sid CHAR(8) PRIMARY KEY,
        sname CHAR(10),
        ssex CHAR(2),
        sage INTEGER,
        sclass CHAR(6)
    )
    """)
    # 创建Course数据表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS course 
    (
        cid CHAR(4) PRIMARY KEY,
        cname CHAR(30),
        credit FLOAT(1),
        chours INTEGER,
        t CHAR(3)
    )
    """)
    # 创建SC数据表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS sc
    (
        sid   CHAR(8),
        cid   CHAR(4),
        score FLOAT(1),
        CONSTRAINT sc_course_cid_fk
            FOREIGN KEY  (cid) REFERENCES course (cid),
        CONSTRAINT sc_student_sid_fk
            FOREIGN KEY (sid) REFERENCES student (sid)
    )
    """)
    cursor.close()

sql = """
INSERT INTO student VALUES ('20200001', '张三','男',20,'01')
UPDATE student SET SClass = '02' WHERE S# = '20200001'
DELETE FROM student WHERE S# = '20200001'
SELECT * FROM student
"""

if __name__ == '__main__':
    conn = pymysql.connect(user="root", password="123456", host="127.0.0.1", port=3306)
    create_database()
    create_table()
    conn.close()  # 关闭链接