MySQL必會

来源:https://www.cnblogs.com/SutCoderHang/archive/2018/09/25/9703527.html
-Advertisement-
Play Games

SQL語言對大小寫不敏感，但一般使用大。1.創建資料庫 CREATE DATABASE test; 2.授予許可權 CRANT ALL ON test.* to user(s); 3.使用指定資料庫 USE test; 4.刪除資料庫（可刪除資料庫里所有的表數據，並將其從系統中刪除） DROP DAT ...


SQL語言對大小寫不敏感，但一般使用大。
1.創建資料庫
CREATE DATABASE test;

2.授予許可權
CRANT ALL ON test.* to user(s);

3.使用指定資料庫
USE test;

4.刪除資料庫（可刪除資料庫里所有的表數據，並將其從系統中刪除）
DROP DATABASE test;

5.創建一個字元串列，login，兩個整形列，userid、projid
CREATE TABLE user (login VARCHAR(8),userid INT,projid INT);

6.刪除資料庫中的一個表，並清空其中的所有數據
DROP TABLE users;

7.向資料庫插入一個新行，需要指定表名以及其中每列的值。
INSERT INTO users VALUES('leanna',211,1);

8.更新行
UPDATE users SET projid=4 WHERE projid=2;
UPDATE users SET projid=1 WHERE userid=311;
修改以及存在的行用UPDATE語句。使用SET來確定需要修改的列，並提供條件
來確定修改的行。在一個例子，所有的 project id為2的改為4.
在第二個例子中，將UID為311的用戶移動到編號為#1的項目組中。

9.刪除行
DELETE FROM users WHERE projid=%d;
DELETE FROM users;
例子1刪除指定行，例子2沒指定，將所有行刪除。

******************************************************************************

例：
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘寶 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鳥教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+--------------+---------------------------+-------+---------+

10.從資料庫中選取數據
SELECT name,country FROM Websites;
從Websites選取name,country
SELECT *FROM websites;
從Websites選取所有列。

11. SELECT DISTINCT country FROM Websites;
從websites選country取出唯一不同的值，也就是說去除重覆的。

12.WHERE 用於過濾
SELECT * FROM websites WHERE country ='CN';
從websites取出，country為CN所有國家的網站。

13.''單引號只用作文本，如果是數字不可以用引號。
SELECT *FROM websites WHERE id=1;

14.AND 和 OR 運算符
在使用WHERE可以配上AND 和 OR以增加過濾條件
SELECT *FROM websites WHERE contry='CN' AND alexa > 50;
SELECT *FROM websites WHERE alexa >50 AND(country='CN' OR country='USA' );

