SQL--数据分析必会篇

1.选取数据前几行

• select * from tablename LIMIT 2;
• oracle里用ROWNUM

2.虚拟查询

• SELECT * FROM Websites WHERE name LIKE 'G%';
• 
• REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式

3.正则表达式匹配的字符类：

• .：匹配任意单个字符。
• ^：匹配字符串的开始。
• $：匹配字符串的结束。
• *：匹配零个或多个前面的元素。
• +：匹配一个或多个前面的元素。
• ?：匹配零个或一个前面的元素。
• [abc]：匹配字符集中的任意一个字符。
• [^abc]：匹配除了字符集中的任意一个字符以外的字符。
• [a-z]：匹配范围内的任意一个小写字母。
• \d：匹配一个数字字符。
• \w：匹配一个字母数字字符（包括下划线）。
• \s：匹配一个空白字符。

例：

• 下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站

SELECT * FROM Websites WHERE name REGEXP '^[GFs]';

• SQL 语句选取 name 以 A 到 H 字母开头的网站：

SELECT * FROM Websites WHERE name REGEXP '^[A-H]';

4.in操作符

• 选取 name 为 "Google" 或 "菜鸟教程" 的所有网站：

SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程');

• 选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站：

SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');

• 选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站：

SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';

5.起别名

• 我们把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名：

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;

• 选取 "菜鸟教程" 的所有访问记录。我们使用 "Websites" 和 "access_log" 表，并分别为它们定表别名 "w" 和 "a"（通过使用别名让 SQL 更简短）

SELECT w.name, w.url, a.count, a.date

FROM Websites AS w, access_log AS a

WHERE a.site_id=w.id and w.name="菜鸟教程";

6.连接

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行（mysql中不支持）

语句：

SELECT Websites.name, access_log.count, access_log.date

FROM Websites

INNER JOIN access_log

ON Websites.id=access_log.site_id

ORDER BY access_log.count;

7.UNION操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集，并去除重复的行。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。允许重复的值，请使用 UNION ALL。

SELECT country, name FROM Websites

WHERE country='CN'

UNION ALL

SELECT country, app_name FROM apps

WHERE country='CN'

ORDER BY country

8.SQL约束

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

主键必须是唯一的且不能为空的，必须存在的

• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

FOREIGN KEY 约束用于预防破坏表之间连接的行为。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一

• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。
• 添加 NOT NULL 约束（删除的话删除NOT就行）：

ALTER TABLE Persons

MODIFY Age int NOT NULL;

• 当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束，（主键同理）请使用下面的 SQL：

ALTER TABLE Persons

ADD UNIQUE (P_Id)；

• 如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：：

ALTER TABLE Persons

ADD CONSTRAINT uc_PersonID（名字） UNIQUE (P_Id,LastName)

• 撤销约束

ALTER TABLE Persons

DROP INDEX uc_PersonID

• 撤销主键

ALTER TABLE Persons

DROP PRIMARY KEY

• 表已创建，创建主键

ALTER TABLE Orders

ADD FOREIGN KEY (P_Id)

 REFERENCES Persons(P_Id)

9.索引

更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

• 在表上创建索引，允许重复值

CREATE INDEX index_name

ON table_name (column_name)

• 在表上创建索引，不允许重复值

CREATE UNIQUE INDEX index_name

 ON table_name (column_name)

10.drop

DROP 语句，可以轻松地删除索引、表和数据库

• DROP INDEX index_name ON table_name
• DROP TABLE table_name
• DROP DATABASE database_name

如果我们仅仅需要删除表内的数据，但并不删除表本身，那么我们该如何做

• TRUNCATE TABLE table_name

11.递增创建主键

Auto-increment 会在新记录插入表中时生成一个唯一的数字

12.视图

视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段

注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

• 创建

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

• 更新

CREATE OR REPLACE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

• 删除

DROP VIEW view_name

13.日期函数

•  用year/month函数的year(date)=2021 and month(date)=8转换
• 用date_format函数的date_format(date, "%Y-%m")="202108"

