MySQL的学习

张比伯

已于 2022-05-31 11:13:13 修改

阅读量321

点赞数 1

分类专栏： MySQL 文章标签： mysql java

于 2022-01-10 11:56:38 首次发布

本文链接：https://blog.csdn.net/m0_54992396/article/details/122407321

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL

数据库的概念

数据库：

英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

数据库管理系统： DataBaseManagement，简称DBMS。数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。

常见的数据库管理系统： MySQL、Oracle、MS SqlServer、DB2、sybase等....
SQL：结构化查询语言程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

SQL是一套标准，程序员主要学习的就是SQL语句，这个SQL在mysql中可以使用，同时在Oracle中也可以使用，在DB2中也可以使用。
先安装数据库管理系统MySQL，然后学习SQL语句怎么写，编写SQL语句之后，DBMS对SQL语句进行执行，最终来完成数据库的数据管理。

MySQL的安装与卸载

安装

在官网下载对应的安装包，安装选择“经典版”；
之后一路下一步就可以了，注意端口号可以改；不过一般默认3306就好
设置mysql数据库的字符编码方式为 UTF8一定要注意：先选中第3个单选按钮，然后再选择utf8字符集。
选择配置环境变量path：如果不选择，你可以手动配置
mysql超级管理员用户名不能改，一定是：root
设置密码的同时，可以激活root账户远程访问。激活：表示root账号可以在外地登录。不激活：表示root账号只能在本机上使用。
计算机-->右键-->管理-->服务和应用程序-->服务-->找mysql服务
mysql -uroot -p密码查看数据库是否可以连接
show databases; 查看有哪些数据库；use test;

卸载

双击安装包进行卸载删除。
把C:\ProgramData和C:\Program Files (x86)下面的MySQL目录干掉。

SQL语句

分类

DQL：数据查询语言（凡是带有select关键字的都是查询语句） select...
DML：数据操作语言（凡是对表当中的数据进行增删改的都是DML） insert delete update
DDL：数据定义语言（凡是带有create、drop、alter的都是DDL)

是对表结构的操作，不是表中数据
TCL：是事务控制语言包括：事务提交：commit; 事务回滚：rollback;
DCL：是数据控制语言。例如：授权grant、撤销权限revoke....

DQL语句

简单入门

查看某张表：select * from 表名;有一个万能表select 1 from dual
只看表的结构：desc 表名;
查询所有字段时 select a,b,c from test 比select * 效率高；因为*要转换
使用as关键字起别名，也可以打空格忽略as；当别名中有空格时，需要使用单引号'test name'

条件查询

between … and …. 两个值之间, 等同于 >= and <=
- 使用between and的时候，必须遵循左小右大；between and是闭区间，包括两端的值。
is null 为 null（is not null 不为空)；in代表多个or,in中数据时具体的值，not in时不包括
and优先级比or高；若要先or就需要添加小括号
模糊查询，%匹配任意多个字符；_匹配任意一个字符；所以%在前时以什么结尾在后是以什么开头两边都有是包含；当使用%和_特殊符号时要使用转义字符\
distinct去重关键字，必须在所有的的字段前吗，出现在多个字段前面时是多字段联合去重；可以结合count去使用：count(distinct test)去重后计数

排序查询

order by 默认是升序aes，降序是desc；当有多个时，order by第一个相等时才会再比较第二个

数据处理函数

单行处理函数
- lower 转换小写；upper 转换大写
- substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度))
- concat函数进行字符串的拼接；length 取长度；trim 去空格
- case..when..then..when..then..else..end
- round 四舍五入，select round(1236.567, -1)保留到十位。第二个参数决定保留到哪一位0是整数，正数时几位小数，负数时十百千
- ifnull 可以将 null 转换成一个具体值，null不论参加任何运算结果都是null，除非时多行函数会自动忽略null
分组函数(多行处理函数)
- 注意：分组函数在使用的时候必须先进行分组，然后才能用。如果你没有对数据进行分组，整张表默认为一组。
- count 计数；sum 求和；avg 平均值；max 最大值；min 最小值
- count(*)和count(具体字段)的区别是前者只要有一行数据count++而后者统计该字段下所有不为null的元素总和
分组查询
- group by分组
  - select语句的执行顺序是：from-->where-->group by-->having-->select-->order by；所以分组函数不能在where后面直接使用，但是可以使用单行函数
  - 在一条select语句中，如果使用了group by分组，那么select后面只能跟参加分组的字段以及分组函数，其他一律不能跟，Oracle会直接报错，而MySQL即使查询出来也是没有意义的数据
