Sql学习笔记一基础语法篇

练习网站

1.网页sql zoo,切换到MySQL

2.点击SELECT from word，开始练习第一题

一．基础语句

主知识点一：select&from

第一题：从单表中查询多列

在select后指定要查询的字段名称，多个字段名之间用英文逗号隔，最后一个字号不加逗号

“Select * from word”从表格中查询所有的列

“as”别名，原数据不会被改变，可以用空格代替

第二题：选择所有大洲且每个大洲只出现一次

“select distinct continent from word ”实现去重功能，放在多个字段前，对多个字段去重，不能放在字段后面

第三题：除法计算人均GDP

Gdp/population，同理，加减乘除均可计算

总结：1.基础语法：select字段名 from 表名

2.别名语法：select字段名as别名 from 表名

3.查询多列：select 字段1，字段2，字段3 from 表名

4.查询所有列：select* from 表名

5.数据去重：select distinct 字段名 from 表名

6.select中的计算字段：select 字段名，计算字段 from 表名

主要知识点二：where，筛选运算

=、>、<、>=、<=、<>、!=为比较运算符，用于判断表中的哪些数据符合条件

and、or和not为逻辑运算符

and ：一般用于组合两个及以上表达式，要求同时满足多个查询条件

or：一般用于组合两个及以上表达式，要求满足多个查询条件中的一个

not：not一般和与其他连用例如not in，用于条件取反

and和not可以一起使用，但是默认and优先级高于or，可以使用括号来界定优先级

between and，用于查询两个值之间范围的值（包含这两个值）

in，用于查询指定条件范围内的数据，一般为in (xxx,xxx,......)，用括号将条件括起来，is null，用于查询空值（NULL），空值不同于0，也不同于null字符串

第一题：查询人口数至少2亿的国家名和人均gdp

Select name, gdp/population 人均gdp from world where population >=200000000

第二题：查询德国（Germany）的人口

Select name, population from world where name='Germany'

第三题：查询瑞典(Sweden)、挪威 (Norway) 和丹麦 (Denmark)的国家名和人口

Select name, population from world where name in ('Sweden','Norway','Denmark')

第四题：查询面积(area)在250000和300000之间的国家名和面积

Select name, area from world where area between 250000 and 300000（>250000 and area<300000也可）

【模糊查询like】：where子句的表达式中除了使用运算符来进行条件判断，还可以使用like操作符组，通配符用来匹配值的一部分，跟在like后面进行数据过滤常用的通配符有%和_，%用来匹配多个字符可以是零个、一个也可以是多个字符，_仅能用来匹配单个字符

第五题：查询国家名中以C开头ia结尾的国家

select name from world where name like 'C%ia'

第六题：查询国家名中含有两个o且被两个字符隔开的国家名

Select name from world where name like '%o__o%'

【多条件查询】:使用and或者or逻辑运算符对多个条件进行组合筛选想要的数据

第七题：查询国家名中含有三个a且面积大于60万(600000)的国家及其面积

Select name,area from world where name like '%a%a%a%' and area>=600000

第八题：查询国家名中含有三个a且面积大于60万(600000)的国家及其面积，或者人口大于13亿(1300000000)且面积大于500万(5000000)的国家及其面积

Select name, area from world where name like '%a%a%a%' and area >= 600000 or  population >= 1300000000 and  area >= 5000000

第九题：查询既包含有所有元音字母（a,e,i,o,u），同时国家名中没有空格的国家，最后显示他们的名字

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

第十题：查询1910年以前（不含1910）诺贝尔医学奖获得者和2004年及以后诺贝尔文学奖获得者的所有信息

Select * from noble where (subjiect in (‘Medicine’) and yr < 1910) and (subjiect in (‘Literature’) and yr >= 2004)

Select * from nobel where (subject = 'Medicine' and yr < 1910) or (subject = 'Literature' and yr >= 2004)

总结：1.标准语法：select字段名 from 表名 where表达式

2.运算符查询语法：select字段名 from 表名 where 字段名 运算符

3.模糊查询语法：select字段名 from 表名 where 字段名 like ‘通配符+字符’

4.使用多条件查询：select字段名 from 表名 where 条件代码1 and /or 2

主要知识点三：order by 

