SQL常用語句總結

2021-02-19 大數據分析和人工智慧

編者按:由於大量數據保存在關係資料庫中,因此數據科學家難免要和SQL打交道。當然,面試的時候也常常考察SQL。Moratuwa大學生物信息學研究員Vijini Mallawaarachchi總結了常用的SQL語句用法,可供參考和溫習。

本文總結了常用的SQL語句,尤其適合在面試前複習你的SQL知識。你可以嘗試文中的例子,溫習下你很久以前在資料庫系統課程上學到的知識。

為了演示每個命令的用法,我們將使用一個樣例資料庫。生成該資料庫的腳本可以從Google網盤下載:

如不便訪問Google網盤,可以在論智公眾號(ID: jqr_AI)留言sql recap獲取。

下載文件後,輸入以下命令進入MySQL控制臺(假設你已經裝好了MySQL或MariaDB)。

mysql -u root -p

mysql會提示你輸入密碼,輸入安裝配置MySQL服務時設置的密碼即可。

輸入如下命令生成樣例資料庫:

CREATE DATABASE university;

USE university;

SOURCE <DLL.sql文件路徑>;

SOURCE <InsertStatements.sql文件路徑>;

好了,現在讓我們開始溫習SQL語句吧。

1. 查看現有資料庫

SHOW DATABASES;

2. 新建資料庫

CREATE DATABASE <資料庫名>;

3. 選擇資料庫

USE <資料庫名>;

4. 從.sql文件引入SQL語句

SOURCE <.sql文件路徑>;

5. 刪除資料庫

DROP DATABASE <資料庫名>;

6. 查看當前資料庫中的表

SHOW TABLES;

7. 創建新表

CREATE TABLE <表名> (

   <列名1> <列類型1>,

   <列名2> <列類型2>,

   <列名3> <列類型3>,

   PRIMARY KEY (<列名1>),

   FOREIGN KEY (<列名2>) REFERENCES <表名2>(<列名2>)

);

主鍵(PRIMARY KEY)用來標識一條記錄(一行),所以每條記錄的主鍵值必須是唯一的。主鍵可以定義在多列上,這稱為聯合主鍵(composite primary key)

如果我們把表視作具有某種結構的數組(例如,C語言中的struct),那麼外鍵(FOREIGN KEY)可以視作指針。

例子:

CREATE TABLE instructor (

   ID CHAR(5),

   name VARCHAR(20) NOT NULL,

   dept_name VARCHAR(20),

   salary NUMERIC(8,2),

   PRIMARY KEY (ID),

   FOREIGN KEY (dept_name) REFERENCES department(dept_name));

在上面的例子中,我們創建了一個教員(instructor)表,該表的主鍵是ID,外鍵是教員所在的部門名稱(dept_name),關聯部門(department)表。此外,教員表還包括姓名(name)、薪水(salary)。其中,姓名有約束NOT NULL,表示姓名這一項不能為空。

8. 概述表中的列

使用如下語句查看表中的列的基本信息:

DESCRIBE <表名>;

下圖顯示了一些例子:

9. 在表中插入新紀錄

INSERT INTO <表名> (<列名1>, <列名2>, <列名3>, …)

   VALUES (<值1>, <值2>, <值3>, …);

也可以省略列名(依序在所有列上插入新值):

INSERT INTO <表名>

   VALUES (<值1>, <值2>, <值3>, …);

10. 在表中更新記錄

UPDATE <表名>

   SET <列名1> = <值1>, <列名2> = <值2>, ...

   WHERE <條件>;

11. 清空表

DELETE FROM <表名>;

12. 刪除表

DROP TABLE <表名>;

13. SELECT

SELECT語句可以從表中選擇數據:

SELECT <列名1>, <列名2>, …

   FROM <表名>;

以下語句選擇所有內容:

SELECT * FROM <表名>;

選中部門(department)表和課程(course)表中的所有內容

14. SELECT DISTINCT

SELECT DISTINCT過濾掉了重複的值:

SELECT DISTINCT <列名1>, <列名2>, …

   FROM <表名>;

