MySQL基础笔记

一.常用命令

启动和停止MySQL服务

//windows
net start mysql
net stop mysql
//linux
service mysqld start
service mysqld stop
service mysqld status

登录mysql

mysql -h 主机地址 -u 用户名 -p 用户密码 -P 连接端口号
mysql -uroot -p

常用命令

修改字符集：set names xxx;
展示数据库：show databases;
使用某一个数据库：use xxx;
展示表：show tables; show tables from xxx;
查案当前所在数据库：select database();
创建表：create table xxx(字段名称 数据类型,...);
查看表的结构：desc xxx;
插入数据：insert into xxx values(...);
更新数据：update 表名称 set 属性名称 = xxx where 字段名称 =xx;
删除数据：delete from 表名称 where 名称字段=xx；
修改表结构：alter table 表名称 ...
删除表：drop table 表名称;
退出客户端：exit

Linux服务器

//配置开机自启
1.修改文件 添加service mysqld start
vim /etc/rc.local
 
2.启动服务：
systemctl start mysqld

3.设置开机自启动：
systemctl enable mysqld
systemctl daemon-reload

//配置远程登陆
1.在安全组中入规则开放3306端口
2.通过shell登录数据库
mysql -uroot -p
3.查看当前连接规则
use mysql;
select host,user from user;
4.修改连接规则
update user set host='%' where user='root';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
5.刷新权限
FLUSH PRIVILEGES;

安装过程：（在Centos8上安装mysql8）

#下载
wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm
#安装数据源
yum install mysql80-community-release-el8-1.noarch.rpm
#检查数据源
yum repolist enabled | grep "mysql.*-community.*"
#禁用CentOS8自带mysql模块
yum module disable mysql
#安装数据库
yum install mysql-community-server
#启动mysql
service mysqld start
service mysqld status
#显示mysql的随机密码
grep 'temporary password' /var/log/mysqld.log
#登录mysql
mysql -u root -p
#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Test_123456';
#查看密码策略
SHOW VARIABLES LIKE 'validate_password%';
#开放远程访问
#1.创建权限用户记录
create user 'root'@'%' identified by 'Test_123456';
#2.授权
grant all privileges on *.* to 'root'@'%' with grant option;
#开放3306端口

1.创建表的时候指定主键和外键：

[CONSTRAINT 外键名]  FOREIGN KEY (外键字段名) REFERENCES  父表名(参照字段名);
PRIMARY KEY (主键字段名)

2.创建表之后指定外键依赖关系：

ALTER TABLE 子表名 ADD [CONSTRAINT 外键名] FOREIGN KEY(外键字段名) REFERENCES 父表名(参照字段名);

二.学习笔记

1. 数据准备

导入数据：

source  sql路径

示例数据：bjpowernode.sql

表格：show tables；

dept：部门表

emp：员工表

salgrade：工资等级表

大致查看数据：

查看所有数据：
select * from 表名称; 

查看表的结构：
desc 表名;
describe 表名;

查看mysql版本：
select version();

查看当前使用的数据库：
select database();

2. SQL语句

简单分类：

1. DDL（Data Definition Language）：数据定义语言
2. DML（Data Manipulation Language）：数据操作语言
3. DCL（Data Control Language）：数据控制语言
4. DQL（Data Query Language）：数据查询语言

3. 简单查询

查询：select ... from ... where...

• select后面也可以直接跟字面值，例如1，'abc'...
• 字面值会借助表结构直接打印出来
• distinct：去重（select distinct）

起别名：使用as关键字，或者使用空格

在所有的数据库中，字符串统一使用单引号，单引号是标准。

双引号在oracle数据库中用不了，在mysql中可以使用。

条件查询：可以使用比较符号

条件查询运算符
=	<	>
<> 或者 !=	<=	>=
between ... and ...	is null	is not null
and	or	in
not	like

• like模糊查询，支持使用%和_进行匹配，前者匹配任意个字符，后者匹配一个字符
• between and：必须左小右大，并且包含观点

排序：使用关键字order by

• 默认是升序，指定降序需要在后面添加 desc（升序为asc）

• 多个字段排序：使用逗号隔开多个字段

  order by sal desc, name asc;

• 也可以使用字段位置- 不建议使用

  order by 2;
  //表示第二列

