数据库操作 Hive / sql sever

Introduction PV:

数据库在磁盘上就是一个文件；数据库管理系统是管理数据库的一个软件； 数据库：存放数据的仓库；Databsae,简称DB
模型训练需要使用大量的数据 使用python读取数据库再用 pandas 进行操作 占用时间/内存资源 并且处理大量数据时效率较低
实际使用时直接在数据库里进行简单的字符串操作/数据简单的加减乘除等 最后把处理后的数据结果表拉近python用于模型训练等操作

数据库基础

  • 数据表：
    在数据库(database)中,可以创建很多张表(table) ；
    通常情况下,一张表用于保存一类数据,例如网站中的所有用户信息会保存在一张表中,所有商品信息会保存在另一张表中

  • 表记录：
    数据表中(table)中,可以插入很多条记录；数据表往往保存一类数据,对应java中的一个类；
    而一条记录往往对应java中的一个具体的实例 （即表中的一行数据）

  • 数据库管理系统：对数据库进行统一地管理和控制，以保证数据库地安全性和完整性；

  • 数据库系统：包括了数据库、数据库管理系统、应用系统、数据库管理员 即软件+人。

  • 数据库服务器：
    数据库服务器其实就是一个软件,比如我们安装的mysql软件(或者mariadb软件)
    mysql服务器软件需要安装在服务器硬件上(就是一台计算机)才可以让外界来访问
    在mysql服务器中,可以创建很多的数据库(database

  • 数据库查询：select * from isupport_customer order by id desc（查询数据表中的相应数据）

  • 数据库的分类：数据库根据句存储采用的数据结构的不同可以分为许多种,其中包含

    • 早期的数据库类型：
      *层次式数据库
      *网络式数据库

    • 目前占市场主流的是
      *关系型数据库
      *非关系型数据库
      *键值对数据库,例如:MongDB,Redis

  • 关系型数据库：
    底层是以二维表的及其之间的关系所组成的数据库.

  • 常见关系型数据库：
    SQL Server 微软提供(收费.java中使用不多)
    Oracle 甲骨文公司(收费,功能强大,性能优异,java中使用者很多)
    DB2 IBM(收费,中型/大型,银行/电信等企业)

                   瑞典MySQL AB(免费,小型,性能较优异,适用于中小型项目,可集群)

    SQLite 迷你数据库

SQL语言：Structured Query Language：结构化的查询语言; SQL是操作所有关系型数据库的通用的语言

SQL语言的分类:

  • DDL - - 数据库定义语言,指Create,Allter,Drop等操作(即创建,删除,修改数据库和数据表)
  • DML - - 数据库操作语言,指Insert.Update.Delete等操作(即数据表中数据的增删改操作)
  • DQL - - 数据查询语言(指Selete操作,即数据表中数据的查询操作)
    其中DQL,也就是数据查询操作是在开发中使用最多的操作,也是我们关注的重点.

SQL增删改查：

  • 增：insert 表的名称 values 新增的内容
    例子：
    insert SQ_Academe(Academe,Specialty.Grade,Class)
    values(‘信息技术学院’，’计算机’,’2019’,’1班’)

  • 改：update 表的名称 set 要改的内容 where 条件
    例子：
    update SQ_Academe
    set Academe = ‘药数学院’
    where AcademeID =1010

  • 查：# select 查询的内容 from 表的名称
    例子：
    select * from SQ_Academe
    select * from pm_customer order by id desc;
    select * from isupport_customer where id =621 order by id desc;
    select * from isupport_customer_property where isupport_customer_id=621

  • 删：delete 表的名称 where 条件 —>相应行变成 NULL
    例子：
    delete SQ_Academe
    where AcademeID = 1010

hive

  • 创建表/新增数据/删除表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    DROP TABLE IF EXISTS 库名.表名;
    CREATE TABLE 库名.表名(
        字段1 bigint,
        字段2 int,
        字段3 string,
        字段4 timestamp
        ...
    );
    insert into 库名.表名

  • 删除表中某一部分数据

    1
    2
    INSERT OVERWRITE TABLE table_name SELECT * FROM table_name WHERE year>2018;
    #WHERE后的条件是需要保留的数据的查询结果,即删除2018年及以前的数据

  • Quantile

    1
    2
    3
    分析函数：用于等级、百分点、n分片等 Ntile 是Hive很强大的一个分析函数。
    ntile (num)  over ([partition_clause]  order_by_clause)  as your_bucket_num
    然后可以根据桶号，选取前或后 n分之几的数据

  • ROW_NUMBER
    hive的分组和组内排序—语法

    1
    2
    3
    4
    5
    row_number() over (partition by 字段a order by 计算项b desc ) rank
    rank是排序的别名
    partition by：类似hive的建表，分区的意思；
    order by ：排序，默认是升序，加desc降序；
    这里按字段a分区，对计算项b进行降序排序

  • LAG

    1
    2
    LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
    第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

  • 时间数据格式化操作
    hive 常用日期格式转换

    • 固定日期转换成时间戳
      select unix_timestamp(‘2016-08-16’,’yyyy-MM-dd’) –1471276800
      select unix_timestamp(‘20160816’,’yyyyMMdd’) –1471276800
      select unix_timestamp(‘2016-08-16T10:02:41Z’, “yyyy-MM-dd’T’HH:mm:ss’Z’”) –1471312961

    16/Mar/2017:12:25:01 +0800 转成正常格式（yyyy-MM-dd hh:mm:ss）
    select from_unixtime(to_unix_timestamp(‘16/Mar/2017:12:25:01 +0800’, ‘dd/MMM/yyy:HH:mm:ss Z’))

    • 时间戳转换程固定日期
      select from_unixtime(1471276800,’yyyy-MM-dd’) –2016-08-16
      select from_unixtime(1471276800,’yyyyMMdd’) –20160816
      select from_unixtime(1471312961) – 2016-08-16 10:02:41
      select from_unixtime( unix_timestamp(‘20160816’,’yyyyMMdd’),’yyyy-MM-dd’) –2016-08-16
      select date_format(‘2016-08-16’,’yyyyMMdd’) –20160816

    • 取当前时间
      select from_unixtime(unix_timestamp(),’yyyy-MM-dd HH:mm:ss’)
      select from_unixtime(unix_timestamp(),’yyyy-MM-dd’)

    • 返回日期时间字段中的日期部分 select to_date(‘2016-08-16 10:03:01’) –2016-08-16
      返回日期中的年 select year(‘2016-08-16 10:03:01’) –2016
      返回日期中的月 select month(‘2016-08-16 10:03:01’) –8
      返回日期中的日 select day(‘2016-08-16 10:03:01’) –16
      返回日期中的时 select hour(‘2016-08-16 10:03:01’) –10
      返回日期中的分 select minute(‘2016-08-16 10:03:01’) –3
      返回日期中的秒 select second(‘2016-08-16 10:03:01’) –1

    • 返回日期在当前的周数 select weekofyear(‘2016-08-16 10:03:01’) –33
      返回结束日期减去开始日期的天数 select datediff(‘2016-08-16’,’2016-08-11’)

    • 返回开始日期startdate增加days天后的日期 select date_add(‘2016-08-16’,10)
      返回开始日期startdate减少days天后的日期 select date_sub(‘2016-08-16’,10)

    • 返回当天三种方式
      SELECT CURRENT_DATE; –2017-06-15
      SELECT CURRENT_TIMESTAMP;–返回时分秒 –2017-06-15 19:54:44
      SELECT from_unixtime(unix_timestamp()); –2017-06-15 19:55:04

    • 返回当前时间戳 select current_timestamp–2018-06-18 10:37:53.278
      返回当月的第一天 select trunc(‘2016-08-16’,’MM’) –2016-08-01
      返回当年的第一天 select trunc(‘2016-08-16’,’YEAR’) –2016-01-01

    • 从时间数据中提取年/月/天等

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      select to_date(’2011-12-08 10:03:01′) from dual; -----返回日期时间字段中的日期部分:2011-12-08
      select year(‘2011-12-08 10:03:01’) ----返回日期中的年:2011
      select month(‘2011-12-08 10:03:01’) from iteblog; ----返回日期中的月份:12
      select day(‘2011-12-08 10:03:01’) from iteblog; -----返回日期中的天:8
      select hour(‘2011-12-08 10:03:01’) from iteblog; -----返回日期中的小时:10
      select second(‘2011-12-08 10:03:01’) from iteblog; -----返回日期中的秒:1
      select weekofyear(‘2011-12-08 10:03:01’) from iteblog; ----返回日期在当前的周数:49
      select datediff(‘2012-12-08’,‘2012-05-09’) from iteblog; -----返回结束日期减去开始日期的天数:213
      select date_add(‘2012-12-08’,10) from iteblog; ----返回开始日期startdate增加days天后的日期:2012-12-18
      select date_sub(‘2012-12-08’,10) from iteblog; -----返回开始日期startdate减少days天后的日期:2012-11-28

sql sever

  • 字符串操作
    字符串类型的数据切词提取有效信息：substring(字符串字段名,开始位置,结束位置)

    1
    2
    3
    select substring
    (字符串字段名,charindex('abc',字符串字段名)+11,ABS(charindex('bjsa',字符串字段名)-charindex('abc',字符串字段名)-14))
    FROM 库名.表名

    获取某个字符串在字段中的位置：charindex(字符串,字段名)

    1
    charindex('mainBody',content)+11

  • 排序规则不同导致join失败

    1
    2
    3
    无法改变数据库的情况下：
    1.在 on 后的字段后加上 collate Chinese_PRC_CS_AI_WS 指定排序规则
    2.在建表时给字符串类型的字段后面加上 collate Chinese_PRC_CS_AI_WS

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2024 HELLO WORLD All Rights Reserved.

UV : | PV :