sql查询相同数据 序号递增_运用SQL对数据进行简单查询

一、基本的查询语句

1.1 从表中查询所有的行和列

SELECT * FROM [table name]

注意：一般而言，除非确实需要用到表中的每一列，否则最好别使用*通配符。虽然使用通配符可以让自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

此外，使用*通配符无法设定列的显示顺序了。这时就会按照CREATE TABLE语句的定义对列进行排序。

1.2 从表中查询特定的列

SELECT [column name1], [column name2], .... [column name N] FROM [table name]

注意：查询结果中列的顺序和 SELECT 子句中的顺序相同。

1.3 列重命名

SELECT [old column name] AS [new column name] FROM [table name]

设定汉语别名时需要使用双引号(")括起来。

注意：在where子句中引用重命名的列名会报错

举例：

原因：where子句是在select之前处理的，也就是说在执行where语句时，student_id这一列并不存在，因此系统报错。

解决方案：将查询作为内联视图

之前提到了where子句在select子句之前执行，因此在where子句中引用重命名的列会报错，但是将查询作为内联视图可解决这个问题，因为from子句在where子句之前执行。

1.4 查询常数

SELECT 'text' AS [column name],
       number AS [column name],
       'date' AS [column name]
FROM [table name]

1.5 查询不重复的信息/删除重复行

SELECT DISTINCT [column name] FROM [table name]

注意：

1. 如果将 DISTINCT 放在多个列名前，则删除的的是所有列信息均相同的行。
2. 在使用 DISTINCT 时，NULL 也被视为一类数据。NULL 存在于多 行中时，也会被合并为一条 NULL 数据。
3. DISTINCT 关键字只能用在第一个列名之前。 因此 SELECT id, DISTINCT c_name FROM classes 会报错。

1.6 连接列值

SELECT CONCAT([column name 1], [column name2], .... [column name N]) AS [new column name] FROM [table name]

提示：在使用CONCAT连接列的同时，可以结合RTRIM, LTRIM和TRIM函数去掉空格。

1.6 限制返回行数

SELECT ... FROM ... LIMIT N

1.7 跳过某些行

SELECT ... FROM ... LIMIT N1 OFFSET N2

LIMIT 指定返回的行数，LIMIT 带的 OFFSET 指定从哪儿开始。

MySQL支持简化版的 LIMIT 4 OFFSET 3 语句，即 LIMIT 3,4。 使用这个语法，逗号之前的值对应 OFFSET，逗号之后的值对应 LIMIT。

1.8 随机返回N条记录

SELECT ... FROM ... ORDER BY RAND() LIMIT N

注意：在order by子句中指定数字常量时，是要求根据select列表中相应位置列来排序，在order by子句中使用函数时，则按函数在每一行计算的结果排序。

1.9 在SELECT语句中使用条件逻辑

对成绩表中的成绩一列执行IF-ELSE操作，如果成绩在90分及以上，则返回"excellent"，如果成绩在70分及以下，则返回"failed"，如果成绩在两者之间，则返回"ok"。

更多 CASE WHEN 表达式用法请参考如下文章：

shanshant：CASE 表达式​zhuanlan.zhihu.com

二、指定查询条件

2.1 查询满足特定条件的行

SELECT 语句通过 WHERE 子句来指定查询数据的条件。在 WHERE 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于 这个数字”等条件。执行含有这些条件的 SELECT 语句，就可以查询出只符合该条件的记录了。

SELECT [column name1], ..., [column name N] FROM [table name] WHERE ...

SQL 中子句的书写顺序是固定的，不能随意更改。WHERE 子句必须在 FROM 子句之后，书写顺序发生改变的话会造成执行错误。

2.2 查询满足多个条件的行

运算符分为算术运算符、比较运算符和逻辑运算符
算术运算符：+，-，*，/
比较运算符：=，<>，>，>=，<，<=
逻辑运算符：and，or，not，between...and，in

其中，SQL先处理AND操作符，再处理OR操作符，但是圆括号优先级高于AND和OR。因此，可以使用运算符和圆括号的组合来查找满足多个条件的数据。

SELECT [column name1], ..., [column name N] FROM [table name]
WHERE [condition1] AND/OR [condition2]

IN操作符与OR有相同的功能，其优点如下：

(1) 在有很多合法选项时，IN 操作符的语法更清楚，更直观。

(2)在与其他 AND 和 OR 操作符组合使用 IN 时，求值顺序更容易管理。

(3) IN 操作符一般比一组 OR 操作符执行得更快。

(4) IN操作符可以包含其他 SELECT 语句，能够更动态地建立 WHERE 子句。

NOT操作符总是与其他操作符一起使用，通常在WHERE子句中用来否定其后条件，例如：

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

上面的例子中查找了DLL01之外的所有供应商制造的产品。

2.3 查找空值

NULL不能用等于或不等于跟任何值比较， 包括它自身。所以，不能使用=或<>来测试一列是否为NULL。为了确定一行是否有空值，必须使用IS NULL，也可以使用IS NOT NULL来查找给定列的值不为空的行。

SELECT [column name1], ... [column name N] FROM [table name] 
WHERE [column name] IS NULL

所有包含 NULL 的计算，结果都是 NULL。即使用 NULL 除以 0 时这一原则也适用。

2.4 比较字符串大小

在对字符串类型的数据进行大小比较时，使用的是和数字比较不同的规则。典型的规则就是按照字典顺序进行比较，也就是像姓名那样，按照条目在字典中出现的顺序来进行排序。该规则最重要的一点就是，以相同字符开头的单词比不同字符开头的单词更相近。 Chars 表 chr 列中的数据按照字典顺序进行排序的结果为： 1，10，11，2，222，3 。

2.5 字符串模糊查询(like, %，_，[])

2.5.1 通配符 %

最常使用的通配符是百分号(%)。在搜索串中，%表示任何字符出现任意次数，代表搜索模式中给定位置的 0 个、1 个或多个字符。 但是，通配符%不可以匹配NULL。
例如，查找名字以k开头的学生的信息：

2.5.2 通配符 _

下划线的用途与%一样，但它只能匹配单个字符，而不是多个字符。

2.5.3 通配符 []

方括号([])通配符用来指定一个字符集，它必须匹配指定位置(通配符的位置)的一个字符。

例如，找出所有名字以J或M开头的人：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

此语句的 WHERE 子句中的模式为'[JM]%'。这一搜索模式使用了两个不同的通配符。[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。 因此，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。

此通配符可以用前缀字符^(脱字号)来否定。例如，下面的查询匹配以J和M之外的任意字符起头的任意联系人名(与前一个例子相反):

SELECT cust_contact FROM Customers 
WHERE cust_contact LIKE '[^JM]%' 
ORDER BY cust_contact;

当然，也可以使用NOT操作符得出类似的结果。^的唯一优点是在使用多个 WHERE 子句时可以简化语法。

2.5.4 使用通配符的技巧

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

三、练习

来源：SQLZOO

数据表：世界国家信息表

列名含义：

name：国家名称

continent：该国家属于哪个州

area：国土面积

population：人口数

gdp：国内生产总值

【SELECT basics 练习题】

地址：https://sqlzoo.net/wiki/SELECT_basics

练习一：显示德国的人口数

Show the population of Germany.

SELECT population FROM world
WHERE name = 'Germany';

练习二：显示瑞典、挪威和丹麦各国的人口数

Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.

SELECT name, population FROM world
WHERE name IN ('Sweden', 'Norway', 'Denmark');

练习三：显示国土面积范围在200000平方公里至250000平方公里之间的国家及其国土面积

Which countries are not too small and not too big? show the country and the area for countries with an area between 200,000 and 250,000.

SELECT name, area FROM world
WHERE area BETWEEN 200000 AND 250000;

【SELECT from world 练习题】

练习一：显示列表中全部国家的名称、所属州、以及人口数

Show the name, continent and population of all countries.

SELECT name, continent, population FROM world;

练习二：显示人口数至少为2亿的国家

Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

SELECT name FROM world
WHERE population >= 200000000;

练习三：显示人口数至少为2亿的国家名称及其人均GDP

Give the name and the per capita GDP for those countries with a population of at least 200 million.

SELECT name, gdp/population AS 'per capita GDP' FROM world
WHERE population >= 200000000;

练习四：显示南美洲地区各个国家名称及其人口数(以百万为单位)

Show the name and population in millions for the countries of the continent 'South America'. Divide the population by 1000000 to get population in millions.

SELECT name, population/1000000 AS 'population' FROM world
WHERE continent = 'South America';

练习五：显示德意法各国人口数

Show the name and population of France, Germany, Italy.

SELECT name, population FROM world
WHERE name IN ('France', 'Germany', 'Italy');

练习六：显示名称中包含"United"的国家

Show the countries which have a name that includes the word 'United'.

SELECT name FROM world
WHERE name LIKE '%United%';

练习七：显示人口数大于2亿5000万或国土面积大于3百万平方公里的国家名称、人口数以及国土面积

Two ways to be big: A country is big if it has an area of more than 3 million sq km or it has a population of more than 250 million.

Show the countries that are big by area or big by population. Show name, population and area.

SELECT name, population, area FROM world
WHERE area > 3000000
OR population > 250000000;

练习八：显示人口数大于2亿5000万或国土面积大于3百万平方公里的国家名称、人口数以及国土面积(但是需要排除人口数大于2亿5000万且国土面积大于3百万平方公里的国家)

Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.

• Australia has a big area but a small population, it should be included.
• Indonesia has a big population but a small area, it should be included.
• China has a big population and big area, it should be excluded.
• United Kingdom has a small population and a small area, it should be excluded.

SELECT name, population, area FROM world
WHERE (area > 3000000 AND population <= 250000000)
OR (population > 250000000 AND area <= 3000000);

练习九：显示南美洲各国名称、人口数(以百万为单位)以及GDP(以10亿为单位)，并保留两位小数

Show the name and population in millions and the GDP in billions for the countries of the continent 'South America'. Use the ROUND function to show the values to two decimal places.

For South America show population in millions and GDP in billions both to 2 decimal places.

SELECT name, ROUND(population/1000000,2) AS 'population', 
ROUND(GDP/1000000000,2) AS 'GDP' FROM world
WHERE continent = 'South America';

练习十：显示GDP高于1万亿的国家名称及其人均GDP，并将人均GDP的值舍入到1000

Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.

Show per-capita GDP for the trillion dollar countries to the nearest $1000.

SELECT name, ROUND(gdp/population, -3) AS 'GDP per capita' FROM world
WHERE GDP >= 1000000000000;

练习十一：显示国家名称的长度等于其首都名称长度的国家及其首都

Greece has capital Athens. Each of the strings 'Greece', and 'Athens' has 6 characters.

Show the name and capital where the name and the capital have the same number of characters.

SELECT name, capital FROM world
WHERE LENGTH(name) = LENGTH(capital);

练习十二：显示国家名称和其首都名称首字母相同，但是国家名称和其首都名称不完全相同的国家及其首都

Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.

SELECT name, capital FROM world
WHERE LEFT(name,1) = LEFT(capital,1)
AND name <> capital;

练习十三：显示名称中包含全部元音字母(a,e,i,o,u)且名称中无空格的国家名称

Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.

Find the country that has all the vowels and no spaces in its name.

SELECT name FROM world
WHERE name LIKE '%a%' 
AND name LIKE '%e%' 
AND name LIKE '%i%'  
AND name LIKE '%o%'  
AND name LIKE '%u%' 
AND name NOT LIKE '% %'; 

【SELECT names 练习题】

地址：https://sqlzoo.net/wiki/SELECT_names

练习一：显示名称以Y开头的国家

Find the country that start with Y.

SELECT name FROM world 
WHERE name LIKE 'Y%';

练习二：显示名称以y结尾的国家

Find the countries that end with y.

SELECT name FROM world 
WHERE name LIKE '%Y';

练习三：显示名称中包含x的国家

Find the countries that contain the letter x.

SELECT name FROM world 
WHERE name LIKE '%x%';

练习四：显示名称以land结尾的国家

Find the countries that end with land.

SELECT name FROM world 
WHERE name LIKE '%land';

练习五：显示名称以C开头以ia结尾的国家

Find the countries that start with C and end with ia.

SELECT name FROM world 
WHERE name LIKE 'C%ia';

练习六：显示名称中包含连续的两个o的国家

Find the country that has oo in the name.

SELECT name FROM world 
WHERE name LIKE '%oo%';

练习七：显示名称中至少包含3个a的国家

Find the countries that have three or more a in the name.

SELECT name FROM world 
WHERE name LIKE '%a%a%a%';

练习八：显示名称中第二个字母为t的国家，并按名称升序排列

Find the countries that have "t" as the second character.

SELECT name FROM world
WHERE name LIKE '_t%'
ORDER BY name;

练习九：显示名称格式为xxxxoxxoxxx的国家

Find the countries that have two "o" characters separated by two others.

SELECT name FROM world 
WHERE name LIKE '%o__o%';

练习十：显示名称中只有个字母的国家

Find the countries that have exactly four characters.

SELECT name FROM world 
WHERE LENGTH(name) = 4;

练习十一：显示国家名称和其首都名称一致的国家

The capital of Luxembourg is Luxembourg. Show all the countries where the capital is the same as the name of the country

Find the country where the name is the capital city.

SELECT name FROM world 
WHERE name = capital;

练习十二：显示首都名称为’国家名称 City'的国家

The capital of Mexico is Mexico City. Show all the countries where the capital has the country together with the word "City".

Find the country where the capital is the country plus "City".

SELECT name FROM world
WHERE capital = CONCAT(name, ' City');

练习十三：显示首都名称中包含国家名称的国家首都及国家名称

Find the capital and the name where the capital includes the name of the country.

SELECT capital, name FROM world
WHERE capital LIKE CONCAT('%', name, '%');

练习十四：显示国家名称是首都名称一部分的首都及国家名称

Find the capital and the name where the capital is an extension of name of the country.

You should include Mexico City as it is longer than Mexico. You should not include Luxembourg as the capital is the same as the country.

方法一：

SELECT capital, name FROM world
WHERE capital LIKE CONCAT('%', name, '%')
AND LENGTH(capital) > LENGTH(name);

方法二：

SELECT capital, name FROM world
WHERE capital LIKE CONCAT('%', name, '_%');

练习十五：显示国家名字，及其延伸词。（"Monaco-Ville"是合并国家名字 "Monaco" 和延伸词"-Ville"。）

For Monaco-Ville the name is Monaco and the extension is -Ville.

Show the name and the extension where the capital is an extension of name of the country.

方法一：

SELECT name, RIGHT(capital, (LENGTH(capital) - LENGTH(name))) AS 'extension' FROM world
WHERE capital LIKE CONCAT('%',name,'%') AND
LENGTH(capital) > LENGTH(name);

方法二：

SELECT name, REPLACE(capital,name,'') AS 'extension' FROM world
WHERE capital LIKE CONCAT('%',name,'%') AND
LENGTH(capital) > LENGTH(name);