4. 数据处理函数（单行处理函数）

数据处理函数又称为单行处理函数（一个输入对应一个输出）

ifnull：在所有数据库的运算当中，只要有null参与运算，结果都是null

ifnull可以进行空处理
ifnull(数据， 如果数据是null时候的替代值)

字符函数
concat(str1, str2,...)	拼接函数
length(str)	获取字节长度
char_length(str)	获取字符长度
substr(str, 起始索引, 截取长度)	截取字符（第一个字符索引为1）
instr(str, substr)	获取字符串第一次出现的索引
trim(str)	去除前后指定的字符，默认去除空格
lpad/rpad(str, 要求字符长度，填充字符长度)	左填充和右填充
upper/lower(str)	转大小写
strcmp(str1, str2)	比较两个字符的大小
left/right(str, 截取指定长度)	从左边或者右边截取字符串

数学函数
abs()	绝对值
ceil()/floor()	向上向下取整
round(数字， 位数)	四舍五入，可以指定保留位数（位数可以是负数）
truncate()	可以指定小数点后保留位数
mod()	取余

时间日期函数
now	当前日期+时间
curdate	当前日期
curtime	当前时间
datediff(expr1, expr2)	两个时间的查
date_format(expr, fmt)	日期格式，fmt为格式化字符串
str_to_date(expr, fmt)	根据指定格式解析字符串为日期格式

流程控制函数
if(expr1, expr2, expr3)	类似于?:运算符
case()	类似于switch-case

case 表达式
when 值1/条件1 then 结果1
when 值2/条件2 then 结果2
...
eles 结果n
end

5. 分组处理函数（多行处理函数）

sum、avg、max、min、count

分组处理函数在使用之前必须先进行分组，如果没有进行分组的话，则默认整张表为一个组。

• 分组函数自动忽略null
• count(*)和count(具体字段)的区别 具体字段中可能会具有null，但是一行中全部为null的情况是不会出现的

已有的关键字的执行顺序：

from > where > group by > select > order by

分组函数不能直接用在where之后，因为在where执行的时候还没有进行分组

分组关键字：group by

• 可以有多个分组条件，用逗号隔开

分组后筛选使用关键字：having

分组前筛选使用关键字：where

优化策略：having和where优先使用where，能用where先过滤的先使用where

6. 连接查询

连接查询：多表连接

分类：

• 内连接：等值连接、非等值连接、自连接
• 外连接：左外连接、右外连接
• 全连接

连接查询的时候，应该要想办法降低笛卡尔乘积的次数，减少表的连接次数

内连接：

SQ92语法：

select 查询列表
from 表名1,表名2,...
where 筛选条件 and (其他条件)

• 缺点：结构不侵袭，表的连接条件和后续其他筛选条件混在一起

SQ99语法：

select 查询列表
from 表名1 别名1
(inner) join 表名2 别名2
on 连接条件
...join ...
on ...
where 筛选条件

• 等值连接：连接条件是等价关系
• 非等值连接：连接条件不是等价关系（例如在范围之间等）
• 自连接：自己和自己连接

外连接：

内连接的特点：完全能够匹配才出现在结果中

外连接：某张表中的内容会全部显示，哪张表全部显示则对应了左右全外连接

left/right/full (outer) join

7. 子查询

子查询就是select语句嵌套select语句。

from后面的子查询可以将子查询的结果当作一张临时表

select后面：要求子查询的结果为单行单列

from后面：要求子查询的结果可以为多行多列

where或having后面：要求子查询的结果必须为单列

exists后面：要求子查询结果必须为单列（相关子查询）

union的用法：union联合查询

(子查询)
union
(子查询)

• 当查询结果来自多张表，但是多张表之间没有关联的时候，这个时候往往使用union将表结果进行合并

• 多条查询语句的列数必须一致（相当于在后面拼接）

  列的数据类型在mysql中不要求一致，在Oracle中要求一致

• union自动去重，union all可以支持重复项

union的效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积

union可以满足匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接

8. 分页查询

使用limit关键字

limit 起始条目索引, 显示的条目数

• limit语句放在命令后面，也在最后执行
• 起始条目索引从0开始

9. 表的创建和删除

