MySql笔记

服务器简介

1.服务器是一种软件，不是硬件，不是计算机。
2.不同服务器负责调用不同的文件类型

表文件，数据库，数据库服务器

1.表文件:

1. 表文件是以".frm"结尾一种文件
2. 存在于服务端计算机硬盘上
3. 以数据行形式进行数据存储的文件

2.表文件结构：
student.frm

标题行 sid sname sex age home

数据行 10 mike 男 23 河南
20 allen 女 22 河北

3.数据库： 开发人员将用于存放表文件的文件夹称为【数据库】

4.数据库服务器 ：一种专门对表文件进行调用和管理的软件

sql含义简介

1.全称，Struct Query Language, 结构化查询编程语言

2.作用，用户通过SQL命令向数据库服务器发送请求，用于对表文件进行调用管理
3.特点，SQL语法结构与中国人日常用语结构非常相似，包含主谓宾

数据库服务器分类:

 1.关系型数据库服务器
管理的表文件彼此之间往往具有隶属关系特征，可以完整描述一段数据，但是在查询时由于涉及数据较多，因此查询速度并不理想


2.非关系数据库服务器【HashMap(key-value)】
管理表文件都是独立，无法描述一段完整的数据但是由于每次查询的大数据较少，因此查询速度非常快

登录MySql服务器命令

格式：mysql -u用户名 -p密码

备注：mysql -uroot -p585690lzy

数据库管理

• 1.MySql服务器管理数据库位：C:\ProgramData\MySQL\MySQL Server 5.5\data

• 查看所有的数据库名：show databases;

• 创建一个数据库：create database 数据库名；

• 删除一个数据库：drop database 数据库名；

表文件管理

• 查看指定的数据库下所有的表文件名

1.use 数据库名；

2.show tables；

• 创建表文件

create table 表名（

​ 字段名 数据类型，

​ 字段名 数据类型#注意：最后一个字段不要用“，”

）

• 删除一个表文件

drop table 表文件名；

• 查看创建表文件字段信息

show create table 表名；

• 为表文件添加字段

alter table 表文件名 add 新字段名 数据类型；

• 删除表文件字段

alter table 表文件名 drop 字段名

表文件数据管理

• 插入命令

1.格式：insert into 表文件名（字段名1，字段名2，字段名3） values（值1，值2，值3）；

2.省略字段名

格式：insert into 表文件名 values(值1，值2.。。。。)

注意：插入数据时每一个字段都能赋值，此时插入命令不需要指定字段名，插入值顺序此时需要与表文件字段顺序保持一致

3.一行想表文件插入多行数据

insert into 文件名（字段名1，字段名2，字段名3）

values

(值1，值2，值3),

(值11，值22，值33);

• 查询命令

select * from 表名；#查询全部信息

select * from 表名 where 条件；#根据条件进行查询

• 删除命令

delete from 表文件名；#删除表文件所有的数据行

delete from 表名 where 条件；#根据条件进行删除

• 更新命令

update 表名 set 字段名1=值1，字段名2=值2；#将所有数据行指定字段的值重新赋值

update 表名 set 字段名1=值1，字段名2=值2 where 判断条件；#将满足条件的数据行字段信息更新

• 数据行备份命令：将表a中所有数据信息赋值到表b（表a和表b必须要求字段结构完全一致）

  insert into b select * from a

• 表文件备份命令：将表文件a进行一份备份，备份生成的表文件b

create table b select * from a;

临时表讲解

• 临时表定义： 由查询命令在执行时，在内存中生成的表文件

• 临时表作用： 每一个查询命令在执行时，实际上操作都是上一个查询命令生成的临时表

• 临时表生命周期：

    在当前查询命令执行完毕后，MySql服务器自动将上一个查询命令生成的临时表从内存中销毁。导致用户最终看到的临时表只能查询语句中，最后一个查询生成的临时表
  

  举例：需求执行查询所有职员姓名，职位，工资

