SQL学习整理

参考菜鸟教程：https://www.runoob.com/sql/

 

以下语句中"//"表示有多个选项可选，"《》"表示便于理解而自定义的解释,有很多省略和简写，仅供参考

sql通识：
sql以";"为结尾，不区分大小写，但是最好将关键字大写，其他参数小写，同时注意换行，以保证更高的可读性。
单行注释写在“--”之后，多行注释用/* */
数值和字符是不同的因此1<>'1',具体使用的时候注意定义的是字符还是数字,字符在单引号内，数字不需要用引号。
时间类型一般采用-分隔
关键字书写顺序为：SELECT>FROM>WHERE>GROUP BY>HAVING>ORDER BY
关键字实际执行的顺序可能为：FROM>WHERE>GROUP BY>HAVING>SELECT>ORDER BY
先基于原始已有的数据，对数据源定位，然后筛选选择符合条件的源数据，对源数据进行分组，对分组的数据进行再筛选，select需要的列,最后对数据进行排序

算数运算符：
加减乘除+-*/，对NULL的任何运算结果都为NULL,包括null/0，在函数中参数为NULL值的结果一般也为NULL。

比较运算符：
<>表示不等于。>=,<=,注意=在后面，不可颠倒顺序。
日期也可以使用比较运算符，time<'2020-1-1'表示2020.1.1之前。
不能对NULL使用比较运算符，当数据为null时，使用条件WHERE column_name <>2不会因为数据不等于2而取出值为null的数据，为了取出值为null的数据应该使用专用的IS NULL或者IS NOT NULL语句，注意始终将要NULL数据特殊处理

逻辑运算符：
在逻辑运算符中应减少NOT的使用，NOT可以直接用相反的逻辑运算符来表示。
AND优先级高于OR，使用多个逻辑运算符时应该用（）来减少歧义和错误，
同样的对NULL不能直接进行逻辑运算判断，NULL既不是真也不是假，应该先用IS NULL判断真假

定义数据库：
CREATE DATEBASE datebase_name;
定义表结构：
CREATE TABLE table_name
(
column_name1  data_type(size)  constraint_name,
column_name2  data_type(size)  constraint_name,
....
);

数据类型和长度： 
 

SQL约束：
NOT NULL -                                      指示某列不能存储 NULL 值。
UNIQUE -                                        保证某列的每行必须有唯一的值。
PRIMARY KEY -                                     NOT NULL 和 UNIQUE 的结合。      确保某列（或两个列多个列的结合）有唯一标识，
FOREIGN KEY -                                   保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK -                                         保证列中的值符合指定的条件。
DEFAULT -                                       规定没有给列赋值时的默认值

修改表结构：
ALTER TABLE table_name ADD//DROP《删除或者新增列》 （column_name1，column_name2）《多个列的话用括号》

删除表：
DROP TABLE table_name
 

插入：
INSERT  INTO  table_name (column1,column2,column3,...)《全部列插入可省略列名》
VALUES  (value1,value2,value3,...);

插入NULL：
前提是不能设置NOT NULL约束

插入默认值：
建表时设置DEFAULT<默认值>
隐式：不填写就不插入，自动为默认值
显式：对应字段填写为DEFAULT，对应默认值

插入其他表中查询到的数据：
INSERT INTO tn1 (cn1,cn2)
SELECT cn1,cn2
FROM tn2
查询数据还可以使用其他任何语法，但是ORDER BY排序无效,数据无法按照顺序插入表
 

删除：

DELETE  FROM table_name
WHERE  some_column=some_value;《若无限定条件将删除所有的记录》
只能使用WHERE,不能使用GROUP BY,HAVING,ORDER BY,因为很明显后三者是查询的数据分布，不影响实际数据内容

修改：

UPDATE  table_name
SET  column1=value1,column2=value2,...
WHERE  some_column=some_value;《不写条件会改很多值》
分组和排序和DELECT相似，同理后三者不能使用
NULL值问题和INSTER类似

SQL聚合函数：
聚合函数会将多行汇聚为一行，因此数据行间的对应关系会发生改变，会导致其他列的数据无法正确对应本该对应的聚合列，因此不能在使用聚合函数后对其他进行查询，直接导致了后面的相关查询限制和错误，

SELECT  
    //DISTINCT可以用于所有的函数中除去重复值

    // COUNT(column_name)《返回表中指定列不为NULL的行数》
    // COUNT(DISTINCT column_name)《返回表中指定列不重复的行数》
    // COUNT(*)《返回表中包括NULL的总行数》

    // AVG(column_name)《平均值,NULL值会忽略不会拉低平均值》 
    // AVG(DISTINCT column_name)《会除掉重复再算平均值,会影响平均值》
    // MAX(column_name)《最大值》

    // MIN(column_name)《最小值》

    // SUM(column_name)《指定列的合计值》(

 FROM table_name

查询案例：
SELECT  DISTINCT《去重取值》 TOP number《SQL server限制查询数量》  column_name  AS《1，若as后为别名，常用于函数值的计算结果，简化表达式，2，若as前为常量的话，可以用常量替换该列数据，3，as前的列名还可以进行算数操作》  alias_name
FROM  table_name
WHERE  column_name = value  AND  //  OR
// column_name （NOT） LIKE《模糊查询，使用通配符来表示value》  value
// column_name  IN《子集取值》   (value1,value2)
// column_name  BETWEEN《范围内取值》 value1 AND value2
// ROWNUM《oracle限制查询数量》 <= number;
// LIMIT《MySQL限制查询数量》 number
ORDER  BY  column_name  ASC  //  DESC《降序排列》