- having对分组进一步过滤
  - having不能代替where，having必须和group by联合使用。
  - where和having，优先选择where，where实在完成不了了，再选择having

多表查询(连接查询)

概念

从一张表中单独查询，称为单表查询。
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字；这种跨表查询，多张表联合起来查询数据，被称为连接查询。

分类

根据语法的年代分类：SQL92；SQL99
根据表连接的方式分类：
- 内连接
  - 等值连接
  - 非等值连接
  - 自连接
- 外连接
  - 左外连接(左连接)
  - 右外连接(右连接)
当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象
- 为解决笛卡尔积现象所以需要：连接时加条件，满足这个条件的记录被筛选出来！

内连接

内连接之等值连接

#SQL92语法：
select 
        e.ename,d.dname
    from
        emp e, dept d
    where
        e.deptno = d.deptno;
#sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。
#SQL99语法：
    select 
        e.ename,d.dname
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno;

采用sql99表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

内连接之非等值连接

select 
    e.ename, e.sal, s.grade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。

内连接之自连接，技巧：一张表看做两张表。

select 
    a.ename as '员工名', b.ename as '领导名'
from
    emp a
join
    emp b
on
    a.mgr = b.empno; //员工的领导编号 = 领导的员工编号

外连接

内连接：（A和B连接，AB两张表没有主次关系。平等的。）

#外连接（右外连接）：
select 
    e.ename,d.dname
from
    emp e 
right join 
    dept d
on
    e.deptno = d.deptno;

#right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。
#外连接（左外连接）：
select 
    e.ename,d.dname
from
    dept d 
left join 
    emp e
on
    e.deptno = d.deptno;

带有right的是右外连接，又叫做右连接；带有left的是左外连接，又叫做左连接。左右外连接可以互换，主要是看那张表是主表
外连接的查询结果一定大于等于内连接的查询结果

多表连接

#语法：
        select 
            ...
        from
            a
        join
            b
        on
            a和b的连接条件
        join
            c
        on
            a和c的连接条件
        right join
            d
        on
            a和d的连接条件
#一条SQL中内连接和外连接可以混合。都可以出现！

子查询

概念

select语句中嵌套select语句，被嵌套的select语句称为子查询。
子查询可以出现在select from where后面
- select ename,sal from emp where sal > (select min(sal) from emp);
- select t.*, s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
union合并查询结果集
- union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。。。但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。
- union使用时要求结果集合并时列和列的数据类型也要一致。
- union和union all的区别：
  - union会对两个结果集进行合并，并且去重按照默认规则排序
  - union all只会合并两个结果集，不排序，不去重

分页查询

limit的使用

limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。

完整用法：limit startIndex, length startIndex是起始下标，length是长度。起始下标从0开始。

select 
        ename,sal
    from
        emp
    order by
        sal desc
    limit
        2, 3;
    
#2表示起始位置从下标2开始，就是表中第三条记录。3表示长度。

注意：mysql当中limit在order by之后执行！！！！！！

分页

每页显示pageSize条记录第pageNo页：limit (pageNo - 1) * pageSize , pageSize
记公式：limit (pageNo-1)*pageSize , pageSize

关于DQL语句的大总结： select ... from ... where ... group by ... having ... order by ... limit ...

执行顺序？
    1.from
    2.where
    3.group by
    4.having
    5.select
    6.order by
    7.limit..

DDL语句

建表

语法：create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型 );

