MysqL

最新推荐文章于 2024-04-07 11:17:57 发布

爱吃零食の羔羊

最新推荐文章于 2024-04-07 11:17:57 发布

阅读量90

点赞数

文章标签： mysql 运维

本文链接：https://blog.csdn.net/qq_55262911/article/details/135094080

版权

一、数据库简介

1：定义

数据库就是一个存放计算机数据的仓库，这个仓库是按照一定的数据结构（数据结构是指数据的组织形式或数据之间的联系）来对数据进行组织和存储的，可以通过数据库提供的多种方法来管理其中的数据。

2：数据库分类

最常用的数据库模式主要有两种，即关系型数据库和非关系型数据库。

3：生产环境常用数据库

生产环境主流的关系型数据库有 Oracle、Microsoft SQL Server、MySQL/MariaDB 等。

生产环境主流的关系型数据库有 MongoDB—文档数据库 Memcached Redis—键值数据库

4、关系型数据库

最大是实例（就是一个MySQL服务，启动一个服务就是一个实例）：一台数据库服务器是一个（默认端口为3306），或多个实例（也就是多个端口，如3306,3307），进入到库——表，一个库可以有一张表，也可以有多张表，——行，列

进入实例（登录数据库中）库，一个实例可以有一个库，也可以有多个库

关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。

5、非关系型数据库——当前比较火

①定义

非关系型数据库也被称为 NoSQL 数据库，NoSQL 的本意是 “Not Only SQL” ，

大部分非关系型数据库是内存数据库，磁盘只作为数据库持久化容器

数据操作原理：将磁盘内数据库全部加载到内存，所有操作都是在内存中进行的，内存关闭时，又将数据全部放入磁盘中

②种类

1、键值（Key-Value）存储数据库

2、列存储（Column-Oriented）数据库

3、面向文档（Document-Oriented）的数据库

4、图形（Graph）数据库

6、常用关系型数据库管理系统

1、Oracle 数据库

2、MySQL 数据库

3、MariaDB 数据库==mysql

4、SQL Server 数据库

7、常用非关系型数据库管理系统

1、Memcached（Key-Value）

2、Redis（Key-Value）

3、MongoDB （Document-Web）

二、安装 MySQL 5.7 数据库

1：linux安装软件介绍

rpm ：就是一个封装好的二进制，rpm，yam ，dnf，启动服务—定制化操作—性能损耗，安全隐患

二进制 ；就是编译好的压缩包，tar xf ，添加配置（服务配置、systemctl配置，将自己编写并将其放入），启动服务

写一个.service文件，将其放入 /usr/lib/systemd/system目录下，并systemctl dameo-reload

编译：下载源码，实际开发的代码，编译，将代码转成为二进制，变成二进制安装——可自定义安装功能

2：MySQL数据库的重要配置文件

/etc/my.conf 是MySQL的配置文件

/var/lib/mysql/mysql: 存放mysql的软件

/var/run/mysqld/mysqld.pid ；路径

3：安装MySQL数据库操作

①安装MySQL Repository

<span style="background-color:#f8f8f8"><span style="color:#333333"> <span style="color:#aa5500">#1、默认 yum 存储库安装</span>
 [root@qfedu.com ~]<span style="color:#aa5500"># yum -y install wget     # 安装 wget下载工具</span>
 [root@qfedu.com ~]<span style="color:#aa5500"># wget https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm  # 下载 mysql 官方 yum 源安装包</span>
 [root@qfedu.com ~]<span style="color:#aa5500"># yum -y localinstall mysql57-community-release-el7-11.noarch.rpm  # 安装 mysql 官方 yum 源</span>
 [root@qfedu.com ~]<span style="color:#aa5500"># rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql</span></span></span>

<span style="background-color:#f8f8f8"><span style="color:#333333"> <span style="color:#aa5500">#2、安装 yum 配置工具</span>
 [root@qfedu.com ~]<span style="color:#aa5500"># yum -y install yum-utils</span>
 <span style="color:#116644">3</span>、禁用 <span style="color:#116644">8</span>.0 版本
 [root@qfedu.com ~]<span style="color:#aa5500"># yum-config-manager --disable mysql80-community</span>
 <span style="color:#116644">4</span>、启用 <span style="color:#116644">5</span>.7 版本
 [root@qfedu.com ~]<span style="color:#aa5500"># yum-config-manager --enable mysql57-community</span>
 <span style="color:#116644">5</span>、遇到无法下载mysql-community-server mysql 包
 /etc/yum.repos.d/mysql-community.repo    <span style="color:#aa5500">#将5.7版本的SQL，check设为0</span>
  /etc/yum.repos.d/mysql-community-source.repo
 <span style="color:#116644">6</span>、下载服务端、客户端
 [root@qfedu.com ~]<span style="color:#aa5500"># yum -y install mysql-community-server mysql </span>
 <span style="color:#116644">7</span>、<span style="color:#aa5500"># 启动 mysql 服务</span>
 [root@qfedu.com ~]<span style="color:#aa5500"># systemctl start mysqld      </span>
 <span style="color:#116644">8</span>、 <span style="color:#aa5500"># 设置 mysql 服务开机启动</span>
 [root@qfedu.com ~]<span style="color:#aa5500"># systemctl enable mysqld </span>
 <span style="color:#116644">9</span>、重启 MySQL
 <span style="color:#116644">10</span>、安装完毕！！！！！！！！！！！！！</span></span>

