between and 包含边界吗_自学SQL很难吗

根据进度随时更新。

确立目标

第一部分：入门+练习

1、安装MySQL和客户端Navicat，并连接成功。

2、练习表的创建、删除和更新

通过Navicat客户端创建表：

3、练习数据的插入、删除和更新

入门总结：

第一部分：入门总结

---

第二部分：简单查询+练习

1、select查询语句

select * from student;
select 姓名,学号 from student;
select 姓名,学号 as '名字','编号' from student;
select distinct 姓名 as '1' from student;
select distinct 姓名,学号 as '1','2' from student;

2、条件查询

select 姓名 from student where 姓名='王思聪';
select 学号 from student where 学号=0001;

3、注释的使用和正确的书写

-- 注释
/* 从student 中查找 学号=0001 的学号 */

select
姓名,学号
from
student;

4、算术运算符

比较运算符——

select 学号,成绩,
成绩/100 as '百分比成绩'
from score;
/* 注意成绩后面还有一个逗号 */

select 姓名,出生日期 
from student
where 出生日期>'1990-01-01';

select 学号,成绩
from score
where 成绩<>80;

select 教师号,教师姓名
from teacher
where 教师姓名 is null;

select 教师号,教师姓名
from teacher
where 教师姓名 is not null;

逻辑运算符——

select 学号,成绩
from score
where not 成绩<90;

select 学号,成绩
from score
where 成绩>=60 and 成绩<90;

select 学号,姓名,性别
from student
where 性别='男'and (姓名='王思聪'or 姓名='马云');
/* 注意：'马云'前面少写一个姓名，会只出现'王思聪' */

select 学号,成绩
from score
where 成绩 between 60 and 90;

select 学号,成绩
from score
where 成绩>=60 and 成绩<=90;
/* 注意：以上两个查询结果一致 */

select 学号,成绩
from score
where 成绩<60 or 成绩>90;

-- 查询不是马云或王思聪的姓名
select 姓名,性别
from student
where 姓名 not in ('马云','王思聪');

5、模糊查询

select *
from student
where 姓名 like'%云';

select *
from student
where 姓名 like'%思%';

select *
from student
where 姓名 like'_云';

select *
from student
where 姓名 like'王__';

简单查询总结：

第二部分：简单查询总结

sqlzoo平台练习：

1、 SELECT basics/zh

world表：世界国家信息表

-- 1、這個例子顯示’France法國’的人口。字串應該在'單引號'中。
SELECT population FROM world
  WHERE name = 'France'
-- 修改此例子,以顯示德國 Germany 的人口。
select population from world
where name = 'Germany';

-- 2、查詢顯示面積為 5,000,000 以上平方公里的國家,該國家的人口密度(population/area)。
-- 人口密度並不是 WORLD 表格中的欄,但我們可用公式(population/area)計算出來。
SELECT name, population/area FROM world
  WHERE area > 5000000;
-- 修改此例子,查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值(gdp/population)。
select name,gdp,population,gdp/population from world
where area>5000000;

-- 3、檢查列表:單詞“IN”可以讓我們檢查一個項目是否在列表中。
-- 此示例顯示了“Luxembourg 盧森堡”,“Mauritius 毛里求斯”和“Samoa 薩摩亞”的國家名稱和人口。
SELECT name, population FROM world
  WHERE name IN ('Luxembourg', 'Mauritius', 'Samoa');
-- 顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。
select name,population from world
where name in ('Ireland','Iceland','Denmark');

-- 4、哪些國家是不是太小,又不是太大?BETWEEN 允許範圍檢查 
-- 注意,這是包含性的。 此例子顯示面積為 250,000 及 300,000 之間的國家名稱和該國面積。
SELECT name, area FROM world
  WHERE area BETWEEN 250000 AND 300000
-- 修改此例子,以顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積。
select name,area from world
where area between 200000 and 250000;

2、SELECT from WORLD Tutorial

-- 1、显示所有国家，所在州名称和国家人口
select name, continent, population from world;

