Codecademy中Learn SQL,
SQL: Table Transformaton和SQL: Analyzing Business
Metrics三门课程的笔记,以及补充的叠合笔记。

Codecademy的教程以SQLite编写,笔记中改成了MySQL语句。

 

I.
Learn SQL

澳门凯旋门游戏网址, 

 

1.
Manipulation – Create, edit, delete data

 

1.4
Create 创设数据库或数据库中的表

 

CREATE TABLE celebs 
    (
    id INTEGER, 
    name TEXT, 
    age INTEGER 
    ); # 第一列id,数据类型整数;第二列name,数据类型文本;第三列age,数据类型整数

 

1.5
Insert 向表中插入行

 

INSERT INTO celebs ( id, name, age)
    VALUES ( 1, 'Alan Mathison Turing', 42); # 在celebs表最下方插入数据:id列为1,name列为Alan Mathion Turing,age列为42

 

1.6
Select 选用数据

 

SELECT 
    *
FROM
    celebs; # 显示celebs表所有数据

 

1.7
Update 修改数据

 

UPDATE celebs 
SET 
    age = 22
WHERE
    id = 1; # 将celebs表中id=1的行的age改为22

在Safe
Update情势下,WHERE指令必须钦命KEY列 

 

1.8
Alert 改变表结构或数据类型

 

ALERT TABLE celebs
ADD COLUMN twitter_handle TEXT AFTER id; # 在celebs表的id列后增加twitter_handle列

 

ALERT TABLE celebs
DROP email, DROP instagram; # 同时删除celebs的email和instagram两列

 

ALERT TABLE 'test'.'data'
CHANGE COLUMN 'Mobile' 'Mobile' BLOB NULL DEFAULT NULL; # 将表test.data的Mobile列的数据类型改为BLOB,该列数据默认为NULL

 

1.9
DELETE 删除行

 

DELETE FROM celebs 
WHERE
    twitter_handle IS NULL; # 删除表celebs中twitter_handle为NULL的行

在Safe
Update格局下,WHERE指令必须钦定KEY列 

 

DELETE FROM course
WHERE
    Cno IN (8,9);

 

 

2.
Queries – Retrieve data

 

2.3
Select Distinct 再次回到唯一差异的值

 

SELECT DISTINCT
    genre
FROM
    movies; # 查询movies表中genre列的所有不重复值

 

2.4
Where 规定采纳的正经

 

SELECT 
    *
FROM
    movies
WHERE
    imdb_rating > 8; # 查询movies表中imdb_rating大于8的行

 

= equals

!= not
equals

> greater
than

< less
than

>= greater
than or equal to

<= less than
or equal to

 

2.5
Like I 在 WHERE 子句中寻找列中的内定格局

 

SELECT 
    *
FROM
    movies
WHERE
    name LIKE ' Se_en';

 

2.6
Like II

 

SELECT 
    *
FROM
    movies
WHERE
    name LIKE 'a%';

 

查询KPI表的cout_process列中含有’四分之二’字符的行:

SELECT 
    *
FROM
    KPI
WHERE
    count_process LIKE '%50\%%';

 

NB

“\”为转义符号,代表第三个%为文本字符,而非通配符

 

‘_’
substitutes any individual character

‘%’ matches
zero or more missing characters

 

澳门凯旋门注册网址, 

2.7
Between 在 WHERE 子句中使用,选择介于七个值时期的数码范围

 

The BETWEEN
operator is used to filter the result set within a certain range. The
values can be numbers, text or dates.

SELECT 
    *
FROM
    movies
WHERE
    name BETWEEN 'A' AND 'J'; # 查询movies中name以A至J开头的所有行

 

NB: names
that begin with letter “A” up to but not including “J”.

今是昨非的数据库对
BETWEEN…AND
操作符的管理方式是有距离的,有开区间、闭区间,也可能有半开半闭区间。

 

SELECT 
    *
FROM
    movies
WHERE
    year BETWEEN 1990 AND 2000; # 查询movies中year在1990至2000年间的行

 

NB: years
between 1990 up to and including凯旋门074网址, 2000

 

2.8
And 且运算符

 

AND is an
operator that combines two conditions. Both conditions must be true for
the row to be included in the result set.

SELECT 
    *
FROM
    movies
WHERE
    year BETWEEN 1990 AND 2000
        AND genre = 'comedy'; # 查询movies中year在1990至2000间,且genre为comedy的行

 

2.9
Or 或运算符

 

OR is used to
combine more than one condition in WHERE clause. It evaluates each
condition separately and if any of the conditions are true than the row
is added to the result set. OR is an operator that filters the result
set to only include rows where either condition is true.

SELECT 
    *
FROM
    movies
WHERE
    genre = 'comedy' OR year < 1980; # 查询movies中genre为comedy,或year小于1980的行

 

2.10
Order By 对结果集进行排序

 

SELECT 
    *
FROM
    movies
ORDER BY imdb_rating DESC; # 查询movies中的行,结果以imdb_rating降序排列

 

DESC sorts the
result by a particular column in descending order (high to low or Z –
A).

ASC ascending
order (low to high or A – Z).

 

2.11
Limit 规定返回的笔录的数目

 

LIMIT is a
clause that lets you specify the maximum number of rows the result set
will have.

SELECT 
    *
FROM
    movies
ORDER BY imdb_rating ASC
LIMIT 3;  # 查询movies中的行,结果以imdb_rating升序排列,仅返回前3行

 

MS SQL
Server中使用SELECT TOP 3,Oracle中使用WHERE ROWNUM <= 5(?)

 

 

3.
Aggregate Function

 

3.2
Count 再次来到相配内定条件的行数

 

COUNT( ) is a
function that takes the name of a column as an argument and counts the
number of rows where the column is not NULL.

SELECT 
    COUNT(*)
FROM
    fake_apps
WHERE 
    price = 0; # 返回fake_apps中price=0的行数

 

3.3
Group By 合计函数

 

SELECT 
    price, COUNT(*)
FROM
    fake_apps
WHERE
    downloads > 2000
GROUP BY price; # 查询fake_apps表中downloads大于2000的行,将结果集根据price分组,返回price和行数

 

Here, our
aggregate function is COUNT( ) and we are passing price as an
argument(参数) to GROUP BY. SQL will count the total number of apps
for each price in the table.

It is usually
helpful to SELECT the column you pass as an argument to GROUP BY. Here
we SELECT price and COUNT(*).

 

3.4
Sum 重回数值列的总量(总额)

 

SUM is a
function that takes the name of a column as an argument and returns the
sum of all the values in that column.

SELECT 
    category, SUM(downloads)
FROM
    fake_apps
