SQL 学习笔记 (1)

SELECT prod_name
FROM Products
ORDER BY prod_name

SELECT prod_name
FROM Products
WHERE prod_price = 3.49

LENGTH()
UPPER()
LEFT()
RIGHT()
LOWER()
LTRIM()
RTRIM()

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2
ORDER BY items, order_num

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01')

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
--注意：在该情况下，要保证所有的联结都有WHERE字句，否则会返回笛卡尔积(cross join)的不正确数据

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
--使用INNER JOIN联结时，联结条件用特定的ON字句而不是WHERE字句
--ANSI SQL规范首选INNER JOIN语法

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';
--子查询并不总是执行复杂SELECT操作最好方法，使用联结查询往往会更有效

SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
--函数调用COUNT(Orders.order_num)对每个顾客的订单计数，将它作为num_ord返回
--AS用于使用表别名

UNION
UNION ALL --使用UNION ALL不会取消重复的行

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('10005', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA');

UPDATE Customers
SET cust_email = '[email protected]'
WHERE cust_id = '10005';

DELETE FROM Customers
WHERE cust_id = '10006';

CREATE TABLE Products
(
	prod_id    CHAR(10)      NOT NULL,
	vend_id    CHAR(10)      NOT NULL,
	prod_name  CHAR(254)     NOT NULL,
	prod_proce DECIMAL(8,2)  NOT NULL,
	prod_desc  VARCHAR(1000) NULL
)

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

DROP TABLE CustCopy;

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
--ProductCustomers是一个视图
--视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

SELECT e1.Name AS Employee FROM Employee e1
JOIN Employee e2
ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary

SELECT Email FROM Person
GROUP BY Email
HAVING count(*) > 1;

SELECT FirstName, LastName, City, State FROM Person
LEFT JOIN Address
ON Person.PersonID = Address.PersonID;

SELECT Name AS Customers
FROM Customers LEFT JOIN Orders
ON Customers.ID = Orders.CustomerID
WHERE Orders.CustomerID IS NULL;

SELECT t1.Id FROM Weather t1
INNER JOIN Weather t2
ON TO_DAYS(t1.Date)=TO_DAYS(t2.Date) + 1
AND t1.Temperature > t2.Temperature

SELECT MAX(Salary) FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee)

DELETE p1 FROM Person p1, Person p2
WHERE p1.ID > p2.ID AND p1.Email = p2.Email

SELECT DISTINCT(l1.num)
FROM Logs l1, Logs l2, Logs l3
WHERE l1.ID + 1 = l2.ID
AND l2.ID + 1 = l3.ID
AND l1.Num = l2.Num
AND l2.Num = l3.Num

SELECT Scores.Score, COUNT(Ranking.Score) AS RANK
FROM Scores
   , (
   SELECT DISTINCT Score
   FROM Scores
   ) Ranking
WHERE Scores.Score <= Ranking.Score
GROUP BY Scores.Id, Scores.Score
ORDER BY Scores.Score DESC;

SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary
FROM Employee E,
    (SELECT DepartmentId,max(Salary) as max
    FROM Employee GROUP BY DepartmentId) T,
    Department D
WHERE E.DepartmentId = T.DepartmentId
AND E.Salary = T.max
AND E.DepartmentId = D.id

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
	DECLARE M INT;
	SET M=N-1;
	RETURN (
SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    LIMIT M, 1);
END