4：修改SQL初次登录密码

<span style="background-color:#f8f8f8"><span style="color:#333333"> [root@qfedu.com ~]<span style="color:#aa5500"># grep 'temporary password' /var/log/mysqld.log    # 获取首次登录密码</span>
 [root@qfedu.com ~]<span style="color:#aa5500"># mysql -uroot -p'awm3>!QFl6zR'                    # 登录mysql数据库</span>
 mysql > alter user <span style="color:#aa1111">'root'</span>@<span style="color:#aa1111">'localhost'</span> identified by <span style="color:#aa1111">'Qf.123com'</span>;     <span style="color:#aa5500"># 修改 mysql </span>
 [root@qfedu.com ~]<span style="color:#aa5500"># mysql -uroot -p'Qf.123com'                       # 用新密码登录数据库</span></span></span>

5：MySQL系统库介绍

①mysql库

MySQL服务的核心数据库，主要存储了数据库用户、权限等信息，慢日志或者通用日志调整成table形式也存在这个库里。

user表：用户全局权限表，记录了每个用户权限

db表：库级别权限表（资源权限）

select * from mysql.db where user='root' \G #查询root用户库级别的权限

tables_priv：表级权限
columns_priv：列级别全新表
procs_priv：存储过程与函数权限
proxies_priv：代理用户的权限
event：事件与任务调度表
gtid：与GTID主从复制有关的表
innodb_index_stats：innodb索引统计信息
innodb_table_stats：innodb表统计信息
plugin：插件表
mysql默认的库 mysql默认的几个库

②performance_schema库

用于收集数据库服务器性能数据，以便分析问题。比如哪个SQL执行次数最多、耗时最长、哪个SQL被锁等有用的信息

file_instances：用于监控文件IO操作的性能统计数据。
events：用于监控MySQL执行过程中的各种事件，如SQL分析、SQL执行、锁等等。
sessions：用于监控当前MySQL系统内所有活跃的会话信息。

③sys库

sys库的所有数据都是来自performancce_schema，这些视图简化了对performancce_schema的查询，便于更快速了解数据库运行情况。

④information_schema库

这个库也被称为MySQL的数据字典，存放了所有的元数据信息，比如所有的数据库、表、索引，甚至每个会话信息也在该库中记录。

TABLES表：保存了所有表的数据字典信息，比如表名、表引擎、表大小、表行数等等有用信息

PROCESSLIST表：记录了会话详细信息，执行show processlist命令其实就是在查询该表

INNODB_TRX表：记录了所有事务，包括事务是否被锁

INNODB_LOCK_WAITS：记录了事务正在等待的锁的信息

三：结构化查询语言SQL

①、登录数据库

<span style="background-color:#f8f8f8"><span style="color:#333333"> mysql <span style="color:#0000cc">-u</span> root <span style="color:#0000cc">-p</span><span style="color:#aa1111">'密码'</span> <span style="color:#aa5500">#登录本地SQL数据库</span></span></span>

②、查看资源

<span style="background-color:#f8f8f8"><span style="color:#333333"> show databases;                                 <span style="color:#aa5500"># 查看所有数据库：</span>
 show tables;                                    <span style="color:#aa5500"># 查看所有表：    </span>
 drop database db1;                              <span style="color:#aa5500"># 删除数据库   </span>
 use database;                                   <span style="color:#aa5500"># 选择数据库</span>
 desc t1                                         <span style="color:#aa5500"># 查看表结构 </span></span></span>

③、创建数据库、表

<span style="background-color:#f8f8f8"><span style="color:#333333"> create database db1 default character <span style="color:#770088">set</span> utf8; <span style="color:#aa5500"># 创建数据库               </span>
 create table t1(id int(3),name varchar(20));    <span style="color:#aa5500"># 创建表    </span></span></span>

