mysql第三天（存储引擎）

一、mysql体系结构介绍

mysql可以分为Server层和存储引擎层。

• server层包括连接器、查询缓存、分析器、优化器、执行器，涵盖MySQL大多数核心服务功能，比如存储过程，触发器，视图等
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM，MEmory等多个存储引擎
• 现在最常用的存储引擎是InnoDB,它从MySQL5.5.5版本开始取代了MyISAM成为了默认的存储引擎

二、mysql存储引擎概述

• 定义：MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
• MySQL有一个很大的优势在于插件式的存储引擎，用户可以根据应用的需要选择如何存储贺索引数据、是否使用事务等
• MySQL的存储引擎包括
  • InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、ARCHIVE、MERGE(MGR_MyISAM)、FEDERATED、NDB等
  • 其中InnoDB和BDB提供事物安全表，其他存储引擎都是非事物安全表
  • MySQL 5.5之前的默认存储引擎是MyISAM，5.5之后就改为InnoDB
• 查看当前的存储引擎：

#方式一
show variables like 'default_storage_engine';
#方式二 查看所支持的引擎
show engines\G;

• 操作存储引擎

#创建表时指定存储引擎为myisam
create table testengine(
    col1 int primary key auto_increment,
    col2 varchar(4)
)engine=myisam;

#修改该表的存储引擎
alter table testengine engin = innodb;

#查看建表语句
show create table testengine\G;

三、MySQL支持的数据类型

1. 什么是数据类型
   1. MySQL属于关系型数据库，他很大的特点就是以二维表方式存储数据，所以 占据很大空间。
   2. 数据类型又称列类型，具体值数据表中某个字段的类型。
2. 数据类型
   1. 数值类型
      • INT整数类型
        • 属性
          • ZEROFILL:定义整数类型时如果需要指定长度需要搭配上关键字“ZEROFILL”，指定 了“ZEROFILL”关键 字后，少了就会补零，位数多了也不会报错。
        • Unsigned:无符号数，该字段无负数只有正数
          • Auto_increment:该字段为自增长属性
   2. 小数类型
      • 定点数类型decimal
      • 浮点数类型 单精度float和双精度double
      • 小数点浮动，精度有限，浮点型有时会造成精度缺失
      • 是一种精度型数据，超出指定分为会丢失精度，进行四舍五入
   3. 日期时间类型
      • DATE
      • DATETIME
      • TIMESTAMP
      • TIME
      • YEAR
   4. 字符串类型
      • char定长字符串
      • varchar不定长字符串
      • binary类型与varbinary类型
      • text类型与blob类型
   5. JSON类型
   6. 其他类型

四、练习

1. 现任意数据库中存在一表testtable：

   1. 请创建该表，需要包括id、name、score等字段：

CREATE TABLE testtable(
  id varchar(255) DEFAULT NULL,
  year year(4) DEFAULT NULL,
  stu_name varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.2、为其设计数据，数据内容如下，类型自定义：

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

|    id    |  year | stu_name |

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

| itcast01 |  2000 |          |

| itcast01 |  2001 |          |

| itcast02 |  2000 |          |

| itcast03 |  2002 |          |

| itcast04 |  2000 |          |

| itcast02 |  2002 |          |

| itcast04 |  2001 |          |

| itcast03 |  2000 |          |

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

备注：stu_name自拟,其中一个为自己姓名，意思大致为id为itcast01的课程2000年有xxx同学选修

insert into testtable values( 'itcast01','2000','张三'),

( 'itcast01','2001','张三'),

( 'itcast02','2000','李四'),

( 'itcast03','2002','李四'),

( 'itcast04','2000','王五'),

( 'itcast02','2002','王五'),

( 'itcast04','2001','魏睿'),

( 'itcast03','2000','魏睿');

1.3请写出：分别用通配符和正则表达式匹配以自己姓开始的“stu_name”的语句:

#通配符：
select * from testtable where stu_name like '魏_%';
#正则表达式：
select * from testtable where stu_name regexp '[魏][:print:]{1,}';

1.4请写出：检索内容格式为“id-year(stu_name)的语句”

select concat(id,'-',year,'(',stu_name,')') '课程-年份-学生' from testtable;

1.5请写出：查看选择itcast02的人数的语句：

select id,count(*) num from testtable where id = 'itcast02';

1.6请写出：查看选择2000年itcast02的人数的语句：

select id,year,count(*) num from testtable where id = 'itcast02' and  year = '2000';

1.7请写出：查看各课程各有多少人上课的语句：

select id,count(*) num from testtable group by id;

1.8、请写出：查看各课程每个月有多少人上课的语句：

select id,year,count(*) num from testtable group by id,year;

2、用自己的话简单说说什么是子查询？

子查询就是查询嵌套的，即查询语句的where语句中包含了另一个查询语句;

如：select * from test16 where  score > (select score from test16 where name ='赵六');

子查询分为相关子查询很不相关子查询；

相关子查询与不相关子查询区别为：处理次数不同、依赖不同、效率不同。 

1. 相关子查询：相关子查询被多次处理，需要重复求值以供外部查询使用。
2. 不相关子查询：不相关子查询的处理一次完成，执行后传递给外部查询。

1. 相关子查询：相关子查询中的查询条件取决于外部查询中的值。
2. 不相关子查询：无关子查询是独立于外部查询的子查询，不依赖于外部查询中的值。 1、相关子查询：相关子查询可以嵌套在多个层中，但嵌套层越多，效率越低。

3、不相关子查询：不相关子查询不能嵌套，效率高于相关子查询。

4.用自己的话简单说说什么是笛卡尔积？有什么危害？

笛卡尔积，又叫cross join，是SQL中两表连接的一种方式。假如A表中的数据为m 行，B表中的数据有n行，那么A和B做笛卡尔积，结果为m*n行。 通常我们都要在 实际SQL中避免直接使用笛卡尔积，因为它会使“数据爆炸”，尤其是数据量很大的时 候。