GROUP BY category;

 

3.5
马克斯 重返一列中的最大值(NULL 值不包蕴在盘算中)

 

MAX( ) is a
function that takes the name of a column as an argument and returns the
largest value in that column.

SELECT 
    name, category, MAX(downloads)
FROM
    fake_apps
GROUP BY category;

 

3.6
Min 再次来到一列中的最小值(NULL 值不蕴涵在测算中)

 

MIN( ) is a
function that takes the name of a column as an argument and returns the
smallest value in that column.

SELECT 
    name, category, MIN(downloads)
FROM
    fake_apps
GROUP BY category;

 

3.7
Average 重临数值列的平均值(NULL 值不包含在测算中)

 

SELECT 
    price, AVG(downloads)
FROM
    fake_apps
GROUP BY price;

 

3.8
Round 把数值字段舍入为内定的小数位数

 

ROUND( ) is a
function that takes a column name and an integer as an argument. It
rounds the values in the column to the number of decimal places
specified by the integer.

SELECT 
    price, ROUND(AVG(downloads), 2)
FROM
    fake_apps
GROUP BY price;

 

 

4.
Multiple Tables

 

4.2
Primary Key 主键

 

A primary key
serves as a unique identifier for each row or record in a given table.
The primary key is literally an “id” value for a record. We could use
this value to connect the table to other tables.

CREATE TABLE  artists
    (
    id INTEGER PRIMARY KET,
    name TEXT
    );

 

NB

By specifying
that the “id” column is the “PRIMARY KEY”, SQL make sure that:

1. None of the
values in this column are “NULL”;

2. Each value
in this column is unique.

 

A table can
not have more than one “PRIMARY KEY” column.

 

4.3
Foreign Key 外键

 

SELECT 
    *
FROM
    albums
WHERE
    artist_id = 3;

 

A foreign key is
a column that contains the primary key of another table in the database.
We use foreign keys and primary keys to connect rows in two different
tables. One table’s foreign key holds the value of another table’s
primary key. Unlike primary keys, foreign keys do not need to be unique
and can be NULL. Here, artist_id is a foreign key in the “albums”
table.

 

The relationship
between the “artists” table and the “albums” table is the “id” value of
the artists.

 

4.4
克罗斯 Join 用于生成两张表的笛Carl集

 

SELECT 
    albums.name, albums.year, artists.name
FROM
    albums,
    artists;

 

One way to query
multiple tables is to write a SELECT statement with multiple table names
seperated by a comma. This is also known as a “cross join”.

 

When querying
more than one table, column names need to be specified by
table_name.column_name.

 

Unfortunately,
the result of this cross join is not very useful. It combines every row
of the “artists” table with every row of the “albums” table. It would be
more useful to only combine the rows where the album was created by the
artist.

 

4.5
Inner Join 内连接:在表中设有至少一个相称时,INNE翼虎 JOIN
关键字再次回到行

 

SELECT 
    *
FROM
    albums
        JOIN
    artists ON albums.artist_id = artists.id; # INNER JOIN等价于JOIN,写JOIN默认为INNER JOIN

 

In SQL, joins
are used to combine rows from two or more tables. The most common type
of join in SQL is an inner join.

 

An inner join
will combine rows from different tables if the join condition is
true.

  1. SELECT *:
    specifies the columns our result set will have. Here * refers to every
    column in both tables;

  2. FROM albums:
    specifies first table we are querying;

  3. JOIN artists
    ON: specifies the type of join as well as the second table;

4.
albums.artist_id = artists.id: is the join condition that describes how
the two tables are related to each other. Here, SQL uses the foreign key
column “artist_id” in the “albums” table to match it with exactly one
row in the “artists” table with the same value in the “id” column. It
will only match one row in the “artists” table because “id” is the
PRIMARY KEY of “artists”.

 

4.6
Left Outer Join
左外连接:纵然右表中并未相称,也从左表再次回到全数的行

 

SELECT 
    *
FROM
    albums
        LEFT JOIN
    artists ON albums.artist_id = artists.id;

 

Outer joins also
combine rows from two or more tables, but unlike inner joins, they do
not require the join condition to be met. Instead, every row in the
left table is returned
 in the result set, and if the join condition is
not met, the NULL values are used to fill in the columns from the right
table.

 

奥德赛IGHT JOIN
右外链接:即便左表中未有相配,也从右表重返全体的行

FULL JOIN
全链接:只要在那之中一个表中存在特别,就再次来到行

 

4.7
Aliases 为列名称和表名称钦赐别称

 

AS is a keyword
in SQL that allows you to rename a column or table using an alias. The
new name can be anything you want as long as you put it inside of single
quotes.

SELECT 
    albums.name AS 'Album',
    albums.year,
    artists.name AS 'Artist'
FROM
    albums
        JOIN
    artists ON albums.artist_id = artists.id
WHERE
    albums.year > 1980;

 

NB

The columns
have not been renamed in either table. The aliases only appear in the
result set.

 

 

 

II.
SQL: Table Transformation

 

 

1.
Subqueries 子查询

 

1.2
Non-Correlated Subqueries I 不相关子查询

 

SELECT 
    *
FROM
    flights
WHERE
    origin IN (SELECT 
            code
        FROM
            airports
        WHERE
            elevation > 2000);

 

1.4
Non-Correlated Subqueries III

 

SELECT 
    a.dep_month,
    a.dep_day_of_week,
    AVG(a.flight_count) AS average_flights
FROM
    (SELECT 
        dep_month,
            dep_day_of_week,
            dep_date,
            COUNT(*) AS flight_count
    FROM
        flights
    GROUP BY 1 , 2 , 3) a
WHERE
    a.dep_day_of_week = 'Friday'
GROUP BY 1 , 2
ORDER BY 1 , 2; # 返回每个月中,每个星期五的平均航班数量

 

结构

[outer
query]

    FROM

    [inner
query] a

WHERE

GROUP BY

ORDER BY

 

NB

“a”: With the
inner query, we create a virtual table. In the outer query, we can refer
to the inner query as “a”.

“1,2,3” in
inner query: refer to the first, second and third columns
selected

         for
display                      DBMS

SELECT
dep_month,                 (1)

dep_day_of_week,
                    (2)

dep_date,    
                               (3)

COUNT(*) AS
flight_count         (4)

FROM
flights

 

SELECT 
    a.dep_month,
    a.dep_day_of_week,
    AVG(a.flight_distance) AS average_distance
FROM
    (SELECT 
        dep_month,
    dep_day_of_week,
    dep_date,
    SUM(distance) AS flight_distance
    FROM
        flights
    GROUP BY 1 , 2 , 3) a