④、数据操作

<span style="background-color:#f8f8f8"><span style="color:#333333"> select * from t1  ;                             <span style="color:#aa5500">#查询表内数据</span>
 insert into t1 values(1,<span style="color:#aa1111">'user1'</span>);               <span style="color:#aa5500"># 插入数据   </span>
 update ti <span style="color:#770088">set</span> <span style="color:#0000ff">id</span><span style="color:#981a1a">=</span><span style="color:#116644">3433</span>  where <span style="color:#0000ff">id</span><span style="color:#981a1a">=</span><span style="color:#116644">1</span>               <span style="color:#aa5500">#修改数据</span>
 delete from t1 where <span style="color:#0000ff">id</span><span style="color:#981a1a">=</span> <span style="color:#116644">33</span>;                    <span style="color:#aa5500">#删除指定数据</span></span></span>

⑤、修改表结构

<span style="background-color:#f8f8f8"><span style="color:#333333"> 
 alter table t1 add(age int(3));                 <span style="color:#aa5500"># 添加表字段语句   </span>
 alter table t1 drop id;                         <span style="color:#aa5500"># 删除表字段语句   </span>
 alter table t1 modify age varchar(2);           <span style="color:#aa5500"># 修改表字段类型格式   </span>
 alter table t1 change age p1age int(3);         <span style="color:#aa5500"># 修改表字段名称   </span>
 alter table t1 rename per;                      <span style="color:#aa5500"># 修改表名        </span>
 truncate table t1;                              <span style="color:#aa5500"># 清空表结构   </span>
 drop table t1;                                  <span style="color:#aa5500"># 删除表结构   </span></span></span>

⑥、运算符

0： where: 限制查询条件

1：关系运算符: >，>=，<，<=, 等于：=，不等于：!= 或 <>

2：null值操作：比较null时，不能使用=或者!= 或者<>，而是使用 is或者is not，在select子句中，使用关系运算符

3:in | not in (集合元素，使用逗号分开); 注意：同一个字段有多个值的情况下使用。 in 相当于 or， not in 相当于 and

<span style="background-color:#f8f8f8"><span style="color:#333333">select qfeduid,name,job,sal from qfedu where deptid in(10,20);
select * from qfedu where deptid not in (10,20);</span></span>

4:all | any 与集合连用，此时集合中的元素不能是固定的必须是从表中查询到的数据。

<span style="background-color:#f8f8f8"><span style="color:#333333"># 查询员工qfedu,blake,clark三个人的工资
select * from qfedu where sal>all(1200,800,4230); 
#all大于里面的所有的值，
#any 从中的任意一个</span></span>

5:范围查询：colName between val1 and val2; 查询指定列名下的val1到val2范围中的数据

<span style="background-color:#f8f8f8"><span style="color:#333333">select * from qfedu where sal between 1500 and 2500; 
#between 700 and  900 :        700<n<900 ——。。。。之间
#not between 700 and 900        700>n and n>2500—— ...不在其之间</span></span>

6:模糊查询：like ——与通配符进行使用

7:通配符：% 表示0或多个以上字符。_ 表示匹配一个字符

<span style="background-color:#f8f8f8"><span style="color:#333333">select name,job,sal,comm,deptid from qfedu where name like '%a%s%' or name like '%s%a%';</span></span>

8： and 、or、

⑦排序查询 Order By子句——默认为升序排序

排序规则: ASC：升序（由小到大），DESC: 降序（由大到小），默认是升序排序。

<span style="background-color:#f8f8f8"><span style="color:#333333">select * from qfedu order by sal DESC;  </span></span>

⑧Distinct 去重——对列名进行去重

位置：必须写在select关键字后。

<span style="background-color:#f8f8f8"><span style="color:#333333">select distinct name from qfedu;</span></span>

⑨分组查询 Group By 子句——以某个列值进行分组

分组，即将相同的列值作为一组，

<span style="background-color:#f8f8f8"><span style="color:#333333">select deptid,avg(sal) as av from qfedu group by deptid;</span></span>

⑩聚合函数——自动忽略null值

count(Filed) 统计指定字段的记录数。
sum(Filed) 统计指定字段的和。
avg(Filed) 统计指定字段的平均值
max(Filed) 返回指定字段中的最大值。
min(Filed) 返回指定字段中的最小值。

十一：分组查询添加条件 Having 子句——即针对分组后的数据再进行筛选

<span style="background-color:#f8f8f8"><span style="color:#333333">#以deptid列值为分组，再对分组的不同组进行筛选，最后，再select查询某列名的值
select deptid,avg(sal) as av from qfedu group by deptid having avg(sal)>2000;</span></span>