15. WHERE

我們之前在更新記錄時已經用到了WHERE關鍵字,用來指明條件。這裡我們稍微詳細一點地介紹下WHERE

WHERE的條件通常是:

比較文本(text)

比較數字(numbers)

ANDORNOT等邏輯運算

讓我們來看一些例子:

SELECT * FROM course WHERE dept_name='Comp. Sci.';

SELECT * FROM course WHERE credits>3;

SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3;

16. GROUP BY

GROUP BY語句可以分組結果,常用於COUNTMAXMINSUMAVG聚合函數(aggregate functions)

SELECT <列名1>, <列名2>, …

   FROM <表名>

   GROUP BY <列名>;

讓我們來看一個例子,列出每個部門的課程數量:

SELECT COUNT(course_id), dept_name

    FROM course

    GROUP BY dept_name;

17. HAVING

乍看起來,HAVINGWHERE很像:

SELECT <列名1>, <列名2>, …

   FROM <表名>

   GROUP BY <列名x>

   HAVING <條件>;

那麼,HAVINGWHERE有什麼不同呢?讓我們先來看一個例子,列出開了不止一門課程的部門開設的課程數:

SELECT COUNT(course_id), dept_name

   FROM course

   GROUP BY dept_name

   HAVING COUNT(course_id)>1;

這裡HAVING不能換成WHERE,因為WHERE直接針對行操作,且在GROUP BY之前運行(即先通過WHERE篩選行,之後再將篩選出的行通過GROUP BY分組)。假設SQL中不存在HAVING語句,那麼我們只能先新建一張表,將COUNT(course_id)作為新表的列,然後在新表上再通過WHERE進行篩選(當然,實際上SQL提供了派生表、CTE等機制,並不用真的手工建新表)。

18. ORDER BY

ORDER BY可以對結果進行排序,在沒有明確指定ASC(升序)或DESC(降序)的情況下,默認按升序排列。

SELECT <列名1>, <列名2>, …

FROM <表名>

ORDER BY <列名1>, <列名2>, …, ASC|DESC;

例子:

SELECT * FROM course ORDER BY credits;

SELECT * FROM course ORDER BY credits DESC;

19. BETWEEN

BETWEEN語句用於指定區間

SELECT <列名1>, <列名2>, …

   FROM <表名>

   WHERE <列名x> BETWEEN <值1> AND <值2>;

其中「值」可能是數字,文本,乃至日期等。

例如,列出薪資在50000和100000之間的教員:

SELECT * FROM instructor

   WHERE salary BETWEEN 50000 AND 100000;

20. LIKE

LIKE用於匹配文本中的特定模式

SELECT <列名1>, <列名2>, …

   FROM <表名>

   WHERE <列名x> LIKE <模式>;

模式中可以使用以下兩個通配符:

例子:列出課程名中包含「to」的課程,以及課程ID以「CS-」開頭的課程。

SELECT * FROM course WHERE title LIKE '%to%';

SELECT * FROM course WHERE course_id LIKE 'CS-___';

21. IN

IN語句表示值屬於某個集合。

SELECT <列名1>, <列名2>, …

   FROM <表名>

   WHERE <列名n> IN (<值1>, <值2>, …);

例子:列出計算機科學、物理、電子工程部門的學生。

SELECT * FROM student

   WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Elec. Eng.');

22. JOIN

JOIN用來組合兩張以上表中的值。下圖展示了JOIN的三種類型:

圖片來源:zeroturnaround.com

SELECT <列名1>, <列名2>, …

   FROM <表名1>

   JOIN <表名2>

   ON <表名1.列名x> = <表名2.列名x>

讓我們來看三個例子,分別對應三種JOIN的類型。

第一個例子,列出課程時包含開設課程的部門詳情:

SELECT * FROM course

   JOIN department

   ON course.dept_name=department.dept_name;

第二個例子,列出所有具有前置課程的課程的詳情:

SELECT prereq.course_id, title, dept_name, credits, prereq_id

   FROM prereq

   LEFT OUTER JOIN course

   ON prereq.course_id=course.course_id;

