美文网首页
SQL基础教程（2）

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;

注意事项

  1. select的列必须一致
  2. select的列的类型必须一致
  3. 如果有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

内联结要点

  1. 进行联结时需要在from字句中使用多张表
  2. on必不可少，它指定了联结条件，需要指定多个键时，可以使用and、or。必须在from和where之间
  3. 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/

相关文章

  • MySQL一一sql基本语法

    sql基础教程 sql教程 什么是sql？ SQL 指结构化查询语言 SQL 使我们有能力访问数据库 SQL 是一...

  • SQL基础

    SQL基础教程。 注意： SQL 对大小写不敏感！ SQL DML 和 DDL 可以把 SQL 分为两个部分：数据...

  • SQL基础教程（2）

    函数、谓词、CASE表达式 函数 先创建表和数据 abs(数值) 计算绝对值 mod(被除数, 除数) 计算除法余...

  • 视图与子查询

    《SQL基础教程第2版》Chap.5练习题 5-2 向视图 ViewPractice5_1 插入数据. ❓为什么S...

  • SQL基础教程：搭建SQL开发环境

    视频教程地址：SQL基础教程：搭建SQL开发环境_哔哩哔哩_bilibili[https://www.bilibi...

  • SQL 学习笔记

    《SQL 基础教程》学习笔记 01 - 数据库和 SQL 02 - 表的创建、删除和更新 03 - 查询基础 04...

  • PL_SQL_Oracle基础教程.pdf

    【下载地址】 PL /SQL基础教程-PL/SQL是ORACLE对标准数据库语言的扩展，ORACLE公司已经将PL...

  • SQL基础<一>

    前言 计划 传送门：SQL基础教程 数据库和SQL 1.数据库 提到SQL，首先必须谈到数据库，那么什么是数据库？...

  • 《sql基础教程》书里的sql文件如何导入数据库？

    对于刚入门学习sql的，我只推荐一本书Mick的《SQL基础教程》。网上很多人推荐《SQL必知必会》，其实这本书更...

  • SQL基础教程-1章1节：数据库结构和SQL书写规则

    视频教程地址：SQL基础教程-1章1节：数据库结构和SQL书写规则_哔哩哔哩_bilibili[https://w...

网友评论

      本文标题：SQL基础教程（2）

      本文链接：https://www.haomeiwen.com/subject/tdlmactx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|SQL基础教程（2）|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！