MySQL数据库管理

MySQL数据库管理

一、数据库的定义

• 定义：数据库是存放数据的仓库，它的存储空间很大，可以存放百万条、千万条、上亿条数据。数据库是一个按数据结构来存储和管理数据的计算机软件系统，能够合理保管数据的“仓库”。
• 作用：对数据进行存储、删除等操作，是数据管理新的方法和技术，能更合适的组织数据、更方便的维护数据、更严密的控制数据和更有效的利用数据。
• 功能：组织、存储和管理数据。

发展趋势

1. 多样化：数据库技术的多样化是未来发展的一个重要方向，包括实时分析型数据库、内存数据库、分布式数据库等。
2. 云化：云数据库因其灵活性和可扩展性，正在成为企业数据管理的首选。
3. 智能化：数据库技术正朝着智能化的方向发展，如数据库自治、智能优化等。

二、分类

• 关系数据库：存储的格式可以直观地反映实体间的关系，和常见的表格比较相似，表与表之间有很多复杂的关联关系。常见的关系型数据库有Mysql、SqlServer等。
• 非关系数据库：即NoSQL（Not only SQL）数据库，有更好的高并发读写性，也更利于存储海量数据。

关系型数据库（SQL）

关系型数据库是建立在关系模型基础上的数据库。

1. 常见的关系型数据库：MySQL、MariaDB、PostgreSQL、SQL Server、Oracle、DB2，以及国产的达梦、人大金仓、greatSQL、华为的高斯OpenGauss、阿里的Oceanbase、阿里云的RDB、腾讯的TDSQL等。
2. 存储结构：关系型数据库采用二维表格的形式存储数据，每个数据表都必须对各个字段定义好（即先定义好表的结构），再根据表的结构存入数据。数据表中的行（记录）用来描述一个对象的信息，而列（字段）则用来描述对象的一个属性。

非关系型数据库（NoSQL）

非关系型数据库是指那些不使用关系模型来组织数据的数据库。

1. 常见的非关系型数据库：
   • 缓存型：Redis、Memcached等，主要用于简单的缓存功能。
   • 文档型：MongoDB等，将数据存储为面向文档的格式。
   • 索引型：ElasticSearch等，主要用于全文搜索和数据分析。
   • 时序型：Prometheus、InfluxDB等，专门用于处理时间序列数据。
   • 列式型：HBase等，适合大规模分布式数据。
2. 存储结构：非关系型数据库采用非二维表格结构来存储数据。不同类型的NoSQL数据库会采用不同的存储结构。

三、数据库管理系统（DBMS）

1. 定义：数据库管理系统是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。
2. 分类：数据库管理系统可以依据它所支持的数据库模型、所支持的计算机类型、所用查询语言、性能冲量重点等来作分类。
3. 功能：数据库管理系统是数据库系统的核心组成部分，主要完成对数据库的操作与管理功能，实现数据库对象的创建、数据库存储数据的增、删、改、查操作和数据库的用户管理、权限管理等。

基本概念

• 数据库：是存储数据的容器，通常包含多个数据表。
• 数据表：是数据库中存储数据的结构化形式，由行和列组成。
  • 行（记录）：数据表中的一行代表一个实体对象的信息。
  • 列（字段）：数据表中的一列代表实体对象的一个属性。

常用的数据类型

• int：整型，分为无符号和有符号两种。无符号的范围是 [0, 2^32-1]，有符号的范围是 [-2^31, 2^31-1]。
• float：单精度浮点类型，占用4字节（32位）。
• double：双精度浮点类型，占用8字节（64位）。
• char：固定长度的字符类型，定义时指定长度，存储时会用空格填充至指定长度。
• varchar：可变长度的字符类型，定义时指定最大长度，存储时仅占用实际字符长度加上额外的长度标识字节。
• text：用于存储大文本数据。
• image：用于存储图片数据。
• decimal(m, n)：定点数类型，用于存储精确的小数。m表示总的有效数字位数，n表示小数点后的位数。例如，decimal(5,2)表示最多5位有效数字，其中2位在小数点后。

查看数据库结构

查看MySQL版本信息

SELECT VERSION();

查看当前服务器中的数据库

SHOW DATABASES;

MySQL会附带几个系统数据库，包括：

