MySQL官方文檔閱讀與記錄

来源:https://www.cnblogs.com/maoshiling/archive/2018/01/25/8321397.html
-Advertisement-
Play Games

接觸變成時間不久，之前對於MySQL的瞭解局限於簡單的CURD，沒有系統和深入的學習過，最近想要更深入的學習和瞭解一下MySQL，打算先從官方文檔入手。 最新官方文檔：https://dev.mysql.com/doc/refman/8.0/en 1. MySQL對標準SQL的擴展 (1) MySQ ...


接觸變成時間不久，之前對於MySQL的瞭解局限於簡單的CURD，沒有系統和深入的學習過，最近想要更深入的學習和瞭解一下MySQL，打算先從官方文檔入手。

最新官方文檔：https://dev.mysql.com/doc/refman/8.0/en

1. MySQL對標準SQL的擴展

(1) MySQL對標準的SQL進行了擴展，當進行遷移到其他資料庫時，SQL語句的語法可能存在不支持的情況，可以將MySQL的特定語法用如下的方式進行編寫，MySQL資料庫將會對【/*！語句*/】內的語句進行解析並執行，其他資料庫將會忽略該語句，因此可以實現遷移。

/*! MySQL-specific code */

示例：

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

(2) 不同的MySQL版本之間存在著語法的差異，因此，在特定場景下，可以指明該語句支持的MySQL語法支持的版本號

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;
-- 上述語句表明，當且僅當MySQL的版本大於等於指定版本 50110 的時候才會執行 KEY_BLOCK_SIZE=1024 語句

(3) 按類型列舉MySQL對標準SQL的擴展

  • 數據的組織方式
    • 每個資料庫對應數據目錄下的一個目錄
    • 每個表對應一個文件
    • 資料庫表名是否大小寫敏感與操作系統對文件名大小寫是否敏感有關
  • 一般語法SQL語法
    • 字元串可以用"或者'括起來，當ANSI_QUOTES啟用的時候則只能使用單引號
    • \表示轉義字元
    • 可以用db_name.tbl_name來訪問不同資料庫下的表，MySQL不支持表空間
  • 數據類型
  • 函數與操作符

2. MySQL與標準SQL的語法差異

(1) SELECT ... INTO TABLE

-- 不支持SELECT ... INTO TABLE， 可以用INSERT INTO ... SELECT 代替（待驗證），支持 SELECT ... INTO OUTFILE 和 CREATE TABLE ... SELECT.
INSERT INTO tbl_temp2 (fld_id)
    SELECT tbl_temp1.fld_order_id
    FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

(2) UPDATE


UPDATE t1 SET col1 = col1 + 1, col2 = col1;

(3) FOREIGN KEY

(4) -- 註釋

/* comment */是標準SQL的註釋，MySQL支持該語法，用於實現MySQL的特殊語法

-- 註釋的時候--和後面的註釋內容之間必須有空格
UPDATE account SET credit=credit-payment

-- 當payment等於-1時：
UPDATE account SET credit=credit--1

-- 如果將--後面的內容解析為註釋，則上面的語句相當於：
UPDATE account SET credit=credit

3. MySQL對於約束的處理

(1) 主鍵和唯一索引

當執行INSERT和UPDATE的時候將出現違反主鍵約束和唯一性約束的情況；

當表支持事務的時候，遇到違反約束的語句時將會停止執行並自動回滾；

當表不支持事務的時候，遇到違反約束的語句時將會停止執行該語句及之後的語句，已經執行的語句無法回滾

MySQL支持IGNORE關鍵字來忽略違反約束的語句並繼續執行，可以使用mysql_info() 的API或者SHOW WARNING來查看實際插入或者更新的語句數量

(2) 外鍵

Mysql支持在CREATE TABLE和ALTER TABLE的時候對外鍵進行UPADTE和DELETE，可選的操作包括：RESTRICT（預設）, CASCADE, SET NULL, NO ACTION

MySQL要求外鍵必須有索引，如果沒有索引的話將會創建自動索引

可以通過查詢 INFORMATION_SCHEMA.KEY_COLUMN_USAGE表來獲取外鍵的信息

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
     > FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
     > WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------+---------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | CONSTRAINT_NAME |
+--------------+---------------+-------------+-----------------+
| fk1          | myuser        | myuser_id   | f               |
| fk1          | product_order | customer_id | f2              |
| fk1          | product_order | product_id  | f1              |
+--------------+---------------+-------------+-----------------+
3 rows in set (0.01 sec)

 

(3) 無效數據

預設情況下，MySQL將會對非法的輸入值進行強制處理使其符合規範，可以啟用strict SQL mode

(4) 枚舉和集合

ENUM枚舉類型要求輸入的數據必須是已定義的數據之一

SET集合類型要求輸入的數據必須是空字元串或者或者由已定義的元素組成

當strict mode啟用的時候，將會拒絕並拋出錯誤

 

ENUM('a','b','c') -- 值''、'd'、'ax'均為無效的錯誤數據
SET('a','b','c') -- 值'd'、'a,b,c,d'均為無效的錯誤數

 

在strict mode情況下，可以用INSERT IGNORE或者UPDATE IGNORE來忽略錯誤，對於ENUM類型的非法數據將會插入非法數據0，對於SET類型數據將會插入去除非法字元之後的數據

 3.Tutorial

(1) MySQL提示符的含義

提示符 含義
mysql> 可以輸入下一條執行語句
> 等待繼續輸入
'> 等待以單引號開始的字元串的結尾
"> 等待以雙引號開始的字元串的結尾
`> 等待以`開始的字元串的結尾
/*>　　

等待以/*開始的註釋的結束*/