GROUP BY 1 , 2
ORDER BY 1 , 2; # 返回每个月中,每个周一、周二……至周日的平均飞行距离

 

1.5
Correlated Subqueries I 相关子查询

 

NB

In a correlated
subquery, the subquery can not be run independently of the outer
query. The order of operations is important in a correlated
subquery:

  1. A row is
    processed in the outer query;

  2. Then, for
    that particular row in the outer query, the subquery is executed.

This means
that for each row processed by the outer query, the subquery will also
be processed for that row.

 

SELECT 
    id
FROM
    flights AS f
WHERE
    distance > (SELECT 
            AVG(distance)
        FROM
            flights
        WHERE
            carrier = f.carrier); # the list of all flights whose distance is above average for their carrier

 

1.6
Correlated Subqueries II

 

In the above
query, the inner query has to be reexecuted for each flight. Correlated
subqueries may appear elsewhere besides the WHERE clause, they can also
appear in the SELECT.

 

SELECT 
    carrier,
    id,
    (SELECT 
            COUNT(*)
        FROM
            flights f
        WHERE
            f.id < flights.id
                AND f.carrier = flights.carrier) + 1 AS flight_sequence_number
FROM
    flights; # 结果集为航空公司,航班id以及序号。相同航空公司的航班,id越大则序号越大

 

相关子查询中,对于外查询推行的每一行,子查询都会为这一行实施二回。在这段代码中,每当外查询提取一行数据中的carrier和id,子查询就能够COUNT表中有多少行的carrier与外查询中的行的carrier一样,且id小于外查询中的行,并在COUNT结果上+1,这一结实列别称字为flight_sequence_number。于是,id越大的航班,序号就越大。

假设将”<“改为”>”,则id越大的航班,序号越小。

 

 

2.
Set Operation

 

2.2
Union 并集 (only distinct values)

 

Sometimes, we
need to merge two tables together and then query the merged
result.

There are two
ways of doing this:

1) Merge the
rows, called a join.

2) Merge the
columns, called a union.

 

SELECT 
    item_name
FROM
    legacy_products 
UNION SELECT 
    item_name
FROM
    new_products;

 

Each SELECT
statement within the UNION must have the same number of
columns
 with similar data types. The columns in each SELECT
statement must be in the same order. By default, the UNION operator
selects only distinct values.

 

2.3
Union All 并集 (allows duplicate values)

 

SELECT 
    AVG(sale_price) 
FROM
    (SELECT 
        id, sale_price
    FROM
        order_items UNION ALL SELECT 
        id, sale_price
    FROM
        order_items_historic) AS a;

 

2.4
Intersect 交集

 

Microsoft SQL
Server’s INTERSECT returns any distinct values that are returned
by both the query on the left and right sides of the INTERSECT
operand.

 

SELECT category FROM new_products
INTERSECT
SELECT category FROM legacy_products;

 

NB

MySQL不滋瓷INTECR-VSECT,但足以用INNELX570JOIN+DISTINCT或WHERE…IN+DISTINCT或WHERE EXISTS完毕:

 

SELECT DISTINCT
    category
FROM
    new_products
        INNER JOIN
    legacy_products USING (category);

SELECT DISTINCT
    category
FROM
    new_products
WHERE
    category IN (SELECT 
            category
        FROM
            legacy_products);

 

 

英特网海人民广播电视台湾大学经过UNION
ALL 达成的方式(如下)是不当的,或者会回去仅在四个表中出现且COUNT(*)
> 1的值:

 

SELECT 
    category, COUNT(*)
FROM
    (SELECT 
        category
    FROM
        new_products UNION ALL SELECT 
        category
    FROM
        legacy_products) a
GROUP BY category
HAVING COUNT(*) > 1;

 

2.5
Except (MS SQL Server) / Minus (Oracle) 差集

 

SELECT category FROM legacy_products
EXCEPT # 在Oracle中为MINUS
SELECT category FROM new_products;

 

NB

MySQL不滋瓷差集,但能够用WHERE…IS
NULL+DISTINCT或WHERE…NOT IN+DISTINCT或WHERE EXISTS达成:

 

SELECT DISTINCT
    category
FROM
    legacy_products
        LEFT JOIN
    new_products USING (category)
WHERE
    new_products.category IS NULL;

SELECT DISTINCT
    category
FROM
    legacy_products
WHERE
    category NOT IN (SELECT 
            category
        FROM
            new_products);

 

 

3.
Conditional Aggregates

 

3.2
NULL

 

use IS NULL or
IS NOT NULL in the WHERE clause to test whether a value is or is not
null.

 

SELECT 
    COUNT(*)
FROM
    flights
WHERE
    arr_time IS NOT NULL
        AND destination = 'ATL';

 

3.3
CASE WHEN “if, then, else”

 

SELECT
    CASE
        WHEN elevation < 250 THEN 'Low'
        WHEN elevation BETWEEN 250 AND 1749 THEN 'Medium'
        WHEN elevation >= 1750 THEN 'High'
        ELSE 'Unknown'
    END AS elevation_tier
    , COUNT(*)
FROM airports
GROUP BY 1;

 

END is required
to terminate the statement, but ELSE is optional. If ELSE is not included, the result
will be NULL.

 

3.4
COUNT(CASE WHEN)

 

count the number
of low elevation airports by state where low elevation is defined as
less than 1000 ft.

SELECT 
    state,
    COUNT(CASE
        WHEN elevation < 1000 THEN 1
        ELSE NULL
    END) AS count_low_elevaton_airports
FROM
    airports
GROUP BY state; 

 

3.5
SUM(CASE WHEN)

 

sum the total
flight distance and compare that to the sum of flight distance from a
particular airline (in this case, Delta) by origin airport. 

SELECT 
    origin,
    SUM(distance) AS total_flight_distance,
    SUM(CASE
        WHEN carrier = 'DL' THEN distance
        ELSE 0
    END) AS total_delta_flight_distance
FROM
    flights
GROUP BY origin; 

 

3.6
Combining aggregates

 

find out the
percentage of flight distance that is from Delta by origin
airport. 

SELECT 
    origin,
    100.0 * (SUM(CASE
        WHEN carrier = 'DL' THEN distance
        ELSE 0
    END) / SUM(distance)) AS percentage_flight_distance_from_delta
FROM
    flights
GROUP BY origin; 

 

3.7
Combining aggregates II

 

Find the
percentage of high elevation airports (elevation >= 2000) by state
from the airports table.

SELECT 
    state,
    100.0 * COUNT(CASE
        WHEN elevation >= 2000 THEN 1
        ELSE NULL
    END) / COUNT(elevation) AS percentage_high_elevation_airports