select ename,job,sal from emp

第一步：from emp

关于from命令，是所有查询命令之中第一个执行的查询命令。

执行作用：from命令相当于一个读取流：负责将硬盘上表文件加载到内存中生成一个临时表，供后续查询命令使用。

注意：由于查询命令对临时表进行操作，因此不会修改文件原有的数据

第二步：select ename ，job，sal

1.select 在from之后执行的，因此操作的为from生成的临时表

2.select像切蛋糕一样，将指定字段下所有的数据读取出来，在内存中将读取数据组成一个全新的临时表

where后面可加条件总结

• in

where 字段名 in (值1，值2)
#查询所有【项目经理】，【办事员】，【销售】
#select * from emp where job=‘salesman’ or job = ‘clerk’ or job=‘manager’
改进：select * from emp where job in(‘salesman’,‘clerk’,‘manager’)

• not in

where 字段名 not in(值1，值2)
#查询职位不是【项目经理】，【办事员】，【销售】

select ename,job from emp where job !=‘manager’ and job!=‘salesman’ and job!=‘clerk’

改进： select * from emp where job not in(‘salesman’,‘manager’,‘clerk’)

• null值，不表示空含义，表示一个不确定的值，因为这个值既然是不确定因此无法进行任何运算

is null

例如：查询所有补助为null的职员信息
select ename ,comm from emp where comm = null 无法得到真实数据
改进：select ename,comm from emp where comm is null

is not null

例如：查询所有补助不为null的职员信息
select ename,comm from emp where comm != null 无法得到真实数据
改进： select ename,comm from emp where comm is not null

• like模糊查询

“%”：表示一个长度任意字符串

“_”：表示一个任意字符

举例：

1.查询所有名称以S开头的职员信息【前置条件模糊查询】 select * from emp where ename like ‘s%’ # %是一个通配符，表示一个长度任意字符串

2.查询名字中第二个字母是A的人
select * from emp where ename like ‘_A%’

聚合函数

1.定义：用于对临时表指定字段下【所有内容】进行统计的函数

2.分类：

max（字段）：最大值

min（字段）：最小值

sum（字段）：求和

avg（字段）：平均数

count（字段）：求总数

3.强调：

• 当count后面加具体字段时，返回指定字段下内容不等于null的数据个数
• 当count后面加*时，表示统计临时表下所有数据行个数，不考虑数据行是否存在null

Group by分组查询命令

1.执行顺序：
FROM–>WHERE–>GROUP BY–>SELECT
2.书写顺序:
SELECT 字段名,函数，子查询
FROM 表名
WHERE 定位数据行条件
GROUP BY 分组字段
***如果同时出现WHERE与GROUP BY;GROUP BY应该在where之后
3.执行原理：
GROUP BY 执行时
首先根据分组字段数据种类，将临时表数据行进行分类。
然后将具有相同特征的数据行读取出来保存到一个全新临时表

4.七个查询命令中，只有GROUP BY有机会在执行完毕后，一次生成多个临时表

多字段分组

规则：

1.多字段分组时，分组字段出现顺对最终查询结果没有任何影响，即：group by deptno，job等于group by job，deptno

2.多字段分组时，group by一次只能根据一个分组字段进行分组

group by deptno，job需要执行两次分组

3.多字段分组时，从第二个分组字段开始，操作的是时尚一个分组字段生成的临时表

group by job时，操作临时表由group by deptno生成。

having

having用在group by之后表示过滤（补充：where是进行初次过滤，having是分组之后过滤，效率更高的肯定是用where过滤）

注意：having在执行完毕之后不会生成全新临时表

select执行原理

1.select执行特征与提供临时表的命令相关

2.如果临时表由【FROM】或则【WHERE】提供select面对只有一个临时表，此时select将指定字段下所有内容读取出来生成一个全新的临时表

3.如果临时表由【group by】提供，此时select可能面对多个临时表

