mysql数据库管理

数据库种类

数据库分两大类： 关系型数据库 SQL 非关系型数据库 NoSQL

1. 关系型数据库 SQL

   • 典型代表 ：MySQL MariaDB PostgreSQL(pgdb) Oracle SQL Server Db2
   • 操作命令：sql语句
   • 存储结构：二维表格
   • 存储的数据：结构化数据
   • 使用场景：存储业务数据和账户信息
   • 对象： 库 -> 表 -> 二维表格形式的结构化数据

   tips：每个二维表分为列和行 列（字段）：用来描述对象的一个属性 行（记录）：用来描述一个对象的信息

2. 非关系型数据库 NoSQL

   • 存储结构：不同类型的NoSQL使用不同的存储结构，比如 键值对、文档、索引、时间序列等
   • 缓存型 Redis Memcached
   • 文档型 MongoDB
   • 索引型 Elasticsearch
   • 时序型 Prometheus InfluxDB
   • 列式型 HBase

常用的数据类型

• int ：整型 无符号[0,2^{32-1]，有符号[-2}31,2^31-1]
• float ：单精度浮点 4字节32位
• double ：双精度浮点 8字节64位
• char ：固定长度的字符类型
• varchar ：可变长度的字符类型
• text ：文本
• image ：图片
• decimal(5,2) ：5个有效长度数字，小数点后面有2位

SQL语言分类

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

SQL语言分类：

• DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
• DML：数据操纵语言，用于对表中的数据进行管理
• DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
• DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

Mysql基础操作

select version(); #查询版本
select user();    #查看用户名和主机名
show databases;   
use 库名;   show tables [from 库名];
describe 表名;   desc [库名.]表名;

SQL语句

DDL

# DDL：用于管理数据库对象（库、表、索引等）
create database 库名;
use 库名;
create table 表名 (字段1 数据类型, 字段2 数据类型, ....[, primary key(字段)]);

drop table [库名.]表名;
drop database 库名;

# 修改表结构
改表名         alter table 旧表名 rename 新表名
增加字段       alter table 表名 add 新字段 数据类型 [约束属性];
修改字段       alter table 表名 change 旧字段 新字段 数据类型 [约束属性];
删除字段       alter table 表名 drop 字段名;

show create table 表名;


# MySQL的六大约束特性：
primary key     主键约束     字段的值不能重复，不能为Null，一个表只能有一个主键
unique key      唯一性约束   字段的值不能重复，但能为Null，一个表可以有多个唯一键
not null        非空约束     字段的值不能为Null
default         默认值约束   字段的值如果没有设置则使用默认值自动填充
auto_increment  自增约束     字段的如果没有设置默认会从 1 开始每次自动递增 1，要求自增字段必须为主键
foreign key     外键约束     保证外键表与主键表相关联的表数据的完整性和一致性

int(N) zerofill   零填充


# 克隆表
create table 新表 like 旧表;                 #克隆表结构
insert into 新表 (select * from 旧表);       #克隆表数据        可以实现表结构和表数据与旧表都一致

create table 新表 (select * from 旧表);      #表数据和旧表一致，表结构和旧表不一定一致


# 清空表
delete from 表名;       #一条一条的删除记录，效率较慢；自增长字段仍然会按照清空表前的最大记录继续自增

truncate table 表名;    #直接重建表，清空表效率更快；自增长字段会重新从 1 开始自增 


# 临时表
create temporary table 表名 (....);       临时表跟普通表一样可以进行增删改查，但是show tables是查不到的；
                                  临时表只能在当前会话中有效，退出当前会话或在其它会话中，临时表都会失效

# 外键约束
主键表： alter table 表名 add primary key (主键字段);
外键表： alter table 表名 add [constraint] [约束] foreign key (外键字段) references 主键表名 (主键字段);
        插入新数据时，需要先在主键表插入数据再在外键表插入相关联的数据；删除数据时，需要先在外键表删除数据再在主键表删除相关联的数据
   	 #与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。
   	 alter table 表名 drop foreign key '外键别名';
   	 alter table 表名 drop key '键名';

DML

# DML：用于管理表数据
insert into 表名 (字段1, 字段2, ....) values (字段1的值, 字段2的值, ....);
insert into 表名 values (所有字段的值);

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

delete from 表名 where 条件表达式;

DQL

# DQL：用于根据条件查询表数据
select * from 表名 [where 条件表达式];  // [and|or]
select  字段1,字段2,....  from 表名 [where 条件表达式];

select * from 表名\G      #纵向查看每行记录的字段的值

select * from 表名 limit N;      #查看表的前N行的记录
select * from 表名 limit N,M;    #查看表的前N行之后的连续M行的记录（不包含第N行）

DCL

# DCL：用于管理用户和权限
# 用户管理：
create user '用户名'@'源地址'  identified by '密码';
                     源地址可以是localhost/IP/网段/主机名/%
					 
rename user '用户名'@'源地址' to '新用户名'@'源地址';

drop user '用户名'@'源地址';

select user,host,authentication_string from mysql.user;

select user();

set password [for '用户名'@'源地址'] = password('密码');
alter user '用户名'@'源地址' identified by '密码';
        

如何找回root密码

1. 修改MySQL配置文件，在 [mysqld] 配置项下面添加 skip-grant-tables 配置参数
2. 重启mysqld服务，使用 mysql 密码直接登录数据库
3. 执行命令 update mysql.user set authentication_string=password(‘密码’) where user=‘root’; 修改 root 用户密码
4. 还原MySQL配置，重启mysqld服务，使用 mysql -u用户名 -p密码 [-h mysql地址 -P mysql端口] 命令登录数据库验证

vim /etc/my.cnf
[mysqld]
skip-grant-tables					#添加，使登录mysql不使用授权表

systemctl restart mysqld

mysql								#直接登录

使用 update 修改 root 密码，刷新数据库
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';

FLUSH PRIVILEGES; || flush privileges
quit

mysql -u root -pabc123

注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。

权限管理

grant 权限1,权限2,....  on 库名.表名 to '用户名'@'源地址' [identified by '密码'];   
       all                   *.*
#5.7或之前版本支持创建新用户和授予权限，8.0版本只能用于授予权限

show grants for '用户名'@'源地址';

revoke 权限1,权限2,....  on 库名.表名 from  '用户名'@'源地址';
       all                   *.*