可能是史上最适合入门SQL语句的教程——自学SQL网学习笔记

最新推荐文章于 2024-09-18 14:51:26 发布
最新推荐文章于 2024-09-18 14:51:26 发布
阅读量1.7k 收藏 10
点赞数 1
分类专栏： SQL 文章标签： 数据库 大数据 sql mysql ai
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35547281/article/details/113476897
版权

自学SQL网Note

学习网址：http://xuesql.cn/

表格、题目和知识点采集于自学SQL网，这个网站提供直接练习SQL的页面，免去了安装MySQL和导入表格的繁琐步骤，非常推荐初学者学习！

部分答案参考：https://blog.csdn.net/Xemacil/article/details/107086456

因为现在网站删掉了部分题目，我根据上面的博客补充了之前的题目，但是否准确就无法验证了。

本文除了整理提供了网站的答案外，还写入了部分从的题目中得到的思考和总结，适合需要初步学习SQL的朋友。

SQL Lesson 1: SELECT 查询 101

IdTitleDirectorYearLength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoFinding Nemo2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110

  1. 找到所有电影的名称Title
    SELECT Title FROM Movies;

  2. 找到所有电影的导演
    SELECT Director FROM Movies;

  3. 找到所有电影的名称和导演
    SELECT Title,Director FROM Movies;

  4. 找到所有电影的名称和上映年份
    SELECT Title,Year FROM Movies;

  5. 找到所有电影的所有信息
    SELECT * FROM Movies;

  6. 找到所有电影的名称,Id和播放时长
    SELECT Title,Id,Length_minutes FROM Movies;

  7. 请列出所有电影的Id,名称和出版国(即美国)
    SELECT Id,Title,“美国” as Country FROM Movies;

    note:这里再Country列加入“美国”这个条件，从而简化了后续增加WHERE的语法量

总结：

主要是

SELECT * from 表名的应用

SQL Lesson 2: 条件查询 (constraints) (Pt. 1)

IdTitleDirectorYearLength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoFinding Nemo2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110

  1. 找到Id为6的电影
    SELECT * FROM Movies WHERE Id = 6;

  2. 找到在2000-2010年间Year上映的电影
    SELECT * FROM Movies WHERE Year BETWEEN 2000 AND 2010;

  3. 找到不是在2000-2010年间Year上映的电影
    SELECT * FROM Movies WHERE Year not BETWEEN 2000 AND 2010;

  4. 找到头5部电影
    SELECT * FROM Movies LIMIT 5;

    note: 详见LIMIT方法

  5. 找到2010（含）年之后的电影里片长小于两个小时的片子
    SELECT * FROM Movies WHERE Year >=2010 AND Length_minutes < 120;

  6. 找到99年和09年的电影,只要列出年份和片长看下
    SELECT Year,Length_minutes FROM Movies WHERE Year =1999 or Year =2009;

补充：

LIMIT方法

LIMIT语句用于限制select语句返回的行数

主要有两个参数：LIMIT 和 offset

SELECT 
    column_list
FROM
    table1
ORDER BY column_list
LIMIT row_count OFFSET offset;
SQL

在这个语法中，

  • row_count确定将返回的行数。
  • OFFSET子句在开始返回行之前跳过偏移行。 OFFSET子句是可选的。 如果同时使用LIMIT和OFFSET子句，OFFSET会在LIMIT约束行数之前先跳过偏移行。

row_count是限制一共返回多少行

offset是从上到下跳过多少行开始

LIMIT 1 offset 1

就是取第二行

LIMIT 5 offset 3

就是从第四行开始取五行

总结：

这里讲了几种简单的条件查询方法

