Learning SQLite! (2)

《SQL必知必会》笔记
（Lesson 19 存储过程、Lesson 20 事务处理、Lesson 21 游标 和 Lesson 22高级SQL特性 待看）

打开数据库

.open tysql.sqlite

设置格式

.header on;
.mode column;

检索 retrieve

SELECT prod_name From Products;

检索多列数据

SELECT prod_id,prod_name,prod_price FROM Products;

检索所有列

SELECT * FROM Products；

检索不同的行，关键字DISTINCT

SELECT DISTINCT  vend_id FROM Products;

Limiting results 及 设置“偏置”，在SQLite中用关键字LIMIT和OFFSET

SELECT prod_name FROM Products LIMIT 2 OFFSET 1;

排序检索结果，关键字ORDER BY

SELECT prod_name FROM Products ORDER BY prod_name;

/*根据检索数据中的第2行（即prod_price）和第3行（即prod_name）排序*/
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3; 

降序排列关键字DESC（不用DESC的话默认为升序ASC）

--按价格降序，名称升序排列
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC,prod_name;

筛选关键字WHERE，与ORDER BY一起用时要放在ORDER BY之前
可以使用=、<>、!=、<、<=、!<、>、>=、!>、BETWEEN、IS NULL等运算符，<>和!=均表示不等于，具体支持哪个运算符要看所用的DBMS，实测在SQLite中二者均可用

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49 ORDER BY prod_name DESC;
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 3 AND 5;
SELECT cust_name FROM Customers WHERE cust_email IS NULL;

WHERE可以结合AND、OR、NOT和IN使用，要注意运算符优先级，必要时使用括号

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id ='DLL01' AND prod_price <= 5;
SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id= 'BRS01') AND prod_price >= 10;
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01');

使用wildcard（只能在文本中用）关键字LIKE
符号%可以补全不限字数，符号_只能补全1个字（空格也算1个字）。

SELECT prod_id ,prod_name FROM Products WHERE prod_name LIKE 'Fish%';
SELECT prod_id ,prod_name FROM Products WHERE prod_name LIKE '__inch teddy bear';

使用wildcards的几条tips:

1.Don’t overuse wildcards. If another serch operator will do, use it instead.
2.When you do use wildcards, try to not use them at the beginning of the search pattern unless absolutely necessary.Search patterns that begin with wildcards are the slowest to process.
3.If wildcard symbols are misplaced, you might not return the data you intended.

连接 concatenate，SQLite中使用符号||

SELECT vend_name || '(' ||vend_country|| ')' FROM Vendors;

TRIM()函数，移除空格？
给连接后的新列起个别名，关键字AS，注意放AS的位置

SELECT vend_name || '(' ||vend_country|| ')' AS vend_title  FROM Vendors;

但是起了别名之后用SELECT vend_title FROM Vendors;其实是查不到的。

运算符

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

输出：

SQL中的几种函数：text functions、numeric functiongs、date and time funtions 和 system functions。
text funtion例子，UPPER()

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors;

date and time functions在不同的DBMS中差异较大，要参考DBMS的资料。

统计 aggregate functions：AVG()，COUNT()，MAX()、MIN()和SUM()。在COUNT()函数中是不计NULL的。MAX()函数用于文本列时，返回按该列排序时的最后一个；MIN()的话则是第一个。

SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
SELECT COUNT(*) AS num_cust FROM Customers;
SELECT MAX(prod_price) AS max_price FROM Products;
SELECT SUM(item_price*quantity) AS total_price FROM OrderItems;

--注意这里用了DISTINCT，与上面第一句不同
SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

--多列使用
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min FROM Products;

分类 group
关键字GROUP BY，放在WHERE之后，ORDER BY之前。

SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;

输出：

关键字HAVING用于筛选groups

SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id HAVING COUNT(*) >= 4;

输出：

HAVING和WHERE也有一起使用的时候

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

输出：

子查询 subquery

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

在子查询中可以进行运算（例子中WHERE的用法再琢磨！）

--注意这里WHERE的用法！
SELECT cust_name,cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers;

输出：

联结 joins
The basis for relational database design:

Having multiple occurrences of the same data is never a good thing.

equijoin or inner join (based on the testing of equality between to tables)
注意WHERE

SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;

输出：

代替子查询的例子

SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems WHERE OrderItems.prod_id = 'RGAN01' AND Orders.cust_id = Customers.cust_id AND OrderItems.order_num = Orders.order_num;

输出：

给表起个别名，便于缩短code，注意这个别名与列的别名不同的是：列的别名会返回client，而表的别名不会。

SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

self join，重复使用同一个表，效率通常会高于子查询

SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';

natural join (in which you select only columns that are unique)

SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

Every inner join you have created is actually a natural join, and you will probably never need an inner join that is not a natural join.

outer join，要限定LEFT或RIGHT，SQLite只支持LEFT

SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

一个总结：
图解SQL的inner join、left join、right join、full outer join、union、union all的区别

combined query，关键字UNION，合并多个SELECT，结果中相同的row会被自动移除，如果不想被移除就用UNION ALL

SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All';

插入数据，关键字INSERT INTO
比较安全的方法是先提供列名，再提供相应的数据，这样NULL的项的列名和‘NULL’其实都可以省略

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city,cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000006', 'Toy Land', '123 Any Street', 'New YORK', 'NY', '1111', 'USA', NULL, NULL);

还可以INSERT SELECT

还可以用SELECT FROM复制一个表

CREATE TABLE CustCopy AS SELECT * FROM Customers;

更新，关键字UPDATE，注意WHERE，如果没有WHERE那么整个表的每一项都被重新SET

UPDATE Customers SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com' WHERE cust_id = '1000000006';

删除，关键字DELETE，注意WHERE，如果没有WHERE那么整个表都被删掉

DELETE FROM Customers WHERE cust_id = '10000006';

使用UPDATE和DELETE之前最好用SELECT确认一下是否是正确的数据项

创建表，关键字CREATE TABLE
更新表的定义，关键字ALTER TABLE，在SQLite中也用该方法重命名表
删除表，关键字DROP TABLE

views，do not contain any columns or data, contain queries
SOLite只支持read-only views，只能创建和读，不能更新

Why use views:

1. To reuse SQL statements.
2. To simplify complex SQL operations.
3. To expose parts of table instead of complete tables.
4. To secure data.
5. To change data formatting and representation.

创建views，关键字CREATE VIEW

It is a good idea to create views that are not tied to specific data.

创建stored procedure，关键字EXECUTE

transaction processing 事务处理，is used to maintain database integrity by ensuring that batches of SQL operations execute completely or not at all.