什么是SQL？

SQL?(Structured Query Language:結(jié)構(gòu)化查詢語言) 是用于管理關(guān)系數(shù)據(jù)庫管理系統(tǒng)（RDBMS）。 SQL 的范圍包括數(shù)據(jù)插入、查詢、更新和刪除，數(shù)據(jù)庫模式創(chuàng)建和修改，以及數(shù)據(jù)訪問控制。

SQL語法

數(shù)據(jù)庫表

一個(gè)數(shù)據(jù)庫通常包含一個(gè)或多個(gè)表。每個(gè)表有一個(gè)名字標(biāo)識(shí)（例如:"Websites"）,表包含帶有數(shù)據(jù)的記錄（行）。SQL 對(duì)大小寫不敏感：SELECT 與 select 是相同的。

SQL一些最重要的命令（常用）

• SELECT?- 從數(shù)據(jù)庫中提取數(shù)據(jù)
• UPDATE?- 更新數(shù)據(jù)庫中的數(shù)據(jù)
• DELETE?- 從數(shù)據(jù)庫中刪除數(shù)據(jù)
• INSERT INTO?- 向數(shù)據(jù)庫中插入新數(shù)據(jù)
• CREATE DATABASE?- 創(chuàng)建新數(shù)據(jù)庫
• ALTER DATABASE?- 修改數(shù)據(jù)庫
• CREATE TABLE?- 創(chuàng)建新表
• ALTER TABLE?- 變更（改變）數(shù)據(jù)庫表
• DROP TABLE?- 刪除表
• CREATE INDEX?- 創(chuàng)建索引（搜索鍵）
• DROP INDEX?- 刪除索引

select

SELECT 語句用于從數(shù)據(jù)庫中選取數(shù)據(jù)。結(jié)果被存儲(chǔ)在一個(gè)結(jié)果表中，稱為結(jié)果集。

SELECT column1, column2, ...
FROM table_name;

• column1, column2, ...：要選擇的字段名稱，可以為多個(gè)字段。如果不指定字段名稱，則會(huì)選擇所有字段。*為查所有字段。
• table_name：要查詢的表名稱。

select distinct

在表中，一個(gè)列可能會(huì)包含多個(gè)重復(fù)值，有時(shí)您也許希望僅僅列出不同（distinct）的值。DISTINCT 關(guān)鍵詞用于返回唯一不同的值。

SELECT DISTINCT column1, column2, ...
FROM table_name;

• column1, column2, ...：要選擇的字段名稱，可以為多個(gè)字段。如果不指定字段名稱，則會(huì)選擇所有字段。
• table_name：要查詢的表名稱。

SQL WHERE

WHERE 子句用于提取那些滿足指定條件的記錄。

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• column1, column2, ...：要選擇的字段名稱，可以為多個(gè)字段。如果不指定字段名稱，則會(huì)選擇所有字段。
• table_name：要查詢的表名稱。

下面的運(yùn)算符可以在 WHERE 子句中使用：

運(yùn)算符	描述
=	等于
<>	不等于。注釋：在 SQL 的一些版本中，該操作符可被寫成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某個(gè)范圍內(nèi)
LIKE	搜索某種模式
IN	指定針對(duì)某個(gè)列的多個(gè)可能值

SQL AND & OR 運(yùn)算符

如果第一個(gè)條件和第二個(gè)條件都成立，則 AND 運(yùn)算符顯示一條記錄。如果第一個(gè)條件和第二個(gè)條件中只要有一個(gè)成立，則 OR 運(yùn)算符顯示一條記錄。

例子：

SELECT * FROM Websites
WHERE country='CN'
AND alexa > 50;

SQL ORDER BY 關(guān)鍵字

ORDER BY 關(guān)鍵字用于對(duì)結(jié)果集按照一個(gè)列或者多個(gè)列進(jìn)行排序。

ORDER BY 關(guān)鍵字默認(rèn)按照升序?qū)τ涗涍M(jìn)行排序。如果需要按照降序?qū)τ涗涍M(jìn)行排序，您可以使用 DESC 關(guān)鍵字。

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

• column1, column2, ...：要排序的字段名稱，可以為多個(gè)字段。
• ASC：表示按升序排序。
• DESC：表示按降序排序。

