MySQL資料庫操作 Lab1

来源:https://www.cnblogs.com/cwh-top/archive/2023/09/30/17737872.html
-Advertisement-
Play Games

1. 複製切換 1.1. 複製是高可用性的基礎 1.1.1. 總是保留一份持續更新的副本數據，會讓災難恢復更簡單 1.2. “切換副本”（promoting a replica）和“故障切換”（failing over）是同義詞 1.2.1. 意味著源伺服器不再接收寫入，並將副本提升為新的源伺服器 ...


          實驗一 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

    • img

    原因分析:

    1. 通常表示在伺服器端和客戶端都沒有啟用載入本地數據的功能。這可能是由於安全設置或配置限制所致
    2. 一般情況下是 local_infile=OFF 導致不能從本地導入
    3. 未啟用的話會導致 Mysql對其安全性的保護，從而拒絕連接
        show global variables like 'local_infile';
    -- 表示找到全局配置中的local file文件所在

    顯然會顯示 OFF

    img
    需要對其進行修改為 true

    SET GLOBAL local_infile = true; -- 將local_file的值修改為 ON

    img
    值得註意的是 在我們重啟之後依舊是OFF，所有我們需要對my.ini進行修改
    my.ini是在Window下麵的文件

    Docker和Linux中的文件是叫my.cnf，文件路徑在/etc/mysql/my.cnf

    img

    這裡增加配置 copy到配置文件就可以了

    [mysql]
