【MySQL数据库】MySQL数据库初体验

数据库

数据库是在20世纪60年代末发展起来的一项重要技术，主要是用来解决数据处理的非数值计算问题，数据处理的主要内容是数据的存储、查询、修改、排序和统计等。数据库技术和系统已经成为信息基础设施的核心技术和重要基础，作为数据管理的最有效的手段，极大地促进了计算机应用的发展。

定义与概念

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

技术特点

1. 存储方式：数据库并不是随意地将数据进行存放，而是有一定的规则的，否则查询的效率会很低。
2. 存储结构：数据库按照数据结构来组织、存储和管理数据。
3. 存储规范：数据库中的数据需要遵循一定的规范和标准，以确保数据的准确性和一致性。
4. 扩展方式：数据库系统可以根据需要进行扩展，以满足不断增长的数据存储需求。
5. 查询方式：数据库提供了丰富的查询功能，用户可以根据需要查询满足条件的数据。
6. 规范化：数据库设计需要遵循规范化原则，以减少数据冗余和提高数据查询效率。
7. 事务性：数据库支持事务处理，可以确保数据的一致性和完整性。
8. 读写性能：数据库系统需要具备良好的读写性能，以满足高效的数据处理需求。

发展趋势

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

分类

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

一、关系型数据库

关系型数据库是建立在关系模型基础上的数据库，其借助于集合代数等数学概念和方法来处理数据库中的数据。

1. 常见的关系型数据库：MySQL、MariaDB、PostgreSQL、SQL Server、Oracle、DB2，以及国产的达梦、人大金仓、greatSQL、华为的高斯OpenGauss、阿里的Oceanbase、阿里云的RDB、腾讯的TDSQL等。
2. 存储结构：关系型数据库采用二维表格的形式存储数据，每个数据表都必须对各个字段定义好（即先定义好表的结构），再根据表的结构存入数据。数据表中的行（记录）用来描述一个对象的信息，而列（字段）则用来描述对象的一个属性。
3. 特点：关系型数据库强调数据的一致性和完整性，采用ACID事务模型来保证数据的安全性和稳定性。同时，关系型数据库使用结构化查询语言（SQL）进行数据查询，支持复杂的查询和分析。

二、非关系型数据库（NoSQL）

非关系型数据库是指那些不使用关系模型来组织数据的数据库。它们通常使用键值对、文档、列族或图等非传统的关系型数据表结构来存储数据。

1. 常见的非关系型数据库：
   • 缓存型：Redis、Memcached等，主要用于简单的缓存功能。
   • 文档型：MongoDB等，将数据存储为面向文档的格式，如JSON或BSON，适合处理半结构化数据。
   • 索引型：ElasticSearch等，主要用于全文搜索和数据分析。
   • 时序型：Prometheus、InfluxDB等，专门用于处理时间序列数据。
   • 列式型：HBase等，将数据存储为列族和行的组合，类似于稀疏矩阵，适合大规模分布式数据。
2. 存储结构：非关系型数据库采用非二维表格结构来存储数据。不同类型的NoSQL数据库采用不同的存储结构，如键值对、文档、列族或图等。
3. 特点：非关系型数据库更加关注数据的可用性和灵活性，采用最终一致性模型，允许系统在一定时间内自动同步数据。它们通常使用简单的键值对查询语言或特定的查询语言进行数据查询，限制了查询的复杂性。同时，非关系型数据库采用分布式架构，可以通过添加节点来水平扩展性能。

数据库管理系统（DBMS）

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

数据库、数据表、行（记录）和列（字段）的基本概念

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

常用的数据类型

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

查看数据库结构

查看MySQL版本信息和连接用户名

SELECT VERSION();
SELECT USER();

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

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（自增）。
  例如，对于一个名为 students 的表，DESCRIBE students; 可能会返回如下信息：

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | YES  |     | NULL    |                |
| age         | int(11)      | YES  |     | NULL    |                |
| major       | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

这表示 students 表有四个字段：id（整型，主键，自增），name（可变长度字符型，允许为空），age（整型，允许为空），major（可变长度字符型，允许为空）。

数据库查询语言（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、COMMIT和ROLLBACK等。

DDL 数据定义语言 用于管理数据库对象（库、表、索引等）

创建数据库

CREATE DATABASE 数据库名;

删除数据库

DROP DATABASE 数据库名;

创建表

CREATE TABLE 表名 (
    字段1 数据类型,
    字段2 数据类型[,...][,PRIMARY KEY (主键名)]
);

删除表

DROP TABLE [数据库名.]表名;

修改表名

ALTER TABLE 旧表名 RENAME 新表名;

扩展表结构（增加字段）

ALTER TABLE 表名 ADD 字段名 数据类型 [default 默认值];

修改字段名或添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

删除字段

ALTER TABLE 表名 DROP 字段名;

添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY (主键名);

添加外键约束

ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 主表名 (主键字段);

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

DML 数据操纵语言 用于对表中的数据进行管理

插入数据

INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);

更新数据

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

删除数据

DELETE FROM 表名 [WHERE 条件表达式];

清空表（DELETE方法）

DELETE FROM 表名;

清空表（TRUNCATE方法）

TRUNCATE TABLE 表名;

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

查询数据

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

查询所有数据

SELECT * FROM 表名;

以列表方式竖向显示

SELECT 字段名 FROM 表名\G;

限制查询结果数量

SELECT * FROM 表名 LIMIT 行数;
SELECT * FROM 表名 LIMIT 偏移量,行数;