Operator（关键字）Condition（意思）SQL Example(例子）
=, !=, < <=, >, >=Standard numerical operators 基础的 大于，等于等比较col_name != 4
BETWEEN … AND …Number is within range of two values (inclusive) 在两个数之间col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND …Number is not within range of two values (inclusive) 不在两个数之间col_name NOT BETWEEN 1 AND 10
IN (…)Number exists in a list 在一个列表col_name IN (2, 4, 6)
NOT IN (…)Number does not exist in a list 不在一个列表col_name NOT IN (1, 3, 5)

可以用 AND or OR 这两个关键字来组装多个条件（表示并且，或者）

(ie. num_wheels >= 4 AND doors <= 2 这个组合表示 num_wheels属性 大于等于 4 并且 doors 属性小于等于 2)

SQL Lesson 3: 条件查询(constraints) (Pt. 2)

IdTitleDirectorYearLength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoFinding Nemo2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110
  1. 找到所有Toy Story系列电影
    SELECT * FROM Movies WHERE Title LIKE “%Toy Story%”;
  2. 找到所有John Lasseter导演的电影
    SELECT * FROM Movies WHERE Director LIKE “John Lasseter%”;
  3. 找到所有不是John Lasseter导演的电影
    SELECT * FROM Movies WHERE Director not LIKE “John Lasseter%”;
  4. 找到所有电影名为 “WALL-” 开头的电影
    SELECT * FROM Movies WHERE Title LIKE “%Wall%”;
  5. 有一部98年电影中文名《虫虫危机》请给我找出来
    SELECT * FROM Movies WHERE Year =1998;
  6. 找出所有Pete导演的电影,只要列出电影名,导演名和年份就可以
    SELECT Title,Director,Year FROM Movies WHERE Director LIKE “%Pete%”
  7. John Lasseter导演了两个系列，一个Car系列一个Toy Story系列,请帮我列出这John Lasseter导演两个系列千禧年之后(含千禧年)的电影
    SELECT * FROM Movies WHERE Director="John Lasseter"AND Year>= 2000

总结：

Operator（操作符）Condition（解释）Example（例子）
=Case sensitive exact string comparison (notice the single equals)完全等于col_name = “abc”
!= or <>Case sensitive exact string inequality comparison 不等于col_name != “abcd”
LIKECase insensitive exact string comparison 没有用通配符等价于 =col_name LIKE “ABC”
NOT LIKECase insensitive exact string inequality comparison 没有用通配符等价于 !=col_name NOT LIKE “ABCD”
%Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符col_name LIKE “%AT%” (matches “AT”, “ATTIC”, “CAT” or even “BATS”) “%AT%” 代表AT 前后可以有任意字符
_Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似，代表1个字符col_name LIKE “AN_” (matches “AND”, but not “AN”)
IN (…)String exists in a list 在列表col_name IN (“A”, “B”, “C”)
NOT IN (…)String does not exist in a list 不在列表col_name NOT IN (“D”, “E”, “F”)

LIKE + 通配符对条件进行模糊匹配

=是对条件进行精准匹配，用LIKE可以模糊匹配

通配符%代表匹配0个以上的任意字符

通配符_代表1个任意字符

SQL Lesson 4: 查询结果Filtering过滤 和 sorting排序

Table: Movies (Read-Only)

IdTitleDirectorYearLength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoFinding Nemo2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110
  1. 按导演名排重列出所有电影(只显示导演)，并按导演名正序排列
    SELECT DISTINCT Director FROM Movies ORDER BY Director;
  2. 列出按上映年份最新上线的4部电影
    SELECT * FROM Movies ORDER BY Year DESC LIMIT 4;
  3. 按电影名字母序升序排列，列出前5部电影
    SELECT * FROM Movies ORDER BY Title ASC LIMIT 5;
  4. 按电影名字母序升序排列，列出上一题之后的5部电影
    SELECT * FROM Movies ORDER BY Title ASC LIMIT 5 offset 5;
  5. 如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可
    SELECT Title FROM Movies WHERE Director=“John Lasseter” ORDER BY Length_minutes DESC LIMIT 1 offset 2
  6. 按导演名字母升序,如果导演名相同按年份降序,取前10部电影给我
    SELECT * FROM Movies ORDER BY Director ASC,Year DESC LIMIT 10;

总结：

1、WHERE/ORDER BY/LIMIT OFFSET要按这个顺序来写

2、ORDER BY的降序是DESC

3、DISTINCT是将该列去重

SQL Review: 复习 SELECT 查询

Table: North_american_cities (Read-Only)

CityCountryPopulationLatitudeLongitude
GuadalajaraMexico150080020.659699-103.349609
TorontoCanada279506043.653226-79.383184
HoustonUnited States219591429.760427-95.369803
New YorkUnited States840583740.712784-74.005941
PhiladelphiaUnited States155316539.952584-75.165222
HavanaCuba210614623.05407-82.345189
Mexico CityMexico855550019.432608-99.133208
PhoenixUnited States151336733.448377-112.074037
Los AngelesUnited States388430734.052234-118.243685
Ecatepec de MorelosMexico174200019.601841-99.050674
MontrealCanada171776745.501689-73.567256
ChicagoUnited States271878241.878114-87.629798

​ 1.列出所有加拿大人的Canadian信息(包括所有字段)
​ SELECT * FROM North_american_cities WHERE Country=“Canada”;

​ 2.列出所有美国United States的城市按纬度从北到南排序(包括所有字段)

​ SELECT * FROM North_american_cities WHERE Longitude < ‘-87.629798’ ORDER BY Longitude ASC;

​ --SELECT * FROM North_american_cities WHERE Longitude < (SELECT Longitude FROM North_american_cities WHERE City = ‘Chicago’) ORDER BY Longitude;

​ 3.列出所有在Chicago西部的城市，从西到东排序(包括所有字段)

​ SELECT * FROM North_american_cities WHERE Longitude<-87.629798 ORDER BY Longitude ASC;

​ 4.用人口数Population排序,列出墨西哥Mexico最大的2个城市(包括所有字段)

​ SELECT * FROM North_american_cities WHERE Country = ‘Mexico’ ORDER BY Population DESC LIMIT 2;

​ 5.列出美国United States人口3-4位的两个城市和他们的人口(包括所有字段)
​ SELECT * FROM North_american_cities WHERE Country=‘United States’ ORDER BY Population DESC LIMIT 2 offset 2;

​ 6.北美所有城市,请按国家名字母序从A-Z再按人口从多到少排列看下前10位的城市(包括所有字段)
​ SELECT * FROM North_american_cities ORDER BY Country ASC,Population DESC LIMIT 10;

总结：

这节没啥好总结的，单表查询的基本操作看之前的就可以。

SQL Lesson 6: 用JOINs进行多表联合查询

Table: Movies (Read-Only)

IdTitleDirectorYearLength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoFinding Nemo2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110

Table: Boxoffice (Read-Only)

Movie_idRatingDomestic_salesInternational_sales
58.2380843261555900000
147.4268492764475066843
88206445654417277164
126.4191452396368400000
37.9245852179239163000
68261441092370001000
98.5223808164297503696
118.4415004880648167031
18.3191796233170162503
77.2244082982217900167
108.3293004164438338580
48.1289916256272900000
27.2162798565200600000
137.2237283207301700000

  1. 找到所有电影的国内Domestic_sales和国际销售额
    SELECT * FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id;

  2. 找到所有国际销售额比国内销售大的电影
    SELECT * FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id WHERE demostic_sales < International_sales;

  3. 找出所有电影按市场占有率Rating倒序排列
    SELECT * FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id ORDER BY Rating ASC;

  4. 每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少
    SELECT Director,International_sales FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id ORDER BY International_sales LIMIT 1;

    这个答案不对！

    自己写的：SELECT Director, International_sales FROM Movies INNER JOIN Boxoffice On Movies.Id = Boxoffice.Movie_id GROUP BY Director ORDER BY International_sales DESC LIMIT 1;

    要先GROUP BY一下把International_sales加起来然后再排序

总结：

用JOINs进行多表联合查询

主键(primary key), 一般关系数据表中，都会有一个属性列设置为 主键(primary key)。主键是唯一标识一条数据的，不会重复复（想象你的身份证号码)。一个最常见的主键就是auto-incrementing integer(自增Id，每写入一行数据Id+1, 当然字符串，hash值等只要是每条数据是唯一的也可以设为主键.

借助主键(primary key)（当然其他唯一性的属性也可以），我们可以把两个表中具有相同 主键Id的数据连接起来（因为一个Id可以简要的识别一条数据，所以连接之后还是表达的同一条数据）（你可以想象一个左右连线游戏）。具体我们用到 JOIN 关键字。我们先来学习 INNER JOIN.

用INNER JOIN 连接表的语法

SELECT column, another_table_column, … FROM mytable （主表） 
INNER JOIN another_table （要连接的表）    
ON mytable.Id = another_table.Id (想象一下刚才讲的主键连接，两个相同的连成1条)
WHERE condition(s) ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

通过ON条件描述的关联关系;INNER JOIN 先将两个表数据连接到一起. 两个表中如果通过Id互相找不到的数据将会舍弃。此时，你可以将连表后的数据看作两个表的合并，SQL中的其他语句会在这个合并基础上 继续执行（想一下和之前的单表操作就一样了）.
还有一个理解INNER JOIN的方式，就是把 INNER JOIN 想成两个集合的交集。

img

SQL Lesson 7: 外连接（OUTER JOINs）

Table: Employees (Read-Only)

RoleNameBuildingYears_employed
EngineerBecky A.1e4
EngineerDan B.1e2
EngineerSharon F.1e6
EngineerDan M.1e4
EngineerMalcom S.1e1
ArtistTylar S.2w2
ArtistSherman D.2w8
ArtistJakob J.2w6
ArtistLillia A.2w7
ArtistBrandon J.2w7
ManagerScott K.1e9
ManagerShirlee M.1e3
ManagerDaria O.2w6
EngineerYancy I.null0
ArtistOliver P.null0

Table: Buildings (Read-Only)

Building_nameCapacity
1e24
1w32
2e16
2w20

  1. 找到所有有雇员的办公室(buildings)名字
    SELECT DISTINCT Building FROM Employees WHERE Building is not null;

  2. 找到所有办公室和他们的最大容量
    SELECT * FROM buildings;

  3. 找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)
    SELECT DISTINCT buildings.Building_name,Employees.Role FROM buildings LEFT JOIN Employees on Employees.Building=buildings.Building_name;

    自己写的：SELECT DISTINCT Building_name, Role FROM Buildings LEFT JOIN Employees On Buildings.Building_name = Employees.Building;

  4. 找到所有有雇员的办公室(buildings)和对应的容量
    SELECT DISTINCT Building,capacity FROM Employees LEFT JOIN buildings on Employees.Building=buildings.Building_name WHERE Employees.Building is not null;

