Javaweb开发项目实例教程（idea版）——MySQL（下篇）

2021-03-02 10:03 作者:动力节点小王本王 0人读过 | 我要投稿

DQL【重点】

一、学习流程

1. 单表查询：单表查询七个查询命令执行特征以及临时表

2.多表查询：连接查询合并方案 / 联合查询合并方案

3.子查询：【简单】依赖子查询 / 独立子查询

4.自关联查询：

5.查询结果中解释字段数据来源

6.特殊查询案例: 行转列查询 / 列转行查询

MySql服务器管理规则

一、存储引擎:

1.介绍：mysql服务器提供表文件管理方案

2.查看mysql服务器存在存储引擎

show engines

3.修改mysql服务器默认存储引擎

my.ini设置默认存储引擎 default-storage-engine=myisam

4.查看表文件依赖存储引擎

show create table 表文件

5.修改表文件依赖存储引擎

alter table 表文件 engine=新存储引擎名

6. INNODB 与 MYISAM 区别

1）myisam：

***采用三个文件存储信息 xx.frm----字段信息

xx.myd---数据行

xx.myi----索引

***增加表文件操作速度，不支持事务管理

***如果表文件操作以查询为主，建议采用myisam

2) innodb：

***采用frm文件存储信息（字段，数据行，索引）

***增加表文件数据安全性。在九种存储引擎中唯一支持事务管理

***如果表文件操作以修改为主，建议采用INNODB

二、约束管理方案

1.介绍：确保字段中内容符合实际要求

2.分类：【背】

1）非空约束

2）唯一性约束

3）主键约束

4）外键约束

5) 自定义约束

3. 非空约束

1）作用：要求管理字段下不允许存储null值

2）例子：

create table student(

sid int ,

sname varchar(20) not null #非空约束

)

insert into student values(1,"mike"); #ok

insert into student values(null,'allen');#ok

insert into student values(3,null); #error

4.唯一性约束:

1) 作用：要求字段下内容不能出现重复值，但是允许出现多个null

2) 例子：

create table student(

sid int ,

sname varchar(20) not null #非空约束，

email varchar(20) unique #唯一性约束

)

insert into student values(1,"mike",'mike@163.com')#ok

insert into student values(2,"allen",'mike@163.com')#error

insert into student values(3,"smith",null)#ok

insert into student values(4,"tom",null)#ok

5.主键约束:

1）作用：管理主键字段。确保主键字段下内容不能出现重复值，也不能出现null

2）例子：

create table student(

sid int primary key， #主键约束

sname varchar(20)

)

insert into student values(1,'mike') #ok

insert into student values(1,'allen') #error

insert into student values(null,'smith')#error

6.外键约束:

1) 作用：管理外键字段。要求外键字段值必须来自于一方表主键字段已经存在的值同时允许外键字段存在多个null

2）外键约束绑定：

alter table 多方表 add constraint 约束对象名 foreign key(多方表外键字段)references 一方表（一方表主键字段名）

三、视图(VIEW)-----【背】

1. 什么是视图:

1)视图是MySql服务器提供一个内置管理对象

2)视图对象内部采用键值对(key-value)形式存储

3)视图中key存放一个查询语句。value存放查询语句对应字段管理权

2.例子：视图1对象

key -----------------------------------------value

select deptno from dept deptno 使用权

3.视图创建:

create view 视图对象名 as 查询语句

4.视图作用:[重点]

1)提高查询语句复用性 select * from view_1

2)分配开发人员对表文件字段使用权利

create view view_2 as select empno, ename,job,sal from emp

插入

insert into view_2(empno,ename,job,sal)

values(3999,'xxxx','xxx',4000); #OK

insert into view_2(empno,ename,job,sal,comm)

values(4999,'xxxx','xxx',4000,100); #error

删除

delete from view_2 where empno>=9999 #ok

delete from view_2 where deptno=10; #error

更新

update view_2 set ename='ssss',job='ccccc' where empno=3999

update view_2 set ename='ssss',job='ccccc' ,hireDate='2009-08-13' where empno=3999

四、索引（index）-----SQL优化【近两年面试必考科目】

1.什么是索引：

1.索引存在硬盘中一个数组

2.数组有序存放表文件中某个字段下所有数据以及数据所在行数

3.如果表文件采用INNODB引擎管理，索引存在frm文件。

如果表文件采用myisam引擎管理，索引存在myi文件

2.索引作用：

减少遍历临时表中数据行次数，增加查询速度

SELECT * FROM STUDENT WHERE AGE =20 #遍历了5次

如果where使用字段上设置了索引。此时mysql服务器使用BTREE算法

对应索引中定位满足条件的数据行行数并返回给where

[21]

[20---3,5] 【22---4】

[19---1行] 【25---2】

3. 索引操作:

1)查看表文件已近存在索引

