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;


标题名称:MySQL二次整理(4)v1.0
分享链接:http://scyanting.com/article/jgepec.html