牢牢记住：FROM>WHERE>GROUP BY>HAVING>SELECT>ORDER BY的执行顺序

分组查询：
GROUP BY就像是一把分割刀，会重置表结构，将指定列相同的分为同一类
SELECT column_name,COUNT(*) AS number
FROM table_name
WHERE ...
GROUP BY  column_name
先进行WHERE查询，再对指定列相同的内容进行分组，再对每一组进行行数计算，得到不同分组的名字和该分组对应的数量，如果存在NULL值也会被单独分组，并且列出来NULL的数量

分组查询和聚合函数的常见错误：
1，不能在WHERE中使用聚合函数，而应该用后续的HAVING
2，使用聚合函数时，不能在select中使用非聚合键，因为非聚合键无法形成一对一关系
3, 不能在GROUP BY后使用别名，不能在group by 排序

错误示范：
SELECT column_name1 AS cn,column_name2,COUNT(column_name1) AS number
FROM table_name
WHERE COUNT(column_name1) = 2
GROUP BY cn

正确示范：
1，想写在WHERE中使用聚合函数改到HAVING中
2，不要查询非聚合键
SELECT column_name1 COUNT(column_name1) AS number
FROM table_name
GROUP BY column_name1
HAVING COUNT(column_name1)=2 
这样才能正确的查询行数为2的组了

HAVING子句的构成要素：

常数，聚合函数，聚合键
如果包含非聚合键是错误的，可以将HAVING子句的起点理解为GROUP BY的结果，GROUP BY结果是他的前提，语句执行的前后顺序将影响语法的正确性

综合来看：

使用聚合函数后若子语句构成要素中是聚合键的话，HAVING和WHERE是都可以使用的，但是优先使用WHERE,因为WHERE是面对所有的数据行，而HAVING是面对的数据组，若子语句包含要素有聚合函数就要用HAVING，若子语句包含要素无聚合键，无聚合函数就要用WHERE

ORDER BY（c_n1,cn_2）排序：
ORDER BY 默认是升序的ASC,也可以使用DESC降序
NULL值根据不同的数据库可能在最前或者最后
用逗号分隔列名还可以同时对多个列排序
也可以在子语句中使用聚合函数

事物：
每个数据操作都可以理解为事物，事物以原子性改变了数据库，可以类比为GIT的版本控制，
不同的是数据库使用COMMIT后将无法回退，因此COMMIT前务必确认没有问题，一旦COMMIT将很难回退到操作前，而未COMMIT的操作还可以使用ROLLBACK来回退。

视图：
视图可以理解为通过查询得到的虚拟表，类似于子查询，因此视图是可以多层嵌套的，但是由于性能原因不建议嵌套。可以直接将视图中的数据当做子查询的结果来使用，他的数据是实际存储在其他真实存在的表中的，本身没有存储数据，视图相当于提前查询出来了常用的数据模型来方便使用，

创建视图：
CREATE VIEW view_name(cn1,cn2) AS 
SELECT cn3,cn4
FROM tn
相当于建立了一个虚拟的新表view_name,包含cn1和cn2

使用视图查询：
SELECT cn1,cn2 FROM view_name

视图的限制：
1，定义视图的时不能使用OEDER BY
2，对视图进行更新时需要保证：SELECT子句未使用DISTINCT,FROM只有一张表，未使用GROUP BY，HAVING子句

删除视图：
DROP VIEW view_name
如果视图存在关联，底层视图将无法直接删除

子查询：

函数：

数值操作
绝对值        ABS（数值）
求余数        MOD(被除数，除数)
四舍五入      ROUND(数值，保留位数)

字符串拼接
str1 || str2                NULL值参与拼接时结果为NULL
str1 + str2                 sql server
CONCAT(str1,str2)           mysql
字符串长度    LENGTH(str)
小写转换      LOWER(str)
大写转换      UPPER(str)
字符串截取    SUBSTRING(str,起始位,位数)

时间日期:    各个数据库语法差距很大    
日期         CURRENT_DATE   2020-11.12
时间         CURRENT_TIME   21.12.11.3400000
截取日期元素

类型转换      CAST(前类型，转换后类型)

谓词：
匹配符        LIKE

SQL通配符：
%                                 替代 0 个或多个字符
_                                 替代一个字符
[charlist]                        字符列中的任何单一字符
[^charlist]或[!charlist]               不在字符列中的任何单一字符

范围匹配      BETWEEN 1 AND 2
子值匹配      IN(s1,s2)

判断真假值    EXIST 子语句             

多分枝        CACE

多表间操作
表的加减法

表取并集
UNION操作的两张表结构必须完全相同,不写ALL时将抛弃重复行，写上ALL保留重复行
SELECT ...
UNION // ALL
SELECT ...

表取交集
取相同的数据行
SELECT ...
INTERSECT
SELECT ...

表相减
前面的表减去后面表，不要求前面的表包含后面的表，只会在前表中减去二者相同的数据行
SELECT ...
EXCEPT
SELECT ...

表的连接：
通过两张表中存在相同的列，将两张表中的数据集中到一张表，新表包含二者的信息，

Join联接：

INNER JOIN：
内连接，如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

FULL JOIN：只要其中一个表中存在匹配，则返回行

Joinl连接使用：

SELECT  column_name(s)  FROM table1
（INNER）  JOIN《有INNER效果一样，等同JOIN。取两个表的交集》  table2
ON  table1.column_name=table2.column_name;

左连接：

SELECT  column_name(s) FROM table1
LEFT JOIN 《以左表为主体，返回满足条件的所有组合，左表中所有的记录都会被与右表组合，其中若右表没有符合条件的值对应的列为NULL》

右连接：

//RIGHT JOIN《与左连接相反》