mysql教程

数据存取方式的对比

传统的数据存储方式有

Java集合：程序一旦停止运行，内存中的数据全部清空，无法做到持久化存储
XML文件：存取非常麻烦
传统文件系统：数据之间没有结构化的关联关系，难以应对业务开发中经常使用的增删改查操作

那么，如何同时实现数据的持久化+结构化+方便存取呢？
使用数据库就可以解决这个问题，数据库中的数据都是按照特定的格式进行存储的，可以方便地对数据进行增删改查操作。

mysql概述

mysql是一个开源免费可跨平台的数据库管理系统（DBMS）

数据库相关概念

数据库|DB
存储数据的仓库，本质上是一个文件系统，保存了一系列有组织的数据
数据库管理系统|DBMS
操纵和管理数据库的大型软件，如mysql
结构化查询语言|SQL
专门用来操作数据库的语言

常见的DBMS

oracle 收费，商业级数据库，收购界大佬
mysql 开源的，使用最多的关系型数据库管理系统
sqlserver 微软开发的
sqlite 嵌入式的小型数据库管理系统，应用于手机端

tips：mysql5.7和mysql8.0是mysql最受欢迎的两个版本

RDBMS与非RDBMS
关系型数据库（RDBMS）把复杂的数据结构归结为简单的二元关系。
非关系型数据库通常基于键值对存储数据，不需要经过SQL层的解析，进一步提高性能。

非关系型数据库分类：

键值型数据库：通过key-value的方式存储数据，常用作内存缓存，redis是最受欢迎的键值型数据库
文档型数据库：把文档作为处理信息的基本单位，一个文档就相当于一条记录，mongoDB是最流行的文档型数据库
搜索引擎数据库：核心原理是倒排索引，典型代表有elasticsearch、solr等
列式数据库：与oracle、mysql、sqlserver等行式存储数据库相对，列式数据库将数据按列存储到数据库中，大大降低I/O时间，适合分布式文件系统，典型代表HBase
图形数据库：利用图这种数据结构存储实体对象之间的关系，典型代表neo4j、infogrid等

tips：NoSQL对SQL做出了很好的补充。在实际开发中，有很多业务需求其实并不需要完整的关系型数据库功能，非关系型数据库的功能就足够使用了，比如日志收集、排行榜、定时器等。使用性能更高、成本更低的非关系型数据库会是更好的选择。
但是，掌握SQL又是必要的，是每个IT从业者的必备技能之一。

学习mysql，我们应该：

掌握数据库的使用，能够根据业务需求对数据进行增删改查
掌握数据库的设计，能够根据项目需求进行数据库和表关系的设计

mysql可视化管理工具推荐使用navicat或DataGrip，两者底层其实都有对SQL语句的封装，我们鼠标点击，就相当于执行一条SQL命令

sql命令速查

数据定义语言DDL

DDL是对数据库、数据表、的增删改查（create、drop、alter）

Command	描述
create database <db> default character set utf8 collate utf8_general_ci;	创建数据库

📢 collate指定数据库排序规则，也就是当我们对多个字符串类型的数据进行order by查询时，数据最终排序表现的方式。 utf8_general_ci是不区分大小写的

数据操作语言DML

DML主要完成数据的增删改（insert、update、delete）

Command	描述
mysql -u <user> -p	值

数据查询语言DQL

DQL是对数据进行查询（select）

数据控制语言DCL

DCL用来控制数据库组件的存取（事务commit、事务rollback）

mysql存储引擎

存储引擎定义了存储数据、建立索引、更新数据、查询数据的具体实现规则，又称表的类型

mysql支持的存储引擎有InnoDB（默认）、MyISAM等，可以使用show engines;命令查看所有存储引擎

InnoDB存储引擎的特点：支持事务、保存点、主外键、主键自增等

📢存储引擎相对于数据库，就好比马达相对于汽车，一个没有马达的汽车是跑不起来的。

mysql索引

索引类似一个目录或者标签，mysql查询有索引的特定记录时，就不需要从头开始一个一个找，而是直接“定位”，这会大大提高查询效率！（通常主键自带索引）

索引的分类：

普通索引可以添加在任意数据类型的字段上
唯一性索引
全文索引只能创建在字符串类型的字段上，InnoDB不支持
单列索引一个索引只对应一个字段
多列索引一个索引可对应多个字段
空间索引 InnoDB不支持

索引的缺点：

创建和维护索引需要消耗时间
索引需要占用物理空间