十二：混合使用

<span style="background-color:#f8f8f8"><span style="color:#333333">select..from..[where..][group by..][having..][order by..]</span></span>

执行顺序 ：先执行from子句，再执行where子句，然后group by子句，再次having子句，之后select子句，最后order by子句

十三：高级关联查询——即多表查询

多表查询，要使用到别名，等值查询，内查询，外查询等等

<span style="background-color:#f8f8f8"><span style="color:#333333"># 查询表中各部门人员中大于部门平均工资的人
select name,sal,a.deptid ,b.av
from qfedu a,
(select deptid,avg(ifnull(sal,0)) as av from qfedu group by deptid) b
where a.deptid=b.deptid and a.sal>b.av
order by deptid ASC;</span></span>

十四：多表操作

概念：当在查询时，所需要的数据不在一张表中，可能在两张表或多张表中。此时需要同时操作这些表。即关联查询。

等值连接 ：在做多张表查询时，这些表中应该存在着有关联的两个字段。使用某一张表中的一条记录与另外一张表通过相关联的两个字段进行匹配，组合成一条记录。

笛卡尔积：在做多张表查询时，使用某一张表中的每一条记录都与另外一张表的所有记录进行组合。比如表A有x条，表B有y条，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。

内连接 ：只要使用了join on。就是内连接。查询效果与等值连接一样。
外连接：在做多张表查询时，所需要的数据，除了满足关联条件的数据外，还有不满足关联条件的数据。此时需要使用外连接。

外连接分为三种

驱动表(主表)：除了显示满足条件的数据，还需要显示不满足条件的数据的表
从表(副表)：只显示满足关联条件的数据的表

左外连接：表A left [outer] join 表B on 关联条件，表A是驱动表，表B是从表——即将左边的表内关联的值，匹配右表，有，则生成一条记录，无责，也生成一条记录，但右表字段值为null 值
右外连接：表A right [outer] join 表B on 关联条件，表B是驱动表，表A是从表
全外连接：表A full [outer] join 表B on 关联条件，两张表的数据不管满不满足条件，都做显示。
自连接：在多张表进行关联查询时，这些表的表名是同一个。即自连接。

左连接例子

四、事务控制语言(TCL)

成功则写入数据库

失败则不写入数据库

事物控制语言（Transation Control Language）有时可能需要使用 DML 进行批量数据的删除，修改，增加。对于事务，要保证事务的完整性。要么成功，要么撤回。

事务要符合四个条件(ACID)：

原子性(Atomicity)：事务要么成功，要么撤回。不可切割性。 ，操作失败，则回滚（返回原有的状态）
一致性(Consistency)：事务开始前和结束后，要保证数据的一致性 。转账前账号A和账号B的钱的总数为10000，转账后账号A和账号B的前的总数应该还是10000;
隔离性(Isolation) ：当涉及到多用户操作同一张表时，数据库会为每一个用户开启一个事务。那么当其中一个事务正在进行时，其他事务应该处于等待状态。保证事务之间不会受影响。
持久性(Durability)： 当一个事务被提交后，我们要保证数据库里的数据是永久改变的。即使数据库崩溃了，我们也要保证事务的完整性。

commit 提交
rollback 撤回，回滚。——执行失败
savepoint 保存点——数据的保护点

只有DML（增删改）操作会触发一个事务。存储引擎(ENGINE):就是指表类型.当存储引擎为innodb时，才支持事务。默认的存储引擎为 Myisam。不支持事务。

事务的验证/数据库操作原理：

第一步：start transaction (交易) 第二步：savepoint 保存点名称。第三步：DML 第四步：commit/rollback;

五、数据控制语言(DCL)

数据控制语言 Data Control Language,作用是用来创建用户，给用户授权，撤销权限，删除用户。

1、创建用户

<span style="background-color:#f8f8f8"><span style="color:#333333">create user username@ip identified by newPwd;
ip: 可localhost，即只能localhost（127.0.0.1）本地登录， 192.168.65.100，即指允许该台pc登录
                           192.168.96.0   即允许该网段pc登录      % 所有pc都可登录
 newPed: 密码复杂度需满足，，，，，，，默认为8位，（字母大小写，数字，特殊符号）
create user 'qfedu'@'192.168.152.166' identified by 'Qfedu.123com';</span></span>

2、显示用户的权限

<span style="background-color:#f8f8f8"><span style="color:#333333">show grants for username@ip;</span></span>

3、授权数据

