[Linux]经典面试题 - 数据库管理 - MySQL

目录

参考：

一、SQL语句

可以把 SQL 分成两个部分：数据操作语言（DML）和数据定义语言（DDL）

SQL 适用于执行查询的语法。但是 SQL 语言也包含用于增删改查的语法。

增删改查语句构成 SQL 的 DML 部分：

  • SELECT - 查询
  • UPDATE - 更改
  • DELETE - 删除
  • INSERT INTO - 增加

创建删除表格，定义索引（键），规定表间链接和设定约束的 DDL 语句：

  • CREATE DATABASE - 创建新数据库
  • ALTER DATABASE - 修改数据库
  • CREATE TABLE - 创建新表
  • ALTER TABLE - 变更（改变）数据库表
  • DROP TABLE - 删除表
  • CREATE INDEX - 创建索引（搜索键）
  • DROP INDEX - 删除索引

二、数据库简介

  • 数据库（DataBase）是按照数据结构来组织、存储和管理数据的仓库。

  • 每个数据库都有一个或多个不同的API用于对数据的增删改查。

三、关系型数据库

RDBMS（Relational DataBase Management System），关系型数据库。

关系型数据库的一些术语：

  • 数据库（DataBase）：数据库是表的集合，可以理解为用来存放数据表的仓库。
  • 数据表（Table）：表是数据的矩阵，看起来就像一个简单的Excel表格。
  • 列（Column）：一列相同类型的数据。
  • 行（Row）：一组相关的数据。
  • 主键（Primary Key)：主键是唯一的，一个数据表中只能包含一个主键，可以使用主键来查询数据。
  • 外键（Foreign key）：外键用于关联两个表。
  • 复合键（Composite key）：复合键将多个列作为一个索引键，一般用于符合索引。
  • 索引（Index）：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

四、MySQL 安装

在 CentOS 7 之后的MySQL被其分支MariaDB代替，在存储引擎方面采用XtraDB来代替MySQL的InnoDB。

查看系统中的是否自带MariaDB：

[root@localhost ~]# rpm -qa | grep mariadb
mariadb-errmsg-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-gssapi-server-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-backup-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-connector-c-config-3.1.11-2.el8_3.noarch
mariadb-server-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-server-utils-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-common-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-connector-c-3.1.11-2.el8_3.x86_64

如果有，可以选择卸载（因为系统自带的版本可能会比较旧，存在兼容性问题）

rpm -e mariadb // 普通删除模式
rpm -e --nodeps mariadb // 强力删除模式，会删除依赖文件

安装 Mariadb：

yum install -y mariadb mariadb-server

初始化 Mariadb：

mysqld --initialize

设置开机启动：

systemctl enable mariadb.service
systemctl start mariadb.service

验证：

[root@localhost ~]# mysqladmin --version
mysqladmin  Ver 9.1 Distrib 10.3.28-MariaDB, for Linux on x86_64

初始化root登录密码：

mysqladmin -u root password "new_password"

登录数据库：

mysql -u root -p

五、MySQL 管理

5.1 用户设置

在MySQL（MariaDB）中，添加用户有两种方式：

5.1.1 创建用户

切换到mysql库：

MariaDB [(none)]> use mysql;

添加sky用户，密码为skybiubiu：

MariaDB [mysql]> create user sky identified by 'skybiubiu';
Query OK, 0 rows affected (0.000 sec)

5.1.2 用户授权

以skydb库为例子：

MariaDB [mysql]> CREATE DATABASE skydb;
Query OK, 1 row affected (0.000 sec)

通过GRANT命令进行授权，授予sky用户SELECT，INSERT，UPDATE操作权限：

MariaDB [skydb]> GRANT SELECT,INSERT,UPDATE ON skydb.*
    -> TO 'sky'@'localhost'
    -> IDENTIFIED BY 'skybiubiu';
Query OK, 0 rows affected (0.001 sec)

5.2 管理命令

  • USE 数据库名 - 选择要操作的数据库
  • SHOW DATABASES - 查看所有数据库
  • SHOW TABLES - 查看指定库的所有表
  • SHOW COLUMNS FROM 数据表 - 显示数据表的属性，属性类型，主键信息，NULL，默认值等信息
  • SHOW INDEX FROM 数据表 - 显示数据表的详细索引信息，包括主键。
  • SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] - 查看表状态

六、MySQL 数据结构

有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。

6.1 Text 类型

数据类型 描述
CHAR(size) 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size) 保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT 存放最大长度为 255 个字符的字符串。
TEXT 存放最大长度为 65,535 个字符的字符串。
BLOB 用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。
MEDIUMTEXT 存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB 用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。
LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.) 允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照您输入的顺序排序的。可以按照此格式输入可能的值： ENUM('X','Y','Z')
SET 与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。

