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);