SQL 語法筆記

Between

SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;

like: 後面要加%

SELECT * FROM Customers
WHERE City LIKE 's%';

distinct: 後面好像只能加一個

SELECT DISTINCT Country FROM Customers;
-- 這樣子每一個country就只會跑一筆record出來

SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);

where + AND OR NOT

-- AND  
SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';

-- OR
SELECT * FROM Customers
WHERE Country='Germany' OR Country='Spain';

-- NOT : 接著條件關鍵字的前面
SELECT * FROM Customers
WHERE NOT Country='Germany';

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

NULL

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

Insert Into: 建立一筆新的record

-- Insert 到所有column
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

-- Insert 部份
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

Update: 更新既有的record，一定要加where 否則整個表都會被update

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Delete: 刪指定table的record, 也可以把table的內容全刪掉

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

MIN() and MAX()

SELECT MIN(Price) AS SmallestPrice
FROM Products;

COUNT(), AVG() and SUM()

SELECT COUNT(ProductID)
FROM Products;

SELECT AVG(Price)
FROM Products;

SELECT SUM(Quantity)
FROM OrderDetails;

In: 用括號括起來，各個字串用引號包並用逗號格開

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

UNION: 預設是distinct

SELECT City FROM Customers
UNION 
SELECT City FROM Suppliers
ORDER BY City;

-- 如果要全部的話
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

group by: 把有指令欄位中相同value的record給group起來

-- 把有county 是一樣的record給group起來
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Having: 有運算時設條件 (Sum(), Count(), Avg())

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

Exists 和where配合的條件限制

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

ANY and ALL

-- Any: 只要中一個就可
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

-- All: 要全部都符合 
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

CREATE/DROP/BACKUP DATABASE

CREATE DATABASE testDB;

DROP DATABASE testDB;

BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak'
WITH DIFFERENTIAL;
-- A differential back up reduces the back up time (since only the changes are backed up).

Create table

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

-- 從其他table來建table
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;
-- As 後面不能加東西 不是Alias

Alter table

ALTER TABLE Customers
ADD Email varchar(255);

ALTER TABLE Customers
DROP COLUMN Email;

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year; -- 改變欄位屬性

ALTER TABLE Persons
DROP COLUMN DateOfBirth;

contraint

-- 放在 create table 或是alter  用來限制欄位的條件
create table Employee

(EID integer not null primary key,

First_Name varchar(15) not null,

Last_Name varchar(15) not null,

Address varchar(30));

以上資料出自

https://www.w3schools.com/sql/