簡單學習SQL語言

語言: CN / TW / HK

title: 簡單學習SQL語言 tags: sql categories: sql theme: vue-pro highlight:


SQL(Structured Query Language)就是一個語言,操作數據庫的語言,可以實現增刪改查(庫、表、列、數據)。

  • 表的每一行稱為記錄(Record),記錄是一個邏輯意義上的數據。
  • 表的每一列稱為字段(Column),同一個表的每一行記錄都擁有相同的若干字段。
  • 字段定義了數據類型(整型、浮點型、字符串、日期等),以及是否允許為 NULL。注意 NULL 表示字段數據不存在。一個整型字段如果為 NULL 不表示它的值為 0,同樣的,一個字符串型字段為 NULL 也不表示它的值為空串''
  • 主鍵是表裏記錄的唯一標識,現在一般採用自增,與業務邏輯無關

MySQL

MySQL 是應用最光泛的數據庫,本文以此為例。

  • Ubuntu 用户 - 可通過命令apt-get install mysql-server安裝最新的 MySQL 版本。
  • Mac用户 - 可通過命令brew install mysql安裝,mac詳細安裝教程

安裝完MySQL後,以下兩個同時具備了

  • 一個是 MySQL Server,即真正的 MySQL 服務器,
  • 還附贈一個 MySQL Client 程序。其是一個命令行客户端,可登錄 MySQL,然後,輸入 SQL 語句並執行。

MySQL Client命令行客户端輸入命令,mysql -u root -p,輸入正確的密碼,即可連接MySQL Server,輸入exit,退出連接模式。

EXIT 僅僅斷開了客户端和服務器的連接,MySQL 服務器仍然繼續運行。

  • MySQL Client中輸入的 SQL 語句通過TCP連接發送到MySQL Server。默認端口號是3306
  • 如果發送到本機MySQL Server,地址就是127.0.0.1:3306
  • 如果連接遠程,需要指定IP或者域名,使用命令mysql -h 10.0.1.99 -u root -p

mysql_2

管理庫、表、列

庫:

  • 列出所有數據庫 - SHOW DATABASES
  • 創建一個新數據庫 - CREATE DATABASE <數據庫名字>
  • 刪除一個數據庫 - DROP DATABASE <數據庫名字>,刪除一個數據庫將導致該數據庫的所有表全部被刪除
  • 切換數據庫 - SHOW TABLES,對一個數據庫進行操作時,要首先將其切換為當前數據庫

表: - 列出當前數據庫的所有表 - USE <數據庫名字> - 查看一個表的結構 - DESC <數據庫名字> - 查看創建表的 SQL 語句 - SHOW CREATE TABLE <數據庫名字> - 創建一個新表 - CREATE TABLE <表名字> - 刪除一個表 - DROP TABLE <表名字>

列: - 給表增加新列 - ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;,給 students 表新增一列 birth - 修改列名 - ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;,列名改為 birthday,類型改為 VARCHAR(20) - 刪除列 - ALTER TABLE students DROP COLUMN birthday;

查詢數據

基礎查詢

sql -- SELECT * FROM students; SELECT * FROM <表名>

  • SELECT是關鍵字,表示將要執行一個查詢
  • *表示“所有列”
  • FROM表示將要從哪個表查詢,本例中是 students 表。
  • 該 SQL 將查詢出 students 表的所有數據。注意:查詢結果也是一個二維表,它包含列名和每一行的數據
  • 許多檢測工具會執行一條SELECT 1;來測試數據庫連接

條件查詢

很多時候,我們並不需要獲得所有記錄,而是獲取指定條件的記錄

sql -- SELECT * FROM students WHERE score >= 80; SELECT * FROM <表名> WHERE <條件表達式>

  • 條件 1:根據 score 列的數據判斷:score >= 80
  • 條件 2:根據 gender 列的數據判斷:60 <= score <= 90
  • AND:score >= 80 AND gender = 'M'
  • OR:score >= 80 OR gender = 'M'
  • NOT:NOT class_id = 2,其實等價於class_id <> 2,所以不常用NOT
  • LIKE:name LIKE 'ab%',LIKE 相似,%是任意字符

投影查詢

希望返回某些列的數據,而不是所有列的數據

sql -- SELECT id, score, name FROM students; SELECT 列1, 列2, 列3 FROM ... -- 也可別名 SELECT id, score points, name FROM students; SELECT 列1 別名1, 列2 別名2, 列3 別名3 FROM ...

排序查詢

默認是主鍵排序,其他排序的話用ORDER BY,不寫的話,默認ASC

sql SELECT * FROM students ORDER BY score; SELECT * FROM students ORDER BY score DESC;

  • 分數相同按 gender 排序的話,ORDER BY score DESC, gender

分頁查詢

數據量龐大的話,就需要分頁展示,可以設置每頁 100 條

sql SELECT id, name, gender, score FROM students ORDER BY score DESC -- 也可簡寫為 LIMIT 3,0 LIMIT 3 OFFSET 0;

  • LIMIT 3 OFFSET 0表示,對結果集從 0 號記錄開始,最多取 3 條。注意 SQL 記錄集的索引從 0 開始
  • LIMIT總是設定為pageSizeOFFSET計算公式為pageSize * (pageIndex - 1)

聚合查詢

統計一張表的數據量用COUNT(),表示查詢所有列的行數,就是記錄的數量