15.ORDER BY 用於對結果集排序
ORDER BY 預設從小到大，若想從大到小，需在後面加上DESC
SELECT * FROM websites ORDER BY alexa;
按照alexa從小到大排序
SELECT * FROM websites ORDER BY alexa DESC;
按照alexa從大到小排序
SELECT * FROM websites ORDER BY country,alexa;
按照country和SELECT進行排序
16.INSERT INTO插入語句
不在指定列插入
INSERT INTO websites(name, url, alexa, country) VALUES('百度','https://www.baidu.com/','4','CN');
在指定列中插入行
INSERT INTO websites(name, url,country) VALUES('百度','https://www.baidu.com/',CN');

17.UPDATE 更新數據
UPDATE websites
SET alexa='5000',country='USA'
WHERE name='菜鳥教程';
如果不加WHERE會把表裡所有所有alexa和country更改

18.DELETE 刪除數據
DELETE FROM websites WHERE name='百度' AND country='CN';
如果要將所有行刪除，則不需加WHERE。這個時候會保留表的結構、屬性、索引。
DELETE FROM table_name;
DELETE * FROM table_name;

************************************************************************
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
SQL函數

19.SQL COUNT()函數 函數返回指定列值的數目
SQL COUNT(*)函數 返回所有行的數目
SQL COUNT(DISTINCT) 返回指定列不同行的數目

SELECT COUNT(count) AS nums FROM access_log //括弧里的count什麼意思。
WHERE site_id=3;
求sit_id=3時總訪問量
SELECT COUNT(*) AS nums FROM access_log;
求"access_log" 表中總記錄數
SELECT COUNT(DISTINCT site_id) As nums FROM access_log;

20.SQL SUM()函數 求 指定數字的那一列(count) 的總數。
SELECT SUM(count) As nums FROM access_log;

21.SQL SELECT LIMIT語句
從websites庫中選取頭兩條
SELECT * FROM websites LIMIT 2;
當指定了位置偏移量時，從websites庫第3條起取4條時,預設偏移量為0
SELECT * FROM websites LIMIT 1,4;

22.SQL SELECT TOP 語句 也表示取前多少數據
TOP PERCENT:SELECT TOP 50 PERCENT * FROM websites;
表示從websites取前百分之50

23.SQL GROUP BY 語句用於結合聚合函數，根據一個或多個列對結果集進行分組。
SELECT site_id,SUM(count) As nums FROM access_log GROUP BY sit_id;


24.SQL HAVING 語句 與WHERE關鍵字對比去記，因為WHERE無法與聚合函數一起使用。HAVING 子句可以讓我們篩選分組後的各組數據。

25.WHERER 與 SELECT連用，在查詢數據時做篩選的；在篩選的時候，除了用'=' '>' '<' and or等條件判斷之外
還可以使用：'in' 'BETWEEN 1 AND 20' 表示範圍。

26.在查詢中涉及超過一個表，在查詢中使用了函數，列名稱很長或者可讀性差，需要把兩個列或者多個列結合在一起
以上四個條件適合將表或列改為別名。
AS +name
具體用法：SELECT name AS n, country AS c FROM Websites;
就是查詢表Websites，將name列與country更名為n或c在輸出。同理表的名也可如此更改。AS一般放FROM後使用
SELECT w.name, w.url, a.count,
a.date FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鳥教程";


27.SQL INNER JOIN 連接兩個表格
找到兩個表格的公共部分，一般都是id。將兩個表格連接成一個表格。並將將所需要信息提取出來。
SELECT websites.name,access_log.count, access_log.date
FROM websites INTER JOIN access_log
ON websites.id = access_log.site_id
ORDER BY access_log.count;
28.SQL CREATE DATABASE 語句 創建一個庫
CREATE DATEBASE my_db； 創建一個名為 "my_db" 的資料庫

29.SQL CREATE TABLE 語句 創建一個表
現在想要創建一個名為 "Persons" 的表，包含五列：PersonID、LastName、FirstName、Address 和 City。
CREATE TABLE Persons
(
【column_name1 data_type(size),】
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

30.LIKE 與WHERE 用於篩選字元串
'%a' //以a結尾的數據

'a%' //以a開頭的數據

'%a%' //含有a的數據

‘_a_’ //三位且中間字母是a的

'_a' //兩位且結尾字母是a的

'a_' //兩位且開頭字母是a的
SELECT * FROM Websites
WHERE name LIKE '%k';
輸出name以k結尾的的行。

--------------------------------------------
1.事物---->保證業務邏輯的完整性
四大特性ACID
1)原子性：資料庫操作是不可分割的，要麼全做完，要麼不執行
2)一致性：資料庫操作的順序與結果無關
3)隔離性：加鎖，不受其他事物干擾
4)持久性：任意提交事物，系統保證改變不被丟失，即使資料庫出現故障。

2.資料庫引擎

* 引擎:資料庫操作的底層代碼，內部設計數據結構與演算法

3.使用事物的情況：當數據被更改時：insert update delete
* 表鎖--------必須等到一個人操作完瞭解鎖以後另一個人才能操作
* 行鎖--------允許多個人操作一張表，即只要不操作被鎖的那一行即可。
* begin開始事物------>上鎖----->sql語句---------->產生記憶體級臨時表
* commit------------->將剛剛臨時表裡的數據轉移到資料庫
* rollback-------->撤銷剛剛操作

4.索引----索引是一種加快檢索速度的數據結構
* 數據預設是按照主鍵索引存儲的。
* 索引的分類：
按照建索引的方式
單列索引，即索引只包含一個類
組合索引，即一個索引包含多個列

聚集索引
非聚集索引
* 使用索引的缺點：
建立太多了索引會導致物理開銷太大，即會占用太多的記憶體空間。
增加了對錶的維護成本。在對錶更改時，不僅要保存數據，還有保存索引文件。
* 對某個列建索引，儘量讓這個有比較間單的數據類型，越小處理越快，避開NULL
* 沒有索引WHERE一行一行查，有了索引一步到位。
* 對於範圍查找的欄位索引可能會失效，等值往前寫，範圍往後寫，儘量不用or
* 查看索引 show index from 欄位名;
* 創建索引 create index 索引名 on 表名(欄位名(欄位長度)) 數字可以不用指定長度
* 刪除索引 DROP index [索引名] on 表名