FROM
    airports
GROUP BY 1;

SELECT 
    state,
    100.0 * SUM(CASE
        WHEN elevation >= 2000 THEN 1
        ELSE 0
    END) / COUNT(elevation) AS percentage_high_elevation_airports
FROM
    airports
GROUP BY 1;

 

 

4.
Date, Number and String Functions

 

MySQL Date
函数:

 

NOW()  
 重回当前的日子和岁月

CUWranglerDATE()  
 重返当前的日期

CURTIME()  
 重回当前的时日

DATE()  
 提取日期或日期/时间表明式的日期部分

EXTRACT()  
 重返日期/时间的单独部分,例如年、月、日、小时、分钟等等

DATE_ADD()  
 给日期增添钦赐的时刻间隔

DATE_SUB()  
 从日期减去钦定的光阴距离

DATEDIFF()  
 再次回到五个日子之间的天数

DATE_FORMAT()  
 用分歧的格式显示日期/时间

 

例1:

 

SELECT NOW(),
CURDATE(), CURTIME();

结果:

NOW()          
                 CURDATE()    CURTIME()

2008-12-29
16:25:46      2008-12-29       16:25:46

 

例2:

 

CREATE TABLE Orders (
    OrderId INT NOT NULL,
    ProductName VARCHAR(50) NOT NULL,
    OrderDate DATETIME NOT NULL DEFAULT NOW (),
    PRIMARY KEY (OrderId)
);

 

OrderDate 列规定
NOW()
作为私下认可值。作为结果,当您向表中插入行时,当前几日期和时间自动插入列中。

 

例3:

笔记中改成了MySQL语句。 

EXTRACT (unit
FROM date): date参数是法定的日期表明式,unit参数能够是下列值:

DATE_ADD(date,INTECR-VVAL
expr type):
date参数是官方的日子表明式,expr是期待丰盛的时刻距离,type参数能够是下列值:

DATE_SUB(date,INTESportageVAL
expr type):
date参数是官方的日期表达式,expr是期望丰富的流年间隔,type参数能够是下列值:

 

笔记中改成了MySQL语句。MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

笔记中改成了MySQL语句。HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

 

例4:

 

DATEDIFF(date1,date2):
date1 和 date2
参数是法定的日子或日期/时间表明式。唯有值的日期部分插手计算。

 

例5:

 

DATE_FORMAT(date,format):
date 参数是官方的日期。format 规定日期/时间的出口格式。

能够选择的格式有:

 

SELECT 
    id,
    carrier,
    origin,
    destination,
    DATE_FORMAT(NOW(), '%Y-%c-%d %T') AS datetime
FROM
    flights;

 

4.2
Dates

 

select the date
and time of all deliveries in the baked_goods table using the column
delivery_time.

SELECT 
    DATE(delivery_time), TIME(delivery_time)
FROM
    baked_goods;

 

4.4
Dates III

 

Each of the
baked goods is packaged five hours, twenty minutes, and two days after
the delivery. Create a query returning all the packaging times for the
goods in the baked_goods table.

SELECT 
    DATE_ADD(delivery_time,
        INTERVAL '2 5:20:00' DAY_SECOND) AS package_time
FROM
    baked_goods; 

 

DATE:

 

4.6
Numbers II

 

GREATEST(n1,n2,n3,…):
returns the greatest value in the set of the input numeric
expressions;

LEAST(n1,n2,n3,…):
returns the least value in the set of the input numeric
expressions;

 

Find the
greatest time value for each item.

SELECT 
    id, GREATEST(cook_time, cool_down_time)
FROM
    baked_goods;

 

NB:区别数据类型的可比准绳

 

上述命令的结果集为:

澳门凯旋门游戏网址 1

 

而baked_goods中的cook_time和cool_down_time实际为:

澳门凯旋门游戏网址 2

 

芸芸众生row
2,13和15中的33>5,20>8,45>8,但GREATEST重临的是十分小的值

那是因为cook_笔记中改成了MySQL语句。time和cool_down_time这两列的数据类型是TEXT:

澳门凯旋门游戏网址 3

 

在GREATEST和LEAST命令中,

当数据类型为TEXT等文本类时,相比较的是字符串的尺寸,即从字符串的首个字符开首比较。’5’比>3’,所以’5′>’33’。’T’>’冠道’,所以’TO牧马人RES’>’RENE’;

当数据类型为INT、BIGINT等数字类时,比较的才是数值的分寸。

 

 

4.7
Strings

 

CONCAT(concatenate):

 

Combine the
first_name and last_name columns from the bakeries table as the
full_name.

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name
FROM
    bakeries;

 

GROUP_CONCAT:

 

Combine the
cities of the three states in city column from bakeries table as
cities.

SELECT 
    state, GROUP_CONCAT(DISTINCT (city)) AS cities
FROM
    bakeries
WHERE
    state IN ('California' , 'New York', 'Texas')
GROUP BY state;

 

 

NB

CONCAT再次回到结果为连日来参数发生的字符串。

如有任何二个参数为NULL
,则重回值为 NULL;

若果具备参数均为非二进制字符串,则结果为非二进制字符串;

如果自变量中包涵任一二进制字符串,则结果为三个二进制字符串;

五个数字参数被转载为与之对等的二进制字符串格式;可接纳呈现档案的次序CAST幸免这种景况,举例:

SELECT
CONCAT(CAST(int_col AS CHAR), char_col)

 

CAST:

 

例1:

 

SELECT CAST(12
AS DECIMAL) / 3;

 

Returns the
result as a DECIMAL by casting one of the values as a decimal rather
than an integer.

 

例2:

 

SELECT 
    CONCAT(CAST(distance AS CHAR), ' ', city)
FROM
    bakeries;

 

4.8
Strings II

 

REPLACE(string,from_string,to_string);

The function
returns the string ‘string’ with all occurrences of the string
‘from_string’ replaced by the string ‘to_string’.

 

Replace
‘enriched_flour’ in the ingredients list to just ‘flour’.

SELECT 
    id,
    REPLACE(ingredients,
        'enriched_flour',
        'flour')
FROM
    baked_goods;

 

 

 

III.
SQL: Analyzing Business Metrics

 

笔记中改成了MySQL语句。 

1.
Advanced Aggregates

 

1.4
Daily Revenue

 

how much we’re
making per day for kale-smoothies.

SELECT 
    DATE(ordered_at), ROUND(SUM(amount_paid), 2)
FROM
    orders
        JOIN
    order_items ON orders.id = order_items.order_id
WHERE
    name = 'kale-smoothie'
GROUP BY 1
ORDER BY 1;

 

1.6
Meal Sums

 