• select将依次操作每一个临时表
• select在操作某个临时表时，注意：只会读取指定字段下第一个数据（这个就说明了当用来group by之后，select后面只能跟聚合函数或者用在group by后面的字段名，其他不能行）
• select将从多个临时表读取数据合成一列保存到一个全新的临时表

order by与limit

1.order by表示排序，desc表示降序，asc表示升序

limit表示从中截取，格式为：limit 起始位置，长度；

举例：按照薪水降序排序（使用：order by sal desc），选出最高薪水的人的信息（limit 0，1）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VpEw6KcF-1635518870043)(C:\Users\小样\AppData\Roaming\Typora\typora-user-images\image-20211025214636982.png)]

表文件字段分类

1.主键字段：用于存放主键编号的字段就是主键字段。每一个表都应该存在一个主键字段。相当于身份证编号，用于对表文件中数据行身份进行确认。

要求：主键字段即不能出现null，也不能出现重复值。

2.非主键字段：用于描述主键编号

3.外键字段：外键字段只存在于【多方表】，描述多方数据与一方数据之间依赖关系，外键字段的值应该来自于一方表中主键值。

补充：

• mysql服务器被称为关系型数据库服务器，是因为通过表文件数据关系来描述现实生活中【隶属关系】

• 隶属关系中角色划分：

  1. 一方: 拥有资源的一方

  2. 多方：依附于一方的资源

     例子： 一个部门拥有多个职员。
     【部门】 就是一方
     【职员】 就是多方

多表联合查询

• 本质：将多张表数据行合并到同一个临时表，然后使用【六个查询命令】根据需求对临时表数据行进行定位分组排序等操作最终为用户提供需要的数据的查询过程

• 多张表中数据行合并方案：

1.连接查询合并方案：

1）首先确保两张表之间存在了【隶属关系】，才可以使用连接查询合并方案
2）将两张表中数据行【沿着水平方向进行拼接】,最终得到一个拥有【完整隶属关系】新数据行

2.联合查询合并方案：

1）不需要两张表之间存在了【隶属关系】
2）将两张表中数据行【沿着垂直方向进行堆砌】

join

join就是连接合并方案，on是两表进行连接的条件

注意：只用join进行两表联合时，会出现笛卡尔积现象，得到的结果数为：第一张表的数据✖第二张表的数据，这样会出现很多“脏数据”

因此要用on进行过滤，on被称为连接查询过滤方案

内连接过滤方案

举例一：查询【部门20的名称】及其部门下【职位名称与人数】

代码实现：

select d.dname,e.job,count(*) #4.最后一步执行，根据group by的结果统计每个工作的人数
from emp e join dept d on e.deptno=d.deptno#1.第一步执行，两表根据on的条件生成一张表
where d.deptno=20#2.第二步执行，根据题意筛选部门编号等于20的
group by e.job#3.第三步执行group by e.job根据工作种类进行分类，生成多张临时表

举例二:查询各个部门名称及其部门下最高工资，根据部门编号升序

select d.deptno,d.dname,max(sal)#注意：这里为什们能写d.deptno是因为一个部门对应一个部门编号，不对应不能乱用。
from dept d join emp e on e.deptno=d.deptno
group by d.dname 
order by  d.deptno asc

外连接过滤方案

注意区分：

内连接和外连接的区别，主要在内连接只会将on相等的数据行显示出来，而外连接会将主表中所有数据行显示，如果不满足on相等条件的话，会自动加null

语法格式：

select 需要选出的字段
from 主表 left join 副表 on 相等条件
    或
select 需要选出的字段
from 副表  join right 主表  on 相等条件

举例说明：查询所有部门名称及其部门下人数（无论这个部门是否有人，都要展示出来

分析：无论有无人都要显示，那么表名部门表是主表

故得：

select dept.dname,count(emp.empno)
    from dept left join emp
    on dept.deptno=emp.deptno
    group by dept.dname