order by 字段名 asc|desc 规定查询出的结果集显示的顺序order by核心子句是可选项，使用该子句是为了对被查询出的结果集，指定依据字段排序asc指定该字段升序排序，desc为降序排序，不写则默认为升序排序

第一题：查询姓名以Sir开头的获奖者(winner)，获奖年份(yr)和科目(subject)，查询结果按照年份从近到远排序，再按照姓名顺序升序排序

Select winner, yr, subject from nobel where winner like 'Sir%'

order by yr desc, winner asc

总结：1.标准语法：select字段名 from 表名 where 表达式 order by 字段名 asc /desc

第二题：查询1984年所有获奖者的姓名和奖项科目。结果将诺贝尔化学奖和物理学奖排在最后，然后按照科目排序，再按照获奖者姓名排序

Select winner, subject from nobel where yr = 1984 order by subject in (‘chemistry','physics’) , subject , winner(括号里为零自动降序排列)

主知识点四：limit

limit [位置偏移量,]行数 限制查询结果集显示的行数limit子句是可选项，行数是子句中的必选参数，参数位置偏移量是可选参数

第一题：查询面积排名前三的国家

Select * from world order by area desc limit 3

第二题：查询人口数第4到第7的国家和人口

Select * from world order by area desc limit 3, 4(limit x, n 从x+1行开始记录n行，这道题就是从第四行开始记录4行)

第三题：查询nobel表中第100行到120行的数据

Select * from world order by area desc limit 99, 20

总结：1.查询结果返回前n行select 字段名from 表名 where 表达式order by 字段名 asc|desc limit n

1. 查询结果从第x+1行开始记录n行select 字段名from 表名where 表达式 order by 字段名 asc|desc  limit x,n

主知识点五：聚合函数&group by

group by 字段名 规定依据哪个字段分组聚合

group by核心子句是可选项，使用该子句是为了依据相同字段值分组后进行聚合运算，常和聚合函数联用

第一题：查询非洲总人口数

Select  sum(population) 人口总数 from world where continent = 'africa'

将sum函数依次修改为avg()、max()、min()，依次计算平均人口数、最大人口数、最小人口数

Select  avg(population),max(population),min(population) from world where continent = 'africa'

第二题：计算表格行数

Select count（*）from world

第三题：查询每个大洲（continent）和大洲内的国家（name）数量

Select continent,count(name) from world group by continent

第四题：查询2013至2015年每年每个科目的获奖人数，结果按年份从大到小，人数从大到小排序

Select yr, subject,count(winner) 获奖人数 from nobel where yr between 2013 and 2015 group by yr subject order by yr, count(winner) desc

第五题：计算Estonia, Latvia, Lithuania这几个国家的总人口数

Select sum(population) 总人数 from world where name in ( 'Estonia', 'Latvia', 'Lithuania')

第六题：查询每个大洲和该大洲里人口数超过1千万的国家的数量

Select continent,count(name) from world where population > 10000000 group by continent

总结：聚合函数

标准语法：select 字段名1,聚合函数(字段名) from 表名where 表达式group by 字段名1 order by 字段名 asc|desc limit [位置偏移量,]行数

主知识点六：having&简单运行原理

having 表达式 限定分组聚合后的查询行必须满足的条件

having核心子句是可选项，使用该子句是为了对group by分组后的数据进行筛选

select 字段名from 表名where 表达式group by 字段名having 表达式order by 字段名 asc|desc  limit [位置偏移量,]行数]

第一题：查询总人口数量至少为1亿（100000000）的大洲

Select continent from world group by continent having sum(population) >=100000000 

第二题：查询总人口数至少为3亿的大洲和其平均gdp，其中只有gdp高于200亿且人口数大于6000万或者gdp低于80亿且首都中含有三个a的国家的计入计算，最后按国家数从大到小排序，只显示第一行

Select continent, avg(gdp) 平均gdp from world where (gdp > 20000000000 and population > 60000000) or (gdp < 8000000000 and capital like '%a%a%a%') group by continent having sum(population) >= 300000000  order by count(name) desc limit 1

 

第三题：查询人均gdp大于3000的大洲及其人口数，仅gdp在200亿和300亿之间的国家计入计算

Select continent, sum(population)人口数,  sum(gdp)/sum(population) 人均gdp from world where gdp > 20000000000 and gdp < 30000000000 group by continent having  sum(gdp)/sum(population) > 3000