SQL INSERT INTO 語句

INSERT INTO 語句用于向表中插入新記錄。

第一種形式無需指定要插入數(shù)據(jù)的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二種形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

參數(shù)說明：

• table_name：需要插入新記錄的表名。
• column1, column2, ...：需要插入的字段名。
• value1, value2, ...：需要插入的字段值。

SQL UPDATE 語句

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name：要修改的表名稱。
• column1, column2, ...：要修改的字段名稱，可以為多個(gè)字段。
• value1, value2, ...：要修改的值，可以為多個(gè)值。
• condition：修改條件，用于指定哪些數(shù)據(jù)要修改。

SQL DELETE 語句

DELETE 語句用于刪除表中的行。

DELETE FROM table_name
WHERE condition;

• table_name：要?jiǎng)h除的表名稱。
• condition：刪除條件，用于指定哪些數(shù)據(jù)要?jiǎng)h除。

高級(jí)操作

SQL SELECT TOP 子句

SELECT TOP 子句用于規(guī)定要返回的記錄的數(shù)目。

SELECT TOP 子句對(duì)于擁有數(shù)千條記錄的大型表來說，是非常有用的。

SELECT column_name(s)
FROM table_name
LIMIT number;

SQL LIKE 操作符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

• column1, column2, ...：要選擇的字段名稱，可以為多個(gè)字段。如果不指定字段名稱，則會(huì)選擇所有字段。
• table_name：要查詢的表名稱。
• column：要搜索的字段名稱。
• pattern：搜索模式。

例子：

SELECT * FROM Websites
WHERE name LIKE '%k';

SQL 通配符

在 SQL 中，通配符與 SQL LIKE 操作符一起使用。

SQL 通配符用于搜索表中的數(shù)據(jù)。

在 SQL 中，可使用以下通配符：

通配符	描述
%	替代 0 個(gè)或多個(gè)字符
_	替代一個(gè)字符
[charlist]	字符列中的任何單一字符
[^charlist] 或 [!charlist]	不在字符列中的任何單一字符

例子：

SELECT * FROM WebsitesWHERE url LIKE 'https%';

IN 操作符

IN 操作符允許您在 WHERE 子句中規(guī)定多個(gè)值。

SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

• column1, column2, ...：要選擇的字段名稱，可以為多個(gè)字段。如果不指定字段名稱，則會(huì)選擇所有字段。
• table_name：要查詢的表名稱。
• column：要查詢的字段名稱。
• value1, value2, ...：要查詢的值，可以為多個(gè)值。

SQL BETWEEN 操作符

BETWEEN 操作符選取介于兩個(gè)值之間的數(shù)據(jù)范圍內(nèi)的值。這些值可以是數(shù)值、文本或者日期。

SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

• column1, column2, ...：要選擇的字段名稱，可以為多個(gè)字段。如果不指定字段名稱，則會(huì)選擇所有字段。
• table_name：要查詢的表名稱。
• column：要查詢的字段名稱。
• value1：范圍的起始值。
• value2：范圍的結(jié)束值。

SQL 別名

通過使用 SQL，可以為表名稱或列名稱指定別名。

基本上，創(chuàng)建別名是為了讓列名稱的可讀性更強(qiáng)。

-- 列的 SQL 別名語法 -->
SELECT column_name AS alias_name
FROM table_name;

-- 表的 SQL 別名語法 -->
SELECT column_name(s)
FROM table_name AS alias_name;

SQL JOIN

SQL JOIN 子句用于把來自兩個(gè)或多個(gè)表的行結(jié)合起來，基于這些表之間的共同字段。

最常見的 JOIN 類型：SQL INNER JOIN（簡(jiǎn)單的 JOIN）。 SQL INNER JOIN 從多個(gè)表中返回滿足 JOIN 條件的所有行。

在 SQL 中使用 INNER JOIN ... ON ... 時(shí)，ON 后面跟的連接條件用于指定如何將兩個(gè)表中的行匹配起來。這個(gè)連接條件通常涉及兩個(gè)表中的一個(gè)或多個(gè)字段，SQL 會(huì)根據(jù)這些條件找到兩個(gè)表中滿足條件的行的交集。

