您的位置: 首页  >  文章  >  Sql基础概念和常规用法示例

Sql基础概念和常规用法示例

分类: 文章 • 2024-06-23 22:15:16

总结一些Sql入门知识和常规用法示例，示例用到新表时会先列出表结构和关键字段

基础概念

  1. sql（Structured Query Language）：结构化查询语言
  2. 数据库：可以理解为一个Excel文件
  3. 数据表：可以理解为Excel文件中的一个sheet
  4. 一条数据：对应Excel的一行数据
  5. 表结构注释如下：

    Sql基础概念和常规用法示例

    字段：对应Excel的列
    索引：作用类比字典的目录，字段增加了索引，可以提高该字段作为查询条件时的查询速度，规则为：索引类型 索引名称（字段名）
    字符集：字符集设置错误会导致中文乱码
    Latin1 ： 老系统用的比较多
    utf8： 行业建议统一用utf8，避免很多问题
    utf8mb4：支持存储emoji表情信息

基础语法

  1. select : 选择查询结果包含哪些字段（ * ：代表查询所有字段）
  2. distinct：对结果去重
  3. from：从哪些表中查询数据
  4. where：设定查询条件
  5. 精确匹配：= 、!=、<>
  6. 范围查询：>、<、>=、<=
  7. 条件运算符 
    and ：多个条件的交集
or ：多个条件的并集
order by: 对查询结果按字段排序
asc 升序
desc 降序

    举例：