sql SELECT COUNT(*) FROM students;

  • 一般給列名設置一個別名,便於處理結果:SELECT COUNT(*) num FROM students;,沒有記錄返回0
  • 其他聚合函數,可以計算某列的總體值:SUM、AVG、MAX、MIN,SELECT AVG(score) average FROM students,找不到列返回NULL
  • 獲取總頁數SELECT CEILING(COUNT(*) / <pageSize>) FROM students

分組

學生列表裏,想要分別統計一班、二班、三班的學生數量,就是用分組,省的一個個查詢

sql SELECT class_id,COUNT(*) num FROM students GROUP BY class_id;

mysql_1

執行該 SELECT 語句時,會把class_id相同的列先分組,再分別計算,因此,得到了多行結果。

  • 查詢各班的男女生人數:SELECT class_id,gender,count(*) num FROM students GROUP BY class_id,gender
  • 查詢各班的平均分:SELECT class_id,AVG(score) average FROM students GROUP BY class_id;

多表查詢

多表查詢其實就是FROM <表名1>, <表名2>

比如聯合查詢學生和班級表

sql SELECT s.id sid, s.name, s.gender, s.score, c.id cid, c.name cname FROM students s, classes c; WHERE s.gender = 'M' AND c.id = 1;

  • 兩個表有相同字段的時候,需要使用別名
  • 表也可以使用別名
  • 多表查詢,記錄可能會很多,所以儘量帶條件

連接查詢

主表想增加別的表的字段

比如學生表想增加班級名稱字段,需要班級表配合

sql SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s INNER JOIN classes c ON s.class_id = c.id;

  • 先確定主表,仍然使用FROM <表1>的語法;
  • 再確定需要連接的表,使用INNER JOIN <表2>的語法;
  • 然後確定連接條件,使用ON <條件...>,這裏的條件是s.class_id = c.id,表示 students 表的 class_id 列與 classes 表的 id 列相同的行需要連接;
  • 可選:加上 WHERE 子句、ORDER BY 等子句。

不同的 join:

  • INNER JOIN 只返回同時存在於兩張表的行數據
  • RIGHT OUTER JOIN 返回右表都存在的行
  • LEFT OUTER JOIN 則返回左表都存在的行
  • FULL OUTER JOIN,它會把兩張表的所有記錄全部選擇出來,並且,自動把對方不存在的列填充為 NULL

修改數據

關係數據庫的基本操作就是增刪改查,即 CRUD:Create、Retrieve、Update、Delete。其中,對於查詢,我們已經詳細講述了 SELECT 語句的詳細用法。

而對於增、刪、改,對應的 SQL 語句分別是:

  • INSERT:插入新記錄;
  • UPDATE:更新已有記錄;
  • DELETE:刪除已有記錄。

INSERT:插入新記錄

sql -- INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80); -- 查詢並觀察結果: -- SELECT * FROM students; INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

可以添加多條記錄:

sql INSERT INTO students (class_id, name, gender, score) VALUES (1, '大寶', 'M', 87), (2, '二寶', 'M', 81);

UPDATE

sql -- UPDATE students SET name='大牛', score=66 WHERE id=1; -- 查詢並觀察結果: -- SELECT * FROM students WHERE id=1; UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

更新字段,可以使用表達式UPDATE students SET score=score+10 WHERE score<80;

UPDATE語句可以沒有WHERE條件,整個表的所有記錄都會被更新。所以,在執行 UPDATE 語句時要非常小心 最好先用 SELECT 語句來測試 WHERE 條件是否篩選出了期望的記錄集,然後再用 UPDATE 更新

DELETE

sql DELETE FROM <表名> WHERE ...;

同樣也應該SELECT提前測試

SQL 的常用語句

插入一條新記錄(INSERT)

  • 若記錄已存在則刪除,再插入新記錄,否則直接插入新紀錄。可以使用 REPLACE 語句,省去查詢步驟(記錄存在與否通過主鍵判斷)

sql REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

  • 若記錄已存在則更新,再插入新記錄,否則直接插入新紀錄。

sql INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

  • 若記錄已存在則停止操作,否則直接插入新紀錄。

sql INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

複製表

複製一份當前表的數據到一個新表,專業名詞快照,新創建的表結構和原表結構完全一致。

sql -- 對class_id=1的記錄進行快照,並存儲為新表students_of_class1: CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

查詢結果集寫入到指定表中

一般先創建表

sql CREATE TABLE statistics ( id BIGINT NOT NULL AUTO_INCREMENT, class_id BIGINT NOT NULL, average DOUBLE NOT NULL, PRIMARY KEY (id) );

用一條語句寫入各班的平均成績

sql INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

強制使用指定索引

在查詢的時候,數據庫默認選擇最合適的索引。如果我們知道如何選擇索引,可以使用FORCE INDEX強制查詢使用指定的索引。例如:

sql -- 指定索引的前提是索引idx_class_id必須存在 SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

事務

事務,其實是説一系列的操作,都執行成功才成功,否則等於沒執行。

一系列的操作作為整體,需要用關鍵詞括起來:

sql BEGIN; -- 第一步:將id=1的A賬户餘額減去100 UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 第二步:將id=2的B賬户餘額加上100 UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

有些時候,我們希望主動讓事務失敗,這時,可以用ROLLBACK回滾事務,整個事務會失敗:

sql BEGIN; -- 第一步:將id=1的A賬户餘額減去100 UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 第二步:將id=2的B賬户餘額加上100 UPDATE accounts SET balance = balance + 100 WHERE id = 2; ROLLBACK;

引用

本文主要參考和大量使用廖大神的原句,簡單構成手冊使用,感謝廖大神