postgresql笔记

因为傻鸟里面自带postgresql，因此就买了sql基础教程和sql进阶教程，依然以笔记的方式进行记录

初始

1. service postgresql start //因为现在傻鸟里面postgresql不再开机自启了，所以需要手动启动。
2. su - postgresql //密码为该用户名的密码
3. psql -U postgresql -d shop

数据准备

1. create database shop; //创建数据库

2. 1 2 3 4 5 6 7 8

   create table Product ( product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER , purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id) );

3. 注：定长字符串（即char类型）在字符数未达到最大长度时会用半角空格补足。

4. drop table Product; //删除表
5. alter table Product ADD COLUMN product_name_pinyin VARCHAR(100); //追加列
6. alter table Product DROP COLUMN product_name_pinyin; //删除列

7. 1 2 3 4 5 6 7 8 9 10

   BEGIN TRANSACTION; INSERT INTO Product VALUES('0001' , 'T恤衫', '衣服', '1000', '500', '2009-09-20'); INSERT INTO Product VALUES('0002' , '打孔器', '办公用品', '500', '320', '2009-09-11'); INSERT INTO Product VALUES('0003' , '运动T恤', '衣服', '4000', '2800', NULL ); INSERT INTO Product VALUES('0004' , '菜刀', '厨房用具', '3000', '2800', '2009-09-20'); INSERT INTO Product VALUES('0005' , '高压锅', '厨房用具', '6800', '5000', '2009-01-15'); INSERT INTO Product VALUES('0006' , '叉子', '厨房用具', '500', NULL, '2009-09-20'); INSERT INTO Product VALUES('0007' , '擦菜板', '厨房用具', '880', '790', '2009-04-28'); INSERT INTO Product VALUES('0008' , '圆珠笔', '办公用品', '100', NULL, '2009-11-11'); COMMIT;

8. ALTER TABLE Poduct RENAME TO Product; //修改表名

9. 1 2 3 4 5 6 7 8

   CREATE TABLE ProductIns ( product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER DEFAULT 0, purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id) );

10. 1 2 3 4 5 6 7 8

    create table ProductCopy ( product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER , purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id) );

11. 1 2 3 4 5

    create table ProductType ( product_type VARCHAR(32) NOT NULL, sum_sale_price INTEGER , sum_purchase_price INTEGER , PRIMARY KEY (product_type) );

12. 1 2 3 4

    create table SampleMath (m NUMERIC (10,3), n INTEGER , p INTEGER);

13. 1 2 3 4 5 6 7 8 9 10 11 12 13

    BEGIN TRANSACTION; INSERT INTO SampleMath(m, n, p) values (500, 0 , NULL); INSERT INTO SampleMath(m, n, p) values (-180, 0 , NULL); INSERT INTO SampleMath(m, n, p) values (NULL, NULL, NULL); INSERT INTO SampleMath(m, n, p) values (NULL, 7 , 3); INSERT INTO SampleMath(m, n, p) values (NULL, 5 , 2); INSERT INTO SampleMath(m, n, p) values (NULL, 4 , NULL); INSERT INTO SampleMath(m, n, p) values (8,NULL , 3); INSERT INTO SampleMath(m, n, p) values (2.27, 1 , NULL); INSERT INTO SampleMath(m, n, p) values (5.555, 2 , NULL); INSERT INTO SampleMath(m, n, p) values (NULL, 1 , NULL); INSERT INTO SampleMath(m, n, p) values (8.76, NULL , NULL); COMMIT;

14. 1 2 3 4

    create table SampleStr (str1 VARCHAR(40), str2 VARCHAR(40), Str3 VARCHAR(40));

15. 1 2 3 4 5 6 7 8 9 10 11 12 13

    BEGIN TRANSACTION; INSERT INTO SampleStr (str1, str2, str3) values ('opx' , 'rt' , NULL ); INSERT INTO SampleStr (str1, str2, str3) values ('abc', 'def' , NULL); INSERT INTO SampleStr (str1, str2, str3) values ('山田', '太郎' ,'是我'); INSERT INTO SampleStr (str1, str2, str3) values ('aaa', NULL ,NULL); INSERT INTO SampleStr (str1, str2, str3) values (NULL, 'xyz' ,NULL); INSERT INTO SampleStr (str1, str2, str3) values ('!@#$%', NULL ,NULL); INSERT INTO SampleStr (str1, str2, str3) values ('ABC', NULL ,NULL); INSERT INTO SampleStr (str1, str2, str3) values ('aBC', NULL ,NULL); INSERT INTO SampleStr (str1, str2, str3) values ('abc太郎', 'abc' ,'ABC'); INSERT INTO SampleStr (str1, str2, str3) values ('abcdefabc', 'abc' ,'ABC'); INSERT INTO SampleStr (str1, str2, str3) values ('micmic', 'i' ,'I'); COMMIT;