• information_schema：存储关于数据库对象的信息，如数据库名、表、表字段的数据类型与访问权限等。
• performance_schema：存储数据库服务器的性能参数信息。
• mysql：存储系统的用户权限信息。
• sys（MySQL 5.7及之后）：提供快速了解MySQL信息的视图、函数和存储过程，数据来源于performance_schema。

查看数据库中包含的表

首先，选择目标数据库：

USE 数据库名;

然后，查看该数据库中的表：

SHOW TABLES;

查看表的结构（字段）

同样，首先选择目标数据库：

USE 数据库名;

然后，查看表的结构：

DESCRIBE 表名;

或简写为：

DESC 表名;

DESCRIBE 语句

DESCRIBE 语句用于显示表的字段信息，包括：

• Field：字段名。
• Type：字段的数据类型。
• Null：字段是否允许为空（YES/NO）。
• Key：字段是否被索引（PRI表示主键，UNI表示唯一索引，MUL表示非唯一索引）。
• Default：字段的默认值。
• Extra：额外的信息，如auto_increment（自增）。

数据库查询语言（SQL语句）

SQL（Structured Query Language）是用于管理关系型数据库的标准化语言。它分为四种基本操作类型：DDL、DML、DQL和DCL。

1. DDL（Data Definition Language，数据定义语言）：用于定义数据库结构和对象，如创建表、修改表结构、删除表等。常见的DDL语句包括CREATE、ALTER和DROP等。
2. DML（Data Manipulation Language，数据操作语言）：用于操作数据库中的数据，如插入、更新、删除数据等。常见的DML语句包括INSERT、UPDATE和DELETE等。
3. DQL（Data Query Language，数据查询语言）：用于从数据库中查询数据。常见的DQL语句是SELECT语句，用于从表中检索数据。
4. DCL（Data Control Language，数据控制语言）：用于授权、回滚事务等操作。常见的DCL语句包括GRANT、REVOKE等。

DDL 数据定义语言

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

创建数据库

CREATE DATABASE 数据库名;示例
create database crazzy;

删除数据库

DROP DATABASE 数据库名;示例
drop database crazzy;

创建表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);示例
CREATE TABLE crazzy (id INT,name VARCHAR(20),age INT,sex varchar(10));

删除表

DROP TABLE [数据库名.]表名;示例
DROP TABLE crazzy;

修改表名

ALTER TABLE 旧表名 RENAME 新表名;示例
ALTER TABLE crazzy RENAME twlw;

扩展表结构（增加字段）

ALTER TABLE 表名 ADD 字段名 数据类型 [default 默认值];示例
ALTER TABLE twlw ADD email varchar(20);

修改字段名或添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];示例
ALTER TABLE twlw CHANGE email adress varchar(20);

删除字段

ALTER TABLE 表名 DROP 字段名;示例
ALTER TABLE twlw DROP adress;

添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY (主键名);示例
ALTER TABLE twlw ADD PRIMARY KEY (id);

添加外键约束

ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 主表名 (主键字段);示例
ALTER TABLE crazzy ADD CONSTRAINT user_id FOREIGN KEY (id) REFERENCES twlw (id);
SHOW CREATE TABLE crazzy;  #查看

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;示例
ALTER TABLE crazzy DROP FOREIGN KEY user_id;

MySQL 中常见的约束

1. 主键约束（PRIMARY KEY）：唯一标识表中的每一行。
2. 外键约束（FOREIGN KEY）：用于维护和保证两个表之间的数据一致性。
3. 非空约束（NOT NULL）：确保字段不能为空。
4. 唯一性约束（UNIQUE [KEY|INDEX]）：确保字段的值唯一。
5. 默认值约束（DEFAULT）：为字段设置默认值。
6. 自增约束（AUTO_INCREMENT）：使字段的值自动递增。

DML 数据操纵语言

用于对表中的数据进行管理

插入数据

INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);示例
INSERT INTO twlw (id,name,age,sex) VALUES (1,'张三',24,'男');

更新数据

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];示例
UPDATE twlw SET name='王五',sex='女') WHERE id=1;

删除数据

DELETE FROM 表名 [WHERE 条件表达式];示例
DELETE FROM twlw WHERE id=1;
select * from twlw;  #查看

清空表（DELETE方法）