商家信息表结构(只列举了部分字段)：
CREATE TABLE `partner` (
  `id` int(11)  COMMENT '商家ID',
  `name` varchar(255) COMMENT '商家名称',
  `info_check` tinyint(1)  COMMENT '商家状态：-2 在线入驻审核中，-1 审核中，0 信息不完整，1 无网点经营信息，2 注销，3 合作中',
  `merchant_type` tinyint(1) COMMENT '商家类型 1 常规商家，2 专柜商家，3 集合店商家，4 自营转POP商家',
  `creation_time` int(10) COMMENT '商家创建时间',
  `cooperation_mode` tinyint(1) COMMENT '合作方式 0未设置，1特卖，2商城，3 特卖+商城',
  `confirm_time` datetime COMMENT '审核通过时间'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

1.查询出所有常规商家的商家ID、商家名称：
select id,name from partner where merchant_type=1;

2.查询出2019年1月1号之后创建，状态为合作中且商家类型为（常规商家或专柜商家）的商家的所有字段信息，查询结果按照商家id倒序排列：
select * from partner 
where 
  creation_time >= unix_timestamp('2019-01-01 00:00:00') 
  and info_check = 3 
  and (merchant_type = 1 or merchant_type = 2)
order by id desc;

3.查询出所有不同的商家类型：
select distinct merchant_type from partner

 

高级语法

  1. in、not in 
    查询商家类型为普通商家或专柜商家的所有商家
select * from partner where merchant_type in (1,2);

查询商家类型不是普通商家和专柜商家的所有商家
select * from partner where merchant_type not in (1,2);

     

  2. between 
    查询商城价在10到20元闭区间的sku信息
select * from partner_product_skus pps 
where pps.mall_price between 10 and 20

     

  3. join : 关联其他表（左右表都匹配才返回该行） 
    //商家用户表结构(只列了部分字段)
CREATE TABLE `partner_user` (
  `id` int(11) COMMENT '自增ID',
  `partner_id` int(11) COMMENT '商家ID 关联partner表的id',
  `username` varchar(100) COMMENT '用户名',
  `status` tinyint(4) COMMENT '子账号是否禁用：0 启用，1 禁用',
  `passport_uid` int(11) COMMENT 'Passport唯一用户ID',
  `staff_name` varchar(255) DEFAULT '' COMMENT '商家名称/负责人',
  `is_staff` tinyint(4) DEFAULT '0' COMMENT ' 是 否员工：0否，1是 ',
  `safe_email` varchar(50) DEFAULT '' COMMENT '安全邮箱',
  `safe_phone` varchar(128) DEFAULT '' COMMENT '安全手机'
) ENGINE=InnoDB AUTO_INCREMENT=35509 DEFAULT CHARSET=latin1

// 查询所有商家及商家主账号信息
select 
  p.id as partner_id,p.name,pu.username,pu.is_staff,pu.safe_email 
from 
  partner p join partner_user pu on pu.partner_id = p.id
where 
  pu.is_staff = 0 
order by p.id

     

  4. left join : 左关联（只要左表匹配则返回该行），可类比Excel的vlookup 
    // 查询所有商家及商家子账号信息
select 
	p.id as partner_id,p.name,pu.username,pu.is_staff,pu.safe_email 
from 
	partner p 
    left join partner_user pu on pu.partner_id = p.id and pu.is_staff = 1
where p.id in (10536,10493)
order by p.id desc
// 查询所有商家及商家子账号信息
select 
	p.id as partner_id,p.name,pu.username,pu.is_staff,pu.safe_email 
from 
	partner p 
    left join partner_user pu on pu.partner_id = p.id 
where p.id in (10536,10493) and pu.is_staff = 1
order by p.id desc
//注意上面两个sql的区别，如果商家没有子账号，第一个会返回商家信息，第二个不会返回商家信息

     

  5. left join : 左关联（只要左表匹配则返回该行），可类比Excel的vlookup
  6. right join：右关联（只要右表匹配则返回该行）
  7. full join：完全关联（只要有一个表匹配则返回该行）
  8. 模糊匹配：like、not like 
    '%bcd'：以bcd结尾
'abc%'：以abc开头
'%bc%'：包含bc
'%e_'：倒数第二个字符是'e'

//查询所有名称包含"测试"的商家
select * from partner where name like '%测试%'
//查询所有名称以"测试"开头的商家
select * from partner where name like '测试%'
//获取所有名称第二个汉字是"美"的商家(注意Latin1字符集一个汉字占用3个字符)
select * from partner where name like '___美%'

     

  9. 子查询：把查询结果作为一个表进行关联查询 
    //商家品牌表结构(只列了部分字段)
CREATE TABLE `partner_brand_drafts` (
  `id` int(10) COMMENT '自增ID',,
  `brand_id` int(10)COMMENT '品牌ID',
  `brand_label` varchar(45) COMMENT '品牌缩写',
  `partner_id` int(11)COMMENT '商家ID 关联partner表的id',
  `shipping_system_id` int(11) COMMENT '审核通过的当前运货仓库ID',
  `chinese_name` varchar(100) COMMENT '品牌中文名',
  `status` tinyint(4) COMMENT '状态 草稿0 待审核1 驳回2 审核通过3 已删除4',
  `is_enabled` tinyint(1) DEFAULT '1' COMMENT '开启或禁用品牌'
) DEFAULT CHARSET=latin1 COMMENT='POP商家品牌表'

//先查询所有有子账号的专柜商家，再查询这些商家的所有审核通过的品牌信息
select ppu.partner_id as partner_id,ppu.name,pbd.brand_id,pbd.brand_label
from 
(
	select distinct pu.partner_id,p.name  
  	from partner_user pu 
  	join partner p on p.id = pu.partner_id 
  	where pu.is_staff = 1 and p.merchant_type = 2
) ppu join partner_brand_drafts pbd on pbd.partner_id = ppu.partner_id
where pbd.status = 2

     

  10. 函数
  • count()：统计条数 
    //统计所有专柜商家的数量
select count(*) from partner where merchant_type = 2

     

  • sum()：对字段求和 
    //获取2019年之后所有已处罚的虚假发货罚款金额
select sum(pfs.amerce_price) from partner_fake_shipping pfs where pfs.amercing_time>= '2019-01-01' and pfs.status = 'amerced'

     

  • group by： 按字段汇总聚合 
    //商家虚假发货表结构(只列出部分字段)
CREATE TABLE `partner_fake_shipping` (
  `id` int(11) COMMENT '主键id',
  `shipping_no` varchar(50) COMMENT '包裹号',
  `partner_id` int(11) COMMENT '商家id',
  `type` varchar(25) COMMENT '商家类型 partner : 国内 ，global : 海淘',
  `settlment_id` varchar(45) COMMENT '对账单ID',
  `status` varchar(20) COMMENT '状态,new:新增，valid:合格,invalid:虚假发货,appealing:申诉中,no_amerce:不处罚,amercing:处罚中,amerced:已罚款',
  `amerce_price` decimal(10,2) COMMENT '罚款金额',
  `shipping_create_time` int(11) COMMENT '包裹生成时间',
  `last_update_time` timestamp COMMENT '最后更新时间'
) DEFAULT CHARSET=latin1 COMMENT='POP虚假发货表'

//按汇总2019年之后每个商家已处罚的虚假发货罚款金额及罚款订单数
select
  pfs.partner_id,
  count(DISTINCT pfs.shipping_no) as total_shippings,
  sum(pfs.amerce_price)
from
  partner_fake_shipping pfs
where
  pfs.last_update_time >= '2019-01-01'
  and pfs.status = 'amerced'
group by
  pfs.partner_id

     

  • having：常用于对聚合后的数据进行条件过滤 
    //按商家汇总2019年之后已处罚的虚假发货罚款金额及罚款订单数，并且只展示总罚款金额大于100元的商家
select
  pfs.partner_id,
  count(DISTINCT pfs.shipping_no) as total_shippings,
  sum(pfs.amerce_price) as sum_amerce_price
from
  partner_fake_shipping pfs
where
  pfs.last_update_time >= '2019-01-01'
  and pfs.status = 'amerced'
group by
  pfs.partner_id
having sum_amerce_price>100

     

  • avg()：求平均值
  • max()：求最大值
  • min()：求最小值
  • length()：获取字段值长度 
    //按商家汇总2019年之后已处罚的虚假发货罚款订单数、罚款金额、每单平均罚款金额、每单最大罚款金额、每单最小罚款金额，查询结果包含商家id，商家名称，商家名称的长度
select
  pfs.partner_id,
  p.name as partner_name,
  length(p.name) as partner_name_length,
  count(DISTINCT pfs.shipping_no) as total_shippings,
  sum(pfs.amerce_price) as sum_amerce_price,
  avg(pfs.amerce_price) as avg_amerce_price,
  max(pfs.amerce_price) as max_amerce_price,
  min(pfs.amerce_price) as min_amerce_price
from
  partner_fake_shipping pfs
  join partner p on p.id = pfs.partner_id
where
  pfs.last_update_time >= '2019-01-01'
  and pfs.status = 'amerced'
group by
  pfs.partner_id
having
  sum_amerce_price > 100

//先获取按商家汇总2019年之后已处罚的虚假发货罚款订单数、罚款金额，再计算所有商家总罚款订单数、总罚款金额、平均罚款金额、最大罚款金额、最小罚款金额
select
  sum(pfs.total_shippings) as total_shippings,
  sum(pfs.sum_amerce_price) as sum_amerce_price,
  avg(pfs.sum_amerce_price) as avg_sum_amerce_price,
  max(pfs.sum_amerce_price) as max_sum_amerce_price,
  min(pfs.sum_amerce_price) as min_sum_amerce_price
from
  (
    select
      pfs.partner_id,
      count(DISTINCT pfs.shipping_no) as total_shippings,
      sum(pfs.amerce_price) as sum_amerce_price
    from
      partner_fake_shipping pfs
    where
      pfs.last_update_time >= '2019-01-01'
      and pfs.status = 'amerced'
    group by
      pfs.partner_id
  ) pfs
where
  pfs.sum_amerce_price > 100

     

  • unix_timestamp()：把标准时间转换为unix时间戳
  • from_unixtime()：把unix时间戳转换为标准时间 
    //unix_timestamp 与 from_unixtime用法
select
  p.id as partner_id,
  p.name,
  p.creation_time,
  from_unixtime(p.creation_time, '%Y-%m-%d %H:%i:%s') as creation_time_format
from
  partner p
where
  p.creation_time >= unix_timestamp('2019-01-01 00:00:00');

     

相关推荐