sql入门

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. sets 取得部分组合的结果
    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数好不好)所有源码都在啥鸟里面测试通过(大概)。进阶那本明年再说(如果不会压箱底的话),大家能看懂就加油看,看不懂就问度娘,或者问情书大佬(逃

文章目录
  1. 1. postgresql笔记
    1. 1.1. 初始
    2. 1.2. 数据准备
    3. 1.3. select语句
    4. 1.4. 对表进行聚合查询
    5. 1.5. 数据更新
    6. 1.6. 复杂查询
    7. 1.7. 函数、谓词、case表达式
    8. 1.8. 表的加减法
    9. 1.9. SQL高级处理
,