联合查询知识点

注意：实际开发基本不用，只是一个知识点

联合查询方案的特点：

• 要求参与合并的两个临时表的字段结构必须保持一致【字段个数，字段类型顺序】
• 将两表数据沿着垂直方向进行堆砌到同一临时表中
• 联合查询生成的临时表的字段只能来自于【第一个临时表字段】
• 语法格式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9oNywj1Z-1635518870045)(C:\Users\小样\AppData\Roaming\Typora\typora-user-images\image-20211029145332459.png)]

• union命令在执行时，自动将两个临时表内容相同数据行进行过滤，类似于java中set集合
• union all命令执行时，不会将临时表重复的数据进行过滤

子查询（重要）

• 子查询：查询时无法从当前临时表得到需要的数据，此时mysql服务器允许开发人员通过【完整查询语句】从别处得到需要的数据，从而保证查询正常执行。注意：子查询语句也必须是一个完整查询语句
• 子查询可以在哪些场景中提供数据：

1.select 字段，聚合函数，子查询

举例：查询职员姓名，工资，已经公司的平均工资

select ename,sal,(select avg(sal) from emp)

from emp

2.from ：作用1：将硬盘上表文件加载到内存中生成一个临时表

​ 作用2：指向在内存中已经存在的临时表，此时可以借助于子查询提供这个临时表

举例：

select t1.deptno,t1.dname,t1.ename
from(
select dept.deptno,dept.dname,emp.ename,emp.job,emp.sal
from emp join dept
on dept.deptno = emp.deptno
) as t1

3.where：可以通过子查询得到进行判断的数据

举例：查询工资高于公司平均工资的职员信息

select ename,sal

from emp

where sal>(select avg(sal) from emp)

4.having：可以通过子查询得到删除临时表时需要判断数据

举例：查询出部门平均工资高于公司平均工资的部门信息

select deptno,avg(sal)针对部门的

from emp

group by deptno#按照部门进行分组

having avg(sal)（这个avg（sal）是针对部门的） >(select avg(sal) from emp)（这个avg（sal）针对整个公司）

不能使用子查询的语句为：

1.group by 分组字段名1，分组字段名2

2.order by排序字段1，排序字段2

3.limit 起始位置，向下截取行数

数据类型

1.整数类型

int 4字节

Bigint 8字节

2.小数类型

float 单精度浮点小数，7位有效数字

double 双精度浮点小数，15位有效数字

decimal(M,D):定点小数，m表示精度，d表示小数的位数，decimal是以字符串形式存储的，主要用于货币计算

3.日期与时间类型

year 格式：YYYY

time 格式：HH:MM:SS

DATE 格式：YYYY-MM-DD

4.字符串类型

重点讲解varchar和char

• varchar(m)表示定长可变字符串

可变表示字段在硬盘上存储字符空间可以根据实际情况进行缩小（注意：不能大于定义的长度）

举例：

ename varchar(3)
insert into test1 values(‘abc’) #硬盘 【a】【b】【c】
insert into test1 values(‘ef’) #硬盘 【e】【f】

• char定长不可变字符串

定长：当前字段可以存储的字符个数是固定的

举例：

sex char(3)
insert into test1 values(‘abc’) 【a】 【b】 【c】
insert into test1 values(‘ef’) 【e】 【f】 【空格】

注意：针对char类型字段进行数据读取时，mysql服务器会自动将字符串结尾出空格去掉，因此插入字符串以空格结尾，不要添加到由char类型修饰的字段中

约束

1.非空约束：表示要求管理的字段不能存储null值

举例：

create table test(

sid int,

sname varchar(10) not null#为sname字段添加非空约束

)

注意:不能为空但是可以重复出现

2.唯一性约束：表示要求管理的字段存储值不能有重复，但是可以可以存储null值（因为null值无法比较）

举例：