-- 2、显示人口至少为2亿的国家/地区的名称
select name from world
where population >200000000;

-- 3、找出人口大于2亿的国家名字和人均GDP
select name,gdp/population from world
where population>200000000;

-- 4、查找属于南美洲(South America)的国家名称，并将人口除以100万，以获得数百万人口数
select name,population/1000000 from world
where continent = 'South America';

-- 5、查找法国，德国，意大利(France, Germany, Italy)的国家名称和人口
select name,population from world
where name in ('France','Germany','Italy');

-- 6、查找国家名称中包含“United”的国家
select name from world
where name like '%United%';

-- 7、找出大国的名称，人口和面积
select name,population,area from world
where area>3000000 or population>250000000;

-- 8、找出符合下面条件国家的名称，人口和面积。条件：人口（大于250000000）或者面积（大于3000000）大的国家，排除同时面积大而且人口大的国家
select name,population,area from world
where (area>3000000 and population<=250000000)or(area<=3000000 and population>250000000);

-- 9、查找南美洲（South America）的国家名称，人口数以百万表示（1后面6个0），gpd以十亿表示（1后面9个0），使用ROUND函数将值显示为两位小数
select name,round(population/1000000,2),round(gdp/1000000000,2) from world
where continent = 'South America';

-- 10、查找gdp大于1万亿（100亿，即12个零）的国家的名称和人均GDP。
select name,round(gdp/population,-3) from world
where gdp >= 1000000000000;

3、 SELECT names/zh - SQLZOO

-- 1、找出以 Y 為開首的國家。
select name from world
where name like 'Y%';

-- 2、找出以 Y 為結尾的國家。
select name from world
where name like '%y';

--3、找出所有國家,其名字包括字母x。
select name from world
where name like '%x%';

--4、找出所有國家,其名字以 land 作結尾。
select name from world
where name like '%land';

--5、找出所有國家,其名字以 C 作開始,ia 作結尾。
select name from world
where name like 'C%ia';

-- 6、找出所有國家,其名字包括字母oo。
select name from world
where name like '%oo%';

-- 7、找出所有國家,其名字包括三個或以上的a。
select name from world
where name like '%a%a%a%';

-- 8、找出所有國家,其名字以t作第二個字母。
select name from world
where name like '_t%'
order by name;

-- 9、找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着。
select name from world
where name like '%o__o%';

-- 10、找出所有國家,其名字都是 4 個字母的。
select name from world
where name like '____';

-- 11、顯示所有國家名字,其首都和國家名字是相同的。
select name,capital,continent from world
where name=capital;

-- 12、顯示所有國家名字,其首都是國家名字加上”City”。
select name from world 
where capital = concat(name,' City');
/* city前面有空格 */

-- 13、找出所有首都和其國家名字,而首都要有國家名字中出現。
select capital,name from world
where capital like concat('%',name,'%');

-- 14、找出所有首都和其國家名字,而首都是國家名字的延伸。你應顯示 Mexico City,因它比其國家名字 Mexico 長。
-- 你不應顯示 Luxembourg,因它的首都和國家名相是相同的。
select name,capital from world
where capital like concat('%',name,'_%');

-- 15、"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville"。顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
-- 你可以使用SQL函數 REPLACE 或 MID.
select name,replace(capital,name,'')from world
where capital like concat(name,'%_');

---

第三部分：汇总分析+练习

1、汇总函数

-- 查询课程编号为“002”的总成绩
select sum(成绩) from score where 课程号 ='0002';

-- 查询选了课程的学生数
select count(distinct(学号)) as 学生人数 from score;

2、分组

# 查询男生、女生人数
select 性别,count(*) from student group by 性别;

select 性别,count(*) 
from student 
where 出生日期 > 1990-01-01
group by 性别;

# 查询各科成绩最高和最低的分
select 课程号,max(成绩) as 最高分,min(成绩) as 最低分 from score group by 课程号;

# 查询每门课程被选修的学生数
select 课程号,count(distinct(学号)) from score group by 课程号;

对分组结果指定条件