然后，基于這個(gè)條件匹配的結(jié)果集，SELECT 語句后面跟的字段將決定輸出表格中包含哪些列。簡(jiǎn)單來說，INNER JOIN 會(huì)連接兩個(gè)表中滿足 ON 后面條件的那些行，而 SELECT 則用來選擇需要顯示的字段。

SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;

• column1, column2, ...：要選擇的字段名稱，可以為多個(gè)字段。如果不指定字段名稱，則會(huì)選擇所有字段。
• table1：要連接的第一個(gè)表。
• table2：要連接的第二個(gè)表。
• condition：連接條件，用于指定連接方式。

SQL INNER JOIN 關(guān)鍵字

INNER JOIN 關(guān)鍵字在表中存在至少一個(gè)匹配時(shí)返回行。

SQL INNER JOIN 語法

SELECT?column_name(s)
FROM?table1
INNER JOIN?table2
ON?table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

• columns：要顯示的列名。
• table1：表1的名稱。
• table2：表2的名稱。
• column_name：表中用于連接的列名。

SQL LEFT JOIN 關(guān)鍵字

LEFT JOIN 關(guān)鍵字從左表（table1）返回所有的行，即使右表（table2）中沒有匹配。如果右表中沒有匹配，則結(jié)果為 NULL。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL RIGHT JOIN 關(guān)鍵字

RIGHT JOIN 關(guān)鍵字從右表（table2）返回所有的行，即使左表（table1）中沒有匹配。如果左表中沒有匹配，則結(jié)果為 NULL。和left差不多，只不過是反過來

SQL FULL OUTER JOIN 關(guān)鍵字

FULL OUTER JOIN 關(guān)鍵字只要左表（table1）和右表（table2）其中一個(gè)表中存在匹配，則返回行.

FULL OUTER JOIN 關(guān)鍵字結(jié)合了 LEFT JOIN 和 RIGHT JOIN 的結(jié)果。

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL UNION 操作符

UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語句的結(jié)果集。

請(qǐng)注意，UNION 內(nèi)部的每個(gè) SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每個(gè) SELECT 語句中的列的順序必須相同。

SQL UNION 語法

SELECT?column_name(s)?FROM?table1
UNION
SELECT?column_name(s)?FROM?table2;

注釋：默認(rèn)地，UNION 操作符選取不同的值。如果允許重復(fù)的值，請(qǐng)使用 UNION ALL。

SQL UNION ALL 語法

SELECT?column_name(s)?FROM?table1
UNION ALL
SELECT?column_name(s)?FROM?table2;

注釋：UNION 結(jié)果集中的列名總是等于 UNION 中第一個(gè) SELECT 語句中的列名。

MySQL Date 函數(shù)

下面的表格列出了 MySQL 中最重要的內(nèi)建日期函數(shù)：

函數(shù)	描述
NOW()	返回當(dāng)前的日期和時(shí)間
CURDATE()	返回當(dāng)前的日期
CURTIME()	返回當(dāng)前的時(shí)間
DATE()	提取日期或日期/時(shí)間表達(dá)式的日期部分
EXTRACT()	返回日期/時(shí)間的單獨(dú)部分
DATE_ADD()	向日期添加指定的時(shí)間間隔
DATE_SUB()	從日期減去指定的時(shí)間間隔
DATEDIFF()	返回兩個(gè)日期之間的天數(shù)
DATE_FORMAT()	用不同的格式顯示日期/時(shí)間

MySQL?使用下列數(shù)據(jù)類型在數(shù)據(jù)庫中存儲(chǔ)日期或日期/時(shí)間值：

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
• YEAR - 格式：YYYY 或 YY

SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函數(shù)

請(qǐng)看下面的 "Products" 表：

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Jarlsberg	10.45	16	15
2	Mascarpone	32.56	23
3	Gorgonzola	15.67	9	20

假如 "UnitsOnOrder" 是可選的，而且可以包含 NULL 值。

我們使用下面的 SELECT 語句：

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

在上面的實(shí)例中，如果有 "UnitsOnOrder" 值是 NULL，那么結(jié)果是 NULL。

微軟的 ISNULL() 函數(shù)用于規(guī)定如何處理 NULL 值。

NVL()、IFNULL() 和 COALESCE() 函數(shù)也可以達(dá)到相同的結(jié)果。

