學習筆記 | SQL中的全部常用操作,看到這篇文章就邁出學習SQL的第一步

2021-02-14 天池大數據科研平臺
首先，正式開始閱讀前請理解一點。MySQL是你正在使用的資料庫服務軟體，除了mysql也可以是Oracle、SQL Server、Access等等。SQL是你用來實現和資料庫的交互的語言。本文以mysql為例。

SQL是一個存活近半個世紀的語言，如今仍有大量人在使用。它語法簡單，對培養數據整理和提取的思維有很大幫助。我將我過去的筆記分享給大家，希望能為大家的學習提供參考，更希望有人因此能邁出學習SQL的第一步～

既然是筆記，就省去了很多介紹性定義，一切都在代碼在注釋中！這一萬字符的筆記中包含了SQL中的全部常用操作，可以邊看邊敲也可以收藏當成速查手冊隨用隨查！

mysql -h$ip -P$port -u$user -p
net start mysql # 或 mysql.server start
mysql -u root -p 
mysql -u user -p db_name # 直接進入指定資料庫

導出現有資料庫數據：
mysqldump -u用戶名 -p密碼 資料庫名稱 >導出文件路徑           # 結構+數據
mysqldump -u用戶名 -p密碼 -d 資料庫名稱 >導出文件路徑       # 結構 
導入現有資料庫數據：
mysqldump -uroot -p密碼  資料庫名稱 <導入文件路徑
SELECT version(), current_date, now(); # 查看mysql版本號和日期、時間
SELECT version(); select now(); select current_date; # 分開寫則表格分開

SOURCE c://test.sql # 用txt文件保存命令該後綴名 可直接執行文件內的命令
\c # 撤銷先前輸入 
\G # 按行輸出
SHOW DATABASES; # 查看所有資料庫
SELECT DATABASE(); # 進入資料庫後查看當前資料庫
# 資料庫名稱組成除了三大項還可以含$，但不能是純數字
CREATE DATABASE db_name; # 創建spiders資料庫
CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 
# 安裝utf8規則排序
# utf8mb4支持墨跡表情
USE db_name； # 進入資料庫
ALTER DATABASE db_name DEFAULT CHARSET SET utf8;  # 修改資料庫

DROP DATABASE db_name； # 刪除資料庫

DROP VARIABLES LIKE 'datadir'; # 查看資料庫所在位置
SHOW TABLES; # 查看所有表
DESCRIBE db_name; # 查看表的描述，也可以用 DESC table;
SELECT * FROM db_name; # 查看表中所有數據
SELECT host,user FROM db_name; # 大小寫不敏感
INSERT INTO person(name,birth) VALUES('A',1994-01-01); # VALUE也可
CREATE TABLE students(
    nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  # 自增列必須是索引，最好是主鍵
    name VARCHAR(20),
    num INT NOT NULL DEFAULT 2)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


一張表只能有一個主鍵，值是唯一的（或多列組合是唯一的），不能重複不能為空，一般情況下自增列設置為主鍵。常用nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY一張表可以多個唯一列
CREATE TABLE tb1(
                nid INT NOT NULL,
                num INT NOT NULL,
                PRIMARY KEY(nid,num) # 兩列組成一個主鍵
            )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
主鍵的作用：