16. 1 2 3

    create table SampleLike ( strcol VARCHAR(6) NOT NULL, PRIMARY KEY (strcol));

17. 1 2 3 4 5 6 7 8

    BEGIN TRANSACTION; Insert INTO SampleLike (strcol) VALUES ('abcddd'); Insert INTO SampleLike (strcol) VALUES ('dddabc'); Insert INTO SampleLike (strcol) VALUES ('abdddc'); Insert INTO SampleLike (strcol) VALUES ('abcdd'); Insert INTO SampleLike (strcol) VALUES ('ddabc'); Insert INTO SampleLike (strcol) VALUES ('abdd c'); COMMIT;

18. 1 2 3 4 5 6

    create table ShopProduct (shop_id CHAR(4) NOT NULL, shop_name VARCHAR(200) NOT NULL, product_id CHAR(4) NOT NULL, quantity INTEGER NOT NULL, PRIMARY KEY (shop_id, product_id));

19. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

    BEGIN TRANSACTION; INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000A', '东京', '0001', 30); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000A', '东京', '0002', 50); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000A', '东京', '0003', 15); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B', '名古屋', '0002', 30); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B', '名古屋', '0003', 120); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B', '名古屋', '0004', 20); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B', '名古屋', '0006', 10); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B', '名古屋', '0007', 40); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000C', '大阪', '0003', 20); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000C', '大阪', '0004', 50); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000C', '大阪', '0006', 90); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000C', '大阪', '0007', 70); INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000D', '福冈', '0001', 100); COMMIT;

20. 1 2 3 4 5 6 7 8 9

    create table Product2 ( product_id char(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER , purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id));

21. 1 2 3 4 5 6 7

    BEGIN TRANSACTION; INSERT INTO Product2 VALUES('0001', 'T恤衫', '衣服', 1000, 500 , '2008--09-20'); INSERT INTO Product2 VALUES('0002', '打孔器', '办公用品', 500, 320 , '2009-09-11'); INSERT INTO Product2 VALUES('0003', '运动T恤', '衣服', 4000, 2800 , NULL); INSERT INTO Product2 VALUES('0009', '手套', '衣服', 800, 500, NULL); INSERT INTO Product2 VALUES('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20'); COMMIT;

22. 1 2 3 4 5 6

    create table InventoryProduct ( inventory_id CHAR(4) NOT NULL, product_id CHAR(4) NOT NULL, inventory_quantity INTEGER NOT NULL, PRIMARY KEY (inventory_id, product_id));

23. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

    BEGIN TRANSACTION; INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001', '0001', 0); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001', '0002', 120); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001', '0003', 200); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001', '0004', 3); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001', '0005', 0); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001', '0006', 99); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001', '0007', 999); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P001', '0008', 200); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P002', '0001', 10); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P002', '0002', 25); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P002', '0003', 34); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P002', '0004', 19); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P002', '0005', 99); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P002', '0006', 0); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P002', '0007', 0); INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) values('P002', '0008', 18); COMMIT;

select语句

(万物基于select。。。。。[啊呸，咩有insert你select毛线])

