sql代码基础

1.ActiveSheet.Cells(1, 1).PasteSpecial（拷贝方法为选择性拷贝）
2.ActiveSheet.Cells(ActiveSheet.Range(“a65536”).End(xlUp).Row + 1, (索引exce工作簿中最最底部的位置)

1.select选择查询
2.from
3.where
4.group by 参数 按参数分组 HAVING 放在groupby 后面作用相当于where 但比较条件已为分组后数据
5.having

6.order by 排序 – asc升 默认 desc 降

• SQL语言的默认排序方式是升序，ORDER BY 子句若未显式指定升序(ASC)或降序(DESC)，那么就认按默认升序排序。
  例如下列语句为默认按升序排序
  select * from t1 order by col1;
  它与下列语句等效
  select * from t1 order by col1 asc;

7.limit 限制数量
8.DESC 排序
9.round（数字，2） 保留两位小数

顺序 -> sfwgho 顺序
left(nam

sql代码基础

SELECT * FROM `world`;
use world;
select name,continent,population FROM world;

2.找到人口大于200w的国家的名字和人口

SELECT name,population from world where population > 2000000
3.找到人口大于200w的国家的名字和人均GDP
SELECT NAME,gdp FROM WHERE population > 2000000
4.找到南美洲大陆所有国家和对应的人口总数(以百万单位展示)
SELECT NAME,population/1000000 as popula FROM world where  continent = 'South America'
5.找到France,Italy,Germany对应的人口总数(in,注意字符串)
SELECT name,population FROM world where name in ('France','Italy','Germany')
6.找到名字中包含'united'的国家(like)
SELECT name from world where NAME LIKE '%united%'

aeiou
SELECT name from world where NAME LIKE '%a%' or '%e%' or '%i%' or '%o%' or '%u%';
SELECT name from world where NAME LIKE '%a%' OR LIKE  '%e%' OR LIKE '%i%' OR LIKE '%o%' OR LIKE '%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%';

7.找出名字包含三个或三个以上a的国家
SELECT name from world where NAME LIKE '%a%a%a%'

8.找出名字第二个字母为t的国家(占位符用_)
SELECT name from world where NAME LIKE '_t%'

9.找出所有国家,其名字都有两个字母 o,被另外两个字母相隔
SELECT name from world where NAME LIKE '%o__o%'
10.找出首都和国家名字相同的国家
SELECT name,capital from world where NAME =capital


11.找到面积大于3000000或者人口总数大于250000000的国家,人口和面积
SELECT NAME,population,area FROM world where area >3000000 or population >250000000

12.找到南美洲的国家,人口和GDP(人口以百万显示,GDP以10亿显示,保留两位小数)(round函数)
SELECT name,round(population/1000000,2) as pop,ROUND(gdp /1000000000,2) as gdpp FROM world WHERE name='South Africa'



13.找到国家名字和首都名字字符长度一致的国家和首都(length函数)
SELECT name,capital FROM  world where LENGTH(name)=LENGTH(capital)

14.找到国家和首都名字的首字母一致,但是全称不一致的国家和首都(left(c,1),<>)
SELECT name,capital FROM  world where left(name,1)=left(capital,1) and NAME !=capital


15.找到以U开头的国家名
SELECT name FROM world WHERE left(name,1)='u'
16.找到以U开头的国家名,但是名字里不包含空格
SELECT name FROM world WHERE left(name,1)='u' and name not LIKE '% %'
17.找到以c开头以a结尾的国家名
SELECT NAME FROM world WHERE NAME like 'c%a'


18.找到"United Kingdom"对应的人口总数
SELECT name,population FROM world WHERE name= "United Kingdom"

19.找到人口总数小于10000的国家和对应的人口数
SELECT name,population FROM world WHERE population <10000

20.找到人口总数小于10000的国家和对应的人口数(人口数以万为单位,保留两位小数)
SELECT name,ROUND(population/10000,2) as pop FROM world WHERE population <10000
21.找到国家的首字母和对应的首都尾字母相同的国家和对应的首都
SELECT name,capital FROM world WHERE LEFT(name,1)=RIGHT(capital,1)
22.找到亚洲和欧洲各国的名字和GDP
SELECT name,gdp,continent FROM world where continent in ("asia",'europe')



计算世界的总人口(sum)
SELECT sum(population) as sum from world
计算非洲的GDP总数
desc world 
SELECT sum(gdp) from world where continent='Africa'
计算'Estonia', 'Latvia', 'Lithuania'的人口总数
SELECT sum(population) FROM world WHERE NAME in ('Estonia','Latvia','Lithuania')
计算世界有多少个不同的大洲(distinct)
SELECT count(distinct(continent)) FROM world
计算国土面积超过1000000平方公里的国家总数
SELECT count(DISTINCT(name)) FROM world where area > 1000000

计算亚洲和美洲的国家总数和人口总数
SELECT count(name),sum(population) FROM world where continent in('Asia','America')
计算gdp超过100亿的所有国家数和gdp总数
SELECT count(name),sum(population) from world where gdp>10000000000



找到每个大洲对应的国家总数
SELECT continent,count(NAME)FROM world GROUP BY continent

找到每个大洲对应的国家总人口数
SELECT continent,count(population)FROM world GROUP BY continent
找到每个大洲对应的国家gdp平均数
SELECT continent,sum(gdp)/count(name)FROM world GROUP BY continent

找到每个大洲比非洲所有国家gdp都高的GDP总数
SELECT continent,sum(gdp)FROM world   GROUP BY continent HAVING  sum(gdp) > (SELECT maX(gdp) FROM world where continent ='Africa')

找到每个大洲对应的国家人口超过10000000的国家总数
SELECT continent,count(population)FROM world where population > 10000000 GROUP BY continent

找到每年的诺奖总数
SELECT yr,count(winner) from nobel GROUP BY yr

找到每个奖项对应的总人数

SELECT subject,COUNT(winner) FROM nobel GROUP BY SUBJECT
找到2014年每个奖项对应的人数的top5(按获奖人数)

SELECT yr,COUNT(winner) FROM nobel where yr =2014 GROUP BY SUBJECT  ORDER BY COUNT(winner) DESC LIMIT 5


-------------
找到人口比Russia人口多的国家
SELECT NAME FROM world where 	population >(SELECT population FROM world where name ='Russia')

找到人均gdp比United kingdom多的欧洲国家
SELECT name,round(gdp/population,2)  AS avegdp FROM world WHERE gdp/population >(SELECT gdp/population FROM world WHERE name='United kingdom')

找到Argentina or Australia所在洲对应的国家和洲名
SELECT name ,continent from world WHERE CONTINENT IN(SELECT continent FROM world WHERE name IN('Argentina','Australia'))

找到人口比Canada多但是比Poland少的国家的名字和人口between(select) and(select)
SELECT name,population FROM world  where population BETWEEN(SELECT population FROM world WHERE name ='Canada') and (SELECT population FROM world WHERE name ='Poland')
计算欧洲各个国家人口总数占德国人口总数的百分比
SELECT NAME,population/(SELECT population from world where name ="Germany") FROM world WHERE continent ="Europe"
找到比欧洲各个国家GDP都高的那些国家  gdp>all(select gdp from wrold where continent = europe )
SELECT name,gdp FROM world WHERE gdp > all(select gdp from world where continent = 'europe' and gdp>0 )

show DATABASES;
SHOW TABLES;
DROP TABLE student1;
gdp> (SELECT gdp FROM world WHERE name='India') 
DESC world
ALTER TABLE world MODIFY  population INT;
ALTER TABLE world MODIFY  area INT;
ALTER TABLE world MODIFY  gdp BIGINT;

1找到人口,gdp比印度多的国家
SELECT name,gdp,population FROM world WHERE  population >= (SELECT population FROM world WHERE name='India')  and gdp> (SELECT gdp FROM world WHERE name='India')
2找到人口比法国多的国家和对应的洲
SELECT name,continent,population  FROM world WHERE population >(SELECT population FROM world WHERE NAME ='france')
3找到人均gdp比中国多,比美国少的欧洲国家
SELECT name,gdp/population as avegdp FROM world where gdp/population BETWEEN (SELECT gdp/population FROM world WHERE NAME='China' ) AND (SELECT gdp/population FROM world WHERE NAME='United States')

4找到south africa和Vietnam所在州对应的国家和人均gdp(保留两位小数)

SELECT name ,ROUND(gdp/population,2) FROM world WHERE continent in (SELECT  continent FROM world WHERE NAME in('south africa','Vietnam'))
5找到人口比Canada多但是比France少的国家的名字和人口(两种方式写)
SELECT name,population FROM world WHERE population BETWEEN(SELECT population FROM world WHERE NAME= 'Canada') and(SELECT population FROM world WHERE NAME= 'France')
计算亚洲各个国家人均gdp占中国人均gdp的百分比(保留4位有效数字)
SELECT NAME, gdp/(SELECT gdp FROM world WHERE NAME ='China' and gdp>0) FROM world 
找到比非洲各个国家gpd都高的亚洲国家
SELECT name,gdp FROM world  WHERE continent='Asia' and gdp > ALL(SELECT gdp FROM world WHERE continent ='Africa' and gdp>0)
找到比亚洲各个国家国土面积都大的北美洲国家
SELECT name,area FROM world WHERE continent='North America' and  area> all(SELECT area FROM world WHERE continent='Asia')

找到1998年化学奖奖情况yr winner subject
SELECT * FROM nobel WHERE yr=1998 and subject ='Chemistry'
找到1962年物理学获奖者
SELECT * FROM nobel WHERE yr=1962 and subject ='Physics'
找到2000年后文学奖得主
SELECT * FROM nobel WHERE yr>2000 and subject ='Literature'
找到2001年到2004年间的所有医学奖获奖情况
SELECT * FROM nobel WHERE subject='Medicine' AND yr BETWEEN 2001 and 2004
找到Theodore Roosevelt,Woodrow Wilson,Jimmy Carter,Barack Obama的获奖详情
SELECT * FROM nobel WHERE winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter','Barack Obama')
找到名字为frank的所有获奖者
SELECT * FROM nobel WHERE winner LIKE'%frank%'
找到1980年除化学奖和医学奖之外的所有奖项的具体信息
SELECT * FROM nobel WHERE subject not in('Chemistry','Medicine')
找到1910年之前的医学奖和2000年之后的物理学奖
SELECT * FROM nobel WHERE (subject ='Medicine' and yr<1910) or (subject ='Physics' and yr>2000)

------------

查询score表中成绩在60到80之间的所有记录
select * FROM score WHERE DEGREE BETWEEN 60 AND 80;
查询score表中成绩为85，86或88的记录
SELECT * FROM score WHERE DEGREE in('85','86','88');
-- 
-- 查询student表中"95031"班且性别为"女"的同学记录
SELECT * FROM student WHERE CLASS =95031 and SSEX='女';

-- 以class降序查询student表的所有记录
SELECT * FROM student ORDER BY CLASS desc;
-- 以cno升序、degree降序查询score表的所有记录(升序关键字为asc)
SELECT * FROM score ORDER BY CNO ASC ;
SELECT * FROM score ORDER BY DEGREE DESC;
-- 查询"95031"班的学生人数
usr student;
SELECT count(SNAME) FROM student WHERE CLASS='95031' 
-- 查询score表中的最高分的学生学号和课程号(子查询)

-- 查询"3-105"号课程的平均分
-- 查询score表中至少有5名学生选修的并以3开头的课程号和该课程的平均分数(group by,having)
-- 查询最低分大于70，最高分小于90的sno列(having)
-- 查询"95033"班学生所选课程的平均分
-- 查询score表中选修"3-105"课程的成绩高于"109"号同学'3-105'课程成绩的所有同学的记录
-- 查询存在有85分以上成绩的课程cno
-- 查询至少有2名男生的班号
-- 查询Student表中不姓“王”的同学记录
-- 查询student表中每个学生的姓名和年龄year(now()-year(sbirthday))