A query is a request for data or information from a database.

Here's the structure of a basic query. You can see that with this query we can select specific data from a table by adding where we can filter the data based on certain conditions.

Synta: is the predetermined structure of a language that includes all required words, symbols, and punctuation, as well as their proper placement.

• Use SELECT to choose the columns you want to return.

• Use FROM to choose the tables where the columns you want are located.

• Use WHERE to filter for certain information.

• Use ORDER BY to sort your datasets.

• Use LIMIT to constrain your output lines.

  SELECT 
    *
  FROM 
    `bigquery-public-data.sdoh_cdc_wonder_natality.county_natality` 
  WHERE 
    Year = '2018-01-01'
  ORDER BY
    Births DESC
  LIMIT 
    10
  -- 順序不太能變
  -- LIMIT 不能用在 WHERE 的子查詢裡
  WHERE
  	YEAR(HIREDATE) IN (
  			SELECT
  				DISTINCT YEAR(HIREDATE)
  			FROM EMP
  			ORDER BY
  				YEAR(HIREDATE)
  			LIMIT 2
      )-- 這樣會錯
  
  WHERE
  	YEAR(HIREDATE) IN (
  		SELECT *
  			FROM (
  				SELECT
  					DISTINCT YEAR(HIREDATE)
  				FROM EMP
  				ORDER BY
  					YEAR(HIREDATE)
  				LIMIT 2
  			) AS t
      )-- 這樣才會對
  

  limit
      [略過筆數,] 顯示筆數
  

• Use INSERT INTO to insert other data.

• Use UPDATE, SET, to update information.

• Use CREATE if you want to create a new table.

  • Just running a SQL query doesn't actually create a table for the data we extract. It just stores it in our local memory. To save it, we'll need to download it as a spreadsheet or save the result into a new table.

• Use DROP if you want to drop a table.

INSERT INTO customer_data.customer_address --資料庫名字
	(customer_id, address, city, state, zipcode, country) -- 形式 
VALUES
	(2645, 333 SQL Road', 'Jackson', 'MI, 49202, 'US') -- 要插入的東西

-- 也可以接子查詢
INSERT INTO EMP
	(SELECT * FROM XXX WHERE ???=XXX)

--DELETE 刪除資料列
DELETE FROM DEPT
WHERE 條件

-- UPDATE 更新資料列
UPDATE customer_data.customer_address --要更新的資料庫名字
SET address = '123 New Address' -- 要更新的欄位及內容
WHERE customer_id = 2645 -- 要更新的那個條件

CREATE customer_address IF NOT EXISTS

DROP customer_address IF EXISTS

TCL 交易控制

⚡只有 innodb 支援

開頭

• set autocommit = 0/1: 啟用/停用交易控制模式。是「全局」設定，之後的所有敘述都會受影響
  • 0 / off:啟用交易控制模式
  • 1 / on:停用交易控制模式(預設)
  • select @@autocommit 可以看到目前 @@autocommit 的狀態
  • 變數autocommit的意義是自動送交，所以設為0或off才是啟用交易控制(手動控制)
  • set autocommit敘述只會影響當前連線，其他連線不會受影響
  • 通常交易控制結束後，會馬上改回1或on，保持自動送交
  • 可用右邊敘述查詢目前變數autocommit之值
• start transaction : 開啟單一交易控制。只有「當前範圍」內敘述會受影響