1. select * from Product; //大力出奇迹
2. select product_id, product_name from Product;
3. select product_id as id, product_name as name, purchase_price as price from Product; //起外号，当然也可以用中文，但是需要使用双引号引起来（书上是这样写的但是在鸟里面是不需要用双引号的）。
4. select ‘fff’ as string, 18 as number, product_id, product_name from Product; //查询中插入常数或者字符串。
5. select DISTINCT product_type from Product; //清除重复内容 注：NULL的条目也会缩减为一条，并且DISTINCT只能放在第一个列名之前。
6. select * from Product where product_type = ‘衣服’;
7. 注释语句： –或者/××/
8. select product_name, sale_price, sale_price * 2 as “salse_price_x2” from Product; //进行四则运算。 注：包含null的运算最后结果肯定为NULL。
9. select * from Product where sale_price <> 500; //不等于500
10. select * from Product where sale_price <= 500; //小于等于500
11. 注：一定让不等号在左，等号在右。
12. 注：不能对NULL使用比较运算符。
13. select * from Product where purchase_price is null; //字段内容为空
14. select * from Product where not sale_price <= 500; //大于500
15. AND运算符在两侧的查询条件同时成立时整个查询条件才成立。
16. OR运算符在其两侧的查询条件有一个成立时整个查询条件成立。
17. select * from Product where product_type = ‘厨房用具’ and sale_price >= 3000;
18. select * from Product where product_type = ‘厨房用具’ or sale_price >= 3000;
19. AND运算符优先级高于OR，可以使用括号调整优先级顺序。
20. select * from Product where product_type = ‘办公用品’ and ( regist_date = ‘2009-09-11’ or regist_date = ‘2009-09-20’);

对表进行聚合查询

1. 常用聚合函数：
   • COUNT： 计算表中的记录数（行数）
   • SUM： 计算表中数值列中数据的合计值
   • AVG： 计算表中数值列中数据的平均值
   • MAX： 求出表中任意列中数据的最大值
   • MIN： 求出表中任意列中数据的最小值
2. select count(×) from Product;
3. select sum(sale_price) from Product;
4. select sum(sale_price) as sale_price ,sum(purchase_price) as purchase_price from Product;
5. select avg(sale_price) from Product;
6. select MAX(sale_price), MIN(purchase_price) from Product;
7. select count(distinct product_type) from Product; //计算去除重复数据之后的数据行数。
8. select product_type, COUNT(×) from Product GROUP BY product_type; //分组计算
9. select purchase_price, COUNT(×) FROM Product where product_type = ‘衣服’ GROUP BY purchase_price;
10. 注：group by 子句中不能使用别名
11. 只有select子句和having子句以及order by子句能使用COUNT等聚合函数
12. select product_type, count(×) from Product group by product_type having COUNT(×) = 2; //having 子句使用方法
13. select product_type, avg(sale_price) from Product group by product_type having avg(sale_price) >= 2500; //having 子句使用方法
14. select product_type, count(×) from Product group by product_type having product_type = ‘衣服’; 结果等同于 select product_type, count(×) from Product where product_type = ‘衣服’ group by product_type;
15. select * from Product order by sale_price; //升序排序
16. select * from Product order by sale_price desc; //降序排序
17. select * from Product order by sale_price， purchase_price; 在sale_price相同时使用purchase_price排序。
18. select product_id as id, sale_price as sp from Product order by id;
19. select子句中未查询的列也可以在order by子句中使用。
20. order by 子句也可以使用聚合函数。
21. select product_type, count(×) from Product group by product_type order by count(×);

数据更新

恩，万物基于insert(create你闭嘴)。

1. insert into ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) values (‘0001’, ‘T恤衫’, ‘衣服’, 1000, 500, ‘2009-09-20’);

2. 1 2 3

   INSERT INTO ProductIns values ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'), ('0003', '运动T恤', '衣服', 4000, 2800, NULL), ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

3. insert into ProductIns values (‘0005’, ‘高压锅’, ‘厨房用具’, 6800, 5000, ‘2009-01-15’); //省略列清单