数据类型有很多，我们只需要掌握一些常见的数据类型即可。

    varchar(最长255)
        可变长度的字符串
        比较智能，节省空间。
        会根据实际的数据长度动态分配空间。

        优点：节省空间
        缺点：需要动态分配空间，速度慢。

    char(最长255)
        定长字符串
        不管实际的数据长度是多少。
        分配固定长度的空间去存储数据。
        使用不恰当的时候，可能会导致空间的浪费。

        优点：不需要动态分配空间，速度快。
        缺点：使用不当可能会导致空间的浪费。

        varchar和char我们应该怎么选择？
            性别字段你选什么？因为性别是固定长度的字符串，所以选择char。
            姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。

    int(最长11)
        数字中的整数型。等同于java的int。

    bigint
        数字中的长整型。等同于java中的long。

    float   
        单精度浮点型数据

    double
        双精度浮点型数据

    date
        短日期类型

    datetime
        长日期类型

    clob
        字符大对象
        最多可以存储4G的字符串。
        比如：存储一篇文章，存储一个说明。
        超过255个字符的都要采用CLOB字符大对象来存储。
        Character Large OBject:CLOB
    blob
        二进制大对象
        Binary Large OBject
        专门用来存储图片、声音、视频等流媒体数据。
        往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
        你需要使用IO流才行。

删除表

删除表：
- drop table t_student; // 当这张表不存在的时候会报错！
- drop table if exists t_student;// 如果这张表存在的话，删除

DML语句

insert

insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);
- insert into t_user(字段名1,字段名2) values(),(),(),();
字段名要和值一一对应；当全部插入时字段名可以忽略不写，但是值要按顺序匹配好
insert语句但凡是执行成功了，那么必然会多一条记录。没有给其它字段指定值的话，默认值是NULL。可以在创建时使用default添加默认值
insert插入日期的时候有两个函数可以使用
- mysql的日期格式：%Y 年； %m 月；%d 日；%h 时； %i 分；%s 秒
- str_to_date：将字符串varchar类型转换成date类型
  - str_to_date('字符串日期', '日期格式')
- date_format：将date类型转换成具有一定格式的varchar字符串类型
  - date_format(日期类型数据, '日期格式')；通常使用在查询日期方面。设置展示的日期格式。
- MySQL会自动转换日志和字符串格式，但格式必须是%Y-%m-%d
- date时短日期只包括年月日；datetime是长日期包括年月日时分秒

update

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;
注意：没有条件限制会导致所有数据全部更新。

delete

delete from 表名 where 条件;
注意：没有条件，整张表的数据会全部删除！
delete语句在删除数据的时候效率较低，并且磁盘的物理空间不会被释放，例如删除数据后，自增的id还会在原有基础上继续增加，而不会从0开始；但支持回滚，可以恢复
truncate语句是DDL语句，效率高，速度快会把所有的数据包括磁盘的物理空间全部删除；但不支持回滚，删了就没了

约束

基本概念

在创建表的时候添加约束以保证数据的完整和有效性
约束有：非空约束：not null；唯一性约束：unique；主键约束：primary key (简称PK)；外键约束：foreign key（简称FK）；检查约束：check（MySQL不支持Oracle支持）

非空约束

即添加约束的字段不能为null；在创建表时，列后添加 not null即可；not null只有列级约束，没有表级约束

唯一约束

即添加约束的字段不能重复，但可以为null，两个null也可以；在创建表的时候添加 unique即可，unique可以添加在列后面也可以添加在所有列的最后，以提供联合约束称为表级约束
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
在MySQL中当一个字段被同时添加了非空约束和唯一约束该字段会自动变成主键字段

主键约束

任何一张表都有主键，没有主键，表无效；即使没有指定主键，MySQL的innoDB也会自动创建维护一个不可见的、长度为6字节的row_id
这个row_id是全局的，所以这个值会一直涨，涨到2148次减一，再加一row_id就会变成0会导致主键冲突
主键特征：not null+unique不为空且不重复
在列后面添加 primary key称为列级主键，也可以添加表级主键，做复合主键，但不建议，因为主键的意义在于区分，复合主键较复杂，不推荐
一张表只能有一个主键；一般使用自然主键，业务主键不宜维护
可以使用auto_increment自动维护一个自增的主键

外键约束

当A的a字段需要引用B表的b字段时，为了保证a字段的值都是另B表的b字段值，所以需要给A表的a字段添加外键约束
存在父子表关系，即A表为子表，B表为父表；父子表中先创建父再创建子，先删除子再删除父
子表中引用父表的某个字段这个字段不一定时主键，只要唯一约束(unique)即可;子表中的外键值可以为null

存储引擎

基本概念

