You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
64 lines
1.6 KiB
64 lines
1.6 KiB
5 months ago
|
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() # 关闭链接
|