<span style="background-color:#f8f8f8"><span style="color:#333333">grant 权限1，权限2... on 数据库名.* to username@ip;
grant select,drop,insert on qfedu.* to 'qfedu'@'192.168.152.166';
                             # qfdue为数据库  .* :数据库内所有的表
grant select,drop,insert on *.* to 'qfedu'@'%' identified by 'LIlin123..';  #授权同时添加用户
GRANT ALL PRIVILEGES ON wor.* TO 'july01'@'%' IDENTIFIED BY 'JUly123..' WITH GRANT OPTION;</span></span>

4、撤销权限

<span style="background-color:#f8f8f8"><span style="color:#333333">revoke 权限1，权限2..on 数据库名.* from username@ip;
revoke drop on qfedu.* from 'qfedu'@'192.168.152.166';</span></span>

5、删除用户

<span style="background-color:#f8f8f8"><span style="color:#333333">drop user username;  默认为%
drop user qfedu@'192.168.152.166';  </span></span>

6、使权限立即生效

<span style="background-color:#f8f8f8"><span style="color:#333333">flush privileges;，需要在特定条件下才生效规则全打破</span></span>

7、远程控制登录

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql - uroot -h ip地址 -p'密码'</span></span>

六：数据导入导出

1、数据导出方式

<span style="background-color:#f8f8f8"><span style="color:#333333">mysqldump -h IP -u 用户名 -p -d 数据库名 > 导出的文件名</span></span>

1、数据库的导出

1、导出所有数据

<span style="background-color:#f8f8f8"><span style="color:#333333">[root@qfedu.com ~]# mysqldump -uroot -pQfedu.123com --all-databases > /data/mysql.sql</span></span>

2、导出数据库

<span style="background-color:#f8f8f8"><span style="color:#333333">[root@qfedu.com ~]# mysqldump -uroot -pQfedu.123com --databases qfedu > /data/mysql1.sql</span></span>

3、导出某张表

<span style="background-color:#f8f8f8"><span style="color:#333333">[root@qfedu.com ~]# mysqldump -uroot -pQfedu.123com mysql user >/data/mysql.sql</span></span>

七：MySQL数据类型和表约束

1、MySQL 数据类型

整数类型：BIT、BOOL、TINYINT、SMALLINT、MEDIUMINT、 INT 、 BIGINT
浮点数类型：FLOAT 、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
日期类型：Date 、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

2、MySQL 定义列数据类型

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> create table 表名(
    字段名  列类型 unsigned [可选的参数],  # 记住加逗号,unsigned,无符号类型，只有正无负——则访问只能为整值。默认有符号
    #可选的参数为，可显示参数，不够，则0补充？？？？
    字段名  列类型 [可选的参数],  # 记住加逗号
    字段名  列类型 [可选的参数]   # 最后一行不加逗号
)charset=utf8;                # 后面加分号</span></span>

（1）、整型——int

在 MySQL 中，int 数据类型是主要的整数数据类型。

int(n)里的n是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，只影响到显示的宽度

N 表示的是显示宽度，不足的用 0 补足，超过的无视长度而直接显示整个数字，但要整型设置了unsigned zerofill才有效

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> create table test_int_width (
    a int(5),
    b int(5) unsigned,
    c int(5) unsigned zerofill,  #宽度为5，不足的，在前面补充0
    d int(8) unsigned zerofill
) engine=innodb charset=utf8;  默认latin1 编码</span></span>

（2）、浮点型——float、double

小数点四舍五入，指定宽度时，输出数据，先满足指定宽度小数位数，+整数位，不能超过总位数，超过则报错。

总位数不超过8位的，使用float

MySQL 浮点型数据类型	字节数	含义
float(m,d)	4	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	8	双精度浮点型 16位精度(8字节) m总个数，d小数位

（3）、定点数——decimal

浮点型在数据库中存放的是近似值 ，而定点类型在数据库中存放的是精确值。

而decimal则是以字符串形式进行保存的

而decimal在不指定M、D时默认为decimal(10, 0)

（4）、字符串 ——char、varchar、text

字符类型，是显示的是个数

空格是个字符

varchar可指定n，text不能指定

varchar表示可变长字符串，长度是可变的；插入的数据是多长，就按照多长来存储；

每行长度不能超过64k, 英文、特殊字符，一个字符占一位，中文的长度，gbk，默认一个汉字2字节

MySQL单行最大数据量为64K,当varchar(M)的M大于某些数值时，varchar会自动转为text：

MySQL数据类型	含义
char(m)	固定长度，最多255个字符
varchar(m)	可变长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

