MySQL数据库操作 Lab1
实验一 MySQL数据库操作
实验目的：
掌握MySQL安装、配置与登录方法，使用MySQL客户创建数据库及对数据库表完成各种操作
实验内容：
1、 安装MySQL数据库管理系统，5.7.X（建议5.7.23及以上）或8.X版本都可以。客户端不限。
2、 使用MySQL客户端创建数据库，并且在库中按照设计创建数据库表，并把数据插入各表中。
实验过程及要求：
1、学习并掌握MySQL的登录方法，能够使用官方的MySQL客户端（或者其他能够连接MySQL的客户端）连接并登录到MySQL服务器，能够在服务器上进行各种操作。
2、创建数据库LearnMySQL，按照给定Excel文件中表定义创建各数据表，建立表的各约束关系，创建索引。（创建表、约束关系、索引的SQL命令写在下面的空白处）
3、把给定Excel文件中的数据录入（导入）到各数据表。
4、往数据库的honor表中插入至少20条以上有效记录，在该表上创建全文索引。
（1）使用Select命令列出honor表所有记录，结果贴在下面。
（2）写出创建全文索引的过程与对应命令。
（3）基于全文索引写至少3条查询语句，并且贴出查询结果。
实现操作过程
前提说明 -> 在操作过程中遇到的一些小问题，以及这些问题的解决方法:
- a. mysql 报错：Loading local data is disabled； this must be enabled on both the client and
- 通常表示在服务器端和客户端都没有启用加载本地数据的功能。这可能是由于安全设置或配置限制所致
- 一般情况下是 local_infile=OFF 导致不能从本地导入
- 未启用的话会导致 Mysql对其安全性的保护，从而拒绝连接
原因分析:
show global variables like 'local_infile';
-- 表示找到全局配置中的local file文件所在
显然会显示 OFF
需要对其进行修改为 true
SET GLOBAL local_infile = true; -- 将local_file的值修改为 ON
值得注意的是 在我们重启之后依旧是OFF，所有我们需要对my.ini进行修改
my.ini是在Window下面的文件
Docker和Linux中的文件是叫my.cnf，文件路径在/etc/mysql/my.cnf
这里增加配置 copy到配置文件就可以了
[mysql]
local_infile=ON
[client]
local_infile=ON
这个样子我们就对其local_file的读权限打开了
b. secure-file-priv 所示文件路径对读取的位置限制问题
原因分析:
如果是这个样子的 我们不能直接对全局的文件进行随意读取
否则会报错，mysql为保护安全性而设置的，这里我们需要进入配置文件 mysql.ini
MySQL服务器配置了--secure-file-priv选项，限制了从文件加载数据的操作
本质是为了增加数据库的安全性而采取的一项措施。
解决方法
- 选择一个允许加载数据的目录：可以通过修改MySQL的配置文件，将--secure-file-priv选项设置 为一个允许加载数据的目录。你可以在配置文件中找到该选项并修改它。修改后，重新启动 MySQL服务器使更改生效 解决方法
- 将数据文件移动到允许的目录：另一种方法是将数据文件移动到MySQL允许加载数据的目录 中。你可以通过查看MySQL配置文件中--secure-file-priv选项指定的目录，或者使用以下SQL查询 来获取允许的目录位置
这里我使用修改 --secure-file-priv的方法 找到my.ini配置文件 修改secure_file_priv
用管理员权限 覆盖my.ini
show variables like "secure_file_priv"; -- 显示配置路径
这个样子我们可以对文件进行读取了 ，我修改了文件名字，叫test.csv
这里可以做个了解：
secure_file_priv="null" -- 对全局所有的文件都不能读取
secure_file_priv="指定路径" -- 只能读取当前指定路径下的文件夹内容 也就是信任
secure_file_priv="" -- 对读取文件夹位置不做限制
c. ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
先quit出MySQL的控制台，再使用如下命令登入：
```sql
mysql -u root -p --local-infile
```
d. 对文件的指定列进行选择性读入，一些非法情况引发的ERROR
首先先了解对参数的认识，这些是LOAD DATA 语法的介绍
load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name, )]
这个 是ChatGPT 给出的解释
LOAD DATA INFILE` 是一个 MySQL 的语句，用于将外部数据文件加载到数据库表中。
[low_priority] -- （可选）：使用低优先级执行加载操作，以允许其他操作在加载过程中进行。
[local] -- （可选）：指定数据文件位于本地客户端而不是服务器上。
infile 'file_name.txt' -- ：指定要加载的数据文件的路径和名称。
[replace|ignore] -- （可选）：指定当加载的数据与表中的数据冲突时的处理方式。`REPLACE` 表示替换冲突的数据，`IGNORE` 表示忽略冲突的数据。
into table tbl_name -- ：指定要加载数据的目标表的名称。
[fields terminated by 't'] -- （可选）：指定字段之间的分隔符。默认为制表符。
[OPTIONALLY] enclosed by '' -- （可选）：指定字段值的包围符号。默认为空。
[escaped by '\''] -- （可选）：指定转义字符。默认为反斜杠。
[lines terminated by 'n'] -- （可选）：指定行之间的分隔符。默认为换行符。
[ignore number lines] -- （可选）：指定要忽略的行数。
[(col_name, )] -- （可选）：指定要加载的列的列表。如果省略，则默认加载所有列。
这些参数允许你根据数据文件的格式和需求来配置加载操作。你可以根据实际情况提供适当的参数。
请注意，使用 LOAD DATA INFILE 进行数据加载需要适当的权限，并且在安全性方面要谨慎。确保仅从可信的来源加载数据，并仔细验证数据的格式和内容，以避免潜在的安全问题。
- 在MySQL中将数据加载到表中并指定列：
LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE t1
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(@col1,@col2,@col3,@col4) set name=@col4,id=@col2 ;
- 如果我有100列并且我只想导入2列
LOAD DATA INFILE 'file.csv'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3, ...)
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
只需将column1,column2等替换为您的列名,然后将@dummy放在CSV中您要忽略的列中.
- 在load data infile语句中指定CSV中的列名称.
LOAD DATA INFILE '/path/filename.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(column_name3, column_name5);
测试指定读取成功 代码演示
数据表
实现代码
create table test1 (
pid varchar(255)
); -- 建立测试表
LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\FILE_Data\\CSV\\test.csv'
INTO TABLE test1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@col1, @col2, @col3, @col4, @col5,@col6,@col7,@col8)
set pid=@col2; -- 读取表数据并指定
实现结果
至此 目前的一些问题全部被解决 接下来是这次实验报告的具体实现
1、连接并登录MySQL客户端,并在终端进行操作
2、创建数据库LearnMySQL，按照给定Excel文件中表定义创建各数据表，建立表的各约束关系，创建索引。（创建表、约束关系、索引的SQL命令写在下面的空白处）
-
建立数据库LearnMySQL
create database if not exists LearnMySQL; use LearnMySQL; -
建立表的结构
这里我设计的是3NF的结构
-- 创建 Course 表 CREATE TABLE Course ( cid INT PRIMARY KEY, cname VARCHAR(255), credit INT, pcid INT, chour INT, cattr VARCHAR(255), cnum INT, did VARCHAR(2) ); -- 创建 Department 表 CREATE TABLE Department ( did VARCHAR(2) PRIMARY KEY, dname VARCHAR(255) ); -- 创建 CourseHour 表 CREATE TABLE CourseHour ( chour INT PRIMARY KEY, cid INT ); -
创建各表的约束关系
-- 创建外键约束关系 ALTER TABLE Course ADD CONSTRAINT fk_course_pcid FOREIGN KEY (pcid) REFERENCES Course (cid); ALTER TABLE CourseHour ADD CONSTRAINT fk_coursehour_cid FOREIGN KEY (cid) REFERENCES Course (cid); -
创建索引
-- 创建索引 CREATE INDEX idx_course_did ON Course (did);
3、把给定Excel文件中的数据录入（导入）到各数据表。
因为给定实验数据表有点问题 我这里自己造了组数据
这里我们使用 LOAD DATA 的方法
LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\FILE_Data\\CSV\\test.csv'
INTO TABLE course
FIELDS TERMINATED BY ',' -- 列分割
LINES TERMINATED BY '\n' -- 行分割
IGNORE 1 LINES ;
LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\FILE_Data\\CSV\\test.csv'
INTO TABLE CourseHour
FIELDS TERMINATED BY ',' -- 列分割
LINES TERMINATED BY '\n' -- 行分割
IGNORE 1 LINES
(@col1, @col2, @col3, @col4, @col5,@col6,@col7,@col8)
set chour=@col5,cid=@col2;
LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\FILE_Data\\CSV\\test.csv'
INTO TABLE Department
FIELDS TERMINATED BY ',' -- 列分割
LINES TERMINATED BY '\n' -- 行分割
IGNORE 1 LINES
(@col1, @col2, @col3, @col4, @col5,@col6,@col7,@col8)
set did=@col8,dname=@col2;
4、往数据库的honor表中插入至少20条以上有效记录，在该表上创建全文索引。
（1）使用Select命令列出honor表所有记录，结果贴在下面。
（2）写出创建全文索引的过程与对应命令。
（3）基于全文索引写至少3条查询语句，并且贴出查询结果。
-- 创建"honor"表
CREATE TABLE honor (
cid INT,
cname VARCHAR(50),
credit INT,
pcid INT,
chour INT,
cattr VARCHAR(50),
cnum INT,
did INT,
PRIMARY KEY (cid)
);
-- 插入记录
INSERT INTO honor (cid, cname, credit, pcid, chour, cattr, cnum, did)
VALUES
(1, 'Mathematics', 3, NULL, 36, 'Science', 100, 1),
(2, 'English', 3, NULL, 48, 'General Education', 80, 2),
(3, 'Physics', 4, 1, 48, 'Science', 60, 1),
(4, 'Computer Science', 3, NULL, 36, 'Science', 120, 3),
(5, 'History', 3, 2, 36, 'General Education', 90, 2),
(6, 'Chemistry', 4, 1, 48, 'Science', 70, 1),
(7, 'Biology', 4, 1, 48, 'Science', 80, 1),
(8, 'Art', 2, NULL, 24, 'General Education', 50, 4),
(9, 'Geography', 3, 2, 36, 'General Education', 60, 2),
(10, 'Physical Education', 2, NULL, 24, 'General Education', 120, 5),
(11, 'Economics', 3, NULL, 36, 'Social Science', 70, 6),
(12, 'Music', 2, NULL, 24, 'General Education', 40, 4),
(13, 'Psychology', 3, 11, 36, 'Social Science', 60, 6),
(14, 'Sociology', 3, 11, 36, 'Social Science', 50, 6),
(15, 'Statistics', 3, 1, 36, 'Science', 80, 1),
(16, 'Literature', 3, 2, 36, 'General Education', 70, 2),
(17, 'Political Science', 3, 11, 36, 'Social Science', 60, 6),
(18, 'Philosophy', 3, NULL, 36, 'General Education', 40, 7),
(19, 'Engineering', 4, 4, 48, 'Science', 90, 3),
(20, 'Foreign Language', 3, 2, 36, 'General Education', 80, 2);
-- 在"honor"表的"cattr"列上创建全文索引
ALTER TABLE honor ADD FULLTEXT INDEX idx_cattr (cattr);
基于全文索引的三个查询示例：
- 查找包含关键词"Science"的课程：
SELECT * FROM honor WHERE MATCH(cattr) AGAINST('Science');
- 查找属于"General Education"类别且学分大于等于3的课程：
SELECT * FROM honor WHERE MATCH(cattr) AGAINST('General Education') AND credit >= 3;
- 查找属于"Social Science"类别且教学单位编号为6的课程：
SELECT * FROM honor WHERE MATCH(cattr) AGAINST('Social Science') AND did = 6;