(2) 創建並使用database

-- 查看所有的資料庫
show databases；

-- 使用某一個資料庫，use和其他的語句不同，不需要在結尾處加分號，但是必須在一行內輸完，不能換行
use test

-- 創建資料庫
CREATE DATABASE menagerie;

-- 登陸並連接使用資料庫
mysql -h host -u user -p menagerie

-- 查看當前使用的資料庫
SELECT DATABASE();

-- 查看資料庫下有哪些表
SHOW TABLES;

-- 創建表
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

-- 查看表定義
describe pet；

-- 從文件中導入數據到表中
-- pet.txt內容為：Whistler Gwen bird \N 1997-12-09 \N
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

-- 當文件中行結尾的分隔符為\n\r的時候需要特別的標明
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';
-- 當從文件中load數據失敗時（ERROR 1148 (42000): The used command is not allowed with this MySQL version），需要查詢是否啟用了該功能，預設情況下是關閉的
show variables like '%LOCAL%';

-- 可以看到local_infile變數，預設情況下為OFF，設置該屬性為ON，註意：使用這種方式修改後仍無法導入數據
SET global local_infile=1；

-- 需要在與資料庫伺服器建立連接的時候，設置該連接數據
mysql -h host -u user --local_infile -p menagerie

-- 插入數據
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

-- 查詢數據
SELECT * FROM pet;

-- 刪除所有數據
DELETE FROM pet;

-- 更新數據
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

-- 查詢符合條件的數據
SELECT * FROM pet WHERE name = 'Bowser';
SELECT * FROM pet WHERE birth >= '1998-1-1';
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');

-- 排序
SELECT name, birth FROM pet;
SELECT owner FROM pet;
SELECT DISTINCT owner FROM pet;
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';

MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
-- 時間日期計算

SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

SELECT name, birth, MONTH(birth) FROM pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT name, birth FROM pet WHERE

 

-- 對NULL值的支持，NULL表示值缺失

-- 0和NULL代表false，其他均表示true
SELECT 1 IS NULL, 1 IS NOT NULL;
-- 任何數據和NULL進行=、<>、>、<均為NULL
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
-- 空字元串和NULL是不一致的
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

 

-- 模式匹配
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
-- 查找名稱長度為5個字元的數據
SELECT * FROM pet WHERE name LIKE '_____';

-- 正則表達式匹配
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');

-- 當需要大小寫敏感的時候需要指定大小寫敏感的字元集
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');

 

-- 統計記錄數目
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
 SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;

 

-- 多表操作
SELECT pet.name,TIMESTAMPDIFF(YEAR,birth,date) AS age,remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

 

 

 

 

 

 

 

 

 

 

 

　　

 


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

-Advertisement-
Play Games
更多相關文章
  • MySQL-索引詳解
    索引是用來快速檢索出具有特定值的記錄。如果沒有索引，資料庫就必須從第一條記錄開始進行全表掃描，直到找出相關的行。數據越多，檢索的代價就越高，檢索時如果表的列存在索引，那麼MySQL就能快速到達指定位置去搜索數據文件，而不必查看所有數據。 概述 索引依托於存儲引擎的實現，因此，每種存儲引擎的索引都不一 ...
  • mysql索引的註意事項
    索引的優點 大大加快數據的查詢速度 使用分組和排序進行數據查詢時，可以顯著減少查詢時分組和排序的時間 創建唯一索引，能夠保證資料庫表中每一行數據的唯一性 在實現數據的參考完整性方面，可以加速表和表之間的連接 索引的缺點 創建索引和維護索引需要消耗時間，並且隨著數據量的增加，時間也會增加 索引需要占據 ...
  • vim撤銷undo與反撤銷redo
    普通模式下 u 撤銷 ctrl + r 反撤銷 ...
  • Apache Sentry部署
    三台hadoop集群，分別是master、slave1和slave2。下麵是這三台機器的軟體分佈： master：NameNode、ZK、HiveMetaSotre、HiveServer2、SentryServer slave1：DataNode、ZK slave2：DataNode、ZK 2 軟體 ...
  • Hive_UDF函數中集合對象初始化的註意事項
    UDF函數中定義的集合對象何時初始化 udf函數放在sql中對某個欄位進行處理，那麼在底層會創建一個該類的對象，這個對象不斷的去調用這個evaluate(...)方法，截圖如下： 1.1 如果說對於每一條傳入UDF中需要處理的數據都需要全新的集合對象，那麼這個時候集合對象就需要在類中聲明，在eval ...
  • SQL Server（MySql）中的聯合主鍵（聯合索引） 索引分析
    聯合索引概念：當系統中某幾個欄位經常要做查詢，並且數據量較大，達到百萬級別，可多個欄位建成索引 使用規則： 1.最 左 原則，根據索引欄位，由左往右依次and（where欄位很重要，從左往右） 2.Or 不會使用聯合索引 3.where語句中查詢欄位包含全部索引欄位，欄位順序無關，可隨意先後... ...
  • BugFree後臺統計Bug信息
    以下為二維表信息 //統計嚴重等級Bug SELECT severity,count(severity) FROM `bf_bugview` where product_id=476 GROUP BY severity //統計創建者Bug SELECT created_by_name,count( ...
  • oracle中RAW數據類型
    近日在研究v$latch視圖時，發現一個從未見過的數據類型。v$latch 中ADDR屬性的數據類型為RAW(4|8) 同時也發現v$process中的ADDR屬性的數據類型也為RAW(4|8)。於是查了一下oracle 的SQL Language Reference文檔，文檔如下描述： The R ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類