create table 表名(
    字段名1 数据类型,
    字段名2 数据类型,
    ...,
    字段名n 数据类型
);

• 最后一个没有逗号

常见的数据类型
varchar(n)	可变长度字符串（根据实际存储决定开辟空间，n表示最多存储字符数，最多255，必须给出）
char(n)	固定长度字符串（无论存储，实际开辟n个字符的空间，n不写默认为1，最多255）
int	数字中的整数型，java-int
bigint	数字中的长整型，java-long
float	单精度浮点数
double	双精度浮点数
date	短日期类型
datetime	长日期类型
clob	字符大对象，最多可以存储4G的字符串
blob	二进制大对象

表的删除

删除表
drop table 表名;
drop table if exists 表名;

10. 表中数据的操作

插入数据

insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...)

• 其中字段名和值需要一一对应
• 字段名可以省略，缺省表示匹配全部字段
• value为单行插入，values为多行插入

修改数据

update 表名 set 字段1 = 值1, 字段2 = 值2, ... where 条件;

• 没有条件限制会导致所有数据全部更新

删除数据

delete from 表名 where 条件;

• 没有条件全部删除
• 这种删除数据的方式比较慢：逐个删除，存储空间不释放，后续数据可以回滚
• 属于DML语句

表中数据的快速删除

truncate table 表名;

• 删除效率较高，物理删除：存储空间释放，后续数据不支持回滚
• 属于DDL语句
• 不能添加where语句

表的快速复制（快速复制表）

create table 新表名 as select * from 旧表名;

• 将一个查询结果当作一张表新建

将查询结果插入

insert into 被插入的表名 select * from 查询表名;

11. 对表结构的增删改

实际业务当中，需求和表确定了之后，对表结构的修改一般是比较少的。因为成本较高。

添加字段
alter table 表名 add 新字段 数据类型;

修改字段
alter table 表名 modify 旧字段 新数据类型;

删除字段
alter table 表名 drop 旧字段;

12. 约束

约束：constraint

常见约束：

• 非空约束：not null
• 唯一性约束：unique
• 主键约束：primary key（PK）
• 外键约束：foreign key（FK）
• 检查约束：check（mysql不支持，Oracle支持）

.sql文件被称为sql脚本文件，sql脚本文件中编写了大量的sql语句，批量执行sql语句可以使用sql脚本文件

利用source关键字执行

注意事项：

1. 唯一性约束的字段可以为null，null和null不算重复

2. 两个字段综合起来的唯一性（联合唯一）

   create table t_test (
       id int,
       name varchar(255),
       email varchar(255),
       unique(name, emial)
   )

3. 约束分为列级约束和表级约束

   • 列级约束：直接添加在字段后面的约束，仅对单个字段的约束
   • 表级约束：非列级约束，需要对多个字段进行约束

4. not null只有列级约束

5. 在mysql中，not null unique 自动变成 primary key（在Oracle中不一样）

6. 修改约束就是修改字段

主键约束

1. 主键值是每一行记录的唯一标识

2. 任何一张表都应该有主键，没有主键的话表是无效的

3. 复合主键（是表级约束，但是在实际开发中不建议使用复合主键）

   primary key(id, name)

4. 主键值建议使用int、bigint、char等类型，不建议使用varchar

5. 主键还可以分为自然主键和业务主键，自然主键使用比较多

6. 在mysql中，可以使用自增机制来帮助自动维护主键值，从1开始自增

   id int primary key auto_increment

外键约束

1. 外键需要时刻保证外键值一定在另一张表中存在（涉及到表，数据等的创建和删除顺序）

2. 外键约束的写法，写在字段定义的后面

   foreign key(本表字段) references 其他表名(该表字段)

3. 外键可以为null，外键在另一张表中一定要具有唯一性，不一定是主键

13. 存储引擎

存储引擎是MySQL中特有的一个术语，实际存储引擎指的就是一个表存储和组织数据的方式，不同的存储引擎，表存储数据的方式不同。

展示建表的时候的sql语句
show create table 表名

可以在建表的时候指定存储引擎和字符编码方式

create table t_test (
    id int,
    name varchar(20)
)ENGINE=InnoDB DEFAULE CHARSET=utf8

• mysql默认的存储引擎是：InnoDB
• mysql默认的字符编码方式是：utf8

