MySQL小结_MySQL小结

1.Where in 的用法：

IN运算符允许您在WHERE子句中指定多个值。IN 操作符允许我们在 WHERE 子句中规定多个值。IN运算符是多个OR条件的简写。

SELECT "栏位名"

FROM "表格名"

WHERE "栏位名" IN('值一', '值二', ...)

#字符串用单引号标注，包括后面的应用like的模糊查询，like后面的字符串也要加引号。

#操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

z.B Show all details of the presidential winners:Theodore Roosevelt

Woodrow Wilson

Jimmy Carter

Barack Obama

SELECT *

FROM nobel

WHERE winner IN ('Theodore Roosevelt',

'Woodrow Wilson',

'Jimmy Carter','Barack Obama')

2.ROUND 用法

z.BShow thenameandpopulationin millions and the GDP in billions for the countries of thecontinent'South America'. Use the ROUND function to show the values to two decimal places.

select name,round(population/1000000,2),round(gdp/1000000000,2)

from world

where continent='South America'

####ROUND函数，有2个参数

ROUND(你需要修改的数字，保留的小数点位数)

默认是不显示小数点，正数是保留的小数点的位数

负数是向左进行保留

比如

ROUND(10/3,2)=3.33

ROUND(10/3)=3

ROUND(12345,-2)=12300

3.Length 的用法

z.BGreece 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.You can use the LENGTH function to find the number of characters in a string

SELECT name,capital

FROM world

WHERE length(name)=length(capital)

4.Matching name and capital 以及<>‘不等同’的引用

The capital of Sweden is Stockholm. Both words start with the letter 'S'.

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.You can use the function LEFT to isolate the first character.

You can use <> as the NOT EQUALS operator.

select name, capital

from world

where left(name,1)=left(capital,1) and name<>capital;

5.Like--模糊查询的应用

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.You can use the phrase name NOT LIKE '%a%' to exclude characters from your results.

The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'

###like 后面要模糊查找的字符串要加引号。

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 '% %'

6.