# 創建表時創建
CREATE TABLE color(
    INT NOT NULL PRIMARY KEY,
    name CHAR(16) NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;

CREATE TABLE fruit(
    INT NOT NULL PRIMARY KEY,
    smt CHAR(32) NOT NULL ,
    color_id INT NOT NULL,
    CONSTRAINT fk_fruit_color FOREIGN KEY (color_id) REFERENCES color(nid)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;
# 表外創建
ALTER TABLE students ADD CONSTRAINT fk_tb2_tb1 FOREIGN KEY tb2(info) REFERENCES tb1(nid);


# 添加列：
ALTER TABLE 表名 ADD 列名 類型
# 刪除列：
ALTER TABLE 表名 DROP COLUMN 列名

# 修改列：
ALTER TABLE 表名 MODIFY COLUMN 列名 類型;  一般只改類型
ALTER TABLE 表名 CHANGE 原列名 新列名 類型; 可改列名+類型
  
# 添加主鍵：
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
# 刪除主鍵：
ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE 表名  MODIFY 列名 INT, DROP PRIMARY KEY;
  
# 添加外鍵：
ALTER TABLE 從表 ADD CONSTRAINT 外鍵名稱（形如：FK_從表_主表）FOREIGN KEY 從表(外鍵欄位) REFERENCES 主表(主鍵欄位);
# 刪除外鍵：
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;
  
# 修改默認值：
ALTER TABLE tbl ALTER列名 SET DEFAULT 1000;
# 刪除默認值：
ALTER TABLE tbl ALTER列名 DROP DEFAULT; 
bit(M): 
二進位位，M為1~64，默認M=1
int:
nid int unsigned最大4GB (2**32 − 1)
整數類型中的m僅用於顯示，對存儲範圍無限制
tinyint: 有符號-128127，無符號0255，默認是signed
MySQL中無布爾值，使用tinyint(1)構造
bigint
smallint
decimal: 
精確的小數，能夠存儲精確值的原因在於其內部按照字符串存儲
num decimal(6,2)   6個有效數字(總長度)，2位小數，最大是65和30
float，double: 不精確
char:
 唯一定長，其他都是變長，數據不到該長度其他空間會閒置，查找速度快，浪費空間
varchar: 變長，相對於char效率低。查找下一列時不確定跳多少存儲空間
字符最大是255
text：字符最大是65535 2**16-1
mediumtext：2**24-1
longtext：2**32-1
二進位數據：
TinyBlob、Blob、MediumBlob、LongBlob
上傳文件，blob強制二進位方式。現在多用varchar保存路徑，上傳文件保存在硬碟
時間
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時）
枚舉 enum
支持65535個元素枚舉
CREATE TABLE shirts (
 name VARCHAR(40),
 size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));
 
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
集合 set
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

# 寫的順序
SELECT... FROM...
WHERE...
GROUP BY... HAVING...
ORDER  BY... 
LIMIT...

# 執行順序
FROM...
WHERE...
GROUP BY...
SELECT...
HAVING...
ORDER BY...
LIMIT...

INSERT INTO 表 (列名,列名...) VALUES(值,值,值...);
INSERT INTO  表 (列名,列名...) VALUES(值,值,值...),(值,值,值...); # 增加多條數據

INSERT INTO  表 (列名,列名...) SELECT 列名,列名 FROM 表;  # 後面選擇列不需要空格
# 如果數據可以轉則允許互轉
insert into students(name,age) SELECT caption,nid FROM tb2 WHERE nid>2;

DELETE FROM 表;
DELETE FROM 表 WHERE id＝1 AND name＝'alex';
DELETE FROM 表 WHERE id > 1 OR name＝'alex';

# 刪除和清空
DROP TABLE student; # 刪除表
DELETE FROM student; # 清空表，有自增列則清空後自增記憶存在
TRUNCATE (TABLE) student；# 快速清空表，有自增列則清空後自增從1重新開始

UPDATE students SET name ＝ 'alex' WHERE id>1;

UPDATE salary SET sex = IF(sex = 'm', 'f', 'm')  # 條件判斷更改
UPDATE salary SET sex = char(ascii('m') + ascii('f') - ascii(sex));
UPDATE salary 
SET 
   sex = CASE sex 
        WHEN "m" THEN "f" 
        ELSE "m" END;
        
# UPDATE和JOIN
UPDATE A JOIN B ON A.URL = B.URL
SET member_id = '00012138' 
WHERE LOGIN_time BETWEEN '2019' AND '2020' 
AND B.class_id = 'TNT'; # BETWEEN 後可以再跟AND


SELECT * FROM 表;
# 要全部列可以把列名寫一遍，效率比*高
SELECT * FROM 表 WHERE id <> 1; # 即!=
SELECT * FROM 表 WHERE id BETWEEN 3 AND 5;  # 即 id>=3 AND id<=5
SELECT nid,name,gender FROM 表 WHERE id%2 = 1;

SELECT DISTINCE name FROM score WHERE num < 60；
# distinct 去重只保留各組一項 也可以使用group by

WHERE (class, name) NOT IN (SELECT class, name FROM B) # 多個欄位限制

SELECT * FROM 表 WHERE id BETWEEN 5 AND 16; # 也支持多個並列
SELECT * FROM 表 WHERE id IN(11,22,33)
SELECT * FROM 表 WHERE id NOT IN(11,22,33)
SELECT * FROM 表 WHERE id IN(select nid from 表)


SELECT * FROM 表 WHERE name LIKE 'ale%'  # - ale開頭的所有（多個字符串） where ... like ''
SELECT * FROM 表 WHERE name LIKE 'ale_'  # - ale開頭的所有（1個字符）
SELECT * FROM 表 WHERE name LIKE '_le%'
SELECT * FROM 表 LIMIT 5;            # 前5行
SELECT * FROM 表 LIMIT 4,5;          # 從第4行下一行開始的5行
SELECT * FROM 表 LIMIT 5 OFFSET 4    # 從第4行開始的5行，與上一條功能一樣
SELECT * FROM 表 ORDER BY 列 ASC             # 根據 「列」 從小到大排列
SELECT * FROM 表 ORDER BY 列 DESC            # 根據 「列」 從大到小排列
SELECT * FROM 表 ORDER BY 列1 DESC,列2 ASC   # 根據 「列1」 從大到小排列，如果相同則按列2從小到大排序，否則一列相同時其他列默認升序排序

# group by獲取各組第一行作為標識，其餘行丟棄
SELECT num FROM 表 GROUP BY num 
# 分組的原理默認是升序排序，也可以降序
SELECT num FROM 表 GROUP BY num DESC

SELECT count(1) FROM 表 GROUP BY num # 也可使用
SELECT count(name) FROM students GROUP BY classid
# count(1) 和 count(name)區別
# 如果name中有null則count(name)不記錄，其餘時候二者完全一樣
SELECT count(distinct name) FROM students GROUP BY classid # 去重

# sum只能對int類型計算 否則是0

# 多列分組 多個均相同的分到一組
SELECT num,nid FROM 表 GROUP BY num,nid
SELECT num,nid FROM 表  WHERE nid > 10 GROUP BY num,nid ORDER BY nid DESC
SELECT num,nid,count(*) AS count,sum(score) AS sum,avg(score),max(score),min(score) FROM 表 GROUP BY num,nid  # as 是自定義命名
 
# 如果要對聚合函數進行篩選需引入having 順序在group by後
SELECT num FROM 表 GROUP BY num HAVING max(id) > 10
# 聚集函數
count   sum   max   min   avg   group_concat(字符串拼接) # 都會去除null
# sum(1) 等於 count(1) 只能針對int類型

# group_concat
SELECT id,GROUP_CONCAT(name) FROM aa GROUP BY id;
SELECT id,GROUP_CONCAT(name SEPARETOR ';') FROM aa GROUP BY id;  
SELECT id,GROUP_CONCAT(DISTINCT name) FROM aa GROUP BY id;  
SELECT id,GROUP_CONCAT(name ORDER BY name DESC) FROM aa GROUP BY id;
# 語法如下
DISTINCT name ORDER BY id DESC SEPARETOR '-'
# 縱向組合，並以第一個表欄位為準
# 組合，自動處理重合
SELECT nickname FROM A UNION SELECT name FROM B;
 
# 組合，不處理重合
SELECT nickname FROM A UNION ALL SELECT name FROM B;
# 需要多行之間滿足一個需求就需要join

# 笛卡爾積
SELECT * FROM students,disc_info;

# 根據對應關係連表，實際等同於inner join
SELECT * FROM students,disc_info WHERE students.discipline = disc_info.nid;
SELECT students.name,disc_info.discipline FROM students,disc_info WHERE students.discipline = disc_info.nid;

# join 左右連接如果無對應關係顯示NULL，join效率高

# 取交集，inner join，也可以理解成過濾掉含NULL數據行的左右連接
SELECT A.num, A.name, B.name FROM 
A INNER JOIN B 
ON A.nid = B.nid；

# 右連接，right join
SELECT A.num, A.name, B.name FROM 
A RIGHT JOIN B 
ON A.nid = B.nid

# 左連接，right join
SELECT A.num, A.name, B.name FROM 
A LEFT JOIN B 
ON A.nid = B.nid
CASE WHEN 表達式
    THEN 輸出
WHEN 表達式 # 多少個WHEN都可以
    THEN 輸出
ELSE
    輸出
END

SELECT id,name,
(
    CASE WHEN classid = 1 THEN 2
    WHEN classid =2 THEN 1
    ELSE classid END
) AS clid
FROM stu;


# 把tidydata轉為正常數據
SELECT name,
MAX(CASE WHEN project = '基礎' THEN score ELSE NULL END) as '基礎',
MAX(CASE WHEN project = '爬蟲' THEN score ELSE NULL END) as '爬蟲',
MAX(CASE WHEN project = 'SQL' THEN score ELSE NULL END) as 'SQL'
FROM score;


# 查詢兩門及以上不及格同學信息
SELECT st.Name, AVG(score) as Score_n
FROM SC JOIN student st ON SC.SId = st.SId
GROUP BY SId
HAVING COUNT(CASE WHEN Score < 80 THEN 1 ELSE NULL END) >=2；

SELECT
    id,NAME,classid
FROM
    student tf
WHERE
    id = (
        SELECT
            max(id)
        FROM
            student ts
        WHERE
            ts.classid = tf.classid
    );
    
# 也可以用常規方法
SELECT
    id,NAME,classid
FROM
(
        SELECT
            max(id)
        FROM
            student
        GROUP BY
 classid
    );

# 基於B+樹
# 數據分的越開的列則建索引效果越好
# OR情況不能用索引
# 如果是聯合索引 前部過濾條件可以做為索引
# 在SQL語句前加EXPLAIN就可以明確是否走索引

# 創建索引
CREATE INDEX name_index ON student(name);

# 聚簇索引葉子節點跟著數據，非聚簇索引葉子節點跟著主鍵(聚簇索引)
# 非聚簇走完多數會再走聚簇，除非SELECT內容均被包含於索引(全覆蓋索引)
# MySQL中主鍵是聚簇索引，其他均為非聚簇索引
# 如果沒有主鍵，內部會虛擬一個AUTO_INCREMENT的主鍵

CREATE VIEW V1 AS  # 反覆利用某個臨時表則可以創建視圖 
SELECT * FROM stu WHERE id > 10;
# 視圖是一個動態表，會從物理表動態讀出來。但無法直接對虛擬表即視圖修改
# 修改視圖
ALTER VIEW V1 AS SELECT * FROM stu WHERE id > 20;
# 刪除視圖
DROP VIEW V1;

DELIMITER // # 修改終止符
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT INTO tb1 FOR EACH ROW
BEGIN
    INSERT INTO tb2(name) VALUES('chenx'); 
    INSERT INTO tb2(name) VALUES(NEW.sname);  # 指代新插入的一行，OLD可以用在DELETE和UPDATE
END //
DELIMITER ; # 修改回原終止符

# BEFORE可以換成AFTER，INSERT可以換成DELETE或者UPDATE

# 刪除觸發器
DROP TRIGGER tri_before_insert_tb1;
# 內置函數
SELECT CURDATE(); # 執行函數的用法
SELECT DATE_FORMAT(ctime, "%Y-%m") FROM stu GROUP BY DATE_FORMAT(ctime, "%Y-%m") # 時間格式化

# 自定義函數
DELIMITER \\
CREATE FUNCTION f1(
    i1 INT,
    i2 INT)
RETURNS INT# 強類型語言
BEGIN
    DECLARE num INT DEFAULT 0; # 聲明變量
    SET num = i1 + i2;
    RETURN(num);
END \\
DELIMITER ;
看到這裡，不要忘記點讚+收藏+轉發三連。
【往期精彩】
首屆雲原生編程挑戰賽
2020天池國際學術會議聯賽 KDD、IJCAI、CVPR學術頂會賽事
【天池大賽】3D人工智慧挑戰賽
轉發等於理解，收藏等於學會

相關焦點

  • Sql盲注學習筆記1
    payload='id=0 or if((select ascii(substr(({substr_sql} limit {limit_offset},1),{substr_offset},1))={ascii_num}),1,0)'column_content=''column_contents=[]#通過字典的形式關聯起來 列表中第一個元素表示對應的表的列數
  • 如何學習SQL語言
    第4部分：複雜查詢學習以下內容：1）視圖（什麼是視圖，如何使用，有什麼用，注意事項）2）子查詢、標量子查詢、關聯子查詢3）各種常用函數從零學會SQL：複雜查詢第5部分：多表查詢在有多張表的情況下，表和表之間的運算和聯繫就變得很重要，這部分學習多個表如何查詢：1）表的加法2）聯結，包括交叉聯結、內聯結、左聯結、右聯結、全聯結3）一張圖記住各種聯結
  • 一場pandas與SQL的巔峰大戰(七)
    的使用    簡介    安裝    使用pandas操作MySQL資料庫    read_sql    to_sql巔峰系列總結十條(驚喜在此)reference 在之前的六篇系列文章中，我們對比了pandas和SQL在數據方面的多項操作
  • SQL 學習筆記
    1、SQL分類DDL（數據定義語言）create：創建資料庫和表drop：刪除資料庫和表alter：修改資料庫和表的結構DML（數據操縱語言）insert：向表中插入數據delete：刪除數表中的數據select：查詢表中的數據update
  • Mybatis的sql組裝詳解
    上一篇分析了SqlSession執行sql的過程，其中並沒有分析sql是從哪裡來的，今天就來仔細分析下。Sql來源從上一篇的最後一步執行sql那裡倒推sql的來源，源碼主要過程如下圖：可以看到最後是通過BoundSql直接獲取的sql，然後往前倒推最後發現是通過MappedStatement的getBoundSql方法返回的。
  • 續 | 《SQL基礎教程》學習筆記
    點擊上方SQL資料庫開發，關注獲取SQL視頻教程上周給大家分享了一部分《SQL基礎教程》學習筆記
  • Mybatis中SqlSource解析流程詳解
    前面幾篇文章都在詳細分析mapper的加載過程，但是始終沒有看到sql的解析過程，今天來詳細分析下。而他們有一個由SqlNode規定的apply方法，這才是他們正真的作用所在，比如MixedSqlNode是遍歷所有節點執行對應的apply方法，StaticTextSqlNode就只是把對應sql拼接到後面，IfSqlNode是在進行判斷後調用MixedSqlNode去執行if節點下所有的節點。
  • PandaSQL:一個讓你能夠通過SQL語句進行pandas的操作的python包
    如果你熟練的使用SQL,那麼這篇文章將介紹一種更直接、簡單的使用Pandas處理大多數數據操作案例。假設你對SQL非常的熟悉，或者你想有更可讀的代碼。或者您只是想在dataframe上運行一個特殊的SQL查詢。或者，也許你來自R，想要一個sqldf的替代品。
  • 產品經理學SQL(二)一天學會用SQL解決業務查詢問題
    本篇文章意在幫助大家系統地入門SQL，教大家如何解決sql查詢任務。前言回顧一下，上一篇文章我們已經知道了SQL語言的基本框架，並能完成簡單的單表查詢和雙表連接查詢。這篇文章希望能幫助你系統地入門SQL，從而解決產品經理80%的sql查詢任務。和上篇的一個小時入門SQL一樣，這篇文章的建議學習時間為一天。
  • 十分鐘學習pandas! pandas常用操作總結!
    學習Python, 當然少不了pandas，pandas是python數據科學中的必備工具
  • Myql SLEEP函數和SQL注入
    ）訪問中看到的。所以蟲蟲以也提到過在主機防護中的條款：禁止把埠暴露在公網；修改常用埠。專門對於mysql，最常用的掃描滲透和安全審計工具是sqlmap。快速解剖在本文開頭，我們舉了一個sql語句的例子，這是常用的盲注的典型語句。如果執行SELECT語句，其中的條件語句部分被替換為類似以下的內容，那麼它將絕對鎖定資料庫：注意該語句巧妙地使用各種運算符和分隔符。
  • 資料庫sql 學習
    sql 用於訪問和處理資料庫的標準計算機語言rdbms 關係型資料庫管理系統ANSI 美國國家標準化組織
  • 適用於初學者和分析師的SQL –使用Python入門SQL
    \ sql_db \ Demo_table.db'）＃創建遊標以執行查詢cur = conn。遊標（）列印（「已創建資料庫」。）現在，您可以對資料庫執行查詢並處理數據了。但是，在完成此操作之後，做兩件事非常重要：使用commit（）方法提交/保存我們對資料庫執行的操作。
  • 十分鐘學習pandas!pandas常用操作總結!
    學習Python, 當然少不了pandas，pandas是python數據科學中的必備工具，熟練使用pandas是從sql boy/girl 跨越到一名優秀的數據分析師傅的必備技能。這篇pandas常用操作總結幫大家回顧下pandas的常用語法，尤其是我們分析數據時常用的方法。
  • 大數據分析工程師入門9-Spark SQL
    本文為《大數據分析師入門課程》系列的第9篇，在本系列的第8篇-Spark基礎中，已經對Spark做了一個入門介紹，在此基礎上本篇拎出Spark SQL，主要站在使用者的角度來進行講解，需要注意的是本文中的例子的代碼均使用Scala語言。
  • PySpark SQL——SQL和pd.DataFrame的結合體
    中的第一個重要組件SQL/DataFrame，實際上從名字便可看出這是關係型資料庫SQL和pandas.DataFrame的結合體，功能也幾乎恰是這樣，所以如果具有良好的SQL基本功和熟練的pandas運用技巧，學習PySpark SQL會感到非常熟悉和舒適。
  • MySQL sql_mode 說明(及處理一起 sql_mode 引發的問題)
    因為 insert 失敗在資料庫層面是看不出來的，於是找php的同事看下錯誤信息：1[Err] 1364 - Field `f_company_id` doesn't have a default value很明顯2個 insert 操作，第一條成功，第二條失敗了，但因為沒有控制在一個事務當中，導致app裡面依然提示成功，這是客戶入庫操作
  • SQL注入常規Fuzz全記錄
    前言本篇文章是在做ctf bugku的一道sql 盲注的題（題目地址:注入題目）中運用了fuzz的思路，完整記錄整個fuzz的過程
  • SQL 語句中 where 條件後 寫上 1=1 是什麼意思!
    來源：cloud.tencent.com/developer/article/1475146這段代碼應該是由程序（例如Java）中生成的，where條件中 1＝1 之後的條件是通過 if 塊動態變化的例如:String sql="select * from table_name where 1=1";if( conditon 1) {  sql=sql+"  and  var2=value2";}if(conditon 2) {  sql=sql+"  and var3=value3";
  • SQL 中的(+)用法
    今天在代碼review的時候，發現sql中很多 （+）的寫法，看得頭暈，記錄下。對於sql邏輯比較複雜的場景不推薦是有 （+），容易自己暈掉。Oracle sql 中 (+)號可以理解為補充的意思，即哪個表有加號，這個表就是匹配表。