服务器可用的引擎依赖于以下因素：

• mysql的版本
• 服务器在开发时候的配置
• 启动选项

查看当前可用的存储引擎，使用show engines语句：

show engines \G

常用的存储引擎

MyISAM：

1. MyISAM存储引擎是MySQL最常用的引擎
2. 使用三个文件来表示每个表
   • 格式文件：（mytable.frm）存储表结构的定义
   • 数据文件：（mytable.MYD）存储表的行内容
   • 索引文件：（mytable.MYI）存储表上的索引
3. 对于一张表来说，只要是主键，或者加有unique约束的字段上都会自动创建索引
4. 优势：可用被转换为压缩或只读表来节省存储空间
5. 不支持事务

InnoDB：

1. 这是MySQL默认的存储引擎，同时也是一个重量级的引擎
2. 支持事务，支持数据库崩溃后的自动恢复机制
3. 最主要的特点就是：非常安全
4. 它管理的表具有以下特征
   • 每个InnoDB表在数据库目录中以.fmt格式文件表示
   • InnoDB表空间tablespace中用于存储表的内容（表空间是一个逻辑名称，表空间中存储表数据和索引）
   • 提供一组用来记录事务性活动的日志文件
   • 用commit，savepoint和rollback支持事务处理
   • 提供全ACID兼容
   • 在MySQL服务器崩溃后自动恢复
   • 多版本（MVCC）和行级锁定
   • 支持外键及引用的完整性，包括级联删除和更新
5. InnoDB最大的特点就是支持事务，以保证数据的安全。因此它的效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间

MEMORY：

1. 数据存储在内存中，且行的长度固定，这使得MEMORY引擎非常快
2. MEMORY存储引擎管理的表具有以下特征：
   • 在数据库目录中，每个表均以.frm格式的文件表示
   • 表数据及索引被存储在内存当中
   • 表级的锁机制
   • 不能包含text和blob字段
3. MEMORY存储引擎以前被称为HEAP引擎
4. 优点：查询效率最高，不需要和硬盘交互
5. 缺点：不安全，断电后数据消失

引擎的选择：

• MyISAM表最适合存储大量的数据读，少量数据更新的混合操作，另一种适用情形是使用压缩的只读表
• InnoDB引擎适合较多的数据更新操作，其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制
• MEMORY引擎存储非永久需要的数据，或者是能够从基于硬盘的表中重新生成的数据

14. 事务

事务的特性：ACID（原子性，一致性，隔离性，持久性）

与事务有关的语句是DML语句（insert、delete、update），其他语句与事务无关。

批量的DML语句同时成功或者同时失败。

实现原理：InnoDB存储引擎提供一组用来记录事务性活动的日志文件

• 提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中
  • 提交事务标志着事务的全部成功的结束
• 回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
  • 提交事务标志着事务的全部失败的结束
  • 回滚事务只能回滚到上一次事务的提交点

事务举例：
start transaction;

...一系列操作

提交事务：commit;
回滚事务：rollback;

• MySQL当中默认支持自动事务提交（每执行一条DML语句，就提交一次）
• 如果不用自动提交的话，就需要先执行start transaction开启事务

事务的隔离性

事务有不同的隔离级别，一共有4个级别

• 读未提交（read uncommitted）-最低级别
  • 提交事务之前就能读取到
  • 事务A可以读取到事务B未提交的数据
  • 存在问题：脏读
• 读已提交（read committed）
  • 提交事务之后才能读取到
  • 事务A只能读取到事务B提交之后的数据
  • 存在问题：不可重复读取数据（事务A每次读取的数据可能是不一致的）
  • 每次读取到的数据都是真实的
• 可重复读（repeatable read）
  • 提交事务之后也读不到，永远读取的都是刚开启事务时的数据
  • 其他事务不会改变这个事务读取到的数据
  • 事务A开启之后，不管是多久，每一次事务A中读取到的数据都是一致的
  • 存在问题：幻读
  • 这是mysql中默认的隔离级别
• 序列化（serializable）-最高级别
  • 事务排队，不能并发

查看目前的事务隔离级别
查看当前会话的隔离级别：(8.0后的表达)
select @@transaction_isolation;
查看全局隔离级别：
select @@session.transaction_isolation;