存储引擎是MySQL中特有的一个术语，其它数据库中没有；实际上存储引擎是一个表存储/组织数据的方式；不同的存储引擎，表存储数据的方式不同。
show engines \G 查看MySQL有哪些存储引擎
常见的存储引擎有：MyISAM存储引擎；InnoDB存储引擎(默认)；MEMORY存储引擎

MyISAM存储引擎

使用三个文件表示每个表：

格式文件 — 存储表结构的定义（mytable.frm）

数据文件 — 存储表行的内容（mytable.MYD）

索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。

可被转换为压缩、只读表来节省空间

MyISAM不支持事务机制，安全性低。

InnoDB存储引擎

这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征：
        – 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
        – InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）

        – 提供一组用来记录事务性活动的日志文件
        – 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
        – 提供全 ACID 兼容
        – 在 MySQL 服务器崩溃后提供自动恢复
        – 多版本（MVCC）和行级锁定
        – 支持外键及引用的完整性，包括级联删除和更新
    
    InnoDB最大的特点就是支持事务：
        以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
        不能很好的节省存储空间。

MEMORY存储引擎

    使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，
    这两个特点使得 MEMORY 存储引擎非常快。

    MEMORY 存储引擎管理的表具有下列特征：
        – 在数据库目录内，每个表均以.frm 格式的文件表示。
        – 表数据及索引被存储在内存中。（目的就是快，查询快！）
        – 表级锁机制。
        – 不能包含 TEXT 或 BLOB 字段。

    MEMORY 存储引擎以前被称为HEAP 引擎。

    MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
    MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

事务

基本概念

一个事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分割
只有DML语句才会有事务，其他DQL语句没有事务的说法
事务通俗的讲就是批量的DML语句同时成功，或者同时失败
MySQL默认提交所有事务；Oracle需要手动提交
- 所以在MySQL中需要使用start transaction;来开启手动提交事务，执行完DML语句后选择commit还是rollback

原理

通过数据库的InnoDB存储引擎：提供一组用力啊记录事务性活动的日志文件
再事务的执行过程中，每一条DML的操作都会记录到"事务性活动的日志文件"中，再事务的执行过程中，外卖可以提交事务，也可以回滚事务
- 提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中；提交事务标志这，事务的结束；并且时全部成功的结束
- 回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件；事务回滚标志着，事务的结束，并且是一种全部失败的结束。
commit提交事务；rollback回滚事务事务对应的单词：transaction

ACID

A 原子性：说明事务时最小的工作单元，不可再分割
C 一致性：所有的事务要求再同一个事务中，所有的操作必须同时成功或者同时失败
I 隔离性：两个事务之间的影响关系
D 持久性：事务最终结束的保障，事务的提交，就相当于将没有保存到硬盘上的数据保存在硬盘上

事务的四大隔离级别

读未提交
- 最低的隔离级别，还没提交就已经读到了；即事务A可以读到事务B还未提交的数据，这种现象称为脏读现象
读已提交
- 提交后才能读取，事务A只能读到事务B已经提交的事务；解决了藏独现象；但出现了不可重复读现象；时Oracle的默认级别
- 不可重复读：即事务A在一次事务中读取数据，若事务B一直在提交，则会造成每次读取结果都不相同
可重复读
- 只能读取到事务刚开启时的数据；事务没结束前，即使其他事务有提交，也不会读取到；MySQL默认的隔离级别
- 解决了脏读和不可重复读；但出现了幻读现象
- 幻读：事务开启后，只要事务不结束，读到的永远是事务开始的数据，不够真实；
序列化
- 最高的隔离级别，效率低，解决所有问题；这种隔离级别意味着事务排队，不能并发；每次读到的数据都是真实的
- 但是事务A只要不结束事务B就不能操作，要一直等到事务A结束

索引

基本概念

索引是指数据库的字段上添加的，是为了提高查询效率存在的一种机制；可以理解为一本书的目录
一张表的一个字段可以添加一个索引，也可以多个字段联合起来做索引
MySQL中的扫描机制有两种：全表扫描和根据索引扫描
索引采用B+树的数据结构；遵循左小右大，中序遍历获取数据

类型

