November 14, 2015

SQL 学习笔记

Basic Knowledges in Database

Reference Book: 《SQL必知必会》

检索数据
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 = 'kim@thetoystore.com'
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是一个视图; 视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。

Some Examples

(Blow are the codes I wrote. All codes are accepted.)

1. Employees Earning More Than Their Managers
SELECT e1.Name AS Employee FROM Employee e1
JOIN Employee e2
ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary
2. Duplicate Emails
SELECT Email FROM Person
GROUP BY Email
HAVING count(*) > 1;
3. Combine Two Tables
SELECT FirstName, LastName, City, State FROM Person
LEFT JOIN Address
ON Person.PersonID = Address.PersonID;
4. Customers Who Never Order
SELECT Name AS Customers
FROM Customers LEFT JOIN Orders
ON Customers.ID = Orders.CustomerID
WHERE Orders.CustomerID IS NULL;
5. Rising Temperature
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
6. Second Highest Salary
SELECT MAX(Salary) FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee)
7. Delete Duplicate Emails
DELETE p1 FROM Person p1, Person p2
WHERE p1.ID > p2.ID AND p1.Email = p2.Email
8. Consecutive Numbers
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
9. Rank Scores
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;
10. Department Highest Salary
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
11. Nth Highest Salary
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
12. Trips and Users

TBD

13. Department Top Three Salaries

TBD

comments powered by Disqus