SQL基础教程（2）

作者: leejnull | 来源:发表于2020-01-10 17:19 被阅读0次

函数、谓词、CASE表达式

函数

先创建表和数据

create table SampleMath (
    m numeric (10, 3),
    n integer ,
    p integer
);

start transaction;
insert into SampleMath values (500, 0, null);
insert into SampleMath values (-180, 0, null);
insert into SampleMath values (null, null, null);
insert into SampleMath values (null, 7, 3);
insert into SampleMath values (null, 5, 2);
insert into SampleMath values (null, 4, null);
insert into SampleMath values (8, null, 3);
insert into SampleMath values (2.27, 1, null);
insert into SampleMath values (5.555, 2, null);
insert into SampleMath values (null, 1, null);
insert into SampleMath values (8.76, null, null);
commit;

abs(数值)

计算绝对值

select m, abs(m) as abs_col from SampleMath;

mod(被除数, 除数)

计算除法余数（求余）

select n, p, mod(n, p) as mod_col from SampleMath;

round(对象数值, 保留小数的位数)

四舍五入，如果指定位数为1，那么会对小数点第2位进行四舍五入处理

select m, n, round(m, n) as round_col from SampleMath;

字符串拼接函数

Mysql 中使用 concat， PostgreSql 中用 ||

select str1, str2, concat(str1, str2) as str_concat from SampleStr;

length函数

字符串长度，注意这里由于每个DBMS的计算方式不同，结果可能不一样，这涉及到字符串的字节

select str1, length(str1) as len_str from SampleStr;

lower(字符串)

小写转换，对应的upper是大写转换

select str1, lower(str1) as low_str from SampleStr where str1 in ('ABC', 'aBC', 'abc', '山田');

replace(对象字符串, 替换前的字符串, 替换后的字符串)

select str1, str2, str3, replace(str1, str2, str3) as rep_str from SampleStr;

substring(对象的字符串 from 截取的起始位置 for 截取的字符数)

select str1, substring(str1 from 3 for 2) as sub_str from SampleStr;

current_date 当前日期

select current_date;

current_time 当前时间

select current_time;

current_timestamp 当前日期和时间

select current_timestamp;

extract 截取日期元素

extract(日期元素 from 日期)

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(minute from current_timestamp) as minute,
       extract(second from current_timestamp) as second;

cast 类型转换

case(转换前的值 as 想要转换的数据类型)

select cast('0001' as signed integer) as int_col;
select cast('2009-12-14' as date) as date_col;

coalesce 将NULL转换为其他值

coalesce(数据1, 数据2, 数据3...)
是sql特有的函数。该函数会返回可变参数中左侧开始第1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
在sql语句中将NULL转换为其他值时就会用到转换函数。

select coalesce(null, 1) as col_1,
       coalesce(null, 'test', null) as col_2,
       coalesce(null, null, '2009-11-01') as col_3;
       
select coalesce(str2, 'NULL') from SampleStr;

谓词

like 字符串的部分一致查询

查询结果有前方一致、中间一致和后方一致这三个概念

前方一致：其实部分相同
中间一致：存在相同即可，就是说包括前方一致和后方一致
后方一致：末尾部分相同

-- 前方一致
select * from SampleLike where strcol like 'ddd%';

-- 中间一致
select * from SampleLike where strcol like '%ddd%';

-- 后方一致
select * from SampleLike where strcol like '%ddd';

% 代表 0字符以上的任意字符串
_ 代表任意一个字符，有几个 _ 就只能有几个字符

select * from SampleLike where strcol like 'abc__';
select * from SampleLike where strcol like 'abc___';

between 范围查询

包含临界值

select product_name, sale_price from Product where sale_price between 100 and 1000;

is null 、 is not null

判断是否为null

select product_name, purchase_price from Product where purchase_price is null;
select product_name, purchase_price from Product where purchase_price is not null;

in -- or的简便用法

否定形式是 not in

select product_name, purchase_price from Product where purchase_price = 320 or purchase_price = 500 or purchase_price = 5000;

select product_name, purchase_price from Product where purchase_price in (320, 500, 5000);

select product_name, purchase_price from Product where purchase_price not in (320, 500, 5000);

使用子查询作为in谓词的参数

先创建一个商品商店的关联表

create table ShopProduct (
    shop_id varchar(4) not null ,
    shop_name varchar(200) not null ,
    product_id varchar(4) not null ,
    quantity integer not null ,
    primary key (shop_id, product_id)
);