/* 
分析思路
select 查询结果 
from 从哪张表中查找数据 
where 查询条件
group by 分组 
having 对分组结果指定条件
order by 对查询结果排序 
*/

#查询平均成绩大于60分学生的学号和平均成绩
select 学号,avg(成绩) as 平均成绩
from score
group by 学号
having 平均成绩>60;

#查询至少选修两门课程的学生学号
select 学号,count(课程号) as 选修课程数
from score
group by 学号
having 选修课程数>=2;

#查询同名同姓学生名单并统计同名人数
select 姓名,count(姓名) as 同名人数
from student
group by 姓名
having 同名人数>=2;


select 姓名,count(*) as 同名人数
from student
group by 姓名
having count(*)>=2;

#如何计算每门课程的平均成绩并且平均成绩大于等于80分
select 课程号,avg(成绩) as 平均成绩
from score
group by 课程号
having 平均成绩>=80;

3、对查询结果排序

#查询不及格的课程并按课程号从大到小排列
select 课程号 as 不及格的课程
from score
where 成绩 < 60
order by 课程号 desc;
/* 先从表中取出成绩小于60的课程号,再按照课程号从大到小排列*/

#查询每门课程的平均成绩，结果按照平均成绩升序排序。平均成绩相同时，按课程号降序排列
select 课程号,avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc,课程号 desc;
/* 先从表中取出课程号和成绩的数据，进行平均成绩计算，结果排序*/

汇总分析总结：

第三部分：汇总分析总结

sqlzoo平台练习：

1、 SELECT from Nobel Tutorial/zh

#1.更改查询语句，显示1950年的诺贝尔奖
select yr,subject,winner from nobel where yr = 1950;

#2.谁赢得了1962年的文学奖（Literature）
select winner from nobel where yr=1962 and subject='Literature';

#3.获奖者“阿尔伯特爱因斯坦”是哪一年获奖的，得的是哪个诺贝尔奖
select  yr,subject from nobel where winner='Albert Einstein';

#4.自2000年以来，包括2000年的“和平”获奖者名称
select winner from nobel where subject='Peace' and yr>=2000;

#5.查找1980年至1989年的文学奖获奖者的所有细节（年份，奖项，获奖者）
select yr,subject,winner 
from nobel 
where subject='Literature' and (yr between 1980 and 1989);

#6.查找下面获奖者的所有详细信息：
select * from nobel 
where winner in ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter');

#7.获奖姓名以John开头的获奖者有谁？
select winner from nobel where winner like 'John%';

#8.查找1980年物理学奖（Physics）获得者，以及1984年的化学奖（Chemistry）获得者的信息
select * from nobel
where (yr=1980 and subject='Physics') or (yr=1984 and subject='Chemistey');

#9.查找1980年获奖者的年份，奖项和名称，不包括化学奖和医学奖('Chemistry', 'Medicine')
select winner from nobel
where yr=1980 and subject not in ('Chemistry','Medicine');

#10.在早年（1910年之前，不包括1910年）获得“医学”奖的人的年份，主题和名称，以及晚年（2004年之后，包括2004年）的“文学”奖的获奖者
select * from nobel 
where (subject='Medicine' and yr<1910) or (subject='Literature'and yr>2004);

#11.查找获奖者（PETERGRÜNBERG）的获奖信息
select * from nobel where winner='PETER GRÜNBERG';

#12.查找获奖者（Eugene O''Neill）的获奖信息
select * from nobel where winner = 'Eugene O''Neill';

#13.列出获奖者，年份以及获奖者从Sir开始的主题。首先显示最新的，然后按名称顺序显示
select *
from nobel
where winner like 'sir%'
order by yr desc,winner;

#14.查找1984年获奖者和主题按主题和获胜者名称排序,并把化学奖和物理奖排到最后显示
select winner, subject
from nobel
where yr=1984
order by subject in ('Physics','Chemistry'),subject,winner;

/*
subject in ('Physics','Chemistry')返回值（0或者1），会对每一个subject做一个if的判断，有的是1，没有的是0，再用order by把这些值排序在下面。
*/