总结：

INNER JOIN 只会保留两个表都存在的数据（还记得之前的交集吗），这看起来意味着一些数据的丢失，在某些场景下会有问题.

真实世界中两个表存在差异很正常，所以我们需要更多的连表方式，也就是本节要介绍的左连接LEFT JOIN,右连接RIGHT JOIN 和 全连接FULL JOIN. 这几个 连接方式都会保留不能匹配的行。

用LEFT/RIGHT/FULL JOINs 做多表查询

SELECT column, another_column, … FROM mytable 
INNER/LEFT/RIGHT/FULL JOIN another_table
ON mytable.Id = another_table.matching_id
WHERE condition(s) ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

和INNER JOIN 语法几乎是一样的. 我们看看这三个连接方法的工作原理：
在表A 连接 B, LEFT JOIN保留A的所有行，不管有没有能匹配上B 反过来 RIGHT JOIN则保留所有B里的行。最后FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行

！也就是说只要On 后面的条件两边都能完全对应，那么JOIN/LEFT JOIN/RIGHT JOIN都是一样的

我们还是可以用集合的图示来描述：
LEFT JOIN img
RIGHT JOIN img
FULL JOIN img

将两个表数据1-1连接，保留A或B的原有行，如果某一行在另一个表不存在，会用 NULL来填充结果数据。所有在用这三个JOIN时，你需要单独处理 NULL. 关于 NULL 下一节会做更详细的说明