SQL函數(shù)

AVG() 函數(shù)

AVG() 函數(shù)返回?cái)?shù)值列的平均值。

SQL AVG() 語法

SELECT AVG(column_name) FROM table_name

SQL?COUNT()?函數(shù)

COUNT() 函數(shù)返回匹配指定條件的行數(shù)。

SQL COUNT(column_name) 語法

COUNT(column_name) 函數(shù)返回指定列的值的數(shù)目（NULL 不計(jì)入）：

SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) 語法

COUNT(*) 函數(shù)返回表中的記錄數(shù)：

SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) 語法

COUNT(DISTINCT column_name) 函數(shù)返回指定列的不同值的數(shù)目：

SELECT COUNT(DISTINCT column_name) FROM table_name;

FIRST() 函數(shù)

FIRST() 函數(shù)返回指定的列中第一個(gè)記錄的值。

SQL FIRST() 語法

SELECT FIRST(column_name) FROM table_name;

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

LAST() 函數(shù)

LAST() 函數(shù)返回指定的列中最后一個(gè)記錄的值。

SQL LAST() 語法

SELECT LAST(column_name) FROM table_name;

SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;

MAX() 函數(shù)

MAX() 函數(shù)返回指定列的最大值。

SQL MAX() 語法

SELECT MAX(column_name) FROM table_name;

MIN() 函數(shù)

MIN() 函數(shù)返回指定列的最大值。

SQL MIN() 語法

SELECT MIN(column_name) FROM table_name;

SUM() 函數(shù)

SUM() 函數(shù)返回?cái)?shù)值列的總數(shù)。

SQL SUM() 語法

SELECT SUM(column_name) FROM table_name;

GROUP BY 語句

GROUP BY 語句用于結(jié)合聚合函數(shù)，根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組。

SQL GROUP BY 語法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 關(guān)鍵字無法與聚合函數(shù)一起使用。

HAVING 子句可以讓我們篩選分組后的各組數(shù)據(jù)。

SQL HAVING 語法

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

• column1：要檢索的列。
• aggregate_function(column2)：一個(gè)聚合函數(shù)，例如SUM、COUNT、AVG等，應(yīng)用于column2的值。
• table_name：要從中檢索數(shù)據(jù)的表。
• GROUP BY column1：根據(jù)column1列的值對(duì)數(shù)據(jù)進(jìn)行分組。
• HAVING condition：一個(gè)條件，用于篩選分組的結(jié)果。只有滿足條件的分組會(huì)包含在結(jié)果集中。

EXISTS 運(yùn)算符

EXISTS 運(yùn)算符用于判斷查詢子句是否有記錄，如果有一條或多條記錄存在返回 True，否則返回 False。

SQL EXISTS 語法

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

MID() 函數(shù)

MID() 函數(shù)用于從文本字段中提取字符。

SQL MID() 語法

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

參數(shù)	描述
column_name	必需。要提取字符的字段。
start	必需。規(guī)定開始位置（起始值是 1）。
length	可選。要返回的字符數(shù)。如果省略，則 MID() 函數(shù)返回剩余文本。

LEN() 函數(shù)

LEN() 函數(shù)返回文本字段中值的長(zhǎng)度。

MySQL 中函數(shù)為 LENGTH():

SELECT LENGTH(column_name) FROM table_name;

ROUND() 函數(shù)

ROUND() 函數(shù)用于把數(shù)值字段舍入為指定的小數(shù)位數(shù)。

SQL ROUND() 語法

SELECT?ROUND(column_name,decimals)?FROM?TABLE_NAME;

參數(shù)	描述
column_name	必需。要舍入的字段。
decimals	可選。規(guī)定要返回的小數(shù)位數(shù)。

SQL進(jìn)階

子查詢

子查詢用于為主查詢返回其所需數(shù)據(jù)，或者對(duì)檢索數(shù)據(jù)進(jìn)行進(jìn)一步的限制,通常將一個(gè)查詢（子查詢）的結(jié)果作為另一個(gè)查詢（主查詢）的數(shù)據(jù)來源或判斷條件，常見的子查詢有WHERE子查詢，HAVING子查詢，FROM子查詢，SELECT子查詢，EXISTS子查詢。