索引的设计原则：

为经常出现在where语句中的字段添加索引，而不是select后面的（where是定位的）
索引的值尽量唯一
不要添加过多的索引，这会大大提高维护成本！

索引的用法：

-- 添加索引
alter table  <tname> add index <index_name>
(字段)
create <index_name> on <tname>(字段)

-- 删除索引
alter table  <tname> drop index <index_name>
drop index <index_name> on <tname>

mysql视图

视图是数据库中虚拟的表，并不真实存在，它能够针对不同用户或业务需求以不同的方式查询同一张表中的数据（我们通常只需要查询出原始表中特定的几个字段来满足业务需求，而不需要把所有字段全部查出）而视图本质上就是一条查询语句执行的结果

视图的用法：

-- 创建视图
create view <view_name> as <特定的select语句，查询结果保存在视图中>

-- 使用视图
select * from <view_name>

-- 删除视图
drop view <view_name>

视图和原始表之间的数据是同步的，只不过少了几个字段而已。在视图中添加数据，原始表也会改变

mysql触发器

触发器（Trigger）中定义了一系列操作，当对指定表进行增删改查时，会自动触发执行这些操作以更新目标表。

触发器用法：

-- 创建触发器 当在指定表中插入数据时，在目标表也新增一条name字段记录
-- begin后end前填写触发的操作

create trigger <trigger_name> 
  after insert on <指定表>
  for each row
  begin         
    insert into <目标表>(name) values(new.name)
  end;

create trigger <trigger_name> 
  after delete on <指定表>
  for each row
  begin         
    delete into <目标表>(name) values(old.name)
  end;

-- 🤔我们好像可以利用触发器来备份用户刚删除的文件
-- 删除触发器
drop trigger <trigger_name>;

mysql存储过程

存储过程是一组定义好的、为了完成特定功能的SQL语句的集合，存储在数据库中，供开发人员调用（类似Java中的方法）

存储过程的优点：

一次定义，多次调用
与使用原始sql语句相比，执行速度更快，更安全

存储过程用法：

-- 1. 入参
-- 创建存储过程
create procedure <proc_name>(<入参或出参> <参数名称> <参数类型>)
  begin
    declare <变量名称> <变量类型>;
    if <参数名称> = <?> then
        set <变量名称> = 'mysql';
        else
        set <变量名称> = 'java';
    end if;
    insert into <目标表>(字段) values(变量名称);
  end;

-- 调用存储过程
call <proc_name>(参数);

-- 删除存储过程
drop procedure <proc_name>;


-- 2. 出参/返回值
-- 创建存储过程


-- 调用存储过程
call <proc_name>(@count_num);
-- 查看调用结果
select @count_num


-- 删除存储过程
drop procedure <proc_name>;

mysql数据类型

整数类型

数据类型	大小	描述
tinyint	1byte	非常小的整数
smallint	2byte	小整数
mediumint	3byte	中等大小的整数
int	4byte	整数
bigint	8byte	大整数

📢 int是最常用的整数类型，适合存储生活中常见的整数。太小存不下，太大又会浪费掉占用的空间

小数类型

数据类型	大小	描述
float(M,D)	4byte	单精度浮点数
double(M,D)	8byte	双精度浮点数
decimal(M,D)	3byte	定点型小数，将整数部分和小数部分分开存储

M: 总位数，该字段最多需要的十进制有效数字个数
D：保留几位小数，小数点后最多保留的十进制数字个数，多余的位自动四舍五入

📢 计算机在进行小数运算时，不可避免地会损失一些精度，所以"金额"尽量不要用浮点数类型，可以使用decimal定点型保证不丢失精度。除此之外，浮点数和定点数用法基本一致

字符串类型

常用字符串类型：char(M)，varchar(M)

数据类型	大小	描述
char(M)	M个字符	固定长度的字符串
varchar(M)	M个字符	可变长度的字符串
tinytext	2^8-1byte	非常小的字符串
text	2^16-1byte	小型字符串
mediumtext	2^24-1byte	中等长度的字符串
longtext	2^32-1byte	大的字符串

日期和时间类型

数据类型	大小	描述
year	1byte	年份值
time	3byte	时间值
date	3byte	日期值
datetime	8byte	日期+时间值
timestamp	4byte	时间戳，从1970-01-01 00:00:00到今天的毫秒值

二进制类型

二进制类型用来保存图片、视频等数据，常用的有tinyblob，blob，mediumblob，longblob