char表示定长字符串，长度是固定的；如果插入数据的长度小于char的固定长度时，则用空格填充；因为长度固定—char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。

（5）、字符编码

lantin1: MySQL服务器（5.7）的默认字符编码——latin1 一个英文、特殊字符占用1个字节，一个中文字符占用2个字节。

utf8，一个汉字占3字节,一个英文占1字节

,gbk编码，一个汉字占两个字节,一个英文占1字节

unicode 一个英文占2个字节，一个中文，占2个1字节

utf8mb4，一个英文字符通常占用1个字节，一个中文字符通常占用3个字节

gbk， Windows系统编码， utf-8，最普遍的编码

所有英文无论其编码方式，都占用1个字节，但对于gbk编码，一个汉字占两个字节，因此最大 *n=65532/2=32766；对于utf8编码，一个汉字占3个字节，因此最大M=65532/3=21844 ，**

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> create table t9 (
    id int auto_increment primary key,
    gender enum('male','female')  #选择范围固定
)charset utf8;</span></span>

（6）、二进制数据（Blob）——存照片、视频

BLOB 和 text 存储方式不同，TEXT 以文本方式存储，英文存储区分大小写，而 Blob 是以二进制方式存储，不分大小写。

BLOB 存储的数据只能整体读出。

blob是用于存储例如图片、音视频这种文件的二进制数据的

TEXT可以指定字符集，Blob 不用指定字符集。

（7）、日期时间类型

数据类型	字节数	格式	备注
date	3	yyyy-MM-dd	存储日期值
time	3	HH:mm:ss	存储时分秒
year	1	yyyy	存储年
datetime	8	yyyy-MM-dd HH:mm:ss	存储日期+时间——到9999年
timestamp	4	yyyy-MM-dd HH:mm:ss	存储日期+时间，可作时间戳，——局限性，只能到2038年

（8）、枚举（enum）——限制选择范围

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> create table t9 (
    id int auto_increment primary key,
    gender enum('male','female')  #选择范围固定，即输入该列值的数据必须要是这两个中的任意一个，不能是其他自定义值。
)charset utf8;</span></span>

（9）、MySQL 数据类型使用建议——存储引擎

在指定数据类型的时候一般是采用从小原则， 比如能用TINYINT的最好就不用INT，能用FLOAT类型的就不用DOUBLE类型，这样会对MYSQL在运行效率上提高很大，尤其是大数据量测试条件下。

（10）、MySQL 选择数据类型的基本原则

（1）MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。

（2）MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。

（3）InnoDB 存储引擎和数据列：建议使用 VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列。

3、MySQL 数据类型约束

约束是一种限制，它通过对表的行或者列的数据做出限制，来确保表数据的完整性和唯一性

不指定值时，默认为null

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

①非空约束——【不能是默认值（null）】

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> create table testnull(id int, username varchar(20) not null);    # 创建 testnull 设置 username 字段为非空约束 notnull</span></span>

②唯一约束——【列值唯一】

字段添加唯一约束之后，该字段的值不能重复，也就是说在一列当中不能出现一样的值。

<span style="background-color:#f8f8f8"><span style="color:#333333">create table testnull(id int, username varchar(20) unique key); #创建表时添加
ALTER TABLE tbl_name ADD unique(id);  # 添加唯一约束
alter table tbl_name drop key index_name #删除唯一约束
alter table tbl_name drop index index_name  #删除索引</span></span>

③主键约束——primary ===not null 且 unique

特点:

主键保证记录的唯一性，主键自动为NOT NULL

每张数据表只能存在一个主键

一个UNIQUE KEY 又是一个NOT NULL的时候，那么它被当做PRIMARY KEY主键

当一张表里没有一个主键的时候，第一个出现的非空且为唯一的列被视为有主键。

<span style="background-color:#f8f8f8"><span style="color:#333333">create table tb_name(id int primary key);#创建表的同时添加主键
alter table tb_name add primary key(id) #为某个字段名，添加主键
alter table tb_name Drop prinary key #删除主键</span></span>

④自增长——错误插入，也算值

自增长 AUTO_INCREMENT 自动编号，且必须与主键组合使用 默认情况下，起始值为1，每次的增量为1。

当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况：

如果插入的值与已有的编号重复，则会出现出错信息，编号冲。
下一个编号从这个新值开始递增。

<span style="background-color:#f8f8f8"><span style="color:#333333">ALTER TABLE user CHANGE id id INT NOT NULL AUTO_INCREMENT; #增加自增长
 ALTER TABLE user CHANGE id  id INT NOT NULL;  #删除自增长</span></span>