total revenue of
each item.

SELECT 
    name, ROUND(SUM(amount_paid), 2)
FROM
    order_items
GROUP BY name
ORDER BY 2 DESC;

 

1.7
Product Sum 2

 

percent of
revenue each product represents.

SELECT 
    name,
    ROUND(SUM(amount_paid) / (SELECT 
                    SUM(amount_paid)
                FROM
                    order_items) * 100.0,
            2) AS PCT
FROM
    order_items
GROUP BY 1
ORDER BY 2 DESC;

 

Subqueries can
be used to perform complicated calculations and create filtered or
aggregate tables on the fly.

 

1.9
Grouping with Case Statements

 

group the order
items by what type of food they are.

SELECT 
    *,
    CASE name
        WHEN 'kale-smoothie' THEN 'smoothie'
        WHEN 'banana-smoothie' THEN 'smoothie'
        WHEN 'orange-juice' THEN 'drink'
        WHEN 'soda' THEN 'drink'
        WHEN 'blt' THEN 'sandwich'
        WHEN 'grilled-cheese' THEN 'sandwich'
        WHEN 'tikka-masala' THEN 'dinner'
        WHEN 'chicken-parm' THEN 'dinner'
        ELSE 'other'
    END AS category
FROM
    order_items
ORDER BY id;

 

look at percents
of purchase by category:

SELECT 
    CASE name
        WHEN 'kale-smoothie' THEN 'smoothie'
        WHEN 'banana-smoothie' THEN 'smoothie'
        WHEN 'orange-juice' THEN 'drink'
        WHEN 'soda' THEN 'drink'
        WHEN 'blt' THEN 'sandwich'
        WHEN 'grilled-cheese' THEN 'sandwich'
        WHEN 'tikka-masala' THEN 'dinner'
        WHEN 'chicken-parm' THEN 'dinner'
        ELSE 'other'
    END AS category,
    ROUND(1.0 * SUM(amount_paid) / (SELECT 
                    SUM(amount_paid)
                FROM
                    order_items) * 100,
            2) AS PCT
FROM
    order_items
GROUP BY 1
ORDER BY 2 DESC;

 

NB

Here 1.0 * is
a shortcut to ensure the database represents the percent as a
decimal.

 

1.11
Recorder Rates

 

We’ll define
reorder rate as the ratio of the total number of orders to the number of
people making those orders. A lower ratio means most of the orders are
reorders. A higher ratio means more of the orders are first
purchases.

 

SELECT 
    name,
    ROUND(1.0 * COUNT(DISTINCT order_id) / COUNT(DISTINCT delivered_to),
            2) AS reorder_rate
FROM
    order_items
        JOIN
    orders ON orders.id = order_items.order_id
GROUP BY 1
ORDER BY 2 DESC;

 

 

2.
Common Metrics

 

2.2
Daily Revenue

 

SELECT 
    DATE(created_at), ROUND(SUM(price), 2)
FROM
    purchases
GROUP BY 1
ORDER BY 1;

 

2.3
Daily Revenue 2

 

Update our daily
revenue query to exclude refunds.

SELECT 
    DATE(created_at), ROUND(SUM(price), 2) AS daily_rev
FROM
    purchases
WHERE
    refunded_at IS NULL
GROUP BY 1
ORDER BY 1;

 

此地Codecademy的Instructions和代码识别都务求“WHERE
refunded_at IS NOT NULL”,应该是写错了。

 

2.4
Daily Active Users

 

Calculate
DAU

SELECT 
    DATE(created_at), COUNT(DISTINCT user_id) AS DAU
FROM
    gameplays
GROUP BY 1
ORDER BY 1;

 

笔记中改成了MySQL语句。2.5
Daily Active Users 2

 

Calculate DAU
per-platform

SELECT 
    DATE(created_at), platform, COUNT(DISTINCT user_id) AS DAU
FROM
    gameplays
GROUP BY 1 , 2
ORDER BY 1 , 2;

 

2.6
Daily Average Revenue Per Paying User(ARPPU)

 

Calculate Daily
ARPPU

SELECT 
    DATE(created_at),
    ROUND(SUM(price) / COUNT(DISTINCT user_id), 2) AS ARPPU
FROM
    purchases
WHERE
    refunded_at IS NULL
GROUP BY 1
ORDER BY 1;

 

2.8
ARPU 2

 

One way to
create and organize temporary results in a query is with CTEs, Common
Table Expressions, aka WITH … AS clauses. The WITH … AS clauses make
it easy to define and use results in a more organized way than
subqueries.

 

NB

MySQL不滋瓷CTE。大概能够用不经常表实现,待验证。(?)

 

Calculate Daily
Revenue

WITH daily_revenue AS 
    (
    SELECT 
        date(created_at) AS dt,
        ROUND(SUM(price), 2) AS rev
    FROM
        purchases
    WHERE refunded_at IS NULL
    GROUP BY 1
    )
SELECT 
    * 
FROM 
    daily_revenue 
ORDER BY dt;

 

2.9
ARPU 3

 

Calculate Daily
ARPU

WITH daily_revenue AS (
  SELECT
    DATE(created_at) AS dt,
    ROUND(SUM(price), 2) AS rev
  FROM purchases
  WHERE refunded_at IS NULL
  GROUP BY 1
), 
daily_players AS (
  SELECT
    DATE(created_at) AS dt,
    COUNT(DISTINCT user_id) AS players
  FROM gameplays
  GROUP BY 1
)
SELECT
  daily_revenue.dt,
  daily_revenue.rev / daily_players.players
FROM daily_revenue
  JOIN daily_players USING (dt);

 

2.12
1 Day Retention 2

 

SELF
JOIN:

By using a
self-join, we can make multiple gameplays available on the same row of
results. This will enable us to calculate retention.

The power of
self-join comes from joining every row to every other row. This makes it
possible to compare values from two different rows in the new result
set.

 

SELECT 
    DATE(g1.created_at) AS dt, g1.user_id
FROM
    gameplays AS g1
        JOIN
    gameplays AS g2 ON g1.user_id = g2.user_id
ORDER BY 1
LIMIT 100;

 

2.13
1 Day Retention 3

 

Calculate 1 Day
Retention Rate

SELECT 
    DATE(g1.created_at) AS dt,
    ROUND(100 * COUNT(DISTINCT g2.user_id) / COUNT(DISTINCT g1.user_id)) AS retention
FROM
    gameplays AS g1
        LEFT JOIN
    gameplays AS g2 ON g1.user_id = g2.user_id
        AND DATE(g1.created_at) = DATE(DATE_SUB(g2.created_at, INTERVAL 1 DAY))