通过命令设置隔离级别：
set transaction isolation level 隔离等级

15. 索引

索引概述：

索引是在数据库表的字段上添加的，每个字段上都可以添加索引，添加索引可以提高查询效率。

mysql查询的两种方式：

1. 全表查询
2. 根据索引值来查询

如果在这个字段上有索引，那么mysql就会根据索引值来进行查询，否则进行全表扫描

MyISAM和InnoDB使用B+树作为索引结构（主键，unqiue都会默认添加索引）

• 在任何数据库中，主键上都会自动添加索引对象；在mysql中，一个字段上如果有unique约束的话，也会自动添加索引对象

• 遵循左小右大原则存放，采用中序遍历

索引实现原理

1. 在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。（前面引擎提到过索引的存放）
2. 不管存储引擎在哪里，索引在mysql当中都是以一个树的形式存在的（B+树）

mysql中为什么不使用B树而是用B+树？

B 树与 B+ 树的最大区别就是，B 树可以在非叶结点中存储数据，但是 B+ 树的所有数据其实都存储在叶子节点中由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量的随机 I/O，也是 B 树最大的性能问题。

B+ 树中就不存在这个问题了，因为所有的数据行都存储在叶节点中，而这些叶节点可以通过『指针』依次按顺序连接，当我们在如下所示的 B+ 树遍历数据时可以直接在多个子节点之间进行跳转，这样能够节省大量的磁盘 I/O 时间，也不需要在不同层级的节点之间对数据进行拼接和排序；通过一个 B+ 树最左侧的叶子节点，我们可以像链表一样遍历整个树中的全部数据，我们也可以引入双向链表保证倒序遍历时的性能

在什么条件下考虑给字段添加索引：

1. 数据量庞大（到底多少数据量算庞大需要测试，因为硬件环境不同）
2. 该字段经常出现在where后面，以条件形式存在
3. 该字段很少的DML操作（因为DML之后，索引需要重新排序）
4. 建议不要随便添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能，建议通过主键查询，建议通过unique约束的字段进行查询，效率是较高的

索引使用的语法

创建索引
create index 索引对象名 on 表名(字段名); 

删除索引
drop index 索引对象名 on 表名;

查看sql语句是否使用了索引进行查询
explain sql语句;

索引失效：

1. 模糊查询中，如果以%开头，不会走索引
   • 对应优化模糊查询
2. 使用or的时候会失效。如果使用or那么要求or两边的条件字段都要求有索引，才能走索引
   • 对应优化尽量少用 or
3. 使用复合索引的时候，没有使用左侧的列（一级索引）查找，索引失效
4. 在where当中，索引列参加了运算，索引失效
5. 在where当中，索引列使用了函数

16. 视图

视图：view，在不同的角度看待同一份数据

创建视图
create view 视图名称 as select语句

删除视图
drop view 视图名称;

视图的作用：我们可以面向视图对象进行增删改查，对视图对象的增删改查会导致原表被操作

17.数据的备份 导出和导入

数据导出

在命令行窗口中（不是登录到mysql）
mysql dump 数据库名称 > 导出路径 -uroot -p123123
导出指定的表
mysql dump 数据库名称 数据表名称 > 导出路径 -uroot -p123123

数据导出

先登录到mysql
source语句

18. 数据库设计三范式

1. 要求任何一张表必须有主键，每个字段原子性不可再分

2. 建立在第一范式的基础上，要求所有的非主键字段完全依赖主键，不要产生部分依赖

   • 一般不要复合主键

   • 多对多，使用三张表，关系表为两个外键

3. 建立在第二范式的基础上，要求所有的非主键字段直接依赖主键，不要产生传递依赖

   • 一对多，拆分成两张表，多的表加外键

设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

一张表太大可能会进行拆分，拆分之后添加外键，外键上添加unique约束（一对一，外键唯一）

数据库设计三范式是理论上的，实践和理论有的时候是有偏差的，最终的目的都是为了满足客户的需求。

有的时候会拿冗余换执行速度，因为在表和表之间连接次数较多，效率会越低。有的时候可能存在冗余，但是为了减少表的连接次数，这样做也是合理的。并且对于开发人员来说，sql语句的编写难度也会降低。