DELETE FROM 表名;    #删除数据，id会原来最大记录的id后面继续自增

清空表（TRUNCATE方法）

TRUNCATE TABLE 表名;  #清空数据，id会从1重新记录

DQL 数据查询语言

用于从数据表中查找符合条件的数据记录

查询数据

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

查询所有数据

SELECT * FROM 表名;

以列表方式竖向显示

SELECT 字段名 FROM 表名\G示例
select * from twlw\G

限制查询结果数量

SELECT * FROM 表名 LIMIT 行数;
SELECT * FROM 表名 LIMIT 偏移量,行数;示例
select * from twlw limit 2;
select * from twlw limit 1,2;

DCL 数据控制语言

用于设置或者更改数据库用户或角色权限

1. 新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];

• 参数说明：

  • '用户名'：指定新用户的名称。

  • '来源地址'：指定用户可以从哪些主机登录，如localhost、IP地址、网段或%（任意主机）。

  • '密码'：用户的密码，可以是明文或加密后的密文。

示例

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';

2. 查看用户信息

USE mysql;
SELECT User, authentication_string, Host FROM user;

• 查看当前登录用户：

  SELECT USER();
  

3. 重命名用户

RENAME USER '旧用户名'@'来源地址' TO '新用户名'@'来源地址';

​ 示例：

RENAME USER 'user1'@'localhost' TO 'user2'@'localhost';

4. 删除用户

DROP USER '用户名'@'来源地址';

示例：

DROP USER 'user2'@'localhost';

5. 修改用户密码

• 修改当前登录用户密码：

  SET PASSWORD = PASSWORD('新密码');
  ALTER USER '用户名'@'来源地址' IDENTIFIED BY '新密码';
  

• 修改其他用户密码：

  SET PASSWORD FOR '用户名'@'来源地址' = PASSWORD('新密码');示例
  CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
  SELECT User, authentication_string, Host FROM user; #查看user1的信息
  SET PASSWORD FOR 'user1'@'localhost' = 'abc123';
  

忘记root密码的解决办法

1、修改配置文件/etc/my.cnf，添加skip-grant-tables。

2、重启MySQL服务，使用 mysql 命令直接登录数据库，执行 update mysql.user set authentication_string=password('密码') where user='root'; 修改root用户密码

3、还原mysql配置文件，再重启mysqld服务，即可使用密码登录数据库 mysql -u 用户名 -p密码 [-h mysql服务器地址 -P mysql端口]

数据库用户授权

1. 授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

• 参数说明：
  • 权限列表：如SELECT, INSERT, UPDATE，或ALL PRIVILEGES表示所有权限。
  • 数据库名.表名：如mydb.*表示mydb数据库中的所有表。
  • 用户名@来源地址：指定用户及其来源地址。

示例：

DROP USER 'user1'@'localhost';
CREATE USER 'user1'@'%' IDENTIFIED BY '123456';
GRANT SELECT ON xy103.* TO 'user1'@'%';
FLUSH PRIVILEGES;

2. 查看权限

SHOW GRANTS FOR '用户名'@'来源地址';

示例：

SHOW GRANTS FOR 'user1'@'%';

3. 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'来源地址';

• 注意：USAGE权限只能用于登录，不能执行任何操作，且不能被回收。

示例：

REVOKE ALL ON xy103.* FROM 'user1'@'%';
SHOW GRANTS FOR 'user1'@'%';
FLUSH PRIVILEGES;

克隆表

克隆表是指将数据表的结构和数据复制到一个新的表中。
方法一：使用 LIKE 和 INSERT

-- 通过 LIKE 方法，复制 twlw 表结构生成 test01 表
CREATE TABLE test01 LIKE twlw;-- 将 twlw 表的数据插入到 test01 表中
INSERT INTO test01 SELECT * FROM twlw;-- 查询 test01 表的数据
SELECT * FROM test01;

这种方法可以确保新表的表结构和表数据与旧表完全一致。
方法二：使用 SELECT 创建表

-- 这种方法创建的新表的表数据和旧表是一样的，但可能会出现新表的表结构和旧表的不一致
CREATE TABLE test02 (SELECT * FROM twlw);-- 获取数据表的表结构、索引等信息
SHOW CREATE TABLE test02\G-- 查询 test02 表的数据
SELECT * FROM test02;