greenplum 查詢出來的數字加減日期_SQL查詢語句總結【完整彙總版】

SQL思維導圖：查詢語句 

SQL思維導圖：DDL&DML

SQL查詢：基礎知識點

1、左連接配接、右連接配接、内連接配接、外連接配接

• 内連接配接(INNER JOIN)，用于傳回兩個表中滿足連接配接條件的資料行。
• 左外連接配接(LEFT OUTER JOIN)，傳回左表中所有的資料行；對于右表中資料，如果沒有比對的值，傳回空值。
• 右外連接配接(RIGHT OUTER JOIN)，傳回右表中所有的資料行；對于左表中資料，若沒有比對的值，傳回空值。
• 全外連接配接(FULL OUTER JOIN)，等價于左外連接配接加上右外連接配接，傳回左表和右表中所有的資料行。MySQL 不支援全外連接配接。
• 交叉連接配接(CROSS JOIN)，也稱為笛卡爾積(Cartesian product)，兩個表的笛卡爾積相當于一個表的所有行和另一個表的所有行兩兩組合，結果的數量為兩個表的行數相乘。
• 自連接配接(Self Join)，是指連接配接操作符的兩邊都是同一個表，即把一個表和它自己進行連接配接。自連接配接主要用于處理那些對自己進行了外鍵引用的表。

2、SQL支援三種注釋：

##注釋1

--注釋2

3、LIKE支援兩個通配符比對選項：% 和 _

         %表示任何字元出現任意次數

         _表示任何字元出現一次

4、去重方式：

• DISTINCT：在映射之後對資料進行去重；
• UNION(并集運算)：将兩個子查詢拼接起來并去重；
• UNION ALL(并集運算)：将兩個子查詢拼接起來但不去重；
• EXCEPT(差集運算)：将第二個字查詢中的結果從第一個子查詢中去掉；MySQL 不支援 EXCEPT，Oracle 使用MINUS 替代 EXCEPT。
• INTERSECT(交集運算)：保留兩個子查詢中都有的結果并去重；MySQL 不支援 INTERSECT。

5、SQL查詢語句執行順序：

6、資料庫範式

• 第一範式(1NF)：強調的是列的原子性，即列不能夠再分成其他幾列。如電話列可進行拆分---家庭電話、公司電話
• 第二範式(2NF)：首先是 1NF，另外包含兩部分内容，一是表必須有主鍵；二是沒有包含在主鍵中的列必須完全依賴于主鍵，而不能隻依賴于主鍵的一部分。
• 第三範式(3NF)：首先是 2NF，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴。如Student表(學号，姓名，年齡，性别，所在院校，院校位址，院校電話)

  

7、資料庫常見對象有哪些？

  表(Table)、視圖(View)、序列(Sequence)、索引(Index)、存儲過程(Stored Procedure)、觸發器(Trigger)、使用者(User)以及同義詞(Synonym)等等。其中，表是關系資料庫中存儲資料的主要形式。

8、drop、delete與truncate的差別

SQL查詢優化 

