|
实验一 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的控制台,再使用如下命令登入:```sqlmysql -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 进行数据加载需要适当的权限,并且在安全性方面要谨慎。确保仅从可信的来源加载数据,并仔细验证数据的格式和内容,以避免潜在的安全问题。
- 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 ;
复制代码- 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);
复制代码
基于全文索引的三个查询示例:
- 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;
复制代码
来源:https://www.cnblogs.com/cwh-top/p/17737872.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作! |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?立即注册
x
|