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')