Homework5
- 所有的题目结果中，给出SQL语句和执行结果。
In [1]:
Copied!
import pymysql
# 连接到MySQL服务器
connection = pymysql.connect(
host="localhost",
user="root",
password="test"
)
cursor = connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS test_database;")
cursor.execute("USE test_database;")
import pymysql
# 连接到MySQL服务器
connection = pymysql.connect(
host="localhost",
user="root",
password="test"
)
cursor = connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS test_database;")
cursor.execute("USE test_database;")
Out[1]:
0
In [2]:
Copied!
cursor.execute("DROP TABLE IF EXISTS score;")
cursor.execute("DROP TABLE IF EXISTS team;")
cursor.execute("DROP TABLE IF EXISTS user;")
cursor.execute("DROP TABLE IF EXISTS score;")
cursor.execute("DROP TABLE IF EXISTS team;")
cursor.execute("DROP TABLE IF EXISTS user;")
Out[2]:
0
在新数据库中新建一张 user 表,插入几条数据,属性包含:唯一标识(id),姓名(name)性别(sex).年龄(age).联系方式(phone)，数据如下：
('John Doe', 'Male', 25, '123-456-7890')
('Jane Smith', 'Female', 31, '987-654-3210')
('Bob Johnson', 'Male', 22, '555-123-4567')
In [3]:
Copied!
# 创建 user 表
create_table_query = """
CREATE TABLE IF NOT EXISTS user (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(66) NOT NULL,
sex ENUM('Male', 'Female') NOT NULL,
age INT NOT NULL,
phone VARCHAR(50) NOT NULL
);
"""
cursor.execute(create_table_query)
# 插入数据
insert_data_query = """
INSERT INTO user VALUES
(NULL, 'John Doe', 'Male', 25, '123-456-7890'),
(NULL, 'Jane Smith', 'Female', 31, '987-654-3210'),
(NULL, 'Bob Johnson', 'Male', 22, '555-123-4567');
"""
cursor.execute(insert_data_query)
select_query = "SELECT * FROM user"
cursor.execute(select_query)
results = cursor.fetchall()
for row in results:
print(row)
# 创建 user 表
create_table_query = """
CREATE TABLE IF NOT EXISTS user (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(66) NOT NULL,
sex ENUM('Male', 'Female') NOT NULL,
age INT NOT NULL,
phone VARCHAR(50) NOT NULL
);
"""
cursor.execute(create_table_query)
# 插入数据
insert_data_query = """
INSERT INTO user VALUES
(NULL, 'John Doe', 'Male', 25, '123-456-7890'),
(NULL, 'Jane Smith', 'Female', 31, '987-654-3210'),
(NULL, 'Bob Johnson', 'Male', 22, '555-123-4567');
"""
cursor.execute(insert_data_query)
select_query = "SELECT * FROM user"
cursor.execute(select_query)
results = cursor.fetchall()
for row in results:
print(row)
(1, 'John Doe', 'Male', 25, '123-456-7890') (2, 'Jane Smith', 'Female', 31, '987-654-3210') (3, 'Bob Johnson', 'Male', 22, '555-123-4567')
- 写出 SQL语句,查询 user 表中所有年龄在 20-30 范围内的用户
In [4]:
Copied!
query = "SELECT * FROM user WHERE age >= 20 and age <= 30;"
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
query = "SELECT * FROM user WHERE age >= 20 and age <= 30;"
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
(1, 'John Doe', 'Male', 25, '123-456-7890') (3, 'Bob Johnson', 'Male', 22, '555-123-4567')
- 写出SQL语句，向user表中添加自己的个人信息，并添加几条和你姓名同姓的虚拟信息。
In [6]:
Copied!
query = """
INSERT INTO user VALUES
(NULL, 'Xiahui Guo', 'Male', 20, '111-222-3333'),
(NULL, 'One Guo', 'Female', 18, '985-211-6666'),
(NULL, 'Two Guo', 'Male', 22, '222-888-8848'),
(NULL, 'Three Guo', 'Male', 29, '111-1112-0000'),
(NULL, 'Four Guo', 'Female', 100, '111-1111-1111');
"""
cursor.execute(query)
query = "SELECT * FROM user"
cursor.execute(query)
results = cursor.fetchall()
for row in results:
print(row)
query = """
INSERT INTO user VALUES
(NULL, 'Xiahui Guo', 'Male', 20, '111-222-3333'),
(NULL, 'One Guo', 'Female', 18, '985-211-6666'),
(NULL, 'Two Guo', 'Male', 22, '222-888-8848'),
(NULL, 'Three Guo', 'Male', 29, '111-1112-0000'),
(NULL, 'Four Guo', 'Female', 100, '111-1111-1111');
"""
cursor.execute(query)
query = "SELECT * FROM user"
cursor.execute(query)
results = cursor.fetchall()
for row in results:
print(row)
(1, 'John Doe', 'Male', 25, '123-456-7890') (2, 'Jane Smith', 'Female', 31, '987-654-3210') (3, 'Bob Johnson', 'Male', 22, '555-123-4567') (4, 'Xiahui Guo', 'Male', 20, '111-222-3333') (5, 'One Guo', 'Female', 18, '985-211-6666') (6, 'Two Guo', 'Male', 22, '222-888-8848') (7, 'Three Guo', 'Male', 29, '111-1112-0000') (8, 'Four Guo', 'Female', 100, '111-1111-1111')
- 写出 SQL语句,查询 user 表中年龄在 20-30 范围内,名字包含“你的姓氏”的用户,并按照年龄从大到小排序输出
In [7]:
Copied!
query = """
SELECT * FROM user
WHERE age >= 20 AND age <= 30
AND username LIKE '%Guo%'
ORDER BY age DESC;
"""
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
query = """
SELECT * FROM user
WHERE age >= 20 AND age <= 30
AND username LIKE '%Guo%'
ORDER BY age DESC;
"""
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
(7, 'Three Guo', 'Male', 29, '111-1112-0000') (6, 'Two Guo', 'Male', 22, '222-888-8848') (4, 'Xiahui Guo', 'Male', 20, '111-222-3333')
- 写出 SQL 语句,计算 user 表中所有用户的平均年龄
In [8]:
Copied!
query = "SELECT AVG(age) FROM user;"
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
query = "SELECT AVG(age) FROM user;"
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
(Decimal('33.3750'),)
- 新建两张表team 表(id,teamName)和score 表(id,teamid,userid,score)。其中score 表中的 teamid 为指向 team表id 的外键，userid 为指向 user表id的外键
In [9]:
Copied!
query = """
CREATE TABLE IF NOT EXISTS team(
id INT AUTO_INCREMENT PRIMARY KEY,
teamName VARCHAR(50) NOT NULL
);
"""
cursor.execute(query)
query = """
CREATE TABLE IF NOT EXISTS score(
id INT AUTO_INCREMENT PRIMARY KEY,
teamid INT NOT NULL,
userid INT NOT NULL,
score INT NOT NULL,
FOREIGN KEY (userid) REFERENCES user(id),
FOREIGN KEY (teamid) REFERENCES team(id)
);
"""
cursor.execute(query)
query = """
CREATE TABLE IF NOT EXISTS team(
id INT AUTO_INCREMENT PRIMARY KEY,
teamName VARCHAR(50) NOT NULL
);
"""
cursor.execute(query)
query = """
CREATE TABLE IF NOT EXISTS score(
id INT AUTO_INCREMENT PRIMARY KEY,
teamid INT NOT NULL,
userid INT NOT NULL,
score INT NOT NULL,
FOREIGN KEY (userid) REFERENCES user(id),
FOREIGN KEY (teamid) REFERENCES team(id)
);
"""
cursor.execute(query)
Out[9]:
0
- 在team表中和score表中插入合适的记录，写出 SQL语句,查询 teamName 为“ECNU”的队伍中，年龄小于 20 的用户们，结果不得为空。
In [10]:
Copied!
query = """
INSERT INTO team VALUES
(NULL, 'Thu'),
(NULL, 'Ecnu'),
(NULL, 'Fudan');
"""
cursor.execute(query)
query = """
INSERT INTO score VALUES
(NULL, 1, 1, 70),
(NULL, 1, 2, 80),
(NULL, 1, 3, 60),
(NULL, 2, 4, 100),
(NULL, 2, 5, 90),
(NULL, 2, 6, 90),
(NULL, 3, 7, 80),
(NULL, 3, 8, 50);
"""
cursor.execute(query)
query = """
INSERT INTO team VALUES
(NULL, 'Thu'),
(NULL, 'Ecnu'),
(NULL, 'Fudan');
"""
cursor.execute(query)
query = """
INSERT INTO score VALUES
(NULL, 1, 1, 70),
(NULL, 1, 2, 80),
(NULL, 1, 3, 60),
(NULL, 2, 4, 100),
(NULL, 2, 5, 90),
(NULL, 2, 6, 90),
(NULL, 3, 7, 80),
(NULL, 3, 8, 50);
"""
cursor.execute(query)
Out[10]:
8
In [11]:
Copied!
query = """
SELECT username
FROM user u
JOIN score s ON u.id = s.userid
JOIN team t ON s.teamid = t.id
WHERE t.teamName = 'ECNU' AND u.age < 20;
"""
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
query = """
SELECT username
FROM user u
JOIN score s ON u.id = s.userid
JOIN team t ON s.teamid = t.id
WHERE t.teamName = 'ECNU' AND u.age < 20;
"""
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
('One Guo',)
- 写出 SQL 语句,计算 teamName为“ECNU”的总分(假设 score 存在 null值,nul值默认为 0 加入计算)。
In [12]:
Copied!
query = """
SELECT t.teamName, SUM(COALESCE(s.score, 0)) AS totalScore
FROM team t
JOIN score s ON t.id = s.teamid
WHERE t.teamName = 'ECNU';
"""
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
query = """
SELECT t.teamName, SUM(COALESCE(s.score, 0)) AS totalScore
FROM team t
JOIN score s ON t.id = s.teamid
WHERE t.teamName = 'ECNU';
"""
cursor.execute(query)
results = cursor.fetchall()
# 显示查询结果
for row in results:
print(row)
('Ecnu', Decimal('280'))
- 写出SQL语句，删除user表中个人信息的记录。
In [13]:
Copied!
cursor.execute("DROP TABLE IF EXISTS score;")
cursor.execute("DROP TABLE IF EXISTS team;")
cursor.execute("DROP TABLE IF EXISTS user;")
cursor.execute("DROP TABLE IF EXISTS score;")
cursor.execute("DROP TABLE IF EXISTS team;")
cursor.execute("DROP TABLE IF EXISTS user;")
Out[13]:
0
In [14]:
Copied!
# 关闭连接
cursor.close()
connection.close()
# 关闭连接
cursor.close()
connection.close()