⑤默认约束——default

<span style="background-color:#f8f8f8"><span style="color:#333333">ALTER TABLE user ALTER number SET DEFAULT 0; #添加默认值</span></span>

⑥外键约束—受到外表内容限制

外键约束要求数据表的存储引擎只能为 InnoDB，默认值也是该引擎，可使用show engines查看引擎

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> SHOW ENGINES;  #查看当前mysql服务器支持的存储引擎</span></span>

4、MySQL索引

（1）分类

索引作为一种数据结构，其用途是用于提升检索数据的效率。

普通索引（INDEX）：索引列值可重复
唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL,即必须要有唯一限制。
主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引
全文索引（FULL TEXT）：给每个字段创建索引

（2）用途

普通索引常用于过滤数据。例如，以商品种类作为索引，检索种类为“手机”的商品。
唯一索引主要用于标识一列数据不允许重复的特性，相比主键索引不常用于检索的场景。
主键索引是行的唯一标识，因而其主要用途是检索特定数据。
全文索引效率低，常用于文本中内容的检索。

（3）使用

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> CREATE TABLE student1 ( #创建普通索引
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    birthday DATE,
    sex CHAR(1) NOT NULL,
    INDEX nameIndex (name(50))
); 
mysql> CREATE TABLE student4 ( #创建唯一索引（UNIQUE）
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    birthday DATE,
    sex CHAR(1) NOT NULL,
    UNIQUE idIndex (id)
);
mysql> CREATE TABLE student6 (  #创建主键索引（PRIMARY KEY）——   一表一个
    id INT,
    name VARCHAR(100) NOT NULL,
    birthday DATE,
    sex CHAR(1) NOT NULL,
    PRIMARY KEY (id)
);
</span></span>

（4）删除索引

1、普通索引（INDEX）

<span style="background-color:#f8f8f8"><span style="color:#333333"># 直接删除
mysql> DROP INDEX nameIndex ON student;

# 修改表结构删除
mysql> ALTER TABLE student DROP INDEX nameIndex;</span></span>

2、唯一索引（UNIQUE）

<span style="background-color:#f8f8f8"><span style="color:#333333"># 直接删除
mysql> DROP INDEX idIndex ON student;

# 修改表结构删除
mysql> ALTER TABLE student DROP INDEX idIndex;</span></span>

3、主键索引（PRIMARY KEY）

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> ALTER TABLE student DROP PRIMARY KEY;</span></span>

主键不能采用直接删除的方式删除。

3、查看索引

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> SHOW INDEX FROM student;</span></span>

4、选择索引的原则

创建索引的时候，会遍历访问数据库，很耗性能，避免数据库繁忙，尽量晚上10点后进行，

更新过于频繁（更新频率远高于检索频率）的字段不适合作为索引

八：MySQL权限控制及日志管理

（一）、MySQL 的用户管理和权限管理

1、DCL（Data Control Language 数据库控制语言）

数据库授权、角色控制等操作
GRANT 用户授权，为用户赋予访问权限
REVOKE 取消授权，撤回授权权限

2、MySQL 权限表

①mysql.user——针对用户，存放用户授权信息

用户字段：Host、User、Password——授权必须要
权限字段：_Priv结尾的字段
安全字段：ssl x509字段
资源控制字段：max_开头的字段

②mysql.db——针对资源

用户字段：Host、User、Password
权限字段：剩下的_Priv结尾的字段

③mysql.tables_priv，mysql.columns_priv、procs_priv

表、列、存储过程的授权表

④授权级别排列

mysql.user #全局授权，对数据库实例的授权（登录授权，如，root@192.168.2.156登录实例）
mysql.db #数据库级别授权，针对于资源（某个库/表进行增、删、改、查进行授权）
其他 #表级，列级授权

④数据库和表格式

<span style="background-color:#f8f8f8"><span style="color:#333333">数据库名.*            数据库中的所有表、存储过程...
数据库名.表名         指定数据库中的某张表
数据库名.存储过程      指定数据库中的存储过程
*.*                 所有数据库
授权权限</span></span>

⑤用户和 IP格式

<span style="background-color:#f8f8f8"><span style="color:#333333">用户名@IP地址         用户只能在改IP下才能访问
用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
用户名@%.qfedu.com    %代表任意字符             需满足条件才可域名为指定主机，1：hosts写了具体解析，2：dns支持主机名解析 【即可以ping通】
域名授权好处？
1：方便以后的变更，即便于修改IP地址——建议使用域名授权，而研发同学数据库要有重连机制。防止主从切换、网络问题
用户名@%             用户可以再任意IP下访问(默认IP地址为%)——禁止使用</span></span>