local_infile=ON
[client]
local_infile=ON

    這個樣子我們就對其local_file的讀許可權打開了

  • b. secure-file-priv 所示文件路徑對讀取的位置限制問題

    • img

    img

    原因分析:

    如果是這個樣子的 我們不能直接對全局的文件進行隨意讀取
    否則會報錯，mysql為保護安全性而設置的，這裡我們需要進入配置文件 mysql.ini
    MySQL伺服器配置了--secure-file-priv選項，限制了從文件載入數據的操作
    本質是為了增加資料庫的安全性而採取的一項措施。

    解決方法

    1. 選擇一個允許載入數據的目錄：可以通過修改MySQL的配置文件，將--secure-file-priv選項設置 為一個允許載入數據的目錄。你可以在配置文件中找到該選項並修改它。修改後，重新啟動 MySQL伺服器使更改生效 解決方法
    2. 將數據文件移動到允許的目錄：另一種方法是將數據文件移動到MySQL允許載入數據的目錄 中。你可以通過查看MySQL配置文件中--secure-file-priv選項指定的目錄，或者使用以下SQL查詢 來獲取允許的目錄位置

    這裡我使用修改 --secure-file-priv的方法 找到my.ini配置文件 修改secure_file_priv

    img

    用管理員許可權 覆蓋my.ini

    img

    show variables like "secure_file_priv"; -- 顯示配置路徑

    img

    這個樣子我們可以對文件進行讀取了 ，我修改了文件名字，叫test.csv

    img

    img
    這裡可以做個瞭解：

    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 ```

    img

  • 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 進行數據載入需要適當的許可權，並且在安全性方面要謹慎。確保僅從可信的來源載入數據，並仔細驗證數據的格式和內容，以避免潛在的安全問題。

    1. 在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 ;
    1. 如果我有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中您要忽略的列中.

    1. 在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);

    測試指定讀取成功 代碼演示

    數據表
    img
    實現代碼

    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; -- 讀取表數據並指定

    實現結果

    img

    img


    至此 目前的一些問題全部被解決 接下來是這次實驗報告的具體實現

    1、連接並登錄MySQL客戶端,併在終端進行操作

    img

    2、創建資料庫LearnMySQL，按照給定Excel文件中表定義創建各數據表，建立表的各約束關係，創建索引。（創建表、約束關係、索引的SQL命令寫在下麵的空白處）


    1. 建立資料庫LearnMySQL 
          create database if not exists LearnMySQL;
    use LearnMySQL;

      img


    2. 建立表的結構

      這裡我設計的是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
);

    3. 創建各表的約束關係 
      -- 創建外鍵約束關係
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);

    4. 創建索引 
      -- 創建索引
CREATE INDEX idx_course_did ON Course (did);

img


3、把給定Excel文件中的數據錄入（導入）到各數據表。

因為給定實驗數據表有點問題 我這裡自己造了組數據

img

這裡我們使用 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;

img

img


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);

img

基於全文索引的三個查詢示例：

  1. 查找包含關鍵詞"Science"的課程：
SELECT * FROM honor WHERE MATCH(cattr) AGAINST('Science');

img

  1. 查找屬於"General Education"類別且學分大於等於3的課程：
SELECT * FROM honor WHERE MATCH(cattr) AGAINST('General Education') AND credit >= 3;

img

  1. 查找屬於"Social Science"類別且教學單位編號為6的課程：
SELECT * FROM honor WHERE MATCH(cattr) AGAINST('Social Science') AND did = 6;

img


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 聲明式調用 —— SpringCloud OpenFeign
    Feign 簡介 Spring Cloud Feign 是一個 HTTP 請求調用的輕量級框架，可以以 Java 介面註解的方式調用 HTTP 請求，而不用通過封裝 HTTP 請求報文的方式直接調用 Feign 通過處理註解，將請求模板化，當實際調用的時候傳入參數，根據參數再應用到請求上，進而轉化成 ...
  • Get請求和Post請求
    Get請求和Post請求都是HTTP協議中的兩種常見請求方法，底層都是TCP/IP協議，用於客戶端與伺服器之間的數據傳輸。 ...
  • 洛谷題解 | AT_abc321_c Primes on Interval
    目錄題目翻譯題目描述輸入格式輸出格式樣例 #1樣例輸入 #1樣例輸出 #1樣例 #2樣例輸入 #2樣例輸出 #2樣例 #3樣例輸入 #3樣例輸出 #3題目簡化題目思路AC代碼 題目翻譯 【題目描述】 你決定用素數定理來做一個調查. 眾所周知, 素數又被稱為質數，其含義就是除了數字一和本身之外不能被其 ...
  • Python 布爾類型
    布爾值表示兩個值之一：True（真）或False（假）。 布爾值 在編程中，您經常需要知道一個表達式是否為True或False。 您可以在Python中評估任何表達式，並獲得兩個答案之一：True或False。 當您比較兩個值時，表達式會被評估，Python會返回布爾答案： 示例 print(10 ...
  • 【matplotlib 實戰】--面積圖
    面積圖，或稱區域圖，是一種隨有序變數的變化，反映數值變化的統計圖表。 面積圖也可用於多個系列數據的比較。這時，面積圖的外觀看上去類似層疊的山脈，在錯落有致的外形下表達數據的總量和趨勢。面積圖不僅可以清晰地反映出數據的趨勢變化，也能夠強調不同類別的數據間的差距對比。 面積圖的特點在於，折線與自變數坐標 ...
  • 6. 用Rust手把手編寫一個wmproxy(代理，內網穿透等), 通訊協議源碼解讀篇
    用Rust手把手編寫一個wmproxy(代理，內網穿透等), 通訊協議源碼解讀篇 項目 ++wmproxy++ gite: https://gitee.com/tickbh/wmproxy github: https://github.com/tickbh/wmproxy 事件模型的選取 OS線程, ...
  • 8.郵件發送功能
    這裡想補充下上個文章，感覺有點不太行。因為每次設計新的表結構就要去更新一下，所以，乾脆隨著我要做的功能去展示我的表結構設計，最終再把所有的表結構包括sql語句統計出來，感覺這樣更新會方便很多~ 這個文章主要是發送郵件的功能。之前提過，我不是一下子把後端全部完成，然後再一下子搞定後端。所以我前後端是要 ...
  • Hyper-V 安裝 CentOS 8.5
    前言 Hyper-V安裝文檔：在 Windows 10 上安裝 Hyper-V CentOS 系統下載：CentOS 國內鏡像源 8.5.2111 作者：易墨 發佈時間：2023.10.01 原文地址：https://www.cnblogs.com/morang/p/devops-hyperv-ce ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類