4. insert into ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) values (‘0006’, ‘叉子’, ‘厨房用具’, 500, NULL, ‘2009-09-20’); //插入NULL
5. insert into ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) values (‘0007’, ‘擦菜板’, ‘厨房用具’, DEFAULT, 790, ‘2009-04-28’); //显式插入默认值
6. insert into ProductIns ProductCopy(product_id, product_name, product_type, sale_price, purchase_price, regist_date) select product_id, product_name, product_type, sale_price, purchase_price, regist_date from Product; //copy一个表中的数据
7. insert into ProductType (product_type, sum_sale_price, sum_purchase_price) select product_type, sum(sale_price), sum(purchase_price) from Product GROUP BY product_type; //计算统计值
8. delete from Product where sale_price >= 4000;
9. update Product set regist_date = ‘2009-10-10’;
10. update Product set sale_price = sale_price * 10 where product_type = ‘厨房用具’;
11. update Product set sale_price = sale_price × 10, purchase_price = purchase_price / 2 where product_type = ‘厨房用具’ ; //多列更新
12. update Product set (sale_price, purchase_price) = (sale_price × 10, purchase_price / 2) where product_type = ‘厨房用具’;
13. 事务：需要在同一个处理单元中执行的一系列更新处理的集合。
14. COMMIT 在事务末尾为提交， rollback 数据回滚，回滚到开始事务之前的状态。
15. DBMS事务的四种特性：
    • 原子性（Atomicity）：指在事务结束时，所包含的更新处理要么全都执行，要么完全不执行。
    • 一致性（Consistency）:事务中所包含的数据更新处理要满足数据库提前设置的约束。
    • 隔离性（isolation）：不同事务之间互不干扰。例如在某个事务中进行的更改，在该事务完成之前对其他事务而言是不可见的。
    • 持久性（Durability）：在事务结束后，DBMS能保证该时间点的数据状态会被保存。

复杂查询

1. 视图和表的区别：
   • 表中保存的是实际的数据。
   • 视图中保存的是select语句，视图本身并不保存数据。
2. 视图的优点：
   • 因为视图不保存数据，因此节省存储设备的容量。
   • 将频繁使用的select语句保存成视图，这样就不需要每次重写select语句。

3. 1 2 3

   create view ProductSum(product_type, cnt_product) as select product_type, count(*) from Product group by product_type;

4. select * from ProductSum; //查询试图（貌似和查表没啥区别）

5. 可以创建多重视图（少用，因为貌似会降低sql的性能）

6. 视图的限制

   • 定义视图时不可以使用order by语句。因为视图和表一样，数据行都是没有顺序的。
   • 可以更新视图的限制条件：
     • select语句未使用distinct。
     • from语句只有一张表。
     • 未使用group by语句。
     • 未使用having语句。

7. 1 2 3

   create view ProductJim(product_id, product_name, product_type, sale_price, purchase_price, regist_date) as select * from Product where product_type = '办公用品';

8. postgresql中的视图会被初始设定为只读，如果需要允许更新时，需要执行下面的语句。

9. 1 2 3 4 5 6 7 8 9

   create or replace rule insert_rule as on insert to ProductJim do instead insert into Product VALUES( new.product_id, new.product_name, new.product_type, new.sale_price, new.purchase_price, new.regist_date);

10. drop view ProductJim; //删除视图

11. drop view ProductJim CASCADE;删除关联视图
12. 子查询：一次性的视图，用完即删。就是将用来定义视图的select语句直接写到from子句中。
13. select product_type, cnt_product from (select product_type, count(×) as cnt_product from Product group by product_type) as ProductSum; //子查询
14. 子查询可以无限嵌套
15. 原则上子查询必须设定名称。
16. 标量子查询就是必须并且只能返回一行一列的结果的子查询。
17. select product_id, product_name,sale_price from Product where sale_price > (select avg(sale_price) from Product); //标量子查询
18. 标量子查询的语句可以用在任何可以使用单一值的地方。
19. select product_id, product_name, sale_price, (select avg(sale_price) from Product) as avg_price from Product;
20. select product_type, product_name, sale_price from Product as p1 where sale_price > (select avg(sale_price) from Product as p2 where p1.product_type = p2.product_type); //关联子查询

函数、谓词、case表达式