2、https://sqlzoo.net/wiki/SUM_and_COUNT/zh

#1. 显示世界总人口
select sum(population) from world;

#2. 列出所有洲名称，不能有重复值
select distinct(continent) from world;

#3. 非洲的国内生产总值是多少？
select sum(gdp) from world where continent='Africa';

#4. 有多少国家的面积至少为100万（土地面积）
select count(name) from world where area>=1000000;

#5. （'法国'，'德国，'西班牙'）的总人口是多少？
select sum(population) from world 
where name in ('France','Germany','Spain');

#6.显示每个大陆以及大陆的国家数量
select continent,count(name) from world group by continent;

#7.显示每个大陆的国家数量，并且这些国家里人口数量至少为1000万
select continent,count(name)
from world
where population>10000000
group by continent;

#8.列出每个洲名称，并且每个洲的总人口数要大于等于一亿
select continent
from world
group by continent
having sum(population)>100000000;

---

第四部分：复杂查询

1、创建视图

create view 按性别汇总(性别,人数) 
as
select 性别,count(*)
from student
group by 性别;

#可作为表直接取用
select 性别,人数
from 按性别汇总;

2、子查询

#分组取每组最小值。案例：按课程号分组取成绩最小值所在行的数据
select *
from score as a
where 成绩=(
select min(成绩)
from score as b
where a.课程号=b.课程号)

#每组最大的N条记录。案例：查询各科成绩前两名的记录
(select * from score where 课程号='0001' order by 成绩 desc limit 2)
union ALL
(select * from score where 课程号='0002' order by 成绩 desc limit 2)
union ALL
(select * from score where 课程号='0003' order by 成绩 desc limit 2)

# 哪些学生的成绩比课程002的全部成绩里的任意一个高呢？
select 成绩 from score where 成绩 > any(
select 成绩 from score where 课程号 = '0002');

# 哪些学生的成绩比课程002的全部成绩里的都高呢？
select 成绩 from score where 成绩 > all(
select 成绩 from score where 课程号 = '0002');

select 成绩 from score where 成绩 > (
select max(成绩) from score where 课程号 = '0002');

3、标量子查询

# 大于平均成绩的学生的学号和成绩
select 学号,成绩 from score 
where 成绩 > (select avg(成绩)from score);

# 查询成绩介于差生平均成绩和优等生平均成绩之间的学生学号和成绩
select 学号,成绩 from score 
where 成绩 between 
(select avg(成绩) from score where 成绩<=60) and 
(select avg(成绩)from score where 成绩>80);

# 对比
select 学号,成绩,(select avg(成绩) from score) as 平时成绩
from score 

4、关联子查询

# 查找出每个课程中大于对应课程平均成绩的学生
select 学号,课程号,成绩 from score as a
where 成绩>
(select avg(成绩) from score as b
where a.课程号 = b.课程号
group by 课程号);

复杂查询总结：

第四部分：复杂查询总结

sqlzoo平台练习：

1、https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh

#1.列出符合条件的国家名称，条件：国家人口大于俄罗斯(Russia)的人口
select name from world
where population >
(select population from world
where name='Russia');

#2.列出欧洲每个国家的人均GDP，其中人均GDP要高于英国(United Kingdom)
select name from world
where continent='Europe' and 
gdp/population>(
select gdp/population from world
where name='United Kingdom');

#3.在阿根廷(Argentina)和澳大利亞(Australia)所在的洲份中的国家有哪些？查找出国家名称和洲名称，并按国家名称排序
select name,continent from world
where continent in 
(select continent from world 
where name ='Argentina'or name='Australia')
order by name;

#4.查找符合下面条件的国家名称和人口：国家的人口比加拿大(Canada)的多，但比波兰(Poland)的少
select name, population from world
where population >
(select population from world
where name='Canada') and
population<
(select population from world
where name='Poland')

select name, population 
from world
where population between
(select population 
from world
where name='Canada')+1 and
(select population 
from world
where name='Poland')-1;