GROUP BY 1
ORDER BY 1;

 

NB

游戏行其中,公认的后天留存率(1
Day Retention
Rate)定义为:DNU在前几日再也登陆的百分比。而本题总括的是:DAU在明日再度登陆的比重。

 

 

 

IV.
附录

 

 

1.
DISTINCT和GROUP BY的去重逻辑分析

 

SELECT 
    COUNT(DISTINCT amount_paid)
FROM
    order_items;

SELECT 
    COUNT(1)
FROM
    (SELECT 
        1
    FROM
        order_items
    GROUP BY amount_paid) a;

SELECT 
    SUM(1)
FROM
    (SELECT 
        1
    FROM
        order_items
    GROUP BY amount_paid) a;

 

分别是在运算和存款和储蓄上的衡量:

DISTINCT供给将列中的全部内容存储在叁个内存中,将具备不一样值存起来,内部存款和储蓄器消耗恐怕非常的大;

GROUP
BY先将列排序,排序的主干理论是:时间复杂为nlogn,空间为1。优点是空中复杂度小,(?)缺点是实施时间会较长。

 

行使时依据具体情况取舍:数据分布离散时,使用GROUP
BY;数据分布集中时,使用DISTINCT,功用高,空间攻下极小。

 

 

2.
SELECT 1 FROM table

 

1是一常量(可认为私行数值),查到的全体行的值都以它,但从作用上的话,1>column
name>*,因为不用查字典表(?)。

未曾特别含义,只要有数量就赶回1,未有则赶回NULL。

常用于EXISTS、子查询中,一般在剖断子查询是否成功(就是不是有满意条件)时行使,如:

SELECT 
    *
FROM
    orders
WHERE
    EXISTS( SELECT 
            1
        FROM
            orders o
                JOIN
            order_items i ON o.id = i.order_id);

 

 

3.
WHERE 1=1和WHERE 1=0的作用

 

3.1
WHERE 1=1:条件恒真,同理WHERE ‘a’ =
‘a’等。在布局动态SQL语句,如:不定数量查询条件时,1=1能够很有益于地专门的学业语句:

 

3.1.1
制作查询页面时,若可查询的选项有多个,且用户可活动接纳并输入关键词,那么根据平日的查询语句的动态构造,代码差不离如下:

MySqlStr="SELECT * FROM table WHERE";

  IF(Age.Text.Lenght>0)
  {
    MySqlStr=MySqlStr+"Age="+"'Age.Text'";
  }

  IF(Address.Text.Lenght>0)
  {
    MySqlStr=MySqlStr+"AND Address="+"'Address.Text'";
  }

 

1)即便上述的三个IF判别语句,均为True,即用户都输入了查询词,那么,最终的MySqlStr动态构造语句变为:

MySqlStr=”SELECT
* FROM table WHERE Age=’27’ AND
Address=’广西省广州市陆丰市科兴科学园'”语句完整,能够被科学试行;

2)假诺七个IF都不树立,MySqlStr=”SELECT
* FROM table WHERE”;,语句错误,不能够实行。

 

3.1.2 使用WHERE
1=1:

 

1)MySqlStr=”SELECT *
FROM table WHERE 1=1 AND Age=’27’ AND
Address=’江苏省河源市禅东源县科兴科学园'”;,准确可实践;

2)MySqlStr=”SELECT *
FROM table WHERE 1=1″;,由于WHERE
1=1恒真,该语句能够被科学实施,功效相当于:MySqlStr=”SELECT * FROM
table”;

 

也正是说:如若用户在多规格查询页面中,不选取其余字段、不输入任何重大词,那么,必将重临表中全体数据;即便用户在页面中,选拔了一部分字段并且输入了一部分查询关键词,那么,就按用户设置的条件进行查询。

 

WHERE
1=1仅仅只是为了满意多规格查询页面中不明确的各个因素而使用的一种结构一条正确能运作的动态SQL语句的一种办法。

 

3.2
WHERE 1=0:条件恒假,同理WHERE 1 <>
1等。不会回去任何数据,只有表结构,可用以快捷建表:

 

3.2.1
用于读取表的布局而不思虑表中的多寡,那样节约了内部存款和储蓄器,因为能够不要保存结果集:

SELECT 
    *
FROM
    table
WHERE
    1 = 0; 

 

3.2.2
创立一个新表,新表的结构与查询的表的构造同样:

CREATE TABLE newtable AS SELECT * FROM
    oldtable
WHERE
    1 = 0;  

 

 

 

4.
复制表

 

CREATE TABLE newtable AS SELECT * FROM
    oldtable;  

 

5.
Having

 

NB

在 SQL 中扩充HAVING 子句原因是,WHERE
关键字不可能与聚合函数(aggregate
function)一齐行使。常用的聚合函数有:COUNT,SUM,AVG,MAX,MIN等。

 

例1:查找订单总金额有限
3000 的客户

 

SELECT 
    Customer, SUM(OrderPrice)
FROM
    Orders
GROUP BY Customer
HAVING SUM(OrderPrice) < 2000;

 

例2:查找客户
“Bush” 或 “亚当斯” 具有超过 1500 的订单总金额

 

SELECT 
    Customer, SUM(OrderPrice)
FROM
    Orders
WHERE
    Customer = 'Bush' OR Customer = 'Adams'
GROUP BY Customer
HAVING SUM(OrderPrice) > 1500;

 

 

 

6.
USING

 

It is mostly
syntactic sugar, but a couple differences are noteworthy:

 

ON is the more
general of the two. One can JOIN tables ON a column, a set of columns
and even a condition. For example:

SELECT 
    *
FROM
    world.City
        JOIN
    world.Country ON (City.CountryCode = Country.Code) 
WHERE ...

 

USING is useful
when both tables share a column of the exact same name on which they
join. In this case, one may say:

SELECT 
    film.title, film_id # film_id is not prefixed
FROM
    film
        JOIN
    film_actor USING (film_id)
WHERE ...

 

To do the above
with ON, we would have to write:

SELECT 
    film.title, film.film_id # film.film_id is required here
FROM
    film
        JOIN
    film_actor ON (film.film_id = film_actor.film_id)
WHERE ...

 

NB film.film_id
qualification in the SELECT clause. It would be invalid to just say
film_id
 since that would make for an ambiguity.

 

 

 

7.
IFNULL()和COALESCE()函数 用于规定如何处理NULL值

 

假如
“UnitsOnOrder” 列可选,且包含 NULL 值。使用:

SELECT
    ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM
    Products;

 

由于
“UnitsOnOrder” 列存在NULL值,那么结果是 NULL。

为了有利于计算,我们盼望如若值是NULL,则重临0:

SELECT
    ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM
    Products;

SELECT
    ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM
    Products;

 

 

8.
UCASE()和LCASE()函数 字段值得大小写转变

 

SELECT 
    LCASE(first_name) AS first_name,
    last_name,
    city,
    UCASE(state) AS state
FROM
    bakeries;

 

9.
MID()函数 用于从文本字段中领到字符

 

SELECT 
    MID(column_name, start, length)
FROM
    table_name;

 

column_name:
必需。要提取字符的字段。

start:
必需。规定开头地点(初阶值是 1)。

length:
可选。要回到的字符数。如若轻巧,则 MID() 函数重回剩余文本。

 

SELECT 
    MID(state, 1, 3) AS smallstate
FROM
    bakeries;

 

10.
LENGTH()函数 重回文本字段中 值的尺寸

 

SELECT 
    LENGTH(City) AS LengthOfCity
FROM
    bakeries;

 

11.
WHERE EXISTS命令

 

11.1
WHERE EXISTS与WHERE NOT EXISTS

 

EXISTS:推断子查询获得的结果集是或不是是三个空集,借使不是,则赶回
True,假诺是,则赶回
False。即:尽管在当下的表中存在符合条件的这么一条记下,那么返回True,不然再次回到 False。

 

NOT
EXISTS:成效与 EXISTS 正相反,当子查询的结果为空集时,再次回到True,反之再次来到 False。也正是所谓的”若不存在“。

 

EXISTS和NOT
EXISTS所在的查询属于相关子查询,即:对于外层父查询中的每一行,都实施壹遍子查询。先取父查询的率先个元组,根据它与子查询有关的性质管理子查询,若子查询WHERE条件表明式成立,则表明式重返True,则将此元组放入结果集,就那样推算,直到遍历父查询表中的全部元组。

 

 

询问选修了自由课程的学员的真名:

SELECT 
    Sname
FROM
    student
WHERE
    EXISTS( SELECT 
            *
        FROM
            sc,
            course
        WHERE
            sc.Sno = student.Sno
                AND sc.Cno = course.Cno);

 

查询未被二〇〇三15123号学生选修的学科的课名:

SELECT 
    Cname
FROM
    course
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            sc
        WHERE
            Sno = 200215123
                AND Cno = course.Cno);

 

11.2
WHERE EXISTS与WHERE NOT EXISTS的双层嵌套

 

1)查询选修了整个科目标学员的课程:

SELECT 
    Sname
FROM
    student
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            course
        WHERE
            NOT EXISTS( SELECT 
                    *
                FROM
                    sc
                WHERE
                    Sno = student.Sno AND Cno = course.Cno));

 

思路:

STEP1:先取
Student 表中的第三个元组,得到其 Sno 列的值。

STEP2:再取
Course 表中的第多少个元组,得到其 Cno 列的值。

STEP3:依照 Sno
与 Cno 的值,遍历 SC 表中的全体记录(也正是选课记录)。若对于有些 Sno 和
Cno 的值来讲,在 SC 表中找不到对应的笔录,则表达该 Sno
对应的学生未有选修该 Cno 对应的学科。

STEP4:对于有个别学生来讲,若在遍历
Course
表中全部记录(也正是颇具科目)后,仍找不到任何一门她/她一向不选修的学科,就印证此学生选修了全套的科目。

STEP5:将此学生放入结果元组会集中。

STEP6:回到
STEP1,取 Student 中的下三个元组。

STEP7:将装有结果元组集结展现。

中间第三个 NOT
EXISTS 对应 STEP4,第三个 NOT EXISTS 对应 STEP3。

 

2)查询被全数学员选修的教程的课名:

SELECT 
    Cname
FROM
    course
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            student
        WHERE
            NOT EXISTS( SELECT 
                    *
                FROM
                    sc
                WHERE
                    Cno = course.Cno AND Sno = student.Sno));

 

3)查询选修了200115123号学生选修的总体科目标学习者的学号:

SELECT 
    DISTINCT Sno
FROM
    sc scx
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            sc scy
        WHERE scy.Sno = 200215123
           AND NOT EXISTS( SELECT 
                    *
                FROM
                    sc
                WHERE
                    Sno = scx.Sno AND Cno = scy.Cno));

 

 

扩展:

澳门凯旋门游戏网址 4

 

  1. Condition
    1:TA会C2。Condition 2:TA选修了学科 =>
    exists+exists:查询选修了随机课程的学习者;

  2. Condition
    1:TA不会C2。Condition 2:TA选修了课程 => not
    exists+exists:查询未选修任何学科的学生;

  3. Condition
    1:TA不会C2。Condition 2:TA有学科课没选 => not exists+not
    exists:查询选修了具备课程的学员;

  4. Condition
    1:TA会C2。Condition 2:TA有学科没选 => exists+not
    exists:查询未选修全部课程的学员;

 

11.3
WHERE EXISTS与WHERE IN的比较与利用

 

SELECT 
    Sname
FROM
    student
WHERE
    EXISTS( SELECT 
            *
        FROM
            sc,
            course
        WHERE
            sc.Sno = student.Sno
                AND sc.Cno = course.Cno
                AND course.Cname = '操作系统');

 

SELECT 
    Sname
FROM
    student
WHERE
    Sno IN (SELECT 
            Sno
        FROM
            sc,
            course
        WHERE
            sc.Sno = student.Sno
                AND sc.Cno = course.Cno
                AND course.Cname = '操作系统');

 

以上七个查询都回去选修了“操作系统”课程的学习者的姓名,但原理不一致:

EXISTS:对外表做loop循环,每一趟loop循环再对内表举行查询。首先检查父查询,然后运维子查询,直到它找到第二个匹配项。

IN:把内表和外界做hash连接。首先试行子查询,并将获得的结果存放在三个加了目录的有时表中。在执行子查询前,系统先将父查询挂起,待子查询试行完结,存放在一时表中时现在再实施父查询。

 

因此:

1)要是多少个表中贰个不大,贰个是比较大,则子查询表大的用EXISTS,子查询表小的用IN

2)在查询的多少个表大小相当时,3种查询艺术的实践时间平时是:

EXISTS <=
IN <= JOIN

NOT EXISTS
<= NOT IN <= LEFT JOIN

唯有当表中字段允许NULL时,NOT
IN最慢:

NOT EXISTS <=
LEFT JOIN <= NOT IN

3)不论哪个表大,NOT
EXISTS都比NOT IN快。
因为NOT
IN会对上下表都进行全表扫描,未有使用索引;而NOT
EXISTS的子查询还能够用到表上的目录。

 

 

 

12.
特殊字符单引号的处理

 