哪一列是唯一且不重复的就以它为左连的第一个表

SQL Lesson 8: 关于特殊关键字 NULLs

Table: Employees (Read-Only)

RoleNameBuildingYears_employed
EngineerBecky A.1e4
EngineerDan B.1e2
EngineerSharon F.1e6
EngineerDan M.1e4
EngineerMalcom S.1e1
ArtistTylar S.2w2
ArtistSherman D.2w8
ArtistJakob J.2w6
ArtistLillia A.2w7
ArtistBrandon J.2w7
ManagerScott K.1e9
ManagerShirlee M.1e3
ManagerDaria O.2w6
EngineerYancy I.null0
ArtistOliver P.null0

Table: Buildings (Read-Only)

Building_nameCapacity
1e24
1w32
2e16
2w20

  1. 找到雇员里还没有分配办公室的(列出名字和角色就可以)
    SELECT Name,Role FROM Employees WHERE Building is null;

    自己的：SELECT Name, Role FROM Employees WHERE Building is null;

  2. 找到还没有雇员的办公室
    SELECT Building_name FROM Buildings LEFT JOIN Employees on Buildings.Building_name = Employees.Building WHERE Name is null;

    自己的：SELECT Building_name FROM Buildings LEFT JOIN Employees On Buildings.Building_name = Employees.Building WHERE Building is null;

