SQL 练习记录

SQL技能在很多岗位都有涉及，如 数据分析师、DBA、研发、大数据工程师.... 不同的岗位对知识的要求不尽相同，本文关注点目前在于 数据分析、取数、查询等日常操作上。

大学时期虽然有学习过数据库课程（其中对SQL有所涉及），但工作中使用场景不多，存在一些似是而非的概念，因此通过刷leetcode来加深对Mysql SQL查询的学习，以下是学习过程z

一、SQL的关键字顺序和执行顺序

关键字顺序：

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit …

执行过程顺序：

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit …

二、连接查询

1、交叉连接（笛卡尔积），cross join

相当于两个矩阵的乘积。

SELECT * FROM table1 CROSS JOIN table2  
SELECT * FROM table1 JOIN table2  
SELECT * FROM table1,table2

2、内连接，inner join

相当于excel vlookup在SQL的应用。

SELECT fieldlist
FROM table1 [INNER] join table2 ON table1.column=table2.column

3、外连接

1）左连接，left join

以左表为基准，返回左表的全部内容；对于右表，根据on匹配上的行进行返回，没有的列值为null。

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column

仅在左表且不在右表的内容，使用where对null进行判断。

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column where table2.column is null. 

2）右连接，right join

以右表为基准，返回右表的全部内容；对于左表，根据on匹配上的行进行返回，没有的列值为null。

SELECT column_name FROM table1 
RIGHT [OUTER] JOIN table2 ON table1.column=table2.column;

右表独有：

SELECT column_name FROM table1 
RIGHT [OUTER] JOIN table2 
ON table1.column=table2.column 
where table1.column is null;

4、全连接

mysql不支持全连接(full join，oracle支持），但可以通过左外连接+ union+右外连接实现；

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。（UNION ALL，不去重）。

所以左右连接中重合的部分被自动删除了，得到一个全集。

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id;

5、自连接

通过使用别名，把一张表当成多张不同的表进行跨表查询

SELECT A.column, B.column 
FROM table A, table B
WHERE A.column = B.column;

三、筛选，where 、on、using 、having

1、where、on、using

where：对两表Join后的结果进行筛选
on：筛选条件是在连接前的话，先用On，对两表连接后的筛选用where
using：当相连接的两个表中有同名的列时，为了避免结果出现重复列，使用using指定列来去重

SELECT * FROM table1 
RIGHT JOIN table2 using(cno);

2、where 和 having的区别

从整体声明的角度来理解：

Where是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用聚合函数

Having是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用聚合函数，放在GROUP BY的后面。

所谓聚合函数，是对一组值进行计算并且返回单一值的函数：sum---求和，count---计数，max---最大值，avg---平均值等。

四、分组，Group by A,B,C

Group by A：按照A列来排序；

Group by A,B：按照A列排序后再按照B列排；

……

五、聚合函数

1、按条件求和SUM()

select DATE_FORMAT(trans_date,'%Y-%m') as month,
country,count(state) as trans_count,
sum(state='approved') as approved_count, 
sum(amount) as trans_total_amount,sum(if(state='approved',amount,0))as approved_total_amount from Transactions
group by month,country;

sum(state='approved') ：计算列state枚举值为approved的行数；

sum(if(state='approved',amount,0))：当列state枚举值为approved时，求和列amount的值；

2、按条件计数count()

count(if(expression,1,null))

3、按条件求平均

avg(column)：常规的求平均
avg(expression)：
AVG(column<3)：小于3的值占全部的平均比例
AVG(column1=value1)：等于value的值占全部的平均比例
AVG(IF(productline='Classic Cars',buyprice,NULL))：求列值为Classic Cars时的金额平均值

4、group_concat() 和 concat()

concat和group_concat都是用在sql语句中做拼接使用的，但是两者使用的方式不尽相同，concat是针对以行数据做的拼接，而group_concat是针对列做的数据拼接，且group_concat自动生成逗号

六、视图和存储过程

1、视图

虚拟表，基于原表虚拟出来的一张表，可以是原表的全部或部分数据，不会对原表中的数据产生任何改变；可用于将常用的查询/部分常用数据放置在视图中，便于日常高效的查询。

2、存储过程

存储过程类似于函数，是一组sql命令的封装，在使用时通过存储过程指令来执行一组sql语句。

七、MySQL有关权限的表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

db权限表：记录各个帐号在各个数据库上的操作权限。

table_priv权限表：记录数据表级的操作权限。

columns_priv权限表：记录数据列级的操作权限。

host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

附：

一条SQL语句的执行过程：
https://pdai.tech/md/db/sql-mysql/sql-mysql-execute.html
一条SQL语句的解析过程：
https://pdai.tech/md/db/sql-mysql/sql-mysql-sql-parser.html