3、MySQL 用户与权限管理

①语法：

<span style="background-color:#f8f8f8"><span style="color:#333333">CREATE USER '用户名'@'IP地址' [ IDENTIFIED BY '密码' ]； #创建数据库用户</span></span>

<span style="background-color:#f8f8f8"><span style="color:#333333">GRANT SELECT ON *.* TO '用户名'@’IP地址’  IDENTIFIED BY "密码"；#创建用户，设密码并授权

GRANT SELECT ON *.* TO '用户名'@’IP地址’   #已有用户进行授权，不需要设置密码</span></span>

②实例：

<span style="background-color:#f8f8f8"><span style="color:#333333">CREATE USER 'qfedu'@'localhost' IDENTIFIED BY '123456';  #localhost，只能使用127.0.0.1进行登录‘#localhost 要先登录本地主机，再登录数据库</span></span>

③查询权限

<span style="background-color:#f8f8f8"><span style="color:#333333">查询root用户授权信息
select * from mysql.user where user='root'\G
查询root用户对所有库具有哪些权限
select *  from mysql.db where db='root'\G
查询表具有哪些用户的权限
select * from mysql.tables_priv\G</span></span>

④删除用户

<span style="background-color:#f8f8f8"><span style="color:#333333">DROP USER '用户名'@'IP地址';
DELETE FROM mysql.user WHERE user='用户名' AND host='IP地址'  # 创建该用户的创建逻辑</span></span>

⑤修改用户

<span style="background-color:#f8f8f8"><span style="color:#333333">ENAME USER '旧用户名'@'IP地址' TO '新用户名'@'IP地址' ;</span></span>

⑥修改密码

注意！修改玩密码后必须要刷新权限

<span style="background-color:#f8f8f8"><span style="color:#333333">FLUSH PRIVILEGES;</span></span>

root用户修改自己、他人密码

<span style="background-color:#f8f8f8"><span style="color:#333333">方法一：mysqladmin -uroot -p'旧密码' password '新密码' #只能修改root用户

方法二： alter user 'root'@'localhost'  identified by '新密码'# #注意权限，登录用户要有修改权限</span></span>

普通用户修改自己密码

<span style="background-color:#f8f8f8"><span style="color:#333333">set password for '用户'@’ip地址‘ =password('新密码')</span></span>

⑦找回密码（需要重启两次数据库）

<span style="background-color:#f8f8f8"><span style="color:#333333"> [root@qfedu.com ~]# vim /etc/my.cnf  #修改mysql配置文件
[mysqld]
···
#设置免密登录
skip-grant-tables
[root@qfedu.com ~]# systemctl restart mysqld #重启mysql服务
[root@qfedu.com ~]# mysql #直接进行mysql
mysql> use mysql;  #进入mysql的系统库，mysql
mysql> alter user 'root'@'localhost'  identified by '新密码'  #设置新的mysql密码
#或者这样配置
mysql> update user set authentication_string=password('密码') where user='root';
[root@qfedu.com ~]# vim /etc/my.cnf      #取消免密登录
[mysqld]
···
#设置免密登录
#skip-grant-tables
[root@qfedu.com ~]# systemctl restart mysqld  #重启msyql服务器
[root@qfedu.com ~]# mysql -uroot -p    #使用设置的新密码进行登录</span></span>

⑧密码复杂度——最低4位

MySQL 默认启用了密码复杂度设置，插件名字叫做 validate_password

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';</span></span>

<span style="background-color:#f8f8f8"><span style="color:#333333">[root@qfedu.com ~]#  vim /etc/my.cnf #修改配置文件
[mysqld]
plugin-load=validate_password.so   #
validate_password_policy=0       #密码复杂度策略，0，关闭
validate-password=FORCE_PLUS_PERMANENT
[root@qfedu.com ~]# systemctl restart mysqld  #重启mysql服务生效
mysql>  show variables like 'validate%';  #验证密码检查</span></span>

爱吃零食の羔羊

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MysqL

最大是实例（就是一个MySQL服务，启动一个服务就是一个实例）：一台数据库服务器是一个（默认端口为3306），或多个实例（也就是多个端口，如3306,3307），进入到库——表，一个库可以有一张表，也可以有多张表，——行，列。要么成功，要么撤回。：先执行from子句，再执行where子句，然后group by子句，再次having子句，之后select子句，最后order by子句。这个库也被称为MySQL的数据字典，存放了所有的元数据信息，比如所有的数据库、表、索引，甚至每个会话信息也在该库中记录。
复制链接

扫一扫