#5.德国(Germany)在欧洲(Europe)國家的人口最多。奧地利(Austria)拥有德国总人口的11％
#查找欧洲的国家名称和每个国家的人口，其中人口以德国人口的百分比来显示人口数
select name,concat(round(population*100/(
select population from world where name = 'Germany')),'%')
as population 
from world where continent='Europe';

#6.哪些国家的GDP比欧洲(Europe)的全部国家都要高呢?  (有些国家的记录中，GDP是空值NULL，没有填入资料）
select name from world where gdp>
all(select gdp from world where continent='Europe'and gdp>0 );

#7.在每一个州中找出最大面积的国家，查找出洲、国家名字、面积。 (有些国家的记录中，面积是空值NULL，没有填入资料）
select continent,name,area from world as x
where area >= all
(select area from world as y
where x.continent = y.continent
and area>0);

#8.列出洲份名称和国家名称，其中每个洲只取出一个国家（条件：该国家排序在这个洲的首位）
select continent,name from world as x
where name <= all
(select name from world as y
where x.continent = y.continent);

#9.找出符合条件的洲和国家名称，条件：该洲中的全部国家人口都有少于或等于 25000000 人口）
select name,continent,population from world as x
where 25000000>=all
(select population from world as y
where  x.continent =y.continent);

#10.有些国家的人口是同洲份的所有其他国的3倍或以上。列出这些国家的名称和洲
select name,continent from world as x
where population/3 > all
(select population from world as y
where x.continent = y.continent and 
x.name <> y.name);

select name, continent 
from world as x
where population > all
(select 3*population 
from world as y
where y.continent=x.continent and x.name <> y.name);

---

第五部分：多表查询

1、表的加法

# 重复的行已并列
select 课程号,课程名称 from course1 
union
select 课程号,课程名称 from course2；

# 重复的行不并列
select 课程号,课程名称 from course1 
union all
select 课程号,课程名称 from course2

2、表的联结

#内联结 inner join = join
select a.学号,a.姓名,b.课程号,b.成绩
from 学生表 as a inner join 成绩表 as b
on a.学号 = b.学号;

select a.学号,a.姓名,b.课程号,b.成绩
from 学生表 as a join 成绩表 as b
on a.学号 = b.学号;

#左联结 left join
select a.学号,a.姓名,b.课程号,b.成绩
from 学生表 as a left join 成绩表  as b
on a.学号 = b.学号;

#left join左联结+去掉交集
select a.学号,a.姓名,b.课程号,b.成绩
from 学生表 as a left join 成绩表 as b
on a.学号 = b.学号
where b.学号 is null;

select a.学号,a.姓名,b.课程号,b.成绩
from 学生表 as a left join 成绩表 as b
on a.学号 = b.学号
where b.学号 = null;

#右联结 right join
select a.学号,a.姓名,b.课程号,b.成绩
from 学生表 as a right join 成绩表 as b
on a.学号 = b.学号;

#right join右联结+去掉交集
select a.学号,a.姓名,b.课程号,b.成绩
from 学生表 as a right join 成绩表 as b
on a.学号 = b.学号
where a.学号 is null;

# 查询所有学生的学号、姓名、选课数和总成绩
select a.学号,a.姓名,count(b.课程号)as 课程数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;

# 查询平均成绩大于85分的所有学生的学号、姓名和平均成绩
select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
having avg(b.成绩)>85;

# 查询学生的选课情况
select a.学号,a.姓名,c.课程号,c.课程名称
from student a inner join score b on a.学号 = b.学号
inner join course1 c on b.课程号 = c.课程号;

3、case表达式

select 学号,课程号,成绩,
(case when 成绩 >= 60 then '及格'
when 成绩 < 60 then '不及格'
else null
end) as '是否及格'
from score;

#查询出每门课程的及格人数和不及格人数
select 课程号,
sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
sum(case when 成绩>=60 then 0 else 1 end) as 不及格人数
from score 
group by 课程号;

