CTGU数据库小结

数据库sql部分

只写到了一些自己曾经很模糊的地方，大佬轻点喷，如果错误还需指正。

1.distinct

distinct一般是用来去除查询结果中的重复记录，在insert，update，select，delete中只有select可以使用。

select distinct expression[,expression...] from tables [where conditions];

只对一列进行操作

select distinct country from person

对多列进行操作

select distinct country, province from person

注意：distinct用于多个字段的时候，其应用的范围是其后面所有的字段，而不是紧挨着的字段。distinct只能放到所有字段的前面。

SELECT country, distinct province from person; // 该语句是错误的

另外distinct不对NULL进行过滤。

2.group by

GROUP BY我们可以先从字面上来理解，GROUP表示分组，BY后面写字段名，就表示根据哪个字段进行分组，GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。

常用的聚合函数

• count() 计数
• sum() 求和
• avg() 平均数
• max() 最大值
• min() 最小值

语法

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

HAVING

我们提到group by的时候，就不得不提及到HAVING，HAVING相当于条件筛选，但它与where的区别是HAVING是对group对象进行筛选。

group by的用法

3.子查询

子查询是指出现在其他SQL语句内的SELECT语句。

例如：

select * from t1 where col1=(select col2 from t2)

子查询指嵌套在查询内部，且必须始终出现在圆括号内，子查询可以分为四种：

• 标量子查询：返回单一值的标量，最简单的形式
• 列子查询：返回的结果集是N行一列
• 行子查询：返回的结果集是一行N列
• 表子查询：返回的结果是N行N列

带比较运算符的子查询

运算符	说明
>	大于
>=	大于或等于
=	等于
!=或<>	不等于
<	小于
<=	小于或等于

带有比较运算符的子查询是指父查询和子查询使用比较运算符连接的嵌套查询；

使用场景：当用户能够确切的知道内层查询返回的是单个值时,可以使用比较运算符。

ALL,ANY,SOME,IN 四种关键字

因为列子查询返回的结果是N行一列，因此不能直接使用运算法进行操作，在列子查询中我们可以使用ALL，ANY，SOME，IN等关键字操作符。

ALL

ALL必须接在一个比较运算符的后面，表示与子查询返回的所有值比较都为TRUE则返回TRUE。

SELECT col1 FROM table1 WHERE col1 > ALL (SELECT col2 FROM table2)

意思是col1中的值必须要大于col2中所有的值才会被返回

ANY和SOME

ANY与比较操作符联合使用，表示与子查询返回的任何值比较为TRUE，则返回TRUE。

SOME是ANY的别名，一般用的比较少。

IN

IN的意思就是指定的一个值是否在这个集合中，如果在就返回true，否则就返回false。

in是=any的别名，二者相同，但not in的别名不是<>any而是<>some.

4.exists

我一直都很不理解exists和in的区别，感觉都是子查询需要用到的东西，所以在这里写一下。

in

确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

SELECT
    *
FROM
    `user`
WHERE
    `user`.id IN (
        SELECT
            `order`.user_id
        FROM
            `order`
    )

这条语句很简单，通过子查询查到的user_id的数据去匹配user表中的id然后得到结果。

那么他的查询的顺序是什么样子的呢？

1.首先执行子查询

      SELECT
            `order`.user_id
        FROM
            `order`

2.将结果和user表做一个笛卡尔积

3.此时，再根据我们的user.id in order.user_id的条件，对结果进行筛选，最后得到符合条件的数据。

exists

指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

SELECT
    `user`.*
FROM
    `user`
WHERE
    EXISTS (
        SELECT
            `order`.user_id
        FROM
            `order`
        WHERE
            `user`.id = `order`.user_id
    )

注意：使用in和exists返回的结果是一样的

但是他们的执行流程却不一样

使用exists关键字进行查询的时候，首先，我们先查询的不是子查询的内容，而是查我们的主查询的表，也就是说，我们先执行的sql语句是：

SELECT `user`.* FROM `user` 

然后，根据表的每一条记录，执行以下语句，依次去判断where后面的条件是否成立：

EXISTS (
        SELECT
            `order`.user_id
        FROM
            `order`
        WHERE
            `user`.id = `order`.user_id
    )

如果成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回。

exists并不返回结果集，他只返回true或者false

区别和应用场景

in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

5.like

like用于where表达式中，搜索匹配的字段中的指定的内容。其中not like和like的表达的意思正好相反。

like通常与通配符%一起结合使用，如果不加%，则可以理解为精确匹配，即=

SELECT * FROM [user] WHERE u_name LIKE '%三%' //找出u_name中含有三的所有值
SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'  //u_name中不仅含有三而且含有猫的所有字段的值
SELECT * FROM [user] WHERE u_name LIKE '_三_' //表示找出长度为3且汉字三在中间的数据
SELECT * FROM [user] WHERE u_name LIKE '[张李王]三' //类似于正则表达式 可以找出张三 李三 王三等字段
SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三' //找出除了张三 李三 王三 的其他 带三的字段值