MySQL学习笔记（派生表、JOIN、UNION、函数、CTE、存储过程、事务、锁）_mysql cte 事务

MySQL笔记

1. 派生表

派生表是从SELECT语句返回的虚拟表，类似于临时表，但是派生表不需要创建临时表的步骤。 （当SELECT语句中的FROM使用独立子查询时，我们将它成为派生表）
例如：

SELECT column_list  
FROM (  
    SELECT column_list  
    FROM table_1  
) derived_table_name  
WHERE derived_table_name.c1>0;  
1
2
3
4
5
6

与子查询不同，派生表必须有别名，以便稍后再查询中引用该派生表。

派生表示例：
查询2013年销售收入最高的前5名产品

SELECT  
    productCode,  
    ROUND(SUM(quantityOrdered * priceEach)) sales  
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2013
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;
1
2
3
4
5
6
7
8
9
10
11
12

1. 连接两表：INNER JOIN
2. 限制条件为2013年：YEAR(shippedDate) = 2013
3. 计算产品销量：GROUP BY productCode 并使用SUM()函数
4. 取前5名：ORDER BY sales 并 LIMIT 5

使用派生表将以上表与另一个products表关联：

SELECT 
    productName, sales
FROM
    (SELECT  
        productCode,  
        ROUND(SUM(quantityOrdered * priceEach)) sales  
    FROM
        orderdetails
            INNER JOIN
        orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2013
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2013
INNER JOIN products 
USING (productCode);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2. MYSQL JOIN

1. inner join：内连接，取得两个表中存在连接匹配关系的记录
2. left join：左连接，以左表为基准，取左表的所有记录，右表匹配向左表
3. right join：右连接，同上

使用示例：
SELECT * FROM article INNER JOIN user ON article.uid = user.uid
或
SELECT * FROM article INNER JOIN user USING(uid)

3. MYSQL函数

数学函数

• ABS(x) 返回x的绝对值
• BIN(x) 返回x的二进制 (OCT八进制，HEX十六进制)
• CEILING(x) 返回大于x的最小整数值
• EXP(x) 返回值e的x次方
• FLOOR(x) 返回小于x的最大整数值
• GREATEST(x1,x2,…,xn) 返回集合中最大的值
• LEAST(x1,x2,…,xn) 返回集合中最小的值
• LN(x) 返回x的自然对数
• LOG(x,y) 返回x的以y为底的对数
• MOD(x,y) 返回x/y的模（余数）
• PI() 返回pi的值（圆周率）
• RAND() 返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
• ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
• SIGN(x) 返回代表数字x的符号的值
• SQRT(x) 返回一个数的平方根
• TRUNCATE(x,y) 返回数字x截短为y位小数的结果

聚合函数(与GROUP BY一起使用)

• AVG(col) 返回指定列的平均值
• COUNT(col) 返回指定列中非null的值的个数
• MIN(col) 返回指定列的最小值
• MAX(col) 返回指定列的最大值
• SUM(col) 返回指定列的所有值之和
• GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

控制流函数

• CASE WHEN[test1] THEN[result1] … ELSE[default] END; 如果testN为真，就返回resultN; 否则返回default
• CASE[test] WHEN[val1] THEN[result1] … ELSE[default] END; 如果test = valN, 就返回resultN；否则返回default
• IF(test,t,f) 如果test为真，就返回t，否则返回f
• IFNULL(arg1,arg2) 如果arg1不为NULL，就返回arg1，否则返回arg2
• NULLIF(arg1,arg2) 如果arg1=arg2，就返回NULL，否则返回arg1

一个登陆验证的示例：

SELECT 
IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResult
FROM users WHERE uname = 'sue';
1
2
3

其他函数

• last_insert_id()：获取最后插入的记录的最后生成的序列号。
• mysql常用函数：https://blog.csdn.net/sugang_ximi/article/details/6664748

4. MySQL CTE（公共表表达式）

公用表表达式是一个命名的临时结果集，仅在单个SQL语句的执行范围内存在。与派生表类似，CTE不作为对象存储，仅在查询期间持续；与派生表不同，CTE可以是自引用（递归CTE），也可以在同一次查询中多次引用。此外，CTE提供了更好的可读性和性能。

CTE语法：

WITH cte_name (column_list) AS (
    query
)
SELECT * FROM cte_name;
1
2
3
4

注意：查询中的列数必须与column_list中的列数相同，如果省略column_list, CTE将使用定义CTE的查询的列列表。

示例：

WITH topsales2013 AS(
    SELECT  
        productCode,  
        ROUND(SUM(quantityOrdered * priceEach)) sales  
    FROM
        orderdetails
            INNER JOIN
        orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2013
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5
)
SELECT
    employeeNumber, firstName, lastName, sales
FROM
    employees
        JOIN
    topsales2013 USING (employeeNumber);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CTE的其他用法：
在子查询或是派生表子查询的开头使用WITH子句：
SELECT ... WHERE id IN (WITH ... SELECT ...);
SELECT * FROM (WITH ... SELECT ...) AS derived_table;

CTE 递归查询：
递归CTE语法：

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;
1
2
3
4
5
6

递归CTE由三个主要部分组成：
1. 形成CTE结构的基本结果集的初始查询(initial_query)，初始查询部分被称为锚成员。
2. 递归查询部分是引用CTE名称的查询，因此称为递归成员。递归成员由一个UNION ALL或UNION DISTINCT运算符与锚成员相连。
3. 终止条件是当递归成员没有返回任何行时，确保递归停止。

示例数据库：

mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL    |       |
| lastName       | varchar(50)  | NO   |     | NULL    |       |
| firstName      | varchar(50)  | NO   |     | NULL    |       |
| extension      | varchar(10)  | NO   |     | NULL    |       |
| email          | varchar(100) | NO   |     | NULL    |       |
| officeCode     | varchar(10)  | NO   | MUL | NULL    |       |
| reportsTo      | int(11)      | YES  | MUL | NULL    |       |
| jobTitle       | varchar(50)  | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
8 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14

使用CTE递归查询整个组织结构，定义最高级总经理的lvl=1：

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

毛

5. MySQL UNION

UNION用于合并另个或多个SELECT语句的结果集，并消除重复行
UNION ALL 不消除重复行

UNION要求SELECT语句必须拥有相同数量的列，列必须有相似的数据类型，同时，每条SELECT语句中的列的顺序必须相同。
语法：

SELECT column_name FROM table1
UNION (ALL)
SELECT column_name FROM table2
1
2
3

UNION结果集中的列名总是等于第一个SELECT语句的列名。
另外，如果使用order by则必须配合limit一起使用，否则会被优化分析时忽略。

6. MySQL REGEXP正则表达式匹配

SELECT 
    productname
FROM
    products
WHERE
    productname REGEXP '^.{10}$';
1
2
3
4
5
6

7. MySQL存储过程

存储过程的优点：
1. MySQL存储过程按需编译，在编译存储过程之后，MYSQL将其放入缓存中，它为每个连接维护自己的存储过程高速缓存，如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。（JIT思想？？）
2. 存储过程有助于减少应用程序和数据库服务器之间的流量，应用程序不必发送多个冗长的SQL语句。
3. 可重用、透明
4. 安全性：授予权限

存储过程的缺点：
1. 存储过程的保存需要占用内存，且逻辑过多时会耗费CPU计算量
2. 复杂业务逻辑开发困难
3. 难于调试，MYSQL不支持调试
4. 存储过程一般需要单独人员开发，难于维护

8. Java Statement

三类statement对象：
1.Statement：执行不带参数的简单SQL语句
2.PreparedStatement(extends Statement)：执行带或不带IN参数的预编译SQL语句

PreparedStatement可以使用’?’占位符，并在java中使用preStatement.setXXX(index,value)进行设置
设置sql语句： preparedStatement = connection.prepareStatement(sql);
调用executeQuery和executeUpdate执行（没有参数）
可以防止sql注入

3.CallableStatement(extends PreparedStatement)：执行对数据库中已存储的存储过程的调用，添加了处理OUT参数的方法

示例：
首先新建一个存储过程：

delimiter &&
create procedure pro_getCountById(in tid int, out counts double, out userNames varchar(20))
begin
  select salary*tyear into counts from t_employee where id = tid;
  select userName into userNames from t_employee where id = tid;
end
&&
1
2
3
4
5
6
7

Java中使用CallableStatement对该存储过程进行调用：

public static List getCountsById(CEmployee cemployee)throws Exception{
    List list = new ArrayList();
    Connection conn = dbUtil.getCon();
    String sql = "{call pro_getCountById(?, ?, ?)}";
    CallableStatement cstmt = conn.prepareCall(sql);
    cstmt.setInt(1, cemployee.getId());
    cstmt.registerOutParameter(2, Types.DOUBLE);
    cstmt.registerOutParameter(3, Types.VARCHAR);
    cstmt.execute();
    double counts = cstmt.getDouble("counts");
    String userNames = cstmt.getString("userNames");
    CEmployee emp = new CEmployee(counts, userNames);
    list.add(emp);
    dbUtil.close(cstmt, conn);
    return list;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

使用到的方法：
1. getInt(int parameterIndex)、getInt(String parameterName)、getString、getBigDecimal…… 获取返回值
2. registerOutParameter(int parameterIndex, int sqlType): 按顺序位置parameterIndex将OUT参数注册为JDBC类型sqlType

关于缓存：
Statement缓存：参数改变时缓存也会失效
PreparedStatement缓存：每个连接有一个缓存，实现参数化查询。

9. MYSQL事务

只有InnoDB支持事务,使用start transaction或begin开始事务，使用commit提交
COMMIT：当一个成功的事务完成后，发出commit命令应使所有参与表的修改生效
ROLLBACK：如果发生故障，发出rollback命令取消当前事务中的所有修改

（1）四种事务隔离级别

（2）事务锁定模式
系统默认： 不需要等待某事务结束，可直接查询到结果，但不能再进行修改、删除。
缺点：查询到的结果，可能是已经过期的。
优点：不需要等待某事务结束，可直接查询到结果。

（3）开启事务处理方法
MYSQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现

begin开始一个事务
rollback事务回滚
commit 事务确认

2.直接用set来改变mysql的自动提交模式

mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过
set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交

10. java.util.concurrent.ThreadPoolExecutor

corePoolSize:
线程池的基本大小，即在没有任务需要执行的时候线程池的大小，并且只有在工作队列满了的情况下才会创建超出这个数量的线程。刚刚创建ThreadPoolExecutor的时候，线程不会立即启动，而是要等到有任务提交时才会启动（除非调用prestartCoreThread/prestartAllCoreStart事先启动核心线程）。 再考虑到keepAliveTime和allowCoreThreadTimeOut超时参数的影响，所以没有任务需要执行的时候，线程池的大小不一定是corePoolSize。

maximumPoolSize:
线程池中允许的最大线程数，线程池中的当前线程数目不会超过该值。如果队列中任务已满，并且当前线程个数小于maximumPoolSize，那么会创建新的线程来执行任务。
largestPoolSize: 记录了线程池在整个生命周期中曾经出现的最大线程个数，线程池创建之后可以调用setMaximumPoolSize()改变运行的最大线程的数目。

poolSize:
线程池中当前线程的数量。

新提交一个任务时的处理流程：
1. 如果线程池的当前大小还没有到达corePoolSize，那么启动一个新的线程完成任务。
2. 如果线程池的大小到达了corePoolSize, 那么将新提交的任务提交到阻塞队列排队。
3. 如果阻塞队列容量已到达上限，并且当前大小poolSize

11. BIO NIO

BIO： 连接和线程对应
NIO： 事件驱动，解决BIO的并发性。一个连接不一定对应一个线程，每个新的连接被注册到多路复用器上，基于请求来新建线程。

其他

1. MYSQL5.6版本开始支持InnoDB引擎的全文索引，目前仅支持在CHAR、VARCHAR、TEXT类型上的全文检索
2. ACID：Atomicity Consistency Isolation Durability
3. Java特点：抽象、继承、多态、封装