数据类型	大小	描述
bit(M)	M位二进制01	小的二进制数据
binary	M byte	普通的二进制数据
varbinary	M byte	普通的二进制数据，可变长
tinyblob	255byte	大的二进制数据
blob	2^16-1byte	更大的二进制数据
mediumblob	2^24-1byte	最大单位MB
longblob	2^32-1byte	最大单位GB

基本sql语句

操作数据库

-- 查看所有的数据库
show databases;

-- 使用数据库
use <dbname>;

-- 创建数据库,同时指定字符集和排序规则
create database <dbname> default character set utf8 collate utf8_general_ci;

-- 删除数据库
drop database <dbname>;

操作数据表

-- 在操作表之前，需要指定去使用一个数据库
-- 字段参数：主键（非空唯一），自增 ，默认值 
-- 查看数据库中的表
show tables;

-- 查看指定表的结构
desc <tname>;

-- 创建表
create table <tname> (
     id    int,
     name  varchar(40) NOT NULL,
     price int DEFAULT 0,
     PRIMARY KEY(id)
);

-- 修改表结构，字段约束
alter table <tname> <alter|add|drop> column <cname> <对字段的修改>;

-- 重命名字段或更改数据类型
alter table <tname> change column <old_cname> <new_cname> <datatype>;

-- 删除表
drop table <tname>;

操作数据

-- 插入数据
-- 省略字段列表相当于添加全部字段
insert into <tname>(字段列表) values (值列表),(值列表)...;

-- 删除数据
delete 字段列表 from <tname> where 条件

-- 修改数据，字符串值要加''
update <tname> set 字段=值,字段=值... where 条件

-- 查询
select 字段列表 from <tname> where 条件

-- 清空表（谨慎使用）
delete from <tname>

mysql函数

数学函数

函数	功能
abs()	求绝对值
floor()	向下取整
ceil()	向上取整

数学函数举例：

1
2
3

select abs(score) from course where id=1;
select floor(score) from course where id=1;
select ceil(score) from course where id=1;

字符串函数

函数	功能
insert(s1,index,len,s2)	向字符串中插入新的字符串,下标从1开始
left(s,len)	截取字符串的前几个字符
right(s,len)	截取字符串的后几个字符
substring(s,index,len)	截取字符串
reverse()	反转字符串
upper()	将字符串转为大写
lower()	将字符串转为小写

字符串函数举例：

select left(name,1) from student where id=1;
select right(name,2) from student where id=1;
select substring(name,1,3) from student where id=1;
select reverse(name) from student where id=1;
select lower(name) from student where id=1;
select upper(name) from student where id=1;

日期函数

函数	功能
curdate()	获取当前日期
curtime()	获取当前时间
now()	获取当前日期+时间
datediff(d1,d2)	计算两个日期的差值
adddate(d,n)	计算累加n天后的日期
subdate(d,n)	计算指定日期n天前的日期

日期函数举例：

select curdate();
select curtime();
select now();
select datediff('2022-12-26','2022-12-01');  -- 25
select datediff('2022-12-01','2022-12-26');  -- -25
select adddate('2022-12-01',25);  -- 2022-12-26
select adddate('2022-12-01',-25);  -- 2022-11-06
select subdate('2022-11-29',31);  -- 2022-10-29

聚合函数

函数	功能
count()	统计总记录数
sum()	计算某个字段所有值的和
avg()	求平均值
max()	求最大值
min()	求最小值

聚合函数举例：

select count(id) from student;  --统计总人数
select sum(score) from course where name='lisi';  --计算总成绩
select avg(score) from course where name='lisi';  --计算平均成绩
select max(score) from course where name='lisi';  --计算最高成绩
select min(score) from course where name='lisi';  --计算最低成绩

条件查询

分组查询，排序查询

1
2
3

select name,avg(score) from course group by name;  -- 按学生姓名分组，以统计每个学生的平均成绩

select * from course order by score asc;  --按成绩从高到低排序

mysql运算符

与Java类似：

算术运算符（+、-、*、/）
比较运算符（>、<、=、>=、<=）
逻辑运算符（&& || ）

1 2	select name,score-60 from course; -- lisi 39 select name,score from course where score>98;

mysql中的函数和运算符可以用于select子句或where子句，在select语句中使用，将直接输出函数或运算符的结果；在where语句中使用，将根据函数或运算符结果去增删改查满足条件的数据