子查詢是一種嵌套在其他 SQL 查詢的 WHERE 子句中的查詢，可以在 SELECT、INSERT、UPDATE 和 DELETE 語句中，同邏輯運(yùn)算符一起使用。

使用子查詢必須遵循以下幾個(gè)規(guī)則：

• 子查詢必須括在圓括號(hào)中。

• 子查詢的 SELECT 子句中只能有一個(gè)列。

• 子查詢不能使用 ORDER BY，在子查詢中，GROUP BY 可以起到同 ORDER BY 相同作用。

• 返回多行數(shù)據(jù)的子查詢只能同多值操作符一起使用，比如 IN 操作符。

• 子查詢不能直接用在聚合函數(shù)中。

• BETWEEN 不能同子查詢一起使用，但 BETWEEN 操作符可以用在子查詢中。

• 使用子查詢查詢薪水大于8000的員工的所有信息，首先內(nèi)部查詢薪水大于8000的ID，然后外部使用一個(gè)WHERE查詢即可得到結(jié)果。

SELECT *
FROM SALARY
WHERE ID IN (SELECT IDFROM SALARYWHERE SAL > 8000);

使用子查詢的另一方法是創(chuàng)建計(jì)算字段，創(chuàng)建計(jì)算字段需要使用聚合函數(shù)，例如count，sum，avg，max，min等，這里首先計(jì)算平均薪水作為一個(gè)內(nèi)查詢，然后在外部使用WHERE子句進(jìn)行查詢，得出薪資比平均薪資低的員工的所有信息。

SELECT * FROM SALARY
WHERE SAL < (SELECT AVG(SAL)FROM SALARY);

窗口函數(shù)

窗口函數(shù)與數(shù)據(jù)分組功能相似，可指定數(shù)據(jù)窗口進(jìn)行統(tǒng)計(jì)分析，但窗口函數(shù)與數(shù)據(jù)分組又有所區(qū)別，窗口函數(shù)對(duì)每個(gè)組返回多行，而數(shù)據(jù)分組對(duì)每個(gè)組只返回一行；窗口函數(shù)指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化，而數(shù)據(jù)分組是針對(duì)所有數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，窗口函數(shù)的寫法如下。

<窗口函數(shù)>?over?(partition?by?<用于分組的列名>order?by?<用于排序的列名>)

窗口函數(shù)主要有兩種，一種是專用窗口函數(shù)，包括rank、dense_rank、row_number等。另一種是聚合函數(shù)，包括sum、avg、count、max、min等，下面逐一介紹窗口函數(shù)的五個(gè)功能，分別是聚合、排序、極值、移動(dòng)、切片。

計(jì)算各國家總金額就要對(duì)各個(gè)國家分組，這里分組使用的是PARTITION by，PARTITION by的功能與GROUP BY的功能類似，指定按照那一列進(jìn)行分組，用country分組求和，則每個(gè)country的輸出結(jié)果一致。

SELECT *, SUM(payment) OVER() as Total_payment,
SUM(payment) OVER(PARTITION by country) as country_payment 
from pay;

這里使用SQL中常用的向下累計(jì)求和的方法，當(dāng)使用order by時(shí)，沒有rows between則意味著窗口是從起始行到當(dāng)前行，所以對(duì)不同國家進(jìn)行累加求和操作。

SELECT *, SUM(payment) OVER() as Total_payment,SUM(payment) OVER(PARTITION by country) as country_payment,
SUM(payment) OVER(PARTITION by country ORDER BY payment DESC) as order_payment 
from pay;

基礎(chǔ)查詢

1.查詢所有列

select * from user_profile

2.查詢多列

select device_id,gender,age,university from user_profile

簡(jiǎn)單處理查詢結(jié)果

3.查詢結(jié)果去重

select distinct university from user_profile

4.查詢結(jié)果限制返回行數(shù)

select device_id from user_profile where id<=2

5.將查詢后的列重新命名

select device_id as user_infos_example from user_profile where id <= 2

基礎(chǔ)操作符

6.查找學(xué)校是北大的學(xué)生信息

select device_id,university from user_profile where university = '北京大學(xué)'

7.查找年齡大于24歲的用戶信息

8.查找某個(gè)年齡段的用戶信息

select device_id,gender,age from user_profile where age >=20 and age <= 23