show index from 表文件

2) 创建一个索引

create index 索引名 on 表文件(字段)

3) 删除索引

drop index 索引名 on 表文件

4. 执行计划

1) 命令格式: explain 查询语句

2) 执行计划type(查询速度级别)：all < index <range <ref <const

all: 最慢查询速度。表示没有通过索引帮助定位数据行。

采用全表扫描方式定位数据行，进行SQL优化时避免

all级别

explain select * from emp where ename='smith'

index: 进行SQL优化时避免index级别。采用全表扫描方式

定位数据行，但是在SELECT抓取数据时从索引中得到结果，对于查询速度有所帮助。但是这个帮助忽略不计

explain select * from emp #all

create index ename_index on emp(ename)

explain select ename from emp

range: 进行SQL优化时达到最低标准.采用索引提供数据行位置同时一次返回多个数据行

explain select * from emp where sal<=1000 #all

create index sal_index on emp(sal)

explain select * from emp where sal<=1000 #range

这个级别并不稳定，如果通过索引返回行数达到总行数1/3

mysql服务器考虑性能与消耗关系，放弃使用索引

ref: 进行SQL优化时达到最高标准，采用索引提供数据行位置并且每次只能返回一行

explain select * from emp where ename='smith' # all

create index ename_index on emp(ename)

explain select * from emp where ename='smith' # ref

const: 采用聚簇索引进行定位，是最快查询级别

5.索引失效:

1) 如果通过索引定位行数达到了总行数1/3时，必然导致索引失效

2) 如果在索引字段进行数学运算，必然导致索引失效

explain select * from emp where sal =800 #ref

explain select * from emp where sal+100=900 #all

3)如果在索引字段进行函数处理，必然导致索引失效

explain select * from emp where ename='smith' #ref

explain select * from emp where upper(ename)='SMITH' #ALL

4) 前置模糊查询可以达到range

explain select * from emp where ename like 's%' #range

后置模糊查询和包含模糊查询一定会导致索引失效

explain select * from emp where ename like '%s' #all abc abd

explain select * from emp where ename like '%s%' #all

5) 如果采用类型自动转换，必然导致索引失效

explain select * from emp where ename='100' #ref

explain select * from emp where ename=100 #all

五、事务（transaction）

1.什么是事务：

对于采用innodb管理的表文件，在进行数据修改时（insert,update,delete）mysql服务器自动对当前表文件中数据进行一次备份然后再进行操作。这个备份就是事务

2.事务作用：

在操作之后，如果需要反悔。可以通过事务覆盖表文件实现操作撤销

3.事务管理方式

1）mysql服务器自动管理事务，如果当前DML命令无法正常执行。

此时mysql服务器使用事务撤销本次操作。如果当前DML命令正常执行，mysql服务器销毁掉事务

2）手动事务管理方式，要求mysql服务器将多个SQL命令产生备份交给同一个事务对象管理，操作完毕后由开发人员根据实际情况决定是否测校

start transaction;

delete from emp; #emp_copy

delete from dept; #dept_copy

rollback; #回滚

commit; #提交--事务管理对象将本次所有备份销毁

六、事务使用原则(ACID)

1. A（原子性）：只有来自于同一个业务中SQL命令才应该交给同一个事务管理对象管理

例子：撤销部门20同时将部门20下职员开除

start transaction；

delete from emp where deptno=20;

delete from dept where deptno=20;

commit/rollback

volaite

错误使用

start transaction；

1 delete from emp where deptno=20;

2 delete from dept where deptno=20;

3 insert into book values(1,"java编程思想")；

commit/rollback

2.C.(一致性) 一个业务中只要有一个分支任务执行失败或则返回。

此时就应该将事务中所有sql操作都视为无效

3.D(持久性) 在commit执行之后，无法通过rollback撤销操作

4.I(隔离性): 两个事务对于同一个表操作应该彼此相互影响

七、char 与 varchar区别 (面试常考题型--（HR邀约面试）)

1.char（m），固定不可变字符串

1）固定，char类型可以接收字符个数是固定，赋值时不能超过这个个数

create table test1(

tname char(3) # 可以存放3个英文字母，或则3个中文汉字

)

insert into test1 values('abc');

insert into test1 values('吃了吗')

insert into test1 values('abcd'); #error

2)不可变，在磁盘用于存储字符空间是固定。

tname char(3) 'abc' [a] [b] [c]

'ab' [a] [b] [空格]

2. varchar(m): 固定可变字符串

1）固定，varchar类型可以接收字符个数是固定，赋值时不能超过

create table test1(

   tname varchar(3) # 可以存放3个英文字母，或则3个中文汉字

   )

   insert into test1 values('abc');

insert into test1 values('吃了吗')

insert into test1 values('abcd'); #error

2) 可变：根据实际接收字符个数减少磁盘空间