• 避免在WHERE子句中使用in、not in (會導緻全表掃描), 可以使用exist和notexist代替。
• 避免在WHERE子句中使用in、not in (會導緻全表掃描)，對于連續的數值，能用between 就不要用 in 了。
• 将對于同一個表格的多個字段的操作寫到同一個sql中， 而不是分開成兩個sql語句實作。
• 盡量使用exists代替select count(1)來判斷是否存在記錄，count函數隻有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。
• 最好不要使用“ * ”。
• 盡量使用“>=”，不要使用“>”。
• 用IN替換OR。
• 把 substr函數 換成 like xxx%。
• 提高GROUP BY語句的效率, 可以通過将不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢傳回相同結果，但第二個明顯就快了許多。
• 應盡量避免在 where 子句中對字段進行表達式操作，這将導緻引擎放棄使用索引而進行全表掃描。
• 不要有超過5個以上的表連接配接(JOIN)，考慮使用臨時表或表變量存放中間結果。少用子查詢，視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。
• 使用别名，别名是大型資料庫的應用技巧，就是表名、列名在查詢中以一個字母為别名，查詢速度要比建連接配接表快1.5倍。
• 應盡量避免在 where 子句中使用 or 來連接配接條件，否則将導緻引擎放棄使用索引而進行全表掃描。
• 應盡量避免在 where 子句中對字段進行 null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描。
• 應盡量避免在where子句中對字段進行函數操作，這将導緻引擎放棄使用索引而進行全表掃描。
• 如果已知隻有一個查詢結果，推薦使用limit 1。
• 優化limit分頁：通常用limits來實作日常分頁，但當偏移量特别大時，查詢效率便會降低。因為Mysql不會跳過偏移量，而是直接擷取資料。
• 謹慎使用distinct關鍵詞：Distinct關鍵詞通常用于過濾重複記錄以傳回唯一記錄。當其被用于查詢一個或幾個字段時，Distinct關鍵詞将為查詢帶來優化效果。然而，在字段過多的情況下，Distinct關鍵詞将大大降低查詢效率。
• 使用explain分析SQL步驟。

SQL限制、鍵與索引

SQL限制用于規定表中的資料規則。

• 如果存在違反限制的資料行為，行為會被限制終止。
• 限制可以在建立表時規定(通過 CREATE TABLE 語句)，或者在表建立之後規定(通過 ALTER TABLE 語句)。

SQL限制類型：

• 非空限制NOT NULL：指某列不能存儲 NULL 值。比如員工姓名不能為空。
• 唯一限制UNIQUE：保證某列的每行必須有唯一的值。例如每個員工的電子郵箱不能重複。每個表可以有多個唯一限制。
• 主鍵限制PRIMARY KEY ：NOT NULL 和 UNIQUE 的結合。確定某列(或兩個列多個列的結合)有唯一辨別，有助于更容易更快速地找到表中的一個特定的記錄。例如員工編号，部門編号等。
• 外鍵限制FOREIGN KEY：保證一個表中的資料比對另一個表中的值的參照完整性，用于表示兩個表之間的引用關系。例如，員工屬于部門，是以員工表中的部門編号字段可以定義為外鍵，它引用了部門資訊表中的主鍵。
• 檢查限制CHECK：保證列中的值符合指定的條件。例如，薪水必須大于 0 ，性别隻能是男和女等。
• 預設值DEFAULT：規定沒有給列指派時的預設值。

SQL鍵類型：

• PrimaryKey(主鍵)——是表中的一個或多個字段的集合。它們不接受空值和重複值。并且表中隻存在一個主鍵。
• ForeignKey(外鍵)——在一個表中定義主鍵并在另一個表中定義字段的鍵被辨別為外鍵。
• UniqueKey(唯一鍵)——除了主鍵之外，表中還有更多鍵，它們隻辨別記錄，但唯一的差別是它們隻接受一個空值但不接受重複值。
• CandidateKey(候選密鑰)——在任何情況下，如果需要，任何候選密鑰都可以作為主鍵。
• CompoundKey(複合鍵)——此鍵是候選鍵和主鍵的組合。
• SuperKey(超級密鑰)——一個或多個密鑰的集合被定義為超級密鑰，它用于唯一地辨別表中的記錄。主鍵，唯一鍵和備用鍵是超級鍵的子集。
• AlternateKey(備用密鑰)——在任何情況下，如果需要，任何備用密鑰都可以作為主鍵或候選鍵。

資料庫索引：