最後一個例子,列出所有課程的詳情,不管是否具有前置課程:

SELECT course.course_id, title, dept_name, credits, prereq_id

   FROM prereq

   RIGHT OUTER JOIN course

   ON prereq.course_id=course.course_id;

23. 視圖

視圖(view)是虛擬的SQL表。它包含行和列,和一般的SQL表格很類似。視圖總是顯示資料庫中的最新數據。

CREATE VIEW

創建視圖:

CREATE VIEW <視圖名> AS

   SELECT <列名1>, <列名2>, …

   FROM <表名>

   WHERE <條件>;

DROP VIEW

刪除視圖:

DROP VIEW <視圖名>;

例如,創建3學分的課程視圖:

CREATE VIEW my_view AS

   SELECT * FROM course

   WHERE credits=3;

24. 聚合函數

我們之前已經提到聚合函數,這裡列出最常用的一些聚合函數:

COUNT(列名) 返回行數

SUM(列名) 返回指定列的值之和

AVG(列名) 返回指定列的平均值

MIN(列名) 返回指定列的最小值

MAX(列名) 返回指定列的最大值

25. 嵌套子查詢

在SQL請求中,可以嵌套SELECT-FROM-WHERE表達式,稱為嵌套子查詢(nested subqueries)

例如,查找2009年秋、2010年春都開的課程:

SELECT DISTINCT course_id

   FROM section

   WHERE semester = 『Fall』 AND year= 2009 AND course_id IN (

       SELECT course_id

           FROM section

           WHERE semester = 『Spring』 AND year= 2010

   );