5.最左首碼原則
如果對column1，column2，column3建立了聯合索引，那麼在使用該索引時只有三種組合，它們分別是：column1 、column1 and column2、column1 and column2 and column3，
概括起來就是想要使用右邊的索引，必須用上左邊的所有索引。如果只使用column2作為where的查詢條件，將不會用到所建好的索引。這是為什麼呢？請看表2。這就好比直接用月薪作為查詢條件，
有沒有發現月薪是呈全局亂序的狀態，儘管它是局部有序的。
如果業務上要求只能用月薪來查詢，可行的解決辦法是在建立聯合索引時把月薪放在最左邊，或者直接建立單列索引。
建立聯合索引時，數據粒比較小的放大左邊

6.索引的本質其實就是新建了一張表，而表本質上的數據結構就是樹形結構，所以索引也是樹形結構

7.聚集索引：聚集索引的順序就是數據存儲的物理順序，一個表只能包含一個聚集索引。所以查找更快
非聚集索引：非聚集索引存儲在一個地方，數據存儲在另一個地方，索引帶有指針指向數據存儲位置。
索引中的項目按引健值的順序存儲，而表中的信息按另一種順序存儲。
InnoDB的數據文件本身就是索引文件。從上文知道，MyISAM索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。
8.聚合
COUNT\MAX\MIN\（id） SUM\AVG---必須是（int 或 double）
原始數據看到了，只能看結果。
9.子查詢，查詢裡面嵌套查詢
10.分組
分組一般與聚合連用。分組的目的為了更好的統計，非聚合欄位不能出現結果集。
分組依據一般是一個欄位里相同的會被分到一塊去
11.分組後篩選用WHERE
WHERE對原始集進行篩選，HAVING 對分組後的結果集進行篩選。

12.關聯表查詢
連接分為內鏈接inner JOIN、左連接 left JOIN 、 右right JOIN
三種連接的差異就在於結果集能顯示出來的數據不同，使用內鏈接，結果集只能顯示出兩個表匹配上的信息。
使用left join 在顯示兩個表匹配上信息的基礎上，額外還會增加的是左邊表獨有的信息，沒匹配上的用NULL
使用right join 在顯示兩個表匹配上信息的基礎上，額外還會增加的是右邊表獨有的信息，沒匹配上的用NULL

具體操作方式 SELECT 查詢的信息用.表示 FROM tablename1
INNER JOIN/LAFT JOIN/ RIGHT JOIN ON tablename1. = tablename2.

13.查詢語句順序
先結果集——然後關聯——然後WHERE 篩選——然後GROUP BY/ORDER BY ——然後HVING 對分組過的結果集篩選

14.分頁用limit 每頁m條數據，當前顯示n頁
limit n *m ，m n從0開始

15.一索引二級索引
一級索引：
innoDB會自動建立聚集索引，即將數據與索引存放在一起。使用B+tree的數據結構，將數據存儲在葉子結點，
各個葉子結點通過指針相連。
1) InnoDB通常根據主鍵值(primary key)進行聚簇
2) 如果沒有創建主鍵，則會用一個唯一且不為空的索引列做為主鍵，成為此表的聚簇索引-----自增id
3) 上面二個條件都不滿足，InnoDB會自己創建一個虛擬的聚集索引，隱藏。
輔助索引：
在聚簇索引之上創建的索引稱之為輔助索引，輔助索引訪問數據總是需要二次查找。輔助索引葉子節點存儲的不再是行的物理位置，而是主鍵值。
通過輔助索引首先找到的是主鍵值，再通過主鍵值找到數據行的數據葉，再通過數據葉中的Page Directory找到數據行。
即二級索引是我們後建的，它只存儲了部分的數據。如果根據鍵值查找到的數據不能包括全部目標數據時
（就是無法使用到覆蓋索引），就需要通過二級索引的指針，也就是鍵值對中的值，來找到聚簇索引的全部數據，
然後根據完整的數據取出所需要的列的過程就稱之為回表。

16.行級鎖和頁級鎖會出現死鎖，表鎖不會。
表鎖的好處是上鎖解鎖快，並且能避免出現死鎖。

17.使用鎖是為了保持數據的一致性，但要避免死鎖，死鎖是兩個或多個事物在同一資源上相互占用，並請求鎖定對方的資源，
從而導致惡性迴圈的現象。
常見的解決死鎖的方法

1、如果不同程式會併發存取多個表，儘量約定以相同的順序訪問表，可以大大降低死鎖機會。

