以下是针对每个问题的 SQL 查询语句:
创建 user 表并插入数据:
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(255),
sex VARCHAR(10),
age INT,
phone VARCHAR(20)
);
INSERT INTO user (id, name, sex, age, phone) VALUES
(1, '张三', '男', 25, '1234567890'),
(2, '李四', '女', 22, '9876543210'),
(3, '王五', '男', 28, '4567890123');
查询年龄在 20 到 30 范围内的用户:
SELECT * FROM user WHERE age BETWEEN 20 AND 30;
删除名字包含“张”的用户:
DELETE FROM user WHERE name LIKE '%张%';
计算所有用户的平均年龄:
SELECT AVG(age) AS average_age FROM user;
查询年龄在 20 到 30 范围内、名字包含“张”的用户,并按照年龄从大到小排序:
SELECT * FROM user WHERE age BETWEEN 20 AND 30 AND name LIKE '%张%' ORDER BY age DESC;
创建 team 表和 score 表:
CREATE TABLE team (
id INT PRIMARY KEY,
teamName VARCHAR(255)
);
CREATE TABLE score (
id INT PRIMARY KEY,
teamid INT,
userid INT,
score INT,
FOREIGN KEY (teamid) REFERENCES team(id),
FOREIGN KEY (userid) REFERENCES user(id)
);
查询 teamName 为“ECNU”中年龄小于 20 的用户:
SELECT u.* 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;
计算 teamName 为“ECNU”的总分:
SELECT t.teamName, SUM(s.score) AS total_score
FROM team t
JOIN score s ON t.id = s.teamid
WHERE t.teamName = 'ECNU'
GROUP BY t.teamName;