SQL 学习笔记

SQL基础

参考书籍: 《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是一个视图
--视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

SQL练习

下面是我写的且被通过的代码：

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

Duplicate Emails

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

Combine Two Tables

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

Customers Who Never Order

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

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

Second Highest Salary

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

Delete Duplicate Emails

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

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

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;

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

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

---

原文链接：http://dinglisa.com/blog/2015/11/14/my-database-learning-notes/