总结：

先不要想着一步到位，SELECT的部分可以先用*，等结果出来之后再去选列

SQL Lesson 9: 在查询中使用表达式

Table: Movies (Read-Only)

IdTitleDirectorYearLength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoFinding Nemo2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110

Table: Boxoffice (Read-Only)

Movie_idRatingDomestic_salesInternational_sales
58.2380843261555900000
147.4268492764475066843
88206445654417277164
126.4191452396368400000
37.9245852179239163000
68261441092370001000
98.5223808164297503696
118.4415004880648167031
18.3191796233170162503
77.2244082982217900167
108.3293004164438338580
48.1289916256272900000
27.2162798565200600000
137.2237283207301700000
  1. 列出所有的电影Id,名字和销售总额(以百万美元为单位计算)
    SELECT Id,Title,(Domestic_sales+International_sales)/1000000 as “销售总额” FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id;
  2. 列出所有的电影Id,名字和市场指数(Rating的10倍为市场指数)
    SELECT Id,Title,Rating*10 as “市场指数” FROM Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id;
  3. 列出所有偶数年份的电影，需要电影Id,名字和年份
    SELECT Id,Title,Year from Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id WHERE Year%2=0;
  4. John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
    SELECT Title,(Domestic_sales+International_sales)/Length_minutes as “价值” from Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id WHERE Director = “Jhon Lasseter” ORDER BY “价值” LIMIT 3;
  5. 电影名最长的3部电影和他们的总销量是多少
    SELECT，length(Title) as title_len,Title,(Domestic_sales + International_sales) as “总销量” from Movies LEFT JOIN Boxoffice on Movies.Id = Boxoffice.Movie_id ORDER BY title_len DESC LIMIT 3;

自己的答案：

  1. SELECT Id, Title, (Domestic_sales + International_sales)/1000000 as ‘销售总额’ FROM Movies LEFT JOIN Boxoffice On Movies.Id = Boxoffice.movie_id;
  2. SELECT Id, Title,(Rating * 10) AS ‘市场指数’ FROM Movies LEFT JOIN Boxoffice On Movies.Id = Boxoffice.Movie_id;
  3. SELECT Id, Title, Year FROM Movies WHERE Year&1 = 0;
  4. SELECT Title, (Domestic_sales + International_sales)/Length_minutes AS ‘价值’ FROM Movies LEFT JOIN Boxoffice On Movies.Id = Boxoffice.Movie_id WHERE Director = ‘John Lasseter’ ORDER BY 价值 DESC LIMIT 3;

总结：

mysql判断奇数偶数，效率按顺序

– 按位与

select * from cinema WHERE Id&1;

– Id先除以2然后乘2 如果与原来的相等就是偶数

select * from cinema WHERE Id=(Id>>1)<<1;

– Id计算

select * from cinema WHERE Id%2 = 1;
select * from cinema WHERE Id%2 = 0;

– 与上面的一样

select * from cinema WHERE mod(Id, 2) = 1;
select * from cinema WHERE mod(Id, 2) = 0;

– -1的奇数次方和偶数次方

select * from cinema WHERE POWER(-1, Id) = -1;
select * from cinema WHERE POWER(-1, Id) = 1;

– 正则匹配最后一位

select * from cinema WHERE Id regexp '[13579]$';
select * from cinema WHERE Id regexp '[02468]$';

SQL Lesson 10: 在查询中进行统计I (Pt. 1)

