MySQL二次整理(4)v1.0

联合查询：将多个查询语句的执行结果相合并；

UNION

SELECT clause UNION SELECT cluase；

练习：导入hellodb.sql生成数据库

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；

(2) 以ClassID为分组依据，显示每组的平均年龄；

(3) 显示第2题中平均年龄大于30的分组及平均年龄；

(4) 显示以L开头的名字的同学的信息；

(5) 显示TeacherID非空的同学的相关信息；

(6) 以年龄排序后，显示年龄最大的前10位同学的信息；

(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

练习：导入hellodb.sql，以下操作在students表上执行

1、以ClassID分组，显示每班的同学的人数；

2、以Gender分组，显示其年龄之和；

3、以ClassID分组，显示其平均年龄大于25的班级；

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

练习：导入hellodb.sql，完成以下题目：

1、显示前5位同学的姓名、课程及成绩；

2、显示其成绩高于80的同学的名称及课程；

3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

4、显示每门课程课程名称及学习了这门课的同学的个数；

思考：

1、如何显示其年龄大于平均年龄的同学的名字？

2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？

3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？

4、统计各班级中年龄大于全校同学平均年龄的同学。

*********************************MySQL存储引擎***************

存储表类型: 表级别的概念 不建议在同一个库中的表上使用不同的ENGINE(引擎);

创建表时指定引擎:

CREATE TABLE ... ENGINE[=]STORAGE_ENGINE_NAME ...

查看表信息: SHOW TABLE STATUS

常见的存储引擎：

MyISAM:  意外崩溃后 无法保证数据安全  不支持事物

Aria:  意外崩溃后,可以保证数据安全

InnoDB 支持事物

MRG_MYISAM 用于实现将两个MyISAM表在逻辑层上连接在一起.

CSV 基于文本文件存储文件 跨数据库交换比较好 但损失数据精度

BLACKHOLE 黑洞存储引擎 在级联复制时比较有用 (后面讲MySQL主从模型时的半同步模型时用到)

MEMORY 基于内存的存储引擎 不适用于持久存储 但性能高 支持hash索引 通常临时表用

PERFORMANCE_SCHEMA 兼容表的mysql接口来显示统计数据的 并非真正的表 虚表(类似/proc伪文件系统)

ARCHIVE 归档存储引擎 做数据仓库用

FEDERATED 夸服务器 夸物理主机 表联合

***************************************************************

InnoDB: InnoBase公司研发 后赠送给MySQL公司  后被甲骨文买了

并非原版InnoDB 而是第三方二次开发板

Percona-XtraDB, Supports transactions, row-level locking, and foreign keys

支持事物,行级锁,外键

数据存储于“表空间(table space)"中：自组织文件系统

(1) 所有InnoDB表的数据和索引存储于同一个表空间中；

表空间文件：datadir定义的目录中

文件：ibdata1, ibdata2, ...

(2) innodb_file_per_table=ON，意味着每表使用单独的表空间文件；

数据文件（数据和索引，存储于数据库目录）: tbl_name.ibd

表结构的定义：在数据库目录，tbl_name.frm

事务型存储引擎，适合对事务要求较高的场景中；但较适用于处理大量短期事务；

基于MVCC（Mutli Version Concurrency Control）支持高并发；支持四个隔离级别，默认级别为REPEATABLE-READ；间隙锁以防止幻读；

使用聚集索引（主键索引）；

支持”自适应Hash索引“；

锁粒度：行级锁；

总结：

数据存储：表空间；

并发：MVCC，间隙锁，行级锁；

索引：聚集索引、辅助索引；

性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；

备份：支持热备；

***************************************************************

MyISAM：

支持全文索引（FULLTEXT index）、压缩、空间函数（GIS）；

不支持事务

锁粒度：表级锁

崩溃无法保证表安全恢复

适用场景：只读或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）；

文件：每个表有三个文件，存储于数据库目录中

tbl_name.frm：表格式定义；

tbl_name.MYD：数据文件；

tbl_name.MYI：索引文件；

特性：

加锁和并发：表级锁；

修复：手动或自动修复、但可能会丢失数据；

索引：非聚集索引；

延迟索引更新；

表压缩；

行格式：

 {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

***************************************************************

其它的存储引擎：

CSV：将CSV文件（以逗号分隔字段的文本文件）作为MySQL表文件；

MRG_MYISAM：将多个MyISAM表合并成的虚拟表；

BLACKHOLE：类似于/dev/null，不真正存储数据；

MEMORY：内存存储引擎，支持hash索引，表级锁，常用于临时表；

FEDERATED: 用于访问其它远程MySQL服务器上表的存储引擎接口；

MariaDB额外支持很多种存储引擎：

OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE、...

搜索引擎：

lucene, sphinx

lucene：Solr, Elasticsearch

***************************************************************

并发控制：

锁：Lock

为什么要用锁:

当一个表中有多个并发访问时,为防止别人读到的数据是正在修改的数据,所以存在锁机制.

锁类型 ：

读锁：共享锁，可被多个读操作共享；会导致写饥饿(都操作不间断 写操作就必须一直等待读操作完成)

写锁：排它锁，独占锁；

锁粒度：

表锁：在表级别施加锁，并发性较低；

行锁：在行级另施加锁，并发性较高；(被锁行前后的行也会被锁定 防止插入)

锁策略：在锁粒度及数据安全性之间寻求一种平衡机制；

存储引擎：级别以及何时施加或释放锁由存储引擎自行决定；

MySQL Server：表级别，可自行决定，也允许显式请求；

锁类别：

显式锁：用户手动请求的锁；

隐式锁：存储引擎自行根据需要施加的锁；

显式锁的使用：

(1) LOCK TABLES

LOCK TABLES  tbl_name [AS alisa]  read|write, tbl_name read|write, ...

UNLOCK TABLES

(2) FLUSH TABLES 把内存中的表写到磁盘上 再重新打开

FLUSH TABLES tbl_name,... [WITH READ LOCK];

UNLOCK TABLES;

(3) SELECT

[FOR UPDATE | LOCK IN SHARE MODE共享模式锁定]

例:SELECT * FROM students WHERE StuID IN (1,2,3) FOR UPDATE;完成后既立即释放

  事务：

事务：一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元；

事务日志：在磁盘上开辟一块连续空间 连续写在事物日志中,由于连续写操作没有寻到时间性能提升比较明显,最终再更新至磁盘中,

如果日志空间较大,则发生意外后重启要把日志空间中的语句逐一写入磁盘数据,速度较慢

未防止日志写满,所以事物文件分组.

innodb_log_files_in_group 事物日志组数量

innodb_log_group_home_dir  事物日志目录

innodb_log_file_size 单个日志大小

Innodb_mirrored_log_groups    多写  多次写日志组(确保安全  当然不能放在一块硬盘上)

ACID测试：是否满足事物

A：AUTOMICITY，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；

C：CONSISTENCY，一至性；数据库总是应该从一个一致性状态转为另一个一致性状态；

I：ISOLATION，隔离性；一个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之目的，隔离有多种级别；

D：DURABILITY，持久性；事务一旦提交，其所做出的修改会永久保存；

自动提交：单语句事务

mysql> SELECT @@autocommit;

+------------------------+

| @@autocommit |

+------------------------+

|       1       |

+------------------------+

mysql> SET @@session.autocommit=0；

手动控制事务：

启动：START TRANSACTION

提交：COMMIT

回滚：ROLLBACK

事务支持savepoints：一个事物的多条语句中插入时间点  可以回归到事物中的某一个时间点

SAVEPOINT identifier   插入时间点

ROLLBACK [WORK] TO [SAVEPOINT] identifier 回归到指定插入点

RELEASE SAVEPOINT identifier 删除指定时间点

***********************************

事务隔离级别：

READ-UNCOMMITTED：读未提交 --> 脏读；可以读取别人尚未提交的数据 (允许读取别人尚未提交的数据)

READ-COMMITTED：读提交--> 不可重复读；事物修改的数据未提交之前.其他事物是看不见的.

REPEATABLE-READ：可重复读 --> 幻读；数据已经更改数据,但实际读到的是旧数据

SERIALIZABLE：串行化；

mysql> SELECT @@session.tx_isolation; 查看当前隔离级别

+----------------------------------+

| @@session.tx_isolation |

+----------------------------------+

| REPEATABLE-READ         |

+----------------------------------+

会出现死锁.

查看InnoDB存储引擎的状态信息：

SHOW ENGINE innodb STATUS;