2、在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；

3、對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；

如果業務處理不好可以用分散式事務鎖或者使用樂觀鎖

18.樂觀鎖與悲觀鎖
悲觀鎖：
悲觀鎖指對數據被意外修改持保守態度，依賴資料庫原生支持的鎖機制來保證當前事務處理的安全性，
防止其他併發事務對目標數據的破壞或破壞其他併發事務數據，
將在事務開始執行前或執行中申請鎖定，執行完後再釋放鎖定。這對於長事務來講，
可能會嚴重影響系統的併發處理能力。 自帶的資料庫事務就是典型的悲觀鎖。
樂觀鎖：
樂觀鎖（Optimistic Lock），顧名思義，就是很樂觀，每次去拿數據的時候都認為別人不會修改，所以不會上鎖，
但是在提交更新的時候會判斷一下在此期間別人有沒有去更新這個數據。樂觀鎖適用於讀多寫少的應用場景，這樣可以提高吞吐量。
一般是加一個版本號欄位 每次更新時候比較版本號

19.innodb的主鍵是主索引，MyISAM的主鍵與輔助沒什麼區別，存的是數據的地址。
所以InnoDB要求表必須有主鍵（MyISAM可以沒有）InnoDB的所有輔助索引都引用主鍵作為data域。
聚集索引搜索是很高效的，而輔助索引必須遍歷兩遍。遍歷第一遍獲得主鍵，根據主鍵檢索。

 


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

-Advertisement-
Play Games
更多相關文章
  • https協議詳解
    HTTPS協議建立過程 1) 客戶端首次發送請求時，由於客戶端（瀏覽器等）對一些加解密演算法的支持程度不一樣，但是在TLS傳輸中必須使用相同的加解密演算法，所以在TLS握手的階段，客戶端告訴伺服器端自己支持的加密演算法（加密套件list），客戶端產生一個隨機數存在客戶端，並且傳送給伺服器，客戶端的隨機數要 ...
  • Linux編程 19 編輯器(vim 用法)
    一．概述 在開啟shell腳本編程之前，必須要知道一款文本編輯器的用法，如文本編輯的查找，剪切，粘貼，定位等, 本篇只講vim編輯器。vim編輯器全名叫vi improved，是經過對Unix系統vi編輯器的一些改進而來，vi編輯器是Unix系統最初的編輯器。 1.1 檢查vim軟體包 下麵分析vi ...
  • 升級Linux中的JAVA版本
    首先查看本機中的JAVA版本 如何需要卸載掉現有的JAVA版本的話，可以使用rpm -qa | grep java 和 rpm -e xxx --nodeps進行卸載 登錄到JAVA官方下載界面，提供了rpm包和tar.gz包兩種包。rpm包的話直接安裝就可以了，不用做修改。tar.gz包需要修改環 ...
  • tcpdump抓包具體分析
    Tcpdump抓包分析過程 一、TCP連接建立（三次握手） 過程 客戶端A，伺服器B，初始序號seq，確認號ack 初始狀態：B處於監聽狀態，A處於打開狀態 A -> B : seq = x （A向B發送連接請求報文段，A進入同步發送狀態SYN-SENT） B -> A : ack = x + 1, ...
  • 轉：tcpdump抓包分析(強烈推薦)
    轉自：https://mp.weixin.qq.com/s?__biz=MzAxODI5ODMwOA==&mid=2666539134&idx=1&sn=5166f0aac718685382c0aa1cb5dbca45&scene=5&srcid=0527iHXDsFlkjBlkxHbM2S3E#r ...
  • Linux常用命令大全（非常全！！！）
    最近都在和Linux打交道，感覺還不錯。我覺得Linux相比windows比較麻煩的就是很多東西都要用命令來控制，當然，這也是很多人喜歡linux的原因，比較短小但卻功能強大。我將我瞭解到的命令列舉一下，僅供大家參考： 系統信息 arch 顯示機器的處理器架構(1) uname -m 顯示機器的處理 ...
  • ATM-簡單SQL查詢
    End ...
  • 批量刪除MSSQL 中主外鍵約束
    轉自: http://www.maomao365.com/?p=813 在製作 MSSQL同步工具的時候,發現由於主外鍵的約束,導致數據同步異常,所有我們需要把 讀資料庫裡面的主外鍵約束,進行批量刪除操作. 1 如何批量查詢資料庫的主外鍵? 在MSSQL2005以上版本中,系統提供一個系統視圖 sy ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類