Database Management (資料庫管理)

Last updated on 04/10/2024
home . back

SQL Example

 

Consider northwind(simple) ERD

SQL SELECT

Syntax Example
SELECT SELECT CustomerName,City FROM Customers;    
SELECT * SELECT * FROM Customers;    

Alias

 SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;		 SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address
FROM Customers;		 SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
SELECT TOP

SELECT TOP 2 * FROM Customers; (for MS SQL server, Access)

SELECT* FROM Customers LIMIT 2; (for MySQL)


 SELECT TOP 50 PERCENT * FROM Customers; (for MS SQL server, Access)  
DISTINCT SELECT DISTINCT City FROM Customers;    
WHERE

SELECT * FROM Customers
WHERE Country='Mexico';


 SELECT * FROM Customers
WHERE CustomerID=1;		  
Wildcard

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

 

 SELECT * FROM Customers
WHERE City LIKE '_erlin';		  
LIKE SELECT * FROM Customers
WHERE City LIKE 's%';		 SELECT * FROM Customers
WHERE City LIKE '%s';		 SELECT * FROM Customers
WHERE Country LIKE '%land%';
AND SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';		    
OR SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';		    
ORDER BY SELECT * FROM Customers
ORDER BY Country;		 SELECT * FROM Customers
ORDER BY Country DESC;

SELECT * FROM Customers
ORDER BY Country, CustomerName;

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
IN SELECT * FROM Customers
WHERE City IN ('Paris','London');		    
BETWEEN

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

 SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);		 SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';
JOIN (simple) SELECT Customers.CustomerName, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;		    
INNER JOIN SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;		    
LEFT JOIN SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;		    
RIGHT JOIN  SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;		    
FULL OUTER JOIN       

GROUP BY

 SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;		 SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;		  
HAVING SELECT country, count(*)
FROM customers
GROUP BY country
HAVING count(*) >5
ORDER BY country DESC		    
       

SQL FUNCTIONS

Syntax Example

AVG()

 SELECT AVG(Price) AS PriceAverage
FROM Products;		 SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);		  

COUNT()

 SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;		 SELECT COUNT(*) AS NumberOfOrders FROM Orders; SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;

MAX()

 SELECT MAX(Price) AS HighestPrice
FROM Products;		    

MIN()

 SELECT MIN(Price) AS SmallestOrderPrice
FROM Products;		    

SUM()

 SELECT SUM(Quantity) AS TotalItemsOrdered
FROM OrderDetails;		    
GROUP BY SELECT Country, COUNT(customerid) AS noOfCustomers
FROM Customers
GROUP BY Country
ORDER BY noOfCustomers DESC;		    
       
       
       

 

SQL INSERT, UPDATE, DELETE

Syntax Example

INSERT

 INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');		 INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');		 INSERT INTO Customers
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

UPDATE

 UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';		 UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'; (小心使用)		  

DELETE

 DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';		 DELETE FROM Customers; (小心使用)  

 

SQL CREATE, DROP

Syntax Example

CREATE DATABASE

 CREATE DATABASE my_db;    

CREATE TABLE

 CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);		 CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)		 CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

PRIMARY KEY

 CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)		 CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

FOREIGN KEY

 

 CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)		 CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

CHECK

 CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)		 CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
AUTO-INCREMENT

CREATE TABLE Persons
(
P_Id int IDENTITY(1,1) NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

[MS SQL Server 適用]

CREATE TABLE Persons
(
P_Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

[MS ACCESS 適用]

DEFAULT

 CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)		  

DROP

 DROP DATABASE my_db; DROP TABLE Persons;