postgresql笔记
因为傻鸟里面自带postgresql,因此就买了sql基础教程和sql进阶教程,依然以笔记的方式进行记录
初始
- service postgresql start //因为现在傻鸟里面postgresql不再开机自启了,所以需要手动启动。
- su - postgresql //密码为该用户名的密码
- psql -U postgresql -d shop
数据准备
- create database shop; //创建数据库
1
2
3
4
5
6
7
8create 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) );注:定长字符串(即char类型)在字符数未达到最大长度时会用半角空格补足。
- drop table Product; //删除表
- alter table Product ADD COLUMN product_name_pinyin VARCHAR(100); //追加列
- alter table Product DROP COLUMN product_name_pinyin; //删除列
1
2
3
4
5
6
7
8
9
10BEGIN 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;ALTER TABLE Poduct RENAME TO Product; //修改表名
1
2
3
4
5
6
7
8CREATE 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) );1
2
3
4
5
6
7
8create 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) );1
2
3
4
5create table ProductType
( product_type VARCHAR(32) NOT NULL,
sum_sale_price INTEGER ,
sum_purchase_price INTEGER ,
PRIMARY KEY (product_type) );1
2
3
4create table SampleMath
(m NUMERIC (10,3),
n INTEGER ,
p INTEGER);1
2
3
4
5
6
7
8
9
10
11
12
13BEGIN 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;1
2
3
4create table SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
Str3 VARCHAR(40));1
2
3
4
5
6
7
8
9
10
11
12
13BEGIN 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;1
2
3create table SampleLike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol));1
2
3
4
5
6
7
8BEGIN 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;1
2
3
4
5
6create 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));1
2
3
4
5
6
7
8
9
10
11
12
13
14
15BEGIN 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;1
2
3
4
5
6
7
8
9create 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));1
2
3
4
5
6
7BEGIN 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;1
2
3
4
5
6create 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));1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18BEGIN 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毛线])
- select * from Product; //大力出奇迹
- select product_id, product_name from Product;
- select product_id as id, product_name as name, purchase_price as price from Product; //起外号,当然也可以用中文,但是需要使用双引号引起来(书上是这样写的但是在鸟里面是不需要用双引号的)。
- select ‘fff’ as string, 18 as number, product_id, product_name from Product; //查询中插入常数或者字符串。
- select DISTINCT product_type from Product; //清除重复内容 注:NULL的条目也会缩减为一条,并且DISTINCT只能放在第一个列名之前。
- select * from Product where product_type = ‘衣服’;
- 注释语句: –或者/××/
- select product_name, sale_price, sale_price * 2 as “salse_price_x2” from Product; //进行四则运算。 注:包含null的运算最后结果肯定为NULL。
- select * from Product where sale_price <> 500; //不等于500
- select * from Product where sale_price <= 500; //小于等于500
- 注:一定让不等号在左,等号在右。
- 注:不能对NULL使用比较运算符。
- select * from Product where purchase_price is null; //字段内容为空
- select * from Product where not sale_price <= 500; //大于500
- AND运算符在两侧的查询条件同时成立时整个查询条件才成立。
- OR运算符在其两侧的查询条件有一个成立时整个查询条件成立。
- select * from Product where product_type = ‘厨房用具’ and sale_price >= 3000;
- select * from Product where product_type = ‘厨房用具’ or sale_price >= 3000;
- AND运算符优先级高于OR,可以使用括号调整优先级顺序。
- select * from Product where product_type = ‘办公用品’ and ( regist_date = ‘2009-09-11’ or regist_date = ‘2009-09-20’);
对表进行聚合查询
- 常用聚合函数:
- COUNT: 计算表中的记录数(行数)
- SUM: 计算表中数值列中数据的合计值
- AVG: 计算表中数值列中数据的平均值
- MAX: 求出表中任意列中数据的最大值
- MIN: 求出表中任意列中数据的最小值
- select count(×) from Product;
- select sum(sale_price) from Product;
- select sum(sale_price) as sale_price ,sum(purchase_price) as purchase_price from Product;
- select avg(sale_price) from Product;
- select MAX(sale_price), MIN(purchase_price) from Product;
- select count(distinct product_type) from Product; //计算去除重复数据之后的数据行数。
- select product_type, COUNT(×) from Product GROUP BY product_type; //分组计算
- select purchase_price, COUNT(×) FROM Product where product_type = ‘衣服’ GROUP BY purchase_price;
- 注:group by 子句中不能使用别名
- 只有select子句和having子句以及order by子句能使用COUNT等聚合函数
- select product_type, count(×) from Product group by product_type having COUNT(×) = 2; //having 子句使用方法
- select product_type, avg(sale_price) from Product group by product_type having avg(sale_price) >= 2500; //having 子句使用方法
- 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;
- select * from Product order by sale_price; //升序排序
- select * from Product order by sale_price desc; //降序排序
- select * from Product order by sale_price, purchase_price; 在sale_price相同时使用purchase_price排序。
- select product_id as id, sale_price as sp from Product order by id;
- select子句中未查询的列也可以在order by子句中使用。
- order by 子句也可以使用聚合函数。
- select product_type, count(×) from Product group by product_type order by count(×);
数据更新
恩,万物基于insert(create你闭嘴)。
- insert into ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) values (‘0001’, ‘T恤衫’, ‘衣服’, 1000, 500, ‘2009-09-20’);
1
2
3INSERT INTO ProductIns values ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');insert into ProductIns values (‘0005’, ‘高压锅’, ‘厨房用具’, 6800, 5000, ‘2009-01-15’); //省略列清单
- insert into ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) values (‘0006’, ‘叉子’, ‘厨房用具’, 500, NULL, ‘2009-09-20’); //插入NULL
- insert into ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) values (‘0007’, ‘擦菜板’, ‘厨房用具’, DEFAULT, 790, ‘2009-04-28’); //显式插入默认值
- 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一个表中的数据
- 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; //计算统计值
- delete from Product where sale_price >= 4000;
- update Product set regist_date = ‘2009-10-10’;
- update Product set sale_price = sale_price * 10 where product_type = ‘厨房用具’;
- update Product set sale_price = sale_price × 10, purchase_price = purchase_price / 2 where product_type = ‘厨房用具’ ; //多列更新
- update Product set (sale_price, purchase_price) = (sale_price × 10, purchase_price / 2) where product_type = ‘厨房用具’;
- 事务:需要在同一个处理单元中执行的一系列更新处理的集合。
- COMMIT 在事务末尾为提交, rollback 数据回滚,回滚到开始事务之前的状态。
- DBMS事务的四种特性:
- 原子性(Atomicity):指在事务结束时,所包含的更新处理要么全都执行,要么完全不执行。
- 一致性(Consistency):事务中所包含的数据更新处理要满足数据库提前设置的约束。
- 隔离性(isolation):不同事务之间互不干扰。例如在某个事务中进行的更改,在该事务完成之前对其他事务而言是不可见的。
- 持久性(Durability):在事务结束后,DBMS能保证该时间点的数据状态会被保存。
复杂查询
- 视图和表的区别:
- 表中保存的是实际的数据。
- 视图中保存的是select语句,视图本身并不保存数据。
- 视图的优点:
- 因为视图不保存数据,因此节省存储设备的容量。
- 将频繁使用的select语句保存成视图,这样就不需要每次重写select语句。
1
2
3create view ProductSum(product_type, cnt_product)
as
select product_type, count(*) from Product group by product_type;select * from ProductSum; //查询试图(貌似和查表没啥区别)
- 可以创建多重视图(少用,因为貌似会降低sql的性能)
视图的限制
- 定义视图时不可以使用order by语句。因为视图和表一样,数据行都是没有顺序的。
- 可以更新视图的限制条件:
- select语句未使用distinct。
- from语句只有一张表。
- 未使用group by语句。
- 未使用having语句。
1
2
3create view ProductJim(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
as
select * from Product where product_type = '办公用品';postgresql中的视图会被初始设定为只读,如果需要允许更新时,需要执行下面的语句。
1
2
3
4
5
6
7
8
9create 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);drop view ProductJim; //删除视图
- drop view ProductJim CASCADE;删除关联视图
- 子查询:一次性的视图,用完即删。就是将用来定义视图的select语句直接写到from子句中。
- select product_type, cnt_product from (select product_type, count(×) as cnt_product from Product group by product_type) as ProductSum; //子查询
- 子查询可以无限嵌套
- 原则上子查询必须设定名称。
- 标量子查询就是必须并且只能返回一行一列的结果的子查询。
- select product_id, product_name,sale_price from Product where sale_price > (select avg(sale_price) from Product); //标量子查询
- 标量子查询的语句可以用在任何可以使用单一值的地方。
- select product_id, product_name, sale_price, (select avg(sale_price) from Product) as avg_price from Product;
- 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
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;
1
2
3
4
5
6
7select 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;
表的加减法
- select product_id, product_name from Product union select product_id, product_name from Product2; //并集运算
- select product_id, product_name from Product union all select product_id, product_name from Product2; //并集运算但是会显示重复记录
- union运算注意事项:
- 作为运算对象的记录的列数必须相同。
- 作为运算对象的记录中列的类型必须一致。
- 可以使用任意select语句,但是order by 语句只能在最后使用一次。
- select product_id, product_name from Product intersect select product_id, product_name from Product2; //交集运算
- select product_id, product_name from Product intersect all select product_id, product_name from Product2; //交集运算并显示重复记录
- select product_id, product_name from Product except select product_id, product_name from Product2; //差集运算(啥,你问什么是差集。。。。。你可以去问问你高(高中)数(数学)老师,顺便拜个年。)
- 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; //对两张表进行内联结
- 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’; //对两张表进行内联结并查询东京店的内容
- 内联结要点
- 需要在from子句中指定多张表。
- 进行内联结的时候必须使用on子句,并且要书写在from和where之间。on子句也可以使用and和or
- 使用联结时select子句中的列需要按照<表的别名或者表名>.<列名>的格式来进行书写。
- 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 子句中写在右侧的表为主表,最终查询结果会包含主表的所有数据。
1
2
3
4
5
6
7select 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'; //是不是看着头晕,是不是以为这里会有分析,没事,晕啊晕啊也就习惯了select SP.shop_id, SP.shop_name, SP.product_id, P.product_name from ShopProduct as SP cross join Product as P; //交叉联结,又称迪卡儿积
SQL高级处理
- OLAP函数,你可以称为窗口函数或者分析函数,可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。
- 窗口函数的公式 <窗口函数> over ([PARTITION BY <列清单>] order by <排序用列清单>)
- 可以作为窗口函数的聚合函数
- 能够作为窗口函数的聚合函数(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
- select product_name, product_type, sale_price, RANK () OVER (PARTITION BY product_type order by sale_price) as ranking from Product; // 根据不同的商品品类,按照销售单价从低到高的顺序创建排序表
- select product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) as ranking from Product; //不按照商品品类,直接进行排序
- 通过PARTITION BY 分组之后的记录集合称为窗口,此处的窗口并非“窗户”的含义, 而是代表范围, 这也是窗口函数名称的由来。
- 专用窗口函数无需任何参数,因此括号内通常是空的
- 原则上窗口函数只能在select子句中使用。
- select product_id, product_name, sale_price, sum(sale_price) over (order by product_id) as current_sum from Product; //使用聚合函数作为窗口函数
1
2
3select product_id, product_name, sale_price, avg(sale_price)
over (order by product_id rows 2 preceding) as moving_avg
from Product; //preceding 之前, following 之后,示例自己写1
2
3
4
5select 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;select product_type, sum(sale_price) as sum_price from Product group by rollup(product_type); //使用rollup同时得出合计和小计
1
2
3
4select 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);1
2
3
4
5
6
7
8
9select 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); //看着头晕系列1
2
3
4
5
6
7
8
9select 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);sets 取得部分组合的结果 1
2
3
4
5
6
7
8
9select 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数好不好)所有源码都在啥鸟里面测试通过(大概)。进阶那本明年再说(如果不会压箱底的话),大家能看懂就加油看,看不懂就问度娘,或者问情书大佬(逃