自学内容网 自学内容网

MySQL数据库管理

目录

一、数据库的基本操作

1.查看数据库信息

1.1查看所有数据库

1.2切换到某个数据库 

1.3查看当前数据库的表 

1.4查看表结构 

2.数据库表中常用字段 

二、SQL语句

1.SQL语句概述

2.SQL语言的分类 

2.1DDL(Data Definition Language-数据定义语言)

2.2DML(Data Manipulation Language-数据操纵语言)

2.3DQL(Data Query Language-数据查询语言) 

2.4DCL(Data Control Language-数据控制语言) 

三、高级操作

1.创建临时表

2.克隆表 

2.1克隆表结构 

2.2克隆表并复制数据 

2.3克隆表数据到新表 

2.4查看新表结构和索引信息 

四、数据库用户管理 

1.新建用户

2.重命名用户 

3.删除用户 

4.修改用户密码 

4.1忘记密码

5.数据库用户授权 

6.查看权限 

7.撤销权限 


一、数据库的基本操作

1.查看数据库信息

1.1查看所有数据库

show databases;

1.2切换到某个数据库 

use 数据库名;

1.3查看当前数据库的表 

show tables;

1.4查看表结构 

desc 表名;

2.数据库表中常用字段 

字段(Field)

表示数据库表中的列名称,每个字段代表表中的一个数据属性

数据类型(Type)

指该字段中存储数据的类型,常见的数据类型如下:

int:整数类型,通常用于存储不带小数点的数字
float:单精度浮点数,用于存储带小数的数值
double:双精度浮点数,精度更高,适用于需要存储更大范围和更精确的小数
char(n):定长字符类型,`n`代表字符长度。如果存储的字符串长度不足,会在右边用空格补足
varchar(n):可变长度的字符类型,`n`表示最大长度,实际存储时不填充空格
text:用于存储大文本数据,不固定长度
decimal(m, n):定点数,用于存储精确的数值。`m`表示总长度,`n`表示小数点后的位数。
date:存储日期,格式为YYYY-MM-DD
datetime:存储日期和时间,格式为`YYYY-MM-DD HH:MM:SS`
timestamp:时间戳,用于记录数据的创建或修改时间
blob:二进制大对象,用于存储图片、音频、视频等二进制数据

是否为空(NULL) 

标识该字段是否允许存储 NULL 值(空值)。如果字段为 NOT NULL,则该字段不能为空

主键(Key) 

定义某个字段是否为主键或索引

Primary Key:唯一标识表中的每一行记录,不允许重复和为空
Unique Key:字段的值必须唯一,但允许有一个 NULL 值
Index (Key):加速查询的索引,非唯一

默认值(Default) 

表示字段在没有显式赋值时,自动填充的值。如果未指定,通常默认值为 NULL,但某些字段类型可以指定其他默认值,如 DEFAULT '未知'

扩展属性(Extra) 

表示该字段的其他特性,如:

auto_increment:用于整数类型字段,表示自增的特性,通常与主键配合使用
on update CURRENT_TIMESTAMP:表示字段会在每次更新该行时自动记录当前时间

二、SQL语句

1.SQL语句概述

SQL(Structured Query Language,结构化查询语言)是一种专门用于与关系型数据库进行通信和操作的标准语言。SQL语句是一组标准化的指令,用户可以通过它来定义、操作、查询、控制数据库中的数据和结构

2.SQL语言的分类 

2.1DDL(Data Definition Language-数据定义语言)

DDL用于定义数据库结构或模式,主要用于创建和修改数据库对象,如数据库、表、索引等

常见 DDL 语句

CREATE:创建数据库对象(库、表、索引等)

创建新的数据库
CREATE DATABASE 数据库名;

创建新的表
CREATE TABLE KY37 (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT'', PRIMARY KEY (id));

ALTER:修改数据库对象(如表结构) 

ALTER TABLE KY37 ADD salary DECIMAL(10, 2);

DROP:删除数据库对象(如表、索引)

DROP TABLE KY37;

2.2DML(Data Manipulation Language-数据操纵语言)

DML 主要用于对数据库中的数据进行操作,包括插入、更新、删除和合并数据

常见 DML 语句 

INSERT:插入数据

INSERT INTO KY37 (id, name, age, department) VALUES (1, 'Alice', 30, 'HR');

UPDATE:更新数据 

UPDATE KY37 SET age = 31 WHERE name = 'Alice';

DELETE:删除数据 

DELETE FROM KY37 WHERE id = 1;

2.3DQL(Data Query Language-数据查询语言) 

DQL用于查询数据库中的数据,最常用的语句是 SELECT,它用于从数据库表中检索符合条件的数据记录