6.2 Number 类型

数据类型 描述
TINYINT(size) -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size) -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size) -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d) 带有浮动小数点的小数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。

这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

6.3 Date 类型

数据类型 描述
DATE() 日期。格式：YYYY-MM-DD注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME() *日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP() *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME() 时间。格式：HH:MM:SS注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR() 2 位或 4 位格式的年。注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

七、MySQL 库&表操作

7.1 库操作

7.1.1 创建库

方法一：登入MySQL中，通过SQL语句创建。

CREATE DATABASE IF NOT EXISTS <数据库名>;
  • IF NOT EXISTS - 如果不存在该数据库则创建。

方法二：在bash中，利用mysqladmin命令创建。

mysqladmin -u root -p create <数据库名>

7.1.2 删除库

同上两种删除库的方法：

登入MySQL中，通过SQL语句删除：

DROP DATABASE <数据库名>;

在Bash，通过mysqladmin命令删除：

mysqladmin -u root -p drop <数据库名>

7.1.3 选择库

进入mysql，使用SQL语句选择库：

USE <数据库名>;

7.2 表操作

7.2.1 创建表

登入MySQL中，通过SQL语句创建：

CREATE TABLE <表名> (column_name column_type);

示例：

CREATE TABLE IF NOT EXISTS books (
  BookID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  Title VARCHAR(100) NOT NULL, 
  SeriesID INT, AuthorID INT)CHARSET UTF8;

注意：`是反单引号，ESC下面那个，可加可不加。

  • NOT NULL - 该字段不能为空。
  • AUTO_INCREMENT - 自增，常用于主键，数值会自动加1。
  • PRIMARY KEY - 定义主键，可使用多列来定义主键，列间以逗号分隔。
  • ENGINE - 设置存储引擎。
  • CHARSET - 设置编码。

7.2.2 删除表

登入MySQL中，通过SQL语句创建：

DROP TABLE <表名>;

示例：

MariaDB [skydb]> show tables;
+-----------------+
| Tables_in_skydb |
+-----------------+
| books           |
| sky_table       |
+-----------------+
2 rows in set (0.000 sec)

MariaDB [skydb]> DROP TABLE sky_table;
Query OK, 0 rows affected (0.002 sec)

MariaDB [skydb]> show tables;
+-----------------+
| Tables_in_skydb |
+-----------------+
| books           |
+-----------------+
1 row in set (0.000 sec)

7.2.3 查询表

查看库中所有表：

MariaDB [skydb]> SHOW TABLES;
+-----------------+
| Tables_in_skydb |
+-----------------+
| books           |
+-----------------+
1 row in set (0.000 sec)

查看一个某个表头的详情：

MariaDB [skydb]> DESCRIBE books;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| BookID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| Title    | varchar(100) | NO   |     | NULL    |                |
| SeriesID | int(11)      | YES  |     | NULL    |                |
| AuthorID | int(11)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

DESCRIBE 可以简写为 DESC。

7.3 数据操作

7.3.1 插入数据

INSERT INTO books (Title, SeriesID, AuthorID)
VALUES ("Lair of Bones", 2, 2);

7.3.2 查询数据

语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

Clause - 从哪个表里找。

LIMIT N - 设定返回的记录数。

OFFSET M - 查询数据的偏移量，默认为0。

效果：

MariaDB [skydb]> SELECT * FROM books;
+--------+---------------+----------+----------+
| BookID | Title         | SeriesID | AuthorID |
+--------+---------------+----------+----------+
|      1 | Lair of Bones |        2 |        2 |
|      2 | Lair of Bones |        2 |        2 |
+--------+---------------+----------+----------+
2 rows in set (0.000 sec)

因为我插入了两次数据，所以有两条一样的，留着下面更改的时候用。

7.3.3 更改数据

将7.3.2中BookID为2的Title改为 I'm SkyBiuBiu

SQL语法：

UPDATE books 
SET Title = "I'm SkyBiuBiu" 
WHERE BookID = 2;

效果：

MariaDB [skydb]> SELECT * FROM books;
+--------+---------------+----------+----------+
| BookID | Title         | SeriesID | AuthorID |
+--------+---------------+----------+----------+
|      1 | Lair of Bones |        2 |        2 |
|      2 | I'm SkyBiuBiu |        2 |        2 |
+--------+---------------+----------+----------+
2 rows in set (0.000 sec)
posted @ 2021-07-02 20:39  SkyBiuBiu  阅读(247)  评论(0)  编辑  收藏  举报