MySQL笔记

1. 派生表

派生表是从SELECT语句返回的虚拟表，类似于临时表，但是派生表不需要创建临时表的步骤。（当SELECT语句中的FROM使用独立子查询时，我们将它成为派生表）
例如：
SELECT column_list  
FROM (  
    SELECT column_list  
    FROM table_1  
) derived_table_name  
WHERE derived_table_name.c1>0;  
与子查询不同，派生表必须有别名，以便稍后再查询中引用该派生表。 
派生表示例： 
 查询2013年销售收入最高的前5名产品 
  
SELECT  
    productCode,  
    ROUND(SUM(quantityOrdered * priceEach)) sales  
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2013
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;
连接两表：INNER JOIN
限制条件为2013年：YEAR(shippedDate) = 2013
计算产品销量：GROUP BY productCode 并使用SUM()函数
取前5名：ORDER BY sales 并 LIMIT 5 
使用派生表将以上表与另一个products表关联： 
SELECT 
    productName, sales
    (SELECT  
        productCode,  
        ROUND(SUM(quantityOrdered * priceEach)) sales  
        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);
2. MYSQL JOIN 
inner join：内连接，取得两个表中存在连接匹配关系的记录
left join：左连接，以左表为基准，取左表的所有记录，右表匹配向左表
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';
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;
注意：查询中的列数必须与column_list中的列数相同，如果省略column_list, CTE将使用定义CTE的查询的列列表。 
WITH topsales2013 AS(
    SELECT  
        productCode,  
        ROUND(SUM(quantityOrdered * priceEach)) sales  
        orderdetails
            INNER JOIN
        orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2013
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5
SELECT
    employeeNumber, firstName, lastName, sales
    employees
    topsales2013 USING (employeeNumber);
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;
 递归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
使用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,
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;
5. MySQL UNION 
UNION用于合并另个或多个SELECT语句的结果集，并消除重复行 
 UNION ALL 不消除重复行 
UNION要求SELECT语句必须拥有相同数量的列，列必须有相似的数据类型，同时，每条SELECT语句中的列的顺序必须相同。 
 语法： 
SELECT column_name FROM table1
UNION (ALL)
SELECT column_name FROM table2
 UNION结果集中的列名总是等于第一个SELECT语句的列名。 
 另外，如果使用order by则必须配合limit一起使用，否则会被优化分析时忽略。 
6. MySQL REGEXP正则表达式匹配 
SELECT 
    productname
    products
WHERE
    productname REGEXP '^.{10}$';
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;
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. 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）四种事务隔离级别  
隔离级别 读数据一致性 脏读 不可重复读 幻读
未提交读（Read uncommitted） 最低级别，只能保证不读取物理上损坏的数据 是 是 是
已提交读（Read committed） 把其他事务的修改提交考虑在内，同一事务同一SELECT可能会返回不同结果 否 是 是
可重复读（Repeatable read）默认 不把其他事务的修改考虑在内,无论其他事务是否用commit命令提交过，同一事务同一SELECT返回相同结果（本事务不修改） 否 否 是
可序列化（Serializable） 最高级别，事务级 否 否 否 
（2）事务锁定模式 
 系统默认： 不需要等待某事务结束，可直接查询到结果，但不能再进行修改、删除。 
 缺点：查询到的结果，可能是已经过期的。 
 优点：不需要等待某事务结束，可直接查询到结果。  
级别 说明 命令
快照读/普通读 不上锁，不受读锁和写锁的影响 SELECT … FROM …
共享锁/读锁/当前读 SELECT必须等待其他事务COMMIT之后才能执行，多个事务共享一把锁，只能读不能修改 SELECT …… LOCK IN SHARE MODE
排他锁/写锁 事务在一行数据上了排他锁以后，该事务可以对数据进行读写操作，该行数据不能再上其他锁(select … lock in share mode 和 select … for update都不可以再访问该行数据) 但是，普通的select语句可以访问。  SELECT …… FOR UPDATE / INSERT / UPDATE / DELETE 
防插入锁 例如 SELECT * FROM tablename WHERE id>200, 那么id>200的记录无法被插入 SELECT
死锁 自动识别死锁，先进来的进程被执行，后来的进程收到出错消息，并按ROLLBACK方式回滚。innodb_lock_wait_timeout = n 可以设置最长等待时间，默认50s。  
（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的并发性。一个连接不一定对应一个线程，每个新的连接被注册到多路复用器上，基于请求来新建线程。 
MYSQL5.6版本开始支持InnoDB引擎的全文索引，目前仅支持在CHAR、VARCHAR、TEXT类型上的全文检索 
ACID：Atomicity Consistency Isolation Durability 
Java特点：抽象、继承、多态、封装
                    MySQL笔记1. 派生表派生表是从SELECT语句返回的虚拟表，类似于临时表，但是派生表不需要创建临时表的步骤。 （当SELECT语句中的FROM使用独立子查询时，我们将它成为派生表） 例如：  SELECT column_list  FROM (      SELECT column_list      FROM table_1  ) derived_table_na...
				本文实例讲述了mysql派生表（Derived Table）简单用法。分享给大家供大家参考，具体如下：
关于这个派生表啊，我们首先得知道，派生表是从select语句返回的虚拟表。派生表类似于临时表，但是在SELECT语句中使用派生表比临时表简单得多，因为它不需要创建临时表的步骤。所以当SELECT语句的FROM子句中使用独立子查询时，我们将其称为派生表。废话不多说，我们来具体的解释：
SELECT
  column_list
*  (SELECT
*    column_list
*  FROM
*    table_1) derived_table_name;
WHERE der
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
在命令提示符中使用 INNER JOIN
我们在RUNOOB数据库中有两...
				Union
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
//联合两个表，没有重复
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
默认地，UNION 操作符选取不同的值。如果允...
				官网原文链接：添加链接描述
8.2.2.4 使用合并表或者实体化表来优化派生表（derived tables）和视图 (view)
优化器（optimize）能够使用两种策略来处理派生表（derived table）(同样适用于视图（view）)
合并派生表到外部查询块（即去掉子查询）
实体化派生表为一个内部临时表（即生成临时内存表）
SELECT * FROM (SELECT * FROM t1) AS derived_t1;
合并派生表derived_t1, 查询语句执行如下：
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`student_
隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读（Read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读（Read committed）	把其他事务的修改提交考虑在内，同一事务同一SELECT可能会返回不同结果	否	是	是
可重复读（Repeatable read）默认	不把其他事务的修改考虑在内,无论其他事务是否用commit命令提交过，同一事务同一SELECT返回相同结果（本事务不修改）	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否
级别	说明	命令
快照读/普通读	不上锁，不受读锁和写锁的影响	SELECT … FROM …
共享锁/读锁/当前读	SELECT必须等待其他事务COMMIT之后才能执行，多个事务共享一把锁，只能读不能修改	SELECT …… LOCK IN SHARE MODE
排他锁/写锁	事务在一行数据上了排他锁以后，该事务可以对数据进行读写操作，该行数据不能再上其他锁(select … lock in share mode 和 select … for update都不可以再访问该行数据) 但是，普通的select语句可以访问。	SELECT …… FOR UPDATE / INSERT / UPDATE / DELETE
防插入锁	例如 SELECT * FROM tablename WHERE id>200, 那么id>200的记录无法被插入	SELECT
死锁	自动识别死锁，先进来的进程被执行，后来的进程收到出错消息，并按ROLLBACK方式回滚。innodb_lock_wait_timeout = n 可以设置最长等待时间，默认50s。