start transaction ;
insert into ShopProduct values ('000A', '东京', '0001', 30);
insert into ShopProduct values ('000A', '东京', '0002', 50);
insert into ShopProduct values ('000A', '东京', '0003', 15);
insert into ShopProduct values ('000B', '名古屋', '0002', 30);
insert into ShopProduct values ('000B', '名古屋', '0003', 120);
insert into ShopProduct values ('000B', '名古屋', '0004', 20);
insert into ShopProduct values ('000B', '名古屋', '0006', 10);
insert into ShopProduct values ('000B', '名古屋', '0007', 40);
insert into ShopProduct values ('000C', '大阪', '0003', 20);
insert into ShopProduct values ('000C', '大阪', '0004', 50);
insert into ShopProduct values ('000C', '大阪', '0006', 90);
insert into ShopProduct values ('000C', '大阪', '0007', 70);
insert into ShopProduct values ('000D', '福冈', '0001', 100);
commit ;

找出在大阪销售的商品，这里就要用到子查询

select product_name, sale_price from Product where product_id in (select product_id from ShopProduct where shop_id = '000C');

exist 谓词

理论上都可以用 in 和 not in 来代替。
只有1个参数，该参数通常都会是一个子查询。
exist通常都会使用关联子查询作为参数

同样的，使用 not exist 表示相反的结果

select product_name, sale_price from Product as P where exists(select * from ShopProduct as SP where SP.shop_id = '000C'
    and SP.product_id = P.product_id);

case表达式

和if-else差不多，有多个条件需要判断，最终只会有一个返回结果

select product_name,
       case when product_type = '衣服' then concat('A: ', product_type)
            when product_type = '办公用品' then concat('B: ', product_type)
            when product_type = '厨房用具' then concat('C: ', product_type)
            else null
           end as abc_product_type
from Product;

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;

上面使用的是搜索CASE表达式，下面用简单CASE表达式来实现以下

select product_name,
       case product_type
           when '衣服' then concat('A: ', product_type)
           when '办公用品' then concat('B: ', product_type)
           when '厨房用具' then concat('C: ', product_type)
           else null
        end as abc_product_type
from Product;

简单case表达式看上去和代码里的switch差不多了

集合运算

通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。

表的加减法

表的加法 - union（并集）

会去除重复的记录

select product_id, product_name from Product union select product_id, product_name from Product2;

注意事项

select的列必须一致
select的列的类型必须一致
如果有order by的话，必须放在最后使用

保留重复行，在union后面加一个all即可

select product_id, product_name from Product union all select product_id, product_name from Product2;

选取表中公共部分 - intersect（MySQL不支持）

希望保留重复行，同样是加上all

select product_id, product_name from Product intersect select product_id, product_name from Product2;

记录的减法 - except（MySQL不支持）

要注意左右关系，左边 - 右边

select product_id, product_name from Product except select product_id, product_name from Product2 order by product_id;

联结（以列为单位对表进行联结）

一个表中的数据往往不够，这时候要从别的表中把列拿过来，这时候用联结
以表A的列作为桥梁，将表B中满足同样条件的列汇集到同一结果之中

内联结 - inner join

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

内联结要点

进行联结时需要在from字句中使用多张表
on必不可少，它指定了联结条件，需要指定多个键时，可以使用and、or。必须在from和where之间
select指定列时，有些列是共有的，有些列是独有的，独有的列可以不写<表的别名>.<列名>这种形式，共有的一定要写，这里为了不混乱，全都写上表名

内联结和where子句结合使用

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';

联结之后的结果可以想象为一个新表，我们可以对这个表使用where，group by，having，order by等工具

外连接 - outer join

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;

外连接的结果比内联结多了两条数据，这两条数据是ShopProduct中product_id没有的。也就是说，当我使用外连接时，先像内联结一样，找出联结点关联的数据，如果还有没有关联到的数据，就继续取出来。这里有一个left和right，就涉及到取哪边的数据了。

select SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
from Product as P left outer join ShopProduct as SP on SP.product_id = P.product_id;

这两条sql结果是一样的

3张以上表的联结

表的联结是没有数量限制的

准备一个表

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)
);

start 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;

现在需要把仓库P001的产品数量，和商店的信息、产品信息展示出来

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';

这里先把ShopProduct表和Product表联结，再和InventoryProduct表联结，Product表就不需要再和InventoryProduct表联结了

交叉联结 - cross join

很少使用，但是是所有联结运算的基础

select SP.shop_id, SP.shop_name, SP.product_id, P.product_name
from ShopProduct as SP cross join Product as P;

结果有104个记录，ShopProduct有13条记录，Product有8条记录，13x8=104。是两个表交叉组合的结果。
所以，内联结是交叉联结的一部分，”内“也可以理解为”包含在交叉联结结果中的部分“。相反，外联结的”外“可以理解为”交叉联结结果之外的部分“

来自 https://leejnull.github.io/2020/01/10/2020-01-10-01/