相關焦點

  • mysql常用sql語句總結
    sql語言簡潔只有7個動詞:SELECT , DROP, ALTER, CREATE, INSERT, UPDATE ,DELETE;
  • 資料庫常用的sql語句匯總(2)
    資料庫相關sql語句表相關創建表 create table t1(id int,name varchar(10));表相關sql語句修改表修改表名 rename table t1 to t2;修改表相關sql語句數據相關插入數據 insert into t1 values(5,'xiaoming',null
  • Oracle常用sql語句
    正在看的ORACLE教程是:  oracle常用sql語句。
  • 一道簡單的sql語句題
    結果才發現,數據分析崗位大多注重的是資料庫的能力,比如sql語句的考察,hive的考察,以及一些運營思維的考察,所以第一次面試就很悲劇啦,不過題目還是很有代表性的。其他的不寫了,這裡只分享一個關於sql的題目。1、問題引出現在有兩個數據表,一個數據表記錄司機的信息,比如司機id,司機姓名,司機註冊時間等等,一個數據表記錄一天的訂單情況,比如訂單ID,訂單司機id,訂單時間。
  • 日進一步第三天,SQL語句之order by
    公眾號:輕鬆自由7799第一天的SELECT……FEOM……是最常用的查詢語句,數據分析之小白的第一條sql語句第二天的group by則是方便快捷分組語句數據分析之小白的第二條sql語句今天帶來的是ORDER BY---排序語句為了對檢索出的結果進行排序,一般我們就會用到ORDER BY了,它的含義是:根據欄位……進行排序。
  • 資料庫設計:使用PD16創建常用目錄欄並生成sql語句實例!
    這種方式非常類似於哈夫曼編碼Name是目錄名字Father_id是父菜單IDIs_enable是目錄是否刪除Excel左邊兩個的漏鬥筆可以選擇打開哪些列轉成PDM或sql我用的mysql,選的mysql5.0轉出後的效果按快捷鍵Ctrl+G,在選擇裡可以選擇需要的表格,Preview可以看到生成的sql語句,然後就成功了,可以看到sql語句了。
  • 使用explain和show profile來分析SQL語句實現優化SQL語句
    SQL語句優化是建立在慢查詢分析的基礎上,通過慢查詢定位有問題的SQL語句,關於慢查詢的介紹及其分析工具,可以參考[mysql慢查詢及慢查詢日誌分析工具]一、通過explain查詢1 用法:explain sql2 作用:用於分析sql語句
  • 日進一步,SQL語句之order by
    公眾號:輕鬆自由7799第一天的SELECT……FEOM……是最常用的查詢語句,每日進步,寫一條SQL語句第二天的group by則是方便快捷分組語句數據分析之小白的第二條sql語句今天帶來的是ORDER BY---排序語句
  • Mysql常用SQL語句集錦 &建議轉發收藏
    as day from table_name";複製代碼//一個sql返回多個總數$sql = "select count(*) all, " ;$sql .= " count(case when status = 1 then status end) status_1_num, ";$sql .= " count(case when status = 2 then status end) status
  • 常用SQL語句分享
    一、本期小編為大家分享一些常用的SQL語句,能夠較熟練的掌握一定的SQL語句,在做數據維護的時候往往可以達到事半功倍的效果。當然,語句在實際運用中需要舉一反三,使用各種條件組合來實現。小編水平有限,如有錯漏,請不吝賜教。
  • Oracle優化:sql語句的執行順序
    理解 sql 語句的執行順序對我們優化 sql 有很大的幫助,那麼 sql 語句的執行順序是怎樣的呢,以一條簡單的的語句做分析:① 先執行 from 子句,明確數據的來源,從哪個表或哪個視圖來查詢② 接著執行
  • php mysql SQL注入語句構造
    由於PHP和MYSQL本身得原因,PHP+MYSQL的注射要比asp困難,尤其是注射時語句的構造方面更是個難點,本文主要是借對Okphp BBS v1.3一些文件得簡單分析,來談談php+mysql注射語句構造方式,希望本文對你有點幫助。
  • SQL 語句中 where 條件後 寫上1=1 是什麼意思
    例如:  String sql="select * from table_name  where 1=1";        if( conditon 1) {              sql=sql+"  and  var2=value2";            }        if(conditon 2) {
  • 提升SQL語句性能的方法
    用具體案例進行SQL語句性能提升的方法。先用實際案例分析了優化SQL語句的方法,然後再結合nat123這個實際案例分析了如何實現外網訪問內網Mysql資料庫的方法。優化方法:優化的總體思路是拆分sql,將排序操作和查詢所有信息的操作分開。
  • 超全sql語句全集值得收藏
    首頁 > 語言 > 關鍵詞 > 收藏最新資訊 > 正文 超全sql語句全集值得收藏
  • 最強解讀MyBatis是如何執行SQL語句的?
    MyBatis 如何獲取 sql 語句?MyBatis 如何執行 sql 語句?MyBatis 如何實現不同類型數據之間的轉換?在過去程式設計師使用JDBC連接資料庫,總會帶來諸多不便。MyBatis 如何獲取 sql 語句? 與獲取資料庫源類似,只要解析Mapper配置文件中的對應標籤,就可以獲得對應的sql語句。
  • SQL 語句中 where 條件後 寫上1=1 是什麼意思
    例如:  String sql="select * from table_name  where 1=1";        if( conditon 1) {              sql=sql+"  and  var2=value2";            }        if(conditon 2) {
  • Mysql中一條SQL查詢語句是如何執行的?
    2.查詢流程解析select * from table1 where ID=10;這條語句相信大家再熟悉不過了,下面我們就看看這一條語句在mysql中是怎麼執行的。第一步:一條sql語句要經過連接器,客戶端要和mysql建立連接。
  • SQL語句性能調整之ORACLE的執行計劃
    這個語句的優點就是它的缺點,這樣在用該方法查看執行時間較長的sql語句時,需要等待該語句執行成功後,才返回執行計劃,使優化的周期大大增長。  如果不想執行語句而只是想得到執行計劃可以採用:  Sql> set autotrace traceonly  這樣,就只會列出執行計劃,而不會真正的執行語句,大大減少了優化時間。
  • SQL 資料庫語句
    9、說明:創建視圖:create view viewname as select statement刪除視圖:drop view viewname10、說明:幾個簡單的基本的sql語句選擇:select * from table1 where 範圍插入:insert into table1(field1,field2) values(value1