create table test(

sid int,

sname varchar(10),

email varchar(50) unique#为email字段添加唯一性约束

)

insert into test2 values(1,‘mike’,‘mike@163.com’)成功
insert into test2 values(2,‘allen’,‘mike@163.com’) # Duplicate entry 重复数据冲突，失败
insert into test2 values(3,‘smith’,null)成功
insert into test2 values(4,‘tom’,null)成功

3.主键约束：表示管理主键字敦啊，要求主键字段内容不能为null并且不能出现重复值

create table test(

sid int primary key,#为sid字段添加主键约束

sname varchar(20)

)

insert into test3(sid,sname) values(1,‘mike’)成功
insert into test3(sid,sname) values(1,‘allen’) #Dupliate entry，失败
insert into test3(sid,sname) values(null,‘tom’)失败

4.外键约束

这是比较重要的

表示：管理外键字段，要求外键字段的值应该来自于关联的一方表中主键字段的值，可以为null

一般常见写法：

alter table 多方表

add constraint 外键约束对象名 foreign key(外键字段名)

references 一方表（主键字段）

举例：

alter table emp

add constraint drpt_fk foreign key(deptno)

references dept(deptno)

索引

前言：

由于表文件存放数据行会随着时间推移，会越来越多。where命令在执行时需要对表文件所有数据行进行遍历，所以时间越久，数据行越大，where命令执行时消耗时间就会不断增加

索引的作用：在表文件数据行增加时候，不会大幅度降低查询语句执行效率

工作原理：事先将字段中内容进行排序，在where命令进行定位时，避免对表中所有数据进行遍历，将提升查询速度

索引管理：

1.将字段内容交给某个索引进行管理（即为某个字段创建索引）

create index 索引名 on 表名（字段名）

2.查询表文件关联的索引

show index from 表名

注意：在为表文件绑定【主键约束】，【唯一性约束】，【外键约束】时， mysql服务器自动将当前字段数据进行排序并交给指定索引管理

3.删除表文件指定索引

drop index 索引名 on 表名

explain中type的解释

命令各司：explain 查询语句

作用：展示当前查询得到结果是否通过索引来进行定位，DBA通过执行计划了解在本次查询过程，是否使用预先创建好的索引

查询效率：从上往下依次增加

all:

WHERE对表文件所有数据行进行遍历，才得到了查询结果
这种级别查询语句，随着数据行增加导致执行速度大幅度
降低(DBA来说，极力避免这种级别查询)

index:

WHERE对表文件所有数据行进行遍历，只不过在SELECT
抓取字段内容时，从索引中抓取。
这种级别虽然在抓取数据速度有所提升，但是依然面对
随着数据行增加导致执行速度大幅度（DBA来说，极力避免这种级别查询）

range:

WHERE不会对表文件数据行进行遍历，而是直接从索引得到
定位的数据行行数，将大幅度提升查询效率。
这是DBA进行SQL优化时要保证最低级别。但是这种级别存在 [不稳定性] 当字段内容发生变化时，导致索引失效

注意：索引失效的条件是：mysql服务器如果发现从索引得到数据行行数达到表文件总行数1/3时，此时考虑运行成本问题就会放弃使用索引

ref:

WHERE不会对表文件数据行进行遍历，而是直接从索引得到
定位的数据行行数,同时在这种情况根据定位条件一次只能
得到一个数据行，属于比较稳定执行效率.DBA努力达到的优化
程度

const:

根据主键字段上索引进行定位，是执行效率最快的。但是在实际使用过程中，几乎不会被用到的。

视图

定义：是mysql服务器中一个对象，用于存储查询语句

目的：提供查询语句使用效率，避免在多处地方，重复性开发相同查询语句

注意：其实主要目的就是为了想让你不全了解整张表的信息，只知道部分并对这部分进行改变

命令：

1.将查询语句交给一个视图对象管理

create view 视图对象名 as 查询语句（其实就是将查询语句的结果作为一个视图）