Table(表）: Employees

RoleNameBuildingYears_employed
EngineerBecky A.1e4
EngineerDan B.1e2
EngineerSharon F.1e6
EngineerDan M.1e4
EngineerMalcom S.1e1
ArtistTylar S.2w2
ArtistSherman D.2w8
ArtistJakob J.2w6
ArtistLillia A.2w7
ArtistBrandon J.2w7
ManagerScott K.1e9
ManagerShirlee M.1e3
ManagerDaria O.2w6
EngineerYancy I.null0
ArtistOliver P.null0

  1. 找出就职年份最高的雇员(列出雇员名字+年份）
    SELECT Name,MAX(Years_employed) FROM Employees;

    自己写的：

    SELECT Name, Years_employed FROM Employees ORDER BY Years_employed DESC LIMIT 1;

  2. 按角色(Role)统计一下每个角色的平均就职年份
    SELECT Role, AVG(Years_employed) FROM Employees GROUP BY Role;

  3. 按办公室名字总计一下就职年份总和
    SELECT Building, SUM(Years_employed) FROM Employees GROUP BY Building;

  4. 每栋办公室按人数排名,不要统计无办公室的雇员
    SELECT Building, Count(Name) FROM Employees WHERE Building is not NULL GROUP BY Building;

    SELECT Building, Count(Name) FROM Employees GROUP BY Building HAVING Building is not NULL;

    Note:Count(Name)换成Count(*)也可以

  5. 就职1,3,5,7年的人分别占总人数的百分比率是多少(给出年份和比率"50%" 记为 50)
    SELECT Years_employed, Count() * 100/(select count() FROM Employees) AS Rating FROM Employees WHERE Years_employed in (1,3,5,7) GROUP BY Years_employed;

总结：

对全部结果数据做统计的SQL格式

SELECT AGG_FUNC(\column_or_expression\) AS aggregate_description, … 
FROM mytable 
WHERE constraint_expression;

下面介绍几个常用统计函数:

FunctionDescription
COUNT(*), COUNT(column)计数！COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(column)找column最小的一行.
**MAX(**column)找column最大的一行.
**AVG(**column)对column所有行取平均值.
SUM(column)对column所有行求和.

注意：

GROUP BY 之后在SELECT 后使用统计函数是对分组后的每组做这些统计运算

SQL Lesson 11: 在查询中进行统计II (Pt. 2)

Table(表）: Employees

RoleNameBuildingYears_employed
EngineerBecky A.1e4
EngineerDan B.1e2
EngineerSharon F.1e6
EngineerDan M.1e4
EngineerMalcom S.1e1
ArtistTylar S.2w2
ArtistSherman D.2w8
ArtistJakob J.2w6
ArtistLillia A.2w7
ArtistBrandon J.2w7
ManagerScott K.1e9
ManagerShirlee M.1e3
ManagerDaria O.2w6
EngineerYancy I.null0
ArtistOliver P.null0

  1. 统计一下Artist角色的雇员数量
    SELECT Count(*) FROM Employees WHERE Role = ‘Artist’;

  2. 按角色统计一下每个角色的雇员数量
    SELECT Role, Count(*) FROM Employees GROUP BY Role;

  3. 算出Engineer角色的就职年份总计
    SELECT SUM(Years_employed) FROM Employees WHERE Role = ‘Engineer’;

    题目要求用分组，但我觉得速度应该会变慢

    SELECT SUM(Years_employed) FROM Employees GROUP BY Role HAVING Role = ‘Engineer’;

  4. 每栋办公室按人数排名,不要统计无办公室的雇员
    SELECT count(*) as count,Role,building is not null as bn FROM employees group by Role,bn;

  5. 就职1,3,5,7年的人分别占总人数的百分比率是多少(给出年份和比率"50%" 记为 50)
    SELECT Role,Years_employed/3 as year_3,count(*) as count FROM employees group by Role,year_3 order by count desc;

总结：

GROUP BY其实是可以group by 多列的，相当于对遍历这些列的所有情况

比如说col1有0，1两种情况，col2有0，1两种情况

那如果group by col1,col2，那就是按（0，0），（0，1），（1，0），（1，1）四种情况来分

col1col2result
000
011
101
110

SQL Lesson 12: 查询执行顺序

Table: Movies (Read-Only)

IdTitleDirectorYearLength_minutes
1Toy StoryJohn Lasseter199581
2A Bug’s LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoFinding Nemo2003107
6The IncrediblesBrad Bird2004116
7CarsJohn Lasseter2006117
8RatatouilleBrad Bird2007115
9WALL-EAndrew Stanton2008104
10UpPete Docter2009101
11Toy Story 3Lee Unkrich2010103
12Cars 2John Lasseter2011120
13BraveBrenda Chapman2012102
14Monsters UniversityDan Scanlon2013110

Table: Boxoffice (Read-Only)

Movie_idRatingDomestic_salesInternational_sales
58.2380843261555900000
147.4268492764475066843
88206445654417277164
126.4191452396368400000
37.9245852179239163000
68261441092370001000
98.5223808164297503696
118.4415004880648167031
18.3191796233170162503
77.2244082982217900167
108.3293004164438338580
48.1289916256272900000
27.2162798565200600000
137.2237283207301700000

  1. 统计出每一个导演的电影数量（列出导演名字和数量）
    SELECT Director,Count(*) FROM Movies Group by Director;

  2. 统计一下每个导演的销售总额(列出导演名字和销售总额)
    SELECT Director, SUM(Domestic_sales+International_sales) AS ‘销售总额’ FROM Movies Left Join Boxoffice On Movies.Id = Boxoffice.Movie_id GROUP BY Director;

  3. 按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)
    SELECT director,sum(Domestic_sales + International_sales) AS sum_sales,count(director),sum(Domestic_sales + International_sales)/count(director) AS avg_sales FROM movies LEFT JOIN boxoffice ON movies.id = boxoffice.movie_id group by director having count(director) > 1 ORDER BY avg_sales DESC LIMIT 1

    –SELECT Director, SUM(Domestic_sales+International_sales) AS ‘总销量’, Count() AS ‘电影数量’, SUM(Domestic_sales+International_sales)/Count() AS ‘平均销量’ FROM Movies Left Join Boxoffice On Movies.Id = Boxoffice.Movie_id GROUP BY Director HAVING Count() > 1 ORDER BY SUM(Domestic_sales+International_sales)/Count() DESC LIMIT 1;

    note:用中文名的话不可以直接用AS的列名来操作

  4. 找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额
    select title ,(select max(international_sales+domestic_sales) from boxoffice)-(international_sales+domestic_sales) AS Margin from movies left join boxoffice on movies.id=boxoffice.movie_id;

    SELECT Title, ((SELECT (Domestic_sales + International_sales) FROM Movies Left Join Boxoffice On Movies.Id = Boxoffice.Movie_id ORDER BY (Domestic_sales + International_sales) DESC LIMIT 1 ) - (Domestic_sales + International_sales))AS Rest FROM movies LEFT JOIN boxoffice ON movies.id = boxoffice.movie_id;

总结：

按这个顺序来写，注意顺序不能颠倒，否则会报错！

SELECT DISTINCT column, AGG_FUNC(*column_or_expression*),
… FROM mytable
JOIN another_table ON mytable.column = another_table.column    
WHERE constraint_expression
GROUP BY column
AVING constraint_expression    
ORDER BY *column* ASC/DESC    
LIMIT count OFFSET COUNT;
Seanlocked
关注
专栏目录
SQL学习笔记——REGEXP运算符
qq_46416934的博客
07-31 6152
REGEXP运算符，是正则表达式（regularexpression）的缩写，正则表达式在搜索字符串时非常强大，下面是关于它的应用1.查找名字中包含field的顾客运用REGEXP运算符，可以这样写，同样可以得出数据2.查找姓氏以Brush开头的顾客^表示查找的字符串必须以什么开头3.查找姓氏以field结尾的顾客$表示查找的字符串必须以什么结尾3.查找姓氏包含field或者mac的顾客|符号表示多个搜寻模式4.查找以brush开头，或者包含mac，或者包含rose的顾客。...
初始SQL及基本功能的实现——在b站学习SQL（一）
lichensun的博客
11-13 314
b站学习SQL系列（一）
SQL语句教程简洁入门版
04-22
一份简洁的SQL教程，适合刚入门数据库、java、及其他语言编程的菜鸟，祝大家学习进步、学有所成！
SQL入门教程
qq_44624290的博客
06-04 1289
你是一个普普通通的人力，每天面对的就是一张张的员工信息表做一些简单的统计工作，大部分的工作只是简单的表格处理，甚至连宏都不需要，虽然有时候需要用一些笨方法，但是工作还是可以完成的。但是突然有一天，你的老板突发奇想，觉得员工入职的都是好日子，打算只要员工入职了就去买股票去，然后扔给了你了一个表格，里面有着20年的股票数据，让你去算一下只要员工入职了就去买股票，当天是涨的多还是跌的多你要怎么办，你只能无奈的去翻找每个员工入职的日子，然后再去看看当天的股票是不是开盘，然后再算出来是涨还是跌。
SQL语言入门（非常详细）零基础入门到精通，收藏这一篇就够了
最新发布
shanguicsdn000的博客
09-18 2578
一、SQL 概述1、什么是 SQLSQL 指结构化查询语言，全称是 Structured Query Language。SQL 让您可以访问和处理数据库，包括数据插入、查询、更新和删除。SQL 在1986年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。2、SQL语言的版本历史。
初学者怎样快速学会 SQL
IT邦德
05-18 4284
学SQL打算做什么？
SQL 教程
yuanyuan_186的专栏
04-12 544
 SQL 教程 http://www.w3school.com.cn/sql/index.asp
【SQL入门】最全SQL基础知识总结（理论+举例+建议）
热门推荐
qq_39172059的博客
03-06 1万+
数字型（int，tinyint，decimal，float等）、日期和时间类型（Date，TIME等）、字符串类型（char，varchar等）。：于1992年发布，这个版本通过引入新的特性进一步扩展了SQL语言的能力，包括多表连接、子查询、视图和存储过程等，是目前获得最广泛支持的版本。：为表设置适当的表别名，不仅可以减少SQL语句的复杂度，也能尽量避免出错，让SQL语句更加的直观、易理解。：在1989年发布，这个版本引入了更多的特性，包括排序、分组和连接操作的支持。
SQL学习全笔记
12-22
总的来说，这份"SQL学习全笔记"涵盖了从基础到高级的SQL知识，包括数据库管理、查询技巧、函数应用以及事务处理等内容。无论你是刚接触SQL的新手，还是希望巩固提升的开发者，都能从中受益。通过深入学习和实践，你...
ORACLESQL自学1.ppt
11-23
ORACLE SQL 自学笔记是学习 ORACLE SQL 的入门教程，旨在帮助读者快速了解 ORACLE SQL 的基本知识和使用方法。本笔记涵盖了 SELECT 语句的基本用法、SQL 语言和 SQL*Plus 命令的不同、算术运算符的使用、操作符...
【自学SQL】sqlzoo学习笔记二 round函数、选择语句case
mmyx_matlab的博客
04-29 272
官网链接：https://igs.sqlzoo.net/ 第三部分 SELECT FROM WORLD 这部分前面有些是对之前学习的一些回顾，就不全部放出来啦，然后依旧是select语句的应用，数据还是world数据。 1、从【world】数据中筛选出【name】列，【population】列和【gdp】列，其中【population】列以百万为单位，【gdp】列以十亿为单位，保留两位小数。 使用round函数： 例：ROUND(7253.86, 0) -> 7254 ROUND(7253.
非常好的sql语句入门教程
12-13
非常好的sql语句入门教程，我也是靠它入门的，简单易学。
SQL非常全面的入门教程
03-29
SQL的入门教程，初学SQL的不可错过哦……
SQL_入门新手教程
03-31
SQL_入门新手教程
SQL入门新手教程.pdf
09-30
SQL入门新手教程.pdf
SQL入门教程。适合新手学习
04-14
SQL的入门级教程。包含SQL语法。很详尽的教学说明
初识SQL（新手入门）
Hiuyisha的博客
06-14 2781
SQL初读关键字
SQL教程
逆袭的菜鸟X
05-06 1103
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。INSERT INTO 语句用于向表中插入新记录。WHERE 子句用于提取那些满足指定条件的记录。UPDATE 语句用于更新表中已存在的记录。DELETE 语句用于删除表中的行。
SQL 入门教程
梦旅人的博客
06-12 1593
通过一些简单的步骤和例子，希望看到这篇文章的你能够更好地理解SQL的基础知识，并开始使用它来管理和查询数据库。
SQL自学教程：深入理解USE、U和USE_SQL指令
资源摘要信息: "SQL_USE.zip_sql u_sql use_...综上所述，这份SQL自学教程是初学者入门和提高SQL技能的宝贵资源。通过合理使用该教程，结合实践操作，学习者将能够掌握SQL的基本知识，并在数据库管理领域中取得进步。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值