MySQL数据库管理-优快云博客

本文链接：https://blog.youkuaiyun.com/WJC_manong/article/details/142931363

一、数据库简介

一、数据库简介

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

1、关系型数据库(SQL)

①关系型数据库类型：

MySQL MariaDB PostgreSQL SQL_Server Oracle DB2

国产数据库（信创标准）：

达梦人大金仓 greatSQL 华为(高斯OpenGauss) 阿里(Oceanbase) 阿里云（RDB）腾讯(TDSQL)

②存储结构的类型

库 -> 表 -> 二维表格形象的结构化数据

行（记录）：用来描述一个对象的信息

列（字段）：用来描述对象的一个属性

③存储格式：二维表格

2、非关系型数据库(NoSQL)

①存储格式：非二维表格

不同类型的NoSQL采用不同的存储结构，比如键值对、文档、索引、时间序列等

• 缓存型 Redis Memcached • 文档型 MongoDB • 索引型 ElasticSearch • 时序型 Prometheus InfluxDB • 列式型 Hbase

二、SQL语句

关系型数据库专用的操作管理语句 SQL基本操作分4种类型： DDL DML DQL DCL

1、DDL 数据定义语言

用于管理数据库对象（库、表、索引等）

①创建库、表

create database 库名;
use 库名;     切换到库中，进行操作
create table 表名 (字段1 数据类型, 字段2 数据类型, .... , primary key (字段));

②查看库、表

● 查看表内容
show databases;
show tables [from 库名];
● 查看表结构
desc 表名;
show create table 表名;

③删除库、表

drop table 表名;
drop database 库名;

④修改表结构

● 重命名表名
alter table 旧表名 rename 新表名;
● 添加表中字段
alter table 表名 add 新字段 数据类型 [约束属性];
● 删除表中字段
alter table 表名 drop 字段;
● 修改表中字段的数据类型和字段名
alter table 表名 change 旧字段 新字段 数据类型 [约束属性];
                modify  只能修改数据类型，不能重命名字段名

⑤MySQL的6大约束属性

主键约束      primary key       字段的值不能重复，且不能为NULL，一个表只能有一个主键
唯一键约束    unique key        字段的值不能重复，但可以为NULL，一个表可以有多个唯一键
非空约束      not null          字段的值不能为NULL
默认值约束    default 'XXX'     字段的值如果没有设置则使用默认值自动填充
自增约束      auto_increment    字段的值如果没有设置值默认会从 1 开始每行自动递增 1 ，要求设置自增约束的字段必须为主键字段
外键约束      foreign key       保证外键表和主键表相关联数据的完整性和一致性
零填充       int(N) zerofill      使用0进行填充使字段的数值满足 N 位数

PS：外键约束

①主、外键表的创建

主键表    alter table 主表名 add primary key (公共字段);
外键表    alter table 外表名 add foreign key (公共字段) references 主表名 (公共字段);

②外键约束如何保证数据的完整性和一致性的

● 插入数据时，需要先保证主键表的公共字段有相关数据，才能在外键表插入相关联的数据
● 删除数据时，需要先保证外键表已经没有相关联的数据，才能在主键表删除相关的数据

2、DML 数据操纵语言

用于管理表中数据

①插入数据

insert into 表名 (字段1, 字段2, ....) values (字段1的值, 字段2的值, ....);
insert into 表名 values (所有字段的值);

②修改数据

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

③删除数据

delete from 表名 where 条件表达式;

3、DQL 数据查询语言

用于查询表数据

①查询数据

select * from 表名 [where 条件表达式];
select 字段1,字段2 from 表名 where 条件表达式;

②分页查询

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

4、DCL 数据控制语言

用于管理用户和授权

1.用户账户管理

①创建用户

create user '用户名'@'来源地址' identified by '密码';    #来源地址可以是 localhost  IP   网段   %（代表任意地址）

②查看用户信息

● 查看所有用户信息
select user,host,authentication_string from mysql.user;
#创建后的用户保存在 mysql 数据库的 user 表里
● 查看当前用户信息
select user();

③重命名用户

rename user '用户名'@'来源地址' to '新用户名'@'新来源地址';

④删除用户

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

2.用户密码管理

①修改用户密码

● 修改当前用户密码
set password = password('密码');
alter user '用户名'@'来源地址' identified by '密码';           #只能root用户使用
● 修改其他用户密码
set password for '用户名'@'来源地址' = password('密码');       #只能root用户使用

PS：MySQL命令登录

mysql -u 用户名 -p密码 -h mysql服务器地址 -P mysql端口       #mysql命令远程登录格式

3.授权管理

①grant语句基本格式

grant 权限1,权限2,....  on  库名.表名  to '用户名'@'来源地址'  [identified by '密码'];
#在5.7及以前版本支持grant创建新用户并授权，8.0版本开始grant只能用于授权
flush privileges;   #刷新权限更改

e.g：

#允许用户 zhangsan 在本地查询 mydb 数据库中 所有表的数据记录，但禁止查询其他数据库中的表的记录。
GRANT select ON mydb.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123456';

#允许用户 lisi 在所有终端远程连接 mysql ，并拥有所有权限。
GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';

②查看用户权限

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

③撤销用户权限

revoke 权限列表  on 库名.表名  from  '用户名'@'来源地址';
       all

三、数据表高级操作

1、克隆表

①create、insert命令结合克隆

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

②create命令直接克隆

create table 新表名 (select * from 旧表名);
#表数据和旧表是一样的，但是新表的表结构和旧表的不一定一样

2、清空表数据

①delete命令

delete from 表名;             
#一条一条的删除记录，清空表的效率较慢；auto_increment自增字段仍保留原有的记录

②truncate命令

truncate table 表名;         
#直接重建表，清空表的效率较快；auto_increment自增字段的记录会被重置

3、创建临时表

①创建命令

create temporary table 表名 (....);

②临时表特点

● 临时表与一般的表的相同点：都可以在表中进行增删改查表数据
● show tables查不到临时表；临时表只能在当前会话中有效，退出当前会话，临时表就会失效

4、如何找回root密码

1）修改mysql配置文件（/etc/my.cnf），在 [mysqld] 配置项下面添加配置 skip-grant-tables
2）重启mysqld服务，使用 mysql 命令直接登录数据库，执行 update mysql.user set authentication_string=password('密码') where user='root'; 修改root用户密码
3）还原mysql配置文件，再重启mysqld服务，即可使用密码登录数据库  mysql -u 用户名 -p密码 [-h mysql服务器地址 -P mysql端口]