MySQL/语言/练习

练习 SELECT

表 `list`

ID	姓名	姓氏	现住	想住
1	Shantanu	Oak	Goregaon
2	Shantanu	Oak	Andheri
3	Shantanu	Oak		Dadar
4	Ram	Joshi		Goregaon
5	Shyam	Sharma		Andheri
6	Ram	Naik	Sion
7	Samir	Shah	Parle
8	Ram	Joshi	Dadar
9	Shyam	Sharma	Dadar

练习 I - 问题

谁住在“Goreagon”，谁想买房？

这个问题提法有问题，答案也不正确。'谁想买房？' 是指想买一套公寓，还是想买一套在 Goregaon 的公寓（顺便说一句，问题或表格中拼写错误）？答案是错误的，因为问题说的是“AND”，答案说的是 OR。如果问题是想问住在 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"; 如果问题是想问住在 Goregaon 并想买一套公寓的人，那么答案应该是 select name, surname from list where flathave="Goregaon" and flatwant<>"";

下面许多问题也需要修改，或者表格需要前言信息。

谁住在“Parle”，谁想买房？
“Shantanu Oak” 在哪里拥有公寓，他想去哪里买房？
到目前为止，记录了多少条记录？
有多少套公寓出售？
我们的客户姓名是什么？
我们有多少客户？
列出姓名以“S”开头的客户？
按字母顺序重新排列列表。

练习 I - 答案

select * from list where FlatHave = "Goregaon" or FlatWant = "Goregaon";
select * from list where FlatHave = "Parle" or FlatWant = "Parle";
select * from list where Name = "Shantanu" and Surname = "Oak";
select count(*) from list;
select count(FlatHave) from list where FlatHave is not null;
select distinct Name, Surname from list;
select count(distinct Name, surname) from list;
select * from list where Name like "S%";
select Surname, Name, FlatHave, FlatWant from list order by Name;

表 `grades`

ID	姓名	数学	物理	文学
1	John	68	37	54
2	Jim	96	89	92
3	Bill	65	12	57
4	Jeri	69	25	82

练习 II - 问题

所有数学成绩超过 90 分的学生的列表？
所有所有科目成绩超过 85 分的学生的列表？
声明结果：打印所有学生的成绩，包括结果列。
找出所有学生的总成绩。
每个科目的平均成绩是多少？
数学科目最低成绩是多少？
数学科目最高成绩是多少？
谁的数学成绩最高？

练习 II - 答案

注意：许多问题有多个正确答案。

  SELECT * FROM grades WHERE math > 90;
  SELECT name FROM grades WHERE math > 85 AND physics > 85 AND literature > 85;
  SELECT *, IF( (math <= 35 OR physics <= 35 OR literature <= 35), 'fail', 'pass') AS result FROM grades ORDER BY result DESC;
  SELECT name, math+physics+literature FROM grades;
  SELECT AVG(math), AVG(physics), AVG(literature) FROM grades;
  SELECT MIN(math) FROM grades;
  SELECT MAX(math) FROM grades;
  SELECT * FROM grades ORDER BY math DESC LIMIT 1   -- this is good if we have only one guy with top score.
  SELECT * FROM grades where math=max(math);   -- the max() function cannot be used after "where". Such usage results in "ERROR 1111 (HY000): Invalid use of group function"

这两个都可以

 SELECT name, maths FROM grades WHERE maths = (SELECT MAX(maths) from grades);
 SELECT name, maths FROM grades WHERE maths >= ALL (SELECT MAX(maths) from grades);

示例

查找重复项

 SELECT Vendor, ID, Count(1) as dupes
 FROM table_name
 GROUP BY Vendor, ID HAVING Count(1) >1

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

 SELECT id, COUNT( id ) AS cnt, 
 FROM myTable
 GROUP BY id HAVING cnt > 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);

注意，前两行在 a 和 b 列中包含重复项。它包含其他重复项；但是，保留其他重复项。

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