• 主鍵索引: 主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個表隻能有一個主鍵。
• 唯一索引: 索引列的所有值都隻能出現一次，即必須唯一，值可以為空。
• 普通索引 : 基本的索引類型，值可以為空，沒有唯一性的限制。 
• 全文索引: 全文索引的索引類型為FULLTEXT。全文索引可以在varchar、char、text類型的列上建立。可以通過ALTER TABLE或CREATE INDEX指令建立。對于大規模的資料集，通過ALTER TABLE(或者CREATE INDEX)指令建立全文索引要比把記錄插入帶有全文索引的空表更快。

索引的差別：

l  一個表隻能有一個主鍵索引，但是可以有多個唯一索引。

l  主鍵索引一定是唯一索引，唯一索引不是主鍵索引。

l  主鍵可以與外鍵構成參照完整性限制，防止資料不一緻。

l  複合索引将多個列組合在一起建立索引，可以覆寫多個列。

l  外鍵索引：隻有InnoDB類型的表才可使用外鍵索引，保證資料的一緻性、完整性和級聯操作(基本不用)

l  全文索引：MySQL自帶的全文索引隻能用于MyISAM，并且隻能對英文進行全文檢索 (基本不用)。

SQL視圖

定義：

• 視圖是基于 SQL 語句的結果集的可視化的表
• 視圖是虛拟的表，本身不包含資料，也就不能對其進行索引操作。對視圖的操作和對普通表的操作一樣。

作用：

• 簡化複雜的 SQL 操作，比如複雜的聯結
• 隻使用實際表的一部分資料
• 通過隻給使用者通路視圖的權限，保證資料的安全性
• 更改資料格式和表示

建立視圖：

CREATEOR REPLACE VIEW emp_info

    AS

SELECTd.dept_name,j.job_title, e.emp_name, e.sex, e.email

  FROM employee e

  JOIN department d ON (d.dept_id = e.dept_id)

  JOIN job j ON (j.job_id = e.job_id);

SELECT*

  FROM emp_info

  WHERE emp_name = '資料部';

删除視圖：

DROPVIEW emp_info;

SQL函數

字元串函數：

日期和時間函數：

## 日期轉化為時間戳 ##

select unix_timestamp('2020-03-21 17:13:39')：得到1584782019

select unix_timestamp('20200321 13:01:03','yyyyMMdd HH:mm:ss') 得到 1584766863

select unix_timestamp('20200321','yyyyMMdd') 得到 1584720000

## 時間戳轉化為日期 ##

select from_unixtime (1584782175) 得到2020-03-21 17:16:15

select from_unixtime (1584782175,'yyyyMMdd') 得到20200321

select from_unixtime (1584782175,'yyyy-MM-dd')得到2020-03-21

## 日期和日期之間，也可以通過時間戳來進行轉換 ##

selectfrom_unixtime(unix_timestamp('20200321','yyyymmdd'),'yyyy-mm-dd') 得到2020-03-21

selectfrom_unixtime(unix_timestamp('2020-03-21','yyyy-mm-dd'),'yyyymmdd')得到20200321

#日期(2020-03-21 17:13:39)怎麼轉換為想要的格式(2020-03-21)

select to_date('2020-03-21 17:13:39') 得到 2020-03-21

select substr('2020-03-21 17:13:39',1,10) 得到 2020-03-21

日期之間怎麼進行加減操作？

## 使用date_sub (string startdate,int days)得到開始日期startdate減少days天後的日期##

select date_sub('2012-12-08', 10) 得到2012-11-28

## 使用date_add(string startdate,int days)得到開始日期startdate增加days天後的日期 ##

select date_add('2012-12-08', 10) 得到 2012-12-18

## 使用datediff(string enddate,string startdate)得到 結束日期減去開始日期的天數 ##

select datediff('2012-12-08','2012-05-09') 得到 213

(year(curdate()) - year(sage)) as 年齡

timestampdiff(year, sage, curdate()) as 年齡

數值函數：

流程函數：

格式化函數：

不同格式資料的轉換：cast

 ## bigint轉換為字元串

select cast(A to string) as A

歡迎轉發分享、點贊評論