总结：select 字段名 from表名 where 表达式（分组前筛选） group by 字段名 having 表达式（分组后筛选） order by字段名asc desc limit 保留行数

主知识点七：部分常见函数

【数学函数】

round(x,y)——四舍五入函数

round函数对x值进行四舍五入，精确到小数点后y位

y为负值时，保留小数点左边相应的位数为0，不进行四舍五入

例如：round(3.15,1)返回3.2，round(14.15,-1)返回10

【字符串函数】

concat(s1,s2,...)——连接字符串函数

concat函数返回连接参数s1、s2等产生的字符串

任一参数为null时，则返回null

例如：concat('My',' ','SQL')返回My SQL，concat('My',null,'SQL')返回null

replace(s,s1,s2)——替换函数

replace函数使用字符串s2代替s中所有的s1

例如：replace('MySQLMySQL','SQL','sql')返回MysqlMysql

left(s,n)、right(s,n)&substring(s,n,len)——截取字符串一部分的函数

left函数返回字符串s最左边n个字符

right函数返回字符串s最右边n个字符

substring函数返回字符串s从第n个字符起取长度为len的子字符串，n也可以为负值，则从倒数第n个字符起取长度为len的子字符串，没有len值则取从第n个字符起到最后一位

例如：left('abcdefg',3)返回abc，right('abcdefg',3)返回efg，substring('abcdefg',2,3)返回bcd，substring('abcdefg',-2,3)返回fg，substring('abcdefg',2)返回bcdefg

 【数据类型转换函数】

cast(x as type)——转换数据类型的函数

cast函数将一个类型的x值转换为另一个类型的值

type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型

【日期时间函数】

year(date)、month(date)&day(date)——获取年月日的函数

date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间

year(date)返回日期格式中的年份

month(date)返回日期格式中的月份

day(date)返回年日期格式中的日份

例如：year('2021-08-03')返回2021，month('2021-08-03')返回8，day('2021-08-03')返回3

示例一：case when和if函数试运行

select recovered 累计治愈人数, case when recovered = 1 then 'one'  when recovered >1 then 'more' else '0' end from covid where recovered > 0

示例二:year、month、day函数试运行

select whn 更新时间, year(whn)年, month(whn)月, day(whn)日 from covid where recovered > 0

示例三：date_add函数试运行

select whn 时间, date_add(whn, interval 2 day)加2天from covid where recovered > 0

示例四：round和concat嵌套得到百分比数据

Select confirmed, deaths, recovered, recovered/confirmed, concat

(round((recovered/confirmed)*100,2),'%') 治愈率from covid where recovered/confirmed > 0.3

示例五：replace函数

Select distinct name, replace(name,'a','替换')替换 from covid

示例六：substring函数

Select distinct name, substring(name,2,3), substring(name,2) from covid where recovered/confirmed > 0.3

总结：【数学函数】

round(x,y)——四舍五入函数

【字符串函数】

concat(s1,s2,...)——连接字符串函数

replace(s,s1,s2)——替换函数

left(s,n)——从左截取字符串一部分的函数

right(s,n)——从右截取字符串一部分的函数

substring(s,n,len)——从指定位置截取字符串一部分的函数

【数据类型转换函数】

cast(x as type)——转换数据类型的函数

【日期时间函数】

year(date)——获取年的函数

month(date)——获取月的函数

day(date)——获取日的函数

date_add(date,interval expr type)——对指定起始时间进行加操作

date_sub(date,interval expr type)——对指定起始时间进行减操作

datediff(date1,date2)——计算两个日期之间间隔的天数

date_format(date,format)——将日期和时间格式化

【条件判断函数】——根据满足不同条件，执行相应流程

if(expr,v1,v2)

case when

case expr when v1 then r1 [when v2 then r2] ...[else rn] end

case when v1 then r1 [when v2 then r2]...[else rn] end

第一题：查询国家名称及其首都名称都以相同的字母开头的国家名及其首都，且不能包括国家名称和首都名称完全相同的情况

Select name, capital from world where left(name,1) = left(capital,1) and name != capital

第二题：查询首都和名称，其中首都需是国家名称的扩展例如：答案中应该包括墨西哥城（Mexico City），因为它比墨西哥（Mexico）更长，而不应该将卢森堡（Luxembourg）包括在内，因为首都名与国家名相同

Select name, capital from world where capital like concat('%',name,'%') and name != capital