MySQL数据库管理
目录
2.1DDL(Data Definition Language-数据定义语言)
2.2DML(Data Manipulation Language-数据操纵语言)
2.3DQL(Data Query Language-数据查询语言)
2.4DCL(Data Control Language-数据控制语言)
一、数据库的基本操作
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)!