2.通过视图对象调用管理查询语句

select * from 视图对象名

额外功能：视图对象存储一个查询语句，同时视图对象拥有对当前查询语句关联的表文件操作能力

1.通过视图对象对关联表文件数据进行插入

2.通过视图对象对关联表文件数据进行删除

3.通过视图对象对关联表文件数据进行更新

面试会问相关：

1）提高了查询语句复用性，避免了在多处地方重复进行查询语句开发行为

2）隐藏业务中涉及表关系，开发人员通过视图进行操作时是不会知道其具体操作的表

存储引擎（简单了解）

定义：是MySql服务器对应表文件内容管理方式。目前MySql服务器主要采用
INNODB,MyIsam

管理命令：

1.查看mysql服务器支持存储引擎种类：show engines;

2. 修改MySql服务器默认存储引擎

   C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini下
   default-storage-engine=INNODB 进行修改 myIsam

3. 设置表文件依赖存储引擎：

alter table 表名 engine=存储引擎名

了解两种常见存储引擎：

1.MyIsam存储引擎特征:

• 在对表文件内容进行修改时，不会进行备份操作。提高操作执行效率，但是在操作完毕后无法取消本次操作

• MyIsam存储引擎 使用三个文件存储表文件信息

test1.frm : 存储表文件字段信息
test1.myd : 存储表文件数据行信息
test1.myi : 存储表文件字段关联的索引信息

2.INNODB存储引擎:

• 在对表文件内容进行修改时，首先进行一次备份。在进行操作，因此执行效率相对较慢。但是在执行完毕后，由于有备份存在，可以使用备份取消当前操作，增加数据安全性

• INNODB存储引擎，使用一个文件存储表文件信息

test1.frm: 存储表文件字段信息, 存储表文件数据行信息,存储表文件字段关联的索引信息

事务

（transaction)

介绍： 事务是MySql服务器提供一个管理对象，用于对当前表文件备份进行管理

使用：

start transaction;#通知MySql服务器提供一个事务对象，这个事务对象对接下来操作产生所有备份进行管理

举例：

delete from emp where deptno=30 # 生成emp.bak

delete from dept where deptno=30 #声明dept.bak

rollback; #通知mysql服务器将本次操作中所有备份信息覆盖到表文件，来取消本次操作

commit; #通知mysql服务器将本次操作中生成所有备份信息进行删除，并将数据进行提交

置表文件依赖存储引擎：

alter table 表名 engine=存储引擎名

了解两种常见存储引擎：

1.MyIsam存储引擎特征:

• 在对表文件内容进行修改时，不会进行备份操作。提高操作执行效率，但是在操作完毕后无法取消本次操作

• MyIsam存储引擎 使用三个文件存储表文件信息

test1.frm : 存储表文件字段信息
test1.myd : 存储表文件数据行信息
test1.myi : 存储表文件字段关联的索引信息

2.INNODB存储引擎:

• 在对表文件内容进行修改时，首先进行一次备份。在进行操作，因此执行效率相对较慢。但是在执行完毕后，由于有备份存在，可以使用备份取消当前操作，增加数据安全性

• INNODB存储引擎，使用一个文件存储表文件信息

test1.frm: 存储表文件字段信息, 存储表文件数据行信息,存储表文件字段关联的索引信息

事务

（transaction)

介绍： 事务是MySql服务器提供一个管理对象，用于对当前表文件备份进行管理

使用：

start transaction;#通知MySql服务器提供一个事务对象，这个事务对象对接下来操作产生所有备份进行管理

举例：

delete from emp where deptno=30 # 生成emp.bak

delete from dept where deptno=30 #声明dept.bak

rollback; #通知mysql服务器将本次操作中所有备份信息覆盖到表文件，来取消本次操作

commit; #通知mysql服务器将本次操作中生成所有备份信息进行删除，并将数据进行提交