MySQL数据库

练习 SELECT

表 list 示例

问题与答案

1. 谁在 “Goregaon” 有房子并且想买房子？

   如果问题要求 “同时拥有和想买 Goregaon 的房子”：

   SELECT name, surname FROM list WHERE FlatHave="Goregaon" AND FlatWant="Goregaon";
   

   如果问题要求 “拥有 Goregaon 的房子或想买 Goregaon 的房子”：

   SELECT name, surname FROM list WHERE FlatHave="Goregaon" OR FlatWant="Goregaon";
   

2. 谁在 “Parle” 有房子并且想买房子？

   SELECT name, surname FROM list WHERE FlatHave="Parle" OR FlatWant="Parle";
   

3. “Shantanu Oak” 拥有房子的地点以及想买房子的地点？

   SELECT FlatHave, FlatWant FROM list WHERE Name="Shantanu" AND Surname="Oak";
   

4. 已记录的总条目数量：

   SELECT COUNT(*) FROM list;
   

5. 当前出售的房子数量：

   SELECT COUNT(FlatHave) FROM list WHERE FlatHave IS NOT NULL;
   

6. 客户的名字：

   SELECT DISTINCT Name, Surname FROM list;
   

7. 客户的总人数：

   SELECT COUNT(DISTINCT Name, Surname) FROM list;
   

8. 名字以 “S” 开头的客户：

   SELECT * FROM list WHERE Name LIKE "S%";
   

9. 按名字字母顺序排列客户列表：

   SELECT Surname, Name, FlatHave, FlatWant FROM list ORDER BY Name;
   

表 grades 示例

问题与答案

1. 列出所有数学成绩超过 90 的学生：

   SELECT * FROM grades WHERE Math > 90;
   

2. 列出所有科目成绩均超过 85 的学生：

   SELECT Name FROM grades WHERE Math > 85 AND Physics > 85 AND Literature > 85;
   

3. 声明结果（通过或失败）：

   SELECT *, IF((Math <= 35 OR Physics <= 35 OR Literature <= 35), 'fail', 'pass') AS result FROM grades ORDER BY result DESC;
   

4. 统计每个学生的总分：

   SELECT Name, Math + Physics + Literature AS TotalMarks FROM grades;
   

5. 计算每门课的平均分：

   SELECT AVG(Math) AS AvgMath, AVG(Physics) AS AvgPhysics, AVG(Literature) AS AvgLiterature FROM grades;
   

6. 数学最低分：

   SELECT MIN(Math) AS MinMath FROM grades;
   

7. 数学最高分：

   SELECT MAX(Math) AS MaxMath FROM grades;
   

8. 数学最高分的学生：

   SELECT Name, Math FROM grades WHERE Math = (SELECT MAX(Math) FROM grades);
   

查找重复项

示例 1：按 Vendor 和 ID 查找重复项：

SELECT Vendor, ID, COUNT(1) AS dupes
FROM table_name
GROUP BY Vendor, ID
HAVING COUNT(1) > 1;

示例 2：按文本内容查找重复项：

SELECT txt, COUNT(*)
FROM dupes
GROUP BY txt
HAVING COUNT(*) > 1;

删除重复项

假设以下表结构和数据：

CREATE TABLE IF NOT EXISTS dupTest (
  pkey INT(11) NOT NULL AUTO_INCREMENT,
  a INT, 
  b INT, 
  c INT, 
  timeEnter TIMESTAMP(14),
  PRIMARY KEY (pkey)
);

INSERT INTO dupTest (a, b, c) VALUES 
(1, 2, 3), 
(1, 2, 3), 
(1, 5, 4), 
(1, 6, 4);

通过添加唯一索引删除重复项：

ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX (a, b);