常见 DQL 语句

SELECT:查询数据

SELECT name, age FROM KY37 WHERE department = 'HR';

2.4DCL(Data Control Language-数据控制语言) 

DCL用于设置和管理数据库的权限,控制用户对数据库对象的访问权限。主要用于控制数据库的安全性

常见 DCL 语句 

GRANT:授予权限

GRANT SELECT, INSERT ON KY37 TO 'user1';

REVOKE:撤销权限 

REVOKE INSERT ON KY37 FROM 'user1';

三、高级操作

1.创建临时表

临时表是存在于内存中的表,主要用于存储会话过程中临时数据。临时表在连接结束时会自动销毁,但在会话中可以执行增删改查操作。临时表不能创建外键

CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型,...[PRIMARY KEY (主键字段名)]);
CREATE TEMPORARY TABLE xxx (
    id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL,
    cardid INT(18) NOT NULL UNIQUE KEY,
    hobby VARCHAR(50)
);

INSERT INTO xxx VALUES (1, 'zhangsan', 123456, 'running');

-- 查询临时表中的数据
SELECT * FROM xxx;

-- 手动删除临时表
DROP TABLE xxx;

注:执行 SHOW TABLES;并不会显示临时表,因为它们是会话私有的,无法通过这种方式查看  

2.克隆表 

克隆表是将现有的表结构和数据复制到新的表中,这在备份数据、测试数据或创建类似表时非常有用

2.1克隆表结构 

使用 LIKE 语法,可以复制表的结构(不复制数据)

CREATE TABLE 新表名 LIKE 旧表名;

CREATE TABLE kgc LIKE njzb;

2.2克隆表并复制数据 

可以使用 INSERT INTO SELECT 语法将旧表的数据复制到新表中

INSERT INTO 新表名 SELECT * FROM 旧表名;

INSERT INTO kgc SELECT * FROM njzb;

2.3克隆表数据到新表 

直接创建新表,并将数据插入

CREATE TABLE 新表名 AS SELECT * FROM 旧表名;

CREATE TABLE test02 AS SELECT * FROM test;

2.4查看新表结构和索引信息 

使用 SHOW CREATE TABLE 查看表的详细结构信息

SHOW CREATE TABLE test02\G;

四、数据库用户管理 

1.新建用户

创建用户的SQL语句是CREATE USER,它用于添加新用户并设置登录来源地址和密码

CREATE USER '用户名'@'来源地址' IDENTIFIED BY '密码';
创建本地用户user1,密码为123456
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';

创建本地用户user2,使用加密密码
SELECT PASSWORD('abc123');  -- 先获取加密后的密码
CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

2.重命名用户 

重命名现有用户使用 RENAME USER 命令

将用户zhangsan重命名为lisi
RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

3.删除用户 

删除用户使用 DROP USER 命令

删除用户lisi
DROP USER 'lisi'@'localhost';

4.修改用户密码 

修改密码有两种情况:当前用户修改自己密码,或者管理员修改其他用户的密码

当前用户修改密码
SET PASSWORD = PASSWORD('新密码');

管理员修改其他用户密码
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('新密码');

4.1忘记密码

如果 root 密码忘记了,可以通过修改 my.cnf 文件跳过权限来重置密码

1.修改 /etc/my.cnf 文件,添加 skip-grant-tables
[mysqld]
skip-grant-tables


2.重启 MySQL 服务,使用无密码登录
systemctl restart mysqld
mysql


3.修改 root 密码
UPDATE mysql.user SET authentication_string = PASSWORD('新密码') WHERE user = 'root';
FLUSH PRIVILEGES;


4.重启 MySQL 并删除 my.cnf 中的 skip-grant-tables 配置

5.数据库用户授权 

授予权限使用 GRANT 命令,它控制用户对数据库及表的操作权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
授权用户zhangsan在本地查询school数据库的所有表
GRANT SELECT ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';

授权用户lisi从任意来源远程连接,并拥有所有权限
GRANT ALL PRIVILEGES ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';

授权用户lisi从特定IP地址连接kgc数据库,并拥有所有权限
GRANT ALL PRIVILEGES ON kgc.* TO 'lisi'@'192.168.10.2' IDENTIFIED BY 'abc123';


刷新权限
FLUSH PRIVILEGES;

6.查看权限 

使用 SHOW GRANTS 命令查看用户的权限

SHOW GRANTS FOR 'lisi'@'%';

7.撤销权限 

使用 REVOKE 命令可以撤销已授权的权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'来源地址';
撤销用户lisi的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'lisi'@'%';

刷新权限
FLUSH PRIVILEGES;

原文地址:https://blog.csdn.net/2201_75444658/article/details/142323915

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!