9.查找除復(fù)旦大學(xué)的用戶信息

select device_id,gender,age,university from user_profile where university != '復(fù)旦大學(xué)'

10.用where過濾空值練習(xí)

select device_id,gender,age,university from user_profile where age!=' '

11.高級(jí)操作符練習(xí)（1）

select device_id,gender,age,university,gpa from user_profile where gpa > 3.5 and gender = 'male'

12.高級(jí)操作符練習(xí)（2）

select device_id,gender,age,university,gpa from user_profile where gpa > 3.7 or university = '北京大學(xué)'

13.where in 和 Not in

select device_id,gender,age,university,gpa from user_profile where university in ('北京大學(xué)','復(fù)旦大學(xué)','山東大學(xué)')

14.操作符混合運(yùn)用

select device_id,gender,age,university,gpa from user_profile where (gpa > 3.5 and university = '山東大學(xué)') or (gpa > 3.8 and university = '復(fù)旦大學(xué)')

15.查看學(xué)校名稱中含北京的用戶

select device_id,age,university from user_profile where university like '%北京%'

16.查找GPA最高值

select max(gpa) from user_profile 

17.計(jì)算男生人數(shù)以及平均GPA

select count(gender) as male_num,avg(gpa) 
from user_profile
where gender = 'male'

18.分組計(jì)算練習(xí)題

select gender,university,count(device_id) as user_num,avg(active_days_within_30) as avg_active_day,avg(question_cnt) as avg_question_cnt
from user_profile
group by gender,university

19.分組過濾練習(xí)題

select university,avg(question_cnt) as avg_question_cnt,avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt < 5 
or avg_answer_cnt < 20

20.分組排序練習(xí)題

select university,avg(question_cnt) as avg_question_cnt
from user_profile
group by university
order by avg_question_cnt 

21.浙江大學(xué)用戶題目回答情況

select device_id, question_id, result
from question_practice_detail
where device_id in (select device_id from user_profilewhere university = '浙江大學(xué)'
)

22.統(tǒng)計(jì)每個(gè)學(xué)校的答過題的用戶的平均答題數(shù)

select university, 
count(b.question_id)/count(distinct a.device_id) as avg_answer_cnt
from user_profile as a
inner join question_practice_detail as b
on a.device_id = b.device_id
group by a.university

23.統(tǒng)計(jì)每個(gè)學(xué)校各難度的用戶平均刷題數(shù)

select a.university,
c.difficult_level,
count(b.question_id) / count(distinct b.device_id) as avg_answer_cnt
from question_practice_detail as b
left join user_profile as a
on a.device_id = b.device_id
left join question_detail as c
on b.question_id =  c.question_id
group by a.university, c.difficult_level

24.統(tǒng)計(jì)每個(gè)用戶的平均刷題數(shù)

select university, difficult_level, count(b.question_id)/count(distinct b.device_id) as avg_answer_cnt
from question_practice_detail as b,user_profile as a,question_detail as c
where a.university = '山東大學(xué)'and b.device_id = a.device_idand b.question_id = c.question_id
group by c.difficult_level

25.查找山東大學(xué)或者性別為男生的信息

select device_id, gender, age, gpa
from user_profile
where university = '山東大學(xué)'union allselect device_id, gender, age, gpa
from user_profile
where gender = 'male'

26.計(jì)算25以上和以下的用戶數(shù)量

select 
case when age <25 or age is null then '25歲以下'when age >= 25 then '25歲及以上'end age_cut,count(*) as number
from user_profile
group by age_cut

27.查看不同年齡段的用戶明細(xì)

select device_id,gender,
case when age >=20 and age <=24 then '20-24歲'when age >24 then '25歲及以上'else '其他'end age_cut
from user_profile

28.計(jì)算用戶8月每天的練題數(shù)量

select day(date) as day, count(question_id) as question_cnt
from question_practice_detail
where year(date) = 2021 
and  month(date) = 8
group by date

29.計(jì)算用戶的平均次日留存率

SELECT COUNT(distinct q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 left outer join question_practice_detail as q2
on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1

30.統(tǒng)計(jì)每種性別的人數(shù)

select substring_index(profile,',',-1) as gender,
count(*) as number
from user_submit
group by gender