1. 算术函数（进行数值计算）

   • 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

     + select m , abs(m) as abs_col from SampleMath; //计算绝对值 + select n, p, mod(n,p) as mod_col from SampleMath; //计算求余 + select m, n , round(m, n) as round_col from SampleMath; //对m列的数值进行n列位数的四舍五入， 即n表示保留小数的位数 2. 字符串函数（进行字符串操作） + select str1, str2, str1 || str2 as str_concat from SampleStr; //字符串拼接 + select str1 , LENGTH(str1) as len_str from SampleStr; //字符串长度 + select str1, LOWER(str1) as lower_str from SampleStr ; //小写转换， UPPER是大写转换。 + select str1, str2, str3, REPLACE(str1, str2, str3) as rep_str from SampleStr; //将str1中的str2替换为str3。 + select str1, substring(str1 from 3 for 2) as sub_str from SampleStr; //将str1中的第三个字符开始截取2个字符。 3. 日期函数（进行日期操作） + select current_date; //查询当前日期 + select current_time; //查询当前时间 + select current_timestamp; //查询当前日期和时间 + select current_timestamp, extract(year from current_timestamp) as year, extract(month from current_timestamp) as month, extract(day from current_timestamp) as day, extract(hour from current_timestamp) as hour; //extract截取时间函数 4. 转换函数（转换数据类型和值） + select cast('0001' as INTEGER) as int_col; //字符串转换为数值类型 + select cast('2009-12-14' as date) as date_col; //字符串转换为日期类型 + select COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) as col_2, COALESCE(NULL, NULL, '2009-11-01') as col_3; //将null转换为其他值 + select COALESCE(str2, 'NULL') from SampleStr; //将null转换为字符串 + select str1, str2, COALESCE(str2, str1) from SampleStr; // 将str2列中的null替换为st1列中的值 5. 聚合函数（进行数据聚合） 6. 谓词就是返回值为真值的函数 7. 比较运算符的正式名称就是比较谓词。 8. 当需要进行字符串的部分一致查询时需要使用like谓词。 9. 字符串的前方一致，中间一致和后方一致 + 前方一致：以查询字符串开头的字符串。 + 中间一致：字符串含有作为查询条件的字符串。 + 后方一致：以查询字符串作为结尾的字符串。 10. select * from SampleLike where strcol like 'ddd%'; //前方一致查询 11. select * from SampleLike where strcol like '%ddd%'; //中间一致查询 12. select * from SampleLike where strcol like '%ddd'; //后方一致查询 13. 可以使用_代替%来进行模糊匹配，但是_只表示任意一个字符 14. select * from SampleLike where strcol like 'abc__'; //前方一致查询 15. select * from Product where sale_price between 100 and 1000; //范围查询，注：between查询包含临界值 16. select * from Product where purchase_price is null; // 应该不用解释了，还有一个not null 17. select * from Product where purchase_price in (320, 500, 5000); // 等同于 purchase_price = 320 or purchase_price = 500 or purchase_price = 5000 18. 可以将子查询作为in谓词的参数。 19. select * from Product where product_id in (select product_id from ShopProduct where shop_id = '000C'); //别忘了还有not in 20. select * from Product AS P where exists (select * from ShopProduct as SP where SP.shop_id = '000C' AND SP.product_id = P.product_id); 21. ```case表达式 select product_name , case when product_type = '衣服' then 'A: ' || product_type when product_type = '办公用品' then 'B: ' || product_type when product_type = '厨房用具' then 'C: ' || product_type else NULL end as abc_product_type from Product;

2. 1 2 3 4 5 6 7

   select sum(case when product_type = '衣服' then sale_price else 0 end) as sum_price_clothes, sum(case when product_type = '厨房用具' then sale_price else 0 end) as sum_price_kitchen, sum(case when product_type = '办公用品' then sale_price else 0 end) as sum_price_office from Product;

表的加减法

1. select product_id, product_name from Product union select product_id, product_name from Product2; //并集运算
2. select product_id, product_name from Product union all select product_id, product_name from Product2; //并集运算但是会显示重复记录
3. union运算注意事项：
   • 作为运算对象的记录的列数必须相同。
   • 作为运算对象的记录中列的类型必须一致。
   • 可以使用任意select语句，但是order by 语句只能在最后使用一次。
4. select product_id, product_name from Product intersect select product_id, product_name from Product2; //交集运算
5. select product_id, product_name from Product intersect all select product_id, product_name from Product2; //交集运算并显示重复记录
6. select product_id, product_name from Product except select product_id, product_name from Product2; //差集运算（啥，你问什么是差集。。。。。你可以去问问你高(高中)数（数学）老师，顺便拜个年。）
7. select SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price from ShopProduct as SP inner join Product as P on SP.product_id = P.product_id; //对两张表进行内联结
8. select SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price from ShopProduct as SP inner join Product as P on SP.product_id = P.product_id where SP.shop_id = ‘000A’; //对两张表进行内联结并查询东京店的内容
9. 内联结要点
   • 需要在from子句中指定多张表。
   • 进行内联结的时候必须使用on子句，并且要书写在from和where之间。on子句也可以使用and和or
   • 使用联结时select子句中的列需要按照<表的别名或者表名>.<列名>的格式来进行书写。