select 课程号,
sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
sum(case when 成绩<60 then 1 else 0 end) as 不及格人数
from score 
group by 课程号;

/* 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩
分别统计 各分数段人数，课程号和课程名称 */
select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when 成绩 <= 85 and 成绩 >70 then 1 else 0 end) as '[85-70]',
sum(case when 成绩 <=70 and 成绩 >60 then 1 else 0 end) as '[70-60]',
sum(case when 成绩<60 then 1 else 0 end)as '[<60]'
from score as a right join course1 as b
on a.课程号=b.课程号
group by a.课程号,b.课程名称

多表查询总结：

第五部分：多表查询总结

sqlzoo平台练习：

1、https://sqlzoo.net/wiki/The_JOIN_operation/zh

#1. 在进球表（goal）中查找德国球队（teamid = 'GER'）进球的比赛编号（matchid），进球球员姓名（player）
select matchid,player from goal where teamid = 'GER';

#2. 在比赛信息表（game）查找比赛编号1012的信息
select id, stadium, team1, team2 from game where id = '1012';

#3. 查找德国队进球球员姓名，球队编号（在进球信息表goal）, 比赛地点,比赛日期（在比赛信息表game）
select b.player,b.teamid,a.stadium,a.mdate
from game as a inner join goal as b on a.id = b.matchid
where b.teamid = 'GER';

#4. 查找姓名中以Mario开头的进球球员，符合条件球员参加比赛的对战双方
select a.team1,a.team2,b.player
from game as a inner join goal as b on a.id = b.matchid
where b.player like 'Mario%';

#5. 查找进球球员的姓名、球队编号、教练、多长时间进球。要求多长时间进球<=10分钟
select b.player,b.teamid,c.coach,b.gtime
from goal as b inner join eteam as c on b.teamid = c.id
where b.gtime<=10;

#6. 'Fernando Santos'作为教练的比赛日期，球队编号有哪些？
select a.mdate,c.teamname 
from game as a inner join eteam as c on a.team1 = c.id
where c.coach = 'Fernando Santos';

#7. 在比赛地点'National Stadium, Warsaw'有哪些进球球员？
select b.player
from game as a inner join goal as b on a.id=b.matchid
where a.stadium = 'National Stadium, Warsaw';

#8.射入德国球门的球员姓名
select distinct b.player
from game as a inner join goal as b on a.id = b.matchid
where (a.team1 = b.teamid and a.team2 = 'GER' )or
(a.team2 = b.teamid and a.team1 = 'GER');
#在这里容易把distinct漏掉

#9. 查找出球队名称，和每个球队进球人数
select c.teamname,count(b.teamid)
from goal as b inner join eteam as c on b.teamid = c.id
group by c.teamname;

#10. 查找出所有比赛地点，每个比赛地点的进球数
select a.stadium,count(a.stadium)
from game as a inner join goal as b on a.id=b.matchid
group by a.stadium;

#11. 查找出有波兰球队'POL'参加的比赛编号，比赛日期，对应这场比赛的进球数
select b.matchid,a.mdate,count(b.matchid)
from game as a left join goal as b on a.id = b.matchid
where a.team1 = 'POL' or a.team2 = 'POL'
group by b.matchid,a.mdate;

#12. 对于德国队'GER'得分的每场比赛，显示比赛编号，比赛日期和'GER'得分的进球数
select b.matchid,a.mdate,count(b.matchid)
from game as a left join goal as b on a.id = b.matchid
where b.teamid = 'GER'
group by b.matchid,a.mdate;

select a.id,a.mdate,count(b.player)
from game as a inner join goal as b on a.id = b.matchid
where b.teamid='GER'
group by a.id,a.mdate;

#13. 查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数）
select a.mdate,a.team1,
sum(case when b.teamid = a.team1 then 1 else 0 end) as score1,
a.team2, 
sum(case when b.teamid = a.team2 then 1 else 0 end) as score2
from game as a left join goal as b on a.id = b.matchid
group by a.id,a.mdate,a.team1,a.team2;

---