开发笔记:hackerrank刷题

1.问题：Query a list of CITY names from STATION with even ID numbers only. You may print the results in any order, but must exclude duplicates from your answer.

答案：SELECT DISTINCT city FROM station WHERE MOD(id,2)=0;

解析：要输出偶数id的城市名称且不能出现重复，distinct是去重，mod求余函数

2.Let N be the number of CITY entries in STATION, and let N` be the number of distinct CITY names in STATION; query the value of  N-N` from STATION. In other words, find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

答案：SELECT COUNT(city)-COUNT(DISTINCT city) FROM station;

解析：通过统计函数进行数据统计并进行运算。

3.Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

答案：

SELECT CITY, LENGTH(CITY)
FROM STATION ORDER BY LENGTH(CITY),CITY LIMIT 1;
SELECT CITY, LENGTH(CITY)
FROM STATION ORDER BY LENGTH(CITY) DESC,CITY LIMIT 1;

解析：首先查询城市名字，城市长度通过长度排序，升序排序找到最短名字，使用limit来返回第一行数据；

4.Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

答案：

SELECT DISTINCT CITY
FROM STATION
WHERE LOWER(SUBSTR(CITY,1,1)) in(‘a‘,‘e‘,‘i‘,‘o‘,‘u‘) ;

解析：因为是范围选择所以选择in，不能重复用distinct，截取城市名字的第一个字母并转成小写。

5.Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

答案：

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP ‘[aeiou]$‘;

解析：regexp是mysql中的正则表达式，表示匹配到aeiou就提出来，其中有几个定位符：“.”代表任意字符；在中括号外：“^”代表文本开始；“$”文本结束

查询首字母及最后一个字母为（aeiou）时将其变成^[aeiou].*[aeiou]$

查询除了这些字母外，其他作为首字母的城市名字变成^[^aeiou]：中括号内的^表示除了这些之外

6.Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

答案：select distinct city from station where city not regexp ‘[aeiou]$‘;

分析：用not将最后为元音的都排除掉。