10. select SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price from ShopProduct as SP right outer join Product as P on SP.product_id = P.product_id; //外联结查询 ：指定from 子句中写在右侧的表为主表，最终查询结果会包含主表的所有数据。

11. 1 2 3 4 5 6 7

    select SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity from ShopProduct as SP inner join Product AS P ON SP.product_id = P.product_id inner join InventoryProduct as IP ON SP.product_id = IP.product_id where IP.inventory_id = 'P001'; //是不是看着头晕，是不是以为这里会有分析，没事，晕啊晕啊也就习惯了

12. select SP.shop_id, SP.shop_name, SP.product_id, P.product_name from ShopProduct as SP cross join Product as P; //交叉联结，又称迪卡儿积

SQL高级处理

1. OLAP函数，你可以称为窗口函数或者分析函数，可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。
2. 窗口函数的公式 <窗口函数> over ([PARTITION BY <列清单>] order by <排序用列清单>)
3. 可以作为窗口函数的聚合函数
   • 能够作为窗口函数的聚合函数（sum,avg,count,max,min)
   • rank, dense_rank, row_number 等专用窗口函数
   • rank函数在计算排序时，如果存在相同位次的记录，则会跳过之后的位次，例如：1,1,1,4
   • dense_rank函数在计算排序时，在遇到相同位次的记录也不会跳过之后的位次，例如：1,1,1,2,3
   • row_number函数赋予唯一的位次，例如：1,2,3,4
4. select product_name, product_type, sale_price, RANK () OVER (PARTITION BY product_type order by sale_price) as ranking from Product; // 根据不同的商品品类，按照销售单价从低到高的顺序创建排序表
5. select product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) as ranking from Product; //不按照商品品类，直接进行排序
6. 通过PARTITION BY 分组之后的记录集合称为窗口，此处的窗口并非“窗户”的含义， 而是代表范围， 这也是窗口函数名称的由来。
7. 专用窗口函数无需任何参数，因此括号内通常是空的
8. 原则上窗口函数只能在select子句中使用。
9. select product_id, product_name, sale_price, sum(sale_price) over (order by product_id) as current_sum from Product; //使用聚合函数作为窗口函数

10. 1 2 3

    select product_id, product_name, sale_price, avg(sale_price) over (order by product_id rows 2 preceding) as moving_avg from Product; //preceding 之前， following 之后，示例自己写

11. 1 2 3 4 5

    select product_id, product_name, sale_price, avg(sale_price) over (order by product_id rows between 1 preceding and 1 following) as moving_avg from Product;

12. select product_type, sum(sale_price) as sum_price from Product group by rollup(product_type); //使用rollup同时得出合计和小计

13. 1 2 3 4

    select grouping(product_type) as product_type, grouping(regist_date) as regist_date, sum(sale_price) as sum_price from Product group by rollup(product_type, regist_date);

14. 1 2 3 4 5 6 7 8 9

    select case when grouping(product_type) = 1 then '商品种类 合计' else product_type end as product_type, case when grouping(regist_date) = 1 then '登记日期 合计' else cast(regist_date as VARCHAR(16)) end as regist_date, sum(sale_price) as sum_price from Product group by rollup(product_type, regist_date); //看着头晕系列

15. 1 2 3 4 5 6 7 8 9

    select case when grouping(product_type) = 1 then '商品种类 合计' else product_type end as product_type, case when grouping(regist_date) = 1 then '登记日期 合计' else cast(regist_date as VARCHAR(16)) end as regist_date, sum(sale_price) as sum_price from Product group by cube(product_type, regist_date);

16. 1 2 3 4 5 6 7 8 9

    select case when grouping(product_type) = 1 then '商品种类 合计' else product_type end as product_type, case when grouping(regist_date) = 1 then '登记日期 合计' else cast(regist_date as VARCHAR(16)) end as regist_date, sum(sale_price) as sum_price from Product group by grouping sets (product_type, regist_date);

以上，sql入门已经完成（啊呸，心里有点b数好不好）所有源码都在啥鸟里面测试通过（大概）。进阶那本明年再说（如果不会压箱底的话），大家能看懂就加油看，看不懂就问度娘，或者问情书大佬（逃