现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

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

14.NULL函数

• isnull()--检查是否为空

SELECT * FROM employees WHERE department_id IS NULL;

SELECT* FROM employees WHERE department_id IS NOT NULL;

• 当有两列数据相加，但有一列是NULL值时：

MySQL：

SELECT product_name, COALESCE(stock_quantity, 0)AS actual_quantity FROM products;

SQL:

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))

FROM Products

15.聚合函数

（1）SQL Aggregate 函数计算从列中取得的值，返回一个单一的值：

• AVG() - 返回平均值

SELECT AVG(column_name) FROM table_name

• COUNT() - 返回行数
• FIRST() - 返回第一个记录的值
• LAST() - 返回最后一个记录的值
• MAX() - 返回最大值
• MIN() - 返回最小值
• SUM() - 返回总和

（2）SQL Scalar 函数基于输入值，返回一个单一的值：

• UCASE() - 将某个字段转换为大写
• LCASE() - 将某个字段转换为小写
• MID() - 从某个文本字段提取字符，MySql 中使用

      从name列中提取前4个字符：

SELECT MID(name,1,4) AS ShortTitle

FROM Websites;

• SubString(字段，1，end) - 从某个文本字段提取字符
• LEN() - 返回某个文本字段的长度
• ROUND() - 对某个数值字段进行指定小数位数的四舍五入

• ROUND(X)： 返回参数X的四舍五入的一个整数。

• ROUND(X,D)： 返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

• NOW() - 返回当前的系统日期和时间
• FORMAT() - 格式化某个字段的显示方式

• 从 "Websites" 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期：

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date

FROM Websites;

16.分组函数

SELECT site_id, SUM(access_log.count) AS nums

FROM access_log GROUP BY site_i

多表链接：

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log

LEFT JOIN Websites

ON access_log.site_id=Websites.id

GROUP BY Websites.name;

• WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

• 例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

SELECT name, SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

• 其中记录 NULL 表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);参数说明：如果 a==null，则选择 b；如果 b==null,则选择 c；如果  a!=null,则选择 a；如果 a b c 都为 null ，则返回为 null（没意义）。

SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM  employee_tbl  GROUP BY name WITH ROLLUP;

17.replace函数

REPLACE() 函数用于替换字符串中的指定子字符串。它接受三个参数：原始字符串、要被替换的子字符串和替换后的新子字符串。

 ---替换字符串中的单个子字符串

SELECT REPLACE('Hello World', 'World', 'Universe'); -- 输出: Hello Universe

17.Having语句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

1.where在group by前， having在group by 之后

2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log

INNER JOIN Websites

ON access_log.site_id=Websites.id)

GROUP BY Websites.name

HAVING SUM(access_log.count) > 200;

• 查找总访问量大于 200 的网站，并且 alexa 排名小于 200。

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites

INNER JOIN access_log

ON Websites.id=access_log.site_id

WHERE Websites.alexa < 200 

GROUP BY Websites.name

HAVING SUM(access_log.count) > 200;

18.EXISTS

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False

• 查找总访问量(count 字段)大于 200 的网站是否存在。

SELECT Websites.name, Websites.url

FROM Websites

WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id =     access_log.site_id AND count > 200);

• EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录：

SELECT Websites.name, Websites.url

FROM Websites

WHERE NOT EXISTS (SELECT count FROM access_log WHERE Websites.id =    access_log.site_id AND count > 200);

19.substring_index函数

SUBSTRING_INDEX(str, delim, count)

其中，

• str：要被截取的原始字符串。
• delim：指定的分隔符。
• count：指定的出现次数，用于确定返回的子串是在分隔符之前还是之后。
• count=2:取前两个 -2：后两个

20.datediff函数

datediff 函数是一种用于计算两个日期之间的天数差异的函数。在不同的编程语言和数据库中，其具体实现方式可能会有所不同，但通常都需要输入两个日期作为参数，然后返回它们之间相差的天数。

DATEDIFF('2022-12-31', '2022-12-01')