前言

模块

知识点分为六部分

  1. 单个表
  2. 多个表
  3. 分类筛选
  4. 命令的顺序
  5. 表格内的操作
  6. 对表格的操作

前三部分在(1),其他在(2),每部分有包含的对应的操作命令,

此笔记主要是记录相应命令的代码格式,关键词。

由于时间短暂,应该会有理解不到位的地方。

目的

当时脑子一热加入学校信息化处工作,本来以为可以划水赚点零花钱,,,结果emmm
从昨天晚上到今天中午,过了一遍数据库知识,难度还行但是知识很杂,很多命令一次记不住。

用这个网站来学习的,很棒,有知识和对应的习题。

声明下~下面的数据信息举例以及表格信息都源自链接网站。

这是学习时做的笔记,以便于以后时间不够来迅速查找。

PATR ONE 单个表

以此数据为例Table : Movies

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

Step 1 :Select queries

find the title of each film

SELECT title FROM movies;

find 两个以上用 ',' 间隔

SELECT title,year FROM movies;

find 全部

SELECT * from movies;

Step 2 :Select with constraints

格式:

SELECT ....... FROM ....... WHERE .......

对数据的常用判断:

OperatorConditionSQL Example
=, !=, < <=, >, >=Standard numerical operatorscol_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 listcol_name IN (2, 4, 6)
NOT IN (…)Number does not exist in a list

Example : Find the movies not released in the years between 2000 and 2010

SELECT title, year FROM movies
WHERE year < 2000 OR year > 2010;

对字符串的常用判断:

OperatorConditionExample
=Case sensitive exact string comparison (notice the single equals)col_name = "abc"
!= or <>Case sensitive exact string inequality comparisoncol_name != "abcd"
LIKECase insensitive exact string comparisoncol_name LIKE "ABC"
NOT LIKECase insensitive exact string inequality comparisoncol_name NOT LIKE "ABCD"
%Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)col_name LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS")
_Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)col_name LIKE "AN_" (matches "AND", but not "AN")
IN (…)String exists in a listcol_name IN ("A", "B", "C")
NOT IN (…)String does not exist in a listcol_name NOT IN ("D", "E", "F")

Example: Find all the WALL-* movie

SELECT * FROM movies 
WHERE title LIKE "WALL-_";

(这里用下划线'_'代表任何字符)

Step 3:Sort & Filter

过滤

过滤重复信息:关键词 DISTINCT

控制获取信息的行数:关键词 LIMITOFFSET

格式:如:从第二行显示不重复的导演的名字

SELECT DISTINCT director FROM movies
LIMIT 5 OFFSET 1;

排序

按字母字典序 / 数字大小排序:关键词 ORDER BY

格式:

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

如:按字母顺序列出前五部电影

SELECT title FROM movies
ORDER BY title ASC
LIMIT 5;

在列出接下来五部

SELECT title FROM movies
ORDER BY title ASC
LIMIT 5 OFFSET 5;

PATR TWO 多个表

Step 1 :内连接

同行数表格举例:

Table: Movies (Read-Only)

IdTitleDirectorYearLength_minutes
1Toy StoryJohn Lasseter199581
2A Bug's LifeJohn Lasseter199895
3Toy Story 2John Lasseter199993
4Monsters, Inc.Pete Docter200192
5Finding NemoAndrew Stanton2003107
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

关键词:INNER JOINON

格式:

SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table 
    ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

如:找出电影Title和对应的Domestic_sales,International_sales

SELECT title,Domestic_sales,International_sales
FROM movies
    JOIN BoxOffice
        ON movies.id = BoxOffice.Movie_id;

非同行数表格举例:

Table: Buildings (Read-Only)

Building_nameCapacity
1e24
1w32
2e16
2w20

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

Step 2 :外部连接

关键词:LEFT/RIGHT/FULL JOIN

效果:

LEFT

SELECT *
FROM buildings 
  LEFT JOIN employees
    ON building_name = building;
Building_nameCapacityRoleNameBuildingYears_employed
1e24EngineerBecky A.1e4
1e24EngineerDan B.1e2
1e24EngineerDan M.1e4
1e24EngineerMalcom S.1e1
1e24EngineerSharon F.1e6
1e24ManagerScott K.1e9
1e24ManagerShirlee M.1e3
1w32
2e16
2w20ArtistBrandon J.2w7
2w20ArtistJakob J.2w6
2w20ArtistLillia A.2w7
2w20ArtistSherman D.2w8
2w20ArtistTylar S.2w2
2w20ManagerDaria O.2w6

RIGHT

SELECT *
FROM buildings 
  RiGHT JOIN employees
    ON building_name = building;
Building_nameCapacityRoleNameBuildingYears_employed
1e24EngineerBecky A.1e4
1e24EngineerDan B.1e2
1e24EngineerDan M.1e4
1e24EngineerMalcom S.1e1
1e24EngineerSharon F.1e6
1e24ManagerScott K.1e9
1e24ManagerShirlee M.1e3
2w20ArtistBrandon J.2w7
2w20ArtistJakob J.2w6
2w20ArtistLillia A.2w7
2w20ArtistSherman D.2w8
2w20ArtistTylar S.2w2
2w20ManagerDaria O.2w6

通俗来讲就是

FROM A
LEFT/RIGHT  JOIN B 

LEFT 就是以 A 为主表

RIGHT 就是以 B 为主表

格式:

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;

如:List all buildings and the distinct employee roles in each building (including empty buildings)

列出所有建筑和每个建筑中不同的员工角色(包括空建筑)

SELECT DISTINCT building_name,role
FROM buildings 
    LEFT JOIN employees
    ON buildings.building_name = employees.building

Step 3 :About Null

关键词:NULL

很简单就是用 NULL 代表 空

可以在判断表格是否被填时用。

格式:

SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

如:查找未分配到大楼的所有员工的姓名和角色

SELECT * FROM employees
WHERE building is NULL;

Step 4 :Queries with expressions

关键词:AS

格式:

SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;

说明:

就是将一个计算值设为新的变量。

如:(以Step 1 中的表格数据举例)列出所有的电影以及它们以百万美元计的总销售额。

SELECT (domestic_sales+international_sales)/1000000 AS sale,title
FROM movies
    JOIN boxoffice
    ON id = movie_id;

PATR THREE 分类筛选

Step 1 : 查询与聚合

关键词:

计算方面:(类似 <math.h> 中的函数)

FunctionDescription
COUNT(), COUNT(column*)A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.
MIN(column)Finds the smallest numerical value in the specified column for all rows in the group.
MAX(column)Finds the largest numerical value in the specified column for all rows in the group.
AVG(column)Finds the average numerical value in the specified column for all rows in the group.
SUM(column)Finds the sum of all numerical values in the specified column for the rows in the group.

聚合:GROUP BY

格式:

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column;

举例:

以Table: Employees 为例,Find the total number of employee years worked in each building 。

分析:需要统计相同建筑因此要以building聚合,总数用SUM(...) AS ...

SELECT SUM(years_employed) AS years_employed,building
FROM employees
GROUP BY building

Step 2 :分组后的筛选

关键词:HAVING

目的:按照某个标签(如:职业,房子)分组后,筛选要查看的某几个(如:只看画家..)

格式:

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

举例:

以Table: Employees 为例,Find the total number of years employed by all Engineers。

需要在GROUP后筛选(HAVING)出 engineer 看

SELECT role, SUM(years_employed) AS sum
FROM employees
GROUP BY role
HAVING role = "Engineer"
Last modification:July 9th, 2020 at 10:27 pm
请赏我杯奶茶,让我快乐长肉