单一索引；复合索引；主键索引；唯一性索引……
唯一性索引使用在较弱(因为添加了唯一约束，也可以设置插入重复数据，就会产生重复数据了)的字段上添加索引的作用就不大了

实现原理

在任何数据库中主键上会自动添加索引对象；在MySQL中一个字段上如果有unique约束的话，也会自动添加索引对象
在任何数据库中，任何一条记录在硬盘存储上都有一个物理存储编号
在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在

使用条件

数据量庞大，需要具体测试多大是大
该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）
不要随意添加索引，索引是需要维护的，若是太多，反而会降低系统的性能；在一般的查询时多使用主键或者使用unique约束的字段，效率高

创建与删除

创建

语法：creat index 索引名 on 表名(字段名);

删除

语法：drop index 索引名 on 表名;

查看

语法：explain关键字查看该查询是否使用了索引
type属性的值是All则是全表查询；type的值是ref是使用了索引的查询

失效常景

使用了模糊查询，以“%”开头了；查询的时候不知道前面是多少位
使用了or语句，而or语句一边有索引一边没有，那么索引失效；若使用or两边必须都有索引才可以
使用了复合索引(两个或更多字段联合起来添加一个索引)，并且没有遵从最左原则
- 例如将A，B两个字段来做复合索引，在查询时，查询了B而没有查询A，那么就没有遵从最左原则，索引就会失效
在where当中索引列参加了运算，索引失效。
- select * from emp where sal+1 = 800;
在where中使用了单行函数
数据库没有使用了MyISAM存储引擎.......

视图(view)

基本概念

view:站在不同的角度去看待同一份数据。
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！
视图的特点：通过对视图的操作，会影响到原表数据;在实际开发中会简化开发,也利于维护；使用视图时和table一样，视图对象也是存在在硬盘上的，不会消失

创建与删除

创建

语法: create view 视图名 as select * from 表名;
创建的时候，后面只能时DQL语句；创建后，可对其增删改查

删除

语法: drop view 视图名;

DBA常用命令

数据库导入

D:\文件名.sql -u数据库用户名 -p数据库密码

数据库导出

source D:\文件名.sql

数据库三范式

基本概念

第一范式：要求任何一张表都必须有主键；每一个字段原子不可再分割
第二范式：建立再第一范式的基础上，要求所有非主键字段完全依赖主键，不产生部分依赖
第三范式：建立再第二范式的基础上，要求所有的非主键字段直接依赖主键，不产生传递依赖
按照三范式设计数据库，可以有效避免数据冗余以及空间的浪费

部分依赖

学生编号 学生姓名 教师编号 教师姓名

----------------------------------------------------

•   1001            张三      001     王老师
•   1002            李四      002     赵老师
•   1003            王五      001     王老师
•   1001            张三      002     赵老师

上表需要将学生编号教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）经过修改之后，以上的表满足了第一范式

在经过以上修改后，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。需要拆分成三张表，以后在哪个学生以后在哪个老hi一张学生老师关系表

多对多，三张表，关系表两个外键！

学生表
        学生编号(pk)        学生名字
        ------------------------------------
        1001                    张三
        1002                    李四
        1003                    王五
        
        教师表
        教师编号(pk)        教师姓名
        --------------------------------------
        001                 王老师
        002                 赵老师

        学生教师关系表
        id(pk)          学生编号(fk)            教师编号(fk)
        ------------------------------------------------------
        1                       1001                        001
        2                       1002                        002
        3                       1003                        001
        4                       1001                        002

传递依赖

    学生编号（PK） 学生姓名     班级编号      班级名称
    ---------------------------------------------------------
        1001      张三        01          一年一班
        1002      李四        02          一年二班
        1003      王五        03          一年三班
        1004      赵六        03          一年三班

该表满足第一二范式，但是不满足第三范式

因为班级名称依赖于班级编号，而班级编号依赖与学生编号，产生了传递依赖
在一对多的情况下，设计两张表，多的表加外键

班级表：一
        班级编号(pk)                班级名称
        ----------------------------------------
        01                              一年一班
        02                              一年二班
        03                              一年三班

        学生表：多

        学生编号（PK） 学生姓名 班级编号(fk)
        -------------------------------------------
        1001                张三          01          
        1002                李四          02          
        1003                王五          03          
        1004                赵六          03

总结