转义符号:代表下一个字符为文本字符串的一局部,而非字符串的始发、截止或别的指令

 

用反斜线转义引号:(在询问出错开上下班时间能够随心所欲开采多余单引号的任务)

SELECT 
    *
FROM
    my_contacts
WHERE
    location = 'Grover\'s Mill, NJ';

 

用另二个单引号转义引号:

SELECT 
    *
FROM
    my_contacts
WHERE
    location = 'Grover\'s Mill, NJ';

 

 

13.
REGEXP 正则表明式相称查询

 

^:相称输入字符串的初始地方

$:匹配输入字符串的实现地方

.:匹配除”\n”之外的别的单个字符

[…]:字符会集。'[abc]’能够合作’plain’中的’a’

[^…]:负值字符集结

p1|p2|p3:匹配p1或p2或p3。’z|food’匹配’z’或’food’,'(z|f)ood匹配’zood’或’food’

*:匹配0或多次。’zo*’能匹配’z’或’zoo’,*等价于{0,}

+:匹配1或多次。’zo+’能匹配’zo’或’zoo’,+等价于{1,}

?:匹配0或1次。’zo’能匹配’z’或’zo’,?等价于{0,1}

{n}:n为非负整数,代表相配n次

{n,m}:m,
n均为非负整数,且n<=m,代表最少相称n次,最多相配m次

‘[charlist]’
any individual character in string: WHERE city REGEXP ‘[ALN]’
以“A”或”L“或”N“开始的都市

‘[^charlist]’
any individual character not in string: WHERE city REGEXP ‘[^ALN]’
不以“A”或”L“或”N“开首的都会

 

追寻word字段中以acg字符开头或以’vity’字符串结尾的具备数据:

SELECT 
    word
FROM
    dictionary
WHERE
    word REGEXP '^[acg]|vity$'; 

 

NB

正则表明式无法正确相称'[…]’中的汉字,不亮堂是因为MySQL编写翻译时以ISO-8859作为字符集,照旧因为数据库字符未设置为GBK。

 

 

14.
以时日段为单位取数和FLOOTiguan()函数(向下取整)

 

下礼拜天(Feb 24,
2017)蒙受的面试题:以5分钟为单位取数。

有二个之类的表,以5分钟为单位测算有微微个不另行user_id生成。

+————-+———-+——+—–+———+——-+

| Field | Type |
Null | Key | Default | Extra |

+————-+———-+——+—–+———+——-+

| id | int(11) |
NO | PRI | NULL | |

| user_id |
int(11) | YES | | NULL | |

| price | double
| YES | | NULL | |

| refunded_at |
text | YES | | NULL | |

| created_at |
datetime | YES | | NULL | |

+————-+———-+——+—–+———+——-+ 

SELECT 
    STR_TO_DATE(DATE_FORMAT(FLOOR(created_at / 500) * 500,
                    '%Y-%c-%d %T'),
            '%Y-%c-%d %T') AS every_five_minutes,
    COUNT(DISTINCT user_id) AS count_user_id
FROM
    purchases
GROUP BY 1
ORDER BY 1;

思路:

1)DATETIME类型的字段created_at在除以500,再乘以500后会变为数值,2016年四月4日
03:47:44化为二零一四0804034744.0000。因而在created_at/500时,动用FLOOMurano函数向下取整以抹平余数,再乘以500就可知赢得以500为距离的数字;

2)使用DATE_FORMAT将数字转化为日期;

3)由于DATE_FORMAT转化的日期为文本字符串,不便于排序。因而,再使用STENCORE_TO_DATE转化为日期格式。

 

 

15.
转置表的行和列

 

一样是上周三的面试题:转置大肆二个表的行和列。

假定有贰个之类的course表:

+—–+————–+——+———+

| Cno | Cname |
Cpno | Ccredit |

+—–+————–+——+———+

| 1 | 数据库 | 5
| 4 |

| 2 | 数学 |
NULL | 2 |

| 3 | 新闻种类 |
1 | 4 |

| 4 | 操作系统 |
6 | 3 |

| 5 | 数据结构 |
7 | 4 |

| 6 | 数据管理 |
NULL | 2 |

| 7 | PASCAL语言
| 6 | 4 |

+—–+————–+——+———+

要使其改为:

澳门凯旋门游戏网址 5

能够行使SUM(IF())或CASE
WHEN函数。

SELECT 
    c2 AS '课程',
    SUM(IF(c1 = '数据库', c3, NULL)) AS '数据库',
    SUM(IF(c1 = '数学', c3, NULL)) AS '数学',
    SUM(IF(c1 = '信息系统', c3, NULL)) AS '信息系统',
    SUM(IF(c1 = '操作系统', c3, NULL)) AS '操作系统',
    SUM(IF(c1 = '数据结构', c3, NULL)) AS '数据结构',
    SUM(IF(c1 = '数据处理', c3, NULL)) AS '数据处理',
    SUM(IF(c1 = 'PASCAL语言', c3, NULL)) AS 'PASCAL语言'
FROM
    (SELECT 
        Cname AS c1, 'Cpno' AS c2, Cpno AS c3
    FROM
        course ca UNION ALL SELECT 
        Cname, 'Ccredit' AS c2, Ccredit AS c3
    FROM
        course cb) AS row_column_convert
GROUP BY 1
ORDER BY c2 DESC;

SELECT 
    c2 AS '课程',
    SUM(CASE
        WHEN c1 = '数据库' THEN c3
        ELSE NULL
    END) AS '数据库',
    SUM(CASE
        WHEN c1 = '数学' THEN c3
        ELSE NULL
    END) AS '数学',
    SUM(CASE
        WHEN c1 = '信息系统' THEN c3
        ELSE NULL
    END) AS '信息系统',
    SUM(CASE
        WHEN c1 = '操作系统' THEN c3
        ELSE NULL
    END) AS '操作系统',
    SUM(CASE
        WHEN c1 = '数据结构' THEN c3
        ELSE NULL
    END) AS '数据结构',
    SUM(CASE
        WHEN c1 = '数据处理' THEN c3
        ELSE NULL
    END) AS '数据处理',
    SUM(CASE
        WHEN c1 = 'PASCAL语言' THEN c3
        ELSE NULL
    END) AS 'PASCAL语言'
FROM
    (SELECT 
        Cname AS c1, 'Cpno' AS c2, Cpno AS c3
    FROM
        course ca UNION ALL SELECT 
        Cname, 'Ccredit' AS c2, Ccredit AS c3
    FROM
        course cb) AS row_column_convert
GROUP BY 1
ORDER BY c2 DESC;

 

 

 

 

 

 

 

The trouble is,
you think you have time.

相关文章