DCL 数据控制语言 用于设置或者更改数据库用户或角色权限

1. 新建用户

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

• 参数说明：
  • '用户名'：指定新用户的名称。
  • '来源地址'：指定用户可以从哪些主机登录，如localhost、IP地址、网段或%（任意主机）。
  • '密码'：用户的密码，可以是明文或加密后的密文。
    示例：

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
SELECT PASSWORD('abc123');
CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

2. 查看用户信息

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

• 查看当前登录用户：

  SELECT USER();
  

3. 重命名用户

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

示例：

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

4. 删除用户

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

示例：

DROP USER 'lisi'@'localhost';

5. 修改用户密码

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

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

• 修改其他用户密码：

  SET PASSWORD FOR '用户名'@'来源地址' = PASSWORD('新密码');
  

忘记root密码的解决办法

1. 修改配置文件/etc/my.cnf，添加skip-grant-tables。
2. 重启MySQL服务。
3. 直接登录MySQL。
4. 使用UPDATE语句修改root密码。
5. 刷新权限。
6. 删除配置文件中的skip-grant-tables，重启MySQL服务。

数据库用户授权

1. 授予权限

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

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

GRANT SELECT ON mydb.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

2. 查看权限

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

示例：

SHOW GRANTS FOR 'lisi'@'%';

3. 撤销权限

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

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

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

其他高级操作

1. 克隆表

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

-- 通过 LIKE 方法，复制 xy101 表结构生成 test01 表
CREATE TABLE test01 LIKE xy101;

-- 将 xy101 表的数据插入到 test01 表中
INSERT INTO test01 SELECT * FROM xy101;

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

-- 这种方法创建的新表的表数据和旧表是一样的，但可能会出现新表的表结构和旧表的不一致
CREATE TABLE test02 (SELECT * FROM xy101);

-- 获取数据表的表结构、索引等信息
SHOW CREATE TABLE test02\G;

-- 查询 test02 表的数据
SELECT * FROM test02;

这种方法在创建新表时可能不会完全复制旧表的索引和其他表属性。

2. 清空表

清空表是指删除表内的所有数据，但保留表结构。
方法一：使用 DELETE

-- DELETE 清空表后，返回的结果内有删除的记录条目
DELETE FROM test01;

DELETE 是逐行删除数据的，因此效率较低。同时，如果表中有自增长字段，删除数据后，新添加的记录会从原来最大的记录 ID 后面继续自增。
方法二：使用 TRUNCATE

-- TRUNCATE 清空表后，没有返回被删除的条目，速度比 DELETE 快
TRUNCATE TABLE test01;

TRUNCATE 是通过重新创建表来清空数据的，因此效率较高。同时，TRUNCATE 清空数据后，自增长字段的 ID 会从 1 开始重新记录。

3. 创建临时表

临时表是在会话期间存在的表，当会话结束时，临时表会自动删除。

-- 创建临时表
CREATE TEMPORARY TABLE test03 (
    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 test03 VALUES (1, 'zhangsan', 123456, 'running');

-- 查询数据
SELECT * FROM test03;

-- 使用 SHOW TABLES 命令看不到临时表
SHOW TABLES;

-- 退出会话后，临时表会被自动删除
QUIT;

-- 尝试查询临时表（会话已结束，临时表已删除）
SELECT * FROM test03;  -- 会报错，表不存在

4. 创建外键约束

外键约束用于保证数据的完整性和一致性。
步骤：

1. 创建主键表（父表）。
2. 创建从表（外表）。
3. 为主键表添加主键约束。
4. 为从表添加外键约束，并将从表的外键字段与主键表的主键字段建立关联。

-- 创建主键表 profession
CREATE TABLE profession (
    pid INT(4),
    proname VARCHAR(50)
);

-- 创建从表 student
CREATE TABLE student (
    id INT(4) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    age INT(3),
    proid INT(4)
);

-- 为主键表 profession 添加主键约束
ALTER TABLE profession ADD PRIMARY KEY (pid);

-- 为从表 student 添加外键约束
ALTER TABLE student ADD CONSTRAINT FK_pro FOREIGN KEY (proid) REFERENCES profession (pid);

-- 查看 student 表结构
DESC student;

-- 插入数据（要先插入主键表的数据，再插入从表的数据）
INSERT INTO profession VALUES (1, '云计算');
INSERT INTO profession VALUES (2, '大数据');
INSERT INTO student VALUES (1, 'zhangsan', 18, 1);
INSERT INTO student VALUES (2, 'lisi', 19, 1);
INSERT INTO student VALUES (3, 'wangwu', 20, 2);

-- 删除数据（要先删除从表的数据，再删除主键表的数据）
DELETE FROM student WHERE proid = 1;
DELETE FROM profession WHERE pid = 1;

-- 查看和删除外键约束
SHOW CREATE TABLE student;
DESC student;
ALTER TABLE student DROP FOREIGN KEY FK_pro;
-- 注意：ALTER TABLE student DROP KEY proid; 这条语句是错误的，因为 proid 不是索引键，而是外键约束的一部分。要删除外键约束，应使用上面的 ALTER TABLE ... DROP FOREIGN KEY 语句。

MySQL 中常见的约束

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

问答环节

外键约束如何保证数据的完整性和一致性的？
插入数据时，需要先保证主键表的公共字段有相关数据，才能在外键表插入相关联的数据
删除数据时，需要先保证外键表已经没有相关联的数据，才能在主键表删除相关的数据

如何找回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端口]